Results for “service_package_short_name”
10 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
PSB_WS_SERVICE_PKG_PROFILES_V is a database view historically shipped with the PSB (Public Sector Budgeting) product in Oracle EBS 12.1.1 and 12.2.2. The view exposes the association between worksheet user profiles and the service packages assigned to them, joining profile records in PSB_WS_USER_PROFILES to the service package definitions in PSB_SERVICE_PACKAGES. In the ETRM 12.2.2 metadata this object is documented with an explicit Description of "Not implemented in this database," and no owner or referenced base objects are recorded, indicating that the underlying PSB schema objects are absent from the current environment. Consequently, the view is effectively obsolete in databases where the PSB module has been removed or never installed. Its role was to provide a denormalized, read-only presentation of service package profile assignments for reporting and integration purposes, notably allowing applications or queries to resolve a service package identifier to its human-readable short name and name without writing the join manually. Because the "service_package_name" column is surfaced directly by the view, it served as a convenient lookup source for both ad hoc reporting and any programmatic logic that needed to display the package name alongside worksheet and user context.
Underlying Base Objects
The view text defines it over two PSB tables:
- PSB_WS_USER_PROFILES WUP — the driving table, holding the user profile rows keyed by WORKSHEET_ID, USER_ID, PROFILE_TYPE, and SERVICE_PACKAGE_ID. All audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) originate here.
- PSB_SERVICE_PACKAGES SP — the lookup table supplying SHORT_NAME, NAME, and PRIORITY for each service package.
The two tables are joined on SERVICE_PACKAGE_ID (WUP.SERVICE_PACKAGE_ID = SP.SERVICE_PACKAGE_ID), and the view restricts results to PROFILE_TYPE = 'S', isolating service-package profile rows from other profile types that may occupy the same profile table. The view also exposes WUP.ROWID as ROW_ID, a construct commonly used by EBS forms and by programs that need a row-level handle to the underlying profile row. Because the ETRM documentation records no referenced base objects and no owner, the view's definition should be treated as reference information only; where PSB is not installed, attempting to query it raises an invalid identifier or object-not-found error rather than returning rows.
Key Columns
- ROW_ID — the ROWID of the underlying PSB_WS_USER_PROFILES row.
- WORKSHEET_ID — the budgeting worksheet to which the profile assignment belongs.
- PROFILE_TYPE — the profile classification; the view filters to 'S' (service package profiles).
- USER_ID — the application user associated with the profile row.
- SERVICE_PACKAGE_ID — the foreign key linking to PSB_SERVICE_PACKAGES.
- SERVICE_PACKAGE_SHORT_NAME — the SP.SHORT_NAME value, the abbreviated code for the service package.
- SERVICE_PACKAGE_NAME — the SP.NAME value, the descriptive package name; this is the column most often targeted when searching for service_package_name.
- PRIORITY — the SP.PRIORITY value, indicating the package ordering or precedence.
- Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, all inherited from PSB_WS_USER_PROFILES.
Common Use Cases and Queries
Typical usage centers on resolving service package names for worksheet profiles and on reporting profile-to-package assignments per user or worksheet. A representative query lists worksheets and their assigned service packages for a given user:
SELECT w.worksheet_id, w.user_id, w.service_package_short_name, w.service_package_name, w.priority FROM psb_ws_service_pkg_profiles_v w WHERE w.user_id = :p_user_id ORDER BY w.worksheet_id, w.priority;
Another common pattern searches by package name or short name when the numeric SERVICE_PACKAGE_ID is unknown:
SELECT worksheet_id, user_id, service_package_id, service_package_name FROM psb_ws_service_pkg_profiles_v WHERE UPPER(service_package_name) LIKE UPPER('%'||:p_name||'%');
Integration scenarios, such as interface programs or reports that must display a package description rather than an ID, leverage the pre-joined NAME and SHORT_NAME columns. Audit reporting can use CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY to trace when a profile assignment was established or modified. In all cases, the prerequisite is that the PSB module objects exist in the database; where the ETRM indicates the view is not implemented, queries against PSB_WS_SERVICE_PKG_PROFILES_V will fail and any dependent logic should be disabled or replaced.
-
APPS.PSB_WS_SERVICE_PKG_PROFILES_V·↳ PSB_SERVICE_PACKAGES·↳ PSB_WS_USER_PROFILES·Explore PSB module →
-
APPS.PSB_WS_SERVICE_PACKAGES_V·↳ PSB_BUDGET_PERIODS·↳ PSB_BUDGET_YEAR_TYPES·↳ PSB_SERVICE_PACKAGES·Explore PSB module →
-
View: PSB_SERVICE_PACKAGES_V 12.1.1
APPS.PSB_SERVICE_PACKAGES_V·↳ PSB_SERVICE_PACKAGES·↳ PSB_WORKSHEETS·Explore PSB module →
-
Not implemented in this database·Explore PSB module →
-
Not implemented in this database·Explore PSB module →
-
View: PSB_SERVICE_PACKAGES_V 12.2.2
Not implemented in this database·Explore PSB module →
-
eTRM - PSB Tables and Views 12.1.1
User profiles for a worksheet