The Feature

How To Set Flexible PO Approval Limits

September 8, 2010 on 8:25 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

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

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

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

Sample DataLoad Templates for Oracle Financials

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

How the PA Date and GL Date Are Determined in Oracle Projects

July 26, 2006 on 10:57 pm | by Marian Crkon | In How To Guides | 3 Comments |  Print |  Email

You need to know how the Project Accounting (PA) Date and the GL Date are assigned in Oracle Projects in order to understand how your project (P&L) reporting and GL reconciliation work. The dates are determined differently for different project transactions based on each transaction type. Oracle Projects determines the dates as follows:

Timecard, Usage, Miscellaneous, and Supplier Invoice Adjustments

The PA date is set to the transaction expenditure item date if that date falls in a PA period with a status of Open or Future. If the expenditure item date falls in a closed PA period, then the PA date is set to the start date of the earliest open or future enterable PA period that follows the expenditure item date.

The GL date is set to the end date of the earliest GL period that includes or follows the PA date of the cost distribution line and has a status of Open or Future according to the period status in Oracle General Ledger (or in Oracle Projects when enhanced period processing features are enabled). Oracle Projects derives the GL Date when you run the cost distribution processes.

Expense Reports (created in iExpenses)

The Oracle Payables Invoice Import program uses one GL date for each expense report loaded into Oracle Payables from Oracle iExpenses. All project cost distribution lines for an expense report use the same GL date.The GL date is set to the end date of the earliest GL period that has a status of Open or Future according to the period status in Oracle General Ledger.

The PA date is set to the transaction expenditure item date if that date falls in a PA period with a status of Open or Future. If the expenditure item date falls in a closed PA period, then the PA date is set to the start date of the earliest open or future enterable PA period that follows the expenditure item date.Oracle Projects derives the PA date for each expense report cost distribution line when you interface the expense reports from Oracle Payables.

Supplier Invoices Interfaced from Payables

The PA date is set to the transaction expenditure item date if that date falls in a PA period with a status of Open or Future. If the expenditure item date falls in a closed PA period, then the PA date is set to the start date of the earliest open or future enterable PA period that follows the expenditure item date.Oracle Projects derives the PA date for each supplier invoice cost distribution line when you interface the supplier invoice from Oracle Payables.

When you interface supplier invoices from Payables, Oracle Projects copies the GL date for each supplier invoice cost distribution line from the GL date entered for the invoice distribution in Oracle Payables. Oracle Projects derives the PA date for each supplier invoice cost distribution line when you interface the supplier invoice from Oracle Payables.

Draft Revenue

The PA date is set to the end date of the earliest PA period that includes or follows the revenue accrue through date and has a status of Open or Future.

The GL date is set to the end date of the earliest GL period that includes or follows the PA date of the draft revenue and has a status of Open or Future according to the period status in Oracle General Ledger (or in Oracle Projects when enhanced period processing features are enabled ). Projects derives accounting dates during the revenue generation process.

Draft Invoices

The PA date is set to the end date of the earliest PA period that includes or follows the invoice date and has a status of Open or Future.

The GL date is set to the end date of the earliest Open or Future Oracle Receivables GL period that includes or follows the invoice date of the draft invoice. The GL Date is derived when the invoice is generated.

Example

Let’s take for example a billable timecard entered in June 2006. And let’s assume the PA Period and GL Period are the same and JUN-06 is open:

• Expenditure Item Date = June 19 through June 23
• Week Ending Date = June 25 (Sunday is the of that week based on the Implementation Options)
• The PRC: Distribute Labor Costs process will assign the expenditure PA Dates and GL Dates the same as Expenditure Item Dates and both the PA Period and GL Period will be set to JUN-06.
• The PRC: Generate Draft Revenue process will assign the revenue PA Date and GL Date based on the Accrue-Through Date selected during the process (e.g. June 30, 2006) and both the PA Period and GL Period will be set to JUN-06.

Now, let’s assume the timecard was transferred between two projects and billed to a client in July 2006 and JUN-06 period was already closed in the PA calendar and GL calendar in Projects. The dates will be derived as follows:

