The Feature

Sample Ad-Hoc Queries for Oracle Financials

January 11, 2008 on 8:06 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Here is a compilation of several ad-hoc SQL queries I created over time to perform various analytical tasks withing Oracle Financials. I am by no means a SQL guru, I just know enough to keep myself out of trouble. These are simple queries, which at different occasions provided me with the information I needed. I am posting them here to find them again when I need them and to share them with those of you who may find them useful too.

Active Employees and Supervisors

Find a list of active employees and supervisors with their accounting detail. This is needed for instance when defining the AP approval limits for iExpenses.

Active Employees and Supervisors

AP Signing Limits with Employee Information

Extract AP Signing Limits with employee information.

AP Signing Limits with Employee Information

AR AutoInvoice

Analyze the AR Interface tables used by AutoInvoice.

AR AutoInvoice

Asset Categories and Lives

Get a listing of asset categories with the accounting and depreciation information.

Asset Categories and Lives

Asset Details Extracts

Extract asset detail information. Surprisingly, it is not easy to extract assets from Oracle Assets to convert them back to Oracle Assets.

Asset Details Extracts

Employee Email Address Query

Compare employee and user email information.

Employee Email Address Query

Employees and Users

Compare employee and user information. Find out which employees are not set up as users and vice versa.

Employees and Users

Employees with Default Accounting Information

Select employee information from the HR tables with the default accounting information.

Employees with Default Accounting Information

Employees, Organizations, Users and Responsibilities

Analyze by organization what users and responsibilities are set up for employees.

Employees, Organizations, Users and Responsibilities

Expense Report Template Details

Extract expense report template details.

Expense Report Template Details

Future-Dated Expense Report Items Query

Find future-dated expense report items entered incorrectly in the future. Prevent this problem by setting the respective OIE options.

Future-Dated Expense Report Items

HR Managers

Get a list of HR managers.

HR Managers

OIE Expense Report Import Query

Analyze project expense reports imported from iExpenses.

OIE Expense Reports

Update Asset Lives

Unfortunately, the Mass Additions process does not let you provide an asset life during the upload via FA_MASS_ADDITONS table. The life for converted assets always defaults to the life defined in the category. I believe this has changed in R12, but in prior release, you might need to use this script. As a workaround, we put the new life in ATTRIBUTE4 in FA_MASS_ADDTIONS, and updated FA_BOOKS table after the assets were created.

Update Asset Life

Update Email Preference

Update a default email preference.

Update Email Preference

How to Convert Legacy Expenditure Balances to Oracle Projects

October 12, 2007 on 3:58 pm | by Marian Crkon | In How To Guides | 3 Comments |  Print |  Email

Here is another piece on converting legacy balances into Oracle Projects. To see how to convert your legacy project revenue and invoice balances, check here.

Before you start the legacy cost data conversion process, your Oracle Financials and Projects modules need to be fully configured and your prerequisites created. When migrating from your legacy system to Oracle Projects, you may need to bring your ending legacy balances as your opening balances in Oracle. This will allow you to seamlessly manage and report on your project activity.

For the purposes of this article, let’s take for example a scenario when you go live with Oracle Projects on January 1, 2008, and let’s assume we need to convert legacy cost balances in summary. When we extract the balances from the legacy system, we would summarize them by project, task, organization, expenditure type, and date. The balances will be created and processed as miscellaneous transactions in Oracle Projects.

Step 1 (GL): Review your chart of account values. If needed, add new values for revenue accounts, companies, departments, products, etc.

Step 2 (HR): Define your project organizations. Classify organizations that will own projects as Project/Task Owning Organizations, and classify organizations that will incur revenue as Project Expenditure/Event Organizations.

Step 3 (HR): Include new project organizations to your project organizational hierarchy.

Step 4 (HR): Create your employees and employee assignments. Each project has to have a valid employee assigned as project manager in order to generate client invoices.

Step 5 (PA): Review and update project structures: project types, project templates, expenditure categories, revenue categories, expenditure types, lookup sets…

Step 6 (PA): Create your projects and tasks. Make sure to select a correct project type for each project, i.e. (Time and Material, Fixed Price, Cost Plus, etc.) You cannot change project type once you generated project costs.

Note:

If you are converting costs from multiple years, make sure to open prior periods (e.g. DEC-06 for 2006 costs) in Projects in order to determine correct PA and GL Dates! See more details on PA and GL dates in this article. If you do not open respective prior periods, transaction GL and PA dates will be assigned in the current opened period, and your project reporting may be inaccurate.

Step 7 (PA): Define your Transaction Source. Transaction sources identify the source of external transactions you import into Oracle Projects using Transaction Import. You can define a new the transaction source ‘Conversion’ to identify your legacy costs from from your legacy system. When you create a transaction source, you control the Transaction Import processing by the options that you select. Here are few recommended settings for some key options:

• Transaction Source – Define your transaction source, e.g. ‘Conversion’.

• Default Expenditure Type Class – The system uses the default expenditure type class that you assign to a transaction source if an expenditure type class is not specified in the interface table. Enter ‘Miscellaneous Transaction’.

• Raw Cost GL Accounted – Select this option to indicate whether transactions imported from this transaction source have already been accounted for in GL. Oracle Projects expects that the external system has already posted the raw cost to the appropriate debit and credit accounts. None of the Oracle Projects processes will transfer these costs to GL or AP. If you enable tis option, you will also need to provide valid debit and credit code combination IDs. When you select this option, the Import Raw Cost Amounts option is automatically selected.

• Import Raw Cost Amounts – When a transaction source has this option enabled, the raw cost amount of the transactions has already been calculated (costed) in your external system. None of the Oracle Projects processes will calculate raw cost amounts for these transactions.

• Import Burdened Amounts – When this option is selected for a transaction source, Oracle Projects expects the external system to provide burdened costs. If the transaction does not have a burdened cost amount, Transaction Import will reject the transaction. When you select this option, the Import Raw Cost Amounts option is automatically selected.

• Allow Duplicate Reference – Enable this option to allow multiple transactions with this transaction source to use the same original system reference. If you enable this option, you cannot uniquely identify the item by transaction source and original system reference.

• Allow Interface Modifications – This option allows you to modify rejected transactions in the Review Transactions window after the import process is completed.

• Purge After Import – If you select this option, items successfully imported from the transaction source are automatically purged from the interface table when the import process is completed.

• Allow Reversals – If you enable this option, Oracle Projects allows reversals of expenditure batches or expenditure items for the transaction source. When you enable this option, the Allow Adjustments option is automatically enabled. Disable reversals for legacy balances, which should not change, i.e. balances posted in prior periods, and enable it for balances, which need to be processed as “new transactions” in Oracle Projects, e.g. unbilled billable transactions or uncapitalized capitalizable transactions.

• Allow Adjustments – If you enable this option, you can adjust imported transactions in Oracle Projects after you load them via Transaction Import. Disable adjustments for legacy balances, which should not change, i.e. balances posted in prior periods and enable it for balances, which need to be processed as “new transactions” in Oracle Projects, e.g. unbilled billable transactions or uncapitalized capitalizable transactions.

• Processing Set Size – Enter the size of the processing set. The value entered indicates the number of records to be processed in each set. When interfacing large amounts of data, you can reduce the impact of unexpected errors by processing transactions in sets. The import process issues a database commit after each set is complete. If an error occurs and a rollback is issued, only the transactions in the current set are affected.

• Effective Dates – make sure your ‘From’ effective date is before your oldest expenditure item date you intend to upload.

Step 8 (PA): Disable cost transfer to General Ledger. Navigate to the Implementation Options screen and disable the Interface Usage Costs flag. This will prevent your legacy cost balances from being interfaced to Oracle General Ledger since the assumption is they were already posted by your legacy system.

Step 9 (WebADI): Create a WebADI integrator for Project Transaction Import. You can either use one of the seeded Project Integrators, or define your own, which matches your Transaction Source.

Step 10 (PA): Upload legacy cost balances as miscellaneous expenditure transactions. For high volume uploads, create a custom script and upload your data directly into PA_TRANSACTION_INTERFACE table. For medium-size uploads of several thousands records, use the WebADI Upload spreadsheet you created in Step 9 above.

Step 11 (PA): Run Transaction Import. When using WebADI, you can submit this step during your upload. Oracle lets you fix any rejections either in your upload spreadsheet, or in the interface table using the Review Transactions form.

Note

One comment about Transaction Import Status. Even though you can correct your most of your rejections in the Review Transactions screen, Oracle Projects does not let you change transaction status from Rejected (R) to Pending (P) in the Projects Transactions Interface table. Use this Set Transaction Import Status script to re-set the status.

Step 12 (PA): Distribute transaction costs. Run the PRC: Distribute Usage and Miscellaneous Costs process to calculate the amounts and generate account distribution lines. Make sure to run this process with the right period open and the right Through Date to assign correct PA and GL dates to your legacy transactions.

Step 13 (PA): Run the interface to General Ledger. Run the PRC: Interface Usage and Miscellaneous Costs to General Ledger process. This will set the legacy cost balances to look like they are interfaced to GL. If you disabled the GL Transfer option in Step 10, no journal entries will be created. Alternatively, you can run the PRC: Submit Interface Streamline Processes with option ‘DXU: Distribute/Interface Usage and Misc. Costs to GL’ to complete steps 12 and 13 together.

Step 14 (PA): Turn back on the GL interface you disabled in Step 8. Navigate to the Implementation Options screen and enable the Interface Usage Costs flag.

And that’s it. The exact sequence of steps will, of course, depend on your company’s specific business. Hopefully, this article provided a good starting point for understanding this process.

How to Change GL Calendar without Reimplementing Your Oracle Financials

October 5, 2007 on 8:02 am | by Marian Crkon | In How To Guides | 24 Comments |  Print |  Email

We had a pressing business need to change our corporate calendar from a fiscal year ending on March 31 to a calendar year ending on December 31 to be consistent with our parent company, which acquired us this year. Oracle’s recommendation in this situation is to create a new set of books and use the consolidation feature to move account balances from the original set of books to the new set of books. This assumes that the periods that need changing already have data or have a status other than Never Opened which is usually the case.

Creating a new set of books, however, becomes a big problem if General Ledger is not the only application you use. It means creating new operating units and replicating configurations in all your sub-ledgers. It also means supplier and customer sites and all open purchase orders, sales orders, invoices and other pending transactions must be converted. Basically, we are talking about re-implementing your system.

You Are Not Alone

Many other businesses find themselves in a similar situation and have no time or desire to re-implement their system to accommodate this request. The good news is that Oracle Support acknowledged this pressing need and came up with a couple of recommendations.

After reading the Metalink note 102460.1 and OAUG white paper on this subject, we were encouraged there was another way. Changing the GL calendar without defining a new SOB saved us from re-implementing our sub-ledgers in three operating units, which used the current set of books and calendar. The system allows you to make changes to the existing calendar when you meet the following conditions:

• All the periods are in a status of Never Opened.

• A budget year that includes the periods to be changed is no to yet opened.

• An encumbrance year that includes the periods to be changed is not yet opened.

• The periods to be changed have not yet been copied from the GL Calendar to Project Accounting.

The status of the periods for all applications is maintained in the gl_period_statuses table. There is a record for every period for every application. The status in this table of the periods being changed must be ‘N’ for Never Opened for every application. Most subsidiary financial applications can control their own periods and it is maintained in that table. The exception is Project Accounting, which in addition to having its period statuses maintained in the gl_period_statuses table, it also stores its project status information in the pa_periods_all table.

Looking at our GL_PERIOD_STATUSES table, there were future periods that were not in ‘Never Opened’ status in GL, Projects and Payables applications. In Projects because of the way the standard functionality copied the whole calendar from GL to PA. Luckily, we did not have any budgets or encumbrances posted to those future periods.

Here Is How To Do it

We did the following to change our existing corporate calendar:

In General Ledger

• Identified the future periods in calendar, which need to be redefined.

• Updated period statuses to ‘Never Opened’ for all applications and applicable SOBs.

