Search Results hz_thin_st_cpts_u2




Overview
Explore in AI Chat

Overview

AR.HZ_THIN_ST_CPTS is a Registry (TCA) staging table owned by the AR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. The _THIN_ST_ naming convention indicates a "thin" staging structure used during bulk contact point import and DQM (Data Quality Management) processing against the HZ_CONTACT_POINTS model. During such processing, rows are loaded, resolved, and validated here prior to final merge into the production contact point tables. This staging pattern reduces locking and contention on the live TCA contact point tables during high-volume imports from legacy systems, external feeds, or third-party data quality tools.

The row is physically partitioned (via PARTITION_ID) and carries a surrogate-style composite business key used for deduplication resolution. Based on the FK structure, the object behaves largely as a standalone staging entity, though it participates as a link-like structure when joined to party, person, and contact point references. The heuristic Data Vault classification for HZ_THIN_ST_CPTS is standalone (link-like), referencing JTF_FM_PARTITION_X_REQUEST through PARTITION_ID. This should be treated as a modeling suggestion rather than a formal constraint.

Key Information Stored

The table contains ten documented columns, of which the following are most operationally significant:

  • PARTY_ID (NUMBER 15) – The TCA party identifier for the organization or person owning the contact point. Part of unique index HZ_THIN_ST_CPTS_U1.
  • CONTACT_POINT_ID (NUMBER 15) – The contact point identifier (phone, email, URL, etc.). Included in both unique indexes.
  • PERSON_PARTY_ID (NUMBER 15) – The person party reference associated with the contact point; drives uniqueness in HZ_THIN_ST_CPTS_U2.
  • QKEY (VARCHAR2 255) – A derived quality/deduplication key used to detect duplicate contact points across staging loads. It is the second column in both unique indexes.
  • PARTY_SITE_ID (NUMBER 15) – The party site to which the contact point is attached, allowing site-specific phone, email, or fax numbers.
  • ORG_CONTACT_ID (NUMBER 15) – Links the staging row to the organization contact record.
  • PARTITION_ID (NUMBER 15) – Foreign key to JTF_FM_PARTITION_X_REQUEST, delimiting the staging partition/batch.
  • PARENT_ROWID (ROWID 10) – Physical link to the parent or source row from which this staging entry was derived.
  • CONCAT_COL and STATUS_FLAG (VARCHAR2) – Concatenated match column and processing status indicator, respectively, used during DQM matching and staging cleanup.

The documented unique indexes serve as the business-key candidates: HZ_THIN_ST_CPTS_U1 (PARTY_ID, QKEY, CONTACT_POINT_ID) and HZ_THIN_ST_CPTS_U2 (PERSON_PARTY_ID, QKEY, CONTACT_POINT_ID). Both reside on the APPS_TS_TX_IDX tablespace.

Common Use Cases and Queries

Typical scenarios include auditing pending contact point staging rows before promotion, resolving duplicate contact points by QKEY, and reporting on contact points by party. A representative query joining the staging table to its partition request:

  • Select staging rows for a specific party: SELECT * FROM AR.HZ_THIN_ST_CPTS WHERE PARTY_ID = :p_party_id;
  • Identify duplicates by quality key: SELECT QKEY, COUNT(*) FROM AR.HZ_THIN_ST_CPTS GROUP BY QKEY HAVING COUNT(*) > 1;
  • Join to the partition request: SELECT s.*, p.* FROM AR.HZ_THIN_ST_CPTS s JOIN JTF_FM_PARTITION_X_REQUEST p ON s.PARTITION_ID = p.PARTITION_ID;
  • Status-based monitoring: SELECT STATUS_FLAG, COUNT(*) FROM AR.HZ_THIN_ST_CPTS GROUP BY STATUS_FLAG;

Related Objects

The most significant related objects, based on documented dependencies and business keys, include:

  • JTF_FM_PARTITION_X_REQUEST – Referenced via PARTITION_ID; the only documented foreign key dependency.
  • HZ_CONTACT_POINTS – The target production table into which staged rows are eventually merged via CONTACT_POINT_ID.
  • HZ_PARTIES – Joined via PARTY_ID to resolve party names and types.
  • HZ_PERSON_PROFILES / HZ_PARTY_SITES – Joined via PERSON_PARTY_ID and PARTY_SITE_ID for person and site context.
  • APPS.HZ_THIN_ST_CPTS – The APPS-synonym view referenced by this object.
  • HZ_CONTACT_POINT_CPDS – Contact point child/detail records used during resolution.
  • TCA DQM matching APIs – Consume QKEY and CONCAT_COL to drive deduplication logic.