当选择关键字不是MSEG的索引关键字时，用EKBE代替MSEG可以提高查询速度N倍！

*----------------------------------------------------------------------*
* Program Name          : ZKEE13                                       *
* Purpose               : check account for purchaser                  *
* Project Name          : Guan Xin                                     *
* Created by            : Kee(AsiaLink)                                *
* Create on             : 2006.08.23                                   *
* Functional Consultant :
* Description           :
*
*
*
*----------------------------------------------------------------------*
*    Modification Log                                                  *
*Date        Programmer     Corr. #      Description                   *
*2003/MM/DD  xxxxxxxxxx     xxxxxxxxxx   Initial pgm dev               *
*----------------------------------------------------------------------*
Report ZKEE13
        MESSAGE-ID ZASIA
        LINE-COUNT 50
        LINE-SIZE 132
        NO STANDARD PAGE HEADING.

*----------------------------------------------------------------------*
* Local Data Types in Program
*----------------------------------------------------------------------*

TYPES:  BEGIN OF TYP_ITAB,
        EBELN LIKE MSEG-EBELN,
        LIFNR LIKE MSEG-LIFNR,
        BEDAT LIKE EKKO-BEDAT,
        WERKS LIKE MSEG-WERKS,
        MATNR LIKE MSEG-MATNR,
        BUDAT LIKE MKPF-BUDAT,
        MBLNR LIKE MSEG-MBLNR,
        MENGE LIKE MSEG-MENGE,
        NETPR LIKE EKPO-NETPR,
        NETWR LIKE EKPO-NETWR,
        END OF TYP_ITAB.

*----------------------------------------------------------------------*
* TABLES WORK AREA
*----------------------------------------------------------------------*
TABLES: EKPO,MKPF,MSEG,EKBE,EKKO.
*----------------------------------------------------------------------*
* Selection Screen
*----------------------------------------------------------------------*
*SELECT-OPTIONS: ZVENDOR FOR MSEG-LIFNR.
*SELECT-OPTIONS: ZPLANT FOR MSEG-WERKS.
*SELECT-OPTIONS: ZMATNR FOR MSEG-MATNR.
*SELECT-OPTIONS: ZPONR FOR MSEG-EBELN.
*SELECT-OPTIONS: ZBUDAT FOR MKPF-BUDAT.
*SELECT-OPTIONS: ZMBLNR FOR MSEG-MBLNR MATCHCODE OBJECT MBMP.
SELECTION-SCREEN: BEGIN OF BLOCK B1 WITH FRAME.
SELECT-OPTIONS: ZVENDOR FOR EKKO-LIFNR.
SELECT-OPTIONS: ZPLANT FOR EKBE-WERKS.
SELECT-OPTIONS: ZMATNR FOR EKBE-MATNR.
SELECT-OPTIONS: ZPONR FOR EKBE-EBELN.
SELECT-OPTIONS: ZBUDAT FOR MKPF-BUDAT.
SELECT-OPTIONS: ZMBLNR FOR EKBE-BELNR MATCHCODE OBJECT MBMP.
SELECTION-SCREEN:END OF BLOCK B1.

 

*----------------------------------------------------------------------*
* Global Internal Tables Declaration
*----------------------------------------------------------------------*
DATA:   ITAB TYPE STANDARD TABLE OF  TYP_ITAB
                           WITH DEFAULT KEY WITH HEADER LINE.
DATA: TOTAL_PRICE LIKE EKPO-NETWR,
      PRICE LIKE EKPO-NETPR,
      TOTAL_LINE TYPE TYP_ITAB,
            INDX LIKE SY-TABIX,
        ITAB_SIZE LIKE SY-DBCNT,
        TOUCH_BOTTOM TYPE I VALUE 0.

TYPE-POOLS: slis.
DATA: it_sort TYPE slis_sortinfo_alv OCCURS 0,
           g_repid LIKE sy-repid,
           gt_fieldcat TYPE slis_t_fieldcat_alv,
           gs_layout TYPE slis_layout_alv,
           ls_fieldcat TYPE slis_fieldcat_alv,
           pos TYPE i VALUE 1,
           HF_USER_COMMAND TYPE SLIS_FORMNAME VALUE 'USER_COMMAND',
           HF_PF_STATUS TYPE SLIS_FORMNAME VALUE 'PF_STATUS'.

 

DATA: BEGIN OF it_sort_row OCCURS 0,
*        spos(2) type n,
           spos LIKE alvdynp-sortpos,
           fieldname TYPE slis_fieldname,
           tabname TYPE slis_fieldname,
*        up(1) type c,
*        down(1) type c,
*        group(2) type c,
*        subtot(1) type c,
           up LIKE alvdynp-sortup,
           down LIKE alvdynp-sortdown,
           group LIKE alvdynp-grouplevel,
           subtot LIKE alvdynp-subtotals,
           comp(1) TYPE c,
           expa(1) TYPE c,
           obligatory(1) TYPE c,
         END OF it_sort_row.

