Results for “psp_distribution_lines_u1”

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

Overview

PSP.PSP_DISTRIBUTION_LINES is a transactional table in the Oracle E-Business Suite Payroll and Labor Distribution (PSP) schema. It stores distributed Oracle Payroll and non-Oracle payroll sublines that have not yet been transferred to General Ledger, Grants Accounting, or Project Accounting. In this role it functions as the staging and holding area for labor cost distributions produced by the labor distribution engine, capturing both the amounts to be posted and the accounting attributes required to route those amounts to the correct destination ledgers.

The table is a permanent, transaction-level data object rather than a reference or setup entity. Records are created during distribution processing and persist until the corresponding transfers complete, at which point the status is advanced and the rows are eligible for archival or purging. Based on the mined foreign-key structure, the recommended Data Vault classification is link: the table primarily resolves relationships among payroll sublines, effort reports, schedule lines, summary lines, set of books, element accounts, labor schedules, and business groups, with the unique distribution line identifier acting as the link surrogate key. This classification is a modeling suggestion drawn from dependency analysis rather than a mandated Oracle construct.

Key Information Stored

The surrogate primary key is DISTRIBUTION_LINE_ID, a NUMBER(10) column enforced by the unique index PSP_DISTRIBUTION_LINES_U1. It is also the only documented business-key candidate in the metadata; the three non-unique indexes (PSP_DISTRIBUTION_LINES_N1 on PAYROLL_SUB_LINE_ID, N2 on SUMMARY_LINE_ID, and N3 on SUSPENSE_ORG_ACCOUNT_ID) support access paths rather than uniqueness. The most significant columns include:

• DISTRIBUTION_DATE and EFFECTIVE_DATE — the distribution date itself, plus the accounting date used when transferring to General Ledger and the expenditure item date used when transferring to Grants Accounting.
• DISTRIBUTION_AMOUNT — the monetary amount being distributed.
• STATUS_CODE — the lifecycle state of the line: N (New), S (Summarized), or A (Accepted by General Ledger or Grants Accounting).
• DEFAULT_REASON_CODE and SUSPENSE_REASON_CODE — the reason codes explaining why the default or suspense account was used, with the suspense code defined at length 50.
• PAYROLL_SUB_LINE_ID — the originating payroll subline; the principal linkage back to source payroll detail.
• SUMMARY_LINE_ID and EFFORT_REPORT_ID — the summarized labor line and the effort report associated with the distribution.
• SCHEDULE_LINE_ID, ORG_SCHEDULE_ID — the schedule line and organization schedule driving the distribution.
• SET_OF_BOOKS_ID — the ledger to which the distribution ultimately posts.
• DEFAULT_ORG_ACCOUNT_ID, SUSPENSE_ORG_ACCOUNT_ID, and ELEMENT_ACCOUNT_ID — the default, suspense, and global element account identifiers.
• GL_PROJECT_FLAG — indicates whether the line points to General Ledger or Grants/Project Accounting.
• BUSINESS_GROUP_ID — the business group owning the line.

Additional columns such as REVERSAL_ENTRY_FLAG, PRE_DISTRIBUTION_RUN_FLAG, FUNDING_SOURCE_CODE, and the ten ATTRIBUTE flex columns carry configuration, reversal, and descriptive context. The documented schema contains 47 columns in total.

Common Use Cases and Queries

Typical scenarios include reconciling distributed labor amounts before transfer, identifying lines still pending transfer to GL or Grants Accounting, diagnosing suspense account usage, and reporting distribution detail by payroll subline or business group. The STATUS_CODE column is central to most of these queries.

• Locating all unposted lines: SELECT DISTRIBUTION_LINE_ID, DISTRIBUTION_AMOUNT, EFFECTIVE_DATE FROM PSP_DISTRIBUTION_LINES WHERE STATUS_CODE = 'N';
• Aggregating distribution by ledger: SELECT SET_OF_BOOKS_ID, SUM(DISTRIBUTION_AMOUNT) FROM PSP_DISTRIBUTION_LINES GROUP BY SET_OF_BOOKS_ID;
• Investigating suspense usage: SELECT DISTRIBUTION_LINE_ID, SUSPENSE_REASON_CODE FROM PSP_DISTRIBUTION_LINES WHERE SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL;
• Joining to payroll detail through the N1 index path on PAYROLL_SUB_LINE_ID to trace a distributed amount back to its source subline.

Related Objects

The following dependencies are the most significant, drawn from the documented foreign-key relationships:

• PSP.PSP_PAYROLL_SUB_LINES — joined via PAYROLL_SUB_LINE_ID; the source payroll subline for each distribution.
• PSP.PSP_SUMMARY_LINES — joined via SUMMARY_LINE_ID; the summarized line when status is S.
• PSP.PSP_EFFORT_REPORTS — joined via EFFORT_REPORT_ID for effort-based distributions.
• PSP.PSP_SCHEDULE_LINES — joined via SCHEDULE_LINE_ID.
• PSP.PSP_DEFAULT_LABOR_SCHEDULES — joined via ORG_SCHEDULE_ID.
• PSP.PSP_ELEMENT_TYPE_ACCOUNTS — joined via ELEMENT_ACCOUNT_ID for the global element account.
• GL.GL_SETS_OF_BOOKS — joined via SET_OF_BOOKS_ID to resolve the destination ledger.
• HR.HR_ALL_ORGANIZATION_UNITS — joined via BUSINESS_GROUP_ID to resolve the owning business group.