Search Results required_date_from




Overview
Explore in AI Chat

Overview

APPS.OTFV_OVERBOOKED_RESOURCES is a read-only Oracle E-Business Suite view that identifies non-consumable supplied resources which have been double-booked within overlapping booking windows. It is part of the Oracle Time and Labor / ETRM (Enterprise Resource and Time Management) resource scheduling schema, and its name follows the OTFV (Oracle Time and Labor Foundation View) naming convention used for reporting and inquiry objects in the APPS schema.

The view answers a specific operational question: which resources carry two or more concurrent resource bookings? This is essential for capacity conflict detection, scheduling exception reporting, and downstream integration into resource management dashboards. Because the object is defined WITH READ ONLY, it is intended strictly for querying and cannot be used as a DML target. Note that the view does not expose a required_date_to column itself; however, its logic is driven entirely by the required_date_from and required_date_to columns on OTA_RESOURCE_BOOKINGS, which is why users searching on "required_date_to" typically encounter this object.

Underlying Base Objects

The view is defined over the following documented base objects:

  • OTA_RESOURCE_BOOKINGS (SYNONYM) — The transaction table holding individual bookings, including required_date_from and required_date_to. Referenced three times in the definition (aliases trb, trb1, trb2) to perform self-joins and correlated subquery counts.
  • OTA_SUPPLIABLE_RESOURCES (SYNONYM) — The resource entity table, aliased tsr, supplying supplied_resource_id and the consumable_flag and business_group_id attributes.
  • OTA_SUPPLIABLE_RESOURCES_TL (SYNONYM) — The translation table, aliased tst, providing the resource display name filtered by USERENV('LANG').
  • OTA_GENERAL (PACKAGE) — Supplies the business group context via ota_general.get_business_group_id, used to restrict rows to the current operating business group.

The synonyms resolve to the corresponding OTA_ base tables owned by the Oracle Time and Labor product schema; the view itself resides in APPS and is granted accordingly.

Key Columns

  • RESOURCE_NAME — The translated name of the supplied resource, derived from OTA_SUPPLIABLE_RESOURCES_TL in the session language.
  • RESOURCE_BOOKING_ID — The identifier of the conflicting booking in OTA_RESOURCE_BOOKINGS.
  • SUPPLIED_RESOURCE_ID — The unique identifier of the overbooked resource.

Although required_date_from and required_date_to are not projected in the SELECT list, they are central to the WHERE predicate. The view returns a resource booking only when a correlated count of overlapping bookings equals or exceeds two, using the pattern TRUNC(trb.required_date_from) BETWEEN TRUNC(trb2.required_date_from) AND TRUNC(trb2.required_date_to) and the mirrored comparison for required_date_to. The UNION of two such blocks captures both directions of the overlap. Rows are restricted to consumable_flag = 'N' and to the active business group.

Common Use Cases and Queries

Typical uses include conflict exception reporting, pre-scheduling validation, and capacity analysis feeds to external planning tools.

List all overbooked resources in the current business group:

  • SELECT resource_name, supplied_resource_id FROM apps.otfv_overbooked_resources ORDER BY resource_name;

Identify the specific conflicting bookings for a given resource:

  • SELECT resource_name, resource_booking_id FROM apps.otfv_overbooked_resources WHERE supplied_resource_id = :p_resource_id;

Join back to OTA_RESOURCE_BOOKINGS to retrieve the actual required_date_from and required_date_to windows that caused the conflict:

  • SELECT o.resource_name, b.resource_booking_id, b.required_date_from, b.required_date_to FROM apps.otfv_overbooked_resources o, apps.ota_resource_bookings b WHERE o.resource_booking_id = b.resource_booking_id;

Because the view performs correlated subqueries with TRUNC on date columns, queries benefit from an index on supplied_resource_id and the date range columns of OTA_RESOURCE_BOOKINGS. Performance should be assessed before embedding the view in high-volume concurrent reporting.