Versions Compared

Old Version 93

changes.mady.by.user Saparnell

Saved on

compared with

New Version Current

changes.mady.by.user Mohamed Al-Omar

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

Summary

  • This view contains all of the expenditures with dates and costs.
  • Task information is included in this view, including Tasks with no posted costs.
  • This view includes Project that are sponsored and non-sponsored (such as general) - see the field Project Type
  • This view includes Award data associated with Sponsored Projects.  Award data comes from the Research Activity Hub (RAH).
  • This view is sourced primarily from Oracle PPM
  • This view displays select Project Personnel by role.  For a comprehensive list, use the FINAH-ProjectPersonnelRole-View and see How to Understand and Update Oracle (OFC) Project Portfolio Management (PPM) Project Personnel for more information.


Info
titleRecommended Knowledge

Learn more about Understanding Project Costing.


Data Loads

Data is loaded nightly from oracle.  To see when the data completed being loaded by UCSD into FINAH see this report: Tableau > Public > AH Data Load Status Report

Granularity

Granularity is the scale or level of detail present in a set of data.  It identifies where levels of uniqueness exists in the data.  The data lives in the view at the lowest level, but each BI tool totals the levels differently.


Note

Please note:  The Level of Detail (LOD) calculations listed here for Tableau developers are a baseline and do not necessary include all of the LOD equations needed for your report.



LevelKey (uniqueness / group by)Measures
1Award

Award Number

Tableau report developers: {FIXED[Award Number]:MIN([Award...])}

All fields beginning with the word “Award”
2Project

Project ID

Tableau report developers: {FIXED[Project ID]:MIN([Project...])}


All fields beginning with the word “Project” except the following those beginning with "Project Budget Period"
3Award Budget Period Amounts

Award Number + Expenditure Budget Period ID

Tableau report developers: {FIXED [Award Number], [Expenditure Budget Period ID]: MIN([Award Budget Period...])}

All fields beginning with the word “Award Budget Period”
4Project Budget Period Amounts

Award Number + Project ID + Expenditure Budget Period ID

Tableau report developers: {FIXED [Award Number],[Project ID], [Expenditure Budget Period ID]: MIN([Project Budget Period...])}


All fields beginning with the word “Project Budget Period”
5

Task


Task ID

Tableau report developers :{FIXED[Task ID]:MIN([Task...])}


All fields beginning with the word “Task” except the following those beginning with “Task Budget”

All fields beginning with the word “Fund”

6Task Budget Period Amounts

Award Number + Project ID + Task ID + Expenditure Category + Expenditure Budget Period ID + Expenditure Funding Source


Tableau report developers: {FIXED [Award Number],[Project ID], [Task ID], [Expenditure Category], [Expenditure Budget Period ID],  [Expenditure Funding Source]: MIN([Task Budget Period...])}

All fields beginning with “Task Budget”

All fields beginning with “Expenditure Funding”

All fields beginning with “Expenditure Budget”

7

Transaction Number



Transaction Number

Tableau report developers: {FIXED[Transaction Number]:MIN([Expenditure...])}



All fields beginning with “Expenditure” except:

  • All fields beginning with “Expenditure Funding”
  • All fields beginning with “Expenditure Budget”

PO Line Number

PO Number

Transaction Source ID

Transaction Source Line Reference

User Expenditure Batch

8Expenditure Distribution

Transaction Number+Expenditure Dist Line Number


All fields beginning with "Expenditure Dist".

The lowest level is one row per expenditure distribution line.  If an award, project or task does not have an expenditure distribution then it will not appear in this view.  You can use the FINAH-Award-View, FINAH-Project-View or FINAH-Task-View to see all objects without expenditures.

Key Fields to View

  • Account
  • Award
  • Financial Unit (project owning organization)
  • Function
  • Fund
  • Location
  • Program
  • Project
  • Task 


Key Fields Filter on

  • Accounting Period
  • Account
  • Award
  • Expenditure Dist Line Number
  • Financial Unit (project owning organization)
  • Fund
  • Project Type
  • Project
  • Project-Task Code
  • Task
  • Transaction Number


Additional Notes

All measures are pulled directly from the source system, no calculations are done FINAH.


Ui expand
titleAlso known as...


Field name in FINAH-ProjectCosts-ViewAlso known as...
Expenditure Dist Line NumberTransaction Dist ID
Transaction NumberTransaction ID
Project OrganizationProject Owning Organization




Ui expand
titleAward Sponsor

Award Flow Through Sponsor

  • The Award Flow Through Sponsor is the prime sponsor in research on Kuali or RAH. 

Award REMS Sponsor

  • The REMS fields are fields from UC system wide registry for all sponsors and that system is called REMS.  These REMS fields were adding flow through from that system to RAH.  In OFC,  we have sponsor flow through sponsor code and sponsor name. These REMS fields are blending from RAH which are originated in system wide registry.


Ui expand
titleBurden Schedule


Burden Schedules can exists a the Award, Project or Task level.

  • Award Burden Schedule
  • Award Burden Schedule Multiplier
  • Project Burden Schedule
  • Project Burden Schedule Multiplier
  • Task Burden Schedule
  • Task Burden Schedule Multiplier

A Task Burden Schedule will override a Project Burden Schedule and a Project Burden Schedule will override an Award Burden Schedule.

