Search Results msd_dem_debug_mode

Overview

MSD_DEM_QUERY_UTILITIES is an Oracle Application Object Library package owned by APPS and classified as an OTHER API within the Oracle E-Business Suite. Its name and specification place it within the MSD (Marketing/Sales Data) family of packages used by Oracle's Demand Planning and related supply chain planning products. The package serves as a central query-construction utility: rather than embedding SQL text throughout calling code, the various planning modules delegate to this package to build, execute, and manage the SQL statements needed to extract entity data for a given instance identifier. A prominent feature visible in the specification is the use of the profile option MSD_DEM_DEBUG_MODE, which is read at package initialization into the constant C_MSD_DEM_DEBUG. This profile acts as the debug toggle referenced by users searching for "msd_dem_debug_mode"; when set to 'Y' it enables diagnostic behavior in the query-building logic, while the default of 'N' suppresses it. The package therefore fulfils both a functional role (query generation for demand entities) and an operational role (troubleshooting via profile-controlled debugging).

Key Procedures and Functions

The ETRM metadata documents five procedures and functions, three of which are declared in the package specification excerpt.

• GET_QUERY — Builds and returns a query for a named entity and instance, optionally targeting a destination table and appending an additional WHERE clause.
• GET_QUERY2 — A variant of the query builder that accepts a key-values string and a flag, plus an optional view name, allowing callers to supply entity keys directly rather than relying solely on the instance identifier.
• GET_QUERY3 — The most parameterized variant, extending the pattern with key values, a custom view flag and name, a series type, and an optional PS view name. This supports customized or pre-built views over the base entity queries.
• EXECUTE_QUERY — Executes a query previously assembled by the GET_QUERY family, providing the run-time half of the build/execute split.
• TRUNCATE_TABLE — Performs a truncate against a target table, supporting staging or refresh patterns where query results are materialized before use.

All GET_QUERY variants return a retcode and the generated query text through OUT parameters; parameter lists are not reproduced here beyond what the specification documents.

Tables Accessed

The package references three tables through APPS synonyms. MSD_DEM_ENTITY_QUERIES and MSD_DEM_QUERIES together form the metadata repository from which query definitions are resolved — the entity name and instance supplied by the caller are matched against these setup tables to retrieve the appropriate SQL fragment. MSC_APPS_INSTANCES is the standard Applications instance table consulted to identify and validate the instance context for which the query is being built. No direct DML against these configuration tables is implied by the documented procedure names; they are read as sources for query construction, while TRUNCATE_TABLE operates on caller-directed staging or destination tables.

Usage Notes

MSD_DEM_QUERY_UTILITIES is an internal utility rather than an end-user-facing API, and is referenced by eleven other packages within the EBS codebase, indicating widespread internal reuse across demand planning flows. It is invoked programmatically from PL/SQL — most likely from concurrent programs and planning engine routines that need to extract entity data for a specific instance — rather than directly from a form. Because the package is classified as OTHER, it carries no formal public API guarantee, and the differing signatures of GET_QUERY, GET_QUERY2, and GET_QUERY3 suggest callers should select the variant matching their key-handling and view requirements. When diagnosing query-generation issues in 12.1.1 or 12.2.2, the MSD_DEM_DEBUG_MODE profile should be set to 'Y' to enable the diagnostics controlled by C_MSD_DEM_DEBUG, then reset to 'N' afterwards; because the value is captured at package initialization, a session or database restart may be required for a change to take effect.