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 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144
|
CREATE TABLE GGDEMTRA
(
NO_DT VARCHAR2(10 BYTE),
ETABLIE_PAR VARCHAR2(40 BYTE),
DATE_ETABLIS_DT DATE,
HEURE_ETABLIS_DT DATE,
DEPART_DEMANDEUR VARCHAR2(3 BYTE),
NO_SERIE_EQUIP VARCHAR2(20 BYTE),
DESCRIPT_ANOMALIE VARCHAR2(1000 BYTE),
ARRET_EQUIP CHAR(1 BYTE),
DATE_ARRET_EQUIP DATE,
HEURE_ARRET_EQUIP DATE,
DATE_REQUISE DATE,
HEURE_REQUISE DATE,
PRIORITE_DEMAND VARCHAR2(2 BYTE),
APPROUVEE_PAR VARCHAR2(40 BYTE),
DATE_APPROBAT DATE,
HEURE_APPROBAT DATE,
OBSERV_APPROBAT VARCHAR2(100 BYTE),
RECEPT_MAINT_PAR VARCHAR2(40 BYTE),
DATE_RECEPT_MAINT DATE,
HEURE_RECEPT_MAINT DATE,
PRIORITE_MODIFIEE VARCHAR2(2 BYTE),
GENRE_TRAVAUX VARCHAR2(3 BYTE),
RECEPT_PREP_PAR VARCHAR2(40 BYTE),
DATE_RECEPT_PREP DATE,
HEURE_RECEPT_PREP DATE,
DATE_DEBUT_PREP DATE,
HEURE_DEBUT_PREP DATE,
DATE_LIM_FIN_PREP DATE,
HEURE_LIM_FIN_PREP DATE,
CRAFT_LEADER VARCHAR2(3 BYTE),
DIAGNOSTIC_PANNE VARCHAR2(1000 BYTE),
NECESSITE_ETUDE CHAR(1 BYTE),
DELAI_ETUDE DATE,
HEURES_PERT NUMBER(6),
MINUTES_PERT NUMBER(2),
CODE_CAUSE_1 VARCHAR2(3 BYTE),
CODE_CAUSE_2 VARCHAR2(3 BYTE),
CODE_CAUSE_3 VARCHAR2(3 BYTE),
CODE_CAUSE_4 VARCHAR2(3 BYTE),
CODE_CAUSE_5 VARCHAR2(3 BYTE),
CODE_ANOMALIE_1 VARCHAR2(3 BYTE),
CODE_ANOMALIE_2 VARCHAR2(3 BYTE),
CODE_ANOMALIE_3 VARCHAR2(3 BYTE),
CODE_ANOMALIE_4 VARCHAR2(3 BYTE),
CODE_ANOMALIE_5 VARCHAR2(3 BYTE),
RECEPT_PLAN_PAR VARCHAR2(40 BYTE),
DATE_RECEPT_PLAN DATE,
HEURE_RECEPT_PLAN DATE,
PRIORITE_5 VARCHAR2(1 BYTE),
DATE_LANCEMENT_DT DATE,
EQUIP_TESTE_PAR VARCHAR2(40 BYTE),
DATE_TEST_EQUIP DATE,
HEURE_TEST_EQUIP DATE,
RESULTATS_TEST VARCHAR2(100 BYTE),
EQUIP_RECEPT_PAR VARCHAR2(40 BYTE),
DATE_RECEPT_EQUIP DATE,
HEURE_RECEPT_EQUIP DATE,
OBSERV_EQUIP VARCHAR2(100 BYTE),
DATE_DEMAR_EQUIP DATE,
HEURE_DEMAR_EQUIP DATE,
CODE_GAMME VARCHAR2(10 BYTE),
REPERE_EQUIP VARCHAR2(40 BYTE),
CODE_COMPLEXE CHAR(2 BYTE),
CODE_ZONE CHAR(1 BYTE),
CODE_UNITE CHAR(2 BYTE),
CODE_CHAINE CHAR(2 BYTE),
CODE_INTERVENT CHAR(2 BYTE),
NO_RAPPORT_DI VARCHAR2(10 BYTE),
NO_RAPPORT_PREDICTIF VARCHAR2(10 BYTE),
CONSTRAINT GGDEMTRA_CK10
CHECK (date_demar_equip >= date_arret_equip),
CONSTRAINT GGDEMTRA_CK9
CHECK (priorite_5 IN ('O','N')),
CONSTRAINT GGDEMTRA_CK8
CHECK (date_lim_fin_prep >= date_debut_prep),
CONSTRAINT GGDEMTRA_CK7
CHECK (date_debut_prep >= date_recept_prep),
CONSTRAINT GGDEMTRA_CK6
CHECK (necessite_etude IN ('O','N')),
CONSTRAINT GGDEMTRA_CK4
CHECK (priorite_modifiee IN ('1','2','3A','3B','4','4A','4B')),
CONSTRAINT GGDEMTRA_CK3
CHECK (priorite_demand IN ('1','2','3A','3B','4','4A','4B')),
CONSTRAINT GGDEMTRA_CK1
CHECK (minutes_pert >= 0 AND minutes_pert < 60),
CONSTRAINT GGDEMTRA_CK5
CHECK (genre_travaux = '1' AND priorite_modifiee IN ('1','2','3A','3B','4','4A','4B') OR genre_travaux = '2' AND priorite_modifiee IN ('3A','3B','4','4A','4B') OR genre_travaux = '3' OR genre_travaux = '4' OR genre_travaux = '5' AND priorite_modifiee = '3B'),
CONSTRAINT GGDEMTRA_CK2
CHECK (arret_equip IN ('O','N')),
CONSTRAINT GGDEMTRA_PK0
PRIMARY KEY
(NO_DT),
CONSTRAINT GGDEMTRA_FK6
FOREIGN KEY (CODE_CAUSE_1)
REFERENCES GGCAUSES (CODE_CAUSE),
CONSTRAINT GGDEMTRA_FK4
FOREIGN KEY (DEPART_DEMANDEUR)
REFERENCES GXDEPART (DEPART_DEMANDEUR),
CONSTRAINT GGDEMTRA_FK3
FOREIGN KEY (REPERE_EQUIP)
REFERENCES GGTYPEQU (REPERE_EQUIP),
CONSTRAINT GGDEMTRA_FK2
FOREIGN KEY (CODE_COMPLEXE, CODE_ZONE, CODE_UNITE, CODE_CHAINE, CODE_INTERVENT)
REFERENCES GGCODMNT (CODE_COMPLEXE,CODE_ZONE,CODE_UNITE,CODE_CHAINE,CODE_INTERVENT),
CONSTRAINT GGDEMTRA_FK15
FOREIGN KEY (CODE_ANOMALIE_5)
REFERENCES GGANOMAL (CODE_ANOMALIE),
CONSTRAINT GGDEMTRA_FK17
FOREIGN KEY (NO_RAPPORT_PREDICTIF)
REFERENCES GGPANOML (NO_RAPPORT_ANOMALIE),
CONSTRAINT GGDEMTRA_FK12
FOREIGN KEY (CODE_ANOMALIE_2)
REFERENCES GGANOMAL (CODE_ANOMALIE),
CONSTRAINT GGDEMTRA_FK16
FOREIGN KEY (CRAFT_LEADER)
REFERENCES GGCRAFTS (CODE_CRAFT),
CONSTRAINT GGDEMTRA_FK14
FOREIGN KEY (CODE_ANOMALIE_4)
REFERENCES GGANOMAL (CODE_ANOMALIE),
CONSTRAINT GGDEMTRA_FK13
FOREIGN KEY (CODE_ANOMALIE_3)
REFERENCES GGANOMAL (CODE_ANOMALIE),
CONSTRAINT GGDEMTRA_FK11
FOREIGN KEY (CODE_ANOMALIE_1)
REFERENCES GGANOMAL (CODE_ANOMALIE),
CONSTRAINT GGDEMTRA_FK10
FOREIGN KEY (CODE_CAUSE_5)
REFERENCES GGCAUSES (CODE_CAUSE),
CONSTRAINT GGDEMTRA_FK5
FOREIGN KEY (CODE_GAMME)
REFERENCES GGGAMMEO (CODE_GAMME),
CONSTRAINT GGDEMTRA_FK9
FOREIGN KEY (CODE_CAUSE_4)
REFERENCES GGCAUSES (CODE_CAUSE),
CONSTRAINT GGDEMTRA_FK8
FOREIGN KEY (CODE_CAUSE_3)
REFERENCES GGCAUSES (CODE_CAUSE),
CONSTRAINT GGDEMTRA_FK7
FOREIGN KEY (CODE_CAUSE_2)
REFERENCES GGCAUSES (CODE_CAUSE)
); |
Partager