OracleAppsBlog

Industries

Thursday, October 21, 2004

Pricing Scenario: How to use price breaks based on Weight?

This post describes how to use the attribute mapping method in Oracle Advanced Pricing to implement a complex contract pricing rule

Recently I came across the following pricing requirement that seemed to be tricky at first glance: -

Use price breaks based on a weight of an item as defined in a DFF.

1. An item from Inventory is assigned to a contract line and has a quantity of 1 with a UOM of Each
2. Each item has a break price (either by item category or the item number) based on the weight. E.g., price is $1000 < 5000 tons and $1500 if weight is between 5001 and 9999 tons and $2000 if > 10000 tons.

The Advanced Pricing allows you to hook up your SQL code to drive pricing. The attribute mapping feature of Oracle Advanced Pricing helped us to implement the above pricing rules by adding a few lines of SQL code.

First, we cannot use the standard price break functionality to achieve this since standard price break uses volume or quantity as basis. We followed the steps below:

Step 1: Create a function to get the weight of the item using item id

e.g.,

Function get_weight( p_item_id number, p_organization_id number) is
l_weight number;
begin
select attribute5 - DFF that stores weight
into l_weight
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_organization_id
return l_weight;
when others then
return 0;
End

Step 2: Create a pricing attribute called weight (datatype: Number)

Navigation: Pricing Manager > Setup > Attribute Management > Contexts and Attribute

Step 3: Setup Attribute Mapping for this pricing attribute

Navigation: Pricing Manager > Setup > Attribute Management > Attribute Linking and Mapping > (B) Link Attributes

Attribute Mapping Method: Attribute Mapped
Level: Line
User Source Type: PL/SQL API
User Value String: pkg_name.get_weight(okc_price_pub.g_contract_info.inventory_item_id, okc_price_pub.g_contract_info.inv_org_id)

Step 4: Run ‘Build Attribute Mapping Rule’ concurrent program

Navigation: Pricing Manager > Setup > Atrribute Management > Attribute Linking and Mapping > Tools Menu

Step 5: Create Formula that uses weight to calculate price

Navigation: Pricing Manager > Pricing Formulas > Formula Setup

One of the formula line will be this newly created pricing attribute. Now the actual formula will depend on the exact requirement for calculation. For example, the formula for the example you have given can be:

Price = WT_FACTOR

Where WT_FACTOR is “Factor List” which is defined as follows:

WT_FACTOR = 1000 if weight price attribute is between 0 and 5000
WT_FACTOR = 1500 if weight price attribute is between 5001 and 9999
WT_FACTOR = 2000 if weight price attribute is between 10000 and 99999999

A new Factor List can be created from the formula form by clicking the factor button at the bottom right corner.  The formula is so versatile that you can implement it in many ways. The following formula would give the same result:

Price = WT_FACTOR * 500

Where WT_FACTOR is “Factor List” which is defined as follows:

WT_FACTOR = 2 if weight price attr is between 0 and 5000
WT_FACTOR = 3 if weight price attr is between 5001 and 9999
WT_FACTOR = 4 if weight price attr is between 10000 and 99999999

Step 6: Associate the formula with the item

This association can be in Price List or Modifier depending on the scenario. If this is only used in contract and always priced this way then you can tie this in the price list itself. If it needs more context sensitive treatment then you may have to use a modifier and use the formula in the modifier.

Step 7: Attach this price list to the contract

Attribute Mapping provide provides a logical and highly flexible setup framework that can be used to model even the most complex pricing scenario. GET_CUSTOM_PRICE() is another way of implementing fully customized pricing rules based on SQL query. I will show the use of it in my next post. Metalink contains a very well-written white Paper (1.44MB) that explains the use of this functionality.

Tuesday, October 12, 2004

Oracle Enterprise Planning and Budgeting exists next to Public Sector Budgeting

Oracle Enterprise Planning and Budgeting is released and is the successor of the Financial analyzer and the Sales analyzer. According to the frequently asked questions, there is still room for a PSB implementation.

Does EPB replace Oracle Public Sector Budgeting (PSB)?

No. The initial release of EPB is a generic product and does not include some of the Public Sector market specific features (i.e. Position Budgeting, Integration with HR Position Control, Posting Budget Journals and Budget Revision) available in PSB.

Will a future version of EPB support PSB features?

The strategic direction of EPB is to provide industry templates and extensions on top of the generic functionality. For the Public Sector market the extensions will leverage Public Sector market specific features (i.e. Position Budgeting, Integration with HR Position Control, Posting Budget Journals and Budget Revision) available in PSB today. This will be achieved by tightly integrating EPB and PSB to provide a comprehensive Planning, Budgeting, Analysis and Reporting solution for the Public Sector market.

We have bought PSB but not yet implemented. Should we implement?

PSB currently incorporates line item-based budgeting and position-based budgeting. If you are planning to incorporate PSB for position-based budgeting as part of your HR and GL implementation, and are going to leverage the two way integration between both (HR for position budgeting, and GL for budget journals and budget revisions), then you should continue with PSB. If your requirements are more around line item based budgeting, long range planning, and complex analysis then you may want to evaluate EPB to see if there is a fit.

Friday, August 27, 2004

University of Florida ERP Implementation - Bridges

This post contains a review of the University of Florida Bridges site which contains some useful documentation and training material on the implementation of Enterprise Systems.

The University of Florida has a really good site called "Bridges" which provides documentation and training material relating to the implementation of systems in their organisation.

According to their site: -

UF Bridges is a multi-year project to replace the University’s current computer systems with new Web-based, integrated systems that provide real-time information and improve university business processes. This is known as an Enterprise Resource Planning (ERP) solution. The University purchased ERP software from PeopleSoft for its financial, human resources, payroll and student administration functions, and enterprise data warehouse and reporting functions from Cognos (a PeopleSoft partner).

Although the solutions being implemented at this University are not Oracle Applications related I thought the site would be helpful for the following reasons: -

1. It provides some good examples of the type of documentation and training material that should be prepared when implementing Enterprise Systems.
2. The documentation and training material will provide readers with a good understanding of the business processes for each of the functional areas within an organisation (e.g. Finance, HRMS)
3. There are various presentations available that give an idea of the issues that one needs to think about when implementing ERP systems.
4. The site provides a good example of how to effectively manage the knowledge gained in an organisation as a result of such an implementation.

The training section is probably the most useful area of this site. It provides instructions guides, classroom training presentations and handouts, classes on video and web tutorials which cover the following functional areas: -

• Enterprise Reporting
• Finance


• Overviews
• Core User
• Customer Relations
• General Ledger/Budgets
• Purchasing and Payables


• Requisitions
• Change Requests
• Receiving
• Vouchers
• Procurement Card (PCard)
• Sponsored Programs
• Travel and Expense
• Human Resource Management Systems


• Hiring


• Staff, OPS and Student Positions
• Faculty Positions
• Position and Job Actions
• Distributions and Pay
• Reporting Time
• Using Leave
• Approving Time and Leave
• Workflow

Within the training section there are also some really good Project Presentations. Highlights are as follows: -

• An Introduction to ERP and the Changing Landscape at UF
• Project Management, IT and ERP - Mike Conlon, Director of Data Infrastructure
• Entering Time Worked and Leave in PeopleSoft HRMS - A Time & Labor Overview
• ERP at the University of Florida - Mike Conlon, Director of Data Infrastructure

Friday, August 20, 2004

The Strategic Enterprise Planning Solution (STEPS) at the University of Alabama

This post contains a review of the the Strategic Enterprise Planning Solution (STEPS) site which documents an Oracle ERP implementation undertaken at the University of Alabama.

I was rather pleased to stumble across the Strategic Planning and Enterprise Solutions (STEPS) site at the University of Alabama (UAB) the other day. It has loads of documentation and information pertaining to an implementation they are undergoing in the areas of Oracle Financials and Human Resource Management System (HRMS).

According to their site: -

UAB’s vision for a new administrative computing system at UAB has reached a major milestone with the implementation of Oracle Human Resources (HR) applications on January 20, 2004. The Finance implementation has been delayed to October 2004. By exploring these pages, you will gain knowledge about the STEPS Project and the new Oracle system.

Oracle HR

According to this section of the site: -

A suite of Oracle HR modules are being implemented for the management of all UAB personnel. The advantage of the new system is the creation of an integrated information environment, and improvement in the quality of human resources data, thereby providing better HR services to individuals.

Data currently viewed in the Human Resource System (HURS) will be found using the ACT form (screen) in Oracle. Several paper processes will be replaced with new electronic processes, such as new hire appointments, salary reclassifications, and effort reporting.

Specific features of the new system include a web-based self-service environment for all employees and an electronic administrative processing environment for staff to conduct day-to-day business. Also, seven-digit employee numbers will replace social security numbers as a means of identifying UAB employees.

HR modules to be implemented as part of the STEPS project are:

• Core Human Resources Management
• Advanced Benefits
• Payroll and Time and Labor
• Labor Distribution and Effort Reporting

This area of the site also discusses self service and you can download a copy of the new HR Organizational Hierarchy codes.

Oracle Finance

According to this section of the site: -

