Results for “psp_payroll_sources”

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

Overview

PSP_PAYROLL_SOURCES is a reference (lookup) table in the Oracle E-Business Suite Labor Distribution module (PSP). It stores the valid payroll source codes that are associated with payroll source types, providing the controlled vocabulary used throughout Labor Distribution to classify where payroll and adjustment transactions originate. In Oracle EBS 12.1.1 and 12.2.2, this table functions as a validation anchor for transactional tables such as PSP_ADJUSTMENT_LINES, PSP_ADJUSTMENT_LINES_HISTORY, and PSP_PAYROLL_CONTROLS, ensuring that only defined source type and source code combinations are stored against adjustment and payroll control records.

From a Data Vault modeling perspective, the mined foreign-key structure suggests a hub-leaning classification. The table behaves as a durable business-key reference whose primary key (SOURCE_TYPE, SOURCE_CODE) is reused as a foreign key by multiple dependent tables, which is characteristic of a hub entity surrounded by downstream satellites and links. This is a heuristic suggestion only and should be validated against the broader PSP model during any warehouse design effort.

Key Information Stored

The table is documented with 25 physical columns in the ETRM 12.2.2 schema (owner PSP). The most significant columns are:

• SOURCE_TYPE – Identifies the category or type of payroll source. Part of the composite primary key and the principal discriminator referenced by downstream tables.
• SOURCE_CODE – The specific payroll source code within a given source type. The second component of the composite primary key.
• DESCRIPTION – The user-facing description of the source type/source code combination, used in list-of-values and report output.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 – The standard Oracle EBS descriptive flexfield (DFF) columns, enabling client-specific extensions to the payroll source definition.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – Standard WHO audit columns capturing the last change to each row.
• CREATED_BY, CREATION_DATE – Standard WHO audit columns capturing row creation.
• ZD_EDITION_NAME – Editioning column supporting Oracle EBS 12.2 online patching; it participates in the unique index PSP_PAYROLL_SOURCES_U1.

The surrogate primary key is defined by the constraint PSP_PAYROLL_SOURCES_PK over (SOURCE_TYPE, SOURCE_CODE). A unique index, PSP_PAYROLL_SOURCES_U1 over (SOURCE_TYPE, SOURCE_CODE, ZD_EDITION_NAME), serves as the business-key candidate under the editioning model, guaranteeing uniqueness of a source definition within an edition.

Common Use Cases and Queries

This table is primarily used for validation, list-of-values presentation, and reporting joins. Typical scenarios include:

• Validating that adjustment lines and payroll controls carry a legitimate source type and source code combination before processing.
• Producing lookup lists for Labor Distribution setup and adjustment entry forms.
• Reporting payroll activity grouped by source type and source code for reconciliation and audit.

A representative query joining the reference table to adjustment lines is:

• SELECT p.SOURCE_TYPE, p.SOURCE_CODE, p.DESCRIPTION, a.* FROM PSP.PSP_PAYROLL_SOURCES p, PSP.PSP_ADJUSTMENT_LINES a WHERE a.SOURCE_TYPE = p.SOURCE_TYPE AND a.SOURCE_CODE = p.SOURCE_CODE;

For payroll control analysis, the join uses PSP_PAYROLL_CONTROLS.SOURCE_TYPE and PSP_PAYROLL_CONTROLS.PAYROLL_SOURCE_CODE against SOURCE_TYPE and SOURCE_CODE respectively. Analysts should note that SOURCE_CODE is not always populated in the dependent control table, so outer joins may be required to avoid dropping rows.

Related Objects

The following tables reference PSP_PAYROLL_SOURCES through documented foreign-key relationships:

• PSP_ADJUSTMENT_LINES – joined on SOURCE_TYPE (with SOURCE_CODE used as the secondary lookup key).
• PSP_ADJUSTMENT_LINES_HISTORY – historical archive of adjustment lines, joined on SOURCE_TYPE and SOURCE_CODE.
• PSP_PAYROLL_CONTROLS – joined on SOURCE_TYPE via the PAYROLL_SOURCE_CODE column.

These dependent tables form the transactional core of Labor Distribution, and PSP_PAYROLL_SOURCES acts as the shared reference hub validating their source codes. All relationships are expressed in the ETRM metadata under the PSP schema, and the table is documented as VALID in both 12.1.1 and 12.2.2 environments.