Search Results igi_exp_ap_trans_all




Overview
Explore in AI Chat

Overview

The IGI.IGI_EXP_AP_TRANS_ALL table is a transactional table in the Oracle E-Business Suite product IGI – Public Sector Financials International. Per the ETRM metadata, its documented purpose is to store the payables transactions that are held within a Distribution Unit (DU). In practice, this table acts as the intersection between Payables invoices and the DU construct used by Public Sector Financials, providing the association that determines which invoices (and related payables transactions) belong to a given distribution unit. The table is owned by the IGI schema and is classified as VALID.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign key structure is link. This is appropriate given that the table's two most significant foreign keys connect Payables invoices (AP_INVOICES_ALL) to distribution units (IGI_EXP_DUS_ALL). It is worth noting that the table also carries the standard EBS WHO/Audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and a set of descriptive ATTRIBUTE flex columns, which suggests a hybrid link-plus-satellite role rather than a purely associative link. The Data Vault classification should therefore be treated as a modeling suggestion.

Key Information Stored

The table contains 25 documented columns. The most significant are listed below, distinguished by their role in the physical model:

  • AP_TRAN_ID — the surrogate primary key, enforced by the unique index/constraint IGI_EXP_AP_TRANS_PK (and additionally by IGI_EXP_AP_TRANS_U1). It uniquely identifies each payables-transaction-within-DU record.
  • INVOICE_ID — foreign key to AP_INVOICES_ALL. Identifies the Payables invoice that is being associated with a distribution unit.
  • DU_ID — foreign key to IGI_EXP_DUS_ALL. Identifies the Distribution Unit that holds the transaction.
  • ORG_ID — the operating unit (multi-org) identifier, enabling operating-unit security and reporting partitioning.
  • CREATION_DATE, CREATED_BY — standard audit columns recording when and by whom the row was created.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns recording the most recent change.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield (DFF) columns used to capture additional, context-specific information configured by the implementing organization.

The metadata documents AP_TRAN_ID as the business-key candidate via the unique index IGI_EXP_AP_TRANS_U1. No other unique indexes are documented.

Common Use Cases and Queries

The primary use case is determining which Payables invoices are held within a particular Distribution Unit, or conversely, which DU a given invoice is associated with. This supports Public Sector Financials reporting, fund/appropriation tracking, and DU-level transaction auditing.

A typical query joins the table to its two foreign-key parents:

  • Joining IGI_EXP_AP_TRANS_ALL t to AP_INVOICES_ALL i on t.INVOICE_ID = i.INVOICE_ID to obtain invoice number, supplier, amount, and payment status alongside the DU association.
  • Joining to IGI_EXP_DUS_ALL d on t.DU_ID = d.DU_ID to resolve the distribution unit name/number and its attributes.
  • Filtering by ORG_ID to enforce operating-unit security, and by LAST_UPDATE_DATE for incremental or change-based extracts feeding a data warehouse.
  • Interrogating ATTRIBUTE_CATEGORY and the ATTRIBUTE1–15 columns to surface client-specific DFF values for analytical reporting.

Because the table is classified as a link, it is a natural target for ETL into a Data Vault model, where AP_TRAN_ID would become the link hash key and the invoice/DU references used to build the hub keys.

Related Objects

The following objects are most significant by virtue of the documented foreign key relationships and their role in the Public Sector Financials / Payables model:

  • AP_INVOICES_ALL — joined via IGI_EXP_AP_TRANS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID; the Payables invoice header store.
  • IGI_EXP_DUS_ALL — joined via IGI_EXP_AP_TRANS_ALL.DU_ID = IGI_EXP_DUS_ALL.DU_ID; the Distribution Unit master.
  • IGI_EXP_AP_TRANS_PK / IGI_EXP_AP_TRANS_U1 — the primary key constraint and unique index on AP_TRAN_ID.
  • Other IGI_EXP_* Public Sector Financials tables (for example other DU-related transaction and setup tables) that share the DU_ID foreign key to IGI_EXP_DUS_ALL.