Search Results local_head_tax_withheld

Overview

PAYBV_US_LOCAL_ARCHIVE is a read-only database view owned by the APPS schema within the Oracle E-Business Suite Payroll (PAY) product. It exposes archived United States local tax information maintained in the payroll action information store. Unlike transactional payroll run views that report current-period results, this view presents data archived against the action information records for local jurisdictions, encompassing city, county, and school district tax authorities.

Because the view name carries the "BV" prefix convention used throughout Oracle Payroll for "Business View," it is intended for reporting and integration rather than for direct transactional processing. It consolidates the fragmented numeric and descriptive fields that Oracle Payroll stores against PAY_ACTION_INFORMATION into a set of meaningful, business-friendly columns such as local gross, local withheld, subject wages, and pre-tax reductions. The view is defined with WITH READ ONLY, preventing DML and confirming that it is strictly a query surface. It plays a central role in U.S. payroll localization, where local jurisdiction-level tax reporting and reconciliation obligations require historical values to be retrieved independent of the live payroll run.

Underlying Base Objects

The view is defined over two documented referenced objects:

• PAY_ACTION_INFORMATION (referenced via synonym) — the primary source. This table stores action-level miscellaneous information attached to payroll actions, keyed by categories such as 'US CITY', 'US COUNTY', and 'US SCHOOL DISTRICT'. The view filters to exactly these three categories.
• PAY_AC_UTIL (package) — invoked for lookup and decoding functions. Specifically, GET_JURISDICTION_NAME supplies the human-readable jurisdiction description, and GET_STATE_ABBREV returns the two-character state abbreviation.

The view aliases PAY_ACTION_INFORMATION as PAI and projects the numeric action information columns through NVL and TO_NUMBER conversions, defaulting absent values to zero.

Key Columns

• ACTION_NUMBER — mapped from ACTION_CONTEXT_ID, identifying the payroll action context.
• TAX_UNIT_ID / ASSIGNMENT_ID / EFFECTIVE_DATE — the tax unit, the assignment, and the effective date of the archived record.
• JURISDICTION_CODE — the tax jurisdiction identifier, also used to derive STATE_CODE via SUBSTR(...,1,2).
• JURISDICTION_NAME / STATE_ABBREV — descriptive lookups resolved through PAY_AC_UTIL.
• LOCAL_GROSS / LOCAL_WITHHELD — gross wages subject to the local tax and the amount withheld.
• LOCAL_SUBJ_WHABLE / LOCAL_SUBJ_NWABLE — subject wages that are withholding-applicable and non-withholding-applicable respectively.
• LOCAL_PRE_TAX_REDNS — local pre-tax reductions.
• LOCAL_SUBJECT / LOCAL_REDUCED_SUBJECT — derived totals; reduced subject subtracts pre-tax reductions.
• LOCAL_HEAD_TAX_LIABILITY / LOCAL_HEAD_TAX_WITHHELD — head-of-household local tax amounts; for school districts these are forced to zero via DECODE.

Common Use Cases and Queries

Typical scenarios include local tax reconciliation, audit support, and archival reporting by jurisdiction or assignment.

SELECT action_number, jurisdiction_name, state_abbrev,
       local_gross, local_withheld, local_subject
FROM   apps.paybv_us_local_archive
WHERE  state_code = 'CA'
AND    effective_date BETWEEN :start_date AND :end_date;

For school district reporting, filtering on the jurisdiction prefix or restricting to non-zero head tax columns isolates the relevant records. Because the view normalizes every numeric column with NVL(...,0), downstream aggregation queries require no additional null handling, making it well suited for extract feeds, reconciliation reports, and integration with external tax engines.