Search Results ghr_compl_ca_headers_pk

Overview

GHR_COMPL_CA_HEADERS is a table owned by the HR schema in Oracle E-Business Suite, part of the GHR — US Federal Human Resources product family. It stores the Corrective Action (CA) header information for a given complaint, acting as the top-level record in the corrective action tracking model. A row represents a single corrective action associated with a complaint, capturing docket numbers, compliance milestones, appeal activity, and the administrative lifecycle from initiation through closure and possible reinstatement.

In Oracle EBS 12.1.1 and 12.2.2 the table is present and valid, exposing 28 documented columns. A heuristic Data Vault classification, mined from the foreign key structure, suggests a satellite-leaning role: the table hangs off a parent complaint entity via a single foreign key (COMPLAINT_ID → GHR_COMPLAINTS2) and is itself referenced by a detail table. That pattern is characteristic of an entity participating as the header level of a header/detail grouping rather than of a pure hub or link.

Key Information Stored

The surrogate primary key, enforced by the GHR_COMPL_CA_HEADERS_PK unique index, is COMPL_CA_HEADER_ID. This is the only documented unique index and therefore the only business-key candidate identified in the metadata; no alternative natural key is documented.

• COMPLAINT_ID — foreign key to GHR_COMPLAINTS2, linking the corrective action to its parent complaint.
• CA_SOURCE — identifies the origin or initiating source of the corrective action.
• COMPL_DOCKET_NUMBER, APPEAL_DOCKET_NUMBER, PFE_DOCKET_NUMBER — docket identifiers for the complaint, the appeal, and the petition for enforcement (PFE) respectively.
• LAST_COMPLIANCE_REPORT, COMPLIANCE_CLOSED — compliance reporting and closure indicators.
• PFE_RECEIVED, AGENCY_BRIEF_PFE_DUE, AGENCY_BRIEF_PFE_DATE, DECISION_PFE, DECISION_PFE_DATE — petition-for-enforcement receipt, briefing deadlines, actual briefing dates, and decision tracking.
• AGENCY_RECVD_PFE_DECISION, AGENCY_PFE_BRIEF_FORWD, AGENCY_NOTIFIED_NONCOM — agency-side response and notification milestones.
• COMREP_NONCOM_REQ, EEO_OFF_REQ_DATA_FROM_ORG, ORG_FORWD_DATA_TO_EEO_OFF — data-request exchange steps between the compliance organization and the EEO office.
• DEC_IMPLEMENTED, COMPLAINT_REINSTATED, STAGE_COMPLAINT_REINSTATED — implementation of the decision and reinstatement status.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS concurrency and audit columns.

Common Use Cases and Queries

Typical use cases center on corrective action lifecycle reporting: tracking open versus closed corrective actions, monitoring PFE briefing and decision deadlines, and auditing agency notification and EEO data-exchange compliance. The standard join path is from the complaint to its header, then to the detail lines.

• Listing corrective actions for a complaint: SELECT compl_ca_header_id, ca_source, compl_docket_number FROM ghr_compl_ca_headers WHERE complaint_id = :p_complaint_id;
• Identifying compliance closures occurring in a period: SELECT h.compl_ca_header_id, h.compl_docket_number FROM ghr_compl_ca_headers h WHERE h.compliance_closed = 'Y' AND h.last_update_date BETWEEN :from_date AND :to_date;
• Monitoring PFE decisions not yet implemented: SELECT h.pfe_docket_number, h.decision_pfe_date FROM ghr_compl_ca_headers h WHERE h.decision_pfe_date IS NOT NULL AND h.dec_implemented = 'N';
• Reinstatement reporting using COMPLAINT_REINSTATED and STAGE_COMPLAINT_REINSTATED.
• Audit queries against the standard who-columns to trace record changes.

Related Objects

The documented foreign key relationships define the principal dependencies of this table.

• GHR_COMPLAINTS2 — parent complaint table; join on GHR_COMPL_CA_HEADERS.COMPLAINT_ID = GHR_COMPLAINTS2.COMPLAINT_ID.
• GHR_COMPL_CA_DETAILS — detail table for corrective action lines; join on GHR_COMPL_CA_DETAILS.COMPL_CA_HEADER_ID = GHR_COMPL_CA_HEADERS.COMPL_CA_HEADER_ID.
• GHR_COMPL_CA_HEADERS_PK — the primary key index uniquely identifying each header row.

Query and integration designs should preserve the header/detail cardinality: one complaint yields one or more corrective action headers, and each header yields one or more detail records. Any extract or interface built on these objects must respect the header's foreign key to GHR_COMPLAINTS2 and the dependent foreign key held by GHR_COMPL_CA_DETAILS.