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

Get Project Data using Clarity

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')

Group Extract Query In Clarity

"SELECT srm.last_name ""Last Name"",
srm.first_name ""First Name"",
srm.unique_name ""Resource ID"",
srm.email ""Email Address"",
cmn.User_name ""UserName"",
CSG.Group_name,
Date "Extract Date"
FROM srm_resources srm
JOIN prj_resources prj
ON srm.id=prj.prid
JOIN odf_ca_resource odr
ON srm.id=odr.id
JOIN cmn_sec_users cmn
ON cmn.id=srm.user_id
JOIN CMN_SEC_USER_GROUPS CMG
ON cmn.id=CMG.user_id
Join CMN_SEC_GROUPS_V CSG
ON (CMG.GROUP_ID=CSG.id)
JOIN prj_obs_associations aso
ON aso.record_id = srm.id
JOIN obs_units_flat_by_mode mde
ON mde.linked_unit_id = aso.unit_id
WHERE mde.unit_mode ='OBS_UNIT_AND_CHILDREN'
AND mde.unit_id =5054143
AND aso.table_name ='SRM_RESOURCES'
and srm.is_active='1'
and CSG.LANGUAGE_CODE = 'en'"

Get Active Resources From Clarity

SELECT srm.last_name "Last Name",
srm.first_name "First Name",
srm.unique_name "Resource ID",
srm.email "Email Address",
(SELECT srm.last_name ||srm.first_name FROM srm_resources srm WHERE srm.id=
      prj.prprimaryroleid) "Primary Role",
DECODE(srm.person_type,300,'EMPLOYEE',301,'CONTRACTOR',5000580,'EXEMPT',
     5000581,'NON-EXEMPT',5000620,'TEMPORARY') "Employment Type",
SRM.DATE_OF_HIRE "Date of Hire",
srm.date_of_termination "Date of Termination",
PR.HOURS_PER_DAY  "Availability",
odr.contract_time_week "Contract Hours",
DECODE(prj.prtrackmode,0,'None',1,'Other',2,'Clarity') "Track Mode",
DECODE(prj.prisopen,0,'false','true') "Open for Time-entry",
(SELECT cmn.last_name ||', '||cmn.first_name FROM cmn_sec_users cmn WHERE
      cmn.id=srm.manager_id) "Manager Name",
(SELECT user_name FROM cmn_sec_users cmn WHERE cmn.id=srm.manager_id)
      "Manager Id",
DECODE(srm.is_active,1,'true','false') "Status",
DECODE(srm.is_external,1,'true','false') "External",
cmn.Timezone "Timezone",
(SELECT DECODE(prt.prname,'Normal Working Hours','Standard','Overtime') FROM prtypecode prt WHERE prt.prid=prj.prtypecodeid)
      "Input Type Code",
cmn.user_name "User Name",

odr.salary_number "Employee Number",
DECODE(odr.employee_charge_type,'ect_direct','Direct','ect_indirect','Indirect')  "Charge Type",
NBI.PATH HIERARCHICAL_OBS,
sysdate "Extract Date"

FROM srm_resources srm
JOIN prj_resources prj
ON srm.id=prj.prid
JOIN odf_ca_resource odr
ON srm.id=odr.id
JOIN cmn_sec_users cmn
ON cmn.id=srm.user_id
Join PRCALENDAR PR 
on prj.PRCALENDARID=PR.PRID
JOIN prj_obs_associations aso
ON aso.record_id = srm.id
Join prj_obs_units_flat pouf
ON aso.unit_id = pouf.unit_id
JOIN obs_units_flat_by_mode mde
ON mde.linked_unit_id = aso.unit_id
JOIN nbi_dim_obs nbi
ON aso.unit_id=nbi.obs_unit_id
WHERE mde.unit_mode ='OBS_UNIT_AND_CHILDREN'
AND mde.unit_id =5054143
AND aso.table_name ='SRM_RESOURCES' and NBI.OBS_TYPE_ID=5000009
and 
srm.is_active='1'

Query To Extract Team In Clarity

select 
inv.code "Investment ID",
inv.name "Name",
srm2.unique_name "Team Resource ID",
tem.team_uid "Team ID",
tem.prAvailStart "Avail Start",
tem.prAvailFinish "Avail Finish",  
decode(tem.prbooking,15,'Hard',10,'Mixed',5,'Soft') "Booking Status",
'0.00%' "Default Allocation",
decode(tem.prisopen,1,'false',0,'true') "Open for Time-entry",
(select srm.last_name from srm_resources srm where srm.id=tem.prroleid) "Project Role ID",
decode(tem.prresourceid,(select srm1.id from srm_resources srm1 where srm1.user_id=inv.manager_id),'Yes','No') "Is Project Manager"  ,
Sysdate "Extract 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 prj_obs_associations aso
on aso.record_id=inv.id
and aso.table_name='SRM_PROJECTS'
join prteam tem
on tem.prprojectid=inv.id
join srm_resources srm2
on SRM2.ID=tem.prresourceid
where 
Inv.code in
(

'Project1')

Happy Coding By Bala