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

Consultation et edition

Nomenclature Cable

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

#12 Sans informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle nomenclature des cables au PBO ---*/
DROP TABLE IF EXISTS base_rop.ctrl_12;
CREATE TABLE base_rop.ctrl_12 AS
WITH cable_clean AS (
    SELECT DISTINCT t_cable_patch201.cb_code
    FROM t_cable_patch201
    LEFT JOIN t_ebp ON cb_bp1 = bp_code
    LEFT JOIN t_cable ON t_cable.cb_code = t_cable_patch201.cb_code
    WHERE bp_typelog = 'PBO' AND cb_typelog <> 'RA'
    UNION
    SELECT DISTINCT t_cable_patch201.cb_code
    FROM t_cable_patch201
    LEFT JOIN t_ebp ON cb_bp2 = bp_code
    LEFT JOIN t_cable ON t_cable.cb_code = t_cable_patch201.cb_code
    WHERE bp_typelog = 'PBO' AND cb_typelog <> 'RA'
),
t1 AS (
    SELECT
        COALESCE(cb_etiquet, cb_codeext)::TEXT AS code_cable,
        cb_r3_code AS site_sro
    FROM cable_clean
    LEFT JOIN t_cable ON cable_clean.cb_code = t_cable.cb_code
    UNION
    SELECT
        rv_refr_cable_pbo::TEXT AS code_cable,
        rv_refr_pm AS site_sro
    FROM base_rop.baserop
    WHERE rv_refr_cable_pbo IS NOT NULL
),
easy_flag AS (
    SELECT DISTINCT COALESCE(cb_etiquet, cb_codeext)::TEXT AS code_cable
    FROM t_cable
),
baserop_flag AS (
    SELECT DISTINCT rv_refr_cable_pbo::TEXT AS code_cable
    FROM base_rop.baserop
),
-- Sous-requête avec flags TRUE / FALSE / NULL
flags AS (
    SELECT
        t1.code_cable,
        t1.site_sro,
        CASE
            WHEN e.code_cable IS NOT NULL THEN
                CASE WHEN t1.code_cable ~ '^HT-CB-[A-Z0-9]{7}$' THEN TRUE ELSE FALSE END
            ELSE NULL
        END AS easy,
		-- NULL::BOOLEAN AS ipe,
        CASE
            WHEN b.code_cable IS NOT NULL THEN
                CASE WHEN t1.code_cable ~ '^HT-CB-[A-Z0-9]{7}$' THEN TRUE ELSE FALSE END
            ELSE NULL
        END AS baserop
    FROM t1
    LEFT JOIN easy_flag e ON t1.code_cable = e.code_cable
    LEFT JOIN baserop_flag b ON t1.code_cable = b.code_cable
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C12' AS id_ctr,
    'Nomenclature Cable' AS name_ctr,
    site_sro,
    code_cable AS donnee_ref,
    CASE
        WHEN easy = FALSE OR baserop = FALSE THEN 'NOK'
        ELSE 'OK'
    END AS statut,
    easy,
	-- ipe,
    baserop
FROM flags;

Annuler