Results for “ghr_complaints2_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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:

• COMPLAINANT_PERSON_ID — the individual lodging the complaint, linking to the person model.
• BUSINESS_GROUP_ID — the multi-tenant partitioning key scoping the record to an operating enterprise.
• DOCKET_NUMBER — the externally visible agency docket identifier used on correspondence and reports.
• STAGE — the current workflow stage (counseling, investigation, hearing, final decision).
• ALLEG_INCIDENT and ALLEG_DISCRIM_ORG_NAME/ALLEG_DISCRIM_ORG_ID — the alleged discriminatory act and the organizational unit accused.
• ANONYMITY_REQUESTED and CONSOLIDATED_FLAG/CONSOLIDATED_COMPLAINT_ID — describe complaint handling posture.
• INVESTIGATION_START, INVESTIGATION_END, AGENCY_RECVD_ROI — investigation window timestamps.
• COMPLAINT_CLOSED, COMPLAINT_CLOSED_DESC, NATURE_OF_CLOSURE — closure outcome.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER) for auditing and optimistic locking, plus ATTRIBUTE1–30 for client extensibility.

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.