• Identified and deleted any GL Balances in future periods.

• Re-defined our corporate calendar. In our case that meant defining three adjusting periods in year 2007 to make 2007 a short year ending with Dec-07, and re-defining future periods to match the calendar year. (You can use this DataLoad to do so).

In Projects

• • Deleted (old) future periods (beyond Jan-08) from Projects in all operating units
  • Copied newly-defined GL periods from GL to Projects
  • Create new Corporate Books. (Manually)
  • Create a new Prorate Conventions. (Manually)
  • Assign Asset Categories to new that were linked to the new corporate book. (DataLoad)
  • Extract and upload all active assets to the new corporate books. (SQL script to extract, and ADI to upload)
  • Closed all 2007 periods in all sub-ledgers and GL through Dec-07
  • Created transactions in Dec-07 in all sub-ledgers, including POs, invoices, expense reports, payments, INV transactions, AR transactions, cash receipts, timecards, project adjustments, and assets additions and retirements.
  • Ran depreciation for Dec-07.
  • Closed Dec-07 in all sub-ledgers and GL.
  • Opened Jan-2008 all sub-ledgers and GL and “rolled into” the next fiscal year.


Remember, you should not use this solution if any of the periods you wish to modify are in use. This means if the period has a status of something other than Never Opened, or if the period is in an open budget year or open encumbrance year you will not be able to change it on the form.

Fixed Assets

We use use Oracle Assets with asset calendar starting on April 1, 2001. We had about 2,000 assets in our corporate books, which are associated with the set of books and the fiscal calendar ending on March 31. After considering several alternatives including not doing anything, we decided it was best to create a new calendar to be consistent with General Ledger. We couldn’t simply adjust the current calendar. We need to create a new asset calendar.

After creating a new asset calendar we also had to:

Test, Test and Test Again

To test the modified calendar, we did the following:

How to Override Asset Depreciation

October 3, 2007 on 8:39 pm | by Marian Crkon | In Feature of the Week, How To Guides | 3 Comments |  Print |  Email

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 Add a New Operating Unit to Existing Configurations

September 5, 2007 on 1:06 pm | by Marian Crkon | In How To Guides | 20 Comments |  Print |  Email

The following steps are required to add new operating units to your existing organizational structure:

1. Revise the Organization Structure.

2. Define Sets of Books (optional).

3. Define Locations.

4. Define Organizations.

5. Define Relationships.

6. Define Responsibilities.

7. Set Profile Options for Each Responsibility linked to the new Operating Unit.

8. Run the Replicate Seed Data concurrent program.

9. Set profile options specific to operating units.

10. Define Inventory Organization Security (optional).

11. Implement the Application Products.

12. Secure Balancing Segment Values (optional).

13. Run the Setup Validation Report (recommended).

14. Implement Document Sequencing (optional).

15. Define Intercompany Relations (optional).

16. Set the top reporting level (optional).

17. Set up conflict domains (optional).

The trick with adding a new operating unit to the existing configurations was to figure out which configurations were operating unit specific. The attached three documents capture those steps for a typical installation of Oracle Projects and Financials.

• BR100 Application Configurations – Multi-Org
• BR100 Application Configurations – Financials
• BR100 Application Configurations – Projects

Another dilema we had was how many operating units to define in the same set of books. We started with the “best practice” approach to have one set of books for all operating units with the same 3 Cs (Chart of Accounts, Calendar and Currency). The challenge with this approach was how to manage some financial operations, including closing the periods, securing journals by operating unit, or defining default accounting rules. And because we needed to completely separate three legal entities, we ended up with multiple sets of books – one for operating unit. There are several improvements to the multi-organization structures in the release 12 but I will right about those after I can “put my hands on them”.

How to Use Oracle Account Generator for Project-Related Transactions

April 4, 2007 on 6:06 pm | by Marian Crkon | In How To Guides | 6 Comments |  Print |  Email

They say that if you know flexfields, you know Oracle Financials. I dare to say that if you know AutoAccouting and Account Generators, you understand Oracle Projects. For the lucky ones out there who are faced with an opportunity to set up these tools, here are some insights and experiences I’d like to share…

