Clarity

Expand all | Collapse all

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

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

    Posted 08-14-2017 03:42 AM

    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!



  • 2.  Re: How can I read with a query the budgeted cost of the investment object of the portfolio?

    Posted 08-14-2017 06:26 AM

    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



  • 3.  Re: How can I read with a query the budgeted cost of the investment object of the portfolio?

    Posted 08-14-2017 06:57 AM

    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



  • 4.  Re: CA PPM How can I read with a query the budgeted cost of the investment object of the portfolio?

    Posted 02-21-2018 03:16 AM

    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



  • 5.  Re: CA PPM How can I read with a query the budgeted cost of the investment object of the portfolio?

    Posted 02-21-2018 03:32 AM

    Hi Krithigaa

     

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

    regards



  • 6.  Re: CA PPM How can I read with a query the budgeted cost of the investment object of the portfolio?

    Posted 02-21-2018 06:45 AM

    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 .



  • 7.  Re: CA PPM How can I read with a query the budgeted cost of the investment object of the portfolio?

    Posted 02-22-2018 03:30 AM

    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



  • 8.  Re: CA PPM How can I read with a query the budgeted cost of the investment object of the portfolio?

    Posted 03-13-2018 09:13 AM

    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.