Delta reconciliation for Oracle EBS Database connector

[rakesh_iam]

Hi, 

We are trying to perform a delta reconciliation for Oracle EBS DB connector. The connector doesn't seem to accept the incremental configuration that we have been providing,

Query,

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[SELECT U.USER_NAME AS USERNAME, U.LAST_UPDATE_DATE AS LASTUPDATEDATE, TO_CHAR(U.USER_ID) AS USERID,TO_CHAR(U.EMPLOYEE_ID) AS EMPLOYEEID, TO_CHAR(U.CUSTOMER_ID) AS CUSTOMERID, TO_CHAR(U.SUPPLIER_ID) AS SUPPLIERID,TO_CHAR(U.PERSON_PARTY_ID) AS PERSONPARTYID, U.EMAIL_ADDRESS AS EMAILADDRESS,U.DESCRIPTION, CASE when U.END_DATE IS NOT NULL AND U.END_DATE <= SYSDATE THEN 2 ELSE 1 END AS accountstatus, TO_CHAR(U.CREATED_BY) AS CREATEDBY,TO_CHAR(U.LAST_UPDATED_BY) AS LASTUPDATEDBY,U.LAST_LOGON_DATE AS LASTLOGONDATE, U.CREATION_DATE AS CREATIONDATE,U.END_DATE AS ENDDATE,U.START_DATE AS STARTDATE,'Oracle EBS' as securitysystemname, 'Oracle EBS' as endpointname, 'Primary' as accounttype, 'OEBS-Responsibility' as entitlementtype,TO_CHAR(FR.RESPONSIBILITY_KEY) as RESPONSIBILITYKEY, RAWTOHEX(U.USER_GUID) AS USERGUID FROM APPS.FND_USER U JOIN APPS.FND_USER_RESP_GROUPS_ALL R ON U.USER_ID = R.USER_ID AND (R.END_DATE IS NULL OR R.END_DATE >= SYSDATE) JOIN APPS.FND_RESPONSIBILITY_VL FR ON FR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID;
]]>
</sql-query>

Incremental Configuration:

<incrementalcondition>
<![CDATA['${incrementalcolmaxval.format("dd-MMM-yy")}']]>
</incrementalcondition>
<mapper description="This is the mapping field for Saviynt Field name" addOnlyMode = "false" dateformat="timestamp" incrementalcolumn="LAST_UPDATE_DATE" deleteaccountentitlement="false" accountnotinfileaction = "noaction" ifusernotexists="noaction">

Incremental Attribute Mapping:

<mapfield saviyntproperty="accounts.CUSTOMPROPERTY45" sourceproperty="LASTUPDATEDATE" type="character"/>

The reconciliation is always getting triggered as full reconciliation. We even tried mapping the last_update_date column to a Saviynt attribute and it didn't help. 

Kindly let us know on how to configure the delta reconciliation.

Regards,

Rakesh

[rushikeshvartak]

Keep column alias as updatedate

[rakesh_iam]

Hi @rushikeshvartak ,

I updated the column alias as advised and I see two problems,

1. Query is appended with 'UPDATEDATE > '2024-07-25 00:45:18.0' and got the error stating - > java.sql.SQLSyntaxErrorException: ORA-00904: "UPDATEDATE": invalid identifier at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:526) at oracle.jdbc.driver.T4CTTIoer11.processError.This is because there is no UPDATEDATE column in target table.

2. The UPDATEDATE condition is getting appended even when we try to run Full reconciliation. We are planning to run full recon on a weekly basis and incremental on daily basis.

Thanks,

Rakesh

[rushikeshvartak]

• Does database table have update date column if not then use alias

[NM]

Hi @rakesh_iam , target column and incremental colum field names shoul be same

[rakesh_iam]

Hi,

I tried with the Incremental column as 'LAST_UPDATE_DATE', 'U.LAST_UPDATE_DATE' and it is still not working.

Query:

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[SELECT U.USER_NAME AS USERNAME, U.LAST_UPDATE_DATE AS UPDATEDATE, TO_CHAR(U.USER_ID) AS USERID,TO_CHAR(U.EMPLOYEE_ID) AS EMPLOYEEID, TO_CHAR(U.CUSTOMER_ID) AS CUSTOMERID, TO_CHAR(U.SUPPLIER_ID) AS SUPPLIERID,TO_CHAR(U.PERSON_PARTY_ID) AS PERSONPARTYID, U.EMAIL_ADDRESS AS EMAILADDRESS,U.DESCRIPTION, CASE when U.END_DATE IS NOT NULL AND U.END_DATE <= SYSDATE THEN 2 ELSE 1 END AS accountstatus, TO_CHAR(U.CREATED_BY) AS CREATEDBY,TO_CHAR(U.LAST_UPDATED_BY) AS LASTUPDATEDBY,U.LAST_LOGON_DATE AS LASTLOGONDATE, U.CREATION_DATE AS CREATIONDATE,U.END_DATE AS ENDDATE,U.START_DATE AS STARTDATE,'Oracle EBS' as securitysystemname, 'Oracle EBS' as endpointname, 'Primary' as accounttype, 'OEBS-Responsibility' as entitlementtype,TO_CHAR(FR.RESPONSIBILITY_KEY) as RESPONSIBILITYKEY, RAWTOHEX(U.USER_GUID) AS USERGUID FROM APPS.FND_USER U JOIN APPS.FND_USER_RESP_GROUPS_ALL R ON U.USER_ID = R.USER_ID AND (R.END_DATE IS NULL OR R.END_DATE >= SYSDATE) JOIN APPS.FND_RESPONSIBILITY_VL FR ON FR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID;
]]>
</sql-query>

