Oracle R12 – New Features in Oracle Financials

I keep coming again and again to understand these new features and how to apply with new implementation clients. Though they look straight forward , but it is difficult to unlearn old 11i concept.

here putting again so you can again think why we should have a fresh thought process for R12 Implementation Projects and using these feature as starting point.

Oracle Data Migration : SQL Loader or Oracle external tables or Oracle Data Integrator

Data migration is the first step when moving your mission critical data to an Oracle database. The initial data loading is traditionally done using Oracle SQL Loader. As data volumes have increased and data quality has become an issue, Oracle Data Warehouse and Oracle Data Integrator have become more important, because of their capabilities to connect directly to source data stores, provide data cleansing and profiling support, and graphical drag and drop development.

Here i touching SQL Loader, External Table concept and ODI ( Oracle Data Integrator )  in this context. Not covering OWB -Oracle Warehouse builder as my knowledge is limited in that.

SQL LOADER – SQL Loader is the primary method for quickly populating Oracle tables with data from external files. SQL Loader is typically used in ‘fat file’ mode. This means the data is exported into a command-delimited fat file from the source database or arrives in an ASCII fat file. With the growth of data volumes, using SQL Loader with named pipes has become common practice.

External Table : The external tables feature is a complement to the existing SQL Loader functionality. It enables you to access data in external sources as if it were in a table in the database. Therefore, standard SQL or Oracle PL/SQL can be used to load the external file (defined as an external table) into an Oracle database table.

if you know SQL well, then it is easier to code the external table load SQL than SQL Loader control files and load scripts.

Oracle Data Integrator (ODI) –Oracle Data Integrator (ODI) is a product that Oracle acquired from Sunopsis SA in 2006.

ODI is a data migration and integration software product, providing a declarative design approach to defining data transformation and integration processes, resulting in faster and simpler development and maintenance. Based on an Extract-Load- Transform (E-L-T) architecture, Oracle Data Integrator, unlike traditional Extract- Transform-Load (E-T-L) products, loads the data into the target database and then does the transformation processing.

By combining data, event-based, and service-based integration, ODI is able to address varying needs from legacy data migration, data warehousing, and business intelligence to Master Data Management, Service Oriented Architecture, and others (such as cloud computing).

The most likely instances or use cases when ODI would be the Oracle product or tool selected are:

  • Bulk loading data on a continuous, daily, monthly, or yearly basis.
  • Direct connection to ODBC and JDBC compliant databases for data modernization, migration, consolidation, and physical federation (data mart, data hub, and data warehouses).
  • Web services-based data migration.
  • Data migration or Change Data Capture (CDC) that offers a graphical interface, scheduled data movement, data quality and cleansing, and data profiling.

Now a day, more and more projects are using ODI and moving away from SQLLOADER, do reach me out for any questions around ODI.

Oracle AP : Accounts Payable Trial Balance ( New trial Balance design using subledger accounting – Open Account AP Balance Listing )

In release 12:  11i Trial Balance is known as the Accounts Payable Trial Balance Report report in R12. It runs based on the Open Account Balances Listing Definition that you define. You can define the Listing Definition at a Ledger or Ledger Set level

A New concept “Open Account Balances Listing Definition” , what is it ?  –> The Open Account Balances Listing identifies General Ledger accounts with outstanding balances and displays the subledger transactions that contribute to that balance

Define the report definitions either by Accounting Flexfield or by Accounting Flexfield segments. Associate each definition with a single ledger, which indicates the chart of accounts. The chart of accounts sets the context for the following information in the definition details region:

  • General Ledger accounts
  • General Ledger account segments
  • General Ledger account segment values

Define as many definitions as necessary. If defining a report definition by segment, indicate a single segment value, a range of segment values, or a combination of individual values and ranges for different Accounting Flexfield segments. If defining a report by Accounting Flexfield, include one or more entire General Ledger accounts.

The Open Account Balances Data Manager maintains reportable information for all enabled open account balance listing definitions. This program is submitted automatically after a successful transfer to General Ledger for the same ledger or manually by running the Open Account Balances Data Manager program. When changes are applied to a Open Account Balances Listing Definition, the Open Account Balances Data Manager program is automatically submitted for the changed definition.

