Search Results psp_payroll_controls_pk

Overview

PSP_PAYROLL_CONTROLS is a core table within the PSP (Labor Distribution) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. It serves as the control repository for all payroll transactions imported into Oracle Labor Distribution for costing and distribution processing. Each row represents a payroll control record — a logical batch of imported payroll data tied to a single payroll action, source, and accounting period — that drives the flow of payroll costs from Oracle Payroll through Labor Distribution and ultimately into Oracle General Ledger.

Because payroll imports arrive in batches, this table acts as the anchor that tracks balancing amounts, debit and credit counts, and posting status across every stage of the distribution lifecycle, including the pre-generation phase, the general ledger posting phase, and the Grants Management (GMS) posting phase. From a dimensional modeling perspective, the ETRM metadata classifies PSP_PAYROLL_CONTROLS as hub-leaning. This suggests treating PAYROLL_CONTROL_ID as a stable business key surrounded by satellite tables holding descriptive and status attributes, while the many child tables function as link or transaction tables.

Key Information Stored

The table is owned by the PSP schema and contains 39 documented columns. The primary key is the surrogate identifier PAYROLL_CONTROL_ID, backed by the unique index PSP_PAYROLL_CONTROLS_PK; the unique index PSP_PAYROLL_CONTROLS_U1 also covers this column, confirming it as the sole business-key candidate.

• PAYROLL_CONTROL_ID — surrogate primary key and the foreign key referenced by all child line tables.
• PAYROLL_ACTION_ID — links the control record to the originating payroll action in PAY_PAYROLL_ACTIONS.
• PAYROLL_ID and RUN_ID — identify the specific payroll and payroll run that generated the imported data.
• PAYROLL_SOURCE_CODE and SOURCE_TYPE — describe the origin of the import, resolving to PSP_PAYROLL_SOURCES.
• TIME_PERIOD_ID — ties the control record to the accounting period defined in PER_TIME_PERIODS.
• SET_OF_BOOKS_ID — the ledger to which distributions will post, referencing GL_SETS_OF_BOOKS.
• BUSINESS_GROUP_ID — the HR business group context, referencing HR_ALL_ORGANIZATION_UNITS.
• BATCH_NAME and ADJ_SUM_BATCH_NAME — identifiers for the main and adjustment summary batches.
• NUMBER_OF_DR, NUMBER_OF_CR, TOTAL_DR_AMOUNT, TOTAL_CR_AMOUNT — balancing controls confirming debit and credit counts and totals.
• DIST_DR_AMOUNT, DIST_CR_AMOUNT, OGM_DR_AMOUNT, OGM_CR_AMOUNT — distributed and offset-generated amounts used in reconciliation.
• GL_DR_AMOUNT, GL_CR_AMOUNT — amounts actually posted to the general ledger.
• PHASE, STATUS_CODE, GL_PHASE, and GMS_PHASE — track processing state through distribution and posting.
• ARCHIVE_FLAG — marks records that have been archived.
• PARENT_PAYROLL_CONTROL_ID — supports hierarchical relationships between control records.

Common Use Cases and Queries

Typical use cases include payroll cost reconciliation, distribution status tracking, and GL posting audits. A common query identifies control records that have not yet posted to the general ledger:

• SELECT payroll_control_id, batch_name, status_code, gl_phase FROM psp_payroll_controls WHERE status_code != 'COMPLETE';
• Reconciliation of imported totals against distributed totals: compare TOTAL_DR_AMOUNT with DIST_DR_AMOUNT and GL_DR_AMOUNT to detect unbalanced or partially posted batches.
• Batch-level reporting joined to PSP_PAYROLL_LINES and PSP_SUMMARY_LINES for detailed distribution analysis by employee or assignment.
• Period reporting using TIME_PERIOD_ID and SET_OF_BOOKS_ID to aggregate payroll costs by ledger and accounting period.
• Archive audits filtering on ARCHIVE_FLAG and joining history tables such as PSP_PRE_GEN_DIST_LINES_HISTORY.

Related Objects

PSP_PAYROLL_CONTROLS sits at the center of a hub-and-spoke relationship structure. Its parent foreign keys reference PSP_PAYROLL_SOURCES (SOURCE_TYPE, PAYROLL_SOURCE_CODE), PER_TIME_PERIODS (TIME_PERIOD_ID), PAY_PAYROLL_ACTIONS (PAYROLL_ACTION_ID), GL_SETS_OF_BOOKS (SET_OF_BOOKS_ID), and HR_ALL_ORGANIZATION_UNITS (BUSINESS_GROUP_ID). Numerous child tables depend on it through PAYROLL_CONTROL_ID, including PSP_PAYROLL_LINES, PSP_SUMMARY_LINES, PSP_PRE_GEN_DIST_LINES, PSP_ADJUSTMENT_LINES, and their archived counterparts such as PSP_PRE_GEN_DIST_LINES_ARCH, PSP_PRE_GEN_DIST_LINES_HISTORY, PSP_ADJUSTMENT_LINES_ARCH, and PSP_TEMP_ORIG_LINES. These relationships make PSP_PAYROLL_CONTROLS the essential join point for any query spanning imported payroll, distribution detail, and GL posting outcomes.