| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232 |
-
- 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 ,
- crno ,
-
- policyno ,
-
-
-
-
- cnno ,
- losstime ,
-
- rpname ,
-
-
- lpscutid,
-
- cndate ,
- lossdescribe ,
- crdate ,
- cndescribe ,
- claimstatus ,
- csdate ,
- relationship ,
- ccno ,
- ccstatus ,
-
- productid ,
- productname,
- ccamt ,
-
- claimcasestatus ,
- created_by ,
- created_time
-
-
- ) select
- row_number()over(),
- rgtno,
- contno,
- rptno,
- riskdate,
- rptorname,
- INSUREDNO,
-
- 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 ,
- appname ,
- appphone ,
- appcertid,
- branchcode
- ) = (
- select
- applicantid ,
- appname ,
- appphone ,
- appcertid,
- 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;
- ====================================================================================================================
- =============================================数据查询语句============================================================
- ====================================================================================================================
- SELECT count(1) FROM insuranceclaimthread;
|
