Payroll Tax Subsystem

Payroll Tax Subsystem

This essay introduces the tax subsystem in Oracle Payroll for US legislations. It is assumed that you understand the use of elements, entries, formulas, run results and balances in Oracle Payroll. Specifically, this essay covers the following topics:

Installed Tax System

When you install Oracle Payroll with a US legislation, you automatically receive the predefined components you need to calculate the legislated Federal, State and local taxes within the US. These components include the specific Classifications and Categories of elements, Taxability Rules, standard Formulas, Balances and Balance Dimensions you need. As part of your install process you will also install the Vertex tax tables used by Oracle Payroll for all US jurisdictions.

GEOCODES

The interface between Oracle Payroll and the Vertex tax subsystem makes use of a key called the GEOCODE to identify every jurisdiction in the US.

The geocode has a numeric format nn-nnn-nnnn and is used to identify the combination of State, County, and City for tax liability. For school districts, the geocode has the numeric format nn-nnnnn where nn is the state, and nnnnn is the school district number. The geocode is not normally visible to users of Oracle Payroll and special validation is provided on entry of the address for a location and the primary address for an employee to ensure accurate geocodes for both home and work addresses.

Earnings and Deductions

Oracle Payroll uses a combination of classifications and categories of elements to determine the taxability rules of every earning and deduction.

Regular Earnings

For tax purposes, regular earnings are always subject to taxes.

When you define a type of Regular Earnings, the system automatically generates an input value to hold a jurisdiction value. This lets you enter a Vertex geocode for a specific jurisdiction. For example, you might want to create a regular earnings type of "Time Entry Wages - Houston" that always has a "Jurisdiction" entry value of 44-201-1440 for Houston, Texas.

Regular Earnings for which the jurisdiction element entry value is null are considered untagged. This distinction is important for the Earnings Accumulation Rules.

Supplemental Earnings

Supplemental earnings include Bonuses, Commissions or Cash Awards. These earnings are subdivided into several categories and each category may be subject to or exempt from any given tax in any jurisdiction. This category is user-extensible.

Imputed Earnings

Imputed Earnings are non-cash earnings, such as personal use of a company car. These earnings have a dollar value but are not paid directly to the employee.

Income taxes for imputed earnings may not be withheld from the employee's paycheck, but the earnings may be reported as income subject to tax and the tax paid at tax return time.

Pre-Tax Deductions

Pre-Tax Deductions reduce the gross subject to tax before tax is calculated. Examples include Deferred Comp 401k, 403b, and 457 plans, and Health Care 125 plans.

However, not all taxes may be reduced by every pre-tax deduction. For example, FIT gross is reduced by 401k and 125 deductions, but for Social Security Tax, gross is only reduced by 125 deductions. Similarly, rules for the various state taxes vary from state to state.

Taxes

Oracle Payroll supports both income and limit taxes. Income taxes have no maximum limit. Limit taxes apply to earnings up to a maximum limit after which all successive income is not taxable.

Oracle Payroll also supports both employee and employer taxes, indicating which party is liable for the tax. For example, Social Security has both an employee and employer component.

Income Taxes

All income taxes are paid fully by the employee. Income taxes follow:

Limit Taxes

Oracle Payroll supports the following limit taxes:

FICA and Medicare have both employee and employer components. FUTA is purely an employer tax. SUI and SDI may have both employee and employer components (or may not exist) depending on the state.

Other Taxes

Head Tax (HT) does not fall into the above categories since it is paid as a flat amount by the employee on a periodic basis.

Tax Credits

Earned Income Credit (EIC) is supported as the companion tax credit to FIT. The taxability rules for EIC are held separately from those for FIT, but they are (and should remain) identical.

Tax Rules

The term Tax Rules refers to the set of filing information for a given tax. One set of rules for each relevant taxing jurisdiction is required at the assignment level.

Employee Tax Rules Form

The Employee Tax Rules form is used to default and then maintain tax information for an employee's assignment. Tax information is maintained at the Federal, State and Local levels.

Maintaining Tax Information For Government Reporting Entities

You use the Define Organization form for this purpose. For the GRE in question, you select the organization classification Government Reporting Entity. Then, the Others button shows a list of the various contexts defined for the Org Developer DF.

Choosing State Tax Rules allows you to define additional organization information per state such as SUI Company State ID, SIT Company State ID, SUI Self Adjust Method, SDI Self Adjust Method, SUI ER Experience Rate 1, and SUI ER Experience Rate 2, for example.

Taxability Rules

As indicated above, Oracle Payroll maintains rules for deciding whether a particular supplemental or imputed earning is taxable for a given tax, and whether a pretax deduction may reduce taxable gross for a given tax. This information is stored in the table PAY_TAXABILITY_RULES. Given an element classification, its category, and the tax in question (and the locality if necessary) a row in the table means that the earning is taxable, or that the deduction may not reduce taxable gross. Regular earnings are not included, since they are always subject to tax, so no table lookup is necessary.

