Search Results pay_ca_fed_er_v




Overview
Explore in AI Chat

Overview

PAY_CA_FED_ER_V is an APPS-owned database view within the Oracle E-Business Suite Payroll (PAY) product family. It exposes Canadian federal employer-level balance information for assignment actions, resolving balances across multiple time dimensions — current, run, pay date, month, quarter-to-date, and period-to-date. The view is primarily used to support statutory reporting and payroll auditing requirements for Canadian legislation, where employers must reconcile federal remittance balances per assignment and tax unit against their payroll action history.

Because the view is defined over ASSIGNMENT_ACTION granularity, it returns one logical row per assignment action combined with a balance set entry, letting downstream reports trace how a specific balance (identified by the balance set's ATTRIBUTE_1) evolves across each temporal context relevant to federal reporting. In EBS 12.1.1 and 12.2.2 the view remains VALID and is typically referenced by seeded Canadian payroll reports, balance verification extracts, and custom reconciliation queries.

Underlying Base Objects

The view is defined over the following documented base objects:

The Canadian-specific logic is entirely encapsulated in the two PAY_CA_* packages, so the view itself is a relatively thin projection layer that binds assignment action context to the balance calculation routine.

Key Columns

  • ASSIGNMENT_ID / ASSIGNMENT_ACTION_ID — identifier of the assignment action being evaluated; the join key for transactional tracing.
  • TAX_UNIT_ID — the tax unit against which the federal balance is calculated.
  • PAYROLL_ACTION_ID / ACTION_TYPE / EFFECTIVE_DATE — payroll run context and the date that anchors all temporal balance retrieval.
  • ACTION_STATUS — status of the assignment action (e.g., processed, unprocessed).
  • BASE_BALANCE_NAME — the balance set entry (ATTRIBUTE_1) whose value is being retrieved.
  • BALANCE_NAME / REPORTING_NAME — the balance type's descriptive and reporting labels.
  • BUSINESS_GROUP_ID / LEGISLATION_CODE — organizational and legislative context, which should be 'CA' for this view to return meaningful Canadian federal results.
  • CURRENT_VALUE / RUN_VALUE / PYDATE_VALUE / MONTH_VALUE / QTD_VALUE / PTD_VALUE — the balance amount as of each temporal context, each produced by a separate CALL_CA_BALANCE_GET_VALUE invocation.

Common Use Cases and Queries

Typical uses include federal remittance reconciliation, quarter-to-date and year-to-date verification against the Canadian balance engine, and diagnostics where an assignment's current value differs from its run or pay date value. Because each value column invokes a PL/SQL package call, queries against this view are comparatively expensive and should be filtered aggressively on ASSIGNMENT_ACTION_ID, TAX_UNIT_ID, or BUSINESS_GROUP_ID.

Sample query — balances for a specific assignment action:

SELECT assignment_action_id,
       tax_unit_id,
       base_balance_name,
       balance_name,
       current_value,
       run_value,
       pydate_value,
       qtd_value,
       ptd_value
FROM   apps.pay_ca_fed_er_v
WHERE  assignment_action_id = :p_assignment_action_id
AND    legislation_code = 'CA';

Sample query — quarter-to-date federal balances for a business group:

SELECT assignment_id,
       tax_unit_id,
       balance_name,
       reporting_name,
       qtd_value
FROM   apps.pay_ca_fed_er_v
WHERE  business_group_id = :p_business_group_id
AND    effective_date BETWEEN :p_start_date AND :p_end_date
AND    NVL(qtd_value,0) <> 0
ORDER  BY tax_unit_id, balance_name;

In both cases the caller must ensure PAY_CA_BALANCE_VIEW_PKG session variables (REPORT_LEVEL, BUSINESS_GROUP) are set, since the underlying package relies on them to resolve the correct reporting scope. Without them, the returned values may reflect defaults rather than the intended reporting level.