CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_0( individual_count OUT int) -- ��ʼ�� ����֤
IS    
BEGIN
	
	--��ѯ���˴δ��������ݲ�������
	insert into shanglifeecif.individual (
		indid,
		custid,

		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	idcard,
		custtype,
	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	

		scustid ,
		name,
		gender ,
		birthday,
		idcard ,
		max(custtype) AS custtype,
		sysdate,
		'admin'
		FROM (
			SELECT
				customerno AS scustid,--Ͷ����
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard,
				"Ͷ����" AS custtype
			FROM
				policy_information 
			WHERE customerno IS NOT NULL AND idtype=0
			UNION 
			SELECT 
				insuredno AS scustid,--������
				insuredname AS name,
				insuredsex AS gender,
				insuredbirthday AS  birthday,
				insuredidtype AS idtype , 
				insuredidno AS idcard,
				"������" AS custtype
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=0
			) tmpTable GROUP BY scustid ,name,gender,birthday,idcard
	--�Ѿ����ڵ���������
	select count(0) into individual_count from shanglifeecif.individual

END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_1(individual_count INOUT  int) -- ��ʼ�� ����
IS    
BEGIN
	
	--��ѯ���˴δ��������ݲ�������
	insert into shanglifeecif.individual (
			indid,
			custid,
		 	
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	passport,
		 	custtype,
		 	
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over()+individual_count,
		'CP'||lpad(row_number()over()+individual_count,10,'0'),

		scustid ,
		name,
		gender ,
		birthday,
		idcard ,
		max(custtype) as custtype,
		sysdate,
		'admin'
		FROM (
			SELECT
				customerno AS scustid,--Ͷ����
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard,
				"Ͷ����" as custtype
			FROM
				policy_information 
			WHERE customerno IS NOT NULL AND idtype=1
			UNION 
			SELECT 
				insuredno AS scustid,--������
				insuredname AS name,
				insuredsex AS gender,
				insuredbirthday AS  birthday,
				insuredidtype AS idtype , 
				insuredidno AS idcard,
				"������" as custtype
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=1
			) tmpTable GROUP BY scustid ,name,gender ,birthday,idcard

	--�Ѿ����ڵ���������
	select count(0) into individual_count from shanglifeecif.individual


END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_3(individual_count INOUT  int) -- ��ʼ�� ����
IS    
BEGIN
	
	--��ѯ���˴δ��������ݲ�������
	insert into shanglifeecif.individual(
		indid,
		custid,

		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	dlicense,
		custtype,
	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over()+individual_count,
		'CP'||lpad(row_number()over()+individual_count,10,'0'),
		
		scustid ,
		name,
		gender ,
		birthday,
		idcard ,
		max(custtype) as custtype,
		sysdate,
		'admin'
		FROM (
			SELECT
				customerno AS scustid,--Ͷ����
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard,
				"Ͷ����" as custtype
			FROM
				policy_information 
			WHERE customerno IS NOT NULL AND idtype=3
			UNION 
			SELECT 
				insuredno AS scustid,--������
				insuredname AS name,
				insuredsex AS gender,
				insuredbirthday AS  birthday,
				insuredidtype AS idtype , 
				insuredidno AS idcard,
				"������" as custtype
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=3
			) tmpTable GROUP BY scustid ,name,gender ,birthday,idcard

	--�Ѿ����ڵ���������
	select count(0) into individual_count from shanglifeecif.individual
END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_other(individual_count INOUT  int) -- ��ʼ��������������֤����Ϣ
IS    
BEGIN
	
	--��ѯ���˴δ��������ݲ�������
	insert into shanglifeecif.individual(
			indid,
			custid,
		 
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	
		 	
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over()+individual_count,
		'CP'||lpad(row_number()over()+individual_count,10,'0'),
		scustid ,
		name,
		gender ,
		birthday,
		sysdate,
		'admin'
		FROM (
			SELECT
				customerno AS scustid,--Ͷ����
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard,
				"Ͷ����" as custtype
			FROM
				policy_information 
			WHERE customerno IS NOT NULL AND idtype not in (0,1,3)
			UNION
			SELECT 
				insuredno AS scustid,--������
				insuredname AS name,
				insuredsex AS gender,
				insuredbirthday AS  birthday,
				insuredidtype AS idtype , 
				insuredidno AS idcard,
				"������" as custtype
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype in (0,1,3)
			) tmpTable GROUP BY scustid ,name,gender ,birthday,idcard

	--�Ѿ����ڵ���������
	select count(0) into individual_count from shanglifeecif.individual


END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.up_t_customers_class_1() -- �����洢����
IS    
BEGIN
	UPDATE shanglifeecif.individual a SET (
			CustClass	,--	�ͻ��ȼ�
			ConValue	,--	���׶ȷ�
			Awarded3	,--	��ͥ�ӷ�2
			Awarded2	,--	���ڼӷ�
			Awarded1	,--	�����ӷ�
			SOValue	,--	�ܷ�ֵ
			EndDate,	--	�ͻ��ȼ�ʧЧ����
			Height,		--����
			Weight,		--����
			BMI,
			PIncome,	--����������
			FIncome,	--��ͥ������
			IncomeSource,	--������Դ
			SIStatus,	--�籣���
			Ethnic,  --�������
			Nation, --����
			MaritalStat, --����
			Employer,--������λ
			Education,--ѧ��
			Dday, --��������
			regtype,  --��������
			ZIPCODE,--�ʱ�
			HPhone,--�绰
			PMPhone,--�ֻ�
			email, -- ����  
			RAL    --��ϵ��ַ
		) = (
			select 
					CLASS_VALUE	,--�ͻ��ȼ�
					CONTRIBUTION_VALUE ,--���׶ȷ�
					AWARDED3, --��ͥ�ӷ�
					AWARDED2, --���ڼӷ�
					AWARDED1, --�����ӷ�
					TOTAL_VALUE, --�ܷ�ֵ
					END_DATE, --ʧЧ����
					STATURE,	--����
					AVOIRDUPOIS,--����
					BMI,        --�����������ؼ���
					YEARINCOME,	--����������
					FAMILYYEARSALARY,	--��ͥ������
					INCOMESOURCE,	--������Դ
					SOCIALINSUFLAG,	--�籣���
					NATIONALITY, --�������
					NATIVEPLACE, --����
					MARRIAGE,--����
					GRPNAME,--���������
					DEGREE,--ѧ��
					DEATHDATE,--��������
					DENTYPE,--��������
					ZIPCODE,--�ʱ�
					PHONE,--�绰
					MOBILE,--�ֻ�
					EMAIL,--����
					POSTALADDRESS --��ϵ��ַ
					

				from t_customer_class b
				where b.CUSTOMER_ID = a.scustid
		) WHERE 1=1 ;

END;
/

CREATE OR REPLACE PROCEDURE shanglifeecif.up_health_insurance_listing_1() -- �����洢����
IS    
BEGIN
	--�����ʱ������
	delete shanglifeecif.occupation_tmp;
	--����ȥ�����ݵ���ʱ��
	insert into shanglifeecif.occupation_tmp(
		scustid,
		OccupationId,
		Occupation,
		HomeAdress
	)
	select 
		appntno,
		appntoccupationcode	,--ְҵ����
		appntoccupationname ,
		APPNTHOMEADDRESS--ְҵ����
	from HEALTH_INSURANCE_LISTING 
	WHERE appntoccupationcode is not null
	and appntoccupationname is not null
	group by appntno,appntoccupationcode,appntoccupationname,APPNTHOMEADDRESS

	--���¸�����Ϣ��

	UPDATE shanglifeecif.individual a SET (
		OccupationId	,--	ְҵ����
		Occupation,	--	ְҵ����
		HomeAdress
	) = (
		select 
			OccupationId	,--ְҵ����
			Occupation,--ְҵ����
			HomeAdress 
		from shanglifeecif.occupation_tmp b
		where b.scustid = a.scustid 
	) WHERE 1=1 ;

	/*--���±������ֻ���
	UPDATE shanglifeecif.individual a SET (
		PMPhone
	) = (
		select 
			INSUREDMOBILE
			
		from policy_information b
		where b.INSUREDNO = a.scustid 
	) WHERE 1=1 ;*/
	
END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_main() -- ��ʼ�� ����ȫ������
IS    
DECLARE
	individual_count int
	strsql string
BEGIN
	--�����Ƿ�һ���ͻ�id�ж�����¼��Ӧ��ͨ��֤�����
	--��ʼ�� ����֤ �û�
	shanglifeecif.init_individual_0(individual_count);

	--��ʼ��  �����û�
	individual_count:=individual_count+1;
	shanglifeecif.init_individual_1(individual_count);
	--��ʼ�� �����û�
	individual_count:=individual_count+1;
	shanglifeecif.init_individual_3(individual_count);
	--���� ֤�������û�
	individual_count:=individual_count+1;
	shanglifeecif.init_individual_other(individual_count);
	
	--�����û��ȼ���Ϣ
	shanglifeecif.up_t_customers_class_1();
	
	--����ְҵ��Ϣ
	shanglifeecif.up_health_insurance_listing_1();

END;
/
BEGIN
	shanglifeecif.init_individual_main();
end ;