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@