AnsweredAssumed Answered

Total cost from pfm_investments

Question asked by FrodeKommode on Oct 24, 2014
Latest reply on Aug 16, 2017 by CarlosBirlainSelfa82333808

Hi,

i have been reewriting some Querys that was used for portfolios in 13.1 to 13.3, but have dificoulties getting the Investment total cost (the cost inside the Portfolio range as in the provider).

In 13.1 this was easy due to the @Investment_sql@. I know this is because it's a blob Field so i wonder if someone know how to use this or if there is any equivalent.

 

Example Query:

select
       @SELECT:DIM:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_id:NOKKEL@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_id:investment_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.inv_id:inv_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_name:investment_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.alli:allignment@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.cost:cost@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.portfolio_ranking:portfolio_ranking@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.plan_ranking:plan_ranking@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.above_waterline:above_waterline@


FROM

(SELECT NVL(ppi.id,pi.id) investment_id,
       pi.investment_id inv_id,
       NVL(ppi.name,pi.name) investment_name,
       pi.alignment alli,
       pi.plan_ttl_cost cost,
       pr.rank portfolio_ranking,
       NVL(ppr.rank,pr.rank) plan_ranking,
       NVL(ppr.above_waterline,pr.above_waterline) above_waterline
FROM   pfm_portfolios port
       INNER JOIN pfm_portfolio_ranking_v pr ON port.id = pr.context_id
                                            AND pr.context_type = 'pfm'
       INNER JOIN pfm_investments pi ON pr.context_id = pi.portfolio_id
                                    AND pr.instance_id = pi.id
       INNER  JOIN pfm_user_selected_plan usp ON USP.PORTFOLIO_ID = port.id
          AND USP.USER_ID = @WHERE:PARAM:USER_ID@
       INNER  JOIN pfm_plans pplan ON port.id = pplan.portfolio_id
            AND PPlan.ID = USP.PLAN_ID
       LEFT OUTER JOIN pfm_plan_ranking_v ppr ON pplan.id = ppr.context_id
                                             AND ppr.context_type = 'pfmplan'
                                             AND pi.id = ppr.instance_id
       LEFT OUTER JOIN pfm_investments_plan ppi ON ppr.instance_id = ppi.id
                                               AND ppr.context_id = ppi.plan_id
WHERE port.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@

UNION ALL

SELECT pi.id investment_id,
       pi.investment_id inv_id,
       pi.name investment_name,
       pi.alignment alli,
       pi.plan_ttl_cost cost,
       pr.rank portfolio_ranking,
       pr.rank plan_ranking,
       pr.above_waterline above_waterline
FROM   pfm_portfolios port
       INNER JOIN pfm_portfolio_ranking_v pr ON port.id = pr.context_id
                                            AND pr.context_type = 'pfm'
       INNER JOIN pfm_investments pi ON pr.context_id = pi.portfolio_id
                                    AND pr.instance_id = pi.id
WHERE port.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@
and (select usp.plan_id from pfm_user_selected_plan usp where USP.PORTFOLIO_ID = port.id
          AND USP.USER_ID = @WHERE:PARAM:USER_ID@) is null) portplan

Where @filter@

Outcomes