理赔结案没有成果时候处理的脚本只需要修改一下报案号

/*删除所有的结案的信息*/

delete  from upiccore.gcclaimstatus  where  registno='070131021360201900015312'    and nodetype in ('endca','endcv');
delete  from upiccore.gcclaimstatusdetail  where  registno='070131021360201900015312'    and nodetype in ('endca','endcv');

/* 结案状态 */
insert into upiccore.gcclaimstatus (REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, NODETYPE, TIMES, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE, BUSINESSNO, CLAIMDATE, OPERATETYPE, NODESTATUS, OPERATORCODE, COMPANYCODE, OPERATEDATE, FLOWINTIME,
 SUBMITTIME, LOCKEDIND, INJUREDIND, URGENCY, APPLICODE, APPLINAME, INSUREDCODE, INSUREDNAME, INSUREDTYPE,CHANNELDETAILCODE, LICENSENO,  VALIDIND, REMARK)   select  REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, 'endca', TIMES, SUBPOLICYNO, ENDORSEQNO,
 RISKCLASS, MODELTYPE, BUSINESSNO, CLAIMDATE, '03', 'S41',
 
 (select OPERATORCODE from upiccore.gcclaimstatus  where nodetype='undwc'  and registno='070131021360201900015312'  and riskcode='0803') 
 
  ,  COMPANYCODE, to_date('12-12-2019 10:02:18', 'dd-mm-yyyy hh24:mi:ss'), to_date('12-12-2019 10:02:18', 'dd-mm-yyyy hh24:mi:ss'), to_date('12-12-2019 10:02:18', 'dd-mm-yyyy hh24:mi:ss'), LOCKEDIND, 
INJUREDIND, URGENCY, APPLICODE, APPLINAME, INSUREDCODE, INSUREDNAME, '1',CHANNELDETAILCODE, LICENSENO,  VALIDIND, REMARK from  upiccore.gcclaimstatus where  NODETYPE='claim' and riskcode='0803' and registno = '070131021360201900015312';

 /* 结案状态详情 */
insert into upiccore.gcclaimstatusdetail (REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, NODETYPE, TIMES, SERIALNO, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE, BUSINESSNO,
 BUSINESSSEQNO, OPERATETYPE, NODESTATUS, PARENTID, OPERATORCODE, COMPANYCODE, LOSSID, LOSSTYPE, OPERATEDATE, FLOWINTIME, SUBMITTIME, VALIDIND, REMARK, FLAG, LOSSNO, 
 TRANSFERNUM)  select  REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, 'endca', TIMES, SERIALNO, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE, BUSINESSNO,
 BUSINESSSEQNO, '03', 'S41', 'undwc_1',
 (select OPERATORCODE from upiccore.gcclaimstatus  where nodetype='undwc'  and registno='070131021360201900015312'  and riskcode='0803')
 ,
   COMPANYCODE, LOSSID,  LOSSTYPE, to_date('12-12-2019 10:02:18', 'dd-mm-yyyy hh24:mi:ss'), to_date('12-12-2019 10:02:18', 'dd-mm-yyyy hh24:mi:ss'),
  to_date('12-12-2019 10:02:18', 'dd-mm-yyyy hh24:mi:ss'), VALIDIND, REMARK, FLAG, LOSSNO, 
 TRANSFERNUM from upiccore.gcclaimstatusdetail where  registno='070131021360201900015312'  and riskcode='0803'  and nodetype='camv';
 
  /* 结案审核状态 */
  insert into upiccore.gcclaimstatus (REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, NODETYPE, TIMES, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE, BUSINESSNO, CLAIMNO, CLAIMDATE,
  RELATEDCLAIMNO, OPERATETYPE, NODESTATUS, OPERATORCODE, COMPANYCODE, OPERATEDATE, FLOWINTIME, SUBMITTIME, LOCKEDIND, INJUREDIND, URGENCY, APPLICODE, APPLINAME, INSUREDCODE, INSUREDNAME, 
  INSUREDTYPE, CHANNELDETAILCODE, LICENSENO, DAMAGEDATE, VALIDIND, REMARK, FLAG, ID)
select REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, 'endcv', TIMES, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE, BUSINESSNO, 
(select OPERATORCODE  from upiccore.gcclaimstatus  where nodetype='claim'  and registno='070131021360201900015312'  and riskcode='0803'),
CLAIMDATE,
  RELATEDCLAIMNO, OPERATETYPE, 'U3',
   (select CLAIMNO  from upiccore.gcclaimstatus  where nodetype='claim'  and registno='070131021360201900015312'  and riskcode='0803'),
   COMPANYCODE, OPERATEDATE, FLOWINTIME, SUBMITTIME, LOCKEDIND, INJUREDIND, URGENCY, APPLICODE, APPLINAME, INSUREDCODE, INSUREDNAME, 
  INSUREDTYPE, CHANNELDETAILCODE, LICENSENO, DAMAGEDATE, VALIDIND, REMARK, FLAG, ID