Check out this white paper I prepared for COLLABORATE07, which explains how to use the seeded Project Account Generators. Oracle provides these generators for you to start building your own processes for project-related transactions. It explains how Oracle initiates the Account Generators, what rules to follow when customizing them, and how to assign your customized processes to your accounting transactions. It also provides a detail step-by-step example of customizing a selected project-related transaction. The paper assumes that the reader is familiar with Oracle Financials and Projects accounting concepts and terminology and a working knowledge of Oracle Workflow Builder tool. Hope you find this information useful.

Account Generators are workflows that provide selected Oracle Applications the ability and flexibility to automatically generate accounting flexfield combinations using your own business rules. Some Oracle Account Generators come pre-seeded in the applications with pre-built rules. However, all Project Account Generators for project-related transactions must be always customized. Automatic generation of account combinations based on project-related information entered by users improves the accuracy and ease of data entry. When the business requirements change and new needs arise, Project Account Generator processes will have to be modified.

How to Customize Oracle iExpenses Workflows

January 4, 2007 on 8:55 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Here is a couple of examples of how to customize Oracle Internet Expenses workflows. You may need to change the seeded expense report notification routing or expense report approval method.

Changing Receivers of Certain Notifications

We wanted to re-route some notifications from the seeded performers (users, responsibilities, people) to a new performer (group of users). We created a new Oracle user with a new email distribution list as follows:

• Define a new OIEADMIN Role. As System Administrator, complete the following steps, create a new Oracle user
• Assign desired responsibilities to user OIEADMIN
• Run the Synchronize Local WF tables process every time you make changes to user setup.

Define Workflow Notification Performers

Perform these steps in Oracle Workflow Builder to set up/change expense report performers. These steps include recommendations for which item attribute to use for each notification.

• Load OIEADMIN Role. From the Files menu, select Load Roles from Database.
• In the Role Selection window, query the OIEADMIN role.
• From the Query Results region, select the required roles and click the Add button to add the role to the Loaded Roles region. Click OK to save the loaded role to the database. Save your work.
• Assign Role to the attribute: From the Navigator window, open the attribute. In the Navigator Control Properties, under the Attribute tab the Type in the main region should be set to Role. In the Default region, select the proper Value (role) and click Apply. Save your work. Assign a role for each of the attributes listed in the Performer Definitions table below.

Define Notification Performers. For each notification outlined in the Performer Definitions table below:

• Open the appropriate workflow process.
• In the workflow process, open the notification.
• In the Navigator Control Properties window, click the Node tab.
• Set Performer Type as Constant instead of Attribute.
• Choose OIEADMIN as Value.
• Click Apply and save your work.

Note: To directly link a role to a notification, Set Performer Type as Constant instead of Attribute. Then, select the role OIEADMIN. By using the Constant type, you have more flexibility. The table below lists the notifications, the seeded performer for each workflow process, and new performer

Change the Find Approver Method

Perform these steps in Oracle Workflow Builder to change the Find Approver method.

• Open the ‘Expenses’ item type from the database
• In the Navigator window, expend Expenses and Processes folders.
• Open (double-click) the manager (Spending) Approval Process
• Open the Find Approver function
• In the Navigator Control Properties window, click the Node Attributes tab.
• In the Value field, select your approval method (e.g. One Stop Then Go Directly)
• Click Apply and save your work. Click OK to save the workflow file to the database.

How to Use XML Publisher to Generate Oracle Reports in Excel and PDF

December 31, 2006 on 5:49 pm | by Nancy Chung | In How To Guides | 112 Comments |  Print |  Email

In the past, Oracle E-Business Suite Customers seldom used outputs of the seeded Oracle reports because they were unfriendly, and frankly, hard to use. Unfriendly because the generated output was always fixed width courier only font, contained no graphics, and could only be in black and white. Hard to use because the only output was text. In the business world, we all know how important the final output is and text only is not even an option. Businesses require PDF and XLS output that support tables, charts, graphics, color, etc.