Considering earnings only, for limit taxes, a row in the table means taxable.

However, for income taxes, there are two kinds of taxability:

Subject-and-Withholdable means that tax is withheld on payment of the earning. Subject-and-Not-Witholdable means that the earning is taxable, but tax is not withheld, so it must be paid at the end of the year. Imputed earnings, for example, can be Subject-and-Not-Withholdable for FIT, but are either Subject-and-Witholdable or not subject to FUTA.

Earnings Accumulation Rules

The obvious prerequisite for calculating tax is finding the gross applicable to the tax, the value before applying taxability rules on the earnings components and before applying pretax deductions. For the non-federal taxes, in those cases where an employee works and lives in multiple states, the rules for distributing earnings are not trivial. This section outlines the rules followed in the tax system.

For each employee, tax filing information such as Primary Work Location, Resident Location, and SUI State has been set up.

Federal

For federal taxes, all earnings regardless of where they were earned are included in the gross figure.

SDI

In any given period (in which the Primary Work Location is unchanged), all earnings are considered gross for SDI in the Primary Work Location only, and nowhere else. For example, if John Doe lives in New York for the first six months of 1995, and in New Jersey for the second six months, then he will pay SDI tax to New York only on his first six months' pay, and will pay New Jersey SDI on his second six months pay.

SUI

SUI works similarly to SDI, except that it is referenced to the employee's SUI State (which is often the Primary Work Location). An assignment's SUI State is designated on the Employee Tax Rules Form, and is defaulted to their work state.

In any given period (in which the SUI State is unchanged), all earnings are considered gross for SUI in the SUI State only, and nowhere else.

Non-federal Income Taxes

For the non-federal income taxes, we need a method of apportioning earnings among the states where the employee works and lives. For this purpose, tagged earnings (which explicitly indicate where they were earned) are treated differently from untagged earnings. The following steps are used find the applicable gross for one of these taxes (SIT, County Tax, City Tax) in a given jurisdiction.

  1. If an employee's Resident Location is contained within the jurisdiction for which tax is to be calculated, then all the employee's earnings, regardless of where they were earned, are considered as gross, and the following steps below are disregarded.

  2. All supplemental earnings are considered to be implicity tagged in the Primary Work Location. Commission is an exception; it is considered untagged and does follow the percentage scaling rule in step 3.

  3. All untagged earnings are scaled by the percentage of time the employee spent working in the given jurisdiction. For example, if John Doe worked 30% in California, and 70% in Texas, and has a Salary of 1000, then he would have a gross of 300 for California state tax, and 700 for Texas.

  4. All tagged earnings which are contained within the given jurisdiction are included to give a final value.

The special status of the Resident Location is explained as follows. Some states enter into reciprocity agreements with other states. This fact means that if John Doe works in state A and lives in state B, ordinarily, he is taxed in both states for the amount he earned in the work state, A. A reciprocation agreement between state A and state B would specify that the earnings would be taxed in just one state, say the employee's resident state. (To get the tax exemption from this type of agreement, the employee would need a nonresident certificate.)

In a more complex scenario, an employee works in five states. In this case, one considers each work state as part of a pair with the resident state, since reciprocity agreements are not relevant between work states, only between work states and the resident state. Taxes are then calculated independently between each pair of states. Thus, all the employee's earnings are potentially subject to tax in that single resident state. Each work state can either tax the earnings in the state or choose not to do so, but ulinke the resident state, a work state cannot tax earnings from other states.

Deductions

While apportioning the earnings among states, we must also split up the pre-tax deduction amounts as well. The following rule suffices. Within any given time period, for a given tax, total deductions are scaled by the applicable gross for the tax in the relevant jurisdiction, divided by the employee's total gross earnings. For example, if John Doe's total gross was $2000, and his gross for SIT in Colorado was $1500, and he had a total of $200 of 401k deductions, then the portion of his deductions that would apply to SIT in Colorado would be 200 * 1500 / 2000 = $150.

Example

Consider the following situation. The employee lives in Oregon in January, but moves to California in February. He works 50% in California and 50% in Nevada. His primary Work State is California in January and Nevada in February.

Earnings for January and February follow:

1000 Salary

100 Timecard, tagged in Nevada

100 Bonus

100 Commission

-----

1300 = Total

Deductions for January and February follow:

100 401(k)

For January:

FIT Gross = 1300

Deductions = 100

SDI:

In California, Gross = 1300 since California is the primary work state.

Deductions = 100

In Nevada, Gross = 0 "

Deductions = 0

SIT:

In California, Gross: 500 for the California half of salary

100 for bonus (supp earning) in primary work state (California)

50 for the California half of commission

---

650 = Total

Deductions: 50 for the CA portion of 401 (100 * 650/1300)

