topic Re: code correction... in Application Development and Automation Discussions

code correction...

Former Member — Thu, 28 Aug 2008 05:56:54 GMT

Dear Guru's

Below is the code i wrote to fetch some data.....

IST_DATA being my output table.....its taking a lot of time to execute. Can any one help me out weather i can wright it in any better way??

Data declration...

TYPES: BEGIN OF TY_DATA,

MATNR LIKE VBRP_VRPMA-MATNR,

FKDAT LIKE VBRP_VRPMA-FKDAT,

KUNAG LIKE VBRP_VRPMA-KUNAG,

VBELN LIKE VBRP_VRPMA-VBELN,

FKIMG LIKE VBRP_VRPMA-FKIMG,

VRKME LIKE VBRP_VRPMA-VRKME,

MEINS LIKE VBRP_VRPMA-MEINS,

FKLMG LIKE VBRP_VRPMA-FKLMG,

NTGEW LIKE VBRP_VRPMA-NTGEW,

VGBEL LIKE VBRP_VRPMA-VGBEL,

AUBEL LIKE VBRP_VRPMA-AUBEL,

ARKTX LIKE VBRP_VRPMA-ARKTX,

WERKS LIKE VBRP_VRPMA-WERKS,

WKREG LIKE VBRP_VRPMA-WKREG,

BWTAR LIKE VBRP_VRPMA-BWTAR,

OIC_DREGIO LIKE VBRP_VRPMA-OIC_DREGIO,

FKSTO LIKE VBRK-FKSTO,

KNUMV LIKE VBRK-KNUMV,

EXNUM LIKE J_1IEXCDTL-EXNUM, "Excise No.

EXBED LIKE J_1IEXCDTL-EXBED, "BED

EXAED LIKE J_1IEXCDTL-EXAED, "AED

EXSED LIKE J_1IEXCDTL-EXSED, "SED

ECS LIKE J_1IEXCDTL-ECS, "CESS

EXADDTAX1 LIKE J_1IEXCDTL-EXADDTAX1, SHNUMBER LIKE OIGSI-SHNUMBER,

VEHICLE LIKE OIGSV-VEHICLE, "Truck No.

KNUMP LIKE LIKP-KNUMP,

KNUMV1 LIKE VBRK-KNUMV,

VBELN1 LIKE VBFA-VBELN,

MBLNR LIKE MSEG-MBLNR,

MJAHR LIKE MSEG-MJAHR,

BELNR LIKE BKPF-BELNR, "PGI Acct Doc. No.

KBETR LIKE KONV-KBETR,

KWERT LIKE KONV-KWERT, "Freight

KWERT1 LIKE KONV-KWERT, "Base Value

KPEIN LIKE KONV-KPEIN,

KMEIN LIKE KONV-KMEIN,

INV_VAL LIKE KONV-KWERT, "Invoice Value

AWKEY_C LIKE BKPF-AWKEY,

VERPR LIKE MBEW-VERPR,

VMPEI LIKE MBEW-VMPEI,

GJAHR LIKE BKPF-GJAHR,

OTHERS LIKE KONV-KWERT,

HKONT LIKE BSIS-HKONT,

BSCHL LIKE BSIS-BSCHL,

DMBTR LIKE BSIS-DMBTR,

BUZID LIKE BSIS-BUZID,

END OF TY_DATA.

TYPES: BEGIN OF TY_BSIS,

GJAHR LIKE BSIS-GJAHR,

BELNR LIKE BSIS-BELNR,

HKONT LIKE BSIS-HKONT,

BSCHL LIKE BSIS-BSCHL,

DMBTR LIKE BSIS-DMBTR,

BUZID LIKE BSIS-BUZID,

END OF TY_BSIS.

TYPES: BEGIN OF TY_VBRK,

FKSTO LIKE VBRK-FKSTO,

KNUMV LIKE VBRK-KNUMV,

END OF TY_VBRK.

DATA : BEGIN OF IST_DMBTR OCCURS 0,

BELNR LIKE BSIS-BELNR,

GJAHR LIKE BSIS-GJAHR,

DMBTR LIKE BSIS-DMBTR,

END OF IST_DMBTR.

DATA: IST_DATA TYPE TY_DATA OCCURS 0 WITH HEADER LINE.

DATA: IST_BSIS TYPE TY_BSIS OCCURS 0 WITH HEADER LINE.

DATA: IST_VBRK TYPE TY_VBRK OCCURS 0 WITH HEADER LINE.

The code.

SELECT MATNR FKDAT KUNAG VBELN FKIMG VRKME MEINS FKLMG NTGEW VGBEL

AUBEL ARKTX WERKS WKREG BWTAR OIC_DREGIO

FROM VBRP_VRPMA

INTO CORRESPONDING FIELDS OF TABLE IST_DATA

WHERE FKDAT IN S_FKDAT

AND VBELN IN S_VBELN

AND WERKS IN S_WERKS

AND WKREG IN S_WKREG

AND MATNR IN S_MATNR

AND BWTAR IN S_BWTAR

AND KUNAG IN S_KUNAG

AND OIC_DREGIO IN S_OIC_D

AND FKART EQ 'ZSTO'.

IF IST_DATA[] IS NOT INITIAL.

LOOP AT IST_DATA.

CLEAR : WA_VALUE.

SELECT SINGLE FKSTO KNUMV FROM VBRK INTO

(IST_DATA-FKSTO, IST_DATA-KNUMV)

WHERE VBELN EQ IST_DATA-VBELN

AND FKSTO NE 'X'.

SELECT SINGLE EXNUM EXBED EXAED EXSED ECS EXADDTAX1 FROM J_1IEXCDTL

