ETL详细设计

模型部分

待沟通

一、个人信息（Individual）

  1. 从保单主表（POLICY_INFORMATION）中获取投保人的上游客户号和五要素，得到$投保人列表$

code

SELECT CUSTOMERNO,NAME,sex,BIRTHDAY,IDTYPE,IDNO,min(CVALIDATE) FROM POLICY_INFORMATION GROUP BY CUSTOMERNO,NAME,sex,BIRTHDAY,IDTYPE,IDNO;
  1. 从个险承保清单自助分析表（POLICY_INFORMATION）中获取被保人的上游客户号和五要素，得到$被保人列表$

code

SELECT INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,INSUREDIDNO,min(CVALIDATE) FROM POLICY_INFORMATION GROUP BY INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,INSUREDIDNO;
  1. 从个险承保清单自助分析（HEALTH_INSURANCE_LISTING）中获取受益人的上游客户号和三要素，得到$受益人列表$，实际数据上游客户全为null，同时五要素不齐，故暂时不考虑

code

SELECT BENEFICIARY_CUSTOMERNO,BENEFICIARY_NAME,BENEFICIARY_IDTYPE,BENEFICIARY_IDNO FROM HEALTH_INSURANCE_LISTING GROUP BY BENEFICIARY_CUSTOMERNO,BENEFICIARY_NAME,BENEFICIARY_IDTYPE,BENEFICIARY_IDNO;
  1. 将$投保人列表$，$被保人列表$ 合并去重，得到$投被保人列表$，并按保单生效日期（CVALIDATE）从早到晚排序（客户号生成规则之一）
  2. 遍历$投被保人列表$
  3. 标识及证件信息：根据五要素生成CPID，并生成代理主键IndID同时将CPID以及列表中其他字段分别插入个人信息表（Individual）。注意：要根据证件类型，将证件号码插入不同的字段。（如：证件类型为0，则证件号码插入身份证字段）
  4. todo 等级信息：根据上游客户号，从客户等级表（T_CUSTOMER_CLASS）获取客户等级、贡献度分、家庭加分、续期加分、保单加分、总分值。

code

select CLASS_VALUE,CONTRIBUTION_VALUE,AWARDED3,AWARDED2,AWARDED1,TOTAL_VALUE from T_CUSTOMER_CLASS where CUSTOMER_ID = 上游客户号
  1. 籍贯、职业：根据上游客户号，从个险承保清单自助分析（HEALTH_INSURANCE_LISTING）获取投保人籍贯，投保人职业代码，投保人职业名称。注意，此表投保人和被保人都有职业代码名称，取最近的一条记录

code

select APPNTHOMEADDRESS,APPNTOCCUPATIONCODE,APPNTOCCUPATIONNAME from HEALTH_INSURANCE_LISTING where APPNTNO = 上游客户号 order by POLAPPLYDATE desc

二、保单（InsuranceArrangement）

  1. 从新契约签单明细清单（INSURANCEINFO）表中获取相关字段插入到InsuranceArrangement表，由于INSURANCEINFO表中是以保单号唯一的，所以直接插入。语法见下，字段映射详见模型文档

code

INSERT INTO InsuranceArrangement(PolicyNo,PANo) 
SELECT contno,prtno
FROM  INSURANCEINFO
  1. 用保单信息表（POLICY_INFORMATION）去更新InsuranceArrangement，由于POLICY_INFORMATION不是以保单号唯一的，所以先要去重。由于星环inceptor不支持如下在update中使用group by的语法,所以可以对POLICY_INFORMATION按保单号去重后存入临时表POLICY_INFORMATION_tmp

code

insert into POLICY_INFORMATION_tmp(contno,CVALIDATE,ENDDATE)
SELECT contno,max(CVALIDATE),max(ENDDATE)	
ROM POLICY_INFORMATION
GROUP BY contno
  1. 更新临时表POLICY_INFORMATION_tmp中投保人（ApplicantID），主被保险人（InsuredID）为individual表的个体ID(IndID),可根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号（SCustID），得到IndID，支持update，临时表设置为ORC事务表