In NV, Gross: 500 for the Nevada half of salary

100 for the Nevada-tagged timecard earnings

50 for the Nevada half of commission

---

650 = Total

Deductions: 50 for the NV portion of 401 (100 * 650/1300)

For Feb:

FIT Gross = 1300

Deductions = 100

SDI:

In California, Gross = 0 since Nevada is the primary work state.

Deductions = 0

In Nevada, Gross = 1300 "

Deductions = 100

SIT:

In California, Gross = 1300 due to California = resident state

Deductions: 100 since CA = resident state (100 * 1300/1300)

(Since there isn't a reciprocity rule between CA and NV,

NV earnings are reported in CA as gross, but not taxed)

In Nevada, Gross: 500 for the NV half of salary

100 for bonus in primary work state (Nevada)

100 for the Nevada-tagged timecard earnings

50 for the Nevada half of commission

---

750 = Total

Deductions: 57.69 for the NV portion of 401 (100 * 750/1300)

Year-to-date:

FIT: Gross = 2600, Deductions = 200

SDI:

In CA, Gross = 1300, Deductions = 100

In NV, Gross = 1300, Deductions = 100

SIT:

In CA, Gross = 1950, Deductions = 150

In NV, Gross = 1400, Deductions = 107.69

Tax Balances

The starting gross is converted by applying taxability rules and pre-tax deductions, involving several steps and intermediate values. This process results in the final amount on which tax is calculated. These intermediate values (tax balances) are also useful for reporting and other purposes.

Definitions

The various tax balances are defined as follows:

Tax Balances Form

The Tax Balances Form displays a summary of an employee's tax balances for all the various taxes.

Tax Balance API

This function provides a simple single function call method for obtaining the above tax balances. It calls the core balance user exit. For example, in the package PAY_US_TAX_BALS_PKG:

FUNCTION  us_tax_balance (p_tax_balance_category  in varchar2,

                     p_tax_type              in varchar2,

                     p_ee_or_er              in varchar2,

                     p_time_type             in varchar2,

                     p_asg_type              in varchar2,

                     p_gre_id_context        in number,

                     p_jd_context        in varchar2  DEFAULT NULL,

                     p_assignment_action_id  in number    DEFAULT NULL,

                     p_assignment_id   in number DEFAULT NULL,

                     p_virtual_date     in date      DEFAULT NULL,

                     p_payroll_action_id in number  DEFAULT NULL)

RETURN number;

The allowed tax_balance_category values follow:

These values are in the lookup type US_TAX_BALANCE_CATEGORY.

The allowed tax types follow:

The asterisk (*) indicates that the type requires a value EE or ER, denoting either the employee or employer tax in the parameter p_ee_or_er. FUTA and HT are ER only, and the rest are EE only. The API returns an error if an inconsistent value is entered. These tax types are in the lookup type US_TAX_TYPE.

The allowed time types follow:

The allowed asg types follow:

For ASG and PER, the GRE is implicit, so these types return only those values that are associated with the GRE identified by p_gre_id_context. These values are permitted in the following combinations:

  RUN PTD MONTH QTD YTD
ASG x x x x x
PER x   x x x
GRE x   x x x

The tax unit id is p_gre_id_context and is mandatory. p_jd_context should contain the xx-xxx-xxxx format for the Vertex geocode. It is ignored for the federal taxes.

For school district, the five-digit code may be appended, as follows: xx-xxx-xxxx-xxxxx, or it may be appended after the state code, as follows: xx-xxxxx

Two modes of access are allowed (as in the core balance user exit):

All three parameters are provided for, and should be set NULL when not used. Assignment action mode is required for all RUN level inquiries. However, for an asg type of GRE, we can only use Date Mode, and without an assignment_id, so the virtual_date must be set. Also, for GRE, we must provide the payroll_action_id parameter for RUN level balances.

User Defined Reports

Instead of hardcoded balances and dimensions in the report definition, the user can define the set of tax balances and the set of dimensions to report on.

The tax report name is stored in hr_lookups (lookup_type = 'US_TAX_REPORT').

Defining a New Tax Balance Report

