Clarity

starting 13.2 pma_financial_values is gone.

this table stores aggregated values of planned\budgeted item per period (e.g. month).

do you know which table has replaced it?

thanks

Have you checked the changed impact guide ?

https://support.ca.com/cadocs/0/CA%20Clarity%20PPM%2013%202%2000%20%20On%20Premise%20-ENU/Bookshelf_Files/HTML/chgimpact/index.htm

NJ

Prior to 13.2 it is like this

In v13.2 it is like this

So my guess is

PFM_CLOB_CURVES

This table contains timesliced values for portfolio attributes.

That is the data that was in PM_FINANCIAL_VALUES

does not exist any more in v13.2 or after upgrade to v13.2.

The respective values for the new portfolio data modet would be in the blobs as before, but in v13.2 you do not have access to them any more (If you look at the posts by Jörgen and others)

hi,

what we would like to have is a table (like the old pma_financial_values) that stores for each month the budget\planned\actual values.

this is for reporting reasons against the project financial plans.

say that I need just for January-October 2014 the planned value and in the same query also the actual in that period.

how do I achieve this result, after 13.2?

the change\impact guide doenst help very much.

thanks,

Andrea

Hello Andrea,

From 13.2, there is no table wit consoildated financial data. The data is stored in Clobs and timslicing stores in slice tables. You might have to query for the information. Please see the following technical document for further details.

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec440146.aspx

Regards

Chandrani

Just in case Andrea is unable to access the link -

Clarity: Can you provide more details regarding the Financial Planning Slice Tables

Document ID:  TEC440146
Last Modified Date:  11/20/2014
  Hide Technical Document Details

• Products
  • CA PPM
  • CA Governance, Risk ＆ Compliance Manager
• Releases
  • CA PPM:Release:12.1
  • CA PPM:Release:13.0
  • CA PPM:Release:13.0.1
  • CA PPM:Release:13.1
  • CA PPM:Release:13.2
  • CA PPM:Release:13.3
  • CA PPM:Release:14.1
• Components
  • CLARITY HOSTED FINANCIALS, TIME ＆ DATA MANAGEMENT
  • CLARITY ON DEMAND FINANCIALS, TIME ＆ DATA MANAGEMENT
  • CLARITY PPM FINANCIALS, TIME ＆ DATA MANAGEMENT

Description:

Since documentation is lacking regarding the Financial Planning Slice tables, can you please let us know more information about the tables that store the detailed financial planning data for Cost Plans, Benefit Plans and Budget Plans?

Solution:

The FIN_PLANS table stores all the financial plan-to-investment relationship.

There are two types of tables that hold the time-scaled values (TSV) data for each cell within the detailed financial plans. The tables that are named like ODF_SSL_* contain the slice values for the TSV. The tables that are named like ODF_SL_* contain internal, system-defined and user-defined sliced values for financial detailed plans. These tables are dependent on a successful 'Time Slicing' job completion.

Sliced Data
The ODF_SL tables are used to display data in the Investment > Hierarchy > Detail View. For ensuring that updated values are reflected on these views, execute the 'Time Slicing' job.

TSV Data
The tables below link to the FIN_BENEFIT_PLAN_DETAILS which contains the Benefit Properties and reflect the time-scaled values that appear on the detailed financial plans. These tables do not depend on the 'Time Slicing' job.

select id, request_name, table_name from prj_blb_slicerequests where request_name like '%benefitplandetail%::segment%

The tables below link to the FIN_PLAN_DETAILS for Cost Plans

select id, request_name, table_name from prj_blb_slicerequests where request_name like '%costplandetail%::segment%' 

Below is a sample query that you can use to get the cell details for the 'Actual Benefit' value on a specific Benefit Plan.

Based on the information provided above you can modify this query to get results for other cells and other plan types.

For MSSQL - Actual Benefit Plan Data

SELECT I.ID PROJECT_ID, I.NAME PROJECT_NAME, P.ID PLAN_ID , P.NAME PLAN_NAME, D.DETAIL DETAIL_NAME, ABFT.START_DATE, ABFT.SLICE, ROUND(ABFT.SLICE*(DATEDIFF(DAY, ABFT.START_DATE, ABFT.FINISH_DATE)),2) CALC_SLICE FROM ODF_SSL_BFT_DTL_ABFT ABFT, FIN_BENEFIT_PLAN_DETAILS D, FIN_PLANS P, INV_INVESTMENTS I WHERE ABFT.PRJ_OBJECT_ID = D.ID AND D.PLAN_ID = P.ID AND P.OBJECT_ID = 5001379 AND P.OBJECT_CODE ='PROJECT' AND P.OBJECT_ID = I.ID ORDER BY P.ID, P.NAME, D.DETAIL ;

For Oracle - Actual Benefit Plan Data

SELECT I.ID PROJECT_ID, I.NAME PROJECT_NAME, P.ID PLAN_ID , P.NAME PLAN_NAME, D.DETAIL DETAIL_NAME, ABFT.START_DATE, ABFT.SLICE, ROUND(ABFT.SLICE*(to_date(ABFT.FINISH_DATE) - to_date(ABFT.START_DATE)),2) CALC_SLICE FROM ODF_SSL_BFT_DTL_ABFT ABFT, FIN_BENEFIT_PLAN_DETAILS D, FIN_PLANS P, INV_INVESTMENTS I WHERE ABFT.PRJ_OBJECT_ID = D.ID AND D.PLAN_ID = P.ID AND P.OBJECT_ID = 5001379 AND P.OBJECT_CODE ='PROJECT' AND P.OBJECT_ID = I.ID ORDER BY P.ID, P.NAME, D.DETAIL ;

NJ

hi all,

thanks for quick reply.

I understand I need to query the SLICE table.

but as for any other situation where I need to query the slice, this is DEPENDENT on the slice window.

if the slice window is 1 year for example and I have a project spanning over 3 years, I cannot reuse the slice.

do you agree?

if not, how would you solve this ?

creating a procedure that stores in a custom table the values of the slice like it was for PMA_FINANCIAL_VALUES?

thanks

"

if the slice window is 1 year for example and I have a project spanning over 3 years, I cannot reuse the slice.

do you agree?

if not, how would you solve this ?

"

2 options (I guess) -

1. Tweak the OOB slice

2. Create a new slice

In either of the cases, I would recommend consulting with support

NJ

So I do not see slices for PFM_CLOB_CURVES? I meant Targets data seems not sliced? Am I missing something?

Did you take a look at the "PFM_PLANS" table ?

PFM_PLANS

This table contains rows that describe alternative planning criteria.

NJ

PFM_PORTFOLIOS

This table contain rows that describe portfolios.

NJ

In v13.2 the portfolio contents are not automatically synced with the items that were included in the portfolio.

That is the pfm_tables do not necessarily equal to the plans\budgets if they have evolved after the portfolio creation or the plan in the portfolio is a "what if" version.

If you want to go the cumbersome custom path consider custom timevarying attributes. The special feature in them is that even if you define time ranges and values for those you can still slice them per day, per, week, month and so on even if your time range is different should those not meet your later reporting needs.

hi,

I didnt read the entire thread sorry.

if you confirm me that those tables

ODF_SSL_CST contain cost plans\budget data and are

INDIPENDENT from the slicing job, I think we only need to query those tables and the problem is solved.

am I right?

thanks,

andrea

Looking at the PRJ_BLB_SLICEREQUEST table the ODF_SSL_ tables seem to be synchronous ie. sliced immediately.