Search Results hxt_shift_diff_policies_pk

Overview

HXT_SHIFT_DIFF_POLICIES is a Time and Labor (HXT) configuration table in the Oracle E-Business Suite 12.1.1 and 12.2.2 data model. It serves as the master repository for company shift differential policies — the rules that determine when an employee becomes eligible for premium pay and how that premium is applied. Each row represents a single named policy that can be attached to assignment-level shift differential information and expanded into detailed rule lines, making this table the governing header for shift differential processing within Oracle Time and Labor.

Because policies are created once and referenced by many downstream rule and assignment records, the table behaves as a stable, low-volume reference entity. Under the heuristic Data Vault classification mined from the foreign key structure, HXT_SHIFT_DIFF_POLICIES is identified as hub-leaning: it holds the durable business key (the policy name) and a surrogate identifier, while descriptive and rule-specific attributes are carried by dependent tables. This classification is a modeling suggestion rather than a documented design statement.

Key Information Stored

The documented physical schema contains ten columns in the HXT schema. The most significant are:

• ID — The surrogate primary key, enforced by the HXT_SHIFT_DIFF_POLICIES_PK constraint. It is the value propagated to all referencing tables.
• NAME — The business key of the policy. A unique index, HXT_SHIFT_DIFF_POLICIES_UK, is defined on NAME, guaranteeing that no two policies share the same label within the installation.
• DESCRIPTION — Free-text explanation of the policy's intent, typically used to communicate eligibility scope to administrators and payroll analysts.
• DATE_FROM / DATE_TO — The effective date range for the policy. These columns allow policies to be versioned over time and enable date-effective lookups when a rule executes for a given timecard date.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — The standard Oracle EBS audit and concurrency columns, recording who inserted or last modified the row and supporting optimistic locking at the form layer.

The distinction between ID and NAME is important: ID is an internal surrogate with no business meaning, while NAME is the user-facing unique identifier that appears on setup forms and in business documentation.

Common Use Cases and Queries

Typical usage centers on setup validation, effective-dating analysis, and auditing which policies are actually in use. A common pattern joins the policy header to its rule lines:

• Listing all active policies as of a date: SELECT id, name, description FROM hxt_shift_diff_policies WHERE TRUNC(SYSDATE) BETWEEN date_from AND NVL(date_to, TRUNC(SYSDATE));
• Retrieving the full rule set for a policy: SELECT p.name, r.* FROM hxt_shift_diff_policies p, hxt_shift_diff_rules r WHERE r.sdp_id = p.id AND p.name = :name;
• Identifying unused policies: SELECT name FROM hxt_shift_diff_policies p WHERE NOT EXISTS (SELECT 1 FROM hxt_shift_diff_rules r WHERE r.sdp_id = p.id);
• Audit reporting on recent changes: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY to trace configuration drift.
• Reconciling policies against assignments through HXT_ADD_ASSIGN_INFO_F.SHIFT_DIFFERENTIAL_POLICY to confirm which employees are governed by each policy.

Related Objects

The FK metadata identifies two direct dependents, and related setup tables complete the picture:

• HXT_SHIFT_DIFF_RULES — The primary child table. Join on HXT_SHIFT_DIFF_RULES.SDP_ID = HXT_SHIFT_DIFF_POLICIES.ID. Each rule line defines the time band, rate, or multiplier associated with the policy.
• HXT_ADD_ASSIGN_INFO_F — The assignment-level shift differential flexfield, which stores the selected policy. Join on HXT_ADD_ASSIGN_INFO_F.SHIFT_DIFFERENTIAL_POLICY = HXT_SHIFT_DIFF_POLICIES.ID.
• HXT_SHIFT_DIFF_POLICIES_PK / _UK — The constraints that enforce the surrogate and business keys and are relied upon by the above foreign keys.
• Time and Labor setup flows and the shift differential setup forms that populate this table during implementation.
• Payroll and timecard retrieval processes that resolve differentials at calculation time using the policy retrieved through the assignment flexfield.

Because the table is a referenced parent, deletion is restricted while dependent rule or assignment rows exist, and all changes should be managed through the standard ETRM setup interface rather than direct DML.