Search Results psb_ws_service_packages_v

Overview

PSB_WS_SERVICE_PACKAGES_V is an Oracle E-Business Suite (EBS) view owned by the APPS schema and delivered as part of the PSB (Public Sector Budgeting) product module. It exposes the set of service packages that are relevant to a given budgeting worksheet, joining worksheet context data to the service package definitions that drive budget line configuration. In the EBS 12.1.1 and 12.2.2 releases the object is registered as VALID, confirming it is a supported, queryable database object rather than a deprecated or stub artifact.

Its primary role is to answer the question "which service packages apply to this worksheet?" This is a recurring requirement in public sector budgeting, where a worksheet groups budget lines under service packages that determine how accounts and periods are populated. Because the view resolves applicability dynamically at query time, it is suited to reporting, concurrent program data sources, and integration or interface extraction, rather than serving as a persistent configuration table.

Underlying Base Objects

The documented view text references two base tables and several correlated subquery sources. The principal FROM clause objects are PSB_SERVICE_PACKAGES (aliased SP) and PSB_WORKSHEETS (aliased WS). Across the full definition, the following PSB tables participate:

• PSB_SERVICE_PACKAGES (SP) — the driving table supplying service package identity, flags, priority, and descriptive attributes.
• PSB_WORKSHEETS (WS) — provides WORKSHEET_ID and the global worksheet flag that governs scope resolution.
• PSB_WS_LINES (LINES) — worksheet lines restricted by VIEW_LINE_FLAG = 'Y'.
• PSB_WS_ACCOUNT_LINES (ACCTS) — links account lines to a service package via SERVICE_PACKAGE_ID.
• PSB_BUDGET_PERIODS (BP) and PSB_BUDGET_YEAR_TYPES (BT) — used to exclude prior-year categories and to validate the budget period against the package's global worksheet calendar.

The ETRM 12.2.2 metadata documents no referenced base objects explicitly; the relationship above is derived from the published view text. The view is therefore best understood as a denormalized convenience layer over these PSB structures, not as a standalone entity.

Key Columns

The view exposes the following columns, as documented. Note that the ETRM column list and the select list differ slightly in naming (ROW_ID versus SP.ROWID, and SERVICE_PACKAGE_NAME versus SP.NAME).

• ROW_ID — the ROWID of the row in PSB_SERVICE_PACKAGES, useful for uniqueness and update targeting.
• WORKSHEET_ID — the worksheet against which package applicability is being evaluated.
• SERVICE_PACKAGE_ID — surrogate key of the service package; the join key used by account lines.
• SERVICE_PACKAGE_NAME / SERVICE_PACKAGE_SHORT_NAME — the display name and abbreviated form of the package.
• BASE_SERVICE_PACKAGE — a Y/N flag. 'Y' indicates a base package that is always applicable to a worksheet; 'N' indicates a package applicable only when qualifying worksheet lines and account lines exist.
• PRIORITY — ordering or ranking attribute for the package.
• DESCRIPTION — free-text descriptive attribute.

The BASE_SERVICE_PACKAGE flag is the semantic pivot of the view: base packages are returned for every applicable worksheet, while non-base packages are returned only if supported by validated worksheet, account line, and budget period data.

Common Use Cases and Queries

Typical uses include verifying which packages are in scope for a worksheet prior to budget posting, feeding reports that summarize package content, and diagnosing missing packages in worksheet line generation.

List all packages for a worksheet:

• SELECT worksheet_id, service_package_id, service_package_name, base_service_package, priority FROM apps.psb_ws_service_packages_v WHERE worksheet_id = :p_worksheet_id ORDER BY priority;

Isolate non-base packages, which depend on qualifying account lines and budget periods:

• SELECT service_package_id, service_package_name FROM apps.psb_ws_service_packages_v WHERE base_service_package = 'N' AND worksheet_id = :p_worksheet_id;

Join to package definitions for extended attributes:

• SELECT v.worksheet_id, v.service_package_id, p.description FROM apps.psb_ws_service_packages_v v, apps.psb_service_packages p WHERE v.service_package_id = p.service_package_id AND v.worksheet_id = :p_worksheet_id;

Because the definition embeds correlated EXISTS subqueries against budget periods, account lines, and year types, queries returning many worksheets can be costly. Filtering by WORKSHEET_ID, as shown, is the recommended practice to keep execution plans efficient.