PER EEO Consolidated
Description
Categories: BI Publisher
Imported from BI Publisher
Description: EEO Consolidated Report
Application: Human Resources
Source: EEO Consolidated Report (XML)
Short Name: PERRPEOC_XML
DB package: PER_PERUSEOC_XMLP_PKG
Description: EEO Consolidated Report
Application: Human Resources
Source: EEO Consolidated Report (XML)
Short Name: PERRPEOC_XML
DB package: PER_PERUSEOC_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
select count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),3, decode(peo.sex,'M',1,null),null)) "c_hlmale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),3, decode(peo.sex,'F',1,null),null)) "c_hlfemale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),1, decode(peo.sex,'M',1,null),null)) "c_wmale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),2, decode(peo.sex,'M',1,null),null)) "c_bmale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),5, decode(peo.sex,'M',1,null),null)) "c_hmale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),4, decode(peo.sex,'M',1,null),null)) "c_amale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),6, decode(peo.sex,'M',1,null),null)) "c_imale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),13, decode(peo.sex,'M',1,null),null)) "c_tmracesmale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),1, decode(peo.sex,'F',1,null),null)) "c_wfemale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),2, decode(peo.sex,'F',1,null),null)) "c_bfemale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),5, decode(peo.sex,'F',1,null),null)) "c_hfemale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),4, decode(peo.sex,'F',1,null),null)) "c_afemale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),6, decode(peo.sex,'F',1,null),null)) "c_ifemale", count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),13, decode(peo.sex,'F',1,null),null)) "c_tmracesfemale", count(peo.person_id) "c_total_cat", PER_PERUSEOC_XMLP_PKG.cf_audit_reportformula(:CONS_JOB_CATEGORY_CODE) CF_AUDIT_REPORT from per_all_people_f peo, per_assignments_f ass, per_jobs job ,hr_locations_all loc where peo.person_id = ass.person_id and peo.per_information_category = 'US' and job.job_information_category = 'US' and :P_PAYROLL_PERIOD_DATE_START_T <= nvl(job.date_to,:P_PAYROLL_PERIOD_DATE_END_T) and :P_PAYROLL_PERIOD_DATE_END_T >= job.date_from and job.job_information1 = :cons_job_category_code and ass.job_id = job.job_id and peo.effective_start_date = (select max(peo1.effective_start_date) from per_all_people_f peo1 where :P_PAYROLL_PERIOD_DATE_START_T <= peo1.effective_end_date and :P_PAYROLL_PERIOD_DATE_END_T >= peo1.effective_start_date and peo.person_id = peo1.person_id and peo1.current_employee_flag = 'Y' ) and ass.effective_start_date = (select max(ass1.effective_start_date) from per_assignments_f ass1, per_assignment_status_types ast where :P_PAYROLL_PERIOD_DATE_START_T <= ass1.effective_end_date and :P_PAYROLL_PERIOD_DATE_END_T >= ass1.effective_start_date and ass.person_id = ass1.person_id and ass1.assignment_type = 'E' and ass1.primary_flag = 'Y' and ass1.assignment_status_type_id = ast.assignment_status_type_id and ast.per_system_status <> 'TERM_ASSIGN' ) and ass.assignment_type = 'E' and ass.primary_flag = 'Y' and ass.business_group_id = :P_BUSINESS_GROUP_ID and peo.business_group_id = :P_BUSINESS_GROUP_ID and job.business_group_id = :P_BUSINESS_GROUP_ID AND EXISTS ( SELECT 'X' FROM HR_ORGANIZATION_INFORMATION HOI1 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses' AND HOI1.ORGANIZATION_ID = :P_BUSINESS_GROUP_ID ) AND EXISTS ( SELECT 'X' FROM HR_ORGANIZATION_INFORMATION HOI2 WHERE ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1 AND HOI2.ORGANIZATION_ID = :P_BUSINESS_GROUP_ID AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' ) and ass.location_id = loc.location_id and to_char(ass.location_id) in (select pghn.entity_id from per_gen_hierarchy_nodes pghn ,hr_location_extra_info hlei1 ,hr_location_extra_info hlei2 where pghn.node_type = 'EST' and pghn.hierarchy_version_id = :p_hierarchy_version_id and pghn.business_group_id = :P_BUSINESS_GROUP_ID and hlei1.location_id = to_number(pghn.entity_id) and hlei2.location_id = to_number(pghn.entity_id) and hlei1.location_id = hlei2.location_id and hlei1.information_type = 'EEO-1 Specific Information' and hlei1.lei_information_category= 'EEO-1 Specific Information' and hlei2.information_type = 'Establishment Information' and hlei2.lei_information_category= 'Establishment Information' UNION all select pghn2.entity_id from per_gen_hierarchy_nodes pghn2 ,hr_location_extra_info hlei3 where pghn2.node_type = 'LOC' and pghn2.business_group_id = :P_BUSINESS_GROUP_ID and pghn2.hierarchy_version_id = :p_hierarchy_version_id and hlei3.location_id = to_number(pghn2.entity_id) and hlei3.information_type = 'Establishment Information' and hlei3.lei_information_category= 'Establishment Information' ) and exists (select 'X' from per_people_extra_info pei where pei.person_id = peo.person_id and pei.information_type = 'US_ETHNIC_ORIGIN' and (pei.pei_information1 = 'Y' or pei.pei_information2 = 'Y' or pei.pei_information3 = 'Y' or pei.pei_information4 = 'Y' or pei.pei_information5 = 'Y' or pei.pei_information6 = 'Y' or pei.pei_information7 = 'Y' ) ) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Payroll Period Start Date |
|
Date | |
Payroll Period End Date |
|
Date | |
Hierarchy_Name |
|
LOV Oracle | |
Hierarchy Version |
|
LOV Oracle | |
Report Mode: Final (F) or Draft (D) |
|
LOV Oracle | |
Create Audit Report |
|
LOV Oracle |