PER Work Force Count

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'

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'