IBM DB2 SQL code example to pull Quantus GL detailed summary.
CREATE PROCEDURE TEST_GL_DETAILED_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_DETAILED_SUMMARY_REPORT
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
--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;
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),
VENDOR VARCHAR(8),
VENDOR_NAME VARCHAR(64),
DEBITAMT DOUBLE,
CREDITAMT DOUBLE,
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
IF @BRANCH1 = '03' OR @BRANCH2 = '03' OR @BRANCH3 = '03' THEN
loop3: FOR ROW3 AS
SELECT QUANTUS.GL_CHART.GLNUMBER, QUANTUS.GL_CHART.BRANCH
FROM QUANTUS.GL_CHART
WHERE TRIM(GLNUMBER) LIKE @GLRANGE AND LTRIM(BRANCH) = '01'
DO
SET dblBalance = QUERIES.TEST_GL_ACCOUNT_BALANCE(' 03', @STARTDATE - 1 DAY, ROW3.GLNUMBER);
IF dblBalance IS NOT NULL THEN
INSERT INTO SESSION.TEMP_BALANCE VALUES(
' 03',
ROW3.GLNUMBER,
dblBalance);
END IF;
END FOR loop3;
END IF;
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);
IF dblBalance IS NOT NULL THEN
INSERT INTO SESSION.TEMP_BALANCE VALUES(
ROW1.BRANCH,
ROW1.GLNUMBER,
dblBalance);
END IF;
END FOR loop1;
--Consider using select into
loop2: FOR ROW2 AS
SELECT
MAX(QUANTUS.GL_TRANSACTION.TMCREATED) AS TMCREATED,
MAX(QUANTUS.GL_TRANSACTION.DTCREATED) AS DTCREATED,
MAX(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,
COALESCE(MAX(QUANTUS.VENDOR.VENDORACCT),
COALESCE(MAX(QUANTUS.CUSTOMER.CUSTOMERACCT), '')) AS VENDOR,
COALESCE(CONCAT(RTRIM(MAX(QUANTUS.VENDOR.NAME1)), MAX(QUANTUS.VENDOR.NAME2)),
COALESCE(CONCAT(RTRIM(MAX(QUANTUS.CUSTOMER.NAME1)), MAX(QUANTUS.CUSTOMER.NAME2)), '')) AS VENDOR_NAME,
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
LEFT OUTER JOIN QUANTUS.PURCHASE
ON QUANTUS.PURCHASE.ORDERNUM = QUANTUS.GL_TRANSACTION.ORDERNUM
AND QUANTUS.PURCHASE.BRANCH = QUANTUS.GL_TRANSACTION.BRANCH
AND QUANTUS.PURCHASE.RELEASE = QUANTUS.GL_TRANSACTION.ORDERREL
LEFT OUTER JOIN QUANTUS.VENDOR ON QUANTUS.PURCHASE.VENDOR = QUANTUS.VENDOR.VENDORACCT
AND QUANTUS.VENDOR.BRANCH = QUANTUS.PURCHASE.BRANCH
LEFT OUTER JOIN QUANTUS.SALES_ORDER ON QUANTUS.SALES_ORDER.BRANCH = QUANTUS.GL_TRANSACTION.BRANCH
AND QUANTUS.SALES_ORDER.ORDERNUM = QUANTUS.GL_TRANSACTION.ORDERNUM
AND QUANTUS.SALES_ORDER.RELEASE = QUANTUS.GL_TRANSACTION.ORDERREL
LEFT OUTER JOIN QUANTUS.CUSTOMER ON QUANTUS.CUSTOMER.BRANCH = QUANTUS.GL_TRANSACTION.BRANCH
AND QUANTUS.CUSTOMER.CUSTOMERACCT = QUANTUS.SALES_ORDER.BILLCUST
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,
ROW2.VENDOR,
ROW2.VENDOR_NAME,
dblDebit,
dblCredit,
dblBalance);
END IF;
END FOR loop2;
-- Cursor left open for client application
OPEN CURSOR1;
END;
END P1