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 General Ledger: Alternate Account Functionality

Posted on November 14th, 2012 by Sanjit Anand ||Email This Post Email This Post

It is a regular practice amongst users to disable code combinations where they donot require any futher activity to take place.

In 11i if there was a case where users were importing the existing data with one such code combination, the import program would error flagging invalid code combination. This would hold up the entire group of transactions from getting imported.

Release 12 provides a solution to such an issue. Oracle introduced new functionality in R12 called ‘Alternate Account’. This functionality is very useful when you have a need to disable and/or end date an account combination within your chart of accounts or you have a significant requirement to change mass chart of account values.

When an account is disabled, users can prevent transactions that include the account from erroring during journal import by defining a replacement account for the disabled account. Journal import replaces the disabled account with the replacement account and continues the import process if the replacement account is valid.

This improves processing efficiency by preventing the journal import process from erroring and enabling the successful creation of the journal when an account has been disabled.

To setup the alternative accounts, use a GL Super User responsibility:

Setup –> Accounts –> Combinations

Query the account combination, Tab over to Alternate Account and enter the account.

Note -> Balancing segment value in the alternate account must be the same as what is in the original account. Otherwise, the system will not allow it and will throw an error.
“The balancing segment value for alternate account must the same as the original account”

Posted in Oracle General Ledger | No Comments »

Difference between the realized gains and losses accounts and the cross currency rounding account

Posted on July 16th, 2012 by Sanjit Anand ||Email This Post Email This Post

The realized gains and realized losses accounts are used to account for the conversion rate gain or loss in the ledger currency resulting from a cross currency receipt application. For example, if the conversion rate for a foreign currency invoice is 1.7 and the conversion rate of the payment for this invoice is 2.0, Oracle EBS/Fusion posts the difference as a gain to the realized gains account.

The cross currency rounding account is used to record rounding error amounts created during a cross currency receipt application for currencies that have a fixed rate relationship. You must define a rounding error account if you create cross currency receipts.

Posted in Oracle General Ledger | No Comments »

Oracle Desktop Integration Framework

Posted on June 5th, 2011 by Sanjit Anand ||Email This Post Email This Post

Oracle E-Business Suite Desktop Integration Framework or DIF in short bring you the design time framework to develop desktop integration solutions for Oracle E-Business Suite applications.This is important enhancement for Oracle 12.1.2.

If we look at high level architecture of Desktop Integration Framework, we see that it’s a design time framework, that lets you define integrator, interface, content, components and business rules for EBS applications across product families. And it leverages, Oracle Web ADI’s integrator services and metadata repository for its execution.
Architecture Desktop Integration Framework

On a very high level, you can say Desktop Integration Framework features are:

  • A wizard based UI used tocreate and manage desktop integrators.
  • It has ability to define integrator that can upload data through PL/SQL API or directly to database table.
  • It has ability to define integrator that can download data from text file or based on a SQL query.
  • It has ability to define data validation rules that ensure data integrity
  • It has ability to define UI components, so that you can embed EBS UI widgets such as LOVs, pop-lists, date pickers and flex fields in Excel.
  • You can leverage EBS security model – SSO integration compliant solution.
  • It has ability to consume integrator definition and create document within business flow.
  • It has ability to define layouts, mappings for custom integrators.

dgreybarrow What are the key components

Important one is Integrator

Integrator is metadata that encapsulates all information required to move data to and from desktop application into Oracle E-Business Suite. An Integrator definition may include information about open interface tables, validation, business rules, and data that can be downloaded.

In order to enable Developers to create and manage integrators, there is new responsibility called Desktop Integration Manager. So, user (say an Integration Developer) can create, update and delete custom integrators from EBS UI.

From Manage Integrators screen, you can search and view list of integrators. You can select an integrator and define layout and mappings for it.

Lets look at the screenshots below:

DIF menu

So, here we have new responsibility – Desktop Integration Manager

It has 4 functions – Manage Integrators, Create Integrator, Manage Components and Define Parameter.

When you clicks on Manage Integrator, Integrators page is displayed.


Now you can search integrator by name, internal name or code, application to which it belongs to, on its status – whether it is enabled or not. on UI Source is a column that indicates whether its Oracle seeded or custom created integrator.

As you can see in the above screenshot, search results for integrators belonging to Application Object Library is displayed. The Update and Delete icons for Custom integrator is enabled, whereas its disabled for Source ‘Oracle’ integrators.

Now, next question you have how user can create integrator.

Its very simple. As mention earlier, Desktop Integration Framework provides wizard based UI to create integrator.

