--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,
--保险类别
