Application Functional Design Project Accounting Labor Costing Extension
Author: Creation Date: Last Update: Version:
Paige Peterson 25-APR-2012 30-MAY-2012 2.1
File Ref: Document1
Page 1 of 14
Document Control
Change Record
Date
25-APR-2012 16-MAY-2012 30-MAY-2012
Author
Paige Peterson Paige Peterson Paige Peterson
Version
1.0 2.0 2.1
Change Reference
Initial version created and reviewed with Billy Reviewed with business/FP&A. Ready for walkthrough. Hugo to schedule. Walkthrough meeting held with Billy, Hugo, Terry, Ajay, Wayne, Jaideep, Kishore, Ramesh, Praveen, Sireesh, and Srinivas. Update was made to reflect that weekending dates should be used to derive SHR for full-time reporters. Updated issue to reflect that general approach for cost rates will work.
Reviewers
Name
Ajay Rajani Hugo Balboa Keith Wharton Jeet Jagtap Jaideep Palan Billy Moffatt
Position
Oracle Program Manager Program Manager Solution Architect Technical Architect Technical Lead Business Lead Project Accounting
File Ref: Document1
Page 2 of 14
Table of Contents
Document Control .................................................................................................................... 2 Topical Essay ........................................................................................................................... 4 Basic Business Needs ............................................................................................................ 4 Major Features ....................................................................................................................... 4 Definitions............................................................................................................................... 4 User Procedures..................................................................................................................... 5 Business Rules ....................................................................................................................... 5 Assumptions ........................................................................................................................... 9 User Interface Descriptions....................................................................................................10 Contingent Worker Labor Cost Rates Form ...........................................................................10 Contingent Worker Labor Cost Rates Form Screen Mockup...............................................10 Field Definitions .....................................................................................................................10 Program Description...............................................................................................................12 When to Run the Program .....................................................................................................12 Technical Overview.................................................................................................................13 Open and Closed Issues for This Deliverable .......................................................................14
File Ref: Document1
Page 3 of 14
Topical Essay
Oracle Projects provides standard functionality for distributing labor cost based on rates by either person or HR job title. Schedules are defined by operating unit and currency and can be also defined at a lower level organization within the hierarchy. When the distribute labor process is run, the rate from the appropriate schedule is used to calculate the total cost amount for all hours. If it is necessary to deviate from a rate contained within the schedule, customization to the standard functionality can be accomplished by utilizing the Labor Costing Extension. Allscripts needs employee labor cost to be calculated using a standard costing methodology that will closely resemble an actual methodology. For salaried employees that report time, the rates should fluctuate based on actual hours worked. Also, Allscripts would like employee standard cost rates to be maintained by job level (similar to a grade), which will result in many less values to maintain. Any contingent worker cost will be calculated using the standard methodology of applying a single rate for every hour reported.
Basic Business Needs
The Labor Costing Extension provides you with the features you need to satisfy the following basic business needs. You will be able to address the following: Employee Labor Cost - Use standard costs for the labor costing process in PA based on salary information in HR. For non-exempt employees, the process will use their annual salary to come up with an hourly rate at the point that time is reported (which may be more than 40 hours per week).
Major Features
Feature
Calculate labor cost using standardized rates and different methodologies.
Description
Calculate cost amount of a labor expenditure item using different calculations for different types of employees and for contingent workers.
Definitions
Unique Term
Full-time reporter Exception reporter Standard Working Hours (SWH)
File Ref: Document1
Definition
A person that is expected to report all their working hours on a weekly basis A person that only reports exception working hours on a weekly basis Number of hours that a person is expected to work for a
Page 4 of 14
Weekly Reported Hours (WRH) Standard Hourly Cost Rate (SHR) Burden Percentage (BP) PA Cost Rate (PA Rate)
week stored at the person level in HR Total number of hours reported for a week when time is initially submitted for a full-time reporter The unloaded (unburdened) standard cost rate defined for employees and contingent workers To be applied to Standard Hourly Cost Rates in order to arrive at a fully loaded cost rate Calculated fully loaded hourly cost rate to be used for PA costing
User Procedures
In order to calculate labor cost using the extension, you must run one of the following standard Oracle processes: PRC: Distribute Labor Costs PRC: Distribute Labor Costs for a Range of Projects
These processes determine raw labor cost for expenditure items based on person or job title schedule definition at the operating unit level. Oracle also makes a number of extensions available for customer specific code to be called when running the standard processes. This includes the Labor Costing Extension.
Business Rules
The costing process may need to work differently when time is initially reported for a week and if time is adjusted at a later point. It must also take into account if the person is an employee or contingent worker, a full-time reporter or an exception reporter, or is exempt or non-exempt. When weekly time is initially reported for a full time reporting exempt employee, their total cost for the week should be determined using their standard working hours, then the total cost should be divided by the hours reported to arrive at their hourly rate cost for the week. This will allow a more accurate cost rate to be used in the event that the employee works more than their standard working hours in a week. Exception reporting employees will be costed using a standard hourly rate since they won't necessarily be reporting all of their time for a week. Contingent workers will be costed in a similar manner, but their rates will not be stored in the same place as employee rates since their rates will be by person, not job level, and we need the ability to store them outside of HR to be updated during the procurement process. The following table shows the various methodologies that need to be used for costing.
File Ref: Document1
Page 5 of 14
Methodology Combinations
Person Type Employee Employee Employee Employee Employee Contingent Worker Reporting Type Full Time Full Time Full Time Full Time Exception n/a Employee Type Exempt Exempt Non-Exempt Non-Exempt n/a n/a Processing Methodology
Original Adjustment Original Adjustment n/a n/a
Employee Full Time Exempt - Original Employee Full Time Exempt - Adjustment Employee Full Time Non-Exempt - Original Employee Full Time Non-Exempt - Adjustment Employee - Exception Contingent Worker
For employees, their job level and currency will be used as a basis for deriving the standard hourly cost rate. Employee job titles are stored as a key flexfield and have two segments, the job title and job level. The job level acts like a job grade and will map to a standard cost rate by their currency and date. An employees currency can be determined by their salary record. If a salary record doesnt exist for the employee, the currency should be the default value for the Business Group. Rates must be identified by currency and should be date tracked to allow for past period processing. HR will determine the standard hourly cost rates, but finance will have to determine a burden percentage to apply to derive a fully loaded cost rate to be used for PA time reported. The projects lookup type MDRX_LABOR_COST_EXTN_BP has been setup to contain the burden percentage. As the burden percentage may change over time, values in the lookup will be date tracked. For contingent workers, the person will be used as a basis for determining the standard hourly cost rate. A cost rate table for contingent workers will be maintained. This table will store the rates and currency by date. Additionally an override project and task can be stored to allow for different rates to be used for different projects. Once the calculated hourly cost rate is determined for the appropriate methodology, it will be used with the reported hours to determine the cost amount for the expenditure item. The currency associated with the rate must be taken into account in all processing and must be passed back to PA as the currency of the transaction. Employee Full Time Exempt - Original For full time reporting employees that are exempt, the intent is to apply the weekly cost for the employee across all hours reported. If an employee works more than their standard working hours, this would lower the hourly cost rate still resulting in the normal cost for the week.
File Ref: Document1
Page 6 of 14
When weekly time is initially interfaced to PA, the extension will determine the standard hourly cost rate from HR based on the expenditure ending date. The burden percentage will then need to be applied to the standard hourly cost rate to arrive at a fully loaded cost rate. The burdened hourly rate will be multiplied by the employee's SWH (standard working hours) to arrive at a burdened weekly amount. The burdened weekly amount will be divided by the number of hours reported for the week to arrive at an hourly rate for the week. The hourly rate for the week may vary based on the number of hours reported each week. Example Employee Full Time Exempt - Original calculation Standard Hourly Rate (SHR) Burden Percentage (BP) Standard Working Hours (SWH) Hours Reported for Week (WHR) Calculated Hourly Cost Rate Formula: ROUND(((SHR*(1+BP))*SWH)/WHR,2)
52.00 31.00% 37.00 47.00 53.63
Employee Full Time Exempt - Adjustment In the event that an employee adjusts their previously reported time, we may only have a subset of the hours for the week, so we must take a different approach to costing. We do not want to re-cost previously costed transactions because any re-costing will result in re-billing customer facing transactions. In the majority of cases, adjustments are a movement of hours from one project to another with a net effect of zero. Rather than having very complex logic to handle all the different types of scenarios, Allscripts would like to use the same rate that was calculated for the week when the original transactions were processed. Employee Full Time Non-Exempt - Original For full time reporting employees that are non-exempt, the intent is to apply the weekly cost for the employee across all hours reported. Since the employee is paid for overtime, their weekly cost depends on the total number of hours worked and should be spread evenly across all hours worked for the week. When weekly time is initially interfaced to PA, the extension will determine the standard hourly cost rate from HR based on the expenditure ending date. The burden percentage will then need to be applied to the standard hourly cost rate to arrive at a fully loaded cost rate. Burdened hourly rate will be multiplied by the employee's SWH (standard working hours) to arrive at a burdened weekly amount. Any hours over the SWH are considered overtime and an overhead rate of 1.5 should be applied to them in the weekly calculation.
File Ref: Document1
Page 7 of 14
Burdened weekly amount will be divided by the number of hours reported for the week to arrive at an hourly rate for the week. The hourly rate for the week may vary based on the number of hours reported each week. Example Employee Full Time Non-Exempt - Original calculation Standard Hourly Rate (SHR) Burden Percentage (BP) Standard Working Hours (SWH) Hours Reported for Week (WHR) Calculated Hourly Cost Rate Formula if <=SWH: ROUND(((SHR*(1+BP))*SWH)/WHR,2) Formula if >SWH: ROUND(((SHR*(1+BP))*SWH)/WHR,2)
52.00 31.00% 37.00 32.00 78.76
Employee Full Time Non-Exempt - Adjustment In the event that an employee adjusts their previously reported time, we may only have a subset of the hours for the week, so we must take a different approach to costing. We do not want to re-cost previously costed transactions because any re-costing will result in re-billing customer facing transactions. In the majority of cases, adjustments are a movement of hours from one project to another with a net effect of zero. Rather than having very complex logic to handle all the different types of scenarios, Allscripts would like to use the same rate that was calculated for the week when the original transactions were processed. This is the same approach that will be taken for Employee Full Time Exempt Adjustment. Employee Exception Exception reporting employees only report a portion of their time periodically as they report PTO time and work on special projects. When reporting time, they will only report the exception time, therefore most of the time they will not report their total time for the week. For this reason, we must use an hourly rate approach for them. When time is initially interfaced to PA, the extension will determine the standard hourly cost rate from HR based on the expenditure item date. The burden percentage will then need to be applied to the standard hourly cost rate to arrive at a fully loaded cost rate. This is the rate that will be used for costing in PA. Example Exception Time Reporter Calculation Standard Hourly Rate (SHR) Burden Percentage (BP) Calculated Hourly Rate Formula: ROUND(SHR*(1+BP),2)
52.00 31.00% 68.12
File Ref: Document1
Page 8 of 14
Contingent Worker Hourly rates will be stored in the contingent worker rate table as defined in this document. Rates are defined by contingent worker and currency for a date range. Override project and override task fields are available if needed and will be used to store any rates that might have been contracted for a specific project and/or task. The extension will find the appropriate cost rate and currency for the contingent worker from the table depending on the expenditure item date. The program must look for a project and task level override rate first. If one doesnt exist, the program must look for a project level override rate. If neither exist, the program looks for the rate by transaction date without an override project and task. If no rate can be found for the person for the transaction date, an error should be raised that the cost rate does not exist for the contingent worker in the cost rate table. The rate that is derived is the rate that will be used for costing in PA.
Assumptions
This design assumes that the following statements are true: When time is initially interfaced to PA for full-time reporting employees, all the employees hours for the week are being interfaced. Any time received later will be treated as an adjustment. Changes to any assignment level details in HR will be date tracked, so the values will be updated at the appropriate time. This is important as time reported is date specific. Employees will be identified as a full time reporter or an exception reporter. This designation will be stored in a segment within the people group keyflex and updated programmatically by C&T. Employees will be identified as exempt or non-exempt. All employees will have a standard number of weekly working hours defined (SWH) on their assignment record. All non-exempt employees will be full-time reporters. Policy will be put into place that exception reporters will not charge more than 40 hours. If so, they should be reclassified as a full-time reporter. Oracle standard functionality will used to identify contingent workers.
File Ref: Document1
Page 9 of 14
User Interface Descriptions
The following defines the form elements of the Labor Costing Extension customization:
Contingent Worker Labor Cost Rates Form
This form will be used to store cost rates for contingent workers. The form should be able to be accessed across Operating Units and Business Groups enabling users to enter the hourly rate and currency for any contingent worker. Upon entry or update, the form should ensure that a contingent worker only has one combination of rate, override project, and override task per date. This will ensure that only one rate is valid for any transaction date. For instance: if the this record already exists,
Start Date Active 1-Jan-2011 End Date Active Override Project Override Task
Contingent Worker Contingent Worker Hourly Name Number Cost Rate Currency Contractor, Susie 14 85 USD
they shouldnt be able to add this record;
Start Date Active 1-Jan-2012 End Date Active Override Project Override Task
Contingent Worker Contingent Worker Hourly Name Number Cost Rate Currency Contractor, Susie 14 90 USD
they must end date the first record and the new record must start after it as shown below.
Start Date End Date Active Active 1-Jan-2011 31-Dec-2011 1-Jan-2012 Override Project Override Task
Contingent Worker Contingent Worker Hourly Name Number Cost Rate Currency Contractor, Susie 14 85 USD Contractor, Susie 14 90 USD
Contingent Worker Labor Cost Rates Form Screen Mockup
Contingent Worker Name Contractor, Susie Contractor, Susie Contractor, Susie Contractor, Susie Contingent Worker Hourly Number Cost Rate Currency 14 85 USD 14 90 USD 14 105 USD 14 105 USD Start Date End Date Active Active 1-Jan-2011 31-Dec-2011 1-Jan-2012 1-Jan-2012 1-Jan-2012 Override Project Override Task
100001 100001
100.01
Field Definitions
Field Description Required
File Ref: Document1
Page 10 of 14
Contingent Worker Name Contingent Worker Number Hourly Cost Rate Currency Start Date Active End Date Active Override Project Override Task
List of all contingent workers in Last, First format. LOV shows name and number fields. Customer selects value and both name and number are populated in form. List of all contingent workers in by number. LOV shows name and number fields. Customer selects value and both name and number are populated in form. Hourly rate with a precision of 2 LOV shows Oracle standard currencies that have been enabled The effective start date of the rate The effective end date of the rate The project that the rate is associated with. If null, the rate is applicable for all projects. The task that the rate is associated with, which is associated with the Override Project value. If null, the rate is applicable for all tasks on the Override Project. The LOV is dependent upon the Override Project.
Yes
Yes
Yes Yes Yes No No No
File Ref: Document1
Page 11 of 14
Program Description
The Oracle standard distribute labor costing programs are run to determine the appropriate cost rates for expenditure items and to generate cost distribution amounts which can then be interfaced to the ledger. The programs will call the Labor Costing Extension, if enabled.
When to Run the Program
The distribute labor cost for a range of projects program will be scheduled to run on a daily basis. The programs will also be run on demand by the finance operations team as needed.
File Ref: Document1
Page 12 of 14
Technical Overview
File Ref: Document1
Page 13 of 14
Open and Closed Issues for This Deliverable
Open Issues ID 1 Issue Need to determine how HR will store employee cost rates. Michael suggested using a User Defined Table in HR. Resolution Responsibility Target Date 6/15 Impact Date
Alex setup a proof of Alex/Paige concept in a User Defined Table in TMDRXI that we reviewed on 5/21. We agreed the approach will work, but specific details need to be determined and setup needs to be done in CONFIG2.
Closed Issues ID Issue Resolution Responsibility Target Date Impact Date
File Ref: Document1
Page 14 of 14