Results for “asf_customer_lov_v”

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

Overview

ASF_CUSTOMER_LOV_V is a database view shipped within the Oracle E-Business Suite (EBS) application, belonging to the ASF — Sales Online product family. Its name indicates its primary purpose: it serves as a List of Values (LOV) source for customer-related selection fields within Sales Online and related HTML-based or Oracle Forms-based customer-facing screens. The view consolidates party, party site, party site use, and location information into a single denormalized projection, allowing application developers and report authors to resolve a customer selection to a specific party, address, and site use combination without writing multi-table joins against the Trading Community Architecture (TCA) schema.

Per the ETRM documentation for 12.2.2, this view is not implemented by default in the referenced database, meaning it exists as dictionary metadata within the application tier but is created only when the relevant ASF product components are installed and licensed. Its primary role is therefore confined to reporting and integration contexts where Sales Online customer selection logic is required, rather than as a general-purpose TCA query object.

Underlying Base Objects

The view text is defined over four TCA base tables:

• HZ_PARTIES — the master party entity, aliased as PARTY.
• HZ_PARTY_SITES — party-to-location associations, aliased as SITE.
• HZ_PARTY_SITE_USES — site use assignments (e.g., ship-to, bill-to), aliased as USE.
• HZ_LOCATIONS — physical address details, aliased as LOC.

The joins are predominantly outer joins, with the driving filter being PARTY.STATUS = 'A' (active parties). Site-level filtering uses NVL(USE.STATUS(+), 'A') = 'A' and SITE.STATUS(+) = 'A', while the site use end date is constrained with NVL(USE.END_DATE (+), SYSDATE) >= SYSDATE. The ETRM metadata for 12.2.2 documents no referenced base objects separately, but the embedded view text provides the authoritative join definition above.

Key Columns

• PARTY_NAME, PARTY_NUMBER, PARTY_ID — identity and business key of the customer party.
• LOCATION_ID, SITE_ID, SITE_USE_ID — the address, party site, and site-use identifiers that uniquely resolve the selected LOV entry.
• ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, POSTAL_CODE, COUNTY, COUNTRY — the formatted postal address components.
• SITE_USE_TYPE — the use classification (SHIP_TO, BILL_TO, etc.) of the party site use.
• IDENTIFYING_ADDRESS_FLAG — indicates whether the site is the party's primary identifying address.
• STATUS — the status of the underlying site use.
• PARTY_TYPE — classification of the party (e.g., organization, person).

Common Use Cases and Queries

The view is typically used to populate LOVs on Sales Online entry pages, to drive customer search in custom HTML or OAF pages, and as a reporting source joining customer identity to address data. A representative query is:

SELECT PARTY_NAME, PARTY_NUMBER, ADDRESS1, CITY, STATE, POSTAL_CODE, SITE_USE_TYPE FROM ASF_CUSTOMER_LOV_V WHERE SITE_USE_TYPE = 'SHIP_TO' AND UPPER(PARTY_NAME) LIKE UPPER(:p_name)||'%';

Because the view restricts to active parties, active sites, and non-expired site uses, it returns only currently valid customers — a suitable basis for transactional selection. For integration, the returned LOCATION_ID, SITE_ID, and SITE_USE_ID can be passed directly into downstream ASF or Order Management APIs. Where the object is not implemented in a given database, DBAs should confirm the ASF product installation before relying on it in custom code.