eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PER_US_EEO5_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 224


  SELECT name  bg_name,
         location_id
   FROM	 hr_all_organization_units
  WHERE  business_group_id = p_business_group_id
    AND  organization_id = p_business_group_id;

Line: 234


  SELECT address_line_1||' '||address_line_2||' '||address_line_3  bg_address,
         town_or_city  city,
	 region_1      county,
	 region_2      state,
	 postal_code   zip_code
   FROM	 hr_locations
  WHERE  location_id = l_location_id;

Line: 251


  SELECT org_information1	cert_officer_name,
         org_information2	cert_official_title,
         org_information10	contact_telephone,
         org_information12	control_number,
         org_information13	system_district,
         org_information13||' SUMMARY'  type_report
   FROM	 hr_organization_information
  WHERE	 org_information_context = 'EEO_REPORT'
    AND	 organization_id = p_business_group_id;

Line: 275


  SELECT  DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
          	'FR', 'A. FULL-TIME STAFF') employment_category,
          DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
     	        'FR',LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning)          job_category_name,
  -- Added for bug#11736960
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,1,2,1,3,1,4,1,5,1,6,1,NULL)) cons_total_category_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'M',1,NULL),5,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'F',1,NULL),5,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
  /* Commented for bug#11736960 starts
       COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL))  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
     Commented for bug#11736960 ends */
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,p_fr,p_ft,p_pr,p_pt) IN ('FR')
AND    hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
AND    job.job_information_category = 'US'
AND    p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND    p_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,p_fr,p_ft,p_pr,p_pt),
          'FR', 'A. FULL-TIME STAFF') ,
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
     'FR',LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning)
ORDER BY 1,2;

Line: 342


  SELECT  DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
          	'FT','B. PART-TIME STAFF',
		'PR','B. PART-TIME STAFF',
		'PT','B. PART-TIME STAFF') employment_category,
          DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
     	        '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,
  -- Added for bug#11736960
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,1,2,1,3,1,4,1,5,1,6,1,NULL)) cons_total_category_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'M',1,NULL),5,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'F',1,NULL),5,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
       COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
  /* Commented for bug#11736960 starts
       COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL)) 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
     Commented for bug#11736960 ends */
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,p_fr,p_ft,p_pr,p_pt) IN ('PR')
AND    hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
AND    job.job_information_category = 'US'
AND    p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND    p_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,p_fr,p_ft,p_pr,p_pt),
      'FT','B. PART-TIME STAFF',
      'PR','B. PART-TIME STAFF',
      'PT','B. PART-TIME STAFF') ,
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
      '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'))
ORDER BY 1,2;

Line: 431


SELECT	'C. NEW HIRES (JULY THRU SEPT. '||p_report_year||')'  employment_category,
         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.')  job_category_name,
  -- Added for bug#11736960
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,1,2,1,3,1,4,1,5,1,6,1,NULL)) cons_total_category_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'M',1,NULL),5,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'F',1,NULL),5,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
      COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
  /* Commented for bug#11736960 starts
      COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL)) 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
     Commented for bug#11736960 ends */
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   p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date -- Added for #9446839
AND   p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date -- Added for #9446839
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(p_report_date,-3) +1  AND p_report_date
AND   ass.primary_flag	= 'Y'
AND   pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) = 'FR'	-- Only full-time regular no temporaries
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.')
ORDER BY 1,2;

Line: 513


  SELECT  count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
                  count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
                  count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
                  count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
                  count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
                  count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
                  count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
                  count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
                  count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
                  count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
FROM   per_all_people_f  peo,
       per_all_assignments_f  ass,
       per_assignment_status_types     ast,
       per_jobs                        job,
       hr_lookups                      hl,
       per_people_extra_info pei
WHERE  peo.person_id = ass.person_id
AND    peo.per_information1 = '13'
AND    peo.person_id = pei.person_id(+)
AND    pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
AND    pei.pei_information5 is not null
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    p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND    p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
AND    ass.primary_flag	= 'Y'
AND    pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('FR')
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)
AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),'FR', 'A. FULL-TIME STAFF') = p_employment_category
AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),'FR',LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning)  = p_job_category_name;

Line: 567


  SELECT  count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
                  count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
                  count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
                  count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
                  count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
                  count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
                  count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
                  count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
                  count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
                  count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
FROM   per_all_people_f  peo,
       per_all_assignments_f  ass,
       per_assignment_status_types     ast,
       per_jobs                        job,
       hr_lookups                      hl,
       per_people_extra_info pei
WHERE  peo.person_id = ass.person_id
AND    peo.per_information1 = '13'
AND    peo.person_id = pei.person_id(+)
AND    pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
AND    pei.pei_information5 is not null
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    p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND    p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
AND    ass.primary_flag	= 'Y'
AND    pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('PR')
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)
AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
          	'FT','B. PART-TIME STAFF',
		'PR','B. PART-TIME STAFF',
		'PT','B. PART-TIME STAFF') = p_employment_category
AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
     	        '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')) = p_job_category_name;

Line: 636


SELECT	count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
                  count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
                  count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
                  count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
                  count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
                  count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
                  count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
                  count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
                  count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
                  count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
FROM  per_all_people_f      peo,
      per_all_assignments_f ass,
      per_jobs              job,
      hr_lookups            hl,
      per_people_extra_info pei
WHERE peo.person_id = ass.person_id
AND    peo.per_information1 = '13'
AND    peo.person_id = pei.person_id(+)
AND    pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
AND    pei.pei_information5 is not null
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   p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date -- Added for #9446839
AND   p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date -- Added for #9446839
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(p_report_date,-3) +1  AND p_report_date
AND   ass.primary_flag	= 'Y'
AND    pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('FR')
AND   ass.organization_id  IN (
          	SELECT 	organization_id
          	FROM	hr_all_organization_units
          	WHERE	business_group_id = p_business_group_id  )
and 'C. NEW HIRES (JULY THRU SEPT. '||p_report_year||')'  = p_employment_category
and 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.')  = p_job_category_name;

Line: 709


    SELECT COUNT(1)
     FROM (
           SELECT ass.location_id
             FROM per_all_assignments_f ass,
                  hr_organization_units_v hou,
                  per_all_people_f  peo
           WHERE  ass.organization_id = hou.organization_id
             AND  ass.person_id       = peo.person_id
             AND  p_report_date between ass.effective_start_date and ass.effective_end_date
             AND  p_report_date between peo.effective_start_date and peo.effective_end_date
             AND  hou.business_group_id = p_business_group_id
           UNION
          SELECT location_id
           FROM  hr_all_organization_units
          WHERE  business_group_id = p_business_group_id
            AND  NVL(date_to,p_report_date + 1) >= p_report_date );

Line: 729


    SELECT COUNT(1)
      FROM hr_organization_units_v  hou
     WHERE hou.business_group_id = p_business_group_id;

Line: 735


  SELECT org_information12 district_id
    FROM hr_organization_information
   WHERE org_information_context = 'EEO_REPORT'
     AND org_information11 = 'EEO5'
     AND organization_id = p_business_group_id;

Line: 779


  SELECT  LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning
    FROM  hr_lookups hl
   WHERE  lookup_type = 'US_EEO5_JOB_CATEGORIES'
     AND  TO_NUMBER(lookup_code) = p_counter
     AND  application_id = 800;