Search Results get_arrival_time




Overview
Explore in AI Chat

Overview

CSP_PART_SEARCH_PVT is the private implementation package body behind Oracle E-Business Suite's Service Parts Search functionality, part of the Channel Revenue Management / Service (CSP) product family. Its principal responsibility is to identify which inventory organizations and subinventories can satisfy a set of required parts, and then to rank and filter those candidate sources according to availability, geographic proximity, transit time, and shipping constraints. The package resolves the complex business question of "where can we source these required items, and when will they arrive?" by correlating required-parts demand with currently available supply.

The header comment identifies the source as cspvsrcb.pls version 120.48, last updated in 2013, indicating the package is a mature, actively patched component. It is classified as a PVT (private) API, meaning it is not intended for direct customer invocation but is called by the public CSP search APIs and concurrent programs. The package declares two package-level globals for return status and message data, consistent with the standard EBS PL/SQL API error-handling pattern.

Key Procedures and Functions

The package exposes six documented procedures and functions, all oriented around resolving source, quantity, and timing for a parts search:

  • SEARCH — the primary entry point. It accepts a table of required parts and a set of search parameters, and returns status, message data, and message count. It orchestrates the overall matching of demand to available supply, applying the configured search method (defaulted from the CSP_PART_SEARCH_METHOD profile option).
  • GET_AVAIL_QTY — determines the quantity of a part available at a given source, used to qualify candidate organizations and subinventories.
  • GET_ARRIVAL_TIME — computes the projected arrival time for a supply source, incorporating transit and distance logic. This is the object the user searched for, and it is central to time-based source ranking.
  • GET_SHIP_TO_TZ — resolves the ship-to time zone, ensuring arrival calculations are expressed in the correct local time.
  • GET_SRC_DISTANCE — calculates the geographic distance between a source and destination, leveraging Oracle Spatial (SDO_GEOMETRY) types.
  • GET_CUTOFF_TIME — determines the order cutoff time for a source, which constrains whether a shipment can be dispatched within the current cycle.

The SEARCH procedure additionally contains an internal ship_set procedure that drives a cursor over CSP_AVAILABLE_PARTS_TEMP and CSP_REQUIRED_PARTS_TEMP, comparing supplied versus required quantities to satisfy complete part sets. The package uses FND_LOG for statement-level instrumentation and the SERVER_TIMEZONE_ID profile for time normalization.

Tables Accessed

The package reads and writes a set of temporary and configuration tables. The temp tables CSP_AVAILABLE_PARTS_TEMP and CSP_REQUIRED_PARTS_TEMP hold the working supply and demand sets, joined on inventory item and required item. Configuration and geography are drawn from CSP_PLANNING_PARAMETERS, CSP_DEDICATED_SITES, CSP_INV_LOC_ASSIGNMENTS, and CSP_SEC_INVENTORIES, which define sourcing rules and eligible locations. Requirement context comes from CSP_REQUIREMENT_HEADERS, CSP_REQUIREMENT_LINES, and CSP_REQ_LINE_DETAILS. Excess inventory is considered via CSP_EXCESS_LISTS, and capacity via CAC_SR_OBJECT_CAPACITY. Customer and location geography is resolved through HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_LOCATIONS, and FND_TERRITORIES_TL for territory and distance calculations.

Usage Notes

As a PVT package, CSP_PART_SEARCH_PVT is invoked indirectly. It is referenced by seven other packages within the CSP module, which wrap its logic into public APIs and UI flows. It is typically triggered from Oracle Forms-based parts search pages and from concurrent programs that pre-populate the temporary tables before calling SEARCH. Developers should not call it directly; instead they should use the documented public CSP search APIs. Debug output is available via FND_LOG at the csp.plsql.csp_part_search_pvt namespace when the log level permits statement logging.