PER Head Count Detail

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