PER Head Count Detail

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: Head Count Detail Report (XML)
Short Name: PERHDCNT_XML
DB package: PER_PERHDCNT_XMLP_PKG
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,' ''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
--/
Parameter Name SQL text Validation
Job Category
 
Report Date
 
Date
Business group ID
 
Number
Days Prior to End Date
 
Number
Include Assignment Type
 
LOV Oracle
Include Top Organization
 
LOV Oracle
Budget
 
LOV Oracle
Roll Up
 
LOV Oracle
Worker Type
 
LOV Oracle
Top Organization
 
LOV Oracle
Organization Hierarchy
 
LOV Oracle
Date_To
 
Date
Date_From
 
Date