Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

R12 Features > Proxy User

Posted on September 23rd, 2011 by Sanjit Anand ||Email This Post Email This Post

There is “Proxy User” functionality you have in R12. Proxy User allows a user to specify a proxy who can act on their behalf.Such typical example are executive who can designate an assistant/admin staff as a proxy, allowing that assistant to Create, edit or approve transactions on behalf of that executive.This have Integration with Internal Controls Manager for preventive enforcement of Separation of Duties (SoD) constraints

Once you have enabled:

  • No need to share your password!
  • Grant limited auditable access
  • Gives all or nothing delegation ability
  • In Proxy Mode, Page Access Tracking (PAT ) is automatically turned on, to audit the pages visited by the user
  • Can be used to solve the concurrent request scheduling problem

Posted in Security | No Comments »

Oracle XML Gateway

Posted on September 21st, 2011 by Sanjit Anand ||Email This Post Email This Post

XML Gateway is a set of services that allows for easy integration with the Oracle e-Business Suite to create and consume XML messages triggered by business events.

Oracle XML Gateway supports both Business-to-Business (B2B) and Application-to-Application (A2A) initiatives.

Typically used to integrate with trading partners using XML Document formats that conform to OAG standards

With Oracle XML Gateway services, you are assured consistent XML message implementation when integrating with the Oracle E-Business Suite, thereby lowering integration costs and expediting message implementation while supporting corporate e-business initiatives.

It integrates with Oracle Advanced Queuing to enqueue/dequeue a message which is then transmitted to/from the business partner via any message transport agent.

dgreybarrow The Oracle XML Gateway performs the following functions:

  • Define trading partner and trading partner locations
  • Enable transactions for trading partners
  • Provide general code conversion between trading partner codes or standard codes and the codes defined in Oracle Applications
  • Define mapping files for data conversion from XML to relational table formats and vice versa
  • For inbound transactions, receive XML files (typically in OAG format), parse them and import data into application open interface tables so that application program interfaces (API) can validate and update Oracle application tables
  • For outbound transactions, extract data from Oracle application tables generate XML messages and dispatch them to trading partner applications (using OTA)

dgreybarrow Features of XML Gateway

  • XML Gateway is Event Based, real-time system
  • Messages/Information are based on a single transaction.
  • Oracle XML Gateway supports all DTD (Document Type Definition) based XML standards.
  • Oracle XML Gateway processes or sends standards compliant XML messages without the use of a 3rd Party translator.

dgreybarrow Oracle XML Gateway Architecture

The services supported by Oracle XML Gateway are grouped into three major components as follows:

  • Message Designer – used to Create ‘Message Maps’ between Oracle e-Business Suite and XML standard of choice
  • Message Set Up -used to Configure message implementation by Trading Partner and message
  • Execution Engine -Create or consume well-formed and valid XML messages based on trading partner message map

Figure 1 shows the relationship of these three components:


It interfaces with the following Oracle products:

  • Oracle Transport Agent for message delivery
  • Oracle Advanced Queuing for message propagation, and queue management
  • Oracle Workflow Business Event System to publish and subscribe to business events. Workflow also provides an active e-mail notification to report errors detected by the XML Gateway Execution Engine, Advanced Queuing (AQ), or the transport Agent

Design Time Components

  • Message Designer – is a wizard-guided, repository-based tool used to define message maps which defines mapping between Oracle Application data to OAGIS DTD XML documents. A message map represents the relationship between the source and target data elements.
  • XML Gateway Setup – To implement a message with a trading partner, XML Gateway setup features is used to define the Trading Partner or hub, code conversion values, and internal-to-external transaction name cross-references (Domain value maps)

Run Time Components

  • Execution Engine
    The XML Gateway Execution Engine is responsible for interacting with several Oracle technologies to process and transport XML messages to and from Trading Partners for B2B integration, or other information systems both within and outside the enterprise for A2A integration.
  • Engine run time engine for validate TP in case of B2B integration, create consume XML messages, Interacts with Advanced Queuing (AQ) to enqueue / dequeue messages and used to transform XML documents to Oracle applications. It communicates through AQ. It has Transport agents which can expose using XML gateway services over http or web services

