Extract Task Queries

select inv.code ""Project ID"",
inv.name ""Project Name"",
(select name from CMN_LOOKUPS_v where LOOKUP_CODE = odfp.z_process_type and LANGUAGE_CODE ='en' and rownum=1) ""Project Type"",
decode(inv.is_active,1,'Yes','No') ""Active"",
(select name from CMN_LOOKUPS_v where LOOKUP_CODE = odfp.Z_HIERARCHY and LANGUAGE_CODE ='en' and rownum=1) ""HIERARCHY Level"",
odfp.OBJ_METHODOLOGY ""Project Template"",
(select nbi.path from prj_obs_associations ass join nbi_dim_obs nbi on nbi.OBS_UNIT_ID = ass.UNIT_ID where nbi.OBS_TYPE_NAME like '1 Organi%' and ass.TABLE_NAME ='SRM_PROJECTS' and ass.RECORD_ID = inv.id) ""Organization OBS"",
(select nbi.path from prj_obs_associations ass join nbi_dim_obs nbi on nbi.OBS_UNIT_ID = ass.UNIT_ID where nbi.OBS_TYPE_ID = 5000001 and ass.TABLE_NAME ='SRM_PROJECTS' and ass.RECORD_ID = inv.id) ""Business Owner OBS"",
(select nbi.path from prj_obs_associations ass join nbi_dim_obs nbi on nbi.OBS_UNIT_ID = ass.UNIT_ID where nbi.OBS_TYPE_NAME like '3 Department%' and ass.TABLE_NAME ='SRM_PROJECTS' and ass.RECORD_ID = inv.id) ""Department OBS"",
(select nbi.path from prj_obs_associations ass join nbi_dim_obs nbi on nbi.OBS_UNIT_ID = ass.UNIT_ID where nbi.OBS_TYPE_NAME like '4 Location%' and ass.TABLE_NAME ='SRM_PROJECTS' and ass.RECORD_ID = inv.id) ""Location"",
tas.prexternalid ""Task ID"",
tas.prname ""Task Name"",
tas.prwbslevel ""Outline Level"",
decode(tas.prismilestone,1,'Yes','No') ""Milestone"",
decode(tas.priskey,1,'Yes','No') ""Key Task"",
decode(odf.z_labor,1,'Yes',2,'No','NULL') ""Is Labor""
from INV_INVESTMENTS inv left outer join prtask tas on inv.id = tas.PRPROJECTID
join odf_ca_project odfp on inv.id = odfp.id
join ODF_CA_TASK odf on odf.id = tas.prid
join inv_projects invp on invp.prid = inv.id
where invp.IS_TEMPLATE = 0
order by inv.code