Skip to Content

Requirement

I have got chance to work in BW migration project from 3.5 to 7.0. As per the project requirement, we had to migrate all BW 3.x queries into BI 7.x. In order to migrate we had major challenge to identify which are 3.x queries. No standard solution was identified in SAP BW for such requirement. Therefore I have written a small program which gives the list of 3.x queries as output.

Custom Utility – A program

Enter transaction code SE38 to write a utility program. Provide program name, description and other relevant information. Store program in package $tmp.

Insert the code as mentioned below.

Sample Code

*&———————————————————————*
*& Report  Z_LIST_OF_BWQUERY_DETAIL
*& Author – Rahul Goyal
*&———————————————————————*

*& This report in SAP BW provide the information about the Query like
*& Query Version (7.x/3.x) and transport request in which query is captured,
*& Transport request Status (Released/Modifiable). It will also provide the
*& general information about the query like Query GUID, Query Technical Name,
*& Infoprovider Name, Object status, Query READMODE, Owner of the query,
*& Last changed by and Transport owner.

*& This utility can be utilized in SAP BW migration project from 3.x to 7.x
*&———————————————————————*

REPORT  Z_LIST_OF_BWQUERY_DETAIL.

TYPE-POOLS : slis.

DATA: BEGIN OF i_e070 OCCURS 0,
      trkorr    LIKE e070-trkorr,
      trstatus  LIKE e070-trstatus,
      as4user   LIKE e070-as4user,
      as4date   LIKE e070-as4date,
      obj_name  LIKE e071-obj_name,
      compuid   LIKE rsrrepdir-compuid,
      END OF i_e070.

DATA: BEGIN OF i_query OCCURS 0,
      compuid   LIKE rsrrepdir-compuid,
      infocube  LIKE rsrrepdir-infocube,
      compid    LIKE rsrrepdir-compid,
      objstat   LIKE rsrrepdir-objstat,
      readmode  LIKE rsrrepdir-readmode,
      author    LIKE rsrrepdir-author,
      lastuser  LIKE rsrrepdir-lastuser,
      version   LIKE rszcompdir-version,
      END OF i_query.

DATA: BEGIN OF i_output OCCURS 0,
      compuid   LIKE rsrrepdir-compuid,
      infocube  LIKE rsrrepdir-infocube,
      compid    LIKE rsrrepdir-compid,
      objstat   LIKE rsrrepdir-objstat,
      readmode  LIKE rsrrepdir-readmode,
      author    LIKE rsrrepdir-author,
      lastuser  LIKE rsrrepdir-lastuser,
      version(3) TYPE c,
      trstatus  LIKE e070-trstatus,
      trkorr    LIKE e070-trkorr,
      as4user   LIKE e070-as4user,
      END OF i_output.

DATA:  wa_fieldcat TYPE slis_fieldcat_alv,
       it_fieldcat TYPE slis_t_fieldcat_alv.

SELECT a~trkorr a~trstatus a~as4user a~as4date b~obj_name
      FROM e070 AS a INNER JOIN e071 AS b ON a~trkorr = b~trkorr
      INTO TABLE i_e070
       WHERE  b~pgmid = ‘R3TR’
       AND  b~object = ‘ELEM’.

sort i_e070 DESCENDING by as4date.

LOOP AT i_e070.
  i_e070-compuid = i_e070-obj_name(25).
  MODIFY i_e070.
ENDLOOP.

SELECT a~compuid a~infocube a~compid a~objstat a~readmode a~author a~lastuser b~version
       FROM rsrrepdir AS a INNER JOIN rszcompdir AS b ON a~compuid = b~compuid
       INTO TABLE i_query
       WHERE   a~objvers = ‘A’
       AND   a~comptype = ‘REP’
       AND   b~objvers = ‘A’.

LOOP AT i_query.
  IF i_query-version > 100.
    i_output-version = ‘7.X’.
  ELSE.
    i_output-version = ‘3.X’.
  ENDIF.

  MOVE:
     i_query-compuid  TO i_output-compuid,
     i_query-infocube TO i_output-infocube,
     i_query-compid   TO i_output-compid,
     i_query-objstat  TO i_output-objstat,
     i_query-readmode TO i_output-readmode,
     i_query-author   TO i_output-author,
     i_query-lastuser TO i_output-lastuser.

  READ TABLE i_e070 WITH KEY compuid = i_query-compuid.

  IF sy-subrc = 0.

    MOVE:
        i_e070-trstatus to i_output-trstatus,
        i_e070-trkorr  TO i_output-trkorr,
        i_e070-as4user TO i_output-as4user.

  ENDIF.

  APPEND i_output.
ENDLOOP.

wa_fieldcat-fieldname = ‘COMPUID’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘QUERY GUID’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘INFOCUBE’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘INFOPROVIDER’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘COMPID’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘QUERY NAME’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘VERSION’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘QUERY VERSION’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘OBJSTAT’.
wa_fieldcat-ref_fieldname = ‘OBJSTAT’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘OBJECT STATUS’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘READMODE’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘READMODE’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘AUTHOR’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘OWNER’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘LASTUSER’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘LAST CHANGED BY’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘TRSTATUS’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘TR STATUS’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘TRKORR’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘TR #’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘AS4USER’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘TR OWNER’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’
  EXPORTING
*     I_INTERFACE_CHECK              = ’ ’
*     I_BYPASSING_BUFFER             =
*     I_BUFFER_ACTIVE                = ’ ’
*     I_CALLBACK_PROGRAM             = ’ ’
*     I_CALLBACK_PF_STATUS_SET       = ’ ’
*     I_CALLBACK_USER_COMMAND        = ’ ’
*     i_structure_name               =
*     is_layout                      =
    it_fieldcat                    = it_fieldcat[]
*     it_excluding                   =
*     it_special_groups              =
*     it_sort                        =
*     it_filter                      =
*     is_sel_hide                    =
*     i_default                      = ’x’
*     i_save                         = ’ ’
*     is_variant                     =
*     it_events                      =
*     it_event_exit                  =
*     is_print                       =
*     is_reprep_id                   =
*     i_screen_start_column          = 0
*     i_screen_start_line            = 0
*     i_screen_end_column            = 0
*     i_screen_end_line              = 0
*     ir_salv_list_adapter           =
*     it_except_qinfo                =
*     i_suppress_empty_data          = abap_false
*   IMPORTING
*     e_exit_caused_by_caller        =
*     es_exit_caused_by_user         =
  tables
    t_outtab                       = i_output
*   EXCEPTIONS
*     program_error                  = 1
*     OTHERS                         = 2
          .

IF sy-subrc <> 0.
  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF. 
 

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Stan Shuralyov
    I would suggest to include CLEAR statement in the LOOP to fix Transport assignment and some selections would be good as well (by Info Cube, User Name, Query Name etc. ):

    LOOP AT i_query.
    * Add ————
    CLEAR i_query.
    * —————-
      IF i_query-version > 100.
        i_output-version = ‘7.X’.
      ELSE.
        i_output-version = ‘3.X’.
      ENDIF.

      MOVE: …………….

    (0) 

Leave a Reply