In this blog, a method for enabling ICMR to read data from distant systems is introduced. You may already be aware that ICMR can read data in real-time from a CDS view in S/4HANA if you have read the blog post titled “The Next Generation Intercompany Solution: Intercompany Matching & Reconciliation.” Intercompany matching is still a major difficulty because there is still a lot of data in legacy systems. Utilizing SAP HANA Smart Data Access(SDA), ICMR is able to read data directly from distant systems, giving it the capacity to match and reconcile data in real time across a dispersed system landscape.

1. Describe SDA

SAP HANA may access distant data sources through SDA as if the data were locally stored. It is possible to create a “virtual table” that corresponds to a table in a different database system. Like regular tables, virtual tables can be edited. The ability of SAP HANA to connect to other database systems, including Oracle, MSSQL, MySQL, HADOOP, and others, is outstanding. Look in SAP Note 2600176 for the list of accessible remote sources. SDA is also covered in more detail in the SAP Help Portal.

One customer informed us that if S/4HANA is running on Little Endian architecture and the remote database is running on Big Endian, a connection cannot be made.

2. Produce a distant source

We access the information from a distant ERP system built on HANA in the step-by-step tutorial that follows. Since ICMR utilises SAP S/4HANA, the connectivity is HANA-to-HANA. However, it is safe to assume that the same strategy will apply to connections from HANA to anyDB.

Let’s start by setting up a remote source. To use either database system, you must have the appropriate authorization. We’ll refer to the target HANA database system where ICMR reads data as the “remote server” and the S/4HANA database system where ICMR is functioning as the “ICMR server.”

SAP HANA Studio should be used to connect to your ICMR database server. Right-click the “Remote Sources” folder after expanding the “Provisioning” folder and the “DB system” folder. To open the definition window, select “New Remote Source” from the context menu.

Establish a Remote Source

You must enter the following details in the definition window:

1. A distinctive source name, such as “ICMR” in the sample below;
2. You pick the appropriate adapter based on whatever database system you wish to connect to. In this instance, kindly select “HANA(ODBC)”;
3. Enter the host name of the distant server in the “Server” column;
4. Enter “30215” as the remote database server’s port in the “Port” column.
5. We can use “readonly” in the “DML Mode” section because ICMR only reads data from the remote server;
6. Authentication is required. Usually, you can access the remote server by entering the password for a technical user. Of course, you can utilise additional authentication techniques the adapter offers.

Set a distant source

You can expand your remote source definition after successfully saving it to examine every DB schema that belongs to the remote source. All the tables under a schema are visible when you expand it.

View the distant source

3. Make a digital table

You can now build virtual tables in the ICMR server after creating a connection to the distant ERP database. Consider the scenario where you want to read data from table “BSEG” in the distant ERP system.

You must first be aware of your HANA licencing model in order to build virtual tables in SAP HANA. because not all editions are permitted to directly perform data modelling at the database level. Users of S/4HANA should take note of the following two editions and select one of the two possibilities:

1. Application and SAP BW Runtime Edition of SAP HANA (HANA REAB),
2. Full Use Edition of SAP HANA

3.1 The steps that follow are only for HANA REAB:

By copying the following code lines into the ICMR server, you can construct the ABAP report “ZICA CREATE VIRTUAL TABLE”:

*&———————————————————————*

*& Report ZICA_CREATE_VIRTUAL_TABLE

*&———————————————————————*

*& Use this report to create/delete a virtual table in SAP<SID> schema.

*& Prerequites:

*&  1. You must have a remote source created in HANA.

*&  2. DB user SAP<SID> has “create virtual table” permission on

*&     the remote source.

*&  “Grant CREATE VIRTUAL TABLE on remote source <rs_name> to SAP<SID>”

*& Result:

*&  A virtual table is created in schema SAP<SID> with prefix ‘/1BCAMDP/’

*&———————————————————————*

report zica_create_virtual_table.

parameters: vt_name type string obligatory, ” the to-be-created virtual table name

            remote  type string, ” remote source

            schema  type string, ” the DB schema in the remote source

            rt_name type string, ” the remote table name

            delete  as checkbox. ” delete the virtual table

at selection-screen.

  if delete = abap_false and

     ( remote is initial or schema is initial  or rt_name is initial ).

    message ‘Parameters remote, schema, and rt_name are mandatory!’ type ‘E’.

  endif.

