on 10-22-2010 11:41 AM
I want to create a query showing Actual component Cost , Actual Product Costs, where there is a Variance... I cant seem to locate the table names. I have gone on summary on the production order still cant see the fields.
Hi.......
OIGN, IGN1 for Receipt and OIGE and IGE1 for Issue. Try these table and build the proper relation ship which gives you the variance in production.
Also check OWOR and WOR1 for Production Order
Issue-Receipt=Variance.......
Hope this will help you........
Regards,
Rahul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I have managed to create this query am seeking help on how I can subtract Actual Product Cost & Actual Component Cost
SELECT DISTINCT T0.[DocNum], T0.[PlannedQty], T0.[CmpltQty], T1.[Dscription], T0.[PostDate], (T1.[StockPrice]T0.[CmpltQty]) as 'Actual Product Cost', sum(T2.[LineTotal]) as 'Actual Component Cost' FROM OWOR T0 LEFT JOIN IGN1 T1 ON T0.[DocNum]=T1.[BaseRef] LEFT JOIN IGE1 T2 ON T0.[DocNum]=T2.[BaseRef] WHERE T0.[PostDate] >=[%0] AND T0.[PostDate] <=[%1] GROUP BY T0.[DocNum], T0.[PlannedQty], T0.[CmpltQty], T1.[Dscription], T0.[PostDate], (T1.[StockPrice]T0.[CmpltQty])
Thanks,
Mwelwa
Try this:
SELECT DISTINCT T0.DocNum, T0.PlannedQty, T0.CmpltQty, T1.Dscription, T0.PostDate, (T1.StockPrice*T0.CmpltQty) as 'Actual Product Cost', -sum(T2.TransValue) as 'Actual Component Cost'
FROM OWOR T0
LEFT JOIN IGN1 T1 ON T0.DocNum=T1.BaseRef
INNER JOIN OINM T2 ON T0.DocEntry=T2.AppObjAbs AND OutQTY>0
WHERE T0.PostDate >=[%0\] AND T0.PostDate <=[%1\]
GROUP BY T0.DocNum, T0.PlannedQty, T0.CmpltQty, T1.Dscription, T0.PostDate, (T1.StockPrice*T0.CmpltQty)
Thanks,
Gordon
Updated to include difference:
SELECT DISTINCT T0.DocNum, T0.PlannedQty, T0.CmpltQty, T1.Dscription, T0.PostDate, (T1.StockPrice*T0.CmpltQty) as 'Actual Product Cost', -sum(T2.TransValue) as 'Actual Component Cost',
(T1.StockPrice*T0.CmpltQty) +sum(T2.TransValue) as Difference
FROM OWOR T0
LEFT JOIN IGN1 T1 ON T0.DocNum=T1.BaseRef
INNER JOIN OINM T2 ON T0.DocEntry=T2.AppObjAbs AND OutQTY>0
WHERE T0.PostDate >=[%0\] AND T0.PostDate <=[%1\]
GROUP BY T0.DocNum, T0.PlannedQty, T0.CmpltQty, T1.Dscription, T0.PostDate, (T1.StockPrice*T0.CmpltQty)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.