Please find the full query.
SELECT
--x.portfolio_id,x.portfolio_name,
(select distinct pma.name
from
PFM_INVESTMENTS pmac
inner join PFM_PORTFOLIOS pma on pmac.portfolio_id = pma.id
inner join inv_investments inv on pmac.investment_id=inv.id
and inv.ODF_OBJECT_CODE='project' and inv.id=x.investment_id) a,
--(select distinct id from PFM_INVESTMENTS a where is_active=1 and a.investment_id=x.investment_id and rownum <2) a,
x.investment_id,
x.GIL_ID,
x.investment_name,
x.Project_portfolio,
nvl((select distinct res.first_name||' '||res.last_name from srm_resources res, cmn_sec_users usr where res.USER_ID = usr.id and res.id = X.It_lead),'N/A') IT_Lead,
x.investment_manager,
x.start_date,
x.finish_date,
x.stage_code,
decode(x.c_overall_status,'30','Red','20','Yellow','10','Green','White') Stage,
x.gil_corr_action as gil_corr_action,
x.gil_lead_action as gil_lead_action,
x.cop_upcoming_act as cop_upcoming_act
FROM (SELECT i.id investment_id,
pp.name as Project_portfolio
,ic.gil_target_in_svc_go as gil_in_serv
,c.name as project_type
,cat.name as project_category,
ic.gil_unique_id GIL_ID,
i.name investment_name,
m.full_name investment_manager,
i.schedule_start start_date,
ic.gil_prj_finish_date finish_date,
sr.cop_report_date report_date,
s.name stage_code,
NVL(sr.cop_overall_status,0) as c_overall_status,
NVL(sr.cop_schedule_status,0) c_schedule_status,
NVL(sr.cop_scope_status,0) c_scope_status,
NVL(sr.cop_cost_eft_status,0) c_cost_effort_status,
nvl(sr2.cop_overall_status,0) as p_overall_status,
NVL(sr2.cop_schedule_status,0) p_schedule_status,
NVL(sr2.cop_scope_status,0) p_scope_status,
NVL(sr2.cop_cost_eft_status,0) p_cost_effort_status,
sr.gil_var_detail,
sr.cop_key_accomplish,
sr.cop_upcoming_act,
sr.cop_report_update ,
sr.gil_corr_action,
sr.gil_lead_action,
ic.gil_multi_phase_rel multi_phase,
ic.gil_it_lead IT_LEAD
--,ppma.port_id portfolio_id,ppma.port_name portfolio_name
FROM inv_investments i
INNER JOIN odf_ca_inv ic ON i.id = ic.id
left join cmn_lookups_v pp on (pp.lookup_type = 'GIL_PRJ_PORT' and pp.language_code= 'en' and pp.lookup_code = ic.gil_prj_port)
left outer join inv_projects p ON i.id = p.prID
left outer JOIN odf_ca_project pc ON i.id = pc.id
left join cmn_lookups_v c on (c.lookup_type = 'OBJ_IDEA_PROJECT_TYPE' and c.language_code= 'en' and c.lookup_code = pc.obj_request_type)
left join cmn_lookups_v cat on (cat.lookup_type = 'OBJ_IDEA_PROJECT_CATEGORY' and cat.language_code= 'en' and cat.lookup_code = pc.obj_request_category)
left join cmn_lookups_v prg on (prg.lookup_type = 'INVESTMENT_OBJ_PROGRESS' and prg.language_code= 'en' and prg.lookup_enum = i.progress)
LEFT OUTER JOIN cop_prj_statusrpt_latest_v r ON i.id = r.investment_id
AND r.report_order = 1
LEFT OUTER JOIN odf_cop_prj_statusrpt_v2 sr ON r.report_id = sr.odf_pk
LEFT OUTER JOIN srm_resources m ON i.manager_id = m.user_id
LEFT OUTER JOIN cop_prj_statusrpt_latest_v r2 ON i.id = r2.investment_id AND
r2.report_order = 2
LEFT OUTER JOIN odf_cop_prj_statusrpt_v2 sr2 ON r2.report_id = sr2.odf_pk
LEFT OUTER JOIN cmn_lookups_v s ON i.stage_code = s.lookup_code
AND s.lookup_type = 'INV_STAGE_TYPE'
AND s.language_code = 'en'
-- LEFT OUTER JOIN PFM_INVESTMENTS ppma on i.id=ppma.investment_id
/*
INNER JOIN (select distinct pma.id port_id,pma.name port_name, inv.id inv_id
from
PFM_INVESTMENTS pmac
inner join PFM_PORTFOLIOS pma on pmac.portfolio_id = pma.id
inner join inv_investments inv on pmac.investment_id=inv.id) ppma on i.id=ppma.inv_id
*/
WHERE (NVL(p.is_template,0) = 0)
AND (NVL(p.is_program,0) = 0)
and i.id in (select pmac.investment_id
from
PFM_INVESTMENTS pmac
inner join PFM_PORTFOLIOS pma on pmac.portfolio_id = pma.id
inner join inv_investments inv on pmac.investment_id=inv.id
-- and inv.ODF_OBJECT_CODE='project'
and pma.id = '5034011'
)
group by
i.id,
pp.name
,ic.gil_target_in_svc_go
,c.name
,cat.name ,
ic.gil_unique_id,
i.name ,
m.full_name ,
i.schedule_start ,
ic.gil_prj_finish_date ,
sr.cop_report_date ,
s.name ,
NVL(sr.cop_overall_status,0),
NVL(sr.cop_schedule_status,0) ,
NVL(sr.cop_scope_status,0) ,
NVL(sr.cop_cost_eft_status,0) ,
nvl(sr2.cop_overall_status,0) ,
NVL(sr2.cop_schedule_status,0) ,
NVL(sr2.cop_scope_status,0) ,
NVL(sr2.cop_cost_eft_status,0) ,
sr.gil_var_detail,
sr.cop_key_accomplish,
sr.cop_upcoming_act,
sr.cop_report_update,
sr.gil_corr_action,
sr.gil_lead_action,
ic.gil_multi_phase_rel,
ic.gil_it_lead
--,ppma.port_id,ppma.port_name
--,ppma.portfolio_id
) x
Where 1=1
--and x.gil_id='GIL1952'
ORDER BY
x.investment_name ASC