Example 1—Employee location and status by department

Sample Programs—Database Access › Example 1—Employee location and status by department

Example 1—Employee location and status by department

This program reads the DEPARTMENT, EMPLOYEE, and OFFICE database records. For each department that contains employees, the output lists the ID and name of each employee, the employee's office location, and the status of the employee in terms of disability, leave of absence, or termination.

The program performs the following functions:

Sorts the information within each department by location and within each location by employee last name
Begins printing a new page each time the name of the department changes
Tests the value of PATH-ID for alternate path ID values specified on the PATH parameter
Prints messages depending upon the value of PATH-ID and the employee status field

The first figure shows the CA Culprit code that created the report shown in the second figure.

Parameters for Employee Location and Status by Department:

DATABASE DICTNAME=DOCANWK IN DB SS=EMPSS01,EMPSCHM,100 PATHAA DEPARTMENT EMPLOYEE(BB) OFFICE(CC) 010 MESSAGE ' ' 010 TERM-DATE ' ' 010 EMP-ID 0000 01OUT D 01SORT DEPT-NAME-0410,1 OFFICE-CODE-0450 EMP-LAST-NAME-0415 013EMPLOYEE LOCATION AND STATUS BY DEPARTMENT 0141*010 DEPT-NAME-0410 0151*010 EMP-ID SZ=4 FN HH 'EMPLOYEE ID' 0151*020 EMP-NAME-0415 HH 'EMPLOYEE NAME' 0151*030 OFFICE-CITY-0450 HH 'LOCATION' 0151*040 MESSAGE HH 'STATUS' 0151*050 TERM-DATE 0152*010 EMP-ID SZ=4 FN 0152*020 EMP-NAME-0415 0152*040 MESSAGE 0153*010 MESSAGE 017 PATH-ID NE 'BB' 050 017 MOVE 'NO EMPLOYEES' TO MESSAGE 017 TAKE 3 017050 EMP-ID-0415 NE EMP-ID 100 017 DROP 017100 MOVE EMP-ID-0415 TO EMP-ID 017 PATH-ID NE 'CC' 200 017 MOVE 'NO OFFICE' TO MESSAGE 017 TAKE 2 017200 IF STATUS-0415 NE '05' 300 017 MOVE 'TERMINATED' TO MESSAGE 017 MOVE TERMINATION-DATE-0415 TO TERM-DATE 017 B 700 017300 IF STATUS-0415 NE '04' 400 017 MOVE 'LEAVE-OF-ABSENCE' TO MESSAGE 017 B 625 017400 IF STATUS-0415 NE '03' 500 017 MOVE 'LONG-TERM-DISABILITY' TO MESSAGE 017 B 625 017500 IF STATUS-0415 NE '02' 600 017 MOVE 'SHORT-TERM-DISABILITY' TO MESSAGE 017 B 625 017600 MOVE ' ' TO MESSAGE 017625 MOVE ' ' TO TERM-DATE 017700 TAKE 1

Report for Employee Location and Status by Department:

REPORT NO. 01 ACCOUNTING AND PAYROLL EMPLOYEE ID 2002 0069 0100 0011 0067 REPORT NO. 01 BRAINSTORMING EMPLOYEE ID 0334 0301 0015 0466 0467 0457 0341 0458 REPORT NO. 01 CLIMBING EMPLOYEE ID NO EMPLOYEES REPORT NO. 01 PUBLIC RELATIONS EMPLOYEE ID 0476 0120 0007 0158 0127 0149 EMPLOYEE LOCATION AND STATUS BY DEPARTMENT mm/dd/yy PAGE 1 EMPLOYEE NAME LOCATION STATUS ROCCO COLOMBO SPRINGFIELD JUNE BLOOMER BOSTON EDWARD HUTTON BOSTON RUPERT JENSON BOSTON MARIANNE KIMBALL BOSTON EMPLOYEE LOCATION AND STATUS BY DEPARTMENT mm/dd/yy PAGE 1 EMPLOYEE NAME LOCATION STATUS CAROLYN CROW GLASSTER BURT LANCHESTER GLASSTER RENE MAKER GLASSTER BAY ANDOVER WESTON LONG-TERM-DISABILITY S.E.A. BREEZE WESTON HARRY HARM WESTON TERMINATED 091883 RICHARD MUNYON WESTON RICHARD WAGNER WESTON EMPLOYEE LOCATION AND STATUS BY DEPARTMENT mm/dd/yy PAGE 6 EMPLOYEE NAME LOCATION STATUS EMPLOYEE LOCATION AND STATUS BY DEPARTMENT mm/dd/yy PAGE 19 EMPLOYEE NAME LOCATION STATUS BETSY ZEDI SPRINGFIELD TERMINATED 072284 MICHAEL ANGELO BOSTON DONTE BANK BOSTON JOCK JACKSON BOSTON CAROL MCDOUGALL BOSTON LAURA PENMAN BOSTON

