The Feature

How To Use Oracle Personalization Framework to Customize Fields and Messages

April 18, 2006 on 2:42 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

You can use Oracle Personalization Framework to customize standard fields and messages in web-based Oracle modules (iProcurement, iExpenses, iStore, etc.). Here is one example of personalizing expense report fields in iExpenses to make them more intuitive to the end users.

Move the Details Image – Update Expense Report: Cash and Other Expenses Page

1. As System Administrator: Set ‘Personalize Self-Service Defn’ to Yes.
2. Log out of the system.
3. Navigate to Expenses Home page using your iExpenses responsibility
4. Create and submit an expense report
5. In the Update Expense Report: Cash and Other Expenses page, click the ‘Personalize Business Expenses Table’.

6. In the Choose Personalization Context page, click Apply.
7. In the Personalization Structure page, click on Reorder next to the ‘Table: Use this table to’ item.

8. Click on Personalize under the Function section
9. Move Details item up right under Line item. Click Apply.

10. In the Personalize Region page, click Return to Application.

Hide the Itemize Image

11. …continued from Step 10 above.
12. In the Update Expense Report: Cash and Other Expenses page, click the ‘Personalize Business Expenses Table’.
13. In the Choose Personalization Context page, click Apply.
14. In the Personalization Structure page, click on Edit next to the Image: Itemize item.
15. In the Personalization Properties page, select False for the Rendered row at the Function: OIE Expenses Entry Flow level, then click Apply.
16. In the Personalize Region page, click Return to Application.

Hide the Line – Level Attachment Image

17. …continued from Step 16 above.
18. In the Update Expense Report: Review page, click the ‘Personalize Other Expenses Table’.
19. In the Choose Personalization Context page, click Apply.
20. In the Personalization Structure page, click on Edit next to the Column: (AttachmentsColumn) item.
21. In the Personalization Properties page, select False for the Rendered row at the Function: OIE Expenses Entry Flow level, then click Apply.
22. In the Personalize Region page, click Return to Application.

Move the Details Image – Create Expense Report: Cash and Other Expenses Page, Mileage Tab

23. …continued from Step 22 above, the personalization framework is still enabled.
24. Click on Mileage Expenses tab.
25. In the Mileage Expenses window, click the ‘Mileage Table’ link.
26. In the Choose Personalization Context page, click Apply.
27. In the Personalization Structure page, click on Reorder next to the ‘Table: Use this table to’ item.
28. Click on Personalize under the Function section
29. Move Details item up right under Line item. Click Apply.
30. In the Personalize Region page, click Return to Application.
31. Continue creating the expense report.

Move the Details Image – Create Expense Report: Review Page, Business Expenses Table

32. …continued from Step 31 above, the personalization framework is still enabled.
33. In the Business Expenses table, click the ‘Personalize Other Expenses Table’ link.
34. In the Choose Personalization Context page, click Apply.
35. In the Personalization Structure page, click on Reorder next to the ‘Table: Use this table to’ item.
36. Click on Personalize under the Function section
37. Move Details item up right under (DateColumn) item. Click Apply.
38. In the Personalize Region page, click Return to Application.
39. Continue creating the expense report.

Personalize Submission Instructions

40. …continued from Step 39 above, the personalization framework is still enabled.
41. In the Confirmation page, click the Personalize Submission Instructions Header link in the Submission Instructions region.
42. In the Choose Personalization Context page, enter Your Business Group name in the Organization field and click Apply.
43. In the Personalize Region page, click Personalize for the Raw Text item.
44. In the Personalize Raw Text page, select False for the Rendered row at the Site level, then click Apply.
45. In the Personalize Region page, click Create Item for the Header: Submission Instructions item.
46. In the Create Item page, select the ‘Raw Text ‘ value from the Item Style list.
47. Complete the page according to your business requirements:
a. ID = YOUR_COMPANY_SUBMISSION_INSTRUCTIONS
b. Text: Include your customized message here.
c. Click Apply
48. In the Personalize Region page, click Personalize for the message you created.
49. In the Personalize Raw Text page, enter a message in the Text field for the appropriate level, then click Apply.
50. In the Personalize Region page, click Return to Application.

Go-Live Checklist for Oracle Financials

April 4, 2006 on 9:30 pm | by Marian Crkon | In How To Guides | 2 Comments |  Print |  Email

