单价变更时采购单中单价为0的自动校正

--@FPrice为未税单价

CREATE TRIGGER poorderPriceUpdate ON t_SupplyEntry

FOR  UPDATE

AS

BEGIN

  DECLARE @FSupID INT,

    @FItemID INT,

    @FUsed INT,

    @FPrice DECIMAL(28,10),

    @FDiscount DECIMAL(28,10),

    @FDisableDate DATETIME,

    @FQuoteTime DATETIME

  SELECT  @FUsed = FUsed,@FSupID = FSupID,@FItemID = FItemID,@FPrice = FPrice,

          @FDiscount = FDiscount,@FDisableDate = FDisableDate,

          @FQuoteTime = FQuoteTime

  FROM    Inserted

  IF  UPDATE(FUsed)  AND @FUsed = 1 AND @FQuoteTime <= GETDATE() AND @FDisableDate > GETDATE()

  BEGIN

    --select a1.fqty,a1.fcommitQty,a1.FStockQty,  --订货数量,到货数量,入库数量

    --a1.fcess,  --税率

    --a1.fprice,a1.fauxprice,a1.famount,--未税单价,辅助单价,未税金额

    --a1.fauxtaxprice,a1.fallamount,a1.ftaxamount, --含税单价, 价税合计,税额

    --a1.fauxpricediscount,a1.fpricediscount,a1.ftaxprice,  --实际含税单价, 含税单价,含税单价

    --a1.FEntrySelfP0247,a1.FAmtDiscount  --含税单价,折扣额

    --from poorderentry a1

    --left join poorder b1 on a1.finterid=b1.finterid

    --where b1.fbillno='0911M0533'

    UPDATE  a1

    SET     a1.FPrice = @FPrice,a1.FAuxPrice = @FPrice,

            a1.FAmount = @FPrice * a1.FQty,

            a1.FAuxTaxPrice = @FPrice * ( 1 + a1.FCess / 100 ),

            a1.FAllamount = @FPrice * ( 1 + a1.FCess / 100 ) * a1.FQty,

            a1.FTaxAmount = @FPrice * a1.FCess / 100 * a1.FQty,

            a1.FAuxPriceDiscount = @FPrice * ( 1 + a1.FCess / 100 ) * ( 1 - @FDiscount / 100 ),

            a1.FPriceDiscount = @FPrice * ( 1 + a1.FCess / 100 ) * ( 1 - @FDiscount / 100 ),

            a1.FTaxPrice = @FPrice * ( 1 + a1.FCess / 100 ),

            a1.FAmtDiscount = @FPrice * a1.FQty * @FDiscount / 100

    FROM    POOrderEntry a1,

            POOrder b1

    WHERE   a1.FPrice = 0 AND b1.FSupplyID = @FSupID AND a1.FItemID = @FItemID AND b1.FInterID = a1.FInterID

  END

END

 

@FPrice 为含税单价

--------------------------------------------------

CREATE TRIGGER poorderPriceUpdate ON t_SupplyEntry

FOR  UPDATE

AS

BEGIN

  DECLARE @FSupID INT,

    @FItemID INT,

    @FUsed INT,

    @FPrice DECIMAL(28,10),

    @FDiscount DECIMAL(28,10),

    @FDisableDate DATETIME,

    @FQuoteTime DATETIME

  SELECT  @FUsed = FUsed,@FSupID = FSupID,@FItemID = FItemID,@FPrice = FPrice,

          @FDiscount = FDiscount,@FDisableDate = FDisableDate,

          @FQuoteTime = FQuoteTime

  FROM    Inserted

  IF  UPDATE(FUsed)  AND @FUsed = 1 AND @FQuoteTime <= GETDATE() AND @FDisableDate > GETDATE()

    BEGIN

    --@FPrice 为含税单价

    --select a1.fqty,a1.fcommitQty,a1.FStockQty,  --订货数量,到货数量,入库数量

    --a1.fcess,  --税率

    --a1.fprice,a1.fauxprice,a1.famount,--未税单价,辅助单价,未税金额

    --a1.fauxtaxprice,a1.fallamount,a1.ftaxamount, --含税单价, 价税合计,税额

    --a1.fauxpricediscount,a1.fpricediscount,a1.ftaxprice,  --实际含税单价, 含税单价,含税单价

    --a1.FEntrySelfP0247,a1.FAmtDiscount  --含税单价,折扣额

    --from poorderentry a1

    --left join poorder b1 on a1.finterid=b1.finterid

    --where b1.fbillno='0911M0533'

    UPDATE  a1

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

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

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

            a1.FAuxTaxPrice = @FPrice,a1.FAllamount = @FPrice * a1.FQty,

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

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

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

            a1.FTaxPrice = @FPrice,

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

    FROM    POOrderEntry a1,

            POOrder b1

    WHERE   a1.FPrice = 0 AND b1.FSupplyID = @FSupID AND a1.FItemID = @FItemID

            AND b1.FInterID = a1.FInterID

  END

END