PER EEO Individual Establishment

select c_hlmale,
       c_hlfemale,
       c_wmale,
       c_bmale,
       c_hmale,
       c_amale,
       c_imale,
       c_tmracesmale,
       c_wfemale,
       c_bfemale,
       c_hfemale,
       c_afemale,
       c_ifemale,
       c_tmracesfemale,
       c_total_cat,
       CF_set_curr_hw,
       CF_audit_report,
       CP_hw_cat,
       CP_hw_female,
       CP_hw_male,
       PER_PERUSEO1_XMLP_PKG.MAINTAIN_EST_EMP_COUNTS('UPDATE', :Est_Node_Id, c_total_cat, c_wmale, c_bmale, c_hmale, c_amale, c_imale, c_wfemale, c_bfemale, c_hfemale, c_afemale, c_ifemale, c_hlmale, c_hlfemale, c_tmracesmale, c_tmracesfemale) CF_MaintainEstEmpCounts
from
(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_PERUSEO1_XMLP_PKG.cf_set_curr_hwformula(:Estab_State, :CONS_JOB_CATEGORY_CODE, :Est_Node_Id) CF_set_curr_hw,
	PER_PERUSEO1_XMLP_PKG.cf_audit_report(:CONS_JOB_CATEGORY_CODE, :Est_Node_Id) CF_audit_report,
	PER_PERUSEO1_XMLP_PKG.CP_hw_cat_p CP_hw_cat,
	PER_PERUSEO1_XMLP_PKG.CP_hw_female_p CP_hw_female,
	PER_PERUSEO1_XMLP_PKG.CP_hw_male_p CP_hw_male
from
per_all_people_f                         peo,
per_all_assignments_f               ass,
per_jobs                                  job
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_all_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 EXISTS ( 
  SELECT 1
  FROM  PER_GEN_HIERARCHY_NODES PGN
  WHERE PGN.HIERARCHY_VERSION_ID = :P_HIERARCHY_VERSION_ID 
    AND ( PGN.HIERARCHY_NODE_ID = :EST_NODE_ID 
         OR PGN.PARENT_HIERARCHY_NODE_ID = :EST_NODE_ID ) 
    AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) 
    AND ASS.LOCATION_ID=PGN.ENTITY_ID
    )
   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'
                     )
              ))