Sure. Most of this won't make any sense since it's all custom attributes. I've also left in the "counts" at the end, to look at the volume of sub-object instances like Risks, and Issues, as well as custom sub-objects.
SELECT
(CASE WHEN IP.IS_PROGRAM = 1 AND INV.ODF_OBJECT_CODE = 'project'
THEN 'Program'
WHEN IP.IS_PROGRAM = 0 AND INV.ODF_OBJECT_CODE = 'project'
THEN 'Project'
ELSE ' '
END) "Investment Type",
inv.code "Project Code"
,inv.name "Project Name"
,(CASE WHEN TRIM(oci.met_short_desc) IS NOT NULL THEN 'Has Short Description' ELSE 'No Short Description' END) "Short Description"
,(CASE WHEN TRIM(INV.DESCRIPTION) IS NOT NULL THEN 'Has Description' ELSE 'No Description' END) "Description"
,(SELECT full_name FROM srm_resources WHERE user_id = inv.manager_id) "Project Manager"
,inv.schedule_start "Proj Start Date"
,inv.schedule_finish "Proj Finish Date"
,oci.met_fin_close_date "Financial Close Date"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_INIT_CLS' AND language_code = 'en' AND lookup_code = oci.MET_INIT_CLS) "Initiative Class"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_PRJ_TYPE' AND language_code = 'en' AND lookup_code = ocp.met_prj_type) "Project Type"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_GATE_LEVEL' AND language_code = 'en' AND lookup_code = ocp.met_gate_level) "Gate Review Level"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_PROJ_STATE' AND language_code = 'en' AND lookup_code = oci.met_prj_state) "Project State"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'INV_PROCESS_TYPE' AND language_code = 'en' AND lookup_code = inv.process_code)
||(SELECT '/'||name FROM cmn_lookups_v WHERE lookup_type = 'INV_STAGE_TYPE' AND language_code = 'en' AND lookup_code = inv.stage_code) "Project Phase"
-- ,inv.is_active "Is Active"
-- ,ip.is_template "Is Template"
,obs.level2_name "Project Ownership Dept LOB"
,obs.level3_name "Project Ownership Dept Sub-LOB"
,oci.met_incl_curr_plan_y "Incl Current Plan Yr"
,oci.met_par_num "PAR #"
,(CASE WHEN TRIM(dbms_lob.substr(oci.met_com_prj_stmnt)) IS NOT NULL THEN 'Has Statement' ELSE 'No Statement' END) "Commitment/Project Statement"
,(CASE WHEN EXISTS
(SELECT 1 FROM odf_multi_valued_lookups omv
,cmn_lookups_v con
WHERE omv.OBJECT = 'project'
AND omv.attribute = 'met_cap_fmwk'
AND omv.value = con.lookup_code
AND con.lookup_type = 'MET_CAPB3'
AND con.language_code = 'en'
AND omv.pk_id = inv.id)
THEN 'Has Cap Frmwk' ELSE 'No Cap Frmwk' END) "Capability Framework"
,(CASE WHEN EXISTS
(SELECT 1 FROM odf_multi_valued_lookups omv
,cmn_lookups_v con
WHERE omv.OBJECT = 'project'
AND omv.attribute = 'met_growth_init'
AND omv.value = con.lookup_code
AND con.lookup_type = 'MET_SPEC_INIT'
AND con.language_code = 'en'
AND omv.pk_id = inv.id)
THEN 'Has Growth Initiatives' ELSE 'No Growth Initiatives' END) "Growth Initiatives"
,(CASE WHEN ocp.met_ob_lob IS NOT NULL THEN 'Has Orange Book LoB' ELSE 'No Orange Book LoB' END) "Orange Book LOB"
,(CASE WHEN TRIM(ocp.met_prjsplink) IS NOT NULL THEN 'Has SP Link' ELSE 'No SP Link' END) "Proj SharePoint Site Link"
,(CASE WHEN oci.met_prj_priority IS NOT NULL THEN 'Has Proj Priority' ELSE 'No Proj Priority' END) "Proj Priority"
,(CASE WHEN ocp.met_tier IS NOT NULL THEN 'Has Tier' ELSE 'No Tier' END) "Tier"
,ocp.met_top_pgm "Top Program?"
,ocp.met_tp_elig "Transfer Pricing"
,oci.met_enf_team "Enforce Team"
,(SELECT LISTAGG(con.name, '; ') WITHIN GROUP (ORDER BY omv.seq)
FROM odf_multi_valued_lookups omv
,cmn_lookups_v con
WHERE omv.OBJECT = 'project'
AND omv.attribute = 'met_inv_ben_reg'
AND omv.value = con.lookup_code
AND con.lookup_type = 'MET_BEN_COUNTRY'
AND con.language_code = 'en'
AND omv.pk_id = inv.id) "Inv Ben Region/Country"
,(CASE WHEN ocp.met_phase_wave IS NOT NULL THEN 'Has Wave (Day)' ELSE 'No Wave (Day)' END) "Wave (Day)"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_IT_OP_IMP' AND language_code = 'en' AND lookup_code = oci.met_it_op_imp) "IT Operational Impact"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'OBJ_INVESTMENT_PFL_CATEGORY1' AND language_code = 'en' AND lookup_code = oci.obj_pfl_category1) "IT Portfolio Category"
,(CASE WHEN oci.met_pln_strt_dt IS NOT NULL THEN 'Has Planned Start Date' ELSE 'No Planned Start Date' END) "Planned Start Date"
,(CASE WHEN oci.met_pln_comp_dt IS NOT NULL THEN 'Has Planned Completion Date' ELSE 'No Planned Completion Date' END) "Planned Completion Date"
,(CASE WHEN oci.met_go_live_dt IS NOT NULL THEN 'Has Go Live Date' ELSE 'No Go Live Date' END) "Go Live Date"
,(CASE WHEN oci.met_exec_rev_dt IS NOT NULL THEN 'Has Executive Review Date' ELSE 'No Executive Review Date' END) "Executive Review Date"
,(CASE WHEN ocp.met_exec_spon IS NOT NULL THEN 'Has Funding Exec' ELSE 'No Funding Exec' END) "Funding Exec"
,(CASE WHEN oci.met_sr_biz_lead IS NOT NULL THEN 'Has Funding Lead' ELSE 'No Funding Lead' END) "Funding Lead"
,(CASE WHEN oci.met_wrking_client IS NOT NULL THEN 'Has Working Client' ELSE 'No Working Client' END) "Working Client"
,(CASE WHEN oci.met_del_exec IS NOT NULL THEN 'Has Delivery Executive' ELSE 'No Delivery Executive' END) "Delivery Executive"
,(CASE WHEN oci.met_sr_it_ld IS NOT NULL THEN 'Has Delivery Lead' ELSE 'No Delivery Lead' END) "Delivery Lead"
,(CASE WHEN EXISTS
(SELECT 1 FROM
odf_multi_valued_lookups omv
,srm_resources srm
WHERE omv.OBJECT = 'project'
AND omv.attribute = 'met_stcomm_mem'
AND omv.value = srm.id
AND omv.pk_id = inv.id)
THEN 'Has Steering Commitee' ELSE 'No Steering Commitee' END) "Steering Commitee"
,(CASE WHEN EXISTS
(SELECT 1 from prj_obs_units a inner join prj_obs_object_types oot on (a.type_id = oot.type_id) WHERE oot.table_name='SRM_PROJECTS' and a.id = oci.met_cro_dept)
THEN 'Has CRO Dept' ELSE 'No CRO Dept' END) "CRO Dept"
,(CASE WHEN TRIM(oci.met_reg_impl_due) IS NOT NULL THEN 'Has Regulatory Due Date/ID' ELSE 'No Regulatory Due Date/ID' END) "Regulatory Due Date/ID"
,(CASE WHEN TRIM(oci.met_reg_impl ) IS NOT NULL THEN 'Has Regulatory Implications' ELSE 'No Regulatory Implications' END) "Regulatory Implications"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_YESNO' AND language_code = 'en' AND lookup_enum = oci.met_irise_dev) "Includes UI/Screen Dev"
,(SELECT LISTAGG(con.name, '; ') WITHIN GROUP (ORDER BY omv.seq)
FROM odf_multi_valued_lookups omv
,cmn_lookups_v con
WHERE omv.OBJECT = 'project'
AND omv.attribute = 'met_irise_screentype'
AND omv.value = con.lookup_code
AND con.lookup_type = 'MET_IRISE_SCRTYP'
AND con.language_code = 'en'
AND omv.pk_id = inv.id) "Screen Types"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_YESNO' AND language_code = 'en' AND lookup_enum = oci.met_irise_use) "Did Proj Use iRise?"
,oci.met_irise_dcurl "Link to iRise DC"
,(CASE WHEN oci.met_dim_func IS NOT NULL THEN 'Has Functionality Ranking' ELSE 'No Functionality Ranking' END) "Functionality Ranking"
,(CASE WHEN oci.met_dim_qual IS NOT NULL THEN 'Has Quality Ranking' ELSE 'No Quality Ranking' END) "Quality Ranking"
,(CASE WHEN oci.met_dim_dd IS NOT NULL THEN 'Has Delivery Date Ranking' ELSE 'No Delivery Date Ranking' END) "Delivery Date Ranking"
,(CASE WHEN oci.met_dim_cst IS NOT NULL THEN 'Has Total Cost Ranking' ELSE 'No Total Cost Ranking' END) "Total Cost Ranking"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'INVESTMENT_OBJ_STATUS' AND language_code = 'en' AND lookup_enum = inv.status) "Project Status"
,(SELECT NAME from prj_obs_units a inner join prj_obs_object_types oot on (a.type_id = oot.type_id) WHERE oot.table_name='SRM_PROJECTS' and a.id = oci.met_cust_hierarchy) "Customer Alignment Dept"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'EIA_PROJECT_CATEGORY' AND language_code = 'en' AND lookup_code = oci.met_eia_prj_cat) "EIA Project Class"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_PRJ_TYP_GRP_LU' AND language_code = 'en' AND lookup_code = ocp.met_prj_typ_grp) "Project Type Grouping"
,(CASE WHEN EXISTS (SELECT 1 FROM ODF_CA_MET_ITKEYINV WHERE code = oci.met_assoc_it_inv)
THEN 'Has Key Inv' ELSE 'No Key Inv' END) "Associated Key Investment"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_SPEND_TYPE' AND language_code = 'en' AND lookup_code = ocp.met_spnd_typ) "Spend Type"
,ocp.met_inv_cl_new "Investment Class"
,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_YESNO' AND language_code = 'en' AND lookup_enum = oci.met_ce_enabled1) "MADP"
,inv.last_updated_date "Last Updated Date"
,(SELECT full_name FROM srm_resources WHERE user_id = inv.last_updated_by) "Last Updated By"
,(SELECT count(id) from odf_ca_met_proj_app_align where odf_parent_id = inv.id) "Applications Aligned"
,(SELECT COUNT(r.id) FROM RIM_RISKS_AND_ISSUES RIM, ODF_CA_RISK R WHERE RIM.ID = R.ID AND RIM.PK_ID = INV.ID) "Risks"
,(SELECT COUNT(i.id) FROM RIM_RISKS_AND_ISSUES RIM, ODF_CA_ISSUE I WHERE RIM.ID = I.ID AND RIM.PK_ID = INV.ID) "Issues"
,(SELECT COUNT(c.id) FROM RIM_RISKS_AND_ISSUES RIM, ODF_CA_CHANGE C WHERE RIM.ID = C.ID AND RIM.PK_ID = INV.ID) "Change Requests"
,(SELECT count(id) from odf_ca_met_apex_projrep where odf_parent_id = inv.id) "Status Reports"
,(SELECT count(id) from odf_ca_met_prj_prtn where odf_parent_id = inv.id) "Project Partners"
,(SELECT count(id) from odf_ca_met_trans_pric where odf_parent_id = inv.id) "Transfer Pricings"
,(SELECT count(prid) from prtask where prprojectid = inv.id) "Tasks"
FROM inv_investments inv
,odf_ca_inv oci
,odf_ca_project ocp
,inv_projects ip
,(SELECT poa.record_id, ndo.level2_name, ndo.level3_name
FROM prj_obs_associations poa
,nbi_dim_obs ndo
WHERE poa.unit_id = ndo.obs_unit_id
AND poa.table_name = 'SRM_PROJECTS'
AND ndo.obs_type_id = 5001009) obs
WHERE inv.id = oci.id
AND inv.id = ocp.id
AND inv.id = ip.prid
-- AND ip.is_program <> 1
AND ip.is_template = 0
AND inv.odf_object_code = 'project'
AND inv.is_active = 1
AND inv.id = obs.record_id
-- and inv.code = 'PM00049062'