CA Datacom/DB Reporting Facility Tutorial › Writing a Program (Multiple CA Datacom/DB Tables)

Writing a Program (Multiple CA Datacom/DB Tables)

In this section, you have the opportunity to further expand your Reporting Facility programming skills by writing a program that accesses more than one table.

The tables to be accessed are the sample CA Datacom/DB PERSONEL table and PAYROLL table. As with previous sample programs, all the information needed to write the program is provided.

Write the program to generate a report with this format:

                               XYZ COMPANY, INC.

 09   JAN   08                 PAYROLL REPORT                     PAGE  1


EMPLOYEE                                       CURRENT      YEAR-TO-DATE
NUMBER    NAME                      STATUS     PAY RATE             WAGES


XXXXX     XXXXXXXXXXXXXXXXXXXXXX     X         99999.99        $$$,$$9.99
 .        .                          .              .                 .
 .        .                          .              .                 .
 .        .                          .              .                 .
XXXXX     XXXXXXXXXXXXXXXXXXXXXX     X         99999.99        $$$,$$9.99

                                                               __________

GRAND TOTAL                                                  $$$$$,$$9.99

The previous example indicates that the program will generate a report that includes:

• Specified headings
• Employee number
• Employee name
• Employee status (whether the employee is salaried or paid hourly)
• Current pay rate
• Year-to-date wages, with the indicated edit pattern
• Grand total for year-to-date wages

Note: Assume that the status field in the record contains either S (salaried) or H (hourly).

You can make the following assumptions in writing this program:

• The accessed tables are both CA Datacom/DB tables.
• The table name for the PERSONEL table is PMF. It resides in database 001.
• The table name for the PAYROLL table is PAY, and it resides in database 001.
• Total length of the PERSONEL table element, is 75 characters.
• The total length of the PAYROLL table element, is 39 characters.
• The PERSONEL table is searched sequentially, starting with the first record.
• The PAYROLL table is matched one-to-one with a record in the PERSONEL table. (Hint: The employee number is a common key to both tables.)
• All fields defined with the DEFINE statements are alphanumeric, except the current pay rate and year-to-date wages fields, which are numeric. The current pay rate field has three implied decimal places, and the year-to-date wages field has two implied decimal places.
• The field to hold the PERSONEL table CA Datacom/DB command is PERSONEL-COMMAND.
• The field to hold the PERSONEL table CA Datacom/DB key name is PERSONEL-KEY.
• The field to hold the PERSONEL table CA Datacom/DB element is PERSONEL-ELMLIST.
• The employee number field is PERSONEL-EMPLOYEENUM and occupies positions 1—5 of the PERSONEL table element.
• The employee name field is PERSONEL-NAME and occupies positions 6—29 of the PERSONEL table element.
• The field to hold the PAYROLL table CA Datacom/DB command is PAYROLL-COMMAND.
• The field to hold the PAYROLL table CA Datacom/DB key name is PAYROLL-KEY.
• The field to hold the PAYROLL table CA Datacom/DB key value is PAYROLL-VALUE.
• The field to hold the PAYROLL table CA Datacom/DB element is PAYROLL-ELMLIST.
• The employee code field is PAYROLL-CODE in position 6 of the PAYROLL table element. The code field contains an A (active) or an I (inactive).
• The status field is PAYROLL-STATUS and occupies position 7 of the PAYROLL table element.
• The current pay rate field is PAYROLL-CURRENTRATE and occupies positions 8—15 of the PAYROLL table element.
• The year-to-date wages field is PAYROLL-YTDWAGES and occupies positions 16—23 of the PAYROLL table element.
• The key name for the PERSONEL table and PAYROLL table is EMPNO (employee number).
• The element name for the PERSONEL table is IDEMP.
• The element name for the PAYROLL table is PAYRC.
• Code the program so that, if there is no match of employee number in the PERSONEL and PAYROLL tables (return code N for PAYROLL GET statement), neither record is processed, but another PERSONEL record is read.
• Select for processing only those records that have an A (active) in the code field, but do not print it in the report.
• The report is to be sorted in ascending sequence by employee number.

Keep in mind that you would supply the above requirements in a real situation. Study the command formats and note which parameters are commands or reserved words and which parameters are user-supplied.

Note: This should be the general structure of the program:

• USER statement
• First INPUT statement
• DEFINE statements for first table
• Second INPUT statement
• DEFINE statements for second table
• MOVE statements for first table
• GET statement for first table
• GOTO statement for first table
• MOVE statements for second table
• GET statement for second table
• GOTO statement for second table
• REPORT command
• SELECT statements
• CONTROL statement
• PRINT statement
• END statement

After you have written the program, check the following solution to find the correct input statements and their explanation.

