The Feature

Tips and Tricks for Oracle FSGs

Financial Statement Generators (FSG’s) are a necessary evil at any company running Oracle’s E-Business Suite General Ledger. Row Sets, Column Sets, Content Sets, Parents accounts or Ranges, Publish with XML, ADI. . .the combinations and possibilities are endless. And the decisions you make when creating and maintaining your FSGs can affect their ease of use and maintainability in the future.

See my Tips and Tricks for Oracle FSGs white paper for Best Practices, Tips and Tricks to making this task a little more manageable and less time consuming. When working with FSGs, perhaps the most important thing to remember is the FSG functionality is old. I have been a heavy user of General Ledger for 13 years, and except for generating outputs, it is basically the same.

That means that the majority of older installs (implemented prior to 11i) had one-off patches that greatly affected the way FSGs behave. A report created three years ago that is copied or mimicked for a new report-well, they just give different results. Research shows the new report is behaving as Oracle documentation explains it should, but the old report is not.

So I leave you with my biggest tip of the paper: Tip: If it works, don’t change it! See the while paper for more details…

Changes to Web ADI Documents in R12

This feature is for everyone who is trying to create Web ADI documents in Release 12. The documents in R12 are cleaner, more user-friendly and intuitive; they are easier to create and update. Finding them, however, might be a challenge.

The main difference between R11 and 11i was introduction of function security. You either need to figure out which module-specific responsibilities include the Web ADI integrators you want to use, or you need to find out which integrators (functions) need to be associated with the new Desktop Integrator responsibility. Either way, this is not well documented in the Oracle Web ADI User Guide R12; and you have to figure it out by reading product-specific user guides. It took us a couple of hours to find the right Metalink note (472160.1), in which these changes were described…

The Feature

In R12, the existing responsibility Oracle Web ADI is obsolete! The Create Document function does not work and integrators are missing.

The Solution

Add a new responsibility Desktop Integrator. The Create Document function under this responsibility should work for a handful of seeded integrators. You will need to research the user guides to find out how to include additional integrators.

The seeded integrators included in the new Desktop Integrator responsibility:

• Enterprise Performance Foundation: Hierarchy Integrator
• Enterprise Performance Foundation: Member Integrator
• Financial Consolidation Hub: Intercompany maps
• Financial Consolidation Hub: Dimension Hierarchies
• Financial Consolidation Hub: Value Set Mapping
• General Ledger - Budgets
• General Ledger - Journals
• Intercompany - Single Batch Entry

To include additional integrators, you need to include additional menu functions in the menu assigned to your Desktop Integrator responsibility. Navigate to System Administrator > Application > Menu, and include the following sub-menus in your menu (Desktop Integration Menu if you use Desktop Integrator responsibility. Here are few examples of Projects Integrators:

• For Project Budgets and Forecasts, include Financials: Project: Budgets and Forecasts function. This is documented in Oracle Projects Implementation Guide.
• For Project Expenditures (Transaction Import), include Expenditure Entry Using Microsoft Excel menu. The seeded integrators are also available in the Project Super User responsibility and documented in the Oracle Project Costing User Guide.

I find Oracle’s approach to document setup steps in multiple user guides and make them accessible in multiple responsibilities troubling. It makes implementation workflow very confusing. Along with Web ADI, other examples of this approach include iExpenses, Project Management, Resource Management, or iProcurement…

When Oracle Asset Security by Book Doesn’t Work

This feature of the week is for those who operate in a multi-org environment with multiple Asset Books. Unlike other Oracle Financials modules (Payables, Receivables, Purchasing, Order Management or Projects), Oracle Assets is not operating unit specific. The way you separate your transactions is by defining the Asset Security by Book.

Summary of Setup Steps

Step 1: Define Asset Organizations.

As Fixed Assets Manager:

• Navigate to Setup > Security > Organization > Description.
• Query up the organization, for which you want to define the Asset Security. Typically, this could be your operating unit.
• Include classification ‘Asset Organization. Click ‘Other’.
• Associate the organization with the asset book. Now, this step is very peculiar. You want to find an existing asset book. But! If you ran an open query, you’d get all books, which were already associated with this organization. And if you were assigning the first book to this organization, you’d retrieve nothing.
• What you must do is submit a specific query, i.e. run it as View > Query by example > Enter, and enter the book name.
• Then you have to make a change, any change like changing the book description, in order to be able to save it. This is not a bug, it’s a feature! So be aware of it.
• Save you changes. Now your book is associated with the asset organization.

Step 2: Define Asset Security Profile

As Fixed Assets Manager:

• Navigate to Setup > Security > Security
• Define a profile, which you will later assign with user responsibilitities. The profile links together the asset book, asset organization and responsibility.
• Select Organization Hierarchy and Top Organization in it to be valid in this profile, or
• Include specific organizations, which will be valid in the profile.

Step 3: Assign FA Security Profiles

As System Administrator:

• Navigate to Profile > System.
• Enter the asset responsibility you want to assign to a specific asset book (FA security profile).
• Enter ‘FA Security Profile’ as profile.
• Enter ‘Your Asset Profile Name’ in the profile value.

Step 4: Validate Your Asset Security Setup

Log in with the responsibility, for which you just defined the Asset Security above. Navigate to Asset Workbench and select the book values. If you see more than the one book you expected, something is not right.>

When Asset Security Doesn’t Work

• Check the Organization and Organization Hierarchy Setup. The Security by Book feature enables you to designate an organizational hierarchy in which the assets of an organization can only be seen by either the organization or its parent.You may set up a number of organizations and include as many of them as you wish in the hierarchy. Make sure that your hierarchy is set up as you expect.
• Run the Security List Maintenance program. This program populates the LOV’s with the appropriate information. If you miss this important step, your LOV’s will not show the correct books that are available for each organization to access.
• Define Asset Security for all books. You will always see asset books, which are not associated with any security profile. If there is a book that already exists on the system and you have not assigned that book to a particular organization, all organizations will be able to see the book and its assets.

For more information, refer to the Oracle Assets User Guide, or this [old but good] white paper on the subject (Oracle Metalink sign-on required).

How to Override Asset Depreciation

I am not actually sure when this feature became available but, to my delight, I found out today that you can retroactively override existing depreciation amounts for standalone and group assets calculated by Oracle Assets. Hence, depreciation override is my “feature of the week” this week.

Before running depreciation or performing adjustments, you must provide the necessary information in the Depreciation Override window or the FA_DEPRN_OVERRIDE table. Indicate whether the override is for depreciation or adjustments. When running depreciation, the system will upload and use the depreciation amounts provided in the interface table. You have to set the profile option FA: Enable Depreciation Override to Yes in order to use this feature.

Here is how to enter a depreciation override amounts using the Depreciation Override window:

1. As Fixed Assets Manager, navigate to Depreciation > Override.

2. In the Find Assets window, you can search for assets for which you need to change depreciation. If you did not use the Find Assets window, enter the asset number, book, and period of the asset in the rows of Depreciation Override window.

3. In Depreciation Override window, enter the override depreciation amount in the Depreciation field, or enter the override bonus depreciation amount the Bonus Depreciation field.

4. Select the adjustment type of Depreciation or Adjustment in the Use By field. The Status field displays the current status of the override record, which may be New, Post, or Posted. Set the status to ‘Post’ to submit your override. If the status is Post or Posted, you cannot update the record, you can only delete the record and reenter the updated record.

5. Save your work.

6. Run What-If Analysis or Projection to review the estimated depreciation amounts for that period. The depreciation run will pick and process your overrides in the ‘Post’ status and will change them to the ‘Posted’ status.

How to Reclass Projects and Tasks En Masse

Darn it. You manually set up your projects and you found out some attributes were configured incorrectly. Now what? There is good news. You can use the Project Administration feature (Oracle Projects 11i) to update Project and Task Organizations in one, easy update.

The way it works is that you create an ‘update batch’ of changes you want to apply. As Project Billing Super User [or Costing Super User], navigate to Project Administration > Mass Update Batches and generate a mass update batch:

• Enter a Batch Name, Description, and Effective Date for the batch.
• In the Generate Detail Lines region of the window, enter the selection criteria to select the projects and tasks you want to update.
• Choose Generate Detail Lines to generate the mass update batch lines.
• Review and/or revise the mass update batch by choosing Details.
• When ready to execute the mass update, Submit the batch. The system submits a concurrent request, which completes the update.

The system will also update all related burdening and cross-charge schedules and take the new organizations into account after the change.

Enhancement Tip

Unfortunately, the Mass Update functionality is only available to update Project and Task Organizations at this point (11i). It would be great if additional attributes were available, including Start Date and Completion Date, Key Members, Project Classifications, Service Type, etc. Judging by the fact that the ‘Attribute’ field has a drop-down menu, that was probably an original intention, but it was never implemented.

Also, the batch control functions are currently not arranged in any logical order and were confusing to the users. Arranging the functions left-to-right, or right-to-left according to follow their logical work flow would make more sense.

Foresee Those Future-Dated Expense Report Items

You can encounter this feature when using Oracle iExpenses, Payables and Projects 11i.10.

The Feature

Oracle iExpenses 11i.10 lets you enter, approve and pay expense report items with incorrect dates in the future. I was asked by my client today to look into expense report items rejected in the Payables to Projects interface and to my surprise, there were several project-related items in Payables with item dates in the future. The item date is entered by employees when entering their expense report line items. I believe the dates were in most, if not all cases entered in error [and against the expense reimbursement policy].

The Interface Expense Reports from Payables to Projects process checks for, among other things, the following conditions:

• Expenditure Item Date is within an active employee assignment

• Expenditure Item Date is within an active project (and task) date range

• Expenditure Item Date is within an open or future PA Period in Projects

• Expenditure Item is charged to a project in an active status.

Now, this validation happens online during expense report entry and approval, but it happens again several days, or weeks, later during the Payables to Projects interface process. This is after the expense report had already been entered, approved, imported into Payables, validated and accounted for in Payables, and in many cases, already paid, and after some of the original conditions may have changed. Since these are the invoice line item dates, not invoice dates, it is very difficult for Expense Reports Auditors, or Payables, to catch these. Furthermore, once invoices are validated and accounted for, they cannot be updated.

The Workaround

As of OIE.H or higher, you can use the Future-dated expenses feature. You can use the setup utility to define whether future-dated expenses should be treated as warnings or errors during expenses entry. This ensures that expenses are not entered into inappropriate accounting periods, which causes reimbursements billing to be delayed and accounting to be incorrect.

To set the future-dated tolerances, log in with Internet Expenses Setup and Administration responsibility > Expenses Setup > General > Options. On this page you can define future-date tolerances for each operating unit. Individuals can submit future-dated expenses according to the values you specify.

If you did not set these tolerance limits and want to see whether any future-dated items exist in your system, use the ad-hoc query below.

Future-Dated Expense Report Items

Automatic Timecard Approval for Terminated Employees

You may encounter this feature if using Oracle Time and Labor and Oracle Projects 11i.10.

The Feature

Employees enter their project-related timecards in OTL and you want to import them to Oracle Projects. If the timecards are automatically approved by the Workflow Background Process (OTL Workflows for Employees item), they will not get approved for terminated employees and contingent workers. It appears the date of the approval process must fall within the person’s period of employment or placement to complete successfully. If the approval process is outside of the period of employment/placement the approval process fails with the error message ‘ORA-20001: This person does not have preferences for the selected effective date’, which generates an email to the System Administrator.

The Metalink Note 338716.1 describes a similar issue. In the note, Oracle recommends disabling the deferred approval option and approving timecards instantly upon their submission. This would not work because it leaves users with no option to withdraw and correct their timecards once they were submitted.

The Workaround

You need to reverse the terminations for the people on the unapproved timecards list and use the $FND_TOP/sql/wfretry.sql script to approve their timecards. Then run the PRC: Transaction Import Process in Projects to interface the timecards to Projects. And last but not least re-enter the employee terminations.

Another way to avoid this situation is to implement timecard approval by supervisor or project manager. However for those who do require this kind of approval and want to approve timecards automatically, it would be very helpful if the Workflow Background Process had a date parameter, so that it would be possible to run the approval process for past dates.

Using Expense Cost Center in Project Expense Report Account Generator

The Feature

You may encounter this feature if using Oracle iExpenses, Payables and Projects 11i.10. When creating project-related expense reports (invoice type = Expense Report) directly in Payables, Oracle uses the same Project Expense Report Account Generator process as used by iExpenses.

You might have a problem with populating the Cost Center segment during account generation if you use the Expense Cost Center attribute (Override Cost Center field from the expense report header) in your account generator definition. When expense reports are created or adjusted directly in Payables, the Expense Cost Center is not a valid source attribute in Account Generator, and account generation fails.

The Workaround

When creating expense report distribution lines in Payables, bypass the use of Account Generator by populating the GL Account fields first, saving your changes, and only then populating the project related attributes (project, task expenditure type and expenditure date).

How Account Generation Really Works in Oracle iExpenses

Speaking about features and idiosyncrasies, let’s talk about how the accounting flexfield is generated in Oracle iExpenses (10.5.10) when employee submits his or her expense report. For simplicity sake, let’s assume the expense report does not have any multi-currency or credit card items. And as example, let’s assume we are using a simple chart of accounts containing of three segments:

Company-Cost Center-Account

Our employee belongs to company 100, and cost center 1000. Oracle iExpenses follows these steps to generate his account during the expense report entry:

Step 1: The Default Expense Account Defined in employee’s HR assignment:

100-1000-55555

Step 2: Employee enters the Expense Report Header. If he does not override the Cost Center, iExpenses proceeds to Step 3. If he does override the Cost Center (e.g. 2000), Oracle iExpenses builds an initial accounting combination of his Default Expense Account with his new Override Department.

100-2000-55555

The Feature: If this initial combination is invalid, employee will be able to proceed with the expense report entry but the report submission will fail without notifying the employee and regardless what the employee enters below!

Step 3: Employee enters expense line with an expense type (e.g. Airfare=50050) and no project. The system generates the expense line accounting flexfield. If project and task are entered, the system proceeds to Step 5:

100-2000-50050

Step 4: If Expense Allocations functionality is enabled, employee can change the above expense distribution for each line (e.g. 200-2222). If the above expense combination is invalid and the employee does not update it, the expense report submission will fail.

200-2222-50050

Step 5: Project and Task were entered at the expense line level: iExpenses will engage the Account Generator, and will follow its rules to update the initial combination, and build the new one. For example, let’s say the Company is derived from Project Organization (Seattle=100), Department from Override Department above (2000), and Account from Expense Type (Airfare). The resulting accounting combination will look as follows:

200-2000-50050

Implementation Tip: Define one Default Expense Account to be valid across all combinations of companies and cost centers, which will be used in cross-charges.

Hope you will find this information helpful. We did not have much luck trying to figure this out using the available Oracle resources.

Provide comments, or contact me at marian.crkon@itsafeature.com if you have any questions.

Little Loved Lookup Set Maintenance

You may encounter this feature if using Oracle Projects 10.5.10.

The Feature

Maintaining Lookup Sets. I understand their role as user-definable mapping tables between the project attributes and the chart of accounts values. They are great for mapping anything you want - legacy values to Oracle chart of accounts, third party applications to Oracle Projects, etc.

Typically, you end up maintaing same pairings of attributes - HR organization to GL companies, HR organizations to GL cost centers, expenditure types to GL accounts, event types to GL accounts etc. However, having the mapping based on values without any validation creates a maintenance nightmare. Imagine a business with several hundred to several thousands departments. In order to meet the business needs, you define several lookups sets, which map HR organizations to GL company, division or department values, etc. Imagine you change departments names in HR several times a year. Keeping lookup sets up-to-date is a full time job! There is no API, no WebADI upload, no mass update, only a good old Update Lookup Set DataLoad to make the maintenance easier.

Enhancement Tip

Why not build parameters into lookup sets, similar to AutoAccounting Rule parameters? Based on lookup values “parameters”, or “types”, you could store lookup values based on their internal IDs, not actual values. That way, if for instance HR department names change in HR, so will their corresponding lookup values in the lookup sets in Projects. There could be “Text Only” lookup sets too for mapping values, which do not originate in Oracle.