ProTech-Online.com

IBM DB2 SQL code example to pull Quantus invoiced sales orders.

CREATE PROCEDURE TEST_INVOICED_SALES_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 @SHIPCUST VARCHAR(32672),
    IN @NAME1 VARCHAR(32672),
    IN @MAILCITY VARCHAR(32672),
    IN @MAILPROV VARCHAR(32672),
    IN @MAILCOUNTRY VARCHAR(32672),
    IN @BILLACCT VARCHAR(32672),
    IN @SALESPERSON VARCHAR(32672),
    IN @SALESPERSON2 VARCHAR(32672),
    IN @ITEMGROUP VARCHAR(32672),
    IN @SUBGROUP2 VARCHAR(32672),
    IN @PROMOCODE VARCHAR(32672),
    IN @SHIPBRANCH VARCHAR(32672),
    IN @ALTITEMDESC VARCHAR(32672),
    IN @STATUS VARCHAR(32672) )
SPECIFIC INVOICED_SALES_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.SHIPBRANCH,
            QUANTUS.ORDER_LINE.SHIPCUST,
            QUANTUS.CUSTOMER.NAME1,
            QUANTUS.CUSTOMER.MAILCITY,
            QUANTUS.CUSTOMER.MAILPROV,
            QUANTUS.CUSTOMER.MAILCOUNTRY,
            QUANTUS.CUSTOMER.MAILPOSTAL,
            QUANTUS.ORDER_LINE.BILLACCT,
            QUANTUS.ORDER_LINE.DTINVOICED,
            QUANTUS.ORDER_LINE.REFERNUM1,
            QUANTUS.ORDER_LINE.INVOICEQTY,
            QUANTUS.SALES_ORDER.SALESPERSON,
            QUANTUS.SALES_ORDER.SALESPERSON2,
            QUANTUS.ORDER_LINE.ITEMGROUP,
            QUANTUS.ORDER_LINE.SUBGROUP2,
            QUANTUS.SALES_ORDER.PROMOCODE,
           QUANTUS.SALES_ORDER.STATUS,
           QUANTUS.SALES_ORDER.MISCINFO,
           QUANTUS.SALES_ORDER.FOREXCH,
           QUANTUS.ORDER_LINE.EXCHRATE,
           QUANTUS.SALES_ORDER.TAKENBY,
           QUANTUS.ORDER_LINE.TAXAMT1,
           QUANTUS.ORDER_LINE.TAXCODE1,
           QUANTUS.ORDER_LINE.TAXAMT2,
           QUANTUS.ORDER_LINE.TAXCODE2,
           QUANTUS.ORDER_LINE.TAXAMT3,
           QUANTUS.ORDER_LINE.TAXCODE3,
           QUANTUS.ORDER_LINE.TAXAMT4,
           QUANTUS.ORDER_LINE.TAXCODE4
       FROM QUANTUS.CUSTOMER
       JOIN QUANTUS.ORDER_LINE ON
           QUANTUS.CUSTOMER.CUSTOMERACCT = QUANTUS.ORDER_LINE.SHIPCUST
               AND QUANTUS.CUSTOMER.BRANCH = QUANTUS.ORDER_LINE.SHIPBRANCH
       JOIN QUANTUS.SALES_ORDER ON
           QUANTUS.ORDER_LINE.BRANCH = QUANTUS.SALES_ORDER.BRANCH
               AND QUANTUS.ORDER_LINE.ORDERNUM = QUANTUS.SALES_ORDER.ORDERNUM
               AND QUANTUS.ORDER_LINE.RELEASE = QUANTUS.SALES_ORDER.RELEASE
       WHERE
            QUANTUS.ORDER_LINE.SOURCE = 'S' AND
            QUANTUS.SALES_ORDER.TRANSTYPE IN ('S', 'C', 'E', 'K', 'R', 'W', 'G') AND
            QUANTUS.SALES_ORDER.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.SHIPCUST) LIKE @SHIPCUST AND
            RTRIM(QUANTUS.CUSTOMER.NAME1) LIKE @NAME1 AND
            RTRIM(QUANTUS.CUSTOMER.MAILCITY) LIKE @MAILCITY AND
            RTRIM(QUANTUS.CUSTOMER.MAILPROV) LIKE @MAILPROV AND
            RTRIM(QUANTUS.CUSTOMER.MAILCOUNTRY) LIKE @MAILCOUNTRY AND
            LTRIM(QUANTUS.ORDER_LINE.BILLACCT) LIKE @BILLACCT AND
            LTRIM(QUANTUS.SALES_ORDER.SALESPERSON) LIKE @SALESPERSON AND
            LTRIM(QUANTUS.SALES_ORDER.SALESPERSON2) LIKE @SALESPERSON2 AND
            RTRIM(QUANTUS.ORDER_LINE.ITEMGROUP) LIKE @ITEMGROUP AND
            RTRIM(QUANTUS.ORDER_LINE.SUBGROUP2) LIKE @SUBGROUP2 AND
            RTRIM(QUANTUS.SALES_ORDER.PROMOCODE) LIKE @PROMOCODE AND
            LTRIM(QUANTUS.ORDER_LINE.SHIPBRANCH) LIKE @SHIPBRANCH AND
           QUANTUS.ORDER_LINE.ALTITEMDESC LIKE @ALTITEMDESC AND
           QUANTUS.SALES_ORDER.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