Introduction: The requirement was to capture the change logs of configuration data along with the respective descriptions (mostly from text tables), which is currently un-available in SAP directly using standards functionalities. This blog will explain the procedure to get the respective data.
Main Part: This blog can be used as reference for several topics like creating dynamic select query, dynamic table creations, dynamic read statements.
Basically my requirement was to capture all the changes related to configuration tables (similar to change pointer of master data), So I have build the below report which will make use of all the above mentioned dynamic statements.
Initially I created a table to Store the tables list (Lets name is Master Table) which contains the config table names, key fields concatenated by pipe, Description table (can be text table of the actual table containing the text value, Description keys (concatenated by pipe), description text field.
To provide example:
RDPR – (Rounding profile table).
RDTX – (Corresponding text table).
So my ZTABLE would be populated as below
Main Part: This blog can be used as reference for several topics like creating dynamic select query, dynamic table creations, dynamic read statements.
Basically my requirement was to capture all the changes related to configuration tables (similar to change pointer of master data), So I have build the below report which will make use of all the above mentioned dynamic statements.
Initially I created a table to Store the tables list (Lets name is Master Table) which contains the config table names, key fields concatenated by pipe, Description table (can be text table of the actual table containing the text value, Description keys (concatenated by pipe), description text field.
To provide example:
RDPR – (Rounding profile table).
RDTX – (Corresponding text table).
So my ZTABLE would be populated as below
Now I have created one more table to capture the actual values with history and indicators, which will look like below.
Based on the Master table will query and get the ddic info from DD03L tables and then prepare the dynamic select and get the config tables data.
Now for getting the related description for the values again dynamic select query is prepared based on the inputs form the master tables (Fields: DESC_TAB, DESC_KEYS, DESC_TX_FILE).
Note: Initially when the code is executed data will be inserted into the second data table with indicator as “I”.
From the next executions if there is any change in the descriptions then the initial record will be updated as archive = ‘X” and new record will be inserted into the data table with indicator as “U”.
In case if the data is deleted in the original configuration table, then the same will be updated as new record in the data table with indicator as “D” and the original value in the data will be updated as archive.
Conclusion: Once the below code is executed we will be having the required output which consists of the complete list of configuration data along with the change logs and with the respective descriptions.
*&---------------------------------------------------------------------*
*& Report ZGET_CHANGE_LOGS
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zget_change_logs.
TYPES: BEGIN OF ty_ddic,
tabname TYPE tabname,
fieldname TYPE fieldname,
rollname TYPE rollname,
position TYPE tabfdpos,
keyflag TYPE keyflag,
inttype TYPE inttype,
intlen TYPE intlen,
decimals TYPE decimals,
scrtext_l TYPE scrtext_l,
END OF ty_ddic,
BEGIN OF ty_pre_final,
tabname TYPE tabname,
langu TYPE syst_langu,
field TYPE zgoal_sn_field,
fdesc TYPE zgoal_sn_fdesc,
value TYPE zgoal_sn_value,
val_desc TYPE zgoal_sn_vdesc,
desc_field TYPE fieldname,
udate TYPE ersda,
utime TYPE created_at_time,
sno TYPE zgoal_sn_sno,
chngind TYPE cdchngind,
END OF ty_pre_final,
BEGIN OF ty_field_info,
tabname TYPE tabname,
END OF ty_field_info.
DATA: lt_ms_data TYPE TABLE OF zmaster_tab, "List of Tables
lw_ms_data TYPE zmaster_tab,
lt_field_info TYPE TABLE OF ty_field_info,
lw_field_info TYPE ty_field_info,
lt_junk TYPE TABLE OF zmaster_tab, "Cpature the issue list
lt_cfg_data TYPE TABLE OF zdata_table, "Actual Value table
lt_final TYPE TABLE OF zdata_table,
lt_archive TYPE TABLE OF zdata_table,
lw_final TYPE zdata_table,
lt_pre_data TYPE TABLE OF ty_pre_final,
lt_pre_final TYPE TABLE OF ty_pre_final,
lw_pre_data TYPE ty_pre_final,
lt_ms_ddic TYPE TABLE OF ty_ddic,
ls_ms_ddic TYPE ty_ddic,
lt_ms_split TYPE TABLE OF field,
lt_ds_split TYPE TABLE OF field,
ls_split TYPE field.
DATA: lv_sel_mfields TYPE string,
lv_sel_dfields TYPE string,
lv_key1 TYPE string,
lv_key2 TYPE string,
lv_key3 TYPE string,
lv_key4 TYPE string,
lv_key5 TYPE string,
lv_val1 TYPE string,
lv_val2 TYPE string,
lv_val3 TYPE string,
lv_val4 TYPE string,
lv_val5 TYPE string,
lv_key_fields TYPE char300,
lv_main_fdesc TYPE char1024_cs,
lv_val_desc TYPE char1024_cs,
lv_no_cols TYPE i,
lv_intlen TYPE i,
lv_dec TYPE i,
lv_cnt TYPE i,
lv_inttype TYPE inttype,
lv_value TYPE string,
lv_udate TYPE ersda,
lv_utime TYPE created_at_time,
lt_main_comp TYPE cl_abap_structdescr=>component_table,
ls_main_comp LIKE LINE OF lt_main_comp,
l_data TYPE REF TO data,
l_new_tab TYPE REF TO cl_abap_tabledescr,
l_new_type TYPE REF TO cl_abap_structdescr.
FIELD-SYMBOLS: <lt_main_dyntab> TYPE ANY TABLE,
<lt_desc_dyntab> TYPE ANY TABLE,
<lw_main_data> TYPE any,
<lw_desc_data> TYPE any,
<lv_field> TYPE any,
<lv_final> TYPE ty_pre_final.
CONSTANTS: lc_lang TYPE char1 VALUE 'E',
lc_pipe TYPE char1 VALUE '|',
lc_insr TYPE char1 VALUE 'I',
lc_updt TYPE char1 VALUE 'U',
lc_dele TYPE char1 VALUE 'D',
lc_spras TYPE char5 VALUE 'SPRAS'.
INITIALIZATION.
START-OF-SELECTION.
SELECT * FROM zmaster_tab
INTO TABLE lt_ms_data
WHERE langu = lc_lang.
IF sy-subrc = '0'.
SORT lt_ms_data BY langu tabname field.
LOOP AT lt_ms_data INTO lw_ms_data.
lw_field_info-tabname = lw_ms_data-tabname.
APPEND lw_field_info TO lt_field_info.
CLEAR: lw_field_info,lw_ms_data.
ENDLOOP.
LOOP AT lt_ms_data INTO lw_ms_data.
lw_field_info-tabname = lw_ms_data-desc_tab.
APPEND lw_field_info TO lt_field_info.
CLEAR: lw_field_info,lw_ms_data.
ENDLOOP.
IF lt_field_info IS NOT INITIAL.
SORT lt_field_info.
ENDIF.
*--Fetch DDIC Information
SELECT tabname
fieldname
rollname
position
keyflag
inttype
intlen
decimals
scrtext_l FROM dd03m
INTO TABLE lt_ms_ddic
FOR ALL ENTRIES IN lt_field_info
WHERE tabname EQ lt_field_info-tabname
AND ddlanguage EQ lc_lang.
* AND keyflag EQ abap_true.
IF sy-subrc = 0.
SORT lt_ms_ddic BY tabname fieldname.
ENDIF.
*--Fetch the value table data
SELECT * FROM zdata_table
INTO TABLE lt_cfg_data
FOR ALL ENTRIES IN lt_ms_data
WHERE langu EQ lc_lang
AND tabname EQ lt_ms_data-tabname
AND archive NE abap_true.
IF sy-subrc = 0.
SORT lt_cfg_data BY langu tabname field value.
ENDIF.
ENDIF.
LOOP AT lt_ms_data INTO lw_ms_data.
*-------------------Logic for Main data------------------------------
*--Separating the key fields
CLEAR: lt_ms_split, lt_main_comp, lv_sel_mfields, lv_key_fields,
lv_main_fdesc, lv_no_cols.
SPLIT lw_ms_data-field AT lc_pipe INTO TABLE lt_ms_split.
DESCRIBE TABLE lt_ms_split LINES lv_no_cols.
LOOP AT lt_ms_split INTO ls_split.
CLEAR: ls_ms_ddic, ls_main_comp, lv_intlen, lv_dec.
READ TABLE lt_ms_ddic INTO ls_ms_ddic WITH KEY
tabname = lw_ms_data-tabname
fieldname = ls_split
keyflag = abap_true
BINARY SEARCH.
IF sy-subrc = 0.
*--Concatenating fields for select query
CONCATENATE lv_sel_mfields ls_ms_ddic-fieldname
INTO lv_sel_mfields SEPARATED BY space.
*--Concatenating fields to prepare key
CONCATENATE lv_key_fields ls_ms_ddic-fieldname
INTO lv_key_fields SEPARATED BY lc_pipe.
*--Concatenating field descriptions
CONCATENATE lv_main_fdesc ls_ms_ddic-scrtext_l
INTO lv_main_fdesc SEPARATED BY lc_pipe.
*---Filling the component table
lv_intlen = ls_ms_ddic-intlen.
lv_dec = ls_ms_ddic-decimals.
TRY.
CALL METHOD cl_abap_elemdescr=>get_by_kind
EXPORTING
p_type_kind = ls_ms_ddic-inttype
p_length = lv_intlen
p_decimals = lv_dec
RECEIVING
p_result = ls_main_comp-type.
ls_main_comp-name = ls_ms_ddic-fieldname.
CATCH cx_parameter_invalid_range.
ENDTRY.
APPEND ls_main_comp TO lt_main_comp.
CLEAR: ls_main_comp.
ENDIF.
ENDLOOP.
IF lt_main_comp IS INITIAL.
APPEND lw_ms_data TO lt_junk.
CONTINUE.
ENDIF.
*--Remove leading space
SHIFT lv_sel_mfields LEFT BY 1 PLACES.
SHIFT lv_key_fields LEFT BY 1 PLACES.
SHIFT lv_main_fdesc LEFT BY 1 PLACES.
*--- Create a New Type
TRY.
CLEAR: l_new_type.
CALL METHOD cl_abap_structdescr=>create
EXPORTING
p_components = lt_main_comp
RECEIVING
p_result = l_new_type.
CATCH cx_sy_struct_creation.
ENDTRY.
*--- New dynamic internal Table type
TRY.
CLEAR: l_new_tab.
CALL METHOD cl_abap_tabledescr=>create
EXPORTING
p_line_type = l_new_type
p_unique = abap_false
RECEIVING
p_result = l_new_tab.
CATCH cx_sy_table_creation.
ENDTRY.
*--data to handle the new table type
CLEAR: l_data.
CREATE DATA l_data TYPE HANDLE l_new_tab.
*--New internal table in the fieldsymbols
ASSIGN l_data->* TO <lt_main_dyntab>.
CLEAR: l_data.
*-------------------End Logic for Main data---------------------------
*-------------------Logic for Description data------------------------
IF lw_ms_data-desc_tab IS NOT INITIAL.
*--Separating the key fields
CLEAR: lt_ds_split, ls_split, lt_main_comp, lv_sel_dfields.
SPLIT lw_ms_data-desc_keys AT lc_pipe INTO TABLE lt_ds_split.
LOOP AT lt_ds_split INTO ls_split.
CLEAR: ls_ms_ddic, ls_main_comp, lv_intlen, lv_dec.
READ TABLE lt_ms_ddic INTO ls_ms_ddic WITH KEY
tabname = lw_ms_data-desc_tab
fieldname = ls_split
keyflag = abap_true
BINARY SEARCH.
IF sy-subrc = 0.
*--Concatenating fields for select query
CONCATENATE lv_sel_dfields ls_ms_ddic-fieldname
INTO lv_sel_dfields SEPARATED BY space.
*---Filling the component table
lv_intlen = ls_ms_ddic-intlen.
lv_dec = ls_ms_ddic-decimals.
TRY.
CALL METHOD cl_abap_elemdescr=>get_by_kind
EXPORTING
p_type_kind = ls_ms_ddic-inttype
p_length = lv_intlen
p_decimals = lv_dec
RECEIVING
p_result = ls_main_comp-type.
ls_main_comp-name = ls_ms_ddic-fieldname.
CATCH cx_parameter_invalid_range.
ENDTRY.
APPEND ls_main_comp TO lt_main_comp.
CLEAR: ls_main_comp.
ENDIF.
ENDLOOP.
IF lt_main_comp IS NOT INITIAL.
*--Remove leading space
SHIFT lv_sel_dfields LEFT BY 1 PLACES.
*---Populate Value Description Field
READ TABLE lt_ms_ddic INTO ls_ms_ddic WITH KEY
tabname = lw_ms_data-desc_tab
fieldname = lw_ms_data-desc_tx_field
BINARY SEARCH.
IF sy-subrc = 0.
IF lw_ms_data-desc_tx_field IS NOT INITIAL.
TRY.
lv_inttype = 'C'.
lv_intlen = 50.
CALL METHOD cl_abap_elemdescr=>get_by_kind
EXPORTING
p_type_kind = lv_inttype
p_length = lv_intlen
RECEIVING
p_result = ls_main_comp-type.
ls_main_comp-name = lw_ms_data-desc_tx_field.
CATCH cx_parameter_invalid_range.
ENDTRY.
APPEND ls_main_comp TO lt_main_comp.
CLEAR: ls_main_comp.
*--Concatenate desc field to select query
CONCATENATE lv_sel_dfields lw_ms_data-desc_tx_field INTO lv_sel_dfields
SEPARATED BY space.
ENDIF.
ELSE.
APPEND lw_ms_data TO lt_junk.
ENDIF.
*--- Create a New Type
TRY.
CLEAR: l_new_type.
CALL METHOD cl_abap_structdescr=>create
EXPORTING
p_components = lt_main_comp
RECEIVING
p_result = l_new_type.
CATCH cx_sy_struct_creation.
ENDTRY.
*--- New dynamic internal Table type
TRY.
CLEAR: l_new_tab.
CALL METHOD cl_abap_tabledescr=>create
EXPORTING
p_line_type = l_new_type
p_unique = abap_false
RECEIVING
p_result = l_new_tab.
CATCH cx_sy_table_creation.
ENDTRY.
*--data to handle the new table type
CLEAR: l_data.
CREATE DATA l_data TYPE HANDLE l_new_tab.
*--New internal table in the fieldsymbols
ASSIGN l_data->* TO <lt_desc_dyntab>.
CLEAR: l_data.
*--Fetching Description from Text/Main Tables dynamically
IF lw_ms_data-desc_tab IS NOT INITIAL.
SELECT (lv_sel_dfields)
INTO CORRESPONDING FIELDS OF
TABLE <lt_desc_dyntab> FROM (lw_ms_data-desc_tab).
*--Only capture upto 5 keys
CLEAR: lv_cnt,lv_key1,lv_key2,lv_key3,lv_key4,lv_key5,
lv_val1,lv_val2,lv_val3,lv_val4,lv_val5.
LOOP AT lt_ms_split INTO ls_split.
READ TABLE lt_ds_split TRANSPORTING NO FIELDS WITH KEY table_line = lc_spras.
IF sy-subrc = 0 AND lv_key1 IS INITIAL.
lv_key1 = lc_spras.
lv_val1 = sy-langu.
lv_cnt = 1.
ENDIF.
READ TABLE lt_ds_split INTO DATA(lw_sp) WITH KEY table_line = ls_split.
IF sy-subrc = 0 AND lw_sp <> lc_spras.
lv_cnt = lv_cnt + 1.
CASE lv_cnt.
WHEN '1'.
lv_key1 = lw_sp.
WHEN '2'.
lv_key2 = lw_sp.
WHEN '3'.
lv_key3 = lw_sp.
WHEN '4'.
lv_key4 = lw_sp.
WHEN OTHERS.
lv_key5 = lw_sp.
ENDCASE.
ENDIF.
ENDLOOP.
ENDIF.
ENDIF.
ENDIF.
*-------------------End Logic for Description data---------------------
*--Fetching Data from Config Tables dynamically
SELECT (lv_sel_mfields)
INTO CORRESPONDING FIELDS OF
TABLE <lt_main_dyntab> FROM (lw_ms_data-tabname).
LOOP AT <lt_main_dyntab> ASSIGNING <lw_main_data>.
IF lw_ms_data-desc_tab IS NOT INITIAL.
ASSIGN COMPONENT lv_key1 OF STRUCTURE <lw_main_data> TO <lv_field>.
IF sy-subrc = 0.
lv_val1 = <lv_field>.
ENDIF.
ASSIGN COMPONENT lv_key2 OF STRUCTURE <lw_main_data> TO <lv_field>.
IF sy-subrc = 0.
lv_val2 = <lv_field>.
ENDIF.
ASSIGN COMPONENT lv_key3 OF STRUCTURE <lw_main_data> TO <lv_field>.
IF sy-subrc = 0.
lv_val3 = <lv_field>.
ENDIF.
ASSIGN COMPONENT lv_key4 OF STRUCTURE <lw_main_data> TO <lv_field>.
IF sy-subrc = 0.
lv_val4 = <lv_field>.
ENDIF.
ASSIGN COMPONENT lv_key5 OF STRUCTURE <lw_main_data> TO <lv_field>.
IF sy-subrc = 0.
lv_val5 = <lv_field>.
ENDIF.
*--Get the value description
IF <lt_desc_dyntab> IS ASSIGNED.
READ TABLE <lt_desc_dyntab> ASSIGNING <lw_desc_data>
WITH KEY (lv_key1) = lv_val1
(lv_key2) = lv_val2
(lv_key3) = lv_val3
(lv_key4) = lv_val4
(lv_key5) = lv_val5.
IF sy-subrc = 0.
ASSIGN COMPONENT lw_ms_data-desc_tx_field OF STRUCTURE <lw_desc_data> TO <lv_field>.
IF sy-subrc = 0.
lv_val_desc = <lv_field>.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
*--Concatenate the Values
DO.
ASSIGN COMPONENT sy-index
OF STRUCTURE <lw_main_data>
TO <lv_field>.
*--Capture only the field values and not the value description
IF sy-subrc EQ 0 AND sy-index <= lv_no_cols.
IF sy-index EQ 1.
lv_value = <lv_field>.
ELSE.
CONCATENATE lv_value <lv_field> INTO lv_value SEPARATED BY lc_pipe.
ENDIF.
ELSE.
EXIT.
ENDIF.
ENDDO.
lw_pre_data-tabname = lw_ms_data-tabname.
lw_pre_data-langu = lc_lang.
lw_pre_data-field = lv_key_fields.
lw_pre_data-fdesc = lv_main_fdesc.
lw_pre_data-value = lv_value.
lw_pre_data-val_desc = lv_val_desc.
lw_pre_data-desc_field = lw_ms_data-desc_tx_field.
APPEND lw_pre_data TO lt_pre_data.
CLEAR: lw_pre_data.
CLEAR: lv_value, lv_val_desc.
ENDLOOP.
IF <lt_main_dyntab> IS ASSIGNED.
UNASSIGN: <lt_main_dyntab>.
ENDIF.
IF <lt_desc_dyntab> IS ASSIGNED.
UNASSIGN: <lt_desc_dyntab>.
ENDIF.
CLEAR: lw_pre_data, lt_main_comp, l_new_tab, l_new_type, lv_no_cols.
ENDLOOP.
*--Comparing the data and preparing the final for Insert and Update records
SORT lt_pre_data BY langu tabname field value.
SORT lt_cfg_data BY langu tabname field value.
LOOP AT lt_pre_data ASSIGNING <lv_final>.
<lv_final>-sno = sy-tabix.
MOVE-CORRESPONDING <lv_final> TO lw_pre_data.
READ TABLE lt_cfg_data INTO DATA(lw_dt) WITH KEY langu = <lv_final>-langu
tabname = <lv_final>-tabname
field = <lv_final>-field
value = <lv_final>-value
BINARY SEARCH.
IF sy-subrc = 0.
*--For Arciving purpose
CLEAR: lw_final.
MOVE-CORRESPONDING lw_dt TO lw_final.
*--Data Exists
*--Compare the field descriptions and get the updated records
IF <lv_final>-desc_field IS NOT INITIAL.
IF <lv_final>-val_desc <> lw_dt-val_desc.
<lv_final>-chngind = lc_updt.
lw_pre_data-chngind = lc_updt.
<lv_final>-val_desc = <lv_final>-val_desc.
lw_pre_data-val_desc = <lv_final>-val_desc.
APPEND lw_pre_data TO lt_pre_final.
*--Capturing Archive record.
lw_final-archive = abap_true.
APPEND lw_final TO lt_final.
CLEAR: lw_final.
ENDIF.
ENDIF.
ELSE.
*--Data does not exists
*--Capture this data as new records
<lv_final>-chngind = lc_insr.
lw_pre_data-chngind = lc_insr.
APPEND lw_pre_data TO lt_pre_final.
ENDIF.
CLEAR: lw_pre_data, lw_dt.
ENDLOOP.
*--Comparing the data and preparing the final for delete records
SORT lt_pre_data BY langu tabname field value.
LOOP AT lt_cfg_data INTO lw_dt.
CLEAR: lw_pre_data.
READ TABLE lt_pre_data INTO lw_pre_data WITH KEY
langu = lw_dt-langu
tabname = lw_dt-tabname
field = lw_dt-field
value = lw_dt-value
BINARY SEARCH.
IF sy-subrc = '0'.
*--Data Exists Can be ignored
ELSE.
*--Data does not exists Capture the deleted records
MOVE-CORRESPONDING lw_dt TO lw_pre_data.
lw_pre_data-chngind = lc_dele.
APPEND lw_pre_data TO lt_pre_final.
*--Capturing Archive record.
MOVE-CORRESPONDING lw_dt TO lw_final.
lw_final-archive = abap_true.
APPEND lw_final TO lt_final.
CLEAR: lw_final.
ENDIF.
CLEAR: lw_dt.
ENDLOOP.
*--Preparing final table
lv_udate = sy-datum.
lv_utime = sy-timlo.
SORT lt_pre_final BY tabname.
SORT lt_cfg_data BY langu tabname ASCENDING sno DESCENDING.
LOOP AT lt_pre_final ASSIGNING <lv_final>.
AT NEW tabname.
CLEAR: lv_no_cols, lw_dt.
READ TABLE lt_cfg_data INTO lw_dt WITH KEY
langu = lc_lang
tabname = <lv_final>-tabname
BINARY SEARCH.
IF sy-subrc = 0.
lv_no_cols = lw_dt-sno + 1.
ELSE.
lv_no_cols = 1.
ENDIF.
ENDAT.
<lv_final>-sno = lv_no_cols.
lv_no_cols = lv_no_cols + 1.
<lv_final>-udate = lv_udate.
<lv_final>-utime = lv_utime.
MOVE-CORRESPONDING <lv_final> TO lw_final.
lw_final-mandt = sy-mandt.
APPEND lw_final TO lt_final.
CLEAR: lw_final.
ENDLOOP.
*--Inserting data into tabe
IF lt_final IS NOT INITIAL.
SORT lt_final BY langu tabname sno.
MODIFY zdata_table FROM TABLE lt_final.
ENDIF.
*-- Sending only the updated records.
DELETE lt_final WHERE archive IS NOT INITIAL.
IF <lv_final> IS ASSIGNED.
UNASSIGN <lv_final>.
ENDIF.
CLEAR: lv_utime, lv_udate, lt_pre_data, lt_pre_final,
lt_final.
LOOP AT lt_junk INTO lw_ms_data.
WRITE:/ lw_ms_data.
ENDLOOP.
d config data along with texts in the it_final table from the below code snippet.
Note: lt_junk will have the details related to the skipped tables.
No comments:
Post a Comment