ShangHaiLifeECIF/sql-achievement/存储过程/保单qxp.sql

--数据源 policy_information 表结构	

-- CREATE  TABLE shanghailifeecif.policy_information(
--   contno varchar2(200) DEFAULT NULL COMMENT '保单号',  -- dialect: ORACLE
--   riskcode varchar2(200) DEFAULT NULL COMMENT '险种代码',  -- dialect: ORACLE
--   riskname varchar2(200) DEFAULT NULL COMMENT '险种名称',  -- dialect: ORACLE
--   riskperiod varchar2(200) DEFAULT NULL COMMENT '险种分类',  -- dialect: ORACLE
--   risktype varchar2(200) DEFAULT NULL COMMENT '险种设计类型',  -- dialect: ORACLE
--   payintv varchar2(200) DEFAULT NULL COMMENT '缴费方式',  -- dialect: ORACLE
--   security varchar2(200) DEFAULT NULL COMMENT '保障年期',  -- dialect: ORACLE
--   payendyear decimal(10,2) DEFAULT NULL COMMENT '缴费年期', 
--   payendyearflag varchar2(200) DEFAULT NULL COMMENT '缴费年期单位',  -- dialect: ORACLE
--   insuyear decimal(10,2) DEFAULT NULL COMMENT '保险年期', 
--   insuyearflag varchar2(200) DEFAULT NULL COMMENT '保险年期单位',  -- dialect: ORACLE
--   paycount decimal(10,2) DEFAULT NULL COMMENT '续保次数', 
--   signdate string DEFAULT NULL COMMENT '承保日期', 
--   cvalidate string DEFAULT NULL COMMENT '保单生效日期', 
--   enddate string DEFAULT NULL COMMENT '保单终止日期', 
--   modifydate string DEFAULT NULL COMMENT '修改日期', 
--   prem decimal(10,2) DEFAULT NULL COMMENT '保费', 
--   appflag varchar2(200) DEFAULT NULL COMMENT '保单状态',  -- dialect: ORACLE
--   customerno varchar2(200) DEFAULT NULL COMMENT '客户号',  -- dialect: ORACLE
--   name varchar2(200) DEFAULT NULL COMMENT '客户姓名',  -- dialect: ORACLE
--   sex varchar2(200) DEFAULT NULL COMMENT '客户性别',  -- dialect: ORACLE
--   birthday string DEFAULT NULL COMMENT '客户出生日期', 
--   idtype varchar2(200) DEFAULT NULL COMMENT '证件类型',  -- dialect: ORACLE
--   idno varchar2(200) DEFAULT NULL COMMENT '证件号码',  -- dialect: ORACLE
--   insuredno varchar2(200) DEFAULT NULL COMMENT '被保人号',  -- dialect: ORACLE
--   insuredname varchar2(200) DEFAULT NULL COMMENT '被保人姓名',  -- dialect: ORACLE
--   insuredsex varchar2(200) DEFAULT NULL COMMENT '被保人性别',  -- dialect: ORACLE
--   insuredbirthday string DEFAULT NULL COMMENT '被保人出生日期', 
--   insuredidtype varchar2(200) DEFAULT NULL COMMENT '被保人证件类型',  -- dialect: ORACLE
--   insuredidno varchar2(200) DEFAULT NULL COMMENT '被保人证件号码',  -- dialect: ORACLE
--   relationtoappnt varchar2(200) DEFAULT NULL COMMENT '与投保人关系',  -- dialect: ORACLE
--   salechnl varchar2(200) DEFAULT NULL COMMENT '销售渠道',  -- dialect: ORACLE
--   salecom varchar2(200) DEFAULT NULL COMMENT '业绩归属',  -- dialect: ORACLE
--   salechannels varchar2(200) DEFAULT NULL COMMENT '二级业绩归属',  -- dialect: ORACLE
--   customgetpoldate string DEFAULT NULL COMMENT '客户投保日期', 
--   prem_js decimal(10,2) DEFAULT NULL COMMENT ''
-- )




