Search Results del_condition




Overview
Explore in AI Chat

Overview

The APPS.WSH_ITM_QUERY_CUSTOM package is a component of the Oracle E-Business Suite Warehouse Management (WSH) module, specifically supporting the Item Query and Customization framework used during shipping, delivery validation, and trade compliance screening. Its primary business function is to allow organizations to define, edit, and manage dynamic query conditions that are evaluated at runtime against item and party data. This enables extensible, configuration-driven filtering of items or trading partners without requiring modifications to core Oracle shipping logic. The package is classified as an "OTHER" API in the ETRM documentation, indicating it is an internal utility package rather than a public, supported interface.

The package resides in the APPS schema and holds a VALID status. It is tightly integrated with other WSH customization and screening packages, most notably WSH_ITM_CUSTOMIZE, WSH_ITM_ITEM_SYNC, WSH_ITM_PARTY_SYNC, WSH_ITM_EXPORT_SCREENING, and WSH_DELIVERY_VALIDATIONS. These relationships confirm that the package plays a supporting role in the broader item query customization and compliance screening infrastructure of Oracle Shipping Execution.

Key Procedures and Functions

The ETRM documentation lists five documented procedures and functions within this package. These are:

  • ADD_CONDITION — Adds a new condition entry to a query definition stored within the customization framework. It is used to extend the set of criteria applied when an item or party query is executed.
  • DEL_CONDITION — Removes an existing condition from a query definition. This supports maintenance and cleanup of configured query criteria.
  • EDIT_CONDITION — Modifies the attributes of an existing condition, allowing administrators to update operators, values, or logical connectors without deleting and recreating the condition.
  • BIND_VALUES — Binds runtime values to the dynamic SQL generated from the configured conditions. This is essential for safe, parameterized execution of queries built dynamically.

The documentation notes a fifth entry, reflected in the metadata as part of the package's callable surface. As with the others, no parameter lists are published, and the procedure names above should be understood by purpose rather than signature. Together these routines form the CRUD and execution layer for custom query conditions.

Tables Accessed

The ETRM metadata identifies two tables referenced via APPS synonyms:

  • DBMS_SQL — This is not a data table but the Oracle-supplied built-in package used to parse, bind, and execute dynamically constructed SQL statements. Its inclusion confirms that WSH_ITM_QUERY_CUSTOM builds and runs dynamic SQL at runtime, which explains the need for a dedicated BIND_VALUES procedure to safely bind parameters.
  • PLITBLM — A PL/SQL associative array or table type synonym commonly used within Oracle EBS for handling collections of values. It is likely employed here to pass arrays of conditions or bind values into and out of the dynamic SQL execution logic.

No application data tables are directly documented as being read or written by this package. Persistent condition storage is presumably managed by the calling customization packages, with WSH_ITM_QUERY_CUSTOM focusing on the dynamic query construction and execution.

Usage Notes

WSH_ITM_QUERY_CUSTOM is not intended for direct invocation by end users or external applications. The ETRM dependency data shows it is referenced by five other packages — WSH_DELIVERY_VALIDATIONS, WSH_ITM_CUSTOMIZE (referenced twice), WSH_ITM_EXPORT_SCREENING, WSH_ITM_ITEM_SYNC, WSH_ITM_PARTY_SYNC, and itself — confirming that it is an internal service layer consumed by the item customization and screening framework.

Typical invocation occurs when an Oracle Shipping Execution process evaluates configured item query rules during delivery validation, item synchronization, party synchronization, or export/trade compliance screening. The dependent packages call the condition-management routines (ADD, DEL, EDIT) to maintain rule definitions and then rely on BIND_VALUES to execute the resulting dynamic query through DBMS_SQL. Customizations should avoid calling this package directly, as it is an unsupported internal API. Instead, extensions should leverage the documented WSH_ITM_CUSTOMIZE interfaces, which provide the supported entry points to this underlying query engine. Because the package generates dynamic SQL, its behavior is sensitive to the correctness of the condition values supplied by callers, making robust validation at the caller level essential.