Search Results hz_party_cert_level




Overview
Explore in AI Chat

Overview

APPS.AST_LM_ORG_QUICK_W_ACC_V is a reporting view in the Oracle E-Business Suite that consolidates organization-level party, customer account, and lookup information into a single denormalized result set. The view derives its data from the Oracle Trading Community Architecture (TCA) model and is intended to support quick lookup and reporting of organization parties together with their associated customer accounts. Its role is primarily analytic and integration-oriented: it allows external reports, Oracle Discoverer worksheets, OBIEE repositories, and custom concurrent programs to retrieve organization party records without writing the multi-table joins against HZ_PARTIES, HZ_CUST_ACCOUNTS, FND_TERRITORIES_TL, and AR_LOOKUPS directly.

The name references "LM," which in the ETRM/EBS context reflects a listing or lookup master style object, and "QUICK_W_ACC" indicates a quick view that carries the customer account context. The user's search term, hz_party_cert_level, is directly relevant: the view exposes the HZ_PARTY_CERT_LEVEL lookup through the CERTIFICATION_LEVEL column and its decoded meaning column, CERT_LEVEL_MEAN.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, the view is owned by APPS and is defined over the following referenced base objects:

  • HZ_PARTIES (SYNONYM) — the driving table, aliased as PARTY, supplying party identity, address, status, contact, and certification attributes. The view filters PARTY.PARTY_TYPE = 'ORGANIZATION' and PARTY.STATUS IN ('I','A').
  • HZ_CUST_ACCOUNTS (SYNONYM) — aliased as HCA and joined with an outer join on party_id; account status is constrained with hca.status(+)='A'.
  • FND_TERRITORIES_TL (SYNONYM) — aliased as FTT, providing the translated territory short name used as COUNTRY_NAME; joined with outer joins on TERRITORY_CODE and language context via userenv('LANG').
  • AR_LOOKUPS (VIEW) — referenced twice, as ALK for the CODE_STATUS lookup decoding party status, and as ALK1 for the HZ_PARTY_CERT_LEVEL lookup decoding CERTIFICATION_LEVEL.

Key Columns

  • PARTY_ID, PARTY_NUMBER, PARTY_NAME — the primary TCA organization identifiers.
  • ACCOUNT_NUMBER, CUST_ACCOUNT_ID — the linked customer account keys from HZ_CUST_ACCOUNTS.
  • ADDRESS, CITY, COUNTY, STATE, POSTAL_CODE, PROVINCE, COUNTRY, COUNTRY_NAME — concatenated address lines plus the translated territory name.
  • EMAIL_ADDRESS, URL, PRIMARY_PHONE_* — contact information, including concatenated phone formats.
  • STATUS, STATUS_MEAN — party status code and its AR_LOOKUPS meaning.
  • CERTIFICATION_LEVEL, CERT_LEVEL_MEAN — the certification level code and its decoded meaning from the HZ_PARTY_CERT_LEVEL lookup, corresponding directly to hz_party_cert_level.

Common Use Cases and Queries

The view is used to report organizations with associated customer accounts and certification levels, commonly within supplier/customer onboarding, diversity certification tracking, and party master extracts.

SELECT party_id, party_name, account_number,
       cert_level_mean
  FROM apps.ast_lm_org_quick_w_acc_v
 WHERE cert_level_mean IS NOT NULL;

Filtering on certification level is a frequent requirement given the HZ_PARTY_CERT_LEVEL lookup:

SELECT party_name, account_number, country_name, cert_level_mean
  FROM apps.ast_lm_org_quick_w_acc_v
 WHERE status = 'A'
   AND cert_level_mean = :cert_meaning;

Because it joins lookup meanings, it also serves as a convenient source for party/account status dashboards without requiring the caller to replicate the AR_LOOKUPS and territory joins.