Search Results gl_ussgl_transaction_codes_u1

Overview

GL.GL_USSGL_TRANSACTION_CODES is a General Ledger reference table that stores user-defined United States Standard General Ledger (USSGL) transaction codes. These codes instruct Oracle General Ledger how to generate budgetary transactions from proprietary (financial) transactions, enabling federal agencies to satisfy USSGL reporting requirements. Each row defines one transaction code within the context of a specific chart of accounts, forming a controlled vocabulary that drives budgetary-to-proprietary posting logic.

The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10. It carries an FND Design Data registration under SQLGL.GL_USSGL_TRANSACTION_CODES, and its status is VALID in both Oracle EBS 12.1.1 and 12.2.2. Under a heuristic Data Vault classification, this object is hub-leaning: it holds the durable business concept of a USSGL transaction code, with the composite key serving as the natural business key. The standard WHO columns and attribute segments attach satellite-style descriptive context to that hub.

Key Information Stored

The table's identity is anchored by its composite primary key, GL_USSGL_TRANSACTION_CODES_PK, defined on CHART_OF_ACCOUNTS_ID and USSGL_TRANSACTION_CODE. The unique index GL_USSGL_TRANSACTION_CODES_U1 mirrors this same column pair (CHART_OF_ACCOUNTS_ID, USSGL_TRANSACTION_CODE) in the APPS_TS_TX_IDX tablespace, confirming the business-key candidate.

• CHART_OF_ACCOUNTS_ID — Key flexfield structure defining column; scopes each transaction code to a chart of accounts.
• USSGL_TRANSACTION_CODE — The government transaction code itself (VARCHAR2(30)), the core business value.
• DESCRIPTION — Human-readable description of the transaction code (VARCHAR2(240)).
• START_DATE_ACTIVE / END_DATE_ACTIVE — Define the validity window during which the code may be used.
• ASSOC_INTEREST_USSGL_CODE / ASSOC_DISCOUNT_USSGL_CODE — Associated USSGL codes supporting interest and discount budgetary treatment.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking creation and modification.
• ATTRIBUTE1–ATTRIBUTE15 and CONTEXT — Descriptive flexfield segments providing extensibility without schema change.

Common Use Cases and Queries

Typical scenarios include validating an active transaction code before posting, reporting the code inventory by chart of accounts, and auditing codes for expiring validity windows. A representative active-code query follows:

SELECT ussgl_transaction_code, description, start_date_active, end_date_active
FROM gl.gl_ussgl_transaction_codes
WHERE chart_of_accounts_id = :coa_id
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE);

Flexfield-based reporting can filter on ATTRIBUTE segments, while a join to GL_USSGL_ACCOUNT_PAIRS resolves the budgetary pairing logic for a given code. Reconciliation and federal reporting extracts commonly join back to the chart of accounts via CHART_OF_ACCOUNTS_ID to resolve the accounting flexfield structure name.

Related Objects

The most significant dependent object is GL_USSGL_ACCOUNT_PAIRS, which references this table through CHART_OF_ACCOUNTS_ID, linking transaction codes to their account-pair definitions. Joining on that column connects the two tables. The unique index GL_USSGL_TRANSACTION_CODES_U1 and the primary key GL_USSGL_TRANSACTION_CODES_PK enforce uniqueness and support lookup access. Descriptive flexfield metadata registered under SQLGL drives the ATTRIBUTE segment definitions. The chart of accounts key flexfield structure, referenced by CHART_OF_ACCOUNTS_ID, provides the accounting context, and standard WHO audit columns tie rows to application user and login records. Because the object is hub-leaning, downstream budgetary transaction generation and USSGL reporting programs depend on it as the authoritative source of valid transaction codes.