To define a new tax balance report, perform the following steps:

  1. Add a new lookup code in hr_lookups for lookup_type = US_TAX_REPORT.

    The MEANING column is used as the report title. This lookup_type is the primary key for the dimension and balances tables such as STATE_QTD. (At runtime, the user selects from the US_TAX_REPORTs, thereby identifying the set of balances and dimensions.)

  2. For each dimension to be reported, insert a row in pay_us_tax_report_dimensions.

    Valid time dimension values are lookup_codes with US_TAX_BALANCE_DIMENSION lookup_type, such as report_code = STATE_QTD, dimension_code = QTD.

  3. Select the subset of balances from pay_us_tax_balances. Insert a row for each tax_balance_id in pay_us_tax_balances.

    pay_us_tax_report_dimensions stores the dimensions to be used for the report. It includes the following lookup codes:

    • REPORT_CODE: report lookup code

    • DIMENSION_CODE: dimension lookup code

    pay_us_tax_types include the following:

    • TAX_TYPE_ID

    • EE_ER_CODE: indicates whether the tax is EE, ER or "EE and ER"

      Do not use EE_ER_CODE when calling the tax balance api; it is used for informational purposes only.

    • LIMIT_TAX_FLAG: indicates whether or not the tax is a limit tax.

    • TAX_DOMAIN_CODE: indicates whether the tax is Federal, State or Locality

    • TAX_TYPE_CODE: indicates the tax type lookup code (US_TAX_TYPE)

    pay_us_tax_balances include the following:

    • TAX_BALANCE_ID

    • TAX_TYPE_ID: FK to pay_us_tax_types

    • BALANCE_CATEGORY_CODE: balance category lookup code (US_TAX_BALANCE_CATEGORY)

    • EE_OR_ER_CODE: indicates whether the tax is EE or ER for the category.

    • USER_REPORTING_NAME: used for reporting pay_us_tax_report_balances

    • REPORT_CODE: report lookup code

    • TAX_BALANCE_ID: FK to pay_us_tax_balances

    • BALANCE_PRINT_SEQUENCE: used for determining the relative order to print the balance for the report.

Other Forms

This section describes two additional forms.

Statement of Earnings

The Statement of Earnings form provides a summary of an assignment's earnings, deductions, and taxes, as produced by the payroll run process. It can be reached from the Assignment Form, in which case it displays the results of the most recent run, or from the Assignment Actions Form, in which case it shows the results of the desired assignment action.

Assignment Run Results

For debugging purposes, you may need to view the raw run results, not in the processed format shown in the statement of earnings. The Assignment Run Results Form lists the actual run results and their run result values. For example, it shows in the xx-xxx-xxxx format the raw geocodes in the jurisdiction run result values.

Tax Implementation

This section provides an overview of how the tax system for Oracle Payroll operates. Subsequent sections detail the various components, including those used to calculate taxes, and those used to gather the results.

The Quantum Tax Calculation System

A third party tax calculation system, provided by Vertex Inc., has been chosen for use in Oracle Payroll. Vertex is the leading vendor for US tax calculation, providing a C tax calculation function (Quantum) and a research/support infrastructure to ensure that taxes at user sites are always correctly calculated using up-to-date changes in tax rates and limits. Oracle Payroll takes advantage of the many and varied calculation rules and parameters offered in the Quantum tax calculation module. Oracle also includes a layer of additional functionality to address areas of extended requirements, as follows.

The interface that Quantum provided is a single chunk of memory organized as a structure of records, and serves for both input and output values. The Oracle Payroll tax system gathers the information Quantum needs into this "link area," calls the Quantum C code, and then extracts the results, converting them into the familiar run results. Generally, inputs are comprised of tax filing information (filing status and number of exemptions, for example) and the gross amounts. Quantum returns tax codes and the tax amounts.

Vertex Elements

Calculation of tax for an assignment is triggered off by a special element type called "Vertex." The Assignment Tax Rules Form creates Element entries of this element type, one for each jurisdiction in which the assignment has registered for tax, and at all levels (state, city, and county). The two most important input values for this element are Percentage and Jurisdiction (called JD for short).

The Jurisdiction input value is set to the state or locality jurisdiction code as appropriate. The jurisdiction code is determined from the location in question, whether it be the employee's resident address, work location, or subsequent state and locality records entered in the form.

The Percentage input value is set equal to the Remainder Percent for the State and Time In Locality for the Locality. The State's remainder percent is the time in state less the total of the time in localities for the particular state.

In other words, the Percentage input value contains the percentage of time that the assignment spends in the jurisdiction solely at that level. This percentage different from the percentages the user enters in the Tax Rules Form.

In the form, a user might enter 40 percent for California, and then 10percent for Mountain View. The Vertex entry created for Mountain View will have 10 for its input value, but California will have an input value of 30 (the percentage of time spent purely at the state level; this percentage represents the remainder of time not spent in any specific city or county in California). This approach makes sense since the 10 percent in the Mountain View Vertex entry will produce California state tax results, and when the 30 percent is processed, the tax on the other 30 percent will be produced, accounting for the full 40 percent. As a consequence, all the percentages of all the Vertex entries will always sum to 100 percent.

The Jurisdiction input value is used to set up the US-specific "Jurisdiction" context in the payroll run (using the localization C hook). The Jurisdiction input value serves as the reference for all database items in the formula attached to the Vertex element. However, the processing required for setting up Vertex involves database items requiring several different jurisdiction contexts (the primary work location, the resident location, and the SUI state).

Vertex Formulas

