View: PA_RES_EMP_HCOUNT_V

Product:	PA - Projects
Description:	PA_RES_EMP_HCOUNT_V is a view to provide employee headcounts by organization and periods. This information is used by OA Screens
Implementation/DBA Data:	APPS.PA_RES_EMP_HCOUNT_V

View Text

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

Columns