1 --CREATE OR REPLACE VIEW MID_V_SAFE_I03_POLICY AS
2 SELECT
3 T1.STATMONTH, -- 统计月
4 T1.STATDATE, -- 统计日
5 T1.COMCODE, -- 填报机构代码
6 T1.CLASSCODE, -- 保险类别
7 T1.POLICY_NATIONALITY_TYPE, -- 保单相关方所属国家/地区
8 T1.POLICY_DEPARTMENT_TYPE, -- 保单相关方所属部门
9 T1.POLICY_RELATIONSHIP_TYPE, -- 保单相关方与本机构的关系
10 T1.POLICY_NO, -- 保单号
11 T1.ENDORSE_NO, -- 批单号
12 T1.OUTWARD_COMCODE, -- 分出方公司代码
13 T1.CURRENCY_CODE_PREM, -- 币种代码-保费
14 T1.CURRENCY_NAME_PREM, -- 币种-保费
15 T1.NET_PREMIUM, -- 净额保费
16 SUM(T1.PREM_EXPEND_THIS_MONTH) AS PREM_EXPEND_THIS_MONTH, -- 本月末分出业务保费支出
17 SUM(T1.PREM_EXPEND_LAST_MONTH) AS PREM_EXPEND_LAST_MONTH, -- 上月末分出业务保费支出
18 SUM(T1.FEE_REVENUE_THIS_MONTH) AS FEE_REVENUE_THIS_MONTH, -- 本月末摊回分保费用收入
19 SUM(T1.FEE_REVENUE_LAST_MONTH) AS FEE_REVENUE_LAST_MONTH, -- 上月末摊回分保费用收入
20 SUM(T1.CLAIM_REVENUE_THIS_MONTH) AS CLAIM_REVENUE_THIS_MONTH, -- 本月末摊回赔付成本收入
21 SUM(T1.CLAIM_REVENUE_LAST_MONTH) AS CLAIM_REVENUE_LAST_MONTH, -- 上月末摊回赔付成本收入
22 SUM(T1.OS_THIS_MONTH) AS OS_THIS_MONTH, -- 上月末应收分保未决
23 SUM(T1.OS_LAST_MONTH) AS OS_LAST_MONTH, -- 本月末应收分保未决
24 SUM(T1.UPR_THIS_MONTH) AS UPR_THIS_MONTH,-- 本月末应收分保未到期保费
25 SUM(T1.UPR_LAST_MONTH) AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
26 SUM(T1.IBNR_THIS_MONTH) AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
27 SUM(T1.IBNR_LAST_MONTH) AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
28 T1.GROUP_FLAG AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
29 T1.BUSINESS_TYPE AS BUSINESS_TYPE, -- 业务线:1-车险,2-零售,3-健康险,4-商业险
30 T1.ETL_UPDATE_DATE AS ETL_UPDATE_DATE -- 数据调整时间
31 FROM
32 (
33 --未决
34 SELECT OM.STATMONTH AS STATMONTH, -- 统计月
35 ADD_MONTHS(TO_DATE(OM.STATMONTH, 'YYYYMM') - 1, 1) AS STATDATE, -- 统计日
36 A1.COMCODE AS COMCODE, -- 填报机构代码
37 A1.CLASSCODE AS CLASSCODE, -- 保险类别
38 A1.POLICY_NATIONALITY_TYPE AS POLICY_NATIONALITY_TYPE, -- 保单持有人所属国家/地区
39 A1.POLICY_DEPARTMENT_TYPE AS POLICY_DEPARTMENT_TYPE, -- 保单持有人所属部门
40 A1.POLICY_RELATIONSHIP_TYPE AS POLICY_RELATIONSHIP_TYPE, -- 保单持有人与本机构的关系
41 OM.POLICY_NO AS POLICY_NO, -- 保单号
42 OM.ENDORSE_NO AS ENDORSE_NO, -- 批单号
43 OM.OUTWARD_COMCODE AS OUTWARD_COMCODE, -- 分出方公司代码
44 OM.CURRENCY_CODE_OS AS CURRENCY_CODE_PREM, -- 币种代码-保费
45 OM.CURRENCY_NAME_OS AS CURRENCY_NAME_PREM, -- 币种-保费
46 A1.NET_PREMIUM_ORIG AS NET_PREMIUM, -- 净额保费
47
48 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
49 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS PREM_EXPEND_THIS_MONTH, -- 本月末分出业务保费支出 ---代表应付，应付代表贷
50 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
51 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS PREM_EXPEND_LAST_MONTH, -- 上月末分出业务保费支出 ---代表应付，应付代表贷
52
53 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
54 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS FEE_REVENUE_THIS_MONTH, -- 本月末摊回分保费用收入 --代表应收，应付代表借
55 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
56 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS FEE_REVENUE_LAST_MONTH, -- 上月末摊回分保费用收入 --代表应收，应付代表借
57
58 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
59 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS CLAIM_REVENUE_THIS_MONTH, -- 本月末摊回赔付成本收入
60 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
61 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS CLAIM_REVENUE_LAST_MONTH, -- 上月末摊回赔付成本收入
62 OM1.OS OS_THIS_MONTH, -- 上月末应收分保未决
63 OM.OS AS OS_LAST_MONTH, -- 本月末应收分保未决
64 0 AS UPR_THIS_MONTH, -- 本月末应收分保未到期保费
65 0 AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
66 0 AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
67 0 AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
68 A1.GROUP_FLAG AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
69 A1.BUSINESS_TYPE AS BUSINESS_TYPE, -- 业务线:1-车险,2-零售,3-健康险,4-商业险
70 OM.ETL_UPDATE_DATE AS ETL_UPDATE_DATE -- 数据调整时间
71 FROM EDW_OPR.GSL_V_SAFE_OS_ALL_M OM --全业务涉外业务未决月计表
72 LEFT JOIN (SELECT *
73 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PA1.POLICY_NO ORDER BY PA1.ETL_UPDATE_DATE DESC) RN,
74 PA1.*
75 FROM EDW_OPR.GSL_T_SAFE_POLICY_ALL PA1) --全业务涉外业务保批单信息表
76 WHERE RN = 1) A1 --全业务涉外业务保批单信息表
77 ON OM.POLICY_NO = A1.POLICY_NO
78 FULL JOIN EDW_OPR.GSL_V_SAFE_OS_ALL_M OM1
79 ON OM1.POLICY_NO = A1.POLICY_NO
80 AND OM1.REINS_FLAG = '2'
81 AND OM1.STATMONTH = TO_CHAR(ADD_MONTHS(TO_DATE(OM.STATMONTH, 'YYYYMM'), --取上月未决
82 -1),
83 'YYYYMM')
84 LEFT JOIN ODS_OPR.V_CUX_GL_WGJ YY
85 ON OM.OUTWARD_COMCODE = YY.SEGMENT4
86 where OM.REINS_FLAG = '2' --取分出
87
88
89 UNION ALL
90 --未到期
91 SELECT PM.STATMONTH AS STATMONTH, -- 统计月
92 ADD_MONTHS(TO_DATE(PM.STATMONTH, 'YYYYMM') - 1, 1) AS STATDATE, -- 统计日
93 A2.COMCODE AS COMCODE, -- 填报机构代码
94 A2.CLASSCODE AS CLASSCODE, -- 保险类别
95 A2.POLICY_NATIONALITY_TYPE AS POLICY_NATIONALITY_TYPE, -- 保单持有人所属国家/地区
96 A2.POLICY_DEPARTMENT_TYPE AS POLICY_DEPARTMENT_TYPE, -- 保单持有人所属部门
97 A2.POLICY_RELATIONSHIP_TYPE AS POLICY_RELATIONSHIP_TYPE, -- 保单持有人与本机构的关系
98 PM.POLICY_NO AS POLICY_NO, -- 保单号
99 PM.ENDORSE_NO AS ENDORSE_NO, -- 批单号
100 PM.OUTWARD_COMCODE AS OUTWARD_COMCODE, -- 分出方公司代码
101 PM.CURRENCY_CODE_PREM AS CURRENCY_CODE_PREM, -- 币种代码-保费
102 PM.CURRENCY_NAME_PREM AS CURRENCY_NAME_PREM, -- 币种-保费
103 PM.NET_PREMIUM_EP AS NET_PREMIUM, -- 净额保费
104
105 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
106 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS PREM_EXPEND_THIS_MONTH, -- 本月末分出业务保费支出 ---代表应付，应付代表贷
107 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
108 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS PREM_EXPEND_LAST_MONTH, -- 上月末分出业务保费支出 ---代表应付，应付代表贷
109
110 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
111 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS FEE_REVENUE_THIS_MONTH, -- 本月末摊回分保费用收入 --代表应收，应付代表借
112 NVL(YY.YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
113 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS FEE_REVENUE_LAST_MONTH, -- 上月末摊回分保费用收入 --代表应收，应付代表借
114
115 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
116 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS CLAIM_REVENUE_THIS_MONTH, -- 本月末摊回赔付成本收入
117 NVL(YY.BEGIN_BALANCE_DR, 0) - NVL(YY.BEGIN_BALANCE_CR, 0) +
118 NVL(YY.PERIOD_NET_DR, 0) - NVL(YY.PERIOD_NET_CR, '0') AS CLAIM_REVENUE_LAST_MONTH, -- 上月末摊回赔付成本收入
119 0 AS OS_THIS_MONTH, -- 上月末应收分保未决
120 0 AS OS_LAST_MONTH, -- 本月末应收分保未决
121 PM.NET_PREMIUM_EP AS UPR_THIS_MONTH, -- 本月末应收分保未到期保费
122 PM1.NET_PREMIUM_EP AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
123 0 AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
124 0 AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
125 A2.GROUP_FLAG AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
126 A2.BUSINESS_TYPE AS BUSINESS_TYPE, -- 业务线:1-车险,2-零售,3-健康险,4-商业险
127 PM.ETL_UPDATE_DATE AS ETL_UPDATE_DATE -- 数据调整时间
128 FROM EDW_OPR.GSL_V_SAFE_PREM_ALL_M PM --全业务涉外业务保费月计表
129 LEFT JOIN (SELECT *
130 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PA2.POLICY_NO ORDER BY PA2.ETL_UPDATE_DATE DESC) RN,
131 PA2.*
132 FROM EDW_OPR.GSL_T_SAFE_POLICY_ALL PA2) --全业务涉外业务保批单信息表
133 WHERE RN = 1) A2
134 ON PM.POLICY_NO = A2.POLICY_NO
135 FULL JOIN EDW_OPR.GSL_V_SAFE_PREM_ALL_M PM1
136 ON PM1.POLICY_NO = A2.POLICY_NO
137 AND PM1.REINS_FLAG = '2'
138 AND PM1.STATMONTH = TO_CHAR(ADD_MONTHS(TO_DATE(PM.STATMONTH, 'YYYYMM'), --取上月未到期
139 -1),
140 'YYYYMM')
141 LEFT JOIN ODS_OPR.V_CUX_GL_WGJ YY
142 ON PM.OUTWARD_COMCODE = YY.SEGMENT4
143 WHERE PM.REINS_FLAG = '2'
144
145 -- 总账
146 UNION ALL
147
148 SELECT REPLACE ( GW.PERIOD_NAME,'-','') AS STATMONTH, -- 统计月
149 ADD_MONTHS(TO_DATE(GW.PERIOD_NAME, 'YYYY-MM') - 1, 1) AS STATDATE, -- 统计日
150 '310000781901' AS COMCODE, -- 填报机构代码
151 '2 非人寿保险' AS CLASSCODE, -- 保险类别
152 RIC.ORDER_COUNTRY_CODE AS POLICY_NATIONALITY_TYPE, -- 保单相关方所属国家/地区
153 RIC.ORDER_BRANCH_CODE AS POLICY_DEPARTMENT_TYPE, -- 保单相关方所属部门
154 RIC.ORDER_DEPARTMENTAL_CODE AS POLICY_RELATIONSHIP_TYPE, -- 保单相关方与本机构的关系
155 '' AS POLICY_NO, -- 保单号
156 '' AS ENDORSE_NO, -- 批单号
157 GW.SEGMENT4 AS OUTWARD_COMCODE, -- 分出方公司代码
158 GW.CURRENCY_CODE AS CURRENCY_CODE_PREM, --币种代码-保费
159 C.CURRENCY_CHINESE_NAME AS CURRENCY_NAME_PREM, --币种-保费
160 0 AS NET_PREMIUM, -- 净额保费
161 GW.PREM_EXPEND_THIS_MONTH,--本月末分出业务保费支出年累计
162 GW.PREM_EXPEND_LAST_MONTH,--上月末分出业务保费支出年累计
163 GW.FEE_REVENUE_THIS_MONTH,--本月末摊回分保费用收入年累计
164 GW.FEE_REVENUE_LAST_MONTH,--上月末摊回分保费用收入年累计
165 GW.CLAIM_REVENUE_THIS_MONTH, --本月末摊回赔付成本收入年累计
166 GW.CLAIM_REVENUE_LAST_MONTH,--上月末摊回赔付成本收入年累计
167 0 AS OS_THIS_MONTH, -- 上月末应收分保未决
168 0 AS OS_LAST_MONTH, -- 本月末应收分保未决
169 0 AS UPR_THIS_MONTH, -- 本月末应收分保未到期保费
170 0 AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
171 0 AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
172 0 AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
173 '' AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
174 '' AS BUSINESS_TYPE, -- 业务线:1-车险,2-零售,3-健康险,4-商业险
175 GW.ETL_DATE AS ETL_UPDATE_DATE -- 数据调整时间
176 FROM (SELECT A.PERIOD_NAME,
177 A.SEGMENT4,
178 A.CURRENCY_CODE,
179 A.ETL_DATE,
180 SUM(A.PREM_EXPEND_THIS_MONTH) AS PREM_EXPEND_THIS_MONTH,
181 SUM(B.PREM_EXPEND_LAST_MONTH) AS PREM_EXPEND_LAST_MONTH,
182 0 AS FEE_REVENUE_THIS_MONTH,
183 0 AS FEE_REVENUE_LAST_MONTH,
184 0 AS CLAIM_REVENUE_THIS_MONTH,
185 0 AS CLAIM_REVENUE_LAST_MONTH
186 FROM (SELECT A.PERIOD_NAME,
187 A.SEGMENT4,
188 A.CURRENCY_CODE,
189 A.ETL_DATE,
190 SUM(NVL(A.BEGIN_BALANCE_DR, 0) - NVL(A.BEGIN_BALANCE_CR, 0) +
191 NVL(A.PERIOD_NET_DR, 0) - NVL(A.PERIOD_NET_CR, 0)) AS PREM_EXPEND_THIS_MONTH --本月末分出业务保费支出年累计
192 FROM ODS_OPR.V_CUX_GL_WGJ A
193 WHERE A.SEGMENT3 LIKE '6541%'
194 GROUP BY A.PERIOD_NAME, A.SEGMENT4,A.CURRENCY_CODE,A.ETL_DATE) A
195 LEFT JOIN (SELECT B.PERIOD_NAME,
196 B.SEGMENT4,
197 B.CURRENCY_CODE,
198 B.ETL_DATE,
199 SUM(NVL(B.BEGIN_BALANCE_DR, 0) -
200 NVL(B.BEGIN_BALANCE_CR, 0) + NVL(B.PERIOD_NET_DR, 0) -
201 NVL(B.PERIOD_NET_CR, 0)) AS PREM_EXPEND_LAST_MONTH
202 FROM ODS_OPR.V_CUX_GL_WGJ B
203 WHERE B.SEGMENT3 LIKE '6541%'
204 GROUP BY B.PERIOD_NAME, B.SEGMENT4,B.CURRENCY_CODE,B.ETL_DATE)B --上月末分出业务保费支出年累计
205 ON A.SEGMENT4 = B.SEGMENT4
206 AND B.PERIOD_NAME =
207 TO_CHAR(ADD_MONTHS(TO_DATE(A.PERIOD_NAME, 'YYYY-MM'), -1), 'YYYY-MM')
208 GROUP BY A.PERIOD_NAME, A.SEGMENT4,A.CURRENCY_CODE,A.ETL_DATE
209 UNION ALL
210 SELECT C.PERIOD_NAME,
211 C.SEGMENT4,
212 C.CURRENCY_CODE,
213 C.ETL_DATE,
214 0 AS PREM_EXPEND_THIS_MONTH,
215 0 AS PREM_EXPEND_LAST_MONTH,
216 SUM(C.FEE_REVENUE_THIS_MONTH),
217 SUM(D.FEE_REVENUE_LAST_MONTH),
218 0 AS CLAIM_REVENUE_THIS_MONTH,
219 0 AS CLAIM_REVENUE_LAST_MONTH
220 FROM (SELECT C.PERIOD_NAME,
221 C.SEGMENT4,
222 C.CURRENCY_CODE,
223 C.ETL_DATE,
224 SUM(NVL(C.BEGIN_BALANCE_DR, 0) - NVL(C.BEGIN_BALANCE_CR, 0) +
225 NVL(C.PERIOD_NET_DR, 0) - NVL(C.PERIOD_NET_CR, 0)) AS FEE_REVENUE_THIS_MONTH
226 FROM ODS_OPR.V_CUX_GL_WGJ C
227 WHERE C.SEGMENT3 LIKE '6203%'
228 GROUP BY C.PERIOD_NAME, C.SEGMENT4,C.CURRENCY_CODE,C.ETL_DATE) C --本月末摊回分保费用收入年累计
229 LEFT JOIN (SELECT D.PERIOD_NAME,
230 D.SEGMENT4,
231 D.CURRENCY_CODE,
232 D.ETL_DATE,
233 SUM(NVL(D.BEGIN_BALANCE_DR, 0) -
234 NVL(D.BEGIN_BALANCE_CR, 0) + NVL(D.PERIOD_NET_DR, 0) -
235 NVL(D.PERIOD_NET_CR, 0)) AS FEE_REVENUE_LAST_MONTH
236 FROM ODS_OPR.V_CUX_GL_WGJ D
237 WHERE D.SEGMENT3 LIKE '6203%' --上月末摊回分保费用收入年累计
238 GROUP BY D.PERIOD_NAME, D.SEGMENT4,D.CURRENCY_CODE,D.ETL_DATE)D
239 ON C.SEGMENT4 = D.SEGMENT4
240 AND D.PERIOD_NAME =
241 TO_CHAR(ADD_MONTHS(TO_DATE(C.PERIOD_NAME, 'YYYY-MM'), -1), 'YYYY-MM')
242 GROUP BY C.PERIOD_NAME, C.SEGMENT4,C.CURRENCY_CODE,C.ETL_DATE
243 UNION ALL
244 SELECT E.PERIOD_NAME,
245 E.SEGMENT4,
246 E.CURRENCY_CODE,
247 E.ETL_DATE,
248 0 AS PREM_EXPEND_THIS_MONTH,
249 0 AS PREM_EXPEND_LAST_MONTH,
250 0 AS FEE_REVENUE_THIS_MONTH,
251 0 AS FEE_REVENUE_LAST_MONTH,
252 SUM(E.CLAIM_REVENUE_THIS_MONTH),
253 SUM(F.CLAIM_REVENUE_LAST_MONTH)
254 FROM (SELECT E.PERIOD_NAME,
255 E.SEGMENT4,
256 E.CURRENCY_CODE,
257 E.ETL_DATE,
258 SUM(NVL(E.BEGIN_BALANCE_DR, 0) - NVL(E.BEGIN_BALANCE_CR, 0) +
259 NVL(E.PERIOD_NET_DR, 0) - NVL(E.PERIOD_NET_CR, 0)) AS CLAIM_REVENUE_THIS_MONTH
260 FROM ODS_OPR.V_CUX_GL_WGJ E
261 WHERE E.SEGMENT3 LIKE '6202%' --本月末摊回赔付成本收入年累计
262 GROUP BY E.PERIOD_NAME, E.SEGMENT4,E.CURRENCY_CODE,E.ETL_DATE) E
263 LEFT JOIN (SELECT F.PERIOD_NAME,
264 F.SEGMENT4,
265 F.CURRENCY_CODE,
266 F.ETL_DATE,
267 SUM(NVL(F.BEGIN_BALANCE_DR, 0) -
268 NVL(F.BEGIN_BALANCE_CR, 0) + NVL(F.PERIOD_NET_DR, 0) -
269 NVL(F.PERIOD_NET_CR, 0)) AS CLAIM_REVENUE_LAST_MONTH
270 FROM ODS_OPR.V_CUX_GL_WGJ F
271 WHERE F.SEGMENT3 LIKE '6202%' ---下月末摊回赔付成本收入年累计
272 GROUP BY F.PERIOD_NAME, F.SEGMENT4,F.CURRENCY_CODE,F.ETL_DATE)F
273 ON E.SEGMENT4 = F.SEGMENT4
274 AND F.PERIOD_NAME =
275 TO_CHAR(ADD_MONTHS(TO_DATE(E.PERIOD_NAME, 'YYYY-MM'), -1), 'YYYY-MM')
276 GROUP BY E.PERIOD_NAME, E.SEGMENT4,E.CURRENCY_CODE,E.ETL_DATE)GW
277 LEFT JOIN ODS_OPR.T_RI_COMPANY RIC --再保公司信息表
278 ON GW.SEGMENT4 = RIC.FN_COMPANY_CODE --SEGMENT4:分出方机构即再保人
279 LEFT JOIN EDW_OPR.GSL_T_SAFE_CURRENCY_TYPE C --币种码表
280 ON GW.CURRENCY_CODE = C.CURRENCY_CODE
281
282 ) T1 --分出
283 GROUP BY T1.STATMONTH, --统计月
284 T1.STATDATE, --统计日
285 T1.COMCODE, --填报机构代码
286 T1.CLASSCODE, --保险类别
287 T1.POLICY_NATIONALITY_TYPE, --保单持有人所属国家/地区
288 T1.POLICY_DEPARTMENT_TYPE, --保单持有人所属部门
289 T1.POLICY_RELATIONSHIP_TYPE, --保单持有人与本机构的关系
290 T1.POLICY_NO, --保单号
291 T1.ENDORSE_NO, --批单号
292 T1.OUTWARD_COMCODE, --分出方公司代码
293 T1.CURRENCY_CODE_PREM, --币种代码-保费
294 T1.CURRENCY_NAME_PREM, --币种-保费
295 T1.NET_PREMIUM, -- 净额保费
296 T1.GROUP_FLAG, --团单标识:0-个单,1-团单
297 T1.BUSINESS_TYPE, --业务线:1-车险,2-零售,3-健康险,4-商业险
298 T1.ETL_UPDATE_DATE --数据调整时间
299 ;
浙公网安备 33010602011771号