Measurable Decomm: show aggregate decomms

[davidwatkins73]

We can currently see each decomm/replacement by clicking on individual ratings in an applications measurable rating tree. However there is no overall view either for an application or for a group of applications, or for a specific measurable.

[davidwatkins73]

Some sql that I've been playing around with that shows decomms/replacements which include a given org unit (postgres):

with
     ous as (
         select id
         from entity_hierarchy
         where ancestor_id = 20
           and kind = 'ORG_UNIT')
select distinct
       dapp.name,
       m.name,
       m.id,
       case
           when dapp.organisational_unit_id in (select id from ous) then 1
           else 0
       end decommer_in_selector,
       mrd.planned_decommission_date,
       rapp.name,
       case
           when rapp.organisational_unit_id in (select id from ous) then 1
           else 0
       end replacer_in_selector,
       mrr.planned_commission_date
from measurable_rating_replacement mrr
inner join measurable_rating_planned_decommission mrd
    on mrr.decommission_id = mrd.id
inner join measurable m
    on mrd.measurable_id = m.id
inner join application rapp
    on mrr.entity_id = rapp.id
inner join application dapp
    on mrd.entity_id = dapp.id
where
    dapp.organisational_unit_id in (select id from ous)
    or
    rapp.organisational_unit_id in (select id from ous);

• dapp is the decommissioning app
• rapp is a replacement app