Oracle Comms BRM Integration Pack for Oracle EBS: Revenue Accounting 11.1 Configuration and Troubleshooting

Configurations

1. Changing the Oracle General Ledger Database Schema Name

The standard Oracle Application Integration Architecture (Oracle AIA) installation process assumes the Oracle General Ledger database schema name is the same as the database user name. We changed this to “BRM_GL” from “GL”.

ODI Studio > Topology Tab > Technologies > Expand Oracle Folder > Expand Oracle GL Interface DS > Oracle GL Interface DS.GL >Physical Schema

Change Schema and Work Schema value to actual G/L database name provided.

2 2. Scheduling the agent

Portal to Oracle GL Project > Portal To Oracle GL Project > Packages > Load Portal Data To Oracle GL Pkg > Scenarios > LOAD PORTAL DATA TO ORACLE _GL_PKG Version 001 > Scheduling > New Scheduling

In scenario scheduling window, set below values:--

Context = Portal to GL Context.

Agent = RevMgmtAgent.

Log Level = 5.

And set Execution cycle frequency

3. Modify AIAConfigurationProperties.xml

· Added <Property Name=" Source SystemID ">LEDGER ID</Property> under module Name RevMgmtSetOfBooksIDs.

Replace “Source SystemID” tag value with the SourceSystemID that comes in BRM XML GL Reports.

Replace “LEDGER ID” with SOB ID/LEDGER ID as defined in EBiz.

· Under Module Name RevMgmtParameters check and modify if required the values of tags TargetId,ToMailAddress, FromMailAddress, TargetFileLocation, ErrorFileTargetLocation, SegmentSeparator, PayloadSourceLocation, Mail Server and RevMgmtHome.

Running the RA PIP

Starting the scheduler (oracledi/agent/bin) nohup ./agent.sh -NAME=RevMgmtAgent -PORT=20910 &

Stopping the scheduler ./agentstop.sh -NAME=RevMgmtAgent

Issues and Workarounds

Datasource test connection failed (null password given).

ODI-3034:Test connection failed due to ORA-01005:null password given;logon denied

However, password field was not blank. As a workaround, password was punched in again and connection was successfully tested.

1 2. Insufficient privilege issue in Load ARDiscountAccount step

ODI-1228: Task SrcSet0 (Loading) fails on the target ORACLE connection Oracle GL Interface DS. Caused By: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges 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) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)

Recheck the configuration of schema and work schema Oracle GL Interface DS. Schema and work schema configured do not have sufficient privileges.

3. ODISendMail Step Failed. There is no data in GL_Interface tables

javax.mail.MessagingException:Could not connect to SMTP host :xxxx.xxxx.com , port 25

For the data to go into GL_INTERFACE tables , we need to make sure make sure the email address used on the FromMailAddress property of the ModuleConfiguration for module RevMgmtParameters defined on the AIAConfigurationProperties.xml file are correct. (Refer Odi Process LOAD_PORTAL_DATA_TO_ORACLE_GL_PKG Successful load; No Data in Gl_interface (Doc ID 888768.1)).

4. Procedure Load Payload Execution failed

ODI-1226: Step Load Payload fails after 1 attempt(s). ODI-1232: Procedure Load Payload execution fails. ODI-1228: Task Load Payload (Procedure) fails on the target XML connection Portal GL Data DS. Caused By: java.sql.SQLException: object name already exists: PORTAL_REVENUEAMOUNTS in statement [create table PORTAL_REVENUEAMOUNTS(BRM_GL_ID NUMERIC(10) NULL, BRM_GL_IDORDER NUMERIC(10) NULL, ELEMENT NUMERIC(10) NOT NULL, GENERALLEDGERREPORTFK NUMERIC(10) NULL, RESOURCEID NUMERIC(10) NULL, RESOURCEIDORDER NUMERIC(10) NULL, REVENUEAMOUNTSORDER NUMERIC(10) NULL, REVENUEAMOUNTSPK NUMERIC(10) NOT NULL)] at com.sunopsis.jdbc.driver.xml.SnpsXmlStatementRedirector.execute(SnpsXmlStatementRedirector.java:125)

This was because we tried to process a file from “input” folder which was also present in “failure” folder. After clearing the failure folder, files were re-processed, Load Payload Procedure executed successfully.

LoadPayload Step Failed

Error
###############
Return Code : 8000
Message ODI-1226: Step Load Payload fails after 1 attempt(s).
ODI-1232: Procedure Load Payload execution fails.
ODI-1228: Task Load Payload (Procedure) fails on the target XML connection Portal GL Data DS.
Caused By: java.sql.SQLException: ODI-40844: Could not generate the DTD because the file could not be created. Verify that you have write permission in the directory.
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchema.generateDTD(SnpsXmlSchema.java:853)
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchema.<init>(SnpsXmlSchema.java:483)
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchemaManager.createNewSchema(SnpsXmlSchemaManager.java:295)
################

As a workaround, below steps were performed.1.> Edit the Procedure Load Payload
2.> Prefix all variables with the project code LOAD FILE "#PORTAL_TO_ORACLE_GL_PROJECT.PVV_REV_MGMT_HOME/ODIInProcess/#PORTAL_TO_ORACLE_GL_PROJECT.PVV_ORIG_FILE_NAME<@@>" WITH DTD "#PORTAL_TO_ORACLE_GL_PROJECT.PVV_LISTENER_FOLDER/schema/brm_gl_data.xsl
3.> Regenerate the Scenario

Oracle Fusion Middleware

Main Menu

Wednesday, September 3, 2014