Introduction:
While checking the ‘Where-used List’ of any Info-Objects in SAP-BW, the result shows the relevant Infocubes, DSOs, Queries, Transformations, etc. where the Info-Object is used. However, it doesn’t show the Transformation routines/codes (Start Routine/End Routine/Field Routine/Expert Routine) where the Info-Object tables are referred to. For example, the 0MATERIAL master data table /BI0/PMATERIAL might be called in a Start Routine code (Example Code: SELECT * FROM /BI0/PMATERIAL) under a Transformation. While clicking on the ‘where used list’ of 0MATERIAL, it doesn’t show the Code Line where this table has been referred in a Transformation. This table might be used in many routine codes under multiple Transformations. In order to find the Transformation routines in which the tables/Infoproviders are referred to, I have developed the tool called as ‘SAP-BW Transformation Routine References Tool’.
Transformation Routine References Tool Usage:
This tool can be used to find the usage/reference of any Master Data tables, Info-provider, Fiscal Year Variants, etc in transformation routines. SAP-BW Consultants encounter situations to identify the list of Transformations where the references to Master Data Tables (Example – /BI0/PMATERIAL, /BI0/PRT_LOCATIO), DSO Active Data tables (Example – /BI0/AFIAP_O0300), Fiscal Year Variants (Example – ZA, ZB, ZM), etc. are used. The Where-used list in any Info-provider renders the Object references but never yields the Code references used within any transformation. Therefore, this tool serves the purpose to identify the transformations wherever the table (or any other pattern) references are used. It takes the input of any technical name in SAP-BW as a ‘Pattern’ and renders the list of all transformations wherever the reference table/pattern is used. It shows the following details which will help to easily locate the line within the routines in all the relevant transformations.
The Output of this report contains the following fields:
- Transformation ID
- Object Version (A/M/T)
- Transformation Name/Description
- Code ID (Routine Code ID)
- Routine Type (Start Routine/End Routine/Field Routine/Global code/Global Code 2)
- Field Name (Wherever Field Routines are implemented)
- Line No.
- Line (Actual Code)
Benefits:
- The program utilizes standard tables to find the results, therefore the program can be used in any SAP-BW system (Global/Universal tool).
- This program does not impact the performance of the system.
- Since the input is pattern based, any name can be given as an input (such as ‘itab’, ‘lt_vendor’, ‘ZMATERIAL’, ‘/BIC/AZPC0100’, etc).
For example, if we would like to find the transformations wherever the Master data table ‘/BI0/PPLANT’ is used, this table name can be provided as an input and the resulting screen displays all the relevant information.
Conclusion:
The Transformation Routine Reference Finder tool can be used in any SAP-BW system to find the transformation routines where-ever the table or other pattern references are used. This will help SAP-BW consultants to make better decisions, in case of deleting master data or the object itself. For example, if you want to delete a DSO but you are not aware which transformation uses the relevant DSO Active data table /BIC/AXXXX00 in routines, then this tool can be used to locate the relevant transformation.
Program (can be used in any SAP-BW system):
*&---------------------------------------------------------------------*
*& Report ZTRAN_ROUT_REFERENCES
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ztran_rout_references.
TABLES: rstran, rstranroutmap, rsaabap.
*selection-screen begin of block b1 with frame title text-001.
*select-options: INFOPROV for no intervals.
*selection-screen end of block b1.
PARAMETER: target1(30) TYPE c LOWER CASE.
DATA: target TYPE c LENGTH 20.
target = '%' && target1 && '%' .
TYPES: BEGIN OF ty_tran,
tranid TYPE rstran-tranid,
objvers TYPE rstran-objvers,
objstat TYPE rstran-objstat,
strout TYPE rstran-startroutine,
edrout TYPE rstran-endroutine,
exprout TYPE rstran-expert,
glbcode TYPE rstran-glbcode,
glbcode2 TYPE rstran-glbcode2,
END OF ty_tran.
DATA: it_tran TYPE TABLE OF ty_tran,
wa_tran TYPE ty_tran.
TYPES: BEGIN OF ty_tran1,
codeid TYPE rstranroutmap-codeid,
lineno TYPE rsaabap-line_no,
line TYPE rsaabap-line,
END OF ty_tran1.
DATA: it_tran1 TYPE TABLE OF ty_tran1,
wa_tran1 TYPE ty_tran1.
TYPES: BEGIN OF ty_tran_rout,
tranid TYPE rstranroutmap-tranid,
objvers TYPE rstranroutmap-objvers,
codeid TYPE rstranroutmap-codeid,
paramtype TYPE rstranroutmap-paramtype,
fieldnm TYPE rstranroutmap-fieldnm,
END OF ty_tran_rout.
DATA: it_tran_rout TYPE TABLE OF ty_tran_rout,
wa_tran_rout TYPE ty_tran_rout.
TYPES: BEGIN OF ty_rs_abap,
codeid TYPE rsaabap-codeid,
objvers TYPE rsaabap-objvers,
line_no TYPE rsaabap-line_no,
line TYPE rsaabap-line,
END OF ty_rs_abap.
DATA: it_rs_abap TYPE TABLE OF ty_rs_abap,
wa_rs_abap TYPE ty_rs_abap.
TYPES: BEGIN OF ty_trantxt,
tranid TYPE rstran-tranid,
txtlg TYPE c LENGTH 60,
END OF ty_trantxt.
DATA: it_trantxt TYPE TABLE OF ty_trantxt,
wa_trantxt TYPE ty_trantxt.
TYPES: BEGIN OF ty_rec1,
tranid TYPE c LENGTH 32,
objvers TYPE c LENGTH 1,
trantxt TYPE c LENGTH 60,
codeid TYPE c LENGTH 30,
routtyp TYPE c LENGTH 20,
fieldnm TYPE c LENGTH 20,
lineno TYPE c LENGTH 6,
line TYPE c LENGTH 60,
END OF ty_rec1.
DATA: itab TYPE TABLE OF ty_rec1,
wtab TYPE ty_rec1.
DATA : it_field_cat TYPE slis_t_fieldcat_alv ,
wa_field_cat TYPE slis_fieldcat_alv.
SELECT codeid objvers line_no line FROM rsaabap INTO TABLE it_rs_abap WHERE objvers = 'A' AND line LIKE target.
LOOP AT it_rs_abap INTO wa_rs_abap.
wa_tran1-codeid = wa_rs_abap-codeid.
wa_tran1-lineno = wa_rs_abap-line_no.
wa_tran1-line = wa_rs_abap-line.
APPEND wa_tran1 TO it_tran1.
CLEAR wa_tran1.
ENDLOOP.
SORT it_tran1 BY codeid lineno.
* DELETE ADJACENT DUPLICATES FROM it_tran1 COMPARING codeid.
IF sy-subrc = 0.
SELECT tranid objvers codeid paramtype fieldnm FROM rstranroutmap INTO TABLE it_tran_rout WHERE paramtype = 1.
SELECT tranid objvers objstat startroutine endroutine expert glbcode glbcode2 FROM rstran INTO TABLE it_tran.
SELECT tranid txtlg FROM rstrant INTO TABLE it_trantxt.
ENDIF.
LOOP AT it_tran1 INTO wa_tran1.
READ TABLE it_tran INTO wa_tran WITH KEY exprout = wa_tran1-codeid.
IF sy-subrc = 0.
wtab-tranid = wa_tran-tranid.
wtab-objvers = wa_tran-objvers.
wtab-codeid = wa_tran1-codeid.
wtab-routtyp = 'EXPERT ROUTINE'.
wtab-lineno = wa_tran1-lineno.
wtab-line = wa_tran1-line.
ELSE.
READ TABLE it_tran_rout INTO wa_tran_rout WITH KEY codeid = wa_tran1-codeid.
IF sy-subrc = 0.
wtab-tranid = wa_tran_rout-tranid.
wtab-objvers = wa_tran_rout-objvers.
wtab-codeid = wa_tran1-codeid.
wtab-routtyp = 'FIELD ROUTINE'.
wtab-fieldnm = wa_tran_rout-fieldnm.
wtab-lineno = wa_tran1-lineno.
wtab-line = wa_tran1-line.
ELSE.
READ TABLE it_tran INTO wa_tran WITH KEY strout = wa_tran1-codeid.
IF sy-subrc = 0.
wtab-tranid = wa_tran-tranid.
wtab-objvers = wa_tran-objvers.
wtab-codeid = wa_tran1-codeid.
wtab-routtyp = 'START ROUTINE'.
wtab-lineno = wa_tran1-lineno.
wtab-line = wa_tran1-line.
ELSE.
READ TABLE it_tran INTO wa_tran WITH KEY edrout = wa_tran1-codeid.
IF sy-subrc = 0.
wtab-tranid = wa_tran-tranid.
wtab-objvers = wa_tran-objvers.
wtab-codeid = wa_tran1-codeid.
wtab-routtyp = 'END ROUTINE'.
wtab-lineno = wa_tran1-lineno.
wtab-line = wa_tran1-line.
ELSE.
READ TABLE it_tran INTO wa_tran WITH KEY glbcode = wa_tran1-codeid.
IF sy-subrc = 0.
wtab-tranid = wa_tran-tranid.
wtab-objvers = wa_tran-objvers.
wtab-codeid = wa_tran1-codeid.
wtab-routtyp = 'GLOBAL CODE'.
wtab-lineno = wa_tran1-lineno.
wtab-line = wa_tran1-line.
ELSE.
READ TABLE it_tran INTO wa_tran WITH KEY glbcode2 = wa_tran1-codeid.
IF sy-subrc = 0.
wtab-tranid = wa_tran-tranid.
wtab-objvers = wa_tran-objvers.
wtab-codeid = wa_tran1-codeid.
wtab-routtyp = 'GLOBAL CODE2'.
wtab-lineno = wa_tran1-lineno.
wtab-line = wa_tran1-line.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
READ TABLE it_trantxt INTO wa_trantxt WITH KEY tranid = wtab-tranid.
IF sy-subrc = 0.
wtab-trantxt = wa_trantxt-txtlg.
ENDIF.
APPEND wtab TO itab.
CLEAR wtab.
ENDLOOP.
SORT itab BY tranid objvers routtyp fieldnm lineno ASCENDING.
DELETE itab WHERE tranid = ''.
PERFORM build_fieldcat.
PERFORM display.
*&---------------------------------------------------------------------*
*& Form build_fieldcat
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM build_fieldcat .
* clear wa_field_cat.
wa_field_cat-reptext_ddic = 'TRANSFORMATION ID'.
wa_field_cat-fieldname = 'TRANID'.
wa_field_cat-tabname = 'ITAB'.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.
wa_field_cat-reptext_ddic = 'OBJECT VERSION'.
wa_field_cat-fieldname = 'OBJVERS'.
wa_field_cat-tabname = 'ITAB'.
wa_field_cat-outputlen = 1.
APPEND wa_field_cat TO it_field_cat.
wa_field_cat-reptext_ddic = 'TRANSFORMATION DESCRIPTION'.
wa_field_cat-fieldname = 'TRANTXT'.
wa_field_cat-tabname = 'ITAB'.
wa_field_cat-outputlen = 60.
APPEND wa_field_cat TO it_field_cat.
wa_field_cat-reptext_ddic = 'CODE ID'.
wa_field_cat-fieldname = 'CODEID'.
wa_field_cat-tabname = 'ITAB'.
wa_field_cat-outputlen = 30.
APPEND wa_field_cat TO it_field_cat.
wa_field_cat-reptext_ddic = 'ROUTINE TYPE'.
wa_field_cat-fieldname = 'ROUTTYP'.
wa_field_cat-tabname = 'ITAB'.
wa_field_cat-outputlen = 32.
APPEND wa_field_cat TO it_field_cat.
wa_field_cat-reptext_ddic = 'FIELD NAME'.
wa_field_cat-fieldname = 'FIELDNM'.
wa_field_cat-tabname = 'ITAB'.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.
wa_field_cat-reptext_ddic = 'LINE NO.'.
wa_field_cat-fieldname = 'LINENO'.
wa_field_cat-tabname = 'ITAB'.
wa_field_cat-outputlen = 10.
APPEND wa_field_cat TO it_field_cat.
wa_field_cat-reptext_ddic = 'LINE.'.
wa_field_cat-fieldname = 'LINE'.
wa_field_cat-tabname = 'ITAB'.
wa_field_cat-outputlen = 10.
APPEND wa_field_cat TO it_field_cat.
ENDFORM. "build_fieldcat
*&---------------------------------------------------------------------*
*& Form display
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM display .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = sy-repid
i_callback_user_command = 'USER_COMMAND'
it_fieldcat = it_field_cat
i_default = 'X'
i_save = ' '
TABLES
t_outtab = itab
EXCEPTIONS
program_error = 1
OTHERS = 2.
ENDFORM. "display