======================================================================================================================================================
--创建保单表insurancearrangement
DROP TABLE IF EXISTS insurancearrangement;
CREATE  TABLE insurancearrangement(
  iaid string NOT NULL COMMENT '保单ID', 
  policyno string DEFAULT NULL COMMENT '保险单号 INSURANCEINFO.CONTNO', 
  pano string DEFAULT NULL COMMENT '投保单号 INSURANCEINFO.PRTNO', 
  agrmntage string DEFAULT NULL COMMENT '保险期限', 
  pbinst string DEFAULT NULL COMMENT '受益分配方式', 
  pwcomp string DEFAULT NULL COMMENT '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
  pindate date DEFAULT NULL COMMENT '起保日期 POLICY_INFORMATION.CVALIDATE', 
  pmdate date DEFAULT NULL COMMENT '终保日期 POLICY_INFORMATION.ENDDATE', 
  pisdate date DEFAULT NULL COMMENT '签单日期 INSURANCEINFO.SIGNDATE', 
  padate date DEFAULT NULL COMMENT '投保日期 INSURANCEINFO.POLAPPLYDATE', 
  renewaldate date DEFAULT NULL COMMENT '续保日期', 
  norenewal int DEFAULT NULL COMMENT '续保次数 POLICY_INFORMATION.PAYCOUNT', 
  policytype string DEFAULT NULL COMMENT '保单类型 PERSONAL_INSURANCE.CONTTYPE,需上游给出类型枚举值', 
  schannel string DEFAULT NULL COMMENT '销售渠道 HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
  bsource string DEFAULT NULL COMMENT '业务来源', 
  policystate string DEFAULT NULL COMMENT '保单状态 保单包括投保单的所处的状态，例如：录入、生效、批单等 INSURANCEINFO.APPFLAG code 0 1 2 4 9 B F',
  topay string DEFAULT NULL COMMENT '缴费类型', 
  payment string DEFAULT NULL COMMENT '缴费方式 POLICY_INFORMATION.PAYINTV', 
  risk double DEFAULT NULL COMMENT '总保额 HEALTH_INSURANCE_LISTING.AMNT(测试环境中，此表保单数据量较其他表如：POLICY_INFORMATION，INSURANCEINFO 缺少至少一个数据量级)', 
  prem double DEFAULT NULL COMMENT '总保费 INSURANCEINFO.PREM', 
  currency string DEFAULT NULL COMMENT '币种', 
  npdate date DEFAULT NULL COMMENT '下次缴费日期', 
  soinsured int DEFAULT NULL COMMENT '被保人数 HEALTH_GROUP_LISTING.PEOPLES3,团单', 
  bsinsured double DEFAULT NULL COMMENT '基本保额 保险合同条款费率表中载明的单位保额。比如中国人寿的康宁终身保险就是这样，如果购买的基本保险金额是10万的话，那么大病的保险金额就是基本保险金额的2倍即20万；', 
  insurvalue double DEFAULT NULL COMMENT '保单价值 保单价值，即保单现金价值。是指带有储蓄性质的人身bai保险单所具有的价值。保险人为履行合同责任通常提存责任准备金，如果您中途退保，即以该保单的责任准备金作为给付解约的退还金。被保险人要求解约或退保时，寿险公司应该发还的金额。
在长期寿险契约中，保险人为履行契约责任，通常需要提存一定数额的责任准备金。当被保险人于保险有效期内因故要求解约或退保时，保险人按规定，将提存的责任准备金减去解约扣除后的余额退还给被保险人，这部分余额即解约金，亦即退保时保单所具有的现金价值。', 
  applicantid string DEFAULT NULL COMMENT '投保人 根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号，再找到individualid', 
  applicantscustid string DEFAULT NULL COMMENT '投保人上游客户号', 
  appname string DEFAULT NULL COMMENT '投保人名称 POLICY_INFORMATION.NAME', 
  appphone string DEFAULT NULL COMMENT '投保人手机', 
  appcertid string DEFAULT NULL COMMENT '投保人证件号码 POLICY_INFORMATION.IDNO', 
  insuredid string DEFAULT NULL COMMENT '主被保险人 客户号', 
  insuredscustid string DEFAULT NULL COMMENT '主被保险人 上游客户号POLICY_INFORMATION.insuredno', 
  insname string DEFAULT NULL COMMENT '主被保险人名称 POLICY_INFORMATION.INSUREDNAME', 
  insphone string DEFAULT NULL COMMENT '主被保险人手机', 
  inscertid string DEFAULT NULL COMMENT '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO', 
  productid string DEFAULT NULL COMMENT '险种代码 POLICY_INFORMATION.RISKCODE', 
  productname string DEFAULT NULL COMMENT '险种名称 INSURANCEINFO.RISKNAME', 
  salesperson string DEFAULT NULL COMMENT '业务员', 
  spname string DEFAULT NULL COMMENT '业务员名称 HEALTH_INSURANCE_LISTING.AGENTCODE', 
  iaccount string DEFAULT NULL COMMENT '所属保险账户', 
  branchcode string DEFAULT NULL COMMENT '机构代码', 
  policybelong string DEFAULT NULL COMMENT '业绩归属 POLICY_INFORMATION.SALECOM', 
  agentchannel string DEFAULT NULL COMMENT '代理渠道 INSURANCEINFO.SELLTYPE', 
  agentorg string DEFAULT NULL COMMENT '代理机构', 
  regtype string DEFAULT NULL COMMENT '户籍类型', 
  pincome string DEFAULT NULL COMMENT '个人年收入', 
  fincome double DEFAULT NULL COMMENT '家庭年收入', 
  incomesource string DEFAULT NULL COMMENT '收入来源', 
  socialinsurance string DEFAULT NULL COMMENT '是否有社保', 
  oipolicy string DEFAULT NULL COMMENT '是否投保其他保险公司', 
  oicompany string DEFAULT NULL COMMENT '其他保险公司', 
  oicproduct string DEFAULT NULL COMMENT '其他保险公司险种', 
  oiamount double DEFAULT NULL COMMENT '其他保险公司保额', 
  drinking string DEFAULT NULL COMMENT '是否饮酒', 
  dfavor string DEFAULT NULL COMMENT '饮酒喜好', 
  poservice string DEFAULT NULL COMMENT '是否保全 投保人持有保单是否在AUDIT_EDORLIST.CONTNO存在', 
  payendyear int DEFAULT NULL COMMENT '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
  loanmoney double DEFAULT NULL COMMENT '保单质押贷款金额 AUDIT_LN_LIST.LNMONEY ',
  paydate date DEFAULT NULL COMMENT '缴至日期',
  created_by string DEFAULT NULL COMMENT '创建人', 
  created_time date DEFAULT NULL COMMENT '创建时间', 
  updated_by string DEFAULT NULL COMMENT '更新人', 
  updated_time date DEFAULT NULL COMMENT '更新时间'
)
COMMENT '保单 确定参与方之间，参与方和保险公司之间的协议，使相关各方能在遵守一定的规则并履行相关义务的前提下进行产品或资源或服务的供给'
STORED AS ES
with shard number 10
replication 1;


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

