Search Results psb_position_assign_attr_v

Overview

PSB_POSITION_ASSIGN_ATTR_V is a PL/SQL view owned by the APPS schema and registered as VALID in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the PSB (Public Sector Budgeting) product family, where budget worksheets capture position-level and assignment-level data used in position budgeting, salary and fringe computations, and budget roll-ups. The view isolates the attribute-type records from the broader set of position assignment data, exposing the attribute definitions and their resolved values as a single de-normalized result set.

Its principal role in reporting and integration is to provide a stable, read-only projection over the position assignment attribute data model. Rather than joining the assignment, attribute definition, and attribute value tables individually, external reports, extracts, and interfaces can query this view to obtain the attribute name, the effective attribute value, and the association to a worksheet, position, and data extract in a single fetch. Because the view text resolves the attribute value through a DECODE against PSB_ATTRIBUTE_VALUES, consumers receive the correct value whether it was stored directly on the assignment row or via a foreign key to a value-table entry.

Underlying Base Objects

The view is defined over three PSB objects, joined inside the view body:

• PSB_POSITION_ASSIGNMENTS_V (alias PAS) — the driving source, filtered by PAS.ASSIGNMENT_TYPE = 'ATTRIBUTE'. This restriction is what makes the view attribute-specific.
• PSB_ATTRIBUTES (alias PAT) — supplies the attribute definition, including NAME (surfaced as ATTRIBUTE_NAME) and VALUE_TABLE_FLAG. Joined on PAS.ATTRIBUTE_ID = PAT.ATTRIBUTE_ID.
• PSB_ATTRIBUTE_VALUES (alias PATV) — supplies list-of-values entries. The join is outer (PATV.ATTRIBUTE_VALUE_ID(+)), so assignments that carry a literal value rather than a value-table reference are still returned.

The view text also projects the ROWID of PSB_POSITION_ASSIGNMENTS_V as its first column, which enables row-level identification of the underlying assignment record for downstream processing.

Key Columns

• POSITION_ASSIGNMENT_ID — primary identifier of the position assignment record.
• DATA_EXTRACT_ID / WORKSHEET_ID / POSITION_ID — context keys linking the attribute to its extract run, budget worksheet, and position.
• ATTRIBUTE_ID — foreign key to PSB_ATTRIBUTES, identifying which attribute is assigned.
• ATTRIBUTE_VALUE_ID — foreign key to PSB_ATTRIBUTE_VALUES when the value comes from a value table; NULL when a literal value is stored.
• ATTRIBUTE_VALUE — the resolved value, computed as DECODE(PAS.ATTRIBUTE_VALUE_ID, NULL, PAS.ATTRIBUTE_VALUE, PATV.ATTRIBUTE_VALUE).
• ATTRIBUTE_NAME — descriptive name from PSB_ATTRIBUTES.
• VALUE_TABLE_FLAG — indicates whether the attribute draws its value from a value table.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-range validity of the assignment.
• GLOBAL_DEFAULT_FLAG / ASSIGNMENT_DEFAULT_RULE_ID / MODIFY_FLAG — defaulting and override behavior controls.
• CURRENCY_CODE — currency context for the attribute value.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The view is typically queried to audit attribute assignments for a worksheet, to verify harvested values against extract runs, or to drive downstream position-budgeting interfaces.

• List all attributes for a worksheet:

SELECT attribute_name, attribute_value, attribute_value_id
FROM   apps.psb_position_assign_attr_v
WHERE  worksheet_id = :p_worksheet_id;

• Audit values resolved from a value table versus literals:

SELECT attribute_name, attribute_value, value_table_flag
FROM   apps.psb_position_assign_attr_v
WHERE  attribute_value_id IS NOT NULL;

• Extract attribute data for a specific position:

SELECT position_id, attribute_name, attribute_value, currency_code
FROM   apps.psb_position_assign_attr_v
WHERE  position_id = :p_position_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Because the view already applies the ASSIGNMENT_TYPE = 'ATTRIBUTE' filter and resolves the value-table lookup, callers should not re-apply the assignment type predicate, and should apply an outer join to PSB_ATTRIBUTE_VALUES only if they extend the view definition.