PER Head Count Detail

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Head Count Detail Report
Application: Human Resources
Source: Head Count Detail Report (XML)
Short Name: PERHDCNT_XML
DB package: PER_PERHDCNT_XMLP_PKG
Run PER Head Count Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
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
--/
Parameter Name SQL text Validation
Date_From 
 
 Date
Date_To 
 
 Date
Organization Hierarchy 
 
 LOV Oracle
Top Organization 
 
 LOV Oracle
Worker Type 
 
 LOV Oracle
Roll Up 
 
 LOV Oracle
Budget 
 
 LOV Oracle
Include Top Organization 
 
 LOV Oracle
Include Assignment Type 
 
 LOV Oracle
Days Prior to End Date 
 
 Number
Download
Related Blitz Reports
PER Organization Hierarchy PER Work Force Count PER COBRA Coverage INV Organization Hierarchy Exceptions PER ADA (1) PER COBRA Payments PA Implementation Options PER ADA PA Organization Hierarchy
Blitz Report™

Blitz Report™ provides multiple benefits: