Search Results psbfv_budget_groups

Overview

PSBFV_BUDGET_GROUPS is a read-only database view belonging to the PSB (Public Sector Budgeting) product family. In Oracle EBS 12.1.1 and 12.2.2, PSB is classified as obsolete and is no longer shipped or supported as a functional module. The ETRM metadata explicitly notes that this object is "Not implemented in this database," meaning the underlying PSB schema objects are not created in standard installations that do not carry the historical Public Sector Budgeting license and patches. The view's purpose was to present a denormalized, user-friendly projection of budget group definitions, combining a budget group with its root budget group, the associated proposed and non-proposed account position sets, and the set of books (ledger) context. It served reporting, form-validation, and integration layers that needed a single-row snapshot of a budget group's identity, hierarchy, currency, and account position configuration without performing the joins manually.

Underlying Base Objects

The ETRM metadata documents no base objects separately, but the embedded view text reveals the full dependency set. The view is defined over PSB_BUDGET_GROUPS, self-joined (aliases BG and BGR), plus PSB_ACCOUNT_POSITION_SETS (referenced twice as PSS and NPSS) and GL_SETS_OF_BOOKS (SOB). The self-join uses a DECODE on ROOT_BUDGET_GROUP_ID: when a budget group has no root (NULL), it resolves to its own BUDGET_GROUP_ID, otherwise to its root. This pattern normalizes child budget groups to their root definition. The join to PSB_BUDGET_GROUPS BGR then supplies the root-level account position set identifiers, which in turn join to PSB_ACCOUNT_POSITION_SETS for the proposed and non-proposed set names. The join to GL_SETS_OF_BOOKS supplies the ledger name and currency code. The view is defined WITH READ ONLY, so it cannot be used as a DML target.

Key Columns

Columns can be grouped into four functional categories:

• Budget group identity: BUDGET_GROUP_SHORT_NAME, BUDGET_GROUP_NAME, BUDGET_GROUP_DESCRIPTION, NARRATIVE_DESCRIPTION.
• Hierarchy and root: ROOT_SHORT_NAME, ROOT_BUDGET_GROUP, ROOT_BUDGET_GROUP_ID, BUDGET_GROUP_ID, FREEZE_HIERARCHY_FLAG, and BUDGET_GROUP_NUM_PROPOSED_YRS.
• Account position sets and ledger context: ROOT_PS_ACCOUNT_POS_SET_NAME, ROOT_NPS_ACCOUNT_POS_SET_NAME, PS_ACCOUNT_POSITION_SET_ID, NPS_ACCOUNT_POSITION_SET_ID, ROOT_SET_OF_BOOKS_NAME, ROOT_CURRENCY_CODE.
• Audit and descriptor columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, plus the literal '_DF:PSB:PSB_BUDGET_GROUPS:BG' descriptor used by EBS descriptive flexfield / OA framework conventions to identify the source entity.

Effective dating is exposed via START_EFFECTIVE_DATE and END_EFFECTIVE_DATE, derived from BG.EFFECTIVE_START_DATE and BG.EFFECTIVE_END_DATE, enabling point-in-time reporting of budget group validity.

Common Use Cases and Queries

Because PSB is obsolete, live use cases are largely historical or migration-oriented: auditing legacy budget group configurations before decommissioning, extracting hierarchy and account position set mappings into a data warehouse, or reconciling frozen hierarchies. A representative query listing active budget groups with their root and ledger is:

• SELECT BUDGET_GROUP_SHORT_NAME, ROOT_SHORT_NAME, BUDGET_GROUP_NAME, ROOT_SET_OF_BOOKS_NAME, ROOT_CURRENCY_CODE, FREEZE_HIERARCHY_FLAG FROM PSBFV_BUDGET_GROUPS WHERE SYSDATE BETWEEN START_EFFECTIVE_DATE AND END_EFFECTIVE_DATE;
• SELECT ROOT_SHORT_NAME, ROOT_PS_ACCOUNT_POS_SET_NAME, ROOT_NPS_ACCOUNT_POS_SET_NAME, COUNT(*) FROM PSBFV_BUDGET_GROUPS GROUP BY ROOT_SHORT_NAME, ROOT_PS_ACCOUNT_POS_SET_NAME, ROOT_NPS_ACCOUNT_POS_SET_NAME;
• SELECT BUDGET_GROUP_NAME, BUDGET_GROUP_NUM_PROPOSED_YRS, NARRATIVE_DESCRIPTION FROM PSBFV_BUDGET_GROUPS WHERE FREEZE_HIERARCHY_FLAG = 'Y';

In environments where the view does not exist, queries against it raise ORA-00942; DBAs should confirm the PSB schema is installed and expected before relying on the view.