Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Production Order and Sale Order...

Former Member
0 Kudos

I have the folowing query to get the Production order qty. against the sale order and item, but this query makes my report very slow, is there any othe table from where i can get the sale order and production order join..

LOOP AT i_data.
     SELECT sum( b~psmng ) INTO i_data-psmng
        FROM aufk AS a
        INNER JOIN afpo AS b ON
         a~aufnr  = b~aufnr
        WHERE
         a~kdauf  = i_data-vbeln  AND
         a~kdpos  = i_data-posnr  AND
         b~matnr  = i_data-matnr.
modify i_data.
endloop.

abhishek

4 REPLIES 4

Former Member
0 Kudos

Hi Abhishek,

I am not sure if the select in itself is correct, but to improve the performance you might think of adding a FOR ALL ENTRIES instead of selecting within a LOOP..ENDLOOP.

Also, please check if the WHERE clause is hitting an index. That would help increase the performance.

Sudha

Former Member
0 Kudos

Hi Abhishek,

I can think of two approaches,

One - I see that AUFNR already exists in VBAP (Sales Order Item table), if this is the case and is filled properly then I think there is no need for join on AUFK to get the AUFNR before going to AFPO.

So if your i_data internal table is getting data from VBAP already then see whether it is possible to select AUFNR as well.

Then your select statement will directly go to AFPO.

Like,

LOOP AT i_data.

SELECT sum( psmng ) INTO i_data-psmng FROM afpo

WHERE aufnr EQ i_data-aufnr "Add it to the structure

AND matnr EQ i_data-matnr.

MODIFY i_data.

ENDLOOP.

Two - I see that there is an index (Sales Order/Item) on AFPO.

So no need to go to AUFK I guess..

Then,

LOOP AT i_data.

SELECT sum( psmng ) INTO i_data-psmng FROM afpo

WHERE kdauf EQ i_data-kdauf

AND kdpos EQ i_data-kdpos

AND matnr EQ i_data-matnr.

MODIFY i_data.

ENDLOOP.

Hope this helps..

Sri

sagar-acharya
Participant
0 Kudos

Hi,

It is not advisable to use SELECT statements within loop. Please try the following:

Define a new internal table:

TYPES:

BEGIN OF ty_temp,

kdauf TYPE kdauf,

kdpos TYPE kdpos,

matnr TYPE matnr,

psmng TYPE psmng,

END OF ty_temp.

DATA:

i_temp TYPE TABLE OF ty_temp,

wa_temp TYPE ty_temp.

SELECT akdauf akdpos bmatnr bpsmng

INTO CORRESPONDING FIELDS OF TABLE i_temp

FOR ALL ENTRIES IN i_data

FROM aufk AS a

INNER JOIN afpo AS b

WHERE a~kdauf EQ i_data-vbeln

AND a~kdpos EQ i_data-posnr

AND b~matnr EQ i_data-matnr.

SORT i_temp BY kdauf kdpos matnr.

LOOP AT i_data.

LOOP AT i_temp INTO wa_temp WHERE kdauf EQ i_data-kdauf

AND kdpos EQ i_data-kdpos

AND matnr EQ i_data-matnr.

i_data-psmng = i_data-psmng + wa_temp-psmng.

ENDLOOP.

MODIFY i_data TRANSPORTING psmng.

ENDLOOP.

0 Kudos

Hi,

It would be even better if you create a view with the above two tables and read from the view , automatic bufferring will be enable for the view and it will make your program much faster.

regards,

Kunal