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.complement 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'