Results for “ic_item_cnv”

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

Overview

IC_ITEM_CNV is a table owned by the GMI schema within the Oracle E-Business Suite Process Manufacturing Inventory module. It stores item and lot-level conversion factors, allowing the application to translate quantities between units of measure for a specific item, or for a specific item and lot combination. This is essential in process manufacturing environments where bulk materials are received, stored, consumed, and shipped in different units — for example, kilograms versus liters, or drums versus gallons — and where the conversion ratio may vary by lot due to density, concentration, or potency differences.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign key structure suggests a link table. IC_ITEM_CNV sits at the intersection of several reference and master entities — items/lots, units of measure types, and text headers — and records the measured relationship between them via conversion factors. This classification is a modeling suggestion rather than a documented architectural directive, but it accurately reflects the table's role as a connector of business keys rather than a pure descriptive satellite.

Key Information Stored

The table contains 15 documented columns. The most significant are detailed below, distinguishing the surrogate key from the business-key candidates defined by unique indexes.

• CONVERSION_ID — The surrogate primary key (IC_ITEM_CNV_PK). Uniquely identifies each conversion factor record.
• ITEM_ID — Identifies the item to which the conversion applies; part of the business unique key IC_ITEM_CNV_U1 and a foreign key to IC_LOTS_MST.
• LOT_ID — Identifies the specific lot for lot-level conversions; also part of IC_ITEM_CNV_U1 and the FK to IC_LOTS_MST.
• UM_TYPE — The unit of measure type for the conversion; part of IC_ITEM_CNV_U1 and a foreign key to SY_UOMS_TYP.
• TYPE_FACTOR — The conversion factor value applied for the given item/lot/UM combination.
• TYPE_FACTORREV — The reverse conversion factor, permitting conversion in the opposite direction.
• TEXT_CODE — Foreign key to IC_TEXT_HDR, linking the conversion record to descriptive or instructional text.
• EVENT_SPEC_DISP_ID — Foreign key to GMD_EVENT_SPEC_DISP, tying conversions to event specification dispositions where quality or regulatory context applies.
• TRANS_CNT — Transaction counter used for concurrency and audit tracking.
• DELETE_MARK — Soft-delete indicator; zero indicates an active row.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN support standard EBS auditability.

The unique index IC_ITEM_CNV_U1 (ITEM_ID, LOT_ID, UM_TYPE) represents the business key: only one conversion factor is permitted per item, lot, and unit-of-measure type combination. Consuming logic should therefore treat these three columns — not CONVERSION_ID alone — as the natural identity when reconciling or de-duplicating data.

Common Use Cases and Queries

The primary reporting use case is retrieving the conversion factor for a given item and lot to convert transactional quantities between units. A typical query pattern joins the table to IC_LOTS_MST to resolve item and lot details:

• Item-level conversion lookup: SELECT TYPE_FACTOR, TYPE_FACTORREV FROM IC_ITEM_CNV WHERE ITEM_ID = :item_id AND UM_TYPE = :um_type AND DELETE_MARK = 0.
• Lot-specific conversion: Add LOT_ID to the predicate when lot potency or density affects the ratio.
• Reverse conversion verification: Compare TYPE_FACTOR against TYPE_FACTORREV to detect inconsistent or outdated conversion setups.
• Missing-conversion audit: Join IC_ITEM_CNV to item/lot master tables to identify items lacking any defined conversion for a required UM type.
• Quality-linked reporting: Join on EVENT_SPEC_DISP_ID to associate conversion factors with event specification dispositions for compliance reporting.

Reporting against this table is common in inventory valuation, throughput analysis, and material balance reports where quantities posted in one unit must be restated in another.

Related Objects

The most significant related objects, based on documented foreign key relationships, are:

• IC_LOTS_MST — Referenced via IC_ITEM_CNV.ITEM_ID and IC_ITEM_CNV.LOT_ID; the primary source for item and lot master detail.
• SY_UOMS_TYP — Referenced via IC_ITEM_CNV.UM_TYPE; defines the unit-of-measure type used in the conversion.
• IC_TEXT_HDR — Referenced via IC_ITEM_CNV.TEXT_CODE; stores associated descriptive text.
• GMD_EVENT_SPEC_DISP — Referenced via IC_ITEM_CNV.EVENT_SPEC_DISP_ID; links conversions to event specification dispositions.

These relationships confirm IC_ITEM_CNV as an intersection entity that depends on item/lot master data and unit-of-measure definitions, and that optionally extends into quality and text reference domains.