To enable using multiple contexts when processing a Vertex element entry, a chain of elements is used; the Vertex element spawns a different element as an indirect result, passing it a new jurisdiction context. In the following chain of elements, each vertical line indicates an indirect result and a horizontal line indicates formula results without result rules.

(JD = primary work location)

|

VERTEX --> FIT/FSP inputs to Vertex (put into the link area)

|

VERTEX2 --> FICA/FUTA/Medicare/EIC inputs to Vertex

|

VERTEX_WORK --> Work location SIT and SDI inputs to Quantum

|

VERTEX_WORK2 --> Work location County, City, Head Tax and School District Tax inputs to Quantum

|

(change JD to SUI state)

|

VERTEX_SUI --> SUI inputs to Quantum

|

(change JD to resident location)

|

VERTEX_HOME --> Resident location SIT inputs to Quantum

|

VERTEX_HOME2 --> Resident location County, City, and School District Tax inputs to Quantum

|

VERTEX_RESULTS

|

(Quantum Tax Calc called)

|

(All tax run results created)

Each Vertex element is associated with a specific formula (with the same name as the element), which serves to set up the various inputs to the Quantum tax calculation. Additionally, some direct results are created to store some useful results for safekeeping. Multiple elements are used under the same jurisdiction, because the formulas are limited in size and had to be split up. Because database items are cached in memory for use across formulas, this splitting causes minimal performance impact.

The above diagram indicates that after each formula is run, its results get dumped into the Quantum link area. This is accomplished by a C hook that, when the element's name takes the form VERTEX%, scans the formula results to identify those meant to be passed to the Quantum calc. The formula results are then passed into the link area (which is why they do not require proper formula result rules).

For VERTEX_RESULTS, the hook takes the additional step of calling the Quantum C code, and after the calculation is complete, transfers the results out of the link area into the formula results of VERTEX_RESULTS. Formula result rules then take over to produce the final tax run results.

This processing is repeated once for every Vertex entry (each work jurisdiction) belonging to the assignment. A skip rule is used on the VERTEX element to avoid processing overhead when the gross is zero for the jurisdiction. Since multiple Vertex entries may be processed, but federal taxes must only be calculated once, the formulas check whether the jurisdiction on the Vertex entry is the same as the employee's primary work location. Since this can only occur once, federal taxes are calculated at that time, and skipped otherwise.

Processing Priority

An important feature of the tax elements, including the VERTEX% elements, is that all share the same processing priority (4250), and the VERTEX indirect results use a new formula result rule type ("order indirect") to ensure that the entire above chain is completely processed before another chain (headed by a different Vertex entry) is begun. This prohibits the processing of two different Vertex entries from interleaving, which if allowed, can cause subtle problems due to the nature of the dependencies between successive Vertex formula processing chains. (For example, in the above scenario, when the California Vertex entry is started, the Mountain View processing chain must have finished. Its SIT tax result can then be included in the SIT_YTD balance accessed in the California Vertex entry processing.)

Tax Elements

Tax Deduction Elements

Tax deduction elements are categorized as follows:

The county and city taxes (which are income taxes) have two input values each: Pay Value (which contains the tax amount) and Jurisdiction.

The SDI and SUI elements have three input values: Pay Value, TAXABLE (which contains the portion of the gross that was not over the limit), and Jurisdiction.

The SIT elements have three input values: Pay Value, Supp Tax (which contains the portion of the Pay Value tax amount that was derived purely from the supplemental earnings), and Jurisdiction.

EIC has only Pay Value. FIT has only Pay Value and Supp Tax. FUTA, Medicare, and FICA have Pay Value and TAXABLE.

Naturally, the jurisdiction value matches the level of the tax (such as a state geocode for a state-level tax and a city geocode for a city-level tax). However, for the school district taxes, the jurisdiction value used is state code-school district code.

The reason there are WK (work) and RS (resident) element pairs of some tax types is that the VERTEX_RESULTS formula outputs two results for each such tax, since both work and resident jurisdictions may tax the earnings. A formula cannot produce two results of the same element type. Thus, every non-federal income tax deduction element must be split into two versions, each identical (even in their balance feeds) except for name.

If you use the standard tax interface, and an employee and works in the same tax jurisdiction, the data is stored against the _WK element. With the enhanced tax interface, the data is stored against the _RS element. The change provides a more accurate representation of the data, since the calculation uses the _RS element.

Subject Elements

The SUBJECT Elements (classification of Information, with category of Tax Balance) are special elements that store results the Tax Balance API uses. Because the earnings accumulations rules for non-federal taxes are complex, the tax balance values are not derived directly from the earnings and deductions results. Consequently, the run-level values of each non-derived non-federal tax balance are retained as run results. These results are direct results from the appropriate VERTEX formulas.

