ShangHaiLifeECIF/sql-achievement/存储过程/洛阳测试环境使用sql整理/保单信息存储过程.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement() -- 创建主存储过程
IS    
BEGIN
	--根据policy_information 更新保单表
	shanglifeecif.init_insurancearrangement_policy_information();
	--更新险种大类
	shanglifeecif.update_risk_categories();
END;



/

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,--保费
  Risk,--保额
  NPDate,--下次缴费日期
  PADate,--投保日期
  pisdate,--签单日期
  AgentOrg,--代理机构
  schannel,--销售渠道
   POService,--保全标志
  PWComp,--承保分公司
  agrmntage ,--保险期限
  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,--保费
	AMNT,--保额
	trim(PAYTODATE) as PAYTODATE,--下次缴费日期
	trim(CUSTOMGETPOLDATE) as CUSTOMGETPOLDATE,--投保日期
	trim(SIGNDATE) as SIGNDATE,--签单日期
	trim(AGENTCOM) as AGENTCOM,--代理机构
	trim(SALECHNL) as SALECHNL,--销售渠道
	trim(PRESERVATIONFLAG) as PRESERVATIONFLAG,--保全标志
	"上海人寿上海分公司",
	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,
	'admin',
	sysdate() 
	FROM shanghailifeecif.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 shanghailifeecif.INSURANCEINFO b
		where b.contno  = a.policyno
	) WHERE 1=1 ;

END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.update_risk_categories() -- 创建主存储过程
IS    
BEGIN
--更新险种大类
	UPDATE shanglifeecif.insurancearrangement a SET (
		risk_categories,
		risk_categories_name
	) = (
		
		select
				b.kindcode,
				(CASE b.kindcode 
					WHEN 'A' THEN '意外伤害险' 
					WHEN 'U' THEN '万能保险' 
					WHEN 'R' THEN '年金保险' 
					WHEN 'S' THEN '重疾保险' 
					WHEN 'L' THEN '人寿保险' 
					WHEN 'H' THEN '健康险' 
				END) AS kindname
			from lmriskapp b WHERE a.productid = b.riskcode
	
	) WHERE 1=1;

END;