PER Work Force Count

Description
Categories: BI Publisher
Columns: Organization Id, Name, Rev Start Val, Nonrev Start Val, Rev End Val, Nonrev End Val, Rev Net Change, Nonrev Net Change, Rev Nh, Nonrev Nh ...
Application: Human Resources
Source: Work Force Count Report (XML)
Short Name: PERHDSUM_XML
DB package: PER_PERHDSUM_XMLP_PKG
select
distinct pose.ORGANIZATION_ID_CHILD organization_id
,substr((HR_PERSON_FLEX_LOGIC.GetOrgAliasName(pose.organization_id_child,sysdate)),1,60) name
,hr_head_count_summary.get_rev_start_val(pose.organization_id_child) rev_start_val
,hr_head_count_summary.get_nonrev_start_val(pose.organization_id_child) nonrev_start_val
,hr_head_count_summary.get_rev_end_val(pose.organization_id_child) rev_end_val
,hr_head_count_summary.get_nonrev_end_val(pose.organization_id_child) nonrev_end_val
,hr_head_count_summary.get_rev_net_change(pose.organization_id_child) rev_net_change
,hr_head_count_summary.get_nonrev_net_change(pose.organization_id_child) nonrev_net_change
,hr_head_count_summary.get_rev_nh(pose.organization_id_child) rev_nh
,hr_head_count_summary.get_nonrev_nh(pose.organization_id_child) nonrev_nh
,hr_head_count_summary.get_rev_term(pose.organization_id_child) rev_term
,hr_head_count_summary.get_nonrev_term(pose.organization_id_child) nonrev_term
,hr_head_count_summary.get_rev_other_net(pose.organization_id_child) rev_other_net
,hr_head_count_summary.get_nonrev_other_net(pose.organization_id_child) nonrev_other_net
from    per_org_structure_elements pose
where   exists (select 1
from    per_org_structure_versions posv
where   posv.org_structure_version_id = pose.org_structure_version_id
and     posv.organization_structure_id =  :P_ORGANIZATION_STRUCTURE_ID
and     (posv.date_from <= :P_REPORT_DATE_FROM
or     	nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY')) >=  :P_REPORT_DATE_TO
))
and     pose.organization_id_parent = :P_TOP_ORGANIZATION_ID
union
select
TO_NUMBER(:P_TOP_ORGANIZATION_ID) organization_id
,substr((HR_PERSON_FLEX_LOGIC.GetOrgAliasName(:P_TOP_ORGANIZATION_ID,sysdate)),1,60) name
,hr_head_count_summary.get_rev_start_val(:P_TOP_ORGANIZATION_ID) rev_start_val
,hr_head_count_summary.get_nonrev_start_val(:P_TOP_ORGANIZATION_ID) nonrev_start_val
,hr_head_count_summary.get_rev_end_val(:P_TOP_ORGANIZATION_ID) rev_end_val
,hr_head_count_summary.get_nonrev_end_val(:P_TOP_ORGANIZATION_ID) nonrev_end_val
,hr_head_count_summary.get_rev_net_change(:P_TOP_ORGANIZATION_ID) rev_net_change
,hr_head_count_summary.get_nonrev_net_change(:P_TOP_ORGANIZATION_ID) nonrev_net_change
,hr_head_count_summary.get_rev_nh(:P_TOP_ORGANIZATION_ID) rev_nh
,hr_head_count_summary.get_nonrev_nh(:P_TOP_ORGANIZATION_ID) nonrev_nh
,hr_head_count_summary.get_rev_term(:P_TOP_ORGANIZATION_ID) rev_term
,hr_head_count_summary.get_nonrev_term(:P_TOP_ORGANIZATION_ID) nonrev_term
,hr_head_count_summary.get_rev_other_net(:P_TOP_ORGANIZATION_ID) rev_other_net
,hr_head_count_summary.get_nonrev_other_net(:P_TOP_ORGANIZATION_ID) nonrev_other_net
from    sys.dual
where   :P_INCLUDE_TOP_ORG = 'Y'
Parameter Name SQL text Validation
Date_From
 
Date
Date_To
 
Date
Organization Hierarchy
 
LOV Oracle
Top Organization
 
LOV Oracle
Roll Up
 
LOV Oracle
Budget Type
 
LOV Oracle
Include Top Organization
 
LOV Oracle
Include Assignment Type
 
LOV Oracle
Business group ID
 
Number
Report Date
 
Date
Job Category