oracle: advanced concepts in ppm capital projects oracle... · ppm capital projects . training for...

11

Oracle: Advanced Concepts in PPM Capital Projects

Training for Capital Projects

2

Use ‘Chat’ if experiencing technical difficulties or for periodic instructor led Q&A

Practice exercises provide step-by-step guidance for users and weekly office hours are available to support learning. Training Resources: https://blink.ucsd.edu/finance/fis-project/fin-training.html

Oracle Training Environment:https://ekgs-dev6.fa.us2.oraclecloud.com/

Access Course Workbook:https://ucsd.co1.qualtrics.com/jfe/form/SV_39uif9v0x23o4yF

All Participants Muted

Login to Oracle Required Course Workbook

Training Basics

Take 3 minutes to access your course workbook and try logging in to the training environment

Practice Exercises & Office Hours

33

Topic Information

Main Users Construction Management Offices (CMO) and General Accounting Department (GA)

Role Responsibilities

• Project and Task Setup: GA or Med Ctr. Financial Office • Budget Creation: CMO • Funding Transfers: Capital Program Management (CPM) and GA• Charging Costs to Projects: Departments and CMO• Monthly Construction In Progress Process: GA• Project Capitalization: GA• Financial Reporting: GA • Project Closeout: CMO

Accounting Concepts

• Funding Transfers from funding sources to capital projects • Funding/Cost Transfers between Tasks within a Project• Funding/Cost Transfers between Projects• Project Capitalization

PPM Subledger • Reconciliation between PPM subledger and General Ledger is performed by General Accounting

What you should know

44

Overview of Project Structure

Oracle uses project and task structures to organize work performed and costs incurred.

Project: The primary unit of work that can be broken down into one or more tasks.

Project

Task 1

Task 2

Task 3

Task: Used to organize work into smaller more manageable units of work. Transactions are charged to a project-task combination and recorded at the task level.

55

GL CoA + PPM Subledger POET(AF)

Chart stringSegment Length

Entity Fund Financial Unit Account Function Program Location Project Activity

+

• POET for all transactions in PPM• AF for PPM projects with Awards (Sponsored Projects)

Project Number

Expenditure Organization

Expenditure Types

Task Number

Award Number

FundingSources

P O E T A F

66

Commonly Used CoA String Elements for Capital ProjectsEntity

16196 Campus Operations16242 CEO of Medical Center

Fund17300 Res Alt Med Ctr (Med Ctr. Unrestricted Capital Reserve Fund)17301 Capital Construction University Funds (Campus Unrestricted Capital Reserve Fund)42000 NICA-Other42001 NICA - Med Ctr13027 Financial Control24400 Commercial Paper Tax Exempt (Restricted Debt Service Fund)24024 GRB 2015 AP (Restricted Debt Service Fund)24026 GRB 2016 Series AR (Restricted Debt Service Fund)24030 GRB 2017 Series AV (Restricted Debt Service Fund)24031 GRB 2017 Series AW (Restricted Debt Service Fund)24034 GRB 2018 Series AZ (Restricted Debt Service Fund)24035 GRB 2018 Series BA (Restricted Debt Service Fund)24036 GRB 2019 Series BB (Restricted Debt Service Fund)24037 GRB 2019 Series BC (Restricted Debt Service Fund)17852 MCPRB 2007A (Restricted Debt Service Fund)24202 MCPRB 2009 Series F (Restricted Debt Service Fund)24203 MCPRB 2010 Series G (Restricted Debt Service Fund)24206 MCPRB 2013 Series J (Restricted Debt Service Fund)24208 MCPRB 2016 Series L (Restricted Debt Service Fund)24210 MCPRB 2020 Series N (Restricted Debt Service Fund)

77

Commonly Used CoA String Elements for Capital ProjectsFinancial Unit

9699908 Campus Operations Net Investment in Capital Assets4200100 Balance Sheet Accounts only4310162 Balance Sheet

Account102110 Financial Control-Office of President160000 Real Estate161000 Parking lot improvements161100 Other Land Improvements161400 Infrastructure Assets162000 Buildings and Improvements Service Concession Arrangements162100 Buildings and Improvements-Housing162200 Buildings and Structures-Parking162300 Leasehold Improvement162400 Buildings and Improvements Other163000 Equipment163001 Equipment In Process163002 Non-Inv Equip In Process163003 Equipment Clearing163004 Non Inventory Equipment163100 Software greater than $5M163101 Software In Process greater than $5M163150 Software less than or equal to $5M

88

Commonly Used CoA String Elements for Capital ProjectsAccount cont.

