SELECT

    CONCAT(dossier_autorisation_type_detaille.libelle, ' (',dossier_instruction_type.libelle, ')
     
', dossier.dossier_libelle) as dossier,

    CONCAT('Déposé le ', to_char(dossier.date_depot,'DD/MM/YYYY'), 
    CASE WHEN dossier.date_depot <> COALESCE(dossier.date_complet, dossier.date_depot) THEN CONCAT('
Complété le ', to_char(dossier.date_complet, 'DD/MM/YYYY')) ELSE '' END 
    ) as dates,

    CASE
        WHEN petitionnaire_principal.qualite='particulier'
            THEN CONCAT(
                CASE WHEN COALESCE(civilite.code,'') != '' THEN CONCAT(civilite.code, ' ') END,
                CASE WHEN COALESCE(petitionnaire_principal.particulier_nom, '') != '' THEN CONCAT(petitionnaire_principal.particulier_nom, ' ') END,
                CASE WHEN COALESCE(petitionnaire_principal.particulier_prenom, '') != '' THEN CONCAT(petitionnaire_principal.particulier_prenom, '
') END, 
                &DB_PREFIXEadresse(petitionnaire_principal.numero::text, petitionnaire_principal.voie::text, petitionnaire_principal.complement::text, petitionnaire_principal.lieu_dit::text, petitionnaire_principal.bp::text, petitionnaire_principal.code_postal::text, petitionnaire_principal.localite::text, petitionnaire_principal.cedex::text, '
')
                        )
            ELSE CONCAT(
                CASE WHEN COALESCE(petitionnaire_principal.personne_morale_raison_sociale,'') != '' THEN CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '
') END, 
                CASE WHEN COALESCE(petitionnaire_principal.personne_morale_denomination,'') != '' THEN CONCAT(petitionnaire_principal.personne_morale_denomination, ' 
') END, 
                &DB_PREFIXEadresse(petitionnaire_principal.numero::text, petitionnaire_principal.voie::text, petitionnaire_principal.complement::text, petitionnaire_principal.lieu_dit::text, petitionnaire_principal.bp::text, petitionnaire_principal.code_postal::text, petitionnaire_principal.localite::text, petitionnaire_principal.cedex::text, '
')
                )
            END
            as demandeur,

        CONCAT(
            &DB_PREFIXEadresse(dossier.terrain_adresse_voie_numero::text, dossier.terrain_adresse_voie::text, ''::text, dossier.terrain_adresse_lieu_dit::text, dossier.terrain_adresse_bp::text, dossier.terrain_adresse_code_postal::text, dossier.terrain_adresse_localite::text, dossier.terrain_adresse_cedex::text, '
'),
            CASE WHEN arrondissement.libelle != '' THEN CONCAT('
Arrondissement : ', arrondissement.libelle) END
        ) as Terrain,


    CONCAT(
        CASE WHEN COALESCE(dossier.terrain_superficie::text,'') != '' THEN CONCAT('superficie : ', dossier.terrain_superficie::text,' m²
') END,
        CASE WHEN COALESCE(donnees_techniques.co_tot_log_nb::text, '') != '' THEN CONCAT('nombre de logements : ', donnees_techniques.co_tot_log_nb::text) END
        ) as Informations,

    CONCAT('Délai ', delai, ' mois
Date limite le ', COALESCE((CASE WHEN dossier.incomplet_notifie IS TRUE AND dossier.incompletude IS TRUE 
        THEN to_char(dossier.date_limite_incompletude ,'DD/MM/YYYY') 
        ELSE to_char(dossier.date_limite ,'DD/MM/YYYY')
    END), '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(dossier_autorisation_type.code) IN ('pc','pa','pd','dp')
    AND LOWER(dossier_instruction_type.code) IN ('t','m','p')
    AND dossier.om_collectivite = &collectivite
ORDER BY
    dossier.terrain_adresse_code_postal, dossier_autorisation_type.libelle