get a Production order by selecting a material?

[Former Member]

Hey all, i'm trying to get a list of production orders that have a particular material in them and unfortunately it is taking WAY to long because the material is not a key field. Does anyone know a good table to go to where they key is a material and one of the fields is the production order? ... if there is another way to do it please let me know.

I've looked in co03 and that seems to be doing the same thing however, I can't figure out exactly how they are doing it.

Any help would be greatly appreciated. Thank you!

-Shaunt

[RichHeilman]

You could always create an non-unique index against AFPO with the following key.

MANDT

MATNR

This should speed up your select statement.

Regards,

Rich Heilman

[Former Member]

Yea, i've thought about doing that, however, I know its already done in SAP in tn #co03 so I would really rather not do that if at all possible. Any other ideas? is there a good table has a material for a key and a production order # as a field?

Thanks,

-Shaunt

[RichHeilman]

Not sure what you mean when you say "its already done in tcode CO03" More information please. I have done extensive work in PP and have had great success in writing fast custom reporting. I do believe that I can help you with the right information. Maybe post your select statement so that I can take a look.

Regards,

Rich Heilman

[Former Member]

well it in tn#cc03 you can select a material and it will return the production orders that have that material in them (use f4 to get [E] - Production Orders using the info system)

In essence I need to do a:

select * from caufv into table xcaufv where plnbez='some material'

Thanks,

-Shaunt

[Former Member]

sorry, i mean tn#co03

[RichHeilman]

Looks like that transaction uses a logical database. You don't want to mess with that. My suggestion is to create an index on table AFKO with key MANDT PLNBEZ. I just created an index on my system and a quick program and it ran in under 5 secounds.

REPORT ZRICH_0001 .

DATA: ICAUFV LIKE CAUFV OCCURS 0 WITH HEADER LINE.

PARAMETERS: P_MATNR LIKE MARA-MATNR.

START-OF-SELECTION.

SELECT * INTO CORRESPONDING FIELDS OF TABLE ICAUFV

FROM CAUFV

WHERE PLNBEZ = P_MATNR.

LOOP AT ICAUFV.

WRITE:/ ICAUFV-AUFNR, ICAUFV-PLNBEZ.

ENDLOOP.

[Former Member]

You don't think that will have any poor effects on other transactions that use these tables?

-Shaunt

[RichHeilman]

There is something in the ABAP runtime called the optimizer. It looks at your select statement and the table and all available keys(indexes). The optimizer then chooses the best one by looking at the "where" clause and the keys(indexes). I believe that if there are any programs that will be affected by this, it will only make them better.

Regards,

Rich Heilman

[Former Member]

Just curious Rich, but did you actually try the query without having the index? how long does it take?

-Shaunt

[RichHeilman]

Hmmm......thats interesting. I deleted my index the other day. I just ran my program again and it ran really fast. I did not run it before I added the index the other day.

Is your program still running slow. If so, send me your code.

[Former Member]

I'm added the index to AFKO with mandt, plnbez as the index and the performance did not increase at all. For some reason AFKO has a lot of indices although none of them have plnbez in it. Why this is the case? I further attempted to query first AFKO and then AUFK to make it a little more obvious and it was even longer then the last time. Any ideas how to improve this???

data: xcaufv like caufv occurs 0 with header line.

==========================================

select * from caufv

into table xcaufv

where plnbez = part_no.

=======================================================

select * from afko

into corresponding fields of table xcaufv

where plnbez = part_no.

loop at xcaufv.

select single objnr werks

from aufk

into (xcaufv-objnr, xcaufv-werks)

where aufnr = xcaufv-aufnr.

========================================================

[RichHeilman]

When was the last time that you archived your production orders?

[Former Member]

Hi,

if you want to find all production orders for a material you must use afpo~matnr for the query. On 46c there exists an index on this field.

Or am I missing something?

regards

johannes

[RichHeilman]

Yes....I would have done the same thing, he wants to use view caufv. Not sure why he is having so much trouble. Putting a index on AFKO for PLNBEZ should have taken care of it.

Regards,

Rich Heilman

[Former Member]

If he wants use caufv he could try

SELECT * INTO CORRESPONDING FIELDS OF TABLE xcaufv

FROM caufv INNER JOIN afpo

ON caufv_{aufnr = afpo}aufnr

WHERE afpo~matnr = 'PART_NO'

AND afpo~posnr = '0001'.

regards

johannes

[RichHeilman]

that would work too!