To fetch data based on materials or company code

[former_member917731]

I have kept materials and company code as parameters on selection screen.

Now I want to fetch data based on materials or company code.

If material is given on selection screen then that material should be displayed for all available company codes.

If company code is given then all materials under that company code should be displayed.

If both are given only that material and company code should be displayed.

Please guide me through this.

[FredericGirod]

The tables readed is not the good one.

Plant --> T001W-WERKS --> T001W-BWKEY --> T001k-BWKEY --> T001K-BUKRS <-- company code

Plant + Material --> MARC-WERKS & MATNR

[former_member1716]

You Are right, i understood it other way.

Thanks!

[SimoneMilesi]

I find it over complex without a real reason, to be honest, and it lacks the information material - company code.

A simple join (T001K - T001W-MARC) will work better, cleaner, without the big IF chunk and would avoid the FOR ALL ENTRIES which can result into a longer execution time.

Plus you are considering only plant level, not sales organization, for instance.

The OP should explain better what he means with "for the company", but it's a simple join, nothing so hard: he just has to check the external keys of Material main tables and he'll be fine.

[mohit_dev]

Well Simone Milesi, Not always the requirements are real in nature. I think you did not went through the maintenance view V_T001K_ASSIGN, which is eventually getting data from the tables T001K - T001W etc. Moreover, regarding the IF chunk, which you mentioned above to set the narrative you find it difficult to understand, is mainly because of his question in the first place, i.e. in case he just gives the material on the selection screen, then material should be displayed for all available company codes. and getting data from MARC for either condition will require that clauses.In case you have a good solution, paste your code here rather than downvoting and demotivating the individuals. Cheers.

[SimoneMilesi]

No, sorry, I perfectly understand both the FM and the IF, for this reason I'm saying a single JOIN will do the work, perfectly and accessing the DB less times than your solution.
I find odd and redundant to call a FM to read a view without filters and then filter the results after the selection with a loop logically wrong: if in your select option for Company Code I use a partial search it with '*', it will not work at all. Try it and tell me the result 🙂

So, I am sorry if you find my remarks demotivating, I gave all the reasons for them, I exploited an error in your code and added the hints needed to complete such task in a better way, with less access to DB, less code lines, more cleaner way. And working.
Is this demotivating? Again, I'm sorry, I am just trying to help everyone to improve for the little I can.

But I'm not used to spoonfeed anyone: I prefer to give people the tools and then let them to use and try and find the solution because I think it's the best way to improve and, personally, I find it way more satisfactory finding myself the solution.
So, no, i'm sorry, I'll not post my code, but if Akshay needs more hints I'll be happy to help and if you want the code to compare, sure, add me here and i'll send the 20 lines via PM.
If i can suggest, try to change your solution to what I'm suggesting and do a performance comparison and you'll see the results.

[FredericGirod]

Development is a tipical kind of job where everybody learn something from someone (and a lot of time from begginer).

Mohit: Make a little test of your code with the value E EQ (something) in your company code select-option for example.

(Exclude Equal (value from your system) )

Simone: Several SELECT does not means several access to the database, especialy with the customizing table. (I do not say, I am agree with the code)

[SimoneMilesi]

frdric.girod but for all entries yes since it works in chunks 🙂

[mohit_dev]

Thanks Frederic Girod, I agree with you that we do learn something from someone.

Regarding the code, I will check again. No offense.

Regarding the several SELECT statement of yours, I humbly request you to please see my code again. Those selects are in some condition and only one of them actually executes, based on some condition.

[SimoneMilesi]

It's false, sorry, the FM VIEW_GET_DATA access the db with a select otherwise it wouldn't able to extract the values, right?
Why did you use

 LOOP AT s_bukrs.
    READ TABLE lt_view INTO ls_view WITH KEY bukrs = s_bukrs-low BINARY SEARCH.
    IF sy-subrc = 0.
      lv_index = sy-tabix.
      LOOP AT lt_view INTO ls_view FROM lv_index.
        IF ls_view-bukrs <> s_bukrs-low.
          EXIT.
        ELSE.
          APPEND ls_view TO lt_view_tmp.
        ENDIF.
      ENDLOOP.
    ENDIF.
  ENDLOOP.
