-- MàJ de la version en BDD
UPDATE om_version SET om_version = '5.17.0' WHERE exists(SELECT 1 FROM om_version) = true;
INSERT INTO om_version
SELECT ('5.17.0') WHERE exists(SELECT 1 FROM om_version) = false;

--
-- BEGIN / [#10115] - Ajouter le champ motif_pec sur les consultation
--

ALTER TABLE consultation ADD COLUMN IF NOT EXISTS motif_pec text;
COMMENT ON COLUMN consultation.motif_pec IS 'Permet de stocker le motif de la pec';

--
-- END / [#10115] - Ajouter le champ motif_pec sur les consultation
--

--
-- BEGIN /  #1629 — Ajout commentaire table compteur
--

-- Ajout commentaire table compteur
COMMENT ON COLUMN compteur.compteur IS 'Identifiant technique interne du compteur';
COMMENT ON COLUMN compteur.code IS 'Code métier du compteur';
COMMENT ON COLUMN compteur.description IS 'Description des données décomptées';
COMMENT ON COLUMN compteur.unite IS 'Unité de mesure du compteur';
COMMENT ON COLUMN compteur.quantite IS 'Valeur du compteur';
COMMENT ON COLUMN compteur.quota IS 'Seuil au delà duquel le client est hors contrat';
COMMENT ON COLUMN compteur.alerte IS 'Seuil au delà duquel l''utilisateur est averti qu''il se rapproche du quota';
COMMENT ON COLUMN compteur.om_collectivite IS 'Identifiant de la collectivité';
COMMENT ON COLUMN compteur.date_modification IS 'Date de dernière modification du compteur';
COMMENT ON COLUMN compteur.om_validite_debut IS 'Date de début de validité du compteur';
COMMENT ON COLUMN compteur.om_validite_fin IS 'Date de fin de validité du compteur';

--
-- END /  #1629 — Ajout commentaire table compteur
--

--
-- BEGIN / [#10121] Renommer les champs dans les CERFA et données techniques
--

--
COMMENT ON COLUMN donnees_techniques.daact_surf IS 'Surface de plancher créée (en m2)';
COMMENT ON COLUMN cerfa.daact_surf IS 'Surface de plancher créée (en m2)';

-- Oubli dans la version précédente (fait seulement sur la table donnees_techniques)
COMMENT ON COLUMN cerfa.co_fin_autr_nb IS 'Répartition du nombre total de logement créés par type de financement : Autres financements [SUPPRIMÉ]';

--
-- END / [#10121] Renommer les champs dans les CERFA et données techniques
--

--
-- BEGIN / [#10122] Ajout de champs de fusion
--

-- Mise à jour de la requête d'instruction
\i v5.17.0-om_requete.sql

--
-- END / [#10122] Ajout de champs de fusion
--

--
-- BEGIN / [#10128] - suggestion d'évènements en fonction des contraintes applicables au dossier 
--

-- Table de liaison lien_sig_contrainte_evenement entre les tables contrainte_sig et evenement
CREATE TABLE IF NOT EXISTS lien_sig_contrainte_evenement(
   lien_sig_contrainte_evenement INTEGER NOT NULL,
   sig_contrainte INTEGER NOT NULL,
   evenement INTEGER NOT NULL
);

-- Séquence de la table lien_sig_contrainte_evenement
CREATE SEQUENCE IF NOT EXISTS lien_sig_contrainte_evenement_seq
   START WITH 1
   INCREMENT BY 1
   NO MINVALUE
   NO MAXVALUE
   CACHE 1;

-- Contrainte de la table lien_sig_contrainte_evenement
ALTER TABLE ONLY lien_sig_contrainte_evenement
   DROP CONSTRAINT IF EXISTS lien_sig_contrainte_evenement,
   DROP CONSTRAINT IF EXISTS lien_sig_contrainte_evenement,
   DROP CONSTRAINT IF EXISTS lien_sig_contrainte_evenement_evenement_fkey,
   DROP CONSTRAINT IF EXISTS lien_sig_contrainte_evenement_unique,
   ADD CONSTRAINT lien_sig_contrainte_evenement_pkey PRIMARY KEY (lien_sig_contrainte_evenement),
   ADD CONSTRAINT lien_sig_contrainte_evenement FOREIGN KEY (sig_contrainte) REFERENCES sig_contrainte(sig_contrainte),
   ADD CONSTRAINT lien_sig_contrainte_evenement_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement),
   ADD CONSTRAINT lien_sig_contrainte_evenement_unique UNIQUE (sig_contrainte, evenement);

-- Commentaire de la table lien_sig_contrainte_evenement
COMMENT ON TABLE lien_sig_contrainte_evenement IS 'Évènements suggérés associés aux contraintes.';
COMMENT ON COLUMN lien_sig_contrainte_evenement.lien_sig_contrainte_evenement IS 'Identifiant unique de l''évènement suggéré.';
COMMENT ON COLUMN lien_sig_contrainte_evenement.sig_contrainte IS 'Identifiant de la contrainte.';
COMMENT ON COLUMN lien_sig_contrainte_evenement.evenement IS 'Identifiant de l''évènement';

-- Permissions de la table lien_sig_contrainte_evenement
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'lien_sig_contrainte_evenement', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'lien_sig_contrainte_evenement' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
);

--
-- END / [#10128] - suggestion d'évènements en fonction des contraintes applicables au dossier 
--

--
-- BEGIN / [#10127] Corriger le tri de la liste des types de pièces
--

-- Dans le champ lien_document_n_type_d_i_t.code il ne doit pas y avoir plus de
-- deux fois le caractères "-" (tiret)
ALTER TABLE ONLY lien_document_n_type_d_i_t DROP CONSTRAINT IF EXISTS lien_document_n_type_d_i_t__code__check;
ALTER TABLE ONLY lien_document_n_type_d_i_t
    ADD CONSTRAINT lien_document_n_type_d_i_t__code__check CHECK ((CHAR_LENGTH(code) - CHAR_LENGTH(REPLACE(code, '-', ''))) < 3);

--
-- END / [#10127] Corriger le tri de la liste des types de pièces
--

--
-- BEGIN / [#10124] Amélioration de la fonction SQL pour l'affichage d'une adresse
--

--
DROP FUNCTION IF EXISTS adresse(text, text, text, text, text, text, text, text, text, text);
DROP FUNCTION IF EXISTS adresse(text, text, text, text, text, text, text, text, text);
CREATE OR REPLACE FUNCTION adresse(
    numero text,
    voie text,
    complement text,
    lieu_dit text,
    bp text,
    code_postal text,
    localite text,
    cedex text,
    pays text DEFAULT '',
    separateur text DEFAULT '<br>') RETURNS text AS $$

    SELECT TRIM(CONCAT(
        CASE WHEN COALESCE(numero, '') != '' THEN CONCAT(numero, ' ') END,
        CASE WHEN COALESCE(voie, '') != '' THEN CONCAT(voie, separateur) END,
        CASE WHEN COALESCE(complement, '') != '' THEN CONCAT(complement, separateur) END,
        CASE WHEN COALESCE(lieu_dit, '') != '' THEN CONCAT('Lieu-dit ', lieu_dit, separateur) END,
        CASE WHEN COALESCE(bp, '') != '' THEN CONCAT('BP ', bp, separateur) END, 
        CASE WHEN COALESCE(code_postal, '') != '' THEN CONCAT(code_postal, ' ') END,
        CASE WHEN COALESCE(localite, '') != '' THEN CONCAT(localite, ' ') END,
        CASE WHEN COALESCE(cedex, '') != '' THEN CONCAT('CEDEX ', cedex) END,
        CASE WHEN COALESCE(pays, '') != '' AND LOWER(TRIM(pays)) != 'france' THEN CONCAT(separateur, pays) END
    ))
$$ LANGUAGE SQL;

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'dossier.terrain_adresse_cedex::text,', E'\\1dossier.terrain_adresse_cedex::text, ''''::text,')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('dossier.terrain_adresse_cedex::text, ''''::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_principal.cedex::text\)', E'\\1petitionnaire_principal.cedex::text, petitionnaire_principal.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_principal.cedex::text, petitionnaire_principal.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_principal.cedex::text, '' ''::text', E'\\1petitionnaire_principal.cedex::text, petitionnaire_principal.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_principal.cedex::text, petitionnaire_principal.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_principal_initial.cedex::text\)', E'\\1petitionnaire_principal_initial.cedex::text, petitionnaire_principal_initial.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_principal_initial.cedex::text, petitionnaire_principal_initial.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_principal_initial.cedex::text, '' ''::text', E'\\1petitionnaire_principal_initial.cedex::text, petitionnaire_principal_initial.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_principal_initial.cedex::text, petitionnaire_principal_initial.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_1.cedex::text\)', E'\\1petitionnaire_1.cedex::text, petitionnaire_1.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_1.cedex::text, petitionnaire_1.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_1.cedex::text, '' ''::text', E'\\1petitionnaire_1.cedex::text, petitionnaire_1.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_1.cedex::text, petitionnaire_1.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_2.cedex::text\)', E'\\1petitionnaire_2.cedex::text, petitionnaire_2.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_2.cedex::text, petitionnaire_2.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_2.cedex::text, '' ''::text', E'\\1petitionnaire_2.cedex::text, petitionnaire_2.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_2.cedex::text, petitionnaire_2.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_3.cedex::text\)', E'\\1petitionnaire_3.cedex::text, petitionnaire_3.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_3.cedex::text, petitionnaire_3.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_3.cedex::text, '' ''::text', E'\\1petitionnaire_3.cedex::text, petitionnaire_3.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_3.cedex::text, petitionnaire_3.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_4.cedex::text\)', E'\\1petitionnaire_4.cedex::text, petitionnaire_4.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_4.cedex::text, petitionnaire_4.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_4.cedex::text, '' ''::text', E'\\1petitionnaire_4.cedex::text, petitionnaire_4.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_4.cedex::text, petitionnaire_4.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_5.cedex::text\)', E'\\1petitionnaire_5.cedex::text, petitionnaire_5.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_5.cedex::text, petitionnaire_5.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'petitionnaire_5.cedex::text, '' ''::text', E'\\1petitionnaire_5.cedex::text, petitionnaire_5.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('petitionnaire_5.cedex::text, petitionnaire_5.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_principal.cedex::text\)', E'\\1contrevenant_principal.cedex::text, contrevenant_principal.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_principal.cedex::text, contrevenant_principal.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_principal.cedex::text, '' ''::text', E'\\1contrevenant_principal.cedex::text, contrevenant_principal.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_principal.cedex::text, contrevenant_principal.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_1.cedex::text\)', E'\\1contrevenant_1.cedex::text, contrevenant_1.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_1.cedex::text, contrevenant_1.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_1.cedex::text, '' ''::text', E'\\1contrevenant_1.cedex::text, contrevenant_1.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_1.cedex::text, contrevenant_1.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_2.cedex::text\)', E'\\1contrevenant_2.cedex::text, contrevenant_2.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_2.cedex::text, contrevenant_2.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_2.cedex::text, '' ''::text', E'\\1contrevenant_2.cedex::text, contrevenant_2.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_2.cedex::text, contrevenant_2.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_3.cedex::text\)', E'\\1contrevenant_3.cedex::text, contrevenant_3.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_3.cedex::text, contrevenant_3.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_3.cedex::text, '' ''::text', E'\\1contrevenant_3.cedex::text, contrevenant_3.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_3.cedex::text, contrevenant_3.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_4.cedex::text\)', E'\\1contrevenant_4.cedex::text, contrevenant_4.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_4.cedex::text, contrevenant_4.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_4.cedex::text, '' ''::text', E'\\1contrevenant_4.cedex::text, contrevenant_4.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_4.cedex::text, contrevenant_4.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_5.cedex::text\)', E'\\1contrevenant_5.cedex::text, contrevenant_5.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_5.cedex::text, contrevenant_5.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'contrevenant_5.cedex::text, '' ''::text', E'\\1contrevenant_5.cedex::text, contrevenant_5.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('contrevenant_5.cedex::text, contrevenant_5.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_principal.cedex::text\)', E'\\1requerant_principal.cedex::text, requerant_principal.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_principal.cedex::text, requerant_principal.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_principal.cedex::text, '' ''::text', E'\\1requerant_principal.cedex::text, requerant_principal.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_principal.cedex::text, requerant_principal.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_1.cedex::text\)', E'\\1requerant_1.cedex::text, requerant_1.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_1.cedex::text, requerant_1.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_1.cedex::text, '' ''::text', E'\\1requerant_1.cedex::text, requerant_1.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_1.cedex::text, requerant_1.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_2.cedex::text\)', E'\\1requerant_2.cedex::text, requerant_2.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_2.cedex::text, requerant_2.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_2.cedex::text, '' ''::text', E'\\1requerant_2.cedex::text, requerant_2.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_2.cedex::text, requerant_2.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_3.cedex::text\)', E'\\1requerant_3.cedex::text, requerant_3.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_3.cedex::text, requerant_3.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_3.cedex::text, '' ''::text', E'\\1requerant_3.cedex::text, requerant_3.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_3.cedex::text, requerant_3.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_4.cedex::text\)', E'\\1requerant_4.cedex::text, requerant_4.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_4.cedex::text, requerant_4.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_4.cedex::text, '' ''::text', E'\\1requerant_4.cedex::text, requerant_4.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_4.cedex::text, requerant_4.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_5.cedex::text\)', E'\\1requerant_5.cedex::text, requerant_5.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_5.cedex::text, requerant_5.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'requerant_5.cedex::text, '' ''::text', E'\\1requerant_5.cedex::text, requerant_5.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('requerant_5.cedex::text, requerant_5.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_principal.cedex::text\)', E'\\1avocat_principal.cedex::text, avocat_principal.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_principal.cedex::text, avocat_principal.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_principal.cedex::text, '' ''::text', E'\\1avocat_principal.cedex::text, avocat_principal.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_principal.cedex::text, avocat_principal.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_1.cedex::text\)', E'\\1avocat_1.cedex::text, avocat_1.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_1.cedex::text, avocat_1.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_1.cedex::text, '' ''::text', E'\\1avocat_1.cedex::text, avocat_1.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_1.cedex::text, avocat_1.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_2.cedex::text\)', E'\\1avocat_2.cedex::text, avocat_2.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_2.cedex::text, avocat_2.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_2.cedex::text, '' ''::text', E'\\1avocat_2.cedex::text, avocat_2.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_2.cedex::text, avocat_2.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_3.cedex::text\)', E'\\1avocat_3.cedex::text, avocat_3.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_3.cedex::text, avocat_3.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_3.cedex::text, '' ''::text', E'\\1avocat_3.cedex::text, avocat_3.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_3.cedex::text, avocat_3.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_4.cedex::text\)', E'\\1avocat_4.cedex::text, avocat_4.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_4.cedex::text, avocat_4.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_4.cedex::text, '' ''::text', E'\\1avocat_4.cedex::text, avocat_4.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_4.cedex::text, avocat_4.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_5.cedex::text\)', E'\\1avocat_5.cedex::text, avocat_5.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_5.cedex::text, avocat_5.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'avocat_5.cedex::text, '' ''::text', E'\\1avocat_5.cedex::text, avocat_5.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('avocat_5.cedex::text, avocat_5.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_principal.cedex::text\)', E'\\1plaignant_principal.cedex::text, plaignant_principal.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_principal.cedex::text, plaignant_principal.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_principal.cedex::text, '' ''::text', E'\\1plaignant_principal.cedex::text, plaignant_principal.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_principal.cedex::text, plaignant_principal.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_1.cedex::text\)', E'\\1plaignant_1.cedex::text, plaignant_1.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_1.cedex::text, plaignant_1.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_1.cedex::text, '' ''::text', E'\\1plaignant_1.cedex::text, plaignant_1.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_1.cedex::text, plaignant_1.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_2.cedex::text\)', E'\\1plaignant_2.cedex::text, plaignant_2.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_2.cedex::text, plaignant_2.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_2.cedex::text, '' ''::text', E'\\1plaignant_2.cedex::text, plaignant_2.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_2.cedex::text, plaignant_2.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_3.cedex::text\)', E'\\1plaignant_3.cedex::text, plaignant_3.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_3.cedex::text, plaignant_3.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_3.cedex::text, '' ''::text', E'\\1plaignant_3.cedex::text, plaignant_3.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_3.cedex::text, plaignant_3.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_4.cedex::text\)', E'\\1plaignant_4.cedex::text, plaignant_4.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_4.cedex::text, plaignant_4.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_4.cedex::text, '' ''::text', E'\\1plaignant_4.cedex::text, plaignant_4.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_4.cedex::text, plaignant_4.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_5.cedex::text\)', E'\\1plaignant_5.cedex::text, plaignant_5.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_5.cedex::text, plaignant_5.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'plaignant_5.cedex::text, '' ''::text', E'\\1plaignant_5.cedex::text, plaignant_5.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('plaignant_5.cedex::text, plaignant_5.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'delegataire.cedex::text\)', E'\\1delegataire.cedex::text, delegataire.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('delegataire.cedex::text, delegataire.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'delegataire.cedex::text, '' ''::text', E'\\1delegataire.cedex::text, delegataire.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('delegataire.cedex::text, delegataire.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_principal.cedex::text\)', E'\\1bailleur_principal.cedex::text, bailleur_principal.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_principal.cedex::text, bailleur_principal.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_principal.cedex::text, '' ''::text', E'\\1bailleur_principal.cedex::text, bailleur_principal.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_principal.cedex::text, bailleur_principal.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_1.cedex::text\)', E'\\1bailleur_1.cedex::text, bailleur_1.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_1.cedex::text, bailleur_1.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_1.cedex::text, '' ''::text', E'\\1bailleur_1.cedex::text, bailleur_1.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_1.cedex::text, bailleur_1.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_2.cedex::text\)', E'\\1bailleur_2.cedex::text, bailleur_2.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_2.cedex::text, bailleur_2.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_2.cedex::text, '' ''::text', E'\\1bailleur_2.cedex::text, bailleur_2.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_2.cedex::text, bailleur_2.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_3.cedex::text\)', E'\\1bailleur_3.cedex::text, bailleur_3.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_3.cedex::text, bailleur_3.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_3.cedex::text, '' ''::text', E'\\1bailleur_3.cedex::text, bailleur_3.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_3.cedex::text, bailleur_3.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_4.cedex::text\)', E'\\1bailleur_4.cedex::text, bailleur_4.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_4.cedex::text, bailleur_4.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_4.cedex::text, '' ''::text', E'\\1bailleur_4.cedex::text, bailleur_4.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_4.cedex::text, bailleur_4.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_5.cedex::text\)', E'\\1bailleur_5.cedex::text, bailleur_5.pays::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_5.cedex::text, bailleur_5.pays::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'bailleur_5.cedex::text, '' ''::text', E'\\1bailleur_5.cedex::text, bailleur_5.pays::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('bailleur_5.cedex::text, bailleur_5.pays::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.cedex ELSE delegataire.cedex END::text\)', E'\\1CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.cedex ELSE delegataire.cedex END::text, CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.pays ELSE delegataire.pays END::text\)')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.cedex ELSE delegataire.cedex END::text, CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.pays ELSE delegataire.pays END::text)' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');
--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.cedex ELSE delegataire.cedex END::text, '' ''::text', E'\\1CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.cedex ELSE delegataire.cedex END::text, CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.pays ELSE delegataire.pays END::text, '' ''::text')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.cedex ELSE delegataire.cedex END::text, CASE WHEN delegataire.qualite IS NULL THEN petitionnaire_principal.pays ELSE delegataire.pays END::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
UPDATE om_requete AS omr SET requete = (
   SELECT regexp_replace(r.requete, 'donnees_techniques.dia_acquereur_adr_localite::text,
        ''''::text,', E'\\1donnees_techniques.dia_acquereur_adr_localite::text,
        ''''::text,
        ''''::text,')
   FROM om_requete AS r
   WHERE r.om_requete = omr.om_requete
)
WHERE POSITION('donnees_techniques.dia_acquereur_adr_localite::text,
        ''''::text,
        ''''::text,' IN omr.requete) = 0
  AND omr.code IN ('instruction', 'dossier', 'accuse_reception_consultation', 'consultation', 'rapport_instruction', 'commission');

--
-- END / [#10124] Amélioration de la fonction SQL pour l'affichage d'une adresse
--