*----------------------------------------------------------------------*
* Define Macro
*----------------------------------------------------------------------*
DEFINE fieldcat_init.
*  USING rt_fieldcat TYPE slis_t_fieldcat_alv.
  clear ls_fieldcat.
  ls_fieldcat-col_pos       =  pos.
  ls_fieldcat-fieldname     = &1.
  ls_fieldcat-key           = &2.
  ls_fieldcat-seltext_l = &3.
  ls_fieldcat-seltext_m = &3.
*  ls_fieldcat-seltext_s = &3.
*  ls_fieldcat-outputlen = &4.
  ls_fieldcat-do_sum = &4.
  ls_fieldcat-outputlen = &5.
  ls_fieldcat-datatype = &6.
  append ls_fieldcat to gt_fieldcat.
  pos = pos + 1.
END-OF-DEFINITION.   "fieldcat_init

*----------------------------------------------------------------------*
* MAIN PROCESS
*----------------------------------------------------------------------*
START-OF-SELECTION.
   PERFORM GET_DATA.
   PERFORM GET_TOTAL.
END-OF-SELECTION.

PERFORM ALV_OUTPUT.

*----------------------------------------------------------------------*
* FORM GET_DATA
* QUERY DATA WITH INPUT DATA.
*----------------------------------------------------------------------*
FORM GET_DATA .
*  SELECT DISTINCT MSEG~LIFNR
*                 MSEG~WERKS
*                 MSEG~MATNR
*                 MSEG~EBELN
*                 MKPF~BUDAT
*                 MSEG~MBLNR
*                 MSEG~MENGE
*                 EKPO~NETPR
*                 EKPO~NETWR
*    INTO TABLE ITAB
*    FROM MSEG INNER JOIN MKPF ON MSEG~MBLNR = MKPF~MBLNR
*              INNER JOIN EKPO ON EKPO~EBELN = MSEG~EBELN AND EKPO~EBELP
*= MSEG~EBELP
*    WHERE MSEG~MBLNR IN ZMBLNR AND
*          MSEG~LIFNR IN ZVENDOR AND
*          MSEG~WERKS IN ZPLANT AND
*          MSEG~MATNR IN ZMATNR AND
*          MSEG~EBELN IN ZPONR AND
*          MKPF~BUDAT IN ZBUDAT.

  SELECT DISTINCT EKBE~EBELN
                  EKKO~LIFNR
                 EKKO~BEDAT
                 EKBE~WERKS
                 EKBE~MATNR
                 MKPF~BUDAT
                 EKBE~BELNR
                 EKBE~MENGE
                 EKPO~NETPR
                 EKPO~NETWR
    INTO TABLE ITAB
    FROM EKBE  INNER JOIN EKPO ON EKPO~EBELN = EKBE~EBELN  AND
EKPO~EBELP = EKBE~EBELP
                            INNER JOIN EKKO ON EKPO~EBELN = EKKO~EBELN
                            INNER JOIN MKPF ON EKBE~BUDAT = MKPF~BUDAT
    WHERE EKKO~LIFNR IN ZVENDOR AND
          EKBE~MATNR IN ZMATNR AND
          EKBE~BELNR IN ZMBLNR AND
          EKBE~WERKS IN ZPLANT AND
          EKBE~EBELN IN ZPONR AND
          MKPF~BUDAT IN ZBUDAT
     GROUP BY  EKBE~EBELN
                 EKBE~MATNR
                 EKKO~LIFNR
                 EKKO~BEDAT
                 EKBE~WERKS
                 MKPF~BUDAT
                 EKBE~BELNR
                 EKBE~MENGE
                 EKPO~NETPR
                 EKPO~NETWR.

*  IF SY-SUBRC = 4.
*    MESSAGE I000 WITH 'No Hits Found'.
**   LEAVE SCREEN.
*  ENDIF.
    ITAB_SIZE = SY-DBCNT.
ENDFORM.

*----------------------------------------------------------------------*
* FORM GET_TOTAL
* INSERT TOTAL PRICE LINE
*----------------------------------------------------------------------*
FORM GET_TOTAL.
  LOOP AT ITAB.
          AT NEW EBELN.
                TOUCH_BOTTOM = 0.
            ENDAT.
      AT END OF EBELN.
        IF SY-TABIX EQ ITAB_SIZE.
          IF TOUCH_BOTTOM = 0.
            SELECT SUM( NETWR ) SUM( NETPR ) INTO (TOTAL_PRICE, PRICE)
FROM EKPO WHERE EKPO~EBELN EQ ITAB-EBELN.
            TOTAL_LINE-EBELN = ITAB-EBELN.
            TOTAL_LINE-MATNR = '*'.
            TOTAL_LINE-NETWR = TOTAL_PRICE.
            TOTAL_LINE-NETPR = PRICE.
            INDX = SY-TABIX + 1.
            INSERT TOTAL_LINE INTO ITAB INDEX INDX.
            ITAB_SIZE = ITAB_SIZE + 1.
          ENDIF.
          TOUCH_BOTTOM = 1.
        ELSE.
          IF TOUCH_BOTTOM = 0.
          SELECT SUM( NETWR ) SUM( NETPR ) INTO (TOTAL_PRICE,PRICE)
