Results for “reason_code”

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

Overview

PSP_SUB_LINE_REASONS is a table in the PSP (Labor Distribution) product schema within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store the reasons associated with the creation of payroll sublines from payroll lines. In the labor distribution model, payroll processing frequently produces multiple downstream sublines from a single payroll line — for cost allocation, encumbrance adjustments, distribution corrections, or retroactive processing. This table captures the explainable "why" behind each such subdivision, giving administrators and auditors a queryable audit trail of subline generation events. The table has eight documented columns and is classified as VALID in the ETRM repository.

From a Data Vault modeling perspective, the mined heuristic classifies PSP_SUB_LINE_REASONS as satellite-leaning. This suggests it is best treated as a descriptive satellite: its rows record attributes and context about a parent business entity (the payroll subline) rather than serving as an independent hub or a pure relationship link. Practitioners designing a warehouse layer over PSP should consider anchoring this table to PSP_PAYROLL_SUB_LINES as the driving parent and capturing REASON_CODE plus the accompanying audit columns as satellite payload.

Key Information Stored

The documented physical schema contains eight columns. The most significant are:

• PAYROLL_SUB_LINE_ID — The identifier of the payroll subline for which the reason is recorded. This column participates in the primary key and is the sole documented foreign key, referencing PSP_PAYROLL_SUB_LINES.
• REASON_CODE — The coded reason explaining why the subline was created from its originating payroll line. It forms the second component of the composite primary key and is the principal business-meaning attribute.
• PARENT_LINE_ID — Identifies the parent payroll line from which the subline lineage originates, supporting hierarchical tracing of line-to-subline derivation.
• LAST_UPDATE_DATE — The date the row was most recently modified; a standard EBS audit column used for incremental extraction.
• LAST_UPDATED_BY — The application user who performed the most recent update.
• LAST_UPDATE_LOGIN — The login session identifier associated with the last update.
• CREATED_BY — The user who originally created the reason record.
• CREATION_DATE — The timestamp of original record creation.

Two key structures are documented. The primary key, PSP_SUB_LINE_REASONS_PK, is the composite (PAYROLL_SUB_LINE_ID, REASON_CODE), combining the foreign key to the parent subline with the reason code. The unique index PSP_SUB_LINE_REASONS_U1 covers the same column pair and therefore represents the documented business-key candidate: no two identical reason codes may exist for the same payroll subline. There is no separate single-column surrogate key; identity is derived from the foreign key plus the reason code, which is consistent with the satellite-leaning classification.

Common Use Cases and Queries

Typical reporting scenarios include auditing why payroll costs were redistributed, reconciling subline volumes by reason, and tracing a subline back to its parent payroll line. A representative query joining the satellite to its parent is:

• SELECT r.PAYROLL_SUB_LINE_ID, r.REASON_CODE, r.PARENT_LINE_ID, r.CREATION_DATE FROM PSP.PSP_SUB_LINE_REASONS r WHERE r.PAYROLL_SUB_LINE_ID = :sub_line_id; — retrieves all reasons recorded for a given subline.
• SELECT REASON_CODE, COUNT(*) FROM PSP.PSP_SUB_LINE_REASONS GROUP BY REASON_CODE ORDER BY 2 DESC; — profiles subline creation reasons across the enterprise.
• SELECT r.* FROM PSP.PSP_SUB_LINE_REASONS r, PSP.PSP_PAYROLL_SUB_LINES s WHERE r.PAYROLL_SUB_LINE_ID = s.PAYROLL_SUB_LINE_ID; — joins the reason rows to the parent subline for combined cost and reason reporting.
• Incremental extracts should filter on LAST_UPDATE_DATE using a high-water mark, since the table carries the standard EBS WHO audit columns.

Related Objects

• PSP_PAYROLL_SUB_LINES — The parent table referenced by the documented foreign key PSP_SUB_LINE_REASONS.PAYROLL_SUB_LINE_ID; the primary join path.
• PSP_SUB_LINE_REASONS_PK / PSP_SUB_LINE_REASONS_U1 — The primary key constraint and unique index enforcing (PAYROLL_SUB_LINE_ID, REASON_CODE).
• Payroll line and distribution tables in the PSP/PAY schemas — PARENT_LINE_ID relates the reason record to the originating payroll line, enabling line-to-subline traceability.
• Labor distribution cost allocation and encumbrance processes — These processes populate the table when payroll lines are subdivided, making it a dependent audit object.

Because the documented metadata lists only one foreign key, PSP_PAYROLL_SUB_LINES is the definitive parent dependency; all other relationships should be validated against the customer's specific PSP implementation before being relied upon for reporting.