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.
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.
*&---------------------------------------------------------------------*
*& 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.