1. Getting Started with SAP ABAP ERP Adapter for Oracle Data Integrator
  2. Using Advanced Join Handling for Change Detection with SAP CDHDR Table

7 Using Advanced Join Handling for Change Detection with SAP CDHDR Table

The SAP system table CDHDR holds information about data changes in SAP business tables like MARA. Among other data this includes the (concatenated) business key (field CDHDR.OBJECTID), change type and change timestamp (field CDHDR.UDATE and UTIME).

In this example the business key is the material number (MARA.MATNR). Starting with v42 the ODI SAP adapter for SAP ERP/ECC supports substring operations in join conditions. This chapter gives an example of how to use a complex join expression between the SAP tables MARA and CDHDR to extract only new/modified/deleted MARA records.

In case of MARA you can identify relevant changes with the help of CDHDR by applying these filter/join conditions:

  • record type filter on CDHDR: CDHDR.OBJECTCLASS = 'MATERIAL'

  • time filter on CDHDR: the timestamp (CDHDR.UDATE, CDHDR.UTIME) is greater than the last extraction timestamp and smaller than the current timestamp

  • join condition: the first 18 characters of CDHDR.OBJECTID match MARA.MATNR

Note:

For other object classes and their object id composition/ business key, contact your SAP team.

The following sections cover all the steps needed for building an ODI scenario for delta extraction: on every execution the package extracts any MARA changes since last run and applies the changes to the Oracle target table.

7.1 Required Tools and Versions

This section lists the required tools and their versions.
  1. ODI Version — Oracle Data Integrator Version 12.2.1.2.3 & above
  2. Open Tool Version — Oracle SAP Connector Version 10.1.3.23 & above
  3. JCo version
    1. JCo API — 3.0.17 (2017 –07–17) & above version
    2. JCo Middleware — JavaRfc 2.2.20
    3. JCo Library — 721.913
    4. Path to JCo Archive — c:\Oracle\Middleware\Oracle_Home1\odi\sdk\lib\sapjco3.jar
    5. Path to JCo Library — c:\Oracle\Middleware\Oracle_Home1\odi\sdk\lib\sapjco3.dll
  4. SAP KMs Build Number - 42 & above
  5. IKM - IKM Oracle Incremental Update

7.2 Creating Variables

This section describes all the ODI variables used in the below example. Through these variables you can save and get the value of last extract timestamp. Please create all the variables as specified below:
  1. LAST_EXTR_TS — This variable contains the value for Last Extract Timestamp. Its default value is 00000000:000000 which is required for first attempt and it keeps the latest value in history as shown below:
  2. LAST_EXTR_DATE — This variable contains Last Extract Date which takes its value from the Last Extracted Timestamp parameter. The refresh expression shown below is used to update the variable:
  3. LAST_EXTR_TIME — This variable contains Last Extract Time which takes its value from Last Extracted Timestamp parameter.
  4. CURR_TS— This variable contains the Current Timestamp. The refresh expression shown below is used to update the variable:
  5. CURR_DATE— This variable contains Current Date which takes its value from CURR_TSparameter. The refresh expression shown in the below image is used to update the variable:
  6. CURR_TIME — This variable contains Current Time which takes its value from CURR_TSparameter. The refresh expression shown below is used to update the variable:

7.3 Creating Mapping with Advanced Join Condition

Create a simple mapping where you can join two tables as CDHDR and MARA based on below condition and use two filters to extract the data from SAP and store in target oracle table named MARA_REPLICATED.
  1. Join Condition
    Join OBJECTIDof CDHDRfrom index zero to 18 with MATNR of MARA.
    CDHDR.OBJECTID+0(18) = MARA.MATNR
  2. Mapped fields from Source to Target

    In this mapping, you can map (by drag and drop) the following fields from source (MARA and CDHDR)to target (MARA_REPLICATED). 

    MARA.MANDT -> MARA_REPLICATED.MANDT
MARA.MATNR -> MARA_REPLICATED. MATNR
MARA.LVORM -> MARA_REPLICATED.DELETED
MARA.MEINS -> MARA_REPLICATED.MEINS
CDHDR.OBJECTID -> MARA_REPLICATED.CDHDR_OBJECTID
CDHDR.UDATE -> MARA_REPLICATED.UDATE
CDHDR.TCODE -> MARA_REPLICATED.TCODE
Mapping
Description of mapping.png follows
Description of the illustration mapping.png
CDHDR.OBJECTID+0(18) = MARA.MATNR
Description of cdhdr-objectid0-18-mara-matnr-mapping.png follows
Description of the illustration cdhdr-objectid0-18-mara-matnr-mapping.png

