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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
|
DECLARE
var_datedebut VARCHAR2(20);
var_duree VARCHAR2(10);
var_uniqueid VARCHAR2(50);
var_service VARCHAR2(100);
var_interface VARCHAR2(100);
var_type_error VARCHAR2(5);
var_sens VARCHAR2(15);
var_team VARCHAR2(20);
var_categorie VARCHAR2(50);
var_errormessage VARCHAR2(1000);
var_parameter VARCHAR2(40);
var_value VARCHAR2(50);
var_explication VARCHAR2(1000);
var_solution VARCHAR2(800);
var_commentaire VARCHAR2(1000);
WITH TEST_DATA AS (
SELECT TO_CHAR(b.AUDITTIMESTAMP,'YYYY/MM/DD HH24:MI') AS DATE_DEBUT ,
TO_CHAR(TRUNC(b.DURATION/3600),'FM9900') || ':' ||
TO_CHAR(TRUNC(MOD(b.DURATION,3600)/60),'FM00') || ':' ||
TO_CHAR(MOD(b.DURATION,60),'FM00') as DUREE,
b.ROOTCONTEXTID as UNIQUE_ID,
b.SERVICENAME,
tab.SENS,
tab.INTERFACE,
b.ERRORMESSAGE,
ch.Fieldalias as PARAMETER,
ch.STRINGVALUE as VALUE,
tab.ID
FROM bpm.WMSERVICE b, WM_RPT.PROD_TAB_TYPE_ERREUR tab, bpm.WMSERVICECUSTOMFLDS ch
WHERE b.SERVICENAME = tab.SERVICE
AND ch.ROOTCONTEXTID = b.ROOTCONTEXTID
AND b.AUDITTIMESTAMP>=to_date('20160301','YYYYMMDD')
AND b.AUDITTIMESTAMP<to_date('20160302','YYYYMMDD')
AND STATUS = '4'
AND b.ROOTCONTEXTID NOT IN ( SELECT DISTINCT(ROOTCONTEXTID) FROM bpm.WMSERVICECUSTOMFLDS
WHERE fieldalias = 'OPCO' AND stringvalue != 'France'
AND stringvalue != '%lastError/pipeline/OPCO%'
)
)
BEGIN
FOR LINE IN
(
SELECT b.DATE_DEBUT,b.DUREE,b.UNIQUE_ID,b.SERVICENAME,b.INTERFACE as INTERFACES,
err.TYPE_ERREUR,
b.SENS,
err.IN_CHARGE AS TEAM,
err.CATEGORIE,
b.ERRORMESSAGE,
b.PARAMETER,
b.VALUE,
err.EXPLICATION,
err.SOLUTION,
err.COMMENTAIRE
FROM TEST_DATA b,WM_RPT.PROD_TAB_CODE_ERROR_DETAILED err
WHERE b.ID = err.ID_INTERFACE
AND b.ERRORMESSAGE LIKE err.CODE_ERREUR
)
LOOP
dbms_output.put_line(LINE.DATE_DEBUT);
END LOOP;
END;
/ |
Partager