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