Today, Oracle offers a solution that enables our Oracle E-Business Suite Customers to turn the once unfriendly Oracle seeded reports into PDF and XLS outputs of color, graphics, and tables. The solution is XML Publisher. XML Publisher is a java based reporting tool that is bundled for free within the technology stack of the Oracle E-Business Suite. The concept is similar to that of “mail-merge” where the data and template are maintained separately until the two are “married” to generate the desired document. XML Publisher separates a report’s data and layout component into two manageable pieces while it supports the desired outputs of PDF, HTML, XLS and RTF. The prerequisites to use XML Publisher include:

• XML Data
• Report Template
• Registration of Data Definition and Template at XML Publisher

How does XML Publisher leverage on Oracle seeded reports? An Oracle seeded report is a report definition file that integrates both the data query and the report layout into one file. XML Publisher will use Oracle Reports for only the data query portion and will ignore the defined report layout. This will fulfill one of XML Publisher’s prerequisites: XML Data. So the question is, how do you set up the Oracle system to only use the data query portion of the Oracle report?

• Log into Oracle with System Administrator Responsibility
• Navigate to > Concurrent > Program > Define
• Search for the desired Oracle Report
• Change the Output Format from the default ‘Text’ to ‘XML’
• Take special note of the Program Short Name (you’ll need it later to register the data at XML Publisher)

How does it benefit Oracle E-Business Suite customers? XML Publisher provides full control of the layout and look and feel of the report to the Customers. This means the Customer will fulfill the second prerequisite, the Report Layout. Customers will leverage on familiar tools like Microsoft Word and Adobe Acrobat to create the report layout with reference to the XML Data Tags. So the question is, how does the Customer create the Report Layout? PDF Forms template are intended for Customers that require a fixed look and feel such as completed IRS forms and are created in Adobe Acrobat. RTF templates are intended for all other purposes and are created in Microsoft Word. In this example, we’ll go over how to create an RTF Template.To create an RTF Template, you’ll want to download the XML Publisher Desktop Utility and its prerequisite Java Runtime Environment. This enables you to create and test your Report Template in a localized environment, your computer, before registering it at Oracle XML Publisher. So where to download the XML Publisher Desktop Utility?

• Navigate to Oracle eDelivery Site
• Select Product Pack: Oracle Application Server Products
• Select Platform: MS 32 bit
• Click on the GO Button
• Select and Download Oracle XML Publisher Release 5.6.2 Media Pack for Microsoft Windows

The XML Publisher Desktop Utility is a plug-in to Microsoft Word. To create the RTF template, you must first load the XML Data File by clicking on the Data button and selecting Load XML Data. To get a sample XML data file, you can run the Oracle seeded report and download the output. Once the data file is loaded, you can make references to the XML Data elements. For example, if you want to reference the ‘Project_Number’ XML Data Element, the format you would type would be … The common used tags include:

• for-each | end for-each
• if | end if
• choose | when | end when | otherwise | end otherwise | end choose

Alternatively, you can use the Desktop Wizard functionality by clicking the Insert button and selecting Table/Form to drag and drop the desired XML Data Elements into the Template window. This will automatically generate a table for you.

You can test your RTF template with the loaded XML Data File by click on the Preview button and selecting one of the supported output formats. Once satisfied with the created RTF Template, the final steps is to register both the Data Definition and the Template at XML Publisher. The steps are:

Register the Data Definition:

• Log into Oracle with XML Publisher Administrator responsibility
• Navigate to > Home > Data Definitions
• Click on the Create Data Definition button
• The Code must be the Concurrent Program Shortname from above

Register the Template

• From Data Definitions, Navigate to > Templates
• Click on the Create Template button
• The Data Definition value should be the Data Definition you registered above

When the Customer runs this configured report through the Concurrent Manager, the Customer will follow the same steps to run the standard Oracle Report by selecting the report in the Single Request Screen and entering the desired run parameters. Now, the Customer has one additional option to select the desired output format of the report. From the Single Request Screen, click on the Options button and select the desired output.

How to Reclass Projects and Tasks En Masse