When I use the incremental column as 'UPDATEDATE' (alias name), the query is appended with 'UPDATEDATE' as shown below,

SELECT U.USER_NAME AS USERNAME, TO_CHAR(U.LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS UPDATEDATE, TO_CHAR(U.USER_ID) AS USERID,TO_CHAR(U.EMPLOYEE_ID) AS EMPLOYEEID, TO_CHAR(U.CUSTOMER_ID) AS CUSTOMERID, TO_CHAR(U.SUPPLIER_ID) AS SUPPLIERID,TO_CHAR(U.PERSON_PARTY_ID) AS PERSONPARTYID, U.EMAIL_ADDRESS AS EMAILADDRESS,U.DESCRIPTION, CASE when U.END_DATE IS NOT NULL AND U.END_DATE <= SYSDATE THEN 2 ELSE 1 END AS accountstatus, TO_CHAR(U.CREATED_BY) AS CREATEDBY,TO_CHAR(U.LAST_UPDATED_BY) AS LASTUPDATEDBY,U.LAST_LOGON_DATE AS LASTLOGONDATE, U.CREATION_DATE AS CREATIONDATE,U.END_DATE AS ENDDATE,U.START_DATE AS STARTDATE,'Oracle EBS' as securitysystemname, 'Oracle EBS' as endpointname, 'Primary' as accounttype, 'OEBS-Responsibility' as entitlementtype,TO_CHAR(FR.RESPONSIBILITY_KEY) as RESPONSIBILITYKEY, RAWTOHEX(U.USER_GUID) AS USERGUID FROM APPS.FND_USER U JOIN APPS.FND_USER_RESP_GROUPS_ALL R ON U.USER_ID = R.USER_ID AND (R.END_DATE IS NULL OR R.END_DATE >= SYSDATE) JOIN APPS.FND_RESPONSIBILITY_VL FR ON FR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID WHERE UPDATEDATE > '2024-07-25 00:45:18.0'

But the query execution fails as there is no 'UPDATEDATE' column in target DB.

When I use the actual column name 'LAST_UPDATE_DATE' as Incremental column, the query is not appended with the incremental condition as shown below,

SELECT U.USER_NAME AS USERNAME, TO_CHAR(U.LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS UPDATEDATE, TO_CHAR(U.USER_ID) AS USERID,TO_CHAR(U.EMPLOYEE_ID) AS EMPLOYEEID, TO_CHAR(U.CUSTOMER_ID) AS CUSTOMERID, TO_CHAR(U.SUPPLIER_ID) AS SUPPLIERID,TO_CHAR(U.PERSON_PARTY_ID) AS PERSONPARTYID, U.EMAIL_ADDRESS AS EMAILADDRESS,U.DESCRIPTION, CASE when U.END_DATE IS NOT NULL AND U.END_DATE <= SYSDATE THEN 2 ELSE 1 END AS accountstatus, TO_CHAR(U.CREATED_BY) AS CREATEDBY,TO_CHAR(U.LAST_UPDATED_BY) AS LASTUPDATEDBY,U.LAST_LOGON_DATE AS LASTLOGONDATE, U.CREATION_DATE AS CREATIONDATE,U.END_DATE AS ENDDATE,U.START_DATE AS STARTDATE,'Oracle EBS' as securitysystemname, 'Oracle EBS' as endpointname, 'Primary' as accounttype, 'OEBS-Responsibility' as entitlementtype,TO_CHAR(FR.RESPONSIBILITY_KEY) as RESPONSIBILITYKEY, RAWTOHEX(U.USER_GUID) AS USERGUID FROM APPS.FND_USER U JOIN APPS.FND_USER_RESP_GROUPS_ALL R ON U.USER_ID = R.USER_ID AND (R.END_DATE IS NULL OR R.END_DATE >= SYSDATE) JOIN APPS.FND_RESPONSIBILITY_VL FR ON FR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID

It looks like Saviynt only appends the query when the incremental column is equal to 'UPDATEDATE'

Regards,

Rakesh

[rushikeshvartak]

This should work as it was fixed in 

CONN-3763

https://docs.saviyntcloud.com/bundle/Release-Notes/page/Content/v2021.0/Release-Notes-v2021-0-1.htm

Please raise support ticket referencing above details

[rakesh_iam]

Hi @rushikeshvartak ,

Thanks. I will raise a support ticket for this.

Regards,

Rakesh

[NM]

Hi @rakesh_iam , what I will suggest.

Create 2 connections one for full other for incremental.

In incremental connection add your own where condition to get update from last one day.

[rakesh_iam]

Hi @NM ,

Yes, thats the last option that we were considering.

Regards,

Rakesh

[NM]

Hi @rakesh_iam , can you share your entire configuration?

i will try to test it in my environment.