Controles | Unification du Référentiel Télécom

Consultation et edition

Comparatif du BAT en fonction du PBO affecté

Les informations du controle se modifient directement dans le formulaire ci-dessous.

#14 Avec informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Comparatif du PBO affecté au BAT ---*/
DROP TABLE IF EXISTS base_rop.ctrl_14;
CREATE TABLE base_rop.ctrl_14 AS 
WITH easy AS (
    SELECT
        ad.ad_batcode AS code_batiment,
        array_agg(DISTINCT COALESCE(ebp.bp_etiquet, ebp.bp_codeext)::text) AS easy_arr
    FROM t_adresse ad
    JOIN t_suf sf ON ad.ad_code = sf.sf_ad_code
    JOIN t_zpbo_patch201 zp ON sf.sf_zp_code = zp.zp_code
    JOIN t_ebp ebp ON zp.zp_bp_code = ebp.bp_code
    GROUP BY ad.ad_batcode
),
ipe_clean AS (
    SELECT
        identifiantimmeuble AS code_batiment,
        array_agg(DISTINCT trim(pbo)::text) AS ipe_arr
    FROM (
        SELECT
            identifiantimmeuble,
            unnest(string_to_array(referencepbo, '|')::text[]) AS pbo
        FROM base_rop.ipe
    ) t
    GROUP BY identifiantimmeuble
),
baserop_clean AS (
    SELECT
        lrc_idnt_immeuble AS code_batiment,
        array_agg(DISTINCT trim(pbo)::text) AS baserop_arr
    FROM (
        SELECT
            lrc_idnt_immeuble,
            unnest(string_to_array(rv_refr_pbo, ';')::text[]) AS pbo
        FROM base_rop.baserop
    ) t
    GROUP BY lrc_idnt_immeuble
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C14' AS id_ctr,
    'Comparatif du BAT en fonction du PBO affecté' AS name_ctr,
    COALESCE(cb.site_easy, cb.site_ipe, cb.site_baserop) AS site_sro,
    cb.code_batiment AS donnee_ref,
	CASE 
    WHEN 
        COALESCE((easy.easy_arr && ipe.ipe_arr OR easy.easy_arr && baserop.baserop_arr), FALSE)
        AND
        COALESCE((ipe.ipe_arr && easy.easy_arr OR ipe.ipe_arr && baserop.baserop_arr), FALSE)
        AND
        COALESCE((baserop.baserop_arr && easy.easy_arr OR baserop.baserop_arr && ipe.ipe_arr), FALSE)
    THEN 'OK'
    ELSE 'NOK'
	END AS statut,
    COALESCE(
        (easy.easy_arr && ipe.ipe_arr OR easy.easy_arr && baserop.baserop_arr),
        FALSE
    ) AS easy,
    COALESCE(array_to_string(easy.easy_arr, '; '), 'NON EXISTANT') AS easy_value,
    COALESCE(
        (ipe.ipe_arr && easy.easy_arr OR ipe.ipe_arr && baserop.baserop_arr),
        FALSE
    ) AS ipe,
    COALESCE(array_to_string(ipe.ipe_arr, '; '), 'NON EXISTANT') AS ipe_value,
    COALESCE(
        (baserop.baserop_arr && easy.easy_arr OR baserop.baserop_arr && ipe.ipe_arr),
        FALSE
    ) AS baserop,
    COALESCE(array_to_string(baserop.baserop_arr, '; '), 'NON EXISTANT') AS baserop_value
FROM base_rop.code_batiment cb
LEFT JOIN easy ON cb.code_batiment = easy.code_batiment
LEFT JOIN ipe_clean ipe ON cb.code_batiment = ipe.code_batiment
LEFT JOIN baserop_clean baserop ON cb.code_batiment = baserop.code_batiment;

Annuler