1. Guest Spot: Using MS Access during HR implementations
(by Phil Simon, Simon HRIS Consulting; contact Phil at mailto:psimon@simonhris.com)

I recently finished a six-month new HR 8.02 implementation at an organization with about 4,000 employees.  Lawson HR was replacing the old AS400 and Kronos was being implemented simultaneously.  What's more, Lawson Benefits went live on 1/1/03 and PR went live on 4/1/03.  This added an entirely new level of complexity, as Access had to supplant basic Lawson functionality (such as BN145--removing flex credits for employees who have no other time records).

This article highlights how MS Access 2000 was used to provide key audit reports, obviate the need to use vendors for custom reports and benefit election forms, and do a host of other things that were critical to successfully going live.

Comparison queries to map old data to new
This is pretty standard but absolutely critical to compare mainframe data to new Lawson data.  MS Access facilitated keeping track of AS400 data and Lawson data in multiple product lines. 

Comparison queries to spit out differences in data
Once created, basic Access queries identified discrepancies such as:

• Employees who had rate of pay X in the old system and rate of pay Y in the new system
• Employees in one system but not the other
• ACH account and setup discrepancies
• Strange position dates (HR users know full well the issues with PA13.2 and these record.  This issue does not disappear in version 8)
• Employees with 'odd birthdates' (1 year old) or hire dates.
• Employees missing critical values altogether, since HR10 had not been turned on yet to require fields
• Employees missing flex

Access to data during system downtime
During the implementation, the migration to different servers and different system problems left many times in which access to data through normal Lawson channels was not possible.  Regularly importing data into Access allowed for an important backup.  The client had purchased MS Add-ins, which facilitated importing data from Lawson to Access.  ODBC was not enabled and access to tools such as dbdump and rngdbdump was not available.

Macros to automate emailing of audit reports as attached spreadsheets
Through some VBA and macro functionality, Access allowed these audit reports to be generated and automatically emailed to end-users. 

"eBroadcasting"
In addition, I inserted VBA code to 'burst' complicated payroll distribution and overtime reports to 180 people broken down by department.  Each department head now receives an email with an attached Word document containing the pay period, quarterly, and YTD information for only the employees in his/her department.  Lawson's eBroadcasting product was not available and no Lawson report produced the data in the format that the client wanted.

Reports to generate BN election statements
BN245 provides employee benefit choices in a very confusing format.  Again, this does not change in version 8.  To replicate the client's current form (and avoid paying another vendor to do the same), BN245 output was written to CSVs that Access imported through a macro.  The end result provided a much more coherent BN election form (virtually identical to the original, which the client liked very much).

Daily HR Needs
By providing unlimited ability to link tables, complicated HR reports could be easily created and distributed during the implementation.  HIPPA requirements, for example, could be addressed by a report that took a relatively short time to create.  Access' flexibility and power saved an enormous amount of time and HR users' needs could be met.

Combining Lawson standard reports and overcoming limitation of the Excel Add-ins
The client found Lawson's standard reports wanting in many respects.  While the Add-ins are fairly straightforward and powerful, crashes made reporting from them inconsistent.  What's more, Add-ins do not let you link every table.  The client wanted certain reports that combined seemingly disparate tables.  Access does not have the same limitation.

Generating SQL for potential conversion to Crystal
At the end of the project, the client began to consider recreating the reports in Crystal.  Access facilitates this by allowing you to see queries in SQL that can be literally pasted into the Crystal Reports SQL designer.

Pivot table reports for easy auditing
For those of you not familiar with Excel Pivot Tables, you are missing out.  Essentially cross tabs that allow you to slice and dice data, these were critical in identifying odd data: employees who "snuck through" Lawson benefit entry rules, specific payroll issues, and the like.  Access 2000 integrates pivot tables and 2002 takes it a step further.

Creating Lawson Conversion programs and superior reports
Very easily, old data was prepared for the conversion files.  What's more, Access was able to replicate the clients' old reports, obviating the need for expensive programmers. 

Summary and Conclusion
The project faced many challenges.  Without the Access-based application, these challenges would have been greater and more internal and/or external resources would have been needed to go live.  The client intends to use the application, at least in the interim, instead of Crystal for a whole host of reporting needs.  At a bare minimum, it allows for easily auditing data and provides non-HR-end-users with key business data through email bursting. 

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Percentage of IT Applications Budget spent on:

