ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471A451A9E05362FB09050512 select nvl(ass.location_id,-1) location_id ,substr(hla.location_code||' - '||hla.address_line_1||' '||hla.address_line_2||' '||hla.address_line_3,1,115) address1 ,hla.town_or_city||decode(ass.location_id,null,null,', ')||hla.region_2||' '||hla.postal_code city1, PER_PERUSEOX_XMLP_PKG.CP_display_p CP_display from per_all_people_f peo ,per_all_assignments_f ass ,per_jobs job ,hr_locations_all hla where peo.business_group_id = :P_BUSINESS_GROUP_ID and ass.business_group_id = :P_BUSINESS_GROUP_ID and ass.primary_flag = 'Y' and ass.assignment_type = 'E' and peo.current_employee_flag = 'Y' and :report_date_end between peo.effective_start_date and peo.effective_end_date and :report_date_end between ass.effective_start_date and ass.effective_end_date and peo.person_id = ass.person_id and ass.location_id = hla.location_id (+) AND EXISTS ( SELECT 'X' FROM hr_location_extra_info hlei1 where hla.location_id = hlei1.location_id and hlei1.information_type = 'EEO-1 Specific Information' and hlei1.lei_information_category = 'EEO-1 Specific Information') UNION select nvl(ass.location_id,-1) location_id ,substr(hla.location_code||' - '||hla.address_line_1||' '||hla.address_line_2||' '||hla.address_line_3,1,115) address ,hla.town_or_city||decode(ass.location_id,null,null,', ')||hla.region_2||' '||hla.postal_code city ,PER_PERUSEOX_XMLP_PKG.CP_display_p CP_display from per_all_people_f peo ,per_all_assignments_f ass ,per_jobs job ,hr_locations_all hla where peo.business_group_id = :P_BUSINESS_GROUP_ID and ass.business_group_id = :P_BUSINESS_GROUP_ID and ass.primary_flag = 'Y' and ass.assignment_type = 'E' and peo.current_employee_flag = 'Y' and :report_date_end between peo.effective_start_date and peo.effective_end_date and :report_date_end between ass.effective_start_date and ass.effective_end_date and peo.person_id = ass.person_id and ass.location_id = hla.location_id (+) AND ass.location_id in ( select pghn.entity_id -- PER_PERUSEOX_XMLP_PKG.CP_display_p CP_display from per_gen_hierarchy_nodes pghn where pghn.node_type <> 'PAR' and pghn.hierarchy_version_id = :p_hierarchy_version_id ) group by ass.location_id ,hla.town_or_city||decode(ass.location_id,null,null,', ')|| hla.region_2||' '||hla.postal_code ,hla.location_code||' - '||hla.address_line_1||' '|| hla.address_line_2||' '||hla.address_line_3 order by city1, address1 Y PER PERUSEOX_XML US PER EEO-1 Exception Imported from BI Publisher Description: EEO-1 Exception Report Application: Human Resources Source: EEO-1 Exception Report (XML) Short Name: PERUSEOX_XML DB package: PER_PERUSEOX_XMLP_PKG BI Publisher :c_ass_loc :c_business_group_name :c_hierarchy_name :c_hierarchy_version_num :c_parent_node_id :c_parent_org_id :c_report_date :c_report_year :cp_ass_type :cp_display :cp_emp_cat :cp_emp_name :cp_emp_num :cp_ethnic :cp_gender :cp_job_cat :cp_location :cp_no_rows :cp_reason :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_1 :p_payroll_period_date_start :p_payroll_period_date_start_1 :p_report_year :report_date_end 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 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_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