Performance tuning

[Former Member]

Hello Friends,

I have developed a report to display Routing , Component Allocation Verification Report.

It is taking hours to run this report.

Please suggest changes to be made to make it more efficient.

This program does not include any custom Tables.

Thanks,

Ster.

*&---------------------------------------------------------------------*
*& Report  ZCP0A128                                                        *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&                                                                     *
*&---------------------------------------------------------------------*

REPORT  ZCP0A128  NO STANDARD PAGE HEADING
                  LINE-SIZE  80
                  LINE-COUNT 65(0)
                  MESSAGE-ID ZZ.

*Tables declaration
************************************************************************
TABLES : MARC, "	Plant Data for Material
         MKAL,
         MARA. "	Material Descriptions

TYPE-POOLS: KKBLO,
            SLIS.

DATA:       DISVARIANT   LIKE DISVARIANT,
            EVENTCAT     TYPE SLIS_T_EVENT,
            EVENTCAT_LN  LIKE LINE OF EVENTCAT,
            FIELDCAT     TYPE SLIS_T_FIELDCAT_ALV,
            FIELDCAT_KKB TYPE KKBLO_T_FIELDCAT,
            FIELDCAT_LN  LIKE LINE OF FIELDCAT,
            KEYINFO      TYPE SLIS_KEYINFO_ALV,
            LAYOUT       TYPE SLIS_LAYOUT_ALV,
            LAYOUT_KKB   TYPE KKBLO_LAYOUT,
            PGM          LIKE SY-REPID,
            PRINTCAT     TYPE SLIS_PRINT_ALV,
            SORTCAT      TYPE SLIS_T_SORTINFO_ALV,
            SORTCAT_LN   LIKE LINE OF SORTCAT,

            BEGIN OF COLTAB OCCURS 50,
              FIELDNAME LIKE FIELDCAT_LN-FIELDNAME,
            END OF COLTAB.

**************************************************************************
*Types Declaration
**************************************************************************
TYPES :

 BEGIN OF S_MARC_MKAL,
     MATNR LIKE MARC-MATNR,
     WERKS LIKE MARC-WERKS,
     DISPO LIKE MARC-DISPO,
     ADATU LIKE MKAL-ADATU,
     BDATU LIKE MKAL-BDATU,
     PLNTY LIKE MKAL-PLNTY,
 END OF S_MARC_MKAL,

 BEGIN OF S_BOM,
     MATNR_PRIM LIKE MARA-MATNR,
     MATNR_BOM  LIKE MARA-MATNR,
     STLNR      LIKE STPO-STLNR,
     PLNTY      LIKE MKAL-PLNTY,
 END OF S_BOM,

 BEGIN OF S_PLPO,
   PLNTY LIKE PLPO-PLNTY,
   PLNNR LIKE PLPO-PLNNR,
   PLNKN LIKE PLPO-PLNKN,
   ZAEHL LIKE PLPO-ZAEHL,
   DATUV LIKE PLPO-DATUV,
   AENNR LIKE PLPO-AENNR,
   LOEKZ LIKE PLPO-LOEKZ,
   PARKZ LIKE PLPO-PARKZ,
   SUMNR LIKE PLPO-SUMNR,
   VORNR LIKE PLPO-VORNR,
   STEUS LIKE PLPO-STEUS,
   ARBID LIKE PLPO-ARBID,
   OBJTY LIKE PLPO-OBJTY,
   WERKS LIKE PLPO-WERKS,
END OF S_PLPO,

 BEGIN OF S_PLKO,
   PLNTY LIKE PLKO-PLNTY,
   PLNNR LIKE PLKO-PLNNR,
   PLNAL LIKE PLKO-PLNAL,
   ZAEHL LIKE PLKO-ZAEHL,
   AENNR LIKE PLKO-AENNR,
   LOEKZ LIKE PLKO-LOEKZ,
   PARKZ LIKE PLKO-PARKZ,
   VERWE LIKE PLKO-VERWE,
   WERKS LIKE PLKO-WERKS,
