Reporting onExpected usage vs Actual usage on Prod Order Consumption

blackc2005
blackc2005 Member Posts: 18
in SQL General
I've been tasked with creating a SQL report that reports on actual prod order consumption vs expected consumption. This is basically a variance report but instead of on the dollars, the actual quantities.

What is the best way to go about doing this? Comparing the ILE's with the Prod_ Order Component lines?

We have found that people are finishing prod orders differently, some people are adjusting the Production Journal to post actual usage and some people are changing the "Qty Per" on the Prod Order Components. So using the "Remaining Qty" column in the Prod_ Order Component line won't work.

Is there another place/way I can find the actual vs expected component consumption?

Answers

  • Developer101
    Developer101 Member Posts: 569
    yes the prod order component table and link with ILE
    on
    Entry Type=Consumption- (5)
    Order Type=Production- (1)
    Order No.=Prod. Order No.
    Order Line No.=Prod. Order Line No.
    Prod. Order Comp. Line No.=Line No.

    You get expected consumption from the Expected Quantity field on the prod order component table and actual consumption quantity from the link to ILE using quantity field on ILE
    United Kingdom
  • blackc2005
    blackc2005 Member Posts: 18
    Hi, Thanks for your answer, but I don't think that will work because half of our production orders in the past the person finishing them was adjusting the QTY PER on the Consumption lines, which then adjusted the Expected Qty. So therefore the Expected QTY and the ILE would be the same.

    Moving forward this would work because we've corrected the process.
  • blackc2005
    blackc2005 Member Posts: 18 
    SELECT POC.[Item No_], POC.[Line No_], POC.[Prod_ Order No_], POC.[Unit of Measure Code],POC.[Location Code], ILE.[Source No_], POC.[Due Date], I.[Product Group Code], I.Description,
ILE.Quantity, POC.[Expected Quantity], (ILE.Quantity) - POC.[Expected Quantity] AS UsageDiff, (ILE.Quantity - POC.[Expected Quantity])/NULLIF(POC.[Expected Quantity],0) AS PercDiff
FROM (SELECT [Order Type], [Order No_], [Entry Type], [Order Line No_], [Prod_ Order Comp_ Line No_], ILE.[Item No_], ILE.[Source No_],SUM(Quantity) * -1 As Quantity
	FROM [COMPANYNAME$Item Ledger Entry] AS ILE
	WHERE ILE.[Entry Type] = 5 AND ILE.[Order Type] = 1
	GROUP BY [Order Type], [Order No_], [Entry Type], [Order Line No_], [Prod_ Order Comp_ Line No_], ILE.[Item No_], ILE.[Source No_]) AS ILE
JOIN [COMPANYNAME$Prod_ Order Component] AS POC ON ILE.[Order No_] = POC.[Prod_ Order No_] AND ILE.[Order Line No_] = POC.[Prod_ Order Line No_] AND ILE.[Prod_ Order Comp_ Line No_] = POC.[Line No_]
JOIN [COMPANYNAME$Item] AS I ON I.No_ = POC.[Item No_]

    I came up with this code.. It seems to do the trick but doesn't take into account the issue I mentioned about people changing the QTY PER on the Consumption
  • Developer101
    Developer101 Member Posts: 569
    Qty per , is it a field? is it a bespoke stuff and not standard?
    United Kingdom
  • blackc2005
    blackc2005 Member Posts: 18
    1q32iczcdrlq.png

    I'm not sure if this is custom.. I think it's standard?
  • Developer101
    Developer101 Member Posts: 569
    sorry yes of course its standard but you should not be able to change it for finished (status) line
    United Kingdom
  • blackc2005
    blackc2005 Member Posts: 18
    Developer101 wrote: »
    sorry yes of course its standard but you should not be able to change it for finished (status) line

    They edit it before they finish it which adjusts the EXPECTED QTY field.

Categories