7.4 Using Filters Condition in Mapping

You can use filter conditions to select the related data change log entries. Listed below are two filters — OBJECTCLAS of CDHDRand the next filter is using few variables related to date and time as mentioned above for extracting updated records.

  • Filter 1 contains below condition: 

    CDHDR.OBJECTCLAS = 'MATERIAL'
    Description of filter-1.png follows
    Description of the illustration filter-1.png

  • Filter 2 contains below condition:

    Based on this condition you can retrieve only updated records which is not extracted yet.

    (CDHDR.UDATE > #SAP.LAST_EXTR_DATE OR (CDHDR.UDATE = 

#SAP.LAST_EXTR_DATE AND CDHDR.UTIME >  #SAP.LAST_EXTR_TIME)) AND

(CDHDR.UDATE < #SAP.CURR_DATE  OR (CDHDR.UDATE = #SAP.CURR_DATE  AND 

CDHDR.UTIME <= #SAP.CURR_TIME))
    Description of filter-2.png follows
    Description of the illustration filter-2.png

7.5 Creating a Package for Extracting Updated Records

Create a package by following the instructions given below which contains few Date and Time range ODI variables and also create a mapping as described above:

Description of createpackage1.png follows
Description of the illustration createpackage1.png

There are eight steps in creating the package (termed as Steps Hierarchy) as shown below:

Description of createpackage2.png follows
Description of the illustration createpackage2.png

Set Variable: Set the value for LAST_EXTR_TS variable as shown below:

Description of createpackage3.png follows
Description of the illustration createpackage3.png

7.6 Understanding Last Extract Timestamp Parameter

Before extracting the records, you have to check Last Extract Timestamp parameter so that you can know the exact time when the records were extracted. Default value of Last Extract Timestamp is 00000000:000000 and after first execution it picks its value from History.

Description of last-extract-timestamp-parameter.png follows
Description of the illustration last-extract-timestamp-parameter.png

7.7 Running the Package

By using Join function with CDHDR table there are various execution processes to extract record from SAP and store in Oracle database. Here we are using V42 SAP KMs with Incremental IKM.

7.7.1 Execute Scenario to Extract all the Updated Records

In the IKM, set theCreate_Target_Table option to True, as you are retrieving records for the first time, as given below:

7.7.2 Create New Records in SAP table and Execute Package to Extract New Records

To create new records in SAP table:
  1. Create a new material as shown below:
  2. Modify the Mapping Flow
  3. Check the value for last extracted Timestamp in the History tag of LAST_EXTR_TS parameter.
  4. Execute the Package - After creating a new material in the SAP table execute the package again. When the Package gets executed successfully, one new record is inserted in the Oracle Target Database.
  5. Extracted Records- The top most records in the below table is the new record which is inserted post package execution.

7.7.3 Update Records in SAP and Execute the package to Extract Updated Records

From the above image, the value of column MEINS for material 33 and 41 is as HL and G respectively. Now you can update the MEINS column value for these records as shown below:
  1. Update the value for these two records in SAP for material number 41 and 33 as shown below:
  2. Check the last extracted Timestamp from the History tag of the parameter LAST_EXTR_TS.
  3. Execute Package - After updating materials in SAP table execute the package again.
  4. Package is executed successfully and two records are updated in Oracle Target Database. Its record statistics is displayed as shown below:
  5. Extracted Records - Below highlighted records are updated in the target database after package execution.

7.7.4 Delete Records from SAP and Execute Package

For deleting records from SAP table:
  1. From the below MADescripRA_REPLICATED Target table find the highlighted details before the flagging material for deletion.
    Material 31 and 34 are marked for deletion. Field LVORM (DF at client level) will be empty before deletion in the MARA Table which we mapped with field DELETED in Target table.
  2. CDHDR table adds a new row for all the new material created, updated and flagged for deletion.
    TCode field holds the below values:
    1. MM01: New material created
    2. MM02: Existing material updated
    3. MM06: Material marked for deletion
  3. MARA Table - If a material is marked for deletion a new row is added with Tcode : MM06 (Deletion). After flagging material for deletion Field LVORM (DF at client level) is updated with ‘X’.
  4. Execute package - Package execute success as find below instance there are two records are changed.
  5. Extracted Records - After package execution find the extracted records in target oracle table MARA_REPLICATED as below.