Results for “psp_distribution_interface”

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

Overview

PSP_DISTRIBUTION_INTERFACE is a Labor Distribution interface table owned by the PSP (Oracle Grants/Project Labor Distribution) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores distribution lines originating from pre-generated sources such as timecards, prior to their consumption by the Labor Distribution processes that feed Oracle Projects and Oracle General Ledger. Rows in this table represent staged, pre-validated distribution detail for employee labor costs, carrying payroll, project, award, and accounting flexfield context together with status and error information used during the distribution import cycle.

The table is a high-volume transient staging structure. Records progress through a status lifecycle (STATUS_CODE, ERROR_CODE) as distribution generation and interface processing validate and post them. Based on the documented foreign-key topology, the table leans toward a satellite classification in a Data Vault model: it carries descriptive, changing transactional detail (amounts, status, dates, attributes) keyed by a surrogate identifier, with multiple dimension references rather than acting as a pure junction. This should be treated as a modeling suggestion rather than an authoritative classification, given the interface table's hybrid operational role.

Key Information Stored

The primary key is PSP_DISTRIBUTION_INTERFACE_PK, defined on DISTRIBUTION_INTERFACE_ID, the system-generated surrogate identifier. A unique index, PSP_DISTRIBUTION_INTERFACE_U1, spans BATCH_NAME and DISTRIBUTION_INTERFACE_ID, making BATCH_NAME the principal business-key candidate for grouping and controlling interface loads.

• DISTRIBUTION_INTERFACE_ID — surrogate primary key.
• BATCH_NAME — business grouping key for the interface run.
• PERSON_ID, ASSIGNMENT_ID, ELEMENT_TYPE_ID — the employee, assignment, and payroll element context.
• DISTRIBUTION_DATE, DISTRIBUTION_AMOUNT — effective date and monetary value of the distribution line.
• DR_CR_FLAG, SOURCE_CODE, STATUS_CODE, ERROR_CODE — debit/credit indicator, source system, processing status, and rejection reason.
• GL_CODE_COMBINATION_ID — accounting flexfield combination for posting.
• PROJECT_ID, TASK_ID, EXPENDITURE_TYPE, EXPENDITURE_ORGANIZATION_ID, AWARD_ID — Project and Grants dimension references.
• TIME_PERIOD_ID, PAYROLL_ID, SET_OF_BOOKS_ID, BUSINESS_GROUP_ID, CURRENCY_CODE — period, payroll, ledger, operating unit (business group), and currency context.
• GL_POSTING_OVERRIDE_DATE, GMS_POSTING_OVERRIDE_DATE, SUSPENSE_* columns — posting overrides and suspense-account fallback for failed distributions.
• ATTRIBUTE1 through ATTRIBUTE15 with ATTRIBUTE_CATEGORY — the standard EBS descriptive flexfield.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical uses include monitoring interface load results, diagnosing rejected distribution lines, and reconciling labor costs by batch, project, award, or period.

• Batch status summary: SELECT BATCH_NAME, STATUS_CODE, COUNT(*), SUM(DISTRIBUTION_AMOUNT) FROM PSP.PSP_DISTRIBUTION_INTERFACE GROUP BY BATCH_NAME, STATUS_CODE.
• Error triage: SELECT DISTRIBUTION_INTERFACE_ID, PERSON_ID, ERROR_CODE, DISTRIBUTION_AMOUNT FROM PSP.PSP_DISTRIBUTION_INTERFACE WHERE STATUS_CODE = 'ERROR' ORDER BY CREATION_DATE.
• Project/award rollup: join to PA_PROJECTS_ALL and IGF_AW_AWARD_ALL on PROJECT_ID and AWARD_ID to report labor distribution by project and award.
• GL reconciliation: join GL_CODE_COMBINATIONS on GL_CODE_COMBINATION_ID and GL_SETS_OF_BOOKS on SET_OF_BOOKS_ID to verify posting combinations.
• Suspense review: query rows where SUSPENSE_AUTO_GLCCID or SUSPENSE_ORG_ACCOUNT_ID is populated to identify automated suspense assignments.

Related Objects

The table is tightly coupled to downstream staging and history structures, and to a broad set of reference dimensions through documented foreign keys.

• PSP_PRE_GEN_DIST_LINES — child table referencing DISTRIBUTION_INTERFACE_ID; holds pre-generated distribution detail.
• PSP_PRE_GEN_DIST_LINES_HISTORY and PSP_PRE_GEN_DIST_LINES_ARCH — historical and archived counterparts referencing the same key.
• PER_TIME_PERIODS (TIME_PERIOD_ID), HR_ALL_ORGANIZATION_UNITS (EXPENDITURE_ORGANIZATION_ID), and GL_SETS_OF_BOOKS (SET_OF_BOOKS_ID) — period, organization, and ledger dimensions.
• PA_PROJECTS_ALL (PROJECT_ID), PA_TASKS (TASK_ID), PA_EXPENDITURE_TYPES (EXPENDITURE_TYPE), and IGF_AW_AWARD_ALL (AWARD_ID) — Project and Grants dimensions.
• GL_CODE_COMBINATIONS (GL_CODE_COMBINATION_ID) — accounting flexfield reference used for distribution posting.

These relationships make PSP_DISTRIBUTION_INTERFACE the central staging point through which timecard-derived labor costs are validated and routed into Project Costing and General Ledger.