Search Results hz_contact

Overview

HZ_IMP_CONTACTROLES_INT is a public interface table owned by the AR schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It is a member of the Trading Community Architecture (TCA) import infrastructure and stores staging rows destined for population into the HZ_ORG_CONTACT_ROLES base table. Its FND Design Data registration is AR.HZ_IMP_CONTACTROLES_INT, its status is VALID, and it resides in the APPS_TS_INTERFACE tablespace with a PCTFREE of 10. The product scope is HZ, the lifecycle is active, and the display name is "Contact Role." Records are loaded by external or legacy sources, validated through the TCA import batch process, and subsequently transferred to the production contact role entity.

From a Data Vault modeling perspective, the foreign key relationship between BATCH_ID and HZ_IMP_BATCH_SUMMARY, together with the presence of descriptive attributes such as ROLE_TYPE and multiple originating system references, suggests a satellite-leaning classification. In this interpretation BATCH_ID functions as the link to the parent batch hub, while the role type and source-system descriptions are carried as satellite attributes.

Key Information Stored

The table comprises 19 documented columns. The most significant are listed below.

• BATCH_ID — NUMBER(15). Unique identifier for a batch and the foreign key to HZ_IMP_BATCH_SUMMARY. This is the principal grouping key for all rows staged within the same import run.
• CONTACT_ORIG_SYSTEM / CONTACT_ORIG_SYSTEM_REFERENCE — the source system identifier and reference for the contact party participating in the relationship.
• SUB_ORIG_SYSTEM / SUB_ORIG_SYSTEM_REFERENCE — the source system identifier and reference for the subject party of the relationship, typically the organization.
• ROLE_TYPE — VARCHAR2(30). The role assigned to the party in the contact relationship.
• INSERT_UPDATE_FLAG — controls whether the import process performs an explicit insert or an update against the target.
• INTERFACE_STATUS — the processing state of the staging row, used to track validation and transfer progress.
• ERROR_ID — NUMBER(15). Foreign key into the error table that captures rejection reasons when validation fails.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program audit columns identifying the last program to touch the row.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY_MODULE — standard Who columns, with CREATED_BY_MODULE recording the module that originated the row.

No unique index is documented on this table. The only documented index, HZ_IMP_CONTACTROLES_INT_N1, is non-unique and spans BATCH_ID, SUB_ORIG_SYSTEM, INTERFACE_STATUS, and SUB_ORIG_SYSTEM_REFERENCE. The effective surrogate identity is therefore the combination of BATCH_ID with the sub-orig-system business keys rather than a single column.

Common Use Cases and Queries

Typical scenarios include monitoring batch import progress, diagnosing rows that failed validation, and reporting on the volume of contact roles loaded per source system.

To count rows by processing status within a batch:

SELECT interface_status, COUNT(*)
FROM   ar.hz_imp_contactroles_int
WHERE  batch_id = :p_batch_id
GROUP  BY interface_status;

To isolate failed rows and their errors:

SELECT t.sub_orig_system_reference,
       t.role_type,
       t.error_id
FROM   ar.hz_imp_contactroles_int t
WHERE  t.interface_status = 'ERROR'
  AND  t.batch_id = :p_batch_id;

To trace the batch context through the parent summary table:

SELECT s.batch_id, s.status, COUNT(t.batch_id)
FROM   ar.hz_imp_batch_summary s,
       ar.hz_imp_contactroles_int t
WHERE  s.batch_id = t.batch_id
GROUP  BY s.batch_id, s.status;

Related Objects

• HZ_IMP_BATCH_SUMMARY — referenced by BATCH_ID; the parent object governing each import batch.
• HZ_ORG_CONTACT_ROLES — the base TCA table this interface ultimately populates.
• HZ_IMP_CONTACTS_INT — sibling interface table staging the underlying contact records.
• HZ_IMP_CONTACTROLES_ERROR — target of the ERROR_ID foreign key for failed rows.
• HZ_CONTACT — the business entity category to which this object belongs.