Advanced Data Model

The data model overview provided by Regouniversity covers core tables, time slice tables, DataMart tables, and database navigation objects. It discusses the data storage areas, real-time data updates, time frame views, table population methods, and naming conventions used in the Clarity Educational Community. Understanding these concepts is crucial for navigating the database effectively.

• Database Navigation
• Data Model Overview
• Clarity Educational Community
• Table Prefix Definitions
• Database Naming Conventions

arr_t Follow

Uploaded on Feb 15, 2025 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.

Presentation Transcript

1. www.regouniversity.com Clarity Educational Community Advanced Data Model Accessible Data Presented By: Virginia DeCeglia, Chris Shaffer | Date Prepared: May, 2015

2. Agenda Data Model Overview Database Navigation Frequently Accessed Areas Timeslices OBS CA PPM Database Content Portfolio contents Financial Plans & Financial Actuals Hierarchy Idea to Project 2 Clarity Educational Community

3. Data 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 3 Clarity Educational Community

4. Database 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. Prefix Component / Meaning Prefix Component / Meaning BIZ Business Development NBI Datamart BPM Business Process Management NTD Discussions CAL Calendaring ODF Object Definition Fields CLB Collaboration PAC Project Accounting CMN Common (Security, Jobs, Reports, Views, Attributes, etc.) PFM Portfolio Management PPA Financials (WIP and Matrix Values) COP PMO Accelerator PRJ Projects FIN Financials (Cost Plans, Budgets) RSM Resource Management INV Investments SRM Shared SRM Objects (Resources, Companies) 4 Clarity Educational Community

5. Database Navigation, cont. Objects Naming Convention Stored Procs, Functions, Triggers, etc. Type Type ID Description Primary key constraints and indexes PK Primary key constraints need to be named explicitly as they control the name of the primary key index (which is automatically created) Foreign key constraints FKx Foreign key constraints need to be name explicitly ( alter table XYZ add XYZ_FK1 foreign key.. ) Check constraints CKx i.e. CK1 or CK2 Unique indexes Ux i.e. U1 Non-unique indexes Nx i.e. N1 or N2 Sequences (Oracle only) Sx i.e. S1 or S2. S1 is used for primary key sequences only, i.e. CMN_LOOKUPS_S1 Triggers TRGx or Tx i.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 objects 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 5 Clarity Educational Community

6. Database 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 ID Description CREATED_DATE Date/time record was created CREATED_BY User that created the record. Foreign key to CMN_SEC_USERS.ID LAST_UPDATED_DATE Date/time record was last updated LAST_UPDATED_BY User that last updated the record. Foreign key to CMN_SEC_USERS.ID 6 Clarity Educational Community

7. Frequently 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 time Time 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 job Slice data not in PRJ_BLB_SLICES is controlled via internal slices, triggered by a user action in CA PPM The PRJ_BLB_SLICEREQUESTS table lists all slices and the tables where the information is stored 7 Clarity Educational Community

8. Frequently 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. slice_request_id PRJ_BLB_SLICES PRJ_BLB_SLICES_x ODF_SL_x PRJ_BLB_SLICEREQUESTS # 1 2 3 10 11 164 4 5 6 7 Slice Name DAILYRESOURCEAVAILCURVE DAILYRESOURCEACTCURVE DAILYRESOURCEESTCURVE DAILYRESOURCEALLOCCURVE DAILYRESOURCEBASECURVE DAILY_INVESTMENT_ESTIMATES MONTHLYRESOURCEACTCURVE MONTHLYRESOURCEESTCURVE MONTHLYRESOURCEALLOCCURVE MONTHLYRESOURCEAVAILCURVE 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 slice SLICE_DATE: Date of slice (based on interval) 8 Clarity Educational Community

9. Frequently 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. slice_request_id ODF_SSL_x PRJ_BLB_SLICEREQUESTS # Slice Name SLICE_REQUEST_ID: Used to connect to PRJ_BLB_SLICEREQUESTS table 5000020 costplandetail::cost::segment 5000024 costplandetail::units::segment 5000070 benefitplandetail::benefit::segment 5000072 benefitplandetail::variance::segment 5000076 benefitplandetail::actual_benefit::segment PRJ_OBJECT_ID: Used to connect to object instance in question. i.e. Resource id, investment id, etc. SLICE: Value of slice SLICE_DATE: Date of slice (based on interval) 9 Clarity Educational Community

