Enterprise PeopleTools 8.51 PeopleBook: SQR for PeopleSoft Developers

Using Procedures and Local Variables and Passing Arguments

This chapter discusses how to:

Use procedures.
Use local variables.
Pass arguments.

Using Procedures

The code example in this section shows a procedure that spells out a number. The sample program for printing checks uses this procedure. When printing checks, you normally need to spell out the dollar amount.

In the spell.inc code example, the assumption is that the checks are preprinted and that the program has to print only items such as the date, name, and amount.

SQR procedures that contain variables that are visible throughout the program are called global procedures. These procedures can also directly reference any program variable.

In contrast, procedures that take arguments, such as the spell_number procedure in this section’s check-printing sample program, are local procedures. In SQR for PeopleSoft, any procedure that takes arguments is automatically considered local.

Variables that are introduced in a local procedure are readable only inside the spell.inc procedure. This useful feature avoids name collisions. The spell_number procedure is in an include file because other reports may also want to use it.

Using Local Variables

When you create library procedures that can be used in many programs, make them local. Then, if a program has a variable with the same name as a variable that is used in the procedure, a collision will not occur. SQR treats the two variables as separate.

Declare a procedure as local even if it does not take any arguments. To do this, place the LOCAL keyword following the procedure name in the BEGIN-PROCEDURE command.

