The Rewrite And The Bug Fix

Last week I worked on two SQR program bugs.  I rewrote the first one from scratch.  I wanted to rewrite the second one but didn’t.  Here is why.

The Rewrite

My organization has an in-house timecard system.  After the employees complete their timecards, their supervisors review and approve them.  Next, the department timekeepers review and approve them.  We assign employees to approval groups.  Each approval group has a list of employees, a list of supervisors, and a list of timekeepers.  (Multiple supervisors and timekeepers allow the process to move forward even when an employee’s actual supervisor or timekeeper is absent; the listed alternates can substitute.)

We have an SQR that generates a report of approval groups.  It produces four pairs of columns.  The first pair is the group ID and description.  The second pair is the supervisor EMPLID and NAME.  The third pair is the timekeeper EMPLID and NAME.  The fourth pair is the employee (time reporter) EMPLID and NAME.

The program selects the approval group definition table.  For each group, it prints the group id and description, then the columns of supervisors, then the columns of timekeepers, then the columns of employees.  It uses the relative positioning of the print command to move down the page line by line for each pair of columns, then back up the page to start the next pair of columns.  That seems clever, using random access to the page output buffer to simplify the process of printing independent columns side by side.

It does seem clever until a column extends past the end of the page, onto the next page.  The relative positioning feature won’t take us back to previous pages.  It tries to print on a negative line number on the current page, and the program stops.

We could try to preserve this algorithm.  I thought about testing the #current-line, an SQR reserved variable, before each print command.  If we were on the last line of the page, we could jump to the routines that printed the other columns and print the part of the other columns that fit on the current page.  Then, we could continue to the next page and print the remainder of the columns on that page, assuming that we start to keep track of what we’ve already printed.  We also have to prefer for the unlikely but possible case that a single approval group could take three pages to list.

It’s wrong to assume that just fixing a bug and getting a program back into production is the best or even the fastest response to a problem.  That “quick” fix required us to create an engine that would select data from four different tables and place it in four different column pairs in any order, tracking what data had been output and what positions on the page had been used.  It was faster and more reliable to start fresh with a new algorithm.