163151 Software In Process less than or equal to $5M163200 Intg Assts - Cap and Trade CR-Camp163300 Library Rare Bks and Coll163400 Art-Special Collections163500 Construction In Progress163501 Tenant Improvements in Process226200 MCRB Bond CL262200 MCPRB Prem/Disc LT774011 Capital Outlay810000 GRB: Debt proceeds received811000 LPRB: Debt proceeds received812000 MCPRB: Debt proceeds received813000 Third-party debt: Debt proceeds received814000 Debt proceeds received - Capital Lease815000 Debt proceeds received - Commercial Paper

Function640 Operation and Maintenance of Plant420 Teaching Hospitals

99

Commonly Used CoA String Elements for Capital ProjectsLocation Prefix + CAAN + "0" (e.g. C61310)

A IntangiblesB Library Assets C BuildingsD Buildings not in Tririga SystemG General ImprovementsI InfrastructureL LeaseholdsP Prorated LocationR Real EstateS SoftwareT Modular Office/TrailerV Vessels/Ships

1010

POET(AF)

Letter Stands For Description

P Project Project receiving the budget and the expense

O Expenditure Organization

Department that generates the expense (usually notthe same as the one that owns the project)

E Expenditure Type What kind of expense is this? Maps to CoA accounts

T Task Task receiving the budget and the expense

A Award * Award from KR

F Funding Source * Funding source / Sponsor name

* Only required for sponsored research projects

1111

Commonly Used POET String Elements for Capital Projects

Expenditure Organization9699908 Campus Operations Net Investment in Capital Assets4200100 Balance Sheet Accounts only4310162 Balance Sheet

Expenditure Type163500 - CIP - Construction 534100 - Computer Supplies and Equipment less than 5k163500 - CIP - Consultants 580900 - Amort Def Financing Cost-Rev Bonds163500 - CIP - Surveys, Plans, etc. 580700 - Amort of Premium-Revenue Bonds163500 - CIP - Special Items 580901 - Amort Def Financing Cost-3rd Party Bond163500 - CIP - Special Items Interest Expense 580800 - Amort of Premium-3rd Party Bond163500 - CIP - Special Items Interest Income 580100 - Interest Expense CP - Tax Exempt163500 - CIP - Special Items STIP 580000 - Interest Expense CP - Taxable163500 - CIP - Special Items Cost/Issue/Loan Fees 580200 - Interest Expense Long Term Debt163500 - CIP - Special Items Other163500 - CIP - Facilities Management Services163500 - CIP - Utilities163001 - CIP - Equip Fabrication Materials and Labor-No Tax163001 - CIP - Equip Fabrication Materials and Labor-Tax163001 - CIP - Fabrication Components and Supplies163003 - Inventorial Equipment163101 - CIP - Software Greater Than $5M163151 - CIP - Software Less Than or Equal To $5M

1212

PPM CAPITAL PROJECT LANDSCAPE

1313

PROJECT SETUP & MAINTENANCE

1414

Important Information

• Project setup in eBuilder/Maximo by CMO• CMO requests a new project and tasks through SNow. GA processes the

request and communicates the new project/task numbers.

1515

Step-by-step process (Demo)

1616

Step-by-step process

1

2

Creating a Project:

1. Click the Projects button.

2. Then click the Project Financial Management button to enter the PPM module.

1717

Step-by-step process

3 3. Click “+” button to create a project.

1818

Step-by-step process

5

4. Capital Project Template indicates Business Unit:a. Campus Capital Project Template is selected for:

i. Campus owned Campus managed projectii. Med Ctr. owned Campus managed project

b. Med Ctr. Capital Project Template is selected for Med Ctr. owned Med Ctr. managed project

c. Physician’s Group Capital Project Template is selected for Physician’s Group owned project

5. Project Name is divided into two parts: a. Prefix

i. CP-Campus owned projectii. CP MC-Medical Center owned projectiii. CP PG-Physician’s Group owned project

b. Title

6. Organization indicates which CMO manages the project.

4

6

1919

Step-by-step process

7

7. Task:Set up the first task by naming it Task 1. Click on “Basic Task” and edit the field with the task name format AAAAA/BBBBB/Funding Source Fund Description

a. AAAAA is Capital Reserve Fund or Gift Fund Number b. BBBBB is Funding Source Fund Number

Examples: 17301/13042/TR Internal LoanE1919/E1919/Gift

2020

Step-by-step process

8

8. If the Additional Information column is not shown, click the View dropdown button and expand Columns, and then select Show All.

2121

Step-by-step process

9

