--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.7.0-a1
--
-- XXX Ce fichier doit être renommé en v3.7.0-a1.sql au moment de la release
--
-- @package openfoncier
-- @version SVN : $Id$
-------------------------------------------------------------------------------

---
--- Modification champ complement de la table dossier, demande et 
--- dossier_autorisation en terrain_adresse_voie
---
ALTER TABLE dossier RENAME COLUMN complement TO terrain_adresse_voie;
ALTER TABLE demande RENAME COLUMN complement TO terrain_adresse_voie;
ALTER TABLE dossier_autorisation RENAME COLUMN complement TO terrain_adresse_voie;

--
-- Optimisation du registre affichage règlementaire + modification du champ
-- complement de la table dossier, dossier_autorisation et demande
--
UPDATE om_sousetat SET om_sql =
'SELECT

    CONCAT(dossier_autorisation_type.libelle, ''

'',dossier.dossier) as dossier,

    CONCAT(''Dépôt le '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''
Notifié le '', COALESCE(to_char(dossier.date_complet, ''DD/MM/YYYY''), ''inconu'')) as dates,

    CASE
        WHEN petitionnaire_principal.qualite=''particulier''

            THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom, ''
'', (CASE WHEN petitionnaire_principal.complement IS NULL THEN ''''
         ELSE CONCAT(petitionnaire_principal.complement, ''
'') END), petitionnaire_principal.numero,'' '', petitionnaire_principal.voie,''
'', (CASE WHEN petitionnaire_principal.bp IS NULL THEN ''''
         ELSE CONCAT(petitionnaire_principal.bp,''
'') END), petitionnaire_principal.code_postal,'' '', petitionnaire_principal.localite, '' '', petitionnaire_principal.cedex, '' '', petitionnaire_principal.lieu_dit
                                          ))
    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination, ''
'', (CASE WHEN petitionnaire_principal.complement IS NULL THEN ''''
         ELSE CONCAT(petitionnaire_principal.complement, ''
'') END), petitionnaire_principal.numero,'' '', petitionnaire_principal.voie,''
'', (CASE WHEN petitionnaire_principal.bp IS NULL THEN ''''
         ELSE CONCAT(petitionnaire_principal.bp, ''
'') END), petitionnaire_principal.code_postal,'' '', petitionnaire_principal.localite, '' '', petitionnaire_principal.cedex, '' '', petitionnaire_principal.lieu_dit                                      ))
    END as demandeur,

    CONCAT(dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, ''
'', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite,''
Arrondissement : '', arrondissement.libelle) as terrain,


    CONCAT(''superficie : '', '' '', ''
nombre de logements : '', '' '') as informations,

    CONCAT(''Délai '', delai, '' mois
Date limite le '', COALESCE(to_char(date_limite, ''DD/MM/YYYY''), ''inconu'')) as limite

FROM
  &DB_PREFIXEdossier
  LEFT JOIN &DB_PREFIXEdossier_autorisation
    ON dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
  LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille
    ON dossier_autorisation.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
  LEFT JOIN &DB_PREFIXEdossier_autorisation_type
    ON dossier_autorisation_type_detaille.dossier_autorisation_type=dossier_autorisation_type.dossier_autorisation_type
  LEFT JOIN &DB_PREFIXElien_dossier_demandeur
    ON dossier.dossier = lien_dossier_demandeur.dossier
  LEFT JOIN &DB_PREFIXEdemandeur as petitionnaire_principal
    ON lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
  LEFT JOIN &DB_PREFIXEavis_decision
    ON dossier.avis_decision=avis_decision.avis_decision
  LEFT JOIN &DB_PREFIXEarrondissement
    ON dossier.terrain_adresse_code_postal = arrondissement.code_postal
  LEFT JOIN &DB_PREFIXEdonnees_techniques
    ON donnees_techniques.dossier_instruction=dossier.dossier

WHERE
    (select e.statut from &DB_PREFIXEetat e where e.etat = dossier.etat ) = ''encours''

ORDER BY
    dossier_autorisation_type.libelle, arrondissement.libelle'
, entete_hauteur = 10
WHERE id = 'registre_dossiers_affichage_reglementaire';

---
--- Modification des om_requete concernant la modification du champ complement
--- de la table dossier
---

