CA PPM How can I read with a query the budgeted cost of the investment object of the portfolio?

[Legacy User]

Hello,
I am making a query to create a report about the investments of the portfolio.
I want to extract investment costs for each portfolio in the same way that I can see in the list of portfolio investments:

 

The problem is when I query the database:

 

SELECT

budget_ttl_cost Budget_Cost,

plan_ttl_cost  Planned_Cost

FROM 

pfm_investments

 

The result type is BLOB:

 

How can I extract the data from the Database so that it shows the same as in the list of investments in the portfolio?

Forgive my English, it is not my native language.

Thank you community!

[bassa10]

Hi Carlos,

 

The budgeted cost is stored in the following table -> fin_financials , for respective investment objects, in combination with columns Primary_Object_Instance_Code & Secondary_Object_Instance_Code

When you are looking for a rolled up budgeted cost (i.e. Parent budgeted cost + Child(s) budgeted cost), you will need to consider all the hierarchy, that is self record for the parent and then individual records for the children, with respect to budgeted cost.

Hence, you will need to consider the inv_flat_hierarchies tables. You will need to reference this table , along with Fin_Financials, twice, once for the parent and once for the child.

 

Example:

Considering the following program, the parent is having the budget (self) -> 990,000 and the respective children as shown below:

 

 

Hence, for the DB we will need to get the above respective calculation for the Self and the child projects. You can refer to the following query, which respectively traverses the tables from parent as well as from child and finally adds the data to give the consolidated budget. You can explore similar options 

 

select
inv_parent.code,
inv_parent.name,
(NVL(SUM(DISTINCT(fin.budget_cst_total)),0) + NVL(SUM(fin_second.budget_cst_total),0)) program_budgeted_cost
from fin_financials fin,

fin_financials fin_second,
inv_investments inv_parent,

inv_investments i_child,
odf_object_instance_mapping obj_parent,

odf_object_instance_mapping obj_child,

inv_flat_hierarchies flat

where obj_parent.primary_object_instance_code = 'project'
and obj_parent.secondary_object_instance_code = 'financials'
and inv_parent.id = obj_parent.primary_object_instance_id
and fin.id = obj_parent.secondary_object_instance_id
and flat.parent_id = inv_parent.id
and inv_parent.odf_object_code ='project'
and flat.parent_id != flat.child_id
and inv_parent.id = 5001080
and i_child.id = obj_child.primary_object_instance_id
and fin_second.id = obj_child.secondary_object_instance_id
and obj_child.primary_object_instance_code = 'project'
and obj_child.secondary_object_instance_code = 'financials'
and flat.child_id = i_child.id
group by inv_parent.code, inv_parent.name

 

Output:

 

 

Hope this helps.

 

Regards,

Samik

[Legacy User]

Hello Samik Basu!
Thank you very much for your answer.
I have to clarify that the object is not "investment" type, but "portfolio investment" type. Costs are different for each type of object. This object does not use the inv_investments table, the table it uses is pfm_investments. I have modified your query for the pfm_investments table, but it also does not return the correct value.

Regards

[Legacy User]

Hi Carlos,

                     Have you got any answer in this regard ? I am facing same problem, in order to query Investment plan tab for Portfolio, using PFM tables. After having done with some R&D, I found pfm_clob_curves table is holding all value in Clob format and date is in Julian format.

SELECT
clobs.*,
PFM_DATE_FROM_JULIAN_FCT (clobs.segment_start) start_date,
PFM_DATE_FROM_JULIAN_FCT (clobs.segment_finish) finish_date,
Rate * 24 * 3600 * (PFM_DATE_FROM_JULIAN_FCT(segment_finish) - PFM_DATE_FROM_JULIAN_FCT( clobs.segment_start)) tc
FROM pfm_clob_curves p, TABLE (pfm_segments_fct (p.id, p.clob_curve)) clobs,
pfm_investments piv,pfm_portfolios pim
,fin_plans fp
, biz_com_periods bp1
, biz_com_periods bp2
WHERE  p.id = clobs.id and pim.id = p.portfolio_id and  piv.portfolio_id = p.portfolio_id
and fp.object_id = piv.investment_id and fp.is_plan_of_record =1
and bp1.id = fp.start_period_id and bp2.id = fp.end_period_id
and PFM_DATE_FROM_JULIAN_FCT (clobs.segment_start) >= bp1.start_date  and PFM_DATE_FROM_JULIAN_FCT (segment_finish) <= bp2.end_date
AND p.table_name = 'PFM_INVESTMENTS'
AND p.attribute = 'PLAN_OP_COST'
AND p.portfolio_id = 5003000
and piv.investment_id = 5347002
order by clobs.segment_finish desc ;

 

However, I am getting some extra slice data..not sure why..please let me know,if any answer

[Legacy User]

Hi Krithigaa

 

Are you making a report?
Exactly what attributes do you want to show?
In which field do you have problems?

regards

[Legacy User]

Hi Carlos,

                                Yes I am trying to make a report, querying the Portfolio Investment tables. Basically, in the investment tab of the portfolio, if you can see those CLOB fields like Planned_Capital_cost, Planned_operating_Cost, Budgeted Capital or Operating Costs. We need to show those values  on monthly slice basis. Please let me know, if this information suffices .

[Legacy User]

Hi Krithigaa

I hope this query helps you.

regards.

 

SELECT

cartera.code,

inv.id Id_Inversion,

rank.rank Clasificacion,

inv.code Codigo_Inversion,

inv.name,

sums.ph_curve_sum Coste_Planificado,

Actuals.Coste_Real Coste_Real,

inv.schedule_start start_date,

inv.schedule_finish finish_date

 

FROM NIKU.pfm_portfolio_ranking_v rank

INNER JOIN

niku.pfm_investments inv ON inv.id=rank.instance_id

INNER JOIN

niku.pfm_portfolios cartera ON cartera.id = rank.context_id AND cartera.id = inv.portfolio_id

INNER JOIN

NIKU.PFM_INV_CONSTRAINT_SUMS sums ON sums.portfolio_id = cartera.id AND inv.id = sums.instance_id and sums.code = 'plan_cost'

 

LEFT OUTER JOIN(

                select

                cartera.code Codigo_cartera,

                wip.project_code Codigo_Proyecto,

                sum(val.amount) Coste_Real

                from niku.ppa_wip wip

                INNER JOIN niku.ppa_wip_values val ON val.transno = wip.transno AND val.currency_type='HOME'

                INNER JOIN niku.pfm_investments pfm_inv ON pfm_inv.code = wip.project_code

                INNER JOIN niku.pfm_portfolios cartera ON pfm_inv.portfolio_id = cartera.id

                AND to_date(to_char(wip.transdate,'dd/mm/yy'),'dd/mm/yy') between to_date(to_char(cartera.START_DATE,'dd/mm/yy'),'dd/mm/yy') AND to_date(to_char(cartera.FINISH_DATE,'dd/mm/yy'),'dd/mm/yy')

                GROUP BY

                cartera.code,

                wip.project_code

)

Actuals ON Actuals.Codigo_cartera = cartera.code AND Actuals.Codigo_Proyecto = inv.code

 

where rank.context_id= --ID_Portfolio

 

ORDER BY rank.rank

[Legacy User]

Thanks Carlos ..for the help. Even if we need the data based on monthly slice specific on planned cost, this will help us for future reference.