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

Consultation et edition

Présence Tiroir

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

#6 Sans informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle présence Tiroir ---*/
DROP TABLE IF EXISTS base_rop.ctrl_06;
CREATE TABLE base_rop.ctrl_06 AS 
WITH baserop_clean AS (
	SELECT DISTINCT ON (code_tiroir)
		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,
	'C06' AS id_ctr,
	'Présence Tiroir' AS name_ctr,
	COALESCE(st_codeext, baserop_clean.site_sro) AS site_sro,
	t1.code_tiroir AS donnee_ref,
	CASE
		WHEN ti.code_tiroir IS NOT NULL
		 AND baserop_clean.code_tiroir IS NOT NULL
		THEN 'OK'
		ELSE 'NOK'
	END AS statut,
	(ti.code_tiroir IS NOT NULL) AS easy,
	-- NULL::BOOLEAN AS ipe,
	(baserop_clean.code_tiroir IS NOT NULL) AS baserop
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_clean	ON baserop_clean.code_tiroir = t1.code_tiroir;

Annuler