ShangHaiLifeECIF/sql-achievement/存储过程/客户标签koucx.sql

--更新客户标签信息
CREATE OR REPLACE PROCEDURE shanglifeecif.updateIndividualLable() IS
DECLARE
BEGIN
	--label1: 无来源。Individual.Education
	--label2: Individual.Occupation，逻辑见上
	UPDATE shanglifeecif.Individual si1 SET si1.label2 = (SELECT si2.Occupation FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
	--标签3 根据Individual.Birthday年代，更新为70后，80后等
	UPDATE shanglifeecif.Individual si1 SET si1.label3 = (SELECT substr(si2.birthday,3,1) || '0后' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
	--Label4：根据Individual.Birthday算出年龄，儿童:[0,13),少年:[13,19),青年:[19,41),中年:[41,66),老年:[66,)
	UPDATE shanglifeecif.Individual si1 SET si1.label4 = (SELECT CASE WHEN temp.age>=0 AND temp.age<13 THEN '儿童' WHEN temp.age>=13 AND temp.age <19 THEN '少年' WHEN temp.age >=19 AND temp.age <41 THEN '青年' WHEN temp.age >=41 AND temp.age <66 THEN '中年' ELSE '老年' END FROM (SELECT TRUNC(months_between(sysdate, si2.birthday)/12) age,si2.indid FROM shanglifeecif.Individual si2) temp WHERE temp.indid = si1.indid);
	--Label5: todo:只有收件地址（POSTAL_INSURANCE.HOMEADDRESS）且无记录
	--Label6: todo:只有收件地址（POSTAL_INSURANCE.HOMEADDRESS）且无记录
	--Label7：保留暂不实现
	--Label8：保留暂不实现
	--标签9 已婚，未婚  无来源。Individual.MaritalStat，查询个体关系表(IndRelationShip)，RSType = '配偶'，如果有记录则更新为已婚，否则更新为null
	UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT CASE WHEN temp.cnum >0 THEN '已婚' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '配偶' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1);
	--UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT CASE WHEN cnum >0 THEN '已婚' END FROM (SELECT nvl(count(*),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE si1.indid = sirs.indid2 AND sirs.rstype = '配偶' GROUP BY sirs.indid2) temp WHERE si1.indid = temp.indid2)
	--Label10：查询IndRelationShip，RSType='父母'，首先将返回记录数更新Individual.COChild，若记录数为0，则更新本字段为'无子女'，若记录数为1，则更新本字段为'1孩'，若记录数大于等于2，则更新本字段为'2孩及以上'
	UPDATE shanglifeecif.Individual si1 SET si1.COChild = (SELECT CASE WHEN temp.cnum == 0 THEN '无子女' WHEN temp.cnum == 1 THEN '1孩' ELSE '2孩及以上' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '父母' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1);
	UPDATE shanglifeecif.Individual si1 SET si1.label10 = (SELECT CASE WHEN temp.cnum == 0 THEN '无子女' WHEN temp.cnum == 1 THEN '1孩' ELSE '2孩及以上' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '父母' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1);
	--UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT '已婚' FROM shanglifeecif.IndRelationShip sirs WHERE si1.indid = sirs.indid2 AND sirs.rstype = '配偶')
	--Label11：todo:无来源，逻辑需要说明清楚
	
	--Label12：todo:无来源，逻辑需要说明清楚。Individual.Drinking
	UPDATE shanglifeecif.Individual si1 SET si1.label12 = (SELECT si2.Drinking FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
	--Label13：todo:无来源，逻辑需要说明清楚。Individual.Smoking
	UPDATE shanglifeecif.Individual si1 SET si1.label13 = (SELECT si2.Smoking FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
	--Label14：todo:无来源，逻辑需要说明清楚。Individual.Cdiseases
	--Label15：todo:无来源，逻辑需要说明清楚。
	--Label16：todo:无来源，逻辑需要说明清楚。Individual.FMHistory
	--Label17：todo:无来源，逻辑需要说明清楚。Individual.Pregnancy
	--Label18：todo:无来源，逻辑需要说明清楚。
	--Label19：保留暂不实现
	--Label20：todo:无来源，逻辑需要说明清楚。
	--Label21：todo:无来源，Individual.PIncome:[0,120000],'低收入';(120000,1000000],'中产';(1000000,),'富人'
	UPDATE shanglifeecif.Individual si1 SET si1.Label21 = (SELECT CASE WHEN si2.pincome >=0 AND si2.pincome<=120000 THEN '低收入' WHEN si2.pincome >120000 AND si2.pincome<=1000000 THEN '中产' WHEN si2.pincome > 1000000 THEN '富人' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid);
	--Label22：todo:无来源，Individual.COVehicle:0,'无车';1,'1辆车';(1,),'2辆车以上'
	UPDATE shanglifeecif.Individual si1 SET si1.Label22 = (SELECT CASE WHEN si2.COVehicle ==0  THEN '无车' WHEN si2.COVehicle == 1  THEN '1辆车' WHEN si2.COVehicle > 1  THEN '2辆车以上' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid);
	--Label23：todo:无来源，Individual.CORP:0,'无房';1,'1套房';(1,),'2套房以上'
	UPDATE shanglifeecif.Individual si1 SET si1.Label23 = (SELECT CASE WHEN si2.CORP ==0  THEN '无房' WHEN si2.CORP == 1  THEN '1套房' WHEN si2.CORP > 1  THEN '2套房以上' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid);
	--Label24：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且APPFLAG = '有效'，若无记录则更新Individual.NOVPolicy为0，否则更新NOVPolicy为记录数。若NOVPolicy>0，则更新Individual.Label24为'持有寿险有效保单'，否则更新为'未持有寿险有效保单'
	--第一步 更新Individual.NOVPolicy有效记录数
	UPDATE shanglifeecif.Individual si1 SET si1.NOVPolicy = (SELECT temp.cuscount FROM (SELECT  nvl(count(spi.customerno),0) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno  WHERE spi.appflag = '有效' GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid);
	
	--第二步 更新 Individual.NOIVPolicy 无效记录数
	UPDATE shanglifeecif.Individual si1 SET si1.noivpolicy = (SELECT temp.cuscount FROM (SELECT  nvl(count(spi.customerno),0) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno  WHERE spi.appflag <> '有效' GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid);
	--最后一步 更新标签 24
	UPDATE shanglifeecif.Individual si1 SET si1.label24 = (SELECT CASE WHEN nvl(si2.novpolicy,0)>0 THEN '持有寿险有效保单' ELSE '未持有寿险有效保单' END FROM shanglifeecif.individual si2 WHERE si2.indid = si1.indid);
	
	
	--Label25：将Individual.NOVPolicy+Individual.NOIVPolicy的值更新ndividual.Label25
	UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (SELECT (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid);
	UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (SELECT CASE 
														WHEN si2.label25 == 0 THEN '无保单' 
														WHEN si2.label25 == 1 THEN '1件保单' 
														WHEN si2.label25 == 2 THEN '2件保单'  
														WHEN si2.label25 >= 3  AND si2.label25 <= 5 THEN '3-5件保单'  
														WHEN si2.label25 >= 6  AND si2.label25 <= 10 THEN '6-10件保单'  
														WHEN si2.label25 >= 11  AND si2.label25 <= 20 THEN '11-20件保单'  
														WHEN si2.label25 >= 21  AND si2.label25 <= 50 THEN '21-50件保单'  
														WHEN si2.label25 > 50 THEN '50件以上保单'  
														END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid);
	
	--Label26：InsuranceArrangement.PolicyNo = AUDIT_LN_LIST.CONTNO，以AUDIT_LN_LIST.LNMONEY更新InsuranceArrangement.LoanMoney，以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，若无记录更新Individual.Label26为Null,若LoanMoney>0则更新Individual.Label26为'有贷款'，否则更新为'无贷款'
	
	--第一步  以AUDIT_LN_LIST.LNMONEY更新InsuranceArrangement.LoanMoney
	UPDATE shanglifeecif.insurancearrangement sig SET sig.LoanMoney =(SELECT temp.lnmoney FROM (SELECT sall.contno,sum(sall.lnmoney) lnmoney FROM shanghailifeecif.audit_ln_list sall GROUP BY sall.contno) temp WHERE temp.contno = sig.policyno);
	--第二步 以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)
	--SELECT sig.applicantid,sum(sig.loanmoney) loanmoney FROM shanglifeecif.insurancearrangement sig GROUP BY sig.applicantid
	
	UPDATE shanglifeecif.Individual si1 SET si1.Label26 =(SELECT CASE WHEN temp.loanmoney > 0 THEN '有贷款' WHEN temp.loanmoney = 0 THEN '无贷款' ELSE NULL END FROM (SELECT sig.applicantid,sum(sig.loanmoney) loanmoney FROM shanglifeecif.insurancearrangement sig GROUP BY sig.applicantid) temp WHERE si1.indid = temp.applicantid); 
	--Label27：todo:无来源，逻辑需要说明清楚。
	--Label28：保留暂不实现
	--Label29：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表（CUSTOMERNO），POLICY_INFORMATION.APPFLAG='有效' and POLICY_INFORMATION.PAYINTV='趸缴'，若有记录则更新Individual.NOSPInsurance和本标签为'有趸交保单'
	UPDATE shanglifeecif.Individual si1 SET si1.label29 = 
	(SELECT CASE WHEN temp.cnum>0 THEN '有趸交保单' END FROM (SELECT  count(1) cnum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.APPFLAG = '有效' AND spi.PAYINTV = '趸缴' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	UPDATE shanglifeecif.Individual si1 SET si1.NOSPInsurance = 
	(SELECT CASE WHEN temp.cnum>0 THEN '有趸交保单' END FROM (SELECT  count(1) cnum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.APPFLAG = '有效' AND spi.PAYINTV = '趸缴' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	
	--Label30：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.ISelf为NULL，若有记录且RELATIONTOAPPNT仅为'本人'则若无记录则更新Individual.ISelf为'仅本人投保'，若有记录且RELATIONTOAPPNT不为'本人'则若无记录则更新Individual.ISelf为'本人未投保'，否则更新Individual.ISelf为'本人已投保'
	
	UPDATE shanglifeecif.Individual si1 SET si1.iself = (SELECT CASE WHEN temp.snum>0 THEN '本人已投保' WHEN temp.snum=0 THEN '本人未投保' END FROM (SELECT  nvl(count(spi.customerno),null) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '本人' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	UPDATE shanglifeecif.Individual si1 SET si1.iself = (SELECT CASE WHEN temp.snum=0 AND si1.iself ='本人已投保' THEN '仅本人投保'  END FROM (SELECT  nvl(count(spi.customerno),null) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '本人' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	--label31 以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Iparent为NULL，若有记录且RELATIONTOAPPNT仅为'父母'则若无记录则更新Individual.Iparent为'仅父母投保'，若有记录且RELATIONTOAPPNT不为'父母'则若无记录则更新Individual.Iparent为'父母未投保'，否则更新Individual.Iparent为'父母已投保'
	UPDATE shanglifeecif.Individual si1 SET si1.Iparent = (SELECT CASE WHEN temp.snum > 0 THEN '父母已投保' WHEN temp.snum=0 THEN '父母未投保'  END FROM (SELECT  count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '父母' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	UPDATE shanglifeecif.Individual si1 SET si1.Iparent = (SELECT CASE WHEN temp.snum=0  AND si1.Iparent = '父母已投保' THEN '仅父母投保' END FROM (SELECT  count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '父母' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	--Label32：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.IChildren为NULL，若有记录且RELATIONTOAPPNT仅为'子女'则若无记录则更新Individual.IChildren为'仅子女投保'，若有记录且RELATIONTOAPPNT不为'子女'则若无记录则更新Individual.IChildren为'子女未投保'，否则更新Individual.IChildren为'子女已投保'
	UPDATE shanglifeecif.Individual si1 SET si1.IChildren = (SELECT CASE WHEN temp.snum>0 THEN '子女已投保' WHEN temp.snum=0 THEN '子女未投保' END FROM (SELECT  count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '子女' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	UPDATE shanglifeecif.Individual si1 SET si1.IChildren = (SELECT CASE WHEN temp.snum=0 AND si1.IChildren = '子女已投保' THEN '仅子女投保' END FROM (SELECT  count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '子女' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	--Label33：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Imate为NULL，若有记录且RELATIONTOAPPNT仅为'配偶'则若无记录则更新Individual.Imate为'仅配偶投保'，若有记录且RELATIONTOAPPNT不为'配偶'则若无记录则更新Individual.Imate为'配偶未投保'，否则更新Individual.Imate为'配偶已投保'
	UPDATE shanglifeecif.Individual si1 SET si1.Imate = (SELECT CASE WHEN temp.snum>0 THEN '配偶已投保' WHEN temp.snum=0 THEN '配偶未投保' END FROM (SELECT  count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '子女' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid);
	UPDATE shanglifeecif.Individual si1 SET si1.Imate = (SELECT CASE WHEN temp.snum=0 AND si1.Imate = '配偶已投保' THEN '仅配偶投保'  END FROM (SELECT  count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '子女' GROUP BY spi.customerno) temp WHERE temp.customerno  =si1.scustid)
	--Label34：保留暂不实现
	--Label35：保留暂不实现
	--Label36：保留暂不实现
	--Label37：保留暂不实现
	--Label38：保留暂不实现
	--Label39：保留暂不实现
	--Label40：保留暂不实现
	--Label41：保留暂不实现
	--Label42：保留暂不实现
	--Label43：todo:逻辑需要说明清楚（到上游源表和字段）。
	
	--Label44：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且APPFLAG<>'有效'，若无记录则更新Individual.NOIVPolicy为0，否则更新NOIVPolicy为记录数。若NOIVPolicy>0，则更新Individual.Label44为'有失效保单'，否则更新为'无失效保单'
   UPDATE shanglifeecif.Individual si1 SET si1.label44 = (SELECT CASE WHEN si2.noivpolicy>0 THEN '有失效保单' END FROM shanglifeecif.individual si2 WHERE si2.indid = si1.indid);
	--LABEL45:以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且riskperiod='长期险'，若无记录则更新Individual.Label45为NULL，若有记录且paycount= payendyear则更新Individual.Label45为'有缴费期满长险保单'，否则更新Individual.Label45为'无缴费期满长险保单'
   UPDATE shanglifeecif.Individual si1 SET si1.label45 = (SELECT CASE WHEN temp.cuscount IS NULL THEN NULL WHEN temp.cuscount>0 THEN '有缴费期满长险保单' ELSE '无缴费期满长险保单' END  FROM (SELECT  count(spi.customerno) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno  WHERE spi.riskperiod='长期险' AND spi.paycount = spi.payendyear GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid);
   --Label46：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label46为NULL，若有记录且paycount= payendyear则更新Individual.Label46为'有续期缴费的保单'，否则更新Individual.Label46为'无有续期缴费的保单'
   UPDATE shanglifeecif.Individual si1 SET si1.label46 = (SELECT CASE WHEN temp.cuscount IS NULL THEN NULL WHEN temp.cuscount>0 THEN '有续期缴费的保单' ELSE '无有续期缴费的保单' END  FROM (SELECT  count(spi.customerno) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno  WHERE spi.paycount = spi.payendyear GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid);
   --Label47：todo:逻辑需要说明清楚（到上游源表和字段）。
   
   --Label48：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最早日期(PADate)更新Individual.FADate
    UPDATE shanglifeecif.Individual si1 SET si1.FADate =
    (SELECT '首次投保日期' || temp.padate FROM (SELECT min(si.PADate) padate,si.applicantid FROM shanglifeecif.insurancearrangement si GROUP BY si.applicantid) temp WHERE temp.applicantid = si1.indid);
    --Label49：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最近日期(PADate)更新Individual.LAPPDate，最后用当前日期减去Individual.LAPPDate更新Label49
    UPDATE shanglifeecif.Individual si1 SET si1.LAPPDate = (SELECT temp.PADate FROM (SELECT min(si.PADate) padate,si.applicantid FROM shanglifeecif.insurancearrangement si GROUP BY si.applicantid) temp WHERE temp.applicantid = si1.indid);
   UPDATE shanglifeecif.Individual si1 SET si1.Label49 = (SELECT '最后一次投保距今' || TRUNC(months_between(sysdate(), si2.LAPPDate)/12) || '天' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
   
    --Label50：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentChannel更新Individual.LPChannel
   --Label50：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentChannel更新Individual.LPChannel，并更新此标签为：'最近保单'+Individual.LPChannel 
   UPDATE shanglifeecif.Individual si1 SET si1.LPChannel = (SELECT temp.agentchannel FROM (SELECT si.agentchannel,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate DESC) rd FROM shanglifeecif.InsuranceArrangement si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1); 
   UPDATE shanglifeecif.Individual si1 SET si1.Label50 = (SELECT '最近保单' || si1.LPChannel FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);   
   --Label51：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentOrg更新Individual.LPOrg，并更新此标签为：'最近'+Individual.LPOrg+'机构'
    UPDATE shanglifeecif.Individual si1 SET si1.LPOrg = (SELECT temp.AgentOrg FROM (SELECT si.AgentOrg,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate DESC) rd FROM shanglifeecif.InsuranceArrangement si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1);
    UPDATE shanglifeecif.Individual si1 SET si1.Label51 = (SELECT '最近' || si2.LPOrg || '机构' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid) ;
	
    
    --Label52：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentChannel更新Individual.FAChannel
    UPDATE shanglifeecif.Individual si1 SET si1.FAChannel = (SELECT tt.agentchannel FROM (SELECT si.agentchannel,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd  FROM shanglifeecif.insurancearrangement si ) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1);
    UPDATE shanglifeecif.Individual si1 SET si1.Label52 = (SELECT '首单' || si2.FAChannel FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
    --Label53：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentOrg更新Individual.FAOrg
    UPDATE shanglifeecif.Individual si1 SET si1.FAOrg = (SELECT tt.AgentOrg FROM (SELECT si.AgentOrg,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd FROM shanglifeecif.insurancearrangement si) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1);
    UPDATE shanglifeecif.Individual si1 SET si1.Label53 = (SELECT '首单' || si2.FAOrg || '机构' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
    --Label54：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的PolicyState更新Individual.LPState
    UPDATE shanglifeecif.Individual si1 SET si1.LPState = (SELECT tt.PolicyState FROM (SELECT si.PolicyState,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd FROM shanglifeecif.insurancearrangement si) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1);
    
    --Label55 :Label55：由于上游保全类型信息分散在AUDIT_EDORLIST.EDORNAME和edorinfo.edortype 中，同时edorinfo.edortype为code需要转为string（具体映射见下）。以上游客户号(Individual.SCustID)分别查询AUDIT_EDORLIST表和edorinfo表，取保全生效日期最近的记录的更新的保全类型更新Individual.LPOSType。todo:edorinfo.edortype为code，需要转为string，edorinfo中需要增加上游客户号，否则需要多一次关联，建议大数据平台处理完成。
    --AUDIT_EDORLIST表里没有上游客户号，UPDATE 	shanglifeecif.Individual si1 SET si1.LPOSType =(SELECT ae.edorname FROM shanghailifeecif.audit_edorlist ae )
    UPDATE shanglifeecif.Individual si1 SET si1.lpostype = (SELECT tt.edortype FROM ( SELECT temp.scustid,temp.edortype,temp.edorvalidate,row_number()OVER(PARTITION BY temp.scustid ORDER BY temp.edorvalidate DESC) rd FROM ((SELECT si2.scustid,sae.edorname edortype,sae.edorvalidate FROM shanglifeecif.Individual si2 
    	LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno
    	LEFT JOIN shanghailifeecif.audit_edorlist sae ON sae.contno = spi.contno)
    	
    	UNION
   ( SELECT si2.scustid,se.edortype,se.edorappdate edorvalidate FROM shanglifeecif.Individual si2 
    	LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno
    	LEFT JOIN shanghailifeecif.edorinfo se ON se.contno = spi.contno)) temp) tt WHERE tt.scustid = si1.scustid AND tt.rd = 1);
   
    --Label56：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION.CONTNO,再根据CONTNO查询INSURANCEINFO表 and INSURANCEINFO.APPFLAG = '已退保',若有记录则更新此标签为'有退保保单'
    
    UPDATE shanglifeecif.Individual si1 SET si1.Label56 = 
	(SELECT CASE WHEN temp.cunm > 0 THEN '有退保保单' END FROM 	
    (
    SELECT count(1) cunm,si2.scustid FROM shanglifeecif.Individual si2 
    	LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno
    	LEFT JOIN shanghailifeecif.INSURANCEINFO sio ON spi.CONTNO = sio.CONTNO
    	WHERE sio.APPFLAG = '已退保'  GROUP BY si2.scustid
    	) temp WHERE temp.scustid = si1.scustid);
	--Label57：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION.CONTNO,再根据CONTNO查询AUDIT_CLAIM_INSURANCE表，取AUDIT_CLAIM_INSURANCE.RGTDATE最近对应的AUDIT_CLAIM_INSURANCE.RISKTYPE更新此标签。todo:实际数据仅有寿险、意外险、健康险，取值是否正确？
	UPDATE shanglifeecif.Individual si1 SET si1.Label57 =  
    	(SELECT temp.RISKTYPE FROM (
    	SELECT si2.scustid,saci.RISKTYPE,row_number()OVER(PARTITION BY si2.scustid ORDER BY saci.RGTDATE DESC) rd FROM shanglifeecif.Individual si2 
    	LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno
    	LEFT JOIN shanghailifeecif.audit_claim_insurance saci ON saci.CONTNO = spi.CONTNO
    	) temp WHERE si1.scustid = temp.scustid AND temp.rd = 1);
    	
    	
    --Label59 :以个体ID(Individual.IndID)查询InsuranceClaimThread表(Individual.IndID=InsuranceClaimThread.ApplicantID)， 以最近日期(CNDate)的ClaimCaseStatus更新Individual.CNStatus
    UPDATE shanglifeecif.Individual si1 SET si1.CNStatus =(SELECT temp.ClaimCaseStatus FROM  (SELECT si.ClaimCaseStatus,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.CNDate DESC) rd FROM shanglifeecif.insuranceclaimthread si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1);
    --Label60：保留暂不实现
	--Label61：todo:逻辑需要说明清楚（到上游源表和字段）。
	--Label62：保留暂不实现
	--Label63：todo:逻辑需要说明清楚（到上游源表和字段）。
	--Label64：todo:逻辑需要说明清楚（到上游源表和字段）。
    
    --Label65：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label65为NULL，若仅一条记录则更新Individual.Label65为'一单寿险客户'
    UPDATE shanglifeecif.Individual si1 SET si1.Label65 = 
    (SELECT CASE WHEN tt.cnum = 1 THEN '一单寿险客户' END FROM (SELECT nvl(count(spi.customerno),0) cnum,max(si2.indid) indid FROM shanghailifeecif.policy_information spi LEFT JOIN shanglifeecif.Individual si2 ON  si2.scustid = spi.customerno GROUP BY spi.customerno) tt WHERE si1.indid = tt.indid);
    --Label66：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label66为NULL，若 paycount= payendyear更新Individual.Label66为'缴费期满客户'，否则更新为'非缴费期满客户
     UPDATE shanglifeecif.Individual si1 SET si1.Label66 = 
    (SELECT CASE WHEN tt.cnum >0 THEN '缴费期满客户' END FROM (SELECT nvl(count(spi.customerno),0) cnum,max(si2.indid) indid FROM shanghailifeecif.policy_information spi LEFT JOIN shanglifeecif.Individual si2 ON  si2.scustid = spi.customerno and spi.paycount= spi.payendyear GROUP BY spi.customerno) tt WHERE si1.indid = tt.indid);
     --Label67：若Individual.Birthday的月日与上海人寿司庆日月日相同，则设置此标签为：'生日与司庆日同天'。todo:需要确定司庆日是几月几号
     
    --Label68:以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表, and POLICY_INFORMATION.APPFLAG='有效'，若有记录则设置此标签为'持有有效保单'，否则则设置为'未持有有效保单' 
    UPDATE shanglifeecif.Individual si1 SET si1.Label68 = (SELECT CASE WHEN si2.NOVPolicy>0 THEN '持有有效保单' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
    --Label69：Individual.CustClass，逻辑见上(一.7)
    UPDATE shanglifeecif.Individual si1 SET si1.Label69 = (SELECT si2.CustClass FROM shanglifeecif.Individual si2 WHERE si1.IndID = si2.IndID);
	--Label70:Individual.IndID = PartyTimeLine.PartyID,取PartyTimeLine.SDate最近的记录对应的Scenario更新此标签
	UPDATE shanglifeecif.Individual si1 SET si1.Label70 = (SELECT temp.Scenario FROM (SELECT sp.Scenario,sp.PartyID,row_number()OVER(PARTITION BY sp.PartyID ORDER BY sp.SDate DESC) rd FROM shanglifeecif.PartyTimeLine sp) temp WHERE si1.indid = temp.PartyID AND temp.rd = 1);
    --Label71：Individual.IndID = PartyTimeLine.PartyID,取PartyTimeLine.SDate最近的记录对应的Channel更新此标签
	UPDATE shanglifeecif.Individual si1 SET si1.Label71 = (SELECT temp.Channel FROM (SELECT sp.Channel,sp.PartyID,row_number()OVER(PARTITION BY sp.PartyID ORDER BY sp.SDate DESC) rd FROM shanglifeecif.PartyTimeLine sp) temp WHERE si1.indid = temp.PartyID AND temp.rd = 1);
	--Label72：保留暂不实现
	--Label73
	--Label74
	--Label75
	--Label76
     --Label77：Individual.Birthday 距离当前日期5天内，更新Individual.Label77为'本人生日临近'，否则为NULL
     UPDATE shanglifeecif.Individual si1 SET si1.Label77 = (SELECT CASE WHEN DATEDIFF(sysdate(), si2.birthday)<=5 THEN '本人生日临近' END FROM shanglifeecif.Individual si2 WHERE si1.IndID = si2.IndID);
    --Label78:以个体ID(Individual.IndID)查询IndRelationShip表，若Role2为'父亲'或'母亲'的IndID2的生日(通过IndID2再反向关联Individual)，则更新Individual.Label78为'父母生日临近'，否则为NULL
    UPDATE shanglifeecif.Individual si1 SET si1.Label78 = (SELECT CASE WHEN DATEDIFF(sysdate(), si1.birthday)<=5 THEN '父母生日临近' END FROM (
     SELECT sis.indid2 FROM shanglifeecif.Individual si2 LEFT JOIN shanglifeecif.IndRelationShip sis ON si2.indid = sis.indid1 WHERE (sis.role2 = '父亲' or sis.role2 = '母亲')  GROUP BY sis.indid2) tt WHERE  si1.indid =  tt.indid2);
     --Label79：以个体ID(Individual.IndID)查询IndRelationShip表，若Role2为'儿子'或'女儿'的IndID2的生日(通过IndID2再反向关联Individual)，则更新Individual.Label79为'子女生日临近'，否则为NULL
    UPDATE shanglifeecif.Individual si1 SET si1.Label79 = (SELECT CASE WHEN DATEDIFF(sysdate(), si1.birthday)<=5 THEN '子女生日临近' END FROM (
     SELECT sis.indid2 FROM shanglifeecif.Individual si2 LEFT JOIN shanglifeecif.IndRelationShip sis ON si2.indid = sis.indid1 WHERE (sis.role2 = '儿子' or sis.role2 = '女儿')  GROUP BY sis.indid2) tt WHERE  si1.indid =  tt.indid2);
     
     	
     --Label96：根据Individual.Birthday算出当前年龄，如果50<=年龄<60，则更新Individual.Label96为'大龄'，否则为NULL
     UPDATE shanglifeecif.Individual si1 SET si1.Label96 = (SELECT CASE WHEN TRUNC(months_between(sysdate, si2.birthday)/12)>=50 AND TRUNC(months_between(sysdate, si2.birthday)/12)<60 THEN '大龄' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
     --Label97：根据Individual.Birthday算出当前年龄，如果60<=年龄，则更新Individual.Label97为'高龄'，否则为NULL
     UPDATE shanglifeecif.Individual si1 SET si1.Label97 = (SELECT CASE WHEN TRUNC(months_between(sysdate, si2.birthday)/12)>=60  THEN '高龄' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
     --Label98：根据Individual.Occupation，若为记者、律师、公务员、媒体、金融行业，则更新Individual.Label98为'敏感职业'，否则为NULL
     UPDATE shanglifeecif.Individual si1 SET si1.Label98 = (SELECT CASE WHEN si2.occupation = '记者' OR si2.occupation = '律师' OR si2.occupation = '公务员' OR  si2.occupation = '媒体' OR  si2.occupation = '金融行业' THEN '敏感职业' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid);
     --Label109：以个体ID(Individual.IndID)查询InsuranceClaimThread，若ClaimCaseStatus='报案'且 InsuranceClaimThread.CNDate距当前日期超过7天，则更新Individual.Label109为'理赔报案追踪'
    	UPDATE shanglifeecif.Individual si1 SET si1.Label109 = (SELECT CASE WHEN count(1)>0 THEN '理赔报案追踪' END FROM shanglifeecif.insuranceclaimthread sit WHERE si1.indid = sit.applicantid AND sit.claimstatus = '报案' AND DATEDIFF(sysdate(), sit.CNDate)>7);
     --Label110：以个体ID(Individual.IndID)查询InsuranceClaimThread，若ClaimCaseStatus='受理' or '立案'，则更新Individual.Label110为'理赔处理中'
     UPDATE shanglifeecif.Individual si1 SET si1.Label110 = (SELECT CASE WHEN count(1)>0 THEN '理赔处理中' END FROM shanglifeecif.insuranceclaimthread sit WHERE si1.indid = sit.applicantid AND (sit.claimstatus = '受理' OR sit.claimstatus = '立案'));
     
     --Label112：先以个体ID(Individual.IndID)得到InsuranceArrangement.PolicyNo，再以此关联AUDIT_EDORLIST，若有记录且保全状态EDORSTATE<>'确认生效' and '复核终止' and '强制终止'，则更新Individual.Label111为'保全处理中'
    UPDATE shanglifeecif.Individual si1 SET si1.Label112 = (SELECT CASE WHEN nvl(count(sae.contno),0) > 0 THEN '保全处理中' END FROM (SELECT si1.indid indid,max(sia.policyno) policyno  FROM  shanglifeecif.Individual si1 LEFT JOIN shanglifeecif.InsuranceArrangement sia ON si1.indid = sia.applicantid GROUP BY si1.indid) tt LEFT JOIN shanghailifeecif.audit_edorlist sae ON sae.contno = tt.policyno AND sae.edorstate <> '确认生效' AND sae.edorstate <> '复核终止' AND sae.edorstate <> '强制终止' WHERE si1.indid = tt.indid);
    
     EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
END	;