0
Former Member
Dec 10, 2009 at 06:53 PM

# Recalculation of purchase price

38 Views

Dear All,

someone can help me to re-calculate the avarage cost price with a SQL script ?

we have the following script, but this just calculate the profit based on the last purchase price.

Our goal is to calculate the profit on the avarage cost price.

Thank you,

Giuseppe

declare @DocEntry as int

declare @Costo as numeric(19, 6)

declare @ItemCode as nvarchar(20)

declare @PrezzoCosto as numeric(19,6)

declare @PrezzoVendita as numeric(19, 6)

declare @Quantita as numeric(19, 6)

declare @LineNum as int

declare @TotUtile as numeric(19, 6)

-- Dichiaro il cursore per scorre le fatture con prezzo di costo = 0

DECLARE fatture CURSOR FOR

SELECT OINV.DocEntry, INV1.GrossBuyPr, INV1.ItemCode, INV1.Price, INV1.Quantity, INV1.LineNum

FROM OINV INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry

ORDER BY OINV.DocEntry

OPEN fatture

FETCH NEXT FROM fatture

INTO @DocEntry, @Costo, @ItemCode, @PrezzoVendita, @Quantita, @LineNum

WHILE @@FETCH_STATUS = 0

begin

SELECT @PrezzoCosto = ISNULL(LastPurPrc, 0) FROM OITM WHERE ItemCode = @ItemCode

UPDATE INV1

GrssProfit = @Quantita * (@PrezzoVendita - @PrezzoCosto),

GrssProfSC = @Quantita * (@PrezzoVendita - @PrezzoCosto)

WHERE DocEntry = @DocEntry AND LineNum = @LineNum

FETCH NEXT FROM fatture

INTO @DocEntry, @Costo, @ItemCode, @PrezzoVendita, @Quantita, @LineNum

end

CLOSE fatture

DEALLOCATE fatture

DECLARE fatture CURSOR FOR

SELECT OINV.DocEntry

FROM OINV

ORDER BY OINV.DocEntry

OPEN fatture

FETCH NEXT FROM fatture

INTO @DocEntry

WHILE @@FETCH_STATUS = 0

begin

SELECT @TotUtile = SUM(GrssProfit) FROM INV1 WHERE DocEntry = @DocEntry

UPDATE OINV

SET GrosProfit = @TotUtile,

GrosProfSy = GrosProfit

WHERE DocEntry = @DocEntry

FETCH NEXT FROM fatture

INTO @DocEntry

end

CLOSE fatture

DEALLOCATE fatture