Search Results distribution_date




Overview
Explore in AI Chat

Overview

PSP_ADJUSTMENT_COMBO_V is a consolidated union view owned by the APPS schema within the Oracle E-Business Suite environment. It functions primarily within the PSP (Labor Distribution) module. The view is designed to provide a unified interface for querying historical distribution data across three distinct transactional contexts: standard distribution, pre-generation, and adjustments. As indicated by its definition, the view does not store data itself but rather aggregates and transforms data from multiple underlying history tables. Its primary role is to support reporting and integration by presenting a single, queryable source for distribution history records, allowing users to track labor cost allocations and adjustments over time without needing to navigate multiple tables. The view is marked as VALID in version 12.1.1 and 12.2.2, ensuring its availability for concurrent programs, custom reports, and data extracts.

Underlying Base Objects

The view is constructed via a UNION of three SELECT statements, each sourcing data from a different history table within the PSP schema. The first branch joins PSP_DISTRIBUTION_LINES_HISTORY with PSP_PAYROLL_SUB_LINES and PSP_PAYROLL_LINES to derive assignment and element type identifiers. This branch is tagged with the source literal 'DISTRIBUTION_LINES_HISTORY'. The second branch selects directly from PSP_PRE_GEN_DIST_LINES_HISTORY, tagged as 'PRE_GEN_DIST_LINES_HISTORY'. The third branch selects from PSP_ADJUSTMENT_LINES_HISTORY, tagged as 'ADJUSTMENT_LINES_HISTORY'. All referenced base objects are synonyms pointing to the actual tables in the APPS schema. The view therefore serves as a metadata layer that abstracts the physical storage of distribution history, enabling consistent access across all three historical data sets.

Key Columns

  • ASSIGNMENT_ID – Identifier for the employee assignment associated with the distribution line.
  • ELEMENT_TYPE_ID – Identifier for the payroll element type, indicating the nature of the earning or deduction.
  • DISTRIBUTION_DATE – The effective date on which the distribution was recorded or adjusted.
  • BUSINESS_GROUP_ID – Identifier for the business group, used for organizational partitioning.
  • SET_OF_BOOKS_ID – Identifier for the set of books (ledger), establishing the financial context.
  • SOURCE – A literal column that indicates the origin of each row: 'DISTRIBUTION_LINES_HISTORY', 'PRE_GEN_DIST_LINES_HISTORY', or 'ADJUSTMENT_LINES_HISTORY'. This column is critical for distinguishing between the different data sets within the union.

Common Use Cases and Queries

This view is typically employed in labor distribution reporting, audit trails, and reconciliation processes. A common requirement is to retrieve all historical distribution entries for a given assignment, optionally filtered by source or date range. For example, to examine all adjustments for a specific assignment, one might execute:

SELECT * FROM APPS.PSP_ADJUSTMENT_COMBO_V WHERE ASSIGNMENT_ID = :p_assignment_id AND SOURCE = 'ADJUSTMENT_LINES_HISTORY';

Alternatively, to compare pre-generated distributions against final distributions, a query grouping by SOURCE can provide counts and totals. Because the view consolidates three tables, it simplifies data extraction for subledger accounting, cost allocation reviews, and custom OBIEE or BI Publisher reports. It is also useful in integration scenarios where external systems require a single feed of distribution history without needing to understand the underlying PSP schema complexity. When using the view, ensure that joins to other master data (e.g., PER_ALL_ASSIGNMENTS_F, GL_SETS_OF_BOOKS) are performed on the exposed ID columns to enrich the output with descriptive attributes.