ENDIF.

Instead of a simple

DELETE lt_view WHERE bukrs NOT IN s_bukrs.

if you really really really want to use that FM?

Which is the most readable option between the 2 in your opinion?

[FredericGirod]

the comment about the value N EQ ... is regarding these lines:

LOOPAT s_bukrs.

READ TABLE lt_view INTO ls_view WITHKEY bukrs = s_bukrs-low BINARYSEARCH.

You assume the user will only specify a list of expected values, but he could specify nothing, or everything except ... or between value A & value B.

making a big inner join is not beautiful, but avoid playing with table & for all entries (always very dangerous & time consuming)

The remark concerning SELECT statement was not for you 🙂

but, if you want to check it, just make a sql trace on your code (ST05), not sure you will see all the select

[SimoneMilesi]

Since we are speaking about improving, I take this a step further: with your solution, you have to maintain 3 selects if in the future you need to add or remove a field, with the risk to forget a piece here and there. Is it a good design?

Yes, it's quite possible this isn't a real requirement, but as I said, we are here to improve (I'm the first one opening questions when I do not know something), so let's think as this is productive code, so we do not fall in the bad habits 🙂

[FredericGirod]

Akshay the tables are

field BUKRS --> table T001K --> field BWKEY --> table T001W --> field WERKS --> table MARC --> field MATNR

but it is a point of view. because material is not directly linked to company code.

you need to choose by witch organiazational data you want to make the link plant, com. org ....

(and please, don't remove answer, the objective is to help requestor, not to have the perfect answer)

[former_member917731]

How can I write a code for this ?

[former_member917731]

How can I write a code for this ?

[Nag_05]

User Inner joins on tables mentioned, single SELECT will not work

[former_member634045]

Hi Akshay, You can use this code below;

TABLES: marc, t001k.
TYPES: 
BEGIN OF tt_bukrs,
  bukrs TYPE t001k-bukrs,
  END OF tt_bukrs,
BEGIN OF tt_matnr,
    matnr TYPE marc-matnr,
END OF tt_matnr.

DATA: lt_burks TYPE STANDARD TABLE OF tt_bukrs,
      lt_matnr TYPE STANDARD TABLE OF tt_matnr.


SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (10) text-002 FOR FIELD p_mat.
SELECT-OPTIONS: p_mat FOR marc-matnr NO INTERVALS.
SELECTION-SCREEN COMMENT 50(15) text-003 FOR FIELD p_code.
SELECT-OPTIONS: p_code FOR t001k-bukrs NO INTERVALS.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK b1.


START-OF-SELECTION.
IF p_matnr IS NOT INITIAL AND p_code IS INITIAL.
SELECT DISTINCT a~bukrs
      INTO TABLE lt_bukrs
      FROM t001k AS a
      INNER JOIN t001w AS b ON a~bwkey = b~bwkey
      INNER JOIN matnr AS c ON b~werks = c~werks
      WHERE c~matnr IN p_matnr.
ELSEIF p_code-low IS NOT INITIAL AND p_mat-low IS INITIAL.
SELECT a~matnr
      INTO TABLE lt_matnr
      FROM marc AS a
      INNER JOIN t001w AS b ON a~werks = b~werks
      INNER JOIN t001k AScON b~bwkey = c~bwkey
      WHERE c~bukrs IN p_code.
ELSEIF p_mat-low IS NOT INITIAL AND p_code-low IS NOT INITIAL.
"print both code and material into a table
ENDIF.

[FredericGirod]

You loose the advantage of using SELECT-OPTIONS instead of PARAMETER by trying to read LOW. It is really dangerous, take the example of the value E EQ 12345678 for material, that means I want all the material but not the 12346578, what will be the result of your code ?

SELECT-OPTIONS --> IN operator so only one SELECT statement

[former_member634045]

Hi Frederic,

Can you please elaborate your question? It would be more helpful for me to answer your question.

[former_member634045]

frdric.girod As I understood if I write E EQ 12345678 then why it will fetch all the materials?

In select option statement we pass single or multiple value of a table. If I pass single value like 12345678 then it will fetch only particular value of this 12345678. Please point it out if my understanding is wrong.

[FredericGirod]

E EQ 12345678

E --> Exclude

EQ --> Equal

it is the red equal in the dynpro

stop using EQ or = with select-option, use IN even if you have no-interval option selected

[SimoneMilesi]

As frdric.girod already pointed out, you are using in the wrong way a select option.

Your code is messy, not performing and it has BUGS: try to fill your select option in this way and let me know the result, you'll not have a single result, wanna bet? (replace the E* with the 1st character of your material + * )

[FredericGirod]

simone.milesi you are showing different case with your screenshot :

should be (i am not really sure)

I CP E*

I CP A*

I EQ 1231231

I NE GSFS

mehabubm67, you see, you could have so many combinations and your code works with only one of them : I EQ (Include Equal), you really need to stop playing with the LOW & HIGH value of Select-options

[SimoneMilesi]

frdric.girod yes, i added some cases more to show the issues with the suggested code:

• multiple inserts are not handled
• Wildcards would return nothing
• Exclusion will not be managed

[former_member634045]

I love to write code in ABAP and I love this platform. It would be great if you point out more mistakes or bugs in my suggested code because I'm learning this and Implement one by one. For that thank you frdric.girod and simone.milesi .

Now comming to simone.milesi comments. Yeah Multiple inserts are being handled. And yes exclusion and wildcard part are not managed. Beacuse I'm just trying to write a code that will work like aksnar said or as per his requirements. He should give me more details. I'm not here to write a perfect code, like wildcards, exclusion and multiple and many more. I just suggest a simple code to him. After that if he says his requirements breifly then I'll try to write a perfect code in that all the other things would be managed. And I'm not misjudging you guys, I'm just saying my point also.

Again thank you for pointing me out, I'm learning many things daily from community.

[SimoneMilesi]

To be honest, all your code can be reduced to a single join if you properly use Select Options 🙂
Give a try (ok, i sound like a teacher, sorry >_< 😞 use IN for Select Options as Frederic suggested and I'm sure you can find the solution to improve the code with less line, more readable and faster 🙂

[former_member634045]

Hi frdric.girod & simone.milesi ,

Can anyone tell me what is wrong in this below code line:

SELECT a~matnr
      INTO TABLE lt_matnr
      FROM marc AS a
      INNER JOIN t001w AS b ON a~werks = b~werks
      INNER JOIN t001k AS c ON b~bwkey = c~bwkey
      WHERE c~bukrs IN p_code.

It is fetching all the matnr of marc table,

SELECT bwkey INTOTABLE lt_bwkey FROM t001k WHERE bukrs IN p_code.

SELECT werks INTOTABLE lt_werks FROM t001w FORALLENTRIESIN lt_bwkey WHERE bwkey EQ lt_bwkey-bwkey.

SELECT matnr INTOTABLE lt_matnr FROM marc FORALLENTRIESIN lt_werks WHERE werks EQ lt_werks-werks.

It is fetching wrong no entries of matnr

What is wrong in these codes? I'm trying to update the code without using low from select option as you have mentioned.

[SimoneMilesi]

The first one is surely correct because it's what i would have done 🙂

The second one should be the same imho but, indeed, it returns a different set of materials.

I do not know if horst.keller or matthew.billingham can chime in and give a reason (surely an obvious one I'm totally missing) for the different behaviour.

[FredericGirod]

simone.milesi the discussion continues here:

https://answers.sap.com/questions/12885543/fetching-wrong-no-of-entries-from-marc-table.html?childTo...