采购申请单审核时自动转成审核状态的采购单

CREATE TRIGGER [PORequestToPOOrder] ON [dbo].[PORequest]

FOR  UPDATE

AS

--采购申请单自动转至采购订单

BEGIN

  DECLARE @FCurrencyID INT,

    @FInterID INT,

    @FEntryID INT,

    @ROwID INT,

    @FBillno VARCHAR(50),

    @FSupplyID INT,

    @FStatus INT,

    @FNumber VARCHAR(50),

    @FLength INT,

    @FBrNO INT,

    @FZero DECIMAL(28,10)

  DECLARE @FMaxNum INT,

    @FCustID INT,

    @FSaleStyle INT,

    @FDeptID INT,

    @FEmpID INT,

    @FBillerID INT,

    @FMangerID INT,

    @FBillPOONo VARCHAR(50),

    @FBillCurNo INT,

    @FBillCurChar VARCHAR(50),

    @FCheckerID INT

 

  SELECT  @FCustID = 4017,@FBrNO = 0,@FZero = 0.000,@FDeptID = 112,

          @FSupplyID = 71471,@FEmpID = 432,@FBillerID = 16394,

          @FMangerID = 73751,@FCheckerID = 16531

  SELECT  @FBillNo = FBillNo,@FStatus = FStatus,@FInterid = FInterid

  FROM    inserted

  IF (@FStatus=1 AND Update(FStatus))

  BEGIN

     --1. 采购申请单中有吉利发物料

     --2. 采购订单没有吉利发此笔申请单物料

     IF Exists( Select 1 From PORequestEntry  Where FInterID = @FInterID AND FSupplyID=@FSupplyID) AND NOT Exists(SELECT 1 FROM POOrder a1 LEFT JOIN POOrderEntry b1 ON a1.FInterID=b1.FInterID WHERE a1.FSupplyID=@FSupplyID AND b1.FSourceBillNo=@FBillNo)

     BEGIN

       --获取采购订单FInterID 与FBillNo编号

       select @FMaxNum=FMaxNum+1 from ICMaxNum  where FTableName='POOrder'

       update ICMaxNum set FMaxNum=@FMaxNum where FTableName='POOrder'

       select @FBillCurNo=FCurNo from  ICBillNo where FBillID=71

       update ICBillNo set FCurNo=FCurNo+1 where FBillID=71

       update t_billcoderule set FProjectVal=@FBillCurNo+1 where fbilltypeid=71 and fclassindex=2

       select  @FBillPOONo=FProjectVal from t_billcoderule where fbilltypeid=71 and fclassindex=1

       select  @FLength=FLength from t_billcoderule where fbilltypeid=71 and fclassindex=2

       select  @FBillCurChar=right(cast(power(10,4) as varchar)+@FBillCurNo,@FLength)

       select  @FBillPOONo=@FBillPOONo+@FBillCurChar

       --采购订单主表

       INSERT INTO POOrder ( FInterID,FBillNo,FBrNo,FTranType,FCancellation,

                             FStatus,FSupplyID,Fdate,FCurrencyID,FCheckDate,

                             FMangerID,FDeptID,FEmpID,FBillerID,FExchangeRate,

                             FPOStyle,FRelateBrID,FMultiCheckLevel1,

                             FMultiCheckDate1,FMultiCheckLevel2,

                             FMultiCheckDate2,FMultiCheckLevel3,

                             FMultiCheckDate3,FMultiCheckLevel4,

                             FMultiCheckDate4,FMultiCheckLevel5,

                             FMultiCheckDate5,FMultiCheckLevel6,

                             FMultiCheckDate6,FSelTranType,FBrID,FExplanation,

                             FSettleID,FSettleDate,FAreaPS,FPOOrdBillNo,

                             FHeadSelfP0224,FHeadSelfP0225,FHeadSelfP0228 )

       VALUES ( @FMaxNum,@FBillPOONo,'0',71,0,0,@FSupplyID,GETDATE(),1,NULL,

                @FMangerID,@FDeptID,@FEmpID,@FBillerID,1,252,0,NULL,NULL,NULL,

                NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,70,0,'',0,

                GETDATE(),20302,'',NULL,'','' )

       --采购订单子表

       INSERT INTO POOrderEntry ( FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,

                                  FQty,FUnitID,FAuxQty,FSecCoefficient,Fdate,

                                  FSecQty,FAuxTaxPrice,FEntrySelfP0247,

                                  Fauxprice,FAmount,FCess,Fnote,FMapName,

                                  FMapNumber,FTaxRate,FAuxPriceDiscount,

                                  FTaxAmount,FAllAmount,FEntrySelfP0250,

                                  FEntrySelfP0251,FSourceBillNo,

                                  FSourceTranType,FSourceInterId,

                                  FSourceEntryID,FContractBillNo,

                                  FContractInterID,FContractEntryID,

                                  FAuxQtyInvoice,FQtyInvoice,FMrpLockFlag )

              SELECT  @FMaxNum,FEntryID,'0',FItemID,0,FQty,FUnitID,FQty,0,

                      FFetchTime,0,0,0,0,0,17,FUse,'','',0,0,0,0,'','',

                      @FBillNo,70,32971,13,'',0,0,0,0,0

              FROM    PORequestEntry

              WHERE   FInterID = @FInterID AND FSupplyID = @FSupplyID    

      --记录数与最大行号不一致, 行号重新排序

      SELECT @ROwID=COUNT(*)  FROM POOrderEntry  Where FInterID = @FMaxNum

      SELECT @FEntryID=MAX(FEntryID)  FROM POOrderEntry  Where FInterID = @FMaxNum

      IF (@ROwID<>@FEntryID)

      BEGIN

        SELECT @ROwID=1

        DECLARE POOrderEntryCursor CURSOR

        FOR

        SELECT  FEntryID

        FROM    POOrderEntry

        WHERE   FInterID = @FMaxNum

        ORDER BY FEntryID

        OPEN  POOrderEntryCursor

        FETCH NEXT FROM  POOrderEntryCursor  INTO @FEntryID

        WHILE @@FETCH_STATUS = 0

        BEGIN

          UPDATE  POOrderEntry

          SET     FEntryID = @ROwID

          WHERE   FInterID = @FMaxNum AND FEntryID = @FEntryID  

          FETCH NEXT FROM POOrderEntryCursor  INTO @FEntryID        

          SELECT  @ROwID = @ROwID + 1

        END

          CLOSE POOrderEntryCursor

          deallocate POOrderEntryCursor

        END

          --采购订单取价更新

          --1.取最低单价

          SELECT  MIN(b1.FPrice) AS FPrice,b1.FItemID,b1.FDiscount

          INTO    #minFprice

          FROM    t_SupplyEntry b1

                  RIGHT JOIN POOrderEntry a1 ON a1.FItemID = b1.FItemID

          WHERE   a1.FInterID = @FMaxNum AND b1.FUsed = 1

          GROUP BY b1.FItemID,b1.FDiscount         

          --2 更新采购订单单价

          UPDATE  a1

          SET     a1.FPrice = b1.FPrice / ( 1 + a1.FCess / 100 ),

                  a1.FAuxPrice = b1.FPrice / ( 1 + a1.FCess / 100 ),

                  a1.FAmount = b1.FPrice / ( 1 + a1.FCess / 100 ) * a1.FQty,

                  a1.FAuxTaxPrice = b1.FPrice,

                  a1.FAllamount = b1.FPrice * a1.FQty,

                  a1.FTaxAmount = b1.FPrice / ( 1 + a1.FCess / 100 ) * a1.FCess / 100 * a1.FQty,

                  a1.FAuxPriceDiscount = b1.FPrice * ( 1 - b1.FDiscount / 100 ),

                  a1.FPriceDiscount = b1.FPrice * ( 1 - b1.FDiscount / 100 ),

                  a1.FTaxPrice = b1.FPrice,

                  a1.FAmtDiscount = b1.FPrice / ( 1 + a1.FCess / 100 ) * a1.FQty * b1.FDiscount / 100,

                  a1.FEntrySelfP0247 = b1.FPrice * a1.FQty

          FROM    POOrderEntry a1

                  RIGHT JOIN #minFPrice b1 ON a1.FItemID = b1.FItemID

          WHERE   a1.FInterID = @FMaxNum --AND b1.FUsed =1

          --3. 删除临时表

          DROP TABLE #minFPrice 

         -- 审核采购订单

         UPDATE  POOrder SET FStatus=1,FCheckerID= @FCheckerID  WHERE FInterID=@FMaxNum

     END

  END

