Skip to Content
0
Former Member
Dec 14, 2006 at 03:26 PM

Performance Issue

39 Views

Dear All.

Plz find the below report. But when I am running the following report its taking a lot of time to get executed. Can anybody suggest how the performance of the following report can be improved. Please find the report below:

REPORT ZFI_OPEN_ORDER_QTY NO STANDARD PAGE HEADING LINE-SIZE 500 .

TYPE-POOLS: SLIS.

*****TABLES USED

TABLES: VBAK, "Sales Document: Header Data

VBAP, "Sales Document: Item Data

MARA, "General Material Data

VBBE, "Sales Requirements: Individual Records

MAKT, "Material Descriptions

MCHB. "Batch Stocks

  • ALV DECLARATIONS

DATA: FIELDLAYOUT TYPE SLIS_LAYOUT_ALV,

L_FCAT TYPE SLIS_FIELDCAT_ALV,

T_FCAT TYPE SLIS_T_FIELDCAT_ALV,

L_REPID LIKE SY-REPID.

DATA: BEGIN OF I_VBAK OCCURS 0,

VKORG LIKE VBAK-VKORG,

VTWEG LIKE VBAK-VTWEG,

AUART LIKE VBAK-AUART,

VDATU LIKE VBAK-VDATU,

VBELN LIKE VBAK-VBELN,

KUNNR LIKE VBAK-KUNNR,

LIFSK LIKE VBAK-LIFSK,

FLAG(1),

END OF I_VBAK.

DATA: BEGIN OF I_VBAP OCCURS 0,

VBELN LIKE VBAP-VBELN,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

FLAG(1),

END OF I_VBAP.

DATA: I_VBAP1 LIKE I_VBAP OCCURS 0 WITH HEADER LINE,

I_VBAP2 LIKE I_VBAP OCCURS 0 WITH HEADER LINE.

DATA: BEGIN OF I_VBBE OCCURS 0,

VBELN LIKE VBBE-VBELN,

POSNR LIKE VBBE-POSNR,

MATNR LIKE VBBE-MATNR,

OMENG LIKE VBBE-OMENG,

END OF I_VBBE.

DATA: I_VBBE1 LIKE I_VBBE OCCURS 0 WITH HEADER LINE,

I_VBBE2 LIKE I_VBBE OCCURS 0 WITH HEADER LINE.

DATA: BEGIN OF I_VBBE_M OCCURS 0,

MATNR LIKE VBBE-MATNR,

OMENG LIKE VBBE-OMENG,

END OF I_VBBE_M.

DATA: I_VBBE1_M LIKE I_VBBE_M OCCURS 0 WITH HEADER LINE,

I_VBBE2_M LIKE I_VBBE_M OCCURS 0 WITH HEADER LINE,

I_VBBEDEL1 LIKE I_VBBE OCCURS 0 WITH HEADER LINE,

I_VBBEDEL2 LIKE I_VBBE_M OCCURS 0 WITH HEADER LINE.

DATA: BEGIN OF I_LIKP OCCURS 0,

VBELN LIKE LIKP-VBELN,

LFART LIKE LIKP-LFART,

ERDAT LIKE LIKP-ERDAT,

VKORG LIKE LIKP-VKORG,

END OF I_LIKP.

DATA: BEGIN OF I_MATNR OCCURS 0,

MATNR LIKE VBAP-MATNR,

END OF I_MATNR.

DATA: BEGIN OF I_MCHB OCCURS 0,

MATNR LIKE MCHB-MATNR,

CLABS LIKE MCHB-CLABS,

CINSM LIKE MCHB-CINSM,

END OF I_MCHB.

DATA: BEGIN OF I_MCHB1 OCCURS 0,

MATNR LIKE MCHB-MATNR,

CLABS LIKE MCHB-CLABS,

END OF I_MCHB1.

DATA: BEGIN OF I_MCHB2 OCCURS 0,

MATNR LIKE MCHB-MATNR,

CINSM LIKE MCHB-CINSM,

END OF I_MCHB2.

DATA: BEGIN OF I_FINAL OCCURS 0,

MATNR LIKE VBAP-MATNR,

MAKTX LIKE MAKT-MAKTX,

OMENG1 LIKE VBBE-OMENG,

OMENG3 LIKE VBBE-OMENG,

CLABS LIKE MCHB-CLABS,

BCK_ORD LIKE VBBE-OMENG,