Solution

 USER 'XYZ COMPANY, INC.'
 PERSONEL:  INPUT DATACOM  RECORD EQ 375  NAME EQ PMF DBID EQ 001
 DEFINE PERSONEL-COMMAND     001-005 X
 DEFINE PERSONEL-KEY         006-010 X
 DEFINE PERSONEL-ELMLIST     191-201 X
 DEFINE PERSONEL-EMPLOYEENUM 301-305 X 'EMPLOYEE' ' NUMBER '
 DEFINE PERSONEL-NAME        306-329 X 'NAME'
 PAYROLL:  INPUT DATACOM  RECORD EQ 339  NAME EQ PAY DBID EQ 001
 DEFINE PAYROLL-COMMAND      001-005 X
 DEFINE PAYROLL-KEY          006-010 X
 DEFINE PAYROLL-VALUE        011-015 X
 DEFINE PAYROLL-ELMLIST      191-201 X
 DEFINE PAYROLL-CODE             306 X
 DEFINE PAYROLL-STATUS           307 X
 DEFINE PAYROLL-CURRENTRATE  308-315 N3 'CURRENT' ' PAY RATE '
 DEFINE PAYROLL-YTDWAGES     316-323 N2
        'YEAR-TO-DATE' '    WAGES' PIC '$$$,$$9.99'
 MOVE 'GETIT' TO PERSONEL-COMMAND
 MOVE 'EMPNO' TO PERSONEL-KEY
 MOVE 'IDEMP' TO PERSONEL-ELMLIST
 GET PERSONEL
 GOTO EOJ WHEN PERSONEL EQ 'E'
 MOVE 'REDKG' TO PAYROLL-COMMAND
 MOVE 'EMPNO' TO PAYROLL-KEY
 MOVE PERSONEL-EMPLOYEENUM TO PAYROLL-VALUE
 MOVE 'PAYRC' TO PAYROLL-ELMLIST
 GET PAYROLL
 GOTO START WHEN PAYROLL EQ 'N'
 REPORT 'PAYROLL REPORT'
 SELECT PAYROLL-CODE EQ 'A'
 CONTROL PERSONEL-EMPLOYEENUM
 PRINT PERSONEL-EMPLOYEENUM PERSONEL-NAME PAYROLL-STATUS
    PAYROLL-CURRENTRATE (PAYROLL-YTDWAGES)
 END

The following is a line-by-line explanation of the above report.

• In the first statement, the USER command is used to provide the first line of heading on each page of the report.

   USER 'XYZ COMPANY, INC.'
  

• In the second statement, the INPUT command is used to define the first table to the Reporting Facility. Notice the RECORD EQ parameter. Although you only took data from two of the fields (employee number and name) in the element, you must include the total length of all the elements read from this database table, plus 300 characters for the CA Datacom/DB interface.

  PERSONEL:  INPUT DATACOM RECORD EQ 375 NAME EQ PMF DBID EQ 001
  

• The next five statements define these fields: PERSONEL-COMMAND, PERSONEL-KEY, PERSONEL-ELMLIST, PERSONEL-EMPLOYEENUM, PERSONEL-NAME. The PERSONEL-EMPLOYEENUM field is assigned an alternate column heading. These statements make up the Communications Area and the Data Area for the PERSONEL table.

   DEFINE PERSONEL-COMMAND       001-005 X
   DEFINE PERSONEL-KEY           006-010 X
   DEFINE PERSONEL-ELMLIST       191-201 X
   DEFINE PERSONEL-EMPLOYEENUM   301-305 X 'EMPLOYEE' ' NUMBER '
   DEFINE PERSONEL-NAME          306-329 X 'NAME'
  

• In the eighth statement, the INPUT command defines the second table to the Reporting Facility. Notice the RECORD EQ parameter. You took data from only the first 23 bytes, but you must include the total length of all elements read from this database table plus 300 characters for the CA Datacom/DB interface.

   PAYROLL:  INPUT DATACOM RECORD EQ 339 NAME EQ PAY DBID EQ 001
  

• In the next eight statements define these fields: PAYROLL-COMMAND, PAYROLL-KEY, PAYROLL-VALUE, PAYROLL-ELMLIST, PAYROLL-CODE, PAYROLL-STATUS, PAYROLL-CURRENTRATE, PAYROLL-YTDWAGES. The PAYROLL-CURRENTRATE and PAYROLL-YTDWAGES fields are assigned alternate column headings. These statements comprise the Communications Area and Data Area for the PAYROLL table. Notice the way in which the edit pattern is specified for the year-to-date wages field.

   DEFINE PAYROLL-COMMAND       001-005 X
   DEFINE PAYROLL-KEY           006-010 X
   DEFINE PAYROLL-VALUE         011-015 X
   DEFINE PAYROLL-ELMLIST       191-201 X
   DEFINE PAYROLL-CODE              306 X
   DEFINE PAYROLL-STATUS            307 X
   DEFINE PAYROLL-CURRENTRATE   308-315 N3 ' CURRENT ' 'PAY RATE'
   DEFINE PAYROLL-YTDWAGES      316-323 N2
        'YEAR-TO-DATE' '  WAGES' PIC '$$$,$$9.99'
  

