Search Results save_as

Overview

MSC_PERS_QUERIES is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that underpins the personal query and saved-search infrastructure within Oracle Advanced Supply Chain Planning (ASCP). In EBS 12.1.1 and 12.2.2, planners and supply chain users construct ad hoc queries against planning data — items, planned resources, exceptions, suppliers, shipments, orders, worklists, and selection criteria — and store those queries for later reuse. MSC_PERS_QUERIES provides the server-side logic that persists, copies, retrieves, and purges those saved query definitions and their associated result sets. The package is declared AUTHID CURRENT_USER, so it executes with the privileges of the invoking user, and it is referenced by three other packages in the planning schema, indicating it is a foundational rather than terminal component of the planning query framework.

Key Procedures and Functions

The package exposes eleven documented procedures and functions. The query-type constants declared at the top (item, resource, exception, supplier, shipment, order, worklist, criteria) and view-name constants (such as MSC_SYSTEM_ITEMS_SC_V and MSC_EXCEPTION_DETAILS_V) map each query category to its underlying data source.

• POPULATE_RESULT_TABLE — Populates the result table for a given query, using the query ID, query type, plan ID, and a supplied WHERE clause; supports a master query and sequence context.
• GET_USER, GET_QUERY_NAME, GET_QUERY_TYPE — Lookup functions that return the user name, the query name, and the query type respectively for a given query ID.
• POPULATE_CP_TEMP_TABLE — Populates the concurrent-processing temporary table for a query.
• PURGE_PLAN — Removes planning query data associated with a specified plan.
• UPDATE_CATEGORY — Updates the category of a saved query; returns error buffer and return code values.
• SAVE_AS — The procedure most relevant to the "save_as" search: creates a new personal query from an existing query ID, capturing the new name, description, and public flag. A corresponding function form of SAVE_AS is also declared.
• DELETE_QUERY — Deletes a saved query by ID, optionally validated against a query name.
• COPY_QUERY — Duplicates a query definition, supporting the copy pattern that complements Save As.

Tables Accessed

The package reads and writes several planning and foundation tables through APPS synonyms. MSC_PERSONAL_QUERIES and its _S sibling store the saved personal query headers, and MSC_FORM_QUERY holds form-level query definitions; these are the primary targets of SAVE_AS, DELETE_QUERY, and COPY_QUERY. MSC_QUERY and MSC_SELECTION_CRITERIA persist the query criteria, while MSC_PQ_RESULTS and MSC_PQ_TYPES hold result and type metadata that POPULATE_RESULT_TABLE writes. MSC_PLANS and MSC_WORKLIST_GROUPBY tie queries to plans and worklist groupings. MSC_ITEM_ATTRIBUTES, MSC_AMONG_VALUES, and the view constants (MSC_SYSTEM_ITEMS_SC_V, MSC_EXCEPTION_DETAILS_V, MSC_ITEM_SUPPLIER_V, MSC_PLANNED_RESOURCES_V, MSC_SHIPMENT_DETAILS_V, MSC_ORDERS_V) supply the actual planning content surfaced in results. FND_USER resolves user identity, and DUAL, ALL_TAB_PARTITIONS, and PLITBLM support utility operations and partition-aware processing.

Usage Notes

MSC_PERS_QUERIES is invoked primarily from ASCP planning forms and workbenches when users save, rename, copy, or delete personal queries, and when results are regenerated. The "Save As" action in the planning query interface calls the SAVE_AS procedure or function to clone an existing query under a new name, description, and sharing scope. POPULATE_RESULT_TABLE and POPULATE_CP_TEMP_TABLE are typically driven by form-level result population or concurrent program processing, while PURGE_PLAN and DELETE_QUERY are used for housekeeping. Custom integrations should call these APIs rather than writing to the underlying MSC_* tables directly, to preserve the integrity of the personal query framework and its dependencies on the three packages that reference this one.