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
| SET NEWPAGE 0
SET LINESIZE 1024
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET TRIMSPOOL ON
SET COLSEP ';'
-- Formattage des colonnes
COLUMN ReferenceECR format A12;
COLUMN EtatWF format A15;
COLUMN NoeudCCB1 format A20;
COLUMN NoeudCCB2 format A20;
spool C:\temp\Statut_ECR.csv
-- Creation ligne d'entête
select 'Reference ECR','Date de creation','Etat WF','CCB1','date reception CCB1','date de reponse CCB1','delai CCB1',
'CCB2','date reception CCB2','date de reponse CCB2','delai CCB2','date de fin WF' from dual;
-- Extraction des données
select CCB1.processCCB1 ReferenceECR, CCB1.DebutProcess Creation,
(select description from SNE_R19.tn_flow_process_state where object_id=CCB1.StatutProcess) EtatWF,
CCB1.NoeudCCB1 NoeudCCB1,
CCB1.ReceptionCCB1 ReceptionCCB1, CCB1.ReponseCCB1 ReponseCCB1,CCB1.tempsCCB1 TraitementCCB1,
CCB2.NoeudCCB2 NoeudCCB2,
CCB2.ReceptionCCB2 ReceptionCCB2, CCB2.ReponseCCB2 ReponseCCB2,CCB2.tempsCCB2 TraitementCCB2,
case when CCB1.StatutProcess<>2 then null
else CCB1.FinProcess
end ClotureECR
from
(select P1.tdm_name processCCB1,
P1.TDMX_FLOW_PROCESS_STATUS StatutProcess,
P1.creation_date DebutProcess,
H1.TDM_RECEIVE_TIME ReceptionCCB1,
N1.MODIFICATION_DATE ReponseCCB1,
CASE WHEN TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME),0)>1 then
TO_CHAR(TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME),0),'9999') || ' j ' ||
TO_CHAR(TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME)*24,0)-(TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME),0)*24),'99') || ' h '
ELSE
TO_CHAR(TRUNC((N1.MODIFICATION_DATE - H1.TDM_RECEIVE_TIME)*24,0),'99') || ' h '
end tempsCCB1,
N1.tdm_description NoeudCCB1,
P1.modification_date FinProcess
from SNE_R19.tdm_sf_process P1
inner join SNE_R19.tdm_sf_prc_history H1 on P1.object_id=H1.tdm_process_id
inner join SNE_R19.tdm_sf_node N1 on H1.object_id1=N1.object_id and N1.tdm_description like 'Début'
where P1.tdm_name like 'ECR%') CCB1
left join
(select P2.tdm_name processCCB2,
H2.TDM_RECEIVE_TIME ReceptionCCB2,
N2.MODIFICATION_DATE ReponseCCB2,
case when TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME),0)>1 then
TO_CHAR(TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME),0),'9999') || ' j ' ||
TO_CHAR(TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME)*24,0)-(TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME),0)*24),'99') || ' h '
else
TO_CHAR(TRUNC((N2.MODIFICATION_DATE - H2.TDM_RECEIVE_TIME)*24,0),'99') || ' h '
end tempsCCB2,
N2.tdm_description NoeudCCB2
from SNE_R19.tdm_sf_process P2
inner join SNE_R19.tdm_sf_prc_history H2 on P2.object_id=H2.tdm_process_id
inner join SNE_R19.tdm_sf_node N2 on H2.object_id1=N2.object_id and N2.tdm_description like 'Planifier ECO%'
where P2.tdm_name like 'ECR%') CCB2 On CCB1.processCCB1=CCB2.processCCB2
where CCB1.StatutProcess<>3
order by CCB1.processCCB1;
spool off
exit |
