Search Results school_gross




Overview
Explore in AI Chat

Overview

PAY_US_SCHOOL_ACTION_INFO_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It is registered as a VALID object within the PAY (Payroll) product family and is classified as a view rather than a table, meaning it holds no data of its own and derives all content from underlying base objects at query time. Its purpose is to present United States school district withholding information captured on payroll actions in a denormalized, human-readable form suitable for reporting, inquiry, and downstream integration.

The view isolates a specific subset of payroll action data: records whose ACTION_INFORMATION_CATEGORY equals the literal value 'US SCHOOL DISTRICT'. In the Oracle Payroll model, school district tax withholding for US employees is stored as flexible action information entries attached to a payroll action. Rather than requiring report authors to understand the generic ACTION_INFORMATION1 through ACTION_INFORMATION5 column scheme, this view maps each generic slot to a descriptive, domain-specific column name such as SCHOOL_GROSS or SCHOOL_WITHHELD. This abstraction makes the view particularly valuable for payroll administrators, tax analysts, and developers building school district tax reporting or reconciliation queries.

Underlying Base Objects

The view is defined over a single base table, PAY_ACTION_INFORMATION (referenced in the APPS schema via a synonym), combined with a call to the packaged function PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME. The core SELECT projects columns directly from PAY_ACTION_INFORMATION, aliasing the generic action information slots into named fields and applying TO_NUMBER conversions so that numeric tax amounts are returned as numbers rather than character strings. The jurisdiction name is not stored on the action record; instead, the view invokes GET_JURISDICTION_NAME within the PAY_US_EMPLOYEE_PAYSLIP_WEB package, passing the stored JURISDICTION_CODE, to resolve a descriptive jurisdiction label. The view text concludes with a WHERE clause restricting rows to the 'US SCHOOL DISTRICT' action information category, ensuring only relevant school district entries are exposed. Because the view depends on a PL/SQL function call, performance may be affected on large result sets, and the function must remain valid for the view to query successfully.

Key Columns

  • EFFECTIVE_DATE — The effective date of the payroll action to which the school district information belongs.
  • JURISDICTION_CODE — The code identifying the taxing school district jurisdiction.
  • JURISDICTION_NAME — The descriptive name of the jurisdiction, resolved at runtime via PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME.
  • ACTION_NUMBER — Derived from ACTION_CONTEXT_ID; identifies the payroll action context associated with the entry.
  • ASSIGNMENT_ID — The assignment of the employee to whom the school district information applies.
  • SCHOOL_GROSS — Sourced from ACTION_INFORMATION5; the gross wages subject to school district tax.
  • SCHOOL_WITHHELD — Sourced from ACTION_INFORMATION1; the amount withheld for the school district.
  • SCHOOL_SUBJ_WHABLE — Sourced from ACTION_INFORMATION2; subject wages that are withholdable.
  • SCHOOL_SUBJ_NWHABLE — Sourced from ACTION_INFORMATION3; subject wages that are not withholdable.
  • SCHOOL_PRE_TAX_REDNS — Sourced from ACTION_INFORMATION4; pre-tax reductions applied.
  • SCHOOL_SUBJECT — A computed column: the sum of withholdable and non-withholdable subject wages, using NVL to treat nulls as zero.
  • SCHOOL_REDUCED_SUBJECT — A computed column: withholdable subject wages less pre-tax reductions, again NVL-protected.

Common Use Cases and Queries

The view is typically used to report school district tax withholding per employee assignment, to reconcile withheld amounts against gross and subject wages, and to feed downstream payroll or tax extracts. A representative query follows:

SELECT assignment_id,
       jurisdiction_code,
       jurisdiction_name,
       effective_date,
       school_gross,
       school_withheld,
       school_subject,
       school_reduced_subject
  FROM apps.pay_us_school_action_info_v
 WHERE effective_date BETWEEN :start_date AND :end_date
 ORDER BY assignment_id, effective_date;

Analysts may also aggregate withheld amounts by jurisdiction to validate district-level remittances:

SELECT jurisdiction_code,
       jurisdiction_name,
       SUM(school_withheld) total_withheld
  FROM apps.pay_us_school_action_info_v
 GROUP BY jurisdiction_code, jurisdiction_name;

Because the view performs TO_NUMBER conversions and a function-based jurisdiction lookup, queries should filter on EFFECTIVE_DATE, ASSIGNMENT_ID, or JURISDICTION_CODE where possible to limit rows and reduce the overhead of the packaged function call.