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: 

Goods receipt report

Former Member
0 Kudos

Hello,

I wrote a report regarding materials that don't have a goods receipt in the last x days. It works , but I am not satisfied by the performance of the report.

First, I have selected all the materials that I consider as active in the sistem.

SELECT DISTINCT mara~matnr FROM mara

JOIN marc ON maramatnr = marcmatnr

JOIN mard ON maramatnr = mardmatnr

INTO CORRESPONDING FIELDS OF TABLE T_MATNR

WHERE mara~matnr IN so_matnr

AND marc~werks = p_plant

AND marc~ekgrp IN s_raion

AND mara~lvorm <> 'X'

AND mard~labst <> 0

ORDER BY mara~matnr.

After this selection I am verifiy each material selected before if it has a goods receipt in my conditions.

LOOP AT t_matnr INTO s_matnr.

SELECT single msegmatnr mkpfbudat FROM mseg

JOIN mkpf ON msegmblnr = mkpfmblnr

INTO CORRESPONDING FIELDS OF TABLE T_MATNRX

WHERE matnr = s_matnr-matnr AND bwart = '101'

AND mseg~werks = p_plant

AND mkpf~budat > my_date

ORDER BY budat DESCENDING.

DELETE t_matnrx FROM 3.

IF t_matnrx[] IS INITIAL.

APPEND s_matnr TO t_matnry.

ENDIF.

ENDLOOP.

This takes very long, because I have ~ 40000 items in MARA. What can I do to improove the performance?

3 REPLIES 3

Former Member
0 Kudos

Hi,

Negative conditions in WHERE clause are very harmful for performance as it bypass the indexes on the table.

SELECT DISTINCT mara~matnr FROM mara

JOIN marc ON maramatnr = marcmatnr

JOIN mard ON maramatnr = mardmatnr

INTO CORRESPONDING FIELDS OF TABLE T_MATNR

WHERE mara~matnr IN so_matnr

AND marc~werks = p_plant

AND marc~ekgrp IN s_raion

AND mara~lvorm <> 'X'

AND mard~labst <> 0

ORDER BY mara~matnr.

remove AND mara~lvorm <> 'X'

AND mard~labst <> 0 from where clause.

select all the records and then use delete itab where....

Secondly dont call select single inside the loop.

Try using joins or FOR ALL ENTRIES.

Hope it helps.

Regards,

Shashank

Former Member
0 Kudos

first try to arrenge the field in internal table in which way u fetch it .than u can remove into corresponding field.

SELECT DISTINCT mara~matnr FROM mara

JOIN marc ON maramatnr = marcmatnr

JOIN mard ON maramatnr = mardmatnr

INTO TABLE T_MATNR

WHERE mara~matnr IN so_matnr

AND marc~werks = p_plant

AND marc~ekgrp IN s_raion

AND mara~lvorm <> 'X'

AND mard~labst <> 0.

sort t_matnr by matnr.

if not t_matnr[] is inital.

SELECT single msegmatnr mkpfbudat FROM mseg

JOIN mkpf ON msegmblnr = mkpfmblnr

INTO TABLE T_MATNRX for all entries of t_matnr

WHERE matnr = s_matnr-matnr AND bwart = '101'

AND mseg~werks = p_plant

AND mkpf~budat > my_date

ORDER BY budat DESCENDING.

DELETE t_matnrx FROM 3.

IF t_matnrx[] IS INITIAL.

APPEND s_matnr TO t_matnry.

ENDIF.

Former Member
0 Kudos

Hello George,

Couple of options..

1. If u Logistic information system operational u can try some reports already provided by SAP like MC.A. or u can create ur own to suit ur requriement.

2. If what u have put as code is part of bigger picture then I would suggest u access MKPF MSEG and MARA instead of MARD/MARC.

3. Another option is using views...u can create view of MKPF/MSEG and MARA/MARC/MARD and use them in the select stmt. This will be much more effective as views are internally manged by database and hence will optimise the aceess to them.