ProTech-Online.com

IBM DB2 SQL code example to pull Quantus SRO.

CREATE PROCEDURE CHAD_TEST_CUSTOMERS_SERVICE_REQUESTS_ORDERS ( )
SPECIFIC CHAD_CUSTOMERS_SERVICE_REQUESTS_ORDERS
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_ADD_INFO (
       CUSTOMERACCT VARCHAR(8),
       SERVICEREQUESTNUM VARCHAR(8),
       SOURCE VARCHAR(32),
       BRANCH VARCHAR(8),
       ORDERNUM VARCHAR(8),
       RELEASE VARCHAR(8)
       )
       ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
    BEGIN ATOMIC
       DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
           SELECT *
           FROM SESSION.TEMP_ADD_INFO;
       loop1: FOR ROW AS
           SELECT QUANTUS.CUSTOMER.CUSTOMERACCT,
               QUANTUS.SERVICE_REQUEST.SERVICEREQUESTNUM,
               QUANTUS.SALES_ORDER.BRANCH,
               QUANTUS.SALES_ORDER.ORDERNUM,
               QUANTUS.SALES_ORDER.RELEASE
           FROM QUANTUS.SERVICE_REQUEST
               INNER JOIN QUANTUS.CUSTOMER
                  ON QUANTUS.SERVICE_REQUEST.ACCOUNTBRANCH = QUANTUS.CUSTOMER.BRANCH
                  AND QUANTUS.SERVICE_REQUEST.ACCOUNTOBJECTNO = QUANTUS.CUSTOMER.OBJECTNO
               INNER JOIN QUANTUS.OLINK
                  ON QUANTUS.SERVICE_REQUEST.BRANCH = QUANTUS.OLINK.BRANCHA
                  AND QUANTUS.SERVICE_REQUEST.OBJECTNO = QUANTUS.OLINK.OBJECTNOA
               INNER JOIN QUANTUS.SALES_ORDER
                  ON QUANTUS.OLINK.BRANCHB = QUANTUS.SALES_ORDER.BRANCH
                  AND QUANTUS.OLINK.OBJECTNOB = QUANTUS.SALES_ORDER.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'
       DO
           INSERT INTO SESSION.TEMP_ADD_INFO
                      VALUES(
                          ROW.CUSTOMERACCT,
                          ROW.SERVICEREQUESTNUM,
                          'SALES ORDER',
                          ROW.BRANCH,
                          ROW.ORDERNUM,
                          ROW.RELEASE
                          );
       END FOR loop1;
       loop2: FOR ROW2 AS
           SELECT QUANTUS.CUSTOMER.CUSTOMERACCT,
               QUANTUS.SERVICE_REQUEST.SERVICEREQUESTNUM,
               QUANTUS.PURCHASE.BRANCH,
               QUANTUS.PURCHASE.ORDERNUM,
               QUANTUS.PURCHASE.RELEASE
           FROM QUANTUS.SERVICE_REQUEST
               INNER JOIN QUANTUS.CUSTOMER
                  ON QUANTUS.SERVICE_REQUEST.ACCOUNTBRANCH = QUANTUS.CUSTOMER.BRANCH
                  AND QUANTUS.SERVICE_REQUEST.ACCOUNTOBJECTNO = QUANTUS.CUSTOMER.OBJECTNO
               INNER JOIN QUANTUS.OLINK
                  ON QUANTUS.SERVICE_REQUEST.BRANCH = QUANTUS.OLINK.BRANCHA
                  AND QUANTUS.SERVICE_REQUEST.OBJECTNO = QUANTUS.OLINK.OBJECTNOA
               INNER JOIN QUANTUS.PURCHASE
                  ON QUANTUS.OLINK.BRANCHB = QUANTUS.PURCHASE.BRANCH
                  AND QUANTUS.OLINK.OBJECTNOB = QUANTUS.PURCHASE.OBJECTNO
           WHERE --QUANTUS.SERVICE_REQUEST.STATUS = 'C'
               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'
       DO
           INSERT INTO SESSION.TEMP_ADD_INFO
                      VALUES(
                          ROW2.CUSTOMERACCT,
                          ROW2.SERVICEREQUESTNUM,
                          'PURCHASE ORDER',
                          ROW2.BRANCH,
                          ROW2.ORDERNUM,
                          ROW2.RELEASE
                          );
       END FOR loop2;
       OPEN cursor1;
    END;

END