ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/参与方事件.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_tb() -- 创建主存储过程
IS    
BEGIN
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PCertID,
		PRole,
		Scenario,
		SDate,
		enddate,
		created_by ,
		created_time 
	) select 
   		reflect("java.util.UUID", "randomUUID"),
   		trim(contno) as contno,
   		trim(max(customerno)) as customerno,
   		trim(max(name)) as name,
   		trim(max(idno)) as idno,
   		'投保人',
   		'投保',
   		trim(max(polapplydate)) as polapplydate,
		trim(max(enddate)) as enddate,
		'admin',
		sysdate()
	from dsj.policy_information WHERE contno IS NOT NULL GROUP BY contno;
	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;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_lp() 
IS
DECLARE
	p_count int
BEGIN
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PCertID,
		PRole,
		Scenario,
		SDate,
		describe,
		created_by ,
		created_time 
	) select 
   		reflect("java.util.UUID", "randomUUID"),
   		trim(contno) as contno,
   		trim(INSUREDNO) as INSUREDNO,
   		trim(INSUREDNAME) as INSUREDNAME,
   		trim(idno) as idno,
   		'投保人',
   		'理赔',
   		CASE 
   		WHEN APPLYDATE IS NOT NULL THEN APPLYDATE
   		WHEN APPLYDATE IS NULL THEN RPTDATE 
   		END AS SDate,
   		trim(ACCIDENTTYPE) as ACCIDENTTYPE,
   		'admin',
		sysdate()
	from dsj.insurance_claim  WHERE contno IS NOT NULL;
	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;
/

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_zx() 
IS
DECLARE
	p_count int
BEGIN
	
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time 
	) SELECT 
				reflect("java.util.UUID", "randomUUID"),
		   		m.contnos,
		   		max(i.customerno) customerno,
		   		max(m.econtactsName) econtactsName,
		   		'投保人' AS tbr,
		   		'咨询' AS zx,
		   		m.starttime,
		   		max(m.reasonsecondname) reasonsecondname,
		   		'admin',
				sysdate()
		from dsj.cc_record_main  m
		LEFT JOIN dsj.policy_information i ON m.contnos = i.contno
	WHERE m.contnos IS NOT NULL  GROUP BY m.contnos,m.starttime;
	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;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_bq() 
IS
DECLARE
	p_count int
BEGIN
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time 
)select 
	   		reflect("java.util.UUID", "randomUUID"),
	   		e.contno,
	   		max(i.customerno),
	   		max(i.name),
	   		'投保人' AS tbr,
	   		'保全' AS bq,
	   		e.edorappdate,
	   		CASE max(e.edortype)
			WHEN 'AE' THEN '投保人变更'
			WHEN 'AM' THEN '客户联系方式变更'
			WHEN 'AP' THEN '自垫选择权变更'
			WHEN 'BB' THEN '客户基本资料变更'
			WHEN 'BC' THEN '受益人及受益人资料变更'
			WHEN 'BM' THEN '红利领取方式变更'
			WHEN 'BS' THEN '签名变更'
			WHEN 'CM' THEN '客户重要资料变更(客户层)'
			WHEN 'CT' THEN '退保'
			WHEN 'DB' THEN '红利领取'
			WHEN 'EN' THEN '续保方式变更'
			WHEN 'FM' THEN '交费期间变更'
			WHEN 'GC' THEN '生存金转账领取授权申请/取消'
			WHEN 'GM' THEN '领取方式变更'
			WHEN 'GT' THEN '保险公司解除合同'
			WHEN 'HI' THEN '补充告知'
			WHEN 'IC' THEN '客户重要资料变更(保单层)'
			WHEN 'IO' THEN '职业类别变更'
			WHEN 'LG' THEN '生存给付'
			WHEN 'LN' THEN '保单借款'
			WHEN 'LR' THEN '保单补发'
			WHEN 'NS' THEN '新增附加险'
			WHEN 'OP' THEN '万能险部分领取'
			WHEN 'PC' THEN '交费方式及交费账号变更'
			WHEN 'PL' THEN '保单挂失与挂失解除'
			WHEN 'PM' THEN '交费间隔变更'
			WHEN 'PR' THEN '保单迁移'
			WHEN 'PT' THEN '减保'
			WHEN 'PU' THEN '减额交清'
			WHEN 'RB' THEN '保全回退'
			WHEN 'RE' THEN '保单复效'
			WHEN 'RF' THEN '贷款清偿'
			WHEN 'SC' THEN '特别约定变更'
			WHEN 'TR' THEN '保费自垫清偿'
			WHEN 'WT' THEN '犹豫期退保'
			WHEN 'XS' THEN '协议减保'
			WHEN 'XT' THEN '协议退保'
			END AS edortype,
	   		'admin',
			sysdate()
	   from dsj.edorinfo e
	   LEFT JOIN dsj.policy_information i ON e.contno = i.contno
	WHERE e.contno IS NOT NULL
	GROUP BY e.contno,e.edorappdate;
	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

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_hf() 
IS
DECLARE
	p_count int
