Search Results get_lookup_value

Overview

The APPS.MSD_DEM_COLLECT_PRICE_LISTS package body is part of the Oracle Demand Planning / Demantra integration layer within Oracle E-Business Suite. Its principal business function is to extract price list definitions and related metadata from EBS transactional tables and stage them in the Demantra integration schema so that downstream Demand Planning and Advanced Supply Chain Planning processes can consume current pricing structures. The package encapsulates the logic required to resolve the physical schema where Demantra staging tables reside, to locate the correct source table and column for each price list element, and to populate the intermediate price list tables. It also provides a cleanup routine for removing previously staged records. The package header comment indicates it has been shipping as part of the EBS codebase since at least the 12.1.x release, with the highest register listed as 120.5.12010000.2, dated 2008.

Key Procedures and Functions

• COLLECT_PRICE_LISTS — The primary documented procedure. It reads price list metadata from the integration configuration tables, determines the applicable source table and column for the requested price list code, constructs and executes dynamic SQL to derive the series identifier, and writes the collected price list data into the Demantra staging area. It is the main entry point used by the collection process to refresh price list information on a scheduled basis.
• DELETE_PRICE_LISTS — The companion cleanup procedure. It removes previously staged price list rows, typically invoked before a fresh collection cycle or when a price list has been deleted or redefined in EBS and the stale Demantra data must be purged to avoid duplicate or orphaned records.
• get_lookup_value — A private helper function, referenced in the package body source excerpt. It resolves a meaningful value from FND_LOOKUP_VALUES using a lookup type and lookup code, restricting the query to the US language. When the lookup type is MSD_DEM_TABLES, it prefixes the returned table name with the schema returned by the MSD_DEM_SCHEMA profile option, producing a fully qualified object name.

Tables Accessed

• FND_LOOKUP_VALUES — Foreign-key style reference data source for resolving lookup codes into display meanings, including the mapping of logical entity names to physical table names.
• MSD_DEM_ENTITIES_INUSE — Metadata registry that maps EBS entities and Demantra entities (for example, PRL and DISPLAY_UNIT) to the physical table name and column name used during collection.
• MSD_DEM_PRICE_LISTS — The Demantra staging table into which collected price list records are written and from which they are removed by the delete routine.
• MSC_APPS_INSTANCES — Instance and application registration data, used to validate or scope the collection to the correct EBS instance.
• DUAL — Used solely to evaluate the FND_PROFILE.VALUE('MSD_DEM_SCHEMA') call that resolves the Demantra staging schema name.

Usage Notes

The package is invoked through the standard Demantra collection framework in EBS 12.1.1 and 12.2.2, typically by way of the concurrent programs that orchestrate price list and master data collection for Demand Planning. It is not exposed as a user-facing form function; instead, it is called programmatically by the collection driver and, where appropriate, by custom integration code that needs to trigger or re-run price list staging. Because the package depends on the MSD_DEM_SCHEMA profile option being correctly set for the active application instance, administrators must verify that profile configuration before scheduling collection. Deployments should also confirm that the relevant lookup values under the MSD_DEM_TABLES lookup type exist, since the helper function relies on them to resolve physical table names. The package is referenced by no other documented packages, so its invocation surface is limited to the collection programs and any bespoke code that explicitly calls it.