10. Exercise #1A Time Slicing Pull a resource s ETC per project select i.code, i.name, r.full_name, sum(SLICE) etcHours from prj_blb_slices sl JOIN prassignment a ON sl.PRJ_OBJECT_ID = a.prid JOIN srm_resources r ON r.id = a.PRRESOURCEID JOIN prtask t ON a.PRTASKID = t.PRID JOIN inv_investments I ON t.PRPROJECTID = i.ID where SLICE_REQUEST_ID = 5 and i.code = xxxx group by i.code, i.name, r.full_name Project ID Project Name 000021PR Kens Test Project Business Analyst 000021PR Kens Test Project Project Manager 000023PR Test3 000023PR Test3 000025PR RPC POC Review Smith, Ken 000025PR RPC POC Review Doe, Jane Resource Name ETC Hours 64 64 88 88 80 40 Business Analyst Project Manager 10 Clarity Educational Community

11. Frequently 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 unit PRJ_OBS_ASSOCIATIONS Holds the association between investment or resource and the OBS unit itself PRJ_OBS_TYPES Holds the definition of each OBS structure The NBI_DIM_OBS table stores flattened OBS information, including the full path of the OBS unit This table is dependent on the Datamart Extraction being run 11 Clarity Educational Community

12. Frequently 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: 12 Clarity Educational Community

13. Frequently 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) obsPath from prj_obs_units u 13 Clarity Educational Community

14. Exercise #2A OBS Data Model Find the OBS path of all active resources SELECT r.full_name, obs.path, obs.obs_type_name FROM NBI_DIM_OBS obs, PRJ_OBS_ASSOCIATIONS assoc, SRM_RESOURCES r WHERE 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 Name OBS Path America, Captain ALL/Dept1 America, Captain ALL/Rego Consulting Financial Location Lantern, Green ALL/Corp Lantern, Green ALL/Global Hulk, Hulk ALL/Dept3 Hulk, Hulk ALL/Rego Consulting Financial Location OBS Name Financial Department Financial Department Financial Location Financial Department 14 Clarity Educational Community

15. Exercise #2B OBS Data Model Return only the investments within a specific branch of the OBS Select u.name unitName, uv.NAME childUnit, i.name, i.code from obs_units_v uv JOIN prj_obs_units u ON u.id = uv.unit_id JOIN 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.id where uv.UNIT_MODE = 'OBS_UNIT_AND_CHILDREN' and u.name = XXX' Parent Department Child Department Dept1 Dept1 Dept1 Dept1 Dept1 Dept1 Investment Name RUser51 AZ - Project 1 Sharepoint Demo 2015 Infrastructure Services 000039PR 2015 Security Compliance Keri Test Project Investment ID RUser51 000057PR 000014PR Dept1 Dept1 Dept1a Dept1a Dept1b Dept1b 000043PR 000013PR 15 Clarity Educational Community

16. CA 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 16 Clarity Educational Community

17. CA 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 17 Clarity Educational Community

18. CA PPM Database Content, cont. Portfolio Contents The 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 wl JOIN INV_INVESTMENTS inv ON inv.id = instance_id JOIN PFM_PORTFOLIOS port ON port.id = wl.context_id WHERE context_type = 'pfm' AND above_waterline = 1 AND wl.context_id = {PFM_PORTFOLIOS.ID} 18 Clarity Educational Community

19. Exercise #3 Portfolio Contents Select INV.Name, INV.Code, PP.Name PortfolioName, pp.CODE PortfolioCode From PFM_PORTFOLIOS PP , PFM_INVESTMENTS PI , INV_INVESTMENTS INV Where pi.Portfolio_id = PP.id and INV.ID = pi.investment_id 19 Clarity Educational Community

20. CA 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 plan The following slice tables holds detailed interval data for all financial plan types ODF_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 types ODF_SSL_BFT_DTL_X These tables hold the detailed benefit plan information for all investment types ODF_SSL_CST_DTL_REV Holds the detailed revenue information for all investment types 20 Clarity Educational Community

