Account Reconciliation for Oracle EBS Application

[rakesh_iam]

Dear Team,

We are trying to integrate with Oracle EBS application and are using OOTB Oracle EBS connection to reconcile the required data. We could find that the integration is only bringing in active accounts from EBS. Query used by the integration is provided below,

SELECT USER_NAME,
TO_CHAR(LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
EMPLOYEE_ID,
LAST_LOGON_DATE,
CREATION_DATE,
CUSTOMER_ID,
USER_ID,
SUPPLIER_ID,
END_DATE,
START_DATE,
USER_NAME,
USER_GUID,
PERSON_PARTY_ID,
EMAIL_ADDRESS,
DESCRIPTION
FROM APPS.FND_USER
WHERE ( END_DATE > SYSDATE OR END_DATE IS NULL)

We would like to know if there any way to bring in all the accounts (active and inactive) accounts from Oracle EBS using OOTB connection. 

Note: We dont want to use generic database connector as all other information (except inactive accounts) are obtained as expected using OOTB Oracle EBS connection.

Regards,

Rakesh

[rushikeshvartak]

SELECT USER_NAME,
       TO_CHAR(LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LAST_UPDATE_DATE,
       CREATED_BY,
       LAST_UPDATED_BY,
       EMPLOYEE_ID,
       LAST_LOGON_DATE,
       CREATION_DATE,
       CUSTOMER_ID,
       USER_ID,
       SUPPLIER_ID,
       END_DATE,
       START_DATE,
       USER_NAME,
       USER_GUID,
       PERSON_PARTY_ID,
       EMAIL_ADDRESS,
       DESCRIPTION
FROM APPS.FND_USER

[rakesh_iam]

Hi @rushikeshvartak ,

Thanks for your response.

I know the changes that are to be made to the SQL query. The question was more around how to update the SQL query in Saviynt (Oracle EBS Connector). The connector seems to have the queries in-built in the connector rather than exposed as a configuration.

Link to connector document: https://docs.saviyntcloud.com/bundle/OracleEBS-v24x/page/Content/Configuring-the-Integration-for-Imp...

Could you please advise if there is a way to update the SQL query for EBS connection.

Regards,

Rakesh

[rushikeshvartak · Accepted solution]

This is OOTB connector . You can use db connector and raise idea ticket to expose query in OOTB connector

[rakesh_iam]

Hi @rushikeshvartak ,

Thanks! We were hesitant to use DB connector as there are different entitlements and entitlement hierarchies available in Oracle EBS. The document says we would need individual database connector to pull different entitlements and their relationships.

We have found a way to pull the accounts using DB connector and entitlements using OOTB connector.

Thanks

[rushikeshvartak]

It will be great if you can share XML