PER Head Count Detail

select pose.org_structure_version_id
	 ,pose.org_structure_element_id
	 ,rpad(substr((HR_PERSON_FLEX_LOGIC.GetOrgAliasName(pose.organization_id_child,sysdate)),1,60),60,' ')  name
	,hr_head_count.get_rev_start_val(pose.org_structure_element_id) rev_start_val
	,hr_head_count.get_nonrev_start_val(pose.org_structure_element_id) nonrev_start_val
	,hr_head_count.get_rev_end_val(pose.org_structure_element_id) rev_end_val
	,hr_head_count.get_nonrev_end_val(pose.org_structure_element_id) nonrev_end_val
	,hr_head_count.get_rev_perm(pose.org_structure_element_id) rev_perm
	,hr_head_count.get_nonrev_perm(pose.org_structure_element_id) nonrev_perm
	,hr_head_count.get_rev_cont(pose.org_structure_element_id) rev_cont
	,hr_head_count.get_nonrev_cont(pose.org_structure_element_id) nonrev_cont
	,hr_head_count.get_rev_temp(pose.org_structure_element_id) rev_temp
	,hr_head_count.get_nonrev_temp(pose.org_structure_element_id) nonrev_temp
	,hr_head_count.get_rev_cur_nh(pose.org_structure_element_id) rev_cur_nh
	,hr_head_count.get_nonrev_cur_nh(pose.org_structure_element_id) nonrev_cur_nh
	,hr_head_count.get_rev_nh(pose.org_structure_element_id) rev_nh
	,hr_head_count.get_nonrev_nh(pose.org_structure_element_id) nonrev_nh
	,hr_head_count.get_rev_transfer_in(pose.org_structure_element_id) rev_transfer_in
	,hr_head_count.get_nonrev_transfer_in(pose.org_structure_element_id) nonrev_transfer_in
	,hr_head_count.get_rev_transfer_out(pose.org_structure_element_id) rev_transfer_out
	,hr_head_count.get_nonrev_transfer_out(pose.org_structure_element_id) nonrev_transfer_out
	,hr_head_count.get_rev_open_offers(pose.org_structure_element_id) rev_open_offers
	,hr_head_count.get_nonrev_open_offers(pose.org_structure_element_id) nonrev_open_offers
	,hr_head_count.get_rev_accepted_offers(pose.org_structure_element_id) rev_accepted_offers
	,hr_head_count.get_nonrev_accepted_offers(pose.org_structure_element_id) nonrev_accepted_offers
	,hr_head_count.get_rev_vacant_FTE(pose.org_structure_element_id) rev_vacant_FTE
	,hr_head_count.get_nonrev_vacant_FTE(pose.org_structure_element_id) nonrev_vacant_FTE
	,hr_head_count.get_rev_vol_term(pose.org_structure_element_id) rev_vol_term
	,hr_head_count.get_nonrev_vol_term(pose.org_structure_element_id) nonrev_vol_term
	,hr_head_count.get_rev_invol_term(pose.org_structure_element_id) rev_invol_term
	,hr_head_count.get_nonrev_invol_term(pose.org_structure_element_id) nonrev_invol_term
	,hr_head_count.get_rev_cur_term(pose.org_structure_element_id) rev_cur_term
	,hr_head_count.get_nonrev_cur_term(pose.org_structure_element_id) nonrev_cur_term
	,hr_head_count.get_rev_change(pose.org_structure_element_id) rev_change
	,hr_head_count.get_nonrev_change(pose.org_structure_element_id) nonrev_change
	,hr_head_count.get_rev_pct_change(pose.org_structure_element_id) rev_pct_change
	,hr_head_count.get_nonrev_pct_change(pose.org_structure_element_id) nonrev_pct_change
	,posv1.version_number
	,greatest(posv1.date_from,:P_REPORT_DATE_FROM) date_from
	,least(nvl(posv1.date_to,to_date('31/12/4712','DD/MM/YYYY')),:P_REPORT_DATE_TO) date_to,
	PER_PERHDCNT_XMLP_PKG.cf_days_betweenformula(least ( nvl ( posv1.date_to , to_date ( '31/12/4712' , 'DD/MM/YYYY' ) ) , :P_REPORT_DATE_TO ), greatest ( posv1.date_from , :P_REPORT_DATE_FROM )) CF_DAYS_BETWEEN
	/*,
	PER_PERHDCNT_XMLP_PKG.cf_rev_vol_termformula(:CS_rev_vol_term, :Sumrev_start_valPerorg_structu) CF_rev_vol_term,
	PER_PERHDCNT_XMLP_PKG.cf_rev_invol_termformula(:CS_nonrev_invol_term, :Sumnonrev_start_valPerorg_stru) CF_nonrev_invol_term,
	PER_PERHDCNT_XMLP_PKG.cf_nonrev_vol_termformula(:CS_nonrev_vol_term, :Sumnonrev_start_valPerorg_stru) CF_nonrev_vol_term,
	PER_PERHDCNT_XMLP_PKG.cf_nonrev_cur_termformula(:CS_nonrev_cur_term, :Sumnonrev_start_valPerorg_stru) CF_nonrev_cur_term,
	PER_PERHDCNT_XMLP_PKG.cf_rev_cur_termformula(:CS_rev_cur_term, :Sumrev_start_valPerorg_structu) CF_rev_cur_term,
	PER_PERHDCNT_XMLP_PKG.cf_rev_invol_termformula0017(:CS_rev_invol_term, :Sumrev_start_valPerorg_structu) CF_rev_invol_term,
	PER_PERHDCNT_XMLP_PKG.cf_rev_pct_changeformula(:Sumrev_start_valPerorg_structu, :Sumrev_end_valPerorg_structure) CF_rev_pct_change,
	PER_PERHDCNT_XMLP_PKG.cf_nonrev_pct_changeformula(:Sumnonrev_start_valPerorg_stru, :Sumnonrev_end_valPerorg_struct) CF_nonrev_pct_change*/
