Search Results jtf_calendar_pub




Overview
Explore in AI Chat

Overview

JTF_CALENDAR_PUB is a public PL/SQL package owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It provides the calendar and resource-availability engine used throughout the CRM and Field Service components of the E-Business Suite, particularly for the TeleService, Sales, and Resource Scheduler product families. The package exposes the business logic required to determine whether a resource (an employee, a service representative, a field engineer, or a group) is available at a given point in time, and to compute the next available time slot for scheduling purposes.

Because it is classified as a public (PUB) API in the ETRM metadata, JTF_CALENDAR_PUB is intended to be called directly by other application modules and by customer extensions. Packages such as AS_LEAD_ROUTING_WF, AS_SALES_LEAD_ASSIGN_PVT, CSF_RESOURCE_PUB, JTF_AGENDA_CALCULATIONS, JTF_ASSIGN_PUB, and JTF_CALENDAR_PUB_W depend on it, making it a foundational dependency for lead routing, task assignment, agenda calculation, and resource scheduling workflows. The package follows Oracle's API conventions by depending on FND_API, which supplies the standard return-status and message-handling framework.

Key Procedures and Functions

The documented package contains thirteen procedures and functions. Their names and described purposes are as follows:

  • IS_RES_AVAILABLE — Determines whether a specified resource is available for a given date and time, returning a Boolean or status indication based on the resource's calendar and exception rules.
  • GET_AVAILABLE_TIME — Returns the next available working time for a resource, taking into account shifts, exceptions, and existing assignments.
  • GET_AVAILABLE_SLOT — Identifies and returns an available scheduling slot for a resource within the applicable calendar constraints.
  • GET_RESOURCE_SHIFTS — Retrieves the shifts defined for a resource, reflecting the shift assignments and shift constructs configured for that resource.
  • GET_RES_SCHEDULE — Returns the working schedule of a resource over a specified period.
  • GET_G_FALSE — Utility function that returns the package-level constant representing a Boolean false value, used for consistent null handling.
  • GET_G_MISS_NUM — Utility function returning the package-defined sentinel value for a missing numeric input.
  • GET_G_MISS_CHAR — Utility function returning the package-defined sentinel value for a missing character input.
  • GET_G_MISS_DATE — Utility function returning the package-defined sentinel value for a missing date input.
  • GET_RES_TIMEZONE_ID — Returns the time zone identifier associated with a given resource, enabling correct conversion of calendar data across geographies.
  • RESOURCEDT_TO_SERVERDT — Converts a date/time expressed in a resource's local time zone into the server's time zone for consistent storage and comparison.
  • VALIDATE_CAL_DATE — Validates a supplied calendar date against the defined calendar, exceptions, and assignment rules.

Tables Accessed

The package accesses the following tables through APPS synonyms, directly reflecting the data model for CRM calendars: JTF_CALENDARS_B and JTF_CAL_EXCEPTION_ASSIGN, which store the base calendar definitions and resource-specific exception assignments; JTF_CAL_RESOURCE_ASSIGN, which maps calendars to resources; JTF_CAL_SHIFT_ASSIGN and JTF_CAL_SHIFT_CONSTRUCTS, which define shift assignments and the structure of working shifts; JTF_RS_GROUPS_B and JTF_RS_RESOURCE_EXTNS, which supply resource and group identity details; and JTF_TASKS_B, JTF_TASK_ASSIGNMENTS, and JTF_TASK_STATUSES_B, which link resource availability to existing tasks and assignments so that occupied time is excluded. DUAL and PLITBLM are referenced for standard SYS operations and PL/SQL table handling.

Usage Notes

JTF_CALENDAR_PUB is typically invoked by forms, concurrent programs, and custom PL/SQL code that need to evaluate resource availability before creating or assigning a task, lead, or service request. It is most commonly reached indirectly through higher-level APIs such as JTF_ASSIGN_PUB, which handles assignment logic, and AS_LEAD_ROUTING_WF, which routes sales leads. Custom developers integrating with the Resource Scheduler or TeleService module should call the public procedures of this package rather than querying the underlying JTF_CAL_* tables directly, because the package encapsulates the time zone conversion and exception-handling logic. When calling these procedures, developers should account for the missing-value sentinel functions (GET_G_MISS_NUM, GET_G_MISS_CHAR, GET_G_MISS_DATE) and the FND_API error-handling framework to ensure consistent error processing in E-Business Suite Release 12 environments.