Difference in BOM and Production order

[Former Member]

Dear Experts,

I was trying to  make a query to know the difference between BOM and production order.

SELECT T0.[DocNum], T0.[ItemCode], T0.[PostDate], T0.[CmpltQty], T0.[WOR1Count],Count(T1.[LineNum]) as 'Original line no.',(T0.[WOR1Count] - Count(T1.[LineNum])) as 'Difference'  FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[LineNum] <> T0.[WOR1Count] GROUP BY T0.[DocNum], T0.[ItemCode],  T0.[PostDate], T0.[CmpltQty], T0.[WOR1Count]

The above query is not giving me accurate result it is also showing me production orders and BOM in which there is  no difference.

Thanks-
Mona.

[Former Member · Accepted solution]

Dear Mona,

You need ITT1 table in the query to compare Production Order against BOM.

Thanks,

Gordon

[Former Member · Accepted solution]

Gordon,

What will be the query for the same?

MOna.

[Former Member · Accepted solution]

It depends if you have multiple levels in BOM.

[Former Member · Accepted solution]

Gordon,

Thanks for your reply

Ya its multi-level BOM thats why i am not able to get the result.

Please advice.

Mona.

[Former Member · Accepted solution]

If it is multiple levels, what is in your current query to compare with?

[Former Member · Accepted solution]

Gordon,

To get the result i tried below query also for multilevel BOM.

SELECT T0.[DocNum], T0.[ItemCode], T1.[LineNum], T1.[ItemCode], T2.[Father], T2.[Code]
FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry inner join ITT1 T2 on T2.Father = T0.Itemcode
WHERE T1.[ItemCode] <> T2.[Code] GROUP BY T0.[DocNum], T0.[ItemCode],
T1.[LineNum], T1.[ItemCode], T2.[Father], T2.[Code]

Mona.

[Former Member · Accepted solution]

Is the result satisfactory?

[Former Member · Accepted solution]

No Result is not Satisfactory.Even my customer is willing to see in the report format which includes detail report of BOM and production order details only the difference one's

[Former Member · Accepted solution]

If you have multiple level BOM, it may be too hard to compare with production order.

Try this first:

SELECT T0.[DocNum], T0.[ItemCode], T1.[LineNum], T1.[ItemCode]

FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T1.[ItemCode] NOT IN (SELECT T2.[Code] FROM ITT1 T2 WHERE T2.Father = T0.Itemcode) AND T0.DocNum LIKE '[%0]%'

[Former Member · Accepted solution]

Gordon,

Thank you for your reply.I tried but it is not reflecting all the production order with differences.

Mona.

[Former Member · Accepted solution]

What differences are you looking for? Quantity differences?

[Former Member · Accepted solution]

The difference i am looking for is line items.

[Former Member · Accepted solution]

The query I posted can only shoe if the itemcode in production order is not in the BOM for the finished good.