Results for “psp_distribution_lines_arch”

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

Overview

PSP_DISTRIBUTION_LINES_ARCH is an archived history table within the PSP (Labor Distribution) product of Oracle E-Business Suite, owned by the PSP schema. It stores archived Distribution Lines History — that is, the historical, non-current snapshot of effort and payroll cost distribution lines that were previously held in the online PSP_DISTRIBUTION_LINES table. In Oracle EBS 12.1.1 and 12.2.2, labor distribution processes generate distribution lines that allocate payroll and effort costs to projects, awards, and general ledger accounts; when those lines are superseded, purged from the active table, or retained for audit and reporting purposes, they are written to this archive. The table is classified as VALID and exposes 48 documented columns, keyed by a surrogate primary key.

From a data vault modeling perspective, the heuristic classification of this object is standalone. Because it is a historical snapshot rather than a pure insert-only record set with a single immutable business key, it should be treated as a satellite-style history table rather than a hub or link. The classification is a modeling suggestion only; the table does carry foreign key references that link it to parent effort reports, element type accounts, labor schedules, and payroll sub-lines.

Key Information Stored

The surrogate primary key and single unique index — PSP_DISTRIBUTION_LINES_ARCH_PK and PSP_DISTRIBUTION_LINES_ARCH_U1 — both resolve to the single column DISTRIBUTION_LINE_ID. This column therefore serves as both the surrogate identifier and the documented business-key candidate for the archived row.

• DISTRIBUTION_LINE_ID — surrogate primary key and unique business-key candidate identifying each archived distribution line.
• DISTRIBUTION_DATE and EFFECTIVE_DATE — the accounting/effort date and effective date on which the distribution applies.
• DISTRIBUTION_AMOUNT — the monetary or effort amount allocated by the line.
• STATUS_CODE, DEFAULT_REASON_CODE, and SUSPENSE_REASON_CODE — the processing state and the reasons a line was defaulted or placed in suspense.
• EFFORT_REPORT_ID — foreign key to PSP_EFF_REPORTS, identifying the effort report that produced the line.
• ELEMENT_ACCOUNT_ID — foreign key to PSP_ELEMENT_TYPE_ACCOUNTS, indicating the element/account charged.
• ORG_SCHEDULE_ID — foreign key to PSP_DEFAULT_LABOR_SCHEDULES, the labor schedule used to derive the organization.
• PAYROLL_SUB_LINE_ID — foreign key to PSP_PAYROLL_SUB_LINES, linking the distribution to its payroll source.
• DEFAULT_ORG_ACCOUNT_ID, SUSPENSE_ORG_ACCOUNT_ID, AUTO_GL_CODE_COMBINATION_ID, and AUTO_EXPENDITURE_TYPE — the GL code combinations and expenditure types applied automatically.
• SET_OF_BOOKS_ID and BUSINESS_GROUP_ID — the ledger and operating unit context for the row.
• GL_PROJECT_FLAG, REVERSAL_ENTRY_FLAG, INCLUDE_IN_ER_FLAG, and ADJ_ACCOUNT_FLAG — processing indicators controlling GL transfer, reversal treatment, effort-report inclusion, and adjustment accounting.
• ATTRIBUTE_CATEGORY through ATTRIBUTE10 — the standard EBS descriptive flexfield columns for client-specific attributes.

Common Use Cases and Queries

The primary use case is audit and historical reporting: reconstructing how labor costs were distributed at a prior point in time, independent of the active PSP_DISTRIBUTION_LINES data. Typical queries include retrieving all archived lines for a given effort report, reconciling suspense or defaulted distributions, and reporting distributions by ledger or business group.

• Point lookup by the documented key: SELECT * FROM PSP.PSP_DISTRIBUTION_LINES_ARCH WHERE DISTRIBUTION_LINE_ID = :distribution_line_id;
• History for an effort report: SELECT DISTRIBUTION_LINE_ID, DISTRIBUTION_DATE, DISTRIBUTION_AMOUNT, STATUS_CODE FROM PSP.PSP_DISTRIBUTION_LINES_ARCH WHERE EFFORT_REPORT_ID = :effort_report_id ORDER BY DISTRIBUTION_DATE;
• Suspense/default analysis: SELECT STATUS_CODE, SUSPENSE_REASON_CODE, DEFAULT_REASON_CODE, COUNT(*) FROM PSP.PSP_DISTRIBUTION_LINES_ARCH GROUP BY STATUS_CODE, SUSPENSE_REASON_CODE, DEFAULT_REASON_CODE;
• Ledger-scoped reconciliation filtered on SET_OF_BOOKS_ID and BUSINESS_GROUP_ID.

Related Objects

The following objects are the most significant dependencies, based on the documented foreign key relationships:

• PSP_EFF_REPORTS — joined on EFFORT_REPORT_ID; the effort report that originated the distribution.
• PSP_ELEMENT_TYPE_ACCOUNTS — joined on ELEMENT_ACCOUNT_ID; the element/account charged.
• PSP_DEFAULT_LABOR_SCHEDULES — joined on ORG_SCHEDULE_ID; the labor schedule used for organization defaults.
• PSP_PAYROLL_SUB_LINES — joined on PAYROLL_SUB_LINE_ID; the payroll source line.
• PSP_DISTRIBUTION_LINES — the active online counterpart from which archived rows originate and to which they correspond by DISTRIBUTION_LINE_ID.

These relationships make the archive table a dependent child of several PSP parent entities while functioning as its own standalone historical store for labor distribution reporting.