ShangHaiLifeECIF/sql-achievement/存储过程/个体关系koucx.sql

--初始化个体关系
CREATE OR REPLACE PROCEDURE shanglifeecif.initIndRelationShip() IS
DECLARE
BEGIN
	DELETE FROM shanglifeecif.IndRelationShip;
	--从保单信息表（POLICY_INFORMATION）获取投保人和被保人的关系并
	insert into shanglifeecif.IndRelationShip(irsid,RSType,IndID1,Name1,IDCard1,IndID2,Name2,IDCard2,RSSTime,Role2)
	SELECT row_number()over(),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 shanghailifeecif.POLICY_INFORMATION 
		WHERE RELATIONTOAPPNT<>'本人'
		GROUP BY CUSTOMERNO,INSUREDNO;
	--更新IndRelationShip.Role1为IndRelationShip.Role2的相对角色，即role2为母亲则role1为父亲等	
	UPDATE shanglifeecif.IndRelationShip sirs SET sirs.role1 = (SELECT 
	CASE slirs.role2 
		WHEN '父亲' THEN '母亲' 
		WHEN '母亲' THEN '父亲' 
		WHEN '丈夫' THEN '妻子' 
		WHEN '妻子' THEN '丈夫' 
		WHEN '儿子' THEN '女儿' 
		WHEN '女儿' THEN '儿子'
	ELSE '其它' END	
	FROM shanglifeecif.IndRelationShip slirs WHERE sirs.irsid = slirs.irsid);
	--此时插入IndRelationShip的IndID1，IndID2为上游系统客户号，根据Individual.SCustID更新为Individual.IndID	
	
	UPDATE shanglifeecif.IndRelationShip sirs SET (sirs.indid1) = 
	(SELECT si.indid FROM shanglifeecif.individual si WHERE si.scustid = sirs.indid1);
	
	UPDATE shanglifeecif.IndRelationShip sirs SET (sirs.indid2) = 
	(SELECT si.indid FROM shanglifeecif.individual si WHERE si.scustid = sirs.indid2);
	
	
   	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	;