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;