Results for “site_use_code”

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

Overview

OE_CUSTOMER_ADDRESSES_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the Order Management (ONT) product family and presents a denormalized, flattened representation of customer address data drawn from the Oracle Trading Community Architecture (TCA) model. The view consolidates party information, customer account details, account site records, and location address elements into a single queryable structure, allowing order management reports, concurrent programs, and custom extensions to retrieve shipping and billing address information without repeatedly joining the underlying TCA tables manually.

Because it is a standard Oracle-delivered view, it is frequently referenced in custom SQL, Oracle Reports, Oracle Forms personalizations, and inbound/outbound interfaces that require customer address attributes alongside customer name and account identifiers. The view is marked VALID in the data dictionary, indicating its definition compiles cleanly against the referenced synonyms.

Underlying Base Objects

The view is defined over six documented base objects, supplied as synonyms in the APPS schema:

• HZ_CUST_ACCOUNTS — customer account header, supplying CUST_ACCOUNT_ID, ACCOUNT_NUMBER, and PARTY_ID.
• HZ_CUST_ACCT_SITES — the account-site association linking a customer account to a party site, supplying CUST_ACCT_SITE_ID, STATUS, and ORG_ID.
• HZ_CUST_SITE_USES_ALL — site use records (for example ship-to or bill-to), supplying SITE_USE_ID, SITE_USE_CODE, and STATUS.
• HZ_PARTY_SITES — the party-to-location assignment.
• HZ_LOCATIONS — the physical address, supplying ADDRESS1 through ADDRESS5, CITY, STATE, PROVINCE, POSTAL_CODE, and COUNTRY.
• HZ_PARTIES — the party master, supplying PARTY_NAME.

Joins are established through CUST_ACCT_SITE_ID, PARTY_SITE_ID, LOCATION_ID, CUST_ACCOUNT_ID, and PARTY_ID. The SITE.ORG_ID = ACCT_SITE.ORG_ID predicate enforces operating unit consistency between site uses and account sites, an important consideration in multi-org environments.

Key Columns

• ADDRESS_LINE_1 through ADDRESS_LINE_5 — the individual address lines from HZ_LOCATIONS. ADDRESS_LINE_5 is exposed directly and is commonly searched when organizations store supplementary address data (building, floor, or delivery instructions) in that column.
• CUSTOMER_NAME — the party name from HZ_PARTIES.
• CUSTOMER_ID / CUSTOMER_NUMBER — the customer account identifier and account number from HZ_CUST_ACCOUNTS.
• CUST_ACCT_SITE_ID — the account site identifier, the primary key link to downstream order and site records.
• SITE_USE_CODE — identifies the purpose of the site, such as SHIP_TO or BILL_TO.
• SITE_STATUS / ACCT_SITE_STATUS / CUST_ACCT_STATUS — status flags at site use, account site, and account level; active records are typically those with status 'A'.
• ORG_ID — the operating unit identifier.
• LOCATION / LOCATION_CODE — both aliased from SITE.LOCATION, representing the location code on the site use record.

Note that the concatenated address column derived from CITY, STATE/PROVINCE, POSTAL_CODE, and COUNTRY is present in the view text but is not listed among the documented column names, so queries should reference the individual address line columns rather than the concatenated expression.

Common Use Cases and Queries

Typical usage includes validating address completeness, exporting customer ship-to addresses for interfaces, and locating customers whose addresses populate specific lines.

Retrieving all shipping addresses containing ADDRESS_LINE_5:

• SELECT customer_number, customer_name, address_line_1, address_line_5, site_use_code, org_id FROM oe_customer_addresses_v WHERE site_use_code = 'SHIP_TO' AND address_line_5 IS NOT NULL;

Filtering active sites for a given account:

• SELECT customer_number, site_use_code, site_status, acct_site_status FROM oe_customer_addresses_v WHERE customer_number = :p_account AND site_status = 'A';

Joining to order headers to resolve ship-to addresses for a set of orders:

• SELECT h.order_number, a.address_line_1, a.address_line_5, a.site_use_code FROM oe_order_headers_all h, oe_customer_addresses_v a WHERE h.ship_to_org_id = a.cust_acct_site_id;

Because the view spans multiple TCA tables, queries should always constrain by ORG_ID or a specific customer identifier to limit the result set, and ADDRESS_LINE_5 should be included in any address-completeness check to avoid omitting data stored on that line.