Results for “mtl_customer_items_active_v”
41 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MTL_CUSTOMER_ITEMS_ACTIVE_V is an APPS-owned database view in the Oracle E-Business Suite Inventory (INV) product, and is documented as "10SC ONLY" in the ETRM metadata for releases 12.1.1 and 12.2.2. The view presents customer item definitions held in MTL_CUSTOMER_ITEMS, enriched with customer, address, commodity, and lookup context, and exposes them in a denormalized form suitable for reporting and integration. Its name and column set indicate that it is intended to return customer items together with the descriptor of the model customer item to which they belong, via the MODEL_ITEM_ID column that carries the customer item identifier of the parent model. This makes the view the natural access point for queries that require an item-level relationship between a customer item and its model, a pattern associated with customer-item/model-item hierarchies used in specific industry solutions rather than with core inventory transactions. Because it joins party, account, and location data from the Trading Community Architecture (TCA) and reference lookups, the view removes the need for a report or interface to re-implement those joins when displaying customer item information.
Underlying Base Objects
According to the documented view text, MTL_CUSTOMER_ITEMS_ACTIVE_V is defined over the following base objects: MTL_CUSTOMER_ITEMS (synonym), HZ_CUST_ACCOUNTS (synonym), HZ_CUST_ACCT_SITES_ALL (synonym), HZ_LOCATIONS (synonym), HZ_PARTIES (synonym), HZ_PARTY_SITES (synonym), MTL_COMMODITY_CODES (synonym), AR_LOOKUPS (view), FND_TERRITORIES_VL (view), MFG_LOOKUPS (view), and the package ARP_ADDR_PKG. MTL_CUSTOMER_ITEMS is the driving table, aliased MCI, and supplies the customer item identity, description, level, container, tolerance, descriptive flexfield, and audit columns. HZ_CUST_ACCOUNTS (HZC) supplies the account number and status; HZ_PARTIES (HZP) supplies the party name exposed as CUSTOMER_NAME. The address columns (ADDRESS1 through POSTAL_CODE, COUNTRY, ADDRESS_STYLE, and STATUS) are drawn from HZ_CUST_ACCT_SITES_ALL and HZ_LOCATIONS, with HZ_PARTY_SITES providing the party-to-site linkage, and the concatenated address is produced by ARP_ADDR_PKG.FORMAT_ADDRESS. MTL_COMMODITY_CODES (MCC) contributes commodity code, description, and inactive date; FND_TERRITORIES_VL (TERR) provides the territory short name and address style; AR_LOOKUPS (ARL) resolves the customer category code, and MFG_LOOKUPS (MFL) resolves the item definition level.
Key Columns
The view exposes several groups of columns:
- Customer item identity: CUSTOMER_ITEM_ID, CUSTOMER_ITEM_NUMBER, and CUSTOMER_ITEM_DESC identify the customer item record.
- Model relationship: MODEL_CUSTOMER_ITEM_ID holds the model customer item identifier, MODEL_ITEM_ID exposes the related customer item identifier as MCIM.CUSTOMER_ITEM_ID, and MODEL_CUSTOMER_ITEM and MODEL_CUSTOMER_ITEM_DESC provide the model's number and description. This is the set of columns relevant to searches on "model_item_id".
- Customer and address: CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER, CUSTOMER_STATUS, CUSTOMER_PROSPECT_CODE, ADDRESS_ID, the individual address attributes, CONCATENATED_ADDRESS, and ADDRESS_STATUS.
- Classification: CUSTOMER_CATEGORY_CODE with its CUSTOMER_CATEGORY meaning, ITEM_DEFINITION_LEVEL with ITEM_LEVEL meaning, COMMODITY_CODE_ID, COMMODITY_CODE, and commodity DESCRIPTION.
- Inventory attributes: MASTER_CONTAINER_ITEM_ID, CONTAINER_ITEM_ORG_ID, DETAIL_CONTAINER_ITEM_ID, MIN_FILL_PERCENTAGE, DEP_PLAN_REQUIRED_FLAG, DEP_PLAN_PRIOR_BLD_FLAG, INACTIVE_FLAG, and the DEMAND_TOLERANCE_POSITIVE and DEMAND_TOLERANCE_NEGATIVE columns.
- Audit and flexfield columns: ROW_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and ATTRIBUTE_CATEGORY with ATTRIBUTE1 through ATTRIBUTE15.
Common Use Cases and Queries
The view is typically used to report the model-to-detail relationship for customer items, to display customer items with their customer account and address context, and to feed integration extracts that need model item identifiers. A representative query locating the model item for a given customer item is:
SELECT customer_item_id, customer_item_number, model_customer_item_id, model_item_id, model_customer_item FROM mtl_customer_items_active_v WHERE model_item_id = :p_model_item_id;SELECT customer_item_number, customer_name, customer_number, concatenated_address, commodity_code, inactive_flag FROM mtl_customer_items_active_v WHERE customer_id = :p_customer_id AND inactive_flag = 'N';SELECT model_customer_item, COUNT(*) detail_count FROM mtl_customer_items_active_v WHERE model_item_id IS NOT NULL GROUP BY model_customer_item;
Because the object is documented as 10SC-only, its use should be validated against the specific application configuration before being embedded in custom reports or interfaces.
-
10SC ONLY
APPS.MTL_CUSTOMER_ITEMS_ACTIVE_V·↳ ARP_ADDR_PKG·↳ AR_LOOKUPS·↳ FND_TERRITORIES_VL·Explore INV module →
-
10SC ONLY
APPS.MTL_CUSTOMER_ITEMS_ACTIVE_V·↳ ARP_ADDR_PKG·↳ AR_LOOKUPS·↳ FND_TERRITORIES_VL·Explore INV module →
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 FND Design Data 12.2.2
-
VIEW: APPS.AR_LOOKUPS 12.1.1
-
VIEW: APPS.AR_LOOKUPS 12.2.2
-
VIEW: APPS.MFG_LOOKUPS 12.2.2
-
SYNONYM: APPS.HZ_LOCATIONS 12.2.2
-
VIEW: APPS.MFG_LOOKUPS 12.1.1
-
SYNONYM: APPS.HZ_PARTY_SITES 12.1.1
-
SYNONYM: APPS.HZ_LOCATIONS 12.1.1
-
SYNONYM: APPS.HZ_PARTY_SITES 12.2.2
-
SYNONYM: APPS.HZ_PARTIES 12.2.2
-
SYNONYM: APPS.HZ_PARTIES 12.1.1
-
eTRM - INV Tables and Views 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
eTRM - INV Tables and Views 12.2.2
-
eTRM - AR Tables and Views 12.2.2
Territory information
-
eTRM - FND Tables and Views 12.2.2
No longer used
-
eTRM - AR Tables and Views 12.1.1
Territory information
-
eTRM - FND Tables and Views 12.1.1
No longer used
-
eTRM - INV Tables and Views 12.1.1
-
eTRM - INV Tables and Views 12.2.2
-
eTRM - FND Tables and Views 12.2.2
No longer used
-
eTRM - FND Tables and Views 12.1.1
No longer used
-
eTRM - AR Tables and Views 12.2.2
Territory information
-
eTRM - AR Tables and Views 12.1.1
Territory information