ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4719151A9E05362FB09050512 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' ) ) Y PER PERRPEOC_XML US PER EEO Consolidated 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 BI Publisher :c_100_emps :c_business_group_name :c_def_activ_1 :c_def_activ_2 :c_def_activ_3 :c_def_activ_4 :c_def_apprent :c_def_duns :c_def_gov_con :c_def_gre :c_def_naics :c_def_sic :c_est_activ_1 :c_est_activ_2 :c_est_activ_3 :c_est_activ_4 :c_hierarchy_name :c_hierarchy_version_num :c_no_of_establishments :c_parent_node_id :c_parent_org_id :c_payroll_period_date_end :c_payroll_period_date_start :c_report_mode :c_report_type :c_report_year :cons_job_category_code :cp_prev_year_filed :p_audit_report :p_business_group_id :p_conc_request_id :p_hierarchy_id :p_hierarchy_version_id :p_payroll_period_date :p_payroll_period_date_end :p_payroll_period_date_end_t :p_payroll_period_date_start :p_payroll_period_date_start_t :p_report_mode :p_report_year 1 -20 :p_business_group_id LOV Oracle HR_US_BUSINESS_GROUP_NAME Y select hou.business_group_id id, hou.name value, null description from hr_organization_units hou where hou.business_group_id= fnd_global.per_business_group_id and hou.organization_id=fnd_global.per_business_group_id order by hou.name Select hou.name from hr_organization_units hou where hou.business_group_id = fnd_global.per_business_group_id and hou.organization_id = fnd_global.per_business_group_id Y US Business Group Name Used in SRS Reporting 2 10 :p_payroll_period_date_start Date Y US Payroll Period Start Date Used in SRS Reporting 3 20 :p_payroll_period_date_end Date Y US Payroll Period End Date Used in SRS Reporting 4 30 :p_hierarchy_id LOV Oracle HR_US_GH_HIERARCHY_ID Y select pgh.hierarchy_id id, pgh.name value, null description from per_gen_hierarchy pgh where pgh.business_group_id=fnd_global.per_business_group_id order by value,description Y US Hierarchy_Name Used in SRS Reporting 5 40 :p_hierarchy_version_id LOV Oracle HR_US_GH_HIER_VERSION_ID Y select pgv.hierarchy_version_id id, pgh.name||' ver: '||pgv.version_number value, null description from per_gen_hierarchy pgh,per_gen_hierarchy_versions pgv where pgh.hierarchy_id=:$flex$.hierarchy_name and pgh.hierarchy_id=pgv.hierarchy_id and pgv.status='A' and pqh_generic_hierarchy_package.validate_vets_hierarchy (pgv.hierarchy_version_id)='Y' order by pgv.version_number Y US Hierarchy Version Used in SRS Reporting 6 50 :p_report_mode LOV Oracle HR_US_REPORT_MODE Y select ffvv.flex_value id, ffvv.flex_value value, ffvv.flex_value_meaning||nvl2(ffvv.description,': '||ffvv.description,null) description from fnd_flex_values_vl ffvv where ffvv.flex_value_set_id=1015174 order by ffvv.flex_value US Report Mode: Final (F) or Draft (D) Report Mode to determine whether EIT data to be inserted. 7 60 :p_audit_report LOV Oracle HR_US_YES_NO Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='YES_NO' order by value,description N US Create Audit Report Create Audit Report Yes or No :$flex$.hierarchy_name Hierarchy_Name Hierarchy Version