code

UPDATE POLICY_INFORMATION_tmp
SET ApplicantID =
(SELECT ApplicantID
FROM individual
WHERE (POLICY_INFORMATION_tmp.CUSTOMERNO = individual.SCustID))
WHERE 1=1;
  1. 最后用临时表更新InsuranceArrangement（code2）,为了支持update，InsuranceArrangement表设置为ORC事务表。语法见下，字段映射详见模型文档

code

UPDATE InsuranceArrangement
SET (PINDate,PMDate) =
(SELECT CVALIDATE,ENDDATE
FROM POLICY_INFORMATION_tmp
WHERE (POLICY_INFORMATION_tmp.contno = InsuranceArrangement.PolicyNo))
WHERE 1=1;

三、理赔（InsuranceClaimThread）

  1. 首先将个单理赔表（INSURANCE_CLAIM）按保单和赔案号去重并存入临时表INSURANCE_CLAIM_tmp

code

insert into INSURANCE_CLAIM_tmp(ontno,clmno,RISKDATE,INSUREDNO,RPTDATE,LLOCCURREASON,RGTDATE,ACCIDENTDETAIL,LLCLAIMSTATE)
SELECT contno,clmno,max(RISKDATE),max(INSUREDNO),max(RPTDATE),max(LLOCCURREASON),max(RGTDATE),max(ACCIDENTDETAIL),max(LLCLAIMSTATE)
FROM INSURANCE_CLAIM GROUP BY contno,clmno
  1. 更新INSURANCE_CLAIM_tmp中出险人为individual表的个体ID(IndID)
  2. 将INSURANCE_CLAIM_tmp相应字段直接插入到InsuranceClaimThread表中，字段映射详见模型文档。

四、个体关系(IndRelationShip)

  1. 从保单信息表（POLICY_INFORMATION）获取投保人和被保人的关系并

code

insert into IndRelationShip(RSType,IndID1,Name1,IDCard1,IndID2,Name2,IDCard2,RSSTime,Role2)
SELECT max(RELATIONTOAPPNT),CUSTOMERNO,max(NAME),max(IDNO),INSUREDNO,max(INSUREDNAME),max(INSUREDIDNO),min(CUSTOMGETPOLDATE),
   CASE max(RELATIONTOAPPNT) 
   		WHEN '子女' THEN case max(INSUREDSEX) when 1 THEN '女儿' WHEN 0 THEN '儿子' end
        when '父母' THEN case max(INSUREDSEX) when 1 THEN '母亲' WHEN 0 THEN '父亲' end
        WHEN '配偶' THEN case max(INSUREDSEX) when 1 THEN '妻子' WHEN 0 THEN '丈夫' end
        when '祖父母、外祖父母' THEN case max(INSUREDSEX) when 1 THEN '(外)祖母' WHEN 0 THEN '(外)祖父' end
        when '祖孙、外祖孙' THEN case max(INSUREDSEX) when 1 THEN '(外)孙女' WHEN 0 THEN '(外)祖孙' end
        ELSE max(RELATIONTOAPPNT) END
FROM POLICY_INFORMATION 
WHERE RELATIONTOAPPNT<>'本人'
GROUP BY CUSTOMERNO,INSUREDNO
  1. 更新IndRelationShip.Role1为IndRelationShip.Role2的相对角色，即role2为母亲则role1为父亲等
  2. 此时插入IndRelationShip的IndID1，IndID2为上游系统客户号，根据Individual.SCustID更新为Individual.IndID
  3. 根据Individual更新其他信息，如手机号等

五、接触(Communication)

六、事件(PartyTimeLine)

  1. 目前主要有以下事件，一个事件一条记录，包括开始时间和结束时间

标签部分