Results for “consolidated_table_route_id”

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

Overview

PQH_TRANSACTION_CATEGORIES_VL is a translatable (VL suffix) view owned by the APPS schema in Oracle E-Business Suite, delivered under the PQH – Public Sector HR product family. It presents the transaction category definitions used by the HR transactional framework, and exposes both the operational attributes of each transaction category and its language-dependent descriptive name. The view is documented as VALID and appears in ETRM metadata for release 12.2.2, with the same definition applicable to 12.1.1.

The view performs an inner join between the base transaction categories table and its translation table, filtered to the session language via USERENV('LANG'). This makes it the standard reporting and integration entry point for querying transaction categories by their user-visible name rather than by numeric identifier. Because it includes workflow, posting, freeze, and future-action attributes, the view is relevant to both functional configuration reporting and technical integrations that drive the HR transaction routing engine.

Underlying Base Objects

The view is defined over two documented base objects:

• PQH_TRANSACTION_CATEGORIES (SYNONYM) — the base table holding transaction category rows keyed by TRANSACTION_CATEGORY_ID, including the routing and workflow control columns. Referenced in the view text by the alias TCT.
• PQH_TRANSACTION_CATEGORIES_TL (SYNONYM) — the translation table supplying the language-dependent NAME column, referenced by the alias CTL.

The join is TCT.TRANSACTION_CATEGORY_ID = CTL.TRANSACTION_CATEGORY_ID AND CTL.LANGUAGE = USERENV('LANG'), so a row is returned only when a translation exists in the session's language. Because the T (base) table is the driving table and holds the operational columns, the view is effectively a one-to-one projection of PQH_TRANSACTION_CATEGORIES enriched with the translated NAME. The view also exposes TCT.ROWID as ROW_ID, which supports certain reporting and updatable-view patterns, though the view itself is intended for query rather than direct DML.

Key Columns

• TRANSACTION_CATEGORY_ID — primary identifier for the transaction category; the join key to the translation table.
• NAME / SHORT_NAME — the translated display name and the internal short name of the category.
• BUSINESS_GROUP_ID — the business group that owns the definition, enabling multi-organization filtering.
• MASTER_TABLE_ROUTE_ID and CONSOLIDATED_TABLE_ROUTE_ID — identifiers governing the routing of the transaction to its master and consolidated destination tables. CONSOLIDATED_TABLE_ROUTE_ID is the column most directly associated with the search term "consolidated_table_route_id"; it links the category to the consolidated route configuration used when transactional data is aggregated.
• CUSTOM_WORKFLOW_NAME and CUSTOM_WF_PROCESS_NAME — the custom workflow and process invoked for the category, if overridden.
• FORM_NAME — the associated form, where applicable.
• FREEZE_STATUS_CD, FUTURE_ACTION_CD, MEMBER_CD, POST_STYLE_CD, POST_TXN_FUNCTION — controls determining freeze behaviour, deferred action, member grouping, and posting semantics of the transaction.
• ROUTE_VALIDATED_TXN_FLAG and TIMEOUT_DAYS — routing validation indicator and timeout threshold.
• SETUP_TYPE_CD, ENABLE_FLAG, WORKFLOW_ENABLE_FLAG — setup classification and enablement switches for the category and its workflow.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit and optimistic locking columns.

Common Use Cases and Queries

Typical uses include configuration reporting of transaction categories, troubleshooting of routing to consolidated tables, and integration extracts that map category identifiers to workflow and posting behaviour.

List all enabled categories in the session language:

SELECT transaction_category_id, name, short_name, enable_flag, workflow_enable_flag FROM apps.pqh_transaction_categories_vl WHERE enable_flag = 'Y' ORDER BY name;

Inspect routing configuration, including the consolidated table route referenced in the search:

SELECT transaction_category_id, name, master_table_route_id, consolidated_table_route_id, custom_workflow_name, custom_wf_process_name FROM apps.pqh_transaction_categories_vl WHERE consolidated_table_route_id IS NOT NULL;

Join to business group context for a specific population:

SELECT v.name, v.short_name, v.setup_type_cd, v.post_style_cd FROM apps.pqh_transaction_categories_vl v WHERE v.business_group_id = :p_business_group_id AND v.workflow_enable_flag = 'Y';

Because NAME is resolved through the translation table, the same query returns the appropriate language-specific label for each user session, which is essential for localized reporting and user-facing extracts.