BOM reports linking with Production Order table/Column

[former_member217682]

Hi Guys,

I'm trying to create a report based on Production Order + Multi Bom Level on the items.

I'm just wondering where is the Depth column and where does it link to.

How does the linking works?

Thank you for your help.

[KennedyT21 · Accepted solution]

Hi Adrian Lee...

You can Also try This

With BOM (Parent,Child,Quantity,Level) As

(

Select P.Father Parent,P.Code Child, Quantity,0 Level

From ITT1 P

Where P.Father =  '[%0]'

Union All

Select c.Father,C.Code,C.Quantity,Level+1

From Itt1 C

  Inner join BOM B on c.Father=B.Child

)

Select B.Parent,B.Child,B.Quantity,B.Level

From BOM B Inner join OITM I On B.Child=I.ItemCode

Option (MAXRECURSION 99)

[former_member217682 · Accepted solution]

Hi Kennedy,

Thank you very much.

The queries are helpful for me to understand that Depths actually comes from ITT1 tables.

My question is, how can i make it to show exactly like the Column in Bills Of Material Report.

I want to display :

Item 1 = Depth 1

Item 2 = Depth 2

Item 3 = Depth 2

Item 4 = Depth 3

Item 5 = Depth 3

Item 6 = Depth 4

Thank you very much.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

SELECT T0.[Father] as 'Assembly',T0.[code] as 'Depth1', t10.[ItemName] 'Description1',T1.[Code] as 'depth2', t11.[ItemName] 'Description2', T2.[Code] as 'depth3', t12.[ItemName] 'Description3', T3.[Code] as 'depth4', t13.[ItemName] 'Description4',T4.[Code] as 'depth5', t14.[ItemName] 'Description5', T5.[Code] as 'depth6', t15.[ItemName] 'Description6' FROM ITT1 T0 LEFT OUTER JOIN ITT1 T1 on T0.Code = T1.Father LEFT OUTER JOIN ITT1 T2 on T1.Code = T2.Father LEFT OUTER JOIN ITT1 T3 on T2.Code = T3.Father LEFT OUTER JOIN ITT1 T4 on T3.Code = T4.Father LEFT OUTER JOIN ITT1 T5 on T4.Code = T5.Father LEFT OUTER JOIN ITT1 T6 on T5.Code = T6.Father left outer join oitm t20 on t0.father = t20.itemcode left outer join oitm t10 on t0.code = t10.itemcode left outer join oitm t11 on t1.code = t11.itemcode left outer join oitm t12 on t2.code = t12.itemcode left outer join oitm t13 on t3.code = t13.itemcode left outer join oitm t14 on t4.code = t14.itemcode left outer join oitm t15 on t5.code = t15.itemcode

Thanks & Regards,

Nagarajan

[former_member217682 · Accepted solution]

Hi Nagarajan,

That is what i am after.

Right now i just need to figure out how to link the tables in Crystal Reports.

Is there any way to link that query to Production Order where users can key in the OWOR.DocNum and get a list together with the BOM?

Thank you very much again for your help.

[KennedyT21 · Accepted solution]

Hi  Adrian Lee..

To make a report as same as in BOM i would suggest to trace the BOM Report SQL Query  using the SQL Profiler as it is a complex report which might be used  multiple view and procedure

Hope Understand

Regards

Kennedy

[former_member217682 · Accepted solution]

Hi Kennedy,

Do you have skype? or other IM's? perhaps it's easier i can explain to you what i actually wanted.

Thank you.

[KennedyT21]

Hi Adrian Lee ...

Try This query for the Multiple Level

SELECT T0.[Father] as 'Assembly',T0.[code] as 'Component1', t10.[ItemName] 'Description1',T1.[Code] as 'Component2', t11.[ItemName] 'Description2', T2.[Code] as 'Component3', t12.[ItemName] 'Description3', T3.[Code] as 'Component4', t13.[ItemName] 'Description4',T4.[Code] as 'Component5', t14.[ItemName] 'Description5', T5.[Code] as 'Component6', t15.[ItemName] 'Description6' FROM ITT1 T0 LEFT OUTER JOIN ITT1 T1 on T0.Code = T1.Father LEFT OUTER JOIN ITT1 T2 on T1.Code = T2.Father LEFT OUTER JOIN ITT1 T3 on T2.Code = T3.Father LEFT OUTER JOIN ITT1 T4 on T3.Code = T4.Father LEFT OUTER JOIN ITT1 T5 on T4.Code = T5.Father LEFT OUTER JOIN ITT1 T6 on T5.Code = T6.Father left outer join oitm t20 on t0.father = t20.itemcode left outer join oitm t10 on t0.code = t10.itemcode left outer join oitm t11 on t1.code = t11.itemcode left outer join oitm t12 on t2.code = t12.itemcode left outer join oitm t13 on t3.code = t13.itemcode left outer join oitm t14 on t4.code = t14.itemcode left outer join oitm t15 on t5.code = t15.itemcode

Regards

Kennedy

[kothandaraman_nagarajan]

Hi,

You need following tables:

OWOR & WOR1----For production order details

OITT and  ITT1---- For BOM details.

Link between tables:

OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITT T2 ON T0.ItemCode = T2.Code INNER JOIN ITT1 T3 ON T2.Code = T3.Father

Thanks & Regards,

Nagarajan

[former_member217682]

Hi Nagarajan,

Thank you very much for the fast reply.

Can i know which is the Depth column?

I've done the linking like you said below.

Can you correct me if im wrong?

[kothandaraman_nagarajan]

Hi,

For depth you have join ITT1 table as per below up to six level of BOM

FROM ITT1 T0 LEFT OUTER JOIN ITT1 T1 on T0.Code = T1.Father LEFT OUTER JOIN ITT1 T2 on T1.Code = T2.Father LEFT OUTER JOIN ITT1 T3 on T2.Code = T3.Father LEFT OUTER JOIN ITT1 T4 on T3.Code = T4.Father LEFT OUTER JOIN ITT1 T5 on T4.Code = T5.Father LEFT OUTER JOIN ITT1 T6 on T5.Code = T6.Father left outer join oitm t20 on t0.father = t20.itemcode left outer join oitm t10 on t0.code = t10.itemcode left outer join oitm t11 on t1.code = t11.itemcode left outer join oitm t12 on t2.code = t12.itemcode left outer join oitm t13 on t3.code = t13.itemcode left outer join oitm t14 on t4.code = t14.itemcode left outer join oitm t15 on t5.code = t15.itemcode

Thanks & Regards,

Nagarajan