Following is a sample Oracle Financials Go-Live Checklist. It should give you an idea for a sequence of events that occur during the migration to Oracle Applications. The list does not include all application configuration steps, only the “eleventh hour” steps to take the system live. The example covers the applications we needed to set up, including Oracle General Ledger, Oracle Payables, Oracle Receivables, Oracle Projects, Oracle Assets, Oracle Time and Labor, Oracle Internet Expenses and Oracle System Administration. This list is by no means a “fit for all” solution. You can use it as reference to create your own checklist.

Complete HR configurations

1.1 HR Complete HR configuration setups

1.2 HR Complete HR configurations and conversions

• Define New Locations and Organizations
• Define the Organizational Hierarchy
• Define Job Titles and Positions
• Create Employees and Employee Assignments

1.3 AR: Get Customers extract from the Legacy System

1.4 AP: Get Suppliers from the Legacy System

Complete Configuration Changes for all Financial Modules: GL, AP, AR, PA, FA, OTL, IE, SA)

2.1 GL: Define Oracle Chart of Accounts with new values

2.2 GL: Define GL cross-validation rules to allow new values

2.3 AR: Define conversion Transaction Types

2.4 AR: Define AR Receipt Source

2.5 AR: Define lockbox

2.6 AP: Add new Supplier Types

2.7 AP: Add new Pay Groups

2.8 AP: Add a Employee Reimbursement Bank Account

2.9 IE: Enable Expenditure Types in Projects for Expense Report entry.

2.10 IE: Create Expense Report Template

2.11 IE: Define Financial Options (in Payables)

2.12 IE: Define Payables Options

2.13 IE: Assign Manager Signing Limits

2.14 IE: Set OIE Profile Options

2.15 IE: Enable the display of projects and task using AK Developer responsibility

2.16 IE: Set up an Offline Expense Spreadsheet

2.17 IE: Save an offline spreadsheet to the Production database

2.18 IE: Build the Project Expense Report Account Generator.

2.19 IE: Save the Project Expense Report Account Generator to Production database.

2.20 GL: Assign a custom workflow to the Project Expense Report Account Generator process

2.21 FA: Define new Asset Categories

2.22 FA : Define new Asset Locations

2.23 PA: Define Projects Implementation Options

2.24 PA: Add new Expenditure Categories

2.25 PA: Add new Revenue Categories

2.26 PA: Add new Expenditure Types

2.27 PA: Define Transaction Source

2.28 PA: Define Project Bill Rate Schedules

2.29 PA: Define Product Code

2.30 PA: Define Labor Costing Rules

2.31 PA: Define Burden Costing Rules

2.32 PA: Define Cross-Charge Rules

2.33 PA: Define new Event Types

2.34 PA: Enable Percent Complete Billing Extensions

2.35 PA: Define Invoice Formats

2.36 PA: Define Resource Lists with new values

2.37 PA: Define Project Classifications

2.38 PA: Define Project Types

2.39 PA: Define Project Templates

2.40 PA: Define Auto Accounting Lookup Sets

2.41 PA: Define Auto Accounting Rules for new transactions

2.42 PA: Define Auto Accounting Assignments

2.43 PA: Create Admin Projects

Perform Data Conversions of Master Data

3.1 AR: Review and clean up customers

3.2 AP: Review and clean up suppliers and contractors

3.3 AR: Upload customers to Oracle Receivables

3.4 AP: Upload suppliers to Oracle Payables

3.5 AR: Review and validate customers and contacts in Oracle Receivables

3.6 AP: Review and validate suppliers in Oracle Payables

3.7 HR: Upload contingent workers to Oracle HR. Associate contingent workers with AP suppliers

3.8 SA: Create users and assign to responsibilities

3.9 AR: Create customer billing contacts

3.10 FA : Run depreciation for Last Period in the Legacy System

3.11 FA : Get Assets file from the Legacy System

3.12 PA: Extract projects from the Legacy System

3.13 PA: Get project funding amounts

3.14 PA: Ger billing rates for T&M projects

3.15 PA: Upload projects to Oracle Projects

3.16 OTL and iExpenses Go-Live

Execute Conversion of Opening Balances

4.1 AR: Get unapplied AR receipts from the Legacy System

4.2 AR: Get pending AP invoices from the Legacy System

4.3 FA: Close Last Period in Oracle Assets

4.4 FA: Upload legacy assets to Oracle Assets

4.5 PA: Complete Last Period Billing in the Legacy System

4.6 PA: Complete Last Period Billing in Oracle

4.7 GL: Close Last Period in the Legacy System GL