from   per_org_structure_elements pose
	,per_org_structure_versions posv1
where  pose.organization_id_parent = :P_TOP_ORGANIZATION_ID
and      pose.org_structure_version_id = posv1.org_structure_version_id
and    pose.org_structure_version_id in (
		select posv.org_structure_version_id
		from per_org_structure_versions posv
		where posv.organization_structure_id = :P_ORGANIZATION_STRUCTURE_ID
		and (:P_REPORT_DATE_FROM between posv.date_from and
		nvl(posv.date_to, :P_REPORT_DATE_TO)
		or posv.date_from between :P_REPORT_DATE_FROM and :P_REPORT_DATE_TO))
UNION
select
distinct posv.org_structure_version_id
,1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID) org_structure_element_id
,rpad(substr((HR_PERSON_FLEX_LOGIC.GetOrgAliasName(:P_TOP_ORGANIZATION_ID,sysdate)),1,60),60,' ') name
,hr_head_count.get_rev_start_val(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_start_val
,hr_head_count.get_nonrev_start_val(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_start_val
,hr_head_count.get_rev_end_val(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_end_val
,hr_head_count.get_nonrev_end_val(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_end_val
,hr_head_count.get_rev_perm(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_perm
,hr_head_count.get_nonrev_perm(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_perm
,hr_head_count.get_rev_cont(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_cont
,hr_head_count.get_nonrev_cont(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_cont
,hr_head_count.get_rev_temp(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_temp
,hr_head_count.get_nonrev_temp(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_temp
,hr_head_count.get_rev_cur_nh(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_cur_nh
,hr_head_count.get_nonrev_cur_nh(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_cur_nh
,hr_head_count.get_rev_nh(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_nh
,hr_head_count.get_nonrev_nh(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_nh
,hr_head_count.get_rev_transfer_in(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_transfer_in
,hr_head_count.get_nonrev_transfer_in(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_transfer_in
,hr_head_count.get_rev_transfer_out(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_transfer_out
,hr_head_count.get_nonrev_transfer_out(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_transfer_out
,hr_head_count.get_rev_open_offers(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_open_offers
,hr_head_count.get_nonrev_open_offers(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_open_offers
,hr_head_count.get_rev_accepted_offers(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_accepted_offers
,hr_head_count.get_nonrev_accepted_offers(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_accepted_offers
,hr_head_count.get_rev_vacant_FTE(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_vacant_FTE
,hr_head_count.get_nonrev_vacant_FTE(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_vacant_FTE
,hr_head_count.get_rev_vol_term(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_vol_term
,hr_head_count.get_nonrev_vol_term(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_vol_term
,hr_head_count.get_rev_invol_term(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_invol_term
,hr_head_count.get_nonrev_invol_term(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_invol_term
,hr_head_count.get_rev_cur_term(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_cur_term
,hr_head_count.get_nonrev_cur_term(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_cur_term
,hr_head_count.get_rev_change(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_change
,hr_head_count.get_nonrev_change(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_change
,hr_head_count.get_rev_pct_change(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) rev_pct_change
,hr_head_count.get_nonrev_pct_change(1-(posv.org_structure_version_id+:P_TOP_ORGANIZATION_ID)) nonrev_pct_change
,posv.version_number
,greatest(posv.date_from,:P_REPORT_DATE_FROM) date_from
,least(nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY')),:P_REPORT_DATE_TO) date_to,
	PER_PERHDCNT_XMLP_PKG.cf_days_betweenformula(least ( nvl ( posv.date_to , to_date ( '31/12/4712' , 'DD/MM/YYYY' ) ) , :P_REPORT_DATE_TO ), greatest ( posv.date_from , :P_REPORT_DATE_FROM )) CF_DAYS_BETWEEN
	/*,
	PER_PERHDCNT_XMLP_PKG.cf_rev_vol_termformula(:CS_rev_vol_term, :Sumrev_start_valPerorg_structu) CF_rev_vol_term,
	PER_PERHDCNT_XMLP_PKG.cf_rev_invol_termformula(:CS_nonrev_invol_term, :Sumnonrev_start_valPerorg_stru) CF_nonrev_invol_term,
	PER_PERHDCNT_XMLP_PKG.cf_nonrev_vol_termformula(:CS_nonrev_vol_term, :Sumnonrev_start_valPerorg_stru) CF_nonrev_vol_term,
	PER_PERHDCNT_XMLP_PKG.cf_nonrev_cur_termformula(:CS_nonrev_cur_term, :Sumnonrev_start_valPerorg_stru) CF_nonrev_cur_term,
	PER_PERHDCNT_XMLP_PKG.cf_rev_cur_termformula(:CS_rev_cur_term, :Sumrev_start_valPerorg_structu) CF_rev_cur_term,
	PER_PERHDCNT_XMLP_PKG.cf_rev_invol_termformula0017(:CS_rev_invol_term, :Sumrev_start_valPerorg_structu) CF_rev_invol_term,
	PER_PERHDCNT_XMLP_PKG.cf_rev_pct_changeformula(:Sumrev_start_valPerorg_structu, :Sumrev_end_valPerorg_structure) CF_rev_pct_change,
	PER_PERHDCNT_XMLP_PKG.cf_nonrev_pct_changeformula(:Sumnonrev_start_valPerorg_stru, :Sumnonrev_end_valPerorg_struct) CF_nonrev_pct_change*/
from 	per_org_structure_versions posv
where 	posv.organization_structure_id = :P_ORGANIZATION_STRUCTURE_ID
and 	(:P_REPORT_DATE_FROM between posv.date_from and
	nvl(posv.date_to, :P_REPORT_DATE_TO)
	or posv.date_from between :P_REPORT_DATE_FROM and :P_REPORT_DATE_TO)
and            :P_INCLUDE_TOP_ORG = 'Y'
order by 1,3
--/