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
Description: Head Count Detail Report
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 |