Results for “as_interest_codes”

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

Overview

ICX_LEADS_V is a reporting view associated with Oracle iProcurement (product code ICX) in Oracle E-Business Suite 12.1.1 and 12.2.2. The ETRM metadata describes it as a "Retrofitted" object, indicating that the view was carried forward or reconstructed for the current release rather than introduced as new functionality. Its purpose is to expose lead and lead-line information from the Oracle Sales / Advanced Sales (AS) schema in a flattened, denormalized form suitable for display or downstream consumption within iProcurement-related flows.

The view consolidates header-level lead attributes with line-level detail, resolving foreign keys against units of measure, inventory items, interest types, and interest codes. This makes it a convenient single-source object for queries that would otherwise require joining six or more base tables. Note that the ETRM record states "Not implemented in this database," so the view may not exist in every environment despite being documented; validation against the target instance is advisable.

Because the view joins transactional lead data with descriptive lookups, it functions primarily as a reporting and integration surface rather than a transaction-processing object. Consumers should treat it as read-only.

Underlying Base Objects

The view text references the following base objects, all joined with outer-join syntax (the legacy (+) operator):

• AS_LEADS (alias L) — the lead header table, supplying LEAD_ID, LEAD_NUMBER, DESCRIPTION, STATUS, CUSTOMER_ID, ADDRESS_ID, INITIATING_CONTACT_ID, ORG_ID, and PRICE_LIST_ID.
• AS_LEAD_LINES (alias LINE) — the lead line table, supplying LEAD_LINE_ID, INTEREST_TYPE_ID, PRIMARY_INTEREST_CODE_ID, SECONDARY_INTEREST_CODE_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, UOM_CODE, and QUANTITY.
• MTL_UNITS_OF_MEASURE (alias UOM) — resolves UOM_CODE to UNIT_OF_MEASURE.
• MTL_SYSTEM_ITEMS_KFV (alias ITEM) — resolves the inventory item and organization to CONCATENATED_SEGMENTS.
• AS_INTEREST_TYPES (alias INT) — resolves INTEREST_TYPE_ID to the descriptive INTEREST_TYPE.
• AS_INTEREST_CODES (aliases PIC and SIC) — independently resolve the primary and secondary interest code IDs to their CODE values.

All joins except the mandatory AS_LEADS-to-AS_LEAD_LINES link are outer joins, so a lead line is retained even when lookup data is missing. The ETRM metadata records no additional referenced base objects.

Key Columns

• LEAD_ID — Primary identifier of the lead header; the join key to AS_LEADS.
• LEAD_NUMBER — Human-readable lead reference number.
• STATUS — Current lifecycle state of the lead.
• CUSTOMER_ID / ADDRESS_ID / INITIATING_CONTACT_ID — Party, location, and originating contact references.
• ORG_ID — Operating unit, supporting multi-org security and filtering.
• PRICE_LIST_ID — Associated price list reference.
• LEAD_LINE_ID — Identifier of the individual lead line.
• INTEREST_CATEGORY — A concatenated string composed as INTEREST_TYPE / primary CODE / secondary CODE, built with NVL to avoid null concatenation issues. This is the most derived column and is useful for compact reporting.
• INVENTORY_ITEM_ID / ITEM_NUMBER / ORGANIZATION_ID — Item and inventory organization context, where ITEM_NUMBER is the concatenated segment value from MTL_SYSTEM_ITEMS_KFV.
• UOM_CODE / UNIT_OF_MEASURE — Unit of measure for the line quantity.
• QUANTITY — Lead line quantity.

Common Use Cases and Queries

Typical uses include lead-to-item conversion reporting, interest classification analysis, and iProcurement-side extraction of leads for downstream processing. A basic listing filtered by operating unit:

• SELECT LEAD_NUMBER, STATUS, ITEM_NUMBER, QUANTITY FROM ICX_LEADS_V WHERE ORG_ID = :p_org_id;
• SELECT INTEREST_CATEGORY, COUNT(*) FROM ICX_LEADS_V GROUP BY INTEREST_CATEGORY ORDER BY 2 DESC;
• SELECT V.LEAD_NUMBER, V.ITEM_NUMBER, V.UNIT_OF_MEASURE, V.QUANTITY FROM ICX_LEADS_V V WHERE V.STATUS = 'NEW' AND V.INVENTORY_ITEM_ID IS NOT NULL;

Because the lookup joins are outer joins, WHERE predicates applied directly to columns such as UNIT_OF_MEASURE or ITEM_NUMBER silently restrict rows where the lookup failed to resolve; filtering on the ID columns (for example, INVENTORY_ITEM_ID) is preferable when completeness matters.