from upiccore.gcclaimstatus where  NODETYPE='endca' 
 and riskcode='0803' and registno = '070131021360201900015312';
 
  /* 结案审核状态详情 */
 insert into upiccore.gcclaimstatusdetail (REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, NODETYPE, TIMES, SERIALNO, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE,
  BUSINESSNO, BUSINESSSEQNO, OPERATETYPE, NODESTATUS, PARENTID, OPERATORCODE, COMPANYCODE, LOSSID, LOSSNAME, LOSSTYPE, OPERATEDATE, FLOWINTIME, SUBMITTIME, VALIDIND, REMARK, FLAG, 
  LOSSNO, TRANSFERNUM) 
  select REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, 'endcv', TIMES, SERIALNO, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE,
  BUSINESSNO, BUSINESSSEQNO, OPERATETYPE, 'U3', 'endca_1',
(select OPERATORCODE  from upiccore.gcclaimstatus  where nodetype='claim'  and registno='070131021360201900015312'  and riskcode='0803'), 
   COMPANYCODE, LOSSID, LOSSNAME, LOSSTYPE, OPERATEDATE, FLOWINTIME, SUBMITTIME, VALIDIND, REMARK, FLAG, 
  LOSSNO, TRANSFERNUM  from upiccore.gcclaimstatusdetail  where  NODETYPE='endca' and riskcode='0803' and registno = '070131021360201900015312';
  
  /*交强结案状态表*/
insert into upiccore.gcclaimstatus (REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, NODETYPE, TIMES, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE, BUSINESSNO,
 CLAIMNO, CLAIMDATE, RELATEDCLAIMNO, OPERATETYPE, NODESTATUS, OPERATORCODE, COMPANYCODE, OPERATEDATE, FLOWINTIME, SUBMITTIME, LOCKEDIND, 
INJUREDIND, URGENCY, APPLICODE, APPLINAME, INSUREDCODE, INSUREDNAME, INSUREDTYPE, CHANNELDETAILCODE, LICENSENO, DAMAGEDATE, VALIDIND, REMARK, FLAG, ID)
select REGISTNO, POLICYNO, PLANCODE, '0807', 2, NODETYPE, TIMES,
 (select SUBPOLICYNO from  upiccore.gcclaimstatus  where  registno='070131021360201900015312'    and riskcode='0807'  and nodetype='claim')
, ENDORSEQNO, RISKCLASS, MODELTYPE, 
(select BUSINESSNO from  upiccore.gcclaimstatus  where  registno='070131021360201900015312'    and riskcode='0807'  and nodetype='claim')
,
 CLAIMNO, CLAIMDATE, RELATEDCLAIMNO, OPERATETYPE, NODESTATUS, OPERATORCODE, COMPANYCODE, OPERATEDATE, FLOWINTIME, SUBMITTIME, LOCKEDIND, 
INJUREDIND, URGENCY, APPLICODE, APPLINAME, INSUREDCODE, INSUREDNAME, INSUREDTYPE, CHANNELDETAILCODE, LICENSENO, DAMAGEDATE, VALIDIND, REMARK, FLAG, ID  
from upiccore.gcclaimstatus where  registno='070131021360201900015312'  and riskcode='0803'  and nodetype in ('endca');
/*交强结案状态详情表*/
insert into upiccore.gcclaimstatusdetail (REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, NODETYPE, TIMES, SERIALNO, SUBPOLICYNO, ENDORSEQNO, RISKCLASS,
 MODELTYPE, BUSINESSNO, BUSINESSSEQNO, OPERATETYPE, NODESTATUS, PARENTID, 
OPERATORCODE, COMPANYCODE, LOSSID, LOSSNAME, LOSSTYPE, OPERATEDATE, FLOWINTIME, SUBMITTIME, VALIDIND, REMARK, FLAG, LOSSNO, TRANSFERNUM)
select REGISTNO, POLICYNO, PLANCODE, '0807', 2, NODETYPE, TIMES, SERIALNO, 
(select SUBPOLICYNO from  upiccore.gcclaimstatusdetail  where  registno='070131021360201900015312'    and riskcode='0807'  and nodetype='claim')
, ENDORSEQNO, RISKCLASS,
 MODELTYPE, 
