--@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='
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='
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