create or replace procedure p_fp_pjzxkchz(as_fpDjxh in varchar2,
as_swjgDm in varchar2,
as_ckDm in varchar2,
as_swryDm in varchar2) is
/*
存储过程说明:此存储过程用于对通过"回调"或"删除调拨单"进入票据中心库存的同类票(入库单价相同)进行汇总合并*/
ls_fpzlDm t_fp_fpdjmx.fpzl_dm%type;
ls_fpDm t_fp_fpdjmx.fp_dm%type;
ls_fpQshm t_fp_fpdjmx.fp_qshm%type;
ls_fpZzhm t_fp_fpdjmx.fp_zzhm%type;
ls_sldwDm t_fp_fpdjmx.sldw_dm%type;
ls_sl t_fp_fpdjmx.sl%type;
ls_sl1 t_fp_fpdjmx.sl%type;
ls_sl2 t_fp_fpdjmx.sl%type;
ls_dj1 t_fp_fpdjmx.dj%type;
ls_dj2 t_fp_fpdjmx.dj%type;
ls_dj t_fp_fpdjmx.dj%type;
qshm t_fp_fpdjmx.fp_qshm%type;
zzhm t_fp_fpdjmx.fp_zzhm%type;
cursor c_fpdjmx is
select * from t_fp_fpdjmx t where t.fpdj_xh = as_fpDjxh;
begin
--循环刚入库的票
for cur in c_fpdjmx loop
ls_fpzlDm := cur.fpzl_dm;
ls_fpDm := cur.fp_dm;
ls_fpQshm := cur.fp_qshm;
ls_fpZzhm := cur.fp_zzhm;
ls_sldwDm := cur.sldw_dm;
ls_sl := cur.sl;
--1查找库存中是否有与该号码相连的票,并将其合并
--1.1先找与该起始号码相连的
select sum(kc.sl)
into ls_sl1
from t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_zzhm = ls_fpQshm - 1
and kc.sldw_dm = ls_sldwDm;
--1.2找与该终止号码相连的
select sum(kc.sl)
into ls_sl2
from t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_qshm = ls_fpZzhm + 1
and kc.sldw_dm = ls_sldwDm;
--2分三段分别找出其入库价格 select nvl(djmx.dj, 0)
into ls_dj1
from t_fp_fprkd rkd, t_fp_fpdjmx djmx
where rkd.jsdw_dm = as_swjgDm
and rkd.jsck_dm = as_ckDm
and rkd.fprkd_xh = djmx.fpdj_xh
and djmx.fpdj_lx = '0'
and djmx.fpzl_dm = ls_fpzlDm
and djmx.fp_dm = ls_fpDm
and djmx.fp_qshm <= qshm
and djmx.fp_zzhm >= ls_fpQshm - 1;指到这一行错误。
as_swjgDm in varchar2,
as_ckDm in varchar2,
as_swryDm in varchar2) is
/*
存储过程说明:此存储过程用于对通过"回调"或"删除调拨单"进入票据中心库存的同类票(入库单价相同)进行汇总合并*/
ls_fpzlDm t_fp_fpdjmx.fpzl_dm%type;
ls_fpDm t_fp_fpdjmx.fp_dm%type;
ls_fpQshm t_fp_fpdjmx.fp_qshm%type;
ls_fpZzhm t_fp_fpdjmx.fp_zzhm%type;
ls_sldwDm t_fp_fpdjmx.sldw_dm%type;
ls_sl t_fp_fpdjmx.sl%type;
ls_sl1 t_fp_fpdjmx.sl%type;
ls_sl2 t_fp_fpdjmx.sl%type;
ls_dj1 t_fp_fpdjmx.dj%type;
ls_dj2 t_fp_fpdjmx.dj%type;
ls_dj t_fp_fpdjmx.dj%type;
qshm t_fp_fpdjmx.fp_qshm%type;
zzhm t_fp_fpdjmx.fp_zzhm%type;
cursor c_fpdjmx is
select * from t_fp_fpdjmx t where t.fpdj_xh = as_fpDjxh;
begin
--循环刚入库的票
for cur in c_fpdjmx loop
ls_fpzlDm := cur.fpzl_dm;
ls_fpDm := cur.fp_dm;
ls_fpQshm := cur.fp_qshm;
ls_fpZzhm := cur.fp_zzhm;
ls_sldwDm := cur.sldw_dm;
ls_sl := cur.sl;
--1查找库存中是否有与该号码相连的票,并将其合并
--1.1先找与该起始号码相连的
select sum(kc.sl)
into ls_sl1
from t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_zzhm = ls_fpQshm - 1
and kc.sldw_dm = ls_sldwDm;
--1.2找与该终止号码相连的
select sum(kc.sl)
into ls_sl2
from t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_qshm = ls_fpZzhm + 1
and kc.sldw_dm = ls_sldwDm;
--2分三段分别找出其入库价格 select nvl(djmx.dj, 0)
into ls_dj1
from t_fp_fprkd rkd, t_fp_fpdjmx djmx
where rkd.jsdw_dm = as_swjgDm
and rkd.jsck_dm = as_ckDm
and rkd.fprkd_xh = djmx.fpdj_xh
and djmx.fpdj_lx = '0'
and djmx.fpzl_dm = ls_fpzlDm
and djmx.fp_dm = ls_fpDm
and djmx.fp_qshm <= qshm
and djmx.fp_zzhm >= ls_fpQshm - 1;指到这一行错误。
create or replace procedure p_fp_pjzxkchz(as_fpDjxh in varchar2,
as_swjgDm in varchar2,
as_ckDm in varchar2,
as_swryDm in varchar2) is
/*
存储过程说明:此存储过程用于对通过"回调"或"删除调拨单"进入票据中心库存的同类票(入库单价相同)进行汇总合并*/
ls_fpzlDm t_fp_fpdjmx.fpzl_dm%type;
ls_fpDm t_fp_fpdjmx.fp_dm%type;
ls_fpQshm t_fp_fpdjmx.fp_qshm%type;
ls_fpZzhm t_fp_fpdjmx.fp_zzhm%type;
ls_sldwDm t_fp_fpdjmx.sldw_dm%type;
ls_sl t_fp_fpdjmx.sl%type;
ls_sl1 t_fp_fpdjmx.sl%type;
ls_sl2 t_fp_fpdjmx.sl%type;
ls_dj1 t_fp_fpdjmx.dj%type;
ls_dj2 t_fp_fpdjmx.dj%type;
ls_dj t_fp_fpdjmx.dj%type;
qshm t_fp_fpdjmx.fp_qshm%type;
zzhm t_fp_fpdjmx.fp_zzhm%type;
cursor c_fpdjmx is
select * from t_fp_fpdjmx t where t.fpdj_xh = as_fpDjxh;
begin
--循环刚入库的票
for cur in c_fpdjmx loop
ls_fpzlDm := cur.fpzl_dm;
ls_fpDm := cur.fp_dm;
ls_fpQshm := cur.fp_qshm;
ls_fpZzhm := cur.fp_zzhm;
ls_sldwDm := cur.sldw_dm;
ls_sl := cur.sl;
--1查找库存中是否有与该号码相连的票,并将其合并
--1.1先找与该起始号码相连的
select sum(kc.sl)
into ls_sl1
from t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_zzhm = ls_fpQshm - 1
and kc.sldw_dm = ls_sldwDm;
--1.2找与该终止号码相连的
select sum(kc.sl)
into ls_sl2
from t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_qshm = ls_fpZzhm + 1
and kc.sldw_dm = ls_sldwDm;
--2分三段分别找出其入库价格 select nvl(djmx.dj, 0)
into ls_dj1
from t_fp_fprkd rkd, t_fp_fpdjmx djmx
where rkd.jsdw_dm = as_swjgDm
and rkd.jsck_dm = as_ckDm
and rkd.fprkd_xh = djmx.fpdj_xh
and djmx.fpdj_lx = '0'
and djmx.fpzl_dm = ls_fpzlDm
and djmx.fp_dm = ls_fpDm
and djmx.fp_qshm <= qshm
and djmx.fp_zzhm >= ls_fpQshm - 1;
select nvl(djmx.dj, 0)
into ls_dj2
from t_fp_fprkd rkd, t_fp_fpdjmx djmx
where rkd.jsdw_dm = as_swjgDm
and rkd.jsck_dm = as_ckDm
and rkd.fprkd_xh = djmx.fpdj_xh
and djmx.fpdj_lx = '0'
and djmx.fpzl_dm = ls_fpzlDm
and djmx.fp_dm = ls_fpDm
and djmx.fp_qshm <= ls_fpZzhm + 1
and djmx.fp_zzhm >= zzhm;
select nvl(djmx.dj, 0)
into ls_dj
from t_fp_fprkd rkd, t_fp_fpdjmx djmx
where rkd.jsdw_dm = as_swjgDm
and rkd.jsck_dm = as_ckDm
and rkd.fprkd_xh = djmx.fpdj_xh
and djmx.fpdj_lx = '0'
and djmx.fpzl_dm = ls_fpzlDm
and djmx.fp_dm = ls_fpDm
and djmx.fp_qshm <= ls_fpQshm
and djmx.fp_zzhm >= ls_fpZzhm;
--3.根据条件(入库单价相同)合并库存
--3.1前后都有与这相连的票
if ls_sl1 >= 1 and ls_sl2 >= 1 then
--3.1.1三段入库单价相同可同时合并在一起
--删除前后两段
if ls_dj = ls_dj1 and ls_dj = ls_dj2 then
DELETE FROM t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and (kc.fp_zzhm = ls_fpQshm - 1 or kc.fp_qshm = ls_fpZzhm + 1)
and kc.sldw_dm = ls_sldwDm;
--修改刚入库的号码段
UPDATE t_fp_swjgfpkc kc
SET kc.SL = ls_sl + LS_SL1 + LS_SL2,
kc.fp_qshm = qshm,
kc.fp_zzhm = zzhm
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
AND kc.fp_qshm = ls_fpQshm
AND kc.fp_zzhm = ls_fpZzhm;
--3.1.2 只有与起始号码相连的一段可以合并
elsif ls_dj = ls_dj1 then
DELETE FROM t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_zzhm = ls_fpQshm - 1
and kc.sldw_dm = ls_sldwDm;
UPDATE t_fp_swjgfpkc kc
SET kc.SL = ls_sl + LS_SL1, kc.fp_qshm = qshm, kc.fp_zzhm = zzhm
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
AND kc.fp_qshm = qshm
AND kc.fp_zzhm = ls_fpZzhm;
--3.1.3 只有与终止号码相连的一段可以合并
elsif ls_dj = ls_dj2 then
DELETE FROM t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_qshm = ls_fpZzhm + 1
and kc.sldw_dm = ls_sldwDm;
UPDATE t_fp_swjgfpkc kc
SET kc.SL = ls_sl + LS_SL2, kc.fp_qshm = qshm, kc.fp_zzhm = zzhm
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
AND kc.fp_qshm = ls_fpQshm
AND kc.fp_zzhm = zzhm;
end if;
--3.2只有与起始号码相连的票
elsif ls_sl1 >= 1 and (ls_sl2 < 1 or ls_sl2 is null) then
--3.2.1 可以合并
if ls_dj = ls_dj1 then
DELETE FROM t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_zzhm = ls_fpQshm - 1
and kc.sldw_dm = ls_sldwDm;
UPDATE t_fp_swjgfpkc kc
SET kc.SL = ls_sl + LS_SL1, kc.fp_qshm = qshm, kc.fp_zzhm = zzhm
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
AND kc.fp_qshm = qshm
AND kc.fp_zzhm = ls_fpZzhm;
end if;
--3.3只有与终止号码相连的票
elsif (ls_sl1 < 1 or ls_sl1 is null) and ls_sl2 >= 1 then
--3.3.1 可以合并
if ls_dj = ls_dj2 then
DELETE FROM t_fp_swjgfpkc kc
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
and kc.fp_qshm = ls_fpZzhm + 1
and kc.sldw_dm = ls_sldwDm;
UPDATE t_fp_swjgfpkc kc
SET kc.SL = ls_sl + LS_SL2, kc.fp_qshm = qshm, kc.fp_zzhm = zzhm
where kc.swjg_dm = as_swjgDm
and kc.ck_dm = as_ckDm
and kc.swry_dm = as_swryDm
and kc.fpzl_dm = ls_fpzlDm
and kc.fp_dm = ls_fpDm
AND kc.fp_qshm = ls_fpQshm
AND kc.fp_zzhm = zzhm;
end if;
end if;
end loop;
end p_fp_pjzxkchz;
-- Create table
create table DB_FPGL.T_FP_FPDJMX
(
FPDJ_XH VARCHAR2(21) not null,
MX_XH NUMBER(4) not null,
FPDJ_LX VARCHAR2(1) not null,
FPZL_DM VARCHAR2(12) not null,
FP_QSHM VARCHAR2(10),
FP_ZZHM VARCHAR2(10),
SLDW_DM VARCHAR2(4),
SL NUMBER(8) not null,
DJ NUMBER(18,6),
JE NUMBER(18,2),
KBP_SL NUMBER(8),
KBP_QSHM VARCHAR2(10),
KBP_ZZHM VARCHAR2(10),
FPDJMX_ZT VARCHAR2(2),
KCCCYY VARCHAR2(2),
TZNR_BJ CHAR(1),
LR_SJ TIMESTAMP(6),
XG_SJ TIMESTAMP(6),
FP_DM VARCHAR2(12),
MXJLQR_BJ VARCHAR2(1),
WSH VARCHAR2(20)
)
tablespace TS_ZGXT
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table DB_FPGL.T_FP_FPDJMX
is '发出错误调整单,发票挂失申请,它们的明细信息大致相同,泛化出发票单据明细这个object';
-- Add comments to the columns
comment on column DB_FPGL.T_FP_FPDJMX.FPDJ_XH
is '发票单据编号';
comment on column DB_FPGL.T_FP_FPDJMX.MX_XH
is '明细序号';
comment on column DB_FPGL.T_FP_FPDJMX.FPDJ_LX
is '发票单据种类:‘0’ :发票入库单;‘1’ :发票调拨单;‘2’ :发票发出错误调整单;‘4’ :库存差错信息;''a'':纳税人发票挂失;''b'':税务机关发票挂失';
comment on column DB_FPGL.T_FP_FPDJMX.FPZL_DM
is '发票种类代码';
comment on column DB_FPGL.T_FP_FPDJMX.FP_QSHM
is '发票起始号码';
comment on column DB_FPGL.T_FP_FPDJMX.FP_ZZHM
is '发票终止号码';
comment on column DB_FPGL.T_FP_FPDJMX.SLDW_DM
is '本数或者份数';
comment on column DB_FPGL.T_FP_FPDJMX.SL
is '数量';
comment on column DB_FPGL.T_FP_FPDJMX.DJ
is '单价';
comment on column DB_FPGL.T_FP_FPDJMX.JE
is '金额';
comment on column DB_FPGL.T_FP_FPDJMX.KBP_SL
is '“其中空白票”。对于发票挂失,发票缴销等而言的。';
comment on column DB_FPGL.T_FP_FPDJMX.KBP_QSHM
is '空白票起始号码';
comment on column DB_FPGL.T_FP_FPDJMX.KBP_ZZHM
is '空白票终止号码';
comment on column DB_FPGL.T_FP_FPDJMX.FPDJMX_ZT
is '对于验销,填用、作废、空白交回、遗失或损毁。';
comment on column DB_FPGL.T_FP_FPDJMX.KCCCYY
is '库存差错原因';
comment on column DB_FPGL.T_FP_FPDJMX.TZNR_BJ
is '‘0’ :调整前内容;‘1’ :调整后内容;';
comment on column DB_FPGL.T_FP_FPDJMX.LR_SJ
is '录入时间';
comment on column DB_FPGL.T_FP_FPDJMX.XG_SJ
is '修改时间';
comment on column DB_FPGL.T_FP_FPDJMX.FP_DM
is '发票代码';
comment on column DB_FPGL.T_FP_FPDJMX.MXJLQR_BJ
is '明细记录确认标记';
comment on column DB_FPGL.T_FP_FPDJMX.WSH
is '文书号';
-- Create/Recreate primary, unique and foreign key constraints
alter table DB_FPGL.T_FP_FPDJMX
add constraint PK_T_FP_FPDJMX primary key (FPDJ_XH, MX_XH, FPDJ_LX)
using index
tablespace TS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index DB_FPGL.IDX_FP_FPDJMX_WSH on DB_FPGL.T_FP_FPDJMX (WSH)
tablespace TS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
两个字段都是字符型,里面存的是纯数字吗,有没有包含其他字母符号或是空格
fp_zzhm 和ls_fpQshm 里面都是数字
但是如果是djmx.fp_zzhm = ls_fpQshm - 1这样没错,不知道为什么?
隐式转换中出错的吧
最好保持数据类型的一致
比如这句可以改为djmx.fp_zzhm>=to_char(to_number(ls_fpqshm)-1)