Results for “msd_dem_query_utilities”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The APPS.MSD_DEM_QUERY_UTILITIES package body is a shared utility component within the Oracle EBS Application Technology stack, specifically associated with the Discrete Event Management (DEM) and Enterprise Command Center style query infrastructure that emerged from the Oracle Demantra and Manufacturing Execution System integration layer. In Oracle EBS 12.1.1 and 12.2.2, this package serves as a centralized query-handling engine that dynamically retrieves, constructs, and executes SQL definitions stored in the DEM query metadata tables. It abstracts the complexity of resolving entity-specific query definitions, allowing calling modules to obtain ready-to-run SQL statements without embedding query logic directly in their own code.

The package is classified as OTHER in the ETRM API classification scheme, meaning it is not a public, supported business API but rather an internal utility package intended for use by other EBS components. Its status is VALID, confirming it compiles cleanly in the documented environment.

Key Procedures and Functions

The package exposes five documented procedures and functions:

• GET_QUERY — The primary retrieval routine. It resolves and returns a query definition based on the caller's input criteria, drawing from the DEM query metadata tables.
• GET_QUERY2 — An overloaded or extended variant of GET_QUERY, providing an alternate resolution path, likely supporting additional filtering or a different query category.
• GET_QUERY3 — A further variant, extending the family of query retrieval routines to accommodate additional query sources or entity contexts.
• EXECUTE_QUERY — Executes a previously resolved query, typically returning the result set or a ref cursor to the caller. This is the runtime workhorse that turns stored query text into actionable data.
• TRUNCATE_TABLE — A maintenance utility that truncates a designated staging or result table. It is used to reset intermediate query output structures before a fresh execution cycle.

No parameter lists are documented in the source metadata; consumers should treat these signatures as internal and subject to change across patch levels.

Tables Accessed

The package references three application tables through APPS synonyms:

• MSC_APPS_INSTANCES — Provides the registered application instance context, ensuring queries execute against the correct EBS instance or data source.
• MSD_DEM_QUERIES — The core repository of stored query definitions, supplying the SQL text and metadata consumed by the GET_QUERY family.
• MSD_DEM_ENTITY_QUERIES — Maps queries to specific business entities, enabling entity-scoped query resolution.

Additionally, the package depends on MSD_DEM_COMMON_UTILITIES and MSD_DEM_QUERY_UTILITIES itself (recursive internal calls), plus the standard FND_PROFILE, FND_INSTALLATION, and SYS.STANDARD libraries.

Usage Notes

MSD_DEM_QUERY_UTILITIES is not referenced by any database object per the ETRM documentation, yet the 12.2.2 metadata indicates it is referenced by eleven other packages. This apparent discrepancy reflects the difference between static database dependencies and runtime invocation. In practice, the package is invoked by DEM-related concurrent programs, dashboard rendering logic, and custom extensions that require dynamic query generation. It is typically called programmatically rather than from a user-facing form. Developers extending DEM functionality should call the GET_QUERY routines to obtain SQL, then invoke EXECUTE_QUERY, using TRUNCATE_TABLE to reset staging structures between runs. Because it is an unsupported internal utility, direct customization is discouraged; upgrades may alter its behavior without notice.