Search Results cln_itemmst_deleted_items_u1

Overview

CLN.CLN_ITEMMST_DELETED_ITEMS is a technical staging table in the Oracle E-Business Suite CLN (Oracle Configurator / Advanced Product Catalog) schema. Per its documented description, it holds "Archived Item Information, for temporary internal storage." It is not a transactional or master-data table; rather, it functions as an interface-layer holding area into which item master records are copied before they are purged, reorganized, or otherwise removed from the active item master. Its presence in the APPS_TS_INTERFACE tablespace reinforces this role: the table is designed for transient bulk operations rather than steady-state OLTP workloads (PCT Free 10).

From a Data Vault modeling perspective, the heuristic classification for this object is standalone. It has no inbound or outbound foreign key dependencies documented, and it does not reference any other database object. Consequently, it is best treated as a self-contained archival satellite of item master state at a point in time, rather than as a hub or link participating in a broader integration model. The primary key CLN_ITEMMST_DELETED_ITEMS_PK is defined on INVENTORY_ITEM_ID alone, while CLN_ITEMMST_DELETED_ITEMS_U1 enforces uniqueness across ORGANIZATION_ID, INVENTORY_ITEM_ID, and CUSTOMER_ITEM_NUMBER.

Key Information Stored

The table stores six documented columns. The most significant are:

• INVENTORY_ITEM_ID (NUMBER) — the surrogate primary key of the archived item record, matching the corresponding identifier in the active inventory item master.
• ORGANIZATION_ID (NUMBER) — the inventory organization to which the item belonged. This is the first column of the U1 unique index and the most common filtering attribute for multi-org queries.
• CUSTOMER_ITEM_NUMBER (VARCHAR2 100) — the customer-specific cross-reference for the item, and the third component of the U1 business key. This supports customer-facing item cross-reference and cleansing logic that is characteristic of the CLN schema.
• CONCATENATED_SEGMENTS (VARCHAR2 100) — the full concatenated item number as it appeared in the item master flexfield.
• ITEM_TYPE (VARCHAR2 100) — the item type classification at time of archival.
• PRIMARY_UOM_CODE (VARCHAR2 100) — the primary unit of measure for the archived item.

The distinction between the PK (INVENTORY_ITEM_ID) and the U1 business key (ORGANIZATION_ID, INVENTORY_ITEM_ID, CUSTOMER_ITEM_NUMBER) is important: INVENTORY_ITEM_ID alone uniquely identifies a row, but the natural business keys are the organization plus customer item combination, reflecting the multi-org, multi-customer nature of the CLN data model.

Common Use Cases and Queries

Because the object is an interface/archival table, typical usage centers on reconciliation, audit, and restore. A query pattern for confirming whether an item was archived before deletion:

• SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, CONCATENATED_SEGMENTS, ITEM_TYPE, PRIMARY_UOM_CODE, CUSTOMER_ITEM_NUMBER FROM CLN.CLN_ITEMMST_DELETED_ITEMS;
• Join back to MTL_SYSTEM_ITEMS_B on ORGANIZATION_ID and INVENTORY_ITEM_ID to determine which archived items still exist in the active master.
• Validate that U1 uniqueness is not violated before re-importing records into the item interface.
• Report on deleted items by organization, item type, or customer item number for audit trails.

Because the table is in APPS_TS_INTERFACE and described as "temporary internal storage," production reporting against it is discouraged; any long-term retention should be implemented via a custom archive or warehouse extract.

Related Objects

The ETRM metadata indicates no referenced (parent) objects and no downstream child dependencies within the CLN schema. The package or program CLN_ITEMMST_DELETED_ITEMS is documented as the only object that references this table. In practice, the following Oracle EBS objects are the most significant integration points, joined via the columns shown:

• MTL_SYSTEM_ITEMS_B — join on ORGANIZATION_ID and INVENTORY_ITEM_ID.
• MTL_ITEM_REVISIONS — item-level revision data keyed by INVENTORY_ITEM_ID.
• MTL_UNITS_OF_MEASURE — resolves PRIMARY_UOM_CODE to a UOM description.
• MTL_PARAMETERS — resolves ORGANIZATION_ID to an organization name/code.
• INV_ITEM_CATEGORIES — category assignments for archived items.
• FND_FLEX_VALUES — validation of CONCATENATED_SEGMENTS against the item key flexfield.
• CLN Customer Item cross-reference tables — resolved via CUSTOMER_ITEM_NUMBER.

Operationally, the table is consumed by the CLN item-master clean-up/archival program, which writes archived rows here prior to purging them from the active item master, and is subsequently read when a restore or audit is required.