ShangHaiLifeECIF/sql-achievement/存储过程/保单处理增量数据koucx.sql

--处理增量的保单信息
CREATE OR REPLACE PROCEDURE shanglifeecif.incrementInsuranceArrangement2() IS
DECLARE
sianum INT;
BEGIN
	
	SELECT COUNT(*) INTO sianum FROM  shanglifeecif.InsuranceArrangement; 
	MERGE INTO shanglifeecif.InsuranceArrangement sia using 
		(SELECT * FROM shanghailifeecif.policy_information) si ON sia.policyno = si.CONTNO AND sia.ProductID = si.RISKCODE
	WHEN MATCHED THEN UPDATE SET sia.pindate = si.CVALIDATE, sia.pmdate = si.ENDDATE,sia.norenewal = si.PAYCOUNT,
	sia.payment = si.PAYINTV,sia.applicantid = si.individual,
	sia.appname = si.NAME,sia.appcertid = si.IDNO,sia.insuredid= si.insuredno,sia.insname = si.INSUREDNAME,sia.inscertid = si.INSUREDIDNO,
	sia.productid= si.RISKCODE,sia.policybelong = si.SALECOM,sia.payendyear = si.PAYENDYEAR
	WHEN NOT MATCHED THEN 
	INSERT ( iaid ,--'保单ID', 
  policyno ,-- '保险单号 INSURANCEINFO.CONTNO', 
  pindate ,-- '起保日期 POLICY_INFORMATION.CVALIDATE', 
  pmdate ,-- '终保日期 POLICY_INFORMATION.ENDDATE', 
  norenewal ,-- '续保次数 POLICY_INFORMATION.PAYCOUNT', 
  payment ,-- '缴费方式 POLICY_INFORMATION.PAYINTV', 
  applicantscustid ,-- '投保人 上游客户号，再找到individualid', 
  appname ,-- '投保人名称 POLICY_INFORMATION.NAME', 
  appcertid ,-- '投保人证件号码 POLICY_INFORMATION.IDNO', 
  insuredscustid ,-- '主被保险人 游客户号', 
  insname ,-- '主被保险人名称 POLICY_INFORMATION.INSUREDNAME', 
  inscertid ,-- '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO', 
  productid ,-- '险种代码 POLICY_INFORMATION.RISKCODE', 
  policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM',  
  payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
  created_by ,-- '创建人', 
  created_time -- '创建时间', 
  ) 
   	values(row_number()over()+sianum,
	CONTNO,--policyno ,-- '保险单号 INSURANCEINFO.CONTNO', 
	CVALIDATE,--pindate ,-- '起保日期 POLICY_INFORMATION.CVALIDATE', 
	ENDDATE,--pmdate ,-- '终保日期 POLICY_INFORMATION.ENDDATE', 
	PAYCOUNT,--norenewal ,-- '续保次数 POLICY_INFORMATION.PAYCOUNT', 
	PAYINTV,--payment ,-- '缴费方式 POLICY_INFORMATION.PAYINTV', 
	individual,--applicantid ,-- '投保人 根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号，再找到individualid', 
	NAME,--appname ,-- '投保人名称 POLICY_INFORMATION.NAME', 
	IDNO,--appcertid ,-- '投保人证件号码 POLICY_INFORMATION.IDNO', 
	insuredno,--insuredid ,-- '主被保险人 insuredno', 
	INSUREDNAME,--insname ,-- '主被保险人名称 POLICY_INFORMATION.INSUREDNAME', 
	INSUREDIDNO,--inscertid ,-- '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO', 
	RISKCODE,--productid ,-- '险种代码 POLICY_INFORMATION.RISKCODE', 
	SALECOM,--policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM', 
	PAYENDYEAR,--payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
	'koucx',
	sysdate() );
		
		
	UPDATE insurancearrangement a SET (
		applicantid --'投保人ID',
	) = (
		select 
			indid ,--'投保人ID',
		from individual b
		where b.scustid  = a.applicantscustid
	) WHERE 1=1 ;

	UPDATE insurancearrangement a SET (
		insuredid --'主保险人ID',
	) = (
		select 
			indid ,--'投保人ID',
		from individual b
		where b.scustid  = a.insuredscustid
	) WHERE 1=1 ;

	--更新INSURANCEINFO 表中的信息

	UPDATE insurancearrangement a SET (
		pano ,-- '投保单号 INSURANCEINFO.PRTNO',  
		pisdate ,-- '签单日期 INSURANCEINFO.SIGNDATE', 
		padate ,-- '投保日期 INSURANCEINFO.POLAPPLYDATE', 
		policystate ,--INSURANCEINFO,appflag
		prem ,-- '总保费 INSURANCEINFO.PREM', 
		productname ,-- '险种名称	 INSURANCEINFO.RISKNAME'
		agentchannel ,-- '代理渠道 INSURANCEINFO.SELLTYPE', 
	) = (
		select
			PRTNO,--pano ,-- '投保单号 INSURANCEINFO.PRTNO',  
			SIGNDATE,--pisdate ,-- '签单日期 INSURANCEINFO.SIGNDATE', 
			POLAPPLYDATE,--padate ,-- '投保日期 INSURANCEINFO.POLAPPLYDATE', 
			appflag,--policystate ,--INSURANCEINFO,appflag
			PREM,--prem ,-- '总保费 INSURANCEINFO.PREM', 
			RISKNAME,--productname ,-- '险种名称	 INSURANCEINFO.RISKNAME'
			SELLTYPE--agentchannel ,-- '代理渠道 INSURANCEINFO.SELLTYPE', 
		from shanghailifeecif.INSURANCEINFO b
		where b.contno  = a.policyno
	) WHERE 1=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	;