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 SYSDATE AS ETL_UPDATE_DATE -- 数据调整时间
31 FROM
32 (
33 --本月未决
34 SELECT
35 OM.STATMONTH AS STATMONTH, -- 统计月
36 LAST_DAY(TO_DATE(OM.STATMONTH, 'YYYYMM')) AS STATDATE, -- 统计日
37 '310000781901' AS COMCODE, -- 填报机构代码
38 '2 非人寿保险' AS CLASSCODE, -- 保险类别
39 OM.POLICY_NATIONALITY_TYPE AS POLICY_NATIONALITY_TYPE, -- 保单持有人所属国家/地区
40 OM.POLICY_DEPARTMENT_TYPE AS POLICY_DEPARTMENT_TYPE, -- 保单持有人所属部门
41 OM.POLICY_RELATIONSHIP_TYPE AS POLICY_RELATIONSHIP_TYPE, -- 保单持有人与本机构的关系
42 OM.POLICY_NO AS POLICY_NO, -- 保单号
43 OM.ENDORSE_NO AS ENDORSE_NO, -- 批单号
44 OM.OUTWARD_COMCODE AS OUTWARD_COMCODE, -- 分出方公司代码
45 OM.CURRENCY_CODE_OS AS CURRENCY_CODE_PREM, -- 币种代码-保费
46 OM.CURRENCY_NAME_OS AS CURRENCY_NAME_PREM, -- 币种-保费
47 -- OM1.NET_PREMIUM_ORIG AS NET_PREMIUM, -- 净额保费
48
49 0 AS PREM_EXPEND_THIS_MONTH, -- 本月末分出业务保费支出 ---代表应付，应付代表贷
50 0 AS PREM_EXPEND_LAST_MONTH, -- 上月末分出业务保费支出 ---代表应付，应付代表贷
51
52 0 AS FEE_REVENUE_THIS_MONTH, -- 本月末摊回分保费用收入 --代表应收，应付代表借
53 0 AS FEE_REVENUE_LAST_MONTH, -- 上月末摊回分保费用收入 --代表应收，应付代表借
54
55 0 AS CLAIM_REVENUE_THIS_MONTH, -- 本月末摊回赔付成本收入
56 0 AS CLAIM_REVENUE_LAST_MONTH, -- 上月末摊回赔付成本收入
57 0 AS OS_THIS_MONTH, -- 上月末应收分保未决
58 OM.OS AS OS_LAST_MONTH, -- 本月末应收分保未决
59 0 AS UPR_THIS_MONTH, -- 本月末应收分保未到期保费
60 0 AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
61 0 AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
62 0 AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
63 OM.GROUP_FLAG AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
64 OM.BUSINESS_TYPE AS BUSINESS_TYPE -- 业务线:1-车险,2-零售,3-健康险,4-商业险
65 FROM EDW_OPR.GSL_V_SAFE_OS_ALL_M OM --全业务涉外业务未决月计表
66 where OM.REINS_FLAG = '2' --取分出
67
68
69 UNION ALL
70
71 --上月未决
72 SELECT
73 TO_CHAR(ADD_MONTHS(TO_DATE(OM1.STATMONTH, 'YYYYMM'), 1), 'YYYYMM') AS STATMONTH, -- 统计月
74 LAST_DAY(ADD_MONTHS(TO_DATE(OM1.STATMONTH, 'YYYYMM'), 1)) AS STATDATE, -- 统计日
75 '310000781901' AS COMCODE, -- 填报机构代码
76 '2 非人寿保险' AS CLASSCODE, -- 保险类别
77 OM1.POLICY_NATIONALITY_TYPE AS POLICY_NATIONALITY_TYPE, -- 保单持有人所属国家/地区
78 OM1.POLICY_DEPARTMENT_TYPE AS POLICY_DEPARTMENT_TYPE, -- 保单持有人所属部门
79 OM1.POLICY_RELATIONSHIP_TYPE AS POLICY_RELATIONSHIP_TYPE, -- 保单持有人与本机构的关系
80 OM1.POLICY_NO AS POLICY_NO, -- 保单号
81 OM1.ENDORSE_NO AS ENDORSE_NO, -- 批单号
82 OM1.OUTWARD_COMCODE AS OUTWARD_COMCODE, -- 分出方公司代码
83 OM1.CURRENCY_CODE_OS AS CURRENCY_CODE_PREM, -- 币种代码-保费
84 OM1.CURRENCY_NAME_OS AS CURRENCY_NAME_PREM, -- 币种-保费
85 -- OM1.NET_PREMIUM_ORIG AS NET_PREMIUM, -- 净额保费
86 0 AS PREM_EXPEND_THIS_MONTH, -- 本月末分出业务保费支出 ---代表应付，应付代表贷
87 0 AS PREM_EXPEND_LAST_MONTH, -- 上月末分出业务保费支出 ---代表应付，应付代表贷
88
89 0 AS FEE_REVENUE_THIS_MONTH, -- 本月末摊回分保费用收入 --代表应收，应付代表借
90 0 AS FEE_REVENUE_LAST_MONTH, -- 上月末摊回分保费用收入 --代表应收，应付代表借
91
92 0 AS CLAIM_REVENUE_THIS_MONTH, -- 本月末摊回赔付成本收入
93 0 AS CLAIM_REVENUE_LAST_MONTH, -- 上月末摊回赔付成本收入
94 OM1.OS AS OS_THIS_MONTH, -- 上月末应收分保未决
95 0 AS OS_LAST_MONTH, -- 本月末应收分保未决
96 0 AS UPR_THIS_MONTH, -- 本月末应收分保未到期保费
97 0 AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
98 0 AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
99 0 AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
100 OM1.GROUP_FLAG AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
101 OM1.BUSINESS_TYPE AS BUSINESS_TYPE -- 业务线:1-车险,2-零售,3-健康险,4-商业险
102 FROM EDW_OPR.GSL_V_SAFE_OS_ALL_M OM1 --全业务涉外业务未决月计表
103 WHERE OM1.REINS_FLAG = '2' --取分出
104 and ADD_MONTHS(TO_DATE(OM1.STATMONTH, 'YYYYMM'), --取上月未决
105 1) < SYSDATE
106
107
108 UNION ALL
109 --本月未到期
110 SELECT PM.STATMONTH AS STATMONTH, -- 统计月
111 LAST_DAY(TO_DATE(PM.STATMONTH, 'YYYYMM')) AS STATDATE, -- 统计日
112 '310000781901' AS COMCODE, -- 填报机构代码
113 '2 非人寿保险' AS CLASSCODE, -- 保险类别
114 PM.POLICY_NATIONALITY_TYPE AS POLICY_NATIONALITY_TYPE, -- 保单持有人所属国家/地区
115 PM.POLICY_DEPARTMENT_TYPE AS POLICY_DEPARTMENT_TYPE, -- 保单持有人所属部门
116 PM.POLICY_RELATIONSHIP_TYPE AS POLICY_RELATIONSHIP_TYPE, -- 保单持有人与本机构的关系
117 PM.POLICY_NO AS POLICY_NO, -- 保单号
118 PM.ENDORSE_NO AS ENDORSE_NO, -- 批单号
119 PM.OUTWARD_COMCODE AS OUTWARD_COMCODE, -- 分出方公司代码
120 PM.CURRENCY_CODE_PREM AS CURRENCY_CODE_PREM, -- 币种代码-保费
121 PM.CURRENCY_NAME_PREM AS CURRENCY_NAME_PREM, -- 币种-保费
122 -- PM.NET_PREMIUM_EP AS NET_PREMIUM, -- 净额保费
123
124 0 AS PREM_EXPEND_THIS_MONTH, -- 本月末分出业务保费支出 ---代表应付，应付代表贷
125 0 AS PREM_EXPEND_LAST_MONTH, -- 上月末分出业务保费支出 ---代表应付，应付代表贷
126
127 0 AS FEE_REVENUE_THIS_MONTH, -- 本月末摊回分保费用收入 --代表应收，应付代表借
128 0 AS FEE_REVENUE_LAST_MONTH, -- 上月末摊回分保费用收入 --代表应收，应付代表借
129
130 0 AS CLAIM_REVENUE_THIS_MONTH, -- 本月末摊回赔付成本收入
131 0 AS CLAIM_REVENUE_LAST_MONTH, -- 上月末摊回赔付成本收入
132 0 AS OS_THIS_MONTH, -- 上月末应收分保未决
133 0 AS OS_LAST_MONTH, -- 本月末应收分保未决
134 NVL(PM.NET_PREMIUM_EP,0) AS UPR_THIS_MONTH, -- 本月末应收分保未到期保费
135 0 AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
136 0 AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
137 0 AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
138 PM.GROUP_FLAG AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
139 PM.BUSINESS_TYPE AS BUSINESS_TYPE -- 业务线:1-车险,2-零售,3-健康险,4-商业险
140 FROM EDW_OPR.GSL_V_SAFE_PREM_ALL_M PM --全业务涉外业务保费月计表
141 WHERE PM.REINS_FLAG = '2'
142
143 UNION ALL
144 --上月未到期
145 SELECT
146 TO_CHAR(ADD_MONTHS(TO_DATE(PM1.STATMONTH, 'YYYYMM'), 1),
147 'YYYYMM') AS STATMONTH, -- 统计月
148 LAST_DAY(ADD_MONTHS(TO_DATE(PM1.STATMONTH, 'YYYYMM'), 1)) AS STATDATE, -- 统计日
149 '310000781901' AS COMCODE, -- 填报机构代码
150 '2 非人寿保险' AS CLASSCODE, -- 保险类别
151 PM1.POLICY_NATIONALITY_TYPE AS POLICY_NATIONALITY_TYPE, -- 保单持有人所属国家/地区
152 PM1.POLICY_DEPARTMENT_TYPE AS POLICY_DEPARTMENT_TYPE, -- 保单持有人所属部门
153 PM1.POLICY_RELATIONSHIP_TYPE AS POLICY_RELATIONSHIP_TYPE, -- 保单持有人与本机构的关系
154 PM1.POLICY_NO AS POLICY_NO, -- 保单号
155 PM1.ENDORSE_NO AS ENDORSE_NO, -- 批单号
156 PM1.OUTWARD_COMCODE AS OUTWARD_COMCODE, -- 分出方公司代码
157 PM1.CURRENCY_CODE_PREM AS CURRENCY_CODE_PREM, -- 币种代码-保费
158 PM1.CURRENCY_NAME_PREM AS CURRENCY_NAME_PREM, -- 币种-保费
159 -- OM1.NET_PREMIUM_EP AS NET_PREMIUM, -- 净额保费
160
161 0 AS PREM_EXPEND_THIS_MONTH, -- 本月末分出业务保费支出 ---代表应付，应付代表贷
162 0 AS PREM_EXPEND_LAST_MONTH, -- 上月末分出业务保费支出 ---代表应付，应付代表贷
163
164 0 AS FEE_REVENUE_THIS_MONTH, -- 本月末摊回分保费用收入 --代表应收，应付代表借
165 0 AS FEE_REVENUE_LAST_MONTH, -- 上月末摊回分保费用收入 --代表应收，应付代表借
166
167 0 AS CLAIM_REVENUE_THIS_MONTH, -- 本月末摊回赔付成本收入
168 0 AS CLAIM_REVENUE_LAST_MONTH, -- 上月末摊回赔付成本收入
169 0 AS OS_THIS_MONTH, -- 上月末应收分保未决
170 0 AS OS_LAST_MONTH, -- 本月末应收分保未决
171 0 AS UPR_THIS_MONTH, -- 本月末应收分保未到期保费
172 NVL(PM1.NET_PREMIUM_EP,0) AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
173 0 AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
174 0 AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
175 PM1.GROUP_FLAG AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
176 PM1.BUSINESS_TYPE AS BUSINESS_TYPE -- 业务线:1-车险,2-零售,3-健康险,4-商业险
177 FROM EDW_OPR.GSL_V_SAFE_PREM_ALL_M PM1 --全业务涉外业务保费月计表
178 WHERE PM1.REINS_FLAG = '2'
179 AND ADD_MONTHS(TO_DATE(PM1.STATMONTH, 'YYYYMM'), 1) < SYSDATE
180
181
182 -- 总账
183 UNION ALL
184
185 SELECT REPLACE ( GW.PERIOD_NAME,'-','') AS STATMONTH, -- 统计月
186 ADD_MONTHS(TO_DATE(GW.PERIOD_NAME, 'YYYY-MM') - 1, 1) AS STATDATE, -- 统计日
187 '310000781901' AS COMCODE, -- 填报机构代码
188 '2 非人寿保险' AS CLASSCODE, -- 保险类别
189 RIC.ORDER_COUNTRY_CODE AS POLICY_NATIONALITY_TYPE, -- 保单相关方所属国家/地区
190 RIC.ORDER_BRANCH_CODE AS POLICY_DEPARTMENT_TYPE, -- 保单相关方所属部门
191 RIC.ORDER_DEPARTMENTAL_CODE AS POLICY_RELATIONSHIP_TYPE, -- 保单相关方与本机构的关系
192 '' AS POLICY_NO, -- 保单号
193 '' AS ENDORSE_NO, -- 批单号
194 RIC.COMPANY_CODE AS OUTWARD_COMCODE, -- 分出方公司代码
195 GW.CURRENCY_CODE AS CURRENCY_CODE_PREM, --币种代码-保费
196 C.CURRENCY_ENGLISH_ABBR_NAME ||' '||C.CURRENCY_CHINESE_NAME AS CURRENCY_NAME_PREM, --币种-保费
197 -- 0 AS NET_PREMIUM, -- 净额保费
198 GW.PREM_EXPEND_THIS_MONTH,--本月末分出业务保费支出年累计 6541
199 GW.PREM_EXPEND_LAST_MONTH,--上月末分出业务保费支出年累计 6541
200 GW.FEE_REVENUE_THIS_MONTH,--本月末摊回分保费用收入年累计 6203
201 GW.FEE_REVENUE_LAST_MONTH,--上月末摊回分保费用收入年累计 6203
202 GW.CLAIM_REVENUE_THIS_MONTH, --本月末摊回赔付成本收入年累计 6202
203 GW.CLAIM_REVENUE_LAST_MONTH,--上月末摊回赔付成本收入年累计 6202
204 0 AS OS_THIS_MONTH, -- 上月末应收分保未决
205 0 AS OS_LAST_MONTH, -- 本月末应收分保未决
206 0 AS UPR_THIS_MONTH, -- 本月末应收分保未到期保费
207 0 AS UPR_LAST_MONTH, -- 上月末应收分保未到期保费
208 0 AS IBNR_THIS_MONTH, -- 本月末应收分保IBNR
209 0 AS IBNR_LAST_MONTH, -- 上月末应收分保IBNR
210 '' AS GROUP_FLAG, -- 团单标识:0-个单,1-团单
211 '' AS BUSINESS_TYPE -- 业务线:1-车险,2-零售,3-健康险,4-商业险
212 FROM (
213 SELECT PERIOD_NAME,
214 SEGMENT4,
215 CURRENCY_CODE,
216 SUM(PREM_EXPEND_THIS_MONTH) AS PREM_EXPEND_THIS_MONTH,
217 SUM(PREM_EXPEND_LAST_MONTH) AS PREM_EXPEND_LAST_MONTH,
218 SUM(FEE_REVENUE_THIS_MONTH) AS FEE_REVENUE_THIS_MONTH,
219 SUM(FEE_REVENUE_LAST_MONTH) AS FEE_REVENUE_LAST_MONTH,
220 SUM(CLAIM_REVENUE_THIS_MONTH) AS CLAIM_REVENUE_THIS_MONTH,
221 SUM(CLAIM_REVENUE_LAST_MONTH) AS CLAIM_REVENUE_LAST_MONTH
222 FROM (SELECT A.PERIOD_NAME,
223 A.SEGMENT4,
224 A.CURRENCY_CODE,
225 NVL(A.BEGIN_BALANCE_DR, 0) - NVL(A.BEGIN_BALANCE_CR, 0) +
226 NVL(A.PERIOD_NET_DR, 0) - NVL(A.PERIOD_NET_CR, 0) AS PREM_EXPEND_THIS_MONTH, --本月末分出业务保费支出年累计
227 0 AS PREM_EXPEND_LAST_MONTH,
228 0 AS FEE_REVENUE_THIS_MONTH,
229 0 AS FEE_REVENUE_LAST_MONTH,
230 0 AS CLAIM_REVENUE_THIS_MONTH,
231 0 AS CLAIM_REVENUE_LAST_MONTH
232 FROM ODS_OPR.V_CUX_GL_WGJ A
233 WHERE A.SEGMENT3 LIKE '6541%'
234 UNION ALL
235 SELECT TO_CHAR(ADD_MONTHS(TO_DATE(B.PERIOD_NAME, 'YYYY-MM'), 1),
236 'YYYY-MM') PERIOD_NAME,
237 B.SEGMENT4,
238 B.CURRENCY_CODE,
239 0 AS PREM_EXPEND_THIS_MONTH,
240 NVL(B.BEGIN_BALANCE_DR, 0) -
241 NVL(B.BEGIN_BALANCE_CR, 0) + NVL(B.PERIOD_NET_DR, 0) - --上月末分出业务保费支出年累计
242 NVL(B.PERIOD_NET_CR, 0) AS PREM_EXPEND_LAST_MONTH,
243 0 AS FEE_REVENUE_THIS_MONTH,
244 0 AS FEE_REVENUE_LAST_MONTH,
245 0 AS CLAIM_REVENUE_THIS_MONTH,
246 0 AS CLAIM_REVENUE_LAST_MONTH
247 FROM ODS_OPR.V_CUX_GL_WGJ B
248 WHERE B.SEGMENT3 LIKE '6541%'
249 AND ADD_MONTHS(TO_DATE(B.PERIOD_NAME, 'YYYY-MM'), 1) < SYSDATE
250
251
252 UNION ALL
253 SELECT C.PERIOD_NAME,
254 C.SEGMENT4,
255 C.CURRENCY_CODE,
256 0 AS PREM_EXPEND_THIS_MONTH,
257 0 AS PREM_EXPEND_LAST_MONTH,
258 NVL(C.BEGIN_BALANCE_DR, 0) - NVL(C.BEGIN_BALANCE_CR, 0) +
259 NVL(C.PERIOD_NET_DR, 0) - NVL(C.PERIOD_NET_CR, 0) AS FEE_REVENUE_THIS_MONTH, --本月末摊回分保费用收入年累计
260 0 AS FEE_REVENUE_LAST_MONTH,
261 0 AS CLAIM_REVENUE_THIS_MONTH,
262 0 AS CLAIM_REVENUE_LAST_MONTH
263 FROM ODS_OPR.V_CUX_GL_WGJ C
264 WHERE C.SEGMENT3 LIKE '6203%'
265 UNION ALL
266 SELECT TO_CHAR(ADD_MONTHS(TO_DATE(D.PERIOD_NAME, 'YYYY-MM'), 1),
267 'YYYY-MM') PERIOD_NAME,
268 D.SEGMENT4,
269 D.CURRENCY_CODE,
270 0 PREM_EXPEND_THIS_MONTH,
271 0 AS PREM_EXPEND_LAST_MONTH,
272 0 AS FEE_REVENUE_THIS_MONTH,
273 NVL(D.BEGIN_BALANCE_DR, 0) -
274 NVL(D.BEGIN_BALANCE_CR, 0) + NVL(D.PERIOD_NET_DR, 0) - --上月末摊回分保费用收入年累计
275 NVL(D.PERIOD_NET_CR, 0) AS FEE_REVENUE_LAST_MONTH,
276 0 AS CLAIM_REVENUE_THIS_MONTH,
277 0 AS CLAIM_REVENUE_LAST_MONTH
278 FROM ODS_OPR.V_CUX_GL_WGJ D
279 WHERE D.SEGMENT3 LIKE '6203%'
280 AND ADD_MONTHS(TO_DATE(D.PERIOD_NAME, 'YYYY-MM'), 1) < SYSDATE
281
282
283
284 UNION ALL
285 SELECT E.PERIOD_NAME,
286 E.SEGMENT4,
287 E.CURRENCY_CODE,
288 0 AS PREM_EXPEND_THIS_MONTH,
289 0 AS PREM_EXPEND_LAST_MONTH,
290 0 AS FEE_REVENUE_THIS_MONTH,
291 0 AS FEE_REVENUE_LAST_MONTH,
292 NVL(E.BEGIN_BALANCE_DR, 0) - NVL(E.BEGIN_BALANCE_CR, 0) +
293 NVL(E.PERIOD_NET_DR, 0) - NVL(E.PERIOD_NET_CR, 0) AS CLAIM_REVENUE_THIS_MONTH, --本月末摊回赔付成本收入年累计
294 0 AS CLAIM_REVENUE_LAST_MONTH
295 FROM ODS_OPR.V_CUX_GL_WGJ E
296 WHERE E.SEGMENT3 LIKE '6202%'
297
298 UNION ALL
299 SELECT TO_CHAR(ADD_MONTHS(TO_DATE(F.PERIOD_NAME, 'YYYY-MM'), 1),
300 'YYYY-MM') PERIOD_NAME,
301 F.SEGMENT4,
302 F.CURRENCY_CODE,
303 0 AS PREM_EXPEND_THIS_MONTH,
304 0 AS PREM_EXPEND_LAST_MONTH,
305 0 AS FEE_REVENUE_THIS_MONTH,
306 0 AS FEE_REVENUE_LAST_MONTH,
307 0 AS CLAIM_REVENUE_THIS_MONTH,
308 NVL(F.BEGIN_BALANCE_DR, 0) -
309 NVL(F.BEGIN_BALANCE_CR, 0) + NVL(F.PERIOD_NET_DR, 0) -
310 NVL(F.PERIOD_NET_CR, 0) AS CLAIM_REVENUE_LAST_MONTH
311 FROM ODS_OPR.V_CUX_GL_WGJ F
312 WHERE F.SEGMENT3 LIKE '6202%' ---上月末摊回赔付成本收入年累计
313 AND ADD_MONTHS(TO_DATE(F.PERIOD_NAME, 'YYYY-MM'), 1) < SYSDATE
314 )
315 GROUP BY PERIOD_NAME,SEGMENT4,CURRENCY_CODE) GW
316 LEFT JOIN ODS_OPR.T_RI_COMPANY RIC --再保公司信息表
317 ON GW.SEGMENT4 = RIC.FN_COMPANY_CODE --SEGMENT4:分出方机构即再保人*/
318 LEFT JOIN EDW_OPR.GSL_T_SAFE_CURRENCY_TYPE C --币种码表
319 ON GW.CURRENCY_CODE = C.CURRENCY_CODE
320 ) T1 --分出
321 GROUP BY T1.STATMONTH, --统计月
322 T1.STATDATE, --统计日
323 T1.COMCODE, --填报机构代码
324 T1.CLASSCODE, --保险类别
325 T1.POLICY_NATIONALITY_TYPE, --保单持有人所属国家/地区
326 T1.POLICY_DEPARTMENT_TYPE, --保单持有人所属部门
327 T1.POLICY_RELATIONSHIP_TYPE, --保单持有人与本机构的关系
328 T1.POLICY_NO, --保单号
329 T1.ENDORSE_NO, --批单号
330 T1.OUTWARD_COMCODE, --分出方公司代码
331 T1.CURRENCY_CODE_PREM, --币种代码-保费
332 T1.CURRENCY_NAME_PREM, --币种-保费
333 -- T1.NET_PREMIUM, -- 净额保费
334 T1.GROUP_FLAG, --团单标识:0-个单,1-团单
335 T1.BUSINESS_TYPE --业务线:1-车险,2-零售,3-健康险,4-商业险
336 ;
337
338
339 测试结果：
340
341
342
343 本月 上月
344 202003 654 202004 543 --处理本月202003的数据
345 202003 543 202004 676
346 202003 3434 202004 3434
347 202003 676 202004 654
348 202004 543 202005 974
349 202004 974 202005 974
350 202004 332 202005 996 --处理本月202004的数据
351 202004 974 202005 523
352 202004 643 202005 523
353 202004 332 202005 400
354 202004 600 202005 600
355 202004 600 202005 600
356 202004 996 202005 543
357 202004 523 202005 332
358 202004 400 202005 332
359 202004 523 202005 643
360 202005 123
361 202005 123
362 202005 674
363 202005 674
364 202005 500
365 202005 100
366 202005 500
367 202005 400
368 202005 400
369 202005 232
370 202005 232
371 202005 243
372 202005 100
373 202005 243
