SAP Business One query generator

[Former Member]

Hi, Everyone

We recently started using SAP and i'm trying to generate a report and i can use some help!

I want to export a list of all item master data and there parent & child items associated with it and the BP Catalog No. associated with each item and additional identifier for each of them.

Thanks, Sol

[KennedyT21 · Accepted solution]

You can start from 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)

Regards

Kennedy

[Former Member · Accepted solution]

Thanks for the help. When i clicked execute i got a pop up parent item equal, what should i put in there?

Thanks, Sol

[KennedyT21 · Accepted solution]

try this

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

(

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

From ITT1 P

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)

Regards

Kennedy

[former_member212181]

Hi Sol,

Your requirement is not clear. or If we link Bill of materials and business partner catalogue table to Item master table, it may bring duplicate records in item master list.

Please check below query. In this query I didn't link BoM but I link with Catalogue table where Item master preferred supplier = Business partner table supplier code.

SELECT T0.[ItemCode], T0.[ItemName], T0.[FrgnName], T1.[ItmsGrpNam]

               , T0.[SWW], T2.[Substitute]

FROM OITM T0

INNER JOIN OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod]

Left Outer JOIN OSCN T2 ON T0.[ItemCode] = T2.[ItemCode] and T0.CardCode = T2.CardCode

Thanks

Unnikrishnan