• Expenditure Items Date will stay June 19 through June 23.
• Week Ending Date will stay June 25.
• The PRC: Distribute Labor Costs process will change the expenditure PA Date and GL Date to July 1, 2006, and the PA Period and GL Period will be changed to JUL-06.
• The PRC: Generate Draft Revenue process will change the revenue PA Date and GL Date to July 31, 2006 (based on an Accrue-Through Date selected during the process), and both the PA Period and GL Period will be changed JUL-06.
• Project Invoice that includes the timecard in have the PA Date and GL Date of July 31, 2006 (based on a bill-through date of July 31, 2006) and open AR period JUL-06.

Implementation Tip: In order to correctly report on such adjustments as described above and show all debits and credits (net zero transactions), pull your transaction amounts from the expenditure distributions table, and not the expenditure items table in your reporting data mart.

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

How To Enable Oracle Project Status Change Workflow

July 18, 2006 on 8:30 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

In Oracle Projects, you can use the Project Status Change Workflow to automatically route project status changes for approval. The Project Status Workflow needs to be enabled for the project status and the project type. When a project status changes, the seeded PA Project Workflow sends an approval notification to the requesting employee’s HR supervisor who will receive a notification (or an email if WF Mailer is enabled) to change the project status. You can customize the PA Project Workflow according to your business requirements. For more details, see the Oracle Projects Implementation Guide (Oracle OTN login required).

Below is a sample workflow of project status changes. A Project Accountant creates a project and manually sets the project status to Submitted. The workflow process is initiated. A Project Accounting Manager (Project Accountant’s HR supervisor) receives an email requesting his/her approval of a new project. If the manager approves and the workflow process is successful, the project status is changed to Approved.

Similarly, when a project needs to be closed, the Project Accountant will manually change the status from Approved to Pending Close. A second workflow process sends an email to the PA Manager, verifies that the manager approved the project closure, and changes the status to Closed.

Following are the required configuration steps to enable the Project Status changes by Workflow:

Enable Project Types for Workflow Project Status Changes

As Project Billing Superuser, navigate to Setup > Projects > Project Types. Define your project type, or update an existing one. Select the ‘Use Workflow for Project Status Changes’ flag to initiate Workflow for all workflow-eligible project statuses in projects with this project type.

Project Statuses for Workflow Status Changes

As Project Billing Superuser, navigate to Setup > System > Statuses. In our example above, we would need to enable following statuses for workflow: Submitted and Pending Close. The value for Item Type is always ‘PA Project Workflow’ for Process is ‘Project’. Using the Success Status and Failure Status is how you control the next status based on the approver’s action.

Few comments in closing. Using the Project Status Change Workflow is great if you create and approve a lot of projects, or project creation is de-centralized. It enforces consistent rules and keeps an audit trail of changes. Having an automatic emails and notifications sent out by the system brings transparency to the process.

Some of the features we did not like included users’ inability to ”withdraw” a project status change. Once you choose a project type that is enabled for workflow, the process is triggered and you cannot “roll it back”. Also, and this applies to any Oracle Workflow notification, if there is anything wrong with your setup, for instance an employee does not have a HR supervisor, or user account is not associated with an employee, or user name changes were not synchronized in the Workflow tables, the approval notifications are routed to SYSADMIN without any acknowledgement to you.

The workflow messages can be little misleading. After a successful status change, the message always says “Project Approved”, even though the status change was to close the project. It would be more meaningful to say: “…project status was successfully changed to ‘actual status name’…”

Automatic Timecard Approval for Terminated Employees

July 18, 2006 on 8:18 pm | by Marian Crkon | In Feature of the Week | Enter Comments |  Print |  Email

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

June 5, 2006 on 9:02 pm | by Marian Crkon | In Feature of the Week | 3 Comments |  Print |  Email

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

Sample Operational Analysis Questionnaires for Oracle Financials and Projects

May 17, 2006 on 9:20 pm | by Marian Crkon | In How To Guides | 21 Comments |  Print |  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 |  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

How to Get the Expense Report Template Details

May 8, 2006 on 10:13 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Here is an ad-hoc query you may use to get the expense report template details from Accounts Payable, including the mapping of expense type names to GL accounts and project expenditure types. As of 11i.10, it is not possible to export this data from the Expense Report Templates form, nor there is any report that would provide this information. It is nice to have this listing in Excel when reviewing the expense types, making chart of account changes, or updating your accounting rules.

Expense Report Template Details Query