By Rahul Goyal, L&T Infotech
Applies to:
SAP Business Intelligence Datawarehouse (BW) 7.x/ 3.x.
Summary
This document will explain about a utility in SAP BW which will provide the information about the Query like Query Version (7.x/3.x) and transport request detail for query.
Introduction
This utility is a custom program in SAP BW which will provide the unique information about the Query like:
It will also provide the generic 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.
Business Scenario
User wants to know the list of 3.x query and want to migrate into 7.x
User wants to know the transport request # of the query and want to transport if it is not yet moved into Quality and Production system.
Note:
Object Status
(ACT – Active and executable, INA – Inactive and not executable, OFF - Switched off, PRO - Productive)
Transport Status
(R – Released, D - Modifiable)
Query READMODE
(A - Query to Read All Data at Once, H - Query to Read When You Navigate or Expand Hierarchies,
X - Query to Read Data during Navigation)
Writing a program
Enter transaction code SE38 to write a new program. Provide program name and say create.
Provide program description and other relevant information.
Provide package detail or $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.
No comments:
Post a Comment