ShangHaiLifeECIF/sql-achievement/存储过程/洛阳测试环境使用sql整理/接触信息存储过程.sql

BEGIN
	shanglifeecif.init_communication_main();
END;	
/

CREATE OR REPLACE PROCEDURE shanglifeecif.init_communication_main() IS
DECLARE
BEGIN
	shanglifeecif.init_communication();
	shanglifeecif.init_communication_sp01();
	shanglifeecif.init_communication_sp02();
END ;

/
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 (
			SELECT 
		ApplicantID,
		AppCertID,
		row_number() over(partition by PolicyNo) rn,
		PolicyNo
		FROM 
		shanglifeecif.InsuranceArrangement sia
		
		) temp
	WHERE temp.PolicyNo = sc.PolicyNo AND sc.RSOApplicant = '本人' AND temp.rn = 1)
END	;
/

BEGIN
	shanglifeecif.init_communication();
	shanglifeecif.init_communication_sp01();
	shanglifeecif.init_communication_sp02();
END ;