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

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 »

Public API’s for FA Transactions

Posted on December 7th, 2010 by Sanjit Anand ||Email This Post Email This Post

So far Oracle FA is have all the good things except the lack on reporting.Oracle FA is now offer lot of public API’s that can be used to interfacing with third party or Oracle application other modules. Here are some of transaction’s API’s:

Read the rest of this entry »

Posted in API Integration, Oracle Asset | No Comments »

Asset Reclass Programmatically

Posted on December 4th, 2010 by Sanjit Anand ||Email This Post Email This Post

Last article you have seen some insight functionlity for Fixed asset Reclass functionality and Business need.

Majority of time whenever there is merger/acquisitions or Instance consolidation exercise reclassification plays an important role specially when you as project team not allow any new category . Mapping all assets and aligning with Parent’s company existing Category is major challenge depending volume of assets. The more challenge come to IT when you did not find right fitment for Mass reclassification native screen to cater the requirement quickly thus Reclass via API’s Programmatically is good options indeed.

dgreybarrow API availability for Reclass

You can use Reclass API that uses the FA_RECLASS_PUB.DO_RECLASS procedure

You can achieve

  • This API can also be used to automatically reclassify assets in all the tax books that are associated with the corporate book where the reclassification originated.
  • You can automatically reclassify assets to all of the reporting books when MRC is enabled and without generating any rounding issues.

Let take a quick look the API details and Usage part.

Read the rest of this entry »

Posted in API Integration, Oracle Asset | No Comments »

Understand “Integration Types”

Posted on February 25th, 2010 by Sanjit Anand ||Email This Post Email This Post

The benefits of an ERP application are limited unless it is seamlessly integrated with other information systems. Organizations face many challenges in ERP integration viz

  1. The challenges of integrating various functional ERP modules
  2. The challenge of integration with other e-business software applications
  3. The challenge of integration with legacy systems.

To fully leverage the power of integration, its imperative that you understand the various types of integration possiblity. Integrations can be broadly classified into the following categories:

  1. Synchronous Integrations: In synchronous Integration a request response mode of operation between two systems.

In short, the calling application expects a response from the provider when a method on the latter is invoked in the same thread.

2. Asynchronous Integrations: Asynchronous Integration are mostly good when enterprise applications take an indeterminate amount of time to respond to a calling application.

In this case, the initiating application sends the request and proceeds with its own actions without having to wait for the provider application to process in the same thread. The provider application picks up the request at a later stage and processes the message.

3.Batch Integrations: Batch integrations allow you to import or export large batches of data that are collected over a period of time into the system.

Typical end-of-day transactions or scheduled data synchronization routines utilize this technique.

4. Process Oriented Integrations: Process Orchestration takes application integration to the next level by using an application independent state engine to manage business processes that span multiple applications.

Process Orchestration can involve system-to-system integration, system to human task integration, or human task to human task integration.

Posted in API Integration, Integration | No Comments »

API’s availability in Oracle Service Contracts(OKS)

Posted on October 20th, 2009 by Sanjit Anand ||Email This Post Email This Post

dgreybarrow Contract Header Creation : For creating contract header API is:OKS_CONTRACTS_PUB.CREATE_CONTRACT_HEADER

dgreybarrow Line Creation : You creating lines you can this api OKS_CONTRACTS_PUB.CREATE_SERVICE_LINE

dgreybarrow Sub Line Creation : This is important when underline item is IB track able then we can create covered product sub line with IB link established. API you can use is: OKS_CONTRACTS_PUB.CREATE_COVERED_LINE

dgreybarrow UPDATING CONTRACT HEADER – You can use okc_contract_pub.update_contract_header API to Update contract Header

( p_api_version => 1.0,
p_init_msg_list => okc_api.g_true,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => okc_api.g_false,
p_chrv_tbl => l_chrv_tbl_in,
x_chrv_tbl => l_chrv_tbl_out);

