CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement_policy_information() -- 创建主存储过程
IS    
BEGIN

    INSERT INTO shanglifeecif.insurancearrangement(
  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', 
  productname,--险种名称
  policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM',  
  payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
  policystate,--保单状态
  prem,--保费
  sumprem, --累计保费，
  Risk,--保额
  NPDate,--下次缴费日期
  PADate,--投保日期
  pisdate,--签单日期
  AgentOrg,--代理机构
  schannel,--销售渠道
  schannelname,--销售渠道中文
  salecomname,--业绩归属中文
  POService,--保全标志
  PWComp,--承保分公司
  security ,--保险期原始值
  agrmntage ,--保险期限
  salesperson,--业务员标识代码
  SPName,--业务员名称
  renewalDate,--续保日期
  created_by ,-- '创建人', 
  created_time -- '创建时间', 

) 
SELECT 
	row_number()over(),
	trim(CONTNO) as CONTNO,--policyno ,-- '保险单号 INSURANCEINFO.CONTNO', 
	trim(CVALIDATE) as CVALIDATE,--pindate ,-- '起保日期 POLICY_INFORMATION.CVALIDATE', 
	trim(ENDDATE) as ENDDATE,--pmdate ,-- '终保日期 POLICY_INFORMATION.ENDDATE', 
	PAYCOUNT,--norenewal ,-- '续保次数 POLICY_INFORMATION.PAYCOUNT', 
	trim(PAYINTV) as PAYINTV,--payment ,-- '缴费方式 POLICY_INFORMATION.PAYINTV', 
	trim(CUSTOMERNO) as CUSTOMERNO,--applicantid ,-- '投保人 根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号，再找到individualid', 
	trim(NAME) as NAME,--appname ,-- '投保人名称 POLICY_INFORMATION.NAME', 
	trim(IDNO) as IDNO,--appcertid ,-- '投保人证件号码 POLICY_INFORMATION.IDNO', 
	trim(insuredno) as insuredno,--insuredid ,-- '主被保险人 insuredno', 
	trim(INSUREDNAME) as INSUREDNAME,--insname ,-- '主被保险人名称 POLICY_INFORMATION.INSUREDNAME', 
	trim(INSUREDIDNO) as INSUREDIDNO,--inscertid ,-- '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO', 
	trim(RISKCODE) as RISKCODE,--productid ,-- '险种代码 POLICY_INFORMATION.RISKCODE', 
	trim(RISKNAME) as RISKNAME, --productname 险种名称
	trim(SALECOM) as SALECOM,--policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM', 
	PAYENDYEAR,--payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
	trim(APPFLAG) as APPFLAG, --保单状态
	prem,--保费
	sumprem,--累计保费
	AMNT,--保额
	trim(PAYTODATE) as PAYTODATE,--下次缴费日期
	trim(polapplydate) as polapplydate,--投保日期
	trim(SIGNDATE) as SIGNDATE,--签单日期
	trim(AGENTCOM) as AGENTCOM,--代理机构
	trim(SALECHNL) as SALECHNL,--销售渠道
	trim(SALECHNLNAME) as SALECHNLNAME,--销售渠道中文
	trim(salecomname) as salecomname,--业绩归属中文
	trim(PRESERVATIONFLAG) as PRESERVATIONFLAG,--保全标志
	"上海人寿上海分公司",
	trim(security) as security, --保险期原始值
	case
		when security ='终身' then 42720
		when security = '至100周岁' then 36500
		when security = '至80周岁' then 29200
		when security = '70年' then 25550
		when security = '至70周岁' then 25550
		when security = '至65周岁' then 23725
		when security = '至60周岁' then 21900
		when security = '30年' then 10950
		when security = '20年' then 7300
		when security = '10年' then 3650
		when security = '6年' then 2190
		when security = '5年' then 1825
		when security = '1年' then 365
		when security = '180天' then 180
		when security = '6月' then 180
		when security = '90天' then 90
		when security = '3月' then 90
		when security = '1月' then 30
		when security = '30天' then 30
		when security = '15天' then 15
		when security = '7天' then 7
	end,
	trim(AGENTCODE) as AGENTCODE,--代理人代码
	trim(AGENTNAME) as AGENTNAME,--代理人名称
	trim(paytodate) as paytodate,--续保日期
	'admin',
	sysdate() 
	FROM dsj.policy_information; 

	--更新INSURANCEINFO 表中的信息

	UPDATE shanglifeecif.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
			trim(b.PRTNO) as PRTNO,--pano ,-- '投保单号 INSURANCEINFO.PRTNO',  
			--SIGNDATE,--pisdate ,-- '签单日期 INSURANCEINFO.SIGNDATE', 
			--POLAPPLYDATE,--padate ,-- '投保日期 INSURANCEINFO.POLAPPLYDATE', 
			--appflag,--policystate ,--INSURANCEINFO,appflag
			--PREM,--prem ,-- '总保费 INSURANCEINFO.PREM', 
			--RISKNAME,--productname ,-- '险种名称	 INSURANCEINFO.RISKNAME'
			trim(b.SELLTYPE) as SELLTYPE--agentchannel ,-- '代理渠道 INSURANCEINFO.SELLTYPE', 
		from dsj.INSURANCEINFO b
		where b.contno  = a.policyno
	) WHERE 1=1 ;
	dbms_output.put_line('init_insurancearrangement_policy_information函数跑批完成！');
	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;