Sample DataLoad Templates for Oracle Financials

August 25, 2010 on 9:13 pm | by Marian Crkon | In How To Guides | 6 Comments | Print Print | Email Email

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:

E-Business Suite Financial Integration

May 12, 2008 on 9:50 am | by Melanie Cameron | In How To Guides | 1 Comment | Print Print | Email Email

Oracle E-Business Suite (EBS) consists of tightly integrated modules that result in a company’s financial statements. Tracking transactions through the system back to the source, then to the setups that affected the way the source transactions behaved is difficult at best. The Oracle EBS Integration Overview white paper (based on R11.5.10) will help you understand where these transactions are coming from, back through the interfaces to the transactions, then even farther into the setups dictating their behavior.

Sample Ad-Hoc Queries for Oracle Financials

January 11, 2008 on 8:06 pm | by Marian Crkon | In How To Guides | Enter Comments | Print Print | Email 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 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 Print | Email 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).
Accounting Calendar Screen

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.
    • PA Periods Screen

    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 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 Print | Email 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.

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”.

Oracle Published Release Content Documents for eBusiness Suite Release 12

January 16, 2007 on 10:01 am | by Marian Crkon | In Oracle Press, Worth Noting | 4 Comments | Print Print | Email Email

Hooray, the wait is finally over all users and implementors waiting to see what is new in Oracle eBusiness Suite Release 12. Read about the new R12 features in the the content documents available in the Metalink Note 404152.1 [Click on the note link and login to Metalink first to access these links]:

Oracle E-Business Suite R12 Pre-Release Product Demos

August 24, 2006 on 1:05 pm | by Marian Crkon | In Oracle Press | 4 Comments | Print Print | Email Email

In late September, Oracle will be hosting a series of pre-release demonstrations of E-Business Suite R12 applications. Through this program, OAUG members will have the unique opportunity to preview new features and functionality of selected R12 applications before they are generally available. The demonstrations will be held remotely via Web conferencing, and will be conducted by members of the Oracle Applications development team who have a deep understanding of the products.

To participate in the demonstrations, user group members must sign the Oracle Confidential Disclosure Agreement (CDA), and must have licensed and implemented (or be in the process of implementing) a current version of the Oracle Applications product(s) being demonstrated. Oracle will use the customer contact information submitted via the online enrollment form to send the CDA and other pre-demo materials.

Participation is limited to OAUG members in good standing, and each session is limited to 50 dial-in ports, to be distributed on a first-come, first-served basis. To enroll in the individual demonstrations, please complete the online enrollment form by Friday, September 8, 2006.

Oracle will be soliciting feedback from the program participants and may ask you to participate in marketing activities associated with the demonstrations, including providing customer quotations that may be used for R12 marketing purposes, such as presentations at Oracle OpenWorld 2006.

More information on the E-Business Suite R12 user group/special interest group demonstration program including detailed descriptions for each demo and a demo schedule, is available here.

New Features in Oracle Financials and Projects 11i.10

August 2, 2006 on 6:28 pm | by Marian Crkon | In Worth Noting | Enter Comments | Print Print | Email Email

Here is an executive summary of New Features in Oracle Financials and Projects 11i.10 for your reference. I realize I am a year late with this post but since there are still people who are yet to upgrade to release 11i.10 and may be interested in the topic [and since I already have the document ready] and I am going to post it anyway.

The purpose of this document was to provide a consolidated list of new features, functional pre-requisites, and configuration steps delivered with the Oracle Applications 11.5.10 (Financials Family Pack F and Oracle Projects Family Pack M) as they related to our existing or Oracle Applications. It was not the intent of this document to include all features available in the above Family Packs, but focus on the main functionality changes. Likewise, it was not the intent of this document to outline the patch application instructions and list of required patches, but rather focus on the functional impact of the upgrade.

Please refer to the following Oracle release notes on Metalink for complete details (Metalink login is required):

Financials

Projects

Sample Operational Analysis Questionnaires for Oracle Financials and Projects

May 17, 2006 on 9:20 pm | by Marian Crkon | In How To Guides | 19 Comments | Print Print | Email Email

During an operational analysis phase of an ERP implementation project you need to establish a detail project scope and business requirements. Attached you will find several sample questionnaires that may help you conduct discussions with your business users about how to implement Oracle eBusiness Suite applications. Use the documents as samples to define your own list of questions.

Operational Analysis Questionnaire – Accounts Payable

Operational Analysis Questionnaire – Accounts Receivable

Operational Analysis Questionnaire – Contingent Worker Management

Operational Analysis Questionnaire – Fixed Assets

Operational Analysis Questionnaire – General Ledger

Operational Analysis Questionnaire – Project Accounting

Operational Analysis Questionnaire – Resource Management

Operational Analysis Questionnaire – Time and Expenses

How to Find Active Employees, Users and Responsibilities

May 17, 2006 on 11:01 am | by Marian Crkon | In How To Guides | Enter Comments | Print Print | Email Email

Here is a query you may find useful when maintainig users, doing SOX 404 controls audit, creating new users for existing employees in Oracle eBusiness Applications . Use it to find active employees, contingent workers and their user accounts and responsibilities. You will need to know the person_type_id for employees and contingent workers.

Employees, Users and Responsibilities Query

Next Page »

Powered by WordPress with Pool theme design by Borja Fernandez.
Entries and comments feeds. Valid XHTML and CSS. ^Top^