Pitfalls at S/4HANA db tables with replacement object
Due to the change of some data models, SAP uses so-called replacement objects for some db tables in S/4HANA. There are some oddities to consider, which I would like to discuss in this blog post.
Since the basis for the replacement object is a CDS-View, it seems mysteriously that a db table can also exist on the HANA database at the same time, where individual data records can be stored.
2 Main Part
2.1 Example and technical Basics
A good example is the table COEP (CO object line items by period).
In the text editor of eclipse the replacement object is specified using an annotation.
This means that the CDS-View V_COEP_VIEW is responsible for the data collection.
With the help of the Dependency Analyzer you can quickly get an overview which database objects are finally invoved in this CDS-View.
We can see that the view apparently recursively accesses the COEP table and other tables like the ACDOCA.
The UNION statement indicates that part of the data records are from COEP and the rest from ACDOCA and the other tables.
2.2 Where is what
To find out where the data are, let’s try to use our well-known board tools like transaction SE16h.
By the way: In SE16h “Proxy Object” means the sqlViewName for the replacement object CDS-View.
In sum we have 5.395 datarecords, but who many are stored in table COEP directly?
To find this out I wrote a small report with native SQL, considering client handling manually.
REPORT y_coep_demo. DATA: gv_count TYPE i. TRY. EXEC SQL. SELECT COUNT( * ) FROM coep INTO :gv_count WHERE MANDT = :sy-mandt ENDEXEC. WRITE: gv_count. CATCH cx_sy_native_sql_error. ENDTRY.
The result shows that 483 records are stored directly in the HANA COEP table.
However, can we assume that only the remaining 4912 data records can be read via the CDS-View V_COEP_R3?
Unfortunately no. Here we have as result 5262 records.
And what is the difference of 350 datasets?
2.3 Harder weapons
We need to find out the data sets that are in both the COEP table and the CDS-View V_COEP_R3_VIEW.
Therefore I wrote a second little report.
To make it a bit easier and to use few lines, I select with * what is “forbidden” in a productive program.
REPORT y_coep_demo2. DATA: go_table TYPE REF TO data, gt_coep TYPE SORTED TABLE OF coep WITH UNIQUE KEY kokrs belnr buzei. FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE. DATA(go_struct_descr) = CAST cl_abap_structdescr( cl_abap_structdescr=>describe_by_name( 'COEP' ) ). DATA(go_table_descr) = cl_abap_tabledescr=>create( CAST #( go_struct_descr ) ). CREATE DATA go_table TYPE HANDLE go_table_descr. DATA(go_result) = NEW cl_sql_statement( )->execute_query( `SELECT * FROM coep WHERE MANDT = ` && sy-mandt ). go_result->set_param_table( itab_ref = go_table ). go_result->next_package( upto = 6000 ). ASSIGN go_table->* TO <gt_table>. IF <gt_table> IS ASSIGNED. gt_coep = <gt_table>. ENDIF. SELECT kokrs, belnr, buzei FROM v_coep_r3_view INTO TABLE @DATA(gt_coep_r3). DATA(gt_diff) = FILTER #( gt_coep_r3 IN gt_coep WHERE kokrs EQ kokrs AND belnr EQ belnr AND buzei EQ buzei ). WRITE: lines( gt_diff ).
And lo and behold, here are exactly the 350 records, which are present in both.
2.4 Crazy Details
There are document numbers where the individual lines are divided into different db tables and are combined again using the replacement object.
Posting rows 1 and 3 (BUZEI) are in COEP directly.
and posting rows 2 and 4 are finally in the ACDOCA via V_COEP_R3 CDS-View stack.
In order to finally find out from which db table the data really came from using a replacement object, it need a lot of investigation.
You also have to use native SQL to select the “real” data in the db table. This could also be achieved with SQL-Script within AMDP.
An update on the database table with replacement object can only be done for the records which are really stored in HANA table. The update will fail if data records are read from other CDS-Views.