Skip to content

Instantly share code, notes, and snippets.

@sandeep-sparrow

sandeep-sparrow/DB2PGM8.cbl

Last active March 5, 2023 03:51
Show Gist options
  • Save sandeep-sparrow/8b19bb91a92170d2f5235aff9ca1bb5f to your computer and use it in GitHub Desktop.
Save sandeep-sparrow/8b19bb91a92170d2f5235aff9ca1bb5f to your computer and use it in GitHub Desktop.
Download ZIP
COBOL program to Generate CUSTOMER-INVOICE Report using DB2 and JCL - Thanks to Murach's & Captain Uday Prasad
Raw
DB2PGM8.cbl
******************************************************************
* Author: SANDEEP PRAJAPATI *
* Date: 03-07-2020 *
* Purpose: REPORT GENERATION USING COBOL PROGRAM. *
* : THIS PROGRAM READS A CUSTOMER TABLE *
* : WHICH WRITES A REPORT FORMAT *
******************************************************************
IDENTIFICATION DIVISION.
*
PROGRAM-ID. DB2PGM8.
*
ENVIRONMENT DIVISION.
*
INPUT-OUTPUT SECTION.
*
FILE-CONTROL.
*
SELECT SALESRPT ASSIGN TO SP-DISK2
FILE STATUS IS WRITE-WS.
*
DATA DIVISION.
*
FILE SECTION.
FD SALESRPT RECORDING MODE F
RECORD CONTAINS 132 CHARACTERS.
*
01 PRINT-AREA PIC X(132).
*
WORKING-STORAGE SECTION.
*
*DCLGEN
*
EXEC SQL INCLUDE CUSTOMER END-EXEC.
EXEC SQL INCLUDE INVOICE END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
*
*CURSOR
*
EXEC SQL
DECLARE CUS_INV CURSOR FOR
SELECT CUSTNO, FNAME, LNAME, INVNO, INVSUBT, INVTAX,
INVSHIP, INVTOTAL
FROM CUST
INNER JOIN INV
ON CUSTNO = INVCUST
ORDER BY CUSTNO
END-EXEC.
*
01 FILE-STATUS-WS.
05 WRITE-WS PIC X(02) VALUE SPACES.
01 SWITCHES.
05 CUSTINV-EOF-SWITCH PIC X(01) VALUE 'N'.
88 CUSTINV-EOF VALUE 'Y'.
05 OLD-CUSTNO-SWITCH PIC X(01) VALUE 'N'.
88 OLD-CUSTNO VALUE 'Y'.
*
01 PRINT-FIELDS.
05 PAGE-COUNT PIC S9(03) VALUE ZERO.
05 LINES-ON-PAGE PIC S9(03) VALUE +55.
05 LINE-COUNT PIC S9(03) VALUE +99.
*
01 GTL-TOTAL-FIELDS.
05 GRAND-TOTAL-INVSUBT-YTD PIC S9(07)V99 VALUE ZERO.
05 GRAND-TOTAL-INVTAX-YTD PIC S9(07)V99 VALUE ZERO.
05 GRAND-TOTAL-INVSHIP-YTD PIC S9(07)V99 VALUE ZERO.
05 GRAND-TOTAL-INVTOTAL-YTD
PIC S9(07)V99 VALUE ZERO.
*
01 CTL-TOTAL-FIELDS.
05 CUST-TOTAL-INVSUBT-YTD PIC S9(07)V99 VALUE ZERO.
05 CUST-TOTAL-INVTAX-YTD PIC S9(07)V99 VALUE ZERO.
05 CUST-TOTAL-INVSHIP-YTD PIC S9(07)V99 VALUE ZERO.
05 CUST-TOTAL-INVTOTAL-YTD PIC S9(07)V99 VALUE ZERO.
*
01 CUSTNO-OLD PIC X(06) VALUE SPACES.
01 HIGHFEN PIC X(132) VALUE ALL '-'.
01 NO-OF-GET PIC 9(03) VALUE ZERO.
01 INV-COUNT PIC 9(03) VALUE ZERO.
*
01 CURRENT-DATE-AND-TIME.
05 CD-YEAR PIC 9999.
05 CD-MONTH PIC 99.
05 CD-DAY PIC 99.
05 CD-HOURS PIC 99.
05 CD-MINUTES PIC 99.
05 FILLER PIC X(09).
*
* --------------- LINES FORMAT END EDITED CLAUSE ----------------*
*
01 HEADING-LINE-01.
05 FILLER PIC X(07) VALUE 'DATE: '.
05 HL1-DAY PIC 9(02).
05 FILLER PIC X(01) VALUE '/'.
05 HL1-MONTH PIC 9(02).
05 FILLER PIC X(01) VALUE '/'.
05 HL1-YEAR PIC 9(04).
05 FILLER PIC X(11) VALUE SPACES.
05 FILLER PIC X(32)
VALUE 'YEAR-TO-DATE SALES REPORT '.
05 FILLER PIC X(10) VALUE SPACES.
05 FILLER PIC X(06) VALUE 'PAGE: '.
05 HL1-PAGE PIC 9(04).
05 FILLER PIC X(52) VALUE SPACES.
*
01 HEADING-LINE-02.
05 FILLER PIC X(07) VALUE 'TIME: '.
05 HL2-HOURS PIC 9(02).
05 FILLER PIC X(01) VALUE ':'.
05 HL2-MINUTES PIC 9(02).
05 FILLER PIC X(58) VALUE SPACES.
05 FILLER PIC X(10) VALUE 'DB2PGM8 '.
05 FILLER PIC X(52) VALUE SPACES.
*
01 HEADING-LINE-03.
05 FILLER PIC X(02).
05 FILLER PIC X(06)
VALUE 'CUSTNO'.
05 FILLER PIC X(02).
05 FILLER PIC X(20)
VALUE 'FNAME '.
05 FILLER PIC X(02).
05 FILLER PIC X(30)
VALUE 'LNAME '.
05 FILLER PIC X(02).
05 FILLER PIC X(06)
VALUE 'INVNO '.
05 FILLER PIC X(02).
05 FILLER PIC X(10)
VALUE 'INVSUBT '.
05 FILLER PIC X(02).
05 FILLER PIC X(10)
VALUE 'INVTAX '.
05 FILLER PIC X(02).
05 FILLER PIC X(10)
VALUE 'INVSHIP '.
05 FILLER PIC X(02).
05 FILLER PIC X(10)
VALUE 'INVTOTAL '.
05 FILLER PIC X(14).
*
01 CUSTOMER-INVOICE-LINE.
05 FILLER PIC X(02) VALUE SPACES.
05 CIL-CUSTNO PIC X(06).
05 FILLER PIC X(02) VALUE SPACES.
05 CIL-FNAME PIC X(20).
05 FILLER PIC X(02) VALUE SPACES.
05 CIL-LNAME PIC X(30).
05 FILLER PIC X(02) VALUE SPACES.
05 CIL-INVNO PIC X(06).
05 FILLER PIC X(02) VALUE SPACES.
05 CIL-INVSUBT PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 CIL-INVTAX PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 CIL-INVSHIP PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 CIL-INVTOTAL PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 FILLER PIC X(06).
*
01 CUST-LINE.
05 FILLER PIC X(57) VALUE SPACES.
05 FILLER PIC X(15)
VALUE 'CUSTOMER TOTAL:'.
05 CTL-INVSUBT PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 CTL-INVTAX PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 CTL-INVSHIP PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 CTL-INVTOTAL PIC ZZ,ZZ9.99-.
05 FILLER PIC X(14).
*
01 GRAND-TOTAL-LINE.
05 FILLER PIC X(02).
05 FILLER PIC X(15)
VALUE 'INVOICE ISSUED:'.
05 GTL-INVOICE-COUNT PIC ZZ9.
05 FILLER PIC X(37) VALUE SPACES.
05 FILLER PIC X(15)
VALUE ' GRAND TOTAL :'.
05 GTL-INVSUBT PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 GTL-INVSHIP PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 GTL-INVTAX PIC ZZ,ZZ9.99-.
05 FILLER PIC X(02) VALUE SPACES.
05 GTL-INVTOTAL PIC ZZ,ZZ9.99-.
05 FILLER PIC X(14).
*
PROCEDURE DIVISION.
*
000-PREPARE-CUST-INV-REPORT.
*
OPEN OUTPUT SALESRPT
*
PERFORM 100-FORMAT-REPORT-HEADING
PERFORM 200-INITIALIZE-SALES-REPORT
PERFORM 300-PREPARE-SALES-LINES
UNTIL CUSTINV-EOF-SWITCH = "Y"
PERFORM 301-CLOSE-CURSOR
PERFORM 400-PRINT-GRAND-TOTALS
*
CLOSE SALESRPT
*
STOP RUN.
*
100-FORMAT-REPORT-HEADING.
*
MOVE FUNCTION CURRENT-DATE TO CURRENT-DATE-AND-TIME
MOVE CD-DAY TO HL1-DAY
MOVE CD-MONTH TO HL1-MONTH
MOVE CD-YEAR TO HL1-YEAR
MOVE CD-HOURS TO HL2-HOURS
MOVE CD-MINUTES TO HL2-MINUTES.
*
200-INITIALIZE-SALES-REPORT.
*
INITIALIZE CUST-ROW, INV-ROW
*
PERFORM 201-OPEN-CURSOR
PERFORM 210-GET-CUS-INV-RECORD
IF CUSTINV-EOF-SWITCH = "N"
PERFORM 220-PRINT-CUST-INV-LINE
MOVE CUSTNO TO CUSTNO-OLD.
*
300-PREPARE-SALES-LINES.
*
PERFORM 210-GET-CUS-INV-RECORD
IF CUSTINV-EOF-SWITCH = "N"
IF CUSTNO = CUSTNO-OLD
MOVE 'Y' TO OLD-CUSTNO-SWITCH
PERFORM 220-PRINT-CUST-INV-LINE
ELSE
MOVE 'N' TO OLD-CUSTNO-SWITCH
MOVE CUSTNO TO CUSTNO-OLD
PERFORM 240-PRINT-CUST-LINE
PERFORM 220-PRINT-CUST-INV-LINE
END-IF
ELSE
IF NO-OF-GET = 0
DISPLAY 'NO RECORDS WERE FOUND IN TABLE'
ELSE
PERFORM 240-PRINT-CUST-LINE
END-IF
END-IF.
*
201-OPEN-CURSOR.
*
EXEC SQL
OPEN CUS_INV
END-EXEC.
*
210-GET-CUS-INV-RECORD.
*
MOVE 'N' TO CUSTINV-EOF-SWITCH
*
EXEC SQL
FETCH CUS_INV
INTO :CUSTNO, :FNAME, :LNAME, :INVNO, :INVSUBT,
:INVTAX, :INVSHIP, :INVTOTAL
END-EXEC
*
IF SQLCODE = 0
ADD 1 TO NO-OF-GET
END-IF
*
IF SQLCODE = 100
MOVE 'Y' TO CUSTINV-EOF-SWITCH
END-IF.
*
220-PRINT-CUST-INV-LINE.
*
IF LINE-COUNT >= LINES-ON-PAGE
PERFORM 230-PRINT-HEADING-LINE
END-IF.
*
IF OLD-CUSTNO
MOVE SPACES TO CUSTNO
MOVE SPACES TO FNAME
MOVE SPACES TO LNAME
ELSE
MOVE CUSTNO TO CIL-CUSTNO
END-IF
*
MOVE CUSTNO TO CIL-CUSTNO
MOVE FNAME TO CIL-FNAME
MOVE LNAME TO CIL-LNAME
MOVE INVNO TO CIL-INVNO
MOVE INVSUBT TO CIL-INVSUBT
MOVE INVTAX TO CIL-INVTAX
MOVE INVSHIP TO CIL-INVSHIP
MOVE INVTOTAL TO CIL-INVTOTAL
MOVE CUSTOMER-INVOICE-LINE TO PRINT-AREA
WRITE PRINT-AREA
*
ADD 1 TO LINE-COUNT
ADD 1 TO INV-COUNT
ADD INVSUBT TO GRAND-TOTAL-INVSUBT-YTD
CUST-TOTAL-INVSUBT-YTD
ADD INVTAX TO GRAND-TOTAL-INVTAX-YTD
CUST-TOTAL-INVTAX-YTD
ADD INVSHIP TO GRAND-TOTAL-INVSHIP-YTD
CUST-TOTAL-INVSHIP-YTD
ADD INVTOTAL TO GRAND-TOTAL-INVTOTAL-YTD
CUST-TOTAL-INVTOTAL-YTD.
*
230-PRINT-HEADING-LINE.
*
ADD 1 TO PAGE-COUNT
*
MOVE HIGHFEN TO PRINT-AREA
WRITE PRINT-AREA
*
MOVE PAGE-COUNT TO HL1-PAGE
MOVE HEADING-LINE-01 TO PRINT-AREA
WRITE PRINT-AREA
*
MOVE HEADING-LINE-02 TO PRINT-AREA
WRITE PRINT-AREA
*
MOVE HIGHFEN TO PRINT-AREA
WRITE PRINT-AREA
*
MOVE HEADING-LINE-03 TO PRINT-AREA
WRITE PRINT-AREA
*
MOVE HIGHFEN TO PRINT-AREA
WRITE PRINT-AREA
*
MOVE HIGHFEN TO PRINT-AREA
WRITE PRINT-AREA.
*
MOVE ZERO TO LINE-COUNT.
*
240-PRINT-CUST-LINE.
*
MOVE SPACES TO PRINT-AREA
WRITE PRINT-AREA.
*
MOVE CUST-TOTAL-INVSUBT-YTD
TO CTL-INVSUBT
MOVE CUST-TOTAL-INVTAX-YTD
TO CTL-INVTAX
MOVE CUST-TOTAL-INVSHIP-YTD
TO CTL-INVSHIP
MOVE CUST-TOTAL-INVTOTAL-YTD
TO CTL-INVTOTAL
MOVE CUST-LINE TO PRINT-AREA
WRITE PRINT-AREA
*
INITIALIZE CTL-TOTAL-FIELDS
*
MOVE HIGHFEN TO PRINT-AREA
WRITE PRINT-AREA.
*
301-CLOSE-CURSOR.
*
EXEC SQL
CLOSE CUS_INV
END-EXEC.
*
400-PRINT-GRAND-TOTALS.
*
MOVE HIGHFEN TO PRINT-AREA
WRITE PRINT-AREA
*
MOVE GRAND-TOTAL-INVSUBT-YTD
TO GTL-INVSUBT
MOVE GRAND-TOTAL-INVTAX-YTD
TO GTL-INVSHIP
MOVE GRAND-TOTAL-INVSHIP-YTD
TO GTL-INVTAX
MOVE GRAND-TOTAL-INVTOTAL-YTD
TO GTL-INVTOTAL
MOVE INV-COUNT TO GTL-INVOICE-COUNT
MOVE GRAND-TOTAL-LINE TO PRINT-AREA
WRITE PRINT-AREA
*
MOVE HIGHFEN TO PRINT-AREA
WRITE PRINT-AREA.
*
Raw
output.txt
------------------------------------------------------------------------------------------------------------------------------------
DATE: 03/07/2020 YEAR-TO-DATE SALES REPORT PAGE: 0001
TIME: 19:39 DB2PGM8
------------------------------------------------------------------------------------------------------------------------------------
CUSTNO FNAME LNAME INVNO INVSUBT INVTAX INVSHIP INVTOTAL
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
000001 CAPTAIN UDAY 602310 140.00 0.00 7.50 147.70
063082 15.00 0.00 0.00 15.00
063081 200.00 0.00 4.45 204.45
CUSTOMER TOTAL: 355.00 0.00 11.95 367.15
------------------------------------------------------------------------------------------------------------------------------------
000002 SANDEEP PRAJAPATI 602312 162.00 0.00 11.07 173.07
CUSTOMER TOTAL: 162.00 0.00 11.07 173.07
------------------------------------------------------------------------------------------------------------------------------------
000005 JULIA MARIA 062329 140.00 0.00 7.50 147.50
CUSTOMER TOTAL: 140.00 0.00 7.50 147.50
------------------------------------------------------------------------------------------------------------------------------------
000007 DEIGO KING 006233 178.23 0.00 0.00 178.23
CUSTOMER TOTAL: 178.23 0.00 0.00 178.23
------------------------------------------------------------------------------------------------------------------------------------
000009 JOHN SNOW 809316 500.00 5.80 10.00 515.80
709316 200.00 5.80 10.00 215.80
708316 100.00 5.80 10.00 115.80
706316 120.00 5.80 0.00 125.80
CUSTOMER TOTAL: 920.00 23.20 30.00 973.20
------------------------------------------------------------------------------------------------------------------------------------
000010 KARAN MANAS 602316 140.00 0.00 9.80 149.80
CUSTOMER TOTAL: 140.00 0.00 9.80 149.80
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
INVOICE ISSUED: 11 GRAND TOTAL : 1,895.23 23.20 70.32 1,988.95
------------------------------------------------------------------------------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment