How to Prevent Manual Entries to System Accounts

There is a new feature in Oracle General Ledger R12 (12.1.3), which lets you define an additional qualifier called Third Party Control on your natural account values. You can set the Third Party Control qualifier for your accounts to be only used by Payables (Supplier) or Receivables (Customer), or prevent any manual entries (Restrict manual Journals). As General Ledger Super User, navigate to Setup > Financials > Flexfields > Values, select the value set with your GL accounts, and specify how the accounts can be used.

As is often the case with new Oracle functionality, there is a catch. As of today (August 2012), the Third Party Control does not apply to the journals uploaded from WebADI, which is how most (if not all) companies create their “manual” journals. There is an existing Oracle Support note ID 740851.1 acknowledging this fact and referring to a pending enhancement request (Bug 8289661) requesting remedy of this issue.

How To Set Flexible PO Approval Limits

Sometimes you might need to maintain controls over purchases and set flexible limits in the PO approval process. You can set the dollar threshold over which POswould need to be approved manually by a buyer. Here is a summary of a solution to accomplish this:

  • Establish the process for limit-base approvals.
  • Define a descriptive flexfield (DFF) on the Supplier Site to record the Amount threshold limit. If the DFF is blank, there is no limit.
  • Add PO Create Documents workflow attribute “Is PO Line within Tolerance?”, which will call a custom code.
  • Add workflow activity “Is PO Line Under Threshold?” to the “Create and Approve Purchase Order or Release” process, following the activity “Is Automatic Approval Allowed”. Output result of ‘No’ ends process. Output result ‘Yes’ launches the Document Approval Process.

  • Define a custom code to select purchase order’s supplier_siter_id in the PO_VENDOR_SITES_ALL table. Compare all Purchase Order Line(s) Unit Prices to the Site DFF Amount. If no DFF Amount has been defined, substitute 999,999,999. If any Line’s Unit Price is exceeds the DFF Amount, do not submit the PO to the PO Approval Workflow. The PO will remain at Incomplete status and the buyer must approve the PO manually.
  • Optionally, create an Oracle alert to send a listing of the POs being held to the supplier.  The alert could contain the Creation Date, PO Number, Line Number, Supplier Item Number, Quantity Ordered, Unit Price, Buyer Name.

Let’s Make Entry of Project-Related Purchasing Documents Easier!

This feature of the week is for anyone using Oracle Purchasing and Payables with Projects (R11i and R12). I spent some time with a group of users who were new to Oracle Applications (they switched from Sage and QuickBooks), and was showered with a slew of questions like “Why do I have to enter so many fields?”, “Why is it so unintuitive?”, “Can we modify the forms to hide the fields we don’t need and change the order in which they are entered?”…

The area of particular interest and concern was processing of purchase requisitions and orders. They did not use iProcurement, and hundreds of PO documents have to be entered manually in Purchasing. To make matters worse, some of their purchases were project-related, some were not; switching to a fully project-centric processing is out of question at this time.

Anyway, I don’t know how many times I had to defend Oracle’s design of the Distributions window for Requisition and POs. And quite honestly, I am running out of arguments to explain why it is so cumbersome. It is obvious that the option to enter project-related information was added on at later date, and its workflow is quite contra-productive. Let’s take a project-related requisition for example:

  • Enter requisition header
  • Enter requisition lines
  • Enter requisition distributions

Now, if it was a non-project requisition, you’d enter Charge Account and you are done.

PO Distributions

However, if it was a project-related requisition, you’d need to ignore the Charge Account, switch to the Project tab, enter the project-relate information, come back to the Accounts tab, finish the remaining required fields there, and finally, save your changes to initiate the Account Generator to assign the Charge Account. The process for entering the PO distributions is pretty much the same. Now, explain that to users.

Suggestion for Improvement

Why not make the Requisition Distributions and PO Distributions folder-based forms like the Invoice Distributions in Payables? It would not be difficult to do, and this way, users could arrange, display, hide the fields according to their business needs.

Supplier Invoice Distributions

Finally, it was really disappointing to see that this particular process and the Requisition and PO forms, were not enhanced in R12. Is this being discussed in the Supply Chain SIG? Is there a plan to incorporate the existing enhancement requests in the future?

Sample DataLoad Templates for Oracle Financials

Let me publish some sample DataLoad templates for Oracle Financials, along with their descriptions and navigation instructions. Note that these are only samples. You may need to modify the spreadsheets based on your application configurations. The blue columns represent DataLoad commands, the yellow columns is where you paste your data. Hope you will find the templates useful. Find more information and spreadsheets about DataLoad here. Share your own experiences with uploading and maintaining data in Oracle Applications as comments below.

