ProTech-Online.com

IBM DB2 SQL code example to pull Quantus data for a US sales tax audits.

CREATE PROCEDURE TEST_STATES_TAX_AUDITS (
    IN @STARTDATE Date,
    IN @ENDDATE Date,
    IN @MAILPROV VARCHAR(32672) )
SPECIFIC STATES_TAX_AUDITS
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.REFERNUM1,
            QUANTUS.ORDER_LINE.LINESEQNO,
           QUANTUS.ORDER_LINE.DTINVOICED,
           QUANTUS.ORDER_LINE.BILLBRANCH,
           QUANTUS.ORDER_LINE.BILLACCT,
            QUERIES.TEST_GET_BILLING_NAME(QUANTUS.ORDER_LINE.BILLBRANCH, QUANTUS.ORDER_LINE.BILLACCT) AS NAME,
            QUERIES.TEST_GET_BILLING_CITY(QUANTUS.ORDER_LINE.BILLBRANCH, QUANTUS.ORDER_LINE.BILLACCT) AS CITY,
            QUERIES.TEST_GET_BILLING_PROV(QUANTUS.ORDER_LINE.BILLBRANCH, QUANTUS.ORDER_LINE.BILLACCT) AS PROV,
            QUERIES.TEST_GET_BILLING_POSTAL(QUANTUS.ORDER_LINE.BILLBRANCH, QUANTUS.ORDER_LINE.BILLACCT) AS POSTAL,
            QUANTUS.ORDER_LINE.SHIPBRANCH,
            QUANTUS.ORDER_LINE.SHIPCUST,
            QUANTUS.CUSTOMER.NAME1,
            QUANTUS.CUSTOMER.MAILCITY,
            QUANTUS.CUSTOMER.MAILPROV,
            QUANTUS.CUSTOMER.MAILPOSTAL,
            QUANTUS.ORDER_LINE.ITEMNUM,
            QUANTUS.ORDER_LINE.ALTITEMDESC,
            QUANTUS.ORDER_LINE.INVOICEQTY,
            QUANTUS.ORDER_LINE.UNITPRICE,
            QUANTUS.ORDER_LINE.TOTALEXT,
            QUANTUS.ORDER_LINE.TAXAMT1,
            QUANTUS.ORDER_LINE.TAXAMT2,
            QUANTUS.ORDER_LINE.TAXAMT3,
            QUANTUS.ORDER_LINE.TAXAMT4,
            QUANTUS.ORDER_LINE.TAXCODE1,
            QUANTUS.ORDER_LINE.TAXCODE2,
            QUANTUS.ORDER_LINE.TAXCODE3,
            QUANTUS.ORDER_LINE.TAXCODE4,
           QUANTUS.SALES_ORDER.STATUS
       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') AND
            QUANTUS.SALES_ORDER.HISTORY <> 'Y' AND
            LTRIM(QUANTUS.ORDER_LINE.BRANCH) IN ('01', '02') AND
            LTRIM(QUANTUS.ORDER_LINE.BILLACCT) NOT LIKE 'INTDEPT' AND 
            QUANTUS.ORDER_LINE.DTINVOICED BETWEEN @STARTDATE AND @ENDDATE AND
            UPPER(RTRIM(QUANTUS.CUSTOMER.MAILPROV)) LIKE @MAILPROV AND
            UPPER(RTRIM(QUANTUS.CUSTOMER.MAILCOUNTRY)) LIKE 'US%' AND
            (QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 OR QUANTUS.ORDER_LINE.ADJORDERAMT = 'Y')
       ORDER BY
            QUANTUS.ORDER_LINE.BRANCH, QUANTUS.ORDER_LINE.REFERNUM1, QUANTUS.ORDER_LINE.LINESEQNO FOR FETCH ONLY;
    OPEN c1;

END