Notes

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:v.ID:id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:v.name:name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:v.tot_sis_num:tot_sis_num@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:v.tot_rel_num:tot_rel_num@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:v.data_cal:data_cal@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:v.prstart:prstart@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:v.prfinish:prfinish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:v.hg_has_children:hg_has_children@

FROM (

--Level 1
select ROW_NUMBER() OVER (order by I.ID) as ID,I.NAME as name,tot_sis_num.tot_sis as tot_sis_num,null as tot_rel_num,null as data_cal,null as prstart,null as prfinish,I.ID hg_has_children
from INV_INVESTMENTS I
join ODF_CA_SERVICE SER on SER.id=I.ID

left join (

select ODFRELP.gpa_sistema,COUNT(RELP.NAME) as tot_sis
from RQP_RELEASE_PLANS RELP
join ODF_CA_RELEASEPLAN ODFRELP on ODFRELP.id=RELP.ID
group by gpa_sistema

) tot_sis_num on tot_sis_num.gpa_sistema=I.ID
where SER.gpa_tipo=1 and @where:param:user_def:string:hg_row_id@ is null
and (@where:param:user_id@ in (select value from odf_multi_valued_lookups
where object='service' and attribute='gpa_rm_it' and pk_id=ser.id)
or @where:param:user_id@=1
or @where:param:user_id@ in (select ug.USER_ID
from CMN_SEC_GROUPS g
join CMN_SEC_USER_GROUPS ug on ug.GROUP_ID=g.ID
where GROUP_CODE='gpa_cm_it') )

--Level 2
union

select ROW_NUMBER() OVER (order by RELP.ID) as ID,RELP.NAME as name,null as tot_sis_num,tot_rel_num.tot_rel as tot_rel_num,ODFRELP.gpa_data_cal as data_cal,null as prstart,null as prfinish,RELP.ID + '100000' hg_has_children
from RQP_RELEASE_PLANS RELP
join ODF_CA_RELEASEPLAN ODFRELP on ODFRELP.id=RELP.ID

left join (

select PREL.RELEASE_PLAN_ID,COUNT(REL.NAME) as tot_rel
from RQP_RELEASES REL
left join RQP_PLANS_RELEASES PREL on PREL.RELEASE_ID=REL.ID
where RELEASE_PLAN_ID is not null
group by PREL.RELEASE_PLAN_ID

) tot_rel_num on tot_rel_num.RELEASE_PLAN_ID=RELP.ID
where ODFRELP.gpa_sistema = @where:param:user_def:string:hg_row_id@

--Level 3
union

select ROW_NUMBER() OVER (order by REL.ID) as ID,REL.NAME as name,null as tot_sis_num,null as tot_rel_num,null as data_cal,PRT.PRSTART as prstart,PRT.PRFINISH as prfinish,null hg_has_children
from RQP_RELEASES REL
join PRTASK PRT on PRT.PREXTERNALID=REL.CODE
left join RQP_PLANS_RELEASES PREL on PREL.RELEASE_ID=REL.ID
where PREL.RELEASE_PLAN_ID + '100000' = @where:param:user_def:string:hg_row_id@

) v
where @FILTER@