Results for “op_cust_itm”

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

Overview

OP_CUST_ITM is a Process Manufacturing Logistics (GML) table that stores customer item information — the cross-reference between a customer and the inventory item they purchase, scoped by warehouse and sales packaging. In Oracle EBS 12.1.1 and 12.2.2 it resides in the GML schema and carries a VALID status. The table functions as the operational master for customer-specific item attributes such as shipment tolerance percentages, MSDS handling requirements, and warehouse preferences or restrictions.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is link. OP_CUST_ITM resolves relationships among several independent business entities — customer (OP_CUST_MST), item (IC_ITEM_MST / IC_ITEM_MST_B), warehouse (IC_WHSE_MST), and sales package (OP_PKGD_ITM) — so it is best modeled as an intersection (link) entity rather than a pure hub or satellite. This distinction matters when designing integration or warehouse layers, because the natural grain is a combination of keys rather than a single business object.

Key Information Stored

The table contains 52 documented columns. The most important are:

• CUSTITEM_ID — surrogate primary key, defined by index OP_CUST_ITM_PK. This is the unique row identifier.
• CUST_ID — the customer to which the item relationship applies; foreign key to OP_CUST_MST.
• ITEM_ID — the inventory item; foreign key to both IC_ITEM_MST and IC_ITEM_MST_B.
• WHSE_CODE — the warehouse context; foreign key to IC_WHSE_MST.
• SALESPKG_ID — the sales package; foreign key to OP_PKGD_ITM.
• MINSHIP_PCT and MAXSHIP_PCT — minimum and maximum shipment tolerance percentages for this customer/item combination.
• MSDS_REQUIRED — flag indicating whether an MSDS document is mandated.
• MSDS_VERSION and DATE_LAST_MSDS — the MSDS version in effect and the date of the last MSDS.
• WHSE_PREFERENCE and WHSE_RESTRICTIONS — warehouse preference and restriction rules.
• CUSTITEM_ID combined with CUST_ID, ITEM_ID, WHSE_CODE, and SALESPKG_ID forms the business-key candidate referenced by unique index OP_CUST_ITM_U1.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — plus DELETE_MARK for logical deletion and TRANS_CNT for change tracking.
• Descriptive flexfield support via ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE30.
• OPM_CUST_ID and TEXT_CODE — auxiliary customer and text references.

Common Use Cases and Queries

Typical queries retrieve the item-to-customer mapping for order entry, pricing, or shipment validation. A basic join resolves descriptive item and customer data:

SELECT ci.custitem_id, ci.cust_id, ci.item_id, ci.whse_code,
       ci.minship_pct, ci.maxship_pct, ci.msds_required
FROM   gml.op_cust_itm ci
WHERE  ci.cust_id = :p_cust_id
AND    NVL(ci.delete_mark, 0) = 0;

Reporting scenarios include: identifying all items sold to a customer at a given warehouse; auditing MSDS compliance by extracting MSDS_REQUIRED, MSDS_VERSION, and DATE_LAST_MSDS; and validating shipment tolerances through MINSHIP_PCT/MAXSHIP_PCT. The OP_CUST_ITM_U1 unique index supports efficient lookups on the customer-item-warehouse-package combination, making it the preferred predicate for retrieval rather than scanning on CUSTITEM_ID.

Related Objects

OP_CUST_ITM anchors several master tables through documented foreign keys:

• OP_CUST_MST — joined on CUST_ID; the customer master.
• IC_ITEM_MST — joined on ITEM_ID; the item master.
• IC_ITEM_MST_B — joined on ITEM_ID; the item master business-table reference.
• IC_WHSE_MST — joined on WHSE_CODE; the warehouse master.
• OP_PKGD_ITM — joined on SALESPKG_ID; the packaged-item definition.

These five foreign-key relationships make OP_CUST_ITM a central intersection point in the GML logistics model, and application interfaces writing customer-item cross-references should validate against these parents before insert.