December 20, 2006 on 9:20 pm | by Marian Crkon | In Feature of the Week, How To Guides | 1 Comment |  Print |  Email

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.

Learn to Love the Folders

December 8, 2006 on 9:28 pm | by Marian Crkon | In How To Guides | 10 Comments |  Print |  Email

I love the folders in Oracle Applications 11i. They are a great way to modify the forms without customizing them. It is too bad they are so poorly advertised or documented. As a result, only few users actually understand and use them. If you haven’t learned yet how to love the folders, this article is for you.

What is a Folder?

First of all, how do you even know the window you are using is a folder form? Folders are provided only with the core Oracle application forms. They allow you to change the form layout and content, define and run queries, export data to Excel without customizing the forms. Look for the yellow folder icon in the upper-left corner of the screen:

How to Define a Folder

When you open a folder form, the Folder option on the application top menu is enabled. You can use this option to configure the layout and settings of the folder. The Folder Tools option will give you a floating menu, from which you can execute some of the folder tasks. A default folder with default settings and seeded columns appears.

Creating a New Folder Version

To create a new folder, or update an existing one, use the options from the folder menu (not from the main applications menu. You can save a new folder as private, or public (other users can see it) version. Each public version must have a unique name! After you created your new folder, hide the columns you don’t need, show the ones you do, change column titles and size, and more. Hit Save As in the Folder Menu. The Save Folder options will be displayed. Here are few tips on how to set these options.

Autoquery

Make sure to select Never (unless you want to save your existing query – the data you currently see in the window). If you do save this query, make sure you reflect that in the folder name and don’t make this folder your default folder.

Open as Default

Do you want the current folder to be the folder that opens as default when you come back? If this flag is enabled, then this will be the default. It is a good idea to save the seeded folder first before your customized folder as default. Otherwise, you’d lose the seeded folder and can’t use it again.

Public

If enabled then the folder version can be used by other users with access to this form, not only the user who created the folder.

Customizing a Folder

You create or update versions of a folder by changing the following options as appropriate:

• What fields are displayed
• Size of fields
• Order fields are displayed

This can be done from the Folder menu or by using the Folder tools. When you are satisfied with the appearance of the folder, save the definition. Hide all the fields you do not need. This will dramatically improve the performance of the data queries and exports in your folder.

Defining a Folder Query

To update the query run by the folder, enter and run the required query and then save the folder. It is possible to view the new query by going to Folder > View Query. The query can be reset to the default by Folder > Reset Query.

For more complex queries it is possible to use a Clever Query. To do this:

• Put the folder in query mode
• Enter :a in any displayed field
• Run the query

This will display a query/where window. Enter the Where Clause you want to execute in the window. You can reference any field associated with the folder, not just those displayed. To check available fields use Help > Tools > Examine, then Block = FOLDER] and [Field = Use pick list to display available fields.

Folder Administration

Folders can be administered using the System Administrator responsibility. Navigate Application > Administer Folders > Find Default Folders.

From here you can determine which folders are used as default by users and responsibilities. When a folder is created and set as default, it is set for all responsibilities of that user. This can be changed so that different responsibilities, under the same user will invoke different default folders.

The custom folder definitions are referenced under a Folder Set. It is possible to change the owner of a folder and set folder options, such as Autoquery. This is very useful when you save as default a folder with a query, which now prevents you from selecting other data in the form.

Folder versions and details are stored in the tables FND_FOLDERS and FND_FOLDER_COLUMNS. see (7 TABLE DEFINITIONS). When a user invokes a folder, the user_name on the table FND_USERS is compared to the Created_by column in the FND_FOLDERS table. This will determine what folder versions are available.

Enhancement Tip:

It would be a great benefit to users if the applications came seeded with meaningful folders reflecting actual transaction workflows. The required columns should be displayed on the left (unlike in AP Payment Batches); the columns that do not make business sense together should not be displayed in the same folders (e.g. Revenue and Invoice Amounts for indirect projects). When exporting, it would be great to be able to export directly into Excel (instead of having to save the file first). Providing more logical choices to the users will encourage a wider use of this great application functionality.