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

Consultation et edition

Nomenclature du PBO

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

#9 Sans informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle nomenclature boitier PBO ---*/
DROP TABLE IF EXISTS base_rop.ctrl_09;
--
CREATE TABLE base_rop.ctrl_09 AS 
WITH pbo_ref AS (
    SELECT
        cb.code_pbo,
        COALESCE(cb.site_easy, cb.site_ipe, cb.site_baserop) AS site_sro
    FROM base_rop.code_pbo cb
),
easy_flag AS (
    SELECT DISTINCT COALESCE(bp_etiquet, bp_codeext)::text AS code_pbo
    FROM t_ebp
    WHERE bp_typelog = 'PBO'
),
ipe_flag AS (
    SELECT DISTINCT TRIM(unnest(string_to_array(referencepbo, '|'))) AS code_pbo
    FROM base_rop.ipe
    WHERE referencepbo IS NOT NULL
),
baserop_flag AS (
    SELECT DISTINCT rv_refr_pbo::text AS code_pbo
    FROM base_rop.baserop
),
flags AS (
    SELECT
        p.code_pbo,
        p.site_sro,
        CASE
            WHEN e.code_pbo IS NOT NULL THEN
                CASE WHEN p.code_pbo ~ '^HT-(BPE|BPI)-[A-Z0-9]{5}-.{2}$' THEN TRUE ELSE FALSE END
            ELSE NULL
        END AS easy,
        CASE
            WHEN i.code_pbo IS NOT NULL THEN
                CASE WHEN p.code_pbo ~ '^HT-(BPE|BPI)-[A-Z0-9]{5}-.{2}$' THEN TRUE ELSE FALSE END
            ELSE NULL
        END AS ipe,
        CASE
            WHEN b.code_pbo IS NOT NULL THEN
                CASE WHEN p.code_pbo ~ '^HT-(BPE|BPI)-[A-Z0-9]{5}-.{2}$' THEN TRUE ELSE FALSE END
            ELSE NULL
        END AS baserop
    FROM pbo_ref p
    LEFT JOIN easy_flag e ON p.code_pbo = e.code_pbo
    LEFT JOIN ipe_flag i ON p.code_pbo = i.code_pbo
    LEFT JOIN baserop_flag b ON p.code_pbo = b.code_pbo
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C09' AS id_ctr,
    'Nomenclature du PBO' AS name_ctr,
    site_sro,
    code_pbo 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