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

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', 
  policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM',  
  payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
  
  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', 
	SALECOM,--policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM', 
	PAYENDYEAR,--payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
	
	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 policy_information 
	--更新险种大类
	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;
	
	--更新投保人和被保人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 INSURANCEINFO b
		where b.contno  = a.policyno
	) WHERE 1=1 ;

END;


/
DROP TABLE IF EXISTS shanglifeecif.insurancearrangement_health_tmp;
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 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 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 AUDIT_EDORLIST b
	   where b.contno  = a.policyno
	) WHERE 1=1 ;


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

END;
/
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;
/
BEGIN
	shanglifeecif.init_insurancearrangement();
end