Results for “customer_item_number”

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

Overview

CLN_ITEMMST_DELETED_ITEMS is a table owned by the CLN schema, the Supply Chain Trading Connector for RosettaNet in Oracle E-Business Suite. The object is documented as an archived item information store intended for temporary internal storage. In practice, the table acts as a staging and retention area for item master records that have been removed, superseded, or otherwise displaced from the active item definition during RosettaNet trading partner synchronization. RosettaNet message processing relies heavily on the item master as the anchor for partner-specific item identifiers, so maintaining a recoverable snapshot of deleted or replaced item records protects message processing from orphaned references and supports reconciliation when partners resend or renegotiate item definitions.

The table is classified as VALID in the ETRM 12.2.2 dictionary and consists of six documented columns. From a heuristic Data Vault modeling perspective, the documented structure suggests a satellite classification, since the table stores descriptive attributes that qualify the item entity rather than a standalone hub or a link between two business entities. This is offered as a modeling suggestion only; the object is not formally registered as a Data Vault construct, and its FK structure is characterized as standalone.

Key Information Stored

The primary key of the table is CLN_ITEMMST_DELETED_ITEMS_PK, defined on the INVENTORY_ITEM_ID column. A separate unique index, CLN_ITEMMST_DELETED_ITEMS_U1, covers ORGANIZATION_ID, INVENTORY_ITEM_ID, and CUSTOMER_ITEM_NUMBER and serves as a business-key candidate for the archived records. The most significant columns and their contents are:

• INVENTORY_ITEM_ID — Surrogate primary key and the internal Oracle identifier of the item whose record is being archived.
• ORGANIZATION_ID — The inventory organization context under which the item was defined; part of the unique business key.
• CUSTOMER_ITEM_NUMBER — The trading partner's identifier for the item; part of the unique business key and central to RosettaNet Partner Interface Process messaging.
• CONCATENATED_SEGMENTS — The concatenated flexfield representation of the item number, preserving the human-readable item identifier.
• ITEM_TYPE — The classification of the archived item, distinguishing standard items from other item master types.
• PRIMARY_UOM_CODE — The primary unit of measure associated with the item at the time of archival.

Common Use Cases and Queries

Typical scenarios include auditing which items were removed from a given organization, restoring item-to-partner mappings after an erroneous delete, and diagnosing RosettaNet inbound failures traced to item numbers that no longer exist. Reporting on deletion activity by organization or trading partner is also common. Representative query patterns include:

• Retrieve deleted items for an organization: SELECT inventory_item_id, concatenated_segments, customer_item_number FROM cln.cln_itemmst_deleted_items WHERE organization_id = :org_id;
• Locate a partner mapping by customer item number: SELECT * FROM cln.cln_itemmst_deleted_items WHERE customer_item_number = :partner_item;
• Reconcile archived records against the live master: SELECT d.inventory_item_id FROM cln.cln_itemmst_deleted_items d WHERE NOT EXISTS (SELECT 1 FROM mtl_system_items_b m WHERE m.inventory_item_id = d.inventory_item_id AND m.organization_id = d.organization_id);

Related Objects

Because the object is characterized as standalone, no foreign key relationships are documented. The following related objects are the most significant for context and joining:

• MTL_SYSTEM_ITEMS_B — joins on INVENTORY_ITEM_ID and ORGANIZATION_ID to compare archived versus active item master records.
• MTL_SYSTEM_ITEMS_TL — provides translated item descriptions for the referenced item.
• MTL_ITEM_REVISIONS_B — supplies revision context for items that were archived.
• CLN_ITEMMST_PROCESSED_ITEMS — the companion processing table for item master intake from RosettaNet partners.
• CLN_ITEM_CROSS_REF — holds partner cross-reference mappings that may point to archived items.
• EGP_SYSTEM_ITEMS_B — the product-data-hub view of the item master, useful where items originate from PIM.
• INV_ORGANIZATION_DEFINITIONS — resolves ORGANIZATION_ID to the inventory organization name for reporting.

Administrators should treat the table as temporary storage and establish a purge policy consistent with RosettaNet reconciliation requirements, since unbounded growth of archived item records can affect connector performance.