BEGIN
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time 
	) SELECT 
				reflect("java.util.UUID", "randomUUID"),
		   		cad.productno,
		   		cac.customerno,
		   		max(cac.customername),
		   		'投保人',
		   		'回访',
		   		cad.inserttime,
		   		CASE max(cad.actiondefguid) 
		   		when '402837815c1a4fc6015c1a735351122d' then '特殊回访'
				when '402837815c1a4fc6015c1a735350012a' then '其他回访'
				when '402837815c2ff6b5015c3005e7fb0004' then '失效回访'
				when '402837815c2ff6b5015c3005e7fb2222' then '回执超期回访'
				when '402837815c2ff6b5015c3005e7fb0003' then '宽限期50天'
				when '402837815c2ff6b5015c3005e7fb0002' then '宽限期30天'
				when '402837815c1a4fc6015c1a735350012f' then '新契约回访'
				when '402837815c2ff6b5015c3005e7fb0001' then '续期回访'
				end ,
		   		'admin',
				sysdate()
		   	FROM dsj.CC_ACTION_DATA cad,dsj.cc_action_customerinfo cac
			WHERE cac.unioncustomerid = cad.unioncustomerid AND cad.inserttime IS NOT null GROUP BY cad.productno,cac.customerno,cad.inserttime;

	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;

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_ts() 
IS
DECLARE
	p_count int
BEGIN
	
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time 
	) SELECT 
		reflect("java.util.UUID", "randomUUID"),
		max(ac.productnos),
		max(i.customerno),
		max(ac.complaintsname),
		'投保人'||CASE max(ac.complaintsrelation) 
		WHEN '201' THEN ''
		WHEN '202' THEN '子女'
		WHEN '203' THEN '配偶'
		WHEN '204' THEN '父母'
		WHEN '205' THEN '其他关系'
	END AS complaintsrelation,
		'投诉',
		ac.inserttime ,
	CASE max(ac.resultclassification) 
		WHEN '101' THEN '销售纠纷'
		WHEN '102' THEN '理赔纠纷'
		WHEN '103' THEN '退保纠纷'
		WHEN '104' THEN '承保纠纷'
		WHEN '105' THEN '续收续保纠纷'
		WHEN '106' THEN '保全纠纷'
		WHEN '107' THEN '其他'
	END as resultclassification,
	'admin',
	sysdate
FROM dsj.cc_action_complaints ac
LEFT JOIN dsj.policy_information i ON ac.productnos = i.contno
	WHERE ac.productnos IS NOT NULL AND i.customerno IS NOT NULL
	GROUP BY ac.inserttime ;
	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;

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_tuibao() 
IS
DECLARE
	p_count int
BEGIN
	
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time 
	) SELECT 
		reflect("java.util.UUID", "randomUUID"),
		trim(t.contno) as contno,
		trim(t.customerno) as customerno,
		trim(t.name) as name,
		trim(t.tbr) as tbr,
		trim(t.tb) as tb,
		trim(t.edorappdate) as edorappdate,
		t.edorreason,
		'admin',
		sysdate()
		FROM (
			SELECT 
				row_number() over(partition by i.contno) rn,
				lm.contno,
				i.customerno,
				i.name,
				'投保人' AS tbr,
				'退保' AS tb,
				to_char(lm.edorappdate,"yyyy-MM-dd") edorappdate,
				CASE lm.edorreasoncode 
					WHEN '01' THEN '死亡' 
					WHEN '02' THEN '失踪' 
					WHEN '03' THEN '离异' 
					WHEN '04' THEN '债权转移'
					WHEN '05' THEN '被保险人成年'
					WHEN '06' THEN '其它'
					ELSE '其它'
				END AS 	edorreason
			FROM dsj.lpedoritem lm,dsj.policy_information i 
			where lm.contno = i.contno AND lm.edortype='CT' AND lm.edorstate = 0 
			) t WHERE t.rn = 1;
	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;

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_xq() -- 创建主存储过程
IS    
BEGIN
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		created_by ,
		created_time 
	) select 
   		reflect("java.util.UUID", "randomUUID"),
   		lp.contno,
   		lp.appntno,
   		 '',
   		'投保人',
   		'续期',
   		to_char(lp.paydate,'yyyy-MM-dd'),
   		'admin',
   		sysdate
	FROM dsj.ljapayperson lp WHERE lp.paycount > 1;
	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;

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_main() 
IS
BEGIN
	DELETE FROM shanglifeecif.partytimeline;
	shanglifeecif.partytimeline_tb();
	shanglifeecif.partytimeline_lp();
	shanglifeecif.partytimeline_zx();
	shanglifeecif.partytimeline_bq();
END;