Results for “inventory_item_org_id”

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

Overview

AMS_DELIV_PRODUCTS is a table in the AMS (Marketing) schema of Oracle E-Business Suite, holding the association between marketing deliverables (collateral) and the inventory products they promote or reference. In release 12.1.1 and 12.2.2 the table is documented as VALID with 12 columns and two unique indexes, and it participates in the Oracle Marketing deliverable model by capturing one row per product linked to a given deliverable. The table therefore answers the business question "which inventory items are attached to this piece of collateral?" and supports downstream activities such as campaign planning, collateral distribution, and product-tier reporting.

From a modeling perspective, the supplied metadata heuristic classifies this object as satellite-leaning. This is a suggestion rather than a prescribed design: the table carries descriptive attributes (item, item organization, description) that qualify the parent deliverable, and its natural business key is composite, which is characteristic of a satellite attached to the deliverable hub. Implementers designing a Data Vault or dimensional layer should treat AMS_DELIVERABLES_ALL_B as the parent hub and AMS_DELIV_PRODUCTS as a dependent satellite keyed by deliverable plus product.

Key Information Stored

The physical schema exposes twelve columns, of which the following are the most consequential:

• DELIVERABLE_PRODUCT_ID — the surrogate primary key, enforcing AMS_DELIV_PRODUCTS_PK and an additional unique index (AMS_DELIV_PRODUCTS_U1). It uniquely identifies each deliverable-to-product association.
• DELIVERABLE_ID — foreign key to AMS_DELIVERABLES_ALL_B, identifying the parent collateral record. It forms the first component of the composite business key in AMS_DELIV_PRODUCTS_UK1 / _U2.
• INVENTORY_ITEM_ID — the inventory item identifier for the product attached to the deliverable; the second component of the composite unique key.
• INVENTORY_ITEM_ORG_ID — the inventory organization that owns the item, completing the three-part business key (DELIVERABLE_ID, INVENTORY_ITEM_ID, INVENTORY_ITEM_ORG_ID). This column is the direct answer to the search term and is essential because item numbers are only unique within an operating unit's item master context.
• DESCRIPTION — free-text annotation describing the product linkage.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-organization / multi-tenant access control typical of AMS tables.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Applications Framework (OAF) and concurrent update protection.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns tracking who inserted or modified each row and when.

Notably, the table does not store the item description or item number itself; those are resolved through joins to inventory master tables. The surrogate key (DELIVERABLE_PRODUCT_ID) is distinct from the business key candidates (DELIVERABLE_ID, INVENTORY_ITEM_ID, INVENTORY_ITEM_ORG_ID) documented in the unique indexes.

Common Use Cases and Queries

Typical reporting scenarios include listing all products associated with a marketing deliverable, determining which collateral references a given item, and auditing item-to-collateral coverage by inventory organization. A representative query joining the parent table is:

• Products for a deliverable: SELECT dp.deliverable_id, dp.inventory_item_id, dp.inventory_item_org_id, dp.description FROM ams.ams_deliv_products dp WHERE dp.deliverable_id = :deliverable_id;
• Deliverables for an item: SELECT d.deliverable_id, d.name FROM ams.ams_deliv_products dp JOIN ams.ams_deliverables_all_b d ON d.deliverable_id = dp.deliverable_id WHERE dp.inventory_item_id = :item_id AND dp.inventory_item_org_id = :org_id;
• Collateral coverage by item: aggregate counts grouped by INVENTORY_ITEM_ORG_ID to measure how many deliverables reference each product.
• Security-filtered extracts: constrain by SECURITY_GROUP_ID when generating reports for a specific operating unit.

Because the composite unique index guarantees that the same item cannot be attached twice to one deliverable, queries can use INVENTORY_ITEM_ID and INVENTORY_ITEM_ORG_ID confidently without duplicate-elimination logic.

Related Objects

• AMS_DELIVERABLES_ALL_B — parent deliverable table; join on DELIVERABLE_ID. This is the primary FK relationship documented for this table.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for access control.
• MTL_SYSTEM_ITEMS_B — the inventory item master; join on INVENTORY_ITEM_ID (with ORGANIZATION_ID = INVENTORY_ITEM_ORG_ID) to resolve item name and description.
• MTL_PARAMETERS — supplies the inventory organization definition corresponding to INVENTORY_ITEM_ORG_ID.
• AMS_DELIVERABLES_ALL_TL — translation table for deliverable names used in reports.
• AMS_DELIVERABLE_ITEMS / AMS_DELIVERABLE_USAGES — related collateral-child tables often queried alongside this object.
• AMS APIs — deliverable and collateral programmatic interfaces that insert into this table when products are attached to collateral in the Marketing UI.