Clarity

Hi,

Anybody has an experience with creating an NSQL query portlet that updates its values based on the Portfolio Plan (scenario) that is selected in the yellow Scenario bar on the top of the page ?

(An example of a portlet behaving in this way would be for example the portlets on Portfolio object, in tabs Waterline, Capacity, Financials. These are all system portlets, therefore I cannot see the code behind.

Thanks for any insight !

Sylvia - we have delivered to clients portlets that allow the user to select the portfolio plan (scenario) to be used.  They seemed to like having the flexibility of selection for the data displays. 

Think Sylvia is looking for the plan selection to be passed through, not a lookup filter on the portlet.

About a year and half ago I built something that picked up the selected plan but sadly I can't remember what the syntax was.

If I find it out I'll post it.

Hi,

You should get the information from the plan views and use case or nvl functions to either display the portfolio values or the selected plan values.

Se the query below as an example.

select   p.code||'_'||pp.code pa_code

,nvl(pp.name, op.name) name

,nvl(pp.code, op.code) code

,pp.is_approved

,nvl(pp.description,op.description) description

,to_char(nvl(pp.finish_date,op.finish_date),'yyyy-mm-dd')||'T00:00:00' finish_date     

,pp.plan_port_id           

,to_char(nvl(pp.start_date,op.start_date),'yyyy-mm-dd')||'T00:00:00' start_date    

,nvl(pp.benefits_target,op.benefits_target) benefits_target    

,nvl(pp.capital_cost_target,op.capital_cost_target) capital_cost_target    

,nvl(pp.operating_cost_tgt,op.operating_cost_tgt) operating_cost_tgt   

,nvl(pp.resources_target,op.resources_target) resources_target     

,nvl(pp.total_cost_target,op.total_cost_target) total_cost_target  

,nvl(ttl_cost_plan.clob_curve, ttl_cost.clob_curve) ttl_cost       

,nvl(cap_cost_plan.clob_curve, cap_cost.clob_curve) cap_cost   

,nvl(ope_cost_plan.clob_curve, ope_cost.clob_curve) ope_cost   

,nvl(bft_cost_plan.clob_curve, bft_cost.clob_curve) bft_cost   

,nvl(res_plan.clob_curve, res.clob_curve) res  

from ODF_PFM_PLAN_V2 pp    

join pfm_portfolios p on p.id = pp.portfolio_id    

join ODF_PFM_PORTFOLIO_V2 op on op.odf_pk = p.id   

left join PFM_CLOB_CURVES ttl_cost_plan on ttl_cost_plan.portfolio_id = pp.portfolio_id

    and ttl_cost_plan.plan_id = pp.odf_pk and ttl_cost_plan.table_name = 'PFM_PORTFOLIOS'

    and ttl_cost_plan.attribute = 'TOTAL_COST'    

left join PFM_CLOB_CURVES cap_cost_plan on cap_cost_plan.portfolio_id = pp.portfolio_id

    and cap_cost_plan.plan_id = pp.odf_pk and cap_cost_plan.table_name = 'PFM_PORTFOLIOS'

    and cap_cost_plan.attribute = 'CAPITAL_COST'      

left join PFM_CLOB_CURVES ope_cost_plan on ope_cost_plan.portfolio_id = pp.portfolio_id

    and ope_cost_plan.plan_id = pp.odf_pk and ope_cost_plan.table_name = 'PFM_PORTFOLIOS'

    and ope_cost_plan.attribute = 'OPERATING_COST'

left join PFM_CLOB_CURVES bft_cost_plan on bft_cost_plan.portfolio_id = pp.portfolio_id

    and bft_cost_plan.plan_id = pp.odf_pk and bft_cost_plan.table_name = 'PFM_PORTFOLIOS'

    and bft_cost_plan.attribute = 'BENEFITS'      

left join PFM_CLOB_CURVES res_plan on res_plan.portfolio_id = pp.portfolio_id

    and res_plan.plan_id = pp.odf_pk and res_plan.table_name = 'PFM_PORTFOLIOS'

    and res_plan.attribute = 'ROLE_CAPACITY'      

left join PFM_CLOB_CURVES ttl_cost on ttl_cost.portfolio_id = pp.portfolio_id

    and ttl_cost.plan_id = 0

    and ttl_cost.table_name = 'PFM_PORTFOLIOS'

    and ttl_cost.attribute = 'TOTAL_COST'     

left join PFM_CLOB_CURVES cap_cost on cap_cost.portfolio_id = pp.portfolio_id

    and cap_cost.plan_id = 0

    and cap_cost.table_name = 'PFM_PORTFOLIOS'

    and cap_cost.attribute = 'CAPITAL_COST'   

left join PFM_CLOB_CURVES ope_cost on ope_cost.portfolio_id = pp.portfolio_id

    and ope_cost.plan_id = 0

    and ope_cost.table_name = 'PFM_PORTFOLIOS'

    and ope_cost.attribute = 'OPERATING_COST'     

left join PFM_CLOB_CURVES bft_cost on bft_cost.portfolio_id = pp.portfolio_id

    and bft_cost.plan_id = 0

    and bft_cost.table_name = 'PFM_PORTFOLIOS'

    and bft_cost.attribute = 'BENEFITS'   

left join PFM_CLOB_CURVES res on res.portfolio_id = pp.portfolio_id

    and res.plan_id = 0

    and res.table_name = 'PFM_PORTFOLIOS'

    and res.attribute = 'ROLE_CAPACITY'