-- Dossier
UPDATE om_requete SET requete =
'SELECT
    --
    -- INFORMATIONS CONCERNANT LE DOSSIER
    --

    dossier.dossier as dossier,
    dossier.dossier_autorisation,

    dossier_autorisation_type_detaille.code as code_type_da_det,
    dossier_autorisation_type_detaille.libelle as libelle_type_da_det,
    dossier_autorisation_type.code as code_type_da,
    dossier_autorisation_type.libelle as libelle_type_da,
    dossier_instruction_type.code as code_type_di,
    dossier_instruction_type.libelle as libelle_type_di,
    dossier.delai as delai_di,
    replace(dossier.terrain_references_cadastrales, '';'', '' ''),

    avis_decision.libelle as decision,

    --
    -- INSTRUCTEUR
    --

    instructeur.nom as instructeur_nom,
    instructeur.telephone as instructeur_telephone,
    division.code as instructeur_division,
    om_utilisateur.email as instructeur_email,

    --
    -- SIGNATAIRES
    --
    division.chef as division_chef,
    direction.chef as direction_chef,

    --
    -- ADRESSE TERRAIN
    --

    dossier.terrain_adresse_voie_numero,
    dossier.terrain_adresse_voie as adresse_voie,
    dossier.terrain_adresse_lieu_dit,
    dossier.terrain_adresse_bp,
    dossier.terrain_adresse_code_postal,
    dossier.terrain_adresse_localite as terrain_adresse_ville,
    dossier.terrain_adresse_cedex,

    arrondissement.libelle as arrondissement,

    --
    -- PETITIONNAIRE PRINCIPAL
    --

    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
    END as petitionnaire_principal_nom,
    petitionnaire_principal.numero as petitionnaire_principal_numero,
    petitionnaire_principal.voie as petitionnaire_principal_voie,
    petitionnaire_principal.complement as petitionnaire_principal_complement,
    petitionnaire_principal.lieu_dit as petitionnaire_principal_lieu_dit,
    petitionnaire_principal.bp as petitionnaire_principal_bp,
    petitionnaire_principal.code_postal as petitionnaire_principal_code_postal,
    petitionnaire_principal.localite as petitionnaire_principal_ville,
    petitionnaire_principal.cedex as petitionnaire_principal_cedex,
    petitionnaire_principal.pays as petitionnaire_principal_pays,

    --
    -- DELEGATAIRE
    --

    CASE
        WHEN delegataire.qualite=''particulier''
        THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
        ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
    END as delegataire_nom,
    delegataire.numero as delegataire_numero,
    delegataire.voie as delegataire_voie,
    delegataire.complement as delegataire_complement,
    delegataire.lieu_dit as delegataire_lieu_dit,
    delegataire.bp as delegataire_bp,
    delegataire.code_postal as delegataire_code_postal,
    delegataire.localite as delegataire_ville,
    delegataire.cedex as delegataire_cedex,
    delegataire.pays as delegataire_pays,

    --
    -- CORRESPONDANT : destinataire du courrier. Il est le délégataire ou le pétitionnaire principal
    --

    CASE
        WHEN delegataire.qualite IS NULL
        THEN
            CASE WHEN petitionnaire_principal.qualite=''particulier''
                    THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
                    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
            END
        ELSE
            CASE WHEN delegataire.qualite=''particulier''
                THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
                ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
            END
    END as correspondant_nom,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.numero
        ELSE delegataire.numero
    END as correspondant_numero,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.voie
        ELSE delegataire.voie
    END as correspondant_voie,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.complement
        ELSE delegataire.complement
    END as correspondant_complement,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.lieu_dit
        ELSE delegataire.lieu_dit
    END as correspondant_lieu_dit,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.bp
        ELSE delegataire.bp
    END as correspondant_bp,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.code_postal
        ELSE delegataire.code_postal
    END as correspondant_code_postal,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.localite
        ELSE delegataire.localite
    END as correspondant_ville,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.cedex
        ELSE delegataire.cedex
    END as correspondant_cedex,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.pays
        ELSE delegataire.pays
    END as correspondant_pays,

    --
    -- DATES DI
    --

    to_char(dossier.date_depot, ''DD/MM/YYYY'') as date_depot,
    to_char(dossier.date_decision, ''DD/MM/YYYY'') as date_decision,
    to_char(dossier.date_limite,''DD/MM/YYYY'') as date_limite,
    to_char(dossier.date_achevement,''DD/MM/YYYY'') as date_achevement,
    to_char(dossier.date_conformite,''DD/MM/YYYY'') as date_conformite,
    
    --
    -- DONNÉES TECHNIQUES
    --
    donnees_techniques.co_projet_desc,
    donnees_techniques.am_projet_desc,
    donnees_techniques.dm_projet_desc,
    donnees_techniques.am_lot_max_nb,
    donnees_techniques.am_lot_max_shon,
    donnees_techniques.su_cstr_shon_tot,
    donnees_techniques.su_demo_shon_tot,
    CONCAT(''Habitation : '', donnees_techniques.su_trsf_shon1, 
    '' - Hébergement hôtelier : '', donnees_techniques.su_trsf_shon2, 
    '' - Bureaux : '', donnees_techniques.su_trsf_shon3, 
    '' - Commerce : '', donnees_techniques.su_trsf_shon4,
    '' - Artisanat : '', donnees_techniques.su_trsf_shon5,
    '' - Industrie : '', donnees_techniques.su_trsf_shon6,
    '' - Exploitation agricole ou forestière : '', donnees_techniques.su_trsf_shon7,
    '' - Entrepôt : '', donnees_techniques.su_trsf_shon8,
    '' - Service public ou d''''intérêt collectif : '', donnees_techniques.su_trsf_shon9),
    donnees_techniques.co_tot_log_nb,
    donnees_techniques.co_statio_place_nb

FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
LEFT JOIN
    &DB_PREFIXEdemandeur as petitionnaire_principal
    ON
        lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur as delegataire
    ON
        lien_dossier_demandeur.demandeur = delegataire.demandeur AND delegataire.type_demandeur = ''delegataire''
LEFT JOIN
    &DB_PREFIXEdossier_instruction_type
    ON
        dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type
    ON
        dossier_autorisation_type_detaille.dossier_autorisation_type = dossier_autorisation_type.dossier_autorisation_type
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXEom_utilisateur
    ON
        om_utilisateur.om_utilisateur = instructeur.om_utilisateur
LEFT JOIN
    &DB_PREFIXEdivision
    ON
        instructeur.division = division.division
LEFT JOIN
    &DB_PREFIXEdirection
    ON
        division.direction = direction.direction
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEavis_decision
    ON
        dossier.avis_decision = avis_decision.avis_decision
LEFT JOIN
    &DB_PREFIXEetat
    ON
        dossier.etat = etat.etat
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
    dossier.dossier = donnees_techniques.dossier_instruction
WHERE dossier.dossier = ''&idx''' 
WHERE code = 'dossier' ;

-- Instruction
UPDATE om_requete SET requete =
'SELECT

    --
    -- CHAMPS DE L''EVENEMENT D''INSTRUCTION

    instruction.complement,
    instruction.complement2,
    instruction.code_barres as code_barres,
    om_lettretype.libelle as instruction_objet,

    --
    -- INFORMATIONS CONCERNANT LE DOSSIER
    --

    dossier.dossier as dossier,
    dossier.dossier_autorisation,

    dossier_autorisation_type_detaille.code as code_type_da_det,
    dossier_autorisation_type_detaille.libelle as libelle_type_da_det,
    dossier_autorisation_type.code as code_type_da,
    dossier_autorisation_type.libelle as libelle_type_da,
    dossier_instruction_type.code as code_type_di,
    dossier_instruction_type.libelle as libelle_type_di,
    dossier.delai as delai_di,
    replace(dossier.terrain_references_cadastrales, '';'', '' ''),

    avis_decision.libelle as decision,

    --
    -- INSTRUCTEUR
    --

    instructeur.nom as instructeur_nom,
    instructeur.telephone as instructeur_telephone,
    division.code as instructeur_division,
    om_utilisateur.email as instructeur_email,

    --
    -- ADRESSE TERRAIN
    --

    dossier.terrain_adresse_voie_numero,
    dossier.terrain_adresse_voie as adresse_voie,
    dossier.terrain_adresse_lieu_dit,
    dossier.terrain_adresse_bp,
    dossier.terrain_adresse_code_postal,
    dossier.terrain_adresse_localite as terrain_adresse_ville,
    dossier.terrain_adresse_cedex,

    arrondissement.libelle as arrondissement,

    --
    -- PETITIONNAIRE PRINCIPAL
    --

    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
    END as petitionnaire_principal_nom,
    petitionnaire_principal.numero as petitionnaire_principal_numero,
    petitionnaire_principal.voie as petitionnaire_principal_voie,
    petitionnaire_principal.complement as petitionnaire_principal_complement,
    petitionnaire_principal.lieu_dit as petitionnaire_principal_lieu_dit,
    petitionnaire_principal.bp as petitionnaire_principal_bp,
    petitionnaire_principal.code_postal as petitionnaire_principal_code_postal,
    petitionnaire_principal.localite as petitionnaire_principal_ville,
    petitionnaire_principal.cedex as petitionnaire_principal_cedex,
    petitionnaire_principal.pays as petitionnaire_principal_pays,

    --
    -- DELEGATAIRE
    --

    CASE
        WHEN delegataire.qualite=''particulier''
        THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
        ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
    END as delegataire_nom,
    delegataire.numero as delegataire_numero,
    delegataire.voie as delegataire_voie,
    delegataire.complement as delegataire_complement,
    delegataire.lieu_dit as delegataire_lieu_dit,
    delegataire.bp as delegataire_bp,
    delegataire.code_postal as delegataire_code_postal,
    delegataire.localite as delegataire_ville,
    delegataire.cedex as delegataire_cedex,
    delegataire.pays as delegataire_pays,

    --
    -- CORRESPONDANT : destinataire du courrier. Il est le délégataire ou le pétitionnaire principal
    --

    CASE
        WHEN delegataire.qualite IS NULL
        THEN
            CASE WHEN petitionnaire_principal.qualite=''particulier''
                    THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
                    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
            END
        ELSE
            CASE WHEN delegataire.qualite=''particulier''
                THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
                ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
            END
    END as correspondant_nom,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.numero
        ELSE delegataire.numero
    END as correspondant_numero,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.voie
        ELSE delegataire.voie
    END as correspondant_voie,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.complement
        ELSE delegataire.complement
    END as correspondant_complement,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.lieu_dit
        ELSE delegataire.lieu_dit
    END as correspondant_lieu_dit,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.bp
        ELSE delegataire.bp
    END as correspondant_bp,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.code_postal
        ELSE delegataire.code_postal
    END as correspondant_code_postal,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.localite
        ELSE delegataire.localite
    END as correspondant_ville,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.cedex
        ELSE delegataire.cedex
    END as correspondant_cedex,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.pays
        ELSE delegataire.pays
    END as correspondant_pays,

    --
    -- DATES DI
    --

    to_char(dossier.date_depot, ''DD/MM/YYYY'') as date_depot,
    to_char(dossier.date_decision, ''DD/MM/YYYY'') as date_decision,
    to_char(dossier.date_limite,''DD/MM/YYYY'') as date_limite,
    to_char(dossier.date_achevement,''DD/MM/YYYY'') as date_achevement,
    to_char(dossier.date_conformite,''DD/MM/YYYY'') as date_conformite,
    
    --
    -- SIGNATAIRES
    --
    CONCAT(signataire_arrete.prenom, '' '', signataire_arrete.nom) as signataire_arrete,
    division.chef as division_chef,
    direction.chef as direction_chef,
    
    --
    -- DONNÉES TECHNIQUES
    --
    donnees_techniques.co_projet_desc,
    donnees_techniques.am_projet_desc,
    donnees_techniques.dm_projet_desc,
    donnees_techniques.am_lot_max_nb,
    donnees_techniques.am_lot_max_shon,
    donnees_techniques.su_cstr_shon_tot,
    donnees_techniques.su_demo_shon_tot,
    CONCAT(''Habitation : '', donnees_techniques.su_trsf_shon1, 
    '' - Hébergement hôtelier : '', donnees_techniques.su_trsf_shon2, 
    '' - Bureaux : '', donnees_techniques.su_trsf_shon3, 
    '' - Commerce : '', donnees_techniques.su_trsf_shon4,
    '' - Artisanat : '', donnees_techniques.su_trsf_shon5,
    '' - Industrie : '', donnees_techniques.su_trsf_shon6,
    '' - Exploitation agricole ou forestière : '', donnees_techniques.su_trsf_shon7,
    '' - Entrepôt : '', donnees_techniques.su_trsf_shon8,
    '' - Service public ou d''''intérêt collectif : '', donnees_techniques.su_trsf_shon9),
    donnees_techniques.co_tot_log_nb,
    donnees_techniques.co_statio_place_nb
    
FROM
    &DB_PREFIXEinstruction
LEFT JOIN
    &DB_PREFIXEsignataire_arrete
    ON instruction.signataire_arrete = signataire_arrete.signataire_arrete
LEFT JOIN
    &DB_PREFIXEom_lettretype
    ON instruction.lettretype = om_lettretype.id and om_lettretype.actif IS TRUE
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        instruction.dossier=dossier.dossier
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        instruction.dossier = lien_dossier_demandeur.dossier AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur as petitionnaire_principal
    ON
        lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur
LEFT JOIN
    (
    &DB_PREFIXElien_dossier_demandeur AS lien_dossier_delegataire
        JOIN &DB_PREFIXEdemandeur as delegataire
        ON
            lien_dossier_delegataire.demandeur = delegataire.demandeur AND delegataire.type_demandeur = ''delegataire''
    )
    ON
        instruction.dossier = lien_dossier_delegataire.dossier AND lien_dossier_delegataire.petitionnaire_principal IS FALSE
LEFT JOIN
    &DB_PREFIXEdossier_instruction_type
    ON
        dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type
    ON
        dossier_autorisation_type_detaille.dossier_autorisation_type = dossier_autorisation_type.dossier_autorisation_type
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXEom_utilisateur
    ON
        om_utilisateur.om_utilisateur = instructeur.om_utilisateur
LEFT JOIN
    &DB_PREFIXEdivision
    ON
        instructeur.division = division.division
LEFT JOIN 
    &DB_PREFIXEdirection
    ON division.direction = direction.direction
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEavis_decision
    ON
        dossier.avis_decision = avis_decision.avis_decision
LEFT JOIN
    &DB_PREFIXEetat
    ON
        dossier.etat = etat.etat
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
    dossier.dossier = donnees_techniques.dossier_instruction
WHERE instruction.instruction = &idx' 
WHERE code = 'instruction' ;

-- Rapport d'instruction
UPDATE om_requete SET requete =
'SELECT

 
    rapport_instruction.dossier_instruction, analyse_reglementaire, description_projet, proposition_decision, 
    dossier.dossier as d, etat, pos, servitude, terrain_adresse_voie_numero, dossier.terrain_adresse_voie as dc, terrain_adresse_lieu_dit, terrain_adresse_localite, terrain_adresse_code_postal, terrain_adresse_bp, terrain_adresse_cedex, terrain_superficie, dossier.delai, replace(dossier.terrain_references_cadastrales, '';'', '' ''),
    particulier_nom, particulier_prenom, personne_morale_denomination, personne_morale_raison_sociale, personne_morale_siret, personne_morale_nom, personne_morale_prenom, numero, voie, demandeur.complement as deco, lieu_dit, localite, code_postal, bp, cedex, instructeur.nom, 
    dossier_autorisation_type_detaille.libelle as dl, 
    civilite.code as cc,
    avis_decision.libelle as avis,
    division.chef as division_chef,
    direction.chef as direction_chef,
    donnees_techniques.co_projet_desc,
    donnees_techniques.am_projet_desc,
    donnees_techniques.dm_projet_desc,
    donnees_techniques.am_lot_max_nb,
    donnees_techniques.am_lot_max_shon,
    donnees_techniques.su_cstr_shon_tot,
    donnees_techniques.su_demo_shon_tot,
    CONCAT(''Habitation : '', donnees_techniques.su_trsf_shon1, 
    '' - Hébergement hôtelier : '', donnees_techniques.su_trsf_shon2, 
    '' - Bureaux : '', donnees_techniques.su_trsf_shon3, 
    '' - Commerce : '', donnees_techniques.su_trsf_shon4,
    '' - Artisanat : '', donnees_techniques.su_trsf_shon5,
    '' - Industrie : '', donnees_techniques.su_trsf_shon6,
    '' - Exploitation agricole ou forestière : '', donnees_techniques.su_trsf_shon7,
    '' - Entrepôt : '', donnees_techniques.su_trsf_shon8,
    '' - Service public ou d''''intérêt collectif : '', donnees_techniques.su_trsf_shon9),
    donnees_techniques.co_tot_log_nb,
    donnees_techniques.co_statio_place_nb


FROM 

    &DB_PREFIXErapport_instruction 
    LEFT JOIN &DB_PREFIXEdossier 
        ON dossier.dossier=rapport_instruction.dossier_instruction 
    LEFT JOIN &DB_PREFIXEavis_decision 
        ON dossier.avis_decision = avis_decision.avis_decision
    LEFT JOIN &DB_PREFIXEdonnees_techniques
        ON dossier.dossier = donnees_techniques.dossier_instruction
    LEFT JOIN &DB_PREFIXEdivision
        ON dossier.division = division.division
    LEFT JOIN &DB_PREFIXEdirection
        ON division.direction = direction.direction
    LEFT JOIN &DB_PREFIXEinstructeur 
        ON instructeur.instructeur=dossier.instructeur 
    LEFT JOIN &DB_PREFIXElien_dossier_demandeur 
        ON lien_dossier_demandeur.dossier=dossier.dossier 
    LEFT JOIN &DB_PREFIXEdemandeur 
        ON demandeur.demandeur=lien_dossier_demandeur.demandeur 
    LEFT JOIN &DB_PREFIXEcivilite 
        ON demandeur.personne_morale_civilite=civilite.civilite OR demandeur.particulier_civilite=civilite.civilite 
    LEFT JOIN &DB_PREFIXEdossier_instruction_type 
        ON dossier.dossier_instruction_type=dossier_instruction_type.dossier_instruction_type 
    LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille 
        ON dossier_instruction_type.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille 
    
WHERE 

    rapport_instruction = &idx AND lien_dossier_demandeur.petitionnaire_principal IS TRUE' 
WHERE code = 'rapport_instruction' ;


---
--- Modification des om_sousetat concernant la modification du champ complement
--- de la table dossier, dossier_autorisation et demande
---
UPDATE om_sousetat SET om_sql = 
'SELECT
    dossier.dossier as dossier,
    evenement.libelle as evenement,
    CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.localite, '' '', demandeur.code_postal, '' '', demandeur.cedex, '' '', demandeur.bp)
         WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, ''\n'', 
                                                              demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp)
    END as coordonnees_demandeur,
    CONCAT ( dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, '' '', dossier.terrain_adresse_lieu_dit, '' '', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite, '' '', dossier.terrain_adresse_cedex, '' '', dossier.terrain_adresse_bp) as coordonnees_terrain
FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXEinstruction
    ON
        instruction.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEevenement
    ON
        instruction.evenement = evenement.evenement
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        lien_dossier_demandeur.dossier = dossier.dossier
LEFT JOIn
    &DB_PREFIXEdemandeur
    ON
        demandeur.demandeur = lien_dossier_demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        civilite.civilite = demandeur.particulier_civilite OR civilite.civilite = demandeur.personne_morale_civilite
WHERE
    instruction.date_evenement = (SELECT MAX(instr.date_evenement) FROM &DB_PREFIXEinstruction instr WHERE instr.dossier = dossier.dossier) AND
    LOWER(evenement.type) = ''arrete'' AND
    instruction.date_envoi_signature = ''&date_bordereau'' AND
    lien_dossier_demandeur.petitionnaire_principal IS TRUE
ORDER BY
    dossier.dossier'
WHERE id = 'bordereau_decisions';

UPDATE om_sousetat SET om_sql = 
'SELECT
    dossier.dossier as dossier,
    evenement.libelle as evenement,
    CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.localite, '' '', demandeur.code_postal, '' '', demandeur.cedex, '' '', demandeur.bp)
         WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, ''\n'', 
                                                              demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp)
    END as coordonnees_demandeur,
    CONCAT ( dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, '' '', dossier.terrain_adresse_lieu_dit, '' '', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite, '' '', dossier.terrain_adresse_cedex, '' '', dossier.terrain_adresse_bp) as coordonnees_terrain
FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXEinstruction
    ON
        instruction.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEevenement
    ON
        instruction.evenement = evenement.evenement
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        lien_dossier_demandeur.dossier = dossier.dossier
LEFT JOIn
    &DB_PREFIXEdemandeur
    ON
        demandeur.demandeur = lien_dossier_demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        civilite.civilite = demandeur.particulier_civilite OR civilite.civilite = demandeur.personne_morale_civilite
LEFT JOIN
    &DB_PREFIXEautorite_competente
    ON
        autorite_competente.autorite_competente = dossier.autorite_competente
WHERE
    instruction.date_evenement = (SELECT MAX(instr.date_evenement) FROM &DB_PREFIXEinstruction instr WHERE instr.dossier = dossier.dossier) AND
    LOWER(autorite_competente.code) = ''etat'' AND
    instruction.date_envoi_controle_legalite = ''&date_bordereau''
ORDER BY
    dossier.dossier'
WHERE id = 'bordereau_avis_maire_prefet';

UPDATE om_sousetat SET om_sql = 
'SELECT
    dossier.dossier as dossier,
    evenement.libelle as evenement,
    CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.localite, '' '', demandeur.code_postal, '' '', demandeur.cedex, '' '', demandeur.bp)
         WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, ''\n'', 
                                                              demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp)
    END as coordonnees_demandeur,
    CONCAT ( dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, '' '', dossier.terrain_adresse_lieu_dit, '' '', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite, '' '', dossier.terrain_adresse_cedex, '' '', dossier.terrain_adresse_bp) as coordonnees_terrain
FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXEinstruction
    ON
        instruction.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEevenement
    ON
        instruction.evenement = evenement.evenement
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        lien_dossier_demandeur.dossier = dossier.dossier
LEFT JOIn
    &DB_PREFIXEdemandeur
    ON
        demandeur.demandeur = lien_dossier_demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        civilite.civilite = demandeur.particulier_civilite OR civilite.civilite = demandeur.personne_morale_civilite
LEFT JOIN
    &DB_PREFIXEautorite_competente
    ON
        autorite_competente.autorite_competente = dossier.autorite_competente
WHERE
    instruction.date_evenement = (SELECT MAX(instr.date_evenement) FROM &DB_PREFIXEinstruction instr WHERE instr.dossier = dossier.dossier) AND
    LOWER(evenement.type) = ''arrete'' AND
    instruction.date_envoi_controle_legalite = ''&date_bordereau''
ORDER BY
    dossier.dossier'
WHERE id = 'bordereau_controle_legalite';

UPDATE om_sousetat SET om_sql = 
'SELECT
    row_number() OVER(ORDER BY dossier_commission.dossier DESC) cel1,
    CONCAT(
        ''Dossier:      '', dossier.dossier, ''    '', ''Rap.  '', instructeur.nom, ''
'',
        ''Demandeur :   '', CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom) WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, '' '', demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret) END,''
