ShangHaiLifeECIF/sql-achievement/存储过程/理赔qxp.sql

-- -----------------------------------------------------------------------
-- CREATE TABLE shanghailifeecif.insurance_claim(
--    batno varchar2(40) DEFAULT NULL COMMENT '批次号',  -- dialect: ORACLE
--   contno varchar2(40) DEFAULT NULL COMMENT '保单号',  -- dialect: ORACLE
--   clmno varchar2(40) DEFAULT NULL COMMENT '赔案号',  -- dialect: ORACLE
--   cvalidate timestamp DEFAULT NULL COMMENT '保单生效日', 
--   riskdate timestamp DEFAULT NULL COMMENT '出险日期', 
--   ageofdanger decimal(3,0) DEFAULT NULL COMMENT '被保险人出险时年龄', 
--   riskcode varchar2(40) DEFAULT NULL COMMENT '险种代码',  -- dialect: ORACLE
--   riskname varchar2(512) DEFAULT NULL COMMENT '险种名称',  -- dialect: ORACLE
--   lloccurreason varchar2(512) DEFAULT NULL COMMENT '出险原因',  -- dialect: ORACLE
--   accresult1 varchar2(512) DEFAULT NULL COMMENT '出险结果1',  -- dialect: ORACLE
--   accresult2 varchar2(512) DEFAULT NULL COMMENT '出险结果2',  -- dialect: ORACLE
--   accidentdetail varchar2(512) DEFAULT NULL COMMENT '意外细节',  -- dialect: ORACLE
--   llgettype varchar2(512) DEFAULT NULL COMMENT '赔付结论',  -- dialect: ORACLE
--   realpay decimal(12,2) DEFAULT NULL COMMENT '赔付金额', 
--   applydate timestamp DEFAULT NULL COMMENT '申请日期', 
--   accepteddate timestamp DEFAULT NULL COMMENT '交接日期', 
--   rptdate timestamp DEFAULT NULL COMMENT '报案日期', 
--   rgtdate timestamp DEFAULT NULL COMMENT '立案日期', 
--   auditdate timestamp DEFAULT NULL COMMENT '审核日期', 
--   examdate timestamp DEFAULT NULL COMMENT '审批日期', 
--   senddate timestamp DEFAULT NULL COMMENT '抽盘日期', 
--   returndate timestamp DEFAULT NULL COMMENT '回盘日期', 
--   llclaimstate varchar2(512) DEFAULT NULL COMMENT '目前状态',  -- dialect: ORACLE
--   operator varchar2(512) DEFAULT NULL COMMENT '立案人',  -- dialect: ORACLE
--   auditper varchar2(512) DEFAULT NULL COMMENT '审核人',  -- dialect: ORACLE
--   examper varchar2(512) DEFAULT NULL COMMENT '审批人',  -- dialect: ORACLE
--   isinvestigated varchar2(10) DEFAULT NULL COMMENT '是否调查件',  -- dialect: ORACLE
--   idno varchar2(40) DEFAULT NULL COMMENT '被保险人身份证件号码',  -- dialect: ORACLE
--   insuredname varchar2(40) DEFAULT NULL COMMENT '姓名',  -- dialect: ORACLE
--   isfromwx varchar2(10) DEFAULT NULL COMMENT '是否微信申请理赔标志',  -- dialect: ORACLE
--   insuredno varchar2(40) DEFAULT NULL COMMENT '出险人客户号',  -- dialect: ORACLE
--   amnt decimal(12,2) DEFAULT NULL COMMENT '保额', 
--   managecom varchar2(10) DEFAULT NULL COMMENT '管理机构',  -- dialect: ORACLE
--   estimatedreparations varchar2(40) DEFAULT NULL COMMENT '预估赔款金额',  -- dialect: ORACLE
--   risktype varchar2(40) DEFAULT NULL COMMENT '险种类型',  -- dialect: ORACLE
--   getdutycode varchar2(40) DEFAULT NULL COMMENT '给付责任编码',  -- dialect: ORACLE
--   getdutyname varchar2(512) DEFAULT NULL COMMENT '给付责任名称',  -- dialect: ORACLE
--   salechnl varchar2(40) DEFAULT NULL COMMENT '销售渠道',  -- dialect: ORACLE
--   accname varchar2(200) DEFAULT NULL COMMENT '申请人',  -- dialect: ORACLE
--   accidenttype varchar2(200) DEFAULT NULL COMMENT '理赔类型',  -- dialect: ORACLE
--   enddate timestamp DEFAULT NULL COMMENT '保单终止日期', 
--   salechnl2 varchar2(200) DEFAULT NULL COMMENT '销售二级渠道',  -- dialect: ORACLE
--   insuredsex varchar2(200) DEFAULT NULL COMMENT '被保险人性别',  -- dialect: ORACLE
--   insuredjob 	varchar2(200) DEFAULT NULL COMMENT '职业类别',  -- dialect: ORACLE
--   paystatus varchar2(200) DEFAULT NULL COMMENT '支付状态',  -- dialect: ORACLE
--   RGTNO varchar2(200) DEFAULT NULL COMMENT '立案号',
--   RPTNO varchar2(200) DEFAULT NULL COMMENT '报案号',
--   RPTORNAME varchar2(200) DEFAULT NULL COMMENT '报案人名称',
--   RELATION varchar2(200) DEFAULT NULL COMMENT '报案人与出险人关系',
--   ACCDESC varchar2(200) DEFAULT NULL COMMENT '报案描述',
--   ins_time timestamp DEFAULT NULL
-- )
-------------------------------------------------------------------------------------
--数据解析
--SELECT llgettype FROM shanghailifeecif.insurance_claim GROUP BY llgettype  --通融给付、正常给付、全部拒付
--SELECT count(1) a,contno,clmno FROM shanghailifeecif.insurance_claim GROUP BY contno,clmno ORDER BY a DESC; 按照保单和理赔去重 最大重复数为3
--且三条理赔的险种不一样

