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
| CREATE SNAPSHOT HABILITATION TABLESPACE DATA_02 STORAGE (INITIAL 1024K NEXT 1024K ) USING INDEX STORAGE (INITIAL 200K NEXT 128K)
TABLESPACE INDX_02 REFRESH COMPLETE START WITH SYSDATE - 1 NEXT TRUNC(SYSDATE) + 1 + 1/24 WITH PRIMARY KEY AS
SELECT H.NO_DOSSIER,
H.NO_DPSD,
H.NO_DPSD_DECIDEUR,
H.NO_DPSD_DEMANDEUR,
H.DATE_ARRIVEE_COURRIER,
H.REF_COURRIER,
H.DATE_COURRIER,
H.DATE_BE,
H.REF_BE,
H.DATE_OUVERTURE,
H.DATE_REEXAMEN,
H.NO_AVIS_DPSD,
H.DATE_AVIS,
H.DECISION,H.DATE_DECISION,
H.DATE_RFC_DEMANDE,
H.DATE_RFC_RETOUR,
H.COMMENTAIRE,
H.CODE_DECISION,
RD.LIBELLE_DECISION,
H.CODE_NIVEAU,
RNO.LIBELLE LIBELLE_NIVEAU,
H.CODE_MOTIF,
RMCO.LIBELLE LIBELLE_MOTIF,
H.CODE_AVIS,
RA.LIBELLE_AVIS,
H1.DUREE_TOTALE_HABILITATION,
H2.DUREE_PREMIERE_ETAPE,
H3.DUREE_SECONDE_ETAPE,
H4.DUREE_TROISIEME_ETAPE,
H5.DUREE_QUATRIEME_ETAPE,
H6.DUREE_CINQUIEME_ETAPE,
H7.DUREE_SIXIEME_ETAPE,
ODEM.NOM DERN_APPELATION_ORG_DEM,
ODEM.ABREGE DERN_ABREGE_ORG_DEM,
ODEM.Z_CODE_1200 DERN_CODE_ORG_DEM,
ODEC.NOM DERN_APPELATION_ORG_DEC,
ODEC.ABREGE DERN_ABREGE_ORG_DEC,
ODEC.Z_CODE_1200 DERN_VALIDE_ORG_DEC
FROM R_DECISION@DEC RD,
R_NIVEAU_ORG@DEC RNO,
R_MOTIF_CONTROLE_ORG@DEC RMCO,
R_AVIS@DEC RA,
(SELECT NO_DOSSIER,DATE_DECISION-DATE_COURRIER DUREE_TOTALE_HABILITATION FROM HABILITATION@DEC
WHERE DATE_DECISION is not null AND DATE_COURRIER is not null) H1,
(SELECT NO_DOSSIER,DATE_ARRIVEE_COURRIER-DATE_COURRIER DUREE_PREMIERE_ETAPE FROM HABILITATION@DEC
WHERE DATE_ARRIVEE_COURRIER is not null AND DATE_COURRIER is not null) H2,
(SELECT NO_DOSSIER,DATE_BE-DATE_ARRIVEE_COURRIER DUREE_SECONDE_ETAPE FROM HABILITATION@DEC
WHERE DATE_BE is not null AND DATE_ARRIVEE_COURRIER is not null) H3,
(SELECT NO_DOSSIER,DATE_RFC_DEMANDE-DATE_BE DUREE_TROISIEME_ETAPE FROM HABILITATION@DEC
WHERE DATE_BE is not null AND DATE_RFC_DEMANDE is not null) H4,
(SELECT NO_DOSSIER,DATE_RFC_RETOUR-DATE_RFC_DEMANDE DUREE_QUATRIEME_ETAPE FROM HABILITATION@DEC
WHERE DATE_RFC_RETOUR is not null AND DATE_RFC_DEMANDE is not null) H5,
(SELECT NO_DOSSIER,DATE_AVIS-DATE_RFC_RETOUR DUREE_CINQUIEME_ETAPE FROM HABILITATION@DEC
WHERE DATE_AVIS is not null AND DATE_RFC_RETOUR is not null) H6,
(SELECT NO_DOSSIER,DATE_DECISION-DATE_AVIS DUREE_SIXIEME_ETAPE FROM HABILITATION@DEC
WHERE DATE_DECISION is not null AND DATE_AVIS is not null) H7,
ORGANISME@DEC ODEM,ORGANISME@DEC ODEC,HABILITATION@DEC H
WHERE H.CODE_DECISION=RD.CODE_DECISION(+) AND
H.CODE_NIVEAU=RNO.CODE(+) AND
H.CODE_MOTIF=RMCO.CODE(+) AND
H.CODE_AVIS=RA.CODE_AVIS(+) AND
H.NO_DOSSIER=H1.NO_DOSSIER(+) AND
H.NO_DOSSIER=H2.NO_DOSSIER(+) AND
H.NO_DOSSIER=H3.NO_DOSSIER(+) AND
H.NO_DOSSIER=H4.NO_DOSSIER(+) AND
H.NO_DOSSIER=H5.NO_DOSSIER(+) AND
H.NO_DOSSIER=H6.NO_DOSSIER(+) AND
H.NO_DOSSIER=H7.NO_DOSSIER(+) AND
H.NO_DPSD_DEMANDEUR=ODEM.NO_DPSD(+) AND
H.NO_DPSD_DECIDEUR=ODEC.NO_DPSD(+)
/
exit; |
