Results for “psp_distribution_combo_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PSP_DISTRIBUTION_COMBO_V is a reporting view within the Oracle E-Business Suite Labor Distribution module (PSP). It presents a consolidated, union-based projection of labor cost distribution activity by combining three historically distinct distribution sources into a single queryable result set: generated cost distributions, pre-generation distributions, and manual adjustment lines. The view exists to give reporting, reconciliation, and downstream integration consumers one consistent interface for examining how payroll and effort costs have been spread across accounting distributions, regardless of which internal mechanism produced each line.

It is important to note that this object is documented as not implemented in the database. In Oracle EBS 12.1.1 and 12.2.2, that status indicates the view is a reference or historical definition that is not created by the standard product installation. Consequently, it should be treated as a documented design pattern rather than a guaranteed callable object in a production instance. Where the logic is required, it is typically reproduced in custom reporting views or extracted via the underlying base tables.

Underlying Base Objects

The view is defined over three Labor Distribution history tables, combined with inner joins to payroll structures in the first branch of the union:

• PSP_DISTRIBUTION_LINES_HISTORY — joined to PSP_PAYROLL_SUB_LINES and PSP_PAYROLL_LINES to resolve PERSON_ID, ASSIGNMENT_ID, and ELEMENT_TYPE_ID. This branch contributes lines identified by the literal source value 'DISTRIBUTION_LINES_HISTORY'.
• PSP_PRE_GEN_DIST_LINES_HISTORY — consumed directly (no payroll join), tagged with source 'PRE_GEN_DIST_LINES_HISTORY'. Its distribution line identifier is PRE_GEN_DIST_LINE_ID.
• PSP_ADJUSTMENT_LINES_HISTORY — consumed directly, tagged with source 'ADJUSTMENT_LINES_HISTORY'. Its identifier is ADJUSTMENT_LINE_ID.

Although the ETRM metadata lists no directly referenced base objects for the documented 12.2.2 definition, the view text clearly establishes these three history tables, plus the two payroll feeder tables, as the operative sources. Each branch applies the same inclusion filters: rows where INCLUDE_IN_ER_FLAG is 'Y' (or null, via NVL) and REVERSAL_ENTRY_FLAG is 'N' (or null). Reversal entries are therefore excluded by design, so the view reflects net forward cost distributions only.

Key Columns

The view exposes a uniform twelve-column shape across all union branches:

• PERSON_ID, ASSIGNMENT_ID, ELEMENT_TYPE_ID — the person, assignment, and element context of the cost.
• DISTRIBUTION_DATE — the accounting/distribution date for the line.
• DISTRIBUTION_AMOUNT — the distributed amount, coerced to 0 with NVL when null.
• DISTRIBUTION_LINE_ID — a normalized alias that maps the native primary key of each branch (DISTRIBUTION_LINE_ID, PRE_GEN_DIST_LINE_ID, or ADJUSTMENT_LINE_ID) into one column.
• EFFORT_REPORT_ID — links the line to the originating effort report.
• VERSION_NUM — the version of the distribution record. Because the history tables retain successive versions, this column is the key discriminator for isolating the current or a specific edit revision of a line; it is the column most frequently cited when analysts search for "version_num" against this view.
• STATUS_CODE — the processing status of the distribution line.
• SOURCE — a literal indicating which history table produced the row.
• BUSINESS_GROUP_ID, SET_OF_BOOKS_ID — the organizational and ledger context, required for multi-org and multi-ledger filtering.

Common Use Cases and Queries

Typical scenarios include reconciling effort-reported costs, auditing which distribution mechanism generated a given amount, and isolating the latest version of each line before feeding a downstream ledger or reporting process.

To list active lines for a person, filtering by book and business group:

• SELECT person_id, distribution_date, distribution_amount, source, version_num, status_code FROM psp_distribution_combo_v WHERE person_id = :p_person AND set_of_books_id = :p_sobid AND business_group_id = :p_bg ORDER BY distribution_date, source;

To summarize costs by source and identify the maximum version per line:

• SELECT source, COUNT(*) line_count, SUM(distribution_amount) total_amt FROM psp_distribution_combo_v WHERE set_of_books_id = :p_sobid GROUP BY source;
• SELECT distribution_line_id, source, MAX(version_num) latest_version FROM psp_distribution_combo_v GROUP BY distribution_line_id, source;

Because the view is not implemented in the standard database, equivalent logic must often be reproduced directly against the three history tables with the same NVL flag filters. This preserves the documented inclusion semantics while accommodating local schema differences in 12.1.1 and 12.2.2.