END OF S_PLKO,

BEGIN OF S_MAKT,
   MATNR LIKE MAKT-MATNR,
   MAKTX LIKE MAKT-MAKTX,
END OF S_MAKT.

DATA : BEGIN OF S_MAPL.
        INCLUDE STRUCTURE MAPL.
DATA : END OF S_MAPL.

**************************************************************************
*Internal table declaration
**************************************************************************
DATA :

   BEGIN OF T_OUTPUT OCCURS 0,
     WERKS     LIKE MARC-WERKS,	
     MATNR     LIKE MARC-MATNR,
     PLNNR     LIKE PLPO-PLNNR,
     MATNR_COM LIKE MARC-MATNR,
     MAKTX_COM LIKE MAKT-MAKTX,
     VORNR     LIKE PLPO-VORNR,
     PLNAL     LIKE PLKO-PLNAL,
     PLNTY     LIKE MKAL-PLNTY,
     STLNR     LIKE STPOX-STLNR,
     STLAL     LIKE STPOX-STLAL,
   END OF T_OUTPUT.

DATA : T_MARC_MKAL   TYPE TABLE OF S_MARC_MKAL,
       T_BOM         TYPE TABLE OF S_BOM,
       T_PLPO        TYPE TABLE OF S_PLPO,
       T_PLKO        TYPE TABLE OF S_PLKO,
       T_MAKT        TYPE TABLE OF S_MAKT,
       T_MAPL        LIKE S_MAPL OCCURS 0.

DATA BEGIN OF T_PLMZ OCCURS 0.
        INCLUDE STRUCTURE PLMZ.
DATA END OF T_PLMZ.

DATA BEGIN OF T_STKO OCCURS 0.
        INCLUDE STRUCTURE STKO.
DATA END OF T_STKO.

DATA : T_STPOV TYPE STANDARD TABLE OF STPOV.


***************************************************************************
**Work Area Declaration
***************************************************************************
DATA : W_MARC_MKAL LIKE LINE OF T_MARC_MKAL,
       W_BOM       LIKE LINE OF T_BOM,
       W_PLPO      LIKE LINE OF T_PLPO,
       W_PLKO      LIKE LINE OF T_PLKO,
       W_MAKT      LIKE LINE OF T_MAKT,
       W_MAPL      LIKE LINE OF T_MAPL.

DATA : W_STPOV     LIKE LINE OF T_STPOV.

**************************************************************************
*Variable Declaration
**************************************************************************
DATA: V_DATUV LIKE SY-DATUM,
      V_DATUB LIKE SY-DATUM VALUE '99990101',
      V_REPID TYPE SYREPID,
      V_TABIX LIKE SY-TABIX,
      V_FLG(1),
      V_STLAL TYPE STKO-STLAL.
*************************************************************************
*     CSTMAT: structure for BOM material display
DATA: BEGIN OF XTOPMAT.
        INCLUDE STRUCTURE CSTMAT.
DATA: END OF XTOPMAT.

*     STPOX: structure for BOM item extended
DATA: BEGIN OF XSTPOX OCCURS 0.
        INCLUDE STRUCTURE STPOX.
DATA: END OF XSTPOX.

DATA : T_XSTPOX LIKE XSTPOX OCCURS 0 WITH HEADER LINE.

*     CSCMAT: structure for BOM Explosion Materials
DATA: BEGIN OF XMATCAT OCCURS 0.
        INCLUDE STRUCTURE  CSCMAT.
DATA: END OF XMATCAT.

DATA : XFELD      LIKE CSDATA-XFELD.

**************************************************************************
*SELECTION-SCREEN
**************************************************************************
SELECTION-SCREEN : BEGIN OF BLOCK 1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS : P_WERKS FOR MARC-WERKS NO INTERVALS OBLIGATORY,
                 S_DISPO FOR MARC-DISPO OBLIGATORY,
                 S_MATNR FOR MARC-MATNR,
                 S_MTART FOR MARA-MTART,
                 P_ADATU FOR MKAL-ADATU NO INTERVALS OBLIGATORY
                                                     DEFAULT SY-DATUM,
                 P_BDATU FOR MKAL-BDATU NO INTERVALS OBLIGATORY
                                                     DEFAULT '99991231'.