---------------------保单数据处理

--创建保险理赔表insuranceclaimthread
DROP TABLE IF EXISTS insuranceclaimthread;
CREATE  TABLE insuranceclaimthread(
  icthreadid string NOT NULL  COMMENT '保险理赔Id',
  crno string DEFAULT NULL COMMENT '立案号',
  companyno string DEFAULT NULL COMMENT '公司代码',
  policyno string NOT NULL COMMENT '保险单号 INSURANCE_CLAIM.CONTNO',
  applicantid string DEFAULT NULL COMMENT '投保人ID',
  appname string DEFAULT NULL COMMENT '投保人名称',
  appphone string DEFAULT NULL COMMENT '投保人手机',
  appcertid string DEFAULT NULL COMMENT '投保人证件号码 INSURANCE_CLAIM.RISKDATE',
  cnno string DEFAULT NULL COMMENT '报案号',
  losstime date DEFAULT NULL COMMENT '损失发生时间',
  rpid string DEFAULT NULL COMMENT '报案人',
  rpname string DEFAULT NULL COMMENT '报案人名称',
  rpphone string DEFAULT NULL COMMENT '报案人电话',
  lpid string DEFAULT NULL COMMENT '出险人	根据INSURANCE_CLAIM.INSUREDNO获取indid',
  lpscutid string DEFAULT NULL COMMENT '出险人上游客户号	根据INSURANCE_CLAIM',
  lpname string DEFAULT NULL COMMENT '出险人名称',
  cndate date DEFAULT NULL COMMENT '报案日期 INSURANCE_CLAIM.RPTDATE',
  lossdescribe string DEFAULT NULL COMMENT '损失原因 案件发生的原因:INSURANCE_CLAIM.LLOCCURREASON',
  crdate date DEFAULT NULL COMMENT '立案日期 INSURANCE_CLAIM.RGTDATE',
  cndescribe string DEFAULT NULL COMMENT '报案描述 INSURANCE_CLAIM.ACCIDENTDETAIL',
  claimstatus string DEFAULT NULL COMMENT '理赔状态 案件的状态，例如：录入、已立案等',
  csdate date DEFAULT NULL COMMENT '理赔状态日期',
  relationship string DEFAULT NULL COMMENT '报案人与出险人关系',
  ccno string DEFAULT NULL COMMENT '赔案号 AUDIT_CLAIM_INSURANCE.CLMNO',
  ccstatus string DEFAULT NULL COMMENT '赔案状态',
  ccsdate date DEFAULT NULL COMMENT '赔案状态日期',
  productid string DEFAULT NULL COMMENT '险种代码	INSURANCE_CLAIM_HEALTH.RISKCODE',
  productname string DEFAULT NULL COMMENT '险种代码	INSURANCE_CLAIM_HEALTH.RISKNAME',
  ccamt double DEFAULT NULL COMMENT '赔付金额	INSURANCE_CLAIM_HEALTH.REALPAY',
  branchcode string DEFAULT NULL COMMENT '机构代码',
  claimcasestatus string DEFAULT NULL COMMENT '案件状态INSURANCE_CLAIM_HEALTH.LLCLAIMSTATE',
  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 insuranceclaimthread_main() -- 创建主存储过程
IS    
BEGIN
	insert into insuranceclaimthread (
		icthreadid ,--'保险理赔Id',
		crno ,--'立案号',
		--companyno ,-- '公司代码',
		policyno ,--'保险单号 INSURANCE_CLAIM.CONTNO',
		--applicantid ,--'投保人ID',
		--appname ,--'投保人名称',
		--appphone ,--'投保人手机',
		--appcertid ,--'投保人证件号码 INSURANCE_CLAIM.RISKDATE',
		cnno ,--'报案号',
		losstime ,-- '损失发生时间',
		--rpid ,-- '报案人',
		rpname ,--'报案人名称',
		--rpphone ,-- '报案人电话',
		--lpid ,--'出险人	根据INSURANCE_CLAIM.INSUREDNO获取indid',
		lpscutid,--'出险人上游客户号
		--lpname ,-- '出险人名称',
		cndate ,--'报案日期 INSURANCE_CLAIM.RPTDATE',
		lossdescribe ,-- '损失原因 案件发生的原因:INSURANCE_CLAIM.LLOCCURREASON',
		crdate ,-- '立案日期 INSURANCE_CLAIM.RGTDATE',
		cndescribe ,--'报案描述 INSURANCE_CLAIM.ACCIDENTDETAIL',
		claimstatus ,--'理赔状态 案件的状态，例如：录入、已立案等',
		csdate ,--'理赔状态日期',
		relationship ,-- '报案人与出险人关系',
		ccno ,-- '赔案号 AUDIT_CLAIM_INSURANCE.CLMNO',
		ccstatus ,--'赔案状态',
		--ccsdate ,-- '赔案状态日期',
		productid ,--'险种代码	INSURANCE_CLAIM_HEALTH.RISKCODE',
		productname, --'险种名称	INSURANCE_CLAIM_HEALTH.RISKNAME',
		ccamt ,--'赔付金额	INSURANCE_CLAIM_HEALTH.realpay',
		--branchcode,-- '机构代码',
		claimcasestatus ,--'案件状态INSURANCE_CLAIM_HEALTH.LLCLAIMSTATE',
		created_by ,
		created_time 
		-- updated_time,
		-- updated_by
	) select 
   		row_number()over(),
   		rgtno,

		contno,



		rptno,--报案号
		riskdate,--损失发生的时间

		rptorname,--报案人名称


   		INSUREDNO,--出险人 上游客户号
   		--lpname,
		rptdate,
		lloccurreason,
		rgtdate,
		accidentdetail,--报案描述
		llclaimstate,
		auditdate,--审批日期
		relation,
		clmno,--赔案号
		llgettype,--赔案状态

		riskcode,
		riskname,
		realpay,

		llclaimstate,--案件状态
		'admin',
		sysdate()
	from shanghailifeecif.INSURANCE_CLAIM where contno is not null 
	
	update insuranceclaimthread a set (
		lpid,
		lpname
	) = ( 
		select 
			b.indid,
			b.name
		from individual_search1 b
		where a.lpscutid = b.SCustID
	)where 1=1


	UPDATE insuranceclaimthread a SET (
			applicantid ,--'投保人ID',
			appname ,--'投保人名称',
			appphone ,--'投保人手机',
			appcertid, --'投保人证件号码 INSURANCE_CLAIM.RISKDATE',
			branchcode-- '机构代码',
		) = (
			select 
				applicantid ,--'投保人ID',
				appname ,--'投保人名称',
				appphone ,--'投保人手机',
				appcertid, --'投保人证件号码 INSURANCE_CLAIM.RISKDATE',
				branchcode-- '机构代码',
			from shanglifeecif.insurancearrangement b
			where b.policyno  = a.policyno
		) WHERE 1=1 ;



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





----------------------利用insuranceinfo 插入或更新信息临时表


====================================================================================================================
=============================================数据查询语句============================================================
====================================================================================================================
 SELECT count(1) FROM insuranceclaimthread;  --