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:
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!
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.
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_costfrom fin_financials fin,
fin_financials fin_second, inv_investments inv_parent,
inv_investments i_child, odf_object_instance_mapping obj_parent,
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_idand fin.id = obj_parent.secondary_object_instance_idand flat.parent_id = inv_parent.idand inv_parent.odf_object_code ='project'and flat.parent_id != flat.child_idand inv_parent.id = 5001080and i_child.id = obj_child.primary_object_instance_idand fin_second.id = obj_child.secondary_object_instance_idand obj_child.primary_object_instance_code = 'project'and obj_child.secondary_object_instance_code = 'financials'and flat.child_id = i_child.idgroup by inv_parent.code, inv_parent.name
Hope this helps.
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.
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)) tcFROM 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 bp2WHERE 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 =1and bp1.id = fp.start_period_id and bp2.id = fp.end_period_idand 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 = 5003000and 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
Are you making a report?Exactly what attributes do you want to show?In which field do you have problems?
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 .
I hope this query helps you.
FROM NIKU.pfm_portfolio_ranking_v rank
niku.pfm_investments inv ON inv.id=rank.instance_id
niku.pfm_portfolios cartera ON cartera.id = rank.context_id AND cartera.id = inv.portfolio_id
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(
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')
Actuals ON Actuals.Codigo_cartera = cartera.code AND Actuals.Codigo_Proyecto = inv.code
where rank.context_id= --ID_Portfolio
ORDER BY rank.rank
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.