start-of-selection.

  data(lv_virtual_table_name) = |”/1BCAMDP/{ vt_name }”|.

  data(lv_remote_table_name) = |”{ remote }”.”NULL”.”{ schema }”.”{ rt_name }”|.

  data lv_ddl_string type string.

  if delete = abap_false.

    lv_ddl_string = |create virtual table { lv_virtual_table_name } at { lv_remote_table_name }|.

  else.

    lv_ddl_string = |drop table { lv_virtual_table_name }|.

  endif.

  try.

      data(mo_sql) = new cl_sql_statement( ).

      mo_sql->execute_ddl( lv_ddl_string ).

      message ‘Action is successfully performed!’ type ‘S’.

    catch cx_sql_exception into data(lo_exc).

      raise shortdump lo_exc.

  endtry.

If the remote source wasn’t created by the user, also add the “CREATE VIRTUAL TABLE” permission for the DB user SAPSID>.

Grant CREATE VIRTUAL TABLE on remote source ICMR to <Schema of S/4HANA>;

Now switch on and run the report. Enter the aforementioned parameters:

• VT NAME: The name of the upcoming virtual table.
• REMOTE: the name of the distant source
• The remote database schema is SCHEMA.
• RT NAME: The name of the remote table.

Using ABAP, create a virtual table.

The virtual table is generated in the namespace “/1BCAMDP/” after being properly executed. The virtual table in the aforementioned example is created as “/1BCAMDP/REMOTE BSEG.” Both REMOTE BKPF and REMOTE T001 are created using the identical procedures. Give the virtual table’s name and choose the “DELETE” flag if you wish to delete it.

Note the following restrictions:

1. Nothing to get around. In your D, Q, and P systems, you must manually generate the virtual tables.
2. During system upgrades, missing. The virtual tables will be deleted during system upgrades because they were formed under the namespace “/1BCAMDP/,” which refers to temporary items. And following the upgrade, you have to manually create them again.

By modifying the ABAP-Managed HDI Container as part of the solution’s extension, the limits can be overcome.

3.2 The steps that follow are only for the HANA Full Use Edition:
You can easily build virtual tables in HANA if you’re using the HANA Full Use Edition. This gives you extra options for controlling an object’s lifespan.

You utilise a different database schema in the ICMR server (HANA Studio) than the default S/4HANA schema (you are not allowed to create tables directly in the SAP standard schema). Expand the schema, then select “Tables” from the context menu. You select “New Virtual Table” from the context menu.

Making a virtual table

Give the table a distinctive name in the definition box, such as “REMOTE BSEG.” The “Browse…” button will open a window where you can select the target schema in the remote source and the “BSEG” filter to locate the table. After choosing the table, a definition similar to the one below will appear.

What is Virtual Table

Both REMOTE BKPF and REMOTE T001 are created using the identical procedures. Execute the following SQL statement (replacing the placeholders with your own) to ensure that ICMR can access the virtual tables in the schema:

GRANT SELECT ON SCHEMA <Schema of Virtual Table> TO <Schema of S/4HANA>;

4. Utilize the Table Function

The virtual tables cannot be accessed directly by ABAP CDS technology because they are not registered in ABAP DDIC. Table function is required to get around this.

Connect to your ICMR server using the ABAP Development Tool (Eclipse-based), expand the local objects folder, and then right-click “Data Definitions”. You can select “New Data Definition” from the context menu.

Table Function Create

You can paste the scripts below into the editor after giving them a distinctive name (in this case, “ZICMR TF”) and a brief description. You simply save it without turning it on. The fields in the data structure are defined by the scripts. Each field has a Data Element associated with it (after the colon).

@ClientHandling.type: #CLIENT_DEPENDENT

define table function ZICMR_TF

returns

