Search Results gms_txn_xface_u1

Overview

GMS.GMS_TRANSACTION_INTERFACE_ALL is an Oracle E-Business Suite interface staging table owned by the GMS (Grants Management System) schema. It stores supplementary transaction attributes required when transaction rows created in PA_TRANSACTION_INTERFACE_ALL are pushed into Grants Accounting. The table is populated during the grants transaction interface cycle, validated and processed by the Grants Accounting concurrent programs, and then consumed to create the corresponding award, encumbrance, and actual cost distributions. In EBS 12.1.1 and 12.2.2 the object retains the same physical properties: it resides in the APPS_TS_INTERFACE tablespace with PCT FREE 10, reflecting its role as a short-lived staging object rather than a permanent ledger table.

The ETRM relationship metadata classifies this object heuristically as a standalone table, meaning it does not sit in a clear hub, link, or satellite position within a dimensional model. For modeling purposes it is best treated as a link-style staging entity that connects a project transaction (keyed in PA_TRANSACTION_INTERFACE_ALL) to an award and a funding pattern, rather than as a conformed dimension. The single unique index, GMS_TXN_XFACE_U1, combined with the shared identifier with PA_TRANSACTION_INTERFACE_ALL, confirms this bridging role.

Key Information Stored

The table contains 23 documented columns. The most significant are listed below; several older descriptive columns are explicitly marked obsolete in the ETRM metadata and are retained only for backward compatibility.

• TXN_INTERFACE_ID (NUMBER 15, mandatory) — the surrogate primary key and the business-key candidate enforced by unique index GMS_TXN_XFACE_U1. It is the same value as TXN_INTERFACE_ID in PA_TRANSACTION_INTERFACE_ALL, which is the join key between the two tables.
• TRANSACTION_TYPE — identifies the nature of the interfaced transaction; E denotes Encumbrance and A denotes Actuals.
• BURDENABLE_RAW_COST (NUMBER 22) — the raw cost amount eligible for burdening, used when the transaction is costed and burdened during interface processing.
• FUNDING_PATTERN_ID (NUMBER 15) — foreign key to GMS_FUNDING_PATTERNS_ALL, identifying the funding pattern that governs distribution of the award amount.
• AWARD_ID (NUMBER 15, obsolete) — foreign key to IGF_AW_AWARD_ALL; superseded in practice by AWARD_NUMBER, the business-facing award identifier.
• ORIGINAL_ENCUMBRANCE_ITEM_ID — references the original encumbrance line when the row represents an adjustment or relief of a prior commitment.
• ORG_ID (obsolete) — the operating unit context, now generally derived from the referenced award or project.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE follow standard EBS Who column conventions and are essential for reconstructing when a staging row was loaded or re-processed.
• Obsolete descriptive columns — BATCH_NAME, TRANSACTION_SOURCE, EXPENDITURE_ENDING_DATE, EXPENDITURE_ITEM_DATE, PROJECT_NUMBER, TASK_NUMBER, EXPENDITURE_TYPE, TRANSACTION_STATUS_CODE, ORIG_TRANSACTION_REFERENCE, USER_TRANSACTION_SOURCE, and SYSTEM_LINKAGE. These should not be used in new development; equivalent values are sourced from PA_TRANSACTION_INTERFACE_ALL or derived at processing time.

Common Use Cases and Queries

The primary operational use case is troubleshooting the Grants Accounting interface. Support and implementation teams query this table to determine why a project transaction failed to reach awards processing, and they join back to PA_TRANSACTION_INTERFACE_ALL to recover the descriptive detail that this table no longer stores.

• Reconciling staged rows to project transactions: SELECT g.txn_interface_id, g.transaction_type, g.burdenable_raw_cost, p.project_number, p.task_number FROM gms.gms_transaction_interface_all g, pa.pa_transaction_interface_all p WHERE g.txn_interface_id = p.txn_interface_id;
• Identifying award and funding distributions for a batch: join GMS_FUNDING_PATTERNS_ALL on FUNDING_PATTERN_ID and IGF_AW_AWARD_ALL on the award key to confirm the correct award and pattern were attached before submission.
• Isolating encumbrance rows for commitment reporting: filter on TRANSACTION_TYPE = 'E' and aggregate BURDENABLE_RAW_COST by AWARD_NUMBER.
• Audit and aging analysis: compare CREATION_DATE and LAST_UPDATE_DATE against the interface run date to detect rows that were staged but never processed or that were re-borne through ORIGINAL_ENCUMBRANCE_ITEM_ID.

The non-unique indexes GMS_TXN_XFACE_N1 (BATCH_NAME, TRANSACTION_SOURCE, TRANSACTION_STATUS_CODE) and GMS_TXN_XFACE_N2 (ORIG_TRANSACTION_REFERENCE) support historical query patterns only, since these columns are obsolete.

Related Objects

• PA.PA_TRANSACTION_INTERFACE_ALL — the parent staging table; joined on TXN_INTERFACE_ID, which supplies the project, task, expenditure type, and date attributes no longer held locally.
• IGF_AW_AWARD_ALL — referenced through AWARD_ID; the award definition against which amounts are distributed.
• GMS_FUNDING_PATTERNS_ALL — referenced through FUNDING_PATTERN_ID; defines how the award amount is split across funding sources.
• PA_TRANSACTION_INTERFACE and the Grants Accounting transaction interface concurrent programs — the processing layer that reads these rows, validates them, and creates actuals and encumbrances.
• GMS_AWARD_DISTRIBUTIONS / GMS_TRANSACTION_INTERFACE_ALL-related grant accounting views — downstream consumers used in award and expenditure reporting.