UAB is preparing to replace its existing financial accounting system (EFS) with the Oracle Financials suite of applications to create an integrated information environment that is web based and flexible to handle future growth. With the implementation of the Oracle system, UAB will have a new Oracle General Ledger accounting structure and a new Oracle Grants accounting structure.

Data currently viewed in FAS will be found in Oracle and transactions currently processed using EFS will be processed using Oracle. What are currently called “account numbers” in FAS will be referred to as “account strings” in Oracle and the format of these account strings differs from the current account numbers. Prior to implementation, a “cross-walk table” will be provided matching FAS account numbers to Oracle account strings where possible. Also, the object codes will change from a four-digit to a seven-digit number. The new Oracle object codes can be viewed from this site.

Finance modules to implemented as part of the STEPS project are:

• General Ledger
• Purchasing
• Accounts Payable
• Grants/Plant Accounting
• Accounts Receivable (for Sponsored Projects only)

This section of the site has a variety of documents and presentations you can download that relate to the new Oracle General Ledger accounting structure and Oracle Grants accounting structure. Briefly stated, the new chart of accounts will contain six segments as follows: -

• Account - Identifies primary activity for which money is being spent (7 digits) 
• SubAccount - An account can be broken down to track one or more activities, tasks, time periods, or allocations (3 digits)
• Balancing - Indicates the level at which UAB chooses to carry a balance sheet, whether for internal or external purposes (9 digits)
• Organization - Identifies the organizational unit with which has primary fiscal responsibility for the Account String (9 digits)
• Future - Reserved to meet future requirements (4 digits)
• Object Code - Categorizes the nature of the dollars as a specific type of revenue, expense, asset, etc. (7 digits)

The new Grants Accounting segment structure is as follows: -

• Project – The work performed (6 digits)
• Task – Further breakdown of work performed under project (5 digits)
• Award – Funding source (7 digits)
• Organization – Type of string, and University vs Hospital (2 digits)
• Expenditure Type – Similar to GL Object Code (7 numeric digits plus description - 30 maximum segment length)

Oracle Training

Under this section of the site you will find course descriptions, online course manuals and online simulation courses (training videos). Online course manuals are available for the following modules: -

• Finance
• Navigation
• ACT (Appoint, Change and Terminate)
• TEL(Time, Entry and Labour)
• Self Service

The online simulations are quite good for those who are just starting to earn Oracle Applications. The following simulations are available: -

• Oracle Navigation
• Human Resources


• Appoint, Change, Terminate (ACT)
• Workflow Approvals
• View HR Data
• TEL (Time, Entry and Labour)
• Self Service

End User Readiness

Here you will find the specifications for a Desktop PC which needs to run Oracle Applications. There is also a Go-Live Checklist which is designed to ensure that users are prepared for utilising Oracle Applications.

Oracle Access

Here you will find a listing of End User responsibilities for Oracle HRMS at the University. For each responsibility a summary of access areas is documented. If a user does not have access to the system the appropriate forms are made available to apply for access.

Oracle Workflow

Here you will find a workflow presentation given at a STEPS workflow officer meeting.

Lastly, you should also consider visiting the Universities IT department.

Tuesday, August 03, 2004

The Oracle Financial Information Systems (FIS) Project at University College London (UCT)

This post contains a link to the Oracle Financial Information Systems (FIS) website at University College London (UCT) and also mentions the key features of the site.

The Financials Systems Project site at the University College London (UCT) provides readers with a comprehensive understanding of what it takes to implement an Oracle Applications System. The site gives readers a holistic view of all the aspects of Enterprise Systems Implementations. UCT initially implemented the following modules of Oracle Application 11.0.3 and later upgraded to 11i : -

• General Ledger
• Accounts Payable
• Purchase Ordering
• Accounts Receivable
• Grants ( including Project Costing and Project Billing)
• Fixed Assets
• Inventory

Following is a review of the key areas of the site:

FIS Project

This section of the site provides an overview of what the FIS project and has related information categorised as follows: -

• Information on the use of the site
• What is the FIS Project?
• FIS Team Members
• Useful Contact Information
• Migration Plan
• FIS Service Times
• FIS Users Group
• 11.0.3 Enhancement Stage Plan
• FIS Technical and Business Consultancy 2003
• Upgrade to Version 11i
• FIS Newsletters
• FIS Training Dates

Procedures, User Guides and General Reference

This particular section of the site is probably one of the most useful areas as it contains project documentation which has been categorised in to the following areas: -

• General Reference and Procedural Notes
• User Guides for the following modules: -


• Accounts Payable
• Accounts Receivable
• Fixed Assets
• General Ledger
• Grants
• Inventory
• Purchase Ordering
• Other User Guides
• Technical Guidance
• How To...?