Note: Information for each department prints on a separate page.

Parameters coded for this report are described below:

DATABASE and INPUT Parameters

The DATABASE and INPUT parameters define the input for a database access run. The INPUT parameter specifies DB, which indicates an IDMS/R database by default. The SS= keyword expression identifies the subschema, or view, of the database to be accessed. The dictionary named on the DATABASE parameter, DOCANWK, contains the schema and subschema definition. In a CV environment, DOCANWK must also contain the EMPSS01 load module.

PATH Parameter

The PATH parameter specifies three records:

The DEPARTMENT record is the entry record for the run. CA Culprit retrieves each DEPARTMENT record occurrence as it is encountered in the database.
The EMPLOYEE record is the member record of the DEPT-EMPLOYEE set relationship. Path ID BB indicates that no EMPLOYEE record occurrences participate in the set relationship with a particular DEPARTMENT record occurrence.
The OFFICE record is the owner record of the OFFICE-EMPLOYEE set relationship. Path ID CC indicates that no OFFICE record occurrences participate in the set relationship with a particular EMPLOYEE record occurrence.

Work Field Parameters

Report 01 specifies two alphanumeric work fields and one numeric work field:

MESSAGE is an alphanumeric work field initialized as a 20-blank literal. MESSAGE serves as the result field for MOVE operations in the procedure logic for Report 01 and as an output field on a type 5 edit parameter.
TERM-DATE is an alphanumeric work field that has an initial value of six blank spaces. TERM-DATE receives the value of an employee's termination date if the status code for the employee is 05; otherwise, TERM-DATE is reinitialized to blanks.
EMP-ID is a numeric work field initialized to zero. CA Culprit compares the value in this field to the value in the employee ID field in the EMPLOYEE record for the purpose of removing duplicate employee entries.

Output Definition Parameters

The OUTPUT parameter for Report 01 defines a printed report that contains details-only information and 132 characters per line. The SORT parameter indicates that the information for the report is sorted by department name. The sort break associated with the department name instructs CA Culprit to begin printing a new page each time the name changes. Within each department, the information is sorted by the last name of each employee within the same office.

Since DEPT-NAME-0410 appears on the SORT parameter, it can be specified on a type 4 edit parameter. CA Culprit prints the department name below the report title at the top of each page. CA Culprit then prints the automatic headers defined on the type 5 edit parameters for the report.

The type 5 edit parameters define three detail lines:

Detail line 1 contains the employee id, employee name, office location, and a message, depending on whether the employee is actively employed. If the employee has left the company, the detail line also contains the date the employee stopped working.
Detail line 2 contains the employee id, employee name, and a message; the line is printed if an employee does not have an office.
Detail line 3 specifies only the MESSAGE field; the line is printed only if a department has no employees.

Process Parameters

The procedure logic for Report 01 contains a number of comparisons to determine the actual contents of the input buffer:

The first comparison determines whether the value of PATH-ID is BB. If the path ID is BB, CA Culprit was not able to retrieve any EMPLOYEE record occurrences for the current DEPARTMENT record. The procedure logic instructs CA Culprit to move 'NO EMPLOYEES' to MESSAGE and print detail line 3.
The second comparison beginning at sequence number 050 determines whether the employee ID field contains a duplicate value; if it does, CA Culprit stops processing the input buffer.
The third comparison determines whether the value of PATH-ID is CC. If the path ID is CC, CA Culprit moves a literal to MESSAGE and prints detail line 2. The Run Time Messages listing for this report indicates that CA Culprit did not return any strings for path CC, as shown below:
```
STRINGS RETURNED FOR PATH AA-     81
STRINGS RETURNED FOR PATH BB-     11
STRINGS RETURNED FOR PATH CC-      0
```
The procedure logic that begins with sequence number 200 pertains to a complete input buffer; that is, when the value of PATH-ID is AA. Depending upon the value of the STATUS-0415 field, CA Culprit moves various literals to MESSAGE and prints detail line 1.