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

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