ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4719051A9E05362FB09050512Y select c_hlmale, c_hlfemale, c_wmale, c_bmale, c_hmale, c_amale, c_imale, c_tmracesmale, c_wfemale, c_bfemale, c_hfemale, c_afemale, c_ifemale, c_tmracesfemale, c_total_cat, CF_set_curr_hw, CF_audit_report, CP_hw_cat, CP_hw_female, CP_hw_male, PER_PERUSEO1_XMLP_PKG.MAINTAIN_EST_EMP_COUNTS('UPDATE', :Est_Node_Id, c_total_cat, c_wmale, c_bmale, c_hmale, c_amale, c_imale, c_wfemale, c_bfemale, c_hfemale, c_afemale, c_ifemale, c_hlmale, c_hlfemale, c_tmracesmale, c_tmracesfemale) CF_MaintainEstEmpCounts from (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_PERUSEO1_XMLP_PKG.cf_set_curr_hwformula(:Estab_State, :CONS_JOB_CATEGORY_CODE, :Est_Node_Id) CF_set_curr_hw, PER_PERUSEO1_XMLP_PKG.cf_audit_report(:CONS_JOB_CATEGORY_CODE, :Est_Node_Id) CF_audit_report, PER_PERUSEO1_XMLP_PKG.CP_hw_cat_p CP_hw_cat, PER_PERUSEO1_XMLP_PKG.CP_hw_female_p CP_hw_female, PER_PERUSEO1_XMLP_PKG.CP_hw_male_p CP_hw_male from per_all_people_f peo, per_all_assignments_f ass, per_jobs job 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_all_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 EXISTS ( SELECT 1 FROM PER_GEN_HIERARCHY_NODES PGN WHERE PGN.HIERARCHY_VERSION_ID = :P_HIERARCHY_VERSION_ID AND ( PGN.HIERARCHY_NODE_ID = :EST_NODE_ID OR PGN.PARENT_HIERARCHY_NODE_ID = :EST_NODE_ID ) AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) AND ASS.LOCATION_ID=PGN.ENTITY_ID ) 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' ) )) PER PERRPEO1_XML US PER EEO Individual Establishment Imported from BI Publisher Description: EEO Individual Establishment Report Application: Human Resources Source: EEO Individual Establishment Report (XML) Short Name: PERRPEO1_XML DB package: PER_PERUSEO1_XMLP_PKG BI Publisher :c_100_emps :c_affiliated :c_all_total :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_priod_end_date :c_report_mode :c_report_year :cons_job_category_code :cp_hw_aud_female :cp_hw_aud_male :cp_hw_aud_tot :cp_hw_cat :cp_hw_female :cp_hw_male :cp_payroll_period_date_end :cp_payroll_period_date_start :cp_prev_year_filed :cp_report_type :cp_report_year :cp_tot_emps :est_node_id :estab_state :p_audit_report :p_business_group_id :p_conc_request_id :p_hierarchy_id :p_hierarchy_version_id :p_minimum_no_of_employees :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) Final (F) or Draft (D) 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