---execute procedure revisesff('PLAQ201121090000000002');
CREATE PROCEDURE sffpl3g.revisesff(in_documentno char(22))
define v_02pcount Integer;
define v_02f1count Integer;
define v_maxorder02no Integer;
define v_f1flag Integer;
define v_lastorder02no Integer;
define v_change02no Integer;
define v_ksdm char(8);
define v_policyno char(22);
define v_insurant varchar(120);
define v_assuredcode char(16);
define v_classescode char(6);
define v_currency char(6);
define v_documentno char(22);
define v_kindcode char(3);
define v_order02no Integer;
define v_mainamount decimal(14);
define v_foreamount decimal(14);
define v_departcode char(8);
define v_acceptdepname char(80);
define v_mancode char(11);
define v_acceptopname char(80);
define v_agentcode char(12);
define v_refundname char(20);
define v_procflag char(1);
define v_serialno Integer;
define v_tcol1 char(30);
define v_tcol2 char(30);
define v_sendtime datetime year to second;
define v_sffid Integer;
define v_clausetype char(11);
select count(*) into v_02pcount from sf02p where documentno=in_documentno;
if v_02pcount > 0
then
delete from sf02p where documentno=in_documentno;
end if;
select max(order02no) into v_maxorder02no from sf02 where documentno=in_documentno;
--insert into sf02p
foreach v_sf02Cursor for
select ksdm, policyno, insurant, assuredcode, classescode, currency,documentno, kindcode, order02no,mainamount, foreamount, departcode, acceptdeptname, mancode, acceptopname, agentcode, refundname, procflag, serialno, tcol1, tcol2, sendtime, sffid into v_ksdm, v_policyno, v_insurant, v_assuredcode, v_classescode, v_currency, v_documentno, v_kindcode,v_order02no, v_mainamount, v_foreamount, v_departcode, v_acceptdepname, v_mancode, v_acceptopname, v_agentcode, v_refundname, v_procflag, v_serialno, v_tcol1, v_tcol2, v_sendtime, v_sffid from sf02 where documentno=in_documentno order by order02no
let v_order02no=v_maxorder02no+1;
let v_mainamount=-v_mainamount;
let v_foreamount=-v_foreamount;
if (v_kindcode ='R10' or v_kindcode='R91' or v_kindcode='R81' or v_kindcode='R82' or v_kindcode='P98' or v_kindcode='P94' or v_kindcode='P96' or v_kindcode='R18' or v_kindcode='R19' or v_kindcode='R92'or v_kindcode='R98'or v_kindcode='R96'or v_kindcode='RM0' or v_kindcode='RM1'or v_kindcode='RM2'
or v_kindcode='RJ2'or v_kindcode='RJ3'or v_kindcode='R95'or v_kindcode='RD5'or v_kindcode='RD6'or v_kindcode='RD7'or v_kindcode='RD8'or v_kindcode='RD9'or v_kindcode='RC0'or v_kindcode='RC1'or v_kindcode='RC2'or v_kindcode='RC3'or v_kindcode='RC4'or v_kindcode='RD0'or v_kindcode='RD1'
or v_kindcode='RD2'or v_kindcode='RD3'or v_kindcode='RD4'or v_kindcode='RJ2'or v_kindcode='RJ3'or v_kindcode='R83'or v_kindcode='R84' or (CHAR_LENGTH(v_kindcode)>=2 and substr(v_kindcode, 0, 2)='P0'))
then
let v_procflag = 'A';
else
let v_procflag = 'N';
end if;
insert into sf02p values (v_ksdm, v_policyno, v_insurant, v_assuredcode, v_classescode, v_currency, v_documentno,v_kindcode, v_order02no, v_mainamount, v_foreamount, v_departcode, v_acceptdepname, v_mancode, v_acceptopname, v_agentcode, v_refundname, v_procflag, v_serialno,null,null, v_sendtime, '0' );
let v_maxorder02no=v_maxorder02no+1;
end foreach;
-- insert into sf02pf
select count(*) into v_02f1count from sf02f1 where documentno = in_documentno;
let v_f1flag ='1';
if v_02f1count>0
then
foreach v_sf02f1Cursor for
select ksdm, policyno, classescode, clausetype, kindcode, documentno, order02no, mainamount, foreamount,
tcol1, tcol2, sffid
into v_ksdm, v_policyno, v_classescode, v_clausetype, v_kindcode, v_documentno, v_order02no,
v_mainamount, v_foreamount, v_tcol1, v_tcol2, v_sffid
from sf02f1
where documentno=in_documentno order by order02no
if v_f1flag ='1'
then
select max(order02no) into v_maxorder02no from sf02 where documentno=in_documentno;
let v_lastorder02no=v_order02no;
let v_order02no=v_maxorder02no+1;
let v_change02no=v_order02no;
elif v_order02no <> v_lastorder02no
then
select max(order02no) into v_maxorder02no from sf02pf where documentno=in_documentno;
let v_lastorder02no=v_order02no;
let v_order02no=v_maxorder02no+1;
let v_change02no=v_order02no;
elif v_order02no = v_lastorder02no
then
let v_order02no=v_change02no;
end if;
let v_mainamount=-v_mainamount;
let v_foreamount=-v_foreamount;
insert into sf02pf values(v_ksdm, v_policyno, v_classescode, v_clausetype, v_kindcode,
v_documentno, v_order02no, v_mainamount, v_foreamount,null,null, '0');
let v_f1flag = v_f1flag +1;
end foreach;
end if;
end Procedure;
分享到:
相关推荐
INFORMIX最新实用存储过程编写
informix中存储过程的写法,注意点
INFORMIX存储过程编写、INFORMIX存储过程开发指南
从零教你如何编写informix存储过程
Informix存储过程笔记 foreach ON EXCEPTION RAISE EXCEPTION
informix存储过程的建立,语法结构,控制语句,运行及其调试
达梦数据库6与sinoregal ds/informix存储过程对比
INFORMIX存储过程编写[归类].pdf
INFORMIX和ORACLE存储过程的异同.doc
INFORMIX与ORACLE存储过程语言之间的异同分析.pdf
Informix11.5 数据压缩 存储优化 大纲 § 不断增加的存储成本 § 什么是IDS压缩 § 管理
主要介绍 AIX上Informix的安装过程
针对才学informix
INFORMIX安装INFORMIX安装INFORMIX安装
informix数据库操作实例.pdf
协助你成功地管理Informix数据库服务器,基于Informix数据库进行二次开发,同时,也有一些章节是介绍如何进行应用开发的,如存储过程、触发器、分片的介绍等。在读过《IBM Informix11.x系统管理与开发指南》之后,你...
informix考题
4.3.2.1 将逻辑日志切换到下一个 onmode –l 4.3.3 执行完全检查点并释放逻辑日志文件 onmode –c 4.3.4 热备份Informix数据库 执行零级备份(到磁带库)$INFORMIXDIR/bin/onbar -b -w -L 0 4.3.5 备份逻辑...
关于informix数据的资料的收集,比较齐全。
第一章 数据库基础知识 4 第二章 INFORMIX 数据库系统概述 5 2.1 INFORMIX-ONLINE 联机数据库. 5 2.2 INFORMIX-SQL 数据库操纵语言. 5 2.3 INFORMIX-ISQL 数据库操纵工具. 5 2.4 INFORMIX-ESQL/C 数据库编程语言. 6 ...