Query for list of specifications

[former_member604385]

Hi Experts,

We need to execute query which should contain list of specifications with and without material assignments.Selection criteria is based on generation variant.

Please let us know from which tables we can get the above list.

Thanks for your help!!

Regards,

Sivasankar

Message was edited by: Sivasankar P

Message was edited by: Sivasankar P

[christoph_bergemann · Accepted solution]

Dear Sivasankar

I agree to comments of the other. The generation variant (or more precise the report) is not an useful link between specification and material. As mentioned:

ESTRH is the spec header

ESTMJ contains material assigned to specificacion

ESTDH represents the "report header".

The answer to your quesstions depend on this question:

a.) is the analysis something which the end user should do

b.) or is this something related to consultants

If the answer is: a consultant should do that then use simply SAP query. Check e.g:

any many similar ones. If the query should be done by end use you should think of adding an userexit in CG02 to do so.

C.B.

[former_member604385 · Accepted solution]

Hi Chris,

This issue is not related to consultant and it is related to end user. We have query which is already defined to pick up specifications with material.Now they have requested us to get the query even there is no material assigned to specifications.

So we have planned to remove material from query, so if we remove ESTMJ from query whether it will have impact on some other tables.(like ESTDJ,ESTRH,ESDTH,etc..)

Thanks for your help!!

Query:

*delete report if there is no material

SORT I_ESTRH BY RECNROOT.
   SORT I_ESTMJ BY RECNROOT.
   IF I_ZESTRHESTDJESTDH[] IS NOT INITIAL OR
      I_ZESTRHESTDJESTDH1[] IS NOT INITIAL.
     LOOP AT I_ESTRH INTO WA_ESTRH.
       MOVE SY-TABIX TO V_INDEX.
       READ TABLE I_ESTMJ INTO WA_ESTMJ WITH KEY RECNROOT = WA_ESTRH-RECNROOT
                                                               BINARY SEARCH.
       IF SY-SUBRC NE 0.
         READ TABLE I_ZESTRHESTDJESTDH WITH KEY SUBID_ESTRH = WA_ESTRH-SUBID
                                                              BINARY SEARCH.
         IF SY-SUBRC NE 0.
           READ TABLE I_ZESTRHESTDJESTDH WITH KEY SUBID = WA_ESTRH-SUBID
                                                          BINARY SEARCH.
           IF SY-SUBRC NE 0.
             READ TABLE I_ZESTRHESTDJESTDH1 WITH KEY SUBID = WA_ESTRH-SUBID
                                                             BINARY SEARCH.
             IF SY-SUBRC NE 0.
               READ TABLE I_ZESTRHESTDJESTDH1 WITH KEY SUBID_ESTRH = WA_ESTRH-SUBID
                                                                     BINARY SEARCH.
               IF SY-SUBRC NE 0.
                 DELETE I_ESTRH INDEX V_INDEX.
               ENDIF.
             ENDIF.
           ENDIF.
         ENDIF.
       ENDIF.
     ENDLOOP.
   ENDIF.

Regards,

Sivasankar

[christoph_bergemann · Accepted solution]

Dear Sivasankar

I still have some doubt If I am understanding the business demand correct. Therefore these are some ideas:

If you use CG02 you can create any typeof hitlist based on whatever type of search criteria. Then you can immediately switch to CG54. Here you will get any released WWI report for the hit list in quesiotn. Now you can select "Material Master". In doing so you will get those WWI reports which do have a linked material and which do not. All this can be done without any ABAP coding as you have shown.

Can you please try to explain the business need of the query?

C.B.

[former_member604385 · Accepted solution]

Hi Chris,

Above query is not only used for selecting materials with specifications and also used for selecting some other functionalties.So if we use CG02 and try to get the hit list of materials, user feel difficult to get the output as required, So we designed custom query to process the functionality.

Current query functionality which process materials with specifications, so user needs if material is not assigned in specification ,he need to get the specification in the output.

So if we remove ESTMJ from query whether it will affect any other functionalities in the program. (Like ESTDJ,ESTDH tables)

Please let me know if you need information from my side.Thanks!!

Regards,

Sivasankar

[former_member215519 · Accepted solution]

Just my idea here.

You may keep the ESTMJ table, but use the left join instead, that's way all records will be displayed with or without material assignment.

[christoph_bergemann · Accepted solution]

Dear Sivasankar

sorry. I need to come back with my proposal. Why do you not use CG02 => GG54? There is nearly nothing which does not fit the "buisness" demand

