When working with large implementations, the below sql/s come in handy to view information quickly
Query to get Org Hierarchy Information :-
select
(select name from HR_ALL_ORGANIZATION_UNITS where ORGANIZATION_ID = pahd.PARENT_ORGANIZATION_ID) Parent,
(select name from HR_ALL_ORGANIZATION_UNITS where ORGANIZATION_ID = pahd.CHILD_ORGANIZATION_ID) Child,
(select name from HR_ALL_ORGANIZATION_UNITS where ORGANIZATION_ID = pahd.ORG_ID) OU
, pd.*
from apps.pa_org_hierarchy_denorm pahd
where pa_org_use_type in ( ‘REPORTING’)
order by last_update_date desc;
Query to find all Project Owning Organizations, number of Projects, number of tasks, etc :-
select NAME, TYPE,
(select NAME from hr_all_organization_units where organization_id in (select PARENT_ORGANIZATION_ID from PA_ORG_HIERARCHY_DENORM
where CHILD_ORGANIZATION_ID = haou.ORGANIZATION_ID and PA_ORG_USE_TYPE = ‘REPORTING’ and CHILD_ORGANIZATION_ID <> PARENT_ORGANIZATION_ID and PARENT_LEVEL = CHILD_LEVEL+1 )) ParentOrg,
(select NAME from hr_all_organization_units where organization_id in (select ORG_ID from PA_ORG_HIERARCHY_DENORM
where CHILD_ORGANIZATION_ID = haou.ORGANIZATION_ID and PA_ORG_USE_TYPE = ‘REPORTING’ and CHILD_ORGANIZATION_ID <> PARENT_ORGANIZATION_ID and PARENT_LEVEL = CHILD_LEVEL+1 )) OU,
(select count(1) from pa_projects_all where CARRYING_OUT_ORGANIZATION_ID = haou.ORGANIZATION_ID) NumProjects,
(select count(1) from pa_tasks where CARRYING_OUT_ORGANIZATION_ID = haou.ORGANIZATION_ID) NumTasks
,haou.*
from hr_all_organization_units haou
where ORGANIZATION_ID in (select distinct ORGANIZATION_ID from apps.HR_ORGANIZATION_INFORMATION_V where ORG_INFORMATION1_MEANING = ‘Project Task Owning Organization’ and ORG_INFORMATION2_MEANING = ‘Yes’)
and TYPE not in (‘BU’,’LC’)
and (select count(1) from pa_tasks where CARRYING_OUT_ORGANIZATION_ID = haou.ORGANIZATION_ID) > 1;
PS: Subquery is just my personal choice & comfort area. You can denormalize as you please.