PER EEO Consolidated

select count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),3,
                               decode(peo.sex,'M',1,null),null))   "c_hlmale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),3,
        decode(peo.sex,'F',1,null),null))   "c_hlfemale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),1,
        decode(peo.sex,'M',1,null),null))  "c_wmale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),2,
        decode(peo.sex,'M',1,null),null))   "c_bmale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),5,
        decode(peo.sex,'M',1,null),null))   "c_hmale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),4,
        decode(peo.sex,'M',1,null),null))   "c_amale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),6,
        decode(peo.sex,'M',1,null),null))   "c_imale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),13,
        decode(peo.sex,'M',1,null),null))   "c_tmracesmale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),1,
        decode(peo.sex,'F',1,null),null))  "c_wfemale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),2,
        decode(peo.sex,'F',1,null),null))   "c_bfemale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),5,
        decode(peo.sex,'F',1,null),null))   "c_hfemale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),4,
        decode(peo.sex,'F',1,null),null))   "c_afemale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),6,
        decode(peo.sex,'F',1,null),null))   "c_ifemale",
  count(decode(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),13,
        decode(peo.sex,'F',1,null),null))   "c_tmracesfemale",
  count(peo.person_id)  "c_total_cat",
	PER_PERUSEOC_XMLP_PKG.cf_audit_reportformula(:CONS_JOB_CATEGORY_CODE) CF_AUDIT_REPORT
from
per_all_people_f                        peo,
per_assignments_f                    ass,
per_jobs                                 job
,hr_locations_all		 loc
where
    peo.person_id                  = ass.person_id
and peo.per_information_category = 'US'
and job.job_information_category   = 'US'
and :P_PAYROLL_PERIOD_DATE_START_T
       <= nvl(job.date_to,:P_PAYROLL_PERIOD_DATE_END_T)
and :P_PAYROLL_PERIOD_DATE_END_T
       >= job.date_from
and job.job_information1           = :cons_job_category_code
and ass.job_id                     = job.job_id
and peo.effective_start_date =
      (select  max(peo1.effective_start_date)
       from     per_all_people_f peo1
       where  :P_PAYROLL_PERIOD_DATE_START_T <=
                   peo1.effective_end_date
       and      :P_PAYROLL_PERIOD_DATE_END_T >=
                   peo1.effective_start_date
       and      peo.person_id = peo1.person_id
       and      peo1.current_employee_flag = 'Y'
       )
and ass.effective_start_date =
      (select max(ass1.effective_start_date)
       from    per_assignments_f ass1,
               per_assignment_status_types ast
       where :P_PAYROLL_PERIOD_DATE_START_T <=
                  ass1.effective_end_date
       and     :P_PAYROLL_PERIOD_DATE_END_T  >=
                  ass1.effective_start_date
       and     ass.person_id = ass1.person_id
       and     ass1.assignment_type  = 'E'
       and     ass1.primary_flag     = 'Y'
       and     ass1.assignment_status_type_id = ast.assignment_status_type_id
       and     ast.per_system_status <> 'TERM_ASSIGN'
       )
and ass.assignment_type  = 'E'
and ass.primary_flag     = 'Y'
and ass.business_group_id =  :P_BUSINESS_GROUP_ID
 and peo.business_group_id =  :P_BUSINESS_GROUP_ID
 and job.business_group_id =   :P_BUSINESS_GROUP_ID
AND EXISTS ( 
      SELECT 'X'
      FROM   HR_ORGANIZATION_INFORMATION HOI1
      WHERE  TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1 
      AND    HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses' 
      AND    HOI1.ORGANIZATION_ID = :P_BUSINESS_GROUP_ID 
				)
AND EXISTS ( 
      SELECT 'X'
      FROM   HR_ORGANIZATION_INFORMATION HOI2
      WHERE  ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1 
        AND  HOI2.ORGANIZATION_ID = :P_BUSINESS_GROUP_ID 
        AND  HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' 					
	     )
and ass.location_id = loc.location_id
and to_char(ass.location_id) in
      (select pghn.entity_id
       from   per_gen_hierarchy_nodes pghn
                ,hr_location_extra_info hlei1
                ,hr_location_extra_info hlei2
       where pghn.node_type = 'EST'
       and  pghn.hierarchy_version_id = :p_hierarchy_version_id
       and  pghn.business_group_id = :P_BUSINESS_GROUP_ID
       and	hlei1.location_id = to_number(pghn.entity_id)
       and	hlei2.location_id = to_number(pghn.entity_id)
       and	hlei1.location_id = hlei2.location_id
       and	hlei1.information_type = 'EEO-1 Specific Information'
       and	hlei1.lei_information_category= 'EEO-1 Specific Information'
       and	hlei2.information_type = 'Establishment Information'
       and	hlei2.lei_information_category= 'Establishment Information'
      UNION all
       select pghn2.entity_id
       from   per_gen_hierarchy_nodes pghn2
              ,hr_location_extra_info hlei3
       where  pghn2.node_type = 'LOC'
       and  pghn2.business_group_id = :P_BUSINESS_GROUP_ID
       and  pghn2.hierarchy_version_id = :p_hierarchy_version_id
       and	hlei3.location_id = to_number(pghn2.entity_id)
       and	hlei3.information_type = 'Establishment Information'
       and	hlei3.lei_information_category= 'Establishment Information'
       )
and exists (select 'X'
            from   per_people_extra_info pei
            where  pei.person_id = peo.person_id
            and    pei.information_type = 'US_ETHNIC_ORIGIN'
            and   (pei.pei_information1 = 'Y'
                   or pei.pei_information2 = 'Y'
                   or pei.pei_information3 = 'Y'
                   or pei.pei_information4 = 'Y'
                   or pei.pei_information5 = 'Y'
                   or pei.pei_information6 = 'Y'
                   or pei.pei_information7 = 'Y'
                  )
           )