[5系]采购订单数量不能大于(请购数量-已订数量)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/****** Object: Trigger dbo.p_orderdetail_ai Script Date: -8-26 10:51:52 ******/
/****** Object: Trigger dbo.p_orderdetail_ai Script Date: -2-20 9:02:28 ******/
ALTER trigger [p_orderdetail_ai] on [dbo].[p_orderdetail] for insert
as
declare @referbilltype t_integer,
@referbillid t_keyid,
@referitemno t_keyid,
@refergoodsid t_integer,
@unitid t_integer,
@unitquantity t_quantity,
@extqty t_quantity,
@closed t_integer,
@referqty t_quantity, -- referqty被引用数量
@prunitquantity t_quantity, ---明细自定义1=请购单数量
@userdef1 t_userdef,
@itemno t_keyid,
@msg t_remark --增加抛出异常报错参数
begin
set nocount on
declare p_orderdetail_ai_cr cursor local forward_only static optimistic for
select n.referbilltype, n.referbillid, n.referitemno,n.goodsid, n.unitid,n.unitquantity, n.extqty ,p.closed,p.referqty,p.unitquantity ,n.itemno
from inserted n -- p_orderdetail n
left join p_requestdetail p on n.referbilltype=5 and n.referbillid=p.billid and n.referitemno=p.itemno
open p_orderdetail_ai_cr
fetch next from p_orderdetail_ai_cr
into @referbilltype, @referbillid, @referitemno,@refergoodsid, @unitid,@unitquantity, @extqty,@closed,@referqty,@prunitquantity,@itemno
while (@@fetch_status = 0)
begin
if(@closed=1)
begin
exec throwexception 'billcloseerror'
return
end
-- lsl modify -06-12 检查引用的单据/记录是否有效
if(@referbilltype<>12 and @referbilltype<>16)
begin
exec checkbillgoods @referbilltype,@referbillid,@referitemno,@refergoodsid
--更新引用次数和引用数量
exec modifybillreferqty 1,@referbilltype,@referbillid,@referitemno,@unitid,@unitquantity,@extqty
end
/* else lsl modify -06-29 采购订单引用销售订单不回填任何引用数量字段
update s_orderdetail set purrequestqty=isnull(purrequestqty,0)+@unitquantity
where billid=@referbillid and itemno=@referitemno */
if (@referbilltype = 12) --heyao 0820 新需要要求采购订单也回填销售订单
begin
exec checkbillgoods @referbilltype,@referbillid,@referitemno,@refergoodsid
update s_orderdetail set purrequestqty=isnull(purrequestqty,0)+dbo.fn_getunitqty(goodsid,@unitquantity,@unitid,unitid)
where billid=@referbillid and itemno=@referitemno
end
---------------------------------------------------------------------
--林13632349867 -07-08 采购订单引用请购单,数量不能大于请购单数量
if(@referbilltype=5 )
begin
exec checkbillgoods @referbilltype,@referbillid,@referitemno,@refergoodsid
-- select billid,unitquantity,referqty,greferqty,greferextqty from p_requestdetail where billid=8
if(@unitquantity>(@prunitquantity-@referqty))
begin
exec checkbillgoods @referbilltype,@referbillid,@referitemno,@refergoodsid
--declare @msg2 VARCHAR;
set @msg = '第 '+cast(@itemno-1 as VARCHAR)+' 行数量超标，请购数量 '+cast(Convert(decimal(20,2),@prunitquantity) as varchar)+' 其中已下订购数量 '+cast(Convert(decimal(20,2),@referqty) as varchar)+' ，不得超过 '+cast(Convert(decimal(20,2),@prunitquantity-@referqty) as varchar)
exec throwexception @msg
break
return
end
update p_orderdetail set userdef1=@prunitquantity,userdef2=@referqty+@unitquantity where referbillid=@referbillid and referitemno=@referitemno and goodsid=@refergoodsid
end
---------------------------------------------------------------------
fetch next from p_orderdetail_ai_cr
into @referbilltype, @referbillid, @referitemno,@refergoodsid, @unitid,@unitquantity, @extqty,@closed,@referqty,@prunitquantity,@itemno
end
close p_orderdetail_ai_cr
deallocate p_orderdetail_ai_cr
end
