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

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

FOR UPDATE

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

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

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 INTOVALUES (@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 INTOSELECT @FMaxNum,FEntryID,'0',FItemID,0,FQty,FUnitID,FQty,00,0,0,0,0,17,FUse,'','',0,0,0,0,'',''@FBillNo,70,32971,13,'',0,0,0,0,0
             FROMWHERE   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)ASINTO    #minFprice
         FROMRIGHT JOIN POOrderEntry a1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum AND b1.FUsed= 1
         GROUP BY--2 更新采购订单单价
          UPDATE a1
         SET     a1.FPrice= b1.FPrice/ (1 + a1.FCess/ 100= b1.FPrice/ (1 + a1.FCess/ 100= b1.FPrice/ (1 + a1.FCess/ 100 )*== b1.FPrice*= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FCess/ 100 *= b1.FPrice* (1 - b1.FDiscount/ 100= b1.FPrice* (1 - b1.FDiscount/ 100== b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty* b1.FDiscount/ 100= b1.FPrice* a1.FQty
         FROMRIGHT 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 INTOVALUES (@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 INTOSELECT @FMaxNum,FEntryID,'0',FItemID,0,FQty,FUnitID,FQty,00,0,0,0,0,17,FUse,'','',0,0,0,0,'',''@FBillNo,70,32971,13,'',0,0,0,0,0
             FROMWHERE   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)ASINTO    #minFprice
         FROMRIGHT JOIN POOrderEntry a1ON a1.FItemID= b1.FItemID
         WHERE   a1.FInterID= @FMaxNum AND b1.FUsed= 1
         GROUP BY--2 更新采购订单单价
          UPDATE a1
         SET     a1.FPrice= b1.FPrice/ (1 + a1.FCess/ 100= b1.FPrice/ (1 + a1.FCess/ 100= b1.FPrice/ (1 + a1.FCess/ 100 )*== b1.FPrice*= b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FCess/ 100 *= b1.FPrice* (1 - b1.FDiscount/ 100= b1.FPrice* (1 - b1.FDiscount/ 100== b1.FPrice/ (1 + a1.FCess/ 100 )* a1.FQty* b1.FDiscount/ 100= b1.FPrice* a1.FQty
         FROMRIGHT 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

posted on 2011-08-22 16:55 jshchg 阅读(1437) 评论(0) 收藏举报

刷新页面返回顶部

jshchg的学习轨迹

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

导航