Results for “date_notification_sent”

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

Overview

GHR_PA_ROUTING_HISTORY is a transactional table in the HR schema belonging to the GHR — US Federal Human Resources product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the routing history details of a Personnel Action (PA) request. Where GHR_PA_REQUESTS captures the header-level facts of a submitted personnel action, GHR_PA_ROUTING_HISTORY records each routing event that occurs as that request moves through the approval chain: who acted, in what capacity, at which point in the routing sequence, and with what outcome. The table therefore functions as the audit trail of the PA approval workflow, providing the evidence base for compliance, federal reporting, and dispute resolution.

The ETRM metadata classifies this object heuristically as a link in Data Vault terms. This is a modeling suggestion rather than an Oracle-imposed classification: the table sits at the intersection of several business entities — the PA request, the routing list, the group box, the nature of action, and the family — and its grain is one routing event per combination of those references. Analysts building a Data Vault or dimensional model over GHR data should treat it as a relationship or event table rather than a descriptive hub, while recognizing that it also carries descriptive and status attributes of its own.

Key Information Stored

The table is documented with 31 columns. The surrogate primary key is PA_ROUTING_HISTORY_ID, enforced by the unique index GHR_PA_ROUTING_HISTORY_PK. No separate business-key unique index is documented, so the surrogate identifier is the only guaranteed-unique column.

• PA_REQUEST_ID — the PA request to which this routing event belongs; the principal link back to GHR_PA_REQUESTS.
• ROUTING_LIST_ID and ROUTING_SEQ_NUMBER — the routing list governing the action and the ordinal position of this step within it.
• GROUPBOX_ID — the group box context associated with the routing event.
• NATURE_OF_ACTION_ID and SECOND_NATURE_OF_ACTION_ID — the primary and secondary nature-of-action classifications for the event.
• NOA_FAMILY_CODE — the family to which the nature of action belongs, referencing GHR_FAMILIES.
• ACTION_TAKEN and APPROVAL_STATUS — the disposition recorded for the step, such as approval, rejection, or pending review.
• APPROVED_FLAG, APPROVER_FLAG, AUTHORIZER_FLAG, INITIATOR_FLAG, PERSONNELIST_FLAG, REQUESTER_FLAG, REVIEWER_FLAG — role-indicator flags describing the capacity in which the actor participated.
• ATTACHMENT_MODIFIED_FLAG and DATE_NOTIFICATION_SENT — supporting evidence of document change and notification activity.
• USER_NAME and the USER_NAME_EMPLOYEE_ID / name components — the actor identity, denormalized for reporting convenience.
• NOTEPAD — free-text commentary captured at the routing step.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — standard Oracle Applications who-columns and optimistic locking support.

Common Use Cases and Queries

Typical reporting scenarios include reconstructing the approval path of a specific personnel action, measuring elapsed time between routing steps, identifying approvers by role, and auditing actions where attachments were modified after approval. A common pattern joins the history to the request header and the routing list:

• Returning all routing steps for a request ordered by ROUTING_SEQ_NUMBER to display an approval timeline.
• Filtering on APPROVER_FLAG or AUTHORIZER_FLAG to count how many actions a given approver processed in a period, using LAST_UPDATE_DATE or CREATION_DATE as the date predicate.
• Grouping by NATURE_OF_ACTION_ID and NOA_FAMILY_CODE to analyze workload distribution across action families.
• Joining to GHR_ROUTING_LISTS on ROUTING_LIST_ID to determine whether every required step in the list was actually completed — a completeness check useful in audit preparation.
• Detecting stalled requests by identifying rows where APPROVAL_STATUS remains pending and DATE_NOTIFICATION_SENT is null or stale.

Because the table is transactional and can grow large in high-volume federal installations, queries should always be constrained by PA_REQUEST_ID or by a date range on the who-columns to avoid full scans.

Related Objects

The foreign keys documented in the ETRM metadata define the table's dependency graph:

• GHR_PA_REQUESTS — joined on PA_ROUTING_HISTORY.PA_REQUEST_ID = GHR_PA_REQUESTS.PA_REQUEST_ID; the parent request.
• GHR_ROUTING_LISTS — joined on ROUTING_LIST_ID; defines the routing plan.
• GHR_GROUPBOXES — joined on GROUPBOX_ID; provides the organizational grouping context.
• GHR_NATURE_OF_ACTIONS — referenced twice, via NATURE_OF_ACTION_ID and SECOND_NATURE_OF_ACTION_ID.
• GHR_FAMILIES — joined on NOA_FAMILY_CODE to resolve the action family.

Together these objects form the core of the federal personnel action routing and approval architecture, and GHR_PA_ROUTING_HISTORY is the historical ledger that ties them together at the event level.