Search Results hz_imp_finreports_sg




Overview
Explore in AI Chat

Overview

HZ_IMP_FINREPORTS_SG is a TCA (Trading Community Architecture) internal staging table owned by the AR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is not a user-facing entity; rather, it functions as the intermediate processing layer that TCA's bulk import infrastructure uses to transform rows loaded into the interface table HZ_IMP_FINREPORTS_INT before they are validated, deduplicated, and merged into the base financial reports entity. The "_SG" suffix denotes the "staging" generation table within the standard TCA import pattern (INT → SG → base table), where the SG table carries a batch identifier, an action flag, and an error flag so each row can be individually diagnosed and reprocessed by concurrent import programs.

The table's central purpose is to hold third-party financial report information (statements of financial position, report periods, issue dates, and issuing authorities) associated with a party, prior to its definitive persistence in HZ_FINANCIAL_REPORTS. In Data Vault modeling terms, the heuristic classification returned for this object is standalone, meaning it exhibits no subordinate dependencies within the mined foreign-key structure; for modeling purposes it is best treated as a transient link-style staging object whose durable equivalent is the base financial reports entity referenced via FINANCIAL_REPORT_ID.

Key Information Stored

The table is documented with 15 columns. The most significant are grouped below.

  • BATCH_ID — the processing batch identifier that ties every staged row to a specific run of the import program; it is the primary grouping key for troubleshooting and for purging completed batches.
  • BATCH_MODE_FLAG and ACTION_FLAG — control columns that determine how the row is treated during processing (for example, insert versus update versus delete semantics).
  • ERROR_FLAG — marks rows that failed validation or merge, allowing the error report to isolate offending records without scanning the whole batch.
  • INT_ROW_ID — the linkage back to the originating row in HZ_IMP_FINREPORTS_INT, preserving lineage from interface to staging.
  • FINANCIAL_REPORT_ID — the foreign key referencing HZ_FINANCIAL_REPORTS; this is the business join that connects the staged record to its persisted counterpart and is the sole documented foreign key on the table.
  • PARTY_ID, PARTY_ORIG_SYSTEM, PARTY_ORIG_SYSTEM_REFERENCE — the party identification triple used to resolve the owning party, either by internal identifier or by the source-system key combination.
  • DATE_REPORT_ISSUED, REPORT_START_DATE, REPORT_END_DATE, ISSUED_PERIOD — the temporal attributes describing when the report was issued and the period it covers.
  • TYPE_OF_FINANCIAL_REPORT and DOCUMENT_REFERENCE — the classification of the report and the external document identifier supplied by the source.

The documented physical schema does not expose an explicit single-column surrogate primary key in the metadata; in practice the batch/row combination (BATCH_ID with INT_ROW_ID) acts as the working uniqueness handle, while FINANCIAL_REPORT_ID and the party/temporal attributes form the business-key candidates.

Common Use Cases and Queries

Typical usage centers on monitoring and diagnosing bulk loads of financial report data for parties. A common operational query inspects the error state of the most recent batch:

  • SELECT batch_id, COUNT(*) total_rows, SUM(DECODE(error_flag,'Y',1,0)) error_rows FROM hz_imp_finreports_sg GROUP BY batch_id ORDER BY batch_id DESC;
  • Joining staged rows to the base entity to confirm which records were successfully anchored: SELECT s.int_row_id, s.party_id, f.financial_report_id FROM hz_imp_finreports_sg s, hz_financial_reports f WHERE s.financial_report_id = f.financial_report_id AND s.batch_id = :batch_id;
  • Tracing a staged row back to its interface origin via INT_ROW_ID to reconcile source data with the processed result.

Reporting scenarios include supplier or customer financial-health assessments, where issued financial statements captured for a party are analyzed by issued period or report type. Data stewards also use the table to purge obsolete batches after successful merge, and to reprocess errored batches after correcting source data in the interface table.

Related Objects

  • HZ_IMP_FINREPORTS_INT — the interface table whose rows are loaded and transformed into this staging table; linked through INT_ROW_ID.
  • HZ_FINANCIAL_REPORTS — the base TCA entity that ultimately receives the staged financial report records; joined via FINANCIAL_REPORT_ID.
  • HZ_PARTIES — the party master providing PARTY_ID resolution and descriptive context for the owning party.
  • HZ_ORIG_SYSTEMS — reference table underpinning the PARTY_ORIG_SYSTEM / PARTY_ORIG_SYSTEM_REFERENCE identification pattern.
  • TCA import concurrent programs — the batch drivers that read this table, apply validation, and move data into HZ_FINANCIAL_REPORTS.
  • HZ_IMP_FINREPORTS_SG error reporting views — interface and error report outputs that expose ERROR_FLAG rows per BATCH_ID.