To reference a global variable from a local procedure, insert an underscore between the prefix character (#, $, or &) and the variable name. Use the same technique to reference reserved variables , such as #current-line. These variables are always global so that you can reference them from a local procedure.

SQR supports recursive procedure calls, but it maintains only one copy of a local variable. A procedure does not allocate new instances of the local variables on a stack, as C or Pascal would.

Passing Arguments

Procedure arguments are treated as local variables. Arguments can be numeric, date, or text variables or strings. If an argument is preceded with a colon, its value is passed back to the calling procedure.

In the following code example, spell_number takes two arguments. The first argument is the check amount. This argument is a number, and the program passes it to the procedure. The procedure does not need to pass it back.

The second argument is the result that the procedure passes back to the calling program. We precede this variable with a colon, which means that the value of this argument is copied back at the end of the procedure. The colon is used only when the argument is declared in the BEGIN-PROCEDURE command.

Look at the following sample program. It is not a complete program, but it is the spell_number procedure, which is stored in the spell.inc file. The check-printing sample program includes this code by using an #INCLUDE command.

File spell.inc 
begin-procedure spell_number(#num,:$str)
   let $str = ''
   ! break the number to it's 3-digit parts
   let #trillions  = floor(#num / 1000000000000)
   let #billions   = mod(floor(#num / 1000000000),1000)
   let #millions   = mod(floor(#num / 1000000),1000)
   let #thousands  = mod(floor(#num / 1000),1000)
   let #ones       = mod(floor(#num),1000)
   ! spell each 3-digit part
   do spell_3digit(#trillions,'trillion',$str)
   do spell_3digit(#billions,'billion',$str)
   do spell_3digit(#millions,'million',$str)
   do spell_3digit(#thousands,'thousand',$str)
   do spell_3digit(#ones,'',$str)
end-procedure ! spell_number
begin-procedure spell_3digit(#num,$part_name,:$str)
   let #hundreds = floor(#num / 100)
   let #rest     = mod(#num,100)
   if #hundreds
      do spell_digit(#hundreds,$str)
      concat 'hundred ' with $str
   end-if
   if #rest
   do spell_2digit(#rest,$str)
   end-if
   if #hundreds or #rest
      if $part_name != ''
         concat $part_name with $str
          concat ' ' with $str
      end-if
   end-if
end-procedure ! spell_3digit
begin-procedure spell_2digit(#num,:$str)
   let #tens     = floor(#num / 10)
   let #ones     = mod(#num,10)
   if #num < 20 and #num > 9
      evaluate #num
       when = 10
         concat 'ten ' with $str
         break
       when = 11
         concat 'eleven ' with $str
         break
   when = 12
         concat 'twelve ' with $str
         break
      when = 13
         concat 'thirteen ' with $str
         break
       when = 14
         concat 'fourteen ' with $str
         break
     when = 15
         concat 'fifteen ' with $str
         break
       when = 16
         concat 'sixteen ' with $str
         break
       when = 17
         concat 'seventeen ' with $str
         break
       when = 18
         concat 'eighteen ' with $str
         break
       when = 19
         concat 'nineteen ' with $str
         break
      end-evaluate
   else
      evaluate #tens
       when = 2
         concat 'twenty' with $str
         break
       when = 3
         concat 'thirty' with $str
         break
       when = 4
         concat 'forty' with $str
         break
      when = 5
         concat 'fifty' with $str
         break
       when = 6
         concat 'sixty' with $str
          break
      when = 7
         concat 'seventy' with $str
         break
       when = 8
         concat 'eighty' with $str
         break
       when = 9
         concat 'ninety' with $str
         break
       end-evaluate
     if #num > 20
          if #ones
             concat '-' with $str
          else
             concat ' ' with $str
          end-if
       end-if
       if #ones
          do spell_digit(#ones,$str)
       end-if
   end-if
end-procedure ! spell_2digit
begin-procedure spell_digit(#num,:$str)
   evaluate #num
      when = 1
         concat 'one ' with $str
         break
      when = 2
         concat 'two ' with $str
         break
      when = 3
         concat 'three ' with $str
         break
      when = 4
         concat 'four ' with $str
         break
      when = 5
         concat 'five ' with $str
         break
      when = 6
         concat 'six ' with $str
         break
      when = 7
         concat 'seven ' with $str
         break
      when = 8
         concat 'eight ' with $str
         break
      when = 9
         concat 'nine ' with $str
         break
   end-evaluate
end-procedure ! spell_digit

The result argument is reset in the procedure because the program begins with an empty string and keeps concatenating the parts of the number to it. The program supports numbers up to 999 trillion only.

The number is divided into its three-digit parts: trillions, billions, millions, thousands, and ones. Another procedure spells out the three-digit numbers such as one hundred twelve. Note that the word and is inserted only between dollars and cents, but not between three-digit parts. This format is common for check printing in dollars.

Note the use of math functions, such as floor and mod. SQR for PeopleSoft has a large set of functions that can be used in expressions. These functions are listed and described under the LET command.

See LET.

The series of EVALUATE commands in the number-spelling procedures are used to correlate the numbers that are stored in the variables with the strings that are used to spell them out.

This is the sample program that prints the checks:

Program ex17a.sqr
#include 'spell.inc'
begin-setup
  declare-layout default
  end-declare
end-setup
begin-program
  do main
end-program
begin-procedure main
  alter-printer font=5 point-size=15
begin-select
name                               &name
sum(d.price * c.quantity) * 0.10   &refund 
  do print_check(&refund)
from  customers a, orders b,
      ordlines c, products d
  where a.cust_num = b.cust_num
  and   b.order_num = c.order_num
  and   c.product_code = d.product_code
 group by name
 having sum(d.price * c.quantity) * 0.10 >= 0.01
end-select
end-procedure ! main
begin-procedure print_check(#amount)
  print $_current-date (3,45) edit 'DD-Mon-YYYY'
   print &_name (8,12)
  move #amount to $display_amt 9,999,990.99
  ! enclose number with asterisks for security
  let $display_amt = '**' || ltrim($display_amt,' ') || '**'
  print $display_amt (8,58)
  if #amount < 1.00
    let $spelled_amount = 'Zero dollars '
  else
    do spell_number(#amount,$spelled_amount)
    let #len = length($spelled_amount)
    ! Change the first letter to uppercase
    let $spelled_amount = upper(substr($spelled_amount,1,1))
                 || substr($spelled_amount,2,#len - 1)
    concat 'dollars ' with $spelled_amount
  end-if
  let #cents = round(mod(#amount,1) * 100, 0)
  let $cents_amount = 'and ' || edit(#cents,'00') || ' cents'
  concat $cents_amount with $spelled_amount
  print $spelled_amount (12,12)
  print 'Rebate'        (16,12)
  print ' ' (20)
  next-listing need=20
end-procedure ! print_check

The main procedure starts by setting the font to 15-point Times Roman. The select paragraph is a join of several tables. (A join is created when you select data from more than one database table in the same select paragraph.) The customers table has the customer’s name. The program joins it with the orders and ordlines tables to get the customer’s order details. It also joins with the products table for the price.

The following expression adds up all of the customer’s purchases and calculates a 10 percent rebate:

sum(d.price * c.quantity) * 0.10

The statement groups the records by the customer name, one check per customer. This is done with the following clause:

group by name
having sum(d.price * c.quantity) * 0.10 >= 0.01

The having clause eliminates checks for less than 1 cent.

The print_check procedure is a local procedure. Note the way that it references the date and customer name with &_current-date and &_name, respectively.