ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/个体关系信息.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship_main( ) 
IS
DECLARE
	s_count int
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(),
		'父母',
		p.CUSTOMERNO,
		max(p.NAME),
		max(p.IDNO),
		CASE max(p.sex)
			WHEN '0' THEN '儿子'  WHEN '1' THEN '女儿' END,
		p.INSUREDNO,
		max(p.INSUREDNAME),
		max(p.INSUREDIDNO),
		min(p.polapplydate),
		CASE max(p.INSUREDSEX)
			WHEN '0' THEN '父亲'  WHEN '1' THEN '母亲' END
		FROM POLICY_INFORMATION p
		WHERE (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女')
		AND p.birthday > p.insuredbirthday
		GROUP BY p.CUSTOMERNO,p.INSUREDNO;
	
	select count(*) into s_count from shanglifeecif.IndRelationShip;
	
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)	
	SELECT 
	row_number()over()+s_count,
	'子女',
	p.CUSTOMERNO,
	max(p.NAME),
	max(p.IDNO),
	CASE max(p.sex)
		WHEN '0' THEN '父亲'  WHEN '1' THEN '母亲' END,
	p.INSUREDNO,
	max(p.INSUREDNAME),
	max(p.INSUREDIDNO),
	min(p.polapplydate),
	CASE max(p.INSUREDSEX)
		WHEN '0' THEN '儿子'  WHEN '1' THEN '女儿' END
	FROM POLICY_INFORMATION p
	WHERE (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女')
	AND p.birthday < p.insuredbirthday
	GROUP BY p.CUSTOMERNO,p.INSUREDNO;	
	
	select count(*) into s_count from shanglifeecif.IndRelationShip;
	--第二步取关系祖父母、外祖父母    祖孙、外祖孙
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)SELECT 
	row_number()over()+s_count,
	'祖父母、外祖父母',
	p.CUSTOMERNO,
	max(p.NAME),
	max(p.IDNO),
	CASE max(p.sex)
		WHEN '0' THEN '(外)祖孙'  WHEN '1' THEN '(外)孙女' END,
	p.INSUREDNO,
	max(p.INSUREDNAME),
	max(p.INSUREDIDNO),
	min(p.polapplydate),
	CASE max(p.INSUREDSEX)
		WHEN '0' THEN '(外)祖父'  WHEN '1' THEN '(外)祖母' END
	FROM POLICY_INFORMATION p
	WHERE (p.RELATIONTOAPPNT = '祖父母、外祖父母' OR p.RELATIONTOAPPNT = '祖孙、外祖孙')
	AND p.birthday > p.insuredbirthday
	GROUP BY p.CUSTOMERNO,p.INSUREDNO;	
	
	select count(*) into s_count from shanglifeecif.IndRelationShip;
	
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)SELECT 
		row_number()over()+s_count,
		'祖孙、外祖孙',
		p.CUSTOMERNO,
		max(p.NAME),
		max(p.IDNO),
		CASE max(p.sex)
			WHEN '0' THEN '(外)祖父'  WHEN '1' THEN '(外)祖母' END,
		p.INSUREDNO,
		max(p.INSUREDNAME),
		max(p.INSUREDIDNO),
		min(p.polapplydate),
		CASE max(p.INSUREDSEX)
			WHEN '0' THEN '(外)祖孙'  WHEN '1' THEN '(外)孙女' END
		FROM POLICY_INFORMATION p
		WHERE (p.RELATIONTOAPPNT = '祖父母、外祖父母' OR p.RELATIONTOAPPNT = '祖孙、外祖孙')
		AND p.birthday < p.insuredbirthday
		GROUP BY p.CUSTOMERNO,p.INSUREDNO;
	
	select count(*) into s_count from shanglifeecif.IndRelationShip;	
	--第三步取配偶和其他
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)SELECT 
	row_number()over()+s_count,
	max(p.RELATIONTOAPPNT),
	p.CUSTOMERNO,
	max(p.NAME),
	max(p.IDNO),
	CASE max(p.RELATIONTOAPPNT)
		WHEN '配偶' 
			THEN case max(p.sex) WHEN '0' THEN '丈夫'  WHEN '1' THEN '妻子' END
		ELSE max(p.RELATIONTOAPPNT)
		END,
	p.INSUREDNO,
	max(p.INSUREDNAME),
	max(p.INSUREDIDNO),
	min(p.polapplydate),
	CASE max(p.RELATIONTOAPPNT)
		WHEN '配偶' 
			THEN case max(p.INSUREDSEX) WHEN '0' THEN '丈夫'  WHEN '1' THEN '妻子' END
		ELSE max(p.RELATIONTOAPPNT)
		END
	FROM POLICY_INFORMATION p
	WHERE p.RELATIONTOAPPNT = '配偶' or p.RELATIONTOAPPNT = '其他'
	GROUP BY p.CUSTOMERNO,p.INSUREDNO;
	
	
	/*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.polapplydate),
   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 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);
*/
	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;