ENDCREATE TRIGGER [PORequestToPOOrder] ON [dbo].[PORequest]
FOR UPDATE
AS
--采购申请单自动转至采购订单
BEGIN
 DECLARE @FCurrencyID INT,
   @FInterID INT,
   @FEntryID INT,
   @ROwID INT,
   @FBillno VARCHAR(50),
   @FSupplyID INT,
   @FStatus INT,
   @FNumber VARCHAR(50),
   @FLength INT,
   @FBrNO INT,
   @FZero DECIMAL(28,10)
 DECLARE @FMaxNum INT,
   @FCustID INT,
   @FSaleStyle INT,
   @FDeptID INT,
   @FEmpID INT,
   @FBillerID INT,
   @FMangerID INT,
   @FBillPOONo VARCHAR(50),
   @FBillCurNo INT,
   @FBillCurChar VARCHAR(50),
   @FCheckerID INT
 
 SELECT @FCustID = 4017,@FBrNO = 0,@FZero = 0.000,@FDeptID = 112,
         @FSupplyID = 71471,@FEmpID = 432,@FBillerID = 16394,
         @FMangerID = 73751,@FCheckerID = 16531
 SELECT @FBillNo = FBillNo,@FStatus = FStatus,@FInterid = FInterid
 FROM    inserted
 IF (@FStatus=1 AND Update(FStatus))
 BEGIN
    --1. 采购申请单中有吉利发物料
     --2. 采购订单没有吉利发此笔申请单物料
     IF Exists(Select 1 From PORequestEntry Where FInterID= @FInterID AND FSupplyID=@FSupplyID)AND NOT Exists(SELECT 1 FROM POOrder a1LEFT JOIN POOrderEntry b1ON a1.FInterID=b1.FInterIDWHERE a1.FSupplyID=@FSupplyID AND b1.FSourceBillNo=@FBillNo)
    BEGIN
      --获取采购订单 FInterID 与 FBillNo编号
       select @FMaxNum=FMaxNum+1 from ICMaxNum where FTableName='POOrder'
      update ICMaxNumset FMaxNum=@FMaxNum where FTableName='POOrder'
      select @FBillCurNo=FCurNofrom  ICBillNowhere FBillID=71
      update ICBillNoset FCurNo=FCurNo+1 where FBillID=71
      update t_billcoderuleset FProjectVal=@FBillCurNo+1 where fbilltypeid=71 and fclassindex=2
      select @FBillPOONo=FProjectValfrom t_billcoderulewhere fbilltypeid=71 and fclassindex=1
      select @FLength=FLengthfrom t_billcoderulewhere fbilltypeid=71 and fclassindex=2
      select @FBillCurChar=right(cast(power(10,4)as varchar)+@FBillCurNo,@FLength)
      select @FBillPOONo=@FBillPOONo+@FBillCurChar
      --采购订单主表
       INSERT INTO POOrder ( FInterID,FBillNo,FBrNo,FTranType,FCancellation,
                             FStatus,FSupplyID,Fdate,FCurrencyID,FCheckDate,
                             FMangerID,FDeptID,FEmpID,FBillerID,FExchangeRate,
                             FPOStyle,FRelateBrID,FMultiCheckLevel1,
                             FMultiCheckDate1,FMultiCheckLevel2,
                             FMultiCheckDate2,FMultiCheckLevel3,
                             FMultiCheckDate3,FMultiCheckLevel4,
                             FMultiCheckDate4,FMultiCheckLevel5,
                             FMultiCheckDate5,FMultiCheckLevel6,
                             FMultiCheckDate6,FSelTranType,FBrID,FExplanation,
                             FSettleID,FSettleDate,FAreaPS,FPOOrdBillNo,
                             FHeadSelfP0224,FHeadSelfP0225,FHeadSelfP0228 )
      VALUES (@FMaxNum,@FBillPOONo,'0',71,0,0,@FSupplyID,GETDATE(),1,NULL,
               @FMangerID,@FDeptID,@FEmpID,@FBillerID,1,252,0,NULL,NULL,NULL,
               NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,70,0,'',0,
               GETDATE(),20302,'',NULL,'','' )
      --采购订单子表
       INSERT INTO POOrderEntry ( FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,
                                  FQty,FUnitID,FAuxQty,FSecCoefficient,Fdate,
                                  FSecQty,FAuxTaxPrice,FEntrySelfP0247,
                                  Fauxprice,FAmount,FCess,Fnote,FMapName,
                                  FMapNumber,FTaxRate,FAuxPriceDiscount,
                                  FTaxAmount,FAllAmount,FEntrySelfP0250,
                                  FEntrySelfP0251,FSourceBillNo,
                                  FSourceTranType,FSourceInterId,
                                  FSourceEntryID,FContractBillNo,
                                  FContractInterID,FContractEntryID,
                                  FAuxQtyInvoice,FQtyInvoice,FMrpLockFlag )
             SELECT @FMaxNum,FEntryID,'0',FItemID,0,FQty,FUnitID,FQty,0,
                      FFetchTime,0,0,0,0,0,17,FUse,'','',0,0,0,0,'','',
                     @FBillNo,70,32971,13,'',0,0,0,0,0
             FROM    PORequestEntry
             WHERE   FInterID= @FInterID AND FSupplyID= @FSupplyID    
     --记录数与最大行号不一致, 行号重新排序
      SELECT @ROwID=COUNT(*) FROM POOrderEntry Where FInterID= @FMaxNum
     SELECT @FEntryID=MAX(FEntryID) FROM POOrderEntry Where FInterID= @FMaxNum
     IF (@ROwID<>@FEntryID)
     BEGIN
       SELECT @ROwID=1
       DECLARE POOrderEntryCursorCURSOR
       FOR
       SELECT  FEntryID
       FROM    POOrderEntry
       WHERE   FInterID= @FMaxNum
       ORDER BY FEntryID
       OPEN  POOrderEntryCursor
       FETCH NEXT FROM  POOrderEntryCursor INTO @FEntryID
       WHILE @@FETCH_STATUS = 0
       BEGIN
         UPDATE  POOrderEntry
         SET     FEntryID= @ROwID
         WHERE   FInterID= @FMaxNum AND FEntryID= @FEntryID  
         FETCH NEXT FROM POOrderEntryCursor INTO @FEntryID        
         SELECT @ROwID = @ROwID + 1
       END
         CLOSE POOrderEntryCursor
         deallocate POOrderEntryCursor
       END
         --采购订单取价更新
          --1.取最低单价
          SELECT MIN(b1.FPrice)AS FPrice,b1.FItemID,b1.FDiscount
         INTO    #minFprice
         FROM    t_SupplyEntry b1
                 RIGHT JOIN POOrderEntry a1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum AND b1.FUsed= 1
         GROUP BY b1.FItemID,b1.FDiscount         
         --2 更新采购订单单价
          UPDATE  a1
         SET     a1.FPrice= b1.FPrice/ (1 + a1.FCess/ 100 ),
                  a1.FAuxPrice= b1.FPrice/ (1 + a1.FCess/ 100 ),
                  a1.FAmount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty,
                  a1.FAuxTaxPrice= b1.FPrice,
                  a1.FAllamount= b1.FPrice* a1.FQty,
                  a1.FTaxAmount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FCess/ 100 * a1.FQty,
                  a1.FAuxPriceDiscount= b1.FPrice* (1 - b1.FDiscount/ 100 ),
                  a1.FPriceDiscount= b1.FPrice* (1 - b1.FDiscount/ 100 ),
                  a1.FTaxPrice= b1.FPrice,
                  a1.FAmtDiscount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty* b1.FDiscount/ 100,
                  a1.FEntrySelfP0247= b1.FPrice* a1.FQty
         FROM    POOrderEntry a1
                 RIGHT JOIN #minFPrice b1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum --AND b1.FUsed =1
         --3. 删除临时表
          DROP TABLE #minFPrice 
        -- 审核采购订单
         UPDATE  POOrderSET FStatus=1,FCheckerID= @FCheckerID WHERE FInterID=@FMaxNum
    END
 END
