Clarity

 View Only
Expand all | Collapse all

Query investments above the waterline in a portfolio

  • 1.  Query investments above the waterline in a portfolio

    Posted Feb 26, 2015 12:35 PM

    Hi all,

     

    We need to create a portlet that shows information of all investments that are above the Waterline in a portfolio.

     

    Someone already did something like that or have any tips ?

     

    Thanks!



  • 2.  Re: Query investments above the waterline in a portfolio
    Best Answer

    Posted Feb 26, 2015 01:44 PM

    Hi there,

     

    you could use the query bellow to develop your own:

     

    SELECT rank_v.rank ranking, rank_v.above_waterline linha_corte, odf_ptf.br_nivel_priorizacao nivel_prior, pfm_inv.investment_id prj_id, inv.name prj_name, inv.code prj_code,
    to_char(odf_ptf.br_data_priorizacao,'YYYY-MM-DD"T00:00:00"') dt_priorizacao
    FROM PFM_INVESTMENTS pfm_inv
    LEFT JOIN PFM_PORTFOLIO_RANKING_V rank_v ON rank_v.instance_id = pfm_inv.id AND rank_v.context_type = 'pfm'
    LEFT JOIN inv_investments inv ON inv.id = pfm_inv.investment_id
    LEFT JOIN odf_ca_pfm_portfolio odf_ptf ON odf_ptf.id = pfm_inv.portfolio_id

    WHERE pfm_inv.portfolio_id = ${objId}

    ORDER BY rank_v.rank

     

    If the investment is above the waterline, the rank_v.above_waterline is equals to 1. So, you just need to check it.



  • 3.  Re: Query investments above the waterline in a portfolio

    Posted Feb 26, 2015 02:21 PM

    Thank you! It's worked.