Search Results shift_start_time

Overview

HXTFV_SHIFT_POLICY is a read-only reporting view owned by the APPS schema within the HXT (Time and Labor) product family of Oracle E-Business Suite. The view consolidates shift differential policy definitions and their associated shift rules with the people and assignments to which those policies are applied. It presents a denormalized, business-friendly projection joining Oracle HRMS person and assignment data to Time and Labor shift differential configuration tables, eliminating the need for report authors to reconstruct the underlying join logic manually. In EBS 12.1.1 and 12.2.2, the view is registered as VALID and is defined with the WITH READ ONLY clause, meaning it may be queried but never used as the target of DML. Its principal role is to support operational reporting and integration extracts—particularly for identifying the shift start time, shift end time, carryover time, and associated payroll element for each active shift differential policy assigned to an employee. Because it anchors all joins on SYSDATE BETWEEN the effective start and end dates of the various date-tracked entities, the view returns only currently effective policy assignments.

Underlying Base Objects

The documented base objects referenced by the view are: HR_ALL_ORGANIZATION_UNITS_TL (via synonym, supplying the business group name), HR_GENERAL (package, invoked through the NVL(GET_BUSINESS_GROUP_ID) security predicate), HR_SECURITY (package, applying business group row-level security), HXT_ADD_ASSIGN_INFO_F (synonym, linking assignments to a shift differential policy), HXT_SHIFT_DIFF_POLICIES (synonym, the policy header), HXT_SHIFT_DIFF_RULES (synonym, the individual shift rules that carry the start and stop times), PAY_ELEMENT_TYPES_F (synonym, outer-joined to supply the element name), PER_ALL_PEOPLE_F (synonym, person details), and PER_ASSIGNMENTS_F (exposed as a view, providing assignment, grade, job, position, and organization context). The join chain is driven from person to assignment, then to the assignment info record, then to the policy header, and finally to the shift rules. The element type join is an outer join (PET.ELEMENT_TYPE_ID (+)), so policies without a linked element still appear. Effective dating is enforced on PER_ALL_PEOPLE_F, PER_ASSIGNMENTS_F, HXT_ADD_ASSIGN_INFO_F, and HXT_SHIFT_DIFF_RULES.

Key Columns

• BUSINESS_GROUP_NAME — name of the business group, resolved through HR_ALL_ORGANIZATION_UNITS_TL using the session language.
• PERSON_NAME and PERSON_COUNT — the employee's full name and a constant value of 1 used for aggregation.
• POLICY_NAME, POLICY_DESCRIPTION, POLICY_START, POLICY_END — identity and effective date range of the shift differential policy.
• SHIFT_NAME, SHIFT_START_TIME, SHIFT_END_TIME, CARRYOVER_TIME — the shift rule definition. SHIFT_START_TIME and SHIFT_END_TIME are the columns most often queried when a user searches on shift_start_time.
• ELEMENT_NAME — the payroll element associated with the rule, when present.
• ID columns — ASSIGNMENT_ID, PERSON_ID, BUSINESS_GROUP_ID, POLICY_ID, ELEMENT_TYPE_ID, GRADE_ID, JOB_ID, ORGANIZATION_ID, and POSITION_ID support downstream joins and drill-down reporting.

Common Use Cases and Queries

Typical uses include shift differential audits, overtime eligibility reporting, and extracts feeding payroll or third-party time systems. A basic query on shift start times follows:

SELECT business_group_name,
       person_name,
       policy_name,
       shift_name,
       shift_start_time,
       shift_end_time,
       carryover_time,
       element_name
FROM   apps.hxtfv_shift_policy
WHERE  shift_start_time IS NOT NULL
ORDER  BY business_group_name, person_name, shift_start_time;

To scope results to a single policy or shift, filter on POLICY_NAME or SHIFT_NAME. Because HR_SECURITY is invoked, results are automatically restricted to the business group of the querying session unless GET_BUSINESS_GROUP_ID is null. The view is most valuable when the requirement is a single, effective-dated row per person, policy, and shift rule, and it should be treated as read-only in all reporting and integration contexts across both 12.1.1 and 12.2.2.