ProTech-Online.com

IBM DB2 SQL code example to pull Quantus SRO billing information.

CREATE PROCEDURE CHAD_TEST_CUSTOMERS_SERVICE_REQUESTS_BILLING ( )
SPECIFIC CHAD_CUSTOMERS_SERVICE_REQUESTS_BILLING
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
    SELECT QUANTUS.CUSTOMER.CUSTOMERACCT,
       QUANTUS.SERVICE_REQUEST.SERVICEREQUESTNUM,
       QUANTUS.SERVICE_REQUEST.BILLSTATE,
        CHAD_TEST_TEXT_DATA(QUANTUS.SERVICE_REQUEST.BRANCH, QUANTUS.SERVICE_REQUEST.BILLDESCOBJECTNO) AS BILLING
    FROM QUANTUS.SERVICE_REQUEST
       INNER JOIN QUANTUS.CUSTOMER
           ON QUANTUS.SERVICE_REQUEST.ACCOUNTBRANCH = QUANTUS.CUSTOMER.BRANCH
           AND QUANTUS.SERVICE_REQUEST.ACCOUNTOBJECTNO = QUANTUS.CUSTOMER.OBJECTNO
    WHERE QUANTUS.SERVICE_REQUEST.ENTEREDBYOBJECTNO <> 10016
       --AND QUANTUS.SERVICE_REQUEST.STATUS = 'C'
       AND QUANTUS.SERVICE_REQUEST.PROBLEMCODE <> 'GOLD'
       AND QUANTUS.SERVICE_REQUEST.PROBLEMCODE <> 'TEST'
       --AND QUANTUS.SERVICE_REQUEST.PROBLEMCODE <> 'LOGI'
       AND QUANTUS.SERVICE_REQUEST.PROBLEMCODE <> 'SHIP';
       --AND QUANTUS.SERVICE_REQUEST.DTCLOSED < '2011-05-01';
    OPEN cursor1;

END