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