CINSM LIKE MCHB-CINSM,

OMENG2 LIKE VBBE-OMENG,

SHRT_SUPP LIKE VBBE-OMENG,

END OF I_FINAL.

DATA: I_FDCT LIKE I_FINAL OCCURS 0 WITH HEADER LINE.

RANGES: R_VDATU FOR VBAK-VDATU,

R_ERDAT FOR LIKP-ERDAT,

R_VMSTA FOR MVKE-VMSTA.

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

*****Selection Screen *

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

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS:

S_VKORG FOR VBAK-VKORG OBLIGATORY, "Sales Org

S_VTWEG FOR VBAK-VTWEG, "Dist Channel

S_AUART FOR VBAK-AUART, "Sales Doc Type

S_VDATU1 FOR VBAK-VDATU OBLIGATORY NO-EXTENSION,

"Open Order Period 1

S_VDATU2 FOR VBAK-VDATU OBLIGATORY NO-EXTENSION,

"Open Order Period 2

S_VBELN FOR VBAK-VBELN, "Sales Order Number

S_MATNR FOR VBAP-MATNR NO INTERVALS, "Material

S_BISMT FOR MARA-BISMT, "Old Material Number

S_KUNNR FOR VBAK-KUNNR, "Sold to Party

S_LIFSK FOR VBAK-LIFSK, "Delivery Block

S_ABGRU FOR VBAP-ABGRU, "Reason or Rejection

S_WERKS FOR VBAP-WERKS, "Plant

S_LGORT FOR VBAP-LGORT. "Storage Location

SELECTION-SCREEN END OF BLOCK B1.

AT SELECTION-SCREEN.

IF NOT S_VDATU1-HIGH IS INITIAL.

IF S_VDATU1-LOW > S_VDATU1-HIGH.

MESSAGE E650(DB).

ENDIF.

IF S_VDATU2-LOW < S_VDATU1-HIGH.

MESSAGE E001(EBPPLOG) WITH

'period 1 should be less than period 2'.

ENDIF.

ELSE.

IF S_VDATU2-LOW < S_VDATU1-LOW.

MESSAGE E001(EBPPLOG) WITH

'period 1 should be less than period 2'.

ENDIF.

ENDIF.

IF NOT S_VDATU2-HIGH IS INITIAL.

IF S_VDATU2-LOW > S_VDATU2-HIGH.

MESSAGE E650(DB).

ENDIF.

ENDIF.

START-OF-SELECTION.

IF S_MATNR-LOW IS INITIAL.

PERFORM GET_MATERIAL.

ENDIF.

PERFORM GET_VBAK.

PERFORM ACCUMULATE_DATA.

PERFORM DISPLAY.

&----


*& Form get_vbak

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM GET_VBAK.

CLEAR R_ERDAT.

R_ERDAT-SIGN = 'I'.

R_ERDAT-OPTION = 'BT'.

R_ERDAT-LOW = SY-DATUM - 61.

R_ERDAT-HIGH = SY-DATUM + 1.

APPEND R_ERDAT.

CLEAR R_VDATU.

R_VDATU-SIGN = 'I'.

R_VDATU-OPTION = 'BT'.

R_VDATU-LOW = S_VDATU1-LOW.

R_VDATU-HIGH = S_VDATU1-HIGH.

APPEND R_VDATU.

CLEAR R_VDATU.

R_VDATU-SIGN = 'I'.

R_VDATU-OPTION = 'BT'.

R_VDATU-LOW = S_VDATU2-LOW.

R_VDATU-HIGH = S_VDATU2-HIGH.

APPEND R_VDATU.

CLEAR R_VDATU.

SELECT VKORG VTWEG AUART VDATU VBELN KUNNR LIFSK FROM VBAK INTO TABLE

I_VBAK WHERE VKORG IN S_VKORG AND

VTWEG IN S_VTWEG AND

AUART IN S_AUART AND

VDATU IN R_VDATU AND

VBELN IN S_VBELN AND

KUNNR IN S_KUNNR AND

LIFSK IN S_LIFSK.

LOOP AT I_VBAK WHERE VDATU IN S_VDATU1.

I_VBAK-FLAG = 'X'.

MODIFY I_VBAK index sy-tabix.

ENDLOOP.

LOOP AT I_VBAK WHERE VDATU IN S_VDATU2.

I_VBAK-FLAG = 'Y'.