'',
        CONCAT(demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp) , ''
'',
        ''Adresse travaux : '', TRIM(CONCAT(dossier.terrain_adresse_voie_numero,'' '',dossier.terrain_adresse_voie,'' '', dossier.terrain_adresse_lieu_dit,'' '', dossier.terrain_adresse_code_postal,'' '', dossier.terrain_adresse_localite,'' '', dossier.terrain_adresse_bp,'' '', dossier.terrain_adresse_cedex)), ''
'',
        ''Quartier:     '', arrondissement.libelle, ''
'',
        ''Dépot:        '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''         Délai :'', to_char(dossier.date_limite,''DD/MM/YYYY''), ''
'',
        ''Opération:    '', CASE WHEN dossier_autorisation_type_detaille.code=''PCI'' OR dossier_autorisation_type_detaille.code=''AZ'' THEN donnees_techniques.co_projet_desc WHEN dossier_autorisation_type_detaille.code=''PCA'' THEN CONCAT(donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc) WHEN dossier_autorisation_type_detaille.code=''PD'' THEN    donnees_techniques.dm_projet_desc WHEN dossier_autorisation_type_detaille.code=''PA'' THEN donnees_techniques.am_projet_desc WHEN dossier_autorisation_type_detaille.code=''DAT'' THEN CONCAT(donnees_techniques.am_projet_desc, donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc)  END, ''
'',
        ''SHON:         '', donnees_techniques.su_tot_shon_tot, ''     Nbr logements: '', CASE WHEN dossier_autorisation_type_detaille.code=''PCI'' OR dossier_autorisation_type_detaille.code=''AZ'' THEN donnees_techniques.co_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''PCA'' THEN donnees_techniques.co_tot_log_nb + donnees_techniques.dm_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''PD'' THEN donnees_techniques.dm_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''DAT'' THEN donnees_techniques.co_tot_log_nb + donnees_techniques.dm_tot_log_nb END, ''
'',
        ''nb log T1: '', donnees_techniques.co_log_1p_nb, '' T2: '', donnees_techniques.co_log_2p_nb, '' T3: '', donnees_techniques.co_log_3p_nb, '' T4: '', donnees_techniques.co_log_4p_nb, '' T5: '', donnees_techniques.co_log_5p_nb, '' T6: '', donnees_techniques.co_log_6p_nb, '' Nbr places stationnement: '', donnees_techniques.co_statio_place_nb, ''
''
    ) as cel2,
    dossier_commission.motivation as motivation
FROM
    &DB_PREFIXEcommission
LEFT JOIN
    &DB_PREFIXEdossier_commission
    ON
        commission.commission=dossier_commission.commission
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        dossier_commission.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
LEFT JOIN
    &DB_PREFIXEdemandeur
    ON
        lien_dossier_demandeur.demandeur = demandeur.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        demandeur.particulier_civilite = civilite.civilite OR demandeur.personne_morale_civilite = civilite.civilite
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
        dossier.dossier = donnees_techniques.dossier_instruction
