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

Consultation et edition

Comparatif du BAT en fonction du Tiroir affecté

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

#17 Avec informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle du Tiroir en fonction du BAT ---*/
DROP TABLE IF EXISTS base_rop.ctrl_17;
CREATE TABLE base_rop.ctrl_17 AS 
WITH t1 AS (
    SELECT DISTINCT ON (rt_code)
        rt_code,
        FIRST_VALUE(rt_fo_code) OVER (PARTITION BY rt_code ORDER BY rt_fo_ordr ASC) AS fo_code_min,
        FIRST_VALUE(rt_fo_code) OVER (PARTITION BY rt_code ORDER BY rt_fo_ordr DESC) AS fo_code_max
    FROM t_ropt
),
t2 AS (
    SELECT DISTINCT t_cable_patch201.cb_code, t_ebp.bp_code
    FROM t_cable_patch201
    JOIN t_ebp ON cb_bp1 = bp_code OR cb_bp2 = bp_code
    JOIN t_cable ON t_cable.cb_code = t_cable_patch201.cb_code
    WHERE bp_typelog = 'PBO' AND cb_typelog <> 'RA'
),
t3 AS (
    SELECT 
        t1.rt_code,
        COALESCE(t2a.bp_code, t2b.bp_code) AS bp_code
    FROM t1
    LEFT JOIN t_fibre fmin ON t1.fo_code_min = fmin.fo_code
    LEFT JOIN t_fibre fmax ON t1.fo_code_max = fmax.fo_code
    LEFT JOIN t2 t2a ON t2a.cb_code = fmin.fo_cb_code
    LEFT JOIN t2 t2b ON t2b.cb_code = fmax.fo_cb_code
    WHERE t2a.cb_code IS NOT NULL OR t2b.cb_code IS NOT NULL
),
easy AS (
    SELECT
        ad_batcode AS code_batiment,
        array_agg(DISTINCT COALESCE(ti_etiquet, ti_codeext)::text) AS easy_arr
    FROM t_position
    LEFT JOIN t_tiroir ON ps_ti_code = ti_code
    LEFT JOIN t_ropt ON ps_2 = t_ropt.rt_fo_code
    LEFT JOIN t3 ON t3.rt_code = t_ropt.rt_code
    LEFT JOIN t_zpbo_patch201 ON zp_bp_code = t3.bp_code
    LEFT JOIN t_suf ON zp_code = sf_zp_code
    LEFT JOIN t_adresse ON sf_ad_code = ad_code
    WHERE ps_ti_code IS NOT NULL
      AND ps_2 IS NOT NULL
      AND COALESCE(ti_etiquet, ti_codeext) NOT LIKE '%-00'
      AND ad_batcode IS NOT NULL
    GROUP BY ad_batcode
),
baserop AS (
    SELECT
        lrc_idnt_immeuble AS code_batiment,
        array_agg(DISTINCT 
            CONCAT(
                'HT-TIR-',
                SPLIT_PART(rv_refr_module, '-',3), '-',
                SPLIT_PART(rv_refr_module, '-',4), '-',
                SPLIT_PART(rv_refr_module, '-',6)
            )::text
        ) AS baserop_arr
    FROM base_rop.baserop
    WHERE rv_refr_module IS NOT NULL
    GROUP BY lrc_idnt_immeuble
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C17' AS id_ctr,
    'Comparatif du BAT en fonction du Tiroir 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 && baserop.baserop_arr, FALSE)
            AND
            COALESCE(baserop.baserop_arr && easy.easy_arr, FALSE)
        THEN 'OK'
        ELSE 'NOK'
    END AS statut,
    COALESCE(easy.easy_arr && baserop.baserop_arr, FALSE) AS easy,
    COALESCE(array_to_string(easy.easy_arr, '; '), 'NON EXISTANT') AS easy_value,
    COALESCE(baserop.baserop_arr && easy.easy_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 baserop ON cb.code_batiment = baserop.code_batiment;

Annuler