CREATE PROCEDURE shanglifeecif.222NewProcedure( ) 
IS
DECLARE
	s_count int
BEGIN
	 DELETE FROM shanglifeecif.IndRelationShip;
	--从保单信息表（dsj.POLICY_INFORMATION）获取投保人和被保人的关系并
	--第一步取关系父母，子女
	 insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)SELECT 
		reflect("java.util.UUID", "randomUUID"),
		'父母',
		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 dsj.POLICY_INFORMATION p
		WHERE (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女')
		AND p.birthday > p.insuredbirthday
		GROUP BY p.CUSTOMERNO,p.INSUREDNO;

	
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)	
	SELECT 
	reflect("java.util.UUID", "randomUUID"),
	'子女',
	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 dsj.POLICY_INFORMATION p
	WHERE (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女')
	AND p.birthday < p.insuredbirthday
	GROUP BY p.CUSTOMERNO,p.INSUREDNO;	
	
	--第二步取关系祖父母、外祖父母    祖孙、外祖孙
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)SELECT 
	reflect("java.util.UUID", "randomUUID"),
	'祖父母、外祖父母',
	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 dsj.POLICY_INFORMATION p
	WHERE (p.RELATIONTOAPPNT = '祖父母、外祖父母' OR p.RELATIONTOAPPNT = '祖孙、外祖孙')
	AND p.birthday > p.insuredbirthday
	GROUP BY p.CUSTOMERNO,p.INSUREDNO;	
	
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)SELECT 
		reflect("java.util.UUID", "randomUUID"),
		'祖孙、外祖孙',
		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 dsj.POLICY_INFORMATION p
		WHERE (p.RELATIONTOAPPNT = '祖父母、外祖父母' OR p.RELATIONTOAPPNT = '祖孙、外祖孙')
		AND p.birthday < p.insuredbirthday
		GROUP BY p.CUSTOMERNO,p.INSUREDNO;
		
	--第三步取配偶和其他
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)SELECT 
	reflect("java.util.UUID", "randomUUID"),
	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 dsj.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,
		Role2
	)SELECT reflect("java.util.UUID", "randomUUID"),
		CASE RSType 
			WHEN '父母' THEN '子女'
			WHEN '子女' THEN '父母'
			WHEN '祖孙、外祖孙' THEN '祖父母、外祖父母'
			WHEN '祖父母、外祖父母' THEN '祖孙、外祖孙'
			WHEN '配偶' THEN '配偶'
			ELSE RSType
		END,
		IndID2,
		Name2,
		IDCard2,
		Role2,
		IndID1,	
		Name1,
		IDCard1,
		Role1
FROM shanglifeecif.IndRelationShip sis ;
	dbms_output.put_line('init_indrelationship_main函数跑批完成！');
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());	
	
END;