Update any table on remote system? YES! Remote updater via remote enabled function module

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:

1. I – insert only, it adds just new records 
2. 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.
3. 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/

ABAP - RFC FM - How to get user's lockdown date from remote system

Sometime is needed to pickup database data from another SAP system. There is not so many options how to do that.

At first is proper to say, try to search for any already existing SAP standard function module, BAPI. In my case I tested various BAPIs as e.g. BAPI_USER_EXISTENCE_CHECK, BAPI_USER_GET_DETAIL. Non of them returns lockdown date. Note, the date of last modification could be, but does not need to be a date of lockdown.

Option RFC_READ_TABLE

One option is to use function module RFC_READ_TABLE. But it is unfortunately flagged as not purpossed for custommer development and it has no official Unicode support. As param you can provide rows of WHERE SQL conditions, what is quite useful. But no bigger SQL query constructions are allowed, furthermore there is a limit of 72 characters for each row condition.
There is very nice article (in German) describing how to get generic data from FM RFC_READ_TABLE. Normally you just obtain data which has to be additionaly parsed.
http://www.tricktresor.de/content/index.php?navID=123&aID=469

Option Remote-Enabled function module

In my case I used custom function module for reading remote db data. It has advantage of generic output and it is quite easy to use. You have everything under control. So lets have a look at some real simple example, reading of lockdown date. At first we need to setup Remote-Enabled function module. You can do it in standard transactions as SE37 or SE80.

Whole real miracle behind is done by ticking of one radion button "Remote-Enabled Module". It fully automatically gives to your FM functionality to call this FM on remote SAP system. The only thing you need is to add DESTINATION parameter to function call.

 

Setup import parameters - do not forget to tick the checkboxes

Setup export parameters - do not forget to tick the checkbox

Now we are getting close to core to source code itself. See the data below. Whenever a new user is created, a new record is placed also into table USH02. If sometime later is user locked, the column UFLAG contains non zero value. But the first record is normally with 0 flag. Local lockdown has value 64, otherwise Global lockdown contains the value 96.

Now, let's see the data. It is clear that after lockdown (in this case local lock) furthermore changes has been done later over user who has been already locked on 4.11. So we are looking for the first non zero value of UFLAG (any lockdown record) which is immediatelly following the last 0 UFLAG record (last non lockdown status).

It is possible to do it by one Open SQL query.

SELECT SINGLE modda FROM ush02
     INTO lockdown_date
     WHERE bname = username
     AND   uflag <> 0
     AND   modda >= ( SELECT MAX( modda ) FROM ush02 WHERE bname = username AND uflag = 0 ).


If return date value is empty, we know, there is no record with UFLAG = 0 or any locked. This rare situation could be solved as follows.

  IF lockdown_date IS INITIAL.
    SELECT MIN( modda ) FROM ush02
       INTO lockdown_date
       WHERE bname = username
       AND   uflag <> 0.
  ENDIF.


Now we need to add some standard checks and whole miracle is completed.

FUNCTION /YourNameSpace/rfc_get_lockdown_date.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(USERNAME) TYPE  XUBNAME OPTIONAL
*"  EXPORTING
*"     VALUE(LOCKDOWN_DATE) TYPE  D
*"  EXCEPTIONS
*"      PARAM_MISSING
*"      NOT_FOUND
*"      NOT_AUTHORIZED
*"      TABLE_NOT_AVAILABLE
*"----------------------------------------------------------------------

  CLEAR lockdown_date.

  " authority check
  CALL FUNCTION 'VIEW_AUTHORITY_CHECK'
    EXPORTING
      view_action                    = 'S'
      view_name                      = 'USH02'
    EXCEPTIONS
      no_authority                   = 2
      no_clientindependent_authority = 2
      no_linedependent_authority     = 2
      OTHERS                         = 1.
  IF sy-subrc = 2.
    RAISE not_authorized.
  ELSEIF sy-subrc = 1.
    RAISE table_not_available.
  ENDIF.

  " check input param
  IF username IS INITIAL.
    RAISE param_missing.
  ENDIF.

  " get date on remote system
  SELECT SINGLE modda FROM ush02
     INTO lockdown_date
     WHERE bname = username
     AND   uflag <> 0
     AND   modda >= ( SELECT MAX( modda ) FROM ush02 WHERE bname = username AND uflag = 0 )
     .

  " if user has been created as already locked, it means USH02 has only locked records
  " there is no UFLAG = 0. It is exceptional case.
  IF lockdown_date IS INITIAL.
    SELECT MIN( modda ) FROM ush02
       INTO lockdown_date
       WHERE bname = username
       AND   uflag <> 0.
  ENDIF.

  IF sy-subrc NE 0.
    RAISE not_found.
  ENDIF.

ENDFUNCTION.


Do not miss to provide list of used exceptions within FM.

Now whenever you will desire to know when a specific user has been locked out, run function module as follows. That's it.

        CALL FUNCTION '/YourNameSpace/RFC_GET_LOCKDOWN_DATE' DESTINATION gs_uszbvlndrc-rcvsystem

          EXPORTING
            username            = lv_username
          IMPORTING
            lockdown_date       = lv_lockdown_date
          EXCEPTIONS
            not_authorized      = 1
            table_not_available = 2
            param_missing       = 3
            not_found           = 4
            OTHERS              = 5.
          CASE sy-subrc.
            WHEN 1.
              " no authorization
            WHEN 2.
              " no required table on target system
            WHEN 3.
              " missing input parameter
            WHEN 4.
              " no data came
            WHEN OTHERS.
              " other ugly error
          ENDCASE.