ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471DB51A9E05362FB09050512Y SELECT decode(pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt), 'FR', 'A. FULL TIME STAFF', 'B. PART-TIME STAFF') employment_category, decode(pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt), 'FR',lpad(hl.lookup_code,2,' ')||'.'||hl.meaning, 'PR', decode(job.job_information1, '2','20.PROF. INSTR.', '3','20.PROF. INSTR.', '4','20.PROF. INSTR.', '5','20.PROF. INSTR.', '6','20.PROF. INSTR.', '7','20.PROF. INSTR.', '8','20.PROF. INSTR.', '9','20.PROF. INSTR.', '10','20.PROF. INSTR.', '11','20.PROF. INSTR.', '12','20.PROF. INSTR.', '21.ALL OTHER')) Job_category_name, count(peo.person_id) cons_total_category_emps, count(decode(peo.per_information1,'1', decode(peo.sex,'M',1,null),null)) no_cons_wmale_emps, count(decode(peo.per_information1,'2', decode(peo.sex,'M',1,null),null)) no_cons_bmale_emps, count(decode(peo.per_information1,'3', decode(peo.sex,'M',1,null),null)) no_cons_hmale_emps, count(decode(peo.per_information1,'4', decode(peo.sex,'M',1,null),'5', decode(peo.sex,'M',1,null),null)) no_cons_amale_emps, count(decode(peo.per_information1,'6', decode(peo.sex,'M',1,null),'7', decode(peo.sex,'M',1,null),null)) no_cons_imale_emps, count(decode(peo.per_information1,'1', decode(peo.sex,'F',1,null),null)) no_cons_wfemale_emps, count(decode(peo.per_information1,'2', decode(peo.sex,'F',1,null),null)) no_cons_bfemale_emps, count(decode(peo.per_information1,'3', decode(peo.sex,'F',1,null),null)) no_cons_hfemale_emps, count(decode(peo.per_information1,'4', decode(peo.sex,'F',1,null),'5', decode(peo.sex,'F',1,null),null)) no_cons_afemale_emps, count(decode(peo.per_information1,'6', decode(peo.sex,'F',1,null),'7', decode(peo.sex,'F',1,null),null)) no_cons_ifemale_emps, PQH_PQHEEO5_XMLP_PKG.cf_total_titleformula0005(decode(pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt), 'FR', 'A. FULL TIME STAFF', 'B. PART-TIME STAFF')) CF_total_title FROM per_all_people_f peo, per_all_assignments_f ass, per_assignment_status_types ast, per_jobs job, hr_lookups hl WHERE peo.person_id = ass.person_id AND peo.current_employee_flag = 'Y' AND hl.lookup_code = job.job_information1 AND pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt) IN ('FR','PR') AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES' AND job.job_information_category = 'US' AND :cp_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND :cp_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date AND ass.primary_flag = 'Y' AND ass.assignment_status_type_id = ast.assignment_status_type_id AND ast.per_system_status <> 'TERM_ASSIGN' AND ass.job_id = job.job_id AND ass.assignment_type = 'E' AND ass.organization_id in ( select organization_id from hr_all_organization_units where business_group_id = :p_business_group_id) Group by decode(pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt), 'FR', 'A. FULL TIME STAFF', 'B. PART-TIME STAFF') , decode(pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt), 'FR',lpad(hl.lookup_code,2,' ')||'.'||hl.meaning, 'PR', decode(job.job_information1, '2','20.PROF. INSTR.', '3','20.PROF. INSTR.', '4','20.PROF. INSTR.', '5','20.PROF. INSTR.', '6','20.PROF. INSTR.', '7','20.PROF. INSTR.', '8','20.PROF. INSTR.', '9','20.PROF. INSTR.', '10','20.PROF. INSTR.', '11','20.PROF. INSTR.', '12','20.PROF. INSTR.', '21.ALL OTHER')),EMPLOYMENT_CATEGORY UNION SELECT 'C. NEW-HIRES (JULY THRU SEPT. ' || :p_report_year || ')' , decode(job.job_information1, '1','23.0/A/M', '2','24.PRIN/ASST.PR', '3','24.PRIN/ASST.PR', '4','24.PRIN/ASST.PR', '5','25.CLSRM. TCHRS', '6','25.CLSRM. TCHRS', '7','25.CLSRM. TCHRS', '8','26.OTHER PROF.', '9','26.OTHER PROF.', '10','26.OTHER PROF.', '11','26.OTHER PROF.', '12','26.OTHER PROF.', '13','27.NONPROF.', '14','27.NONPROF.', '15','27.NONPROF.', '16','27.NONPROF.', '17','27.NONPROF.', '18','27.NONPROF.'), count(peo.person_id) cons_total_category_emps, count(decode(peo.per_information1,'1', decode(peo.sex,'M',1,null),null)) no_cons_wmale_emps, count(decode(peo.per_information1,'2', decode(peo.sex,'M',1,null),null)) no_cons_bmale_emps, count(decode(peo.per_information1,'3', decode(peo.sex,'M',1,null),null)) no_cons_hmale_emps, count(decode(peo.per_information1,'4', decode(peo.sex,'M',1,null),'5', decode(peo.sex,'M',1,null),null)) no_cons_amale_emps, count(decode(peo.per_information1,'6', decode(peo.sex,'M',1,null),'7', decode(peo.sex,'M',1,null),null)) no_cons_imale_emps, count(decode(peo.per_information1,'1', decode(peo.sex,'F',1,null),null)) no_cons_wfemale_emps, count(decode(peo.per_information1,'2', decode(peo.sex,'F',1,null),null)) no_cons_bfemale_emps, count(decode(peo.per_information1,'3', decode(peo.sex,'F',1,null),null)) no_cons_hfemale_emps, count(decode(peo.per_information1,'4', decode(peo.sex,'F',1,null),'5', decode(peo.sex,'F',1,null),null)) no_cons_afemale_emps, count(decode(peo.per_information1,'6', decode(peo.sex,'F',1,null),'7', decode(peo.sex,'F',1,null),null)) no_cons_ifemale_emps, PQH_PQHEEO5_XMLP_PKG.cf_total_titleformula0005('C. NEW-HIRES (JULY THRU SEPT. ' || :p_report_year || ')') CF_total_title FROM per_all_people_f peo, per_all_assignments_f ass, per_jobs job, hr_lookups hl WHERE peo.person_id = ass.person_id AND peo.current_employee_flag = 'Y' AND hl.lookup_code = job.job_information1 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES' AND job.job_information_category = 'US' AND ass.job_id = job.job_id AND ass.assignment_type = 'E' AND (SELECT date_start FROM per_periods_of_service WHERE period_of_service_id = ass.period_of_service_id) BETWEEN ADD_MONTHS(:CP_REPORT_DATE,-3) +1 AND :CP_REPORT_DATE AND ass.primary_flag = 'Y' AND pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt) = 'FR' AND ass.organization_id in ( SELECT organization_id FROM hr_all_organization_units WHERE business_group_id = :p_business_group_id ) GROUP BY DECODE(job.job_information1, '1','23.0/A/M', '2','24.PRIN/ASST.PR', '3','24.PRIN/ASST.PR', '4','24.PRIN/ASST.PR', '5','25.CLSRM. TCHRS', '6','25.CLSRM. TCHRS', '7','25.CLSRM. TCHRS', '8','26.OTHER PROF.', '9','26.OTHER PROF.', '10','26.OTHER PROF.', '11','26.OTHER PROF.', '12','26.OTHER PROF.', '13','27.NONPROF.', '14','27.NONPROF.', '15','27.NONPROF.', '16','27.NONPROF.', '17','27.NONPROF.', '18','27.NONPROF.'),EMPLOYMENT_CATEGORY PQH PQHEEO5_XML US PQH EEO5 Reporting Imported from BI Publisher Description: EEO5 Reporting Application: Public Sector HR Source: EEO5 Reporting (XML) Short Name: PQHEEO5_XML DB package: PQH_PQHEEO5_XMLP_PKG BI Publisher :cp_fr :cp_ft :cp_no_of_schools :cp_pr :cp_pt :cp_report_date :function_desc :lp_report_date :p_business_group_id :p_conc_request_id :p_function_desc :p_report_day_month :p_report_year :p_total_enrollments :p_type_agency 1 -20 :p_business_group_id Char fnd_profile.value('PER_BUSINESS_GROUP_ID') US Business Group Id Business Group Id 2 10 :p_report_year Number Y US Reporting Year Reporting Year 3 20 :p_type_agency Char US Type Agency Type Agency 4 30 :p_total_enrollments Number US Enrollment as on Reporting Year Enrollment as on Reporting Year