Results for “hz_dqm_ps_cpt_v”

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

Overview

HZ_DQM_PS_CPT_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, catalogued under the Receivables (AR) product family. The name encodes its purpose: it exposes contact points (CPT) associated with party sites (PS) for use by the Data Quality Management (DQM) matching and deduplication engine. The "DQ" prefixes on every projected column confirm that the view is a purpose-built interface for the DQM subsystem rather than a general-purpose reporting object.

The view restricts its output to contact points whose OWNER_TABLE_NAME equals 'HZ_PARTY_SITES', meaning it returns phone numbers, e-mail addresses, telex numbers, and web URLs recorded against party site records only. Records with a STATUS of 'M' are excluded (the NVL test treats NULL status as 'A'), and results are ordered by a descending match score supplied by the HZ_MATCHED_CPTS_GT global temporary table. In the DQM workflow this ordering allows the highest-scoring candidate contact points to be presented first during duplicate review or merge processing.

Underlying Base Objects

The view is defined over six documented base objects:

• HZ_CONTACT_POINTS (synonym) — the primary driver table, supplying contact point identity, owner, type, status, and the type-specific value columns (EMAIL_ADDRESS, RAW_PHONE_NUMBER, TELEX_NUMBER, URL).
• HZ_MATCHED_CPTS_GT (synonym) — a global temporary table populated by the matching engine with candidate contact point IDs and their match scores; joined via CONTACT_POINT_ID with an outer (+) so unmatched rows remain visible with a NULL score.
• AR_LOOKUPS (view) — joined twice, once as H for COMMUNICATION_TYPE meanings and once as H1 for PHONE_LINE_TYPE meanings, both outer-joined on lookup code.
• ARPT_SQL_FUNC_UTIL (package) — its GET_LOOKUP_MEANING function translates the raw STATUS code through the 'REGISTRY_STATUS' lookup type.
• ARH_DQM_TREE_HELPER (package) — supplies CTXMAX, the current search context identifier, used to isolate rows belonging to the active matching session.

Key Columns

• DQCONTACT_POINT_ID — unique identifier of the contact point; the merge/join key for DQM processing.
• DQOWNER_TABLE_NAME / DQOWNER_TABLE_ID — the owning entity; always 'HZ_PARTY_SITES' for this view, with the site identifier.
• DQCONTACT_POINT_TYPE — decoded lookup meaning. Phone records use the PHONE_LINE_TYPE meaning; all other types use the COMMUNICATION_TYPE meaning.
• DQPRIMARY_PER_PURPOSE / DQPRIMARY_FLAG — flags indicating whether the contact point is the primary one for its purpose or overall.
• DQSTATUS / DQSTATUSM — the raw status code and its Registry Status lookup meaning.
• DQCONTACT_INFO — the actual contact value, decoded by type from e-mail, raw phone, telex, or URL.

Common Use Cases and Queries

The view is typically consumed by DQM duplicate-review pages and by diagnostic queries examining which contact points the matching engine returned. A representative query:

• SELECT dqcontact_point_id, dqcontact_point_type, dqcontact_info, dqstatusm FROM hz_dqm_ps_cpt_v WHERE dqowner_table_id = :site_id; — lists scored contact points for a party site.
• SELECT dqcontact_point_type, COUNT(*) FROM hz_dqm_ps_cpt_v GROUP BY dqcontact_point_type; — provides a distribution of contact point types in the current match context.
• Joining DQCONTACT_POINT_ID back to HZ_CONTACT_POINTS for merge validation, or filtering on DQPRIMARY_FLAG = 'Y' to isolate primary contact points.

Because output depends on ARH_DQM_TREE_HELPER.CTXMAX, results are session-sensitive and should be queried within an active DQM context.