9. Funda. Unrestricted Capital Reserve Fund b. Restricted Capital Reserve Fund c. Gift Funds

10. Function

11. Location

11

10

2222

Step-by-step process

1212. To add another task, click the dropdown next to the “+” button and then select Peer Task.

13. Enter Task Number and Task Name.

14. Click Additional Information and assign Fund, Function and Location.

13

14

2323

Step-by-step process

1

2

Adding Project Manager:

1. Click the Projects button.

2. Then click the Project Financial Management button to enter the PPM module.

2424

Step-by-step process

3. Click on the Project Name or Number, then select Manage Financial Project Settings.

3

2525

Step-by-step process

4

4. Click on Parties

2626

Step-by-step process

5

5. Click on the “+” button

6. Fill out Add Team Members box:a. Add project manager by searching Last Name, First Name b. Assign a project rolec. Start Date can be the project start dated. Save

6

2727

• If the funding source is external financing, CMO creates the budget after the project is set up in PPM. Funding is transferred from external financing through the monthly draw.

• If the funding source is not external financing, CMO creates the budget at the same time as the funding transfer.

• After the submitted budget is approved, two budget versions are: ○ Version 1 (Original and Current Baseline) and Version 2 (Current Working)

Important Information

2828

Step-by-step process

1

2Creating the Budget:

1. Click the Projects button.

2. Then click the Project Financial Management button to enter the PPM module.

2929

Step-by-step process

4

5

3. Click on the Project Name or Number.

4. Select Manage Project Budget on the pop-up menu.

5. Click the dropdown for the “+” button and select Create Budget Lines Manually.

3

3030

Step-by-step process

6

7

6. Select UCSD Cost Only Budget for Financial Plan Type.

7. Expand the Budget Creation Details section, then verify the Current Planning Period.

8. Click Save and Continue.

8

3131

Step-by-step process

9 To create a budget for a task:

9. Highlight the Task row and then select the “+” button.

3232

Step-by-step process

10

11

10. Select Financial Resources from the dropdown.

11. Enter the budget amount in the Raw Cost column.

12. Then click the Submit button.

12

3333

Step-by-step process

13. Click OK

After the submitted budget, Version 1, is approved, there will be two budget versions: Version 1 (Original and Current Baseline) and Version 2 (Current Working)

13

3434

Important Information

• Project and task names can be updated after CMO sends SNOW requests to General Accounting

3535

Step-by-step process

1

2

Updating Project Name:

1. Click the Projects button.

2. Then click the Project Financial Management button to enter the PPM module.

3636

Step-by-step process

3. Click on the Project Name or Number, then select Manage Financial Project Settings.

3

3737

Step-by-step process

5

4. Click on General Tab

5. Click on Edit button.

4

3838

Step-by-step process

6

6. Click on field and enter the desired name.

7. Once finished, click the Save and Close button.

7

3939

MANAGE FINANCIAL TRANSACTIONS & CAPTURE COSTS

4040

Important Information

• Funding Transfer is processed through General Ledger entries ○ Transfer from Unrestricted Funding Sources to Unrestricted Capital

Reserve Fund○ Transfer within Gift Fund between Departmental Financial Unit and

Campus Operations Net Investment in Capital Assets (Campus NICA) Financial Unit

○ Transfer from Commercial Paper/Bond Proceeds to Restricted Capital Reserve Funds

○ Transfer between Projects for Sponsored Project Funded Capital Project ○ Transfer State Appropriations to Restricted Capital Reserve Funds

4141

Transfer from Unrestricted Funding Sources to Unrestricted Capital Reserve Fund

Current State

Future State

4242

Transfer within Gift Fund between Departmental Financial Unit and Campus NICA Financial Unit

Current State

Future State

4343

Transfer from Debt Proceeds to Restricted Capital Reserve Funds

Future State

4444

Step-by-step process

12

Submitting a GL Journal:

1. Click the General Accounting button.

2. Then click the Journals button.

4545

Step-by-step process

3

3. Click on the Task pane button.

4. Then select Create Journal in Spreadsheet to download the Excel template.

4

4646

Step-by-step process

55. If Excel doesn’t automatically open, download/select the Excel file at the bottom of the window (if necessary).

4747

Step-by-step process

66. Excel opens and the journal template partially displays, click Enable Editing button.

4848

Step-by-step process

7

7. To begin the OFC connection process, click Yes to be directed to the OFC sign-in window.

4949

Step-by-step process

8

8. Complete the OFC Sign In process.

5050

Step-by-step process

9

9. Once connected to OFC, the Excel journal template will be shown, and then you can prepare the journal.

10. Click the Submit button to upload the journal.

