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

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,
		max(i.name),
		'投保人' AS tbr,
   		'保全' AS bq,
		l.edorappdate,
		max(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
GROUP BY i.customerno,i.contno,l.edorappdate,l.edortype;
dbms_output.put_line('partytimeline_bq函数跑批完成！');
	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