IBM DB2 SQL code example to pull receiving information from Quantus for a receiving report.
CREATE PROCEDURE TEST_RECEIVING_REPORT (IN @BRANCH1 VARCHAR(8),
IN @BRANCH2 VARCHAR(8),
IN @BRANCH3 VARCHAR(8),
IN @STARTDATE Date,
IN @ENDDATE Date,
IN @SHIPPEDBY VARCHAR(8))
SPECIFIC TEST_RECEIVING_REPORT
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_PO_LINES (
INVBRANCH VARCHAR(8),
ORDERNUM VARCHAR(8),
RELEASE VARCHAR(8),
LINESEQNO INTEGER,
DTSHIPPED DATE,
ITEMNUM VARCHAR(32),
ALTITEMDESC VARCHAR(40),
MESSAGE VARCHAR(3000),
SHIPQTY DOUBLE,
MISCINFO VARCHAR(40),
BILLACCT VARCHAR(8),
NAME1 VARCHAR(32),
TAKENBY VARCHAR(8)
)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
BEGIN ATOMIC
DECLARE messageStr VARCHAR(32672) DEFAULT '';
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT *
FROM SESSION.TEMP_PO_LINES;
loop1: FOR ROW1 AS
SELECT
QUANTUS.ORDER_LINE.INVBRANCH,
QUANTUS.ORDER_LINE.ORDERNUM,
QUANTUS.ORDER_LINE.RELEASE,
QUANTUS.ORDER_LINE.LINESEQNO,
QUANTUS.ORDER_LINE.DTSHIPPED,
QUANTUS.ORDER_LINE.ITEMNUM,
QUANTUS.ORDER_LINE.ALTITEMDESC,
QUANTUS.ORDER_LINE.SHIPQTY,
QUANTUS.ORDER_LINE.MISCINFO,
QUANTUS.ORDER_LINE.BILLACCT,
QUANTUS.VENDOR.NAME1,
QUANTUS.PURCHASE.TAKENBY,
QUANTUS.ORDER_LINE.SOURCE,
QUANTUS.ORDER_LINE.BRANCH,
QUANTUS.ORDER_LINE.UNIQUENO,
QUANTUS.ADD_INFO.TYPE
FROM QUANTUS.VENDOR
JOIN QUANTUS.ORDER_LINE ON
QUANTUS.VENDOR.VENDORACCT = QUANTUS.ORDER_LINE.BILLACCT
AND QUANTUS.VENDOR.BRANCH = QUANTUS.ORDER_LINE.BILLBRANCH
JOIN QUANTUS.PURCHASE ON
QUANTUS.ORDER_LINE.BRANCH = QUANTUS.PURCHASE.BRANCH
AND QUANTUS.ORDER_LINE.ORDERNUM = QUANTUS.PURCHASE.ORDERNUM
AND QUANTUS.ORDER_LINE.RELEASE = QUANTUS.PURCHASE.RELEASE
JOIN QUANTUS.ADD_INFO
ON QUANTUS.ADD_INFO.BRANCH = QUANTUS.ORDER_LINE.BRANCH
AND QUANTUS.ADD_INFO.ORDERNUM = QUANTUS.ORDER_LINE.ORDERNUM
AND QUANTUS.ADD_INFO.RELEASE = QUANTUS.ORDER_LINE.RELEASE
AND QUANTUS.ADD_INFO.SOUNIQUENO = QUANTUS.ORDER_LINE.UNIQUENO
WHERE
QUANTUS.ORDER_LINE.SOURCE = 'P'
AND QUANTUS.PURCHASE.TRANSTYPE IN ('S', 'C', 'P', 'K', 'R', 'W')
AND QUANTUS.ORDER_LINE.HISTORY <> 'Y'
AND QUANTUS.ADD_INFO.SOURCE LIKE 'P'
AND QUANTUS.ADD_INFO.TYPE LIKE 'G'
AND QUANTUS.ADD_INFO.UNIQUENO = 1
AND LTRIM(QUANTUS.ORDER_LINE.BRANCH) IN (@BRANCH1, @BRANCH2, @BRANCH3)
AND QUANTUS.ORDER_LINE.DTSHIPPED BETWEEN @STARTDATE AND @ENDDATE
AND RTRIM(QUANTUS.ORDER_LINE.ITEMSOURCE) LIKE 'PUR'
AND (QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 OR QUANTUS.ORDER_LINE.ADJORDERAMT = 'Y')
AND LTRIM(QUANTUS.ORDER_LINE.SHIPPEDBY) = @SHIPPEDBY
ORDER BY
QUANTUS.ORDER_LINE.BRANCH ASC,
QUANTUS.ORDER_LINE.ORDERNUM ASC,
QUANTUS.ORDER_LINE.RELEASE ASC,
QUANTUS.ORDER_LINE.LINESEQNO ASC
DO
SET messageStr = '';
loop2: FOR ROW2 AS
SELECT
QUANTUS.ADD_INFO.MESSAGE
FROM
QUANTUS.ADD_INFO
WHERE
QUANTUS.ADD_INFO.BRANCH = ROW1.BRANCH
AND QUANTUS.ADD_INFO.SOURCE = ROW1.SOURCE
AND QUANTUS.ADD_INFO.ORDERNUM = ROW1.ORDERNUM
AND QUANTUS.ADD_INFO.RELEASE = ROW1.RELEASE
AND QUANTUS.ADD_INFO.TYPE = ROW1.TYPE
AND QUANTUS.ADD_INFO.SOUNIQUENO = ROW1.UNIQUENO
ORDER BY UNIQUENO
DO
SET messageStr = messageStr CONCAT ROW2.MESSAGE;
END FOR loop2;
INSERT INTO SESSION.TEMP_PO_LINES
VALUES(
ROW1.INVBRANCH,
ROW1.ORDERNUM,
ROW1.RELEASE,
ROW1.LINESEQNO,
ROW1.DTSHIPPED,
ROW1.ITEMNUM,
ROW1.ALTITEMDESC,
messageStr,
ROW1.SHIPQTY,
ROW1.MISCINFO,
ROW1.BILLACCT,
ROW1.NAME1,
ROW1.TAKENBY
);
END FOR loop1;
loop3: FOR ROW3 AS
SELECT
QUANTUS.ORDER_LINE.INVBRANCH,
QUANTUS.ORDER_LINE.ORDERNUM,
QUANTUS.ORDER_LINE.RELEASE,
QUANTUS.ORDER_LINE.LINESEQNO,
QUANTUS.ORDER_LINE.DTSHIPPED,
QUANTUS.ORDER_LINE.ITEMNUM,
QUANTUS.ORDER_LINE.ALTITEMDESC,
QUANTUS.ORDER_LINE.SHIPQTY,
QUANTUS.ORDER_LINE.MISCINFO,
QUANTUS.ORDER_LINE.BILLACCT,
QUANTUS.VENDOR.NAME1,
QUANTUS.PURCHASE.TAKENBY
FROM QUANTUS.VENDOR
JOIN QUANTUS.ORDER_LINE ON
QUANTUS.VENDOR.VENDORACCT = QUANTUS.ORDER_LINE.BILLACCT
AND QUANTUS.VENDOR.BRANCH = QUANTUS.ORDER_LINE.BILLBRANCH
JOIN QUANTUS.PURCHASE ON
QUANTUS.ORDER_LINE.BRANCH = QUANTUS.PURCHASE.BRANCH
AND QUANTUS.ORDER_LINE.ORDERNUM = QUANTUS.PURCHASE.ORDERNUM
AND QUANTUS.ORDER_LINE.RELEASE = QUANTUS.PURCHASE.RELEASE
WHERE
QUANTUS.ORDER_LINE.SOURCE = 'P'
AND QUANTUS.PURCHASE.TRANSTYPE IN ('S', 'C', 'P', 'K', 'R', 'W')
AND QUANTUS.ORDER_LINE.HISTORY <> 'Y'
AND LTRIM(QUANTUS.ORDER_LINE.BRANCH) IN (@BRANCH1, @BRANCH2, @BRANCH3)
AND QUANTUS.ORDER_LINE.DTSHIPPED BETWEEN @STARTDATE AND @ENDDATE
AND RTRIM(QUANTUS.ORDER_LINE.ITEMSOURCE) LIKE 'PUR'
AND (QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 OR QUANTUS.ORDER_LINE.ADJORDERAMT = 'Y')
AND LTRIM(QUANTUS.ORDER_LINE.SHIPPEDBY) = @SHIPPEDBY
AND NOT EXISTS(SELECT *
FROM SESSION.TEMP_PO_LINES
WHERE
QUANTUS.ORDER_LINE.INVBRANCH = SESSION.TEMP_PO_LINES.INVBRANCH
AND QUANTUS.ORDER_LINE.ORDERNUM = SESSION.TEMP_PO_LINES.ORDERNUM
AND QUANTUS.ORDER_LINE.RELEASE = SESSION.TEMP_PO_LINES.RELEASE
AND QUANTUS.ORDER_LINE.LINESEQNO = SESSION.TEMP_PO_LINES.LINESEQNO)
ORDER BY
QUANTUS.ORDER_LINE.BRANCH ASC,
QUANTUS.ORDER_LINE.ORDERNUM ASC,
QUANTUS.ORDER_LINE.RELEASE ASC,
QUANTUS.ORDER_LINE.LINESEQNO ASC
DO
INSERT INTO SESSION.TEMP_PO_LINES
VALUES(
ROW3.INVBRANCH,
ROW3.ORDERNUM,
ROW3.RELEASE,
ROW3.LINESEQNO,
ROW3.DTSHIPPED,
ROW3.ITEMNUM,
ROW3.ALTITEMDESC,
'',
ROW3.SHIPQTY,
ROW3.MISCINFO,
ROW3.BILLACCT,
ROW3.NAME1,
ROW3.TAKENBY
);
END FOR loop3;
OPEN cursor1;
END;
END P1