ShangHaiLifeECIF/sql-achievement/存储过程/洛阳测试环境使用sql整理/个体关系存储过程.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship_main( ) 
IS
BEGIN
	DELETE FROM shanglifeecif.IndRelationShip;
	--从保单信息表（POLICY_INFORMATION）获取投保人和被保人的关系并
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)
	SELECT 
	row_number()over(),
	max(p.RELATIONTOAPPNT),
	p.CUSTOMERNO,
	max(p.NAME),
	max(p.IDNO),
	CASE max(p.RELATIONTOAPPNT) 
			WHEN '子女' 
				THEN CASE max(p.sex) 
					WHEN 1 THEN '母亲' 
					WHEN 0 THEN '父亲' 
				END
		    WHEN '父母' 
		    	THEN CASE max(p.sex) 
		    		WHEN 1 THEN '女儿' 
		    		WHEN 0 THEN '儿子' 
		    	END
		    WHEN '配偶' 
		    	THEN CASE max(p.sex) 
		    		WHEN 1 THEN '妻子' 
		    		WHEN 0 THEN '丈夫' 
		    	END
		    WHEN '祖父母、外祖父母' 
		    	THEN CASE max(p.sex) 
		    		WHEN 1 THEN '(外)孙女' 
		    		WHEN 0 THEN '(外)祖孙' 
		    	END
		    WHEN '祖孙、外祖孙' 
		    	THEN CASE max(p.sex) 
		    		WHEN 1 THEN '(外)祖母' 
		    		WHEN 0 THEN '(外)祖父' 
		    	END
			ELSE max(p.RELATIONTOAPPNT) 
		END AS Role1,
	p.INSUREDNO,
	max(p.INSUREDNAME),
	max(p.INSUREDIDNO),
	min(p.CUSTOMGETPOLDATE),
   CASE max(p.RELATIONTOAPPNT) 
			WHEN '子女' 
				THEN CASE max(p.INSUREDSEX) 
					WHEN 1 THEN '女儿' 
					WHEN 0 THEN '儿子' 
				END
		    WHEN '父母' 
		    	THEN CASE max(p.INSUREDSEX) 
		    		WHEN 1 THEN '母亲' 
		    		WHEN 0 THEN '父亲' 
		    	END
		    WHEN '配偶' 
		    	THEN CASE max(p.INSUREDSEX) 
		    		WHEN 1 THEN '妻子' 
		    		WHEN 0 THEN '丈夫' 
		    	END
		    WHEN '祖父母、外祖父母' 
		    	THEN CASE max(p.INSUREDSEX) 
		    		WHEN 1 THEN '(外)祖母' 
		    		WHEN 0 THEN '(外)祖父' 
		    	END
		    WHEN '祖孙、外祖孙' 
		    	THEN CASE max(p.INSUREDSEX) 
		    		WHEN 1 THEN '(外)孙女' 
		    		WHEN 0 THEN '(外)祖孙' 
		    	END
			ELSE max(p.RELATIONTOAPPNT) 
		END AS Role2
		FROM shanghailifeecif.POLICY_INFORMATION p
		WHERE p.RELATIONTOAPPNT<>'本人'
		GROUP BY p.CUSTOMERNO,p.INSUREDNO;
	
	--此时插入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);

END;