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

Consultation et edition

Présence de la position dans le tiroir

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

#19 Avec informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle de la présence de la position dans le tiroir ---*/
DROP TABLE IF EXISTS base_rop.ctrl_19;
CREATE TABLE base_rop.ctrl_19 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_position_pos AS position_tir,
        rv_refr_pm AS site_sro
    FROM base_rop.baserop
    WHERE rv_refr_module IS NOT NULL
),
baserop_check AS (
    SELECT 
        code_tiroir,
        COUNT(CASE 
            WHEN TRIM(position_tir) ~ '^[0-9]+$' AND TRIM(position_tir)::int > 24 THEN 1 
        END) AS nb_positions_sup_24,
        MAX(site_sro) AS site_sro,
        COUNT(*) AS baserop_present_flag
    FROM baserop_clean
    GROUP BY code_tiroir
),
easy_count AS (
    SELECT 
        COALESCE(ti_etiquet, ti_codeext)::TEXT AS code_tiroir,
        COUNT(*) AS nb_positions_easy
    FROM t_position p
    INNER JOIN t_tiroir ti
        ON p.ps_ti_code = ti.ti_code
    WHERE p.ps_ti_code IS NOT NULL
      AND COALESCE(ti_etiquet, ti_codeext) NOT LIKE '%-00'
    GROUP BY COALESCE(ti_etiquet, ti_codeext)
),
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
      AND COALESCE(ti_etiquet, ti_codeext) NOT LIKE '%-00'
    UNION
    SELECT DISTINCT code_tiroir FROM baserop_clean
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C19' AS id_ctr,
    'Présence Tiroir' AS name_ctr,
    COALESCE(st_codeext, baserop_check.site_sro) AS site_sro,
    t1.code_tiroir AS donnee_ref,
    CASE
        WHEN COALESCE(easy_count.nb_positions_easy = 144, FALSE)
             AND COALESCE(baserop_check.baserop_present_flag > 0 
             AND COALESCE(baserop_check.nb_positions_sup_24,0) = 0, FALSE)
        THEN 'OK'
        ELSE 'NOK'
    END AS statut,
    COALESCE(easy_count.nb_positions_easy = 144, FALSE) AS easy,
	CASE 
        WHEN easy_count.nb_positions_easy IS NULL THEN 'NON EXISTANT'
        WHEN easy_count.nb_positions_easy <> 144 THEN '1'
        ELSE '0'
    END AS easy_value,
    COALESCE(
        baserop_check.baserop_present_flag > 0 
        AND COALESCE(baserop_check.nb_positions_sup_24,0) = 0,
        FALSE
    ) AS baserop,
    COALESCE(baserop_check.nb_positions_sup_24::text, 'NON EXISTANT') AS baserop_value
FROM t1
LEFT JOIN (
    SELECT DISTINCT
        COALESCE(ti_etiquet, ti_codeext)::TEXT AS code_tiroir,
        ti_ba_code
    FROM public.t_tiroir
) ti ON ti.code_tiroir = t1.code_tiroir
LEFT JOIN t_baie ON ti.ti_ba_code = t_baie.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'
LEFT JOIN baserop_check ON baserop_check.code_tiroir = t1.code_tiroir
LEFT JOIN easy_count ON easy_count.code_tiroir = t1.code_tiroir;

Annuler