PARAMETER: P_CAPID LIKE TC04-CAPID OBLIGATORY
                                   DEFAULT 'PP01'."Application identifier
SELECTION-SCREEN : END OF BLOCK 1.

* Parameter for list viewer display variant
*
PARAMETERS:    VARIANT  LIKE DISVARIANT-VARIANT.

DATA: PRINT          TYPE SLIS_PRINT_ALV.
**********************************************************************************

INITIALIZATION.
  V_REPID = SY-REPID.
  V_DATUV = SY-DATUM.
*----------------------------------------------------------------------*
* Set up constants and selection criteria                              *
*----------------------------------------------------------------------*
AT SELECTION-SCREEN ON VARIANT.
  CHECK NOT VARIANT IS INITIAL.
  PERFORM CHECK_VARIANT_EXISTENCE USING VARIANT 'U'.
*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR VARIANT.
  PERFORM F4_DISPLAY_VARIANT USING VARIANT 'U'.

START-OF-SELECTION.

  PERFORM EXTRACT_EXTRACT_DATA.
  PERFORM DATA_PROCESSING.

**************************************************************************
*END-OF-SELECTION
**************************************************************************
END-OF-SELECTION.
  IF NOT T_OUTPUT[] IS INITIAL.
    PERFORM OUTPUT_SCREEN1.
  ELSE.
    MESSAGE I999 WITH 'No Records Selected'.
  ENDIF.








