Data Model Presented By Virginia DeCeglia Chris Shaffer Date Prepared May 2015 Agenda Data Model Overview Database Navigation Frequently Accessed Areas Timeslices OBS CA PPM Database Content ID: 913275
Download Presentation The PPT/PDF document "Accessible Data Advanced" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Accessible Data
Advanced Data Model
Presented By
: Virginia
DeCeglia, Chris
Shaffer | Date Prepared: May, 2015
Slide2Agenda
Data Model OverviewDatabase NavigationFrequently Accessed Areas
Timeslices
OBS
CA PPM Database Content
Portfolio contents
Financial Plans & Financial Actuals
Hierarchy
Idea to
Project
Slide3Data Model Overview
Three main
areas where data is
stored
Core Tables
Production
tables used for
day
to day functions
Includes
Investment
Resource
Timesheet
Information
Data
updates
in real time (Live Tables)
Time Slice Tables
Houses summarized data by Daily, Weekly, Bi-Weekly, Monthly, Bi-Monthly, Quarterly, Semi-Annually, Yearly views
Usually
populated via a job process – Time Slice
Some
tables are populated using
instaslice
, an internal function
called
during specific user activities
Time Slices are critical
in defining
how much data is summarized and the time frame in which the data can be pulled
DataMart
Tables
Provides Summary and Rollup Data
Populated
via several job processes – Rate Matrix Extraction,
DataMart
Extraction,
Datamart
Rollup
Slide4Database Navigation
Objects Naming Convention – Tables
Understanding
table prefix definitions
helps you navigate
to the correct
schema area to
find
needed information. The majority of prefixes in the current CA PPM schema are listed here.
PrefixComponent / MeaningNBIDatamartNTDDiscussionsODFObject Definition FieldsPACProject AccountingPFMPortfolio ManagementPPAFinancials (WIP and Matrix Values)PRJProjectsRSMResource ManagementSRMShared SRM Objects (Resources, Companies)
Prefix
Component / Meaning
BIZ
Business
Development
BPM
Business Process Management
CAL
Calendaring
CLB
Collaboration
CMN
Common (Security, Jobs, Reports,
Views, Attributes, etc.)
COP
PMO Accelerator
FIN
Financials
(Cost Plans, Budgets)
INV
Investments
Slide5Database Navigation, cont.
Objects Naming Convention –
Stored
Procs
, Functions, Triggers, etc
.
Type
Type ID
Description
Primary key constraints and indexesPKPrimary key constraints need to be named explicitly as they control the name of the primary key index (which is automatically created)Foreign key constraintsFKxForeign key constraints need to be name explicitly (“alter table XYZ add XYZ_FK1 foreign key..”)Check constraintsCKxi.e. CK1 or CK2Unique indexesUxi.e. U1Non-unique indexesNxi.e. N1 or N2Sequences (Oracle only)Sxi.e. S1 or S2. S1 is used for primary key sequences only, i.e. CMN_LOOKUPS_S1TriggersTRGx or Txi.e. TRG1 or TRG2, T1 or T2
Packages
PKG
Stored Procedures
SP
Functions
FCT
Views
V
Aggregate / Summary
Tables
SUM
Custom
Z_ or ZZ_
Addition of custom object
s
to on premise instances should be signified by “Z_”
or “ZZ_” . This allows the ability to identify customizations simply during an assessment or upgrade initiative
Slide6Database Navigation, cont.
Common Columns
All tables
in CA
PPM house the following attributes. Each time
a custom
object is
created,
these are added by default.
Column IDDescriptionCREATED_DATEDate/time record was createdCREATED_BYUser that created the record. Foreign key to CMN_SEC_USERS.IDLAST_UPDATED_DATEDate/time record was last updatedLAST_UPDATED_BYUser that last updated the record. Foreign key to CMN_SEC_USERS.ID
Slide7Frequently Accessed Areas
A time slice is a flat table containing
data derived from a sliced binary large object (BLOB
)
A
BLOB is a collection of binary data stored as a single entity in a
database
Most reporting tools cannot read a BLOB
Use time slices
to view and report on data over timeTime slices break down the BLOB by several time intervals, such as daily, weekly, and monthly. The majority of time slice data is stored in the PRJ_BLB_SLICES tables with data being controlled by the Time Slicing jobSlice data not in PRJ_BLB_SLICES is controlled via internal slices, triggered by a user action in CA PPMThe PRJ_BLB_SLICEREQUESTS table lists all slices and the tables where the information is stored
Slide8Frequently Accessed Areas, cont.
Time Slices
Slices within the PRJ_BLB_SLICEREQUESTS table
containing populated fields like
frequency, from and to date
are
all populated via the Time Slice job.
You can configure and control most
of these via
the Time Slice area within CA PPM. Examples are listed below. PRJ_BLB_SLICEREQUESTSslice_request_idPRJ_BLB_SLICESPRJ_BLB_SLICES_xODF_SL_x SLICE_REQUEST_ID: Used to connect to PRJ_BLB_SLICEREQUESTS table PRJ_OBJECT_ID: Used to connect to object instance in question. i.e. Resource id, investment id, etc. SLICE: Value of sliceSLICE_DATE: Date of slice (based on interval)#Slice Name1DAILYRESOURCEAVAILCURVE2DAILYRESOURCEACTCURVE3DAILYRESOURCEESTCURVE10DAILYRESOURCEALLOCCURVE11 DAILYRESOURCEBASECURVE164DAILY_INVESTMENT_ESTIMATES4
MONTHLYRESOURCEACTCURVE
5
MONTHLYRESOURCEESTCURVE
6
MONTHLYRESOURCEALLOCCURVE
7
MONTHLYRESOURCEAVAILCURVE
Slide9Frequently Accessed Areas, cont.
Internal Slices
Slices within the PRJ_BLB_SLICEREQUESTS table
without populated fields like frequency
, from and to date
are
updated via
internal
slices (insta-slice
), and takes place after a user action in CA PPM. Examples are listed below. ODF_SSL_xPRJ_BLB_SLICEREQUESTSslice_request_idSLICE_REQUEST_ID: Used to connect to PRJ_BLB_SLICEREQUESTS table PRJ_OBJECT_ID: Used to connect to object instance in question. i.e. Resource id, investment id, etc. SLICE: Value of sliceSLICE_DATE: Date of slice (based on interval)#Slice Name5000020 costplandetail::cost::segment5000024 costplandetail::units::segment5000070 benefitplandetail::benefit::segment5000072 benefitplandetail::variance::segment5000076 benefitplandetail::actual_benefit::segment
Slide10Pull a resource’s ETC per
project
Time Slicing
Exercise #1A
select
i.code
,
i.
name
, r.full_name, sum(SLICE) etcHoursfrom prj_blb_slices sl JOIN prassignment a ON sl.PRJ_OBJECT_ID = a.pridJOIN srm_resources r ON r.id = a.PRRESOURCEIDJOIN prtask t ON a.PRTASKID = t.PRIDJOIN inv_investments I ON t.PRPROJECTID = i.IDwhere SLICE_REQUEST_ID = 5and i.code = ‘xxxx’group by i.code, i.name, r.full_name
Project ID
Project Name
Resource Name
ETC Hours
000021PR
Kens Test Project
Business Analyst
64
000021PR
Kens Test Project
Project Manager
64
000023PR
Test3
Business Analyst
88
000023PR
Test3
Project Manager
88
000025PR
RPC POC Review
Smith, Ken
80
000025PR
RPC POC Review
Doe, Jane
40
Slide11Frequently Accessed Areas, cont.
OBS Data Model
OBS is an integral part of each piece of data within CA PPM, especially with the categorization of resources and investments
Determining and filtering on the related OBS for different objects
is often
a challenge during day to day reporting
The core live tables for OBS do not rely on any jobs for
population,
but are recorded as soon as an OBS change is made in the system
PRJ_OBS_UNITS – Holds all units within each OBS structure, the depth at which it lies in the system, and it’s parent unitPRJ_OBS_ASSOCIATIONS – Holds the association between investment or resource and the OBS unit itselfPRJ_OBS_TYPES – Holds the definition of each OBS structureThe NBI_DIM_OBS table stores flattened OBS information, including the full path of the OBS unitThis table is dependent on the Datamart Extraction being run
Slide12Frequently Accessed Areas, cont.
OBS Data Model – Views / Tables (other)
You can use additional
OBS tables and views
to help you find
hierarchical relationships between
units.
You can use tables like PRJ_OBS_UNITS_FLAT to
query for a unit and all its descendants
You can use views like OBS_UNITS_V to query for a unit and decide for that unit only its descendants, ancestors, or all related units. For example:
Slide13Frequently Accessed Areas, cont.
OBS Data Model – Functions
Finding the OBS path is a common need for
portlet
and report
writing.
It is not always necessary to connect to the
NBI_DIM_OBS
table to get this information if it is needed in real time.
The NBI_GET_OBS_PATH_FCT can be used to pull the path of the current OBS unit by passing it the internal unit ID of the OBS unit. For example:select u.id, NBI_GET_OBS_PATH_FCT (u.id) obsPathfrom prj_obs_units u
Slide14Find the OBS path of all active
resources
OBS Data Model
Exercise #2A
SELECT
r.full_name
,
obs.path
,
obs.obs_type_nameFROM NBI_DIM_OBS obs, PRJ_OBS_ASSOCIATIONS assoc, SRM_RESOURCES rWHERE obs.obs_unit_id = assoc.unit_id AND assoc.record_id = r.id AND assoc.table_name = 'SRM_RESOURCES'AND obs.obs_type_name = <name of OBS>Resource NameOBS PathOBS NameAmerica, CaptainALL/Dept1Financial DepartmentAmerica, Captain
ALL/Rego Consulting
Financial Location
Lantern, Green
ALL/Corp
Financial Department
Lantern, Green
ALL/Global
Financial Location
Hulk, Hulk
ALL/Dept3
Financial Department
Hulk, Hulk
ALL/Rego Consulting
Financial Location
Slide15Return only the investments within a specific branch of the OBS
OBS Data Model
Exercise #2B
Select u.name
unitName
, uv.NAME
childUnit
, i.name,
i.code
from obs_units_v uvJOIN prj_obs_units u ON u.id = uv.unit_idJOIN prj_obs_associations assoc ON uv.LINKED_UNIT_ID = assoc.unit_id and assoc.table_name = 'SRM_PROJECTS'JOIN inv_investments I ON assoc.record_id = i.idwhere uv.UNIT_MODE = 'OBS_UNIT_AND_CHILDREN'and u.name = ‘XXX'Parent DepartmentChild DepartmentInvestment NameInvestment IDDept1Dept1
RUser51
RUser51
Dept1
Dept1
AZ - Project 1
000057PR
Dept1
Dept1a
Sharepoint Demo
000014PR
Dept1
Dept1a
2015 Infrastructure Services
000039PR
Dept1
Dept1b
2015 Security Compliance
000043PR
Dept1
Dept1b
Keri Test Project
000013PR
Slide16CA PPM Database Content
Portfolio Contents
Portfolios allow you to create and review a collection of investments that interest stakeholders in your business
As of 13.2 portfolios are now stored in a new table structure starting with
PFM_
,
with
PFM_PORTFOLIOS
being the main table
Old portfolio contents are archived to the XBKP_PMA tables for reference or conversion
Slide17CA PPM Database Content, cont.
Portfolio Contents, cont.
PFM_INVESTMENTS
Contains
a copy of investment attributes in the context of a
portfolio
Link to Investment ID is on this
table
Slide18CA PPM Database Content, cont.
Portfolio ContentsThe Waterline view allows you to view and rank the investments in a
portfolio
The below query allows you to find all investments above the
waterline
SELECT port.NAME ,inv.name
invName
,
above_waterline
FROM PFM_PORTFOLIO_RANKING_V wlJOIN INV_INVESTMENTS inv ON inv.id = instance_idJOIN PFM_PORTFOLIOS port ON port.id = wl.context_idWHERE context_type = 'pfm' AND above_waterline = 1AND wl.context_id = {PFM_PORTFOLIOS.ID}
Slide19Portfolio Contents
Exercise #3
Select
INV.Name
,
INV.Code
,
PP.Name
PortfolioName,pp.CODE PortfolioCodeFrom PFM_PORTFOLIOS PP, PFM_INVESTMENTS PI, INV_INVESTMENTS INVWhere pi.Portfolio_id = PP.idand INV.ID = pi.investment_id
Slide20CA PPM Database Content, cont.
Financial Plan Tables
Financial Cost, Budget, and Benefit plan data can be found using the following
tables
FIN_PLANS –
Holds high level information on the type of plan, including start and end date, overall units and cost, and the period type (MONTHLY, YEARLY, etc.)
FIN_COST_PLAN_DETAILS –
Holds detailed plan data by each group by element and whether it’s been populated manually or via the resource planThe following slice tables holds detailed interval data for all financial plan typesODF_SSL_CST_DTL_COST, ODF_SSL_CST_DTL_UNITS – Holds the detailed cost and units, respectively, for the cost plan and budget on all investment typesODF_SSL_BFT_DTL_X – These tables hold the detailed benefit plan information for all investment typesODF_SSL_CST_DTL_REV – Holds the detailed revenue information for all investment types
Slide21Financial Plan Tables: Simple Budget Example
CA PPM Database Content, cont.
SELECT I.CODE, I.NAME,
FP.approved
,
FP.departcode
,
fp.locationid
, FP.CLASS
, F.budget_cst_total, F.budget_cst_start, F.budget_cst_finish, F.planned_cst_total, F.planned_cst_finish, F.planned_cst_startFROM INV_INVESTMENTS I JOIN PAC_MNT_PROJECTS FP ON I.ID = FP.ID JOIN odf_object_instance_mapping om ON f.id = om.secondary_object_instance_id AND om.primary_object_instance_id = i.id JOIN FIN_FINANCIALS F JOIN INV_PROJECTS IP ON I.ID = IP.PRIDWHERE F.ODF_OBJECT_CODE = 'project' AND AND om.secondary_object_instance_code = 'financials' AND om.primary_object_instance_code = 'project' AND IP.IS_PROGRAM = 0
Project ID
Project Name
Department ID
Location ID
Budget Cost
Budget Start
Budget Finish
Planned Cost
Planned Start
Planned Finish
000001PR
Jen Test Project
reg_rego_consulting
reg_rego_consulting
100000
1-Nov-14
1-Aug-14
000002PR
Test Idea [JS]
reg_rego_consulting
reg_rego_consulting
30000
4-Sep-14
5-Dec-14
30000
5-Dec-14
4-Sep-14
Slide22The below query shows the total cost and hours by detailed financial plan record
. This example uses Transaction Class and Cost Type as
the group by
.
Financial Plan Tables: Detailed Plans Example #
1
CA PPM Database Content, cont.
SELECT I.CODE
invID
, I.NAME invName, PMP.departcode invDeptID, PMP.locationid invLocID, FP.NAME finPlanName, (select tc.description from transclass tc where tc.id = fpd.transaction_class_id) transclass,(select period_name from biz_com_periods where id = fp.start_period_id) startPer,(select period_name from biz_com_periods where id = fp.end_period_id) endPer, FPD.TOTAL_UNITS , FPD.TOTAL_COSTFROM INV_INVESTMENTS I PAC_MNT_PROJECTS PMP ON I.ID = PMP.ID FIN_PLANS FP ON FP.OBJECT_ID = I.ID AND FP.OBJECT_CODE = 'project'
FIN_COST_PLAN_DETAILS FPD ON
FP.ID
= FPD.PLAN_ID
WHERE FP.IS_PLAN_OF_RECORD
= 1
AND
FP.PLAN_TYPE_CODE = 'FORECAST'
Project ID
Project Name
Department ID
Location ID
Plan Name
Cost Type
Transaction Class
Start Period
End Period
Total Units
Total Cost
SPS_001
Test Project
corp
US
Cost Plan
Capital
Expense
Jan 1, 2014-Dec 31, 2014
Jan 1, 2015-Dec 31, 2015
100000
SPS_001
Test Project
corp
US
Cost Plan
Operating
Expense
Jan 1, 2014-Dec 31, 2014
Jan 1, 2015-Dec 31, 2015
SPS_001
Test Project
corp
US
Cost Plan
Capital
Labor
Jan 1, 2014-Dec 31, 2014
Jan 1, 2015-Dec 31, 2015
SPS_001
Test Project
corp
US
Cost Plan
Operating
Labor
Jan 1, 2014-Dec 31, 2014
Jan 1, 2015-Dec 31, 2015
50000
Slide23Expanding on the example from the former slide, this query shows the cost by
month
Financial Plan Tables: Detailed Plans Example #
2
CA PPM Database Content, cont.
SELECT I.CODE
invID
, I.NAME
invName
, PMP.departcode invDeptID, PMP.locationid invLocID, FP.NAME finPlanName, (select clv.name from cmn_lookups_v clv where clv.id = FPD.COST_TYPE_ID and clv.lookup_type = 'LOOKUP_FIN_COSTTYPECODE' and clv.language_code = 'en') costType, (select tc.description from transclass tc where tc.id = fpd.transaction_class_id) transclass, cpu.start_date, cpu.finish_date-1, (cpu.finish_date-cpu.start_date)*cpu.slice perCostFROM INV_INVESTMENTS IJOIN PAC_MNT_PROJECTS PMP ON I.ID = PMP.IDJOIN FIN_PLANS FP ON FP.OBJECT_ID = I.IDJOIN FIN_COST_PLAN_DETAILS FPD ON FP.ID = FPD.PLAN_ID JOIN odf_ssl_cst_dtl_cost
cpu
ON
FPD.ID = CPU.PRJ_OBJECT_ID
WHERE
FP.OBJECT_CODE = 'project'
AND FP.IS_PLAN_OF_RECORD = 1 AND FP.PLAN_TYPE_CODE = 'FORECAST
'
Project ID
Project Name
Department ID
Location ID
Plan Name
Cost Type
Transaction Class
Start Period
End Period
Total Cost
SPS_001
Demo Project
corp
US
Cost Plan
Capital
Expense
1-Jan-14
31-Dec-14
50000.00
SPS_001
Demo Project
corp
US
Cost Plan
Capital
Expense
1-Jan-15
31-Dec-15
50000.00
SPS_001
Demo Project
corp
US
Cost Plan
Operating
Labor
1-Jan-14
31-Dec-14
25000.00
SPS_001
Demo Project
corp
US
Cost Plan
Operating
Labor
1-Jan-15
31-Dec-15
25000.00
Slide24CA PPM Database Content, cont.
Financial Actuals Tables
Financial tables are utilized to capture both hours and cost for resources by project and task. Data gets processed to these tables via the processing of timesheets or via manually
entered or
imported transactions.
The financial tables or “WIP” tables are the only ones that keep historical information related to a resource and investment at the point in which the transaction was recorded
The major tables accessed for financial data are
PPA_WIP
and PPA_WIP_VALUESPPA_WIP – Stores all primary transactions (Labor, Materials, Equipment, and Expense).PPA_WIP_VALUES – Stores multi-currency values for rows in the PPA_WIP tableGet the most current transactions by using Status = 0 when querying dataMake sure to choose the CURRENCY_TYPE when querying for costPPA_WIPPPA_WIP_VALUEStransno
Slide25CA PPM Database Content, cont.
Hierarchical Relationships
Hierarchies within Investments allow for a quick rollup view of information for related children investments in CA PPM. On this slide we will show how to query for those hierarchal
linkages
INV_HIERARCHIES_FLAT
This
denormalized
table stores data based on
INV_HIERARCHIES
. The flattened table contains parent_id and child_id entries for all descendants of a given investment parent_id that has a hierarchy. link_source_id contains the ID of the immediate parent of the child and allows rapid retrieval of all descendants within a hierarchy. Examining link_source_id also allows you to retrieve the original hierarchical order.
Slide26Select
MAS.NAME
MasterName
,
MAS.CODE
MasterCode
,
SUB.NAME
SubName
,SUB.CODE SubCodeFROM INV_HIERARCHIES IHInner Join INV_INVESTMENTS MAS on MAS.ID = IH.PARENT_IDInner Join INV_PROJECTS MP on MP.PRID = MAS.IDInner Join INV_INVESTMENTS SUB on SUB.ID = IH.CHILD_IDInner Join INV_PROJECTS SP on SP.PRID = SUB.idWhere SP.IS_PROGRAM = 0and MP.IS_PROGRAM = 0Order by MAS.CodeExercise #6 – Hierarchical Relationships
Slide27CA PPM Database Content, cont.
Hierarchical
Relationships, cont.
INV_HIERARCHIES_FLAT
Join to the
INV_INVESTMENTS
on the
ID = PARENT_ID
or CHILD_ID fields from the hierarchy table.
Same table is used for multiple purposes. Filter for Program! INV_PROJECTS . IS_PROGRAMReference the Investments twiceOnce for MasterOnce for SubINV_HIERARCHIES_FLATINV_INVESTMENTS(Parent)INV_INVESTMENTS(Child)INV_PROJECTSIS_PROGRAMIS_TEMPLATEINV_PROJECTSIS_PROGRAMIS_TEMPLATE
PARENT_ID=ID
CHILD_ID=ID
ID=PRID
ID=PRID
Slide28CA PPM Database Content, cont.
Idea to Project Linkage
Ideas often serve as a source for
investments (for example, projects),
and for early analysis prior to being approved and becoming an investment
Understanding the reasons why a project is in flight and the original source of the business request is an important link to track in CA PPM. The below tables and SQL will identify the sources of that information
.
INV_INVESTMENTS
houses all investment data, such as projects, applications, products, services, other, and even
idea.This table is all that is required to create the linkage and get the related information:select i.name prjName, i.code prjID, idea.code ideaID, idea.name ideaNamefrom inv_investments ijoin inv_investments idea on i.idea_id = idea.idwhere i.odf_object_code = 'project'
Slide29Additional Examples & Useful Functions
Please find some additional examples of queries in the following slides
Run and Have Fun!
Slide30Pull a
project’s cost plan and budget information by month
Time Slicing
Example #1
SELECT
I.CODE
invID
, FP.CODE
planID
,cpu.start_date startDate, cpu.finish_date-1 finishDate,SUM(ROUND(cpu.slice*(cpu.finish_date-cpu.start_date))) costFROM INV_INVESTMENTS I JOIN INV_PROJECTS IP ON I.ID = IP.PRID JOIN FIN_PLANS fp ON fp.object_id = i.id AND fp.object_code = 'project' JOIN fin_cost_plan_details fpd ON fp.id = fpd.plan_id JOIN srm_resources r ON fpd.ROLE_ID = r.id JOIN odf_ssl_cst_dtl_cost cpu ON fpd.id = cpu.prj_object_idWHERE IP.IS_PROGRAM
= 0
AND
fp.is_plan_of_record
=
1
AND
fp.plan_type_code
= 'FORECAST'
AND
fpd.source
= 'COSTPLAN'
group by I.CODE, FP.CODE,
cpu.start_date
,
cpu.finish_date
Project ID
Cost Plan ID
Cost Plan Name
Month Start
Month End
Cost
000007PR
CP1
Allocation Test
1-Oct-14
31-Oct-14
24500
000035PR
T01
Test 01
1-Feb-15
28-Feb-15
6000
000036PR
T01
Test 01
1-Feb-15
28-Feb-15
6000
Client Request
CP
Initial Plan
1-Jan-15
31-Jan-15
6000
Client Request
CP
Initial Plan
1-Feb-15
28-Feb-15
6000
Client Request
CP
Initial Plan
1-Mar-15
31-Mar-15
6000
Client Request
CP
Initial Plan
1-Apr-15
30-Apr-15
6000
Slide31Example #2
select
pu.name
branchName
,
pu.DEPTH
branchLvl
, cup.name
ParentOBS, cu.name ChildOBS, cu.DEPTH childLvlfrom prj_obs_units_flat uf, prj_obs_units pu, prj_obs_units cu, prj_obs_units cupwhere uf.branch_unit_id = pu.idand uf.UNIT_ID = cu.idand cu.PARENT_ID = cup.idand pu.name = 'XXX’select u.name unitName, pu.name parentUnit, uv.NAME childUnit, uv.DEPTH childDepthfrom obs_units_v uv, prj_obs_units u, prj_obs_units puwhere u.id
=
uv.unit_id
and
u.name
= ‘XXX'
and
uv.UNIT_MODE
= 'OBS_UNIT_AND_CHILDREN'
and
uv.PARENT_ID
=
pu.id
OBS Data Model
Slide32Example #3
Find the total units and cost for a cost plan of record for a project and compare it against the budget for that
project
SELECT I.CODE
invID
, I.NAME
invName
, FP.NAME
finPlanName
, FP.TOTAL_UNITS cpUnits, FP.TOTAL_COST cpCost, BP.TOTAL_UNITS budUnits, BP.TOTAL_COST budCostFROM INV_INVESTMENTS I LEFT JOIN FIN_PLANS FP ON FP.OBJECT_ID = I.ID AND FP.OBJECT_CODE = 'project' AND FP.PLAN_TYPE_CODE = 'FORECAST' LEFT JOIN FIN_PLANS BP ON BP.OBJECT_ID = I.ID AND BP.OBJECT_CODE = 'project' AND BP.PLAN_TYPE_CODE = 'BUDGET'WHERE FP.IS_PLAN_OF_RECORD = 1Project IDProject NameCost Plan NamePlanned UnitsPlanned CostBudgeted UnitsBudgeted CostAmtrak Enhancements
Verifi Amtrak Enhancements
Initial Plan
0
60000
0
50000
CB_Automation_P2
Verifi CBR Efficiency Phase 2
Initial Plan
0
25000
0
25000
Financial Plan Tables
Slide33Example #4 – Financial Actuals Table
Query for the hours and cost of each resource on the team of an investment. Include
the following columns in the result
set.
Project ID
Project Name
Resource department
Resource role
Total HoursTotal Cost
Slide34Example #4 cont’d– Financial Actuals Table
SELECT i.name,
i.code
,
wip.emplyhomedepart
, (select
r.full_name
from
srm_resources
r where r.unique_name = wip.RESOURCE_CODE) resName,(select r.full_name from srm_resources r where r.unique_name = wip.role_code) resRole, sum(wipv.totalcost)from ppa_wip wip,inv_investments i,ppa_wip_values wipvwhere i.id = wip.investment_idand wip.transno = wipv.transnoand wipv.currency_type = 'HOME'and wip.status = 0group by i.name, i.code, wip.EMPLYHOMEDEPART, wip.ROLE_CODE, wip.resource_codeProject NameProject IDResource Dept IDResource NameResource RoleActual CostCost Type Revision
123121231412
corp
Smith, David
Project Manager
2500
JG Project 1
JGPROJ01
Dept1
Jones, James
Developer
15129
Slide35Useful Functions
Below is a lis
t of functions that can be used to assist conversions inside Clarity:
COP_CALC_FINISH_DATE
: Does finish date calculation on dates that have 12AM midnight as timestamp to be the current day and not the next day. It also removes the time stamp;
ODF_AUD_DATE_FCT
: Puts the date in a format Clarity can accept
Example
SELECT
I.NAME, I.SCHEDULE_START, I.SCHEDULE_FINISH, odf_aud_date_fct(I.SCHEDULE_FINISH) clarityFmtFinish, T.PRFINISH, COP_CALC_FINISH_FCT(T.PRFINISH) noTimeStampFinishFROM INV_INVESTMENTS I INNER JOIN PRTASK T ON T.PRPROJECTID = I.ID
Slide36Questions
Phone
888.813.0444
Email
info@regouniversity.com
Website
www.regouniversity.com
We
hope that you found
this session informative
and worthwhile. Our primary goal was to increase your understanding of
the topic and CA PPM in general.
There
were many
concepts covered
during the
session, if you
would like to contact any presenter with questions, please
reach out to us.
Thank
you for attending
regoUniversity
2015!
Slide37Additional Contact Info
Name
Virginia DeCeglia
Phone
516.924.2853
Email
Virginia.deceglia
@regoconsulting.com
Name
Chris Shaffer
Phone
830.355.2379
Email
Chris.shaffer@regoconsulting.com