Results for “otd_excess”

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

Overview

PAY_CA_WCB_GRP_LEVEL_V is an APPS-owned database view within the Oracle Payroll (PAY) product module. It is a reporting and integration construct designed to expose group-level Workers' Compensation Board (WCB) balance and rate information for Canadian payroll accounts. The view consolidates account, province, rate, and balance set data with dynamically computed balance amounts derived from the PAY_CA_GROUP_LEVEL_BAL_PKG package. Its presence in the ETRM metadata as a VALID object in both Oracle EBS 12.1.1 and 12.2.2 confirms that its definition and behavior have remained stable across these releases.

The view is a legislative-specific object, restricted to Canadian legislation (LEGISLATION_CODE = 'CA'), and is scoped to balance sets whose names begin with the pattern 'WCB_SUM'. This naming convention ties the view directly to WCB summarization balance sets configured during payroll setup. The columns PYDATE_* and OTD_* distinguish pay-period-to-date versus other-period-to-date accumulations, making the view suitable for period-sensitive reporting.

Underlying Base Objects

The view is defined over a mixture of synonyms, views, and package calls documented in the ETRM metadata. The primary source is PAY_WCI_ACCOUNTS_V, which supplies the WCB account number, province, and account identifier. Rate information is drawn from PAY_WCI_RATES, which provides the rate code, rate value, description, and RATE_ID used in the package calls. Balance set metadata, including the base balance names and dimension, is sourced from PAY_BALANCE_SETS.

Session context is established through FND_SESSIONS, joined on SESSION_ID = USERENV('SESSIONID'), which ensures that effective-date resolution reflects the current user session. Balance type names are resolved through the PAY_BALANCE_TYPES and PAY_BALANCE_TYPES_TL tables (multilingual and base), which supply the translated taxable, liability, and excess balance names. The actual balance amounts are not stored in these tables directly; they are computed at query time by invoking PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE, passing the appropriate balance attribute, a date basis marker ('PYDATE' or the dimension attribute), the effective date, and the rate identifier. Additional referenced objects include FF_CONTEXTS, HR_GENERAL, HR_SECURITY, and PAY_ACTION_CONTEXTS, which support context resolution and security filtering.

Key Columns

• ACCOUNT_NUMBER, PROVINCE — WCB account identifier and Canadian province from PAY_WCI_ACCOUNTS_V.
• RATE_ID, CODE, RATE, DESCRIPTION — WCB rate definition from PAY_WCI_RATES.
• BALANCE_SET_NAME — the WCB_SUM% balance set providing the aggregation scope.
• BASE_BAL_NAME_TAXABLE, BASE_BAL_NAME_LIABILITY, BASE_BAL_NAME_EXCESS — the underlying balance names drawn from PAY_BALANCE_SETS attributes 1, 2, and 3.
• TL_BAL_NAME_TAXABLE, TL_BAL_NAME_LIABILITY, TL_BAL_NAME_EXCESS — translated balance names from PAY_BALANCE_TYPES_TL.
• DIMENSION, LEGISLATION_CODE, EFFECTIVE_DATE — the balance set dimension attribute, legislation (fixed to CA), and effective date from FND_SESSIONS.
• PYDATE_TAXABLE, PYDATE_LIABILITY, PYDATE_EXCESS — pay-period-to-date balances returned by the package for each balance category.
• OTD_TAXABLE, OTD_LIABILITY, OTD_EXCESS — other-period-to-date balances, computed as zero when no dimension is defined.

Common Use Cases and Queries

The view supports WCB remittance reporting, rate verification, and reconciliation of accumulated taxable, liability, and excess balances at the group level. A typical query retrieves current session-dependent balances for a given rate:

• SELECT account_number, province, code, rate, pydate_taxable, pydate_liability, pydate_excess FROM pay_ca_wcb_grp_level_v WHERE rate_id = :p_rate_id;
• SELECT balance_set_name, tl_bal_name_excess, otd_excess FROM pay_ca_wcb_grp_level_v WHERE balance_set_name LIKE 'WCB_SUM%';

Because results are effective-date and session sensitive, queries must be executed within an initialized EBS session to yield valid values.