12-02-2008 9:05 AM
here is my code . its taking 12 hours to complete for one plant which is having 14000 material for 300 days.
when i see in sm51 it is showing error in MKPF table.
any body can give any idea?? i shall be thankful.
SELECT MATNR WERKS FROM MARC INTO TABLE IT_MARC
WHERE MATNR IN S_MATNR
AND WERKS = P_WERKS.
IF IT_MARC[] IS NOT INITIAL.
SELECT MATNR MTART MATKL FROM MARA INTO TABLE IT_MARA
for all entries in IT_MARC
WHERE matnr = it_marc-matnr
and MTART in s_MTART
AND MATKL IN S_MATKL.
ENDIF.
LOOP AT IT_MARA.
IT_STOCK-MATNR = IT_MARA-MATNR.
IT_STOCK-WERKS = P_WERKS.
IT_STOCK-MTART = IT_MARA-MTART.
IT_STOCK-MATKL = IT_MARA-MATKL.
APPEND IT_STOCK.
ENDLOOP.
SELECT AMBLNR AMJAHR ABUDAT BZEILE BMATNR BMENGE BDMBTR BSHKZG BWERKS BBWART BXAUTO BBUSTW BSJAHR BSMBLN B~MEINS
INTO TABLE I_STK_GRGI
FROM MKPF AS A INNER JOIN MSEG AS B ON AMBLNR = BMBLNR
AND AMJAHR = BMJAHR
FOR ALL ENTRIES IN IT_STOCK
WHERE A~MBLNR BETWEEN '0000000001' AND '9999999999'
AND A~BUDAT BETWEEN L1_STDATE AND L_DATE
AND B~MATNR = IT_STOCK-MATNR
AND b~bwart NOT IN (311, 312)
AND B~WERKS = IT_STOCK-WERKS.
12-02-2008 9:15 AM
You don't make use of a good index for MKPF.
Why check on MBLN between 000..1 and 999.999 ? Leave it out !
Create an index on matnr and plant if it does not exist yest.
regards
Hans
12-02-2008 9:32 AM
You don't make use of a good index for MKPF.
how to use good index of MKPF.please let me know.
Why check on MBLN between 000..1 and 999.999 ? Leave it out !
this i am removing.
Create an index on matnr and plant if it does not exist yest.
i guess you are talking abt MSEG table.
i have some index for MSEG.
but no index is containing MATNR and WERKS only.
some indices having this field.
please elaborate.
regards
Chandramani
12-02-2008 9:30 AM
First and Second Query :
Reverse the order of your query, first query on MARA and than on MARC.
Collect all materials in IT_MARA first and use it in FOR ALL ENTRIES for query on MARC.
Third Query:
Try left table as MSEG and right as MKPF. If you are having secondary index on MATNR , WERKS etc...that will be helpful. (Generally that is standard sec index provided by SAP).
If you can have P_MJAHR as parameter on screen (obligatory parameter) that will also be very helpful.
Change your query as follows
SELECT A~MBLNR A~MJAHR A~BUDAT B~ZEILE B~MATNR B~MENGE B~DMBTR B~SHKZG B~WERKS B~BWART B~XAUTO B~BUSTW B~SJAHR B~SMBLN B~MEINS
INTO TABLE I_STK_GRGI
FROM MSEG AS B INNER JOIN MKPF AS A ON B~MBLNR = A~MBLNR
AND B~MJAHR = A~MJAHR
FOR ALL ENTRIES IN IT_STOCK
WHERE B~MATNR = IT_STOCK-MATNR
AND B~WERKS = IT_STOCK-WERKS
AND B~MJAHR = P_MJAHR
AND b~bwart NOT IN (311, 312)
AND A~BUDAT BETWEEN L1_STDATE AND L_DATE.
If you can use LGORT that will be very useful, it will be able to use more keys on index M. (Check if you have secondary index with this name which has key field as MATNR, WERKS, LGORT, BWART, SOBKZ) on MSEG...
Regards,
Mohaiyuddin
12-02-2008 9:35 AM
12-02-2008 10:26 AM
You will need to define one more parameter for YEAR - let's say P_MJAHR on your selection screen.
As MJAHR is one of the key field, this should be helpful.. Anyway if you can define LGORT as input parameter / select-option you should be able to use index on MSEG. (Provided index i had mentioned earlier is active on your table MSEG), in that case defining year as input parameter will be helpful but to limited extend
Regards,
Mohaiyuddin
12-08-2008 8:56 PM
Hi
Did you try the view V_MKPF to get the respective data from MKPF and MSEG?
Here are the fields of this view:
MBLNR
MJAHR
ZEILE
WERKS
VGART
BUDAT
BKTXT
Regards
Raj
12-10-2008 6:36 AM