Results for “ap_tran_id”

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

Overview

IGI_EXP_DU_LINES_V is a BI Publisher / Oracle EBS view owned by the APPS schema within the IGI — Public Sector Financials International product family. Its documented purpose is to return the details of all Payables and Receivables transactions that are included in Dialog Units. A Dialog Unit (DU) is the IGI construct used by public sector organizations — particularly those operating under U.S. federal and similar accounting frameworks — to group related financial transactions for reconciliation, reporting, and audit purposes. The view therefore acts as a consolidated line-level reporting surface spanning both the sub-ledger receivable side (Oracle Receivables) and the sub-ledger payable side (Oracle Payables), while resolving each transaction to the Dialog Unit to which it has been assigned.

The view is registered as VALID in the APPS schema and is designed to be queried directly by reports, extracts, and integration interfaces built on top of ETRM. Because it joins across both AP and AR, it is the natural source for cross-module reconciliation reporting where a DU is the unit of analysis rather than an individual invoice or transaction.

Underlying Base Objects

The documented view text shows that IGI_EXP_DU_LINES_V is built as a UNION of two query branches, one for AR transactions and one for AP transactions. The AR branch selects from IGI_EXP_AR_TRANS (a synonym), joined to FND_APPLICATION_VL, RA_CUSTOMER_TRX_V, IGI_EXP_DUS, and IGI_EXP_AR_TRX_AMOUNTS_V. The AP branch follows the mirror pattern over the AP equivalents.

The documented metadata for the 12.2.2 environment lists the referenced base objects as: AP_INVOICES_PKG, AP_INVOICES_UTILITY_PKG, AP_INVOICES_V, AP_PREPAY_UTILS_PKG, ARH_ADDR_PKG, ARPT_SQL_FUNC_UTIL, FND_APPLICATION_VL, FND_GLOBAL, FND_PROFILE, FND_USER_AP_PKG, HR_GENERAL, HR_SECURITY, IGI_EXP_AP_TRANS (synonym), IGI_EXP_AR_TRANS (synonym), IGI_EXP_AR_TRX_AMOUNTS_V, PA_TASK_UTILS, PA_UTILS4, and RA_CUSTOMER_TRX_V. The presence of HR_SECURITY and FND_GLOBAL indicates that the underlying views enforce organization-level and responsibility-level security, so query results are filtered to the operating unit context of the session. The AP_INVOICES_PKG and AP_PREPAY_UTILS_PKG references confirm that invoice-level derived attributes (such as payment scheduling and prepayment handling) are exposed through the AP branch.

Key Columns

The view exposes a consistent column set across both UNION branches so that AP and AR rows can be reported together:

• ROW_ID, DU_ID — the row identifier and the Dialog Unit to which the transaction is assigned; DU_ID is the primary grouping key.
• APPLICATION, APPLICATION_ID — the owning application; the AR branch hard-codes 222 (Oracle Receivables).
• TRANSACTION_TYPE_DESC, TRANSACTION_TYPE — the human-readable class name and the underlying class code for the transaction.
• TRANSACTION_NUMBER, DESCRIPTION — the transaction reference and its comments/description.
• GL_DATE — the general ledger date used for accounting and period assignment.
• THIRD_PARTY, THIRD_PARTY_SITE, THIRD_PARTY_ID, THIRD_PARTY_SITE_ID — the customer or supplier name, site, and their identifiers.
• AMOUNT, CURRENCY_CODE — the transaction amount and its currency.
• ORG_ID — the operating unit, used for multi-org security.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1 … ATTRIBUTE15 — the flexible descriptive flexfield columns carried through from the transaction.
• AP_TRAN_ID, INVOICE_ID — populated for the AP branch; hard-coded to -999 on the AR branch.
• AR_TRAN_ID, CUSTOMER_TRX_ID — populated for the AR branch; the corresponding AP placeholders mirror the -999 convention.

Common Use Cases and Queries

The view is typically queried where a DU-level listing of both payable and receivable activity is required — for example, DU reconciliation reports, audit schedules, or extracts feeding an external reporting warehouse. A simple query filters by Dialog Unit and operating unit:

• SELECT du_id, transaction_number, gl_date, third_party, amount, currency_code FROM igi_exp_du_lines_v WHERE du_id = :p_du_id AND org_id = :p_org_id ORDER BY gl_date;
• SELECT application, transaction_type_desc, SUM(amount) FROM igi_exp_du_lines_v WHERE du_id = :p_du_id GROUP BY application, transaction_type_desc;
• SELECT transaction_number, third_party, amount, gl_date FROM igi_exp_du_lines_v WHERE gl_date BETWEEN :p_from AND :p_to AND org_id = :p_org_id;

Because the view is secured through HR_SECURITY and FND_GLOBAL, callers should set the operating unit context using FND_GLOBAL.APPS_INITIALIZE or an equivalent session initialization before querying, and should filter on ORG_ID explicitly when running outside a secured responsibility. Its UNION structure means that AP_TRAN_ID and AR_TRAN_ID are mutually exclusive; report logic should test APPLICATION or APPLICATION_ID to distinguish payables rows from receivables rows.