you can access this wizard either by clicking ‘Create Integrator’ function from Desktop Integration Manager responsibility or by clicking Create Integrator button in Integrators list page.

Integrator 1

The first step in the wizard is to define integrator related properties – such as its name, internal name, to which application the integrator belongs to, whether its used for reporting only, its status – whether it is enabled or not. Then, there is a check box to indicate whether Integrator should be listed in Create Document page flow of Desktop Integration responsibility. Its very simple.

If this option is left unchecked, the integrator will not be available from Create Document function in Desktop Integration responsibility. However, the user can integrate the generate spreadsheet functionality within application functional page flow. Web ADI is used in the background to generate the spreadsheet or desktop document.

Then, you can select and expand Integrator Parameters section, which holds a list of parameters.

Integrator 2

Then, you can define function based security rules. You have LOV to help user search and select a function. Also, please note, Integrators with Enabled status as Yes are available for end-user in their business flow.

Now lets understand , the Integrator Parameters(look at the expanded view in above figure)..typically these are set of parameters that are defined by Integration Developer depending upon business requirement. These parameters are used by Integrator and Web ADI at run-time.

At the run-time, when end-user is done with data-entry and wants to upload the data to Oracle E-Business Suite. End-user clicks on Upload sub-menu under Oracle menu in Excel, then Upload Page is displayed. It has various options like which rows to upload (All or flagged), whether to validate data while uploading, whether to initiate import program to move uploaded data from interface tables to base tables.

  • Importer Parameters are used to define rules for importing data.
  • Upload & Server Parameters are used to define Upload Page that is displayed to end-user, and for back-end upload service.
  • Document Parameters are set of parameters required for creating document. It could be used to prompt end-user at run-time or programmatically pass them at run-time.
  • Session Parameters are used to define values that are to be stored with the document for the lifecycle of document, that is, for document’s session

Next Security Rules:

As mentioned earlier , user can define function based security rules for an integrator. It ensures that end-users who have access to assigned function can only, create document of integrator.

dgreybarrow Process Flow of Desktop Integration Framework:

In order to define custom desktop integrators the process remain same as earlier in WebADI. the steps are :

  1. Define Integrator properties,
  2. Create Interface
  3. Create Content
  4. Define Layout
  5. Define Mappings
  6. Create Document

Next few slides you can see the navigation steps with screen snapshots:


Integrator 11


If required, Create Content for integrator. There may be scenarios wherein you may want to define integrator for only data entry purpose that is only for uploading data to E-Business Suite, then you may not need to create content.

Next is Interface


As fourth step, create layout for the integrator. Then create mapping for the integrator. Finally, create document using the integrator definition.


next is query




Posted in Web ADI | No Comments »

Its all about GL Drilldown

Posted on May 27th, 2011 by Sanjit Anand ||Email This Post Email This Post

dgreybarrow What is GL Drilldown

GL Drilldown feature allows users to view sub ledger transaction information for the GL journal line created by importing information from the sub ledger products. Users are able to view sub ledger transaction information for different Products using the GL Drilldown feature by navigating to the Product UI that is the source of such transaction.

The Drill Down feature is invoked by Drill Down button on Journal Review screen. The Drill Down button opens XLA Drill Down UI. The UI shows product transaction and accounting information related to journal line.

This post is another extension of my previous post.