Oracle XML Gateway leverages the Oracle Workflow Business Event System to publish and subscribe to application business events of interest to automatically trigger message creation or consumption.

The XML Gateway Execution Engine interfaces with the Oracle E-Business Suite via business events and event subscriptions to retrieve data from or populate data into the Oracle e-Business Suite tables. The XML Gateway Execution Engine interfaces with Oracle Advanced Queuing to stage outbound XML messages or receive inbound XML messages for processing.

The Oracle Transport Agent interfaces with Oracle Advanced Queuing to deliver outbound messages and to receive inbound messages.

Message Queues
The XML Gateway uses queues specifically at two points in the process as well as employing a general error queue. The first point is at the transport agent level between the transport agent module and the XML Gateway. The second point is at the transaction level between base Oracle Applications products and the XML Gateway.

  • Inbound Queues
    Inbound message queues are used for XML messages inbound into Oracle Applications. Inbound message queues are positioned between the Transport Agent and the Oracle Workflow Business Event System.

The messages must be formatted according to the XML Gateway envelope message format. The envelope message format is discussed under XML Gateway Envelope. Oracle Workflow Business Event System copies the inbound messages to the proper inbound Transaction Queue.

  • Outbound Queues
    Outbound message queues are used for XML messages outbound from Oracle Applications. The outbound Message Queue is positioned between the XML Gateway and the Transport Agent.

The XML Gateway creates XML messages, then enqueues them on this queue. The Transport Agent dequeues the message and delivers it to the Trading Partner

Web Services
Oracle XML Gateway uses Web Services Description Language (WSDL) to inform trading partners how to communicate with the Oracle E-Business Suite. The Suite also publishes the WSDL to a URL for customers to access. Partners can use any third party
Web service tools to call for Web services.

dgreybarrow Integration Flow

The following diagram illustrates the process flow of XML messages through the XML Gateway Execution Engine using all the components mentioned above.

Inbound Integration Flow

  1. The XML Gateway Inbound listeners are actively polling for interested events. The Execution Engine will be triggered once Oracle Workflow Business Event System detects that an inbound message has arrived on the Inbound Queue.
  2. It then Dequeue Message from Inbound Queue
  3. Uses the XML Parser to validate the inbound message to determine if it is well-formed and valid
  4. If the inbound message is both well-formed and valid, the Execution Engine proceeds to validate that the Trading Partner and document are defined in the XML Gateway Setup
  5. It then retrieves the Message Map from Repository
  6. Execute the message Map – If the Trading Partner is valid and the message map exists in the repository, the Execution Engine maps the data in the XML message to its target data fields in Oracle e-Business Suite tables and columns identified in the message map. These are often the Application Open Interface tables.
  7. Raises an Business Event when inbound document is successfully processed, which triggers the corresponding oracle workflow to process the data within oracle e-business suite

where as for outbound Integration Flow

  1. The XML Gateway listeners are actively polling for interested events. The Execution Engine will begin processing once Oracle Workflow Business Event System detects an outbound transaction to be processed
  2. Validate Trading Partner or Hub
  3. Get Message Map from Repository
  4. Execute the Message Map – If the Trading Partner is valid and the message map exists in the repository, the Execution Engine gathers the application data from the Oracle e-Business Suite using the database views and columns identified in the message map.
  5. Create XML message using the message map and the application data
  6. Uses the XML Parser to validate the newly created message to ensure that it is well-formed and valid. Enqueue well-formed and valid message to the Outbound Queue.
  7. Oracle Transport Agent will dequeue the message from the Outbound Queue and deliver it to the trading partner

dgreybarrow XML Standards and supportablity

Many standards bodies (for example, EbXML, Rosettanet, SOAP, iFX) exist with published Document Type Definitions (DTD) each claiming to be better than the other. Some standards are strong at managing the message content while others excel at managing both the message content and its related processes.

As a provider of software to support all industries, Oracle has chosen to align with Open Application Group’s (OAG) XML standards for broad based message implementation. OAG is also the standard most widely adopted by the Oracle customer base.

