Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
07/24/2024 05:55 PM
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> 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
07/24/2024 06:51 PM
Keep column alias as updatedate
07/24/2024 10:16 PM
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
07/25/2024 09:55 PM
07/26/2024 11:13 PM
Hi @rakesh_iam , target column and incremental colum field names shoul be same
07/28/2024 09:53 PM
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
07/29/2024 08:16 AM
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
07/29/2024 05:35 PM
07/28/2024 10:55 PM
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.
07/28/2024 10:57 PM
07/28/2024 11:20 PM
Hi @rakesh_iam , can you share your entire configuration?
i will try to test it in my environment.