University of Arkansas
At the University of Arkansas we have taken the mundane mainframe based payroll application, brought it to the web, and made it available to all employees for what if net pay simulations. (Employees are able to view their future earnings and then alter their taxing and benefit options or gross earnings in order to see the affect on their net pay.) This paper demonstrates and explains this web based application and explores how it was implemented with efficiency as a primary goal. The paper is composed of the following sections:
The first three sections require no special knowledge or background on the part of the reader, while the last two presume some understanding of the Natural programming language.
The University of Arkansas has been a Natural/ADABAS shop since 1986. All of our non-student systems have been developed in house with these tools. We have named this suite of applications BASIS for Business and Administrative Strategic Information Systems. The Payroll system was developed and implemented in 1999. Interestingly, Natural and ADABAS were purchased with the intent of developing a replacement Student Information System. At the time we were running MSA packaged systems for purchasing, accounting and payroll/HR. Since then we are on our second package purchase for Student Information Systems (most recently Peoplesoft, which does not use SAG products at all) and have replaced all MSA systems with custom Natural/ADABAS applications.
We began web enabling our BASIS applications in 2001 using the Com-Plete HTTP Server with Natural as the CGI language. (We love the Com-Plete HTTP server and the fact that all our web development is done in Natural and all execution is on the mainframe under Com-Plete.) Our objective with web enablement was not to replace our 3270 screens, but rather to:
Our web applications are built using a browse/select interface and architecture. ADABAS files are browsed and a subset of data is presented for further selection and subsequent operation. We can start at a high level summary financial balance and drill all the way down to the invoice lines or other transactions that made up the balance. Even the menus are ADABAS files that are browsed. The Natural programs providing the browse/select feature are all generated using an ISPF macro. (Programs providing the final presentation or interface are hand coded.) For more on how we do this, please see my 11th Natural Conference presentation Web Enablement: Challenges and Solutions.
We had already provided the display of most of our BASIS information on the web, since the development of programs to present information is fairly easy. We had also developed several maintenance functions on the web: address changes, select employee options, payroll deposit bank account changes, annual benefits enrollment, and we are about to roll out travel claims. There are many more self service options we want to provide, but they are tedious and time consuming to complete (and we have a very small staff of six to do all BASIS development and support).
So, why did we chose to do the Simulated Earnings Statement on the web? There was a need – employees came to Payroll asking their what if questions. Payroll was able to do net pay calculations (or what we call gross-to-net) either via a PC product called Paybreeze or via a 3270 screen we developed along with the payroll application. Paybreeze required the entry of all individual parameters (gross and all deducts and taxing options). Our 3270 screen required the entry of the gross pay and then used all the known Payroll data to figure the taxes and deductions, but it was very difficult to change any of those options. Both of these required employees to go to Payroll and request this service, and it was a very manual effort by the Payroll Office. Neither offered the employee much power in manipulating the variables or anything they could take back with them to study and ponder. The need for this was made more apparent when we did the annual benefits enrollment, since that is when you are forced to make several of these financial decisions.
So we did it because:
Simulating future net pay is a two step process, and thus two separate web pages. The first step is to present the future payrolls and the system calculated gross pay for each. (We run three different types of payrolls a month plus special August and May payrolls for nine month employees.) From this page the employee selects the desired payroll (and gross pay). This results in the presentation of the Simulated Earnings Statement, which is preceded by the simulation options available for change. The following sections present and discuss each of these pages.
The Future Pay web page lists the payrolls that have not yet run but are to be paid within the next 90 days (obtained by browsing an ADABAS file where payrolls are defined). If the payroll has already been run but not yet been paid (the pay date is still in the future), we exclude it from the list since the Earnings Statement with all taxes, deductions, and net is already available to the employee. Of course most employees know what they are going to be paid, but this is not always the case for our nine month faculty in the summer who are teaching or are being paid from grants for summer research. The same may be true for hourly employees or employees expecting overtime. (This is normally just a clerical issue of whether the entries have been made and approved, but sometimes still the employee is not aware of what he will be paid or when.) Therefore, having future pay on the web is a benefit in and of itself –- without the Simulated Earnings Statement. Figure 1 is an example of this Future Pay web page generated on our TEST system.
|Figure 1. Future Pay web page example.|
Determining the gross pay for employees is not trivial, but fortunately for the University of Arkansas it is not too complicated. The following are the possible sources of pay that must be accessed.
Several sources may be combined on the same payroll to result in the employee's gross pay. As shown in Figure 1, these sources are itemized in summary form below the payroll total. The gross pay amounts for the next payrolls of each type are active links (even if zero) which are used to access the Simulated Earnings Statement for these payrolls. Payrolls further out are not allowed as links since the flexible spending deduction would be calculated as if no deduction was performed for the intermediate payrolls and it would calculate an extra catch-up amount. We considered this issue just too difficult to attempt to explain and therefore restricted the payrolls available for simulation.
There are three other hidden issues that had to be dealt with during Future Pay, since they are critical to the subsequent net pay calculation.
After selecting the desired payroll on Future Pay, the Simulated Earnings Statement page is presented. The upper portion of the page contains the options the user is allowed to change for simulation purposes. Figure 2 is an example of this top part.
|Figure 2. Top portion of the initial (before option changes) Simulated Earnings Statement web page.|
The bottom portion of the page contains the simulated earnings statement with all calculations done as currently defined. This shows the taxes, deductions, and net pay as calculated based upon all current definitions. Figure 3 is an example of this bottom part.
|Figure 3. Bottom portion of initial (before what if) Simulated Earnings Statement web page.|
This initial page can be of benefit on its own, but the real power comes to play when the employee changes any of the options on the top portion of the page and then presses the What If button. Following is a discussion of these options.
|Figure 4. Entry fields for changing income tax options.|
An employee's federal and state income tax options are available for change as shown in Figure 4. (Note that marital status is not used in calculating Arkansas state income tax withholding.) Some employee's are exempt from either one or the other or both of these. This entire section of the form is suppressed if the employee is not subject to federal or state income tax withholding. If only one and not the other, then the one not applicable is suppressed.
|Figure 5. Tax basis selection for certain deductions.|
Employees have the option of having any of the following 5 deductions taken on a pre or an after tax basis:
Pre tax means that the earnings on which income taxes and OASDI/Medicare will be calculated will be reduced by the amount of these deductions. Figure 5 shows how the tax basis for these deductions can be changed. The amount of the deduction is also shown for the benefit of the employee. In this example, vision and cancer insurance have been suppressed since the employee has no current deduction for either of these.
|Figure 6. Options for increasing or decreasing tax deferred or tax exempt deductions.|
In the next section, shown in Figure 6, the employee can increase or decrease the amount of tax deferred (retirement) or tax exempt (flexible spending account contributions plus medical, vision, cancer, dental, and parking) deductions. Tax deferred deductions reduce the earnings on which income tax is calculated, while tax exempt deductions reduce the earnings on which income tax, OASDI, and Medicare are calculated. The extent to which an employee already has deferred or exempt deductions is shown as the amount by which he will be allowed to reduce (enter a negative value) for each. There are maximum amounts that can be deferred or exempted, but we don’t try to determine what those might be since there are too many variables. In that regard we refer the employee to the Benefits Office. For most people it is fairly straight forward – what if I increased (or decreased) my medical flexible spending contribution by $100.
|Figure 7. Option for increasing or decreasing gross earnings and the action buttons.|
Gross earnings can be reduced down to zero, or can be increased by an arbitrary maximum amount of $10,000, as shown in Figure 7. This is a nice feature if the employee is anticipating a raise, contemplating going part time, or just wondering how his net is affected by a change in gross.
The action buttons available to the employee are also shown in Figure 7. The Restart button will discard all option changes and allow the employee to start over. The What If button will validate the entries and, if valid, display the options used in the top portion of the page while producing the Simulated Earnings Statement with columns for both the What If and As Currently Defined scenarios in the bottom portion. Figure 8 shows the top portion of the page with the protected options. Notice how the previous option setting is displayed below the entry field with a light yellow background. This is our standard presentation for modified fields, so that the changed values can be readily identified and the previous settings known. Also note that the Restart button is still present so that the employee can start over and see the effect of other option changes.
|Figure 8. Options (top) portion of the Simulated Earnings Statement after What If.|
Figure 9 shows the bottom portion of the page, the Simulated Earnings Statement after the What If button has been pressed.
|Figure 9. The Simulated Earnings Statement after What If.|
The What If column is the result of doing the calculations with the option changes specified while the As Currently Defined column is based upon the current data base settings. Notice how the amounts in the What If column are bold when they differ from those in the As Currently Defined column. This allows easy identification of the impact of the option changes. Also notice how Medical Coverage appears in both the Tax Exempt section and the Other Deductions section. Recall that we chose to make this an after tax deduction, so it moved and appears in different sections for the two scenarios.
This is in some ways a very poor example of using this facility because we made four different changes at the same time. This is something that we advise our employees against doing because it is very difficult to sort out the impact of each change. It would be much more informative to make these changes one at a time and note the impact to net pay individually. Multiple changes were performed here merely for the purpose of demonstrating the process of changing the various options.
Now that we have seen what was done, we will begin to look at how it was done – how this net pay simulation was put together from the batch payroll components. Our batch payroll is comprised of 4 phases or steps (literally 4 job steps).
The generation phase is all that we are concerned about here because it is responsible for determining who to pay how much and it performs all tax, deduction, and net pay calculations. It is comprised of one main program, EPBBPG. Figure 10 shows a slightly sanitized version of the List Xref Invoked Programs output for EPBBPG. (Components dealing with checks, pro-ration of fringe benefits across pay sources, and some other special processes have been excluded as well as some trivial look-up/derivation subprograms.) This is basically what we had to start with, although some minor restructuring was required to accommodate Future Pay and the Simulated Earnings Statement on the web.
|Figure 10. The cross reference Invoked Programs for EPBBPG.|
What we see here is the core of our payroll. The two primary functions performed are:
|Figure 11. The cross reference Invoked Programs
for EPOBFP – Future Pay.
Now lets look at the List Xref Invoked Programs output for the program that presents an employee’s Future Pay on the web, program EPOBFP. This is shown in Figure 11, which has also been slightly sanitized by the removal of trivial subprograms. Notice the five subprograms in green. These obtain the future gross pay from the various pay sources and, although not identical, are equivalent to the five similar subprograms invoked in the batch payroll. Of course in batch, gross pay is obtained for all employees and written to sequential data sets, while on the web it is obtained for only a single employee. Where the logic in determining gross pay is not trivial (appointed pay and summer teaching), the same two subprograms are used on the web as in the batch payroll: EPNPCALC (which indirectly uses results obtained by PBNLCTLG) and EPNSUMT.
UWOFIN is essentially an error processing routine that is called when some unforeseen circumstance is encountered. Subprogram EPNBFP-I was written to collect the diverse parameters required for appointed pay calculation and used by EPNPCALC. Once collected, these parameters are preserved so that the process does not have to be repeated for every Future Pay request. This is discussed more later under Techniques for Efficiency.
|Figure 12. The cross reference Invoked Programs
for EPOGTNSW – Simulated Earnings Statement.
Finally, we will examine the List Xref Invoked Programs output for the program that performs the net pay calculation and presents the Simulated Earnings Statement on the web, program EPOGTNSW. This is shown in Figure 12, which, as in previous examples, has had insignificant programs removed. Notice the subprogram names in blue which are shared with the batch payroll process. These are responsible for the calculation of all taxes, deductions, benefits, and the resulting net pay – all the hard work. Thanks to the modular design initially used, we are now able to share these components between the batch payroll and the simulations performed on the web.
A few comments regarding the other subprograms shown here. EPNCBGTI and EPNFFGI were written to buffer some of the data commonly used for these calculations by storing/retrieving it from the In-Core Data Base – discussed in the next section Techniques for Efficiency. EPNGTNSE actually generates the HTML for the Simulated Earnings Statement.
The most significant factor affecting performance is I/O, and in a data base environment it is data base I/O. Our goal has always been to minimize the number of ADABAS calls, especially in any online environment where any abuse is multiplied (compounded) by the number of simultaneous users. Of course that doesn’t mean we ignore batch efficiency. The same techniques learned and used for an online environment can generally be applied to batch as well.
One way we have minimized I/O is by not re-reading the same records. To do this we merely save the data read – we store it in an area internal to the main program using LDA or GDA variables. Of course we don’t go to this effort for everything, but for high activity areas it can have a significant impact in reducing ADABAS calls. In one area, existing components were already being used for this purpose in both batch and 3270 online environments. So naturally we extended use of these to the web, but had to figure out how to retain this local data (internal table) across stateless web interactions in order to receive maximum benefit. We chose to use the Entire System Server In-Core Data Base for this purpose. Although we have not tried to measure the cost difference between accessing the In-Core Data Base versus ADABAS, the logical presumption is that In-Core access has to be much more efficient than ADABAS. (In the near future, we plan to move this buffered data from the In-Core Data Base to a common GDA that is currently used by our web server ID, making this process even more efficient.)
Following is a table showing the ADABAS calls saved by using the In-Core Data Base (based upon the example demonstrated).
|Future Pay (1st use)||8||30|
|Future Pay (subsequent use)||5||15||15|
|Simulated Earnings Statement (1st use)||37||195|
|Simulated Earnings Statement (subsequent use)||32||22||173|
|What If with Same Gross||32||22||173|
|What If with Gross Change||32||38||173+|
The 1st use represents the check against the In-Core Data Base for the necessary data, finding that it is not present because it has not yet been populated, accessing ADABAS for the data, and then saving the needed information back to the In-Core Data Base so that it is available for subsequent uses. The reduction in ADABAS calls between the 1st and the subsequent use is the savings in calls realized because of the In-Core Data Base. The What Ifs are always a subsequent use because you have to bring up the Simulated Earnings Statement (1st use) before you can press the What If button. The What Ifs would see the same 195 (or more) ADABAS calls as the 1st use Simulated Earnings Statement if it were not for the In-Core Data Base. Additional I/O is required when the Gross Earnings is changed since the routine must then repeat calls to the components that determine benefits and deductions since many of those are based upon the gross pay.
The following links will display the TEST DBLOGs used to generate the above summary information (data base 147 is our Entire System Server).
In the List Xref data previously presented for Future Pay (EPOBFP) and the Simulated Earnings Statement (EPOGTNSW), there were three subprograms listed that are involved in the efficiency issue. The specific techniques used to reduce ADABAS calls will be examined through these three routines.
This subprogram is called by the Future Pay process. It first attempts to read the associated In-Core data. If there is no entry or it wasn’t built today (since the server is basically up 24/7), it gets the necessary data from ADABAS, does some date calculations, saves the necessary information to the In-Core data base, and then returns the desired information. If the In-Core entry was present and built today, it merely returns the data obtained. The data consist of the future payrolls and parameters necessary for calculating future pay:
Also saved to In-Core are the results of date related calculations (work days and total days in the month) so that they will not have to be repeated. As shown in the table above, use of In-Core here saves approximately 15 ADABAS calls with every subsequent execution throughout the day.
In our Payroll, we have a code representing a specific Benefit (including taxes), Deduction (including taxes), Other earning (taxable fringe benefit that increases your taxable wage base), or EIC (federal Earned Income Credit) which we call a BDOE code. For the most part you can think of these as the various lines you see on our Earnings Statement. For each employee paid (or to be paid in the case of our simulation) there is an array of these BDOEs with amounts for each (its a PE group on an ADABAS file).
The attributes for a BDOE are stored on a table. As you can imagine, the attributes for the BDOEs are required for a great deal of our payroll processing. This is the case online (where if nothing else we want to display the description associated with the code) as well as in batch. To keep from hitting the table every time we need a BDOE attribute, we populate an internal table with the needed values as required. Online this internal table is part of each user’s GDA while in batch it is just an LDA used throughout the program execution (payroll generation for example). The same components (copycode EPCFFGBT and subprogram EPNFFGBT) are used for this in batch and 3270 online, so we naturally extended use of these to the web. However, to maximize the benefit we needed to retain this internal table across stateless web interactions. So again we turned to the Entire System Server In-Core Data Base. (Although we plan to move this data to the GDA currently used by our web server ID.)
Subprogram EPNFFGBI manages these entries on the In-Core Data Base. It is executed at the beginning and the end of the Simulated Earnings Statement. At the beginning it is passed a Get request to obtain any current BDOE entries from the In-Core Data Base. After accessing these entries, the date this request is made is compared to the date on which the list was prepared. If out of date, they are discarded and we start over with an empty list. At the end of the simulated earnings statement, if the number of BDOEs on the internal table has increased then EPNFFGBI is called with a Set request to have the current list replace those on the In-Core Data Base.
Management of the internal LDA/GDA table is done with the copycode EPCFFGBT and subprogram EPNFFGBT. Applications pass EPCFFGBT the desired BDOE code and are returned an index entry into the internal array structure. Figure 13 is an example of how an application uses the copycode (the CAF group structure is the internal table).
|Figure 13. Example use of EPCFFGBT.|
If the desired BDOE is not already in the internal array, subprogram EPNFFGBT is called to obtain the data from ADABAS and populate a new array entry. If the BDOE code is invalid, an index entry of zero is returned. If the internal table is full, then the last entry is overwritten. This is used throughout our Payroll application: batch, 3270, and the web.
Some details about our BDOE table. During our August payroll a total of 90 different BDOEs were used. The internal table we currently use is sized at 120 (and has been set at that for the past 7 years). The total number of BDOEs on the table is currently 111 active and 57 inactive. Also, we currently have our programs set up to manage a maximum of 60 BDOEs per employee with high water usage in the 40s.
Within our payroll application, we have various subprograms that perform specific tax and benefit calculations. For example, EPNCFIT calculates the federal income tax deduction for an employee. Parameters (rates and tables) for each calculation subprogram are buffered within individual data blocks by the calling or main program – without knowing or caring what this data is or even how it is defined. The appropriate buffer is passed to the calculation subprogram. If it is not populated or not populated with the needed data (correct version or effective date):
If the needed data is in the buffer, it is used as required and the results returned. This is what we have always done for the batch payroll, for dynamic payroll calculations in the 3270 environment, and now what we are doing on the web for net pay simulations. (This design was core to the development of our payroll system since we always envisioned the need to perform all payroll calculations on an individual basis – and we've always been concerned about efficiency.) What is different on the web is the need to retain and restore all of these parameters across stateless web executions. Subprogram EPNCBGTI is used for this purpose. It collects all the various parameters (buffers) together and saves or restores them to the In-Core Data Base. It is called with a Get at the beginning of the Simulated Earnings Statement and with a Set (if the data changed) at the end. As we saw, this drastically reduces the number of ADABAS calls that have to be performed in subsequent simulations for the same payroll.
Due to a modular design where a main program buffers parameters and tables, we are able to efficiently make complex payroll calculations available to all employees – empowering them to better manage their individual finances.
For more information about our Simulated Earnings Statement, you can view our user help page including screen snap shots at http://www.uark.edu/basis/webBASIS/EPOGTNSW.html.