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

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

	DELETE FROM shanglifeecif.insurancearrangement;
	--根据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,--保费
  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 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 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;
/

CREATE OR REPLACE PROCEDURE shanglifeecif.update_risk_categories() -- 创建主存储过程
IS    
BEGIN
	--更新险种大类
	UPDATE shanglifeecif.insurancearrangement a SET (
		risk_categories_name
	) = (
		select
				kindtype
		from riskkind b WHERE a.productid = b.riskcode
	) 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;
/


万能保险总保额，总客户数，拥有3-5保单数量的客户数
/*
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;
	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;




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;

/
BEGIN
	shanglifeecif.init_insurancearrangement();
end 

/*
UPDATE shanglifeecif.insurancearrangement a SET (
		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--承保分公司 
	) = (
		select
			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,--下次缴费日期
			polapplydate,--投保日期
			AGENTCOM,--代理机构
			SALECHNL,--销售渠道
			"上海人寿上海分公司"
		from shanghailifeecif.policy_information b
		where b.contno  = a.policyno
	) WHERE 1=1 ;
*/