First, read the four tables into a load-lookup (group definition) and three arrays (employees, supervisors, and timekeepers), all sorted the same way.  Second, use a single loop with three array indices (#employee_num, #supervisor_num, #timekeeper_num) to step through the three arrays.  Here’s the routine.

begin-procedure print_report
  let $group = reporter.group(0)
  move '' to $prev_group
  move 0 to #reporter_num
  move 0 to #supervisor_num
  move 0 to #timekeeper_num
  while $group <> ''
    get $rgroup $emplid $name from reporter(#reporter_num)
    get $sgroup $supervisor_id $supervisor_name from supervisor(#supervisor_num)
    get $tgroup $timekeeper_id $timekeeper_name from timekeeper(#timekeeper_num)

    evaluate $group
      when = $rgroup
        add 1 to #reporter_num
      when <> $rgroup
        move ' ' to $emplid
        move ' ' to $name
      when = $sgroup
        add 1 to #supervisor_num
      when <> $sgroup
        move ' ' to $supervisor_id
        move ' ' to $supervisor_name
      when = $tgroup
        add 1 to #timekeeper_num
      when <> $tgroup
        move ' ' to $timekeeper_id
        move ' ' to $timekeeper_name
    end-evaluate

    let #prev_page = #page-count
    print ' ' (+1,1)
    if $group = $rgroup or $group = $sgroup or $group = $tgroup
      if $group <> $prev_group or #page-count <> #prev_page
        lookup group $group $group_name
        print $group ( , {group_col})
        if #page-count <> #prev_page
          print ' cont.' ()
        end-if
        print $group_name ( , {groupname_col})
      end-if
      print $supervisor_id ( , {supervisor_col})
      print $supervisor_name ( , {supername_col})
      print $timekeeper_id ( , {timekeeper_col})
      print $timekeeper_name ( , {tkname_col})
      print $emplid ( , {emplid_col})
      print $name ( , {name_col})
      move $group to $prev_group
    else
      let $group = cond($rgroup < $sgroup, $rgroup, $sgroup)
      let $group = cond($group < $tgroup, $group, $tgroup)
    end-if
  end-while
end-procedure print_report

The evaluate command keeps us on a single $group until all three arrays finish with it.  SQR reserved variable #page-count tells us when we start a new page, so we can print the $group identification with every new group and with every new page.  There shouldn’t be any groups that appear in one array but not the other, but the pair of cond functions at the bottom of the loop act like a minimum function to let us pick the next group, even if it doesn’t appear in all three arrays.

Note that the print columns are not numbers, nor are they unhelpful substitution variables like {col1}.
 

The Bug Fix

My organization populates paychecks (specifically PS_PAY_OTH_EARNS) from two sources: timecards (specifically PS_TL_PAYABLE_TIME) and recurring premium pay (specifically PS_ADDL_PAY_DATA).  The paychecks (specifically PS_PAY_EARNINGS) have a field OK_TO_PAY.  It must be set to “Y” in order to enable premium pay.  According to our business rules, there must be some base time (the basic 40 hours per week pay) in the paycheck to allow payment of premium pay.

Years ago, I inherited a program that evaluated paychecks and set the OK_TO_PAY flag.  I moved some of its function into Time and Labor rules, replaced most of the hardcoded values with references to database tables, and cleaned up the source code.  Everything was fine until Payroll introduced new disability earning codes.  Some employees had nothing but those disability codes on their timecards and the program ignored them on their paychecks, so their premium pay OK_TO_PAY flag was set to “N”.

I reviewed the program and saw that the last hardcoded list of pay and disability codes should be changed to a join with a value list (on PS_TL_VAL_LIST_DTL)..  I also realized that the program was dreadfully inefficient.

First, it joins PS_PAY_LINE and PS_PAY_EARNINGS to identify the paychecks to process.  For each paycheck, it joins PS_PAY_EARNINGS and PS_PAY_OTH_EARNS to see whether the timecard hours make the paycheck eligible for premium pay.  Then it reads PS_PAY_OTH_EARNS again to see whether the paycheck has premium pay.  Finally, it updates PS_PAY_EARNINGS, setting OK_TO_PAY to “Y” or “N.”

It would be better to collect base time and premium pay into an array with one pass through PS_PAY_OTH_EARNS, then use those results to update PS_PAY_EARNINGS.  I wanted to rewrite the program, but I checked the running time in the Peoplesoft Process Monitor.  It takes 45 seconds each pay period.  I could take a few hours to rewrite it, and make it run in 15 seconds.  Assuming my time is worth the no more than CPU time, the payback time is about 14 years.  I let it go.

2 Comments

  1. Lulu Catarroja says:

    Hi! I wanted to ask if there is a syntax/command to go back to the previous page. I have a similar situation in which I have 2 arrays.. However, if the first array printed on one column has moved to the next page, the second array (2nd column) continues to print on where the cursor stopped (which is the 2nd page). I would just need to control the page to go back the previous. I have tried doing let #prev-page = #page-count – 1 and it doesn’t seem to work. Please advise. Thank you. Lulu Catarroja 215/784-1448

    • administrator says:

      The short answer is no. That’s why I had to rewrite the program using a more complex algorithm.

      The long answer is, the SQR Language Reference hints that Oracle could do it, but it would be difficult for them to implement and difficult for us to use. Since Oracle hasn’t made even the easy improvements to SQR, I don’t think we’ll ever get this feature.

      SQR has a feature called “last-page” which allows us to enhance our page numbering with phrases like “Page 1 of 10″. When we use “last-page”, SQR keeps all our printed output in a buffer until the program has ended. It counts the pages (this is independent of the #page-count variable, so don’t bother to manipulate it) and annotates each page in the buffer with the final page count.

      Wow! Think of what we could do with access to that buffer. Now think about never being able to do it. Sorry.