WHERE
    commission.commission = &idx'
WHERE id = 'commission_proposition_ordre_jour';

UPDATE om_sousetat SET om_sql = 
'SELECT
    row_number() OVER(ORDER BY dossier_commission.dossier DESC) cel1,
    CONCAT(
        ''Dossier:      '', dossier.dossier, ''    '', ''Rap.  '', instructeur.nom, ''
'',
        ''Demandeur :   '', CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom) WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, '' '', demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret) END,''
'',
        CONCAT(demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp) , ''
'',
        ''Adresse travaux : '', TRIM(CONCAT(dossier.terrain_adresse_voie_numero,'' '',dossier.terrain_adresse_voie,'' '', dossier.terrain_adresse_lieu_dit,'' '', dossier.terrain_adresse_code_postal,'' '', dossier.terrain_adresse_localite,'' '', dossier.terrain_adresse_bp,'' '', dossier.terrain_adresse_cedex)), ''
'',
        ''Quartier:     '', arrondissement.libelle, ''
'',
        ''Dépot:        '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''         Délai :'', to_char(dossier.date_limite,''DD/MM/YYYY''), ''
'',
        ''Opération:    '', CASE WHEN dossier_autorisation_type_detaille.code=''PCI'' OR dossier_autorisation_type_detaille.code=''AZ'' THEN donnees_techniques.co_projet_desc WHEN dossier_autorisation_type_detaille.code=''PCA'' THEN CONCAT(donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc) WHEN dossier_autorisation_type_detaille.code=''PD'' THEN    donnees_techniques.dm_projet_desc WHEN dossier_autorisation_type_detaille.code=''PA'' THEN donnees_techniques.am_projet_desc WHEN dossier_autorisation_type_detaille.code=''DAT'' THEN CONCAT(donnees_techniques.am_projet_desc, donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc)  END, ''
'',
        ''SHON:         '', donnees_techniques.su_tot_shon_tot, ''     Nbr logements: '', CASE WHEN dossier_autorisation_type_detaille.code=''PCI'' OR dossier_autorisation_type_detaille.code=''AZ'' THEN donnees_techniques.co_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''PCA'' THEN donnees_techniques.co_tot_log_nb + donnees_techniques.dm_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''PD'' THEN donnees_techniques.dm_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''DAT'' THEN donnees_techniques.co_tot_log_nb + donnees_techniques.dm_tot_log_nb END, ''
'',
        ''nb log T1: '', donnees_techniques.co_log_1p_nb, '' T2: '', donnees_techniques.co_log_2p_nb, '' T3: '', donnees_techniques.co_log_3p_nb, '' T4: '', donnees_techniques.co_log_4p_nb, '' T5: '', donnees_techniques.co_log_5p_nb, '' T6: '', donnees_techniques.co_log_6p_nb, '' Nbr places stationnement: '', donnees_techniques.co_statio_place_nb, ''
