OIC ERP - How to extract Incremental data from ERP using BIP report in Oracle integration

Method 1:  Use a custom sql query in Data model to include last update date which will be passed as parameter from OIC. In OIC, we will use schedule parameter to persist the last update date or last run date to get the latest incremental data.

For example, the following custom SQL will create a custom data model for Suppliers.  Add the parameter LAST_RUN_DATE when is prompted and set the Date Format String to MM-dd-yyyy HH:mm:ss.

SELECT
    Supplier.VENDOR_ID AS Supplier_Id,
    Party.PARTY_NAME AS Supplier_Name,
    Party.PARTY_NUMBER AS Supplier_Number,
    Party.DUNS_NUMBER_C AS Duns_Number,
    Supplier.VENDOR_TYPE_LOOKUP_CODE AS Supplier_Vendor_Type,
    Party.PARTY_TYPE AS Supplier_Type,
    Supplier.LAST_UPDATE_DATE AS Last_Update_Date
FROM 
    POZ_SUPPLIERS Supplier LEFT OUTER JOIN 
    HZ_PARTIES Party ON Supplier.PARTY_ID = Party.PARTY_ID
WHERE 
    Supplier.LAST_UPDATE_DATE >= NVL(:LAST_RUN_DATE, Supplier.LAST_UPDATE_DATE)

From OIC:

We can also include start and end date to pull the incremental data as below query:

SELECT
    Supplier.VENDOR_ID AS Supplier_Id,
    Party.PARTY_NAME AS Supplier_Name,
    Party.PARTY_NUMBER AS Supplier_Number,
    Party.DUNS_NUMBER_C AS Duns_Number,
    Supplier.VENDOR_TYPE_LOOKUP_CODE AS Supplier_Vendor_Type,
    Party.PARTY_TYPE AS Supplier_Type,
    Supplier.LAST_UPDATE_DATE AS Last_Update_Date
FROM 
    POZ_SUPPLIERS Supplier LEFT OUTER JOIN 
    HZ_PARTIES Party ON Supplier.PARTY_ID = Party.PARTY_ID
WHERE 
    Supplier.CREATION_DATE >= NVL(:START_DATE, Supplier.CREATION_DATE) AND Supplier.CREATION_DATE < NVL(:END_DATE, Supplier.CREATION_DATE)

Method 2 : using Modified sql query and using ESS job respective ess_request_history table "processstart" time as last run time.

Please follow my below blog.

https://soalicious.blogspot.com/2021/05/oic-ess-job-run-for-delta-calculation.html

Reference:

https://blogs.perficient.com/2017/02/28/using-custom-sql-bip-to-extract-erp-cloud-data-from-otbi/