{ 

RCLNT : mandt; 

method_id : ica_method_id; 

DOCNR : ica_docnr; 

DOCLN : ica_docln; 

GRREF : ica_grref; 

PSTAT : ica_pstat; 

CSTAT : ica_cstat; 

DUE_DATE : ica_due_date;

CLEARING_STATUS : ica_clearing_status; 

rbukrs : bukrs; 

ref_belnr : belnr_d; 

gjahr : gjahr; 

ref_docln : docln6; 

xopvw : xopvw; 

augdt : augdt; 

augbl : augbl; 

auggj : augbl; 

bschl : bschl; 

koart : koart; 

umskz : umskz; 

rwcur : waers; 

wsl   : fins_vwcur12; 

rhcur : waers; 

hsl   : fins_vwcur12;  zuonr : dzuonr; 

sgtxt : sgtxt;  rcomp : rcomp_d;  rassc : rassc; 

racct : bilkt_ska1; 

lracct : hkont; 

kunnr : kunnr; 

lifnr : lifnr; 

awtyp : awtyp; 

awkey : awkey; 

awsys : awsys; 

budat : budat; 

bldat : bldat; 

blart : blart;

  xblnr : xblnr1; 

bktxt : bktxt; 

xref1_hd : xref1_hd; 

xref2_hd : xref2_hd; 

usnam : usnam; 

cpudt : cpudt; 

cputm : cputm; 

aedat : aedat_bkpf; 

bvorg : bvorg;   

 xreversing : co_stflg;

  xreversed : co_stokz;

}

implemented by method

zicmr_remote_sources=>CALL_01;

The AMDP method “zicmr remote sources=>CALL 01” implements the data reading mechanism. The ABAP Development Tool also allows for the creation of AMDP methods. You just add a static method called “CALL 01” to an ordinary ABAP class that you have already created. Simply use the codes below.

class zicmr_remote_sources definition 

    public 

    final 

    create public . 

    public section.   

       interfaces if_amdp_marker_hdb .   

       class-methods call_01      

           for table function zicmr_tf. 

   protected section. 

   private section.

endclass.

class zicmr_remote_sources implementation. 

    method call_01   

       by database function for hdb language sqlscript   

       options read-only.   

       return   

       select     

        SESSION_CONTEXT(‘CDS_CLIENT’)                                                 as RCLNT,                                                    ”                                                                            as method_id,                                                                                        ‘0000000000’                                                                  as DOCNR,     

         0                                                                             as DOCLN,                                                                                          ‘000000000000’                                                                as GRREF,                                                                             ’00’                                                                          as PSTAT,      ”                                                                            as CSTAT,     

        ‘00000000’                                                                    as DUE_DATE,     

      case when xopvw = ‘X’          

              then ( case when augbl <> ” then ‘3’                                      

                                                              else ‘1’                

                         end  )        

        else ‘0’       

        end                                                                        as CLEARING_STATUS,                                                                  bseg.bukrs                                                                    as rbukrs,                                                                                      bseg.belnr                                                                    as ref_belnr,                                                                                  bseg.gjahr                                                                    as gjahr,     

      concat(‘000’, bseg.buzei)                                                     as ref_docln,     

      bseg.xopvw,     

      bseg.augdt,           

      bseg.augbl,     

      bseg.auggj,     

      bseg.bschl,     

      bseg.koart,    

      bseg.umskz,     

      bkpf.waers                                                                    as rwcur,     

      case when bseg.shkzg = ‘H’ then 0 – wrbtr          

               else wrbtr end                                                           as wsl,                                                                                       t001.waers                                                                    as rhcur,      

       case when bseg.shkzg = ‘H’ then 0 – dmbtr          

              else dmbtr end                                                           as hsl,     

     bseg.zuonr,     

     bseg.sgtxt,     

     t001.rcomp,     

     bseg.vbund                                                                    as rassc,                                                                                       bseg.altkt                                                                    as racct,                                                                                           bseg.hkont                                                                    as lracct,   

     bseg.kunnr,    

     bseg.lifnr,     

     bseg.awtyp,    

     bseg.awkey,     

     bseg.awsys,    

     bseg.h_budat                                                                  as budat,                                                                                   bseg.h_bldat                                                                  as bldat,                                                                                       bseg.h_blart                                                                  as blart,     

     bkpf.xblnr,     

     bkpf.bktxt,     

     bkpf.xref1_hd,   

     bkpf.xref2_hd,     

     bkpf.usnam,   

     bkpf.cpudt,    

     bkpf.cputm,                     

     bkpf.aedat,     

     bkpf.bvorg,     

     bkpf.xreversing,     

     bkpf.xreversed     

     from “/1BCAMDP/REMOTE_BSEG” as bseg        /* or use “ZHANGVIN”.”REMOTE_BSEG” */     

     inner join “/1BCAMDP/REMOTE_BKPF” as bkpf  /* or use “ZHANGVIN”.”REMOTE_BKPF” */                            on bseg.mandt = bkpf.mandt          

               and bseg.bukrs = bkpf.bukrs           

               and bseg.belnr = bkpf.belnr           

               and bseg.gjahr = bkpf.gjahr    

    inner join “/1BCAMDP/REMOTE_T001” as t001  /* or use “ZHANGVIN”.”REMOTE_T001″ */                               on bseg.mandt = t001.mandt           

              and bseg.bukrs = t001.bukrs     

    where bseg.mandt = ‘910’      

          and bseg.h_monat > ’00’   

    ; 

  endmethod.