There are over 150 OAG implementations involving the key functional areas.

  • Procurement
    • Add / Create / Change / Cancel / Show Requisition
    • Add / Change / Cancel / Process / Sync PO
    • Acknowledge PO
    • Sync Supplier
  • Sales
    • Sync/Show Sales Order
    • Show Shipment
    • Show/Update Delivery
  • Work in Process
    • Create/Change Production Order
    • Sync Production Order
  • Planning
    • Show/Sync Planning Schedule
    • Sync Shipping Schedule
  • Financials
    • Show Payment
    • Show Payment Error
    • Process Invoice
    • Process Payment
    • Sync Chart of Accounts
    • Sync Exchange Rate
  • Inventory
    • Sync Item
    • Sync UOM Group

Apart from above, there are an additional 30+ non-OAG implementations. Non-OAG implementations exists for two primary reasons; the required BOD is not supported by OAG or a proprietary BOD better describes the required functionality.

  • Rosettanet
    • 3A4
    • 3A6
  • IFX
    • Bank Statements
    • Students
    • Transcripts
  • US Dept of Education
    • Financial Aid (Grant/Loan)
  • PESC
    • Degree Audit
  • Proprietary
    • Lease Booking
    • Lease Quote
    • Lease Restructure
    • Loan Booking
    • Loan Restructure

dgreybarrow XML Vs eCommerce Gateway

Comparing within two, here are some differences .


XML Gateway
eCommerce Gateway
XML Messages EDI Transactions
Event based processing Batch Processing
XML Standard Compliant ASCII file format
No translator required EDI Translator required


Posted in API Integration, Integration | No Comments »

eCommerce Gateway

Posted on September 21st, 2011 by Sanjit Anand ||Email This Post Email This Post

There are are the two out-of-box integration capabilities for E2E available in Oracle Applications:

  • Oracle E-Commerce Gateway (formerly EDI Gateway)
  • Oracle XML Gateway

if you are new about EDI , refer to this post

dgreybarrow Oracle E-Commerce Gateway

eCommerce Gateway enables to expose internal business objects in a standardized format thus the trading partners integrate with the external business objects to send/receive information to/from Oracle E-Business Suite”

  • Support EDI Transactions
  • Executes Batch Processing
  • ASCII file format
  • Interface Files are EDI Standards Independent
  • EDI Translator is required
  • eCommerce Gateway interface is a Concurrent Program Packaged ‘Out of the Box’ key business transactions

A typical Transactional Flow can be best understood as in context of Ecommerce Gateway is as below:



Oracle e-Business Suite in conjunction with Oracle e-Commerce Gateway supports 23 EDI transactions. Oracle e-Business Suite generates and consumes ASCII flat files.

The ASCII flat files are sent to/received from a third party EDI translator who transforms the flat file content into the ASC X12 or EDIFACT equivalent. ASC X12 is the EDI standard for North America and Canada. EDIFACT is the EDI standard for Europe and Asia.

  • Purchasing
    • Outbound 850/ORDERS
    • Outbound 860/ORDCHG
    • Inbound 832/PRICAT
    • Inbound 843/QUOTES
    • Inbound 856/857/DESADV
    • Outbound 824/APERAK
  • Order Management
    • Inbound 850/ORDERS
    • Inbound 875/ORDERS
    • Outbound 855/ORDRSP
    • Inbound 860/ORDCHG
    • Outbound 865/ORDRSP
  • Shipping
    • Outbound 856/DESADV
  • Inventory
    • CUSDEC (No ASCX12 equivalent)
  • Payables
    • Inbound 810/INVOIC
    • Outbound 820/PAYORD-REMADV
    • Outbound 824/APERAK
  • Receivables
    • Outbound 810/INVOIC
    • Outbound 812/DEBADV-CREADV
  • Supplier Scheduling
    • Outbound 830/DELFOR
    • Outbound 862/DELJIT
  • Release Management
    • Inbound 830/DELFOR
    • Inbound 862/DELJIT
    • Inbound 866 (No EDIFACT equivalent)

