Results for “full_address”

2 results




Overview
Explore in AI Chat

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

Overview

PA_CUSTOMER_SITES_V is an APPS-owned database view in the Oracle E-Business Suite Projects (PA) module. It presents a denormalized, reporting-friendly representation of customer site address information by joining the Oracle Trading Community Architecture (TCA) model — parties, party sites, locations, customer accounts, account sites, and site uses — into a single row per active BILL_TO or SHIP_TO site. Unlike the base TCA tables, which spread address and customer attributes across multiple entities requiring several joins, this view flattens the most frequently referenced attributes into one logical record, simplifying report development, integrations, and ad hoc queries.

The view exists under the APPS schema in both EBS 12.1.1 and 12.2.2 and is documented as VALID. Because it is defined over the TCA model (HZ schema objects), it is fully compatible with the multi-organization and multi-Org security architecture of R12. It is read-only in practice; no DML should ever be issued against it, and its definition is maintained by Oracle as part of the standard product installation.

Underlying Base Objects

The view is defined over six base objects, all referenced through APPS synonyms that resolve to the HZ schema:

The join path is: LOCATION_ID from HZ_PARTY_SITES to HZ_LOCATIONS; PARTY_SITE_ID from HZ_CUST_ACCT_SITES_ALL to HZ_PARTY_SITES; CUST_ACCT_SITE_ID from HZ_CUST_SITE_USES to HZ_CUST_ACCT_SITES_ALL; CUST_ACCOUNT_ID from HZ_CUST_ACCT_SITES_ALL to HZ_CUST_ACCOUNTS; and PARTY_ID from HZ_CUST_ACCOUNTS to HZ_PARTIES. The view applies DISTINCT and filters to active site uses only: NVL(SU.STATUS,'A') = 'A' and SITE_USE_CODE restricted to BILL_TO or SHIP_TO. Results are ordered by ADDRESS1.

Key Columns

  • ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4 — raw address line components from HZ_LOCATIONS.
  • ADDRESS_ID — the location identifier; this is the column commonly aliased to fulfill searches for "full_address," though the fully assembled address text is exposed separately as FULL_ADDRESS.
  • CUSTOMER_ID — the customer account identifier (CUST_ACCOUNT_ID).
  • CUSTOMER_NAME — the party name, truncated to 50 bytes via SUBSTRB on PARTY_NAME.
  • SITE_USE_CODE — either BILL_TO or SHIP_TO.
  • FULL_ADDRESS — the concatenated address string: ADDRESS1 through ADDRESS4 plus city, state (or province), and county, with commas inserted after ADDRESS4 when present.
  • CUSTOMER_NUMBER — the account number from HZ_CUST_ACCOUNTS.

Common Use Cases and Queries

This view is typically used to resolve customer billing or shipping addresses for project-related reporting, invoice interfaces, and third-party integrations. A typical query retrieving the full address for a given customer number:

SELECT customer_number, customer_name, full_address, site_use_code
FROM apps.pa_customer_sites_v
WHERE customer_number = :p_account_number;

To list all active ship-to sites:

SELECT customer_name, address1, full_address
FROM apps.pa_customer_sites_v
WHERE site_use_code = 'SHIP_TO';

Because FULL_ADDRESS is a concatenation of raw components, it should be treated as a convenience column for display rather than for exact-match filtering. For structured address validation or geocoding, query the individual ADDRESS1–ADDRESS4 columns directly.