The SUBJECT Elements follow: City_SUBJECT_RS, City_SUBJECT_WK, County_SUBJECT_RS, County_SUBJECT_WK, SIT_SUBJECT_RS, and SIT_SUBJECT_WK, School_SUBJECT_RS, and School_SUBJECT_WK.

These SUBJECT elements have the following input values: Gross, Subj Whable, Subj NWhable, DC 401 Redns, S 125 Redns, Dep Care Redns.

The SDI_SUBJECT_EE, SDI_SUBJECT_ER, SUI_SUBJECT_EE, SUI_SUBJECT_ER elements have these input values: Gross, Subj Whable, DC 401 Redns, S 125 Redns, Dep Care Redns, Jurisdiction (this reflects the fact that limit taxes don't have not-withholdable rules).

Additionally, the FSP_SUBJECT element has the input value Reduced Subj Whable. This input value is used only internally as an input for Quantum.

SUBJECT elements for the federal taxes are not necessary because the federal tax balances can easily be derived directly from the earnings and deductions results through the SUBJECT dimensions. However, we maintain Federal SUBJECT elements for future use.

Run Result Suppression

Because of the potential of each assignment creating more than 40 run results (and over 100 run result values) for every run, and the performance degradation that would ensue, some means to prevent unnecessary run results was required. The core mechanism does not easily allow for the conditional creation of run results. The average assignment will only have 10 or so useful run results (since many of the possible taxes and situations catered for are not very common).

The solution was to add code to the localization C hook. This hook serves to set up the non-core (localization) contexts, such as Jurisdiction and Government Reporting Entity for use in formulas. It is called for each element as it is processed, and allows a check for whether the result is unnecessary. In this case a special flag is set to disable its creation run result on the database. This portion of C code is called the "run result suppressor"; the rules it follows to strip out useless run results follow:

  1. If the element name is like VERTEX% then suppress it.

    (Such results are always useless; the element exists only to trigger processing, not to store information.)

  2. If the element is a non-federal tax deduction and its value is zero, then suppress it.

    This suppression is appropriate since the balance mechanism treats the absence of run results as zero, and no code is dependent on the existence of tax run results.

  3. If the element is a SUBJECT element, then check if the return code indicates that the corresponding tax does not exist, in which case suppress it.

    This suppression prevents the big SUBJECT results from being created for jurisdictions that do not even have tax, such as the state income tax for Texas, or the city tax for Mountain View.

Balances

Naturally, all the tax deduction and SUBJECT elements each have their own primary balances. There is one balance for each of the SUBJECT elements' input values (except Jurisdiction). The earnings and deductions elements also have their own primary balances.

Some of the important composite balances follow:

The following balances are special in that they are fed by elements, not on the basis of their classification, but by their classification and category. This process is accomplished using a PL/SQL procedure called the "category feeder"that is called by the earnings and deductions template procedures to set up any necessary feeds. The package containing the feeder procedure is called pay_us_cgty_feeds_pkg.

Note: There is no true Section 125 Balance that includes both Health Care 125 and Dependent Care 125 (for the Production 3 release).

For all of the above (and many other startup balances), the following dimension combinations are used: _ASG_RUN, _ASG_PTD, _ASG_MONTH, _ASG_QTD, _ASG_YTD, _ASG_GRE_RUN, _ASG_GRE_PTD, _ASG_GRE_MONTH, _ASG_GRE_QTD, _ASG_GRE_YTD, _PER_RUN, _PER_MONTH, _PER_QTD, _PER_YTD, _PER_GRE_RUN, _PER_GRE_MONTH, _PER_GRE_QTD, and _PER_GRE_YTD, _PAYMENTS.

For balances accessed in the Tax Balance API (including the earnings and pre-tax deductions balances), these GRE-level dimensions are also used: _GRE_RUN, _GRE_MONTH, _GRE_QTD, and _GRE_YTD.

For the non-federal tax deductions, jurisdiction gets involved and then the JD versions of the above dimensions must be used. The JD dimensions include extra SQL to filter out those run results whose jurisdiction result value does not match the given context. Thus, SIT_WITHHELD_ASG_JD_YTD, with the JD contexts et to 05-000-0000 (which is California), will return only California state tax withheld.

Jurisdiction Level

In order that the JD dimensions may be used at different levels (state, county, and city), some method of knowing how strictly to match the run result and context JDs is needed. For example, to calculate "all earnings earned within the state of California," all earnings tagged like 05-234-2893 or 05-484-3497 would be included, thus matching the first two digits. But for "all earnings earned within California, but not in any city or county," only tags that exactly match "05-000-0000" would be included.

Creating the jurisdiction level and adding it to the balance type as a column allows for this calculation. It contains a code that specifies how many characters of the jurisdiction code (starting from the left) must match to be included in the balance sum. The allowed values are 0 (federal), 2 (state), 6 (county), 11 (city). Putting this information on the balance type prevents the number of dimensions from multiplying by 4 (since separate, federal, state, county, and city level dimensions would be required). However, the side effect is a replication of some balances into four (to maintain the number of necessary defined balances).

For example, four versions of the Regular Earnings balance are exactly the same, except that they differ in jurisdiction level. These versions follow:

Those balances whose jurisdiction level is 0 are required to use the non-JD dimensions and those whose jd level is not 0 are required to use the JD dimensions. For example, the defined balance REGULAR_EARNINGS_ASG_RUN returns all regular earnings in the run, but REGULAR_EARNINGS_CITY_ASG_JD_RUN (with the JD context set to Houston) returns all those earnings that are tagged, and whose entire eleven character code matches Houston's. Regular Earnings is the only balance that is replicated in this way, because it has the unique property of including tagged and untagged elements. The State and County versions are unused, and the City version is used to get the tagged portion for Step 4 of the Earnings Accumulation Rules for non-federal taxes.

The balance dimension of _DEFAULT_ASG_RUN exists as a special case; it includes only those run results that are not tagged. This balance dimension is used only in conjunction with the Regular Earnings balance, in order to implement Step 3 of the Earnings Accumulation Rules for non-federal taxes.

The jurisdiction code placed on the school district results is state code-school district code. Since the school district code is five digits long, the jurisdiction level is 8. The only place such a code may appear is on a school district tax result; earnings may not be tagged in such a jurisdiction.

Tax Type and the Subject Dimensions

The Subject dimensions were created to allow filtering of run results according to taxability rules. These dimensions are instrumental for obtaining the subject withholdable and pretax reduction types of tax balances.

A design tradeoff similar to the jurisdiction design tradeoff was encountered; tax type is a new context introduced for the subject dimensions, but it would potentially force the creation of a whole set of dimensions for each tax type (of which there are ten). Instead, as for jurisdiction, it was decided to place the tax type context on the balance type, forcing a replication of balance types, though limited only to a small set.

Allowed tax type values are based on the lookup_codes from US_TAX_TYPE). These values follow: FIT, MEDICARE, FICA, FUTA, EIC, NW_FIT, SDI, SUI, SIT, and NW_SIT. (As mentioned before, city and county level taxes use the SIT tax type.)

