Integration

Hi SOA Gurus,
I have an issue with the access rights of the user in the DBAdapter.

I am integration eBS and OTM using standard SOA11g integration provided by Oracle. I have tried to setup as given in metalink note "Integrating Oracle E-Business Suite 12.1.3 with BPEL in SOA Suite 11g (Doc ID 1070257.1)"
When BPEL process from eBS is called, it fails to read objects from eBS DB-> Apps schema and whole process errors out.


1. Below command has been used to create connections and Datasource
ant -f EbsBpelMasterBuild11g.xml createConnections \
-DdataSourceName=CustomerDbConnectionDS \
-DdataSourceJNDIName=jdbc/CustomerDbConnectionDS \
-DasadminUser=SOAADMIN \
-DasadminPassword=qa_soa123 \
-DdbcFile=/u01/app/middleware/appsutil/QA_qa/bpel/QA_SOAQA.dbc \
-DdataSourceType=XA \
-Dadapters=multiple \
-DignoreDataSourceExists=yes

2. When we run the BPEL Process, we get the below error.
Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'AuthenticateTicket' failed due to: Stored procedure invocation error. Error while trying to prepare and execute the WSH_OTM_REF_DATA_GEN_PKG.VALIDATE_TKT API. An error occurred while preparing and executing the WSH_OTM_REF_DATA_GEN_PKG.VALIDATE_TKT API. Cause: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'WSH_OTM_REF_DATA_GEN_PKG.VALIDATE_TKT' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored Check to ensure that the API is defined in the database and that the parameters match the signature of the API


Basically I query any object from APPS schema it says object does not exist. If I prefix apps., then it is able to query the table. But I cannot modify the standard BPEL process.


3. To verify which user I am connected to, I create a Partnerlink to query select user from dual which is shown as "SOAADMIN"

4. So somehow SOAADMIN user used does not have all access to APPS. Or in other words it expect schema name to be prefixed to access tables belonging to APPS schema.

5. The .dbc file that I have in the instance has only the below enteries. GUEST_USER_PWD is not there.
FNDNAM=APPS
APPL_SERVER_ID=D0B9A....
APPS_JDBC_URL=jdbc\:oracle\:thin\:@(DESCRIPTION\=(ADDRESS_LIST\=(LOAD_BALANCE\=YES)(FAILOVER\=YES)(ADDRESS\=(PROTOCOL\=tcp)(HOST\=....)(PORT\=1526)))(CONNECT_DATA\=(SERVICE_NAME\=QA)))
GWYUID=APPLSYSPUB/PUB

So I think my confusion is how this DB Connection works. Is the -DasadminUser-> SOAADMIN user is used or is the .DBC file is used to connect to DB.
a. If .dbc file is used why it does not connects using APPS details.
b. If -DasadminUser user is used, do I need to give any SQL privilage to access Apps tables.

An insight/direction in the above issue will help to answer this BIG open issue which is creating a huge delay in out project.

Thanks,
Shobana