Search Results txn_interface_id_uk

Overview

GMS_TRANSACTION_INTERFACE_ALL is a Grants Accounting (GMS) interface table that serves as an extension to PA_TRANSACTION_INTERFACE_ALL. Its documented purpose is to maintain additional award and funding-related information that the standard Projects interface does not carry. In Oracle EBS 12.1.1 and 12.2.2, this table participates in the transaction import flow, allowing grant-specific attributes — such as award linkage, funding pattern assignment, and burdenable raw cost — to be staged alongside the core expenditure records before they are processed by the Projects costing and interface programs.

The table is owned by the GMS schema and is documented as VALID, with 23 physical columns in the 12.2.2 release. From a Data Vault modeling perspective, the ETRM heuristic classifies this object as standalone, meaning it does not exhibit the multi-parent FK signature of a link table and is not a pure descriptive satellite of an existing hub — it is best treated as its own hub-like staging entity keyed by a surrogate identifier.

Key Information Stored

The physical design centers on a single surrogate primary key, supported by documented business-key candidates:

• TXN_INTERFACE_ID — the surrogate primary key of the table. It is enforced by the unique index GMS_TXN_XFACE_U1 (also referenced as the TXN_INTERFACE_ID_UK constraint), making it the primary business-key candidate and the column by which a staged GMS transaction row is uniquely identified.
• AWARD_ID — foreign key to IGF_AW_AWARD_ALL, tying the staged transaction to the specific award being charged. AWARD_NUMBER carries the corresponding human-readable award identifier.
• FUNDING_PATTERN_ID — foreign key to GMS_FUNDING_PATTERNS_ALL, identifying the funding rule set that governs how the expenditure is distributed across award funding sources.
• BATCH_NAME and TRANSACTION_SOURCE — group and identify the origin of the staged records, typically used for the import run and source system tracking. USER_TRANSACTION_SOURCE provides the user-facing equivalent.
• PROJECT_NUMBER and TASK_NUMBER — the project and task against which the cost is recorded; these key the extension record back to the standard Projects interface row.
• EXPENDITURE_TYPE, EXPENDITURE_ITEM_DATE, and EXPENDITURE_ENDING_DATE — define the nature and accounting period of the transaction.
• TRANSACTION_STATUS_CODE — tracks the processing state of the interface row (for example, whether it has been imported, rejected, or is pending).
• BURDENABLE_RAW_COST — the raw cost amount subject to burdening calculations.
• ORG_ID — the operating unit, enabling multi-org security and reporting.
• ORIG_TRANSACTION_REFERENCE and ORIGINAL_ENCUMBRANCE_ITEM_ID — provide traceability to the originating transaction and encumbrance item.

Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) provide audit tracking.

Common Use Cases and Queries

Typical usage focuses on staging, monitoring, and reconciling grant-related expenditure imports. Common patterns include:

• Identifying stuck or failed interface rows: SELECT TXN_INTERFACE_ID, PROJECT_NUMBER, TASK_NUMBER, TRANSACTION_STATUS_CODE FROM GMS_TRANSACTION_INTERFACE_ALL WHERE TRANSACTION_STATUS_CODE = 'REJECTED';
• Joining to the standard Projects interface on the shared interface identifier to correlate base and GMS extension data.
• Reporting expenditures by award: joining AWARD_ID to IGF_AW_AWARD_ALL, or joining FUNDING_PATTERN_ID to GMS_FUNDING_PATTERNS_ALL to validate funding distribution before import.
• Reconciling imported volumes per batch using BATCH_NAME and EXPENDITURE_ENDING_DATE.

Related Objects

• PA_TRANSACTION_INTERFACE_ALL — the base interface table this object extends; joined on TXN_INTERFACE_ID.
• IGF_AW_AWARD_ALL — parent of AWARD_ID; supplies award attributes.
• GMS_FUNDING_PATTERNS_ALL — parent of FUNDING_PATTERN_ID; defines funding allocation rules.
• GMS_FUNDING_PATTERNS_ALL dependent funding lines used during cost distribution.
• Projects interface import concurrent programs that consume the staged rows and move them into the permanent expenditure tables.