CREATE OR REPLACE PROCEDURE shanglifeecif.init_communication() IS
DECLARE
BEGIN
	DELETE FROM shanglifeecif.communication;
	insert into shanglifeecif.communication (
		CommID,
		CPName,
		CPPhone,
		PolicyNo,
		CommTime,
		Reason,
		CommChannel,
		created_by ,-- '������', 
  		created_time -- '����ʱ��', 
	) select 
   		row_number()over(),
   		econtactsName,
		econtactsPhone,
		contNos,
		startTime,
		reasonSecondName,
		'�������' AS CommChannel,
		'admin',
		sysdate()
	from cc_record_main WHERE contNos IS NOT null;
	
END	;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_communication_sp01() IS
DECLARE
BEGIN
	UPDATE shanglifeecif.communication sc set(
		sc.RSOApplicant,
		sc.CompLink,
		sc.CompReason,
		sc.ProcedureResult
	
	) = (SELECT 
			temp.complaintsRelation,
			temp.complaintsLink,
			temp.resultClassification,
			temp.procedureResult
	
		FROM (
			
			SELECT  
			(CASE scac.complaintsRelation --201  ���� 202  ��Ů 203  ��ż 204  ��ĸ  205 ����
			WHEN '201' THEN '����'  
			WHEN '202' THEN '��Ů'  
			WHEN '203' THEN '��ż'  
			WHEN '204' THEN '��ĸ'  
			WHEN '205' THEN '����'  
			END) AS complaintsRelation,
			(CASE scac.complaintsLink --���б����� : 211 ���ۻ��� :212 ���طû���:213 ����ȫ�������: 214 �����⻷��: 215 ���ۺ���񻷽�: 216
			WHEN '211' THEN '�����'  
			WHEN '212' THEN '���ۻ���'  
			WHEN '213' THEN '�طû���'  
			WHEN '214' THEN '��ȫ�������'  
			WHEN '215' THEN '���⻷��'  
			WHEN '216' THEN '�ۺ���񻷽�'  
			END) AS complaintsLink,
			(CASE scac.resultClassification --��������:101  ��ǩ��:102  ��������:103  ������Ա̬��:104 �ۺ������:105 ��ǰ������:106  �ⲿİ��ɧ��:107
			WHEN '101' THEN '������'  
			WHEN '102' THEN '��ǩ��'  
			WHEN '103' THEN '��������'  
			WHEN '104' THEN '������Ա̬��'  
			WHEN '105' THEN '�ۺ������'  
			WHEN '106' THEN '��ǰ������'  
			WHEN '107' THEN '�ⲿİ��ɧ��'  
			END) AS resultClassification,
			scac.procedureResult,
			row_number() over(partition by scac.productNos) rn,
			scac.productNos,
			scac.complaintsName
		FROM 
		cc_action_complaints scac
		) temp
		WHERE sc.PolicyNo = temp.productNos AND sc.CPName = temp.complaintsName AND temp.rn = 1)
	
END	;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_communication_sp02() IS
DECLARE
BEGIN
	UPDATE shanglifeecif.communication sc SET(
	IndID,
	CPCertID
	) = (
	SELECT 
	ApplicantID,
	AppCertID
	FROM 
	shanglifeecif.InsuranceArrangement sia
	WHERE sia.PolicyNo = sc.PolicyNo AND sc.RSOApplicant = '����'
	)
END	;