SELECT

    CONCAT(dossier_autorisation_type.libelle, '

',dossier.dossier_libelle) 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(civilite.code, ' ', 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 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_PREFIXEcivilite
    ON
        civilite.civilite = petitionnaire_principal.particulier_civilite
    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
    LEFT JOIN &DB_PREFIXEgroupe
        ON dossier_autorisation_type.groupe = groupe.groupe
    LEFT JOIN &DB_PREFIXEdossier_instruction_type
        ON dossier_instruction_type.dossier_instruction_type = dossier.dossier_instruction_type


WHERE
    (select e.statut from &DB_PREFIXEetat e where e.etat = dossier.etat ) = 'encours'
    AND LOWER(groupe.code) = 'ads'
    AND LOWER(dossier_instruction_type.code) IN ('t','m','p')

ORDER BY
    dossier.terrain_adresse_code_postal, dossier_autorisation_type.libelle