Results for “asf_party_sites_lov_v”

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

Overview

ASF_PARTY_SITES_LOV_V is a reporting and list-of-values (LOV) view belonging to the ASF – Sales Online product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description is concise: it "contains active party sites record." In practice, the view exposes a flattened, presentation-ready projection of party site and location data so that Sales Online forms, concurrent programs, and integration queries can retrieve candidate site records without navigating the full HZ (Trading Community Architecture) party model directly. The view is defined as a read-only construct and, per the ETRM record, is not implemented in the referenced database, meaning it may be absent from some environments or present only where the ASF Sales Online products are fully installed. The "_LOV_V" suffix indicates its primary design intent: supplying the value set behind a site selection list of values rather than serving as a high-volume transactional source.

Underlying Base Objects

Although the ETRM metadata documents no referenced base objects, the view text establishes the dependency chain clearly. The view joins three HZ tables:

• HZ_PARTY_SITES SITE — the driving table, supplying PARTY_SITE_ID, PARTY_ID, and STATUS.
• HZ_LOCATIONS LOC — joined on LOC.LOCATION_ID = SITE.LOCATION_ID, supplying the address attributes.
• HZ_PARTY_SITE_USES USE — joined with the outer-join operator (+) on USE.PARTY_SITE_ID = SITE.PARTY_SITE_ID, supplying site use type and the primary-per-type flag.

Filtering is applied twice: SITE.STATUS must be 'A' or 'I', and a DECODE on USE.STATUS(+) admits active ('A'), inactive ('I'), and null statuses while rejecting other values with -99. Because the site use join is outer, party sites lacking a site use row are still returned. The address column is further passed through ASF_CUST_ACCOUNTS_PUB.IS_VALID_LOCATION_SEGMENTS, tying the view to the ASF customer accounts public API for address validation logic.

Key Columns

The ETRM column listing documents eighteen columns. CUST_ACCT_SITE_ID and CUST_ACCOUNT_ID appear in the definition, though the view text itself selects NULL for the first two positions, reflecting a legacy or compatibility-oriented column layout. PARTY_SITE_ID and PARTY_ID are the principal identifiers linking back to the HZ party model. STATUS indicates the party site status, while PRIMARY_FLAG (sourced from USE.PRIMARY_PER_TYPE) and SITE_USE_CODE (sourced from USE.SITE_USE_TYPE) describe how the site is used. The address block — ADDRESS1 through ADDRESS4, CITY, STATE, COUNTRY, COUNTY, POSTAL_CODE, and PROVINCE — mirrors HZ_LOCATIONS. VALID_ADDR_FLAG carries the boolean result of IS_VALID_LOCATION_SEGMENTS, allowing consumers to filter or flag addresses whose segments fail validation.

Common Use Cases and Queries

Typical consumption patterns include populating Sales Online LOVs, driving customer and site validation reports, and feeding downstream integrations that require active shipping or billing addresses. A representative query returning active sites for a party:

• SELECT party_site_id, party_id, site_use_code, address1, city, state, postal_code, valid_addr_flag FROM asf_party_sites_lov_v WHERE party_id = :p_party_id AND status = 'A' AND valid_addr_flag = 'Y' ORDER BY primary_flag DESC, city;

Because the view is not guaranteed to be implemented, callers should verify its presence via ALL_VIEWS before relying on it, and should treat VALID_ADDR_FLAG as a filter rather than a stored attribute. Queries against this view are best restricted to interactive/Lov-style lookups; for bulk extraction, querying HZ_PARTY_SITES, HZ_LOCATIONS, and HZ_PARTY_SITE_USES directly generally yields better performance.