Results for “pay_report_totals”

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

Overview

PAY_REPORT_TOTALS is a table owned by the HR schema within the Oracle E-Business Suite Payroll (PAY) product module. Per the ETRM documentation, its designated purpose is to serve as a temporary (staging/working) table used in the generation of the T4, T4A, and RL1 Register Reports. These reports are Canadian statutory payroll tax filings. T4 slips summarize employment income and deductions, T4A slips cover pension and other income, and the RL1 (Relevé 1) is the corresponding Quebec provincial statement. The table therefore acts as an intermediate aggregation and formatting store that the report process populates before final report output is produced, rather than as a permanent transactional ledger.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is standalone — the table does not sit as a natural hub, link, or satellite within the surrounding relational graph. It has no documented surrogate primary key of its own and instead carries multiple foreign-key references outward, which is characteristic of a transient processing table rather than a durable entity. Modelers should treat it as an operational staging object to be consumed and cleared by the reporting process.

Key Information Stored

The documented physical schema in ETRM 12.2.2 lists 15 columns. The most significant are the two keyed reference columns and the repeating generic attribute/value pairs that carry the report data:

• PAYROLL_ACTION_ID — references PAY_PAYROLL_ACTIONS; identifies the payroll run (action) to which the aggregated totals belong.
• ASSIGNMENT_ACTION_ID — references PAY_ASSIGNMENT_ACTIONS; identifies the specific assignment-level action being reported.
• BUSINESS_GROUP_ID — the business group context for the row, consistent with HR/PAY multi-tenancy.
• INFORMATION_TYPE1 … INFORMATION_TYPE5 — up to five generic descriptors that label the corresponding information values (for example, box or slip category identifiers).
• INFORMATION_VALUE1 … INFORMATION_VALUE5 — the numeric or textual amounts that pair positionally with the information types.
• ATTRIBUTE_TYPE / ATTRIBUTE_VALUE — an additional generic descriptor/value pair used by the report logic.

The surrogate keys are the adopted foreign keys PAYROLL_ACTION_ID and ASSIGNMENT_ACTION_ID, which together anchor each row to a payroll run and assignment action. No separate single-column unique index is documented; the business-key candidate is the combination of PAYROLL_ACTION_ID plus ASSIGNMENT_ACTION_ID, optionally extended by BUSINESS_GROUP_ID, since the generic INFORMATION_TYPE/INFORMATION_VALUE columns are repeating and not themselves unique.

Common Use Cases and Queries

Because the table is populated transiently by the T4/T4A/RL1 Register Report process, the dominant use case is diagnostic and audit-oriented: confirming that a payroll run produced the expected aggregated figures before or after slip generation. A typical query joins back to the driving payroll action to confirm scope:

• Inspect totals for a payroll action: select PAYROLL_ACTION_ID, ASSIGNMENT_ACTION_ID, INFORMATION_TYPE1, INFORMATION_VALUE1 from PAY_REPORT_TOTALS where PAYROLL_ACTION_ID = :action_id.
• Trace an assignment action: filter on ASSIGNMENT_ACTION_ID to see which report lines were generated for a single assignment.
• Cross-check with the payroll run: join PAY_REPORT_TOTALS.PAYROLL_ACTION_ID to PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID to validate the run name, period, and action status.
• Reconciliation reporting: aggregate INFORMATION_VALUE columns by information type to compare against the final T4/T4A/RL1 output.

Because the table is temporary in nature, queries should be run while report processing data is present and should not be relied upon for historical trends.

Related Objects

The following objects are the most significant relative to PAY_REPORT_TOTALS, based on the documented FK relationships and the PAY module context:

• PAY_PAYROLL_ACTIONS — joined on PAYROLL_ACTION_ID; the parent payroll run definition.
• PAY_ASSIGNMENT_ACTIONS — joined on ASSIGNMENT_ACTION_ID; the assignment-level action record.
• PAY_ASSIGNMENT_ACTION_STATISTICS — the standard repository of assignment action totals, frequently compared against this report staging table.
• PER_ALL_ASSIGNMENTS_F — provides the employee/assignment context for each ASSIGNMENT_ACTION_ID.
• PAY_PAYROLL_ACTIONS / PAY_RUN_RESULTS — upstream source of the values aggregated into report totals.

These relationships are grounded in the documented FK data; additional report-specific views and concurrent program definitions are not enumerated in the ETRM excerpt and should be verified against the target instance.