END

CREATE TRIGGER [PORequestToPOOrder] ON [dbo].[PORequest]
FOR UPDATE
AS
--采购申请单自动转至采购订单
BEGIN
 DECLARE @FCurrencyID INT,
   @FInterID INT,
   @FEntryID INT,
   @ROwID INT,
   @FBillno VARCHAR(50),
   @FSupplyID INT,
   @FStatus INT,
   @FNumber VARCHAR(50),
   @FLength INT,
   @FBrNO INT,
   @FZero DECIMAL(28,10)
 DECLARE @FMaxNum INT,
   @FCustID INT,
   @FSaleStyle INT,
   @FDeptID INT,
   @FEmpID INT,
   @FBillerID INT,
   @FMangerID INT,
   @FBillPOONo VARCHAR(50),
   @FBillCurNo INT,
   @FBillCurChar VARCHAR(50),
   @FCheckerID INT
 
 SELECT @FCustID = 4017,@FBrNO = 0,@FZero = 0.000,@FDeptID = 112,
         @FSupplyID = 71471,@FEmpID = 432,@FBillerID = 16394,
         @FMangerID = 73751,@FCheckerID = 16531
 SELECT @FBillNo = FBillNo,@FStatus = FStatus,@FInterid = FInterid
 FROM    inserted
 IF (@FStatus=1 AND Update(FStatus))
 BEGIN
    --1. 采购申请单中有吉利发物料
     --2. 采购订单没有吉利发此笔申请单物料
     IF Exists(Select 1 From PORequestEntry Where FInterID= @FInterID AND FSupplyID=@FSupplyID)AND NOT Exists(SELECT 1 FROM POOrder a1LEFT JOIN POOrderEntry b1ON a1.FInterID=b1.FInterIDWHERE a1.FSupplyID=@FSupplyID AND b1.FSourceBillNo=@FBillNo)
    BEGIN
      --获取采购订单 FInterID 与 FBillNo编号
       select @FMaxNum=FMaxNum+1 from ICMaxNum where FTableName='POOrder'
      update ICMaxNumset FMaxNum=@FMaxNum where FTableName='POOrder'
      select @FBillCurNo=FCurNofrom  ICBillNowhere FBillID=71
      update ICBillNoset FCurNo=FCurNo+1 where FBillID=71
      update t_billcoderuleset FProjectVal=@FBillCurNo+1 where fbilltypeid=71 and fclassindex=2
      select @FBillPOONo=FProjectValfrom t_billcoderulewhere fbilltypeid=71 and fclassindex=1
      select @FLength=FLengthfrom t_billcoderulewhere fbilltypeid=71 and fclassindex=2
      select @FBillCurChar=right(cast(power(10,4)as varchar)+@FBillCurNo,@FLength)
      select @FBillPOONo=@FBillPOONo+@FBillCurChar
      --采购订单主表
       INSERT INTO POOrder ( FInterID,FBillNo,FBrNo,FTranType,FCancellation,
                             FStatus,FSupplyID,Fdate,FCurrencyID,FCheckDate,
                             FMangerID,FDeptID,FEmpID,FBillerID,FExchangeRate,
                             FPOStyle,FRelateBrID,FMultiCheckLevel1,
                             FMultiCheckDate1,FMultiCheckLevel2,
                             FMultiCheckDate2,FMultiCheckLevel3,
                             FMultiCheckDate3,FMultiCheckLevel4,
                             FMultiCheckDate4,FMultiCheckLevel5,
                             FMultiCheckDate5,FMultiCheckLevel6,
                             FMultiCheckDate6,FSelTranType,FBrID,FExplanation,
                             FSettleID,FSettleDate,FAreaPS,FPOOrdBillNo,
                             FHeadSelfP0224,FHeadSelfP0225,FHeadSelfP0228 )
      VALUES (@FMaxNum,@FBillPOONo,'0',71,0,0,@FSupplyID,GETDATE(),1,NULL,
               @FMangerID,@FDeptID,@FEmpID,@FBillerID,1,252,0,NULL,NULL,NULL,
               NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,70,0,'',0,
               GETDATE(),20302,'',NULL,'','' )
      --采购订单子表
       INSERT INTO POOrderEntry ( FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,
                                  FQty,FUnitID,FAuxQty,FSecCoefficient,Fdate,
                                  FSecQty,FAuxTaxPrice,FEntrySelfP0247,
                                  Fauxprice,FAmount,FCess,Fnote,FMapName,
                                  FMapNumber,FTaxRate,FAuxPriceDiscount,
                                  FTaxAmount,FAllAmount,FEntrySelfP0250,
                                  FEntrySelfP0251,FSourceBillNo,
                                  FSourceTranType,FSourceInterId,
                                  FSourceEntryID,FContractBillNo,
                                  FContractInterID,FContractEntryID,
                                  FAuxQtyInvoice,FQtyInvoice,FMrpLockFlag )
             SELECT @FMaxNum,FEntryID,'0',FItemID,0,FQty,FUnitID,FQty,0,
                      FFetchTime,0,0,0,0,0,17,FUse,'','',0,0,0,0,'','',
                     @FBillNo,70,32971,13,'',0,0,0,0,0
             FROM    PORequestEntry
             WHERE   FInterID= @FInterID AND FSupplyID= @FSupplyID    
     --记录数与最大行号不一致, 行号重新排序
      SELECT @ROwID=COUNT(*) FROM POOrderEntry Where FInterID= @FMaxNum
     SELECT @FEntryID=MAX(FEntryID) FROM POOrderEntry Where FInterID= @FMaxNum
     IF (@ROwID<>@FEntryID)
     BEGIN
       SELECT @ROwID=1
       DECLARE POOrderEntryCursorCURSOR
       FOR
       SELECT  FEntryID
       FROM    POOrderEntry
       WHERE   FInterID= @FMaxNum
       ORDER BY FEntryID
       OPEN  POOrderEntryCursor
       FETCH NEXT FROM  POOrderEntryCursor INTO @FEntryID
       WHILE @@FETCH_STATUS = 0
       BEGIN
         UPDATE  POOrderEntry
         SET     FEntryID= @ROwID
         WHERE   FInterID= @FMaxNum AND FEntryID= @FEntryID  
         FETCH NEXT FROM POOrderEntryCursor INTO @FEntryID        
         SELECT @ROwID = @ROwID + 1
       END
         CLOSE POOrderEntryCursor
         deallocate POOrderEntryCursor
       END
         --采购订单取价更新
          --1.取最低单价
          SELECT MIN(b1.FPrice)AS FPrice,b1.FItemID,b1.FDiscount
         INTO    #minFprice
         FROM    t_SupplyEntry b1
                 RIGHT JOIN POOrderEntry a1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum AND b1.FUsed= 1
         GROUP BY b1.FItemID,b1.FDiscount         
         --2 更新采购订单单价
          UPDATE  a1
         SET     a1.FPrice= b1.FPrice/ (1 + a1.FCess/ 100 ),
                  a1.FAuxPrice= b1.FPrice/ (1 + a1.FCess/ 100 ),
                  a1.FAmount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty,
                  a1.FAuxTaxPrice= b1.FPrice,
                  a1.FAllamount= b1.FPrice* a1.FQty,
                  a1.FTaxAmount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FCess/ 100 * a1.FQty,
                  a1.FAuxPriceDiscount= b1.FPrice* (1 - b1.FDiscount/ 100 ),
                  a1.FPriceDiscount= b1.FPrice* (1 - b1.FDiscount/ 100 ),
                  a1.FTaxPrice= b1.FPrice,
                  a1.FAmtDiscount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty* b1.FDiscount/ 100,
                  a1.FEntrySelfP0247= b1.FPrice* a1.FQty
         FROM    POOrderEntry a1
                 RIGHT JOIN #minFPrice b1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum --AND b1.FUsed =1
         --3. 删除临时表
          DROP TABLE #minFPrice 
        -- 审核采购订单
         UPDATE  POOrderSET FStatus=1,FCheckerID= @FCheckerID WHERE FInterID=@FMaxNum
    END
 END
