Results for “org_forwd_rfr_to_agency”

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

Overview

GHR_COMPL_AGENCY_APPEALS is a transactional table in the Oracle E-Business Suite HR schema, delivered as part of the GHR - US Federal Human Resources product family. It stores agency-level appeal records associated with a given complaint, supporting the federal equal employment opportunity (EEO) complaint lifecycle in which a complainant or agency may escalate a decision through the agency appeal process. Each row captures a discrete agency appeal and the administrative milestones that benchmark its progress, including docketing, document receipt and forwarding, briefing deadlines, and final decision tracking.

The object is documented as VALID in ETRM 12.2.2 and is present in the 12.1.1 and 12.2.2 releases of the EBS data model. Its relationship footprint — a single foreign key to GHR_COMPLAINTS2 and a 34-column schema dominated by descriptive attributes rather than secondary references — suggests a satellite-leaning classification under Data Vault modeling heuristics. In practical terms, this positions the table as a dependent child of the complaint hub, accumulating appeal-specific descriptive history keyed to a single parent complaint.

Key Information Stored

The table is anchored by the surrogate primary key column COMPL_AGENCY_APPEAL_ID, which is enforced by the unique index GHR_COMPL_AGENCY_APPEALS_PK. This is the only documented unique index, so it serves as both the technical row identifier and the business-key candidate for the appeal record.

• COMPLAINT_ID — foreign key to GHR_COMPLAINTS2; links the appeal to its parent complaint and defines the table's principal join path.
• APPEAL_DATE — the date the agency appeal was filed or initiated.
• REASON_FOR_APPEAL — narrative or coded justification for the appeal.
• DOCKET_NUM — the docket or case number assigned to the appeal.
• SOURCE_DECISION_DATE — date of the underlying decision being appealed.
• AGENCY_RECVD_REQ_FOR_FILES — date the agency received the request for case files.
• FILES_DUE and FILES_FORWD — due date and actual forwarding date for appeal files.
• AGENCY_BRIEF_DUE and AGENCY_BRIEF_FORWD — deadline and actual submission dates for the agency brief.
• DECISION and DECISION_DATE — the outcome of the agency appeal and the date it was issued.
• DEC_RECVD_BY_AGENCY and DEC_FORWD_TO_ORG — receipt and forwarding milestones for the decision.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — standard EBS WHO-column audit lineage and optimistic-locking control.
• RFR_* columns (REQUEST_FOR_RFR, RFR_DOCKET_NUM, RFR_REQUESTED_BY, AGENCY_RFR_DUE, AGENCY_RFR_SUSPENSE, RFR_DECISION_DATE, RFR_DECISION, and related forwarding dates) — track the request-for-reconsideration (RFR) sub-process, a secondary appeal track folded into the same row.

Common Use Cases and Queries

The primary reporting use case is aging and deadline analysis: identifying appeals where FILES_DUE, AGENCY_BRIEF_DUE, or AGENCY_RFR_DUE have passed without a corresponding forwarding date. A typical pattern joins the appeal to its complaint:

• SELECT a.COMPL_AGENCY_APPEAL_ID, a.COMPLAINT_ID, a.DOCKET_NUM, a.APPEAL_DATE, a.DECISION, a.DECISION_DATE FROM GHR_COMPL_AGENCY_APPEALS a WHERE a.COMPLAINT_ID = :complaint_id; — retrieves all appeals for one complaint.
• SELECT a.COMPLAINT_ID, a.DOCKET_NUM FROM GHR_COMPL_AGENCY_APPEALS a WHERE a.AGENCY_BRIEF_DUE < TRUNC(SYSDATE) AND a.AGENCY_BRIEF_FORWD IS NULL; — flags overdue briefs.
• Joining to GHR_COMPLAINTS2 on COMPLAINT_ID supports complaint-to-appeal rollups, decision-cycle-time metrics, and docket reconciliation reporting for EEO case management.
• RFR tracking reports filter on RFR_DECISION and RFR_DECISION_DATE to separate reconsideration outcomes from primary appeal outcomes.

Related Objects

• GHR_COMPLAINTS2 — the direct parent table; joined on GHR_COMPL_AGENCY_APPEALS.COMPLAINT_ID = GHR_COMPLAINTS2.COMPLAINT_ID.
• GHR_COMPL_AGENCY_APPEALS_PK — the primary key constraint and unique index enforcing COMPL_AGENCY_APPEAL_ID.
• GHR_COMPLAINTS and other GHR_COMPL_* complaint detail tables (hearings, decisions, and investigation records) — companion children under the same complaint that provide the broader case picture when reporting across the EEO lifecycle.
• Standard HR security and lookup objects referenced through COMPLAINT_ID resolution and any WHO-column lookups used by the GHR product UI forms and concurrent programs.

Because the ETRM metadata documents only one outbound foreign key, dependencies beyond GHR_COMPLAINTS2 should be confirmed against the deployed 12.2.2 schema before reliance in custom integrations.