Search Results pay_ca_soe_dedn_balances_v




Overview
Explore in AI Chat

Overview

PAY_CA_SOE_DEDN_BALANCES_V is a payroll reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It supplies the deduction and balance data required by the Canadian Statement of Earnings (SOE) process within the PAY — Payroll product. The view joins assignment action, payroll action, element classification, element type, and balance type information, then resolves the actual balance value through the PAY_CA_BALANCE_PKG package using the business rules defined for Canadian payroll reporting. Its role is to present, at the assignment action level, the deduction and balance figures that feed the SOE report, allowing the report generator to retrieve already-computed, jurisdiction-aware values rather than reconstructing the calculation logic in report SQL.

Underlying Base Objects

The view is defined over a mixture of synonyms, packages, and views. The base synonyms are PAY_ASSIGNMENT_ACTIONS, PAY_PAYROLL_ACTIONS, PAY_ELEMENT_CLASSIFICATIONS, PAY_ELEMENT_TYPES_F, PAY_ELEMENT_TYPES_F_TL, PAY_BALANCE_TYPES, and PAY_RUN_RESULTS, which resolve to the corresponding APPS base tables. Supporting packages include HR_BALANCES (specifically HR_BALANCES.DECODE_BALANCE), HR_GENERAL (HR_GENERAL.DECODE_LOOKUP for CA_SOE_SHORT_NAME), PAY_CA_BALANCE_PKG (CALL_CA_BALANCE_GET_VALUE), and PAY_CA_BALANCE_VIEW_PKG (GET_SESSION_VAR for REPORT_LEVEL and JURISDICTION_CODE). The view also references PAY_CA_FED_EE_V and PAY_CA_PROV_EE_V. The driver of the join is PAY_ASSIGNMENT_ACTIONS, linked to PAY_PAYROLL_ACTIONS and then to element classification, element type, element type translation, and balance type via an ORDERED USE_NL hint with index-driven access paths, which reflects the tuning approach used for high-volume payroll extraction.

Key Columns

  • ASSIGNMENT_ID and ASSIGNMENT_ACTION_ID — identify the assignment and the specific action row being reported.
  • TAX_UNIT_ID — the tax unit for which the balance is calculated.
  • ELEMENT_TYPE_ID and CLASSIFICATION_ID — the element and its classification, used to categorize the deduction or earning.
  • CLASSIFICATION_NAME — the descriptive name of the classification, such as a deduction classification.
  • REPORTING_NAME — the element reporting name, subject to short-name substitution through HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME', ...).
  • ELEMENT_NAME — the translated element name from PAY_ELEMENT_TYPES_F_TL.
  • DECODE_BALANCE result — the balance display name derived from the balance type.
  • PROCESSING_PRIORITY — the element processing order.
  • Calculated balance value — returned by PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE, with the balance context varying by PAYROLL_ACTIONS.ACTION_TYPE: 'R' and 'Q' use 'RUN', while 'P' and 'U' use 'CURRENT'.

The DECODE on ACTION_TYPE is central to the view: it selects the correct balance dimension and passes session variables such as REPORT_LEVEL and JURISDICTION_CODE so that the returned figure matches the reporting context.

Common Use Cases and Queries

Typical usage is SOE report generation and reconciliation of deduction balances for a payroll run. A representative query selecting the reporting columns for a given assignment follows:

  • SELECT assignment_id, assignment_action_id, tax_unit_id, reporting_name, element_name, classification_name, processing_priority FROM apps.pay_ca_soe_dedn_balances_v WHERE assignment_id = :p_assignment_id ORDER BY processing_priority;

Because the balance value is computed through session variables, queries must be executed in a session where PAY_CA_BALANCE_VIEW_PKG has set REPORT_LEVEL and JURISDICTION_CODE, normally by the SOE report concurrent program. Direct ad hoc queries outside that context return NULL or incomplete balance values. The view is therefore best treated as a report-support object rather than a general-purpose balance source.