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

Consultation et edition

Nomenclature Tiroir

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

#11 Sans informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle nomenclature tiroir ---*/
DROP TABLE IF EXISTS base_rop.ctrl_11;
CREATE TABLE base_rop.ctrl_11 AS 
WITH baserop_clean AS (
    SELECT
        CONCAT(
            'HT-TIR-',
            SPLIT_PART(rv_refr_module, '-',3), '-',
            SPLIT_PART(rv_refr_module, '-',4), '-',
            SPLIT_PART(rv_refr_module, '-',6)
        ) AS code_tiroir,
        rv_refr_pm AS site_sro
    FROM base_rop.baserop
    WHERE rv_refr_module IS NOT NULL
),
t1 AS (
    SELECT COALESCE(ti_etiquet, ti_codeext)::TEXT AS code_tiroir
    FROM public.t_tiroir
    WHERE COALESCE(ti_etiquet, ti_codeext) IS NOT NULL
    UNION
    SELECT code_tiroir FROM baserop_clean
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C11' AS id_ctr,
    'Nomenclature Tiroir' AS name_ctr,
    COALESCE(st_codeext, baserop_clean.site_sro) AS site_sro,
    t1.code_tiroir AS donnee_ref,
    CASE
        WHEN t1.code_tiroir IS NULL THEN NULL
        WHEN t_tiroir.ti_codeext IS NOT NULL AND baserop_clean.code_tiroir IS NOT NULL AND t1.code_tiroir ~ '^HT-TIR-[0-9A-Z]{5}-02-[0-9]{2}$' THEN 'OK'
        WHEN t_tiroir.ti_codeext IS NOT NULL AND baserop_clean.code_tiroir IS NULL AND t1.code_tiroir ~ '^HT-TIR-[0-9A-Z]{5}-02-[0-9]{2}$' THEN 'OK'
        WHEN t_tiroir.ti_codeext IS NULL AND baserop_clean.code_tiroir IS NOT NULL AND t1.code_tiroir ~ '^HT-TIR-[0-9A-Z]{5}-02-[0-9]{2}$' THEN 'OK'
        ELSE 'NOK'
    END AS statut,
    CASE
        WHEN t1.code_tiroir IS NULL THEN NULL
        WHEN t_tiroir.ti_codeext IS NULL THEN NULL
        WHEN t1.code_tiroir ~ '^HT-TIR-[0-9A-Z]{5}-02-[0-9]{2}$' THEN TRUE
        ELSE FALSE
    END AS easy,
    -- NULL::BOOLEAN AS ipe,
    CASE
        WHEN t1.code_tiroir IS NULL THEN NULL
        WHEN baserop_clean.code_tiroir IS NULL THEN NULL
        WHEN t1.code_tiroir ~ '^HT-TIR-[0-9A-Z]{5}-02-[0-9]{2}$' THEN TRUE
        ELSE FALSE
    END AS baserop
FROM t1
LEFT JOIN baserop_clean ON baserop_clean.code_tiroir = t1.code_tiroir
LEFT JOIN t_tiroir ON t1.code_tiroir = COALESCE(ti_etiquet, ti_codeext)
LEFT JOIN t_baie ON ti_ba_code = ba_code
LEFT JOIN t_ltech ON ba_lt_code = lt_code
LEFT JOIN t_sitetech ON lt_st_code = st_code AND st_typelog = 'SRO';
/*
SELECT
    ROW_NUMBER() OVER () AS id,
    'C11' AS id_ctr,
    'Nomenclature Tiroir' AS name_ctr,
    COALESCE(st_codeext, baserop_clean.site_sro) AS site_sro,
    t1.code_tiroir AS donnee_ref,
    CASE
        WHEN
            (t1.code_tiroir ~ '^HT-TIR-.{5}-.{2}-.{2}$'
             AND EXISTS (
                SELECT 1 FROM public.t_tiroir
                WHERE COALESCE(ti_etiquet, ti_codeext) = t1.code_tiroir
             )
            )
        AND
            (t1.code_tiroir ~ '^HT-TIR-.{5}-.{2}-.{2}$'
             AND baserop_clean.code_tiroir IS NOT NULL
            )
        THEN 'OK'
        ELSE 'NOK'
    END AS statut,
    CASE
        WHEN t1.code_tiroir IS NULL THEN NULL
        WHEN t_tiroir.ti_codeext IS NULL THEN NULL
        WHEN t1.code_tiroir ~ '^HT-TIR-.{5}-.{2}-.{2}$' THEN TRUE
        ELSE FALSE
    END AS easy,
    CASE
        WHEN t1.code_tiroir IS NULL THEN NULL
        WHEN baserop_clean.code_tiroir IS NULL THEN NULL
        WHEN t1.code_tiroir ~ '^HT-TIR-.{5}-.{2}-.{2}$' THEN TRUE
        ELSE FALSE
    END AS baserop
FROM t1
LEFT JOIN baserop_clean ON baserop_clean.code_tiroir = t1.code_tiroir
LEFT JOIN t_tiroir ON t1.code_tiroir = COALESCE(ti_etiquet, ti_codeext)
LEFT JOIN t_baie ON ti_ba_code = ba_code
LEFT JOIN t_ltech ON ba_lt_code = lt_code
LEFT JOIN t_sitetech ON lt_st_code = st_code AND st_typelog = 'SRO';
*/

Annuler