PQH EEO5 Reporting

➜ Back to Library
Description
Categories: BI Publisher
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
Run PQH EEO5 Reporting and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Reporting Year 
 
 Number
Type Agency 
 
 Char
Enrollment as on Reporting Year 
 
 Number
Download
 
Related Blitz Reports
BEN Participation Audit Log BEN Back-out Life Events Summary BEN Back-out Life Events Audit Log PER Full Applicant Details PER Full Assignment Details PER Worker Organization Movements PER New Hire State PER New Hire State Magnetic Media GL Account Analysis
Blitz Report™

Blitz Report™ provides multiple benefits: