Search Results ghr_compl_adrs




Overview
Explore in AI Chat

Overview

GHR_COMPL_ADRS is a table in the HR schema of Oracle E-Business Suite, belonging to the GHR – US Federal Human Resources product family. It stores Alternate Dispute Resolution (ADR) details associated with a given complaint. Within the federal HR complaint management process, ADR represents the structured mechanisms — such as mediation, facilitation, or settlement conferences — through which a complaint may be resolved outside formal adjudication. This table therefore captures the ADR lifecycle for each complaint, tracking the stage reached, the resource and technique applied, the outcome, and the dates on which ADR was offered and accepted.

The object is documented as VALID in the ETRM 12.2.2 physical schema, with 16 columns and a single unique index, GHR_COMPL_ADRS_PK, defined on COMPL_ADR_ID. Its only documented foreign key points to GHR_COMPLAINTS2 via COMPLAINT_ID, confirming its role as a dependent detail table rather than a standalone entity.

From a Data Vault modeling perspective, the metadata heuristic classifies this table as satellite-leaning. This is a modeling suggestion: the table behaves as a descriptive satellite attached to the complaint hub (GHR_COMPLAINTS2), recording the changing ADR attributes, stages, dates, and outcomes for each complaint. The unique key COMPL_ADR_ID functions as the satellite's own surrogate identifier, while COMPLAINT_ID acts as the link back to the parent hub.

Key Information Stored

The most significant columns and their contents are as follows:

  • COMPL_ADR_ID — The surrogate primary key of the table, uniquely identifying each ADR record. It is the column backing the GHR_COMPL_ADRS_PK unique index.
  • COMPLAINT_ID — The foreign key to GHR_COMPLAINTS2, identifying the complaint to which the ADR record belongs. This is the principal business-key candidate linking ADR activity to the originating case.
  • STAGE — The ADR stage or phase in which the record sits, indicating process progression.
  • START_DATE and END_DATE — The effective window during which the ADR activity occurred.
  • ADR_RESOURCE — The party or resource engaged to conduct the ADR process, such as a mediator or counselor.
  • TECHNIQUE — The specific ADR technique applied, for example mediation or facilitation.
  • OUTCOME — The result of the ADR effort, such as resolution, settlement, or no agreement.
  • ADR_OFFERED and DATE_ACCEPTED — Flags and dates recording whether ADR was offered to the complainant and when it was accepted.
  • OBJECT_VERSION_NUMBER — The optimistic locking column used by the Oracle framework for concurrent update control.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — The standard EBS audit columns capturing who created and last modified the record, when, and from which login session.

Common Use Cases and Queries

The table supports federal complaint tracking and reporting on ADR utilization, effectiveness, and timeliness. Typical scenarios include determining which complaints have ADR records, how many were offered ADR, acceptance rates, and outcomes by technique or resource.

A common query pattern retrieves all ADR records for a specific complaint:

  • SELECT a.compl_adr_id, a.complaint_id, a.stage, a.start_date, a.end_date, a.technique, a.outcome FROM hr.ghr_compl_adrs a WHERE a.complaint_id = :p_complaint_id;

An aggregate reporting query measures ADR acceptance and outcomes over a time window:

  • SELECT a.technique, a.outcome, COUNT(*) FROM hr.ghr_compl_adrs a WHERE a.start_date BETWEEN :p_from AND :p_to GROUP BY a.technique, a.outcome;

Because ADR_OFFERED and DATE_ACCEPTED are stored on the same row, analysts can compute offer-to-acceptance lag and correlate it with complaint resolution timelines. Join to GHR_COMPLAINTS2 to enrich results with complaint-level attributes.

Related Objects

The most significant related objects are the complaint parent and the standard EBS framework components:

  • GHR_COMPLAINTS2 — The parent table referenced by GHR_COMPL_ADRS.COMPLAINT_ID. This is the primary join path and confirms GHR_COMPL_ADRS as its satellite-leaning detail.
  • GHR_COMPL_ADRS_PK — The unique index on COMPL_ADR_ID, providing the row-level identifying access path.
  • HR schema GHR complaint tables — Sibling complaint detail tables (such as remedy, investigation, and tracking records) that share the same COMPLAINT_ID parent and are commonly queried alongside ADR data for complete case reporting.
  • EBS audit/framework columns — The CREATED_BY and LAST_UPDATED_BY columns resolve through FND_USER, and LAST_UPDATE_LOGIN resolves through FND_LOGINS, enabling standard EBS audit reporting.
  • GHR complaint views and concurrent programs — Federal HR complaint inquiry and reporting views typically join GHR_COMPLAINTS2 and GHR_COMPL_ADRS to present full ADR history to end users.