If you are looking for a way how to control database table synchronization process between two systems in a custom way, check out below solution aimed for transferring of transparent tables.
Of course we need two parts, some sender part and receiver part. The base is to do a XML transformation into binary Xstring which is suitable for transferring of any data, even whole table. I did lot of tests, it really nicely flexible.
The sender part
I am not providing the full source of sender part. The corner stone of sender program would be the below XML transformation. Keep in mind to name source on both sides in the same way. Here is the source name "remote_data",it could be anything you want, but it has to be the same on both sides sender and receiver part of XML transformation (see the receiver FM as well).
It is possible to pack into Xstring whole tables, part of the tables based on certain conditions, anything that fits your needs.
Part of the sender code:
" Declaration snippet
METHODS:
encode_input
IMPORTING it_data
TYPE ANY TABLE
EXPORTING ev_xstring
TYPE xstring
,
" Implementation
METHOD encode_input
.
DATA: lo_writer
TYPE REF TO cl_sxml_string_writer
.
lo_writer
= cl_sxml_string_writer
=>create( if_sxml
=>co_xt_json
).
TRY.
CALL TRANSFORMATION
id
SOURCE
remote_data = it_data
RESULT XML lo_writer
.
CATCH cx_xslt_format_error
.
MESSAGE ID sy
-msgid
TYPE sy
-msgty
NUMBER sy
-msgno
WITH sy
-msgv1 sy
-msgv2 sy
-msgv3 sy
-msgv4
.
ENDTRY.
ev_xstring
= lo_writer
->get_output
( ).
ENDMETHOD. "encode_input
The receiver part
Let me describe a bit the receiver part. It consists of remote enabled function module on remote system. There has to be established RFC connection between servers, ask your BC team. As input serves few parameters:
- Table name - in a character way
- Table content as a binary string
- Update type with below options
Possible update types:
- I – insert only, it adds just new records
- N – overwrite older records by newer ones based on standard date colums ERSDA or LAEDA. It skips update of tables where there is no LAEDA, ERSDA column present. This relays on DATE column only, other DATE/DATS columns are not considered.
- F – force update overwrite all records
Furthermore there is an exporting parameter receiving a possible error code or success confirmation. By the sent error code you can act accordingly in your sender part.
The source code of receiver:
FUNCTION /yournamespace/customizing_update
.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" VALUE(IV_TABNAME) TYPE TABNAME
*" VALUE(IV_UPDATE_TYPE) TYPE C1
*" VALUE(IV_XSTRING_TAB) TYPE XSTRING
*" EXPORTING
*" VALUE(EV_STATUS) TYPE CHAR4
*"----------------------------------------------------------------------
IF ( iv_tabname
IS INITIAL )
OR
( iv_xstring_tab
IS INITIAL ).
ev_status
= 'ERR0'.
RETURN.
ENDIF.
"Note: Do here your own AUTHORITY-CHECK stuff!
TYPES : BEGIN OF ty_where_clause
,
line TYPE char72
,
END OF ty_where_clause
.
DATA:
lo_typedescr
TYPE REF TO cl_abap_typedescr
,
lo_struct
TYPE REF TO cl_abap_structdescr
,
lo_new_tab
TYPE REF TO cl_abap_tabledescr
,
lt_comp
TYPE cl_abap_structdescr
=>component_table
,
lt_desc_fields
TYPE ddfields
,
lt_data_remote
TYPE REF TO data,
ls_data_remote
TYPE REF TO data,
ls_data_local
TYPE REF TO data,
lv_count_ok
TYPE i,
lv_count_err
TYPE i,
lt_where_cond
LIKE STANDARD TABLE OF hrcond
,
ls_where_cond
LIKE LINE OF lt_where_cond
,
lt_where_clause
TYPE STANDARD TABLE OF ty_where_clause
,
lt_date_col
TYPE STANDARD TABLE OF cl_abap_structdescr
=>component-name
,
lv_date_col
LIKE LINE OF lt_date_col
,
lv_date_col_comp
LIKE LINE OF lt_date_col
.
FIELD-SYMBOLS:
<ft_data_remote>
TYPE ANY TABLE,
<fs_data_remote>
TYPE data,
<fs_data_local>
TYPE data,
<fv_value>
TYPE any,
<fv_date_remote>
TYPE dats
,
<fv_date_local>
TYPE dats
,
<fs_desc_fields>
LIKE LINE OF lt_desc_fields
,
<fv_col>
LIKE LINE OF lt_comp
,
<fv_col_date>
LIKE LINE OF lt_date_col
.
" Fill known date columns to be checked in received data (it is used in update type "N" - newer records)
lv_date_col
= 'LAEDA'. " Date of last modification (in loop checked as the first one)
APPEND lv_date_col
TO lt_date_col
.
lv_date_col
= 'ERSDA'. " Date of creation
APPEND lv_date_col
TO lt_date_col
.
" Create target itab type
cl_abap_typedescr
=>describe_by_name
( " Note: cx_root does not catch type_not_found here
EXPORTING p_name
= iv_tabname
RECEIVING p_descr_ref
= lo_typedescr
EXCEPTIONS type_not_found
= 1
).
IF sy
-subrc
NE 0.
ev_status
= 'ERR1'.
"MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. --> causes dump within RFC call
WRITE 'Error, unknown target structure/table type in data dictionary: '.
WRITE iv_tabname
.
RETURN.
ENDIF.
IF lo_typedescr
IS NOT INITIAL.
" Get structure components
lo_struct ?= lo_typedescr
.
lt_comp
= lo_struct
->get_components
( ).
" Is date column of last modification/creation present?
IF ( iv_update_type
EQ 'N' ).
LOOP AT lt_date_col
ASSIGNING <fv_col_date>
. " Loop over known date columns (suitable for comparison), items order does matter
LOOP AT lt_comp
ASSIGNING <fv_col>
. " Loop over table components
IF <fv_col_date>
EQ <fv_col>
-name
.
lv_date_col_comp
= <fv_col_date>
.
EXIT.
ENDIF.
ENDLOOP.
IF lv_date_col_comp
IS NOT INITIAL.
EXIT.
ENDIF.
ENDLOOP.
ENDIF.
" Create new target itab
lo_new_tab
= cl_abap_tabledescr
=>create(
p_line_type
= lo_struct
p_table_kind
= cl_abap_tabledescr
=>tablekind_std
p_unique
= abap_false
).
IF lo_new_tab
IS NOT INITIAL.
CREATE DATA lt_data_remote
TYPE HANDLE lo_new_tab
.
ELSE.
ev_status
= 'ERR2'.
RETURN.
ENDIF.
ELSE.
ev_status
= 'ERR3'.
RETURN.
ENDIF.
" Decode data into itab
ASSIGN lt_data_remote
->*
TO <ft_data_remote>
.
TRY.
CALL TRANSFORMATION
id
SOURCE XML iv_xstring_tab
RESULT
remote_data = <ft_data_remote>
.
CATCH cx_xslt_format_error
.
ev_status
= 'ERR4'.
"MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. --> Note: causes dump on both sides, calling program and this target FM
WRITE 'Error occured during itab (xstring) decoding for table: '.
WRITE iv_tabname
.
RETURN.
ENDTRY.
" Loop over itab for DB update
IF <ft_data_remote>
IS ASSIGNED.
CREATE DATA ls_data_remote
LIKE LINE OF <ft_data_remote>
.
ASSIGN ls_data_remote
->*
TO <fs_data_remote>
.
CREATE DATA ls_data_local
LIKE LINE OF <ft_data_remote>
.
ASSIGN ls_data_local
->*
TO <fs_data_local>
.
" Get key fields of the table (keyflag value)
lt_desc_fields
= lo_struct
->get_ddic_field_list
( ).
" Update table according to given type
LOOP AT <ft_data_remote>
ASSIGNING <fs_data_remote>
.
IF ( iv_update_type
EQ 'I' ) " Increment only
OR
( iv_update_type
EQ 'N' ). " Update only older
"*******************************************************************************
" Do partial update
"
" I - Increment only = add missing records only
" or
" N - Update only older recocords by newer ones based on LAEDA, ERSDA columns
"*******************************************************************************
" Prepare SQL WHERE condition
LOOP AT lt_desc_fields
ASSIGNING <fs_desc_fields>
WHERE keyflag
= abap_true
. " get primary key fields
" Get value of key component
ASSIGN COMPONENT <fs_desc_fields>
-fieldname
OF STRUCTURE <fs_data_remote>
TO <fv_value>
.
" Prepare condition table
ls_where_cond
-field = <fs_desc_fields>
-fieldname
.
ls_where_cond
-opera
= 'EQ'.
ls_where_cond
-low
= <fv_value>
.
APPEND ls_where_cond
TO lt_where_cond
.
UNASSIGN <fv_value>
.
CLEAR ls_where_cond
.
ENDLOOP.
IF lt_where_cond
IS NOT INITIAL.
" Convert condition table into SQL WHERE clause
CALL FUNCTION 'RH_DYNAMIC_WHERE_BUILD'
EXPORTING
dbtable
= iv_tabname
TABLES
condtab
= lt_where_cond
where_clause
= lt_where_clause
EXCEPTIONS
empty_condtab
= 1
no_db_field
= 2
unknown_db
= 3
wrong_condition
= 4
OTHERS = 5.
IF sy
-subrc <>
0.
ev_status
= 'ERR7'.
WRITE 'Some error occured during building SQL WHERE condition for table: '.
WRITE iv_tabname
.
ENDIF.
SELECT SINGLE *
FROM (iv_tabname
)
INTO <fs_data_local>
WHERE (lt_where_clause
).
IF sy
-subrc
EQ 0.
" Update only older recocords by newer ones based on LAEDA, ERSDA columns
IF ( iv_update_type
EQ 'N' ).
IF <fs_data_local>
IS ASSIGNED. " Then compare date, if it is present
IF lv_date_col_comp
IS NOT INITIAL.
" Compare remote and local dates
" Get values of date component
ASSIGN COMPONENT lv_date_col_comp
OF STRUCTURE <fs_data_local>
TO <fv_date_local>
.
ASSIGN COMPONENT lv_date_col_comp
OF STRUCTURE <fs_data_remote>
TO <fv_date_remote>
.
IF <fv_date_local> < <fv_date_remote>
.
"*******************************************************************************
" Overwrite record with newer one
"*******************************************************************************
UPDATE (iv_tabname
) FROM <fs_data_remote>
.
IF sy
-subrc
EQ 0.
lv_count_ok
= lv_count_ok +
1.
COMMIT WORK.
ELSE.
lv_count_err
= lv_count_err +
1.
ROLLBACK WORK.
ENDIF.
ENDIF.
UNASSIGN <fv_date_local>
.
UNASSIGN <fv_date_remote>
.
ENDIF.
ENDIF.
ENDIF.
ELSE.
"*******************************************************************************
" Record does not exist = add it (It fits to both types of update I + N)
"*******************************************************************************
INSERT (iv_tabname
) FROM <fs_data_remote>
.
IF sy
-subrc
EQ 0.
lv_count_ok
= lv_count_ok +
1.
COMMIT WORK.
ELSE.
lv_count_err
= lv_count_err +
1.
ROLLBACK WORK.
ENDIF.
ENDIF.
CLEAR lt_where_cond
.
CLEAR lt_where_clause
.
ENDIF.
ELSEIF iv_update_type
EQ 'F'.
"*******************************************************************************
" Do force update = overwrite all already existing records
" Inserts + Updates
"*******************************************************************************
MODIFY (iv_tabname
) FROM <fs_data_remote>
.
IF sy
-subrc
EQ 0.
lv_count_ok
= lv_count_ok +
1.
COMMIT WORK.
ELSE.
lv_count_err
= lv_count_err +
1.
ROLLBACK WORK.
ENDIF.
ENDIF.
ENDLOOP.
IF lv_count_err
EQ 0.
ev_status
= 'OK'.
ELSE.
ev_status
= 'ERR6'.
WRITE 'Some error occured during update of table: '.
WRITE iv_tabname
.
WRITE ', Number of failed records: '.
WRITE lv_count_err
.
WRITE ', Number of well passed records: '.
WRITE lv_count_ok
.
RETURN.
ENDIF.
ELSE.
ev_status
= 'ERR5'.
WRITE 'No data for table: '.
WRITE iv_tabname
.
RETURN.
ENDIF.
ENDFUNCTION.
The sources
https://stackoverflow.com/questions/30597747/cant-catch-certain-exceptions
http://zevolving.com/2008/09/dynamic-internal-table-creation/
https://blogs.sap.com/2013/04/16/writing-dynamic-where-clause-in-abap-select-query/