If you are starting Drilldown from General Ledger that will takes you SLA Journal details. Typically the flow is as:

  1. Drilldown is performed from Journal lines in General Ledger, that means data is stored in the table GL_JE_LINES. The link between SLA data and data in GL is through the table GL_IMPORT_REFERENCES.
  2. This data is populated in GL_IMPORT_REFERENCES only if “Import References”¨ option is selected in the Journal source definition.
  3. Once Populated, the data can be mapped from GL_JE_LINES to GL_IMPORT_REFERENCES table using the columns je_header_id and je_lines_num.
  4. From subledger side , SLA Journal line data is stored in the table XLA_AE_LINES, which is connected with its header which stored in XLA_AE_HEADERS.
  5. Then data in GL_IMPORT_REFERENCES can be mapped to XLA_AE_LINES using the columns gl_sl_link_id and gl_sl_link_table.
  6. When Transfer happen, typically it can be done either Summary or Detail mode, which typically configure when you define the Event Class, which is attached to the Journal Line Type.
    • Take a note, if the transfer is done in Summary mode, then the Reference columns will not be populated in any of these table GL_IMPORT_REFERENCES or GL_JE_LINES
  7. Then Journal Line Type is then attached to the Journal Line Definition, which internally hookup with Subledger Accounting Method.
  8. Data from SLA, which comes to GL_INTERFACE, can then be imported in either Summary or Detail Mode.
  9. The option for summary or details is configure in the Accounting Setup Manager
  10. If you are importing data in Summary mode, then the Reference columns are populated in GL_IMPORT_REFERENCES (provided data has come in Detail mode from SLA), while GL_JE_LINES will not have the Reference columns populated.
  11. On the other hand if data is imported in Detail mode, both GL_IMPORT_REFERENCES and GL_JE_LINES tables will have the Reference columns populated (again provided the data has come in Detail mode from SLA).
  12. Table XLA_AE_LINES Column gl_transfer_mode_code gives the information for the transfer mode from SLA to GL.
  13. For the Journal Import mode, the values for specific applications is stored in the table XLA_LEDGER_OPTIONS which stores the setup for various applications for the Ledgers defined. The column indicating the Journal Import mode is transfer_to_gl_mode_code, which have three values as P (Summarized by Period), A(Summarized by Accounting Date) or D(No Summarization i.e. Detail mode)
  14. The XLA_TRIAL_BALANCES table is populated after successful journal import.
  15. You can use the different SLA reports for pulling SLA data.
  16. Last but not the least, drilldown will work from GL to all the Subledgers that are using the SLA Engine

Posted in Oracle General Ledger, Subledger Accounting | No Comments »

Understanding “GL Program Optimizer”

Posted on February 19th, 2011 by Sanjit Anand ||Email This Post Email This Post

It is one of the performance improvement tools(mimics a cost-based optimizer.) in Oracle EBusiness within GL product ,that is available to the GL super user responsibility. It has two major functions:

  • Collect statistics to aid in the running of FSGs and GL posting
  • Build indexes on gl_code_combinations where they do not exist.

When you run this program, it collects and stores information on the spread of balances in gl.gl_balances which is used by both the GL posting program and FSGs. Given that the number of balances in gl.gl_balances increases each month with the month end roll process, it is important for the overall performance of posting and FSGs to keep the GL program optimizer stats up to date.

dgreybarrow Running the Program
This program is run by logging onto the GL super user responsibility and then running the concurrent program – GL Program Optimizer.

When running the GL Program Optimizer there are two options:

  • Collect Statistics (Default Yes) -Set this value to Yes
  • Rebuild Indexes (Default No) – Set this value to No

The GL Optimizer, when run with Gather statistics checked, will populate the gl table GL_SEGMENT_FREQUENCIES with current data in regard to your GL_CODE_COMBINATIONS and GL_BALANCES tables. This data is used in some gl programs to maximize performance

dgreybarrow How often often GL Program Optimizer be run?

You should run the GL optimizer at least once a period or after creating a large number of segment values, defining a new chart of account.It better set this up as a scheduled job to be run once per month, a couple of days after the month end roll over.

The GL optimizer is designed primarily to make FSGs run faster. Its recommend that for validation of flexfields, you also create a non unique concatenated index on the account segment, followed by all the other defined segments in flexfield order.

Anyone have more experience with this program , share your input:)

Posted in Oracle General Ledger | 1 Comment »

Checks to see that all SOBs have successfully transferred to consol.

Posted on January 27th, 2011 by Sanjit Anand ||Email This Post Email This Post

