Search Results csp_part_search_pvt




Overview
Explore in AI Chat

Overview

CSP_PART_SEARCH_PVT is a private PL/SQL package in the APPS schema that forms the core of Oracle E-Business Suite's spare-parts search and sourcing engine within the field service / depot repair (CSP) product family. The package exposes the search and evaluation logic used to identify which inventory organizations, sub-inventories, and dedicated locations can satisfy a requirement for a given part within a specified time window. In ETRM terms the package is classified as a Private API ("PVT"), meaning it is not a public, documented integration interface; it is intended for internal consumption by other EBS components rather than by customer-written code. It is a VALID object and is available in both 12.1.1 and 12.2.2. Its responsibilities include computing available quantities per source location, projecting arrival times based on shipping transit and time-zone differences, and evaluating cutoff dates relative to sourcing rules. The dependency listing confirms that the unit is heavily reused: it is referenced by CSM_EMAIL_QUERY_PKG, CSM_MULTI_MOBQRY_PKG, CSP_DC_PARTS_V, CSP_SCH_INT_PVT, CSP_SHIPPING_DETAILS_V, CS_CHARGE_DETAILS_PVT, and CSP_PART_SEARCH_PVT_W, and it itself depends only on the SYS STANDARD package — indicating a utility-grade unit with minimal external coupling.

Key Procedures and Functions

The ETRM metadata documents six callable program units:

  • SEARCH — the principal entry point. It performs the parts search across candidate sourcing locations, applying requirement and planning parameters to return matching, available supply options.
  • GET_AVAIL_QTY — returns the quantity of a part that is available at a given source, used to filter sources that cannot fully or partially meet demand.
  • GET_ARRIVAL_TIME — calculates the projected arrival date/time of a shipment from a source location to the destination ship-to, factoring transit time.
  • GET_SHIP_TO_TZ — resolves the time zone associated with the ship-to location, supporting accurate arrival-time computation across regions.
  • GET_SRC_DISTANCE — determines the distance between the source and destination, used as an input for transit-time and source-ranking calculations.
  • GET_CUTOFF_TIME — derives the cutoff date/time by which a source can still satisfy the requirement, based on scheduling and shipping windows.

These units cooperate to answer the central business question: which source, and by when, can deliver a required part. No public parameter lists are documented; callers should treat the signatures as internal and subject to change.

Tables Accessed

The package reads from a broad set of CSP configuration and transactional tables (via APPS synonyms), including CSP_REQUIREMENT_HEADERS, CSP_REQUIREMENT_LINES, and CSP_REQ_LINE_DETAILS for demand data; CSP_SEC_INVENTORIES, CSP_INV_LOC_ASSIGNMENTS, CSP_DEDICATED_SITES, and CSP_AVAILABLE_PARTS_TEMP for available supply; CSP_REQUIRED_PARTS_TEMP for work-set storage of results; CSP_EXCESS_LISTS and CSP_PLANNING_PARAMETERS for sourcing policy; and CAC_SR_OBJECT_CAPACITY for capacity constraints. Geographic elements rely on FND_TERRITORIES_TL, HZ_LOCATIONS, HZ_CUST_ACCT_SITES_ALL, and HZ_CUST_SITE_USES_ALL to resolve ship-to addresses, territories, and time zones. The heavy use of temporary tables (CSP_AVAILABLE_PARTS_TEMP, CSP_REQUIRED_PARTS_TEMP) indicates a multi-step, set-based search that stages intermediate results before final selection.

Usage Notes

CSP_PART_SEARCH_PVT is typically invoked indirectly. Its published callers span email/mobile query packages, shipping and charge-detail views, and scheduling integration, so it fires during field service parts searches, depot repair sourcing, and shipping/charge determination rather than from standalone forms or concurrent programs. Because it is a PVT API, direct invocation from custom code is discouraged; Oracle does not guarantee the stability of its signatures across patches or releases. Customizations should consume the public views (for example CSP_DC_PARTS_V or CSP_SHIPPING_DETAILS_V) that wrap this package, thereby insulating integrations from internal changes. Organizations relying on parts-search behavior in 12.1.1 and 12.2.2 should validate any functional dependency against Oracle Support guidance, since the utility depends on STANDARD only but is referenced by numerous higher-level components.