MODIFY I_VBAK index sy-tabix.

ENDLOOP.

IF NOT I_VBAK[] IS INITIAL.

SELECT VBELN POSNR MATNR FROM VBAP INTO CORRESPONDING FIELDS OF TABLE

I_VBAP FOR ALL ENTRIES IN I_VBAK

WHERE VBELN = I_VBAK-VBELN

AND MATNR IN S_MATNR.

ENDIF.

LOOP AT I_VBAP.

READ TABLE I_VBAK WITH KEY VBELN = I_VBAP-VBELN.

IF SY-SUBRC EQ 0.

I_VBAP-FLAG = I_VBAK-FLAG.

MODIFY I_VBAP.

IF I_VBAP-FLAG = 'X'.

MOVE-CORRESPONDING I_VBAP TO I_VBAP1.

APPEND I_VBAP1.

ELSE.

MOVE-CORRESPONDING I_VBAP TO I_VBAP2.

APPEND I_VBAP2.

ENDIF.

ENDIF.

ENDLOOP.

*1.for open order quantity1

IF NOT I_VBAP1[] IS INITIAL.

SELECT VBELN POSNR MATNR OMENG FROM VBBE INTO TABLE I_VBBE1 FOR ALL

ENTRIES IN I_VBAP1 WHERE VBELN = I_VBAP1-VBELN

AND POSNR = I_VBAP1-POSNR

AND MATNR = I_VBAP1-MATNR.

ENDIF.

LOOP AT I_VBBE1.

I_VBBE1_M-MATNR = I_VBBE1-MATNR.

I_VBBE1_M-OMENG = I_VBBE1-OMENG.

COLLECT I_VBBE1_M.

ENDLOOP.

*2.for open order quantity2

IF NOT I_VBAP2[] IS INITIAL.

SELECT VBELN POSNR MATNR OMENG FROM VBBE INTO TABLE I_VBBE2 FOR ALL

ENTRIES IN I_VBAP2 WHERE VBELN = I_VBAP2-VBELN

AND POSNR = I_VBAP2-POSNR

AND MATNR = I_VBAP2-MATNR.

ENDIF.

LOOP AT I_VBBE2.

I_VBBE2_M-MATNR = I_VBBE2-MATNR.

I_VBBE2_M-OMENG = I_VBBE2-OMENG.

COLLECT I_VBBE2_M.

ENDLOOP.

*3.for open delivery quantity

SELECT VBELN LFART ERDAT VKORG FROM LIKP INTO CORRESPONDING FIELDS OF

TABLE I_LIKP WHERE LFART = 'ZULF'

AND ERDAT IN R_ERDAT

AND VKORG IN S_VKORG.

IF NOT I_LIKP[] IS INITIAL.

SELECT VBELN POSNR MATNR OMENG FROM VBBE INTO CORRESPONDING FIELDS

OF TABLE I_VBBEDEL1 FOR ALL ENTRIES IN I_LIKP

WHERE VBELN = I_LIKP-VBELN

AND MATNR IN S_MATNR.

ENDIF.

LOOP AT I_VBBEDEL1.

I_VBBEDEL2-MATNR = I_VBBEDEL1-MATNR.

I_VBBEDEL2-OMENG = I_VBBEDEL1-OMENG.

COLLECT I_VBBEDEL2.

CLEAR I_VBBEDEL2.

ENDLOOP.

*4.for material stocks

SELECT MATNR CLABS CINSM FROM MCHB INTO CORRESPONDING FIELDS OF

TABLE I_MCHB WHERE MATNR IN S_MATNR

AND WERKS IN S_WERKS

AND LGORT IN S_LGORT.

LOOP AT I_MCHB.

I_MCHB1-MATNR = I_MCHB-MATNR.

I_MCHB1-CLABS = I_MCHB-CLABS.

COLLECT I_MCHB1.

CLEAR I_MCHB1.

ENDLOOP.

*5.for inspection quantity.

LOOP AT I_MCHB.

I_MCHB2-MATNR = I_MCHB-MATNR.

I_MCHB2-CINSM = I_MCHB-CINSM.

COLLECT I_MCHB2.

CLEAR I_MCHB2.

ENDLOOP.

ENDFORM. " get_vbak

&----


*& Form GET_MATERIAL

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM GET_MATERIAL.

CLEAR R_VMSTA.

R_VMSTA-SIGN = 'I'.