Create GL Code Combinations

Define new accounts by creating new combinations of account segment values. You must define all new accounts manually if you do not allow dynamic insertion to create new accounts automatically (most people have it on), or if you need to pre-define your account combinations for data conversions [e.g. employees, assets, invoices, etc.].

Navigation: General Ledger Super User: Setup > Accounts > Combinations

Start Field: Account

DataLoad: Create GL Code Combinations

Create Employee AP Signing Limits

Create employee signing limits for expense report approvals. Managers can approve an expense report only if the total amount of the expense report does not exceed their pre-defined signing limit. The Manager (Spending) Approval Process in the Expense Reporting workflow uses the signing limits to determine which manager has authority to approve expense reports. When you assign signing limits to a manager, you also specify a cost center to which this signing limit applies and you can give managers signing limits for multiple cost centers.

Navigation: Payables Manager: Employees > Signing Limits

Start Field: Document Type

DataLoad: AP Employee Signing Limits

Create AP Expense Report Template

Define your Expense Report Template based on the expense report forms your company uses. You can define default values for expense items and you can choose those items from a list of values when you enter expense reports. During Expense Report Import, Payables uses the expense item information to create invoice distributions. If your employees will be entering project-related expense reports, you need to define project-related expense items and associate them with Oracle Projects expenditure types.

Navigation: Payables Manager: Employees > Signing Limits

Start Field: Document Type

DataLoad: AP Expense Report Template

Create Standard Suppliers

Create suppliers in Oracle Payables.

Navigation: Payables Manager: Suppliers > Entry

Start Field: Supplier Name

DataLoad: Create Suppliers

Create 1099 Suppliers

Create 1099 suppliers and their tax reporting options.

Navigation: Payables Manager: Suppliers > Entry

Start Field: Supplier Name

DataLoad: Create 1099 Suppliers

Create Customers

Create customers in Oracle Receivables. You can enter as much or as little information in the customer accounts as you want. The only information that is required for a new customer account is a party name, account number, and address.

Navigation: Receivables Manager: Customers > Standard

Start Field: Name

DataLoad: Create Customers

Delete AR Transactions

Use the Transaction window to delete the payment schedule by choosing the incomplete button in the and deleting the record. We used this method to remove legacy project invoices, which were generated in Projects (in order to create opening bill balances), and did not belong in Receivables.

Navigation: Receivables Manager: Transactions > Transactions

Start Field: Source

DataLoad: Delete AR Transactions

Create FA Categories

Create your asset categories. Category information is common for a group of assets. Oracle Assets defaults these depreciation rules when you add an asset, to help you add assets quickly. If the default does not apply, you can override many of the defaults for an individual asset in the Asset Details or Books windows. You have to set up default values for each category in each book. The default depreciation rules that you set up for a category also depend upon the date placed in service ranges you specify.

Navigation: Fixed Assets Manager: Setup > Asset System > Asset Categories

Start Field: Category

DataLoad: Create FA Categories

Assign Existing FA Categories To a New Book

Assign the existing asset categories to a new asset book.

Navigation: Fixed Assets Manager: Setup > Asset System > Asset Categories

Start Field: Category

DataLoad: Assign FA Categories To a New Book

Set GL Set of Books Name Profile Option

Set the GL: Set of Books Name profile option for all your responsibilities in the multi-org environment.

Navigation: System Administrator: Profile > System

Start Field: Responsibility in the Find System Profile Values. (Make sure to enable the flag.)

DataLoad: Set GL Set of Books Profile Option

Set MO Operating Unit Profile Option

Set the MO: Operating Unit profile option for all your responsibilities in a multi-org environment.

Navigation: System Administrator: Profile > System

Start Field: Responsibility in the Find System Profile Values. (Make sure to enable the flag.)

DataLoad: Set MO Operating Unit Profile

Set OIE Profile Options

Set the iExpense profile options to match you business requirements.

Navigation: System Administrator: Profile > System

Start Field: Profile

DataLoad: OIE Profiles

Find and Create Document Sequences

So today, I had to figure out which sequences existed and which ones had to be added for a new set of books. No rocket science but still a little messy exercise. I ended up with an ad-hoc query and a couple of DataLoads. Here they are for future reference:

Confused About Setting Up MOAC in R12?

Don’t despair, you are not alone. This feature applies to Oracle EBS R12. If you are implementing, or upgrading to R12, you’ve probably already heard of the new Multi-Org Access Control (MOAC) functionality. In fact, it is one of our main talking points, when we solicit new R12 projects…

