Search Results oe_ar_customer_profiles_v




Overview
Explore in AI Chat

Overview

The OE_AR_CUSTOMER_PROFILES_V view is an APPS-owned, VALID database view shipped with Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 under the Order Management (ONT) product. It provides a consolidated, denormalized projection of customer profile information relevant to order capture and Receivables processing: payment terms, the sold-to customer account, the invoice-to (bill-to) site use, and the ship-to site use. Rather than requiring callers to navigate the underlying Oracle Trading Community Architecture (TCA) model directly, the view publishes a compact, integration-friendly result set keyed on identifiers that downstream EBS APIs and reports expect.

The view is particularly significant because it exposes INVOICE_TO_ORG_ID. In EBS, the internal identifier used for a bill-to site use in order and invoicing contexts is a SITE_USE_ID from HZ_CUST_SITE_USES, not the customer account identifier. Terminology in external systems frequently refers to this as an "org ID," which is a common source of confusion for developers searching on invoice_to_org_id. This view resolves that ambiguity by aliasing the bill-to SITE_USE_ID explicitly as INVOICE_TO_ORG_ID, making it usable in order import, invoice generation, and interface contexts without additional joins.

Underlying Base Objects

The documented base objects are three TCA synonyms: HZ_CUSTOMER_PROFILES, HZ_CUST_ACCOUNTS, and HZ_CUST_SITE_USES. The view joins them as follows:

  • HZ_CUSTOMER_PROFILES (alias PROF) is the driving table, supplying the customer profile rows, the payment term (STANDARD_TERMS), and the join key SITE_USE_ID to the site-use tables.
  • HZ_CUST_ACCOUNTS (alias CUST_ACCT) supplies the customer account identifier via the join on CUST_ACCOUNT_ID, yielding the sold-to account.
  • HZ_CUST_SITE_USES is joined twice as INV_SITE and SHIP_SITE, both on SITE_USE_ID, filtered by SITE_USE_CODE = 'BILL_TO' and SITE_USE_CODE = 'SHIP_TO' respectively.

Both HZ_CUST_SITE_USES joins are outer joins (indicated by the (+) operators), so a profile row is still returned even when no bill-to or ship-to site use exists; in those cases the corresponding columns are NULL. Because the joins rely on PROF.SITE_USE_ID relating to both bill-to and ship-to rows, the view effectively surfaces a site use in both capacities when a single SITE_USE_ID carries the relevant use codes.

Key Columns

  • PAYMENT_TERM_ID — sourced from PROF.STANDARD_TERMS; the payment terms identifier used for the customer profile.
  • SOLD_TO_ORG_ID — sourced from CUST_ACCT.CUST_ACCOUNT_ID; the sold-to customer account identifier.
  • INVOICE_TO_ORG_ID — sourced from INV_SITE.SITE_USE_ID; the bill-to site use identifier (the SITE_USE_ID for BILL_TO).
  • SHIP_TO_ORG_ID — sourced from SHIP_SITE.SITE_USE_ID; the ship-to site use identifier (SITE_USE_ID for SHIP_TO).

Common Use Cases and Queries

Typical uses include looking up the bill-to site use for a customer profile during order import, populating default invoicing attributes in order and invoice interfaces, and reporting on payment terms and site-use relationships. A representative query is:

  • SELECT SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID, PAYMENT_TERM_ID FROM APPS.OE_AR_CUSTOMER_PROFILES_V WHERE INVOICE_TO_ORG_ID = :p_site_use_id;
  • SELECT SOLD_TO_ORG_ID, SHIP_TO_ORG_ID FROM APPS.OE_AR_CUSTOMER_PROFILES_V WHERE PAYMENT_TERM_ID = :p_term_id;
  • Join to HZ_CUST_SITE_USES on INVOICE_TO_ORG_ID = SITE_USE_ID to retrieve bill-to address details for invoice presentation.

Because the view performs no filtering beyond the site-use codes in its join conditions, qualifying queries by SOLD_TO_ORG_ID or INVOICE_TO_ORG_ID is advisable for performance in high-volume environments.