endclass.

5. Create CDS View

You can generate the CDS view based on the table function “ZICMR TF”. 

The table function’s data is read by the CDS view mentioned above. Other ABAP DDIC tables can be joined, and more conversion and filtering can be done. The posting date is converted to a fiscal year and period depending on the specified fiscal year variant in the CDS view by joining with the table “FINSC FISC DATE.”

Please take note of how “cpudt” and “cputm” have been converted to UTC timestamps. The ABAP system timezone is assumed to be the same between the ICMR server and the remote server in the aforementioned example. If your situation is different, make sure to hard code the timezone using your remote server.

Once the CDS view has been successfully activated, you can run a preview on the data to double-check. Please add the required filters to prevent reading excessive amounts of data. You might receive a “out of memory” error if you don’t.

If the data compiled from the distant system and the ICMR server can be compared, you might inquire. Yes, it is the answer. By directly picking from the local table BSEG in the CDS view script mentioned above, you can merge the data from the ICMR server. See the example script below:

@AbapCatalog.sqlViewName: ‘ZICMRBSEGEV’

define view ZICMR_BSEG_ENTRY_VIEW 

     as select from ZICMR_TF as A     

            inner join finsc_fisc_date as B             

                          on A.budat = B.calendar_date

{   

    <field list>

}

union all

select from BSEG as A     

        inner join finsc_fisc_date as B             

                      on A.budat = B.calendar_date

{   

   <field list>

}

6. Develop an ICMR Data Source and Matching Procedure

Now that the technical work is over, you may perform some straightforward customizations. Access the ICMR server by using SAP Logon. To define your own ICMR Data Source, enter the TCode “ICADS”.

ICMR Data Source creation

You may simply copy from SF AR AP ENTRY VIEW and switch the primary CDS view to your remote CDS view because the data structure of the remote CDS view and the standard Data Source are the same. Save it after that.

Please take note of the “ICA COMPANY” Unit Entity CDS View as well. Make sure the CDS view can read the entire company master data set. To do this, either insert the corporate master data into table T880 on the ICMR server, or read it from the remote system using the same method.

The Matching Method you develop can be based on the Data Source. Make that the remote Data Source is assigned to the method.

Clarify the Matching Method

For information on defining Data Source and Matching Method, please see the SAP Help Portal.

Finally, the Fiori App “Manage Assignment” allows you to check the data. You have unrestricted access to all ICMR functions.

Register and manage assignments

7. The Verdict

SDA enables ICMR to read data directly from distant systems. The aforementioned illustration shows how to read data from an ERP system built on HANA. Reading data from other systems is also possible. Additionally, you can read data from various systems by integrating them using the CDS view’s “UNION” operator.

Deep inside the database, the integration takes place invisibly to the application level. The advantage is clear—it lowers the cost of data copying and enables real-time matching and reconciliation in a distributed environment. However, the following potential problems should still be considered:

1. Performance won’t compare favourably to local cases. because information is shared among database servers via a network. The database servers should have guaranteed network throughput.
2. As the filtering may not be properly delivered to the remote computers, memory consumption could be significant. You should test your S/4HANA database and allot sufficient query memory space.
3. Because the ICMR application is unaware of what is happening in the distant systems, error handling would be challenging.

Additionally, write-back changes to the source are supported by ICMR. I would NOT advise you to grant SDA write access to the virtual tables. To make the adjustments, you should rather use the remote systems’ APIs.