#!/usr/bin/env python3 # -*- coding: utf-8 -*- """Generate schema_oracle.sql (DDL, reference data, views, PL/SQL, triggers).""" import json from pathlib import Path HERE = Path(__file__).resolve().parent ROOT = HERE.parent SEED = json.loads((HERE / "seed_data.json").read_text(encoding="utf-8")) OUT = ROOT / "schema_oracle.sql" def q(v): if v is None: return "NULL" if isinstance(v, bool): return "'O'" if v else "'N'" if isinstance(v, (int, float)): return str(v).replace(",", ".") return "'" + str(v).replace("'", "''") + "'" def num(v): return "NULL" if v is None else str(v).replace(",", ".") DDL = r"""-- ===================================================================== -- SRM-RSK | BASE DE DONNEES INDUSTRIELS -- DDL Oracle 12c+ / 19c / 23ai - modele 3NF, conformite automatisee -- Sources : presentation synthetique, canevas de collecte (11 onglets), -- cahier des charges d'assainissement liquide (Art. 42 a 48). -- ===================================================================== -- Principe : l'utilisateur saisit UNIQUEMENT les valeurs brutes d'analyse. -- Le code industriel est genere par direction : IN + CODE_DP + '-' + NNNN. -- Les statuts, alertes, total metaux lourds, KPI et cartes sont derives. -- ===================================================================== SET DEFINE OFF; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; -- --------------------------------------------------------------------- -- 1. REFERENTIELS -- --------------------------------------------------------------------- CREATE TABLE ref_province ( province_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_province PRIMARY KEY, code_province VARCHAR2(10 CHAR) NOT NULL CONSTRAINT uq_ref_province_code UNIQUE, libelle VARCHAR2(100 CHAR) NOT NULL, region VARCHAR2(100 CHAR) DEFAULT 'Rabat-Sale-Kenitra' NOT NULL, actif CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_province_actif CHECK (actif IN ('O','N')), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE direction ( code_dp VARCHAR2(10 CHAR) CONSTRAINT pk_direction PRIMARY KEY, libelle VARCHAR2(150 CHAR) NOT NULL, province_id NUMBER(10) NOT NULL CONSTRAINT fk_direction_province REFERENCES ref_province(province_id), ordre_affichage NUMBER(3) NOT NULL CONSTRAINT ck_direction_ordre CHECK (ordre_affichage > 0), dernier_ordre NUMBER(6) DEFAULT 0 NOT NULL CONSTRAINT ck_direction_dernier CHECK (dernier_ordre >= 0), CONSTRAINT uq_direction_ordre UNIQUE (ordre_affichage), CONSTRAINT ck_direction_code CHECK (REGEXP_LIKE(code_dp, '^DP[A-Z]+$')), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE ref_secteur_activite ( secteur_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_secteur PRIMARY KEY, code_secteur VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_secteur_code UNIQUE, libelle VARCHAR2(150 CHAR) NOT NULL, code_naf VARCHAR2(20 CHAR), actif CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_secteur_actif CHECK (actif IN ('O','N')), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE ref_liste ( code_liste VARCHAR2(40 CHAR) CONSTRAINT pk_ref_liste PRIMARY KEY, libelle VARCHAR2(150 CHAR) NOT NULL, description VARCHAR2(4000 CHAR), modifiable CHAR(1) DEFAULT 'N' NOT NULL CONSTRAINT ck_ref_liste_mod CHECK (modifiable IN ('O','N')), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE ref_valeur_liste ( code_liste VARCHAR2(40 CHAR) NOT NULL, code_valeur VARCHAR2(40 CHAR) NOT NULL, libelle VARCHAR2(150 CHAR) NOT NULL, ordre_affichage NUMBER(5) DEFAULT 0 NOT NULL CONSTRAINT ck_ref_valeur_ordre CHECK (ordre_affichage >= 0), actif CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_valeur_actif CHECK (actif IN ('O','N')), valeur_num NUMBER(12,3), CONSTRAINT pk_ref_valeur_liste PRIMARY KEY (code_liste, code_valeur), CONSTRAINT fk_ref_valeur_liste FOREIGN KEY (code_liste) REFERENCES ref_liste(code_liste), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE ref_unite ( unite_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_unite PRIMARY KEY, code_unite VARCHAR2(20 CHAR) NOT NULL CONSTRAINT uq_ref_unite_code UNIQUE, libelle VARCHAR2(80 CHAR) NOT NULL, cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE zone_industrielle ( zone_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_zone_ind PRIMARY KEY, code_zone VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_zone_ind_code UNIQUE, libelle VARCHAR2(150 CHAR) NOT NULL, province_id NUMBER(10) NOT NULL CONSTRAINT fk_zone_ind_province REFERENCES ref_province(province_id), latitude NUMBER(9,6) CONSTRAINT ck_zone_ind_lat CHECK (latitude IS NULL OR latitude BETWEEN -90 AND 90), longitude NUMBER(9,6) CONSTRAINT ck_zone_ind_lon CHECK (longitude IS NULL OR longitude BETWEEN -180 AND 180), superficie_ha NUMBER(10,2) CONSTRAINT ck_zone_ind_sup CHECK (superficie_ha IS NULL OR superficie_ha >= 0), -- geometrie SDO_GEOMETRY, -- activer si Oracle Spatial / APEX Map Region cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); -- --------------------------------------------------------------------- -- 2. IDENTIFICATION DES INDUSTRIELS -- --------------------------------------------------------------------- CREATE TABLE industriel ( industriel_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_industriel PRIMARY KEY, code_industriel VARCHAR2(20 CHAR) NOT NULL CONSTRAINT uq_industriel_code UNIQUE, ancien_code VARCHAR2(20 CHAR) CONSTRAINT uq_industriel_ancien UNIQUE, direction_id VARCHAR2(10 CHAR) NOT NULL CONSTRAINT fk_industriel_direction REFERENCES direction(code_dp), code_dp VARCHAR2(10 CHAR) NOT NULL, ordre_dp NUMBER(6) NOT NULL CONSTRAINT ck_industriel_ordre CHECK (ordre_dp > 0), raison_sociale VARCHAR2(200 CHAR) NOT NULL, ice CHAR(15) CONSTRAINT uq_industriel_ice UNIQUE, rc VARCHAR2(30 CHAR), secteur_id NUMBER(10) NOT NULL CONSTRAINT fk_industriel_secteur REFERENCES ref_secteur_activite(secteur_id), zone_id NUMBER(10) CONSTRAINT fk_industriel_zone REFERENCES zone_industrielle(zone_id), province_id NUMBER(10) NOT NULL CONSTRAINT fk_industriel_province REFERENCES ref_province(province_id), statut VARCHAR2(30 CHAR) NOT NULL, gps_lat NUMBER(9,6) CONSTRAINT ck_industriel_lat CHECK (gps_lat IS NULL OR gps_lat BETWEEN -90 AND 90), gps_lon NUMBER(9,6) CONSTRAINT ck_industriel_lon CHECK (gps_lon IS NULL OR gps_lon BETWEEN -180 AND 180), effectif NUMBER(8) CONSTRAINT ck_industriel_effectif CHECK (effectif IS NULL OR effectif >= 0), surface_m2 NUMBER(12,2) CONSTRAINT ck_industriel_surface CHECK (surface_m2 IS NULL OR surface_m2 >= 0), capacite_production VARCHAR2(200 CHAR), regime_travail VARCHAR2(50 CHAR), date_implantation DATE, CONSTRAINT ck_industriel_code CHECK (REGEXP_LIKE(code_industriel, '^INDP[A-Z]*-[0-9]{4,}$')), CONSTRAINT ck_industriel_code_genere CHECK (code_industriel = 'IN' || direction_id || '-' || TO_CHAR(ordre_dp, 'FM0000')), CONSTRAINT ck_industriel_code_dp CHECK (code_dp = direction_id), CONSTRAINT ck_industriel_ice CHECK (ice IS NULL OR REGEXP_LIKE(ice, '^[0-9]{15}$')), CONSTRAINT ck_industriel_statut CHECK (statut IN ('Actif','Arret','A verifier','En implantation')), CONSTRAINT uq_industriel_direction_ordre UNIQUE (direction_id, ordre_dp), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE contact_industriel ( contact_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_contact_ind PRIMARY KEY, industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_contact_ind_industriel REFERENCES industriel(industriel_id) ON DELETE CASCADE, type_contact VARCHAR2(30 CHAR) NOT NULL, nom VARCHAR2(120 CHAR) NOT NULL, fonction VARCHAR2(100 CHAR), telephone VARCHAR2(30 CHAR), email VARCHAR2(150 CHAR), principal CHAR(1) DEFAULT 'N' NOT NULL CONSTRAINT ck_contact_principal CHECK (principal IN ('O','N')), CONSTRAINT ck_contact_email CHECK (email IS NULL OR REGEXP_LIKE(email, '^[^@]+@[^@]+\.[^@]+$')), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE UNIQUE INDEX uq_contact_principal ON contact_industriel (industriel_id, CASE WHEN principal = 'O' THEN 1 END); -- --------------------------------------------------------------------- -- 3. ABONNEMENTS, BRANCHEMENTS, COMPTEURS (Art. 47) -- --------------------------------------------------------------------- CREATE TABLE ref_type_branchement ( type_branchement_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_type_branchement PRIMARY KEY, code_type VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_type_branchement UNIQUE, libelle VARCHAR2(120 CHAR) NOT NULL, cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE branchement ( branchement_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_branchement PRIMARY KEY, industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_branchement_industriel REFERENCES industriel(industriel_id), type_branchement_id NUMBER(10) NOT NULL CONSTRAINT fk_branchement_type REFERENCES ref_type_branchement(type_branchement_id), reference_branchement VARCHAR2(50 CHAR) NOT NULL CONSTRAINT uq_branchement_ref UNIQUE, date_mise_en_service DATE, etat VARCHAR2(30 CHAR) NOT NULL, regard_acces VARCHAR2(100 CHAR), CONSTRAINT uq_branchement_unique UNIQUE (industriel_id, type_branchement_id, date_mise_en_service), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE abonnement ( abonnement_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_abonnement PRIMARY KEY, industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_abonnement_industriel REFERENCES industriel(industriel_id), numero_abonnement VARCHAR2(40 CHAR) NOT NULL CONSTRAINT uq_abonnement_num UNIQUE, type_abonnement VARCHAR2(30 CHAR) NOT NULL CONSTRAINT ck_abonnement_type CHECK (type_abonnement IN ('EAU','ASSAINISSEMENT','DEVERSEMENT')), date_debut DATE NOT NULL, date_fin DATE, convention_deversement VARCHAR2(80 CHAR), statut VARCHAR2(30 CHAR) NOT NULL, CONSTRAINT ck_abonnement_dates CHECK (date_fin IS NULL OR date_fin >= date_debut), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE compteur ( compteur_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_compteur PRIMARY KEY, numero_compteur VARCHAR2(50 CHAR) NOT NULL CONSTRAINT uq_compteur_num UNIQUE, abonnement_id NUMBER(10) CONSTRAINT fk_compteur_abonnement REFERENCES abonnement(abonnement_id), branchement_id NUMBER(10) CONSTRAINT fk_compteur_branchement REFERENCES branchement(branchement_id), type_compteur VARCHAR2(30 CHAR) NOT NULL, unite VARCHAR2(20 CHAR) DEFAULT 'm3' NOT NULL, date_pose DATE, CONSTRAINT ck_compteur_rattachement CHECK (abonnement_id IS NOT NULL OR branchement_id IS NOT NULL), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE releve_compteur ( releve_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_releve_compteur PRIMARY KEY, compteur_id NUMBER(10) NOT NULL CONSTRAINT fk_releve_compteur_compteur REFERENCES compteur(compteur_id), date_releve DATE NOT NULL, index_releve NUMBER(14,2) NOT NULL CONSTRAINT ck_releve_index CHECK (index_releve >= 0), volume_m3 NUMBER(14,2) NOT NULL CONSTRAINT ck_releve_volume CHECK (volume_m3 >= 0), source_releve VARCHAR2(40 CHAR) NOT NULL, CONSTRAINT uq_releve_compteur UNIQUE (compteur_id, date_releve), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); -- Controle d'index croissant : a implementer via COMPOUND TRIGGER ou API APEX -- pour eviter ORA-04091 (table mutante) sur les imports multi-lignes. -- --------------------------------------------------------------------- -- 4. ENQUETES ET CONTROLES (Art. 48) -- --------------------------------------------------------------------- CREATE TABLE ref_type_visite ( type_visite_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_type_visite PRIMARY KEY, code_type VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_type_visite UNIQUE, libelle VARCHAR2(120 CHAR) NOT NULL, cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE app_role ( role_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_app_role PRIMARY KEY, code_role VARCHAR2(40 CHAR) NOT NULL CONSTRAINT uq_app_role UNIQUE, libelle VARCHAR2(120 CHAR) NOT NULL, cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE app_utilisateur ( utilisateur_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_app_utilisateur PRIMARY KEY, username VARCHAR2(100 CHAR) NOT NULL CONSTRAINT uq_app_utilisateur UNIQUE, nom_complet VARCHAR2(150 CHAR) NOT NULL, email VARCHAR2(150 CHAR), actif CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_app_user_actif CHECK (actif IN ('O','N')), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE affectation_role ( utilisateur_id NUMBER(10) NOT NULL CONSTRAINT fk_affect_role_user REFERENCES app_utilisateur(utilisateur_id), role_id NUMBER(10) NOT NULL CONSTRAINT fk_affect_role_role REFERENCES app_role(role_id), date_debut DATE NOT NULL, date_fin DATE, CONSTRAINT pk_affectation_role PRIMARY KEY (utilisateur_id, role_id), CONSTRAINT ck_affect_role_dates CHECK (date_fin IS NULL OR date_fin >= date_debut), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE enquete_controle ( enquete_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_enquete PRIMARY KEY, industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_enquete_industriel REFERENCES industriel(industriel_id), type_visite_id NUMBER(10) NOT NULL CONSTRAINT fk_enquete_type REFERENCES ref_type_visite(type_visite_id), date_visite DATE NOT NULL, agent_id NUMBER(10) CONSTRAINT fk_enquete_agent REFERENCES app_utilisateur(utilisateur_id), statut VARCHAR2(30 CHAR) NOT NULL CONSTRAINT ck_enquete_statut CHECK (statut IN ('PLANIFIEE','REALISEE','REPORTEE')), observations CLOB, CONSTRAINT uq_enquete UNIQUE (industriel_id, date_visite, type_visite_id), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE constat_enquete ( constat_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_constat_enquete PRIMARY KEY, enquete_id NUMBER(10) NOT NULL CONSTRAINT fk_constat_enquete_enquete REFERENCES enquete_controle(enquete_id) ON DELETE CASCADE, libelle VARCHAR2(200 CHAR) NOT NULL, gravite VARCHAR2(30 CHAR) NOT NULL, action_requise VARCHAR2(4000 CHAR), echeance DATE, statut_action VARCHAR2(30 CHAR) NOT NULL, cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); -- --------------------------------------------------------------------- -- 5. PARAMETRES, SEUILS REGLEMENTAIRES ET DEROGATIONS -- --------------------------------------------------------------------- CREATE TABLE ref_parametre ( parametre_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_parametre PRIMARY KEY, code_parametre VARCHAR2(40 CHAR) NOT NULL CONSTRAINT uq_ref_parametre_code UNIQUE, libelle VARCHAR2(200 CHAR) NOT NULL, unite_id NUMBER(10) NOT NULL CONSTRAINT fk_ref_param_unite REFERENCES ref_unite(unite_id), famille VARCHAR2(60 CHAR) NOT NULL, type_valeur VARCHAR2(20 CHAR) NOT NULL CONSTRAINT ck_ref_param_type CHECK (type_valeur IN ('NUMERIQUE','BOOLEEN','TEXTE')), min_physique NUMBER(12,4), max_physique NUMBER(12,4), est_derive CHAR(1) DEFAULT 'N' NOT NULL CONSTRAINT ck_ref_param_derive CHECK (est_derive IN ('O','N')), actif CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_param_actif CHECK (actif IN ('O','N')), CONSTRAINT ck_ref_param_physique CHECK (min_physique IS NULL OR max_physique IS NULL OR min_physique <= max_physique), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE ref_seuil_reglementaire ( seuil_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_seuil PRIMARY KEY, parametre_id NUMBER(10) NOT NULL CONSTRAINT fk_ref_seuil_param REFERENCES ref_parametre(parametre_id), seuil_min NUMBER(14,4), seuil_max NUMBER(14,4), date_effet DATE NOT NULL, date_fin DATE, portee VARCHAR2(20 CHAR) DEFAULT 'TOUS' NOT NULL CONSTRAINT ck_ref_seuil_portee CHECK (portee IN ('TOUS','SECTEUR','INDUSTRIEL')), secteur_id NUMBER(10) CONSTRAINT fk_ref_seuil_secteur REFERENCES ref_secteur_activite(secteur_id), industriel_id NUMBER(10) CONSTRAINT fk_ref_seuil_industriel REFERENCES industriel(industriel_id), article VARCHAR2(30 CHAR) NOT NULL, reference_texte VARCHAR2(200 CHAR) NOT NULL, actif CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_seuil_actif CHECK (actif IN ('O','N')), CONSTRAINT ck_ref_seuil_minmax CHECK (seuil_min IS NOT NULL OR seuil_max IS NOT NULL), CONSTRAINT ck_ref_seuil_ordre CHECK (seuil_min IS NULL OR seuil_max IS NULL OR seuil_min <= seuil_max), CONSTRAINT ck_ref_seuil_dates CHECK (date_fin IS NULL OR date_fin >= date_effet), CONSTRAINT ck_ref_seuil_portee2 CHECK ( (portee = 'TOUS' AND secteur_id IS NULL AND industriel_id IS NULL) OR (portee = 'SECTEUR' AND secteur_id IS NOT NULL AND industriel_id IS NULL) OR (portee = 'INDUSTRIEL' AND industriel_id IS NOT NULL)), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE UNIQUE INDEX uq_seuil_version ON ref_seuil_reglementaire (parametre_id, portee, date_effet, NVL(secteur_id,-1), NVL(industriel_id,-1)); -- Anti-chevauchement de periodes : a completer par COMPOUND TRIGGER ou -- validation applicative APEX (REGEXP / date_effet / date_fin). CREATE TABLE derogation_seuil ( derogation_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_derogation PRIMARY KEY, industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_derog_industriel REFERENCES industriel(industriel_id), parametre_id NUMBER(10) NOT NULL CONSTRAINT fk_derog_param REFERENCES ref_parametre(parametre_id), seuil_min_derog NUMBER(14,4), seuil_max_derog NUMBER(14,4), date_debut DATE NOT NULL, date_fin DATE NOT NULL, motif VARCHAR2(4000 CHAR) NOT NULL, approuve_par VARCHAR2(64 CHAR) NOT NULL, statut VARCHAR2(20 CHAR) NOT NULL CONSTRAINT ck_derog_statut CHECK (statut IN ('PROPOSEE','VALIDEE','REJETEE','EXPIREE')), CONSTRAINT ck_derog_dates CHECK (date_fin >= date_debut), CONSTRAINT ck_derog_minmax CHECK (seuil_min_derog IS NULL OR seuil_max_derog IS NULL OR seuil_min_derog <= seuil_max_derog), -- Controle pH-chaux (5,5 a 9,5 maximum) implemente par validation APEX / trigger -- car une contrainte CHECK Oracle ne peut pas contenir de sous-requete. cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); -- --------------------------------------------------------------------- -- 6. PRELEVEMENTS ET RESULTATS (SAISIE UNIQUE) -- --------------------------------------------------------------------- CREATE TABLE prelevement ( prelevement_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_prelevement PRIMARY KEY, industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_prelev_industriel REFERENCES industriel(industriel_id), numero_echantillon VARCHAR2(60 CHAR) NOT NULL CONSTRAINT uq_prelev_echantillon UNIQUE, date_prelevement TIMESTAMP(6) NOT NULL, point_prelevement VARCHAR2(120 CHAR) NOT NULL, type_prelevement VARCHAR2(40 CHAR) NOT NULL CONSTRAINT ck_prelev_type CHECK (type_prelevement IN ('REJET_INDUSTRIEL','EAU_USEE_DOMESTIQUE','EAU_PLUVIALE','CONTROLE_INOPINE','AUTRE')), laboratoire VARCHAR2(120 CHAR) NOT NULL, enquete_id NUMBER(10) CONSTRAINT fk_prelev_enquete REFERENCES enquete_controle(enquete_id), temperature_ambiante NUMBER(6,2), commentaire CLOB, -- Pas de date future : validation APEX / trigger (SYSTIMESTAMP n'est pas -- autorise dans une contrainte CHECK Oracle). cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE resultat_analyse ( resultat_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_resultat_analyse PRIMARY KEY, prelevement_id NUMBER(10) NOT NULL CONSTRAINT fk_resultat_prelevement REFERENCES prelevement(prelevement_id) ON DELETE CASCADE, parametre_id NUMBER(10) NOT NULL CONSTRAINT fk_resultat_parametre REFERENCES ref_parametre(parametre_id), valeur_num NUMBER(14,4), valeur_texte VARCHAR2(200 CHAR), valeur_bool CHAR(1) CONSTRAINT ck_resultat_bool CHECK (valeur_bool IS NULL OR valeur_bool IN ('0','1')), limite_detection NUMBER(14,4), methode_analyse VARCHAR2(100 CHAR), saisi_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, date_saisie TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, CONSTRAINT uq_resultat_param UNIQUE (prelevement_id, parametre_id), CONSTRAINT ck_resultat_valeur CHECK (valeur_num IS NOT NULL OR valeur_texte IS NOT NULL OR valeur_bool IS NOT NULL), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); -- --------------------------------------------------------------------- -- 7. CONFORMITE REGLEMENTAIRE, RECLAMATIONS, DOCUMENTS, ALERTES, AUDIT -- --------------------------------------------------------------------- CREATE TABLE ref_type_dossier ( type_dossier_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_type_dossier PRIMARY KEY, code_type VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_type_dossier UNIQUE, libelle VARCHAR2(120 CHAR) NOT NULL, renouvelable CHAR(1) DEFAULT 'N' NOT NULL CONSTRAINT ck_type_dossier_renouv CHECK (renouvelable IN ('O','N')), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE dossier_reglementaire ( dossier_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_dossier PRIMARY KEY, industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_dossier_industriel REFERENCES industriel(industriel_id), type_dossier_id NUMBER(10) NOT NULL CONSTRAINT fk_dossier_type REFERENCES ref_type_dossier(type_dossier_id), numero_dossier VARCHAR2(80 CHAR) NOT NULL, autorite VARCHAR2(150 CHAR), date_debut DATE NOT NULL, date_echeance DATE, statut VARCHAR2(30 CHAR) NOT NULL CONSTRAINT ck_dossier_statut CHECK (statut IN ('ACTIVE','VALIDEE','CLOTUREE','EXPIREE')), objet VARCHAR2(4000 CHAR), CONSTRAINT uq_dossier_num UNIQUE (type_dossier_id, numero_dossier), CONSTRAINT ck_dossier_dates CHECK (date_echeance IS NULL OR date_echeance >= date_debut), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE ref_type_reclamation ( type_reclamation_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_type_reclam PRIMARY KEY, code_type VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_type_reclam UNIQUE, libelle VARCHAR2(120 CHAR) NOT NULL, cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE reclamation_incident ( reclamation_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_reclamation PRIMARY KEY, industriel_id NUMBER(10) CONSTRAINT fk_reclam_industriel REFERENCES industriel(industriel_id), type_reclamation_id NUMBER(10) NOT NULL CONSTRAINT fk_reclam_type REFERENCES ref_type_reclamation(type_reclamation_id), date_ouverture TIMESTAMP(6) NOT NULL, date_cloture TIMESTAMP(6), statut VARCHAR2(30 CHAR) NOT NULL, gravite VARCHAR2(30 CHAR) NOT NULL, objet VARCHAR2(4000 CHAR) NOT NULL, delai_traitement_j NUMBER(8,2) GENERATED ALWAYS AS (CASE WHEN date_cloture IS NOT NULL THEN ROUND(CAST(date_cloture AS DATE) - CAST(date_ouverture AS DATE), 2) END) VIRTUAL, CONSTRAINT ck_reclam_dates CHECK (date_cloture IS NULL OR date_cloture >= date_ouverture), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE document ( document_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_document PRIMARY KEY, industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_document_industriel REFERENCES industriel(industriel_id), contexte VARCHAR2(30 CHAR) NOT NULL CONSTRAINT ck_document_contexte CHECK (contexte IN ('INDUSTRIEL','PRELEVEMENT','ENQUETE','RECLAMATION','DOSSIER')), source_id NUMBER(10) NOT NULL, type_document VARCHAR2(60 CHAR) NOT NULL, nom_fichier VARCHAR2(255 CHAR) NOT NULL, mime_type VARCHAR2(100 CHAR), contenu_blob BLOB, url_sharepoint VARCHAR2(500 CHAR), date_document DATE NOT NULL, CONSTRAINT ck_document_stockage CHECK (contenu_blob IS NOT NULL OR url_sharepoint IS NOT NULL), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE alerte_non_conformite ( alerte_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_alerte PRIMARY KEY, resultat_id NUMBER(10) CONSTRAINT fk_alerte_resultat REFERENCES resultat_analyse(resultat_id) ON DELETE SET NULL, prelevement_id NUMBER(10) NOT NULL CONSTRAINT fk_alerte_prelevement REFERENCES prelevement(prelevement_id), industriel_id NUMBER(10) NOT NULL CONSTRAINT fk_alerte_industriel REFERENCES industriel(industriel_id), parametre_id NUMBER(10) NOT NULL CONSTRAINT fk_alerte_parametre REFERENCES ref_parametre(parametre_id), valeur_mesuree NUMBER(14,4) NOT NULL, seuil_applique NUMBER(14,4) NOT NULL, depassement_pct NUMBER(10,2) DEFAULT 0 NOT NULL CONSTRAINT ck_alerte_pct CHECK (depassement_pct >= 0), date_alerte TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, gravite VARCHAR2(20 CHAR) NOT NULL CONSTRAINT ck_alerte_gravite CHECK (gravite IN ('MOYENNE','ELEVEE','CRITIQUE')), statut VARCHAR2(20 CHAR) DEFAULT 'NOUVELLE' NOT NULL CONSTRAINT ck_alerte_statut CHECK (statut IN ('NOUVELLE','NOTIFIEE','TRAITEE','CLOTUREE','LEVEE')), notifie_le TIMESTAMP(6) WITH TIME ZONE, CONSTRAINT uq_alerte_resultat UNIQUE (resultat_id), cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); CREATE TABLE journal_maj ( journal_id NUMBER(18) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_journal PRIMARY KEY, date_action TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, utilisateur VARCHAR2(64 CHAR) NOT NULL, action VARCHAR2(40 CHAR) NOT NULL, entite VARCHAR2(60 CHAR) NOT NULL, identifiant_ligne VARCHAR2(80 CHAR), detail CLOB, cree_par VARCHAR2(64 CHAR) DEFAULT USER NOT NULL, cree_le TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL, modifie_par VARCHAR2(64 CHAR), modifie_le TIMESTAMP(6) WITH TIME ZONE, date_archivage DATE ); -- --------------------------------------------------------------------- -- 8. INDEX METIER -- --------------------------------------------------------------------- CREATE INDEX idx_industriel_recherche ON industriel (province_id, secteur_id, statut, date_archivage); CREATE INDEX idx_prelev_ind_date ON prelevement (industriel_id, date_prelevement DESC); CREATE INDEX idx_resultat_prelev ON resultat_analyse (prelevement_id); CREATE INDEX idx_resultat_param ON resultat_analyse (parametre_id); CREATE INDEX idx_seuil_param_date ON ref_seuil_reglementaire (parametre_id, date_effet DESC, date_fin); CREATE INDEX idx_enquete_ind_date ON enquete_controle (industriel_id, date_visite DESC); CREATE INDEX idx_reclam_ouverture ON reclamation_incident (date_ouverture, date_cloture); CREATE INDEX idx_releve_date ON releve_compteur (date_releve); CREATE INDEX idx_dossier_statut ON dossier_reglementaire (type_dossier_id, statut, date_echeance); CREATE INDEX idx_alerte_statut ON alerte_non_conformite (statut, date_alerte DESC); -- --------------------------------------------------------------------- -- 9. DONNEES DE REFERENCE -- --------------------------------------------------------------------- INSERT ALL INTO ref_province (province_id, code_province, libelle) VALUES (1,'RAB','Rabat') INTO ref_province (province_id, code_province, libelle) VALUES (2,'SAL','Sale') INTO ref_province (province_id, code_province, libelle) VALUES (3,'SKT','Skhirat-Temara') INTO ref_province (province_id, code_province, libelle) VALUES (4,'KEN','Kenitra') INTO ref_province (province_id, code_province, libelle) VALUES (5,'KHE','Khemisset') INTO ref_province (province_id, code_province, libelle) VALUES (6,'SIK','Sidi Kacem') INTO ref_province (province_id, code_province, libelle) VALUES (7,'SIS','Sidi Slimane') SELECT * FROM DUAL; __DIRECTION_INSERTS__ INSERT ALL INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (1,'TEXTILE','Textile') INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (2,'CHIMIE','Chimie') INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (3,'AGRO','Agroalimentaire') INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (4,'BOULANGERIE','Boulangerie') INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (5,'TANNERIE','Tannerie') INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (6,'PAPIER','Papier / Carton') INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (7,'METALLURGIE','Metallurgie') INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (8,'AUTRE','Autre') SELECT * FROM DUAL; INSERT ALL INTO ref_unite (unite_id, code_unite, libelle) VALUES (1,'mg/L','Milligramme par litre') INTO ref_unite (unite_id, code_unite, libelle) VALUES (2,'-','Sans unite / pH') INTO ref_unite (unite_id, code_unite, libelle) VALUES (3,'C','Degre Celsius') INTO ref_unite (unite_id, code_unite, libelle) VALUES (4,'uS/cm','Microsiemens par centimetre') INTO ref_unite (unite_id, code_unite, libelle) VALUES (5,'0/1','Booleen 0/1') INTO ref_unite (unite_id, code_unite, libelle) VALUES (6,'m3','Metre cube') SELECT * FROM DUAL; INSERT ALL INTO ref_liste (code_liste, libelle, modifiable) VALUES ('STATUT_INDUSTRIEL','Statuts industriels','N') INTO ref_liste (code_liste, libelle, modifiable) VALUES ('GRAVITE','Gravites','N') INTO ref_liste (code_liste, libelle, modifiable) VALUES ('FREQUENCE_MAJ','Frequences de mise a jour','N') INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('STATUT_INDUSTRIEL','ACTIF','Actif',1) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('STATUT_INDUSTRIEL','ARRET','Arret',2) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('STATUT_INDUSTRIEL','A_VERIFIER','A verifier',3) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('GRAVITE','FAIBLE','Faible',1) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('GRAVITE','MOYENNE','Moyenne',2) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('GRAVITE','ELEVEE','Elevee',3) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('FREQUENCE_MAJ','MENSUELLE','Mensuelle',1) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('FREQUENCE_MAJ','TRIMESTRIELLE','Trimestrielle',2) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('FREQUENCE_MAJ','ANNUELLE','Annuelle',3) INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('FREQUENCE_MAJ','CONTINUE','Continue',4) SELECT * FROM DUAL; INSERT ALL INTO ref_type_branchement (type_branchement_id, code_type, libelle) VALUES (1,'EAU_USEE_DOMESTIQUE','Eaux usees domestiques') INTO ref_type_branchement (type_branchement_id, code_type, libelle) VALUES (2,'EAU_PLUVIALE','Eaux pluviales - reseau separatif') INTO ref_type_branchement (type_branchement_id, code_type, libelle) VALUES (3,'EAU_INDUSTRIELLE','Eaux usees industrielles') INTO ref_type_visite (type_visite_id, code_type, libelle) VALUES (1,'ENQUETE','Enquete terrain') INTO ref_type_visite (type_visite_id, code_type, libelle) VALUES (2,'CONTROLE','Controle inopine') INTO ref_type_visite (type_visite_id, code_type, libelle) VALUES (3,'SUIVI','Visite de suivi') INTO ref_type_dossier (type_dossier_id, code_type, libelle, renouvelable) VALUES (1,'MED','Mise en demeure','N') INTO ref_type_dossier (type_dossier_id, code_type, libelle, renouvelable) VALUES (2,'EIE','Etude d''impact environnemental','O') INTO ref_type_dossier (type_dossier_id, code_type, libelle, renouvelable) VALUES (3,'AUTORISATION','Autorisation / convention de deversement','O') INTO ref_type_dossier (type_dossier_id, code_type, libelle, renouvelable) VALUES (4,'CERTIFICATION','Certification','O') INTO ref_type_reclamation (type_reclamation_id, code_type, libelle) VALUES (1,'RECLAMATION','Reclamation') INTO ref_type_reclamation (type_reclamation_id, code_type, libelle) VALUES (2,'INCIDENT','Incident environnemental') INTO app_role (role_id, code_role, libelle) VALUES (1,'ADMIN','Administrateur') INTO app_role (role_id, code_role, libelle) VALUES (2,'LABO','Laboratoire SRM-RSK') INTO app_role (role_id, code_role, libelle) VALUES (3,'ENV','Cellule Environnement') INTO app_role (role_id, code_role, libelle) VALUES (4,'SIG','Service SIG') INTO app_role (role_id, code_role, libelle) VALUES (5,'CLIENTELE','Service Clientele') INTO app_role (role_id, code_role, libelle) VALUES (6,'ENQUETE','Equipe Enquetes') INTO app_role (role_id, code_role, libelle) VALUES (7,'REPORTING','Division reporting / Direction') SELECT * FROM DUAL; __PARAM_INSERTS__ __SEUIL_INSERTS__ -- --------------------------------------------------------------------- -- 10. PACKAGE PL/SQL DE CONFORMITE -- --------------------------------------------------------------------- CREATE OR REPLACE PACKAGE srm_pkg_conformite AS FUNCTION get_seuil_min(p_parametre_id IN NUMBER, p_industriel_id IN NUMBER, p_date IN DATE) RETURN NUMBER; FUNCTION get_seuil_max(p_parametre_id IN NUMBER, p_industriel_id IN NUMBER, p_date IN DATE) RETURN NUMBER; FUNCTION statut_reglementaire(p_valeur IN NUMBER, p_min IN NUMBER, p_max IN NUMBER) RETURN VARCHAR2 DETERMINISTIC; FUNCTION niveau_vigilance(p_valeur IN NUMBER, p_min IN NUMBER, p_max IN NUMBER) RETURN VARCHAR2 DETERMINISTIC; FUNCTION formater_code_industriel(p_code_dp IN VARCHAR2, p_ordre IN NUMBER) RETURN VARCHAR2 DETERMINISTIC; PROCEDURE reserver_ordre_direction(p_code_dp IN VARCHAR2, p_ordre OUT NUMBER); PROCEDURE journaliser(p_action IN VARCHAR2, p_entite IN VARCHAR2, p_id IN VARCHAR2, p_detail IN VARCHAR2); END srm_pkg_conformite; / CREATE OR REPLACE PACKAGE BODY srm_pkg_conformite AS FUNCTION get_seuil_min(p_parametre_id IN NUMBER, p_industriel_id IN NUMBER, p_date IN DATE) RETURN NUMBER IS v_val NUMBER; BEGIN BEGIN SELECT d.seuil_min_derog INTO v_val FROM derogation_seuil d WHERE d.parametre_id = p_parametre_id AND d.industriel_id = p_industriel_id AND d.statut = 'VALIDEE' AND TRUNC(p_date) BETWEEN d.date_debut AND d.date_fin ORDER BY d.date_debut DESC, d.derogation_id DESC FETCH FIRST 1 ROW ONLY; RETURN v_val; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; SELECT s.seuil_min INTO v_val FROM ( SELECT s.seuil_min FROM ref_seuil_reglementaire s WHERE s.parametre_id = p_parametre_id AND s.actif = 'O' AND TRUNC(p_date) >= s.date_effet AND (s.date_fin IS NULL OR TRUNC(p_date) <= s.date_fin) AND (s.portee = 'TOUS' OR (s.portee = 'SECTEUR' AND s.secteur_id = (SELECT i.secteur_id FROM industriel i WHERE i.industriel_id = p_industriel_id)) OR (s.portee = 'INDUSTRIEL' AND s.industriel_id = p_industriel_id)) ORDER BY CASE s.portee WHEN 'INDUSTRIEL' THEN 1 WHEN 'SECTEUR' THEN 2 ELSE 3 END, s.date_effet DESC, s.seuil_id DESC ) WHERE ROWNUM = 1; RETURN v_val; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END get_seuil_min; FUNCTION get_seuil_max(p_parametre_id IN NUMBER, p_industriel_id IN NUMBER, p_date IN DATE) RETURN NUMBER IS v_val NUMBER; BEGIN BEGIN SELECT d.seuil_max_derog INTO v_val FROM derogation_seuil d WHERE d.parametre_id = p_parametre_id AND d.industriel_id = p_industriel_id AND d.statut = 'VALIDEE' AND TRUNC(p_date) BETWEEN d.date_debut AND d.date_fin ORDER BY d.date_debut DESC, d.derogation_id DESC FETCH FIRST 1 ROW ONLY; RETURN v_val; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; SELECT s.seuil_max INTO v_val FROM ( SELECT s.seuil_max FROM ref_seuil_reglementaire s WHERE s.parametre_id = p_parametre_id AND s.actif = 'O' AND TRUNC(p_date) >= s.date_effet AND (s.date_fin IS NULL OR TRUNC(p_date) <= s.date_fin) AND (s.portee = 'TOUS' OR (s.portee = 'SECTEUR' AND s.secteur_id = (SELECT i.secteur_id FROM industriel i WHERE i.industriel_id = p_industriel_id)) OR (s.portee = 'INDUSTRIEL' AND s.industriel_id = p_industriel_id)) ORDER BY CASE s.portee WHEN 'INDUSTRIEL' THEN 1 WHEN 'SECTEUR' THEN 2 ELSE 3 END, s.date_effet DESC, s.seuil_id DESC ) WHERE ROWNUM = 1; RETURN v_val; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END get_seuil_max; FUNCTION statut_reglementaire(p_valeur IN NUMBER, p_min IN NUMBER, p_max IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS BEGIN IF p_valeur IS NULL THEN RETURN 'NON_ANALYSE'; END IF; IF (p_min IS NOT NULL AND p_valeur < p_min) OR (p_max IS NOT NULL AND p_valeur > p_max) THEN RETURN 'NON_CONFORME'; END IF; RETURN 'CONFORME'; END statut_reglementaire; FUNCTION niveau_vigilance(p_valeur IN NUMBER, p_min IN NUMBER, p_max IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS v_span NUMBER; BEGIN IF p_valeur IS NULL THEN RETURN 'NORMAL'; END IF; IF (p_min IS NOT NULL AND p_valeur < p_min) OR (p_max IS NOT NULL AND p_valeur > p_max) THEN RETURN 'NON_CONFORME'; END IF; IF p_min IS NOT NULL AND p_max IS NOT NULL AND p_max > p_min THEN v_span := p_max - p_min; IF p_valeur < p_min + v_span * 0.10 OR p_valeur > p_max - v_span * 0.10 THEN RETURN 'A_SURVEILLER'; END IF; ELSIF p_max IS NOT NULL AND p_max > 0 AND p_valeur >= p_max * 0.80 THEN RETURN 'A_SURVEILLER'; END IF; RETURN 'NORMAL'; END niveau_vigilance; FUNCTION formater_code_industriel(p_code_dp IN VARCHAR2, p_ordre IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS BEGIN RETURN 'IN' || p_code_dp || '-' || TO_CHAR(p_ordre, 'FM0000'); END formater_code_industriel; PROCEDURE reserver_ordre_direction(p_code_dp IN VARCHAR2, p_ordre OUT NUMBER) IS v_ordre NUMBER; BEGIN SELECT dernier_ordre + 1 INTO v_ordre FROM direction WHERE code_dp = p_code_dp FOR UPDATE; UPDATE direction SET dernier_ordre = v_ordre, modifie_par = USER, modifie_le = SYSTIMESTAMP WHERE code_dp = p_code_dp; p_ordre := v_ordre; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20012, 'Direction provinciale / prefectorale inconnue : ' || p_code_dp); END reserver_ordre_direction; PROCEDURE journaliser(p_action IN VARCHAR2, p_entite IN VARCHAR2, p_id IN VARCHAR2, p_detail IN VARCHAR2) IS BEGIN INSERT INTO journal_maj (utilisateur, action, entite, identifiant_ligne, detail) VALUES (NVL(SYS_CONTEXT('APEX$SESSION','APP_USER'), SYS_CONTEXT('USERENV','SESSION_USER')), p_action, p_entite, p_id, p_detail); END journaliser; END srm_pkg_conformite; / -- --------------------------------------------------------------------- -- 11. VUES CALCULEES - AUCUNE CONFORMITE / KPI SAISIE MANUELLEMENT -- --------------------------------------------------------------------- CREATE OR REPLACE VIEW v_total_metaux AS SELECT r.prelevement_id, p.industriel_id, p.date_prelevement, SUM(r.valeur_num) valeur_totale FROM resultat_analyse r JOIN prelevement p ON p.prelevement_id = r.prelevement_id JOIN ref_parametre rp ON rp.parametre_id = r.parametre_id WHERE rp.code_parametre IN ('CD','CU','ZN','CR6','NI','FE') GROUP BY r.prelevement_id, p.industriel_id, p.date_prelevement; CREATE OR REPLACE VIEW v_resultat_conformite AS WITH base AS ( SELECT r.resultat_id, r.prelevement_id, p.industriel_id, p.date_prelevement, r.parametre_id, rp.code_parametre, rp.libelle, r.valeur_num, ru.code_unite, srm_pkg_conformite.get_seuil_min(r.parametre_id, p.industriel_id, CAST(p.date_prelevement AS DATE)) seuil_min, srm_pkg_conformite.get_seuil_max(r.parametre_id, p.industriel_id, CAST(p.date_prelevement AS DATE)) seuil_max FROM resultat_analyse r JOIN prelevement p ON p.prelevement_id = r.prelevement_id JOIN ref_parametre rp ON rp.parametre_id = r.parametre_id JOIN ref_unite ru ON ru.unite_id = rp.unite_id WHERE r.date_archivage IS NULL AND p.date_archivage IS NULL ), calculee AS ( SELECT b.*, srm_pkg_conformite.statut_reglementaire(b.valeur_num, b.seuil_min, b.seuil_max) statut_resultat, srm_pkg_conformite.niveau_vigilance(b.valeur_num, b.seuil_min, b.seuil_max) niveau_vigilance, CASE WHEN b.seuil_max IS NOT NULL AND b.seuil_max > 0 AND b.valeur_num > b.seuil_max THEN ROUND((b.valeur_num / b.seuil_max - 1) * 100, 2) WHEN b.seuil_min IS NOT NULL AND b.seuil_min > 0 AND b.valeur_num < b.seuil_min THEN ROUND((b.seuil_min - b.valeur_num) / b.seuil_min * 100, 2) ELSE 0 END depassement_pct, 'N' est_derive FROM base b ), total AS ( SELECT t.prelevement_id, t.industriel_id, t.date_prelevement, rp.parametre_id, rp.libelle, t.valeur_totale, ru.code_unite FROM v_total_metaux t JOIN ref_parametre rp ON rp.code_parametre = 'TOTAL_METAUX' JOIN ref_unite ru ON ru.unite_id = rp.unite_id ) SELECT * FROM calculee UNION ALL SELECT NULL resultat_id, t.prelevement_id, t.industriel_id, t.date_prelevement, t.parametre_id, 'TOTAL_METAUX' code_parametre, t.libelle, t.valeur_totale valeur_num, t.code_unite, NULL seuil_min, 15 seuil_max, srm_pkg_conformite.statut_reglementaire(t.valeur_totale, NULL, 15) statut_resultat, srm_pkg_conformite.niveau_vigilance(t.valeur_totale, NULL, 15) niveau_vigilance, CASE WHEN t.valeur_totale > 15 THEN ROUND((t.valeur_totale / 15 - 1) * 100, 2) ELSE 0 END depassement_pct, 'O' est_derive FROM total t; CREATE OR REPLACE VIEW v_prelevement_conformite AS SELECT p.prelevement_id, p.industriel_id, p.numero_echantillon, p.date_prelevement, p.point_prelevement, p.laboratoire, p.type_prelevement, COUNT(v.prelevement_id) nb_resultats, SUM(CASE WHEN v.statut_resultat = 'NON_CONFORME' THEN 1 ELSE 0 END) nb_depassements, SUM(CASE WHEN v.niveau_vigilance = 'A_SURVEILLER' THEN 1 ELSE 0 END) nb_vigilance, CASE WHEN SUM(CASE WHEN v.statut_resultat = 'NON_CONFORME' THEN 1 ELSE 0 END) > 0 THEN 'NON_CONFORME' WHEN COUNT(v.prelevement_id) > 0 THEN 'CONFORME' ELSE 'NON_ANALYSE' END statut_prelevement, CASE WHEN SUM(CASE WHEN v.statut_resultat = 'NON_CONFORME' THEN 1 ELSE 0 END) > 0 THEN 'NON_CONFORME' WHEN SUM(CASE WHEN v.niveau_vigilance = 'A_SURVEILLER' THEN 1 ELSE 0 END) > 0 THEN 'A_SURVEILLER' ELSE 'NORMAL' END niveau_vigilance FROM prelevement p LEFT JOIN v_resultat_conformite v ON v.prelevement_id = p.prelevement_id WHERE p.date_archivage IS NULL GROUP BY p.prelevement_id, p.industriel_id, p.numero_echantillon, p.date_prelevement, p.point_prelevement, p.laboratoire, p.type_prelevement; CREATE OR REPLACE VIEW v_dernier_statut_industriel AS SELECT * FROM ( SELECT i.industriel_id, i.code_industriel, i.ancien_code, i.direction_id, i.code_dp, i.ordre_dp, d.libelle direction_libelle, i.raison_sociale, i.province_id, i.secteur_id, i.zone_id, i.statut statut_exploitation, i.gps_lat, i.gps_lon, v.prelevement_id dernier_prelevement_id, v.date_prelevement date_dernier_controle, v.statut_prelevement, v.niveau_vigilance, v.nb_depassements, CASE WHEN v.statut_prelevement = 'NON_CONFORME' THEN 'NON_CONFORME' WHEN v.niveau_vigilance = 'A_SURVEILLER' THEN 'A_SURVEILLER' WHEN v.statut_prelevement = 'CONFORME' THEN 'CONFORME' ELSE 'NON_CONTROLE' END statut_carte, ROW_NUMBER() OVER (PARTITION BY i.industriel_id ORDER BY v.date_prelevement DESC NULLS LAST, v.prelevement_id DESC NULLS LAST) rn FROM industriel i LEFT JOIN direction d ON d.code_dp = i.direction_id LEFT JOIN v_prelevement_conformite v ON v.industriel_id = i.industriel_id WHERE i.date_archivage IS NULL ) WHERE rn = 1; CREATE OR REPLACE VIEW v_kpi_general AS SELECT (SELECT COUNT(*) FROM industriel WHERE date_archivage IS NULL) industriels_recenses, (SELECT COUNT(*) FROM v_prelevement_conformite WHERE date_prelevement >= TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY') AND date_prelevement < ADD_MONTHS(TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY'),12)) prelevements_annee, ROUND(100 * (SELECT COUNT(*) FROM v_prelevement_conformite WHERE statut_prelevement = 'CONFORME' AND date_prelevement >= TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY') AND date_prelevement < ADD_MONTHS(TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY'),12)) / NULLIF((SELECT COUNT(*) FROM v_prelevement_conformite WHERE statut_prelevement <> 'NON_ANALYSE' AND date_prelevement >= TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY') AND date_prelevement < ADD_MONTHS(TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY'),12)),0),1) taux_conformite, ROUND(100 * (SELECT COUNT(DISTINCT i.industriel_id) FROM industriel i JOIN enquete_controle e ON e.industriel_id = i.industriel_id WHERE i.statut = 'Actif' AND i.date_archivage IS NULL AND e.date_visite >= TRUNC(SYSDATE,'YYYY')) / NULLIF((SELECT COUNT(*) FROM industriel WHERE statut = 'Actif' AND date_archivage IS NULL),0),1) couverture_enquetes, (SELECT COUNT(*) FROM dossier_reglementaire d JOIN ref_type_dossier t ON t.type_dossier_id = d.type_dossier_id WHERE t.code_type = 'MED' AND d.statut = 'ACTIVE' AND (d.date_echeance IS NULL OR d.date_echeance >= SYSDATE)) med_actives, (SELECT NVL(SUM(r.volume_m3),0) FROM releve_compteur r WHERE r.date_releve >= TRUNC(SYSDATE,'YYYY')) conso_annee_m3, ROUND((SELECT AVG(CAST(r.date_cloture AS DATE) - CAST(r.date_ouverture AS DATE)) FROM reclamation_incident r WHERE r.date_cloture IS NOT NULL AND r.date_ouverture >= TRUNC(SYSDATE,'YYYY')),1) delai_moyen_reclamation_j, (SELECT COUNT(*) FROM v_resultat_conformite WHERE statut_resultat = 'NON_CONFORME' AND date_prelevement >= TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY')) nb_depassements FROM DUAL; CREATE OR REPLACE VIEW v_kpi_mensuel AS SELECT TRUNC(CAST(date_prelevement AS DATE),'MM') mois, COUNT(*) nb_prelevements, SUM(nb_resultats) nb_resultats, SUM(nb_depassements) nb_depassements, SUM(CASE WHEN statut_prelevement = 'CONFORME' THEN 1 ELSE 0 END) nb_conformes, ROUND(100 * SUM(CASE WHEN statut_prelevement = 'CONFORME' THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN statut_prelevement <> 'NON_ANALYSE' THEN 1 ELSE 0 END),0),1) taux_conformite FROM v_prelevement_conformite GROUP BY TRUNC(CAST(date_prelevement AS DATE),'MM'); CREATE MATERIALIZED VIEW mv_kpi_mensuel BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT * FROM v_kpi_mensuel; CREATE OR REPLACE VIEW v_alertes_actives AS SELECT a.alerte_id, a.date_alerte, i.code_industriel, i.raison_sociale, rp.code_parametre, a.valeur_mesuree, a.seuil_applique, a.depassement_pct, a.gravite, a.statut, rp.article FROM alerte_non_conformite a JOIN industriel i ON i.industriel_id = a.industriel_id JOIN ref_parametre rp ON rp.parametre_id = a.parametre_id WHERE a.statut NOT IN ('CLOTUREE','LEVEE') AND a.date_archivage IS NULL; -- --------------------------------------------------------------------- -- 12. TRIGGERS : ALERTES, AUDIT, APPEND-ONLY -- --------------------------------------------------------------------- CREATE OR REPLACE TRIGGER trg_resultat_alerte AFTER INSERT OR UPDATE OF valeur_num, valeur_bool ON resultat_analyse FOR EACH ROW DECLARE v_industriel_id NUMBER; v_date DATE; v_min NUMBER; v_max NUMBER; v_valeur NUMBER; v_statut VARCHAR2(20); v_seuil NUMBER; v_pct NUMBER; v_gravite VARCHAR2(20); BEGIN SELECT p.industriel_id, CAST(p.date_prelevement AS DATE) INTO v_industriel_id, v_date FROM prelevement p WHERE p.prelevement_id = :NEW.prelevement_id; v_valeur := COALESCE(:NEW.valeur_num, TO_NUMBER(:NEW.valeur_bool)); -- Les controles booleens utilisent 0/1 ; l'absence de valeur numerique est ignoree ici. IF v_valeur IS NULL THEN RETURN; END IF; v_min := srm_pkg_conformite.get_seuil_min(:NEW.parametre_id, v_industriel_id, v_date); v_max := srm_pkg_conformite.get_seuil_max(:NEW.parametre_id, v_industriel_id, v_date); v_statut := srm_pkg_conformite.statut_reglementaire(v_valeur, v_min, v_max); IF v_statut = 'NON_CONFORME' THEN v_seuil := CASE WHEN v_max IS NOT NULL AND v_valeur > v_max THEN v_max ELSE v_min END; v_pct := CASE WHEN v_max IS NOT NULL AND v_max > 0 AND v_valeur > v_max THEN ROUND((v_valeur / v_max - 1) * 100, 2) WHEN v_min IS NOT NULL AND v_min > 0 AND v_valeur < v_min THEN ROUND((v_min - v_valeur) / v_min * 100, 2) ELSE 0 END; v_gravite := CASE WHEN v_pct >= 100 THEN 'CRITIQUE' WHEN v_pct >= 50 THEN 'ELEVEE' ELSE 'MOYENNE' END; MERGE INTO alerte_non_conformite a USING (SELECT :NEW.resultat_id resultat_id, :NEW.prelevement_id prelevement_id, v_industriel_id industriel_id, :NEW.parametre_id parametre_id, v_valeur valeur_mesuree, NVL(v_seuil,0) seuil_applique, v_pct depassement_pct, v_gravite gravite FROM DUAL) s ON (a.resultat_id = s.resultat_id) WHEN MATCHED THEN UPDATE SET a.valeur_mesuree = s.valeur_mesuree, a.seuil_applique = s.seuil_applique, a.depassement_pct = s.depassement_pct, a.gravite = s.gravite, a.statut = 'NOUVELLE', a.modifie_par = USER, a.modifie_le = SYSTIMESTAMP WHEN NOT MATCHED THEN INSERT (resultat_id, prelevement_id, industriel_id, parametre_id, valeur_mesuree, seuil_applique, depassement_pct, gravite, statut) VALUES (s.resultat_id, s.prelevement_id, s.industriel_id, s.parametre_id, s.valeur_mesuree, s.seuil_applique, s.depassement_pct, s.gravite, 'NOUVELLE'); ELSE UPDATE alerte_non_conformite SET statut = 'LEVEE', modifie_par = USER, modifie_le = SYSTIMESTAMP WHERE resultat_id = :NEW.resultat_id AND statut NOT IN ('CLOTUREE','LEVEE'); END IF; UPDATE prelevement SET modifie_par = USER, modifie_le = SYSTIMESTAMP WHERE prelevement_id = :NEW.prelevement_id; END; / CREATE OR REPLACE TRIGGER trg_journal_append_only BEFORE UPDATE OR DELETE ON journal_maj FOR EACH ROW BEGIN RAISE_APPLICATION_ERROR(-20001, 'JOURNAL_MAJ est append-only : modification interdite.'); END; / CREATE OR REPLACE TRIGGER trg_industriel_code BEFORE INSERT ON industriel FOR EACH ROW DECLARE v_ordre NUMBER; BEGIN IF :NEW.direction_id IS NULL THEN BEGIN SELECT d.code_dp INTO :NEW.direction_id FROM direction d WHERE d.province_id = :NEW.province_id AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20011, 'Aucune direction provinciale / prefectorale pour cette province'); END; END IF; srm_pkg_conformite.reserver_ordre_direction(:NEW.direction_id, v_ordre); :NEW.ordre_dp := v_ordre; :NEW.code_dp := :NEW.direction_id; :NEW.code_industriel := srm_pkg_conformite.formater_code_industriel(:NEW.direction_id, v_ordre); END; / CREATE OR REPLACE TRIGGER trg_industriel_audit AFTER INSERT OR UPDATE OR DELETE ON industriel FOR EACH ROW BEGIN IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','INDUSTRIEL',TO_CHAR(:NEW.industriel_id),'Fiche industriel creee : '||:NEW.code_industriel); ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','INDUSTRIEL',TO_CHAR(:NEW.industriel_id),'Fiche modifiee'); ELSE srm_pkg_conformite.journaliser('DELETE','INDUSTRIEL',TO_CHAR(:OLD.industriel_id),'Fiche supprimee'); END IF; END; / CREATE OR REPLACE TRIGGER trg_prelevement_audit AFTER INSERT OR UPDATE OR DELETE ON prelevement FOR EACH ROW BEGIN IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','PRELEVEMENT',TO_CHAR(:NEW.prelevement_id),'Echantillon '||:NEW.numero_echantillon); ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','PRELEVEMENT',TO_CHAR(:NEW.prelevement_id),'Prelevement modifie'); ELSE srm_pkg_conformite.journaliser('DELETE','PRELEVEMENT',TO_CHAR(:OLD.prelevement_id),'Prelevement supprime'); END IF; END; / CREATE OR REPLACE TRIGGER trg_resultat_audit AFTER INSERT OR UPDATE OR DELETE ON resultat_analyse FOR EACH ROW BEGIN IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','RESULTAT_ANALYSE',TO_CHAR(:NEW.resultat_id),'Resultat brut saisi'); ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','RESULTAT_ANALYSE',TO_CHAR(:NEW.resultat_id),'Resultat corrige'); ELSE srm_pkg_conformite.journaliser('DELETE','RESULTAT_ANALYSE',TO_CHAR(:OLD.resultat_id),'Resultat supprime'); END IF; END; / CREATE OR REPLACE TRIGGER trg_seuil_audit AFTER INSERT OR UPDATE OR DELETE ON ref_seuil_reglementaire FOR EACH ROW BEGIN IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','REF_SEUIL_REGLEMENTAIRE',TO_CHAR(:NEW.seuil_id),'Nouvelle version de seuil'); ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','REF_SEUIL_REGLEMENTAIRE',TO_CHAR(:NEW.seuil_id),'Seuil modifie - verification de version obligatoire'); ELSE srm_pkg_conformite.journaliser('DELETE','REF_SEUIL_REGLEMENTAIRE',TO_CHAR(:OLD.seuil_id),'Seuil supprime'); END IF; END; / CREATE OR REPLACE TRIGGER trg_derogation_audit AFTER INSERT OR UPDATE OR DELETE ON derogation_seuil FOR EACH ROW BEGIN IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','DEROGATION_SEUIL',TO_CHAR(:NEW.derogation_id),'Derogation proposee'); ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','DEROGATION_SEUIL',TO_CHAR(:NEW.derogation_id),'Statut derogation : '||:NEW.statut); ELSE srm_pkg_conformite.journaliser('DELETE','DEROGATION_SEUIL',TO_CHAR(:OLD.derogation_id),'Derogation supprimee'); END IF; END; / -- --------------------------------------------------------------------- -- 13. AUTOMATION : rafraichissement quotidien de la MV KPI -- --------------------------------------------------------------------- BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'SRM_REFRESH_MV_KPI', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_MVIEW.REFRESH(''MV_KPI_MENSUEL'', ''C''); END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0', enabled => TRUE, comments => 'Rafraichissement quotidien des KPI mensuels SRM-RSK'); EXCEPTION WHEN OTHERS THEN IF SQLCODE != -27477 THEN RAISE; END IF; -- job deja existant END; / COMMENT ON TABLE direction IS 'Directions provinciales / prefectorales et compteur de numerotation IN + CODE_DP + NNNN.'; COMMENT ON COLUMN industriel.code_industriel IS 'Genere par TRG_INDUSTRIEL_CODE : IN + CODE_DP + ordre. Ancien code conserve dans ANCIEN_CODE.'; COMMENT ON TABLE resultat_analyse IS 'Saisie unique des valeurs brutes. Aucun champ de conformite ou de KPI.'; COMMENT ON TABLE ref_seuil_reglementaire IS 'Valeurs limites versionnees du cahier des charges (Art. 42 et 44).'; COMMENT ON TABLE alerte_non_conformite IS 'Alertes generees par trigger, jamais saisies manuellement.'; COMMENT ON TABLE journal_maj IS 'Journal MAJ automatique, append-only (onglet 11 du canevas).'; PROMPT SRM-RSK schema created. Next: APEX application, authorization schemes and Automations email. """ def build_inserts(): params = SEED["parametres"] lines = ["INSERT ALL"] for i, p in enumerate(params, 1): unite = p["unite"] unite_code = {"mg/L":"mg/L", "-":"-", "°C":"C", "µS/cm":"uS/cm", "0/1":"0/1", "m3":"m3"}.get(unite, "mg/L") type_valeur = "BOOLEEN" if unite == "0/1" else "NUMERIQUE" if p["code"].startswith("PH"): minp, maxp = "0", "14" elif p["code"] == "TEMPERATURE": minp, maxp = "-10", "100" elif unite == "0/1": minp, maxp = "0", "1" else: minp, maxp = "0", "1000000000" lines.append( " INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) " f"VALUES ({i}, {q(p['code'])}, {q(p['libelle'])}, (SELECT unite_id FROM ref_unite WHERE code_unite = {q(unite_code)}), " f"{q(p['famille'])}, {q(type_valeur)}, {minp}, {maxp}, {q('O' if p['derived'] else 'N')}, {q('O' if p['actif'] else 'N')})" ) lines.append("SELECT * FROM DUAL;") param_sql = "\n".join(lines) active = [p for p in params if p["actif"]] parts = [] for i, p in enumerate(active, 1): parts.append( "SELECT " + ", ".join([ str(i), f"(SELECT parametre_id FROM ref_parametre WHERE code_parametre = {q(p['code'])})", num(p["min"]), num(p["max"]), "DATE '2020-01-01'", "NULL", "'TOUS'", "NULL", "NULL", q(p["article"]), q(p["source"]), "'O'", "USER", "SYSTIMESTAMP" ]) + " FROM DUAL" ) seuil_sql = ("INSERT INTO ref_seuil_reglementaire\n" " (seuil_id, parametre_id, seuil_min, seuil_max, date_effet, date_fin, portee,\n" " secteur_id, industriel_id, article, reference_texte, actif, cree_par, cree_le)\n" + "\nUNION ALL\n".join(parts) + ";\n") # Directions provinciales / prefectorales : compteur initialise a 0. # Le jeu de demonstration (12 industriels) est fourni par seed_data.json / SQLite. dir_parts = [] province_by_code = { "DPR": 1, "DPS": 2, "DPST": 3, "DPK": 4, "DPKH": 5, "DPSK": 6, "DPSS": 7 } for d in SEED["directions"]: dir_parts.append( " INTO direction (code_dp, libelle, province_id, ordre_affichage, dernier_ordre) VALUES " f"({q(d['code_dp'])}, {q(d['libelle'])}, {province_by_code[d['code_dp']]}, {d['ordre']}, 0)" ) direction_sql = "INSERT ALL\n" + "\n".join(dir_parts) + "\nSELECT * FROM DUAL;\n" return param_sql, seuil_sql, direction_sql param_sql, seuil_sql, direction_sql = build_inserts() sql = (DDL.replace("__PARAM_INSERTS__", param_sql) .replace("__SEUIL_INSERTS__", seuil_sql) .replace("__DIRECTION_INSERTS__", direction_sql)) OUT.write_text(sql, encoding="utf-8") print(f"Wrote {OUT} ({len(sql):,} characters, {len(SEED['parametres'])} parametres, {len(SEED['directions'])} directions, 32 tables)")