Creating query for joining table EQST, STPO, MAST to get BOM data

[former_member340772]

Can anyone help me to create a query for joing the tables EQST, STPO, MAST. I want a report for equipment,respective BOM component and respective BOM materials from the query output. I tried with the logic as per screen shot attached in file, but it didn't worked.

[jogeswararao_kavala · Accepted solution]

You link STPO-IDNRK to MAST-MATNR

[former_member340772 · Accepted solution]

Dear Sir

Thanks for your prompt response. However, I am getting the result as shown in 2nd screen shot (i.e. in material column again component number is reflecting)

My equipment BOM-

Output of query-

[jogeswararao_kavala · Accepted solution]

• First table is Equipment BOM  Header table EQST
• Second Table STPO is for Equipment BOM components details
• Third table MAST (Material BOM Header table) gives you which Component of Second table has a Material BOM (Assembly).
• Your work stopped there.
• What you need further is the Components details for Material BOM Header MAST.
• Which table give this info. This is again STPO. You need to use STPO again at the end connecting to MAST.
• But system will not allow you to use same table twice.
• Here comes the concept of using Alias tables.
• Create an Alias table to STPO as STPO1 using Alias button in the Tables join page.
• Take table STPO1 to the joins like shown below.
• And take the corresponding fields from STPO1 to output like those from STPO which you have already taken to output .
• Now see the join given below.
• I have used extra ANNAM joins precautionarily. It's optional. Better to use it to avoid unwanted rows that could come to the report
• Remove ANNAM lines if it does not suit to your situation.

Good luck

KJogeswaraRao

[former_member340772 · Accepted solution]

Brilliant sir. It is working. However, it is missing just one case i.e. those materials which are directly attached to the equipment as encircled in the screen shot.

[jogeswararao_kavala · Accepted solution]

You are talking about the Construction type field of Equipment master. This particular set-up can not bring it. You need to make a single item Equipment BOM for that instead of using it in Equipment master,

[former_member340772 · Accepted solution]

Sir in this case material 210010942 was linked to equipment with ib01 and not by Construction type field of Equipment master. When I had used EQST and STPO table only. I get that desired material code in the list also. But when I further introduce the MAST table it does not get into the report.

[jogeswararao_kavala · Accepted solution]

First remove ANNAM joins shown by me. If it does not work then try Left outer join between STPO and MAST.

[former_member340772 · Accepted solution]

Sir thanks for your timely valuable inputs. I tried with left outer join within each n every tables. But not getting that material in the output.

[former_member301875 · Accepted solution]

Boss give me a clarity that you need the materials assigned in IB01 and the materials (CS01) linked to the materials which you maintained in IB01

[jogeswararao_kavala · Accepted solution]

chetan

You need to apply things already learnt and see.

Means, you create one more Alias table for STPO say STPO2 and connect it to EQST alone (no other joins). This time you should get what you are not getting now.

[former_member340772 · Accepted solution]

Yes sir. Done. Thanks a lot for your support,guidance and patience.

[former_member340772 · Accepted solution]

Anvesh thanks for your concern shown. I will explain you once I finish my task.

[jogeswararao_kavala]

chetan

• One more thing, since beginning I forgot to tell you that you are supposed to link EQST and STPO tables through STKO.
• Similarly MAST,  STPO1 table through STKO1.
• IF necessary use STPO2 also as discussed to connect to EQST, but this too through STKO
• Start with the following, see the results and expand your work to MAST-STKO1-STPO1 and so on.

Because you have not used STKO, you are getting repititive lines.

There is no guarantee that this exercise would give you right results, but the tables must be joined like this to filter components from other BOM categories flow into report. Use Left outer if records do not come. Delete the Internal counter line also which looks not necessary. So like this you need to spend some time. As I said before, I failed making such report using SQVI / Infoset query and finally switched to ABAP report program.

The total exercise will be a learning to you on SAP queries.

[former_member340772]

Dear sir thanks again for this worth info. I tried this. But prima facia again the records are repeating (reason ,i think, against last column material code-which is directly attached to equipment- respective cells of BOM component should be blank). I will try again. And yes this exercise was indeed as learning on queries

[jogeswararao_kavala]

Chetan

You are supposed to close the thread.

Here is how you close a discussion.

• When you click on 'Correct answer' in any one of the received replies, you closed the discussion.
• In case you felt that there was no Correct answer to your query and yet you want to close the discussion, then you click on 'Mark as assumed answered' below the title of your query. This too closes the discussion.
• While closing a discussion, you may choose to click on  'Helpful answer'  for other replies found useful.

Regards

KJogeswaraRao

[former_member340772]

Sir I have issue of duplicate records now. How to avoid it?

For example, there are 5 BOM components and 1 material code attached to the equipment as shown.

(There are further subassembly (material codes) under these 3100000XXXseries BOM component..not shown)

Now  in the report, BOM component and their subassembly material codes (as encircled in red) are repeating for each component in the last column. This last column was introduced for inclusion of material which are directly attached to equipment. Pl help.

[jogeswararao_kavala]

Chetan

I see a left outer, you remove that first.

I thought it has completed. BOM reports using SQVI / Infoset query often have issues like this. It is you to try and rectify now. In Infoset queries there are advance features like coding in Extras tab using  which we can overcome some of the issues. But it is always better to go for ABAP report.

Unlike other cases SAP queries using BOM tables do not work properly, because of several dependencies like Alternative BOMs etc. To understand and rectify the the behavior like you are facing is not easy, and sometimes we fail in that. I remember that I switched to ABAP report program when faced issues like this.

Good luck

KJogeswaraRao

[former_member340772]

Noted sir. Thanks a lot.

[former_member301875]

1) Pass EQUNR and DATBI=31.12.9999 to EQUZ table and get SUBMT (SUBMT means Material BOM).

2) Pass SUBMT as MATNR in MAST table with WERKS and STLAN=4 and get STLNR

3) Pass STLNR to STPO table and get IDNRK (IDNRK means material list)