22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE procedure SOF_dj_field_verify
@ask_interface integer=null, --该参数用来指示当前调用是否为接口询问(null:调用)
@djlxbs char(4)=null, --单据类型标识
@fieldname varchar(20)=null, --校验字段
@spid char(11)=null,
@spmch varchar(80)=null,
@shl dec(14,2)=null,
@chbdj dec(14,2)=null,
@dj dec(14,2)=null,
@hshj dec(14,2)=null,
@shlv dec(7,2)=null,
@xgdjbh char(11)=null,
@hw char(11)=null,
@pihao char(10)=null,
@jlgg int=null
as
/*******************************************************************************
***
为单据明细字段提供校验:返回列表,校验内容:
说明:1、该过程提供对单据明细字段的复杂校验,以根据实际需要来建立不同的校验模块,
2、每一校验模块的定位依赖于单据类型标识以及相关校验字段
3、校验模块的结构包括调用方法应答以及具体校验过程两部分
4、调用方法应答用来明确校验调用规范以及参数
5、校验执行结果信息返回结构为:[] as caption,[] as spmch,[] as holdup
6、holdup取值:0:提示不做拦截;>0提示并拦截;-1校验通过提示成功信息;<-1校
验通过隐含提示信息
7、校验模块框架:
--校验模块说明:
if (@djlxbs in ([单据类型1],[单据类型2],[,单据类型...])) and (@fieldname
=[校验字段名称])
begin
if @ask_interface is null
begin
--校验模块变量申明
declare @[变量]
if 满足条件
begin
--执行校验
end
else
begin
--校验通过
set @okmessage='校验成功提示信息'
set @holdup=-1 或 -2
goto ok_lab
end
end
else
begin
--返回调用方法
select [模块调用方法:如 exec SOF_dj_field_verify @djlxbs=:djlxbs,@fieldnam
e=:fieldname,@spbh=:spbh ' as proc_interface]
end
return 0 --跳出校验
end
8、校验模块注释必须增加
9、校验模块需要的参数在过程的参数列表中申明
10、校验模块中使用的变量必须申明在校验模块内部
********************************************************************************
***/
--定义参数
declare @okmessage varchar(255),
@holdup integer
set @holdup=-2
--采购入库单数量与开票数量进行校验
if (@djlxbs in ('120','','')) and (@fieldname='shl')
begin
declare @kpshl decimal(14,2)
if @ask_interface is null
begin
--执行校验
select @kpshl=sum(shl) from jxdjmx where djbh=@xgdjbh and spid=@spid
if @shl-@kpshl>0 --出库数量与开票数量之差大于0 时提示拦截,否则允许通过;
begin
select '入库数量不能大于开票数量,开票数量为:' as caption,convert(char(20),@kpshl),1 as holdup
end
else
goto ok_lab
end
else
begin
--返回调用方法
select 'exec SOF_dj_field_verify @djlxbs=:djlxbs,@fieldname=:fieldname,@spid
=:spid,@shl=:shl,@xgdjbh=:xgdjbh ' as proc_interface
end
return 0
end
--销售数量校验(到批次)
if (@djlxbs in ('212','','')) and (@fieldname='shl')
begin
declare @hwshl decimal(16,2)
declare @kpshl1 decimal(14,2)
declare @wckshl decimal(16,2)
declare @hjshl decimal(16,2)
declare @pcshl decimal(16,2)
declare @huowei char(20)
if @ask_interface is null
begin
set @hwshl=0
set @kpshl1=0
set @wckshl=0
set @hjshl=0
set @pcshl=0 --执行校验
select @pcshl=shl from sphwph(nolock) where spid=@spid and pihao=@pihao and hw=@hw
select @hwshl=shl from sphwph where spid=@spid and hw=@hw and pihao=@pihao
select @wckshl=sum(shl) from jxdjmxls where spid=@spid and hw=@hw and pihao=@pihao
select @huowei=huowname from huoweizl where hw=@hw
select @kpshl1=sum(shl) from tmp_dj_XSG212 where spid=@spid and hw=@hw and pihao=@pihao
select @jlgg=jlgg from spkfk where spid=@spid
set @hjshl=@hwshl-(isnull(@wckshl,0)+isnull(@kpshl1,0))
if ((@huowei='储备库') and (convert(int,@shl)%@jlgg<>0))
begin select '当前开票数量不是装量的倍数请注意' as caption,convert(char(20),@huowei),1 as holdup
end
else
if @hjshl-@shl<0 --开票数量与货位库存数量和开票未出库数量,正在开票数量之间进行校验提示拦截,否则允许通过;
begin
select '当前开票数量大于可销数量,可销数量为' as caption,convert(char(20),@hjshl),1 as holdup
end
else
goto ok_lab
end
else
begin
--返回调用方法
select 'exec SOF_dj_field_verify @djlxbs=:djlxbs,@fieldname=:fieldname,@spid
=:spid,@hw=:hw,@shl=:shl,@pihao=:pihao,@jlgg=:jlgg' as proc_interface
end
return 0
end
--出库价格低于最低售价校验
if (@djlxbs in ('212','','')) and (@fieldname='hshj')
begin
declare @zdsj dec(16,2)
if @ask_interface is null
begin
select @zdsj=zhdcbj from spkfk where spid=@spid
--执行校验
if @zdsj-@hshj>0 --最低售价限制大于含税价时提示拦截,否则允许通过;
begin
select '当前售价不允许小于最低售价,最低售价为' as caption,convert(char(20),@zdsj),1 as holdup
end
else
goto ok_lab
end
else
begin
--返回调用方法
select 'exec SOF_dj_field_verify @djlxbs=:djlxbs,@fieldname=:fieldname,@spid
=:spid,@hshj=:hshj ' as proc_interface
end
return 0
end
--内部调拨负库存拦截(字段校验)
if (@djlxbs in ('310','314','')) and (@fieldname='shl')
begin
declare @xswcshl decimal(16,2)
declare @xslskpshl decimal(16,2)
declare @dchwshl decimal(16,2)
declare @dkpshl decimal(14,2)
declare @dwckshl decimal(16,2)
declare @dhjshl decimal(16,2)
if @ask_interface is null
begin
set @dchwshl=0
set @dkpshl=0
set @dwckshl=0
set @dhjshl=0
--执行校验
select @xswcshl=sum(shl) from jxdjmx where is_zx='否' and is_zx<>'清'and spid=@spid and hw=@hw and pihao=@pihao and djbh like 'XSG%'
select @xslskpshl=sum(shl) from tmp_dj_XSG212 where spid=@spid and hw=@hw and pihao=@pihao
select @dchwshl=shl from sphwph where spid=@spid and hw=@hw and pihao=@pihao
select @dwckshl=sum(shl) from dbckmx where is_zx='否' and spid=@spid and dchw=@hw and pihao=@pihao AND DJBH LIKE 'KDB%'
if @djlxbs='310'
begin
select @dkpshl=sum(shl) from tmp_dj_KDB310 where spid=@spid and dchw=@hw and pihao=@pihao
end
if @djlxbs='314'
begin
select @dkpshl=sum(shl) from tmp_dj_cdb314 where spid=@spid and dchw=@hw and pihao=@pihao
set @dhjshl=@dchwshl-(isnull(@dwckshl,0)+isnull(@dkpshl,0)+isnull(@xslskpshl,0)+isnull(@xswcshl,0))
if ( (convert(int,@dhjshl)%@jlgg<>0))
begin select '当前开票数量不是装量的倍数请注意' as caption,convert(char(20),@dhjshl),1 as holdup
end
if @dhjshl-@shl<0 --开票数量与货位库存数量和开票未出库数量,正在开票数量之间进行校验提示拦截,否则允许通过;
begin
select '当前开票数量大于可销数量,可销数量为' as caption,convert(char(20),@dhjshl),1as holdup
end
else
goto ok_lab
end
end
else
begin
--返回调用方法
select 'exec SOF_dj_field_verify @djlxbs=:djlxbs,@fieldname=:fieldname,@spid
=:spid,@hw=:dchw,@shl=:shl,@pihao=:pihao' as proc_interface
end
return 0
end
--外调出库负库存拦截
if (@djlxbs in ('424','425','')) and (@fieldname='diaocsl')
begin
declare @wdcshl decimal(16,2)
declare @wdkpshl decimal(14,2)
declare @wdwckshl decimal(16,2)
declare @wdhjshl decimal(16,2)
if @ask_interface is null
begin
set @wdcshl=0
set @wdkpshl=0
set @wdwckshl=0
set @wdhjshl=0
--执行校验
select @wdcshl=shl from sphwph where spid=@spid and hw=@hw and pihao=@pihao
select @wdwckshl=sum(diaocsl) from dbkpcrkmx where is_zx='否' and spid=@spid
and hw=@hw and pihao=@pihao AND DJBH LIKE 'CKD%'
if @djlxbs='424'
begin
select @wdkpshl=sum(diaocsl) from tmp_dj_ckd424 where spid=@spid and
hw=@hw and pihao=@pihao
end
else
select @wdkpshl=sum(diaocsl) from tmp_dj_cck425 where spid=@spid and hw=
@hw and pihao=@pihao
set @wdhjshl=@wdcshl-(isnull(@wdwckshl,0)+isnull(@wdkpshl,0))
if @wdhjshl-@shl<0 --开票数量与货位库存数量和开票未出库数量,正在开票数量之间进行校验提示拦截,否则允许通过;
begin
select '当前开票数量大于可销数量,可销数量为' as caption,convert(char
(20),@wdhjshl), 1 as holdup
end
else
goto ok_lab
end
else
begin
--返回调用方法
select 'exec SOF_dj_field_verify @djlxbs=:djlxbs,@fieldname=:fieldname,@spid
=:spid,@hw=:hw,@shl=:diaocsl,@pihao=:pihao' as proc_interface
end
return 0
end