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 ;