Search Results registry_status

Overview

APPS.HZ_DQM_PS_U_V is a reporting and data-quality view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the APPS schema. It is layered over the party site use entity in the Trading Community Architecture (TCA) model. The suffix "DQM" (Data Quality Management) and the column-name prefix "dq" indicate that the view is intended for data-quality assessment, de-duplication, and cleanse/registry processing rather than for transactional online use. The view exposes a denormalized projection of HZ_PARTY_SITE_USES rows, augmented with decoded lookup meanings, so that downstream data-quality tools, registry interfaces, and reporting queries can evaluate party site use records consistently.

The view is of particular interest when searching for registry_status, because it resolves the site use STATUS value through the ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING function against the REGISTRY_STATUS lookup type, aliased as DQSTATUSM. This makes the raw code and its human-readable meaning available side by side.

Underlying Base Objects

The ETRM metadata documents three referenced base objects:

• HZ_PARTY_SITE_USES (SYNONYM) — the primary TCA table holding party site use records, referenced by alias A. The view selects from it directly.
• AR_LOOKUPS (VIEW) — joined with an outer join (F.LOOKUP_TYPE(+) = 'PARTY_SITE_USE_CODE' and F.LOOKUP_CODE(+) = A.SITE_USE_TYPE) to supply the site use type meaning as DQMEANING.
• ARPT_SQL_FUNC_UTIL (PACKAGE) — a server-side utility package whose GET_LOOKUP_MEANING function is called to translate the site use status code from the REGISTRY_STATUS lookup type.

The predicate NVL(a.status,'A') NOT IN ('M') filters out site use rows whose status resolves to the merged code 'M', so merged party site uses are excluded from the result set. The view is therefore a filtered, decoded read-only projection of the underlying TCA and lookup data.

Key Columns

• DQPARTY_SITE_USE_ID — unique identifier of the party site use record (from PARTY_SITE_USE_ID).
• DQBEGIN_DATE / DQEND_DATE — effective date range of the site use.
• DQPARY_SITE_ID (DQPARTY_SITE_ID) — foreign key to the parent party site.
• DQSITE_USE_TYPE — the site use type code; decoded to text as DQMEANING via AR_LOOKUPS.
• DQPRIMARY_PER_TYPE — indicates the primary site use designation per type.
• DQSTATUS — the raw site use status code.
• DQSTATUSM — the decoded meaning of DQSTATUS obtained by GET_LOOKUP_MEANING('REGISTRY_STATUS', status), falling back to the raw code when no lookup row exists. This is the column the registry_status search targets.
• DQCOMMENTS — free-text comments on the site use.
• DQMEANING — the lookup meaning for the site use type.

Common Use Cases and Queries

Typical uses include data-quality profiling of party site uses, identifying records by their registry status, and exporting decoded data to ETL or registry processes. A representative query that retrieves registry status information is:

• SELECT dqparty_site_use_id, dqparty_site_id, dqsite_use_type, dqstatus, dqstatusm FROM apps.hz_dqm_ps_u_v WHERE dqstatusm = 'Active';
• SELECT dqparty_site_use_id, dqstatus, dqstatusm, dqmeaning FROM apps.hz_dqm_ps_u_v ORDER BY dqparty_site_use_id;
• SELECT dqstatus, COUNT(*) FROM apps.hz_dqm_ps_u_v GROUP BY dqstatus;

Because merged records are excluded and the REGISTRY_STATUS lookup is applied, the view is best suited to active-record analysis and to validating status decoding rather than to auditing merged or historical site uses.