Search Results insert_query_row

Overview

WMS_SAVE_QUERY_PVT is a private (PVT classification) PL/SQL package owned by the APPS schema in Oracle E-Business Suite. It provides the low-level persistence layer for the saved query functionality used by Oracle Warehouse Management (WMS) and related mobile supply chain applications. In WMS and Mobile Supply Chain Application (MSCA) screens, users frequently define and reuse personalized queries — sets of field-level filter criteria that determine which records are retrieved for a given business transaction or inquiry page. The package encapsulates the DML operations required to insert, update, and delete those saved query definitions against the underlying WMS_SAVED_QUERIES table.

The package is declared with AUTHID CURRENT_USER, meaning its SQL executes under the privileges of the calling user rather than the definer, so callers must themselves hold appropriate privileges on the referenced objects (typically granted through APPS synonyms). The header carries the source control tag WMSVSQRS.pls 115.2, indicating its origin as a WMS saved-query definitions script.

Key Procedures and Functions

The package exposes a public record type and collection (save_query_record and save_query_table) mirroring the column structure of WMS_SAVED_QUERIES, plus the following documented procedures:

• INSERT_QUERY_ROW — Creates a new saved query definition for a given query type, query name, organization, and user. It accepts a PL/SQL table of save_query_record elements so that the multiple field/value rows that compose a saved query can be persisted in a single call, and returns a status indicator.
• UPDATE_QUERY_ROW (table form) — Updates an existing saved query for a given query type, query name, organization, and user, again accepting the collection of field rows. Used when a user modifies and re-saves an existing named query.
• UPDATE_QUERY_ROW (single-field form) — An overload that updates one field's value and type within a saved query, keyed by query type, query name, field name, organization, and user. This supports targeted edits without rewriting the entire query row set.
• DELETE_QUERY_ROW — Removes a specific field row from a saved query definition, identified by query type, query name, field name, organization, and user.
• DELETE_QUERY — Removes an entire saved query definition. This is the procedure most directly associated with user requests to discard a previously saved query. Because it is a private API, it is invoked only through WMS application code that has already validated the caller's authority over the named query and organization.

All procedures return an x_return_status OUT parameter following the standard EBS API convention, allowing the calling layer to detect success or failure and present appropriate messages.

Tables Accessed

• WMS_SAVED_QUERIES — The primary table. It stores one row per field-level criterion comprising a saved query, including query type, query name, field name, field type, field value, organization_id, user_id, and audit columns. All insert, update, and delete operations in this package target this table.
• PLITBLM — The standard EBS PL/SQL table-to-database bulk DML utility (a view over a global temporary table) used to convert the incoming PL/SQL collection into a form that can be processed with bulk operations inside the package.

Usage Notes

Because WMS_SAVE_QUERY_PVT is classified as a private package, it is not intended as a supported public integration point. It is called internally by WMS/Oracle Mobile Supply Chain Application forms and framework code — for example, the saved-query dialog or query maintenance windows in MSCA pages — when a user saves, edits, or deletes a named query, including the delete-query action the user searched on. ETRM records that no other documented packages reference it, underscoring its role as an internal utility layer rather than a shared business API.

Customizations that need equivalent behavior should ideally operate on WMS_SAVED_QUERIES through supported means or replicate the pattern carefully, because the private API's signature and behavior are not guaranteed across point releases or patches. Where direct invocation is unavoidable, callers must supply the organization and user identifiers explicitly, honor the return status contract, and ensure they possess the DML privileges required by the AUTHID CURRENT_USER declaration.