Could you please let me know a way to join ESTDH table with ESTMJ table

[surajhny]

I have a requirement where I need to create a report

Wherein I need to find records in ESTDH table with report status as 'W7' and check if the materials associated to a specific is extended to a plant or not

So I'm planning to join ESTDH table with ESTMJ ..kindly let me know if this is the right method and also how do I join ESTDH with ESTMJ

[christoph_bergemann]

Hello

there is no direct relationship between ESTDH and ESTMJ.

Any "report" (either of type "IBD" or of type "SBR") is managed in ESTDH. ESTDH is linked to ESTRH. Linked to ESTRH is ESTMJ

Normally any user uses the SAP transaction Cg54 / Gg50. As soon as the user has generated the so called "report tree" the user can switch between "specification view" and "material view".

In the material view you can see the "related report" (as managed in ESTDH

C.B.

PS: for details: Please check online help (how to use CG54 and CG50)

[Yogananda]

surajhny

Yes, it is the right approach to join the ESTDH and ESTMJ tables in order to find out if a specific material is extended to a plant or not.

To join the tables, you can use the plant, material, and material type columns as the common fields between the two tables.

You can join using a left join from ESTDH to ESTMJ, where ESTDH's plant = ESTMJ's plant, ESTDH's material = ESTMJ's material, and ESTDH's material type = ESTMJ's material type. This will allow you to keep all of the records from ESTDH and any matching records from ESTMJ.

Once you have joined the tables, you can filter on ESTDH's report status field to ensure the records have a status of 'W7' before checking if the material is extended to a plant or not on the ESTMJ table.

[surajhny]

ESTDH table does not have Plant and Material fields to join with ESTMJ? Could your please clarify on this?

[christoph_bergemann]

Hello

Pseudocode like: You can join using a left join from ESTDH to ESTMJ, where ESTDH's plant = ESTMJ's plant, ESTDH's material = ESTMJ's material, and ESTDH's material type = ESTMJ's material type. This will allow you to keep all of the records from ESTDH and any matching records from ESTMJ.

Will not work as ESTDH is a table havin no plan reference.

On top: very rarely on level of ESTMJ a "plant" code us added. I know only one customer which does it like that.

SAP EHS does contain  a User Iterface with SAP transaction CG54 / CG50: Here you get  link

Material - Specification - Report (ESTMJ/ESTRH/ESTDH).

Internally the User Interface is supportd by sime function module.

You should (in most of the case) only use SAP function module to collect the required information and not do a "direct" select.

Experience has shown that SAP changes sometimes the data model and then such "Selects" are not useful.. 

Anyhow: Please check for same SAP standard function module. It is much better.

C.B.

PS: may be https://community.sap.com/t5/product-lifecycle-management-q-a/standard-function-module-which-generat... might be of interest