Stock Report with FIFO, Open SO and PO's and Pricelists

[sean_martin_fl]

Hi Guys,

I am having an issue with some SQL that I am going to use in a Crystal Report that will show the following as a Stock Report, fields I need are as follows;

OITM.ItemCode

OITM.ItemName

OITM.ItmsGrpNam

I then need the average price of all the fifo layers for the ItemCode (OINM?)

OITM.LastPurPrc

MIN(ITM1.Price) (this needs to be the lowest price in all price lists for OITM.ItemCode

ITM1.Price (where OITM.PriceList = 3 for this OITM.ItemCode)

ITM1.Price (where OITM.PriceList = 6 for this OITM.ItemCode)

SUM(All Open PO Quantities for this OITM.ItemCode)

SUM(All Open PO LineTotals for this OITM.ItemCode)

SUM(All Open SO Quantities for this OITM.ItemCode)

SUM(All Open SO LineTotals for this OITM.ItemCode)

Hope the above makes sense, I have put this together so far but am getting a lot of wrong data/duplicated data, could someone very kindly help me out:

SELECT T1.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam], T1.[U_Group], T1.[U_House], T1.[U_Range], T1.[U_Gender], SUM(T9.InQty) AS 'FIFO Qty', SUM(T9.Price) AS 'FIFO Price',
T1.[LastPurPrc]AS 'Last Purchase Price', T5.[Price] AS 'RRP Price',T5.[Price] AS 'Export Price', MIN(T3.[Price])AS 'Min Price',
SUM(T7.[Quantity]) AS 'PO Qty Total', SUM (T7.[LineTotal]) AS 'PO Value Total', SUM(T7.[Quantity]) AS 'SO Qty Total', SUM (T7.[LineTotal]) AS 'SO Value Total'

FROM [dbo].[OITM]  T1
INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod
INNER JOIN ITM1 T3 ON T1.ItemCode = T3.ItemCode
LEFT OUTER JOIN ITM1 T5 ON T1.ItemCode = T3.ItemCode AND T3.PriceList = '3'
LEFT OUTER JOIN ITM1 T6 ON T1.ItemCode = T3.ItemCode AND T3.PriceList = '6'
LEFT OUTER JOIN POR1 T7 ON T1.ItemCode = T7.ItemCode AND T7.LineStatus = 'O'
LEFT OUTER JOIN RDR1 T8 ON T1.ItemCode = T8.ItemCode AND T8.LineStatus = 'O'
LEFT OUTER JOIN OINM T9 ON T1.ItemCode = T9.ItemCode

GROUP BY T1.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam], T1.[U_Group], T1.[U_House], T1.[U_Range], T1.[U_Gender],
T5.Price, T1.[LastPurPrc]

Many many thanks in advance

[kothandaraman_nagarajan]

Hi Sean Martin,

Are you ordering the PO directly from SO?

Thanks & Regards,

Nagarajan

[sean_martin_fl]

Hi Naga,

Thanks for thereply, it will not always be the case that back to back SO to PO's will be done, it will probably only be done that way occassionaly.

Kind regards

Sean