21. CA PPM Database Content, cont. Financial Plan Tables: Simple Budget Example 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_start FROM 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.PRID WHERE 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 Budget Cost Budget Start Budget Finish Planned Cost 100000 1-Nov-14 Planned Start Planned Finish Project ID 000001PR Project Name Jen Test Project reg_rego_consulting reg_rego_consulting Department ID Location ID 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 21 Clarity Educational Community

22. CA PPM Database Content, cont. Financial Plan Tables: Detailed Plans Example #1 The 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. 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_COST FROM 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' Department ID corp corp corp corp Location ID US US US US Plan Name Cost Plan Capital Cost Plan Operating Expense Cost Plan Capital Cost Plan Operating Labor Transaction Class Expense Project ID SPS_001 SPS_001 SPS_001 SPS_001 Project Name Test Project Test Project Test Project Test Project Cost Type Start Period Jan 1, 2014-Dec 31, 2014 Jan 1, 2015-Dec 31, 2015 Jan 1, 2014-Dec 31, 2014 Jan 1, 2015-Dec 31, 2015 Jan 1, 2014-Dec 31, 2014 Jan 1, 2015-Dec 31, 2015 Jan 1, 2014-Dec 31, 2014 Jan 1, 2015-Dec 31, 2015 End Period Total Units Total Cost 100000 Labor 50000 22 Clarity Educational Community

23. CA PPM Database Content, cont. Financial Plan Tables: Detailed Plans Example #2 Expanding on the example from the former slide, this query shows the cost by month SELECT I.CODE invID, I.NAME invName, PMP.departcodeinvDeptID, PMP.locationidinvLocID, 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.descriptionfrom transclasstc where tc.id = fpd.transaction_class_id) transclass , cpu.start_date, cpu.finish_date-1 , (cpu.finish_date-cpu.start_date)*cpu.sliceperCost FROM INV_INVESTMENTS I JOIN PAC_MNT_PROJECTS PMP ON I.ID = PMP.ID JOIN FIN_PLANS FP ON FP.OBJECT_ID = I.ID JOIN FIN_COST_PLAN_DETAILS FPD ON FP.ID = FPD.PLAN_ID JOIN odf_ssl_cst_dtl_costcpu 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' Department ID corp corp corp corp Location ID US US US US Transaction Class Expense Expense Project ID SPS_001 SPS_001 SPS_001 SPS_001 Project Name Demo Project Demo Project Demo Project Demo Project Plan Name Cost Type Cost Plan Cost Plan Cost Plan Cost Plan Start Period End Period Total Cost 1-Jan-14 31-Dec-14 1-Jan-15 31-Dec-15 1-Jan-14 31-Dec-14 1-Jan-15 31-Dec-15 Capital Capital Operating Labor Operating Labor 50000.00 50000.00 25000.00 25000.00 23 Clarity Educational Community

24. CA 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_VALUES PPA_WIP_VALUES PPA_WIP transno PPA_WIP Stores all primary transactions (Labor, Materials, Equipment, and Expense). PPA_WIP_VALUES Stores multi-currency values for rows in the PPA_WIP table Get the most current transactions by using Status = 0 when querying data Make sure to choose the CURRENCY_TYPE when querying for cost 24 Clarity Educational Community

25. CA 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. 25 Clarity Educational Community

26. Exercise #6 Hierarchical Relationships Select MAS.NAME MasterName, MAS.CODE MasterCode, SUB.NAME SubName, SUB.CODE SubCode FROM INV_HIERARCHIES IH Inner Join INV_INVESTMENTS MAS on MAS.ID = IH.PARENT_ID Inner Join INV_PROJECTS MP on MP.PRID = MAS.ID Inner Join INV_INVESTMENTS SUB on SUB.ID = IH.CHILD_ID Inner Join INV_PROJECTS SP on SP.PRID = SUB.id Where SP.IS_PROGRAM = 0 and MP.IS_PROGRAM = 0 Order by MAS.Code 26 Clarity Educational Community