Balances used with the subject dimensions are fed by elements for which taxability rules exist. These elements follow:

The first four balances are used for composing tax balances. Ten versions of each balance appear, with names in the form old name for tax type: Supplemental Earnings for FIT, Supplemental Earnings for NW_FIT, and Supplemental Earnings for SDI, for example.

Commissions is used only internally, so only two versions exist. The jurisdiction level on the balances matches the tax type.

When combined with a subject dimension, these tax types indicate the tax to which the taxability rules refers. For example, when using Supplemental Earnings for SDI with _SUBJECT_TO_TAX_JD_ASG_GRE_RUN and the JD context set to California, we get all supplemental earnings which are subject to California SDI. For income taxes, the FIT or SIT tax type returns the subject and withholdable amount, and the NW_FIT or NW_SIT returns the subject and not the withholdable amount. (For deductions, this distinction is meaningless and NW_FIT/NW_SIT is not used; FIT/SIT tax types return the amount of pretax deduction that is not allowed to reduce gross.)

The following subject dimensions are currently used:

These merely extend the basic balance dimensions with the taxability rules filtering for each run result, its classification and category are retrieved from the parent element type, referenced into the taxability rules table along with the tax type from the balance type. If a row is found in the taxability rules table, then the result is included in the sum. Hence the relationship between the meaning of the existence of a row in the taxability rules table and the value returned by the subject dimensions.

The reason only one JD subject dimension exists is that only the ASG_GRE_RUN level dimension is needed.

Tax Balance API Implementation Details

Federal taxes are obtained directly from the earnings and deductions results. Given a balance dimension (BD), and a reference tax (TAX), the defined balances are as follows:

Other federal balances derived from the above balances follow:

For the nonfederal taxes, we use the balances associated with each of the subject element input values, which store the run-level amounts for the basic (non-derived) tax balances:

Other nonfederal balances derived from the above nonfederal balances follow:

Geocode Tables

For the purpose of identifying taxing jurisdictions, Vertex Inc. has defined a numbering scheme called geocodes. When entering employee tax filing information and work and resident locations, the state name/county name/city name information entered by the user must be converted into geocode format to be understood by the Vertex calculation. Each filing jurisdiction (from the Tax Rules Form) is converted into geocode format and placed in the jurisdiction input value of a Vertex element entry. The geocodes derived from the work and resident addresses are obtained through a PL/SQL function called Addr_Val, since the address tables hold only city/county/state names (plus zip code).

The geocode format is xx-xxx-xxxx, where the first two digits are the state code, the next three digits are the county code, and the last four digits are the city code, with each code separated by hyphens. The county code refers to "county within the state," meaning that the code is meaningful only given the state code. Similarly, the city code identifies the "city within the state".