FROM EKPO WHERE EKPO~EBELN EQ ITAB-EBELN.
          TOTAL_LINE-EBELN = ITAB-EBELN.
          TOTAL_LINE-MATNR = '*'.
          TOTAL_LINE-NETWR = TOTAL_PRICE.
          TOTAL_LINE-NETPR = PRICE.
          INDX = SY-TABIX + 1.
          INSERT TOTAL_LINE INTO ITAB INDEX INDX.
          ITAB_SIZE = ITAB_SIZE + 1.
         ENDIF.
                  TOUCH_BOTTOM = 1.
        ENDIF.
      ENDAT.
    ENDLOOP.
ENDFORM.

*----------------------------------------------------------------------*
* FORM ALV_OUTPUT
* DISPLAY QUERY RESULT WITH ALV FORM.EKKO~BEDAT
*----------------------------------------------------------------------*
FORM ALV_OUTPUT.
***************************Initialization*********************
  FIELDCAT_INIT: 'BEDAT' '' 'PO Date' '16' '' 'DATS'.
  FIELDCAT_INIT: 'LIFNR' '' 'Vender' '' '16' ''.
  FIELDCAT_INIT: 'WERKS' '' 'Plant' '' '10' ''.
  FIELDCAT_INIT: 'MATNR' 'X' 'Material Num' '' '' ''.
  FIELDCAT_INIT: 'EBELN' 'X' 'PO Num' '' '10' ''.
  FIELDCAT_INIT: 'BUDAT' '' 'Posting Date' '16' '' ''.
  FIELDCAT_INIT: 'MBLNR' '' 'Mat Doc Num' '' '16' ''.
  FIELDCAT_INIT: 'MENGE' '' 'Qty' '' '16' ''.
  FIELDCAT_INIT: 'NETPR' '' 'Price' '' '16' 'CURR'.
  FIELDCAT_INIT: 'NETWR' '' 'Total Price' '' '16' 'CURR'.

****************************Set Sort Table*********************
*    IT_SORT_ROW-FIELDNAME = 'EBELN'.
*    IT_SORT_ROW-SPOS = 1.
*    IT_SORT_ROW-UP = 'X'.
*    IT_SORT_ROW-SUBTOT = 'X'.
*    APPEND IT_SORT_ROW TO IT_SORT.

*    IT_SORT_ROW-FIELDNAME = 'MATNR'.
*    IT_SORT_ROW-SPOS = 2.
*    IT_SORT_ROW-UP = 'X'.
*    IT_SORT_ROW-SUBTOT = 'X'.
*    APPEND IT_SORT_ROW TO IT_SORT.

***************************Write Out***************************
    g_repid = sy-repid.
    gs_layout-zebra = 'X'.
    gs_layout-f2code = '&ETA'.
    gs_layout-detail_popup = 'X'.

    CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'
       EXPORTING
            i_callback_program = g_repid
            I_CALLBACK_PF_STATUS_SET = HF_PF_STATUS
            I_CALLBACK_USER_COMMAND  = HF_USER_COMMAND
            it_fieldcat        = gt_fieldcat
            is_layout          = gs_layout
*            it_events          = wtbl_event
            it_sort            = it_sort
       TABLES
            t_outtab           = ITAB.

ENDFORM.

FORM PF_STATUS USING  EXTAB TYPE SLIS_T_EXTAB.
  Set pf-Status  'STANDARD' EXCLUDING EXTAB.
ENDFORM.

*---------------------------------------------------------------------*
*       FORM user_command                                             *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  R_UCOMM                                                       *
*  -->  RS_SELFIELD                                                   *
*---------------------------------------------------------------------*
FORM user_command USING r_ucomm LIKE sy-ucomm
                  rs_selfield TYPE slis_selfield.

* Check function code
  CASE r_ucomm.
    WHEN '&ETA'.
*   Check field clicked on within ALVgrid report
    IF rs_selfield-fieldname = 'MATNR'.
      READ TABLE itab INDEX rs_selfield-tabindex.
*     Set parameter id for transaction screen field
      SET PARAMETER ID 'MAT' FIELD itab-matnr. "'5004'.
*     Execute transaction MM03,and skip initial data entry screen
      CALL TRANSACTION 'MM03' AND SKIP FIRST SCREEN.
    ELSEIF rs_selfield-fieldname = 'EBELN'.
       READ TABLE itab INDEX rs_selfield-tabindex.
       SET PARAMETER ID 'BES' FIELD itab-ebeln.
       CALL TRANSACTION 'ME23N' AND SKIP FIRST SCREEN.
    ELSEIF rs_selfield-fieldname = 'MBLNR'.
       READ TABLE itab INDEX rs_selfield-tabindex.
       SET PARAMETER ID 'MBN' FIELD itab-mblnr.
       CALL TRANSACTION 'MB03' AND SKIP FIRST SCREEN.
    ENDIF.
  ENDCASE.
ENDFORM.