Results for “org_forwd_brief_to_agency”

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

Overview

GHR_COMPL_APPEALS is a transactional table in the HR schema of Oracle E-Business Suite, delivered as part of the GHR – US Federal Human Resources product family. It stores Complainant Appeals associated with a given complaint, capturing the full lifecycle of an appeal filed against an agency decision, including briefing schedules, agency suspense dates, request-for-reconsideration (RFR) tracking, and final decision records. The table is registered as VALID in ETRM and is documented as a physical table owned by HR, containing 38 columns in the 12.2.2 schema.

From a dimensional modeling perspective, the heuristic Data Vault classification is satellite-leaning. This suggests the table behaves primarily as a descriptive satellite attached to a parent business key rather than as an independent hub or a pure link. The single documented foreign key — COMPLAINT_ID referencing GHR_COMPLAINTS2 — reinforces this reading: appeals derive their identity and grain from the complaint they belong to, and each row carries descriptive, time-stamped attributes about that appeal rather than introducing new hub entities. Analysts designing a Data Vault or reporting layer around EEO/complaint data should treat GHR_COMPL_APPEALS as dependent descriptive context subordinate to the complaint.

Key Information Stored

The surrogate primary key is COMPL_APPEAL_ID, enforced by the unique index GHR_COMPL_APPEALS_PK. In the documented metadata this is the only unique index, so there is no separate business-key candidate declared beyond the surrogate; COMPLAINT_ID functions as the foreign business key coupling each appeal to its parent complaint. Among the 38 columns, the most operationally significant are:

• COMPLAINT_ID – foreign key to GHR_COMPLAINTS2, identifying the complaint under appeal.
• APPEAL_DATE and APPEALED_TO – when and to whom the appeal was filed.
• REASON_FOR_APPEAL and SOURCE_DECISION_DATE – the stated grounds and the date of the decision being contested.
• DOCKET_NUM – the appeal docket identifier used for tracking.
• ORG_NOTIFIED_OF_APPEAL – organizational notification marker.
• AGENCY_RECVD_REQ_FOR_FILES, FILES_DUE, FILES_FORWD – the agency file production timeline.
• AGCQ_RECVD_APPELLANT_BRIEF (as documented, AGCY_RECVD_APPELLANT_BRIEF), AGENCY_BRIEF_DUE, APPELLANT_BRIEF_FORWD_ORG, ORG_FORWD_BRIEF_TO_AGENCY, AGENCY_BRIEF_FORWD – briefing exchange and suspense tracking.
• DECISION_DATE, DECISION, DEC_RECVD_BY_AGENCY, DEC_FORWD_TO_ORG – the appellate decision and its dissemination.
• REQUEST_FOR_RFR, RFR_DOCKET_NUM, RFR_REQUESTED_BY, AGENCY_RFR_DUE, RFR_FORWD_TO_ORG, ORG_FORWD_RFR_TO_AGENCY, AGENCY_FORWD_RFR_OFO – the reconsideration request workflow.
• RFR_DECISION, RFR_DECISION_DATE, AGENCY_RECVD_RFR_DEC, RFR_DECISION_FORWD_TO_ORG – the reconsideration outcome.
• OBJECT_VERSION_NUMBER and the standard WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) – concurrency control and auditability.

Common Use Cases and Queries

Typical usage centers on tracking appeal and RFR status, aging suspense items, and producing federal EEO/case-management reporting. A common join retrieves all appeals for a complaint:

• SELECT a.COMPL_APPEAL_ID, a.COMPLAINT_ID, a.APPEAL_DATE, a.DOCKET_NUM, a.DECISION, a.RFR_DECISION FROM HR.GHR_COMPL_APPEALS a WHERE a.COMPLAINT_ID = :complaint_id;
• SELECT a.COMPLAINT_ID, COUNT(*) FROM HR.GHR_COMPL_APPEALS a GROUP BY a.COMPLAINT_ID; — appeal volume per complaint.
• SELECT * FROM HR.GHR_COMPL_APPEALS a WHERE a.FILES_DUE < SYSDATE AND a.FILES_FORWD IS NULL; — overdue file production.
• SELECT * FROM HR.GHR_COMPL_APPEALS a WHERE a.AGENCY_BRIEF_DUE < SYSDATE AND a.AGENCY_BRIEF_FORWD IS NULL; — briefing suspense exception report.
• SELECT * FROM HR.GHR_COMPL_APPEALS a WHERE a.RFR_DECISION IS NOT NULL AND a.RFR_DECISION_DATE >= :from_date; — RFR decision trend analysis.

Reporting commonly aggregates appeal counts, decision turnaround times (APPEAL_DATE to DECISION_DATE), and RFR outcomes by agency or organization.

Related Objects

• HR.GHR_COMPLAINTS2 – parent complaint table; joined via GHR_COMPL_APPEALS.COMPLAINT_ID = GHR_COMPLAINTS2.COMPLAINT_ID. This is the sole documented foreign key relationship.
• HR.GHR_COMPL_APPEALS_PK – the primary key unique index on COMPL_APPEAL_ID; the constraint backing row-level integrity and ORM access.
• GHR complaint/appeal form and OA Framework pages in the US Federal HR module, which read and write GHR_COMPL_APPEALS through the standard EBS entity objects.
• GHR_COMPLAINTS-based related child tables (e.g., complaint actions and processing tables in the GHR_COMPLAINTS family) that share the same COMPLAINT_ID parent and support end-to-end complaint lifecycle reporting.
• Standard HR WHO/audit infrastructure and the Oracle EBS concurrency manager, both referencing OBJECT_VERSION_NUMBER and the LAST_UPDATE_* columns.