Oracle HCM Compliance SQL — Production-Ready

-- EEO-1 Category 1 Headcount
-- Reports as of: snapshot date (typically Oct 15 or Dec 31)
-- Tables: PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M,
--         PER_PERSONS_LEGISLATIVE_F, PER_JOBS_F, HR_LOCATIONS_ALL
SELECT
  -- EEO-1 job category from Oracle job code mapping
  jm.eeo1_category,
  -- EEOC race/ethnicity codes mapped to standard labels
  CASE pl.ETHNIC_GROUP
    WHEN '2' THEN 'Hispanic or Latino'
    WHEN '1' THEN 'White (Not Hispanic)'
    WHEN '4' THEN 'Black or African American'
    WHEN '7' THEN 'Asian'
    WHEN '5' THEN 'American Indian or Alaska Native'
    WHEN '3' THEN 'Native Hawaiian or Other Pacific Islander'
    WHEN '6' THEN 'Two or More Races'
    ELSE        'Not Disclosed'
  END                AS race_ethnicity,
  p.SEX             AS gender,
  COUNT(*)          AS headcount
FROM
  PER_ALL_PEOPLE_F         p
  JOIN PER_ALL_ASSIGNMENTS_M  a
    ON  a.PERSON_ID     = p.PERSON_ID
    AND a.PRIMARY_FLAG  = 'Y'
    AND a.ASSIGNMENT_TYPE IN ('E','C')
    AND :snapshot_date BETWEEN
          a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
  JOIN PER_PERSONS_LEGISLATIVE_F pl
    ON  pl.PERSON_ID    = p.PERSON_ID
    AND pl.LEGISLATION_CODE = 'US'
    AND :snapshot_date BETWEEN
          pl.EFFECTIVE_START_DATE AND pl.EFFECTIVE_END_DATE
  JOIN eeo1_job_category_map  jm  -- supplied mapping table
    ON  jm.oracle_job_code  = a.JOB_CODE
WHERE
  :snapshot_date BETWEEN
    p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
  AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE_PROCESS'
GROUP BY
  jm.eeo1_category,
  pl.ETHNIC_GROUP,
  p.SEX
ORDER BY
  jm.eeo1_category,
  race_ethnicity,
  gender