Open Account Balances Data Manager   :- This program processes subledger journal entries that are transferred to General Ledger and also final accounted journal entries that are flagged as No Transfer because these entries are already represented in General Ledger.

Use the Accounts Payable Trial Balance Report to verify that total accounts payable liabilities in Payables equal those in the general ledger. To reconcile these balances you can compare the cumulative total liability provided by this report with the total liability provided by your general ledger.

The Accounts Payable Trial Balance report is a Payables-specific version of the Open Account Balances Listing report. By running this report from Payables, you can run this report for a specific operating unit.

Latest R12 Accounts Payable Trial Balance (post-patch 9162536) has 2 templates available that control the output that is displayed. The two templates available are:

Accounts Payable Trial Balance – Group by Account
Accounts Payable Trial Balance – Group by Third Party

Oracle AP – Oracle Project Integration : PRC: Interface Supplier Costs : Interfacing Supplier Invoice From Oracle Payables To Oracle Projects:

How to integrate Oracle AP Supplier Invoices to get Charge to PROJECTs:

Interfacing Supplier Invoice From Oracle Payables To Oracle Projects:

This process involves :

  1. Creating the invoice.
  2. Approve the invoice.
  3. Account for the invoice in payables.
  4. Run the ‘Interface Supplier Costs’ process in Oracle Projects which interfaces the invoices from AP to PA.

Important Project Related Fields in AP:

  • Project Name – Project Name to which the Invoice is accounted in Payables.
  • Task Number – Task Number of the Project to which the invoice is accounted. Note: If the Task is not chargeable, the system displays the following error message: APP-PA-19270 The Task is Not Chargeable. The same error will be received if the Chargeable Flag is not checked while defining Tasks in Projects Setup or if the task is a Parent Task. Expenditures cannot be created at the Parent Task level.
  • Expenditure Types – Expenditure Type of the invoice. This is based on the projects expenditure types (pa_expenditure_types table)
  • Expenditure Item Date – The date of the invoice expenditure item to be created in Projects.
  • Expenditure Organization – Active Project Expenditure/Event Organization against which the invoice has to be mapped.
    The Expenditure Org can be find in PA_ALL_Organizations table with pa_org_use_type = ‘EXPENDITURES’. For any organization to be a Expenditure Organization, it has to be classified in HR as ‘Expenditure/Event Organization’.
  • Quantity – Based on the Expenditure Type definition, quantity is verified by checking the PA_QUANTITY column in the AP_INVOICE_DISTRIBUTIONS_ALL. If the COST_RATE_FLAG column in PA_EXPENDITURE_TYPES table is set to Y, then the quantity field in the Payables Invoice Workbench needs to be filled in.

Once all the requisite information has been entered and the invoice distribution saved, the system checks whether the values given in the Project, Task, Expenditure Type and Expenditure Organization fields are active as of the Expenditure Item Date.

The following columns in the AP_INVOICE_DISTRIBUTIONS_ALL table are relevant for project-related supplier invoices:

  1. PROJECT_ACCOUNTING_CONTEXT – This column is set to Yes if the Project ID column is filled.
    1. ASSET_ADDITION_FLAG – If the project-related invoice distribution is charged to a Capital Project, then the  ASSET_ADDITION_FLAG is set to P when the PA_ADDITION_FLAG is set to Y, Z or T.

To avoid the same invoice distribution being interfaced to both Projects and Fixed Assets, you must interface any project-related invoice distribution to Oracle Projects before you interface it to Oracle Assets.
PA_ADDITION_FLAG – The PA_ADDITION_FLAG tracks the status of project-related supplier invoice distribution lines and expense report distribution lines. For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project-related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Projects.  For supplier invoice adjustment lines interfaced from Projects to Payables (which must net to zero with another line), the value for the PA_ADDITION_FLAG is set to T.  Listed below are the Quick Codes available for the PA_ADDITION_FLAG:

  • B  No open PA period
  • C  Task does not allow charges
  • D  Outside project dates
  • E  Non-project related invoice distributions
  • I  Outside task dates
  • J  Project level transaction controls violated
  • K  Task level transaction controls violated
  • M  Invalid project/task combination
  • N  New line not yet processed by Oracle Projects
  • P  Project is closed
  • Q  Transaction control extension violated
  • S  Temporary status used during processing
  • T  Adjustment line transferred from Oracle Projects
  • V  Invalid data (catch-all error)
  • X  Burdening error
  • Y  Transferred to Oracle Projects
  • Z  Net zero adjustment line. Never transferred to PA

