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

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

	--根据health_insurance_listing 更新相关字段
	shanglifeecif.up_insurancearrangement_health_insurance_listing();

	--其他标的字段 更新
	shanglifeecif.up_insurancearrangement_other();
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,--投保日期
  AgentOrg,--代理机构
  schannel,--销售渠道
  PWComp,--承保分公司
  agrmntage ,--保险期限
  created_by ,-- '创建人', 
  created_time -- '创建时间', 

) 
SELECT 
	row_number()over(),
	CONTNO,--policyno ,-- '保险单号 INSURANCEINFO.CONTNO', 
	CVALIDATE,--pindate ,-- '起保日期 POLICY_INFORMATION.CVALIDATE', 
	ENDDATE,--pmdate ,-- '终保日期 POLICY_INFORMATION.ENDDATE', 
	PAYCOUNT,--norenewal ,-- '续保次数 POLICY_INFORMATION.PAYCOUNT', 
	PAYINTV,--payment ,-- '缴费方式 POLICY_INFORMATION.PAYINTV', 
	CUSTOMERNO,--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', 
	RISKNAME, --productname 险种名称
	SALECOM,--policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM', 
	PAYENDYEAR,--payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
	APPFLAG, --保单状态
	prem,--保费
	AMNT,--保额
	PAYTODATE,--下次缴费日期
	CUSTOMGETPOLDATE,--投保日期
	AGENTCOM,--代理机构
	SALECHNL,--销售渠道
	"上海人寿上海分公司",
	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 
	
	--更新投保人和被保人id  因为 scustid有重复所以需要去下重

	UPDATE shanglifeecif.insurancearrangement a SET (
		applicantid --'投保人ID',
	) = (
	   select
	   	c.indid
	   from (
		   	select
				row_number()over(PARTITION BY b.scustid) rn,
				b.indid,
				b.scustid
			from shanglifeecif.individual b
	   ) c
	   WHERE c.scustid  = a.applicantscustid and c.rn=1
	) WHERE 1=1 ;


	UPDATE shanglifeecif.insurancearrangement a SET (
		insuredid --'投保人ID',
	) = (
	   select
	   	c.indid
	   from (
		   	select
				row_number()over(PARTITION BY b.scustid) rn,
				b.indid,
				b.scustid
			from shanglifeecif.individual b
	   ) c
	   WHERE c.scustid  = a.insuredscustid and c.rn=1
	) WHERE 1=1 ;

	--更新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
			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 ;

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;

--临时表

CREATE TABLE shanglifeecif.insurancearrangement_health_tmp(
    id string,
    orderid string  DEFAULT NULL COMMENT '用于分组的排序号',
    contno string DEFAULT NULL COMMENT '保单号',
  	AGENTGROUP string DEFAULT NULL COMMENT '承保分公司',--pwcomp ,-- '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
	SALECHANNELS string DEFAULT NULL COMMENT '销售渠道',--schannel ,-- '销售渠道 HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
	AMNT string DEFAULT NULL COMMENT '总保额',--risk ,-- '总保额 HEALTH_INSURANCE_LISTING.AMNT(测试环境中，此表保单数据量较其他表如：POLICY_INFORMATION，INSURANCEINFO 缺少至少一个数据量级)', 
	AGENTCODE  string DEFAULT NULL COMMENT'业务员名称'--spname ,-- '业务员名称 HEALTH_INSURANCE_LISTING.AGENTCODE', 
) COMMENT '保单处理个险承保清单自助分析 数据临时表 '
STORED AS ES
with shard number 10
replication 1;


CREATE OR REPLACE PROCEDURE shanglifeecif.up_insurancearrangement_health_insurance_listing() -- 创建主存储过程
IS    
BEGIN
	--清空临时表数据
	delete from shanglifeecif.insurancearrangement_health_tmp;

	--初始化临时表
	insert into shanglifeecif.insurancearrangement_health_tmp (
		id,
		orderid,
		contno,
		agentgroup,
		SALECHANNELS,
		AMNT,
		AGENTCODE
	) select 
		row_number()over(),
		row_number()over(PARTITION BY contno),
		contno,
		agentgroup,
		SALECHANNELS,
		AMNT,
		AGENTCODE
	from shanghailifeecif.HEALTH_INSURANCE_LISTING

	--根据临时表更新insurancearrangement 表
	UPDATE shanglifeecif.insurancearrangement a SET (
		pwcomp ,-- '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
		schannel ,-- '销售渠道 HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
		risk ,-- '总保额 HEALTH_INSURANCE_LISTING.AMNT(测试环境中，此表保单数据量较其他表如：POLICY_INFORMATION，INSURANCEINFO 缺少至少一个数据量级)', 
		spname -- '业务员名称 HEALTH_INSURANCE_LISTING.AGENTCODE', 
	) = (
		select
			b.AGENTGROUP,--pwcomp ,-- '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
			b.SALECHANNELS,--schannel ,-- '销售渠道 HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
			b.AMNT,--risk ,-- '总保额 HEALTH_INSURANCE_LISTING.AMNT(测试环境中，此表保单数据量较其他表如：POLICY_INFORMATION，INSURANCEINFO 缺少至少一个数据量级)', 
			b.AGENTCODE--spname ,-- '业务员名称 HEALTH_INSURANCE_LISTING.AGENTCODE', 
		from shanglifeecif.insurancearrangement_health_tmp b
		where b.contno  = a.policyno
		and b.orderid = 1
	) WHERE 1=1 ;


END;

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


	UPDATE shanglifeecif.insurancearrangement a SET (
		policytype  -- '保单类型', 
	) = (
	   select
	   	c.CONTTYPE
	   from (
		   	select
				row_number()over(PARTITION BY b.contno) rn,
				b.CONTTYPE,
				b.contno
			from shanghailifeecif.PERSONAL_INSURANCE b
	   ) c
	   WHERE c.contno  = a.policyno and c.rn=1
	) WHERE 1=1 ;

	UPDATE shanglifeecif.insurancearrangement a SET (
		poservice	  -- '是否保全', 
	) = (
	   SELECT
	   	IF(count(b.CONTNO) >0,1,0)
	   FROM shanghailifeecif.AUDIT_EDORLIST b
	   where b.contno  = a.policyno
	) WHERE 1=1 ;


	UPDATE shanglifeecif.insurancearrangement a SET (
		soinsured-- '被保人数', 
	) = (
	   SELECT
	   		PEOPLES3
	   FROM shanghailifeecif.HEALTH_GROUP_LISTING b
	   where b.contno  = a.policyno
	) WHERE 1=1 ;

END;