Multiple Organizations feature (MOAC) provides security profiles which enable users to access, process, and report data in multiple operating units from a single “global” responsibility.

Both in 11i and R12, the MO:Operating Unit profile option ties a responsibility to a single operating unit. If the MO:Operating Unit profile option is blank at the responsibility level, the MO: Default Operating Unit profile is used for all responsibilities.

In order to make responsibilities “global” and give them access to multiple operating units, you have to define the MO: Security Profile system profile option. Note that if the MO: Security Profile is set, then the MO: Operating Unit profile is ignored, (regardless of which level the options are set at).

When it get tricky is when you need to define a combination of responsibilities intended for one operating unit and responsibilities that cross multiple operating units. In this case you need to carefully design the use of both MO Operating Unit and MO Security Profile options.

And another heads up: you should probably never set the MO: Security Profile at the site level because this overrides the MO: Operating Unit setting at the responsibility level and all responsibilities have access to all operating units.

Take the Oracle Financials Product Strategy Survey

Oracle Financials Product Strategy is conducting a survey to gain insight into how customers are using Oracle Payments solution. This survey targets payments professionals (payment administrators, payables analysts, payables managers, cash managers, etc.) and explores payment processing in customers’ organizations.

Payment processing in the context of this survey means initiating a request to pay supplier invoices, payroll expenses and other external and internal financial obligations by check, ACH, WIRE and other electronic or traditional forms of payment — all the way through to the payment execution by your financial institution. The survey takes approximately 25-30 minutes to complete. The results of this survey will be used for determining which business functions are most critical for payments and where Oracle can consider improvements to the existing solutions.

Please complete the survey or forward it to the appropriate people in your organization to complete and submit no later than August 13, 2010. If you require assistance in accessing the survey, please contact

OAUG Invites You to Present at COLLABORATE11 in Orlando

OAUG invites you to submit a presentation proposal and share your approach to Oracle Applications in an education session at the COLLABORATE 11: Technology and Applications Forum for the Oracle Community. More than 5,000 attendees will gather for the user-driven education and networking event April 10 – 14, 2011 at the Orange County Convention Center West in Orlando, Florida. Proposals are now being accepted. The deadline is Friday, October 1, 2010 at 11:59 p.m. EDT.

Get more information about presenting at COLLABORATE 11, including tracks, specific industry- or product-related areas of emphasis, presenter requirements and the presentation submission processes.

Note These Important Presentation Submission Dates and Deadlines

  • October 1, 2010, 11:59 p.m. EDT: Presentation abstracts due.
  • Week of December 6, 2010: Accepted presenters notified.
  • January 12, 2011: Acceptance of the compliance agreement due.
  • March 1, 2011: All presentation materials including white paper and presentation slides are due.
  • April 10 – 14, 2011: We look forward to seeing you in Orlando!

How To Capitalize Legacy CIP Costs in Oracle Projects

Here is a quick summary of steps to convert legacy CIP costs on capital projects and capitalize them into assets. The instructions are applicable to both: Oracle EBS 11i and R12. This article assumes that you have already configured all prerequisites in your Oracle Project Costing and Fixed Assets. You will need Project Costing Super User and Fixed Assets Manager responsibilities, or the customized versions there of.

Define Capital Projects

Let’s start with defining the project template. This is where you can leverage a lot of good Oracle functionality to your benefit.  The details will vary in every instance, but in general, you need to complete the following steps:

PA1: Define Capital Project Template – Header

Navigate to Setup > Project > Project Templates to create a new template. Define the header-level information including the Number, Name, Project Type (make sure it is a Capital  project type class – you won’t be able to change it later), Organization, and Description.  If you want project created from this template to have a fixed duration (e.g. 12 months), define the Trans Duration. The system will populate the Project Finish dates based on the Project Start date and this duration for new projects.

PA2: Define Capital Project Template – Quick Entry Fields

Click on Setup Quick Entry. Define the fields for quick entry of projects. These are the “bare minimum fields” to enter when you copy a template into a project.

PA3: Define Capital Project Template – Tasks

Click on the blue box next to the Tasks option (I know, it not very intuitive), or click on Detail (while the cursor is on Tasks). Define your tasks, or work breakdown structure (WBS). Also determine which tasks should be capitalizable or not, i.e. can collect CIP costs to become fixed assets.

PA4: Define Capital Project Template – Define Project Assets

In this step, you can pre-define fixed assets you anticipate to complete as part of projects created from this template. Basically, you do here what you’d normally do in FA Mass Additions. You can use the Create Project Assets DataLoad to upload a higher volume of assets…

