Results for “pspfv_generated_dist_histories”

4 results




Overview
Explore in AI Chat

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

Overview

PSPFV_GENERATED_DIST_HISTORIES is a reporting view within the Oracle E-Business Suite Labor Distribution module (PSP). It exposes the historical record of payroll-generated cost distributions, presenting the fully resolved accounting and project context for each distribution line. Unlike operational tables that hold only surrogate identifiers, this view resolves those identifiers into descriptive names, dates, and flexfield semantics suitable for reporting, reconciliation, and downstream integration. It is not implemented as a database object in every environment, and the ETRM documentation explicitly records it as "Not implemented in this database," indicating that availability depends on the applied patch level and module configuration.

The view draws on distribution history, payroll sub-line details, organization account definitions, award and project master data, and general ledger reference data. It is primarily consumed by labor distribution inquiries, audit reports, and interfaces that require a denormalized, human-readable snapshot of historical distribution activity.

Underlying Base Objects

The view is defined by a UNION of two SELECT statements over the same set of base tables, joined largely through outer joins to preserve distribution lines even when related project or award data is missing. The documented base objects are:

All relationships other than the payroll sub-line join are outer joins, so a distribution line will still appear where the project, task, award, or organization reference is absent.

Key Columns

The view exposes identifiers and descriptive attributes side by side. Notable columns include DISTRIBUTION_LINE_ID, DISTRIBUTION_DATE, EFFECTIVE_DATE, and DISTRIBUTION_AMOUNT, which anchor the historical transaction. SET_OF_BOOKS_ID and the joined GL.NAME identify the ledger. BUSINESS_GROUP_ID with HOU2.NAME supplies the business group organization name, while EXPENDITURE_ORGANIZATION_ID with HOU.NAME supplies the expenditure organization. PROJECT_ID, PPA.NAME, TASK_ID, PT.TASK_NAME, AWARD_ID, and GAA.AWARD_FULL_NAME provide project and award context.

Payroll detail is carried through PAYROLL_SUB_LINE_ID, SUB_LINE_START_DATE, SUB_LINE_END_DATE, PAY_AMOUNT, DAILY_RATE, and REASON_CODE. Lookup-backed columns such as STATUS_CODE, SUSPENSE_REASON_CODE, INCLUDE_IN_ER_FLAG, and REVERSAL_ENTRY_FLAG are resolved through PSP_LOOKUPS and GL_LOOKUPS descriptors embedded in the view text, which convert codes into their MEANING values at query time.

Common Use Cases and Queries

Typical scenarios include reconciling payroll-generated distributions to the general ledger, auditing suspense and reversal activity, and validating that expenditure organizations and projects were correctly assigned. A representative query joins the view to organization and ledger context:

SELECT DISTRIBUTION_LINE_ID, DISTRIBUTION_DATE, DISTRIBUTION_AMOUNT,
       NAME, EXPENDITURE_ORGANIZATION_ID, PROJECT_ID, AWARD_FULL_NAME
FROM   PSPFV_GENERATED_DIST_HISTORIES
WHERE  DISTRIBUTION_DATE BETWEEN :p_start AND :p_end;

Because the view resolves HR_ALL_ORGANIZATION_UNITS into two distinct roles — expenditure organization and business group — reports that filter or group by organization should qualify the intended role to avoid ambiguity. Filters on STATUS_CODE should be expressed using the lookup meaning rather than the stored code, consistent with the view's built-in translation logic. Where the view is not implemented, equivalent results require reconstructing the same join across PSP_DISTRIBUTION_LINES_HISTORY and its related tables.