Search Results paybv_us_state_archive

Overview

PAYBV_US_STATE_ARCHIVE is an APPS-owned, VALID database view within the PAY — Payroll product of Oracle E-Business Suite (documented against 12.1.1 and 12.2.2). Its name follows the "BV" naming convention used for Oracle Payroll business views intended for external consumption through the Business View (BV) layer, which means it is a candidate object for use with Oracle Payroll Business Views integration and for reporting rather than a core transactional table. The view exposes archived United States state-level tax results for a payroll assignment, organized by jurisdiction and effective date. Rather than storing data itself, the view derives its rows from payroll action information records and reshapes them into a reporting-friendly layout of gross, taxable, subject, and withheld amounts for multiple US state tax types.

The view is best understood as a de-normalized, human-readable projection of the state tax balances captured during payroll processing. It presents the SIT (State Income Tax), SDI (State Disability Insurance, both employee and employer portions), and SUI (State Unemployment Insurance, employee portion) figures that Oracle Payroll writes into PAY_ACTION_INFORMATION when it runs the US state tax calculations and archives them. Downstream consumers — custom reports, extracts, integrations, and reconciliation tools — use the view to obtain labeled columns instead of having to decode generic ACTION_INFORMATION slots.

Underlying Base Objects

The documented metadata for 12.2.2 lists two referenced base objects: the synonym PAY_ACTION_INFORMATION and the package PAY_US_EMPLOYEE_PAYSLIP_WEB. The view's defining text confirms both dependencies.

• PAY_ACTION_INFORMATION (via synonym) is the sole driving table in the FROM clause, aliased PAI. It is the Payroll action information entity, keyed principally by ACTION_CONTEXT_ID and TAX_UNIT_ID, and holds the archived state tax values that the view selects and renames.
• PAY_US_EMPLOYEE_PAYSLIP_WEB (package) is referenced through the function PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME, which is called in the select list to resolve JURISDICTION_CODE into a descriptive JURISDICTION_NAME. This introduces a functional dependency on that payroll package rather than a join.

Because the view is defined over action information records, it aligns conceptually with the archived payroll action data created during a payroll run's state tax processing. It does not itself enforce any filter visible in the provided excerpt beyond the projection of PAI columns, so row scope is inherited from the base table.

Key Columns

The view renames generic ACTION_INFORMATION slots into descriptive aliases grouped by tax family.

• ACTION_NUMBER — maps to PAI.ACTION_CONTEXT_ID, identifying the payroll action context.
• TAX_UNIT_ID, EFFECTIVE_DATE, ASSIGNMENT_ID — the primary identifying columns: the tax unit, the effective date of the archived result, and the assignment.
• JURISDICTION_CODE / JURISDICTION_NAME — the state tax jurisdiction and its resolved name via GET_JURISDICTION_NAME.
• SIT columns — SIT_GROSS, SIT_SUBJ_WHABLE, SIT_SUBJ_NWHABLE, SIT_PRE_TAX_REDNS, SIT_SUBJECT (whable plus nwhable), SIT_REDUCED_SUBJECT (whable less pre-tax reductions), and SIT_WITHHELD.
• SDI employee columns — SDI_EE_GROSS, SDI_EE_TAXABLE, SDI_EE_SUBJ_WHABLE, SDI_EE_PRE_TAX_REDNS, SDI_EE_REDUCED_SUBJECT, SDI_EE_WITHHELD.
• SDI employer columns — SDI_ER_GROSS, SDI_ER_TAXABLE, SDI_ER_SUBJ_WHABLE, SDI_ER_PRE_TAX_REDNS, SDI_ER_REDUCED_SUBJECT, SDI_ER_LIABILITY.
• SUI employee columns — SUI_EE_GROSS, SUI_EE_TAXABLE, SUI_EE_SUBJ_WHABLE, SUI_EE_PRE_TAX_REDNS, and related withheld figures.

All monetary values are wrapped in NVL(TO_NUMBER(...), 0), so consumers receive numeric zeros rather than nulls for missing slots. Computed subject columns (for example SIT_SUBJECT and the REDUCED_SUBJECT variants) are derived inline from the underlying slots.

Common Use Cases and Queries

Typical uses include state tax reconciliation, archived payslip verification, and extracts feeding financial or compliance reporting. A representative query locates state tax results for one assignment over a date range:

• SELECT action_number, tax_unit_id, effective_date, jurisdiction_code, jurisdiction_name, sit_gross, sit_withheld, sdi_ee_withheld, sui_ee_gross FROM apps.paybv_us_state_archive WHERE assignment_id = :p_assignment_id AND effective_date BETWEEN :p_from AND :p_to ORDER BY effective_date, jurisdiction_code;
• Aggregating withholding by jurisdiction: SELECT jurisdiction_code, SUM(sit_withheld) FROM apps.paybv_us_state_archive WHERE effective_date BETWEEN :p_from AND :p_to GROUP BY jurisdiction_code;
• Comparing employee versus employer SDI liability for a period using SDI_EE_WITHHELD and SDI_ER_LIABILITY.

Because JURISDICTION_NAME resolves through the payroll package function, queries spanning many rows may incur a per-row package call, so performance-sensitive extracts should filter aggressively on assignment, tax unit, or effective date. Access should be granted via the APPS schema and the standard payroll responsibility, respecting the security applied to action information at the base-table level.