INTO (IST_DATA-EXNUM, IST_DATA-EXBED, IST_DATA-EXAED,

IST_DATA-EXSED, IST_DATA-ECS, IST_DATA-EXADDTAX1)

WHERE RDOC2 = IST_DATA-VBELN.

SELECT SINGLE SHNUMBER FROM OIGSI INTO IST_DATA-SHNUMBER

WHERE DOC_NUMBER = IST_DATA-VGBEL.

SELECT SINGLE VEHICLE FROM OIGSV INTO IST_DATA-VEHICLE

WHERE SHNUMBER = IST_DATA-SHNUMBER.

SELECT SINGLE KNUMP FROM LIKP INTO IST_DATA-KNUMP

WHERE VBELN = IST_DATA-VGBEL.

SELECT SINGLE KNUMV FROM EKKO INTO IST_DATA-KNUMV1

WHERE EBELN = IST_DATA-AUBEL.

SELECT SINGLE VBELN FROM VBFA INTO IST_DATA-VBELN1

WHERE VBELV = IST_DATA-VGBEL

AND VBTYP_N = 'R'.

SELECT SINGLE MBLNR MJAHR FROM MSEG INTO (IST_DATA-MBLNR,

IST_DATA-MJAHR) WHERE MBLNR = IST_DATA-VBELN1

AND OIVBELN = IST_DATA-VGBEL.

CONCATENATE IST_DATA-MBLNR IST_DATA-MJAHR INTO IST_DATA-AWKEY_C.

SELECT SINGLE BELNR GJAHR FROM BKPF INTO (IST_DATA-BELNR,

IST_DATA-GJAHR)

WHERE AWKEY = IST_DATA-AWKEY_C.

IF SY-SUBRC EQ 0.

SELECT SINGLE HKONT BSCHL DMBTR BUZID FROM BSIS INTO (IST_DATA-HKONT,

IST_DATA-BSCHL, IST_DATA-DMBTR, IST_DATA-BUZID)

WHERE BELNR = IST_DATA-BELNR

AND GJAHR = IST_DATA-GJAHR.

IF IST_DATA-BSCHL = '99' AND IST_DATA-BUZID = 'M'.

IST_DATA-KBETR = IST_DATA-DMBTR.

ENDIF.

IF IST_DATA-BSCHL = '50' AND IST_DATA-BUZID = 'F'

AND IST_DATA-HKONT = '0000162011'

OR IST_DATA-HKONT = '0000162010'.

IST_DATA-KWERT = IST_DATA-DMBTR.

ENDIF.

SELECT SUM( DMBTR ) FROM BSIS INTO IST_DATA-OTHERS

WHERE HKONT NOT IN ('0000162011', '0000162010', '0000345700',

'0000345600', '0000365170')

AND BELNR = IST_DATA-BELNR

AND GJAHR = IST_DATA-GJAHR.

ENDIF.

IST_DATA-INV_VAL = IST_DATA-KBETR + IST_DATA-EXBED + IST_DATA-EXAED

+ IST_DATA-EXSED + IST_DATA-ECS +

IST_DATA-EXADDTAX1 + IST_DATA-KWERT.

MODIFY IST_DATA.

CLEAR IST_DATA.

************************************

Thank U in advance.

Re: code correction...

Former Member — Thu, 28 Aug 2008 08:25:13 GMT

1) While selecting data from VBRP_VRPMA remove Into corresponding fields and have only the fields

which you fetch in the internal table in the same sequence you fetch.

2) Replace all the select single by getting all the records from VBELN

OIGSI,OIGSV,LIKP, EKKO,VBFA, MSEG using For all entries in seperate internale table and use read statement inside the loop with a binary

3) Loop data from VBRP_VRPMA and concatenate the value in seperate table and then get the value from BKPF

and get the re4spective records from BSIS , Do the same replace the select single with read statement inside the loop.

4) Use onlY Select Sum inside the loop.

Re: code correction...

valter_oliveira — Thu, 28 Aug 2008 11:15:21 GMT

Hello.

After analysing your code I can see problems in some queries (not using key):

1 - VBRP_VRPMA: OK (corresponding fields is not a BIG problem)

2 - VBRK: OK

3 - J_1IEXCDTL: NOT OK -> try to use index 007, which has TRNTYP and RDOC2.

4 - OIGSI: OK, using index DOC (doc_number)

5 - OIGSV: OK

6 - LIPS/EKKO/VBFA/MSEG: OK

7 - BKPF: NOT OK -> try to use index 4, which has AWTYP and AWKEY

8 - BSIS 1st: NOT OK -> try to use index 1, which has BUKRS, BELNR and GJAHR.

9 - BSIS 2nd: NOT OK -> try to use index 1, which has BUKRS, BELNR and GJAHR. Also, probably HKONT NOT IN can make a bad query too. Analyse in ST05 the difference between as it is and verifiing after query that field (removing in select).

When I say try to use, look at the bold field. Is the one you are not using. For example, even if you have 10 bukrs, insert the list like (and BUKRS IN ('BUK1','BUK2',...,'BUK10').

Believe me, this will make the difference.

Regards.

Valter Oliveira.

Re: code correction...

alison_lloyd — Mon, 01 Sep 2008 16:02:17 GMT

Also check with your functional consultant - surely VKORG sales organisation has a known value range you can use?

Re: code correction...

Former Member — Tue, 02 Sep 2008 08:26:11 GMT

Hi,

It will be better if you fetch all required data from database tables into internal tables based on select-options from se;ection screen before using loop. Then sort each internal tables based on fields you want to read in main loop.

Now you make a loop and read each internal table with binary search. It will improve your program drastically.

I hope it will be useful to you.

thanks