Search Results psbfv_parameter_pos_set_lists

Overview

The PSBFV_PARAMETER_POS_SET_LISTS view is a reporting and integration object in the Oracle E-Business Suite PSB – Public Sector Budgeting module. It exposes the association between PSB parameters and the account position sets assigned to them, resolving the many-to-many relationships held across several base entities into a single, denormalized, read-only result set. In EBS 12.1.1 and 12.2.2 this view is registered under the APPS schema with a VALID status and is flagged WITH READ ONLY, meaning it is intended strictly for query and extraction, not for DML.

Its primary role is to provide implementers, report developers, and integration designers with a convenient surface for retrieving which position (account) sets are linked to each parameter, alongside the effective dating and audit columns required for downstream reporting. Because the view pre-joins the entity, assignment, position set, and set relation structures, it removes the need to reconstruct this relationship graph manually within custom SQL, BI Publisher data templates, or interface programs.

Underlying Base Objects

The view is defined over four documented base tables within the PSB schema:

• PSB_ENTITY – aliased PE; supplies the parameter entity and its identifying attributes.
• PSB_ENTITY_ASSIGNMENT – aliased PEA; supplies the effective start and end dates of the assignment.
• PSB_ACCOUNT_POSITION_SETS – aliased APS; supplies the position set name and identifier.
• PSB_SET_RELATIONS – aliased SR; supplies the linking relation between the parameter and the position set.

The joins enforce several filters that define the view's scope: PE.ENTITY_ID = PEA.ENTITY_ID links entities to their assignments; PE.ENTITY_TYPE = 'PARAMETER' restricts output to parameter entities only; APS.ACCOUNT_OR_POSITION_TYPE = 'P' restricts the sets to position type; and SR.ACCOUNT_POSITION_SET_ID = APS.ACCOUNT_POSITION_SET_ID with SR.PARAMETER_ID = PE.ENTITY_ID completes the relation mapping. The ETRM metadata documents no additional referenced base objects beyond these four tables.

Key Columns

The view projects the following columns, aliasing the underlying sources into business-friendly names:

• PARAMETER_NAME – the name of the PSB parameter entity (PE.NAME).
• POSITION_SET_NAME – the name of the assigned account position set (APS.NAME).
• START_EFFECTIVE_DATE – the date the parameter-to-position-set assignment becomes effective (PEA.EFFECTIVE_START_DATE).
• END_EFFECTIVE_DATE – the date the assignment ceases to be effective (PEA.EFFECTIVE_END_DATE).
• PARAMETER_ID – the entity identifier of the parameter (PE.ENTITY_ID).
• POSITION_SET_ID – the identifier of the account position set (APS.ACCOUNT_POSITION_SET_ID).
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE – standard audit columns carried from the entity record.

Together these columns support both human-readable reporting (names) and machine-driven joins (IDs and effective dates).

Common Use Cases and Queries

Typical scenarios include auditing which position sets are attached to a given parameter, validating effective-dated assignments during configuration review, and driving integration extracts that must reproduce the parameter-to-set mapping.

List all parameter-to-position-set associations:

SELECT parameter_name,
       position_set_name,
       start_effective_date,
       end_effective_date
FROM   apps.psbfv_parameter_pos_set_lists;

Filter to currently effective assignments for a specific parameter:

SELECT parameter_name,
       position_set_name,
       start_effective_date,
       end_effective_date
FROM   apps.psbfv_parameter_pos_set_lists
WHERE  parameter_id = :p_parameter_id
AND    TRUNC(SYSDATE) BETWEEN start_effective_date AND NVL(end_effective_date, TRUNC(SYSDATE));

Because the view is read-only, it should be used solely for SELECT operations in reports, extracts, and integration queries, with any maintenance performed against the underlying PSB base tables through the standard application or supported APIs.