-- lien entre les types de demande et les états
INSERT INTO lien_demande_type_etat(lien_demande_type_etat, demande_type, etat)
SELECT nextval('lien_demande_type_etat_seq'), demande_type_dps.demande_type, lien_demande_type_etat.etat
FROM lien_demande_type_etat
LEFT JOIN demande_type 
    ON lien_demande_type_etat.demande_type = demande_type.demande_type
LEFT JOIN dossier_autorisation_type_detaille
    ON demande_type.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN demande_type as demande_type_dps
    ON demande_type.code = demande_type_dps.code
LEFT JOIN dossier_autorisation_type_detaille as dossier_autorisation_type_detaille_dps
    ON demande_type_dps.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille_dps.dossier_autorisation_type_detaille
WHERE dossier_autorisation_type_detaille.code = 'DP' AND dossier_autorisation_type_detaille_dps.code = 'DPS';

-- Mise à jour du sous-états du registre des affichages réglementaire
UPDATE om_sousetat 
SET om_sql = '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'')
    AND dossier.om_collectivite = &collectivite
ORDER BY
    dossier.terrain_adresse_code_postal, dossier_autorisation_type.libelle'
WHERE id = 'registre_dossiers_affichage_reglementaire';