The Feature

Sample DataLoad Templates for Oracle Financials

August 25, 2010 on 9:13 pm | by Marian Crkon | In How To Guides | 5 Comments |  Print |  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:

• Create Document Sequences DataLoad
• Create Document Sequence Assignments DataLoad
• Select Document Sequence Assignments Query

Sample DataLoad Templates for Oracle HRMS

July 19, 2006 on 9:58 pm | by Marian Crkon | In How To Guides | 2 Comments |  Print |  Email

DataLoad is a good option to upload data from Excel to Oracle Applications when other options, like Web ADI, are not available. DataLoad is practical when the volume of data to upload is too small to justify creating a custom script with the use of Oracle APIs, or too big to do it manually (let’s say between 10 and 1,000 records).

Below are several sample DataLoad templates for Oracle Human Resources (HRMS) I used before, 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.

Create HR Organizations

Create HR organization names and their classifications.

Navigation: US HRMS Manager: Work Structures > Organization > Description

Start Field: Name

DataLoad: Create HR Organizations

Create HR Organization Hierarchy

Create organization hierarchy, define parent organizations and their subordinates.

Navigation: US HRMS Manager: Work Structures > Organization > Description

Start Field: Name

DataLoad: Create HR Organization Hierarchy

Update Employee Email Addresses

Update employee email addresses in HR. This email address is used to route Workflow notifications via email and must be the same as employees primary address in your mail server (e.g. MS Outlook) if your Oracle WF Mailer configuration does not allow forwarding of email responses (see the Do Not Forward Oracle Notifications from Outlook! story).

Navigation: US HRMS Manager: People > Enter and Maintain

Start Field: Full Name in the Find Person window

DataLoad: Update Employee Email Addresses

Transfer Employees

Oracle Human Resources provides several flexible options to mass update employees and employee assignments. However, when transferring employees between organizations, you can only update their HR organizations and not their Default Purchasing Information when using Mass Updates. The Purchasing Information is critical for accurate approvals and accounting of employee requisitions and expense reports. This is where DataLoad came in handy.

Navigation: US HRMS Manager: People > Enter and Maintain

Start Field: Full Name in the Find Person window

DataLoad: Transfer Employees

Sample DataLoad Templates for Oracle Projects

March 11, 2006 on 5:22 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

I was little harsh on DataLoad in my last DataLoad story so here is to redeem myself. I use DataLoad anytime I have to create or update more than 10 records. Usually, DataLoad is an excellent option when other options like Web ADI are not available, feasible or practical, and the volume of data to upload isn’t too big (under 1,000 records). Last week for example, we had to update a couple of attributes on 200 project tasks. The volume was too small to justify creating a custom script and use the Oracle API, and too big to do it manually. So the DataLoad came to the rescue!

Below are several sample DataLoad templates for Oracle Projects I used before with descriptions and navigation instructions. Note that these are only samples. You may need to modify the spreadsheets based on your application configuration. The blue columns represent DataLoad commands, the yellow columns is where you paste your data. Hope you will find the templates useful.

Create Project Roles

Create Project Roles used in project Key Member assignments.

Navigation: Project Billing Super User: Projects > Setup > Projects > Roles

Start Field: Name

DataLoad: Create Project Roles

Create Project Tasks

Use this DataLoad for creating a small volume of project tasks. I use it all the time when creating Project Templates. Use CREATE_TASK or UPDATE_TASK APIs with a custom script to create larger volumes of tasks.

Navigation: Project Billing Super User: Setup > Project Templates > Tasks

Start Field: Task Number

DataLoad: Create Project Tasks

Create Agreements

Navigation: Project Billing Super User: Billing > Agreements

Start Field: Customer Name

DataLoad: Create Agreements

Baseline Projects in Funding Inquiry

Note that you can baseline funding in the Funding Inquiry screen only if the Baseline Funding Without Budget option is enabled for your projects.

Navigation: Project Billing Super User: Billing > Funding Inquiry.

