ProTech-Online.com

IBM DB2 SQL code example to pull Quantus GL summary.

CREATE PROCEDURE TEST_GL_SUMMARY_REPORT(
    IN @BRANCH1 VARCHAR(8),
    IN @BRANCH2 VARCHAR(8),
    IN @BRANCH3 VARCHAR(8),
    IN @STARTDATE DATE,
    IN @ENDDATE DATE,
    IN @GLRANGE VARCHAR(20) )
SPECIFIC GL_SUMMARY_REPORT
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_GLTABLE (
       SRCUNIQUE INTEGER,
       JOURNALNUM VARCHAR(8),
       GLACCOUNT VARCHAR(20),
       GLOFFSETACCOUNT VARCHAR(20),
       DTTRANSACTION DATE,
       BRANCH VARCHAR(8),
       SOURCEMODULE CHAR,
       ORDERNUM VARCHAR(8),
       ORDERREL VARCHAR(8),
       DEBITAMT DOUBLE,
       CREDITAMT DOUBLE,
       BALANCE DOUBLE)
       ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
    --This table is used to store balance for each gl account
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_BALANCE (
       BRANCH VARCHAR(8),
       GLACCOUNT VARCHAR(20),
       BALANCE DOUBLE)
    ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
    BEGIN ATOMIC
       DECLARE dblBalance DOUBLE DEFAULT 0;
       DECLARE dblCredit DOUBLE DEFAULT 0;
       DECLARE dblDebit DOUBLE DEFAULT 0;
       DECLARE currGLAccount VARCHAR(20) DEFAULT '';
       DECLARE currBranch VARCHAR(8) DEFAULT '';
       DECLARE CURSOR1 CURSOR WITH RETURN TO CLIENT FOR
           SELECT * FROM SESSION.TEMP_GLTABLE;
       --Get all accounts in GLRANGE and store them and their corresponding balances in temp table
       loop1: FOR ROW1 AS
           SELECT QUANTUS.GL_CHART.GLNUMBER, QUANTUS.GL_CHART.BRANCH
           FROM QUANTUS.GL_CHART
           WHERE TRIM(GLNUMBER) LIKE @GLRANGE AND LTRIM(BRANCH) IN (@BRANCH1, @BRANCH2, @BRANCH3)
       DO
           SET dblBalance = QUERIES.TEST_GL_ACCOUNT_BALANCE(ROW1.BRANCH, @STARTDATE - 1 DAY, ROW1.GLNUMBER);
           INSERT INTO SESSION.TEMP_BALANCE VALUES(
               ROW1.BRANCH,
               ROW1.GLNUMBER,
               dblBalance);
       END FOR loop1;
       --Consider using select into
       loop2: FOR ROW2 AS
           SELECT
               MIN(QUANTUS.GL_TRANSACTION.TMCREATED) AS TMCREATED,
               MIN(QUANTUS.GL_TRANSACTION.DTCREATED) AS DTCREATED,
               MIN(QUANTUS.GL_TRANSACTION.GLOFFSETACCOUNT) AS GLOFFSETACCOUNT,
               QUANTUS.GL_TRANSACTION.SRCUNIQUE,
               QUANTUS.GL_TRANSACTION.JOURNALNUM,
               QUANTUS.GL_TRANSACTION.DTTRANSACTION,
               QUANTUS.GL_TRANSACTION.GLACCOUNT,
               QUANTUS.GL_TRANSACTION.BRANCH,
               QUANTUS.GL_TRANSACTION.SOURCEMODULE,
               QUANTUS.GL_TRANSACTION.ORDERNUM,
               QUANTUS.GL_TRANSACTION.ORDERREL,
               SUM(QUANTUS.GL_TRANSACTION.DEBITAMT) AS DEBIT,
               SUM(QUANTUS.GL_TRANSACTION.CREDITAMT) AS CREDIT,
               MAX(SESSION.TEMP_BALANCE.BALANCE) AS STARTING_BALANCE
           FROM QUANTUS.GL_TRANSACTION
               INNER JOIN SESSION.TEMP_BALANCE
                  ON QUANTUS.GL_TRANSACTION.BRANCH = SESSION.TEMP_BALANCE.BRANCH
                  AND QUANTUS.GL_TRANSACTION.GLACCOUNT = SESSION.TEMP_BALANCE.GLACCOUNT
           WHERE
               TRIM(QUANTUS.GL_TRANSACTION.GLACCOUNT) LIKE @GLRANGE
               AND QUANTUS.GL_TRANSACTION.DTPOSTED BETWEEN @STARTDATE AND @ENDDATE
               AND LTRIM(QUANTUS.GL_TRANSACTION.BRANCH) IN (@BRANCH1, @BRANCH2, @BRANCH3)
           GROUP BY
               QUANTUS.GL_TRANSACTION.BRANCH,
               QUANTUS.GL_TRANSACTION.GLACCOUNT,
               QUANTUS.GL_TRANSACTION.SRCUNIQUE,
               QUANTUS.GL_TRANSACTION.JOURNALNUM,
               QUANTUS.GL_TRANSACTION.DTTRANSACTION,
               QUANTUS.GL_TRANSACTION.ORDERNUM,
               QUANTUS.GL_TRANSACTION.ORDERREL,
               QUANTUS.GL_TRANSACTION.SOURCEMODULE
           ORDER BY
               QUANTUS.GL_TRANSACTION.BRANCH,
               QUANTUS.GL_TRANSACTION.GLACCOUNT,
               QUANTUS.GL_TRANSACTION.DTTRANSACTION ASC,
               DTCREATED ASC,
               TMCREATED ASC
           DO
               IF ROW2.DEBIT - ROW2.CREDIT > 0.00001 OR ROW2.CREDIT - ROW2.DEBIT > 0.00001 THEN
                  IF currGLAccount <> ROW2.GLACCOUNT OR currBranch <> ROW2.BRANCH THEN
                      SET dblBalance = ROW2.STARTING_BALANCE + ROW2.DEBIT - ROW2.CREDIT;
                      SET currGLAccount = ROW2.GLACCOUNT;
                      SET currBranch = ROW2.BRANCH;
                  ELSE
                      SET dblBalance = dblBalance + ROW2.DEBIT - ROW2.CREDIT;
                  END IF;
                  SET dblDebit = 0;
                  SET dblCredit = 0;
                  IF ROUND(ROW2.DEBIT, 2) > ROUND(ROW2.CREDIT, 2) THEN
                      SET dblDebit = ROW2.DEBIT - ROW2.CREDIT;
                  ELSE
                      SET dblCredit = ROW2.CREDIT - ROW2.DEBIT;
                  END IF;
                  INSERT INTO SESSION.TEMP_GLTABLE VALUES(
                      ROW2.SRCUNIQUE,
                      ROW2.JOURNALNUM,
                      ROW2.GLACCOUNT,
                      ROW2.GLOFFSETACCOUNT,
                      ROW2.DTTRANSACTION,
                      ROW2.BRANCH,
                      ROW2.SOURCEMODULE,
                      ROW2.ORDERNUM,
                      ROW2.ORDERREL,
                      dblDebit,
                      dblCredit,
                      dblBalance);
               END IF;
        END FOR loop2;
       -- Cursor left open for client application
       OPEN CURSOR1;
    END;

END P1