*&---------------------------------------------------------------------*
*&      Form  EXTRACT_EXTRACT_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM EXTRACT_EXTRACT_DATA .
  SELECT MARC~MATNR
         MARC~WERKS
         MARC~DISPO
         MKAL~ADATU
         MKAL~BDATU
         MKAL~PLNTY
    INTO TABLE T_MARC_MKAL
    FROM MARC
   INNER JOIN MKAL
      ON MARC~MATNR = MKAL~MATNR
   INNER JOIN MARA
      ON MARC~MATNR = MARA~MATNR
   WHERE MARC~MATNR IN S_MATNR
     AND MARC~WERKS IN P_WERKS
     AND MARC~DISPO IN S_DISPO
     AND MARC~MMSTA <> 'OB'
     AND MARA~MTART IN S_MTART.

  SORT T_MARC_MKAL BY MATNR WERKS.
  DELETE ADJACENT DUPLICATES FROM T_MARC_MKAL COMPARING MATNR WERKS.

  LOOP AT T_MARC_MKAL INTO W_MARC_MKAL.
    T_STKO-STLAL = '01'.
    PERFORM BOM_USAGE.
    LOOP AT XSTPOX WHERE SANFE = 'X'.
      W_BOM-MATNR_PRIM = W_MARC_MKAL-MATNR.
      W_BOM-MATNR_BOM  = XSTPOX-IDNRK.
      W_BOM-STLNR      = XSTPOX-STLNR.
      W_BOM-PLNTY      = W_MARC_MKAL-PLNTY.
      APPEND W_BOM TO T_BOM.
      APPEND XSTPOX TO T_XSTPOX.
    ENDLOOP.
    V_STLAL = XSTPOX-STLAL.

    IF NOT XSTPOX[] IS INITIAL.
      SELECT *
        FROM STKO
        INTO TABLE T_STKO
         FOR ALL ENTRIES IN XSTPOX
       WHERE STLNR = XSTPOX-STLNR.
    ENDIF.

    LOOP AT T_STKO WHERE STLAL <> V_STLAL
                     AND STLAL <> '01'.
      PERFORM BOM_USAGE.
      DELETE XSTPOX WHERE STLAL <> T_STKO-STLAL.

      LOOP AT XSTPOX WHERE SANFE = 'X'.
        W_BOM-MATNR_PRIM = W_MARC_MKAL-MATNR.
        W_BOM-MATNR_BOM  = XSTPOX-IDNRK.
        W_BOM-STLNR      = XSTPOX-STLNR.
        W_BOM-PLNTY      = W_MARC_MKAL-PLNTY.
        APPEND W_BOM TO T_BOM.
        APPEND XSTPOX TO T_XSTPOX.
      ENDLOOP.
    ENDLOOP.
  ENDLOOP.

  IF NOT T_XSTPOX[] IS INITIAL.

    SELECT MATNR MAKTX
       FROM MAKT
       INTO TABLE T_MAKT
        FOR ALL ENTRIES IN T_BOM
      WHERE MATNR = T_BOM-MATNR_BOM.

    IF NOT T_MARC_MKAL[] IS INITIAL.

      SELECT *
        INTO TABLE T_MAPL
        FROM MAPL
         FOR ALL ENTRIES IN T_MARC_MKAL
       WHERE MATNR = T_MARC_MKAL-MATNR
         AND WERKS IN P_WERKS
         AND LOEKZ NE 'X'.

      IF NOT T_MAPL[] IS INITIAL.

        SELECT *
          INTO TABLE T_PLMZ
          FROM PLMZ
           FOR ALL ENTRIES IN T_MAPL
         WHERE PLNTY EQ T_MAPL-PLNTY
           AND PLNNR EQ T_MAPL-PLNNR
           AND PLNAL EQ T_MAPL-PLNAL
           AND DATUV LE SY-DATUM
           AND LOEKZ NE 'X'.

        SELECT PLNTY  PLNNR  PLNKN  ZAEHL
               DATUV  AENNR  LOEKZ  PARKZ  SUMNR
               VORNR  STEUS  ARBID  OBJTY  WERKS
          INTO TABLE T_PLPO
          FROM PLPO
           FOR ALL ENTRIES IN T_MAPL
         WHERE PLNTY = T_MAPL-PLNTY
           AND PLNNR = T_MAPL-PLNNR
           AND LOEKZ NE 'X'.

        SELECT PLNTY PLNNR PLNAL
               ZAEHL AENNR LOEKZ PARKZ
               VERWE WERKS
          INTO TABLE T_PLKO
          FROM PLKO
                 FOR ALL ENTRIES IN T_MAPL
               WHERE PLNTY = T_MAPL-PLNTY
                 AND PLNNR = T_MAPL-PLNNR
                 AND LOEKZ NE 'X'.

      ENDIF.
    ENDIF.
  ENDIF.
ENDFORM.                    " EXTRACT_EXTRACT_DATA

