dgtis-modules-data/一期存储过程（最终版）/partytimeline_hf.sql

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.lastdealtime,
		   		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.lastdealtime IS NOT null GROUP BY cad.productno,cac.customerno,cad.lastdealtime;
   dbms_output.put_line('partytimeline_hf函数跑批完成！');
	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;