4.8 GL: Close Last Period in Oracle General Ledger

4.9 AR: Get open AR transactions from the Legacy System

4.10 AR: Upload open AR transactions to Oracle Receivables. (Make sure you use Transaction Type with ‘Post to GL’ flag set to ‘No’)

4.11 AR: Upload unapplied AR receipts to Oracle Receivables

4.12 AR: Interface open AR cash balances in GL

4.13 AR: Reverse opening AR cash balances in GL

4.14 PA: Get “Billed” project balances from the Legacy System

4.15 PA: Upload project funding amounts to Oracle Projects. Baseline funding and create revenue budgets.

4.16 PA: Upload bill rate schedules to Oracle Projects

4.17 PA: Get “Unbilled” project items from the Legacy System

4.18 PA: Get opening project expenditure balances from the Legacy System

4.19 PA: Get opening project revenue balances from the Legacy System

4.20 PA: Disable the Interface Usage Costs to GL flag in Projects Implementation Options

4.21 PA: Upload opening project expenditure balances as Miscellaneous Expenditures to Oracle Projects

4.22 PA: Distribute Miscellaneous Costs in Oracle Projects. Run the interface usage and miscellaneous costs to GL process. No JE should be created.

4.23 PA: Disable the Interface Revenue to GL flag in Projects Implementation Options

4.24 PA: Disable the ‘Open Receivable’ and ‘Post to GL’ flags for ‘Projects Invoice’ transaction type in Oracle Receivables

4.25 PA: Upload opening project revenue balances as Revenue Events to Oracle Projects (Revenue Amount is populated)

4.26 PA: Generate project revenue in Oracle Projects. Run the GL interface processes. No JE should be created.

4.27 PA: Upload Billed Project Balances as Billing Events to Oracle Projects (Bill Amount is populated)

4.28 PA: Generate draft project invoices. Interface draft invoices to receivables them to Oracle Receivables. No AR transactions should be generated.

4.29 FA Upload assets to Oracle Assets

4.30 AP: Upload pending AP invoices to Oracle Payables

4.31 AP: Interface AP invoices to General Ledger, reverse the JE.

4.32 OA Set the Revenue to General Ledger flag in Projects Implementation Options back to ‘Yes’

4.33 AR: Set the ‘Open Receivable’ and ‘Post to GL’ flags for ‘Projects Invoice’ transaction type in Oracle Receivables to ‘Yes’.

4.34 PA: Upload legacy unbilled timecards as timecards to Oracle Projects

4.35 PA: Distribute legacy labor costs in Oracle Projects

4.36 PA: Upload unbilled expense reports and other costs as Miscellaneous Transactions to Oracle Projects

4.37 PA: Distribute legacy miscellaneous costs transactions in Oracle Projects

4.38 PA: Generate revenue for unbilled legacy transactions

Start Regular Processing in Oracle Production

5.1 PA: Import new timecards from OTL to Projects

5.2 AP: Validate and import from iExpenses to Payables

5.3 PA: Start running the project processes

5.4 AR: Star applying cash in Oracle Receivables

5.5 GL: After First Period is closed in Assets, create a reversing JE in Oracle GL to reverse asset cost and depreciation through Last Period

What To Remember Not To Forget When Transferring Knowledge

March 25, 2006 on 9:18 am | by Marian Crkon | In Ideas and Opinions, Worth Noting | Enter Comments |  Print |  Email

Let me post this while I remember not to forget. The PeoplesoftConer posted a smart little piece with a list of tasks I always forget to remember when in the middle of knowledge transfers. Since the list is applicable to any software package, I will try to remember not to forget it:

• Contact list with roles and responsibilities
• Architecture Diagram for Dev, Test and Production that shows the servers and various PeopleSoft Internet Architecture components
• Server List with description/purpose, IP addresses, memory, CPU, and disk capacity
• Database Instances and corresponding PS Environments
• Documentation index – what documentation exists and where it is
• Password lists, including server, application and database accounts
• Customization lists
• Issues list
• Current tasks and projects that the person you’re replacing is involved with
• Access to servers, applications
• Remote access software and instructions for using it
• Interface diagram
• Time reporting procedures
• Production Change Management procedures
• Migration procedures

Thanks PeopleSoft Corner!

How To Get a List of Project Billing Rates

March 16, 2006 on 8:00 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Sometime you may need to get a list of previously used billing rates (e.g. during the last year) in order to determine the future rates. Since there is no standard report in Oracle Project Billing 10i.10 that provides that information, here is a simple query to accomplish the task.

