Results for “psp_adjustment_lines_arch”

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

Overview

PSP_ADJUSTMENT_LINES_ARCH is an archive table within the PSP (Labor Distribution) product of Oracle E-Business Suite. It stores the archived Adjustment Lines History, retaining historical labor distribution adjustment records that have been moved out of the active transaction tables for performance, audit, and retention purposes. In Oracle EBS 12.1.1 and 12.2.2, the table resides in the PSP schema and carries a status of VALID, with 58 documented columns. It is owned by the Labor Distribution module, which handles costing, effort reporting, and payroll distribution for grants and projects.

Based on the foreign-key structure, the table is classified heuristically as a standalone data object in Data Vault terms. This classification suggests that the table does not function as a traditional hub or link that anchors shared business keys across the warehouse. Instead, it serves as a fact-like or satellite-like repository of adjustment history, referencing related dimensions (payroll controls, time periods, awards, effort reports) without itself being the primary target of foreign-key dependencies.

Key Information Stored

The primary key is PSP_ADJUSTMENT_LINES_ARCH_PK, defined on the surrogate column ADJUSTMENT_LINE_ID. A unique index, PSP_ADJUSTMENT_LINES_ARCH_U, also enforces uniqueness on ADJUSTMENT_LINE_ID, confirming it as the primary business-key candidate and the primary identifier for each archived adjustment line.

The most significant columns captured by the table include:

• ADJUSTMENT_LINE_ID — the surrogate primary key uniquely identifying each archived adjustment line.
• PERSON_ID and ASSIGNMENT_ID — the employee and assignment affected by the adjustment.
• ELEMENT_TYPE_ID — the payroll element type that the adjustment line is associated with.
• DISTRIBUTION_DATE and EFFECTIVE_DATE — key date fields driving accounting periods and effective dating.
• DISTRIBUTION_AMOUNT and DR_CR_FLAG — the monetary value and debit/credit indicator of the adjustment.
• PAYROLL_CONTROL_ID and TIME_PERIOD_ID — references to the payroll control and time period.
• GL_CODE_COMBINATION_ID and SET_OF_BOOKS_ID — the accounting code combination and ledger context.
• PROJECT_ID, TASK_ID, AWARD_ID, EXPENDITURE_TYPE, and EXPENDITURE_ORGANIZATION_ID — the project costing and award dimensions.
• EFFORT_REPORT_ID and INCLUDE_IN_ER_FLAG — effort reporting linkage and inclusion flag.
• STATUS_CODE, SOURCE_CODE, and SOURCE_TYPE — processing status and origin information.
• REVERSAL_ENTRY_FLAG, ORIGINAL_LINE_FLAG, and VERSION_NUM — controls for reversal handling and versioning.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield columns for extensibility.
• LAST_UPDATE_DATE, CREATED_BY, and CREATION_DATE — standard audit columns.

Common Use Cases and Queries

Typical use cases include historical labor distribution reporting, audit trail reconstruction, reconciliation between active and archived adjustment data, and effort reporting verification. A common query pattern joins archived lines to the current adjustment line table to compare pre- and post-archive values, or to retrieve adjustment history for a given person or award:

• Selecting all archived adjustment lines for a specific person and date range, filtered by DISTRIBUTION_DATE.
• Joining to PER_TIME_PERIODS via TIME_PERIOD_ID to report adjustments by payroll period.
• Joining to PSP_PAYROLL_CONTROLS via PAYROLL_CONTROL_ID to associate archived lines with a payroll run.
• Aggregating DISTRIBUTION_AMOUNT by AWARD_ID or PROJECT_ID for grant costing summaries.
• Filtering on REVERSAL_ENTRY_FLAG or ORIGINAL_LINE_FLAG to isolate reversal entries or original adjustments.
• Reporting on ATTRIBUTE1–ATTRIBUTE15 where clients have configured descriptive flexfields for custom tracking.

Related Objects

The table maintains foreign-key relationships with several key Oracle EBS objects. The most significant related objects and their join columns are:

• PSP_PAYROLL_CONTROLS — joined via PAYROLL_CONTROL_ID; identifies the payroll control record associated with the adjustment.
• PER_TIME_PERIODS — joined via TIME_PERIOD_ID; provides the payroll period context.
• IGF_AW_AWARD_ALL — joined via AWARD_ID; links the adjustment to the award record for grants management.
• PSP_EFF_REPORTS — joined via EFFORT_REPORT_ID; connects archived adjustment lines to effort reporting.
• PSP_ADJUSTMENT_LINES — the active adjustment line table (implied source of the archive); useful for active-to-archive comparisons on ADJUSTMENT_LINE_ID.
• PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F — related via PERSON_ID and ASSIGNMENT_ID for employee and assignment details.

These relationships make PSP_ADJUSTMENT_LINES_ARCH a foundational archive object for labor distribution auditing, reconciliation, and historical reporting in Oracle EBS.