END

CREATE TRIGGER [PORequestToPOOrder] ON [dbo].[PORequest]
FOR UPDATE
AS
--采购申请单自动转至采购订单
BEGIN
 DECLARE @FCurrencyID INT,
   @FInterID INT,
   @FEntryID INT,
   @ROwID INT,
   @FBillno VARCHAR(50),
   @FSupplyID INT,
   @FStatus INT,
   @FNumber VARCHAR(50),
   @FLength INT,
   @FBrNO INT,
   @FZero DECIMAL(28,10)
 DECLARE @FMaxNum INT,
   @FCustID INT,
   @FSaleStyle INT,
   @FDeptID INT,
   @FEmpID INT,
   @FBillerID INT,
   @FMangerID INT,
   @FBillPOONo VARCHAR(50),
   @FBillCurNo INT,
   @FBillCurChar VARCHAR(50),
   @FCheckerID INT
 
 SELECT @FCustID = 4017,@FBrNO = 0,@FZero = 0.000,@FDeptID = 112,
         @FSupplyID = 71471,@FEmpID = 432,@FBillerID = 16394,
         @FMangerID = 73751,@FCheckerID = 16531
 SELECT @FBillNo = FBillNo,@FStatus = FStatus,@FInterid = FInterid
 FROM    inserted
 IF (@FStatus=1 AND Update(FStatus))
 BEGIN
    --1. 采购申请单中有吉利发物料
     --2. 采购订单没有吉利发此笔申请单物料
     IF Exists(Select 1 From PORequestEntry Where FInterID= @FInterID AND FSupplyID=@FSupplyID)AND NOT Exists(SELECT 1 FROM POOrder a1LEFT JOIN POOrderEntry b1ON a1.FInterID=b1.FInterIDWHERE a1.FSupplyID=@FSupplyID AND b1.FSourceBillNo=@FBillNo)
    BEGIN
      --获取采购订单 FInterID 与 FBillNo编号
       select @FMaxNum=FMaxNum+1 from ICMaxNum where FTableName='POOrder'
      update ICMaxNumset FMaxNum=@FMaxNum where FTableName='POOrder'
      select @FBillCurNo=FCurNofrom  ICBillNowhere FBillID=71
      update ICBillNoset FCurNo=FCurNo+1 where FBillID=71
      update t_billcoderuleset FProjectVal=@FBillCurNo+1 where fbilltypeid=71 and fclassindex=2
      select @FBillPOONo=FProjectValfrom t_billcoderulewhere fbilltypeid=71 and fclassindex=1
      select @FLength=FLengthfrom t_billcoderulewhere fbilltypeid=71 and fclassindex=2
      select @FBillCurChar=right(cast(power(10,4)as varchar)+@FBillCurNo,@FLength)
      select @FBillPOONo=@FBillPOONo+@FBillCurChar
      --采购订单主表
       INSERT INTO POOrder ( FInterID,FBillNo,FBrNo,FTranType,FCancellation,
                             FStatus,FSupplyID,Fdate,FCurrencyID,FCheckDate,
                             FMangerID,FDeptID,FEmpID,FBillerID,FExchangeRate,
                             FPOStyle,FRelateBrID,FMultiCheckLevel1,
                             FMultiCheckDate1,FMultiCheckLevel2,
                             FMultiCheckDate2,FMultiCheckLevel3,
                             FMultiCheckDate3,FMultiCheckLevel4,
                             FMultiCheckDate4,FMultiCheckLevel5,
                             FMultiCheckDate5,FMultiCheckLevel6,
                             FMultiCheckDate6,FSelTranType,FBrID,FExplanation,
                             FSettleID,FSettleDate,FAreaPS,FPOOrdBillNo,
                             FHeadSelfP0224,FHeadSelfP0225,FHeadSelfP0228 )
      VALUES (@FMaxNum,@FBillPOONo,'0',71,0,0,@FSupplyID,GETDATE(),1,NULL,
               @FMangerID,@FDeptID,@FEmpID,@FBillerID,1,252,0,NULL,NULL,NULL,
               NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,70,0,'',0,
               GETDATE(),20302,'',NULL,'','' )
      --采购订单子表
       INSERT INTO POOrderEntry ( FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,
                                  FQty,FUnitID,FAuxQty,FSecCoefficient,Fdate,
                                  FSecQty,FAuxTaxPrice,FEntrySelfP0247,
                                  Fauxprice,FAmount,FCess,Fnote,FMapName,
                                  FMapNumber,FTaxRate,FAuxPriceDiscount,
                                  FTaxAmount,FAllAmount,FEntrySelfP0250,
                                  FEntrySelfP0251,FSourceBillNo,
                                  FSourceTranType,FSourceInterId,
                                  FSourceEntryID,FContractBillNo,
                                  FContractInterID,FContractEntryID,
                                  FAuxQtyInvoice,FQtyInvoice,FMrpLockFlag )
             SELECT @FMaxNum,FEntryID,'0',FItemID,0,FQty,FUnitID,FQty,0,
                      FFetchTime,0,0,0,0,0,17,FUse,'','',0,0,0,0,'','',
                     @FBillNo,70,32971,13,'',0,0,0,0,0
             FROM    PORequestEntry
             WHERE   FInterID= @FInterID AND FSupplyID= @FSupplyID    
     --记录数与最大行号不一致, 行号重新排序
      SELECT @ROwID=COUNT(*) FROM POOrderEntry Where FInterID= @FMaxNum
     SELECT @FEntryID=MAX(FEntryID) FROM POOrderEntry Where FInterID= @FMaxNum
     IF (@ROwID<>@FEntryID)
     BEGIN
       SELECT @ROwID=1
       DECLARE POOrderEntryCursorCURSOR
       FOR
       SELECT  FEntryID
       FROM    POOrderEntry
       WHERE   FInterID= @FMaxNum
       ORDER BY FEntryID
       OPEN  POOrderEntryCursor
       FETCH NEXT FROM  POOrderEntryCursor INTO @FEntryID
       WHILE @@FETCH_STATUS = 0
       BEGIN
         UPDATE  POOrderEntry
         SET     FEntryID= @ROwID
         WHERE   FInterID= @FMaxNum AND FEntryID= @FEntryID  
         FETCH NEXT FROM POOrderEntryCursor INTO @FEntryID        
         SELECT @ROwID = @ROwID + 1
       END
         CLOSE POOrderEntryCursor
         deallocate POOrderEntryCursor
       END
         --采购订单取价更新
          --1.取最低单价
          SELECT MIN(b1.FPrice)AS FPrice,b1.FItemID,b1.FDiscount
         INTO    #minFprice
         FROM    t_SupplyEntry b1
                 RIGHT JOIN POOrderEntry a1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum AND b1.FUsed= 1
         GROUP BY b1.FItemID,b1.FDiscount         
         --2 更新采购订单单价
          UPDATE  a1
         SET     a1.FPrice= b1.FPrice/ (1 + a1.FCess/ 100 ),
                  a1.FAuxPrice= b1.FPrice/ (1 + a1.FCess/ 100 ),
                  a1.FAmount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty,
                  a1.FAuxTaxPrice= b1.FPrice,
                  a1.FAllamount= b1.FPrice* a1.FQty,
                  a1.FTaxAmount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FCess/ 100 * a1.FQty,
                  a1.FAuxPriceDiscount= b1.FPrice* (1 - b1.FDiscount/ 100 ),
                  a1.FPriceDiscount= b1.FPrice* (1 - b1.FDiscount/ 100 ),
                  a1.FTaxPrice= b1.FPrice,
                  a1.FAmtDiscount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty* b1.FDiscount/ 100,
                  a1.FEntrySelfP0247= b1.FPrice* a1.FQty
         FROM    POOrderEntry a1
                 RIGHT JOIN #minFPrice b1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum --AND b1.FUsed =1
         --3. 删除临时表
          DROP TABLE #minFPrice 
        -- 审核采购订单
         UPDATE  POOrderSET FStatus=1,FCheckerID= @FCheckerID WHERE FInterID=@FMaxNum
    END
 END