10

5151

Important Information

• Capital Projects incur costs from various sources:○ Invoice payments processed by Accounts Payable○ Recharge activities

5252

Important Information

• After augmentation is approved, CMO needs to create a new budget. The new budget should show the cumulative amount. (i.e., Current budget baseline is $1M, and the augmentation is approved for $1M. The new budget version should be $2M)

• Funding transfer between tasks requires an adjustment to the budget at the task level.

• Expenses can be adjusted within Resource and Cost Transfer Application (RACTA). RACTA is still under development. A separate training will be available when it is complete.

5353

Step-by-step process

1

2

Adjusting the budget for augmentation:

1. Click the Projects button.

2. Then click the Project Financial Management button to enter the PPM module.

5454

Step-by-step process

4

3. Click on the Project Name or Number.

4. Select Manage Project Budget.

5. Click on “Current Working” version.

3

5

5555

Step-by-step process

6. Click on the expansion button below the task number

6

5656

Step-by-step process

8

7. Enter the cumulative budget amount in the Raw Cost column.

8. Click the Submit button to submit the change.

7

5757

Step-by-step process

9

9. Select the OK button to submit Version 2.

After the submitted budget, Version 2, is approved, there will be three budget versions: Version 1 (Original Baseline), Version 2 (Current Baseline) and Version 3 (Current Working)

5858

Step-by-step process

1

2

Adjusting the budget for funding transfer between tasks:

1. Click the Projects button.

2. Then click the Project Financial Management button to enter the PPM module.

5959

Step-by-step process

4

5

3. Click on the Project Name or Number

4. Select Manage Project Budget.

5. Click on Current Working Version

3

6060

Step-by-step process

6

6. Click on the expand button of the task you would like to change

7. Highlight Financial Resources under the task and adjust the amount (Repeat Steps 6 and 7 for other tasks that require budget adjustments)

8. Click Submit

7

8

6161

Step-by-step process

9. Click Ok

After the submitted budget, Version 3, is approved, there will be four budget versions: Version 1 (Original Baseline), Version 2 (Baseline), Version 3 (Current Baseline) and Version 4 (Current Working)

9

6262

Step-by-step process

1

2CMO adjusts costs in RACTA and can also view costs in PPM module:

1. Click the Projects button.

2. Then click Costs.

6363

Step-by-step process

3

4

3. Click the Tasks pane.

4. Then click Manage Project Costs

6464

Step-by-step process

5

6

5. Enter Project Number

6. Click Search

6565

Step-by-step process

7

User should be able to view Manage Project Costs Page.

7. If needed, user can adjust the columns seen below by clicking View and then Columns

6666

Important Information

● GA posts monthly CIP journals by moving the CIP account balance from Capital Reserve Funds or Gift Funds to Net Investment in Capital Assets (NICA) Funds.

6767

Financial MONITORING AND REPORTING

6868

Important Information

• Monthly monitoring and internal reporting are currently under development.

• We currently have 3 reports:o Budget vs. Actual Cost Comparisono Project Cost Account Reconciliationo Project Income Statement

6969

Step-by-step process

1

2How to navigate to cost reports:

1. Click the Projects button.

2. Then click Costs.

7070

3. Click the Reports and Analytics pane.

4. Click the triangle next to Shared Reports and Analytics to expand the folder and choose the report you want to run.

5. Click More

3

4

5

7171

Before running the report, be sure to change your Currency type. You should only have to do this once:

6. Click on your name in the top right corner

7. Click on My Account

8. Change Currency to “Ledger Currency”

9. Click OK

76

8

9

7272

7373

7474

7575

PROJECT CAPITALIZATION

7676

Important Information

• CMO notifies GA of project completion

• GA processes capitalization journals

7777

PROJECT CLOSEOUT

7878

Important Information

• CMO sends a request to Campus Planning to return the unused fund • Campus Planning sends GA a fund return letter• GA processes fund return• CMO ends the project in PPM

○ In OFC, closing a project is permanent and irreversible. Thus, we will end a project with the end date information.

○ Expenses can no longer be posted once a project end date has passed.

7979

Step-by-step process

1

2

Ending a Project:

1. Click the Projects button.

2. Then click the Project Financial Management button to enter the PPM module.

8080

Step-by-step process

4

3. Search for the project by the project name or number

4. Click on the status of the project you want to end. Current status should be active

3

8181

Step-by-step process

7

5. Fill in To Field with UCSD Closed

6. Add comments if needed

7. Click Save and Close

56

8282

Oracle: Advanced Concepts in PPM Capital Projects

Thank you!