Our requirement is to fetch data from Oracle EBS and we cannot use APPS schema for connection with EBS. In order to achieve this I checked FDMEE admin guide and found that there are around 45 Ebs table on which I would require access. I created a READONLY schema with name EBS_READONLY in EBS database and then created synonym for these 45 objects and granted READONLY access to my EBS_READONLY schema on these 45 objects.
In ODI console I gave a details of EBS_READONLY schema in EBS_DATA_SERVER and tested the connection, it was successful.
Now I went to FDMEE and in source tab I clicked on Initialize source option but got below error in ODI console.
ODI-1217: Session EBS_GL_SETUP_SOURCE_SYSTEM (3501) fails with return code 903. ODI-1226: Step EBS Extract COA Ledgers fails after 1 attempt(s). ODI-1240: Flow EBS Extract COA Ledgers fails while performing a Loading operation. This flow loads target table AIF_COA_LEDGERS. ODI-1227: Task SS_0 (Loading) fails on the source ORACLE connection EBS_DATA_SERVER. Caused By: java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
The table mentioned in the above error " AIF_COA_LEDGERS " is available in FDMEE repository.
My question :-
1. DO I need to provide any details in ODI console ---> Topology --> Data server --> FDMEE_DATA_SERVER.
2. DO I need to give any detail in Physical schema. Note:- Under physical schema I can see EBS_DATA_SERVER.schema and FDMEE_DATA_SERVER.schema.
Kindly suggest something.
You need to specify the APPS schema in Oracle EBS. It should end up showing as EBS_DATA_SERVER.APPS.
Then retry the connection.