1. Introduction to Oracle Projects
Project is any short term or long term activity to which either investment or generation of revenue can be associated. To mange it a system must be in place wherein fragmentation of activity to various sublevels, association of budgets, expenditure and resources can be done. Oracle Projects suite work together to provide a complete enterprise project management solution. It provides a flexible approach of defining and managing projects and the people, schedules, deliverables, and finances associated with them.
At the aggregate level, enterprise project management involves the collection and coordination of corporate resources (such as people, money, and hard assets) to accomplish a predefined scope of work in a scheduled time frame and budget. Some enterprises only have projects that are internal in nature, such as projects that track time and costs related to marketing campaigns or infrastructure build-out programs for capital development. Alternatively, many enterprises are entirely project oriented: they derive their entire income stream from projects that provide client services. Oracle Projects includes billing functionality that enables these enterprises to generate project invoices of varying complexity for their clients.
Mainly 3 different types of projects are involved namely (Capital, Indirect, Contract projects).Capital projects are all those projects which are internal to an organization and would serve as an asset to company and on the other hand contract project are services which a company provides to another and on behalf of service revenue is generated.
Oracle Projects consists of the following products:
• Oracle Project Costing
• Oracle Project Billing
• Oracle Project Resource Management
• Oracle Project Management
• Oracle Project Collaboration
• Oracle Intelligence for Projects
• Oracle Project Portfolio Analysis
2. Oracle Project Costing
Oracle Project Costing provides an integrated cost management solution for all projects and activities within an enterprise. With Project Costing you can manage costs across currency and organizational boundaries. Project Costing also acts as a central repository of project plans and transactions, processes project costs, and creates corresponding accounting entries to satisfy corporate finance requirements.
Main area of focus of this document would be Project Costing and its implementation in a Book Publishing Company (SAS Publication)
3. About the company structure
SAS Publication is a book publishing company where every year thousands of books are published. Every book serves as an asset to company. Costs are involved in each activity of book publishing. Those activities can be like (editorial, design, production, manufacturing, ed tech/media, translations, permissions, tech/media (cdi), miscellaneous, restricted).Each of these activities are further subdivided into several sub activities. A book may comes across various phases like (Active, Cancelled, Published, Recovered etc.) in its journey of production.
Each of these books are categorised based on some defined classification rules. According to these rules company maps any of its produces book to any of the 3 collections* (Higher Education Courses, School Group Courses, Professional Courses).Each collections have several Divisions, each divisions several departments and further each department have several PU* (Publication Units).Finally project are tagged to a PU. From top to bottom it is one to many mapping and each and every relation is unique. This structure serves as a base for accounting also.
(Collection->Division->Department->PU->Projects)
*Publication Units (PU) - It is a collection of Projects (Books)
4. Business requirement and its mapping to Oracle
Company’s intent is to have a system in place which is capable of capturing various activities of book publishing and associate all those activities to each of the new book produced. Creation of new book must be routed through some user inputs like (Book name, Book Number, Start date, End date, Division, Department, and Publication Units etc.). System should provide a provision of associating an estimate and at the same time actual for each activity of book publishing. Estimate and Actual at the book level must be compared as per the requirement and various reports must be generated for business analysis. Finally it aims at distributing (amortizing) the total cost incurred for publishing any book over a specified time frame. Monthly amortized cost should be calculated based on the monthly production supplied for each asset assuming total production of any book at the end of the time frame would be 10000 units.
The entire business requirement could be mapped in the Oracle environment as follows:
· Each book serves as a project/Asset
· Activity and sub activity are different Tasks and Sub Tasks of projects
· Phases are like different project status.
· Book Creation Option implemented thorough quick entry fields
· Estimate through budget window and Actual through Expenditure window
· Project Status Inquire serve as the base for Reports
· Amortization the cost is done through Fixed Asset after transferring the book form Oracle Projects to Fixed asset through standard programs
5. Business process execution in Oracle for SAS
Project Creation from Template
|
Set Up Required:
Setup 6.2, 6.3, 6.4, 5.5, 6.6, 6.8, 5.9
|
Budget Creation
|
Expenditure Creation
|
Transferring Assets to FA
|
Depreciation in FA
|
Reports Generation
|
Set Up Required:
Setup 6.7
|
Set Up Required:
Setup 6.10, 6.11
|
1. Project Creation: Project is created by copying it from a template defined below as part of setup. While copying system prompts to enter the quick entry fields like (Project Number, Name, Project Start date, project end date, BU, Department, PU etc.) as defined in template setup. Project gets created on entering quick entry fields. System automatically copies project start date and end date in the transaction start date and finish date for the entire parent and child task in work breakdown structure. Created project will have same work break structure, project type, OU and other features as of template.
As each project is asset to company so it is created form the asset window by providing details like asst name, asset description, Asset Key, Category, Depreciation account, Location, Asset Book and assigned to project at project level only.
2. Budgets: It is the approximation of amount which would be spared for completion of project. In SAS two types of budget are created one is forecast cost budget and other is approved cost budget. Budget is created at the child level task. Total budget is aggregation of budgets at all child level tasks.
3. Expenditure: This is the actual cost created on the project. Expenditures are created form the pre-approved expenditure batch screen supplying expenditure batch name, expenditure class and expenditure ending date. In SAS all the expenditures are entered under “miscellaneous expenditure” class as auto accounting is set for this class. Now for entering expenditure first an expenditure organization is selected from organization LOV. This LOV lists all the expenditure owning organizations which are part of the expenditure hierarchy associated to US OU as part of implementation option setup. Secondly project number, task, expenditure type and quantity is selected and at the same time expenditure DFF is also supplied with appropriate values of LE, BU, Department, and account for accounting. Project number field will allow all projects except in cancelled, closed and Recovered status to be entered. Task field will accept all chargeable child level tasks associated to project. Expenditure type will list out all the expenditure type associated to “miscellaneous expenditure” class. Quantity is entered based on which raw cost would be calculated at the time of distributing cost. Raw cost will product of quantity and rate. Rate is set for each expenditure type at the time of expenditure type definition. If no rate is defined it is considered to be one. After entering all the expenditure for a particular batch it s submitted and released for processing.
To process an expenditure batch a series of standard programs are submitted.
· PRC Distribute Usage and Miscellaneous Cost: This process computes the costs and determines the GL account to which to cost must be posted.
· PRC generate cost accounting events
· Create accounting
· PRC update Project summary amounts
4. Capitalization: It is the method of transferring asset from oracle projects to fixed assets. Book becomes an asset to SAS when it gets published. For publishing a book
· Status of the project(book in SAS) is changed to published
· Cost of asset is calculated based on expenditure created for all capitalized task associated to project. Total cost of asset reflects in CIP field of the capitalization screen after successful completion of PRC update project summary amounts. This cost will be the cost of asset reflecting in FA.
· Asset type is changed form “Estimated” to “As-Built” and Actual in service date is also supplied. All mandatory asset information is filled if not filled earlier at the time of asset creation.
· Asset lines needs to be generated for each asset associated to project. PRC generate asset line program generates the asset line. Generated line will reflect asset name and cost in the asset lines window.
· PRC Interface assets to oracle asset process transfer assets from Oracle projects to fixed asset. Once asset is transferred cost moves form CIP to interfaced CIP column in capitalization screen
5. Depreciation: It is the method of distributing cost of asset throughout its life. In SAS predefined Units of Production methodology (UOP) is used for calculating depreciation in FA. As per UOP monthly production against each asset is entered in the production table. Ratio of monthly production to total production (assumed to 10000) times the total cost calculates to depreciation amount for that month.
6. Setups required for Implementation
1. It is considered that (define chart of accounts, define calendar, define a currency, define a sub ledger accounting method, and define a ledger) is complete as part of GL setup.
2.
Prof.
|
US OU
|
HE
|
SAS BG
|
IT_SAS
|
IT OU
|
SP_SAS
|
SP OU
|
MX_SAS
|
MX OU
|
SAS
|
ELS
|
CTB
|
SC
|
3. Organization Hierarchy:It defines the roll up structure between the organizations.
Company has its business in 4 different geographies (Italy, Mexico, Spain, US) so it have 4 different operating units.US OU have 5 different organizations (HE, SG, Prof, CTB, ESL) which hold projects and an organization (SAS) to hold all the events/expenditure associated to these organizations. Similarly for Italy, Mexico, Spain, Colombia OU event/expenditure org are SAS_IT, SAS_MX, SAS_SP, and SAS_CO respectively. For each Operating unit one organization hierarchy is defined. It starts from corresponding OU and having all the subordinate are at the same level in a particular hierarchy.
4. Implementation option: This form is used to relate operating unit to the corresponding ledger, business group, currency, calendar project and expenditure related setup. In projects/expenditure tab a unique organization hierarchy (as defined in step 3) is assigned to each operating unit. Organizations in these hierarchies will be populated for project organizations and expenditure LOV while creating project and expenditure respectively for a particular operating unit.
5. Classificaion:This form help to define the characteristics used to differentiate between the projects. They are department, Division, PU, copyright year, Legal Entity, Attribute. These attribute are entered when new projects are defined in the system.
6. Service Type:This category is used to segregate type of task. Permissible values are (Prepub, Can, WRO, OVS, LATE, Reorg, CIP, MGF)
7. Budget Entry Method: This defines the way how budget would be entered for a particular project. Here only raw cost for lowest level task is entered.
8. Project Types:Oracle broadly divides projects in 3 different types, namely Capital, Contract, Indirect project. Capital project are those projects which an organization develops for internal use and after completion it could be considered as an asset eg. (Construction of building). Contract project are those which an organization does for another company as service and in turn would generate revenue (Any project which Wipro does for a particular org.). Indirect project are used to aggregate the overhead cost. Oracle provides the facility to create any number of project type belonging to any of the above three classes. New project type will have features defined by the class to which it belongs. While defining project types, information for Costing, Budgeting, Capitalization, Classification, Workflow etc. are specified.
Here Capital project is taken into consideration as book is an asset to SAS. Each OU (US, MX, IT, SP, CO) will hold one capital project under the project type name as (Prepub) .SAS Company is not dealing in burden cost, so the costing tab will not have any setup information. Entry method for raw cost at lowest level task is assigned as budget entry method in budget option tab. All the Classification (BU, PU, Department, Copyright year, Attribute) are made compulsory for defining a particular project in classification tab. Capitalized information is entered to have asset information associated to every project in the capitalization information tab. A new project created must have Active status is set in the project status/workflow tab.
9. Project Template: It is a repository of initial setup done for project creation. It has everything defined in it (Organization, Type, Work break down structure, Quick entry fields etc.).This is required so that user might not enter all the information each time new project is created. Once project is created from template necessary changes can be done.
In SAS one template is created for each and every organization .As in US OU 5 different organization (HE, SC, Professional, CTB, ESL) are present 5 templates are created. All 5 template mandates entry of classification (Project name, Number, BU, Department, PU, Attribute, Copyright year). Further 4 another template for other OU’s (Italy, Mexico, Spain, and Colombia).
10. Expenditure: Expenditures are charged to a project to record actual work performed or cost incurred commitments to future, committed costs expect to incur. Expenditure is a group of expenditure items, or transactions, incurred by an employee or an organization for an expenditure period.
All actual expenditure items and future commitments must be charged to a project and task. Examples of actual expenditures are timecards, expense reports, usage logs, and supplier invoices. Examples of commitments are requisitions and purchase orders. Oracle Projects processes transactions by associating each expenditure type with an expenditure type class. For every expenditure items, expenditure type is identified such as professional, labour or personal automobile use. Expenditure class associated to this expenditure type is used to distribute the cost for that particular transaction.
Predefined expenditure types classes are (Straight time, Overtime, Expense reports, Usages, Supplier invoices, Miscellaneous transactions, Burden transactions, Work in process, Inventory)
To setup the expenditure initially expenditure category must be setup. Secondly all the expenditure type belonging to this category must be setup. At the same time expenditure type must be associated to rate if required. Each expenditure type must be associated with a pre defined expenditure type class which helps for distributing the cost for a particular transaction.
In MHG Expenditure categories are (Conversion, Vendor supply charges, Miscellaneous cost).
Name
|
Expenditure Category
|
Revenue Category
|
UOM
|
Conversion
|
Conversion
|
UNASSIGNED
|
Currency
|
ABC Vendors
|
Vendor Supply Charges
|
UNASSIGNED
|
Currency
|
Vendor Services
|
Vendor Supply Charges
|
UNASSIGNED
|
Currency
|
MSS Vendors
|
Vendor Supply Charges
|
UNASSIGNED
|
Currency
|
Miscellaneous Costs
|
Miscellaneous Costs
|
UNASSIGNED
|
Currency
|
Project Worker
|
Miscellaneous Costs
|
UNASSIGNED
|
Currency
|
Name
|
Description
|
Effective Date From
|
Effective Date To
|
Expenditure Type Class
|
Effective Date From
|
Effective Date To
|
Conversion
|
01-Jan-2000
|
Miscellaneous Transaction
|
01-Jan-2000
| |||
ABC Vendors
|
01-Jan-2000
|
Supplier Invoice
|
01-Jan-2000
| |||
Vendor Services
|
01-Jan-2000
|
Supplier Invoice
|
01-Jan-2000
| |||
Miscellaneous Costs
|
01-Jan-2000
|
Miscellaneous Transaction
|
01-Jan-2000
| |||
MSS Vendors
|
01-Jan-2000
|
Supplier Invoice
|
01-Jan-2000
| |||
Project Worker
|
01-Jan-2000
|
Miscellaneous Transaction
|
01-Jan-2000
|
11. Auto accounting: Any transaction that is created in oracle projects need to be accounted. It aims at determining the correct account for each transaction. Oracle project creates the accounting events and creates accounting for the events for the Sub ledger Accounting .Oracle provides predefined rules for SLA which accepts default account for auto accounting without change. These sub ledger accounting rules can be modified as per the implementation requirements.
Accounting transaction is identified by an auto accounting function. Each accounting function is associated with some user defined rules. These rules are used to find segment value for each accounting segment. Rules are so defined that the values derived out of them are in line with COA defied in GL. Programs which are submitted to generate the account code combination calls the accounting function associated to the transactions. Some of the auto accounting functions are like (Miscellaneous transaction cost account, Misc. transaction clearing account etc.)
To implement auto accounting following steps must be followed:
· Define lookup sets (optional): These are required if mapping exist from one set of values to another set of values.
· Define rules :Each rules have an sql statement associated which process the value based on the input parameter and lookup sets .(As required in implementation)
· Assign rules to auto accounting function.
11.1 Implementation of auto accounting in SAS:
11.1.1 Lookups:In SAS six segment account structure is followed of the format (LE.DIVISION.DEPARTMENT.ACCOUNT.INTERCOMPANY.FUTURE1.FUTURE2). From top to bottom one-many unique relationship exists.
Three lookups are defined to find the values for LE, BU, and Account. Values for LE, BU are derived based on department and value for account based on service type of task (Each task is associated with predefined service type).
1. Department to LE: Used to derive LE value based on department(eg.:100723—0101)
2. Department to BU: Used to derive BU value based on department(eg.:100723—1042)
3. Service type to Account: Each task is associated with a service type and each service type is linked to an account. Linkage between service type and account is defined.(eg.:Prepub—15121101)
11.1.2 Rules: Rules are defined to evaluate the segment values for each account segment. Each segment value is determined in two steps. In first step intermediate value is determined and in the second step segment value. To determine intermediate value SQL statement is defined which evaluates this value based on some input parameter. Various input parameter can serve as the feed to SQL statement (Class codes, Expenditure ID, Expenditure Type, Project Type, Project ID etc.) .Intermediate value can be a constant also. Once intermediate value is known, it may be either mapped to auto accounting lookup defined or can itself be the segment value.
Various rules defined are like
Rule Name
|
0-LE from Exp Item DFF
|
Description
|
Intermediate Value
Source
|
SQL Statement
|
Value
|
select ATTRIBUTE1 from PA_EXPENDITURE_ITEMS_ALL where Expenditure_item_ID = :1
|
Segment Value
Source
|
Intermediate Value
|
Lookup Set
|
Rule Parameters
Sequence
|
Parameter
|
Description
|
1
|
Expenditure Item ID
|
Internal ID of Expenditure Item
|
2.
Rule Name
|
1-BU from Exp Item DFF
|
Description
|
Intermediate Value
Source
|
SQL Statement
|
Value
|
select ATTRIBUTE2 from PA_EXPENDITURE_ITEMS_ALL where Expenditure_item_ID = :1
|
Segment Value
Source
|
Intermediate Value
|
Lookup Set
|
Rule Parameters
Sequence
|
Parameter
|
Description
|
1
|
Expenditure Item ID
|
Internal ID of Expenditure Item
|
As six segment accounting is followed 6 different rules must be defined to generate one account code combination.SAS deals with only miscellaneous cost class so twelve different rules as above are defined. Six rules for the credit line and six rules for debit line.
11.1.3 Assign rule to auto accounting function: Oracle provides functionality to account different type of project in different manner by associating different set of rules for the same function based on project types. In SAS all projects are accounted based on the same rules. Six rules are assigned to function “Miscellaneous Transaction cost account” and Six to “Miscellaneous transaction clearing account”.
Name
|
Misc Trans Cost Account
|
Operating Unit
|
SAS_US_USD_CORP_OU
|
Description
|
Determines cost account for Miscellaneous Transaction Items
|
Function Transactions1.
Name
|
Description
|
Enabled
|
Capital, All
|
All Miscellaneous Transaction items on capital projects
|
Checked
|
Segment Rule Pairings
Number
|
Segment Name
|
Rule Name
|
0
|
Legal Entity
|
0-LE from Class-LE or Department
|
1
|
Division
|
1-BU from Class-BU or Department
|
2
|
Department
|
2-Dept from Department
|
3
|
Account
|
3-Acct from Task Service Type
|
4
|
Intercompany
|
4-Intercompany 0000
|
5
|
Future Use 1
|
5-Future1
|
6
|
Future Use 2
|
6-Future2
|
Function 2
Name
|
Misc Trans Clearing Account
|
Operating Unit
|
SAS_US_USD_CORP_OU
|
Description
|
Determines clearing account for Miscellaneous Transaction Items
|
Function Transactions.
Name
|
Description
|
Enabled
|
All Misc Transactions
|
Clearing account for all Miscellaneous Transaction Costs
|
Checked
|
Segment Rule Pairings
Number
|
Segment Name
|
Rule Name
|
0
|
Legal Entity
|
0-LE from Exp Item DFF
|
1
|
Division
|
1-BU from Exp Item DFF
|
2
|
Department
|
2-Dept from Exp Item DFF
|
3
|
Account
|
3-Acct from Exp Item DFF
|
4
|
Intercompany
|
4-Intercompany from Exp Item DFF
|
5
|
Future Use 1
|
5-Future1
|
6
|
Future Use 2
|
6-Future2
|
No comments:
Post a Comment