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;
/ |