''
    ) as cel2,
    '''' as motivation
FROM
    &DB_PREFIXEcommission
LEFT JOIN
    &DB_PREFIXEdossier_commission
    ON
        commission.commission=dossier_commission.commission
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        dossier_commission.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
LEFT JOIN
    &DB_PREFIXEdemandeur
    ON
        lien_dossier_demandeur.demandeur = demandeur.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        demandeur.particulier_civilite = civilite.civilite OR demandeur.personne_morale_civilite = civilite.civilite
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
        dossier.dossier = donnees_techniques.dossier_instruction
WHERE
    commission.commission = &idx'
WHERE id = 'commission_ordre_jour';

UPDATE om_sousetat SET om_sql = 
'SELECT
    row_number() OVER(ORDER BY dossier_commission.dossier DESC) cel1,
    CONCAT(
        ''Dossier:      '', dossier.dossier, ''    '', ''Rap.  '', instructeur.nom, ''
'',
        ''Demandeur :   '', CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom) WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, '' '', demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret) END,''
'',
        CONCAT(demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp) , ''
'',
        ''Adresse travaux : '', TRIM(CONCAT(dossier.terrain_adresse_voie_numero,'' '',dossier.terrain_adresse_voie,'' '', dossier.terrain_adresse_lieu_dit,'' '', dossier.terrain_adresse_code_postal,'' '', dossier.terrain_adresse_localite,'' '', dossier.terrain_adresse_bp,'' '', dossier.terrain_adresse_cedex)), ''
'',
        ''Quartier:     '', arrondissement.libelle, ''
'',
        ''Dépot:        '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''         Délai :'', to_char(dossier.date_limite,''DD/MM/YYYY''), ''
'',
        ''Opération:    '', CASE WHEN dossier_autorisation_type_detaille.code=''PCI'' OR dossier_autorisation_type_detaille.code=''AZ'' THEN donnees_techniques.co_projet_desc WHEN dossier_autorisation_type_detaille.code=''PCA'' THEN CONCAT(donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc) WHEN dossier_autorisation_type_detaille.code=''PD'' THEN    donnees_techniques.dm_projet_desc WHEN dossier_autorisation_type_detaille.code=''PA'' THEN donnees_techniques.am_projet_desc WHEN dossier_autorisation_type_detaille.code=''DAT'' THEN CONCAT(donnees_techniques.am_projet_desc, donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc)  END, ''
'',
        ''SHON:         '', donnees_techniques.su_tot_shon_tot, ''     Nbr logements: '', CASE WHEN dossier_autorisation_type_detaille.code=''PCI'' OR dossier_autorisation_type_detaille.code=''AZ'' THEN donnees_techniques.co_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''PCA'' THEN donnees_techniques.co_tot_log_nb + donnees_techniques.dm_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''PD'' THEN donnees_techniques.dm_tot_log_nb WHEN dossier_autorisation_type_detaille.code=''DAT'' THEN donnees_techniques.co_tot_log_nb + donnees_techniques.dm_tot_log_nb END, ''
'',
        ''nb log T1: '', donnees_techniques.co_log_1p_nb, '' T2: '', donnees_techniques.co_log_2p_nb, '' T3: '', donnees_techniques.co_log_3p_nb, '' T4: '', donnees_techniques.co_log_4p_nb, '' T5: '', donnees_techniques.co_log_5p_nb, '' T6: '', donnees_techniques.co_log_6p_nb, '' Nbr places stationnement: '', donnees_techniques.co_statio_place_nb, ''
''
    ) as cel2,
    dossier_commission.avis as motivation
FROM
    &DB_PREFIXEcommission
LEFT JOIN
    &DB_PREFIXEdossier_commission
    ON
        commission.commission=dossier_commission.commission
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        dossier_commission.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
LEFT JOIN
    &DB_PREFIXEdemandeur
    ON
        lien_dossier_demandeur.demandeur = demandeur.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        demandeur.particulier_civilite = civilite.civilite OR demandeur.personne_morale_civilite = civilite.civilite
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
        dossier.dossier = donnees_techniques.dossier_instruction
WHERE
    commission.commission = &idx'
WHERE id = 'commission_compte_rendu';

UPDATE om_sousetat SET om_sql = 
'SELECT

    CONCAT(dossier_autorisation_type.libelle, ''

'',dossier.dossier) as dossier,

    CONCAT(''Dépôt le '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''
Notifié le '', COALESCE(to_char(dossier.date_complet, ''DD/MM/YYYY''), ''inconu'')) as dates,

    CASE
        WHEN petitionnaire_principal.qualite=''particulier''

            THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom, ''
'', (CASE WHEN petitionnaire_principal.complement IS NULL THEN ''''
         ELSE CONCAT(petitionnaire_principal.complement, ''
'') END), petitionnaire_principal.numero,'' '', petitionnaire_principal.voie,''
'', (CASE WHEN petitionnaire_principal.bp IS NULL THEN ''''
         ELSE CONCAT(petitionnaire_principal.bp,''
'') END), petitionnaire_principal.code_postal,'' '', petitionnaire_principal.localite, '' '', petitionnaire_principal.cedex, '' '', petitionnaire_principal.lieu_dit
                                          ))
    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination, ''
'', (CASE WHEN petitionnaire_principal.complement IS NULL THEN ''''
         ELSE CONCAT(petitionnaire_principal.complement, ''
'') END), petitionnaire_principal.numero,'' '', petitionnaire_principal.voie,''
'', (CASE WHEN petitionnaire_principal.bp IS NULL THEN ''''
         ELSE CONCAT(petitionnaire_principal.bp, ''
'') END), petitionnaire_principal.code_postal,'' '', petitionnaire_principal.localite, '' '', petitionnaire_principal.cedex, '' '', petitionnaire_principal.lieu_dit                                      ))
    END as demandeur,

    CONCAT(dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, ''
'', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite,''
Arrondissement : '', arrondissement.libelle) as terrain,


    CONCAT(''superficie : '', '' '', ''
nombre de logements : '', '' '') as informations,

    CONCAT(''Délai '', delai, '' mois
Date limite le '', COALESCE(to_char(date_limite, ''DD/MM/YYYY''), ''inconu'')) as limite

FROM
  &DB_PREFIXEdossier
  LEFT JOIN &DB_PREFIXEdossier_autorisation
    ON dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
  LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille
    ON dossier_autorisation.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
  LEFT JOIN &DB_PREFIXEdossier_autorisation_type
    ON dossier_autorisation_type_detaille.dossier_autorisation_type=dossier_autorisation_type.dossier_autorisation_type
  LEFT JOIN &DB_PREFIXElien_dossier_demandeur
    ON dossier.dossier = lien_dossier_demandeur.dossier
  LEFT JOIN &DB_PREFIXEdemandeur as petitionnaire_principal
    ON lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
  LEFT JOIN &DB_PREFIXEavis_decision
    ON dossier.avis_decision=avis_decision.avis_decision
  LEFT JOIN &DB_PREFIXEarrondissement
    ON dossier.terrain_adresse_code_postal = arrondissement.code_postal
  LEFT JOIN &DB_PREFIXEdonnees_techniques
    ON donnees_techniques.dossier_instruction=dossier.dossier

WHERE
    (select e.statut from &DB_PREFIXEetat e where e.etat = dossier.etat ) = ''encours''

ORDER BY
    dossier_autorisation_type.libelle, arrondissement.libelle'
WHERE id = 'registre_dossiers_affichage_reglementaire';