dgreybarrow UPDATING CONTRACT LINE – You can use okc_contract_pub.update_contract_line API to Update contract Lines

okc_contract_pub.update_contract_line (
p_api_version => 1,
p_init_msg_list => OKC_API.G_TRUE,
p_restricted_update => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl,
x_clev_tbl => l_x_clev_tbl

dgreybarrow CASCADE DATE -You can use oks_bill_sch.cascade_dates_sll to cascase date in billing agreement(schedule). You just need to pass only the contract line ID

p_top_line_id => <OKS Contract Line ID>,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data

dgreybarrow DEFAULTING ATTRIBUTES FROM LINES TO SUBLINES : You can use below API for daulting attributes

lines_sublines_tbl => l_line_table,
x_return_status => l_return_status,
x_msg_tbl => l_msg_tbl

dgreybarrow CREATING CONTACT AT CONTRACT HEADER LEVEL :You can use okc_contract_party_pub.create_contact to create contact at header.

okc_contract_party_pub.create_contact (
p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_rec => l_ctcv_rec,
x_ctcv_rec => l_x_ctcv_rec

dgreybarrow UPDATING CONTACT AT CONTRACT HEADER LEVEL :You can use okc_contract_party_pub.update_contact to create contact at header.

okc_contract_party_pub.update_contact (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_rec => l_ctcv_rec,
x_ctcv_rec => l_x_ctcv_rec

dgreybarrow CREATING CONTACT E-MAIL ADDRESS AT CONTRACT HEADER LEVEL :You can use OKS_EXTWAR_UTIL_PUB.Contact_Point to create contact at header.

p_api_version => 1,
p_init_msg_list => ‘T’,
P_commit => ‘F’,
P_contact_point_rec => l_cpoint_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_contact_point_id => l_x_contact_point_id

dgreybarrow Sales Credit : For creating sales credit separately, we will have to use API:OKS_SALES_CREDIT_PUB.INSERT_SALES_CREDIT.

Posted in API Integration, Service Contracts | No Comments »

A Newbie’s Guide to E-Business Suite Integration (by Custom Code using API’S!”)

Posted on September 8th, 2008 by Sanjit Anand ||Email This Post Email This Post

questionHow can we Integrate OracleApps with Custom code by using public API’s? What skill is required for E-Business Suite Integration with API’s?

These are the question asked by someone who never exposed to OracleApps development environment.

This was topic I discussed with client IT department folks which includes people who recently inducted in Oracle Group from other technology area, college pass outs and other group like Middleware who are also responsible to putting some nut and bolts in Oracle.Thoughts to convert this in post for newbie and Inhouse IT folks who always struggling for right information for the API Integration.So here to go:

dgreybarrow-2Important Points for those working first time with API’s

  • First and foremost Important, you need to understand API Definition, which includes purpose, input parameter, output parameter, in/out parameter as well as default values for parameter
  • Next is mapping : This is where you map input data from source/staging/temp table to input parameter
  • Next is development of code , you should use cursor/temporary table to load data from user’s table and use Debugging information to detect and diagnose errors.

Read the rest of this entry »

Posted in API Integration | 5 Comments »

API’s or Open Interface

Posted on February 12th, 2008 by Sanjit Anand ||Email This Post Email This Post

I was recently asked by a reader if I would be willing answer his questions, which was :

“Most of us are / have had worked with API’s & Open Interfaces. Both do the similar job, but have we ever wondered why would we use API’s when it can be done using Open Interface or vice versa? And, which is better” ?

….So, here you go.

..My dear friend, there is hardly any difference the way both is working,do check my previous post. Anyway here is back to basic :

OIT APIWhat are Open Interfaces?
The term Open Interfaces actually refers a programming interface, usually a database table, that automates the execution of Oracle APIs.

Open Interfaces provide a single, simple interface for a specified business procedure.

What are the Oracle APIs?

These are called as a collection of “Black Box” interfaces that provide programatic access into the Oracle ERP database.

The term API refers to stored procedure driven interfaces, where you call a stored procedure to perform an action within an Oracle Module, and the data from your external application is passed through the stored procedure’s parameters.

Why use Open Interfaces?

  • In EBS one Open Interface may run many API calls.
  • Open Interface run asynchronously.
  • The good is that if there is failure of record, they remain in the table until either fixed or purged.
  • They automate the interface into the APIs.
  • This requires less work and less code as few SQL DML would simply .

Why use APIs?

  • When there is no corresponding Open Interface.
  • Normally all Oracle APIs run synchronously, and provide immediate responses, therefore machism to be provided to handle such situation.
  • That requires custom error handling routine.
  • This may requires lot more effort as these need fine grain control approach.

Remember, the APIs are also used by the front end screens, and in the same way, will require all the appropriate prerequisites to be implemented.

Important to note, you cannot use APIs as an alternative to implementation.

Releated Post:

Posted in API Integration | 10 Comments »

The world of Oracle API

Posted on February 12th, 2008 by Sanjit Anand ||Email This Post Email This Post

There are 3 types of APIs exist in EBS.

double-arrowPrivate APIs : Private API’s are one which Oracle normally using internal, development purpose only. Details are not provided to anyone outside of the immediate development environment, nor are they intended for use by anyone outside of the e-Business Suite development environment.

double-arrowPublic APIs : These are designed for customers and Oracle consultants to integrate non-Oracle systems into Oracle e-Business Suite or to extend the functionality of the base products. Oracle does not support public APIs unless they are published in a reference manual.

double-arrowPublic, published APIs : These are one which Oracle guaranteed to remain valid from release to release, and patches will not alter the API behaviour. Public, published APIs are supported by Oracle to the same extent as released software.

Is there any way find out whether a standard API is PUBLIC or not in Oracle Application?

Yes, there is way, what you have do ,once you are able to find the information for API from irep, the next you have to find the file name and then you need to pull all information from specification header to know which one is public.

Take a simple case, you need to find API FND_USER_PKG which is defined in file AFSCUSRB.pls

logon to Unix box, and release this sort of command

grep -i public $FND_TOP/patch/115/sql/AFSCUSRB.pls

api's world

Based on the above result one can determine whether API is PUBLIC or not.

Simple example for checking AR Public APIs for finding the status

grep -i public $AR_TOP/patch/115/sql/ARXPRELB.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRELS.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRECS.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRECB.pls

Important to Note:

For non-published APIs, Oracle expressly does not provide any guarantees regarding consistency of naming, usage, or behaviour of any API (public or private) between releases.

It Might be possible that a patch could alter any characteristic of any non-published e-Business Suite API.

Where are APIs located ?

For Oracle release 10.7, the APIs are located in the operating system directories such as:


For Oracle release 11 and release 11i, the APIs are located in the operating system directories:


where xxx represents the release 110 or 115.

Is there any tracking mechanism for API versions in different Applications releases?

As confirmed by some time back by Oracle support team , there is no such database object in Oracle Applications that keep such kind of information.

All APIs are owned and managed by different product groups within Oracle.

Normally each release comes with either product update notes, or and “About” note. You would need to review these documents for each E-Business Product.

The most comprehensive are the family pack “About” notes, as they in turn reference each individual product “About” note, which lists things like “Changes”.

Posted in API Integration, EBS Suite | 14 Comments »

Oracle API Availability -Oracle Assets (FA)

Posted on October 21st, 2007 by Sanjit Anand ||Email This Post Email This Post

Importing of Asset information into the Oracle Assets module is achieved by the transfer of the following five segments of data:

  1. Adjusted current earnings (ACE) information
  2. Budget data
  3. Mass additions of Assets
  4. Production Information which is typically related to depreciation
  5. Physical Inventory data.

Each of the procedures are described in detail below.

redarrow-1Budget Data

The budget information can be entered manually, or it can be maintained in another system and the information uploaded using the budget interface. The budget information is prepared and analyzed on any feeder system and then automatically transferred into Oracle Assets. This information can be used to project depreciation expense for the capital budgets and to compare actual and planned capital spending in Oracle Assets.

  • Manual Loading of the Budget Data

Manual loading of Budget Data is achieved by the following five step process: Open the Capital Budgets window. Choose the budget Book, asset Category, and general ledger Expense Account for which you want to budget. Enter or update the budget amounts for this period. The budget amount is the amount you plan to spend on new assets in this category in this period for this expense account. Save your work. Review the capital budget for the year using the Budget Report

  • Automatic Loading of the Budget Data:

Transfer of Budget information is achieved by transferring the data using a file transfer method. The Budget information from various systems is first copied into an ASCII file which is transferred into the Budget interface file FA_BUDGET_INTERFACE. This file is used to load the Budget data into the Oracle Assets system.

Uploading budgets from other systems (such as a spreadsheet on a personal computer) into Oracle Assets is a five step process. A file transfer program is used to upload the ASCII budget file from any personal computer to the computer where Oracle Assets resides. SQL*Loader is then used to move the budget data into the Budget Interface. The Upload Capital Budget window is used to move the budget into the Budget Worksheet. ‘Delete Existing Budget’ needs to be checked if replacing an existing budget. The Capital Budgets window can be used to review or change the budget. The Capital Budget window is then used to move the budget into a budget book.

redarrow-1ACE Information

Oracle Assets looks at the asset’s financial information in either the Alternate Minimum Tax(AMT) or the federal book to determine new ACE information. If an asset is depreciating in the federal tax book using ACRS, Oracle Assets uses the federal book information. If the asset is depreciating in the federal book using MACRS, Oracle Assets uses the AMT book information. Oracle Assets automatically updates your assets when you run the Update ACE Book program.

For transferring the ACE information from the legacy system, it can either be entered manually, or it can be calculated by the Oracle Assets program. The difference is the process of populating the ACE interface table.

  • Automatic population of the ACE conversion table

Manually, create an ACE tax book with ACE depreciation rules. Using the Mass Copy program, the assets are copied into the ACE book from the corporate book. The ACE conversion table (FA_ACE_BOOKS) is populated using the Populate ACE Interface Table program. The Update ACE Book program is then run to update the information in the ACE conversion table.

  • Manually Loading the ACE conversion table

Manually, create an ACE tax book with ACE depreciation rules. Using the Mass Copy program , the assets are copied into the ACE book from the corporate book. The ACE conversion table is populated by loading the table manually with the ACE information from the previous system. The Update ACE Book program is then run to update the information in the ACE conversion table.

redarrow-1Mass Addition of Assets

The mass additions process lets the addition of new assets or cost adjustments from other systems to your system automatically without reentering the data. For example, new assets can be added from invoice lines brought over to Oracle Assets from Oracle Payables, or from CIP asset lines sent from Oracle Projects. Oracle Assets is already integrated with Oracle Payables; and it can easily be integrated with other payables systems. The mass additions process can also be used to convert assets from an outside system to Oracle Assets. Assets data can be transferred in one of the following three methods, depending on where the source is located.

  • Creating Assets From Oracle Payables

The Create Mass Additions program creates mass additions from invoice information in Oracle Payables. The concurrent process places the new mass additions in a holding area (the table FA_MASS_ADDITIONS) interface tables, so that the mass additions are reviewed and approved, before they become asset additions.

  • Creating Asset Additions From Another Payables System

To integrate Oracle Assets with another system, a program is created to add mass additions to the FA_MASS_ADDITIONS table. This new Custom Concurrent process has to be defined and added to the Oracle Assets menu in order to run it, when needed.

  • Converting Assets From Other Systems

Oracle Assets allows the conversion of assets data from non-Oracle asset systems by using mass additions. Instead of loading the asset information into multiple Oracle Assets tables, the information is loaded into the FA_MASS_ADDITIONS table and the mass additions process is used to simplify the work.

redarrow-1Production Information

Typically, production information relates to the depreciation of assets in a production environment, where assets are depreciated depending on the number of units produced by the asset.
The production information can be entered manually, or it can be maintained in another system and the information uploaded into the Oracle Assets, using the production interface. Oracle Assets uses the information to calculate the depreciation of the units of production assets.

  • Automatic Loading of the Production Information

The automatic loading is achieved in the following 4-step process:

  1. An import program or utility is used to export data from the feeder system to populate the FA_PRODUCTION_INTERFACE table.
  2. The Upload Production program is run to move the production information into Oracle Assets.
  3. The Production History report is run to review the status of all imported items.
  4. The Periodic Production window can be used to review or change the production information.
  • Manual Loading of the Production Information

The manual entry involves the following tasks: Open the Periodic Production window; Find assets within a corporate Book for which you the production information is being entered; Enter the from and to date and the total Production for an asset; Optionally enter production for multiple non-overlapping date ranges within a single period; Save your work.

redarrow-1Physical Inventory

Physical inventory is the process of ensuring that the assets a company has listed in its production system match the assets it actually has in inventory. A company takes physical inventory by manually looking at all assets to ensure they exist as recorded, are in the appropriate locations, and consist of the recorded number of units. The Physical Inventory feature in Oracle Assets assists in comparing and reconciling your physical inventory data. The physical inventory data can be loaded into Oracle Assets using one of the following methods:

  • Importing data from an Excel spreadsheet using ADI

The physical inventory data is entered into an Excel spreadsheet and exported to Oracle Assets using the Applications Desktop Integrator (ADI). ADI is a spreadsheet-based application that allows the user to format data inside a Microsoft Excel spreadsheet and then upload into Oracle Applications. ADI uses Wizards and Templates to simplify the data entry.

  • Entering data in the Physical Inventory Entries window

Data for each asset is entered directly into Oracle Assets using the Physical Inventory Entries window. The limiting factor is that data for only one asset can be entered at a time when using this method

  • Importing data from a non-Oracle system using SQL*Loader

SQL Loader is used to import the Physical inventory data in the following way: A single interim table it be used, if possible. Multiple tables may be used if the data exists in multiple tables or files in the system from which data is being loaded. In either case, the data must eventually be placed in a single table, the FA_INV_INTERFACE table. If preferred, data can be loaded directly into the FA_INV_INTERFACE table, but it is more difficult due to the complexity of the table SQL*Loader is used to import information from outside the Oracle database. SQL*Loader accepts a number of input file formats and loads the physical inventory data into the interim table. If the data already resides within an Oracle database, there is no need to use SQL*Loader. The physical inventory information is consolidated in the interim table using SQL*Plus or imported by any other method.

Posted in API Integration, Finance, Oracle Asset | 2 Comments »

Oracle API Table Definitions Fixed Assets

Posted on October 20th, 2007 by Sanjit Anand ||Email This Post Email This Post

Here is reference note for API table definitions based out of my for previous article for Oracle API Availability -Oracle Assets (FA).


FA_ACE_BOOKS, the ACE conversion table, is organized into the following columns which store ACE information:
























FA_BUDGET_INTERFACE, the budget interface table, is organized into columns in which Oracle Assets stores budget information.



To use the Physical Inventory feature in Oracle Assets, you must load physical inventory data that you have collected into the FA_INV_INTERFACE table in Oracle Assets.




































FA_PRODUCTION_INTERFACE, the production interface table, is organized into columns in which Oracle Assets stores production information. Enter values for the following required columns

Name Null? Type

Posted in API Integration, Oracle Asset, Technical | 2 Comments »

« Previous Entries