PER Head Count Detail

Description
Categories: BI Publisher
Columns: Org Structure Version Id, Org Structure Element Id, Name, Rev Start Val, Nonrev Start Val, Rev End Val, Nonrev End Val, Rev Perm, Nonrev Perm, Rev Cont ...
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,' ') 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
Business group ID
 
Number
Report Date
 
Date
Job Category