select
inv.code "Investment ID",
inv.name "Name",
inv.description "Description",
(
select cmn.last_name||', '||cmn.first_name || SRM.Unique_name from
cmn_sec_users cmn
Join srm_resources SRM
ON cmn.id=srm.user_id
where cmn.id=inv.manager_id
)
"Manager Name",
(
select SRM.Unique_name from
cmn_sec_users cmn
Join srm_resources SRM
ON cmn.id=srm.user_id
where cmn.id=inv.manager_id
)
"Manager ID",
inv.schedule_start "Start",
inv.schedule_finish "Finish",
odp.z_projectlink "Project Link",
decode(inv.is_open_for_te,1,'true','false') "Open for Time-Entry",
CASE odp.schedule_type
when 'st_projectmaster'
then 'Project'
when 'st_projectschedule'
then 'Project'
when 'ss_sub-schedule'
then 'Workpackage'
else 'Program'
END "Schedule Type",
CASE odp.z_project_type
When 'z_sytem'
then 'New Product Development and Launch (NPDL)'
when 'z_subsystem'
then 'New Product Development and Launch (NPDL)'
when 'z_test'
then 'Others'
when 'z_component'
then 'New Product Development and Launch (NPDL)'
when 'z_lcm'
then 'Product Lifecycle Maintenance (PRDM)'
when 'z_advanced_development'
then 'Advanced Development'
when 'z_npra'
then 'Non Project Related Activity (NPRA)'
when 'z_maintenance'
then 'Product Lifecycle Maintenance (PRDM)'
when 'z_featureset'
then 'Others'
when 'z_competenceteam'
then 'Others'
when 'z_feasibility_study'
then 'Exploration(EXPL)'
else 'Not Applicable'
END "Project Type",
decode(inv.progress,0,'Not Started',1,'Started',2,'Completed') "Progress",
decode(inv.status_indicator,1,'Green',2,'Yellow',3,'Red') "Status Indicator",
decode(inv.track_assgn_only,0,'false',1,'true') "Prevent Unassigned Timesheet",
CASE odp.cto_category
when 'cc_not_applicable'
then 'Not Applicable'
when 'cc_to_be_defined'
then 'Not Applicable'
when 'cc_breakthrough'
then 'Not Applicable'
when 'cc_growth'
then 'Not Applicable'
when 'cc_new_platform'
then 'Not Applicable'
when 'cc_evolutionary'
then 'Not Applicable'
when 'cc_life_cycle_management'
then 'Sustaining Innovation'
when 'cc_supporting_projects'
then 'Not Applicable'
when 'cc_sustaining_innovation'
then 'Sustaining Innovation'
when 'cc_game_changer'
then 'Game Changer'
when 'cc_adjacency_program'
then 'Adjacency Program'
when 'cc_eba'
then 'Emerging Business Area'
else 'Not Applicable'
END "CTO Category",
decode(inv.status,0,'Unapproved',1,'Approved',2,'On Hold',3,'Rejected',4,'Cancelled',5,'Resumed',6,'Submitted for Approval',7,'Incomplete',8,'Converted') "Status",
decode(pac.status,'O','Open','H','Hold','C','Closed') "Financial Status",
decode(odp.z_is_cap,0,'FALSE',1,'TRUE') "Capitalisable",
decode(inv.track_mode,0,'None',1,'Other',2,'Clarity') "Track Mode",
decode (inv.IS_ACTIVE,0,'In-Active',1,'Active') "Project Active",
nbi.path "GEO OBS",
Date "Extracted Date"
from inv_investments inv
join odf_ca_project odp
on odp.id=inv.id
join pac_mnt_projects pac
on inv.id=pac.id
Join srm_projects srm
on inv.id=srm.id
JOIN prj_obs_associations poa ON srm.ID = poa.record_id
JOIN prj_obs_units_flat pouf ON poa.unit_id = pouf.unit_id
Join nbi_dim_obs nbi ON poa.unit_id=nbi.obs_unit_id
where
poa.table_name = 'SRM_PROJECTS' AND NBI.OBS_TYPE_ID=5000009 and inv.code in
(
'2D Flow CVB31400')