*&---------------------------------------------------------------------*
*&      Form  DATA_PROCESSING
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM DATA_PROCESSING .

  SORT T_BOM BY MATNR_BOM STLNR.
  SORT T_MAKT BY MATNR.
  SORT T_MAPL BY  MATNR WERKS.
  SORT T_PLMZ BY PLNTY PLNNR PLNAL STLNR STLKN.
  SORT T_PLPO BY PLNTY PLNNR PLNKN.

  LOOP AT T_XSTPOX.

    READ TABLE T_BOM INTO W_BOM WITH KEY MATNR_BOM = T_XSTPOX-IDNRK
                                             STLNR = T_XSTPOX-STLNR
                                                      BINARY SEARCH.

    T_OUTPUT-MATNR     = W_BOM-MATNR_PRIM.
    T_OUTPUT-PLNTY     = W_BOM-PLNTY.
    T_OUTPUT-MATNR_COM = W_BOM-MATNR_BOM.
    T_OUTPUT-WERKS     = P_WERKS-LOW.
    T_OUTPUT-STLNR     = T_XSTPOX-STLNR.
    T_OUTPUT-STLAL     = T_XSTPOX-STLAL.

    READ TABLE T_MAKT INTO W_MAKT WITH KEY MATNR = W_BOM-MATNR_BOM
                                           BINARY SEARCH.
    T_OUTPUT-MAKTX_COM = W_MAKT-MAKTX.

    LOOP AT T_MAPL INTO W_MAPL WHERE MATNR = W_BOM-MATNR_PRIM
                                 AND WERKS = P_WERKS-LOW.

      T_OUTPUT-PLNNR = W_MAPL-PLNNR.

      READ TABLE T_PLMZ WITH KEY PLNTY = W_MAPL-PLNTY
                                 PLNNR = W_MAPL-PLNNR
                                 PLNAL = W_MAPL-PLNAL
                                 STLNR = T_XSTPOX-STLNR
                                 STLKN = T_XSTPOX-STLKN
                                 BINARY SEARCH.

      READ TABLE T_PLPO INTO W_PLPO WITH KEY  PLNTY = W_MAPL-PLNTY
                                              PLNNR = W_MAPL-PLNNR
                                              PLNKN = T_PLMZ-PLNKN
                                              BINARY SEARCH.
      T_OUTPUT-VORNR = W_PLPO-VORNR.
      T_OUTPUT-PLNAL = W_MAPL-PLNAL.

      APPEND T_OUTPUT.
    ENDLOOP.
    CLEAR : T_OUTPUT, W_MAKT, W_MAPL, W_PLPO, T_PLMZ.

  ENDLOOP.
  .
ENDFORM.                    " DATA_PROCESSING
*&---------------------------------------------------------------------*
*&      Form  BOM_USAGE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM BOM_USAGE .

  CALL FUNCTION 'CS_BOM_EXPL_MAT_V2'
    EXPORTING
      FTREL                 = ' '
      ALTVO                 = ' '
      AUFSW                 = ' '
      AUSKZ                 = ' '
      BAGRP                 = ' '
      BEIKZ                 = ' '
      BESSL                 = ' '
      BGIXO                 = ' '
      BREMS                 = ' '
      CAPID                 = P_CAPID
      DATUV                 = V_DATUV
      EMENG                 = ' '
      ERSKZ                 = ' '
      ERSSL                 = ' '
      MBWLS                 = ' '
      MTNRV                 = W_MARC_MKAL-MATNR
      MDMPS                 = ' '
      MEHRS                 = ' '
      MMORY                 = ' '
      POSTP                 = ' '
      SANKO                 = ' '
      SANFR                 = ' '
      SANKA                 = ' '
      SANIN                 = ' '
      SANVS                 = ' '
      RNDKZ                 = ' '
      RVREL                 = ' '
      SCHGT                 = ' '
      STKKZ                 = ' '
      STLAL                 = T_STKO-STLAL
      STLAN                 = '1'
      WERKS                 = P_WERKS-LOW
    IMPORTING
      TOPMAT                = XTOPMAT
      DSTST                 = XFELD
    TABLES
      STB                   = XSTPOX
      MATCAT                = XMATCAT
    EXCEPTIONS
      ALT_NOT_FOUND         = 1
      CALL_INVALID          = 2
      MATERIAL_NOT_FOUND    = 3
      MISSING_AUTHORIZATION = 4
      NO_BOM_FOUND          = 5
      NO_PLANT_DATA         = 6
      NO_SUITABLE_BOM_FOUND = 7
      OTHERS                = 8.

ENDFORM.                    " BOM_USAGE
*&---------------------------------------------------------------------*
*&      Form  OUTPUT_SCREEN1
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM OUTPUT_SCREEN1 .

  PERFORM CALL_LIST_VIEWER.

ENDFORM.                    " OUTPUT_SCREEN1

*&---------------------------------------------------------------------*
*&      Form  CALL_LIST_VIEWER
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*