Example:

1.) if you sue a list of materials (which is possiblke with higher SAP Releases) you get the hit list in CG02; there is no need really to use genvariant and even if so by marking any spec and switching to CG54 you will get the sepc / material relation and the correlation to released reports. As you can use CG02 to switch as well to CG50 you coukld get as well the not released reports

2.) you can use  a list of specifications (which is as well possible using higher SPA releasesI and do the same as mentioned before. Here you will get in CG54 the relaiotn: which Spec does have a material and which does

Prerequisite for both options is: there msut be a report (eithere released or not) to get the relation. But if you are interesed in relation of spec to material only then many other options exists. There is really no need of own ABAP programming (as in you ABAP program you need to e.g. reflect access cocnept in most cases)

So please explain:; what is the business scenario? what is the "real" purpose of your scenario?

C.B.

[former_member604385 · Accepted solution]

Hi Chris,

Thanks for reply and suggestion..!

Let me explain you more clearly what it the current functionality of the query and what is new one, that is expected.

Currently:

We are running a query with below selection input parameters,

to yield a output in a user friendly ALV grid layout format with below displayed output columns,

I hope this clears the major need of running the report through ABAP query than CG54/CG50. As,

we very well know the hit list from CG02, CG54/CG50 will not give as an user friendly output to download the data with above needed fields (Its a business need). Thus we underwent to build the ABAP query.

In the above displayed output screen, we can see that spec and material along with other fields are populated, this output is generated only for specification that have material assigned to them with reports. 

To be more clear, present functionality logic is build as below.

If, Material and report are present - Specification is resulted in report.

If, No Material but have Reports - Specification should NOT resulted in report.

If, No Material, No Reports -  Specification should NOT resulted in report.

But now, new requirement needed:

If, No Material but have Reports - Specification should be resulted in report.

If, No Material, No Reports -  Specification should be resulted in report.

I hope I made the requirement clear now..

Looking forward for your suggestion to attain/un-restrict these condition in our query. Thanks

[christoph_bergemann · Accepted solution]

Dear Sivasankar

I am sorry to say that your explanations did not convice me: I still would do the job using the standard options in Cg02/CG54/CG50.

These are the major reasons for using SAP Standard.

a.) full support of SAP access concept in EHS

b.) can be extended easily etc.

c.) full support regarding any CG02/CG54/CG50 search option

Let us try the other way around and start with options in CG02:

a.) in CG02 you can use either a list of materials or a list of specifications. Result is a hit list. Only con here: if you start with list of materials you do not know which material from original list is linked to which specification. To take care this (and may be other topics) an outputvairant is the right way to do it. Outputvariants are easily to be customized and out put can be specified flexible (e.g. using ALV). In CG02 you can use on top e.g. store hit lists; you can use "dynamic" search criterias to get the specified hit list etc.

b.) Starting in either CG54 or CG50:

Here as well you can use either a list of specifications or materials to get the report hit list. Using higher SAP releases you can use as well list of gen variants. In CG54;CG50 you can as well use an ALV (via existing user exit) to get the output as specificied by you

May be check:;

Topic of output variants is not mentioned there but of yo checl SPRO you will easily get the picture

Ignoring access topics. the tables needed are mentioned yet. Only may be one "crucial" topic might be missing: using the report hearder you can assign additional specifications for which the report is valid. This is called as well "referencing" (e.g. check: Problem with Reference Reports Repeating twice ... | SCN). I am not sure if your query srtil support this feature (Cg02,Cg54/Cg50 are supporting that !).

C.B.

[former_member209335]

Hi Siva,

Check this thread Here, you can get complete list of table information.

FYI,

ESTRH - EHS: Specification Header

ESTVA - EHS: Value Assignment

ESTVH - EHS: Specification - Value Assignment Type Assignment

ESTDF - Additional Information - User-Defined Text

ESTVP - EHS: Value Assignment Item

ESTDR - EHS: Additional Information: Value Assignment Assessment

ESTDU - EHS: Additional Information: Value Assignment Usage

CCRCT_EHS_COMP - EHS: Composition (EH&S Data)

CCRCC_CHCK_ON - EHS: Online Checks for Regulation/Scenario/Scenario Category

Edward

[former_member215519]

Not sure why you want to link substance with gen variant in your query, but normally you will have to use MSDS report to link those two together. Technically you can use below four tables.

ESTRH = specification header

ESTLP = gen var

ESTDH = report header

ESTMJ = material assignment