PA5: Define Capital Project Template – Assign Project Assets

Depending on your implementation approach, you assign default assets at project or task level. This functionality determines how the capitalizable expenditures will be assigned to future assets. In order to automate this, you probably want to assign assets at the task level so that you have one-to-one relationship between the costs and the assets in your tasks. The other options would be the costs in multiple tasks to be summarized into one asset at the project level… Refer to the Asset Summary and Detail Grouping Options in the Oracle Project Costing User Guide. You can use the Create Task Level Asset Assignments.

PA6: Define Capital Project Template – Create Task Transaction Controls

Following the example of the task-level asset assignment we defined in step PA5 above, it is critical, that the right CIP costs get collected in the right tasks. In other words, we want building-related costs be collected in building-specific task to capitalize them into a building asset assigned to the same task. To prevent invalid entries, you can create Transaction Controls. These are, basically, conditions that determine what expenditure types or categories are allowed in what projects or tasks. Again, use this sample Create Task Transaction Controls DataLoad to prepare and execute your upload. The trick will be finding the right tasks based on their WBS level. And one note about the Transaction Controls: make sure the Control Start Date and End Date are consistent with your Task Start Date and Finish Date.

PA6: Define Capital Project Template – Set Task-Level Capitalizable Flag

This is an optional step, and this should be taken care of by your Transaction Controls. However, you can make all expense tasks non-capitalizable by setting the Capitalizable flag to null… You can use this Update Expense Task Capitalizable Flag DataLoad.

PA7: Create Projects

Using the template created in steps PA1-6, create your capital projects. The quick entry fields will vary depending on your template, but as always, you can use the Create Projects DataLoad.

PA8: Upload Legacy CIP Costs

Depending on your conversion strategy, you can use a custom conversion program, or WebADI spreadsheet to upload your legacy CIP costs. Check this article for detail on how to convert legacy costs and how to resolve a couple of “features” during the Transaction Import.

PA9: Update the Project Status Inquiry Amounts

As Project Costing Super User, run the PRC: Update Project Summary Amounts

PA10: Complete Asset Definitions

Based on your implementation strategy and business process, you might be required to complete asset definitions in Projects before asset lines can be generated and interfaced to Oracle Assets. As Project Costing Super User, navigate to Capitalization > Capital Projects. Query up your project(s), click on Assets > Asset Details. Complete the asset definitions, just like you would in the Prepare Mass Additions.

PA11: Generate Asset Lines

As Project Costing Super User, run the PRC: Generate Asset Lines for a Range of Projects process. Watch the Date In Service parameter and PA Date parameter to make sure you select the right lines to generate.

PA12: Interface Asset Lines to Assets

As Project Costing Super User, run the PRC: Interface Assets to Oracle Assets process. It will create mass additions in FA MASS ADDITIONS table.

FA13: Post Mass Additions

If you required complete asset definition in Projects, your mass additions are ready to be posted. If asset lines are incomplete, you need to complete them in Prepare Mass Additions. In any case, once ready, log in as Fixed Assets Manager and navigate to Mass Additions > Post Mass Additions to create assets.

At this point, your legacy CIP costs were capitalized into fixed assets. Obviously, this summary is  simplified; refer to the Oracle Project Costing User Guide and Oracle Assets User Guide for more details.

Who Knew? YouTube Becoming a New Oracle Training Resource?

So far I’ve looked at YouTube as a source of entertainment, not useful information. But recently I noticed several “grass roots” attempts to utilize YouTube as a training and demo resource for Oracle eBusiness Suite. Check out some of the videos about Oracle eBusiness Suite.

Oracle Training – Navigating in Oracle E-Business Suite R12 Video by i-Oracle (about 10 minutes)

What do you think? Is this a way to go?

Projects, Templates Form Does Not Let You Update More Than 25 Records

You can encounter this feature in Oracle Projects 11.5.10, form PAXPREPR, version 11.5.640.


I am building up a project template and using DataLoad to create project and task options like project assets, task-level transaction  controls, or task-level asset assignments.  There are several hundreds of these to upload, so DataLoad seems a perfect method.

The Feature

For some reason, however, and I am not even able to properly articulate this, I am unable to upload more than about 25 records at one time. When it reaches some limit, the form simply freezes up, and you have to shut down the browser and re-open your Oracle Applications session.

Has any of you experienced this? What is causing it? I do not believe it is caused by some DataLoad limitation, I’ve used it to upload hundreds and thousands of records in “one run”.

Any ideas will be greatly appreciated. This is truly driving me insane.