FORM CALL_LIST_VIEWER .

  CLEAR: T_OUTPUT.

  PERFORM BUILD_FIELDCAT USING:
         'WERKS' 'T_OUTPUT'
          TEXT-001 'CHAR'       5 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ',
         'MATNR' 'T_OUTPUT'
          TEXT-002 'CHAR'      18 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ',
         'PLNTY' 'T_OUTPUT'
          TEXT-008 'CHAR'       4 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ',
         'PLNNR' 'T_OUTPUT'
          TEXT-003 'CHAR'      10 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ',
         'PLNAL' 'T_OUTPUT'
          TEXT-007 'CHAR'       5 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ',
         'STLNR' 'T_OUTPUT'
          TEXT-009 'CHAR'      10 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ',
         'STLAL' 'T_OUTPUT'
          TEXT-010 'CHAR'       7 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ',
         'MATNR_COM' 'T_OUTPUT'
          TEXT-004 'CHAR'      18 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ',
         'MAKTX_COM' 'T_OUTPUT'
          TEXT-005 'CHAR'      40 ' ' 'X' ' ' ' ' ' ' ' ' ' ' ' ',
         'VORNR' 'T_OUTPUT'
          TEXT-006 'CHAR'      10 ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '.


  PERFORM BUILD_EVENTCAT USING: 'TOP_OF_LIST',
                                'TOP_OF_PAGE',
                                'USER_COMMAND'.
  SORT T_OUTPUT BY
       WERKS MATNR PLNTY PLNNR PLNAL STLNR STLAL.

  PERFORM LIST_DISPLAY TABLES T_OUTPUT.

ENDFORM.                    "call_list_viewer

*----------------------------------------------------------------------*
* build field catalog entry                                            *
*----------------------------------------------------------------------*
FORM BUILD_FIELDCAT USING A_FIELDNAME
                          A_TABNAME
                          A_HEADING
                          A_DATATYPE
                          A_OUTPUTLEN
                          A_KEY
                          A_NO_OUT
                          A_NO_SUM
                          A_DO_SUM
                          A_NO_ZERO
                          A_INPUT
                          A_SUM
                          A_CHECKBOX.

  CLEAR FIELDCAT_LN.
  FIELDCAT_LN-FIELDNAME = A_FIELDNAME.
  FIELDCAT_LN-TABNAME  = A_TABNAME.
  FIELDCAT_LN-OUTPUTLEN = A_OUTPUTLEN.
  FIELDCAT_LN-DATATYPE = A_DATATYPE.
  FIELDCAT_LN-REPTEXT_DDIC = A_HEADING.
  FIELDCAT_LN-KEY = A_KEY.
  FIELDCAT_LN-NO_OUT = A_NO_OUT.
  FIELDCAT_LN-NO_SUM = A_NO_SUM.
  FIELDCAT_LN-DO_SUM = A_DO_SUM.
  FIELDCAT_LN-NO_ZERO = A_NO_ZERO.
  FIELDCAT_LN-INPUT = A_INPUT.
  FIELDCAT_LN-DO_SUM = A_SUM.
  FIELDCAT_LN-CHECKBOX = A_CHECKBOX.
  APPEND FIELDCAT_LN TO FIELDCAT.

ENDFORM.                    "BUILD_FIELDCAT
*----------------------------------------------------------------------*
*       FORM BUILD_EVENTCAT
*----------------------------------------------------------------------*
FORM BUILD_EVENTCAT USING A_EVENT.

  EVENTCAT_LN-NAME = EVENTCAT_LN-FORM = A_EVENT.
  APPEND EVENTCAT_LN TO EVENTCAT.

ENDFORM.                    "BUILD_EVENTCAT
*----------------------------------------------------------------------*
* call the ABAP list viewer                                            *
*----------------------------------------------------------------------*
FORM LIST_DISPLAY TABLES A_OUTPUT.

  PGM = DISVARIANT-REPORT = SY-REPID.
  DISVARIANT-VARIANT = VARIANT.
* call list viewer
  CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'
    EXPORTING
      I_CALLBACK_PROGRAM       = PGM
