SELECT ROW_NUMBER() OVER (ORDER BY (lpad(saisie_observation.id_obs::text, 7, '0'::text))) AS gid,
        CASE saisie_observation.determination::text
            WHEN 'Vu'::text THEN 'te'::text
            WHEN 'Entendu'::text THEN 'te'::text
            WHEN 'Indice de présence'::text THEN 'te'::text
            WHEN 'Cadavre'::text THEN 'te'::text
            WHEN 'Capture'::text THEN 'te'::text
            WHEN 'Collection'::text THEN 'Co'::text
            WHEN 'Littérature'::text THEN 'Li'::text
            ELSE 'te'::text
        END AS statutsource,
        CASE saisie_observation.determination::text
            WHEN 'Littérature'::text THEN 'à préciser'::text
            ELSE NULL::text
        END AS referencebiblio,
    lpad(lot_donnee.id_lot::text, 4, '0'::text) AS jddid,
    'SICEN-LR:00-175'::text AS jddcode,
    lpad(saisie_observation.id_obs::text, 7, '0'::text) AS identifiantorigine,
    NULL::text AS identifiantpermanent,
        CASE
            WHEN lot_donnee.libelle::text ~~* 'bénévolat%'::text THEN 'Pr'::text
            WHEN saisie_observation.id_etude = ANY (ARRAY[39, 51]) THEN 'NSP'::text
            ELSE 'Ac'::text
        END AS dspublique,
    NULL::text AS codeidcnpdispositif,
    'CEN L-R'::text AS organismestandard,
        CASE
            WHEN LOWER(saisie_observation.type_effectif) = 'absence'::text THEN 'No'::text
            ELSE 'Pr'::text
        END AS statutobservation,
    btrim(concat(saisie_observation.nom_vern, ' / ', saisie_observation.nom_complet), ' / '::text) AS nomcite,
    saisie_observation.cd_nom::INTEGER AS cdnom,
    taxref.cd_ref::INTEGER AS cdref,
    'non'::text AS sensible,
    COALESCE(saisie_observation.effectif, saisie_observation.effectif_min, 1::BIGINT)::INTEGER AS denombrementmin,
    COALESCE(saisie_observation.effectif, saisie_observation.effectif_max)::INTEGER AS denombrementmax,
        CASE
            WHEN saisie_observation.type_effectif ~~* ANY (ARRAY['abondance%'::text, 'classe%'::text]) THEN 'Es'::text
            ELSE 'Co'::text
        END AS typedenombrement,
        CASE
            WHEN saisie_observation.type_effectif IS NOT NULL THEN 'In'::text
            ELSE NULL::text
        END AS objetdenombrement,
        CASE
            WHEN saisie_observation.observateur = '20'::text THEN 'NSP'::text
            ELSE md.liste_nom_auteur(saisie_observation.observateur, ', '::text)
        END AS identiteobservateur,
        CASE md.liste_nom_structure(saisie_observation.STRUCTURE, ', '::text)
            WHEN 'Pas de structure'::text THEN 'indépendant'::text
            ELSE md.liste_nom_structure(saisie_observation.STRUCTURE, ', '::text)
        END AS organismeobservateur,
    'CEN L-R'::text AS organismegestionnairedonnees,
        CASE
            WHEN saisie_observation.observateur <> '20'::text THEN md.liste_nom_auteur(saisie_observation.observateur, ', '::text)
            ELSE NULL::text
        END AS determinateur,
    btrim(concat(validateur.nom, ' ', validateur.prenom)) AS validateur,
    saisie_observation.remarque_obs AS commentaire,
    COALESCE(saisie_observation.date_obs, saisie_observation.date_debut_obs) AS datedebut,
    COALESCE(saisie_observation.date_obs, saisie_observation.date_fin_obs) AS datefin,
    NULL::TIME WITHOUT TIME zone AS heuredebut,
    NULL::TIME WITHOUT TIME zone AS heurefin,
    NULL::DATE AS datedeterminationobs,
    saisie_observation.elevation::NUMERIC AS altitudemin,
    saisie_observation.elevation::NUMERIC AS altitudemax,
    NULL::NUMERIC AS profondeurmin,
    NULL::NUMERIC AS profondeurmax,
    NULL::text AS codehabitat,
    NULL::text AS refhabitat,
    st_asgml(saisie_observation.geometrie) AS geometrie,
        CASE saisie_observation."precision"
            WHEN 'GPS'::saisie.enum_precision THEN 10
            WHEN '0 à 10m'::saisie.enum_precision THEN 10
            WHEN '10 à 100m'::saisie.enum_precision THEN 100
            WHEN '100 à 500m'::saisie.enum_precision THEN 500
            ELSE 1000
        END AS "precision",
        CASE
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%POINT%'::text THEN 'St'::text
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%POLYGON%'::text OR st_geometrytype(saisie_observation.geometrie) ~~* '%LINE%'::text THEN 'In'::text
            ELSE 'NSP'::text
        END AS natureobjetgeo,
    COALESCE(saisie_observation.code_insee, commune.code_insee::text) AS codecommune,
    commune.nom AS nomcommune,
    sites_cen_inpn_2014.id_mnhn::text AS codeen,
    NULL::text AS typeen,
    NULL::text AS codemaille,
    st_x(st_centroid(saisie_observation.geometrie)) AS st_x,
    st_y(st_centroid(saisie_observation.geometrie)) AS st_y
   FROM saisie.saisie_observation
     JOIN inpn.taxref_v8 taxref USING (cd_nom)
     JOIN ign_bd_topo.commune ON st_intersects(commune.geometrie, saisie_observation.geometrie)
     LEFT JOIN referentiels_divers.sites_cen_inpn_2014 ON st_intersects(sites_cen_inpn_2014.geometrie, saisie_observation.geometrie)
     LEFT JOIN md.lot_donnee ON saisie_observation.id_etude = lot_donnee.id_etude AND saisie_observation.id_protocole = lot_donnee.id_protocole AND ((
        CASE
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text
            ELSE NULL::text
        END || '_'::text) ||
        CASE saisie_observation.regne
            WHEN 'Plantae'::text THEN 'espece'::text
            WHEN 'Animalia'::text THEN 'espece'::text
            WHEN 'Fungi'::text THEN 'espece'::text
            WHEN 'Habitat'::text THEN 'habitat'::text
            ELSE NULL::text
        END) = lot_donnee.type_donnee::text
     LEFT JOIN md.personne validateur ON saisie_observation.validateur = validateur.id_personne
  WHERE taxref.cd_nom::text ~ '^[\d+]'::text AND (saisie_observation.regne = ANY (ARRAY['Animalia'::text, 'Plantae'::text, 'Fungi'::text])) AND (md.liste_nom_structure(saisie_observation.STRUCTURE, ', '::text) = ANY (ARRAY['CEN LR'::text, 'Pas de structure'::text])) AND saisie_observation.cd_nom <> '000000'::text;