Following the DEFINE statements for the second table are the MOVE statements for the first table.

• The first MOVE statement moves the CA Datacom/DB command GETIT to the CA Datacom/DB command field in the Communications Area. GETIT specifies a sequential search of the PERSONEL table, beginning with the first record.

   MOVE 'GETIT' TO PERSONEL-COMMAND
  

• The second MOVE statement moves the CA Datacom/DB key name EMPNO to the CA Datacom/DB key name field in the Communications Area. EMPNO indicates that the table is to be searched by using the employee identification number as the key.

   MOVE 'EMPNO' TO PERSONEL-KEY
  

• The third MOVE statement moves the CA Datacom/DB element IDEMP to the CA Datacom/DB element list field in the Communications Area. Although the literal string IDEMP has only 5 characters, the field to which it is moved is 11 characters long. When alphanumeric literals are moved, the receiving field is padded with blanks if the literal is too short. The trailing blanks satisfy the CA Datacom/DB requirement for the element list terminator.

   MOVE 'IDEMP' TO PERSONEL-ELMLIST
  

• The GET statement after the first MOVE statement tells the Reporting Facility to return a record from the first table (PERSONEL).

   GET PERSONEL
  

• The GOTO statement specifies a branch to the end-of-job procedure when the program determines that there are no more records in the first table (PERSONEL) to process.

   GOTO EOJ PERSONEL EQ 'E'
  

After the GOTO statement for the first table, the MOVE statements for the second table (PAYROLL) are coded.

• The first MOVE statement for the second table moves the CA Datacom/DB command REDKG to the CA Datacom/DB command field in the Communications Area of that table. REDKG specifies a random search of the second table.

   MOVE 'REDKG' TO PAYROLL-COMMAND
  

• The next MOVE statement moves the CA Datacom/DB key name EMPNO to the CA Datacom/DB key name field in the Communications Area of the second table. The key name EMPNO indicates that the second table, like the first table, is to be searched by using the employee identification number as the key.

   MOVE 'EMPNO' TO PAYROLL-KEY
  

• The next MOVE statement moves the employee number that was identified in the PERSONEL-EMPLOYEENUM field in the PERSONEL table to the CA Datacom/DB key value field in the Communications Area of the PAYROLL table. The employee number tells the program where to start accessing data in the second table. Since a random search was specified for the second table with the CA Datacom/DB command REDKG, the Reporting Facility must be told where to start accessing the data associated with this table.

   MOVE PERSONEL-EMPLOYEENUM TO PAYROLL-VALUE
  

• The last MOVE statement moves the CA Datacom/DB element PAYRC to the CA Datacom/DB element list field in the Communications Area of the PAYROLL table.

   MOVE 'PAYRC' TO PAYROLL-ELMLIST
  

• The next statement is the GET statement for the second table. This statement tells the Reporting Facility to return a record from the PAYROLL table.

   GET PAYROLL
  

The next line of coding is the GOTO statement for the second table. Remember that you were instructed to code the program so that if there were no match of employee numbers in the PERSONEL record and the PAYROLL record, neither record would be selected for printing.

• The GOTO statement below tells the Reporting Facility to go to START (first procedural statement) if the GET statement for the PAYROLL table has a return code of 'N'.

   GOTO START PAYROLL EQ 'N'
  

• The REPORT command specifies that the second heading line on each page of the report is to be PAYROLL REPORT.

   REPORT 'PAYROLL REPORT'
  

• The SELECT statement specifies that only those records that have an A (active) in the field named PAYROLL-CODE will be selected for processing.

   SELECT PAYROLL-CODE EQ 'A'
  

• The CONTROL statement specifies that the report is to be sorted in ascending order by employee number. The 'A' parameter was not necessary. The Reporting Facility defaults to an ascending sort if the parameter is not included.

   CONTROL PERSONEL-EMPLOYEENUM
  

• The PRINT statement specifies which fields are to be printed, the order in which they are to be printed, column headings for the PERSONEL-NAME and PAYROLL-CODE fields and a total for the PAYROLL-YTDWAGES field.

   PRINT PERSONEL-EMPLOYEENUM PERSONEL-NAME PAYROLL-STATUS
                PAYROLL-CURRENTRATE (PAYROLL-YTDWAGES)
  

• The END command signals that the last statement has been processed.