Search Results exp_report_header_id

Overview

The FII_AP_INV_STG table is a staging table within the Oracle Financial Intelligence (FII) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. Its primary role is to serve as an interim data repository during the extraction, transformation, and loading (ETL) process for Accounts Payable (AP) invoice data destined for the FII data warehouse. The table consolidates key transactional and accounting attributes from the AP subledger, such as invoice distributions, supplier details, project accounting linkages, and currency amounts, to facilitate the subsequent population of FII's dimensional models and cubes for financial reporting and analysis. As indicated by its storage in the APPS_TS_INTERFACE tablespace and the associated warning, it is an internal object designed for use by standard Oracle Applications programs and is not intended for direct operational access or custom integration.

Key Information Stored

The table captures a comprehensive set of attributes for each invoice distribution line. Key columns include transactional identifiers like INVOICE_ID, INVOICE_DISTRIBUTION_ID, and PO_DISTRIBUTION_ID. It stores financial amounts in both transaction (AMOUNT_T) and base (AMOUNT_B) currencies, alongside conversion rates (PRIM_CONVERSION_RATE, SEC_CONVERSION_RATE). Accounting dimensions are represented by DIST_CCID (Code Combination ID), CHART_OF_ACCOUNTS_ID, and ORG_ID. The table also holds critical metadata such as the ACCOUNT_DATE, creation details (INV_DIST_CREATION_DATE), and status flags for posting (POSTED_FLAG) and approval (APPROVED_FLAG). Notably, the user's search term EXP_REPORT_HEADER_ID is a foreign key column (not fully listed in the excerpt) linking to expense report headers, indicating the table also stages invoice data related to employee expenses.

Common Use Cases and Queries

The primary use case is supporting the FII AP Invoice Facts ETL process. Data is populated into this table from AP transactional tables, transformed, and then loaded into FII fact tables. Analysts and administrators might query this staging table for ETL diagnostics or preliminary data validation before formal cube refreshes. A sample query to analyze staged invoice distributions for a specific period and organization would be:

• SELECT org_id, invoice_type, account_date, SUM(amount_b) FROM fii.fii_ap_inv_stg WHERE account_date BETWEEN :p_start_date AND :p_end_date AND org_id = :p_org_id GROUP BY org_id, invoice_type, account_date ORDER BY account_date;

Another common pattern involves joining to referenced tables, such as querying staged distributions linked to a specific expense report using the EXP_REPORT_HEADER_ID foreign key relationship.

Related Objects

Based on the provided relationship data, FII_AP_INV_STG has documented foreign key dependencies to core EBS transactional tables, establishing its role in the data integration flow. The specific relationships are:

• AP_EXPENSE_REPORT_HEADERS_ALL: The column FII.FII_AP_INV_STG.EXP_REPORT_HEADER_ID references AP_EXPENSE_REPORT_HEADERS_ALL. This links staged invoice distributions to their source employee expense reports.
• PO_RELEASES_ALL: The column FII.FII_AP_INV_STG.PO_RELEASE_ID references PO_RELEASES_ALL. This links staged invoice data to specific purchase order releases for matched invoices.

As a staging table, it is also intrinsically related to other FII dimension and fact tables within the data warehouse schema, which consume its processed data.