If an item is rejected, you must correct the rejection reason and re-run the interface process.Once the Invoice is interfaced to Projects, the following tables are populated with appropriate values:


Oracle® Unified Method (OUM) 5.6 – Latest version available to download


Oracle is evolving the Oracle® Unified Method (OUM) to achieve the vision of supporting the entire Enterprise IT Lifecycle, including support for the successful implementation of every Oracle product. OUM replaces the Legacy Methods, such as AIM Advantage, AIM for Business Flows, EMM Advantage, PeopleSoft’s Compass, and Siebel’s Results Roadmap. Oracle PartnerNetwork (OPN) Diamond, Platinum, and Gold Partners are encouraged to transition to OUM.

This release features:

  • Business Process Management (BPM) Project Engineering Supplemental Guide
  • Cloud Roadmap View and Supplemental Guide
  • Enterprise Security View and Supplemental Guide
  • Service-Oriented Architecture (SOA) Governance Implementation Supplemental Guide
  • “Tailoring OUM for Your Project” White Paper
  • OUM Microsoft Project Workplan Template and User’s Guide
  • Mappings: OUM to J.D. Edwards OneMethodology, OUM Roles to Task
  • Techniques: Determining Number of Iterations, Managing an OUM Project using Scrum
  • Templates: Scrum Workplan (WM.010), Siebel CRM
  • Enhanced / Updated:
    • Manage Focus Area reorganized by Activities for all Views
    • Oracle Architecture Development Process (OADP) View updated for OADP v3.0
    • Oracle Support Services Supplemental Guide expanded to include guidance related to IT Change Management
    • Oracle User Productivity Kit Professional (UPK Pro) and Tutor Supplemental Guide expanded guidance for UPK Pro
    • Service-Oriented Architecture (SOA) Application Integration Architecture (AIA) Supplemental Guide updated for SOA Tactical Project Delivery View
    • Service-Oriented Architecture (SOA) Tactical Project Delivery View expanded to include additional tasks
    • Siebel CRM Supplemental Guide expanded task guidance and added select Siebel-specific OUM templates
    • WebCenter View Supplemental Guide updated for WebCenter Portal and Content Management


OPN Diamond, Platinum, and Gold Partners are able to access the OUM method pack, training courses, and collateral from the OPN Portal at no additional cost:

  1. Go to the OPN Portal at
  2. Select the “Partners (Login Required)” tab.
  3. Login.
  4. Select the “Engage with Oracle” tab.
  5. From the Engage with Oracle page, locate the “Applications” heading.
  6. From the Applications heading, locate and select the “Oracle Unified Method” link.
  7. From the Oracle Unified Method Knowledge Zone, select the “Implement” tab.
  8. From the Implement tab, select the “Tools and Resources” link.
  9. Locate and select the “Oracle Unified Method (OUM)” link.


Oracle R12 : Tables or Database Changes between different Versions

Lot of us are involve in Upgrade projects or something where we need to know what are the database level object changes happens between one version to another. Oracle provides something to start our work in that context ;

Refer Oracle support note :

EBS Data Model Comparison Report Overview [ID 1290886.1]

The EBS data model comparison report provides the static database object definition changes between two EBS releases to help users to preview the database object definition changes before upgrading their instances from one release to another and understand the impact of the database object changes that may affect the customization or business flows.

Users can select a product and navigate the database object definition differences for each supported database object type.

Here is the list of additional prefixes known to products that are considered for comparison in addition to their own product short code.

Product Short Name Additional Prefixes
EDW EDW (from all product schema including the common schema, e.g. APPS)