If your company is doing Consol and you have to see results across all SOB’s for timely investigation and resolution during close, use this query .(

select from_sob, to_sob,,
gch.status, batch_name
from apps.gl_consolidation gc,
apps.gl_consolidation_history gch,
apps.gl_sets_of_books sob,
apps.gl_sets_of_books sob1,
apps.gl_je_batches gjb
where gc.consolidation_id = gch.consolidation_id (+)
and gc.from_set_of_books_id = sob.set_of_books_id
and gc.to_set_of_books_id = sob1.set_of_books_id
and gch.je_batch_id = gjb.je_batch_id (+)

Posted in Oracle General Ledger | No Comments »

Financials Accounting Hub

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

Financials Accounting Hub (FAH) provides an orderly, streamlined process to manage Financial Data of Source Systems.

It houses the transformations from business event to accounting generation and then transfers balanced entries to GL.

FAH allows you to create detailed, auditable, reconcilable accounting from a variety of source systems.

It includes an accounting transformation engine with validations plus accounting and rules repositories. The transformation engine consistently enforces accounting policies. The repositories provide centralized control, detailed audit trails, and simultaneously accommodates diverse corporate, management and reporting requirements.

FAH – What’s not for

  • Data Extraction
  • Event Creation
  • Calculation Engine for Source System calculations
  • Inbuilt Intelligence for IFRS Accounting or any GAAP Accounting

Is FAH a fit for you?

FAH may be right for you if:

  • Oracle EBS and/or PeopleSoft are not a good fit to replace your proprietary operational (ordering, billing, inventory, purchasing) systems;
  • Detailed financial reporting is required on legacy system attributes not typically a part of the Chart of Accounts;
  • Accounting requirements for legacy systems are complex and subject to change; and/or
  • You are in the financial services (insurance, banking, brokerage), telecommunications, or distribution industry.

Check out the Oracle data sheet for more information.

Posted in Oracle General Ledger | No Comments »

GL Future Period Opend accidentally

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

Here is one the query from User.

In our GL module, in Dec 2010, some how some one has opened the Jan-11 period (either by accidentally or by mistake), and then immediately they have closed the period.So now our requirement is , the Jan-11 period should be Future Entry status. They donot want to open this period untill all thier activites for Dec 2010 completes.

And from the form , we donot have the option to change the Jan-11 period to Future Entry. Could you please help us how we can change the Mar-11 period to Future entry? Any data fix?

So answer would be:

There is no way within the application to set the period status back to Future Enterable after the period has been opened. You can close the period as work around so it prevent users from posting entries to this period. Hope this helps.

Posted in Oracle General Ledger | No Comments »

AssureNET GL : Thats a good Product for “Account Reconciliation”

Posted on August 13th, 2010 by Sanjit Anand ||Email This Post Email This Post

Have you heard about the product called “ASSURENET” , this is one of good product for reconcilation activity getting attention now a days by Accountants , Auditors ,controllers because the good capability of managing Finance accounts reconciliation and Audits trials feature. No matter its Balance sheet account or bank account, this product have great functionality which will add lot of value to business.

What is AssureNET

AssureNET GL is a web-based process management system for the general ledger account reconciliation & certification function. It is designed to strengthen the account reconciliation internal control while making the process much more efficient and SOX compliance.

This Product normally comes with three major functional modules like:

  1. Complete Assigned Tasks & Activities Module
  2. Prepare or Monitor Quality Assurance Reviews Module
  3. Manage or Monitor Task & Activities Module

Using this software you can get the advantage of following benefits:

  • This will Simplified the account reconciliation process , as most of the ERP does not have enough capability keeping the auditors in mind.
  • This will reduced the amount of time necessary for Internal Audit’s testing of account reconciliations.
  • This enhanced the visibility into the performance of the control across the entire enterprise which may include detailed analysis by department, location, products or region.
  • Automated email notifications will help to inform participants of impending reconciliation and review dates or missed deadlines
  • Automated summaries of exception statistics and extensive management reporting
  • Last but not the least complete segregation of duties between the reconciliation, review, approve and certification functions

Application Technology

Typically this application is developed using

  • .NET Framework
  • ASP.NET , AJAX Extensions
  • And SQL Server as database.

This comes with hosted solutions via vendors On-Demand business unit as well as you manage and own this this product at your site.

Check out this product at vendor site, if your department is ready to spend few thousand of dollar.

Posted in Oracle General Ledger, Oracle Product | 1 Comment »

Journal approval – Handling with Temp/Contract staff [ Customization Option ]

Posted on November 9th, 2009 by Sanjit Anand ||Email This Post Email This Post

Do you know Oracle GL restricts Journal approval process to HR type “Employees” and this approval hierarchy is restrictive….

Business Senarios

Your Company GL team may hires accounting clerks as “temp staff” or “Contract staff”, and if it is part of your business process to test workers in these roles before committing to hiring them full time. A main function of the clerk’s role is to create and submit Journal Entries to a Manager for approval. This should be a simple process, but if such thing happen your DBA team must manually change each batch to route correctly to the manager.

Where this limitation came from
When the contract worker is submits a journal batch into the work flow, there is an error at the GL Approver Process stage due to no approver being linked
to the contract workers user.

The reason there isn’t an approver showing up under the contract worker is due to the table Oracle pulls employees from GL_HR_Employees_current_V

This table is set to “e” = employees So, it only sees users set up as employees. The contract (or contingent) workers are set to “c”, thus Oracle doesn’t see them (or an approver tied to them) in the workflow.

If you would like for users set up in Oracle as a “Contract” or “Contingent” worker to be able to create and send Journal Entries/Batches to an “Employee” (an employee who is a manager) for approval, then either your HR should create a employee record in HR system(with Flagging] or you have to do a minor Customization in order to achieve.

Posted in Oracle General Ledger | No Comments »

« Previous Entries