【速达软件】【速达5000】需求采购订单数量不能大于(请购数量-已订数量)

[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