| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677 |
- CREATE OR REPLACE PROCEDURE shanglifeecif.insuranceclaimthread_main() -- 创建主存储过程
- IS
- BEGIN
- insert into shanglifeecif.insuranceclaimthread (
- icthreadid ,--'保险理赔Id',1
- crno ,--'立案号',2
- --companyno ,-- '公司代码',
- policyno ,--'保险单号 INSURANCE_CLAIM.CONTNO',3
- --applicantid ,--'投保人ID',
- --appname ,--'投保人名称',
- --appphone ,--'投保人手机',
- --appcertid ,--'投保人证件号码 INSURANCE_CLAIM.RISKDATE',
- cnno ,--'报案号',4
- losstime ,-- '损失发生时间',5
- --rpid ,-- '报案人',
- rpname ,--'报案人名称',6
- --rpphone ,-- '报案人电话',
- --lpid ,--'出险人 根据INSURANCE_CLAIM.INSUREDNO获取indid',
- lpscutid,--'出险人上游客户号7
- 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',
- accidenttype,
- created_by ,
- created_time
- -- updated_time,
- -- updated_by
- ) select
- row_number()over(),--1
- trim(rgtno) as rgtno,--2
- trim(contno) as contno,--3
- trim(rptno) as rptno,--报案号4
- trim(riskdate) as riskdate,--损失发生的时间5
- trim(rptorname) as rptorname,--报案人名称6
- trim(INSUREDNO) as INSUREDNO,--出险人 上游客户号
- trim(INSUREDNAME) as INSUREDNAME,
- trim(rptdate) as rptdate,
- trim(lloccurreason) as lloccurreason,
- trim(rgtdate) as rgtdate,
- trim(ACCDESC) as ACCDESC,--报案描述
- trim(llclaimstate) as llclaimstate,
- trim(auditdate) as auditdate,--审批日期
- trim(relationname) as relationname,
- trim(clmno) as clmno,--赔案号
- trim(llgettype) as llgettype,--赔案状态
- trim(riskcode) as riskcode,
- trim(riskname) as riskname,
- realpay,
- trim(llclaimstate) as llclaimstate,--案件状态
- trim(accidenttype) as accidenttype,
- 'admin',
- sysdate()
- from dsj.INSURANCE_CLAIM where contno is not null;
-
- -- 更新渠道字段
- shanglifeecif.update_insuranceclaimthread_add_salecom();
- dbms_output.put_line('insuranceclaimthread_main函数跑批完成！');
- EXCEPTION
- WHEN HIVE_EXCEPTION THEN
- INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
- WHEN Others THEN
- INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
- END;
|