Next we will have a look on XML gateway.

Posted in EDI, Integration | No Comments »

Diagnostics SLA

Posted on September 18th, 2011 by Sanjit Anand ||Email This Post Email This Post

The SLA diagnostic framework provides features to review the journal entries created by the Subledger Accounting program. It provides the input data used for each transaction, such as the following:

  • Transaction objects
  • Ledger information
  • System sources
  • Source values

In order to do a SLA Diagonostic, you have to do these steps as pre-requsite;

  • enable profile option: SLA Enable Diagnostics to Yes
  • run Create Accounting program which then also populates the diagnostic tables.
  • run Transaction Objects Diagnostics to view the diagnostic report.

Diagnostics SLA1

SLA Enable Diagnostics

  • Reset profile option: SLA Enable Diagnostics to No

Diagnostics SLA

  • once done , run the System Administrator submits the Purge Transaction Objects Diagnostics concurrent request.

Hope this post helps in troubleshooting accounting error when you have deal with SLA.

Posted in Subledger Accounting | No Comments »

The Beginner’s Guide to OGA (Oracle Grants Accounting)

Posted on September 15th, 2011 by Sanjit Anand ||Email This Post Email This Post

OGA (Oracle Grants Accounting) provides your organization with the ability to completely track grants and funded projects from inception to final reporting.

Oracle Grants Accounting provides a fully integrated system that supports multi-funded projects and the required compliance terms and conditions by award. Oracle Grants Accounting supports validation of allowable costs as well as flexible budgetary controls to ensure fiscal responsibility. Reporting is comprehensive, supporting internal and external reports and queries needed to effectively manage funded projects.

Take a note, Oracle Grants Accounting is part of the Oracle E-Business Suite, an integrated set of applications that are engineered to work together, see the section for dependency.

To effectively manage funded projects, Oracle Grants Accounting includes award templates that provide an easy method of recording the appropriate level of data and assists in maintaining consistency and standardization of award details.

Detailed award information includes award attributes like the award number, award name, funding source, installment funding, multiple references, budgetary controls, terms and conditions, and key personnel. Oracle Grants Accounting also stores information such as the sponsor contacts, reporting requirements, and compliance parameters.

dgreybarrow Oracle Grants Accounting – General Structure

OGA operates based on a structure consisting of Awards, Projects and Tasks. Depending on the type of sponsored project, the OGA award will be set up with a particular Award, Project and Task structure.

  • Award – Grant, cooperative agreement, or contract received by an organization, which is used to fund one or more projects.
  • Project – Primary unit of work that can be broken down into one or more tasks.
  • Task – Ability to show further detailed breakdown of project work.

dgreybarrow Oracle Grants Accounting – Functionality

OGA was developed for the needs of grant accounting and specifically to assist in maintaining compliance with sponsor regulations – especially federal grant regulations. To that end, OGA has the following functionality:

  • Budgetary Control – Ability to set spending limits within the award – can be set at the award,expenditure category, expenditure type or task level.
  • Encumbrance Accounting (“Commitments”) – Reservation of funds against Budget prior to actual payment.
  • Funds Checking – Compares expenditure amount, including indirect costs, to Budget Available Balance (Budget – Actuals – Commitments)
  • Costing – Compares Expenditure Item Date to Task Start & End Date, Project Start & Completion Date and Award Start & End Date.
  • Transaction Controls – Ability to include or exclude particular expenditure types from being able to be charged on a project.
  • Allowed Cost Schedules – A list of expenditure types that can be used on a sponsored project.
  • Burdening – The process of assessing indirect costs to expenditure transactions.

dgreybarrow Where this application needed

Project-centric organization, Higher Eductional Institute, Reasearch Org , State and Local Governments

dgreybarrow A note to implementer

Implementer might be finding two similar application in EBS.Oracle Projects and grant accounting , in other word they can understand the Grants Accounting module is a “wrapper” around Oracle Projects Costing and Billing modules, and can co-exist within the application suite. One can have Oracle Projects and Grants Accounting operating in the same environment with many of the same users. These are the some of the fundamental difference between two application.