R_VMSTA-OPTION = 'EQ'.

R_VMSTA-LOW = '05'.

APPEND R_VMSTA.

CLEAR R_VMSTA.

R_VMSTA-SIGN = 'I'.

R_VMSTA-OPTION = 'EQ'.

R_VMSTA-LOW = '11'.

APPEND R_VMSTA.

CLEAR R_VMSTA.

R_VMSTA-SIGN = 'I'.

R_VMSTA-OPTION = 'EQ'.

R_VMSTA-LOW = '91'.

APPEND R_VMSTA.

CLEAR R_VMSTA.

R_VMSTA-SIGN = 'I'.

R_VMSTA-OPTION = 'EQ'.

R_VMSTA-LOW = '92'.

APPEND R_VMSTA.

CLEAR R_VMSTA.

CLEAR: I_MATNR,I_MATNR[].

SELECT C~MATNR INTO TABLE I_MATNR FROM MVKE AS A

JOIN MARC AS B

ON AMATNR = BMATNR

JOIN MARA AS C

ON BMATNR = CMATNR

WHERE A~VKORG IN S_VKORG

AND A~VTWEG IN S_VTWEG

AND A~VMSTA IN R_VMSTA

AND A~LVORM = ' '

AND B~WERKS IN S_WERKS

AND B~LVORM = ' '

AND C~BISMT IN S_BISMT

AND C~LVORM = ' '

AND C~ENTAR = ' ' .

SORT I_MATNR.

DELETE ADJACENT DUPLICATES FROM I_MATNR.

LOOP AT I_MATNR.

CLEAR S_MATNR.

S_MATNR-SIGN = 'I'.

S_MATNR-OPTION = 'EQ'.

S_MATNR-LOW = I_MATNR-MATNR.

APPEND S_MATNR.

CLEAR S_MATNR.

ENDLOOP.

ENDFORM. " GET_MATERIAL

&----


*& Form ACCUMULATE_DATA

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM ACCUMULATE_DATA.

LOOP AT S_MATNR.

*1 for material

I_FINAL-MATNR = S_MATNR-LOW.

*2 for material description

SELECT SINGLE MAKTX FROM MAKT INTO I_FINAL-MAKTX

WHERE MATNR = S_MATNR-LOW

AND SPRAS = SY-LANGU.

*3 for open order quantity1

READ TABLE I_VBBE1_M WITH KEY MATNR = S_MATNR-LOW.

IF SY-SUBRC EQ 0.

I_FINAL-OMENG1 = I_VBBE1_M-OMENG.

ENDIF.

*4.for open delivery quantity

READ TABLE I_VBBEDEL2 WITH KEY MATNR = S_MATNR-LOW.

IF SY-SUBRC EQ 0.

I_FINAL-OMENG3 = I_VBBEDEL2-OMENG.

ENDIF .

*5.for material stocks

READ TABLE I_MCHB1 WITH KEY MATNR = S_MATNR-LOW.

IF SY-SUBRC EQ 0.

I_FINAL-CLABS = I_MCHB1-CLABS.

ENDIF .

*6.for Back Order

I_FINAL-BCK_ORD =

I_FINAL-OMENG1 + I_FINAL-OMENG3 - I_FINAL-CLABS.

IF I_FINAL-BCK_ORD LT 0.

I_FINAL-BCK_ORD = 0.

ENDIF.

*7.for inspection quantity

READ TABLE I_MCHB2 WITH KEY MATNR = S_MATNR-LOW.

IF SY-SUBRC EQ 0.

I_FINAL-CINSM = I_MCHB2-CINSM.

ENDIF .

*8.for open order quantity2

READ TABLE I_VBBE2_M WITH KEY MATNR = S_MATNR-LOW.

IF SY-SUBRC EQ 0.

I_FINAL-OMENG2 = I_VBBE2_M-OMENG.

ENDIF .

*9.for Short Supply

I_FINAL-SHRT_SUPP = ( I_FINAL-OMENG1 + I_FINAL-OMENG3 +

I_FINAL-OMENG2 ) - ( I_FINAL-CLABS + I_FINAL-CINSM ).

IF I_FINAL-SHRT_SUPP LT 0.

I_FINAL-SHRT_SUPP = 0.

ENDIF.

APPEND I_FINAL.

CLEAR I_FINAL.

ENDLOOP.

ENDFORM. " ACCUMULATE_DATA