Bill Rates by Job Title Query

7 Guaranteed Questions from New Oracle Users

March 14, 2006 on 7:26 pm | by Marian Crkon | In Ideas and Opinions | 2 Comments |  Print |  Email

I love novice Oracle Applications users. They help me keep my perspective fresh and remind me that things are not always seen by others the way I see them. Like anyone else involved in this business for a long time, I have developed an “operational blindness” where I avoid certain pitfalls by memorizing the ways around them. Here are few sample questions you are guaranteed to hear when introducing new users to the applications. Go ahead to add your fun user experiences as comments below.

• How do I get in the system? You are thinking that’s silly but you’d be amazed in how many different ways users get to Oracle Applications: desktop icons, instance maps, links on company intranets, links sent via email, bookmarks saved in browsers, and even browser’s recent addresses…
• How do I change the date format? Once a new user gets in the system, that’s the guaranteed next question. I realize Oracle sells applications in a hundred or so countries, so it is impossible to have a common format that fits everybody.
• How do I get to my stuff? Thank goodness for those menus. They make those who can navigate them look like geniuses. I understand there are some major UI improvements coming up in Release 12. All I can say is: keep it simple!
• Why do I see things I don’t need? That’s the price you pay for out-of-the-box applications. There is some flexibility to remove menus and functions, and when available, use folders to customize the forms any way you want. But in general, there will always be fields that others use and you don’t.
• Where is my report? This is my favorite. There may be a 100+ standard reports in each Oracle module, but it always comes down to this question and you have to produce that one report that users are familiar with.
• How do I get my data into Excel? Whether we like it or not, Excel is THE tool of our times. We can argue that the data is available online or on the web, but somehow, it always ends up in Excel. It is good to see that Oracle acknowledged the fact and keeps providing easier integration with Excel (WebADI, Folders, XML Publisher).
• Is this an entry mode or query mode? We have all seen the “%” records people entered assuming they were in a search mode. It takes some time before that CTRL-F11 becomes second nature. A related question: How do I stop that run away query submitted by accident…?

How To Find Unbilled Project Expenses

March 14, 2006 on 9:06 am | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Here is a couple of tips on how to find unbilled project expenditures in Oracle Projects (11i.10). There is an excellent online Expenditure Inquiry screen, which lets you submit searches with a whole lot of parameters and define your own folders to export expenditure transactions into Excel.

But if the volume is too big to export, or you need to find unbilled expenditures across broader parameters like Project Type, Project Organization, Customer, or Expenditure Type Classes, use an ad-hoc SQL query. I provided a sample below.

Unbilled Project Expenditures

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.

How To Calculate Revenue Without Actually Generating It

March 9, 2006 on 12:16 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

In Oracle Project Billing (10.5.10), you can calculate forecast labor revenue for your time and material projects without running the actual revenue generation process. Navigate to the Submit Requests window (Other > Requests Run) and submit the PRC: Compute Forecast Labor Revenue process.

The system generates a report that shows revenue forecasts and potential exceptions, when for instance timecards are not costed, or there are missing billing rates, or projects are not funded, etc. Currently, you can run the report by project and date for billable labor only (no expenses).
Enhancement Tip:

Rename the request from PRC to MGT – it is a report, not a program. Also, provide additional parameters like Organization, Customer, or Project Manager. Under the Exception Report, show exception reasons.

Little Loved Lookup Set Maintenance

March 7, 2006 on 10:27 pm | by Marian Crkon | In Feature of the Week | Enter Comments |  Print |  Email

You may encounter this feature if using Oracle Projects 10.5.10.

The Feature

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

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

Enhancement Tip

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

How To Set Up Oracle iExpenses

March 6, 2006 on 10:55 pm | by Marian Crkon | In How To Guides | 104 Comments |  Print |  Email

Here is a summary of steps to set up Oracle Internet Expenses. iExpenses is basically an extension Oracle Payables. Employee and Contingent Worker expense reports become supplier invoices and get paid from Payables. You will need following responsibilties to set up Internet Expenses: Payables Manager, Internet Expenses Setup and Administration, System Administration, Application Developer, and AX Developer. If you are also planning on charging expense reports to projects, you will also need Project Billing Super User and General Ledger Super User responsibilities. You will also need access to Oracle Workflow Builder to customize the Expenses workflow and Project Expense Reports Account Generator.

Continue reading How To Set Up Oracle iExpenses…