Source: Information Week Research survey of Information Week 500 IT executives
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Daily Tasks

• Check for "Needs Recovery" Jobs in Waiting Queue

$ jobschd -wA

• Scan archived logs for errors:
  • $LAWDIR/system/ladb.log
  • $LAWDIR/system/lajs.log
  • $LAWDIR/system/latm.log
  • $LAWDIR/system/latm.log
  • $CGIDIR/logan.log
  • If using ProcessFlow:
    • $CCSDIR/lawson/processflow ".out" logs
    • $CCSDIR/lawson/processflow/wflog/*.*err
  • If using Business Component Integrator (BCI):
    • $LAWDIR/system/BCI.log
       
• Consider implementing a nightly cron job to archive log files and remove archived logs older than 30 days
   
• Check that Lawson Security is on
  • Automate a script to run and email/page you when security is off, e.g.:

$ lawsec | grep Off > lawsec_grep.log

Weekly Tasks

• Remove User Log Files

$ rm -R %LAUNTDIR%/*/lashell*.log

• Run jobinteg
  • Diagnoses and fixes any bad job records

$ jobinteg -vd

• Run secinteg
  • Diagnoses and fixes any bad security definitions records

$ secinteg -f

• Run integ on GEN product line (particularly after a mass compile)
  • Diagnoses any bad records

$ integ GEN > integ.log

• Run rebuild on any bad files found by integ (needs to be running only with ladb running)

$ stoplaw
$ startladb
$ rebuild -f GEN <filename, e.g. PGMLIB>
$ stoplaw
$ startlaw  

• Run verify{db} (i.e. verifyora8, verifyibm, etc.) against your production product line to verify that all tables and indexes are in place.
   
• Reboot your server

Monthly Tasks

• Check for program compile errors:

$ find $LAWDIR/ -name "*.err"  

• Deleting jobs 30 days or older

$ deljobhst -cjrw [-u<user>] 'curdate' -30  

• Clean up the joblog directory:

$ find $LAWDIR/prod/work/tmp* -mtime +6 -exec rm -R {} \;
$ find $LAWDIR/system/joblog -mtime +6 -exec rm -R {} \;  

• Database "Garbage Collection"
  • Rebuilds the tables and indexes
  • Critical for data stored in GEN:
    • Run after deleting a product line  

$ dbreorg -g <prodline>  

• Review table sizing using the Lawson dbdu utility
  • Make adjustments using dbdef
  • Make your database tablespaces can accommodate changes
  • Run blddbdict and dbreorg to implement the changes
  • Plan for quarterly/annual growth
  • Don't micro-manage your tablespaces
     
• Reboot your server [Unix Servers]

Got any more suggestions? Send them to mailto:letter-comments@lawsonguru.com.

3. Lawson's Quality Crisis?
Does Lawson have a problem with software quality? Of course--it's the nature of the business. Is it a crisis?

We all know that software from every vendor has some problems. You can call them issues, defects, flaws, bugs, or whatever you want, and can define them any way you want. I call them defects, and I really don't care what they are, or how they got there--I just know that they need to be fixed. I will qualify my definition by saying that I distinctly differentiate between software defects and vulnerabilities. Vulnerabilities are a different beast, providing a way for a hacker or other untoward person to gain access to and exploit your software, presumably to inflict damage. My complaint is that, vulnerabilities aside, Lawson still delivers too many software logic defects.

This problem is certainly not specific to ERP. Software defects abound in all types of software. A software bug is presumably to blame for the recent Soyuz landing-300 miles off course! Defects are part of the risk associated with using software in the first place. We all wish it wasn't true, but it's the reality. Nevertheless, it's not something we have to live with, and we can do better. It's pretty easy to figure out where the defects are and fix them. Just ask Microsoft. After building the "error reporting" feature into Windows and Office, Microsoft was able to validate that the 80/20 rule applies: 80 % of bugs are caused by 20% of code. They then concentrated on that 20%, and dramatically improved its stability.

So, how well does Lawson's software meet your expectations for quality? Before you answer, let me tell you about a recent experience?

