Search Results hz_cust_site_uses




Overview
Explore in AI Chat

Overview

APPS.OE_AR_CUSTOMER_PROFILES_V is a reporting and integration view in the Oracle E-Business Suite Order Management and Receivables schema. It exposes a compact projection of customer profile data, resolving the relationship between a customer account, its payment terms, and the specific site uses designated as bill-to and ship-to destinations. In the Oracle EBS 12.1.1 and 12.2.2 releases, the trading community model is built on the HZ (Trading Community Architecture) tables rather than the legacy RA/RH customer tables, and this view provides a simplified, denormalized surface over that model.

The view is principally used by Order Management and Receivables logic and by downstream reporting that needs to map an order's sold-to, invoice-to, and ship-to organizations. Because it returns identifiers rather than descriptive attributes, it functions as a bridge view: callers join it to HZ_CUST_ACCOUNTS, HZ_CUST_SITE_USES, or HZ_CUST_ACCT_SITES_ALL to obtain names, addresses, and site details. It is documented in the ETRM as a view owned by APPS with base objects exposed through synonyms.

Underlying Base Objects

The documented base objects referenced by this view are HZ_CUSTOMER_PROFILES, HZ_CUST_ACCOUNTS, and HZ_CUST_SITE_USES, each accessed through a public synonym. The view text joins these three objects as follows:

  • HZ_CUSTOMER_PROFILES (PROF) — the driving table, supplying the payment term (STANDARD_TERMS) and the profile-level SITE_USE_ID used to anchor the bill-to and ship-to lookups.
  • HZ_CUST_ACCOUNTS (CUST_ACCT) — joined on CUST_ACCOUNT_ID to resolve the sold-to organization identifier.
  • HZ_CUST_SITE_USES (INV_SITE, SHIP_SITE) — outer-joined twice, once for SITE_USE_CODE = 'BILL_TO' and once for SITE_USE_CODE = 'SHIP_TO', to derive the invoice-to and ship-to site use identifiers.

The join between the profile and each site-use alias is an outer join on SITE_USE_ID, so a profile row is returned even when a matching bill-to or ship-to site use does not exist; in that case the corresponding INVOICE_TO_ORG_ID or SHIP_TO_ORG_ID is null. The relationship to HZ_CUST_ACCOUNTS is an inner join on CUST_ACCOUNT_ID, so only profiles tied to a valid customer account are surfaced.

Key Columns

  • PAYMENT_TERM_ID — aliased from PROF.STANDARD_TERMS. Identifies the standard payment term associated with the customer profile.
  • SOLD_TO_ORG_ID — aliased from CUST_ACCT.CUST_ACCOUNT_ID. The customer account identifier representing the sold-to organization.
  • INVOICE_TO_ORG_ID — aliased from INV_SITE.SITE_USE_ID. The site use identifier flagged as BILL_TO for the profile's site.
  • SHIP_TO_ORG_ID — aliased from SHIP_SITE.SITE_USE_ID. The site use identifier flagged as SHIP_TO.

These four columns mirror the organization and term identifiers commonly required when populating order or invoice interface structures.

Common Use Cases and Queries

The view is typically queried to resolve the default bill-to and ship-to site uses for a customer profile, often as part of order import, customer profile validation, or reconciliation reports.

  • Retrieve the payment term and site uses for a specific customer account.
  • Identify profiles where a BILL_TO or SHIP_TO site use is missing (null values).
  • Feed order or Receivables interface tables with valid organization identifiers.

Sample query:

SELECT p.payment_term_id, p.sold_to_org_id, p.invoice_to_org_id, p.ship_to_org_id FROM apps.oe_ar_customer_profiles_v p WHERE p.sold_to_org_id = :cust_account_id;

To locate incomplete profiles, filter on the nullable columns:

SELECT sold_to_org_id FROM apps.oe_ar_customer_profiles_v WHERE invoice_to_org_id IS NULL OR ship_to_org_id IS NULL;