Oracle Grant Accounting

dgreybarrow Integration with Other Application

  • Oracle General Ledger
  • Oracle Payables
  • Oracle Purchasing
  • Oracle Iprocurement
  • Oracle Receivables
  • Oracle Assets (is required if capital projects are used and assets are to be capitalized)
  • Oracle Workflow
  • Oracle Projects
  • iExpense
  • Labor Distribution

Hope this post you will find useful.

Posted in Oracle Application | No Comments »

Supporting References In Release 12 SLA

Posted on September 11th, 2011 by Sanjit Anand ||Email This Post Email This Post

Supporting References enables users to enter additional information for the journal entry header and lines that can be used for analytical purposes. This functionality was earlier known as Analytical Criteria. This enhancement was released in R12.0.3.

Business Benifits of Supporting references may be used as follows:

  • To provide additional business information about a subledger journal entry at the header or line level
  • To establish a subledger balance for a particular source value or combination of source values for a particular account
  • To assist with reconciliation of account balances, and
  • For financial and managerial analysis

The use of supporting references is optional, consist of these steps in order to use.

  1. Define a Supporting Reference
  2. Attach the Supporting Reference to the Journal Line Type (JLT)
  3. Validate the Applications Accounting Definitions

In R11i, additional data was interfaced to GL on the journals using Descriptive Flexfields where as R12 With this, customers can create customized sources as Supporting References and pass values into those sources from Subledgers like AP/AR/PA etc. Supporting references (SR) are associated at the Event Class Level. These SRs are stored in XLA_AE_LINE_ACS and associated using ae_header_id/ae_line_num. This helps link back to XLA_AE_LINES records.

From XLA_AE_LINES, the link to GL is using GL_SL_LINK_ID and GL_SL_LINK_TABLE

For setup of Supporting References, there are 3 options

  • Always: Supporting Reference Balances are maintained always on a specific Code Combination and it keeps on accumulating once it is defined. At the end of a Financial Period, the balances are carried forward as the opening balances for the next financial period.
  • Based on Account: Supporting Reference Balances are maintained on a specific Code Combination & it gets accumulated till the end of a financial period. The closing balances of a Financial Period are not carried forward as the opening balances of the next financial period; rather the balance is rolled back to zero (0) at the beginning of the next financial period.
  • Never: Supporting Reference Balances are not maintained for that Supporting Reference, it is only used as an additional reference attribute.
    When the Create Accounting program is run in the “Final” mode for any subledger, it spawns the program “Update Subledger Balances” internally and the balances are updated in the XLA_AC_BALANCES table.

dgreybarrow Supporting References – What Tables for Analytical

Here are the list of table. If you have plan to do some custom reporting for Analytical for Opertional reporting you can understand the data model as per figure below.


SLA Supporting Ref


dgreybarrowSupporting Refrence for Manual created Refrence

In R12, supporting references were introduced and the UI enables a user to However, the current version of manual journal creation API has not been supported to capture. Check it out with latest version of the code.

Posted in Subledger Accounting | 2 Comments »

Industry Terminology -MDM

Posted on September 11th, 2011 by Sanjit Anand ||Email This Post Email This Post

These definitions are taken from the source “The CDI-MDM Institute” for reference use for blog reader.

  • Master Data Management (MDM). Authoritative,reliable foundation for data used across many applications and constituencies with the goal to provide a single view of the truth no matter where it lies.
  • Multi-Entity MDM. An MDM solution to concurrently manage multiple, diverse master data domains (customers, accounts, products) across intra- and extra-enterprise business processes. By centralizing the most critical data to a single trusted source within a cohesive data lifecycle, multi-entity MDM provides configurable process integration across multiple data domains.
  • Customer Data Integration (CDI). Processes and technologies for recognizing a customer and their relationships at any touch-point while aggregating, managing and harmonizing accurate, up-to-date knowledge about that customer to deliver it .just in time. in an actionable form to touch-points.
  • Data Governance (DG). Formal orchestration of people, process, and technology to enable an organization to leverage data as an enterprise asset.

Posted in Oracle Application | No Comments »