PQH EEO5 Reporting

Description
Categories: BI Publisher
Columns: Employment Category, Job Category Name, Cons Total Category Emps, No Cons Wmale Emps, No Cons Bmale Emps, No Cons Hmale Emps, No Cons Amale Emps, No Cons Imale Emps, No Cons Wfemale Emps, No Cons Bfemale Emps ...
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
Reporting Year
 
Number
Type Agency
 
Enrollment as on Reporting Year
 
Number
Business Group Id