Search Results psa_trx_pk

Overview

PSA_TRX_TYPES_ALL is a Public Sector Financials (PSA) application table within the Oracle E-Business Suite 12.1.1 and 12.2.2 schema, owned by the PSA schema. Its documented purpose is to store Public Sector Transaction Types information. In practice, this table acts as a master reference list of the transaction type codes used by Public Sector Financials processes to classify and drive the behavior of monetary and non-monetary transactions.

The table is defined with a physical column count of 22 and is unique across the PSA module implementation. Based on the heuristic Data Vault classification supplied in the metadata, the object is identified as a standalone entity with no documented foreign key relationships. In Data Vault modeling terms, this pattern is most consistent with a hub structure: a business-key-centric reference table that anchors a list of transaction type definitions without enumerating the relationship to dependent facts or links. It may also be treated as a reference hub when the transaction type codes are considered code values rather than true business entities.

Key Information Stored

The table's primary structure is anchored by the surrogate primary key PSA_TRX_TYPE_ID, defined as the object's primary key (PSA_TRX_PK). This numeric identifier is the definitive unique key for each transaction type record and should be treated as the surrogate business key for joins and lookups. The metadata does not document any alternate unique index; business-key candidates such as a transaction type code column are not listed among the documented columns.

The remaining documented columns fall into two functional groupings:

• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN provide standard Oracle EBS audit trail information, capturing who created and last modified each transaction type record and when.
• Descriptive flexfield columns — ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 provide the standard 15-column DFF structure, allowing customers to extend the transaction type definition with additional context-specific attributes without modifying the base schema.

Notably, the documented column list is limited to the primary key, WHO columns, and DFF columns. Transaction type name and description columns, while commonly expected in similar reference tables, are not enumerated in the ETRM metadata provided and should be confirmed against the live data dictionary before reliance.

Common Use Cases and Queries

Because the table functions as a transaction type reference, the most common queries retrieve the list of valid transaction types for a given Public Sector deployment. A simple pattern is to select the surrogate key alongside its DFF attributes:

SELECT psa_trx_type_id,
       attribute_category,
       attribute1,
       attribute2
FROM   psa.psa_trx_types_all
WHERE  psa_trx_type_id = :p_id;

Reporting use cases include joining transaction type definitions to the Public Sector transaction tables that consume them, building validation lists (LOVs) for transaction entry forms, and auditing custom DFF values stored in ATTRIBUTE1 through ATTRIBUTE15. A typical join pattern retrieves all transaction type records referenced by downstream transaction data via PSA_TRX_TYPE_ID. Because the table is classified as standalone, no FK-enforced joins are documented, so joins should be constructed on the PSA_TRX_TYPE_ID column where referencing objects store it.

Related Objects

The ETRM metadata classifies this table as standalone, meaning no foreign key relationships are documented from this object to other tables. Consequently, related objects must be identified by shared column usage rather than enforced constraints. The most significant relationships are:

• Child transaction tables in the PSA schema that store PSA_TRX_TYPE_ID as a reference to the transaction type applied to each record.
• Public Sector Financials setup and transaction APIs that read transaction type definitions to validate or default behavior during transaction processing.
• Concurrent programs and reports within the PSA module that join transaction data to PSA_TRX_TYPES_ALL on PSA_TRX_TYPE_ID to produce human-readable transaction type descriptions.
• Flexfield value sets associated with the transaction type DFF, which define the values acceptable in ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15.

Because no FK paths are documented, DBAs and developers should verify actual referencing objects through the data dictionary (for example, by querying USER_CONSTRAINTS and USER_DEPENDENCIES) prior to designing joins in custom extensions.