Start Field: Project

DataLoad: Baseline Project in Funding Inquiry

Create Labor Costing Overrides Define labor rate overrides by employee. These rates are used by the labor distribution process to calculate labor costs.

Navigation: Project Billing Super User: Setup > Costing > Labor > Labor Costing Overrides.

Start Field: Employee Name

DataLoad: Create Labor Costing Overrides

Create Transfer Price Schedules

Define the transfer price schedule lines for your cross-charge rules. Manually define schedule name and description, and start the DataLoad at Line Number field.

Navigation: Project Billing Super User: Setup > Costing > Cross-Charge > Transfer Price Schedules

Start Field: Line Number

DataLoad: Create Transfer Price Schedules

Create Non-Labor Rate Schedule

Define non-labor bill rates and markups used by the revenue and invoice generation processes in Oracle Project Billing. Manually define the schedule header, click on Non-Labor tab, and start at Expenditure Type field.

Navigation: Project Billing Super User: Setup > Expenditures > Rate Schedules

Start Field: Expenditure Type

DataLoad: Create Non-Labor Rate Schedule

Create Project Employee Bill Rate Overrides

Define project specific employee billing rate overrides used by the revenue and invoice generation processes in Oracle Project Billing.

Navigation: Project Billing Super User: Projects > Options > Bill Rates and Discount Overrides > Employee Bill Rate and Discount Overrides

Start Field: Employee Name

DataLoad: Create Project Employee Bill Rate Overrides

Create Project Lookup Set

Define project lookup sets used in AutoAccounting rules or Account Generator. Manually define lookup set Name, and start at the Intermediate Value field.

Navigation: Project Billing Super User: Projects > Setup > AutoAccounting > Lookup Sets

Start Field: Intermediate Value

DataLoad: Create Project Lookup Set

Update Project Lookup Set

Similar to the DataLoad above, but this one updates the existing lookup sets values.
Navigation: Project Billing Super User: Projects > Setup > AutoAccounting > Lookup Sets

Start Field: Intermediate Value

DataLoad: Update Project Lookup Set

Create Contingent Worker Expenses as Pre-Approved Batch

The preferred option for upload project transactions is the Transaction Import WebADI Integrator. However, as of the release 10.5.10, it does not distinguish between Person Types, and does not successfully upload Contingent Worker transactions.

Navigation: Project Billing Super User: Expenses > Pre-Approved Batches > Enter

Start Field: Employee Name

DataLoad: Create Contingent Worker Expenses as Pre-Approved Batch

Create Percent Complete Amounts

Navigation: Project Billing Super User: Project Status Inquiry > Percent Complete

Start Field: Project Number

DataLoad: Create Percent Complete Amounts

Visit DataLoad website for product information, free downloads, user guides and more sample spreadsheets.

I Will Love DataLoad if I Have To

March 2, 2006 on 8:11 pm | by Marian Crkon | In Reviews | Enter Comments |  Print |  Email

I was really surprised to see that a white paper about DataLoad is scheduled to be presented at the upcoming OAUG conference COLLABORATE 06 in Nashville, Tennessee (April 23 - 27, 2006). When did DataLoad become the official Oracle integration tool?

I embraced DataLoad in its early days in late nineties and it saved my rear many times. However, all this time I’ve been thinking of DataLoad as a provisional “copy and paste” solution until Oracle, or somebody else, comes up with a better idea. I guess there is a place for a nimble data management tool like DataLoad for uploading smaller volumes of data. But I would not recommend it for anything more than 10 to 1,000 records. I’d rather see Oracle Web ADI Integrators or “Define-Your-Own-Integrator” integrator for all existing Oracle Applications APIs. I hope Oracle stays committed to enabling integration between MS Excel and Oracle Applications.

Meanwhile, I will love DataLoad. I will keep creating new templates and keep watching for hours as DataLoad copies and pastes data into the applications. While you are waiting with me, visit DataLoad for product information, user guides, sample spreadsheets and free downloads.