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"),
		i.contno,
		i.customerno,
		i.name,
		'投保人' AS tbr,
   		'保全' AS bq,
		l.edorappdate,
		lm.edorname,
		'admin',
		sysdate()
FROM dsj.lpedoritem l,dsj.lmedoritem lm,dsj.policy_information i 
WHERE l.edortype = lm.edorcode AND lm.appobj <> 'G' AND l.edorstate = '0' AND i.contno = l.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_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.confdate,'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();
	shanglifeecif.partytimeline_tuibao();
	shanglifeecif.partytimeline_ts();
	shanglifeecif.partytimeline_hf();
	shanglifeecif.partytimeline_xq();
END;