Results for “party_orig_system_reference”

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

Overview

HZ_IMP_ADDRESSUSES_SG is a TCA (Trading Community Architecture) internal staging table owned by the AR schema in Oracle E-Business Suite. It is one of the standard interface staging tables used by the party and address import process. In the Oracle EBS 12.1.1 and 12.2.2 file layouts, records are first loaded into the interface table HZ_IMP_ADDRESSUSES_INT, and the import concurrent program then moves the data into the corresponding staging table, HZ_IMP_ADDRESSUSES_SG, before performing validation, resolution of foreign keys, and final insertion into the production TCA tables such as HZ_PARTY_SITE_USES. The "_SG" suffix denotes a staging (or "staging group") table, which is populated and consumed only during a running import batch and is not part of the end-user data model.

The table has a documented physical schema of 12 columns and is classified as VALID in the AR schema. Under the heuristic Data Vault classification mined from its foreign key structure, the table is a standalone entity; this suggests that, if modeled in a Data Vault, it would be treated as its own hub or standalone construct rather than a dependent link, reflecting the fact that it serves as a transient batch-processing work area rather than a persistent relationship between business entities.

Key Information Stored

The 12-column layout captures both batch control and address-use business content. The most operationally significant columns are:

• BATCH_ID — identifies the import batch under which the row was processed; the primary grouping key for monitoring and reconciliation.
• INT_ROW_ID — the identifier of the originating interface row in HZ_IMP_ADDRESSUSES_INT; provides traceability back to the source load.
• BATCH_MODE_FLAG — indicates the mode in which the batch is processed (for example, create, update, or validate).
• ACTION_FLAG — the requested action for the record, typically INSERT or UPDATE.
• ERROR_FLAG — marks whether the row failed validation or processing, enabling error-row reporting.
• PARTY_ORIG_SYSTEM and PARTY_ORIG_SYSTEM_REFERENCE — the source system and source identifier used to resolve the owning party.
• PARTY_ACTION_FLAG — the action to be applied to the parent party when the address use is processed.
• PRIMARY_FLAG — denotes whether the address use is the primary use for the party site.
• PARTY_SITE_USE_ID — the foreign key referencing HZ_PARTY_SITE_USES; the resolved production identifier for the address use.
• PARTY_SITE_ID — the site to which the use type applies.
• SITE_USE_TYPE — the type of site use, such as BILL_TO, SHIP_TO, or STATEMENT.

In EBS convention, the staging table's logical surrogate key is the interface row identifier (INT_ROW_ID), while the business-key candidate is the combination of PARTY_ORIG_SYSTEM, PARTY_ORIG_SYSTEM_REFERENCE, and SITE_USE_TYPE, subject to the import process's unique index definitions.

Common Use Cases and Queries

Technical consultants and DBA teams query this table when troubleshooting address-use imports, particularly when the import completes with warning or error statuses. Typical patterns include retrieving all error rows for a batch, auditing outstanding rows before commit, and reconciling staged rows against production.

• Error extraction: SELECT INT_ROW_ID, PARTY_SITE_ID, SITE_USE_TYPE, ERROR_FLAG FROM HZ_IMP_ADDRESSUSES_SG WHERE BATCH_ID = :batch_id AND ERROR_FLAG = 'Y';
• Batch reconciliation: count staged rows by SITE_USE_TYPE and PRIMARY_FLAG to confirm expected volumes before running the import program.
• Traceability join back to the interface table on INT_ROW_ID to view the originally submitted values.
• Post-import verification joining PARTY_SITE_USE_ID to HZ_PARTY_SITE_USES to confirm each staged row resulted in a production record.

Related Objects

The primary relationship is the documented foreign key from HZ_IMP_ADDRESSUSES_SG.PARTY_SITE_USE_ID to HZ_PARTY_SITE_USES, which allows direct joins between staged and production address uses. The most significant related objects are:

• HZ_IMP_ADDRESSUSES_INT — the source interface table whose rows are loaded into this staging table.
• HZ_PARTY_SITE_USES — the production table referenced by PARTY_SITE_USE_ID.
• HZ_PARTY_SITES — parent of the party site referenced by PARTY_SITE_ID.
• HZ_PARTIES — the party resolved via PARTY_ORIG_SYSTEM and PARTY_ORIG_SYSTEM_REFERENCE.
• HZ_LOCATIONS — the location underlying the party site.
• HZ_IMP_PARTIES_SG and HZ_IMP_PARTYSITES_SG — sibling staging tables processed within the same import batch.
• TCA import concurrent programs — the programs that populate and consume HZ_IMP_ADDRESSUSES_SG during batch processing.