Search Results populate_result_table

Overview

MST_PQ_WORKS is a private PL/SQL package in the APPS schema that provides the underlying engine for the Oracle E-Business Suite personal query (PQ) framework supplied by the Transportation Management (previously Oracle Transportation/Shipping Execution) product family. It is declared AUTHID CURRENT_USER and is classified as OTHER in the ETRM repository, meaning it is a supporting internal package rather than a published open interface or public API. The package encapsulates the mechanics of building, refining, persisting, renaming and deleting personal queries and the result sets they generate, and it also owns the routine that submits the concurrent request that materializes a query's output. It is the object that gives the p_query_id parameter its meaning throughout the personal query subsystem: virtually every documented procedure takes a query identifier and acts on the row (or rows) that identifier selects in MST_PERSONAL_QUERIES.

Key Procedures and Functions

• POPULATE_RESULT_TABLE — The principal worker. It accepts a query identifier and a plan identifier together with the standard concurrent errbuf/retcode pair, and populates the personal query result table for the given query under the given plan. It is the entry point used when a query must be executed server-side.
• REMOVE_QUERY — Deletes a personal query identified by query id and query type.
• REMOVE_QRY_AND_RESULTS — Performs the same deletion but additionally purges the stored result rows associated with the query.
• RENAME_QUERY — Updates the descriptive attributes of a query (name, description and public flag) for a supplied query id.
• INSERT_LOAD_SELECTION, INSERT_CM_SELECTION, INSERT_ORDER_SELECTION, INSERT_EXCEP_SELECTION — Four parallel routines that persist selection criteria for the load, capacity/maintenance, order and exception query categories respectively, keyed by query id.
• SAVE_QUERY_RESULT — Writes the outcome of a query run into permanent storage for the supplied query id.
• CLEAR_TEMP_QUERY — Removes transient/temporary query data for a query id, typically after a run has completed or been abandoned.
• LAUNCH_REQUEST — A function returning a numeric request identifier; given a query id and plan id it submits the concurrent request that drives query execution and returns its request id to the caller.

Tables Accessed

The package reads and writes the personal query definition and result tables, MST_PERSONAL_QUERIES and MST_PERSONAL_QUERY_RESULTS, which hold the query header and its generated rows respectively. Selection criteria are stored in the category-specific criteria tables — MST_LOAD_SELECTION_CRITERIA, MST_CM_SELECTION_CRITERIA, MST_ORDER_SELECTION_CRITERIA and MST_EXCEP_SELECTION_CRITERIA — with MST_SELECTION and MST_SELECTION_CRITERIA providing the shared selection structures used across categories. PLITBLM is the standard EBS PL/SQL table-of-varchar2 index-by table type used for in-memory list handling inside the package. All access is performed through APPS synonyms.

Usage Notes

MST_PQ_WORKS is not intended for direct customer invocation; no other package in the documented repository references it, and it carries a noship revision header consistent with internal-only code. In practice it is invoked indirectly: Transportation Management query maintenance forms call procedures such as RENAME_QUERY, REMOVE_QUERY, REMOVE_QRY_AND_RESULTS and the INSERT_*_SELECTION routines as the user builds or edits a saved query, while LAUNCH_REQUEST submits the concurrent program whose executable calls POPULATE_RESULT_TABLE, SAVE_QUERY_RESULT and CLEAR_TEMP_QUERY to produce and retain results. Customizations that need to reproduce a query run should mimic this pattern — insert criteria through the appropriate INSERT_* routine, submit via LAUNCH_REQUEST, and read results from MST_PERSONAL_QUERY_RESULTS — rather than calling POPULATE_RESULT_TABLE directly. Because the package is AUTHID CURRENT_USER and touches only APPS-owned tables, grants must be managed explicitly for any custom schema that invokes it. As with all unsupported internal packages, signature changes between 12.1.1 and 12.2.2 are possible and dependent objects should be re-verified after patching.