Search Results ghr_complaints2




Overview
Explore in AI Chat

Overview

The GHR_COMPLAINTS2 table is the master transactional entity within the Oracle E-Business Suite US Federal Human Resources (GHR) product for capturing and tracking equal employment opportunity (EEO) complaints filed against a federal agency. Owned by the HR schema and shipped as a VALID object in both EBS 12.1.1 and 12.2.2, it functions as the central anchor for the entire discrimination complaint lifecycle — from pre-complaint counseling through formal investigation, hearing, final agency decision, and closure. The table stores 165 documented columns, making it one of the most architecturally significant objects in the GHR complaint subsystem.

From a dimensional modeling perspective, the heuristic Data Vault classification is hub-leaning. This is derived from the FK topology: six downstream tables reference GHR_COMPLAINTS2 through the shared COMPLAINT_ID column while no inbound foreign keys point from the hub to another parent entity. Practically, this suggests treating GHR_COMPLAINTS2 as the complaint business key hub, with satellite relationships captured by the dependent child tables described below.

Key Information Stored

The surrogate primary key is COMPLAINT_ID, enforced through the unique index GHR_COMPLAINTS2_PK. This same column also serves as the documented business-key candidate, since it is the sole unique index on the table. Semantically important columns include:

Common Use Cases and Queries

The primary usage pattern is complaint lifecycle reporting for EEO compliance and the EEOC/agency reporting cycles. Analysts frequently join GHR_COMPLAINTS2 to GHR_COMPL_PEOPLE to resolve the complainant to a person, or to GHR_COMPL_CA_HEADERS for consolidated case groups. A typical open-case query:

SELECT c.complaint_id, c.docket_number, c.stage,
       c.alleg_incident, c.complaint_closed
  FROM   hr.ghr_complaints2 c
 WHERE  c.business_group_id = :p_bg_id
   AND  NVL(c.complaint_closed,'N') = 'N'
   AND  c.active_flag = 'Y';

Additional scenarios include aging/duration analysis measuring INVESTIGATION_START to INVESTIGATION_END, stage funnel reporting, and docket-number driven queries for in-flight EEOC hearings. Because of the 30 generic ATTRIBUTE columns, customers frequently extend the table for agency-specific tracking fields.

Related Objects

Six documented child tables reference GHR_COMPLAINTS2 through their COMPLAINT_ID foreign key column, and together they constitute the complaint aggregate:

  • GHR_COMPL_PEOPLE.COMPLAINT_ID — persons associated with the complaint (complainants, representatives).
  • GHR_COMPL_CLAIMS.COMPLAINT_ID — individual bases and claims within the complaint.
  • GHR_COMPL_ADRS.COMPLAINT_ID — addresses and correspondence endpoints.
  • GHR_COMPL_CA_HEADERS.COMPLAINT_ID — consolidated-case headers.
  • GHR_COMPL_APPEALS.COMPLAINT_ID — appeal records.
  • GHR_COMPL_AGENCY_APPEALS.COMPLAINT_ID — agency-level appeal activity.

These relationships should be modeled as link/satellite structures keyed on COMPLAINT_ID, reinforcing the hub classification of GHR_COMPLAINTS2.