>Oops! I didn't want to come here - please send me back.
Topics Covered:
When I started at SSEC ('79), requisitions (hereafter reqs) were typed on a typewriter. With up to six carbon copies it was a royal pain in the rear, especially with typing skills like mine. The first hint of automation came when SSEC got their first dedicated word processors (IBM DisplayWriters). These were huge, single purpose, very expensive computers that were slow and noisy, but they were a lot better than typewriters. Having had to get up to my ears in the specifications for these machines in order to get them purchased, I became aware of capabilities that went a little beyond just replacing the typewriters, and easing the preparation of simple documents. I found that there was rudimentary database capability, which would be combined with merging as would be done for a form letter to automate the processing of reqs. Much of the repeated information (like the department name, address and so on) could be already on the form letter, and the variable information could be typed into a source document. When the two were merged, we had a printed req that didn't have any typos, and the variable data could be saved and printed out in other formats. Since that information about every req was being recorded by hand in three different places, this was a great step forward. It took a little convincing to get the use of one of these at first, but once the capability was demonstrated, I not only made this first step in the automation of req processing, but I even became briefly the official DisplayWriter guru of the Center. More importantly though, this little adventure with technology changed forever my career in very good ways.
The life of dedicated word processors was very short lived, as they appeared right at the dawn of the PC phenomenon, and were almost over night replaced by much less expensive and much more capable PCs. Being an enterprising young lad (well, enterprising anyway) with a grand total of one college programming class to my credit (almost 20 years earlier) I decided to buy a PC of my own and learn to program it, largely motivated by my itch to further automate the preparation of reqs. MS Basic came free with the computer, so that was the language of choice. Some of the people here who knew more than I did about programming said that Basic would be too slow, but I plunged ahead and created a system that did just about everything I thought I needed. It had an on line vendor file, built in defaults for all the standard pieces of data, and produced all the reports that we needed for tracking the whole purchasing process. Amazingly, it was a snappy performer, which surprised those who had thought that Basic wouldn't do the job. Anyway, that system served us well for 10 years, starting in late 1984.
As PC technology continued to advance and the Center began the move to Windows on the desktop, the old DOS based purchasing program began to show it's age. It didn't cooperate very well with Windows, and began to be more and more of a problem. As I tried to figure out what to replace it with, I discovered Visual Basic (VB). It was still the Basic language which I had worked with, but in a whole new environment. At first look, my intention was just to run a conversion routine which claimed to be able to take my old Basic program and turn it into a VB/Windows program. When I got my hands on VB though, I discovered a wealth of new capabilities that broadened my vision of what was possible with my limited programming skills. Connection to standard databases such as FoxBase and MS Access was almost too easy. It turned out that the FoxBase connection was buggy, so I ended up going with Access as the container for purchasing data.
A technical summary of where we are now is that I've built a total of eight systems for SSEC Business Services using VB. All of the data lives in Access databases, and a great many reports can be produced, both from the programs themselves, and on an ad-hoc basis via a third party product, Crystal Reports (CR). An additional functionality that CR lends to the overall picture is that we can export data to many formats (Excel, Word and e-mail just to name a few), either directly from the programs or on an ad hoc basis. Some more details on each package appear below.
Purchasing System:
The Purchasing system incorporates quite a few helps for actual req preparation, including an on line vendor file that grows automatically with use, help boxes with all of the special purchasing codes and their definitions, automatic math checks, automatic insertion of some of the standard text phrases most often used in the body of the req and many more. Once the order has been entered, it can be printed in Purchase Order form, along with a FAX cover sheet to make it easy to FAX the order to the vendor. When the req is printed for processing through the UW, an e-mail copy of the req is sent to the requester and any one else that might care (like the program manager that controls the money for the project being charged). There are a total of eleven printed reports produced by the program that help us track every aspect of the purchasing process. There are on line reports that show up in a window on the user's machine, with the option to print, e-mail or export the data to other formats. There is an automatic connection to the Center's overall accounting system by way of data export which passes all newly created reqs to the encumbrance file so that the accounting system knows what has been committed but not spent yet. This also provides the basis for the automatic coding of expenditures in the accounting system. The encumbrance file is given the req number and associated project number(s) so that when ledger charges come through the UW accounting system, the req number provides an automatic allocation to the correct project. There are numerous utilities built in that take care of file turnover at year's end and things like carrying forward continuing blanket orders so they don't have to be re-entered. Overall, it's a pretty good system which in fact has been shared with about a dozen other departments on campus.
Bid Processing Utility:
As a delegated purchasing department, we are authorized to use a simplified bid process for competitive purchases costing between $5,000 and $25,000. For the smaller purchases, we may just get verbal quotes via phone, e-mail or web pages. As the dollar amount goes up significantly ($10K and up) we tend to get a little more formal about the process. We developed a little utility that produces the documents necessary for this more formal process. It uses a database of potential bidders, and produces reports consisting of the formal invitation to bid with all the bid details in either a mailed or faxed format, along with the bidders list and envelopes if the bids will be mailed out. Not a major effort, but again it's a lot better than doing it all on a typewriter.
ProCard System:
I served on a campus committee to bring more automation to the purchasing process by way of charge cards. After about two years of hard work, we finally got our purchasing charge card in December of 1994. This opened up a whole chunk of monthly ledger charges that would not have a req number attached, so I had to come up with a system for tracking these charges. We call it the ProCard system in house, though I understand that's actually a proprietary name used by the data processing company that provides our charge card data (sorry, I'm using it anyway). With the VB based Purchasing system up and running, it seemed a simple matter to clone it, and make some minor changes to customize it for tracking our charge card expenditures. It shares many of the same features as the Purchasing program, including the use of the same vendor file. It is extremely useful, as we now place more orders via charge card than we do via reqs (see my Purchasing page for a statistical table if you're interested in the numbers). This program also has been shared with at least one other department on campus.
Travel System:
About a year into our use of the charge card for purchasing, we were asked by the folks at the Peterson Building to run a pilot program testing the use of the charge card to pay for airline tickets for travelers. Another cloning was in order. The ProCard program was cloned, and modifications added to track not only the purchase of airline tickets on the charge card, but to also track the processing of Travel Expense Reports. One of the interesting and serendipitous results of this was that we found the electronic data we receive from the card processing company includes the ticket number that was purchased. This led to the ability to put the ticket number in our travel database and get an automatic match up with the charge coming across on the ledger, just like we do with req numbers from the Purchasing system. Again, there are a great many reports that can be printed or exported as needed to manage the whole travel area.
External Invoice System:
Occasionally the Center is contracted to do a project that is funded by a Purchase Order from another institution, or by some other arrangement that doesn't warrant the establishment of a separate UW account number. To get payments from these institutions, we needed to send them an invoice, which until recently was prepared on a typewriter (yes, we still have them around for some things). This seemed like a good candidate for automation, so we cloned the Purchasing system again. The on line vendor file became a customer file, and the reporting capability evolved to help track invoices sent and payments received. The invoicing system also includes e-mail notification to concerned parties. Overall, another small step in the right direction toward office automation.
Proposal Processing System:
Proposals are the life blood of the Center, and every proposal that goes out requires a UW Transmittal Form (T-form), another typed, multi-carbon pain in the rear. This was another obvious candidate for automation, so we did it. The on line vendor file in this case became the list of funding agencies. There's a database of Primary Investigators so that the user can just type in a last name, and the full name, title and SS number are inserted. There's the usual compliment of reports to help track what proposals are out, and their status. The best part of all though is that now those ugly T-forms are printed out on the laser printer with all the right boxes checked and all the other data on the right lines without the user having to fiddle with it on a typewriter.
Personnel System:
This is the biggest project for me to date, and unfortunately couldn't be cloned from the Purchasing system. Ever since we developed the Center's PC based accounting system, we have incorporated rudimentary personnel features, but it has always been a problem. The Personnel Office always wanted to change the data when a change really happened (not unreasonable!), but Accounting, always processing data after the fact, needed the data to stay unchanged until the month after the change was made. We had a work-around for this, but in fact never really developed the personnel side of the system to the point that it met the needs of the Personnel Office. Things really were exacerbated when the UW began requiring all kinds of special forms (seven different forms at this point, referred to as IADS forms) for every imaginable kind of personnel transaction. Back to the typewriter again! Not only that, but after typing these ugly forms, the same data has to be re-keyed into the UW's personnel system.
Starting in mid '96 I began work (off and on unfortunately, as I do have other responsibilities) on a completely new personnel system. As of the first of June ('97) we're getting the new system installed at the users' desktops. It not only prints the seven horrible IADS forms, but also tracks everything else the Personnel Office cares to keep track of. We track data specific to academic appointments, classified appointments, emergency contact information, all relevant dates associated with an individual, degree and visa information, keys, home addresses, miscellaneous notes and action items as well as a complete title and rate history. Yet to come will be a section on benefits, and finally I hope to look into transmitting this data to the UW's system to avoid the currently required re-keying of IADS data. The accounting interaction is less painful, as we can extract the changed data that are relevant to accounting when they're needed without getting in the way of the personnel folks making changes whenever they need to. This is obviously a fairly new system, and is evolving as the users identify what works and what could be improved or added.
Shipper/RMA System:
There is a great deal of shipping that takes place associated with our research. These shipments need to be well documented for purposes of insurance, customs entry & etc., There is also frequent need to return purchased items to vendors for replacement, repair and other reasons associated with the purchasing operation. These two functions have been rolled together into the Shipper system. It has ties to purchasing's vendor file for things that need to be returned to vendors, and it has it's own on-line file of addresses for things shipped to locations not associated with purchasing. A special report from this system acts as our interface to the UW SWAP operation. Surplus or obsolete materials are reported on this special form, and faxed to SWAP, resulting in their hauling away all of our junk. This system has ties to our internal inventory system as well. The user can enter the SSEC bar-code numbers for the items shipped, and the capital inventory system will be automatically updated with the change in location and a reference to the shipper number that facilitated the move. A fairly simple system, but very useful.
Web and E-Mail Accounting:
The other software development I'm involved in is the overall accounting system for the Center. The output of that system has always been several boxes of paper per month, printed and distributed to managers, or bound and stored for auditors. In December of '97 we began putting all of that information on web pages that are accessible to SSEC staff. The hope is that by making all of this available on the web, we can eliminate printing of at least the management reports that are distributed each month. The pages have been produced each month, but the printing goes on - management is still deciding whether to discontinue the printing for distribution at this point.
An outgrowth of the web accounting effort has been the development of a program to distribute the same information via the Center's e-mail system. Our first approach has been to deliver what we call "Level 1" reports only. These reports would only contain the summarized data for a given contract or project by major spending category (Labor, Materials, Services, etc.,). These are distributed in an automated fashion that is driven by a database of managers and the projects/contracts that they're in charge of. The footer of each of these mail messages contains information on how the manager (or anyone else) can get the rest of the detail by just sending off an e-mail request. It also contains a link to the web pages noted above, although the advantage of receiving the data via e-mail is that the text can then be incorporated into MS Word, Excel, or whatever for use in other ways without having to be re-keyed. One feature that is offered is brand new and has never before been available from our accounting programs. The cross-tab report allows a user to request data on a contract or project over an extended time period (all of the standard reports only show current month and to date numbers, but no month-by-month figures). The returned e-mail contains an attached Excel spread sheet with all the data that was requested.
Hopefully this combination of web pages and e-mail distribution and response will eliminate the need to print reports for managers in the near future.
Capital Inventory System:
It has always been a chore to keep track of all the "stuff" we accumulate in the Center. Unfortunately, we do have to keep pretty close tabs on equipment we purchase because much of it is purchased with Federal funds, and we are accountable to periodically report on the status, condition and whereabouts of all such equipment. In an effort to help track all this, we've developed a system to keep track of it all. It starts at the point of purchase where the various purchasing systems create a record in the inventory system with the basics of the item saved in a database, and a bar-code number assigned to the item. Matching bar-code stickers are printed, and are applied to the item when it is received in Mike Dean's shipping/receiving department. Then throughout the life of the item, it's movements and anything else of importance is noted in the database. The shipper system updates the inventory system when an item leaves SSEC, and if it returns, Mike Dean's shop again updates the database. It remains a bit of a work in progress, as there are not many reports it can generate so far. There are a lot of them on my "do-list" for when I get a slow day.
Applets:
There are a number of little programs that can best be called Applets, as they're small, "quick-n-dirty" solutions to some of our requirements in the Business Office. Four that I completed recently have to do with getting payments/accounting information out of our "historical" databases. I can get payment, project, req and travel data into printed or exportable formats from data as far back as it's available from our current accounting system('89 for some of the earliest data). A couple Applets that Kelly has worked up have to do with getting data out of the UW's data warehouse. She's able to get the same data that we currently FTP from DoIT. They have done a special run to produce this file for years. With that costing about $100 per month, we hope to eventually replace the FTP download with Kelly's applet. She's also cloned that system to enable the retrieval of data on receipts, which reflect payments made in response to the invoices generated by the Invoice system noted above.
Summary:
It's been interesting to help build these systems that pretty much keep the Business Office at SSEC afloat. As you can see from my VB based products, I'm too much of a "do-it-yourselfer" to let Kelly have all the fun. I dare say there are those on SSEC's staff who will be surprised at the volume of software development that comes out of this office. That's part of what these pages are for - to let you know what we do down here, and to invite feedback. If you're a user of our software, please feel free to comment, complain or whatever. That's the only way we know when something's broken or needs to be improved. Also, if you are a user of the output of any of these systems (and most everyone in the Center is in one way or another), now that you know where the technology comes from, if you have suggestions for improvements or additions that would be helpful, please let us know. I hope you've enjoyed a little history and a brief tour of software development on the third floor.
Return to the top of this page.
Last modified 22 February 2002
Dave Allen