I've been working with customer over the past month or so to repair the damage caused by a recently-discovered Lawson software defect. This defect, while stunningly simple, required weeks of work to repair. Not only my time and the client's time, but Lawson's time as well. Lawson expended many hours of non-billable consulting, support, and development resources to repair the data integrity caused by this defect.

The culprit? Simply put, a counter that was sized too small, causing a program to drop detail records if an invoice contained more than 999 lines of system-generated detail. Since the invoices printed correctly, it wasn't until an invoice was canceled--months later--that the defect was uncovered, and by that time many other invoices now lacked the correct details. Lawson did eventually write some fix programs and manually create some missing records. Let's look at the underlying reason why this happened.

The sequence counters inside the program didn't match the size as defined in the database tables. This is a simple enough mistake for any programmer, and will hopefully be a valuable learning experience. But for Lawson, I think it's an indication of their lack of quality processes. This code never should have been released to a customer. Do you think Lawson uses peer code reviews? How about volume load tests?

So, once this particular issue was resolved, what does the support person tell my client? "Don't call me back with any more problems until you've made sure you've applied the latest patches". Gosh, I sure hope that was a joke! Even though there is a patch available now, the fact is that this defect existed and was generating incorrect data--for months before the patch was released.

Speaking of patches, have you looked on Lawson's website lately? The arrival rate of new patches is alarming. In the first 4 months of this year, Lawson has released more than 50 patches per month for the 8.0.3 procurement suite alone! So, how can you keep up? One client told me, "I have been live on 8.0.x for 5 weeks now and I have had to put in 2 ESPs, one MSP and at least a dozen CTPs. That is simply criminal."

As part of a broader initiative, Peoplesoft has announced development of a new auto-update facility that--based on a customer's configuration--will download and install patches on demand. If you have used Microsoft Automatic Update feature, the concept is similar: background download and installation of patches and updates.

Could Lawson benefit from this type of a system? Does it solve the intrinsic quality problem? Certainly not. It just makes it easier to apply patches, which then have to be thoroughly tested in your particular environment. A major complaint I hear from clients is that Lawson is bundling too many fixes into a single CTP, which then mandates system-wide regression testing prior to installation in a production environment.

Lawson needs to concentrate on cleaning up and restructuring the code base. To do this will require a cultural shift. For those of you who've spent much time inside Lawson's code, you know that it's often a mess. Comments are rare. In many cases, modularity is an afterthought. Some of the code is downright sloppy. Lawson needs to educate its developers on methodologies and programming as a science rather than as an art. They need to enforce strict coding standards. They need to increase the tester-to-developer ratio.

If Lawson doesn't focus on improving the quality of their product, they will just end up with a code base that cannot be maintained, and a customer base that is unhappy and ready to abandon the product. While Lawson Interactive Support is a great idea, wouldn't the better investment be to improve the quality of the software so that it works better, and thereby reduce the need for support?
 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- QUOTE OF THE ISSUE -

"There's no business like show business, but there are several businesses like accounting."
- David Letterman
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

A 12-Step Program for Aspiring CIOs
Mattress Giant's CIO suggests a path to the big chair.
CIO, April 15, 2003
http://www.cio.com/archive/041503/counsel.html

CFO's Guide to 401(k) Providers
Before you decide on a full-service company, or on a provider of investment-only or recordkeeping-only services, consult this interactive guide.
CFO, April 2003
http://www.cfo.com/charts/1,5520,B|12|486|584,00.html

Five Steps to Driving Costs From Your Supply Chain
Microsoft Executive Circle, Spring 2003
http://www.microsoft.com/misc/external/executivecircle/CDA/ContentPage.aspx?ContentId=492&VersionId=1&ZoneId=207

Mission: Critical
Imagine 30% fewer deaths in intensive-care units and half the medication errors in hospitals. IT is poised to deliver improved patient care.
Information Week, May 19, 2003
http://www.informationweek.com/story/showArticle.jhtml?articleID=10000106

Questions You Need to Be Able to Answer
Cap Gemini Ernst & Young auditors will ask CIOs the following questions in order to gauge their companies' compliance with the Sarbanes-Oxley Act. Would you have the answers?  
CIO, May 15, 2003
http://www.cio.com/archive/051503/rules_sidebar_2.html
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++