Search Results pay_us_local_taxes_rbr_v

Overview

PAY_US_LOCAL_TAXES_RBR_V is a PL/SQL-based reporting view owned by the APPS schema in Oracle E-Business Suite (validated under 12.1.1 and 12.2.2). It belongs to the PAY (Payroll) product family and specifically addresses United States local tax reporting. The view is a "row-by-row" (RBR) construct that resolves city, county, and transit-district withholding balances for an assignment action within the payroll action lifecycle. Rather than residing on physical columns alone, its projection is largely computed through calls to two packaged APIs—PAY_US_BALANCE_VIEW_PKG and PAY_US_TAXBAL_VIEW_PKG—which return period-to-date, run, quarter-to-date, and year-to-date tax balances on demand.

The suffix "RBR" indicates the view is designed to feed a BI Publisher or report-level data model rather than a persistent table. It is typically consumed by US local tax reports (e.g., city/county withholding summaries and reconciliation reports) that must display current, run, QTD, and YTD amounts for each local tax authority, along with the associated employee assignment, action status, and jurisdiction identifiers.

Underlying Base Objects

The view is defined over a mix of assignment/action synonyms and US-specific tax setup and balance synonyms:

• Action-layer objects: PAY_ASSIGNMENT_ACTIONS (PAA), PAY_PAYROLL_ACTIONS (PPA), and PAY_RUN_BALANCES supply the assignment_id, payroll_action_id, effective_date, and action_type driving each row.
• Tax authority/setup objects: PAY_US_CITY_TAX_INFO_F, PAY_US_COUNTY_TAX_INFO_F, PAY_US_CITY_NAMES, PAY_US_COUNTIES, PAY_US_TAX_TYPES, PAY_US_EMP_CITY_TAX_RULES_F, and PAY_US_EMP_COUNTY_TAX_RULES_F provide jurisdiction codes, tax_type_id, tax_type_code, and employee-level applicability rules.
• Balance objects: PAY_US_TAX_BALANCES and the packaged APIs PAY_US_BALANCE_VIEW_PKG (view mode, calculation flags) and PAY_US_TAXBAL_VIEW_PKG (US_TAX_BALANCE_VM) generate CURRENT_VAL, RUN_VAL, QTD_VAL, and YTD_VAL.
• Reporting object: PAY_US_ASG_REPORTING ties the assignment to reported tax locations.

A notable characteristic is the literal 'INVALID' text placed in SCHOOL_DISTRICT_NAME/CODE, STATE_NAME/ABBREV, and COUNTY_NAME/ABBREV columns, confirming this view is specialized for city/county local taxes and intentionally blanks out unrelated jurisdictions.

Key Columns

• ASSIGNMENT_ID / ASSIGNMENT_ACTION_ID / PAYROLL_ACTION_ID: keys linking the row to a specific action against an assignment.
• ACTION_STATUS and ACTION_TYPE: the state of the assignment action and the payroll action category.
• EFFECTIVE_DATE: payroll action effective date.
• JURISDICTION_CODE, CITY_NAME, CITY_CODE, COUNTY_CODE, STATE_CODE: resolved local geography identifiers.
• TAX_TYPE_ID / TAX_TYPE_CODE / TAX_DOMAIN_CODE / EE_OR_ER_CODE: the tax authority and whether the amount is employee or employer withholding.
• LIMIT_TAX_FLAG and BALANCE_CATEGORY_CODE / USER_REPORTING_NAME: control balance selection and labeling.
• LOCAL_TAX_NAME: concatenation of TAX_TYPE_CODE and the resolved city name or jurisdiction code for reporting display.
• VIEW_MODE and CALC_ALL_TIMETYPES: returned by PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE and GET_CALC_ALL_TIMETYPES_FLAG to govern UI/report behavior.
• CURRENT_VAL, RUN_VAL, QTD_VAL, YTD_VAL: computed tax balance amounts across the four reporting horizons.

Common Use Cases and Queries

The view is predominantly used in US local tax reporting and payroll reconciliation. A typical query filters to a payroll action and assignment to display all local tax withholding lines with their period balances:

• City/county withholding detail for a pay run.
• QTD/YTD local tax reconciliation for a payroll period.
• Employee-level audit of city tax rule applicability versus actual balances.

Sample SQL:

SELECT assignment_id, assignment_action_id, payroll_action_id, tax_type_code, local_tax_name, ee_or_er_code, current_val, run_val, qtd_val, ytd_val FROM apps.pay_us_local_taxes_rbr_v WHERE payroll_action_id = :p_payroll_action_id AND tax_type_code IN ('CITY','HT') ORDER BY assignment_id, tax_type_code;

Because the balance columns invoke packaged functions, performance is sensitive to indexing on the underlying PAY_ASSIGNMENT_ACTIONS and PAY_US_TAX_BALANCES tables; queries should always be constrained by payroll action or assignment to avoid expensive full scans.