Search Results update_items_in_star

Overview

ENI_ITEMS_STAR_PKG is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that supports the ETRM (Enterprise Item and Trading Repository / Product Information Management) star-schema extraction of item master data. Its central purpose is to populate and maintain a denormalized "item star" table (ENI_OLTP_ITEM_STAR) that flattens the highly normalized Oracle Inventory item structures — concatenated key flexfield segments, system items, and category assignments — into a form optimized for downstream reporting, analytics, and data-warehouse consumption. The package is declared AUTHID CURRENT_USER and is classified as an OTHER API, meaning it is intended primarily for internal scheduled processing rather than as a public, externally callable interface.

The business function is straightforward: whenever items are created, updated, deleted, or re-categorized in the operational (OLTP) inventory tables, or are loaded through the Item Open Interface, this package synchronizes those changes into the star table so that dimensional reporting reflects current item attributes and category hierarchies.

Key Procedures and Functions

The documented package exposes eight procedures. CREATE_STAR_TABLE is the entry point most commonly associated with the "create_star_table" search; it is a concurrent-program style procedure (using the standard errbuf/retcode OUT NOCOPY parameters) that establishes or rebuilds the underlying star-table structure prior to population. INSERT_ITEMS_IN_STAR inserts a new item row into the star table for a given inventory item and organization. DELETE_ITEMS_IN_STAR removes an item's star-table row when the item is deleted or becomes invalid. UPDATE_ITEMS_IN_STAR refreshes the concatenated flexfield segments for an existing item, capturing changes to the item key flexfield. UPDATE_CATEGORIES creates or changes a category assignment for a supplied category and structure. SYNC_CATEGORY_ASSIGNMENTS updates or deletes item-to-category assignment records so the star table stays aligned with MTL_ITEM_CATEGORIES. SYNC_STAR_ITEMS_FROM_IOI bulk-populates or refreshes the star table from the Item Open Interface using a set/process identifier. SYNC_STAR_ITEMCATG_FROM_COI performs the analogous synchronization of item-category assignments from the Category Open Interface. Several procedures follow the standard EBS API signature pattern (p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data).

Tables Accessed

The package reads and writes a defined set of Inventory and ETRM tables through APPS synonyms. Source data is drawn from MTL_SYSTEM_ITEMS_B and its concatenated-key view MTL_SYSTEM_ITEMS_KFV, MTL_ITEM_CATEGORIES, MTL_CATEGORY_SETS_B, MTL_DEFAULT_CATEGORY_SETS, and MTL_PARAMETERS. Open Interface staging is read from MTL_ITEM_CATEGORIES_INTERFACE. The ETRM-specific targets and staging objects are ENI_OLTP_ITEM_STAR (the star table), ENI_ITEM_STAR_TEMP, ENI_ITEM_STAR_VALID_ERR (error capture), and ENI_DENORM_HIERARCHIES (denormalized category hierarchies). These tables collectively supply the item attributes and hierarchy relationships that are flattened into the star.

Usage Notes

ENI_ITEMS_STAR_PKG is typically invoked by concurrent programs and internal ETRM processes rather than by end-user forms directly. CREATE_STAR_TABLE is best understood as a setup/rebuild step that runs before bulk population. The synchronization procedures are commonly driven from Item Open Interface and Category Open Interface processing, so that new or modified items flow automatically into the star table. The package is referenced by one other package, indicating it participates in a larger ETRM extraction chain. Customizations should avoid modifying the package directly, since it carries a standard Oracle header and is patched with ETRM releases; instead, callers should invoke the documented procedures and honor the standard return-status convention. Because of the AUTHID CURRENT_USER declaration, execution grants and synonym resolution must be verified whenever the package is called from a custom schema.