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

Consultation et edition

Nomenclature du PM

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

#10 Sans informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle nomenclature PM ---*/
DROP TABLE IF EXISTS base_rop.ctrl_10;
CREATE TABLE base_rop.ctrl_10 AS 
WITH t1 AS (
    SELECT st_codeext AS code_pm FROM public.t_sitetech WHERE st_typelog = 'SRO' AND st_codeext IS NOT NULL
    UNION
    SELECT referencepm FROM base_rop.ipe WHERE referencepm IS NOT NULL
    UNION
    SELECT rv_refr_pm FROM base_rop.baserop WHERE rv_refr_pm IS NOT NULL
),
flags AS (
    SELECT
        t1.code_pm,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM public.t_sitetech
                WHERE st_codeext = t1.code_pm AND st_typelog = 'SRO'
            ) THEN
                CASE WHEN t1.code_pm ~ '^HT-TEC-[A-Z0-9]{5}$' THEN TRUE ELSE FALSE END
            ELSE NULL
        END AS easy,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM base_rop.ipe
                WHERE referencepm = t1.code_pm
            ) THEN
                CASE WHEN t1.code_pm ~ '^HT-TEC-[A-Z0-9]{5}$' THEN TRUE ELSE FALSE END
            ELSE NULL
        END AS ipe,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM base_rop.baserop
                WHERE rv_refr_pm = t1.code_pm
            ) THEN
                CASE WHEN t1.code_pm ~ '^HT-TEC-[A-Z0-9]{5}$' THEN TRUE ELSE FALSE END
            ELSE NULL
        END AS baserop
    FROM t1
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C10' AS id_ctr,
    'Nomenclature du PM' AS name_ctr,
    code_pm AS site_sro,
    code_pm AS donnee_ref,
    CASE
        WHEN easy = FALSE OR ipe = FALSE OR baserop = FALSE THEN 'NOK'
        ELSE 'OK'
    END AS statut,
    easy,
    ipe,
    baserop
FROM flags;

Annuler