[5系]采購訂單數量不能大于(請購數量-已訂數量)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****** Object: ?Trigger dbo.p_orderdetail_ai ? ?Script Date: 2013-8-26 10:51:52 ******/

/****** Object: ?Trigger dbo.p_orderdetail_ai ? ?Script Date: 2013-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 2009-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 2009-06-29 ?采購訂單引用銷售訂單不回填任何引用數量字段
? ? ? update s_orderdetail set purrequestqty=isnull(purrequestqty,0)+@unitquantity?
? ? ? ? where billid=@referbillid and itemno=@referitemno ?*/
? ? if (@referbilltype = 12) --heyao 20130820 新需要要求采購訂單也回填銷售訂單
? ? 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 2020-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

?

總結

以上是生活随笔為你收集整理的【速达软件】【速达5000】需求采购订单数量不能大于(请购数量-已订数量)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。