Search Results customer_prospect_code




Overview
Explore in AI Chat

Overview

The APPS.MTL_CUSTOMER_ITEMS_ALL_V view presents Oracle Inventory customer item definitions in a flattened, reporting-ready form. It is catalogued under the INV – Inventory product family and carries the restrictive description "10SC ONLY," indicating that it was implemented specifically for a 10SC (discrete/process industry) deployment rather than as a broadly supported Applications view. In Oracle E-Business Suite 12.1.1 and 12.2.2, the object remains a VALID APPS-owned view, meaning it is queryable through the standard APPS schema and inherits the privileges of that schema for direct SQL access, custom reports, and integration extracts.

The view is significant because it joins customer item master data to the Oracle Trading Community Architecture (TCA) parties, customer accounts, addresses, lookups, commodity codes, and territories. Beyond simple joins, it derives several presentation attributes, including a concatenated address produced through ARP_ADDR_PKG.FORMAT_ADDRESS and a hard-coded CUSTOMER_PROSPECT_CODE value of 'CUSTOMER'. This makes the view useful where an interface or report expects a uniform customer/prospect discriminator without having to inspect the underlying HZ tables.

Underlying Base Objects

The documented base objects for this view are MTL_CUSTOMER_ITEMS, MTL_COMMODITY_CODES, MFG_LOOKUPS, AR_LOOKUPS, FND_TERRITORIES_VL, HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_LOCATIONS, HZ_PARTIES, HZ_PARTY_SITES, and the ARP_ADDR_PKG package. The primary driving table is MTL_CUSTOMER_ITEMS, aliased MCI in the view text; it supplies the customer item identifier, customer identifier, customer item number, item definition level, model and container relationships, demand tolerances, and the fifteen attribute columns. Customer name and status are resolved from HZ_PARTIES and HZ_CUST_ACCOUNTS (HZP and HZC aliases). Address data flows through HZ_CUST_ACCT_SITES_ALL, HZ_PARTY_SITES, and HZ_LOCATIONS, exposed as the RAD alias, with country and address style from FND_TERRITORIES_VL. Customer category meaning is decoded through AR_LOOKUPS, item level through MFG_LOOKUPS, and commodity code description through MTL_COMMODITY_CODES. The dependent package ARP_ADDR_PKG supplies the formatted, concatenated address string.

Key Columns

Common Use Cases and Queries

Typical uses include customer item catalog extracts, cross-reference validation between Oracle Inventory and customer-supplied part numbers, and reports that must display a customer/prospect flag. Because the view already supplies CUSTOMER_PROSPECT_CODE and a formatted address, it reduces the need for application-side string construction and TCA joins.

Example query listing active customer items with their owning customer:

  • SELECT customer_item_number, customer_name, customer_number, customer_prospect_code, item_level, inactive_flag FROM apps.mtl_customer_items_all_v WHERE inactive_flag = 'N' ORDER BY customer_name, customer_item_number;

Example query isolating the prospect-code column referenced in the search:

  • SELECT customer_id, customer_name, customer_number, customer_prospect_code FROM apps.mtl_customer_items_all_v WHERE customer_prospect_code = 'CUSTOMER';

Example query joining the view to inventory for reconciliation:

  • SELECT v.customer_item_number, v.customer_name, v.commodity_code, v.demand_tolerance_positive FROM apps.mtl_customer_items_all_v v WHERE v.customer_id = :customer_id AND NVL(v.inactive_flag,'N') = 'N';

Because the view is documented as "10SC ONLY," implementations should confirm its presence in each target instance and treat it as a customer-specific object rather than a guaranteed standard interface when upgrading between 12.1.1 and 12.2.2.