Search Results count_extra_trx_edw




Overview
Explore in AI Chat

Overview

FII_AR_TRX_DIST_F_D is an Oracle Applications (APPS) PL/SQL package that belongs to the Oracle Financial Intelligence / Enterprise Data Warehouse (EDW) integration layer for Oracle Receivables. Its name follows the FII (Financial Intelligence Interface) naming convention, with the "AR_TRX_DIST" segment indicating that it operates on Accounts Receivable transaction distributions and the "F_D" suffix indicating that it is a load/ETL utility package used to extract, transform, and stage Receivables distribution data for downstream reporting and analytics. In Oracle EBS 12.1.1 and 12.2.2, this package is part of the Receivables "Financials Intelligence" extraction infrastructure, which collects subledger transaction accounting data and forwards it to the Enterprise Data Warehouse / Business Intelligence infrastructure.

The package is classified as an OTHER API in the ETRM repository, meaning it is not a formally supported public API; it is an internal element of the extract subsystem. The package header is dated 2005/06/07 (version 120.1) and carries the "noship" designation, indicating that it is a utility/staging package used as part of internal data movement rather than a customer-facing interface.

Key Procedures and Functions

The package header exposes eight documented procedures. Their purposes, per the ETRM metadata, are:

  • INIT — Initialization routine invoked with an instance code. It prepares the package's session context, most likely resolving the source instance information needed for the subsequent extract steps.
  • DROP_TABLE — This is the procedure that matches the user's search term "drop_table". It accepts a table name parameter and removes the named temporary/staging table. It is used to clean up transient tables before re-creation, supporting re-runnable extract loads.
  • CREATE_OLTP_TRX_TMP_TABLE — Creates the temporary table that holds OLTP (online transaction processing, i.e., EBS source) transaction data.
  • POPULATE_OLTP_TRX_TMP_TABLE — Populates that OLTP temporary table with Receivables transaction/distribution rows.
  • CREATE_EDW_TRX_TMP_TABLE — Creates the temporary table used to hold EDW-side transaction rows.
  • FIND_EXTRA_TRX_EDW — Identifies transactions present on the EDW side that do not have a matching source record, i.e., detects "extra" transactions.
  • COUNT_EXTRA_TRX_EDW — Returns an OUT parameter containing the count of those extra transactions, supporting reconciliation/validation of the extract.
  • INSERT_STAGING — Inserts the reconciled rows into the staging table, returning a row identifier via an OUT parameter.

Tables Accessed

Two tables are documented as referenced via APPS synonyms:

  • EDW_SOURCE_INSTANCES — The source-instance registry for the Enterprise Data Warehouse extract. It is read to resolve the instance code passed to INIT, so the package knows which EBS instance/source the extract is running against.
  • ALL_TABLES — The Oracle data dictionary view of tables. It is used for existence checks so that CREATE/DROP operations on the temporary tables can be made defensive and idempotent.

Because the temporary tables created and dropped by this package are dynamically named, they are not themselves listed as static references in the metadata. Only EDW_SOURCE_INSTANCES and ALL_TABLES appear as static SQL dependencies.

Usage Notes

This package is not referenced by any other documented package (zero inbound references in the ETRM metadata). It is therefore invoked directly by the Financials Intelligence extract subsystem — typically from a concurrent program or an extract driver script — rather than being called from within a higher-level PL/SQL API. Its expected invocation order is: INIT (with the instance code), DROP_TABLE / CREATE_*_TMP_TABLE (to build clean temporary structures), POPULATE_OLTP_TRX_TMP_TABLE, FIND_EXTRA_TRX_EDW and COUNT_EXTRA_TRX_EDW (to reconcile source against EDW), and INSERT_STAGING to persist the reconciled result.

Because the package is marked "noship" and OTHER in ETRM, it should be treated as internal infrastructure. Customizations should not call these procedures directly, since their signature and behavior may change between EBS patch levels; instead, integration should target the supported extract concurrent programs. Any manual invocation of DROP_TABLE against arbitrary table names carries obvious data-loss risk and must be limited to the package's own transient staging tables.