Results for “availability_type_code”

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

Overview

JTF_CAL_SHIFT_CONSTRUCTS is a CRM Foundation (JTF) table that stores shift patterns for calendar-based scheduling and resource availability in Oracle E-Business Suite 12.1.1 and 12.2.2. Each row defines a discrete construct: a start time, an end time, the unit of time that applies, an availability type, and the effectivity window during which the pattern is valid. Rather than representing a named shift itself, the table decomposes a shift into its time-bound building blocks, which the scheduling engine then combines to determine when a resource is available.

In the calendar hierarchy, JTF_CAL_SHIFT_CONSTRUCTS functions as a satellite-leaning entity, per the heuristic Data Vault classification mined from its foreign key structure. It carries descriptive detail (times, effectivity dates, availability type) that qualifies a parent shift, which is the more hub-like construct. The table is qualified by SHIFT_CONSTRUCT_ID, the surrogate primary key, and is reached through JTF_CAL_SHIFTS_B rather than standing alone as a reference entity.

Key Information Stored

The 31 documented columns fall into three logical groups: identity, scheduling detail, and standard audit/extension attributes.

• SHIFT_CONSTRUCT_ID — surrogate primary key. Enforced by unique index JTF_CAL_SHIFT_CONSTRUCTS_U1 and by constraints JTF_CAL_SHIFTC_PK and JTF_CAL_SHIFT_CONSTRUCTS_PK. This is the only documented business-key candidate.
• SHIFT_ID — foreign key to JTF_CAL_SHIFTS_B, identifying the parent shift to which this construct belongs.
• BEGIN_TIME and END_TIME — the start and end times that define the interval represented by the construct.
• UNIT_OF_TIME_VALUE — the unit in which the shift duration is expressed.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effectivity window controlling when the construct is valid.
• AVAILABILITY_TYPE_CODE — the availability type associated with the construct, distinguishing availability semantics.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-tenant / group-level data isolation.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the framework to detect concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit Who columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — DFF/flexfield extension columns for customer-specific data.

Common Use Cases and Queries

Typical use cases include generating availability calendars for service or field personnel, validating that a task assignment falls within an active shift, and reporting on shift coverage and resource capacity. Because construct rows are consumed by JTF_TASK_ASSIGNMENTS (via SHIFT_CONSTRUCT_ID), a common query joins the two to determine which assignments reference active shift constructs:

SELECT a.task_assignment_id, c.begin_time, c.end_time, c.availability_type_code
FROM jtf_task_assignments a, jtf_cal_shift_constructs c
WHERE a.shift_construct_id = c.shift_construct_id
AND SYSDATE BETWEEN c.start_date_active AND c.end_date_active;

To enumerate all constructs for a given parent shift, join on SHIFT_ID to JTF_CAL_SHIFTS_B. Operators filtering by availability should restrict on AVAILABILITY_TYPE_CODE, and security-aware queries should filter SECURITY_GROUP_ID. Effectivity filters using START_DATE_ACTIVE and END_DATE_ACTIVE are essential, as historical and future rows coexist.

Related Objects

• JTF_CAL_SHIFTS_B — parent shift definition; joined on SHIFT_ID.
• JTF_TASK_ASSIGNMENTS — references JTF_CAL_SHIFT_CONSTRUCTS via SHIFT_CONSTRUCT_ID; link between tasks and the shift construct governing them.
• CSF_R_TASK_ASSIGN_EXTNS — extension table referencing SHIFT_CONSTRUCT_ID, providing additional assignment detail in the CRM scheduling stack.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for group-level data segregation.
• JTF_CAL_SHIFT_CONSTRUCTS self-reference — the FK list shows a SHIFT_ID reference with a placeholder foreign table, indicating internal or legacy linkage to verify at implementation.

These relationships confirm that JTF_CAL_SHIFT_CONSTRUCTS sits at the detail level of the Oracle CRM scheduling model, qualified by a single surrogate key and consumed primarily by task-assignment processing rather than by reference data lookups.