eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_RES_EMP_HCOUNT_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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