1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
| PROCEDURE GETCALLSTOIMPORT (SOURCE_DB IN VARCHAR2,
TARGET_DB IN VARCHAR2,
USERNAME IN VARCHAR2,
PASSWD IN VARCHAR2,
CLOSING_DATE IN VARCHAR2)
IS
V_DB_LINK VARCHAR2(100);
TYPE REF_CURSOR IS REF CURSOR;
V_CALL_ID_CUR REF_CURSOR;
V_CALL_ID NUMBER;
V_CALL_CODE VARCHAR2(100);
V_CALL_CLOSE_DATE VARCHAR2(100);
V_QUERY VARCHAR2(4000);
BEGIN
V_DB_LINK := CREATE_DB_LINK(SOURCE_DB, TARGET_DB, USERNAME, PASSWD);
V_QUERY := 'SELECT DISTINCT EC.CALL_ID, EC.CALL_CODE, TO_CHAR ( START_DATE, ''DD-MM-YYYY HH24:MI:SS'')
FROM EPSS_CALL@'||V_DB_LINK||' EC
INNER JOIN EPSS_INSTRUMENT@'||V_DB_LINK||' EI ON ( EC.CALL_ID = EI.CALL_ID )
INNER JOIN EPSS_WORKFLOWSTATE@'||V_DB_LINK||' EW ON ( EI.INSTRUMENT_ID = EW.INSTRUMENT_ID AND EI.CALL_ID = EW.CALL_ID )
WHERE STATE_ID = 3
AND CALL_ID < 500
AND START_DATE < TO_DATE ( '''|| CLOSING_DATE ||''', ''DD-MM-YYYY HH24:MI:SS'' )
AND EC.TYPE = 1
UNION
SELECT DISTINCT EC.CALL_ID, EC.CALL_CODE, TO_CHAR ( START_DATE, ''DD-MM-YYYY HH24:MI:SS'' )
FROM EPSS_CALL@'||V_DB_LINK||' EC
INNER JOIN EPSS_INSTRUMENT@'||V_DB_LINK||' EI ON ( EC.CALL_ID = EI.CALL_ID )
INNER JOIN EPSS_WORKFLOWSTATE@'||V_DB_LINK||' EW ON ( EI.INSTRUMENT_ID = EW.INSTRUMENT_ID AND EI.CALL_ID = EW.CALL_ID )
WHERE STATE_ID = 7
AND CALL_ID < 500
AND START_DATE < TO_DATE ( '''|| CLOSING_DATE ||''', ''DD-MM-YYYY HH24:MI:SS'' )
AND EC.TYPE = 2
UNION
SELECT DISTINCT EC.CALL_ID, EC.CALL_CODE, TO_CHAR ( START_DATE, ''DD-MM-YYYY HH24:MI:SS'' )
FROM EPSS_CALL@'||V_DB_LINK||' EC
INNER JOIN EPSS_INSTRUMENT@'||V_DB_LINK||' EI ON ( EC.CALL_ID = EI.CALL_ID )
INNER JOIN EPSS_WORKFLOWSTATE@'||V_DB_LINK||' EW ON ( EI.INSTRUMENT_ID = EW.INSTRUMENT_ID AND EI.CALL_ID = EW.CALL_ID )
WHERE STATE_ID = 3
AND CALL_ID < 500
AND START_DATE < TO_DATE ( '''|| CLOSING_DATE ||''', ''DD-MM-YYYY HH24:MI:SS'' )
AND EC.TYPE = 3';
DBMS_OUTPUT.PUT_LINE(V_QUERY);
OPEN V_CALL_ID_CUR FOR V_QUERY;
LOOP
FETCH V_CALL_ID_CUR INTO V_CALL_ID, V_CALL_CODE, V_CALL_CLOSE_DATE;
EXIT WHEN V_CALL_ID_CUR%NOTFOUND;
--IMPORT_CALL(V_CALL_ID);
DBMS_OUTPUT.PUT_LINE(V_CALL_ID);
END LOOP;
DROP_DB_LINK(V_DB_LINK);
END GETCALLSTOIMPORT; |
Partager