ProTech-Online.com

IBM DB2 SQL code example to pull Quantus GL transactions.

CREATE PROCEDURE TEST_GL_TRANSACTION_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_TRANSACTION_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),
       DTTRANSACTION DATE,
       BRANCH 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,
               QUANTUS.GL_TRANSACTION.SRCUNIQUE,
               QUANTUS.GL_TRANSACTION.JOURNALNUM,
               QUANTUS.GL_TRANSACTION.DTTRANSACTION,
               QUANTUS.GL_TRANSACTION.GLACCOUNT,
               QUANTUS.GL_TRANSACTION.BRANCH,
               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
           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.DTTRANSACTION,
                      ROW2.BRANCH,
                      dblDebit,
                      dblCredit,
                      dblBalance);
               END IF;
        END FOR loop2;
       -- Cursor left open for client application
       OPEN CURSOR1;
    END;

END P1