*      I_CALLBACK_PF_STATUS_SET = 'SET_PF_STATUS'
      IT_FIELDCAT              = FIELDCAT
      IS_VARIANT               = DISVARIANT
      IS_LAYOUT                = LAYOUT
      IS_PRINT                 = PRINT
      I_SAVE                   = 'A'
      IT_EVENTS                = EVENTCAT
      IT_SORT                  = SORTCAT
      I_CALLBACK_USER_COMMAND  = 'USER_COMMAND'
    TABLES
      T_OUTTAB                 = A_OUTPUT
    EXCEPTIONS
      PROGRAM_ERROR            = 1
      OTHERS                   = 2.

ENDFORM.                    "LIST_DISPLAY

*----------------------------------------------------------------------*
*       FORM CHECK_VARIANT_EXISTENCE
*----------------------------------------------------------------------*
*       Verify that a variant on the selection screen exists
*----------------------------------------------------------------------*
FORM CHECK_VARIANT_EXISTENCE USING VARNAME LIKE DISVARIANT-VARIANT
                                   SAVE   TYPE C.

  DATA: XDISVAR LIKE DISVARIANT.

  XDISVAR-REPORT  = SY-REPID.
  XDISVAR-VARIANT = VARNAME.

  CALL FUNCTION 'REUSE_ALV_VARIANT_EXISTENCE'
    EXPORTING
      I_SAVE        = SAVE
    CHANGING
      CS_VARIANT    = XDISVAR
    EXCEPTIONS
      WRONG_INPUT   = 1
      NOT_FOUND     = 2
      PROGRAM_ERROR = 3
      OTHERS        = 4.

  IF SY-SUBRC <> 0.
    MESSAGE E256(56) WITH VARNAME.
*   Anzeigevariante &1 nicht gefunden
  ENDIF.

  DISVARIANT-REPORT  = SY-REPID.
  DISVARIANT-VARIANT = VARNAME.

ENDFORM.                    "check_variant_existence
*----------------------------------------------------------------------*
*       Form  F4_DISPLAY_VARIANT
*----------------------------------------------------------------------*
*       F4 help to find a display variant
*       varname = name of the dynpro field for which f4 is requested
*       save    = type of list variant saving
*                 ' ' = no saving allowed
*                 'A' = standard & user specific variants can be saved
*                 'U' = only user specific variants can be saved
*                 'X' = only standard variants can be saved
*----------------------------------------------------------------------*
FORM F4_DISPLAY_VARIANT USING VARNAME LIKE DISVARIANT-VARIANT
                              SAVE    TYPE C.

  DISVARIANT-REPORT = SY-REPID.

  CALL FUNCTION 'REUSE_ALV_VARIANT_F4'
    EXPORTING
      IS_VARIANT    = DISVARIANT
      I_SAVE        = SAVE
    IMPORTING
      ES_VARIANT    = DISVARIANT
    EXCEPTIONS
      NOT_FOUND     = 1
      PROGRAM_ERROR = 2
      OTHERS        = 3.

  IF SY-SUBRC = 0.
    VARNAME = DISVARIANT-VARIANT.
  ELSE.
    MESSAGE S245(56).
*   Keine Anzeigevariante(n) vorhanden
  ENDIF.
ENDFORM.                               " F4_DISPLAY_VARIANT

[Former Member]

Hi Ster,

You have used <> at so many places in the where clauses first avoid that

there are 2 instances you had used Loop...indide loop... avoid that.

Avoid Select Queries inside the loops. Select data outside the loop and use read statements inside the loop.

Regards,

Satish

[Former Member]

Thanks Satish.

I always try to avoid select within a loop.

But in this case I am not able to avoid it.

Do you think it can be acheived by any alternative way?

Ster.

[Former Member]

For starters, I think you've got a cartesian product on your MARC-MKAL join, you need to be joining on WERKS as well.

Run a SQL trace using ST05 and use the output to pinpoint further issues

[Former Member]

Thanks Chris.

I will look at it.

Ster

[Former Member]

Hi Ster,

1) Select data using View V_LI_PA rather than Join condition on MARA, MKAL and MARC tables.

2) Remove Select statements from inside the loops. Rather use select for all entries.

3) Use View M_MAT1R for selecting on tables MAKT and MAPL.

4) Use View M_PLKSC for selecting on tables MAPL and PLKO.

Hope it helps.

Lokesh

[ferry_lianto]

Hi,

Please try this.

...

SELECT MARC~MATNR
         MARC~WERKS
         MARC~DISPO
         MKAL~ADATU
         MKAL~BDATU
         MKAL~PLNTY
    INTO TABLE T_MARC_MKAL
    FROM MARC
   INNER JOIN MKAL
      ON MARC~MATNR = MKAL~MATNR AND
         MARC~WERKS = MKAL~WERKS                "Add here
   INNER JOIN MARA
      ON MARC~MATNR = MARA~MATNR
   WHERE MARC~MATNR IN S_MATNR
     AND MARC~WERKS IN P_WERKS
     AND MARC~DISPO IN S_DISPO
     AND MARC~MMSTA <> 'OB'
     AND MARA~MTART IN S_MTART.

...

Regards,

Ferry Lianto

[Former Member]

Thanks Everyone.

I have done an SQL Trace and looks like it is taking time to fetch data from STKO table.

ANyone has better suggestions to get from stko.

Ster

[Former Member]

Avoid * in select Query and fetch data for required fields. Also try to use all the key fields in the where clause at least by passing blank ranges.

Regards,

Satish

[Former Member]

Then this should take care of it:

IF NOT xstpox[] IS INITIAL.
  SELECT *
    FROM stko
    INTO TABLE t_stko
     FOR ALL ENTRIES IN xstpox
   WHERE stlty IN ('D', 'E', 'K', 'M', 'S', 'T', 'P')   "<=== added
     AND stlnr = xstpox-stlnr.
ENDIF.

Rob

[Former Member]

Thanks Ferry and ROb,

I guess this should solve the problem.

I will try out and will get back.

Rob,

Can you explain why did u add this statement and have hardcoded this values.

Ster.

[Former Member]

I agree it seems a bit odd at first, but when you are missing one index field from a SELECT, putting that field in with all possible values (from the domain here) can get the SELECT to use the index.

Please see:

<a href="/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields">Using an Index When You Don't Have all of the Fields</a>

Rob

[ferry_lianto]

Hi,

You can read table MAST to get all key fields for STKO instead of PERFORM BOM_USAGE.

Perhaps you can add another join table MAST as part of populating internal table T_MARC_MKAL by adding fields STLAN, STLNR, STLAL.

Try something like this.

...

SELECT MARC~MATNR
       MARC~WERKS
       MARC~DISPO
       MKAL~ADATU
       MKAL~BDATU
       MKAL~PLNTY
       MAST~STLAN
       MAST~STLNR
       MAST~STLAL
INTO TABLE T_MARC_MKAL
FROM MARC
INNER JOIN MKAL
   ON MARC~MATNR = MKAL~MATNR AND
      MARC~WERKS = MKAL~WERKS                
INNER JOIN MARA
   ON MARC~MATNR = MARA~MATNR
INNER JOIN MAST
   ON MARC~MATNR = MAST~MATNR
      MARC~WERKS = MAST~WERKS
WHERE MARC~MATNR IN S_MATNR
  AND MARC~WERKS IN P_WERKS
  AND MARC~DISPO IN S_DISPO
  AND MARC~MMSTA <> 'OB'
  AND MARA~MTART IN S_MTART.

LOOP AT T_MARC_MKAL INTO W_MARC_MKAL.
  
     SELECT *
     FROM STKO
     INTO TABLE T_STKO
     WHERE STLTY =  W_MARC_MKAL-STLAN
       AND STLNR =  W_MARC_MKAL-STLNR 
       AND STLAL =  W_MARC_MKAL-STLAL.
...

Regards,

Ferry Lianto

[Former Member]

Thanks Ferry,

By using MAST table the run time has reduced a lot. Thanks for the help.

Ster..