Results for “ar_transactions_rep_itf”

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

Overview

AR_TRANSACTIONS_REP_ITF is a Report Exchange interface table owned by the AR (Receivables) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. As its description states, it serves as the Report Exchange interface table for the Receivables Transactions reports. It is a staging and integration object rather than a transactional base table: data is populated, consumed by reporting programs, and generally not treated as a system of record for receivables entries. The table carries 55 documented columns in the 12.2.2 physical schema, organized to present transaction header, line, accounting distribution, payment schedule, customer, and batch context in a single denormalized structure suited to report generation.

Heuristic Data Vault classification, mined from the foreign key structure, indicates a link modeling suggestion. The table's identity is built around REQUEST_ID together with the convention columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN, and it references multiple independent business entities — customers, sites, transaction types, terms, batches, and receipt methods. This link-like profile reflects its role as an associative interface between the Receivables transactional world and its reporting consumers, rather than a pure hub or satellite.

Key Information Stored

The most significant columns fall into four groups:

• Run and identity context: REQUEST_ID ties each row to a concurrent program submission; ORGANIZATION_NAME and ORG_ID supply multi-org context; FUNCTIONAL_CURRENCY_CODE establishes the reporting currency.
• Transaction header: CUSTOMER_TRX_ID and TRX_NUMBER identify the receivable transaction; TRX_DATE, TRX_CURRENCY, EXCHANGE_RATE, EXCHANGE_DATE, and EXCHANGE_TYPE describe currency conversion; TRX_AMOUNT and TRX_ACCTD_AMOUNT hold entered and accounted amounts; TRX_DUE_DATE and RECEIVABLES_GL_DATE provide aging and accounting dates.
• Line and accounting detail: CUSTOMER_TRX_LINE_ID, LINK_TO_CUST_TRX_LINE_ID, INVENTORY_ITEM, and ITEM_DESCRIPTION describe the line; CUST_TRX_LINE_GL_DIST_ID, ACCOUNT, ACCOUNT_DESC, BALANCE, NATACCT, and NATACCT_DESC present distribution accounting; REC_CUST_TRX_LINE_GL_DIST_ID and the REC_* columns mirror the corresponding receivable-side distribution values.
• Customer, setup, and batch references: SHIP_TO_CUSTOMER_ID, SHIP_TO_SITE_USE_ID, BILL_TO_CUSTOMER_ID, BILL_TO_SITE_USE_ID, CUST_TRX_TYPE_ID, TERM_ID, DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE, RECEIPT_METHOD_ID, BATCH_ID, and BATCH_SOURCE_ID.

No explicit primary key is documented; REQUEST_ID combined with the transaction and line identifiers (CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID) serves as the practical row identifier, while TRX_NUMBER and DOC_SEQUENCE_VALUE act as business-key candidates for users. The oracle-generated surrogate identifiers (CUSTOMER_TRX_ID, CUST_TRX_LINE_GL_DIST_ID) are the reliable join keys.

Common Use Cases and Queries

Primary usage is report extraction. A typical pattern retrieves all interface rows for a run, joining to reference tables for descriptive attributes:

• Run listing: SELECT trx_number, trx_date, trx_amount, trx_acctd_amount FROM ar.ar_transactions_rep_itf WHERE request_id = :request_id ORDER BY trx_number;
• Accounting extract: select ACCOUNT, ACCOUNT_DESC, BALANCE, and BALANCE_DESC grouped by CUSTOMER_TRX_LINE_GL_DIST_ID to reconcile distributions against the transaction amount.
• Customer enrichment: join BILL_TO_CUSTOMER_ID to HZ_CUST_ACCOUNTS for customer name, and BILL_TO_SITE_USE_ID or SHIP_TO_SITE_USE_ID to HZ_CUST_SITE_USES_ALL for site details.
• Setup validation: link CUST_TRX_TYPE_ID to RA_CUST_TRX_TYPES_ALL, TERM_ID to RA_TERMS_B, and BATCH_ID to RA_BATCHES_ALL to confirm report values against setup.
• Run cleanup: delete rows by REQUEST_ID after the report completes to prevent stale data from inflating later runs.

Because this is a report-exchange object, it should not be used as a source for reconciliation against RA_CUSTOMER_TRX_ALL or RA_CUST_TRX_LINE_GL_DIST_ALL without validating freshness and run scope.

Related Objects

• HZ_CUST_ACCOUNTS — joined via BILL_TO_CUSTOMER_ID and SHIP_TO_CUSTOMER_ID; supplies customer identity.
• HZ_CUST_SITE_USES_ALL — joined via BILL_TO_SITE_USE_ID and SHIP_TO_SITE_USE_ID; supplies site-use context.
• RA_CUST_TRX_TYPES_ALL — joined via CUST_TRX_TYPE_ID; classifies the transaction type.
• RA_TERMS_B — joined via TERM_ID; provides payment terms.
• FND_DOCUMENT_SEQUENCES — joined via DOC_SEQUENCE_ID; supports document numbering.
• AR_RECEIPT_METHODS — joined via RECEIPT_METHOD_ID; identifies the receipt method.
• RA_BATCHES_ALL and RA_BATCH_SOURCES_ALL — joined via BATCH_ID and BATCH_SOURCE_ID; provide batch context.

These foreign key relationships define the table's link-style integration role and are its principal documented dependencies.