ProTech-Online.com

IBM DB2 SQL code example to pull Quantus back ordered purchase orders.

CREATE PROCEDURE TEST_BACK_ORDER (IN @VENDORACCT VARCHAR(8))
SPECIFIC BACK_ORDER
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
       SELECT
           QUANTUS.ORDER_LINE.INVBRANCH,
           QUANTUS.ORDER_LINE.ORDERNUM,
           QUANTUS.ORDER_LINE.RELEASE,
           QUANTUS.ORDER_LINE.LINESEQNO,
           QUANTUS.ORDER_LINE.DTORDERED,
           QUANTUS.ORDER_LINE.ITEMNUM,
           QUANTUS.ORDER_LINE.ALTITEMDESC,
           QUANTUS.ORDER_LINE.ORDERQTY - QUANTUS.ORDER_LINE.SHIPQTY AS BACKORDERQTY,
           QUANTUS.VENDOR.VENDORACCT,
           QUANTUS.PURCHASE.TAKENBY,
           QUANTUS.PURCHASE.ATTENTION
       FROM
           QUANTUS.VENDOR
           INNER JOIN QUANTUS.ORDER_LINE
               ON QUANTUS.VENDOR.VENDORACCT = QUANTUS.ORDER_LINE.BILLACCT
               AND QUANTUS.VENDOR.BRANCH = QUANTUS.ORDER_LINE.BILLBRANCH
           INNER 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
           INNER JOIN QUANTUS.INVENTORY
               ON QUANTUS.ORDER_LINE.INVBRANCH = QUANTUS.INVENTORY.BRANCH
               AND QUANTUS.ORDER_LINE.ITEMNUM = QUANTUS.INVENTORY.ITEMNUM
       WHERE
           QUANTUS.ORDER_LINE.SOURCE = 'P'
           AND QUANTUS.PURCHASE.TRANSTYPE IN ('P', 'K', 'R')
           AND QUANTUS.PURCHASE.STATUS IN ('O', 'D', 'S', 'I')
           AND QUANTUS.PURCHASE.HISTORY <> 'Y'
           AND QUANTUS.ORDER_LINE.DTREQUESTED < CURRENT DATE - 1 DAY
           AND RTRIM(QUANTUS.ORDER_LINE.ITEMSOURCE) LIKE 'PUR'
           AND QUANTUS.ORDER_LINE.SHIPQTY = 0
           AND (QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 OR QUANTUS.ORDER_LINE.ADJORDERAMT = 'Y')
           AND LTRIM(UPPER(QUANTUS.VENDOR.VENDORACCT)) LIKE UPPER(@VENDORACCT)
           AND LTRIM(QUANTUS.ORDER_LINE.INVBRANCH) <> '02'
       ORDER BY
           QUANTUS.ORDER_LINE.INVBRANCH,
           QUANTUS.VENDOR.VENDORACCT,
           QUANTUS.ORDER_LINE.ITEMNUM;
    -- Cursor left open for client application
    OPEN cursor1;

END P1