Results for “jtf_rs_res_availability”

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

Overview

JTF_RS_RES_AVAILABILITY is a CRM Foundation (JTF) table in the Oracle E-Business Suite that stores resource availability records, specifically those resources that are marked as unavailable for a defined period. It sits within the Resource Manager schema area and supports the scheduling, calendar, and assignment logic used by CRM applications such as TeleSales, Sales, Service, and Field Service. When a resource — an employee, a partner contact, an assignee, or a group member — is placed on leave, assigned to a non-working shift, or otherwise blocked from receiving work, a row is created here to record the exception.

The single documented foreign key, SECURITY_GROUP_ID referencing FND_SECURITY_GROUPS, indicates the table is secured by Multi-Org / security group semantics. Under the heuristic Data Vault classification mined from the FK structure, the table is modeled as standalone. In Data Vault terms a hub-and-satellite decomposition is not indicated (only a security group reference exists, not a business relationship to resources), so JTF_RS_RES_AVAILABILITY is best treated as an independent satellite-style record keyed on its own identifier rather than as a link. The physical schema documented for 12.2.2 lists 30 columns; the structure is stable across 12.1.1 and 12.2.2.

Key Information Stored

The table is anchored by the surrogate primary key column AVAILABILITY_ID, enforced by the constraint JTF_RS_RES_AVAILABILITY_PK. A separate unique index, JTF_RS_RES_AVAILABLILITY_U1 (note the documented spelling), is also defined on AVAILABILITY_ID, making that column both the primary key and the sole documented business-key candidate. The most significant columns are:

• AVAILABILITY_ID — surrogate primary key and unique business key for each availability row.
• RESOURCE_ID — identifies the resource whose availability is being recorded; the primary join key to resource tables.
• AVAILABLE_FLAG — flag indicating whether the resource is available or unavailable for the period.
• REASON_CODE — lookup code describing the reason for the availability status (for example, leave or training).
• START_DATE and END_DATE — the effective window during which the availability status applies.
• MODE_OF_AVAILABILITY — classification of the availability mode used by scheduling logic.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS controlling row-level access.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/BC4J framework.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns for customer-specific extensions.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns present on EBS transactional tables.

Common Use Cases and Queries

Typical usage centers on resource scheduling and calendar validation: determining whether a resource can be assigned to a task on a given date, building availability calendars, and reporting on planned absences. A common pattern joins the table to resource masters by RESOURCE_ID and filters on the effective date range:

• Find all unavailable resources for a date: SELECT RESOURCE_ID, START_DATE, END_DATE, REASON_CODE FROM JTF_RS_RES_AVAILABILITY WHERE TRUNC(SYSDATE) BETWEEN START_DATE AND END_DATE AND AVAILABLE_FLAG = 'N'.
• Availability check for a specific resource: filter on RESOURCE_ID, AVAILABLE_FLAG, and an overlapping START_DATE/END_DATE window.
• Calendar and dashboard reporting by REASON_CODE or MODE_OF_AVAILABILITY across a team or organization.
• Extract custom DFF values via ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 for downstream reporting.
• Security-scoped queries that join SECURITY_GROUP_ID to FND_SECURITY_GROUPS.

Related Objects

The following objects are the most significant references and dependencies for this table:

• FND_SECURITY_GROUPS — joined on JTF_RS_RES_AVAILABILITY.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID (the only documented foreign key).
• JTF_RS_RESOURCE_EXTNS / JTF_RS_RESOURCE_TL — resource master tables, joined on RESOURCE_ID, providing the resource name and attributes.
• JTF_RS_RESOURCE_CALENDARS — combined with this table to derive effective working time.
• JTF_RS_ASSIGNMENTS — assignment records validated against availability.
• JTF_RS_GROUPS / JTF_RS_GROUP_MEMBERS — group membership context for resources.
• JTF_RS_RES_AVAILABILITY_PK — the primary key constraint on AVAILABILITY_ID.