Results for “dqpostal_code”

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

Overview

HZ_DQM_PS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Receivables (AR) product family and is part of the Data Quality Management (DQM) layer used by Oracle's Trading Community Architecture (TCA) and DQM utilities. The view presents party site information together with its associated address, status, and territory data in a denormalized, reporting-friendly format. It is not a base transaction table; rather, it consolidates data from party sites, locations, and territory definitions so that downstream DQM processes, customer data cleansing, and integration routines can consume a single flat result set.

The view is documented as VALID in ETRM, meaning its compiled definition resolves against all referenced objects in a supported EBS instance. The name prefix HZ_ indicates the TCA (Trading Community Architecture) family of objects, while the DQM_PS segment denotes Data Quality Management – Party Site.

Underlying Base Objects

The view definition joins three source objects and calls one package function:

• HZ_PARTY_SITES (referenced via synonym) — supplies PARTY_SITE_ID, PARTY_ID, STATUS, and LOCATION_ID. This is the primary driver of the view.
• HZ_LOCATIONS (referenced via synonym) — supplies ADDRESS1, ADDRESS2, COUNTY, PROVINCE, CITY, STATE, POSTAL_CODE, and COUNTRY.
• FND_TERRITORIES_VL (view) — supplies TERRITORY_SHORT_NAME, joined on COUNTRY = TERRITORY_CODE with an outer-join (+) so sites with unmapped countries are not dropped.
• ARPT_SQL_FUNC_UTIL (package) — invoked through GET_LOOKUP_MEANING to translate the raw registry status code into a lookup meaning.

The join condition is B.LOCATION_ID = C.LOCATION_ID. Crucially, the WHERE clause filters out sites whose NVL(STATUS,'A') equals 'M', excluding merged party sites from the result set.

Key Columns

• DQPARTY_SITE_ID — the party site identifier (HZ_PARTY_SITES.PARTY_SITE_ID).
• DQPARTY_ID — the owning party identifier.
• DQSTATUS — decoded status: 'A' renders as ACTIVE, 'I' as INACTIVE, otherwise NULL.
• DQSTATUSM — the lookup meaning for the REGISTRY_STATUS lookup type, obtained via ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING; falls back to the raw status if no meaning exists. This is the column most relevant to searches on "registry_status".
• DQADDRESS — concatenation of ADDRESS1 and ADDRESS2 (separated by a hyphen when ADDRESS2 is populated).
• DQCOUNTY, DQPROVINCE, DQCITY, DQSTATE, DQPOSTAL_CODE, DQCOUNTRY — the standard location components, with DQCOUNTRY resolving through FND_TERRITORIES_VL.

Common Use Cases and Queries

Typical usages include DQM data extraction, address validation reporting, and integration feeds that require a flattened party-site-plus-address record. Because DQSTATUSM resolves the REGISTRY_STATUS lookup, the view is frequently used to report on registration status without an explicit join to FND_LOOKUP_VALUES.

Sample query listing active party sites with resolved registry status:

• SELECT dqparty_site_id, dqparty_id, dqstatus, dqstatusm, dqaddress, dqcity, dqcountry FROM hz_dqm_ps_v WHERE dqstatus = 'ACTIVE';
• SELECT dqstatusm, COUNT(*) FROM hz_dqm_ps_v GROUP BY dqstatusm;
• SELECT dqparty_id, dqaddress, dqpostal_code FROM hz_dqm_ps_v WHERE dqparty_id = :party_id;

Note that the view exposes no DML capability; it is intended strictly for query and extraction. Since it filters out merged sites and relies on a package function call per row, performance may degrade on large party populations, and a targeted index on HZ_PARTY_SITES (LOCATION_ID, STATUS) is advisable for high-volume queries.