Search Results collect_bom_data

Overview

MSD_DEM_COLLECT_BOM_DATA is an Oracle Application Object Library PL/SQL package owned by the APPS schema and classified in the ETRM repository as an "OTHER" API type. It belongs to the MSD (Manufacturing Source Data) product family used by Oracle's Demantra demand management integration. Its principal purpose is to gather bill-of-material (BOM) structure data from the Oracle E-Business Suite planning and manufacturing tables and stage that information into the Demantra-specific collection tables, where it can be consumed by the Demantra BOM collection and integration processes.

The package declares AUTHID CURRENT_USER, so all SQL statements execute under the privileges of the calling schema rather than the package owner. It also defines several custom PL/SQL types — NUMBERLIST, DATELIST, VARCHAR2LIST, and record/table types PARENT_TYPE and PARENTS — used internally to hold item, planning-factor, quantity-per, and disable-date values during processing. Two global constants, G_YES and G_NO, are initialized from MSD_DEM_COMMON_UTILITIES.

Key Procedures and Functions

The package exposes a single documented procedure: COLLECT_BOM_DATA. Its signature includes an error buffer output parameter (errbuf), a return code output parameter (retcode), and an input parameter p_sr_instance_id that identifies the source instance for which BOM data is collected. This conforms to the standard concurrent program parameter convention in Oracle EBS, allowing the procedure to be registered and executed as a concurrent request from the Application Object Library or run directly from PL/SQL, with its success/failure status reported through the standard errbuf/retcode pair.

No public functions are documented for this package; all other declared elements are internal type definitions and global constants used to support COLLECT_BOM_DATA. The package header shows a revision check-in comment from 2009, indicating this interface is a relatively old, stable component of the Demantra integration layer.

Tables Accessed

The ETRM metadata lists the following tables referenced through APPS synonyms:

• MSC_BOMS — the advanced planning BOM header table in the MSC schema, providing BOM identifiers and parent item information.
• MSC_BOM_COMPONENTS — the corresponding component/child item table, supplying component item IDs, quantities per assembly, and effectivity information.
• MSC_SYSTEM_ITEMS — used to resolve item attributes such as item identifiers, planning factors, and organization context for both parents and components.
• MSD_DEM_BOM_COMPONENTS — the Demantra-specific target table into which collected BOM component rows are written or staged.
• PLITBLM — a numeric temporary table type commonly used by Oracle planning collections to pass ID lists (e.g., IN lists) between collection logic and downstream procedures.

Collectively these tables support reading the planning BOM structure, resolving item details, and populating the Demantra staging structures that the demand management collection uses.

Usage Notes

MSD_DEM_COLLECT_BOM_DATA is typically invoked from the Demantra/BOM data collection concurrent program or from custom integration scripts executed during the planning collection cycle. Because the entry point passes errbuf and retcode, it is suitable for registration as a concurrent program in EBS 12.1.1 and 12.2.2; the p_sr_instance_id parameter ties the collection to a specific source instance identifier. The package does not appear to be referenced by any other documented package, so dependencies run from the package outward to the MSC and MSD_DEM tables rather than inward from other PL/SQL modules. When troubleshooting, verify that the caller has select privileges on the MSC tables and that the MSD_DEM_BOM_COMPONENTS and PLITBLM tables are properly initialized, since missing staging data or instance identifiers will cause collection to return an error status through retcode.