SELECT ORGDNORM2.PARENT_ORGANIZATION_ID AS ORGANIZATION_ID , LKUP.MEANING AS HEADCOUNT_LEVEL , LKUP.LOOKUP_CODE AS HEADCOUNT_CODE , COUNT(DISTINCT RESDNORM.PERSON_ID) AS EMP_HEADCOUNT FROM PA_RESOURCES_DENORM RESDNORM , PA_ORG_HIERARCHY_DENORM ORGDNORM1 , PA_ORG_HIERARCHY_DENORM ORGDNORM2 , PA_LOOKUPS LKUP WHERE LKUP.LOOKUP_TYPE = 'PA_OASCREEN_CHARS' AND LKUP.LOOKUP_CODE = 'TOTALS' AND NVL(ORGDNORM1.ORG_ID , -99) = PA_REP_UTIL_GLOB.GETORGID AND ORGDNORM1.PA_ORG_USE_TYPE = 'REPORTING' AND ORGDNORM1.ORG_HIERARCHY_VERSION_ID = PA_REP_UTIL_GLOB.GETORGSTRUCTUREVERSIONID AND ORGDNORM1.PARENT_ORGANIZATION_ID = PA_REP_UTIL_GLOB.GETU1ORGANIZATIONID AND ORGDNORM1.PARENT_LEVEL - ORGDNORM1.CHILD_LEVEL <= 1 AND ORGDNORM1.CHILD_ORGANIZATION_ID = ORGDNORM2.PARENT_ORGANIZATION_ID AND NVL(ORGDNORM2.ORG_ID , -99) = NVL(ORGDNORM1.ORG_ID , -99) AND ORGDNORM2.PA_ORG_USE_TYPE = 'REPORTING' AND ORGDNORM2.ORG_HIERARCHY_VERSION_ID = PA_REP_UTIL_GLOB.GETORGSTRUCTUREVERSIONID AND RESDNORM.RESOURCE_ORGANIZATION_ID = ORGDNORM2.CHILD_ORGANIZATION_ID AND PA_RESOURCE_UTILS.GET_PERIOD_DATE BETWEEN RESDNORM.RESOURCE_EFFECTIVE_START_DATE AND RESDNORM.RESOURCE_EFFECTIVE_END_DATE AND RESDNORM.UTILIZATION_FLAG = 'Y' GROUP BY ORGDNORM2.PARENT_ORGANIZATION_ID , LKUP.MEANING , LKUP.LOOKUP_CODE UNION ALL SELECT ORGDNORM1.PARENT_ORGANIZATION_ID AS ORGANIZATION_ID , LKUP.MEANING AS HEADCOUNT_LEVEL , LKUP.LOOKUP_CODE AS HEADCOUNT_CODE , COUNT(DISTINCT RESDNORM.PERSON_ID) AS EMP_HEADCOUNT FROM PA_RESOURCES_DENORM RESDNORM , PA_ORG_HIERARCHY_DENORM ORGDNORM1 , PA_LOOKUPS LKUP WHERE LKUP.LOOKUP_TYPE = 'PA_OASCREEN_CHARS' AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS' AND NVL(ORGDNORM1.ORG_ID , -99) = PA_REP_UTIL_GLOB.GETORGID AND ORGDNORM1.PA_ORG_USE_TYPE = 'REPORTING' AND ORGDNORM1.ORG_HIERARCHY_VERSION_ID = PA_REP_UTIL_GLOB.GETORGSTRUCTUREVERSIONID AND ORGDNORM1.PARENT_ORGANIZATION_ID = PA_REP_UTIL_GLOB.GETU1ORGANIZATIONID AND ORGDNORM1.PARENT_LEVEL - ORGDNORM1.CHILD_LEVEL >= 1 AND RESDNORM.RESOURCE_ORGANIZATION_ID = ORGDNORM1.CHILD_ORGANIZATION_ID AND PA_RESOURCE_UTILS.GET_PERIOD_DATE BETWEEN RESDNORM.RESOURCE_EFFECTIVE_START_DATE AND RESDNORM.RESOURCE_EFFECTIVE_END_DATE AND RESDNORM.UTILIZATION_FLAG = 'Y' GROUP BY ORGDNORM1.PARENT_ORGANIZATION_ID , LKUP.MEANING , LKUP.LOOKUP_CODE UNION ALL SELECT RESDNORM.RESOURCE_ORGANIZATION_ID AS ORGANIZATION_ID , LKUP.MEANING AS HEADCOUNT_LEVEL , LKUP.LOOKUP_CODE AS HEADCOUNT_CODE , COUNT(DISTINCT RESDNORM.PERSON_ID) AS EMP_HEADCOUNT FROM PA_RESOURCES_DENORM RESDNORM , PA_LOOKUPS LKUP WHERE LKUP.LOOKUP_TYPE = 'PA_OASCREEN_CHARS' AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE' AND RESDNORM.RESOURCE_ORGANIZATION_ID = PA_REP_UTIL_GLOB.GETU1ORGANIZATIONID AND PA_RESOURCE_UTILS.GET_PERIOD_DATE BETWEEN RESDNORM.RESOURCE_EFFECTIVE_START_DATE AND RESDNORM.RESOURCE_EFFECTIVE_END_DATE AND RESDNORM.UTILIZATION_FLAG = 'Y' GROUP BY RESDNORM.RESOURCE_ORGANIZATION_ID , LKUP.MEANING , LKUP.LOOKUP_CODE