The following fields will indicate which level of Burden Schedule was used.

  • Effective Burden Schedule
  • Effective Burden Schedule Multiplier
  • Effective Burden Schedule Multiplier Level - possible values include "Task", "Project" and "Award"



Ui expand
titleCosts

Costs

Expenditure %% Cost = to be used with Expenditure ID, is a summation of Expenditure Dist %% Cost (+ and -)

Expenditure Dist %% Cost = to be used with Expenditure Dist Line Number

Expenditure Dist Burden Cost and Expenditure Dist Raw Cost at at the lowest level.


Ui expand
titleDates

Dates

Expenditure Created Date = date it was entered

Expenditure Date = date when expenditure occurred

Expenditure End Date = date when re-occurring expenditure is scheduled to end

Expenditure Last Updated Date = date when record was last updated

Expenditure Dist Created Date = date when expenditure dist created


Ui expand
titleDerived Fund Code and Derived Function Code

The Fund and Function Codes in FINAH-ProjectCosts-View arecurrently pulling from GL accounting lines for a given expenditure transaction.  This can be problematic because there are many manual errors in the GL accounting lines, so an an alternative a Derived Fund and Function are provided based on the following logic (which mimics the logic in the Oracle based project information lookup tool)


Derived Fund Code

For sponsored projects,

Step 1) If the "Expenditure Funding Source Code" = 'Internal' then the "Derived Fund Code" will display 13991.

Step 2) If the "Expenditure Funding Source Code" field is 5-digits then it is an internal code and the "Derived Fund Code" will display the "Expenditure Funding Source Code".

Step 3) If the "Expenditure Funding Source Code" field is not 5-digits then match on the "Award Type" to find the Fund Code*.

Step 4) If the "Expenditure Funding Source Code" field is not 5-digits and "Award Type" = Special State Appropriations, then match on the "Award Sponsor REMS Code" to find the Fund Code*.

For general projects, use the codes from Task Fund Code Flex Field.

Derived Function Code

For sponsored projects, match on "Award Purpose" to find the Function Code*.  

For general projects, use the codes from Task Function Code Flex Field.


*For information on how Award Types are mapped to fund and function, please refer to the following page

/wiki/spaces/IPAAS/pages/19596801


Ui expand
titleExpenditure Dist Line Number Reversed

Expenditure Dist Line Number Reversed

When an expenditure is reversed a new Expenditure Dist Line Number is generated and the original line referenced in the Expenditure Dist List Number Reversed field.  The Expenditure Dist Reversed Flag is set to Yes for the original line.


Ui expand
titleFund and Function

Fund and Function

The Fund and Function Codes in FINAH-ProjectCosts-View are currently pulling from GL accounting lines for sponsored project.  See section Derived Fund and Derived Function (which mimic the logic in the Oracle based project information lookup tool)

**Note this information is only available for expenditures that are generated in OFC and may not be available for expenditures, as an example, that were imported during conversion from IFIS or other sources.


Ui expand
titleMeasures


FieldDefinition
Awards Per Project Count

Count of awards associated with a project.



Ui expand
title Most Recent Project Fund Manager Name Full

  • If the project fund manager assigned to a project in OFC is end dated and there is no current assignment, then project fund manager will show “null” in the project cost view. The Most Recent Project Fund Manger Name Full will show the latest project fund manager name full.


Ui expand
title Most Recent Project Manager Name Full
  • If the project manager assigned to a project in OFC is end dated and there is no current assignment, then project manager will show “null” in the project cost view. The Most Recent Project Manger Name Full will show the latest project manager name full .


Ui expand
titleOracle Set Up Scenario

We sometimes see a setup scenario in OFC where a sponsored project is assigned to more than one award. This seems to happen when they increment the award version from -00001 to -00002.

If there are two records with the same base number then always select the -0002 record.


Project Most Recent Award Flag

  • The Project Most Recent Award Flag is set to "Yes" to indicate one award per project or the most recent award to be associated with the project. 




Ui expand
titlePerson Role

Person Role

If there is more than one person per role then FINAH is displaying the most recent person.  If there are multiple people with the same start date then the alphabetically first person is displayed.

For more details, use the FINAH-ProjectPersonnelRole-View and How to Understand and Update Oracle (OFC) Project Portfolio Management (PPM) Project Personnel



Ui expand
titlePre Built Filters
  • Most Recent Award Per Project filter for 'Project Most Recent Award Flag' = Yes



Ui expand
titleRole Active Count

Role Active Counts

Indicates how many people are actively assigned to the role.  Many roles are intended to have 1 person.



Ui expand
titleTask Fund Manager

Task Fund Manager

For 'Task Fund Manager' only the person name full will be populated (i.e. not email address, employee ID, or other person attributes), as OFC currently does not store the employee ID for the Task Fund Manager, only the person's full name.



Ui expand
titleTransaction Entry (C/D)
  • Transaction Entry (C/D)  should use the same logic for the Project GL Transaction Signed Amount in this view.



Ui expand
titleFinancial Unit

Financial Unit and the Financial Unit hierarchy are based on the Project Organization (aka the Project Owning Organization in Oracle).

The Financial Unit (Project Owning Organization) can be different than the Award Owning Organization.