Skip to Content
0
Jun 13, 2018 at 02:07 PM

Production Order Summary Details Query

1505 Views

Hi Experts

I need a query to display the summary tab details as a result. I found a query on here which I have modified to suit my requirements, but I need help to add one more requirement added to the query to display in the results

The query is below

SELECT DISTINCT 
T0.DocNum as 'Production Order', 
T0.[DueDate] as 'Build Date',
T0.OriginNum as 'Sales Order',
T0.U_CustName as 'Sales Partner',
T0.PlannedQty as 'Planned Qty', 
T0.ItemCode as 'Product Code', 
T1.Dscription as 'Product Description', 
(T1.StockPrice*T0.CmpltQty) as 'Actual Product Cost', -sum(T2.TransValue) as 'Actual Item Component Cost',
(T1.StockPrice*T0.CmpltQty) +sum(T2.TransValue) as 'Actual Additional 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.DueDate >=[%0\] AND T0.DueDate <=[%1\] AND T0.Type = 'S'


GROUP BY T0.DocNum, T0.U_CustName, T0.[DueDate], T0.OriginNum, T0.PlannedQty, T0.ItemCode, T0.CmpltQty, T1.Dscription, T0.PostDate, (T1.StockPrice*T0.CmpltQty)


ORDER BY T0.[DueDate], T0.[U_CustName], T0.[DocNum]

The query results displays the:

  • Actual Item Component Cost
  • Actual Additional Cost
  • Actual Product Cost

What do I need to do for the query to show me the Total Variance as well?

Regards

Rahul

Attachments