Converting to Outside Payroll

Got a call from a programmer who was helping a company convert a 5-store auto dealership company from Reynolds and Reynolds in-house payroll to an outside payroll service. The payroll company I had never heard of but they were very astute and specialized from what I could tell.

The impetus for the change seemed to be a time and attendance software vendor who appeared to use this payroll service company to to produce the payroll using data from his system. It could have been the other way around, the payroll company offered the Time and Attendance software as an important part of their service. I did not get a demo of the Time and Attendance system but the installer and I shared a remote PC connection and it seemed very comprehensive. Time and Attendance systems, if properly setup and managed (the critical part), saves dealerships money; serious money.

So the programmer who contacted me needed help extracting check history and YTD balance data from the Reynolds system. The programmer, Jim Barbour of Sequencing, Inc. was very professional and managed the project very well. Jim needed me to pull all check history as well as pay, tax and deduction year to date totals for the employees. I gave him a quote that made sense and I went to work. I was successful pulling what I thought was all the data and I forwarded a file for his review. Upon verification by the client it was determined that we did not have it all. In particular there were a few deduction items on a several employees that were unaccounted for. Having experience with Reynolds file structures, I planned for this possibility and I had built time in for the possibility that we might need to do extra digging for the odd item. I found the offending miscellaneous deduction codes and related year-to-date totals buried in a multi-valued field called “I/D”. I extracted that data which was tricky because I wanted my condition statement to filter for only those values I was looking for in the multi-valued field. It didn’t work that way. Depending on the field attributes one may have to grab it all and filter programmatically. All was well although the flat file output was rather ugly. Jim being a good sport was able to select for and import the few codes into his conversion.

The interesting thing about this conversion was that the client needed an export for tech hours flagged to go each payday to the service bureau (old term I know, I did payroll in the early 80’s on IBM 360’s for a service bureau subsequently purchased by ADP called Stat-Tab……I was the payroll manager!)  I wrote the export query and it worked out well however the concern on the client’s side was how to make sure the technician’s flags (time entries) are only paid once in cases where the flags are adjusted or if the RO is re-opened and closed again (as in possibly a warranty ticket). The answer was two-fold; one was to isolate flags by the Work Date field (in the query) rather than the RO date or any other date field. Work Date is the date the time entry is actually written, not the date the work is done or the RO was finished or closed. If we stick with the Work Date condition, during the pay cycle only those flags or adjustments done during the cycle get paid regardless of what goes on with the RO. In addition, the client had Body Shops and they wanted to pay flags (time) on ROs as the job was progressing regardless of whether or not the RO was closed in the pay cycle. Using Work Date allowed them to pay on work in progress as well.

The 2nd step in the process from the dealership’s side (I am an accountant, CPA actually but not current on my license) was to schedule the Work In Process – Labor (WIP) general ledger asset account (usually 247) by RO number. Many dealerships do this to control flagging irregularities and I believe the client had these schedules already in place. However, they became extremely important under the new outside payroll processing system. On the credit side of the of the WIP account will be the ROs total of cost of labor, i.e., all tech flags in total costed out on the RO when it was closed. On the debit side will be all the individual flags paid against that RO’s total costed labor. The RO control subtotal should always zero after closing the RO and the subsequent payroll is applied. The office, payroll clerk or office manager, must monitor the schedule often at the start of outside processing and every pay cycle thereafter for problems or irregularities. Techs are smart and some are inclined to “game” the system. Researching ROs with balances and resolving with either a back flag (a charge-back) or a correction to the RO cost and/or costing procedure is imperative so it doesn’t happen again.

All in all a workable solution for those who want to take time and attendance and payroll to the next step in these automobile dealership systems.

3rd Party Data Extraction with Reynolds and Reynolds and ERAccess

I had a call from a 3rd Party vendor to the automobile dealership industry who was pulling data from their client’s Reynolds DMS’ and all of a sudden couldn’t do it anymore. Not wanting to become colossal pains to their clients they asked me to help. These are nice folks not pulling a high volume of data but important data none-the-less. Definitely not candidates for RCI. I had to do a little research to get myself up to speed as I hadn’t done much R&R work lately. See my last post about a previous conversation with the RCI team.

Installing the Reynolds Software Manager and ERAccess and is the key. To do that someone needs to help you. Otherwise you will probably be remoting into an ERAccess PC on the customer’s network and working there. Once we have access unfortunately we have to use the R&R proprietary Query (7601) or Report Generator programs to pull the data just like before. I’ve yet to find a 3rd Party extraction tool for UniVerse although it probably wouldn’t be hard to wire up PowerShell using the UniOLEDB Data Provider. The problem is someone with access to the Unix root would need to get to the UniVerse Server Admin and setup an account to show files. That’s not going to happen unless someone with an R&R system wants to experiment with me.

Since ADP and R&R use the same database (Currently Rocket UniVerse, formerly IBM UniVerse, originally PICK) their “Certified” interface would be a .Net data provider (hopefully) or XML service. I have a data provider for ADP but it crashes on Win7 systems. At any rate, we were able to solve the vendor’s problems going forward and get them back in the game. The goal now is to streamline this process with Rocket Software’s assistance and in this regard I will keep you posted.

Extracting Data from Car Dealership Computer Systems

Recently I received an inquiry as to my availability to consult for a company that needs to extract data from Automobile Dealership Computer Systems. They build websites for dealers and wanted to extract vehicle inventory data. Here is my response;

December 6, 2010

Dear Mr. Jones,

I enjoyed talking with you last week about your data extraction needs. I have been involved in data extraction while creating Business Intelligence solutions for auto dealerships for the past ten years. I currently am contracted with ADP to prepare extractions for their ADP ARG (AS400) clientele who are (were) mainly Saturn customers. I also have worked with a large dealership group here in Nevada building business intelligence Sharepoint solutions which are heavily dependent on data extraction. The following is a summary of my skills as they relate to this project:

Data Extraction Platform Experience:

  • ADP – UNIX, PICK – Both Telnet and DAP (Data Access Program)
  • ADP ARG – IBM AS400, DB2 – (System modified by EDS for Saturn Dealers)
  • Reynolds & Reynolds – Query Builder – Telnet
  • Arkona – IBM AS400, DB2
  • InfinitiNet – IBM AS400, DB2
  • With the advent of Microsoft’s Windows Communication Foundation and ADO.Net there’s really very few systems that can’t be mapped given access.

Data Extraction Methods and Languages Experience:

  • ADO.Net
  • Microsoft SQL Server 2008
  • PowerShell
  • XML
  • AS400 (DB2) SQL

Data Presentation Experience:

  • Sharepoint
  • Excel Services
  • ASP.Net
  • Silverlight

Based on our conversation last week I put together the following proposal.

That’s it. I include it here in case any one might be looking for similar abilities.

Happy New Year!

Follow

Get every new post delivered to your Inbox.

Join 28 other followers