27. CA 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_PROGRAM Reference the Investments twice Once for Master Once for Sub INV_HIERARCHIES_FLAT PARENT_ID=ID CHILD_ID=ID INV_INVESTMENTS (Parent) INV_INVESTMENTS (Child) ID=PRID ID=PRID INV_PROJECTS INV_PROJECTS IS_PROGRAM IS_TEMPLATE IS_PROGRAM IS_TEMPLATE 27 Clarity Educational Community

28. CA 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 ideaName from inv_investments i join inv_investments idea on i.idea_id = idea.id where i.odf_object_code = 'project' 28 Clarity Educational Community

29. Additional Examples & Useful Functions Please find some additional examples of queries in the following slides Run and Have Fun! 29 Clarity Educational Community

30. Example #1 Time Slicing Pull a project s cost plan and budget information by month 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))) cost FROM 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_id WHERE 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 Cost Plan ID CP1 T01 T01 Project ID 000007PR 000035PR 000036PR Client Request CP Client Request CP Client Request CP Client Request CP Cost Plan Name Month Start Month End Cost Allocation Test 1-Oct-14 Test 01 1-Feb-15 Test 01 1-Feb-15 Initial Plan 1-Jan-15 Initial Plan 1-Feb-15 Initial Plan 1-Mar-15 31-Mar-15 6000 Initial Plan 1-Apr-15 31-Oct-14 24500 28-Feb-15 6000 28-Feb-15 6000 31-Jan-15 6000 28-Feb-15 6000 30-Apr-15 6000 30 Clarity Educational Community

31. Example #2 OBS Data Model select pu.name branchName, pu.DEPTH branchLvl, cup.name ParentOBS, cu.name ChildOBS, cu.DEPTH childLvl from prj_obs_units_flat uf, prj_obs_units pu, prj_obs_units cu, prj_obs_units cup where uf.branch_unit_id = pu.id and uf.UNIT_ID = cu.id and cu.PARENT_ID = cup.id and pu.name = 'XXX select u.name unitName, pu.name parentUnit, uv.NAME childUnit, uv.DEPTH childDepth from obs_units_v uv, prj_obs_units u, prj_obs_units pu where u.id = uv.unit_id and u.name = XXX' and uv.UNIT_MODE = 'OBS_UNIT_AND_CHILDREN' and uv.PARENT_ID = pu.id 31 Clarity Educational Community

32. Example #3 Financial Plan Tables 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 budCost FROM 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 = 1 Cost Plan Name Planned Cost Budgeted Units Budgeted Cost Project ID Project Name Planned Units Amtrak Enhancements Verifi Amtrak Enhancements Initial Plan 0 60000 0 50000 CB_Automation_P2 Verifi CBR Efficiency Phase 2 Initial Plan 0 25000 0 25000 32 Clarity Educational Community

33. Example #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 Hours Total Cost 33 Clarity Educational Community

34. Example #4 contd 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 wipv where i.id = wip.investment_id and wip.transno = wipv.transno and wipv.currency_type = 'HOME' and wip.status = 0 group by i.name, i.code, wip.EMPLYHOMEDEPART, wip.ROLE_CODE, wip.resource_code Resource Dept ID corp Dept1 Project Name Cost Type Revision JG Project 1 Project ID 123121231412 JGPROJ01 Resource Name Smith, David Jones, James Resource Role Project Manager Developer Actual Cost 2500 15129 34 Clarity Educational Community

35. Useful Functions Below is a list 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) noTimeStampFinish FROM INV_INVESTMENTS I INNER JOIN PRTASK T ON T.PRPROJECTID = I.ID 35 Clarity Educational Community

36. Questions 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. Phone 888.813.0444 Email info@regouniversity.com There were many concepts covered during the session, if you would like to contact any presenter with questions, please reach out to us. Website www.regouniversity.com Thank you for attending regoUniversity 2015! 36 Clarity Educational Community

37. Additional Contact Info Name Virginia DeCeglia Name Chris Shaffer Phone 516.924.2853 Phone 830.355.2379 Email Virginia.deceglia@regocons ulting.com Email Chris.shaffer@regoconsulti ng.com 37 Clarity Educational Community