Search Results resource_consumable

Overview

OTFV_RESOURCES is a business view template owned by the APPS schema in Oracle E-Business Suite, belonging to the OTA (Learning Management) product family. It is generated as part of the flexfield view framework, in which a business view template supplies the descriptive column aliasing and joins required to present a user-facing, flexfield-driven representation of underlying supply and resource data. In 12.1.1 and 12.2.2 the object retains a VALID status and continues to be documented in the ETRM repository.

The view describes suppliable training resources: the resources an organization or supplier can provide for a class, event, or scheduled offering, together with their cost, capacity, and validity period. It exposes the resource start and end dates (mapped to RESOURCE_START_DATE and RESOURCE_END_DATE, the latter derived from TSR.END_DATE), making it the principal reference for reporting on resource availability windows. A resource search on "resource_end_date" therefore resolves to the column RESOURCE_END_DATE within this view.

Underlying Base Objects

OTFV_RESOURCES is defined over the following documented objects:

• OTA_SUPPLIABLE_RESOURCES (TSR) — the driving table holding the actual suppliable resource records.
• HR_ALL_ORGANIZATION_UNITS_TL (BGRT) — supplies the business group name, joined on BUSINESS_GROUP_ID with the session language filter.
• PO_VENDORS (VEN) — outer-joined to provide the supplier (vendor) name.
• OTA_RESOURCE_DEFINITIONS (RES) — the resource definition/master record, joined on RESOURCE_DEFINITION_ID.
• OTA_SUPPLIABLE_RESOURCES_TL, HR_BIS, and OTA_GENERAL — supporting translation and utility packages (lookup decoding, currency naming, and business group retrieval).

The view is created WITH READ ONLY, and its business group scope is restricted via OTA_GENERAL.GET_BUSINESS_GROUP_ID, matching the session's operating unit context. The literal column selectors "_KF:OTA:RES:RES" and "_DF:OTA:OTA_SUPPLIABLE_RESOURCES:TSR" identify the key and descriptive flexfield definitions applied to the underlying resource entity.

Key Columns

• BUSINESS_GROUP_NAME / BUSINESS_GROUP_ID — the owning business group.
• RESOURCE_TYPE — decoded via BIS_DECODE_LOOKUP from the RESOURCE_TYPE lookup.
• RESOURCE_NAME, SUPPLIABLE_RESOURCE_COMMENTS, RESOURCE_ADDRESS — descriptive attributes of the suppliable resource.
• COST, COST_UNIT, CURRENCY — pricing information, with CURRENCY derived from FND_CURRENCY_NAME.
• RESOURCE_CAPACITY — the capacity associated with the resource.
• RESOURCE_START_DATE / RESOURCE_END_DATE — the effective validity window of the resource; RESOURCE_END_DATE is the column most commonly queried for expiration and availability reporting.
• RESOURCE_CONSUMABLE — YES/NO decoded consumable indicator.
• SUPPLIER_LEAD_TIME, SUPPLIER_REFERENCE, SUPPLIER_NAME, SUPPLIER_ID — supplier (vendor) details.
• RESOURCE_DEFINITION_ID, SUPPLIED_RESOURCE_ID — primary linkage keys to the definition and held resource records.

Common Use Cases and Queries

The view is most often used to report on resource validity, cost, and supplier readiness. A typical query to identify resources expiring within a range is:

SELECT resource_name, supplier_name, resource_start_date, resource_end_date
FROM   apps.otfv_resources
WHERE  resource_end_date BETWEEN SYSDATE AND SYSDATE + 30
ORDER BY resource_end_date;

A query to list supplier-provided resources for a business group:

SELECT business_group_name, resource_type, resource_name,
       cost, cost_unit, currency, resource_end_date
FROM   apps.otfv_resources
WHERE  business_group_name = :p_bg
AND    supplier_name IS NOT NULL;

Because the view is read-only and business-group restricted, it is safe for operational reports and can be used directly in BI Publisher and Discoverer data models without risk of DML. It is also referenced in integration scenarios where resource availability must be validated against the RESOURCE_START_DATE and RESOURCE_END_DATE window before scheduling.