Search Results pattern_description

Overview

APPS.AHL_DEPARTMENT_SHIFTS_V is a reporting and integration view in Oracle E-Business Suite (available on both 12.1.1 and 12.2.2) that belongs to the AHL – Complex Maintenance Repair and Overhaul product family. It is the primary denormalized access point for the assignment of work shifts to maintenance departments. Rather than requiring callers to join the department, calendar, organization, shift-time, and workday-pattern tables individually, the view presents a single query that resolves a department shift record into a human-readable form, including the department description, the owning organization name, the calendar description, the shift start and end times, and the workday pattern attributes (days on / days off). The view is therefore used in AHL data entry, inquiry pages, and downstream reports that need to display shift calendars for a department. It is also frequently referenced during integration work, because external systems consuming shift calendars typically need descriptive fields such as CALENDAR_DESCRIPTION instead of the raw CALENDAR_CODE. The view carries no DML of its own; it is read-only and is defined in the APPS schema with the standard concurrent-programming and audit columns.

Underlying Base Objects

The view is defined over six base objects, combining AHL and BOM/HR data:

• AHL_DEPARTMENT_SHIFTS — the driving table holding department shift assignments and the audit columns.
• BOM_DEPARTMENTS_V — supplies the department description via DEPARTMENT_ID and links to HR_ALL_ORGANIZATION_UNITS.
• HR_ALL_ORGANIZATION_UNITS — supplies the organization (inventory/operating unit) NAME.
• BOM_CALENDARS — supplies the calendar DESCRIPTION for the shift's calendar code.
• BOM_SHIFT_TIMES — supplies the FROM_TIME and TO_TIME used to derive SHIFT_START_TIME and SHIFT_END_TIME.
• BOM_WORKDAY_PATTERNS — supplies the PATTERN_DESCRIPTION, DAYS_ON and DAYS_OFF for the shift.

The joins are all effectively equi-joins on DEPARTMENT_ID, ORGANIZATION_ID, CALENDAR_CODE, SHIFT_NUM and SEQ_NUM. Because BOM_DEPARTMENTS_V and HR_ALL_ORGANIZATION_UNITS are joined, organization security and department visibility in the view follow the behavior of those underlying objects, including the HR_GENERAL package and MTL_ITEM_LOCATIONS_KFV referenced in the dependency chain.

Key Columns

• AHL_DEPARTMENT_SHIFTS_ID — primary key of the underlying AHL shift record.
• DEPARTMENT_ID / DEPT_DESCRIPTION / ORGANIZATION_NAME — the department and the organization that owns the shift.
• CALENDAR_CODE / CALENDAR_DESCRIPTION — the calendar identifier and its descriptive label; CALENDAR_DESCRIPTION is the most commonly queried attribute for reporting.
• SHIFT_NUM / SHIFT_START_TIME / SHIFT_END_TIME — shift sequence and the derived start and end times (computed from BOM_SHIFT_TIMES by rounding seconds to hours).
• SEQ_NUM / PATTERN_DESCRIPTION / DAYS_ON / DAYS_OFF — the workday pattern governing the shift, including how many consecutive days are worked and off.
• SECURITY_GROUP_ID, OBJECT_VERSION_NUMBER, and the ATTRIBUTE1–15 / ATTRIBUTE_CATEGORY — standard audit and descriptive-flexfield columns inherited from AHL_DEPARTMENT_SHIFTS.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO columns.

Common Use Cases and Queries

The most frequent use is retrieving descriptive shift calendar information for a department, or producing a listing of all shifts within an organization. A typical query is:

SELECT department_id, dept_description, organization_name, calendar_code, calendar_description, shift_num, shift_start_time, shift_end_time, pattern_description, days_on, days_off FROM apps.ahl_department_shifts_v WHERE calendar_description LIKE :p_calendar ORDER BY organization_name, dept_description, calendar_code, shift_num;

Other common scenarios include validating that each shift in a department resolves to a calendar and workday pattern before loading AHL maintenance schedules, joining the view to work-order or resource queries by CALENDAR_CODE to derive available shift windows, and building OBIEE/BI Publisher reports that expose department shift calendars to planners. Because SHIFT_START_TIME and SHIFT_END_TIME are derived character expressions rather than native time columns, any time arithmetic performed downstream should be based on BOM_SHIFT_TIMES directly.