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;
			dbms_output.put_line('partytimeline_tuibao函数跑批完成！');
	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;