Search Results pspbv_payroll_trans_controls

Overview

PSPBV_PAYROLL_TRANS_CONTROLS is a read-only reporting view in the Oracle E-Business Suite Labor Distribution module (product code PSP). It exposes payroll transaction control information, providing a consolidated presentation layer over the base table PSP_PAYROLL_CONTROLS. The view is intended for reporting and integration scenarios in which payroll distribution balances must be examined by control record, run, batch, and accounting phase.

The PSPBV_ prefix denotes a Business View, a naming convention Oracle uses for views designed for inquiry and reporting rather than for transactional maintenance. Consistent with that convention, the view is defined with the WITH READ ONLY clause, which prevents DML against the view and confirms its intended use as a query-only surface.

Each row represents a payroll transaction control record. The control aggregates counts and amounts across several processing stages: sublines, distribution, Oracle Grants Management (OGM/GMS), and the General Ledger (GL). The presence of distinct GL and GMS phase columns reflects that Labor Distribution processing moves through accounting stages before final posting, and the control record tracks the monetary totals and phase state at each stage.

Underlying Base Objects

The view is defined over a single documented base object, PSP_PAYROLL_CONTROLS, aliased as PPC in the view text. No other base tables are referenced in the documented SELECT statement. The view text projects columns directly from this table without joins or aggregation of its own; all consolidation logic is performed by the underlying table.

Two columns in the view are derived through Oracle Application Object Library (AOL) lookup translation rather than stored as literal columns. The view text substitutes the literals '_LA:SOURCE_TYPE:PSP_LOOKUPS:PSP_SOURCE_TYPE:MEANING' and '_LA:STATUS_CODE:PSP_LOOKUPS:PSP_STATUS_CODE:MEANING' for the source type and status code columns. The _LA: token instructs the reporting layer to translate the stored lookup code into its meaning using the PSP_LOOKUPS lookup type, specifically PSP_SOURCE_TYPE and PSP_STATUS_CODE. The corresponding columns in the view are exposed as "_LA:PAYROLL_SOURCE_TYPE" and "_LA:BATCH_STATUS_FLAG".

Under ETRM 12.2.2 metadata the view is documented as not implemented in the ETRM database and records no referenced base objects beyond the excerpt above; the definition text nonetheless confirms PSP_PAYROLL_CONTROLS as the sole source.

Key Columns

• PAYROLL_CONTROL_ID — Primary identifier for the payroll transaction control record.
• BATCH_RUN_ID (RUN_ID) and BATCH_NAME — Identify the payroll run and batch to which the control belongs.
• PAYROLL_SOURCE_CODE and "_LA:PAYROLL_SOURCE_TYPE" — The source of the payroll transactions and its translated lookup meaning via PSP_SOURCE_TYPE.
• NUMBER_OF_CREDIT_TRANSACTIONS / NUMBER_OF_DEBIT_TRANSACTIONS — Counts of credit and debit lines in the control.
• TOTAL_DEBIT_AMOUNT / TOTAL_CREDIT_AMOUNT — Aggregate debit and credit amounts for the control.
• TOTAL_SUBLINES_*, TOTAL_DIST_*, GRANTS_TOTAL_*, GL_TOTAL_*_SHARE_AMOUNT — Stage-specific amounts for sublines, distribution, Grants, and GL share.
• "_LA:BATCH_STATUS_FLAG" — Status of the batch, translated through PSP_STATUS_CODE.
• GL_PHASE / GMS_PHASE — Accounting phase indicators for General Ledger and Grants processing.
• PAYROLL_ACTION_ID, TIME_PERIOD_ID, PAYROLL_ID, SET_OF_BOOKS_ID, BUSINESS_GROUP_ID — Contextual keys for action, period, payroll, ledger, and business group.
• ADJ_SUM_BATCH_NAME and the standard audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE).

Common Use Cases and Queries

The view supports reconciliation of payroll distribution balances across stages, verification of batch processing status, and Grants/GL phase monitoring. A representative query retrieving control balances for a given payroll run follows:

• SELECT payroll_control_id, batch_name, payroll_source_code, total_debit_amount, total_credit_amount, gl_phase, gms_phase FROM pspbv_payroll_trans_controls WHERE batch_run_id = :run_id;
• SELECT payroll_control_id, batch_name, "_LA:BATCH_STATUS_FLAG", gl_total_debit_share_amount, gl_total_credit_share_amount FROM pspbv_payroll_trans_controls WHERE set_of_books_id = :sob_id;
• SELECT payroll_control_id, grants_total_debit__amount, grants_total_credit_amount FROM pspbv_payroll_trans_controls WHERE gms_phase IS NOT NULL;

Because the view is read-only, it is suited to extracts, concurrent report sources, and integration queries that read payroll control state without altering it.