INSERT INTO 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', 
  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', 
	individual,--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', 
	'koucx',
	sysdate() 
	FROM shanghailifeecif.policy_information 

	--更新投保人和被保人id

	UPDATE insurancearrangement a SET (
		applicantid --'投保人ID',
	) = (
		select 
			indid ,--'投保人ID',
		from individual b
		where b.scustid  = a.applicantscustid
	) WHERE 1=1 ;

	UPDATE insurancearrangement a SET (
		insuredid --'主保险人ID',
	) = (
		select 
			indid ,--'投保人ID',
		from individual b
		where b.scustid  = a.insuredscustid
	) WHERE 1=1 ;

	--更新INSURANCEINFO 表中的信息

	UPDATE 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 ;




	EXCEPTION
		WHEN HIVE_EXCEPTION THEN 
			 log_exception('init_insurancearrangement_main',sqlerrm(),sqlcode())
		WHEN Others THEN
			 log_exception('init_insurancearrangement_main',sqlerrm(),sqlcode())
END;


=====================================================================================================================================
--数据分析sql


SELECT count(1) FROM shanghailifeecif.POLICY_INFORMATION ;
--2185616
SELECT count(1) FROM
(
	SELECT count(1) FROM shanghailifeecif.POLICY_INFORMATION GROUP BY CONTNO
);
--1466424


SELECT count(1) FROM shanghailifeecif.INSURANCEINFO GROUP BY CONTNO;
--1520720

SELECT count(1) FROM
(
SELECT count(1) FROM shanghailifeecif.INSURANCEINFO GROUP BY CONTNO
);
--1520720

SELECT count(1) FROM shanghailifeecif.health_insurance_listing WHERE agentgrouparea IS NOT NULL;
--121
SELECT count(1) FROM shanghailifeecif.health_insurance_listing WHERE agentgroup IS NOT NULL;
--10275

SELECT count(1) FROM shanghailifeecif.health_insurance_listing WHERE agentgroup IS  NULL;
--	0