(select BUSINESSNO from  upiccore.gcclaimstatusdetail  where  registno='070131021360201900015312'    and riskcode='0807'  and nodetype='claim')
 , BUSINESSSEQNO, OPERATETYPE, NODESTATUS, PARENTID, 
OPERATORCODE, COMPANYCODE, LOSSID, LOSSNAME, LOSSTYPE, OPERATEDATE, FLOWINTIME, SUBMITTIME, VALIDIND, REMARK, FLAG, LOSSNO, TRANSFERNUM  from 
 upiccore.gcclaimstatusdetail where  registno='070131021360201900015312'  and riskcode='0803'  and nodetype in ('endca');
/*交强结案审核状态表*/
insert into upiccore.gcclaimstatus (REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, NODETYPE, TIMES, SUBPOLICYNO, ENDORSEQNO, RISKCLASS, MODELTYPE, BUSINESSNO,
 CLAIMNO, CLAIMDATE, RELATEDCLAIMNO, OPERATETYPE, NODESTATUS, OPERATORCODE, COMPANYCODE, OPERATEDATE, FLOWINTIME, SUBMITTIME, LOCKEDIND, 
INJUREDIND, URGENCY, APPLICODE, APPLINAME, INSUREDCODE, INSUREDNAME, INSUREDTYPE, CHANNELDETAILCODE, LICENSENO, DAMAGEDATE, VALIDIND, REMARK, FLAG, ID)
select REGISTNO, POLICYNO, PLANCODE, '0807', 2, NODETYPE, TIMES,
 (select SUBPOLICYNO from  upiccore.gcclaimstatus  where  registno='070131021360201900015312'    and riskcode='0807'  and nodetype='claim')
, ENDORSEQNO, RISKCLASS, MODELTYPE, 
(select BUSINESSNO from  upiccore.gcclaimstatus  where  registno='070131021360201900015312'    and riskcode='0807'  and nodetype='claim')
,
 CLAIMNO, CLAIMDATE, RELATEDCLAIMNO, OPERATETYPE, NODESTATUS, OPERATORCODE, COMPANYCODE, OPERATEDATE, FLOWINTIME, SUBMITTIME, LOCKEDIND, 
INJUREDIND, URGENCY, APPLICODE, APPLINAME, INSUREDCODE, INSUREDNAME, INSUREDTYPE, CHANNELDETAILCODE, LICENSENO, DAMAGEDATE, VALIDIND, REMARK, FLAG, ID  
from upiccore.gcclaimstatus where  registno='070131021360201900015312'  and riskcode='0803'  and nodetype in ('endcv');
/*交强结案审核状态详情表*/
insert into upiccore.gcclaimstatusdetail (REGISTNO, POLICYNO, PLANCODE, RISKCODE, ITEMNO, NODETYPE, TIMES, SERIALNO, SUBPOLICYNO, ENDORSEQNO, RISKCLASS,
 MODELTYPE, BUSINESSNO, BUSINESSSEQNO, OPERATETYPE, NODESTATUS, PARENTID, 
OPERATORCODE, COMPANYCODE, LOSSID, LOSSNAME, LOSSTYPE, OPERATEDATE, FLOWINTIME, SUBMITTIME, VALIDIND, REMARK, FLAG, LOSSNO, TRANSFERNUM)
select REGISTNO, POLICYNO, PLANCODE, '0807', 2, NODETYPE, TIMES, SERIALNO, 
(select SUBPOLICYNO from  upiccore.gcclaimstatusdetail  where  registno='070131021360201900015312'    and riskcode='0807'  and nodetype='claim')
, ENDORSEQNO, RISKCLASS,
 MODELTYPE, 
(select BUSINESSNO from  upiccore.gcclaimstatusdetail  where  registno='070131021360201900015312'    and riskcode='0807'  and nodetype='claim')
 , BUSINESSSEQNO, OPERATETYPE, NODESTATUS, PARENTID, 
OPERATORCODE, COMPANYCODE, LOSSID, LOSSNAME, LOSSTYPE, OPERATEDATE, FLOWINTIME, SUBMITTIME, VALIDIND, REMARK, FLAG, LOSSNO, TRANSFERNUM  from 
 upiccore.gcclaimstatusdetail where  registno='070131021360201900015312'  and riskcode='0803'  and nodetype in ('endcv');

  /*修改立案主表*/
 update upiccore.gcclaimmain e
   set e.casetype = '4',e.endcasestatus = '3'
 where e.registno = '070131021360201900015312';