Search Results pay_ca_prov_ee_v




Overview
Explore in AI Chat

Overview

PAY_CA_PROV_EE_V is a PL/SQL-based view in the APPS schema belonging to the Payroll (PAY) product family, specifically scoped to the Canadian localization. Its name denotes a "Provincial Employee" reporting construct: it surfaces, for a given assignment and assignment action, the values of provincial tax-related balances and balance sets as of several time horizons — CURRENT, RUN, PYDATE (period-to-date), MONTH, and QTD (quarter-to-date). Rather than storing data itself, the view acts as a calculated reporting layer that dynamically invokes balance retrieval logic through the PAY_CA_BALANCE_PKG package at query time.

Because Canadian payroll reporting requires provincial breakdowns (for example, provincial tax, CPP, and EI categorizations resolved by province), the view projects balance reporting names concatenated with a province abbreviation, and exposes parallel value columns for each time horizon. It is designed primarily for inquiry, reporting, and extraction against completed or in-progress payroll actions, and is commonly referenced by Canadian payroll reports regulated by CRA provincial remittance requirements.

Underlying Base Objects

The view is defined over the following documented objects:

Join logic links each assignment action to its payroll action and its applicable balance set/balance type, then resolves the province through the provinces view. Values are computed lazily via the balance package, meaning performance depends heavily on session context (REPORT_LEVEL and BUSINESS_GROUP must be set before querying).

Key Columns

  • ASSIGNMENT_ID, ASSIGNMENT_ACTION_ID, TAX_UNIT_ID — identify the employee assignment, the action instance, and the tax unit context.
  • ACTION_STATUS, ACTION_TYPE, EFFECTIVE_DATE, PAYROLL_ACTION_ID — payroll action context for filtering and traceability.
  • BALANCE_NAME — the underlying balance definition (from PBS.ATTRIBUTE_1).
  • REPORTING_NAME and BASE_REPORTING_NAME — reporting labels with the province abbreviation appended in parentheses, e.g., "Provincial Tax (ON)".
  • PROVINCE_ABBREV, LEGISLATION_CODE, BUSINESS_GROUP_ID — Canadian province, legislative, and business group context.
  • CURRENT_VALUE, RUN_VALUE, PYDATE_VALUE, MONTH_VALUE, QTD_VALUE — balance amounts returned per time horizon via PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE.

Common Use Cases and Queries

Typical uses include provincial tax reporting, year-to-date and quarter-to-date remittance extracts, payroll audit inquiries, and ad hoc reconciliation of assignment action balances. Because the value columns are function-driven, callers must initialize session variables first.

  • Inquiry for a single assignment action: SELECT assignment_action_id, province_abbrev, reporting_name, current_value, run_value, qtd_value FROM apps.pay_ca_prov_ee_v WHERE assignment_action_id = :p_aa_id;
  • Provincial summary by province: SELECT province_abbrev, reporting_name, SUM(qtd_value) FROM apps.pay_ca_prov_ee_v GROUP BY province_abbrev, reporting_name;
  • Filtered to completed actions: SELECT * FROM apps.pay_ca_prov_ee_v WHERE action_status = 'C' AND effective_date BETWEEN :p_start AND :p_end;

Session variables REPORT_LEVEL and BUSINESS_GROUP should be populated via PAY_CA_BALANCE_VIEW_PKG before execution; otherwise the returned values may be null or incorrect.