bill of material wrong

José Luis De Oliveira

Hello,

When I print a bill of material, items doesn't appear in order. I don't understand because a lot of BOM works and with others doesn't work. Why? Is it a setting of BOM?

Please see attachement file to understand better.

Thank you for your help.

Best regards.

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-image-uploads/joseluis-deoliveira/billofmaterial.JPG]billofmaterial.JPG[/url]

Philipp Knecht

Hi José

I cannot reproduce it here.
Is it possible to get access to this system?
Or maybe you can put the db on our ftp.

Mail me if you have further questions.

hth

José Luis De Oliveira

Hi Philipp,

I sent you the costumer database on your FTP.

Please try with this BOM : 1239000-300-01

Thank you for your help.

Have a nice day.

Best regards.

José Luis De Oliveira

Hi Philipp,

Have you news about my problem?

Thank you in advance.

Best regards,

Philipp Knecht

Hi José

I didn't found the DB and contacted somebody from your department.
Please upload again.

hth

José Luis De Oliveira

Hi Philipp,

I just finished send you the database.

It name : Hauni.zip

Thank you in advance,

Best regards,

José Luis De Oliveira

Hi Philipp,

Have you news about my problem?

Have you found database in your FTP?

Thank you for your help.

Best regards,

Philipp Knecht

Hi José

It's a bug in the Query.
Save tzhe following Query in B1 and add it in the Layoutdefinition:

/* @@Overwrite */
SELECT Code,Qauntity AS Qty,Qauntity AS MainQty,0 AS SortExpr,0 AS Lvl, 'Y' AS IsTreeCode  INTO #Lvl00 FROM OITT WHERE Code = '[%TreeCode]'
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,(T0.ChildNum+1) * 10000000000000000 + T1.SortExpr AS SortExpr,1 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl01 FROM ITT1 T0 INNER JOIN #Lvl00 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty ,(T0.ChildNum+1)* 100000000000000 + T1.SortExpr AS SortExpr,2 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode INTO #Lvl02 FROM ITT1 T0 INNER JOIN #Lvl01 T1 ON T0.Father = T1.Code  LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,(T0.ChildNum+1) * 1000000000000 + T1.SortExpr AS SortExpr,3 AS Lvl,CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl03 FROM ITT1 T0 INNER JOIN #Lvl02 T1 ON T0.Father = T1.Code  LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,(T0.ChildNum+1) * 10000000000 + T1.SortExpr AS SortExpr,4 AS Lvl,CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl04 FROM ITT1 T0 INNER JOIN #Lvl03 T1 ON T0.Father = T1.Code  LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,(T0.ChildNum+1) * 100000000 + T1.SortExpr AS SortExpr,5 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl05 FROM ITT1 T0 INNER JOIN #Lvl04 T1 ON T0.Father = T1.Code  LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,(T0.ChildNum+1) * 1000000 + T1.SortExpr AS SortExpr,6 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl06 FROM ITT1 T0 INNER JOIN #Lvl05 T1 ON T0.Father = T1.Code  LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,(T0.ChildNum+1) * 10000 + T1.SortExpr AS SortExpr,7 AS Lvl,CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl07 FROM ITT1 T0 INNER JOIN #Lvl06 T1 ON T0.Father = T1.Code  LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,(T0.ChildNum+1) * 100 + T1.SortExpr AS SortExpr,8 AS Lvl,CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl08 FROM ITT1 T0 INNER JOIN #Lvl07 T1 ON T0.Father = T1.Code  LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
          SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,(T0.ChildNum+1) * 1 + T1.SortExpr AS SortExpr,9 AS Lvl,CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl09 FROM ITT1 T0 INNER JOIN #Lvl08 T1 ON T0.Father = T1.Code  LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code

          SELECT * INTO #Result FROM #Lvl00

          UNION ALL SELECT * FROM #Lvl01
          UNION ALL SELECT * FROM #Lvl02
          UNION ALL SELECT * FROM #Lvl03
          UNION ALL SELECT * FROM #Lvl04
          UNION ALL SELECT * FROM #Lvl05
          UNION ALL SELECT * FROM #Lvl06
          UNION ALL SELECT * FROM #Lvl07
          UNION ALL SELECT * FROM #Lvl08
          UNION ALL SELECT * FROM #Lvl09
          
          SELECT T0.*, T1.* FROM #Result T0 INNER JOIN OITM T1 ON T0.Code = T1.ItemCode ORDER BY SortExpr

hth

José Luis De Oliveira

Hi Philipp,

I am sorry, but I do that you say and now I have this message error. see attachement.

Can you help me please?

Than you in advance.

Best regards

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-image-uploads/joseluis-deoliveira/error.JPG]error.JPG[/url]

Philipp Knecht

Hi José

Strangely it works on my SQL Server 2005 (with your DB)
But:
Replace #Lvl with #Lvl2 (all)
And #Result with #Result2

hth