CREATE PROCEDURE informix.remove_2( settleno_in VARCHAR(22),flag_in varchar(8))
-- 定义settle变量
define settleno_v varchar(22) ;
define operatecode_v char(10) ;
define operatedate_v date ;
define sumdocfee_v decimal(14,2) ;
define settledocrate_v decimal(8,2) ;
define settledocfee_v decimal(14,2) ;
define remark_v varchar(255) ;
define flag_v varchar(8) ;
define inserttimeforhis_v datetime year to second ;
define operatetimeforhis_v datetime year to second ;
-- 定义detail变量------------------------------------
define settleno_vv varchar(22) ;
define receserino_vv varchar(22) ;
define suffixno_vv integer ;
define serialno_vv integer ;
define policyno_vv char(22) ;
define certitype_vv varchar(1) ;
define certino_vv varchar(25) ;
define riskcode_vv char(3) ;
define payno_vv integer ;
define sffreason_vv varchar(3) ;
define businessnature_vv varchar(3) ;
define agentcode_vv varchar(12) ;
define agentname_vv varchar(255) ;
define costrate_vv decimal(5) ;
define costfee_vv decimal(14,2) ;
define handlertype_vv varchar(1) ;
define handlercode_vv char(10) ;
define comcode_vv varchar(8) ;
define applicode_vv varchar(30) ;
define appliname_vv varchar(255) ;
define insuredcode_vv varchar(30) ;
define insuredname_vv varchar(255) ;
define currency_vv char(3) ;
define docfeetype_vv varchar(3) ;
define paidpremium_vv decimal(14,2) ;
define deskdate_vv date ;
define extractcode_vv varchar(11) ;
define extractdate_vv date ;
define settleflag_vv varchar(1) ;
define remark_vv varchar(255) ;
define chgfeeno_vv char(22) ;
define sellerno_vv char(22) ;
define sellername_vv varchar(255) ;
define sellerserialno_vv integer ;
define mainflag_vv char(1) ;
define flag_vv varchar(8) ;
define inserttimeforhis_vv datetime year to second;
define operatetimeforhis_vv datetime year to second;
-- 先定义prpdocfeesetplan的字段
define settleno_vvv varchar(22) ;
define payno_vvv decimal(8,2) ;
define sumdocfee_vvv decimal(14,2) ;
define currency_vvv char(3) ;
define thissettledocrate_vvv decimal(8,2) ;
define thissettledocfee_vvv decimal(14,2) ;
define settledocfeebefor_vvv decimal(14,2) ;
define settledocratebefor_vvv decimal(8,2) ;
define settledocfeeafter_vvv decimal(14,2) ;
define settledocrateafter_vvv decimal(8,2) ;
define remark_vvv varchar(255) ;
define flag_vvv varchar(8) ;
define inserttimeforhis_vvv datetime year to second ;
define operatetimeforhis_vvv datetime year to second ;
define operatedate_vvv date ;
define operatorcode_vvv char(10) ;
---- prpdocfeedetplan 的字段
define settleno_vvvv varchar(22) ;
define payno_vvvv decimal(8,2) ;
define receserino_vvvv varchar(22) ;
define suffixno_vvvv integer ;
define agentcode_vvvv varchar(12) ;
define clausecode_vvvv char(6) ;
define kindcode_vvvv char(6) ;
define serialno_vvvv integer ;
define policyno_vvvv char(22) ;
define certitype_vvvv varchar(1) ;
define certino_vvvv varchar(25) ;
define riskcode_vvvv char(3) ;
define sffreason_vvvv varchar(3) ;
define businessnature_vvvv varchar(3) ;
define agentname_vvvv varchar(255) ;
define handlertype_vvvv varchar(1) ;
define handlercode_vvvv char(10) ;
define comcode_vvvv varchar(8) ;
define applicode_vvvv varchar(30) ;
define appliname_vvvv varchar(255) ;
define insuredcode_vvvv varchar(30) ;
define insuredname_vvvv varchar(255) ;
define currency_vvvv char(3) ;
define docfeetype_vvvv varchar(3) ;
define thissettledocrate_vvvv decimal(8,2) ;
define thissettledocfee_vvvv decimal(14,2) ;
define deskdate_vvvv date ;
define extractcode_vvvv varchar(11) ;
define extractdate_vvvv date ;
define remark_vvvv varchar(255) ;
define flag_vvvv varchar(8) ;
define inserttimeforhis_vvvv datetime year to second ;
define operatetimeforhis_vvvv datetime year to second ;
-- settleno
define settleno_gby varchar(22) ;
define payno_gby decimal(8,2) ;
define receserino_gby varchar(22) ;
define suffixno_gby integer ;
define thissettledocfee_gby DECIMAL(8,4);
-- 判断是否已经存在
define isExists integer;
BEGIN
-- 如果已经迁?乒??ettleno,则直接跳过。
select count(removenum) into isExists from removeremark where removenum=settleno_in;
let isExists = 0;
IF (isExists > 0) THEN
-- 查询出prpinsdb:prpdocfeesettle记录
begin work;
select settleno, operatecode, operatedate, sumdocfee, settledocrate, settledocfee, remark, flag, inserttimeforhis, operatetimeforhis
into settleno_v, operatecode_v, operatedate_v, sumdocfee_v, settledocrate_v, settledocfee_v, remark_v, flag_v, inserttimeforhis_v, operatetimeforhis_v
from prpinsdb:prpdocfeesettle where settleno=settleno_in;
-- 插入主表scmsdocfeesettle
insert into scmsdocfeesettle (settleno, agentname, agentcode, currency, sumdocfee, settledocrate, settledocfee, ksdm, accountname, bankcode, accountno, licenseno, contractno, settleflag, operatetime, operatercode, operatedepcode, linkphone, remarks, auditstatus, processid, sumpremium, businessnature, validstatus, inserttimeforhis, operatetimeforhis)
values (settleno_v, null, null, null, sumdocfee_v, settledocrate_v, settledocfee_v, null, '', '', '', '', '', flag_v, operatedate_v, operatecode_v, null, null, remark_v, '2', null, 4144.14, '3', '1', inserttimeforhis_v, operatetimeforhis_v);
-- 插入记录表
-- 插入到新增的迁移记录表
insert into removeremark (removenum, tabtype, tabname) values (settleno_in, 'settle', 'prpinsdb:prpdocfeesettle');
-- 通过settleno_in 查询 prpdocfeesetdetail 的记录,并把相应记录插入到scmsdocfeesetdet
SELECT
detail.settleno,detail.receserino,detail.suffixno,detail.serialno,detail.policyno,detail.certitype, detail.certino,detail.riskcode,
detail.payno,detail.sffreason,detail.businessnature,detail.agentcode,detail.agentname,detail.costrate,detail.costfee,detail.handlertype,
detail.handlercode,detail.comcode,detail.applicode,detail.appliname,detail.insuredcode,detail.insuredname,detail.currency,detail.docfeetype,
detail.paidpremium,detail.deskdate,detail.extractcode,detail.extractdate,detail.settleflag, detail.remark,detail.chgfeeno,detail.sellerno,
detail.sellername,detail.sellerserialno,detail.mainflag,detail.flag,detail.inserttimeforhis,detail.operatetimeforhis
INTO
settleno_vv,receserino_vv,suffixno_vv,serialno_vv,policyno_vv,certitype_vv,certino_vv,riskcode_vv,payno_vv,sffreason_vv,businessnature_vv,agentcode_vv,agentname_vv,costrate_vv,costfee_vv,handlertype_vv,handlercode_vv,comcode_vv,applicode_vv,appliname_vv,insuredcode_vv,insuredname_vv,currency_vv,docfeetype_vv,paidpremium_vv,deskdate_vv,extractcode_vv,extractdate_vv,settleflag_vv,remark_vv,chgfeeno_vv,sellerno_vv,sellername_vv,sellerserialno_vv,mainflag_vv,flag_vv,inserttimeforhis_vv,operatetimeforhis_vv
FROM
prpinsdb:prpdocfeesetdetail detail
WHERE detail.settleno=settleno_in;
-- 插入 scmsdocfeesetdet
insert into scmsdocfeesetdet (settleno, serialno, receserino, suffixno, policyno, certitype, certino, classcode, riskcode, sffreason, businessnature, costrate, costfee, agentcode, agentname, handlertype, handlercode, handler1code, comcode, applicode, appliname, insuredcode, insuredname, currency, docfeetype, paidpremium, deskdate, extractcode, extractdate, settleflag, settlesubno, flag, remark, startdate, enddate, payrefreason, basecurrency, basepremium, detserialno, finalexchrate, ksdm, inserttimeforhis, operatetimeforhis,payno)
values (settleno_vv, serialno_vv, receserino_vv, suffixno_vv, policyno_vv, certitype_vv, certino_vv,null, riskcode_vv, sffreason_vv,businessnature_vv, 0.00, 0.00, agentcode_vv, agentname_vv, handlertype_vv, handlercode_vv,null,comcode_vv, applicode_vv, appliname_vv, insuredcode_vv,insuredname_vv, currency_vv, docfeetype_vv, paidpremium_vv,deskdate_vv, extractcode_vv, extractdate_vv, settleflag_vv,null, null, remark_vv, null,null, null, null, null, null, null, null, inserttimeforhis_vv, operatetimeforhis_vv,payno_vv);
-- 通过settleno,riskcode生成ScmsDocFeeSetSub 子结算单。
-- 更新 scmsdocfeedetail
update scmsdocfeedetail set settleno = settleno_in ,flag = flag_vvvv where receserino = receserino_vvvv;
-- 判断结算单状态是否为零,如果不等于零则生成 scmsdocfeesetplan和scmsdocfeedetplan记录。
IF flag_in <> '0' THEN
-- 通过settleno查询出prpinsdb:prpdocfeesetplan所有的记录
foreach
select
settleno,
payno,
sumdocfee,
currency,
thissettledocrate,
thissettledocfee,
settledocfeebefor,
settledocratebefor,
settledocfeeafter,
settledocrateafter,
remark,
flag,
inserttimeforhis,
operatetimeforhis,
operatedate,
operatorcode
into
settleno_vvv,
payno_vvv,
sumdocfee_vvv,
currency_vvv,
thissettledocrate_vvv,
thissettledocfee_vvv,
settledocfeebefor_vvv,
settledocratebefor_vvv,
settledocfeeafter_vvv,
settledocrateafter_vvv,
remark_vvv,
flag_vvv,
inserttimeforhis_vvv,
operatetimeforhis_vvv,
operatedate_vvv,
operatorcode_vvv
from prpinsdb:prpdocfeesetplan where settleno=settleno_in
-- 插入scmsdocfeesetplan
insert into scmsdocfeesetplan (settleno, payno, sumdocfee, currency, costrate, settledocrate, settledocfee, settledocfeebefor, settledocratebefor, settledocfeeafter, settledocrateafter, paymentstatus, operatetime, operatercode, validstatus, operatedepcode, remark, realpayflag, makercode, operatetimeforhis, comcode, businessnature, agentcode, handler1code, licenseno, accountname, bankcode, accountno, linkperson, linkphone, businesstax, persontax, realpayfee, inserttimeforhis, flag, operatorcode)
values (settleno_vvv,payno_vvv,sumdocfee_vvv,currency_vvv,thissettledocrate_vvv,0.00,thissettledocfee_vvv,settledocfeebefor_vvv,settledocratebefor_vvv,settledocfeeafter_vvv,settledocrateafter_vvv,null,operatedate_vvv,
null,null,null,remark_vvv,null,null,operatetimeforhis_vvv,null,null,null,null,null,null,null,null,null,null,null,null,null,inserttimeforhis_vvv,flag_vvv,operatorcode_vvv);
-- 插入scmsdocfeedetplan
-- payno receserino settleno suffixno 分组求和 查询出数据库插入到佣金系统的 scmsdocfeedetplan
-- 多条或一条
foreach
select settleno,payno,receserino,suffixno,sum(thissettledocfee) into settleno_gby,payno_gby,receserino_gby,suffixno_gby,thissettledocfee_gby FROM prpinsdb:prpdocfeedetplan GROUP BY
settleno,payno,receserino,suffixno
-- 通过 settleno_gby,payno_gby,receserino_gby,suffixno_gby,thissettledocfee_gby 查询 prpinsdb:prpdocfeedetplan 的所有记录,并插入到scmsdocfeedetplan。
select settleno,payno,receserino,suffixno,agentcode,clausecode,kindcode,serialno,policyno,certitype,certino,riskcode,sffreason,businessnature,agentname,handlertype,handlercode,comcode,applicode,appliname,insuredcode,insuredname,currency,docfeetype,thissettledocrate,thissettledocfee,deskdate,extractcode,extractdate,remark,flag,inserttimeforhis,operatetimeforhis
into settleno_vvvv,payno_vvvv,receserino_vvvv,suffixno_vvvv,agentcode_vvvv,clausecode_vvvv,kindcode_vvvv,serialno_vvvv,policyno_vvvv,certitype_vvvv,certino_vvvv,riskcode_vvvv,sffreason_vvvv,businessnature_vvvv,agentname_vvvv,handlertype_vvvv,handlercode_vvvv,comcode_vvvv,applicode_vvvv,appliname_vvvv,insuredcode_vvvv,insuredname_vvvv,currency_vvvv,docfeetype_vvvv,thissettledocrate_vvvv,thissettledocfee_vvvv,deskdate_vvvv,extractcode_vvvv,extractdate_vvvv,remark_vvvv,flag_vvvv,inserttimeforhis_vvvv,operatetimeforhis_vvvv
from prpinsdb:prpdocfeedetplan
where settleno=settleno_gby and payno=payno_gby and receserino = receserino_gby and suffixno = suffixno_gby;
-- 插入数据到 scmsdocfeedetplan
-- 用thissettledocfee_gby 替换掉查询出来的thissettledocfee_vvvv
let thissettledocfee_vvvv = thissettledocfee_gby;
insert into scmsdocfeedetplan (settleno, payno, serialno, receserino, suffixno, agentcode, agentname, kindcode, policyno, certitype, certino, classcode, riskcode, sffreason, businessnature, applicode, appliname, insuredcode, insuredname, currency, docfeetype, deskdate, extractcode, extractdate, costrate, costfee, handlertype, handlercode, handler1code, comcode, paidpremium, flag, remark, settledocfeeafter, settledocrateafter, startdate, enddate, sendtime, sendcounts, errormessage, sendflag, settledocrate, settledocfee, realpayflag, payrefreason, basecurrency, basepremium, ksdm, inserttimeforhis, operatetimeforhis)
values (settleno_vvvv,payno_vvvv,serialno_vvvv,receserino_vvvv,suffixno_vvvv,agentcode_vvvv,agentname_vvvv,kindcode_vvvv,policyno_vvvv,certitype_vvvv,certino_vvvv,null,riskcode_vvvv,sffreason_vvvv,businessnature_vvvv,applicode_vvvv,appliname_vvvv,insuredcode_vvvv,insuredname_vvvv,currency_vvvv,docfeetype_vvvv,deskdate_vvvv,null,extractdate_vvvv,0.00,0.00,handlertype_vvvv,handlercode_vvvv,null,null,null,flag_vvvv,remark_vvvv,0.00,0.00,null,null,null,0,null,null,thissettledocrate_vvvv,thissettledocfee_vvvv,null,null,null,0.00,null,inserttimeforhis_vvvv,operatetimeforhis_vvvv);
end foreach;
end foreach;
END IF;
commit work;
ELIF isExists = 0 THEN
-- 不做任何操作,结束。
END IF;
END;
END PROCEDURE;
informix 写的存储过程
最新推荐文章于 2015-04-07 16:46:08 发布