Results for “asf_organizations_qf_v”

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

Overview

ASF_ORGANIZATIONS_QF_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite (documented across 12.1.1 and 12.2.2). It belongs to the ASF — Sales Online product family and is described in the ETRM metadata as the "View for Organization Quick Find." Its purpose is to supply a flattened, denormalized result set that powers quick-find (QF) searches for organization-type parties within Sales Online and adjacent Oracle CRM/TeleSales flows.

The view consolidates party attributes, a single concatenated address string, and an order activity indicator onto one row per organization party, removing the need for the calling form or report to join HZ_PARTIES against multiple address and site tables at runtime. Status is VALID in the ETRM registry, confirming the object is compiled and available for query. In practice the view functions as a reporting and integration surface rather than a transaction table: it exposes organizations with an active or inactive status, making it useful for list-of-values, search pages, and downstream extracts.

Underlying Base Objects

The documented metadata states that ASF_ORGANIZATIONS_QF_V is defined over a single referenced base object: HZ_PARTIES (accessed through a SYNONYM). The view text confirms this, selecting from HZ_PARTIES and aliasing it as ORG. No additional joins to HZ_CUST_ACCOUNTS, HZ_LOCATIONS, or HZ_PARTY_SITES are present in the documented definition; the address is instead assembled from denormalized columns carried on HZ_PARTIES itself.

Two filters constrain the result set:

• PARTY_TYPE = 'ORGANIZATION' — person parties are excluded.
• STATUS IN ('A', 'I') — both active and inactive organizations are returned; other statuses are omitted.

Because the view is defined entirely on HZ_PARTIES, its freshness, partitioning, and index usage are governed by the TCA party model. Any change to a party record is immediately reflected in the view with no materialization or refresh lag.

Key Columns

The view exposes a column list whose names differ in several cases from the underlying HZ_PARTIES columns, which is important when writing SQL:

• ORG_ID / PARTY_ID — the party identifier; note the view text selects ORG.PARTY_ID twice, a documented quirk of the definition.
• ORG_NAME, ORG_NUMBER, TAX_REFERENCE — map to PARTY_NAME, PARTY_NUMBER, and TAX_REFERENCE respectively.
• STATUS_CODE, SIC_CODE, CATEGORY_CODE, URL — party classification and web attributes.
• ADDRESS_BOOK — the fully concatenated address including city, state, postal code, and country.
• ADDRESS — the concatenated street lines only (ADDRESS1 through ADDRESS4).
• ADDRESS1–ADDRESS4, STATE, CITY, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — individual address components.
• ALIAS, YEAR_ESTABLISHED, EMAIL, FISCAL_YEAREND_MONTH — map to KNOWN_AS, YEAR_ESTABLISHED, EMAIL_ADDRESS, and FISCAL_YEAREND_MONTH.
• LAST_ORDER_DATE — exposed from the underlying LAST_ORDERED_DATE column. This is the column most relevant to a search on "last_order_date" and represents the most recent order placed by the organization; it is frequently NULL for parties with no order history.
• CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY — standard audit columns.

Common Use Cases and Queries

Typical usage includes populating organization quick-find lists of values, driving Sales Online search pages, building receivables or CRM dashboards, and extracting organization master data for analytics. A frequent requirement is to identify organizations by recency of purchasing activity.

To list organizations ordered by their most recent order date, filtering out those with no orders:

SELECT party_id, org_name, org_number, last_order_date
FROM apps.asf_organizations_qf_v
WHERE last_order_date IS NOT NULL
ORDER BY last_order_date DESC;

To find organizations whose last order predates a cutoff (dormant accounts):

SELECT org_name, city, country, last_order_date
FROM apps.asf_organizations_qf_v
WHERE status_code = 'A'
AND last_order_date < SYSDATE - 365;

For a quick-find search on name or address, the concatenated columns are convenient:

SELECT org_name, address_book, email, last_order_date
FROM apps.asf_organizations_qf_v
WHERE UPPER(org_name) LIKE UPPER(:search_term)||'%';

Because last_order_date can be NULL, any ordering or comparison logic should explicitly handle nulls — for example with ORDER BY last_order_date NULLS LAST — to avoid inactive or newly created organizations appearing unexpectedly at the top of result sets.