All zeros for a code means that the particular level is ignored. Thus, 44-201-0000 refers to a whole county and no city, 44-000-0000 represents an entire state, and 00-000-0000 is the federal jurisdiction (anywhere in the United States). Note that 44-201-0000 refers to Texas-Harris County, but 18-201-0000 refers to Robertson County in Kentucky. 44-201-1440 represents Texas-Harris County-Houston, but 44-157-1440 means Texas-Fort Bend County-Houston. This approach enables us to identify cities that span multiple counties.

Since counties cannot cross state borders, it is sufficient to identify them by a unique number within a state. Identifying cities is a different matter. Cities can cross county and state boundaries, so the proper scheme is to use a universally unique code. Vertex does not use this approach, so we find that 04-091-0590 and 44-037-2950 represent two parts of the city of Texarkana, which lies partly in Arkansas, and partly in Texas. by looking at the geocodes, there is no way to tell that they refer to the same actual city.

This is a limitation of the Vertex geocode scheme and means that Oracle Payroll cannot calculate the "city tax in all of Texarkana." The Texas and Arkansas portions must be calculated separately.

A similar problem concerns the format of the geocode and the simplistic matching scheme of the JD dimensions. For city-level balances, the jurisdiction level is 11, and so the entire geocode must match in order to be included in the balance sum. This approach has the side effect of only including the part of the city in the county that appears in the JD context. For example, CITY_WITHHELD_ASG_JD_RUN with the JD context set to 44-157-1440 only returns the portion of the Houston city tax based on earnings in Fort Bend County, not in Harris or Montgomery County. To get the full "Houston city tax withheld" value, the balance must be evaluated for all three counties.

A somewhat separate set of codes are the school district codes, used to identify which school district is owed tax. The school district code is a five digit number, with one set of codes for each state, chosen by the state.

An employee registers for school district tax in his resident city. Earnings are never tagged against school districts, so the code only appears on jurisdiction _result_ values for school district tax, the school district SUBJECT results, and nowhere else. The format used is xx-xxxxx, where the first two digits are the state code, and the next five digits are the school district code. This approach avoids the potential problems of school districts crossing city borders. Thus, when accessing school district level balances, this format must be used for the JD context.

Table Structure

The geocode data is held in the following tables:

The data in these tables is sourced from Vertex, and does not contain cities with less than 250 residents.

APIs

The tables that contain addresses (PER_ADDRESSES and HR_LOCATIONS) are referenced against this data to ensure that proper geocode translations exist. These tables themselves only hold the state, county, city names and the zip code. A function Addr_Val translates the information on the address to the internally used geocode representation. In addition to the validation built into the forms, a SQL script is available to check all addresses at once.

Address Validation

A SQL script (pyvaladr.sql) has been provided to validate US addresses. Customers who have been using Oracle Human Resources and are now implementing Oracle Payroll can use this script to ensure that the addresses have already been entered are valid. In this case, valid means that the state, county, city, and zip code are all consistent with the data in the geocode tables. This script lists all invalid addresses in an output file (pyvaladr.lst).

For PER_ADDRESSES, it shows the full name, employee number, social security number and address of those people whose address is invalid. For HR_LOCATIONS, it shows the location code and address of the location whose address is not valid.

Addr_Val

This function takes the state abbreviation, county name, city name, and zip code from an address and returns the corresponding geocode. Addr_Val is in the HR_US_FF_UDFS package. This function is available for use in formulas as a User-Defined Function (UDF) called get_geocode. It appears as follows:

FUNCTION addr_val (     p_state_abbrev  IN VARCHAR2 DEFAULT NULL,

                        p_county_name   IN VARCHAR2 DEFAULT NULL,

                        p_city_name     IN VARCHAR2 DEFAULT NULL,

                        p_zip_code      IN VARCHAR2 DEFAULT NULL)

RETURN VARCHAR2;

If no valid geocode is found, then 00-000-0000 is returned.

Expansion

In the near future, Oracle Payroll will allow employees to have work and resident locations for cities that Vertex does not support. In order to do so, a special non-Vertex geocode xx-xxx-UNKN will be used. No city with such a geocode will have the PRIMARY_FLAG set to Y, since they will not be aliases to a single true city with that geocode.

Users will be able to add new cities to the geocode tables, which will be marked with the special geocode. Addresses can then include the new cities. The tax subsystem, of course, will not support these new cities; tax rules will not be permitted to be entered against the new cities. Instead the user will need to enter any relevant percentages and filing status against the containing county. Addr_Val (from above) assists by converting the UNKN city code to 0000, resulting in a county level geocode. This measure prevents any problems stemming from the fact that different "new cities" might have the same geocode. If multiple Vertex entries with the same geocode were created, the same tax could potentially be calculated more than once.