Search Results site_code

Overview

AS_L_SITES_V is a Sales Foundation (AS) reporting view that consolidates customer party site information into a single, query-ready structure. It joins address data, customer data, and territory (country) reference data, providing a flattened record of a party site together with its owning customer. Its purpose is to expose address-level detail — including what users commonly identify as the site_code — alongside customer identifiers so that reports, forms, and integrations can resolve a customer site without navigating the normalized Trading Community Architecture (TCA) tables directly.

Within Oracle EBS 12.1.1 and 12.2.2, this view is typically consumed by Sales, Order Management, and Receivables-related reporting where a customer site must be displayed or validated. The ETRM metadata notes that in the source database instance examined, the view is not implemented in this database, meaning the definition exists in the repository but has no runtime instantiation. Where it is deployed, it derives its name from the "L" (list/lookup) convention and the "SITES" entity, returning one row per party site joined to its customer.

Underlying Base Objects

The view text defines three referenced objects, all themselves views in the AS layer:

• AS_PARTY_CUSTOMERS_V CUST — supplies customer identity: CUSTOMER_ID, PARTY_ID, CUSTOMER_NAME, CUSTOMER_NUMBER, and CUSTOMER_STATUS_CODE.
• AS_PARTY_ADDRESSES_V ADDR — supplies the physical site data: PARTY_SITE_ID, ADDRESS_ID, ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY, and STATUS.
• FND_TERRITORIES_TL TERR — the territory (country) translation table, supplying TERRITORY_SHORT_NAME.

The join conditions are: ADDR.PARTY_ID = CUST.PARTY_ID, with outer joins (+) on COUNTRY = TERR.TERRITORY_CODE and TERR.LANGUAGE = USERENV('LANG'). The outer join ensures sites with an unrecognized or null country are still returned, while the language predicate restricts territory rows to the session's current language. No base tables are documented at the table level; both AS_PARTY_CUSTOMERS_V and AS_PARTY_ADDRESSES_V are themselves TCA-derived views.

Key Columns

• SITE_CODE — the site identifier most relevant to the user's search. It maps the site reference used in Sales contexts; the view text aliases PARTY_SITE_ID into this position.
• CUSTOMER_ID / PARTY_ID / CUSTOMER_NUMBER / CUSTOMER_NAME — customer identity keys and descriptive fields.
• PARTY_SITE_ID / ADDRESS_ID — the primary keys for the site and its address record.
• ADDRESS1 through ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — the full postal address, with TERRITORY_SHORT_NAME providing the localized country label.
• CUSTOMER_STATUS_CODE / ADDRESS_STATUS — status flags indicating whether the customer and the site address are active or inactive.

Note that the column list documents COUNTRY and ADDRESS_STATUS, whereas the SELECT projects ADDR.STATUS and TERR.TERRITORY_SHORT_NAME; the published column aliases reconcile these.

Common Use Cases and Queries

Typical uses include customer site lookups on order entry, address validation reports, and integration extracts that need customer-plus-site rows. A standard query filtering by site:

• SELECT site_code, customer_number, customer_name, address1, city, postal_code, country FROM as_l_sites_v WHERE site_code = :p_site_code;
• SELECT customer_number, customer_name, site_code, address1, city FROM as_l_sites_v WHERE customer_id = :p_customer_id ORDER BY site_code;
• SELECT site_code, customer_name, address_status FROM as_l_sites_v WHERE customer_status_code = 'A' AND address_status = 'A';

Because the view contains no filtered predicates beyond the language restriction, callers should always apply their own status and customer filters to limit result sets.