Results for “pqh_txn_category_documents”

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

Overview

The PQH_TXN_CATEGORY_DOCUMENTS table resides in the HR schema and is a component of the Public Sector HR (PQH) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as an intersection entity between transaction categories and documents, and additionally stores the document type in the context of the associated transaction category. The table therefore supports the assignment of supporting documentation to transaction categories, allowing the application to define which document types are relevant to a given category and to record the specific documents attached to it.

Under the heuristic Data Vault classification mined from the foreign-key structure, this object is modeled as standalone. In Data Vault terms this suggests it is neither a classic hub nor a pure satellite but a link-style intersection combining two business keys — DOCUMENT_ID and TRANSACTION_CATEGORY_ID — together with a descriptive type attribute. This classification should be treated as a modeling suggestion rather than a definitive designation, since the metadata records no enforced foreign keys.

Key Information Stored

The table is documented with nine columns in the ETRM 12.2.2 physical schema. The most significant columns are:

• DOCUMENT_ID — identifier of the document, forming part of the composite primary key.
• TRANSACTION_CATEGORY_ID — identifier of the transaction category, forming the other part of the composite primary key.
• TYPE_CODE — the type associated with the document within the transaction category context; this is the key descriptive discriminator carried by the intersection.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle EBS framework to detect concurrent updates.
• LAST_UPDATE_DATE — timestamp of the most recent modification, used for audit and incremental extraction.
• LAST_UPDATED_BY — user who performed the most recent update.
• LAST_UPDATE_LOGIN — login identifier associated with the last update.
• CREATED_BY — user who created the row.
• CREATION_DATE — timestamp when the row was created.

The surrogate primary key is PQH_TXN_CATEGORY_DOCUMENTS_PK, defined over the unique index columns TRANSACTION_CATEGORY_ID and DOCUMENT_ID. These two columns therefore constitute the business-key candidate, guaranteeing that a given document is associated with a given transaction category only once. The remaining columns are standard EBS audit and concurrency attributes.

Common Use Cases and Queries

Typical usage involves enumerating the documents and document types linked to a transaction category, validating that a document is permitted for a category's TYPE_CODE, and driving reporting on documentation coverage. A representative query listing documents for a category is:

• SELECT t.DOCUMENT_ID, t.TRANSACTION_CATEGORY_ID, t.TYPE_CODE FROM HR.PQH_TXN_CATEGORY_DOCUMENTS t WHERE t.TRANSACTION_CATEGORY_ID = :category_id;
• Audit and change-tracking extracts filter on LAST_UPDATE_DATE and join to user tables via LAST_UPDATED_BY.
• Reconciliation queries join to the transaction category and document masters to detect orphaned or missing assignments.

Because the table is flagged standalone, reporting layers frequently treat it as a bridge between the category and document dimensions, commonly materialized into a Data Vault link for analytical consumption.

Related Objects

The most significant related objects are:

• The transaction category master table, joined on TRANSACTION_CATEGORY_ID, providing the category definition used to contextualize the document.
• The document master table, joined on DOCUMENT_ID, supplying document attributes referenced by this intersection.
• The Public Sector HR transaction tables that reference transaction categories and consume the document assignments established here.
• The document type lookup referenced by TYPE_CODE, which qualifies the role of the document within the category context.
• FND user and login tables, joined on CREATED_BY, LAST_UPDATED_BY and LAST_UPDATE_LOGIN, for audit reporting.

As no enforced foreign keys are documented, integrity between these objects is largely maintained at the application tier, so query authors should validate join results carefully when integrating this table into reporting solutions.