Results for “pa_transaction_interface_all”

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

Overview

PA_TRANSACTION_INTERFACE_ALL is the staging interface table in the Oracle Projects (PA) module that receives expenditure transactions from external sources before they are validated and imported into Oracle Projects as actual expenditure items. This table forms the boundary between feeder systems and the Projects transaction engine. External systems — including Oracle Purchasing, Oracle Payables, Oracle Inventory, Oracle Work in Process, Oracle Time and Labor, and third-party labor or cost collection applications — write candidate expenditure records into this table, and Oracle Projects' Transaction Import process reads them, validates them against project, task, employee, and expenditure type setup, then creates the corresponding PA_EXPENDITURE_ITEMS_ALL and PA_EXPENDITURES_ALL records.

Functionally the table is a batch staging area. Transactions are grouped by BATCH_NAME, processed through Transaction Import, and marked with a TRANSACTION_STATUS_CODE of P (processed), R (rejected), or U (unprocessed). Rejected transactions remain in the table with a TRANSACTION_REJECTION_CODE so they can be corrected and resubmitted. From a Data Vault modeling perspective, the metadata classifies this object as satellite-leaning: its grain is one row per incoming interface transaction, keyed by a surrogate TXN_INTERFACE_ID, with foreign-key references to originating hubs such as PROJECT_ID and PERSON_ID. It is best modeled as a satellite attached to the project and person hubs, with transaction-heavy descriptive and measure attributes.

Key Information Stored

The 119 columns of this table fall into identification, source attribution, financial measure, currency conversion, and status control groups. The most significant columns are:

• TXN_INTERFACE_ID — surrogate primary key (PA_TRANSACTION_INTERFACE_PK), populated by PA_TRANSACTION_INTERFACE_S. It is the unique index PA_TRANSACTION_INTERFACE_U1 and the definitive row identifier.
• TRANSACTION_SOURCE and USER_TRANSACTION_SOURCE — the originating feeder source; TRANSACTION_SOURCE is a foreign key to PA_TRANSACTION_SOURCES and drives import logic.
• BATCH_NAME — groups importable transactions into logical batches.
• EXPENDITURE_ENDING_DATE and EXPENDITURE_ITEM_DATE — the accounting period and transaction date for expenditure.
• PROJECT_NUMBER / PROJECT_ID — the target project; PROJECT_NUMBER is the user-facing identifier, PROJECT_ID (FK to PA_PROJECTS_ALL) the internal key.
• TASK_NUMBER / TASK_ID — the target task, referencing PA_TASKS.
• EXPENDITURE_TYPE — FK to PA_EXPENDITURE_TYPES, determining cost treatment.
• QUANTITY, RAW_COST, BURDENED_COST — the core quantity and cost measures written into Projects.
• EMPLOYEE_NUMBER, PERSON_ID, PERSON_BUSINESS_GROUP_ID — labor identity information for the transaction.
• NON_LABOR_RESOURCE, NON_LABOR_RESOURCE_ORG_ID — non-labor resource attribution (vendor, item, asset).
• VENDOR_ID, PO_HEADER_ID, PO_LINE_ID, WIP_RESOURCE_ID — traceability keys to purchasing and manufacturing source documents.
• TRANSACTION_STATUS_CODE / TRANSACTION_REJECTION_CODE — the processing state and reason for rejection.
• DENOM_RAW_COST / DENOM_BURDENED_COST / DENOM_CURRENCY_CODE — transacted currency amounts.
• PROJECT_RAW_COST, PROJECT_EXCHANGE_RATE, PROJECT_RATE_TYPE — converted project currency values.
• GL_DATE, DR_CODE_COMBINATION_ID, CR_CODE_COMBINATION_ID — accounting distribution information.

The unique index confirms TXN_INTERFACE_ID is the only business-key candidate; all other fields are descriptive or referential.

Common Use Cases and Queries

Typical uses center on loading, monitoring, and reconciling the import stream. A loader would insert validated rows and set TRANSACTION_STATUS_CODE to U. An analyst monitoring a batch would query:

SELECT transaction_source, transaction_status_code, COUNT(*)
FROM   pa_transaction_interface_all
WHERE  batch_name = :batch
GROUP  BY transaction_source, transaction_status_code;

To retrieve rejected rows for correction:

SELECT txn_interface_id, project_number, task_number, expenditure_type,
       raw_cost, transaction_rejection_code
FROM   pa_transaction_interface_all
WHERE  transaction_status_code = 'R'
AND    batch_name = :batch;

Joining the interface to the final expenditure to confirm import success uses EXPENDITURE_ITEM_ID, which points to the created record in PA_EXPENDITURE_ITEMS_ALL. Currency conversion reporting, source-system traceability (via PO_HEADER_ID or VENDOR_ID), and purge-cycle analysis on CREATION_DATE are other common patterns.

Related Objects

• PA_TRANSACTION_SOURCES — joined on TRANSACTION_SOURCE; defines the originating source and its import options.
• PA_PROJECTS_ALL — joined on PROJECT_ID; the destination project for imported transactions.
• PA_TASKS — joined on TASK_ID; the destination task.
• PA_EXPENDITURE_TYPES — joined on EXPENDITURE_TYPE; categorizes and validates the expenditure.
• PA_EXPENDITURES_ALL — referenced via EXPENDITURE_ID; the header expenditure row created upon import.
• PA_EXPENDITURE_ITEMS_ALL — referenced via EXPENDITURE_ITEM_ID; the detail item created upon import.
• PA_MC_TXN_INTERFACE_ALL — multi-currency child table joined on TXN_INTERFACE_ID, holding per-currency conversion rows for the same interface transaction.
• PO_VENDORS, PO_HEADERS_ALL, PO_LINES_ALL, BOM_RESOURCES — referenced through VENDOR_ID, PO_HEADER_ID, PO_LINE_ID, and WIP_RESOURCE_ID for source document traceability.
• PA_PROJECT_ASSIGNMENTS — joined on ASSIGNMENT_ID; holds the assignment context for the labor transaction.