END

CREATE TRIGGER [PORequestToPOOrder] ON [dbo].[PORequest]
FOR UPDATE
AS
--采购申请单自动转至采购订单
BEGIN
 DECLARE @FCurrencyID INT,
   @FInterID INT,
   @FEntryID INT,
   @ROwID INT,
   @FBillno VARCHAR(50),
   @FSupplyID INT,
   @FStatus INT,
   @FNumber VARCHAR(50),
   @FLength INT,
   @FBrNO INT,
   @FZero DECIMAL(28,10)
 DECLARE @FMaxNum INT,
   @FCustID INT,
   @FSaleStyle INT,
   @FDeptID INT,
   @FEmpID INT,
   @FBillerID INT,
   @FMangerID INT,
   @FBillPOONo VARCHAR(50),
   @FBillCurNo INT,
   @FBillCurChar VARCHAR(50),
   @FCheckerID INT
 
 SELECT @FCustID = 4017,@FBrNO = 0,@FZero = 0.000,@FDeptID = 112,
         @FSupplyID = 71471,@FEmpID = 432,@FBillerID = 16394,
         @FMangerID = 73751,@FCheckerID = 16531
 SELECT @FBillNo = FBillNo,@FStatus = FStatus,@FInterid = FInterid
 FROM    inserted
 IF (@FStatus=1 AND Update(FStatus))
 BEGIN
    --1. 采购申请单中有吉利发物料
     --2. 采购订单没有吉利发此笔申请单物料
     IF Exists(Select 1 From PORequestEntry Where FInterID= @FInterID AND FSupplyID=@FSupplyID)AND NOT Exists(SELECT 1 FROM POOrder a1LEFT JOIN POOrderEntry b1ON a1.FInterID=b1.FInterIDWHERE a1.FSupplyID=@FSupplyID AND b1.FSourceBillNo=@FBillNo)
    BEGIN
      --获取采购订单 FInterID 与 FBillNo编号
       select @FMaxNum=FMaxNum+1 from ICMaxNum where FTableName='POOrder'
      update ICMaxNumset FMaxNum=@FMaxNum where FTableName='POOrder'
      select @FBillCurNo=FCurNofrom  ICBillNowhere FBillID=71
      update ICBillNoset FCurNo=FCurNo+1 where FBillID=71
      update t_billcoderuleset FProjectVal=@FBillCurNo+1 where fbilltypeid=71 and fclassindex=2
      select @FBillPOONo=FProjectValfrom t_billcoderulewhere fbilltypeid=71 and fclassindex=1
      select @FLength=FLengthfrom t_billcoderulewhere fbilltypeid=71 and fclassindex=2
      select @FBillCurChar=right(cast(power(10,4)as varchar)+@FBillCurNo,@FLength)
      select @FBillPOONo=@FBillPOONo+@FBillCurChar
      --采购订单主表
       INSERT INTO POOrder ( FInterID,FBillNo,FBrNo,FTranType,FCancellation,
                             FStatus,FSupplyID,Fdate,FCurrencyID,FCheckDate,
                             FMangerID,FDeptID,FEmpID,FBillerID,FExchangeRate,
                             FPOStyle,FRelateBrID,FMultiCheckLevel1,
                             FMultiCheckDate1,FMultiCheckLevel2,
                             FMultiCheckDate2,FMultiCheckLevel3,
                             FMultiCheckDate3,FMultiCheckLevel4,
                             FMultiCheckDate4,FMultiCheckLevel5,
                             FMultiCheckDate5,FMultiCheckLevel6,
                             FMultiCheckDate6,FSelTranType,FBrID,FExplanation,
                             FSettleID,FSettleDate,FAreaPS,FPOOrdBillNo,
                             FHeadSelfP0224,FHeadSelfP0225,FHeadSelfP0228 )
      VALUES (@FMaxNum,@FBillPOONo,'0',71,0,0,@FSupplyID,GETDATE(),1,NULL,
               @FMangerID,@FDeptID,@FEmpID,@FBillerID,1,252,0,NULL,NULL,NULL,
               NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,70,0,'',0,
               GETDATE(),20302,'',NULL,'','' )
      --采购订单子表
       INSERT INTO POOrderEntry ( FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,
                                  FQty,FUnitID,FAuxQty,FSecCoefficient,Fdate,
                                  FSecQty,FAuxTaxPrice,FEntrySelfP0247,
                                  Fauxprice,FAmount,FCess,Fnote,FMapName,
                                  FMapNumber,FTaxRate,FAuxPriceDiscount,
                                  FTaxAmount,FAllAmount,FEntrySelfP0250,
                                  FEntrySelfP0251,FSourceBillNo,
                                  FSourceTranType,FSourceInterId,
                                  FSourceEntryID,FContractBillNo,
                                  FContractInterID,FContractEntryID,
                                  FAuxQtyInvoice,FQtyInvoice,FMrpLockFlag )
             SELECT @FMaxNum,FEntryID,'0',FItemID,0,FQty,FUnitID,FQty,0,
                      FFetchTime,0,0,0,0,0,17,FUse,'','',0,0,0,0,'','',
                     @FBillNo,70,32971,13,'',0,0,0,0,0
             FROM    PORequestEntry
             WHERE   FInterID= @FInterID AND FSupplyID= @FSupplyID    
     --记录数与最大行号不一致, 行号重新排序
      SELECT @ROwID=COUNT(*) FROM POOrderEntry Where FInterID= @FMaxNum
     SELECT @FEntryID=MAX(FEntryID) FROM POOrderEntry Where FInterID= @FMaxNum
     IF (@ROwID<>@FEntryID)
     BEGIN
       SELECT @ROwID=1
       DECLARE POOrderEntryCursorCURSOR
       FOR
       SELECT  FEntryID
       FROM    POOrderEntry
       WHERE   FInterID= @FMaxNum
       ORDER BY FEntryID
       OPEN  POOrderEntryCursor
       FETCH NEXT FROM  POOrderEntryCursor INTO @FEntryID
       WHILE @@FETCH_STATUS = 0
       BEGIN
         UPDATE  POOrderEntry
         SET     FEntryID= @ROwID
         WHERE   FInterID= @FMaxNum AND FEntryID= @FEntryID  
         FETCH NEXT FROM POOrderEntryCursor INTO @FEntryID        
         SELECT @ROwID = @ROwID + 1
       END
         CLOSE POOrderEntryCursor
         deallocate POOrderEntryCursor
       END
         --采购订单取价更新
          --1.取最低单价
          SELECT MIN(b1.FPrice)AS FPrice,b1.FItemID,b1.FDiscount
         INTO    #minFprice
         FROM    t_SupplyEntry b1
                 RIGHT JOIN POOrderEntry a1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum AND b1.FUsed= 1
         GROUP BY b1.FItemID,b1.FDiscount         
         --2 更新采购订单单价
          UPDATE  a1
         SET     a1.FPrice= b1.FPrice/ (1 + a1.FCess/ 100 ),
                  a1.FAuxPrice= b1.FPrice/ (1 + a1.FCess/ 100 ),
                  a1.FAmount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty,
                  a1.FAuxTaxPrice= b1.FPrice,
                  a1.FAllamount= b1.FPrice* a1.FQty,
                  a1.FTaxAmount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FCess/ 100 * a1.FQty,
                  a1.FAuxPriceDiscount= b1.FPrice* (1 - b1.FDiscount/ 100 ),
                  a1.FPriceDiscount= b1.FPrice* (1 - b1.FDiscount/ 100 ),
                  a1.FTaxPrice= b1.FPrice,
                  a1.FAmtDiscount= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty* b1.FDiscount/ 100,
                  a1.FEntrySelfP0247= b1.FPrice* a1.FQty
         FROM    POOrderEntry a1
                 RIGHT JOIN #minFPrice b1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum --AND b1.FUsed =1
         --3. 删除临时表
          DROP TABLE #minFPrice 
        -- 审核采购订单
         UPDATE  POOrderSET FStatus=1,FCheckerID= @FCheckerID WHERE FInterID=@FMaxNum
    END
 END
END