&----


*& Form DISPLAY

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM DISPLAY.

L_REPID = SY-REPID.

PERFORM GET_FIELDCAT.

FIELDLAYOUT-WINDOW_TITLEBAR = 'Open order quantity'.

FIELDLAYOUT-ZEBRA = 'X'.

FIELDLAYOUT-COLWIDTH_OPTIMIZE = 'X'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = L_REPID

IT_FIELDCAT = T_FCAT

IS_LAYOUT = FIELDLAYOUT

I_SAVE = 'A'

TABLES

T_OUTTAB = I_FINAL

EXCEPTIONS

PROGRAM_ERROR = 1

OTHERS = 2.

ENDFORM. " DISPLAY

&----


*& Form GET_FIELDCAT

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM GET_FIELDCAT.

DATA: OPEN1(50),

OPEN2(50).

IF S_VDATU1-HIGH IS INITIAL.

CONCATENATE 'Open Order Qty on' S_VDATU1-LOW INTO OPEN1

SEPARATED BY SPACE.

ELSE.

CONCATENATE'Open Order Qty b/w' S_VDATU1-LOW 'to'

S_VDATU1-HIGH INTO OPEN1 SEPARATED BY SPACE.

ENDIF.

IF S_VDATU2-HIGH IS INITIAL.

CONCATENATE 'Open Order Qty on' S_VDATU2-LOW INTO OPEN2

SEPARATED BY SPACE.

ELSE.

CONCATENATE'Open Order Qty b/w' S_VDATU2-LOW 'to'

S_VDATU2-HIGH INTO OPEN2 SEPARATED BY SPACE.

ENDIF.

PERFORM WRITE_FIELDCAT USING 'MATNR' 'I_FINAL'

'Material' '1' 'L' ' '.

PERFORM WRITE_FIELDCAT USING 'MAKTX' 'I_FINAL'

'Material Description' '2' 'L' ' '.

PERFORM WRITE_FIELDCAT USING 'OMENG1' 'I_FINAL'

OPEN1 '3' 'L' 'X'.

PERFORM WRITE_FIELDCAT USING 'OMENG3' 'I_FINAL'

'Open Delivery Qty' '4' 'L' 'X'.

PERFORM WRITE_FIELDCAT USING 'CLABS' 'I_FINAL'

'Material Stock' '5' 'L' 'X'.

PERFORM WRITE_FIELDCAT USING 'BCK_ORD' 'I_FINAL'

'Back Order' '6' 'L' 'X'.

PERFORM WRITE_FIELDCAT USING 'CINSM' 'I_FINAL'

'Q.I.Quantity' '7' 'L' 'X'.

PERFORM WRITE_FIELDCAT USING 'OMENG2' 'I_FINAL'

OPEN2 '8' 'L' 'X'.

PERFORM WRITE_FIELDCAT USING 'SHRT_SUPP' 'I_FINAL'

'Short Supply' '9' 'L' 'X'.

  • IF NOT S_MWERKS-LOW IS INITIAL.

  • IF NOT S_MLGORT-LOW IS INITIAL.

  • PERFORM WRITE_FIELDCAT USING 'CLABS1' 'I_FINAL'

  • 'MFG plant Unrest Qty.' '10' 'L' 'X'.

  • PERFORM WRITE_FIELDCAT USING 'CINSM1' 'I_FINAL'

  • 'MFG plant Q.I Qty.' '11' 'L' 'X'.

  • ENDIF.

  • ENDIF.

ENDFORM. " GET_FIELDCAT

&----


*& FORM WRITE_FIELDCAT

&----


  • TEXT

----


  • --> P1 TEXT

  • <-- P2 TEXT

----


FORM WRITE_FIELDCAT USING NAME TAB TXT NUM JUST SUM.

L_FCAT-FIELDNAME = NAME.

L_FCAT-TABNAME = TAB.

L_FCAT-DDICTXT = 'L'.

L_FCAT-SELTEXT_L = TXT.

L_FCAT-SELTEXT_M = TXT.

L_FCAT-SELTEXT_S = TXT.

L_FCAT-COL_POS = NUM.

L_FCAT-JUST = JUST.

L_FCAT-DO_SUM = SUM.

APPEND L_FCAT TO T_FCAT.

CLEAR L_FCAT.

ENDFORM. " WRITE_FIELDCAT

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

Thanks in advance,

Nishu