ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/参与方事件.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_tb() -- 创建主存储过程
IS    
BEGIN
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PCertID,
		PRole,
		Scenario,
		SDate,
		enddate,
		created_by ,
		created_time 
	) select 
   		row_number()over(),
   		trim(contno) as contno,
   		trim(max(customerno)) as customerno,
   		trim(max(name)) as name,
   		trim(max(idno)) as idno,
   		'投保人',
   		'投保',
   		trim(max(polapplydate)) as polapplydate,
		trim(max(enddate)) as enddate,
		'admin',
		sysdate()
	from policy_information WHERE contno IS NOT NULL GROUP BY contno;
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());	
	
END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_lp() 
IS
DECLARE
	p_count int
BEGIN
	
	SELECT count(*) INTO p_count FROM shanglifeecif.partytimeline;
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PCertID,
		PRole,
		Scenario,
		SDate,
		describe,
		created_by ,
		created_time 
	) select 
   		row_number()over()+p_count,
   		trim(contno) as contno,
   		trim(INSUREDNO) as INSUREDNO,
   		trim(INSUREDNAME) as INSUREDNAME,
   		trim(idno) as idno,
   		'投保人',
   		'理赔',
   		trim(RPTDATE) as RPTDATE,
   		trim(ACCIDENTTYPE) as ACCIDENTTYPE,
   		'admin',
		sysdate()
	from insurance_claim WHERE contno IS NOT NULL;
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());	
END;
/

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_zx() 
IS
DECLARE
	p_count int
BEGIN
	
	SELECT count(*) INTO p_count FROM shanglifeecif.partytimeline;
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time 
	) SELECT 
		row_number() over()+p_count,
		trim(t.contnos) as contnos,
		trim(t.customerno) as customerno,
		trim(t.econtactsName) as econtactsName,
		trim(t.tbr) as tbr,
		trim(t.zx) as zx,
		trim(t.starttime) as starttime,
		trim(t.reasonsecondname) as reasonsecondname,
		'admin',
		sysdate()
		FROM (
			select 
		   		row_number() over(partition by i.contno) rn,
		   		m.contnos,
		   		i.customerno,
		   		m.econtactsName,
		   		'投保人' AS tbr,
		   		'咨询' AS zx,
		   		m.starttime,
		   		m.reasonsecondname
		from cc_record_main  m
		LEFT JOIN policy_information i ON m.contnos = i.contno
	WHERE m.contnos IS NOT NULL
	) t WHERE t.rn = 1;
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());	
END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_bq() 
IS
DECLARE
	p_count int
BEGIN
	
	
	insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		created_by ,
		created_time 
)SELECT 
	row_number() over(),
		t.contno,
		t.customerno,
		t.name,
		t.tbr,
		t.bq,
		t.edorappdate,
		'admin',
		sysdate()
		FROM (
			
				select 
				   		row_number() over(partition by e.contno) rn,
				   		e.contno,
				   		i.customerno,
				   		i.name,
				   		'投保人' AS tbr,
				   		'保全' AS bq,
				   		e.edorappdate
				from edorinfo e
				LEFT JOIN policy_information i ON e.contno = i.contno
			WHERE e.contno IS NOT NULL
		
		) t WHERE t.rn = 1;
END
CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_main() 
IS
BEGIN
	DELETE FROM shanglifeecif.partytimeline;
	shanglifeecif.partytimeline_tb();
	shanglifeecif.partytimeline_lp();
	shanglifeecif.partytimeline_zx();
	shanglifeecif.partytimeline_bq();
END;