Results for “hz_dqm_ac_c_v”

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

Overview

HZ_DQM_AC_C_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, catalogued under the Receivables (AR) product family. It forms part of the Oracle Data Quality Management (DQM) framework used by the Trading Community Architecture (TCA) and Receivables customer matching and de-duplication facilities. The view presents a consolidated, denormalised projection of customer account contacts and their party relationships, exposing contact identity, title, job responsibility, contact number, and status within a single queryable structure. In both EBS 12.1.1 and 12.2.2 the view retains a VALID status and is defined over TCA foundation tables. Its role is primarily internal: it supplies candidate contact records to the DQM party and account matching engine, including fuzzy-search scoring when invoked through the matched-contacts global temporary table. Because the view is not designed as a general-purpose reporting interface, direct ad hoc use should be treated with caution; nevertheless it is frequently referenced during data-quality investigations, contact de-duplication analysis, and technical troubleshooting of account role assignments.

Underlying Base Objects

The view is constructed from a multi-table join documented in the EBS data model:

• HZ_CUST_ACCOUNT_ROLES (synonym) — the driving table, supplying the customer account and account role identifiers; rows with a non-null CUST_ACCT_SITE_ID are excluded, restricting output to account-level rather than site-level roles.
• HZ_PARTIES (synonym) — joined twice: once as the relationship party (PARTY_TYPE = 'PARTY_RELATIONSHIP') and once as the subject party providing PARTY_NAME.
• HZ_RELATIONSHIPS (synonym) — links the relationship party to the subject party, with DIRECTIONAL_FLAG = 'F' and SUBJECT_TABLE_NAME = 'HZ_PARTIES'.
• HZ_ORG_CONTACTS (synonym) — supplies organisation contact number, job title code, and title code.
• HZ_MATCHED_CONTACTS_GT (synonym) — the DQM global temporary table used for match scoring; joined via ORG_CONTACT_ID with search context CTXMAX.
• AR_LOOKUPS (view) — joined twice, without filtering, to resolve RESPONSIBILITY and CONTACT_TITLE meanings.
• ARPT_SQL_FUNC_UTIL (package) — supplies GET_LOOKUP_MEANING for the REGISTRY_STATUS status translation.
• ARH_DQM_TREE_HELPER (package) — provides the CTXMAX search context constant used in the match join.

Key Columns

• DQCUST_ACCOUNT_ID / DQCUST_ACCOUNT_ROLE_ID — account and role identifiers from HZ_CUST_ACCOUNT_ROLES.
• DQRELATIONSHIP_ID / DQORG_CONTACT_ID — relationship and organisation contact keys used for matching.
• DQPARTY_NAME — the subject party name.
• DQTITLE / DQJOB_TITLE — lookup meanings for contact title and responsibility respectively.
• DQCONTACT_NUMBER — the organisation contact number.
• DQSTATUS / DQSTATUSM — raw status code and its REGISTRY_STATUS lookup meaning.

Common Use Cases and Queries

Typical usage includes inspecting account-level contact roles and their associated party names, validating status translations, and tracing DQM match candidates.

SELECT dqcust_account_id,
       dqparty_name,
       dqjob_title,
       dqstatusm
FROM   apps.hz_dqm_ac_c_v
WHERE  dqcust_account_id = :cust_account_id;

Contact counts per account:

SELECT dqcust_account_id, COUNT(*)
FROM   apps.hz_dqm_ac_c_v
GROUP  BY dqcust_account_id
HAVING COUNT(*) > 1;