ProTech-Online.com

IBM DB2 SQL code example to pull Quantus invoice purchase orders.

CREATE PROCEDURE TEST_INVOICED_PURCHASE_ORDERS_CW_IC (
    IN @BRANCH1 VARCHAR(8),
    IN @BRANCH2 VARCHAR(8),
    IN @BRANCH3 VARCHAR(8),
    IN @ITEMNUM VARCHAR(32672),
    IN @STARTDATE Date,
    IN @ENDDATE Date,
    IN @BILLACCT VARCHAR(32672),
    IN @NAME1 VARCHAR(32672),
    IN @MAILCITY VARCHAR(32672),
    IN @MAILPROV VARCHAR(32672),
    IN @MAILCOUNTRY VARCHAR(32672),
    IN @ITEMGROUP VARCHAR(32672),
    IN @SUBGROUP2 VARCHAR(32672),
    IN @PROMOCODE VARCHAR(32672),
    IN @BILLBRANCH VARCHAR(32672),
    IN @ALTITEMDESC VARCHAR(32672),
    IN @MISCINFO VARCHAR(32672),
    IN @STATUS VARCHAR(32672) )
SPECIFIC INVOICED_PURCHASE_ORDERS_CW_IC
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
BEGIN
    DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR
       SELECT
            QUANTUS.ORDER_LINE.BRANCH,
            QUANTUS.ORDER_LINE.ORDERNUM,
            QUANTUS.ORDER_LINE.RELEASE,
            QUANTUS.ORDER_LINE.LINESEQNO,
            QUANTUS.ORDER_LINE.DTORDERED,
            QUANTUS.ORDER_LINE.DTSHIPPED,
            QUANTUS.ORDER_LINE.ITEMNUM,
            QUANTUS.ORDER_LINE.ALTITEMDESC,
            QUANTUS.ORDER_LINE.ORDERQTY,
            QUANTUS.ORDER_LINE.UNITPRICE,
            QUANTUS.ORDER_LINE.TOTALEXT,
            QUANTUS.ORDER_LINE.MISCINFO,
            QUANTUS.ORDER_LINE.BILLBRANCH,
            QUANTUS.ORDER_LINE.BILLACCT,
            QUANTUS.VENDOR.NAME1,
            QUANTUS.VENDOR.MAILCITY,
            QUANTUS.VENDOR.MAILPROV,
            QUANTUS.VENDOR.MAILCOUNTRY, 
            QUANTUS.ORDER_LINE.DTINVOICED,
            QUANTUS.ORDER_LINE.REFERNUM1,
            QUANTUS.ORDER_LINE.INVOICEQTY,
            QUANTUS.ORDER_LINE.ITEMGROUP,
            QUANTUS.ORDER_LINE.SUBGROUP2,
            QUANTUS.PURCHASE.PROMOCODE,
           QUANTUS.PURCHASE.STATUS
       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', 'G') AND
            QUANTUS.PURCHASE.HISTORY <> 'Y' AND
            LTRIM(QUANTUS.ORDER_LINE.BRANCH) IN (@BRANCH1, @BRANCH2, @BRANCH3) AND
            LTRIM(QUANTUS.ORDER_LINE.ITEMNUM) LIKE @ITEMNUM AND
            QUANTUS.ORDER_LINE.DTINVOICED BETWEEN @STARTDATE AND @ENDDATE AND
            LTRIM(QUANTUS.ORDER_LINE.BILLACCT) LIKE @BILLACCT AND
            RTRIM(QUANTUS.VENDOR.NAME1) LIKE @NAME1 AND
            RTRIM(QUANTUS.VENDOR.MAILCITY) LIKE @MAILCITY AND
            RTRIM(QUANTUS.VENDOR.MAILPROV) LIKE @MAILPROV AND
            RTRIM(QUANTUS.VENDOR.MAILCOUNTRY) LIKE @MAILCOUNTRY AND
            RTRIM(QUANTUS.ORDER_LINE.ITEMGROUP) LIKE @ITEMGROUP AND
            RTRIM(QUANTUS.ORDER_LINE.SUBGROUP2) LIKE @SUBGROUP2 AND
            RTRIM(QUANTUS.PURCHASE.PROMOCODE) LIKE @PROMOCODE AND
            LTRIM(QUANTUS.ORDER_LINE.BILLBRANCH) LIKE @BILLBRANCH AND
           QUANTUS.ORDER_LINE.ALTITEMDESC LIKE @ALTITEMDESC AND
           QUANTUS.PURCHASE.STATUS LIKE @STATUS AND
            (QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 OR QUANTUS.ORDER_LINE.ADJORDERAMT = 'Y')
       ORDER BY
            QUANTUS.ORDER_LINE.BRANCH, QUANTUS.ORDER_LINE.ORDERNUM, QUANTUS.ORDER_LINE.RELEASE, QUANTUS.ORDER_LINE.LINESEQNO;
    OPEN c1;

END