Search Results icx_customer_contacts_v

Overview

ICX_CUSTOMER_CONTACTS_V is a database view shipped with Oracle E-Business Suite, catalogued under the ICX (Oracle iProcurement) product family. Its documented description is "Web Customers Salesrep Customer Contacts List View." As the name and description indicate, the view presents a denormalized read-only listing that joins customer account information, customer site addresses, phone numbers, and customer contact records into a single flattened result set. It is intended to support iProcurement and related web-facing functionality where a sales representative or internal user needs to browse customer contacts alongside the associated customer and address context.

The view is significant because it consolidates data that would otherwise require multiple joins across the Oracle Receivables and Oracle Customers (TCA) data model. Rather than querying separate customer, address, phone, and contact entities independently, consumers of this view receive a pre-joined projection suitable for display in an HTML or forms-based list. The ETRM entry notes that the view is "Not implemented in this database," meaning this particular ETRM catalog captured the view text definition but the object was not present in that specific instance. The view text is therefore the authoritative source for its structure.

Underlying Base Objects

The ETRM metadata does not document explicit referenced base objects, and no owning schema is recorded. However, the view text aliases reveal the underlying tables:

• CUST — The customer entity (for example, HZ_CUST_ACCOUNTS or its AR predecessor), supplying CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER, credit and demographic attributes.
• CONT — The contact entity (for example, HZ_CUST_ACCOUNT_ROLES or HZ_ORG_CONTACTS), supplying CONTACT_ID, LAST_NAME, FIRST_NAME, TITLE, JOB_TITLE, PRIMARY_ROLE, and related contact attributes.
• CUST_ADDR — The customer site or address entity, supplying ADDRESS_ID, ADDRESS1-4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, and site flags such as BILL_TO_FLAG, SHIP_TO_FLAG, and MARKET_FLAG.
• CUST_PHON — The phone entity, supplying PHONE_ID, PHONE_NUMBER, PHONE_TYPE, AREA_CODE, EXTENSION, and PRIMARY_FLAG.
• TERR1 — A territory lookup, supplying TERRITORY_SHORT_NAME, used to substitute the territory short name for the country where available.

The concatenated address expression and the NVL against TERR1.TERRITORY_SHORT_NAME indicate the view performs an outer or lookup join to territory data to produce a display-ready country value.

Key Columns

Prominent columns exposed by the view include:

• CUSTOMER_ID, CONTACT_ID — Primary identifiers linking the flattened row back to the customer account and contact.
• CUSTOMER_NAME, CUSTOMER_NUMBER — Human-readable customer identification.
• Concatenated address string — A composed value joining customer name with ADDRESS1-4, CITY, PROVINCE, COUNTY, STATE, POSTAL_CODE, and the resolved territory or country, suitable for single-column display.
• Address component columns — ADDRESS1 through ADDRESS4, CITY, POSTAL_CODE, STATE, PROVINCE, COUNTY, plus site flags (BILL_TO_FLAG, SHIP_TO_FLAG, MARKET_FLAG, KEY_ACCOUNT_FLAG, REFERENCE_USE_FLAG, ADDRESS_STYLE).
• Phone columns — PHONE_ID, PHONE_NUMBER, PHONE_TYPE, AREA_CODE, EXTENSION, PRIMARY_FLAG.
• Contact columns — LAST_NAME || ', ' || FIRST_NAME as a display name, plus LAST_NAME, FIRST_NAME, TITLE, JOB_TITLE, MAIL_STOP, JOB_TITLE_CODE, PRIMARY_ROLE, SUFFIX, and DO_NOT_MAIL_FLAG.
• Customer demographic/credit columns — FISCAL_YEAREND_MONTH, NET_WORTH, NUM_OF_EMPLOYEES, TAX_CODE, YEAR_ESTABLISHED, SHIP_VIA, FREIGHT_TERM, PAYMENT_TERM_ID, COMPETITOR_FLAG, JGZZ_FISCAL_CODE, DO_NOT_MAIL_FLAG.

Common Use Cases and Queries

Typical uses include iProcurement contact lookups, sales-representative contact browsing, and custom reports requiring a flattened customer-contact-address projection. A representative query might be:

SELECT customer_id, customer_name, contact_id, last_name, first_name, phone_number, address1, city, state, postal_code FROM apps.icx_customer_contacts_v WHERE customer_id = :p_customer_id;

Because the view is not documented as implemented in all instances, developers should verify existence in the target environment and confirm the owning schema (typically APPS) before referencing it in custom code. Where the view is absent, equivalent joins across the TCA customer, address, phone, and contact tables should be constructed.