PQH EEO5 Reporting

Description
Categories: BI Publisher, Human Resources
Application: Public Sector HR
Source: EEO5 Reporting (XML)
Short Name: PQHEEO5_XML
DB package: PQH_PQHEEO5_XMLP_PKG
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
Business Group Id
 
Enrollment as on Reporting Year
 
Number
Type Agency
 
Reporting Year
 
Number