return: text
lang: plpgsql
src: |
  DECLARE	
  	
  	
  	
  	result TEXT;
  BEGIN
  	-- Réplication des médecins dans Base
  	IF EXISTS (SELECT * FROM activite.t_medecins_administratifs WHERE oid <> 0 AND COALESCE(medecin_id,0) = 0) THEN
  		BEGIN
  			PERFORM base.cti_initialize_medecins();
  			EXCEPTION 
  				WHEN others THEN RAISE NOTICE 'Erreur %' , 'base.cti_initialize_medecins'; 
  		END;
  	END IF;
  	
  	BEGIN
  		-- Mise à jour code médecin
  		UPDATE base.t_medecins
  		SET code = subview.code
  		FROM
  		(
  			SELECT medecin_id, (MAX(Array[date_entree::text, t_medecins_administratifs.code]))[2] AS code
  			FROM activite.p_sejours
  			JOIN activite.t_medecins_administratifs ON medecin_sejour_id = t_medecins_administratifs.oid
  			JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid AND t_medecins.oid <> 0 AND (t_medecins.code = '' OR t_medecins.code IS NULL)
  			GROUP BY 1
  		) subview
  		WHERE t_medecins.oid = subview.medecin_id;
  		UPDATE base.t_medecins
  		SET code = subview.code
  		FROM
  		(
  			SELECT medecin_id, (MAX(Array[date_debut::text, t_medecins_administratifs.code]))[2] AS code
  			FROM activite.p_factures_lignes_h
  			JOIN activite.t_medecins_administratifs ON p_factures_lignes_h.medecin_facture_id = t_medecins_administratifs.oid
  			JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid AND t_medecins.oid <> 0 AND (t_medecins.code = '' OR t_medecins.code IS NULL)
  			GROUP BY 1
  		) subview
  		WHERE t_medecins.oid = subview.medecin_id;
  		UPDATE base.t_medecins
  		SET code = subview.code
  		FROM
  		(
  			SELECT medecin_id, (MAX(Array[date_debut::text, t_medecins_administratifs.code]))[2] AS code
  			FROM activite.p_factures_lignes_non_facturees_h
  			JOIN activite.t_medecins_administratifs ON p_factures_lignes_non_facturees_h.medecin_facture_id = t_medecins_administratifs.oid
  			JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid AND t_medecins.oid <> 0 AND (t_medecins.code = '' OR t_medecins.code IS NULL)
  			GROUP BY 1
  		) subview
  		WHERE t_medecins.oid = subview.medecin_id;
  		UPDATE base.t_medecins
  		SET code = subview.code
  		FROM
  		(
  			SELECT medecin_id, (MIN(Array[to_char(t_medecins_administratifs.oid,'FM000000000000000000000000'), t_medecins_administratifs.code]))[2] AS code
  			FROM activite.t_medecins_administratifs 
  			JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid AND t_medecins.oid <> 0 AND (t_medecins.code = '' OR t_medecins.code IS NULL)
  			GROUP BY 1
  		) subview
  		WHERE t_medecins.oid = subview.medecin_id;
  		UPDATE base.t_medecins
  		SET code = subview.code
  		FROM
  		(
  		SELECT t_medecins_administratifs.medecin_id,  MAX(t_medecins_administratifs.code) AS code
  		FROM activite.t_medecins_administratifs
  		JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid
  		LEFT JOIN activite.t_budget_cle_rule ON
  			t_medecins.code <> '' AND
  			t_budget_cle_rule.liste_medecin LIKE '(R)%' AND
  			t_budget_cle_rule.liste_medecin LIKE '%'||t_medecins.code||'%'
  		WHERE t_medecins_administratifs.oid <> 0 AND
  			t_medecins_administratifs.medecin_id <> 0 AND
  			t_budget_cle_rule.oid IS NULL
  		GROUP BY 1
  		HAVING count(*) = 1 AND
  			MAX(t_medecins_administratifs.code) <> MAX(t_medecins.code)
  		) subview
  		WHERE t_medecins.oid = subview.medecin_id
  		;
  		EXCEPTION 
  		WHEN others THEN RAISE NOTICE 'Erreur %' , '1'; 
  	END;
  	BEGIN
  		INSERT INTO base.t_specialites_medecin (code_original, code, texte, texte_court)
  		SELECT t_specialites_internes_pmsi.code, t_specialites_internes_pmsi.code, t_specialites_internes_pmsi.texte, t_specialites_internes_pmsi.texte
  		FROM base.t_medecins
  			JOIN activite.t_medecins_administratifs ON medecin_id = t_medecins.oid
  			JOIN pmsi.t_medecins t_medecins_pmsi ON t_medecins_administratifs.code = t_medecins_pmsi.code
  			JOIN pmsi.t_specialites_internes_medecin t_specialites_internes_pmsi ON t_medecins_pmsi.specialite_interne_id = t_specialites_internes_pmsi.oid
  		WHERE t_medecins.specialite_id = 0 AND
  			t_specialites_internes_pmsi.code NOT IN (SELECT code_original FROM base.t_specialites_medecin)
  		GROUP BY 1,2,3,4;
  		EXCEPTION 
  			WHEN others THEN RAISE NOTICE 'Erreur %' , '2'; 
  	END;
  	
  	
  	BEGIN
  		UPDATE base.t_medecins
  		SET specialite_id = t_specialites_medecin.oid
  		FROM activite.t_medecins_administratifs
  			JOIN pmsi.t_medecins t_medecins_pmsi ON t_medecins_administratifs.code = t_medecins_pmsi.code
  			JOIN pmsi.t_specialites_internes_medecin t_specialites_internes_pmsi ON t_medecins_pmsi.specialite_interne_id = t_specialites_internes_pmsi.oid
  			JOIN base.t_specialites_medecin ON t_specialites_internes_pmsi.code = t_specialites_medecin.code_original
  		WHERE t_medecins_administratifs.medecin_id = t_medecins.oid AND
  			t_medecins.specialite_id = 0;
  		EXCEPTION 
  			WHEN others THEN RAISE NOTICE 'Erreur %' , '3'; 
  	END;
  	
  	-- Mise à jour du code rpps médecin des médecins administratifs depuis les données du module base
  	BEGIN 
  		UPDATE activite.t_medecins_administratifs
  		SET code_rpps = subview.code_rpps
  		FROM 
  			(
  				SELECT t_medecins.code_rpps, t_medecins.oid
  				FROM base.t_medecins
  					JOIN activite.t_medecins_administratifs ON t_medecins_administratifs.medecin_id = t_medecins.oid
  				WHERE t_medecins.code_rpps <> '' 
  					AND medecin_id <> 0
  			) subview
  		WHERE t_medecins_administratifs.code_rpps = ''
  			AND t_medecins_administratifs.medecin_id = subview.oid
  		;
  		EXCEPTION 
  			WHEN others THEN RAISE NOTICE 'Erreur %' , 'Mise à jour des code rpps médecins'; 
  	END;
  	-- Conditions d'exercice, alimentation automatique d'une classe
  	BEGIN
  		INSERT INTO activite.t_classes(
  		    code, 
  		    texte, 
  		    table_id, 
  		    is_cti, 
  		    sequence, 
  		    contenu_by_code, 
  		    contenu_non_replicable_ailleurs
  		    )
  		SELECT 
  		    'CTI_MEDLIBSAL'::text AS code, 
  		    'Conditions d''exercice'::text AS texte, 
  		    t_listes_tables.oid AS table_id, 
  		    '1'::text AS is_cti, 
  		    (SELECT COALESCE(MAX(sequence),0)+1 FROM activite.t_classes JOIN activite.t_listes_tables ON t_classes.table_id = t_listes_tables.oid AND t_listes_tables.code = 'MEDECIN' WHERE sequence < 30) AS sequence, 
  		    '0'::text AS contenu_by_code, 
  		    '1'::text AS contenu_non_replicable_ailleurs
  		FROM activite.t_listes_tables
  		WHERE t_listes_tables.Code = 'MEDECIN' AND 
  			'CTI_MEDLIBSAL'::text NOT IN (SELECT code FROM activite.t_classes)
  		;
  
  		INSERT INTO activite.t_classes_sections(
  		    code, 
  		    texte, 
  		    classe_id, 
  		    condition, 
  		    include_code, 
  		    exclude_code
  		    )
  		SELECT 
  		    '1'::text AS code, 
  		    'Libéraux'::text AS texte, 
  		    t_classes.oid AS classe_id, 
  		    ''::text AS condition, 
  		    ''::text AS include_code, 
  		    ''::text AS exclude_code
  		FROM activite.t_classes
  		WHERE t_classes.code = 'CTI_MEDLIBSAL' AND
  			(t_classes.oid::text||'-1') NOT IN (SELECT classe_id::text||'-'||code FROM activite.t_classes_sections WHERE code IS NOT NULL)
  		;
  
  		INSERT INTO activite.t_classes_sections(
  		    code, 
  		    texte, 
  		    classe_id, 
  		    condition, 
  		    include_code, 
  		    exclude_code
  		    )
  		SELECT 
  		    '2'::text AS code, 
  		    'Salariés'::text AS texte, 
  		    t_classes.oid AS classe_id, 
  		    ''::text AS condition, 
  		    ''::text AS include_code, 
  		    ''::text AS exclude_code
  		FROM activite.t_classes
  		WHERE t_classes.code = 'CTI_MEDLIBSAL' AND
  			(t_classes.oid::text||'-2') NOT IN (SELECT classe_id::text||'-'||code FROM activite.t_classes_sections WHERE code IS NOT NULL)
  		;
  			
  		
  		DROP TABLE IF EXISTS w_classe_CTI_MEDLIBSAL;
  		CREATE TEMP TABLE w_classe_CTI_MEDLIBSAL AS
  		SELECT t_medecins_administratifs.oid AS to_id,
  			MIN(classe_id) AS classe_id,
  			MIN(section_id_1) AS section_id_1,
  			MIN(section_id_2) AS section_id_2,
  			MIN(CASE WHEN est_medecin_salarie = '1' OR t_medecins.numero_ordre = t_finess.code THEN section_id_2 ELSE section_id_1 END) AS section_id
  		FROM activite.t_medecins_administratifs
  		LEFT JOIN base.t_finess ON t_finess.secondaire = '0'
  		LEFT JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid
  		JOIN 
  		(
  		SELECT classe_id, 
  			MAX(CASE WHEN t_classes_sections.code = '1' THEN t_classes_sections.oid ELSE 0 END) AS section_id_1, 
  			MAX(CASE WHEN t_classes_sections.code = '2' THEN t_classes_sections.oid ELSE 0 END) AS section_id_2
  		FROM activite.t_classes
  		JOIN activite.t_classes_sections ON classe_id = t_classes.oid
  		WHERE t_classes.code = 'CTI_MEDLIBSAL'
  		GROUP BY 1
  		) subview ON 1=1
  		GROUP BY 1
  		;
  
  		DELETE
  		FROM activite.t_classes_sections_elements
  			USING w_classe_CTI_MEDLIBSAL
  		WHERE t_classes_sections_elements.to_id = w_classe_CTI_MEDLIBSAL.to_id AND
  			t_classes_sections_elements.section_id = section_id_2 AND
  			w_classe_CTI_MEDLIBSAL.section_id = section_id_1
  		;
  			
  		DELETE
  		FROM activite.t_classes_sections_elements
  			USING w_classe_CTI_MEDLIBSAL
  		WHERE t_classes_sections_elements.to_id = w_classe_CTI_MEDLIBSAL.to_id AND
  			t_classes_sections_elements.section_id = section_id_1 AND
  			w_classe_CTI_MEDLIBSAL.section_id = section_id_2
  		;
  		
  		INSERT INTO activite.t_classes_sections_elements(section_id, to_id)
  		SELECT section_id, to_id
  		FROM w_classe_CTI_MEDLIBSAL
  		WHERE (section_id::text||to_id::text) NOT IN (SELECT (section_id::text||to_id::text) FROM activite.t_classes_sections_elements)
  		;
  
  		PERFORM activite.cti_update_schema_classes('MEDECIN')
  		;
  	EXCEPTION 
  			WHEN others THEN RAISE NOTICE 'Erreur %' , '4'; 
  	END;
  	
  	BEGIN
  		UPDATE activite.t_medecins_administratifs_c
  		SET
  			adm_code = t_medecins_administratifs.code, 
  			adm_nom = t_medecins_administratifs.nom, 
  			adm_prenom = t_medecins_administratifs.prenom, 
  			adm_nom_prenom = (t_medecins_administratifs.nom || ' ') || t_medecins_administratifs.prenom,
  			medecin_id = t_medecins_administratifs.medecin_id,
  			medecin_code = COALESCE(t_medecins.code,''),
  			nom = t_medecins.nom,
  			prenom = t_medecins.prenom, 
  			nom_prenom = (t_medecins.nom || ' ') || t_medecins.prenom,
  			specialite_id = t_medecins.specialite_id, 
  			specialite_code = t_specialites_medecin.code, 
  			specialite_texte = t_specialites_medecin.texte_court 
  		FROM activite.t_medecins_administratifs, base.t_medecins, base.t_specialites_medecin 
  		WHERE 	t_medecins_administratifs_c.oid = t_medecins_administratifs.oid
  			AND t_medecins_administratifs.medecin_id = t_medecins.oid 
  			AND t_medecins.specialite_id = t_specialites_medecin.oid  
  			AND (
  				t_medecins_administratifs_c.adm_code IS DISTINCT FROM t_medecins_administratifs.code OR
  				t_medecins_administratifs_c.adm_nom IS DISTINCT FROM t_medecins_administratifs.nom OR
  				t_medecins_administratifs_c.adm_prenom IS DISTINCT FROM t_medecins_administratifs.prenom OR
  				t_medecins_administratifs_c.adm_nom_prenom IS DISTINCT FROM (t_medecins_administratifs.nom || ' ' || t_medecins_administratifs.prenom) OR
  				t_medecins_administratifs_c.medecin_id IS DISTINCT FROM t_medecins_administratifs.medecin_id OR
  				t_medecins_administratifs_c.medecin_code IS DISTINCT FROM COALESCE (t_medecins.code,'') OR
  				t_medecins_administratifs_c.nom IS DISTINCT FROM t_medecins.nom OR
  				t_medecins_administratifs_c.prenom IS DISTINCT FROM t_medecins.prenom OR
  				t_medecins_administratifs_c.nom_prenom IS DISTINCT FROM (t_medecins.nom || ' ' || t_medecins.prenom) OR
  				t_medecins_administratifs_c.specialite_id IS DISTINCT FROM t_medecins.specialite_id OR
  				t_medecins_administratifs_c.specialite_code IS DISTINCT FROM t_specialites_medecin.code OR
  				t_medecins_administratifs_c.specialite_texte IS DISTINCT FROM t_specialites_medecin.texte_court
  			);
  		EXCEPTION 
  		WHEN others THEN RAISE NOTICE 'Erreur %' , '5'; 
  	END;
  	BEGIN
  		INSERT INTO activite.t_medecins_administratifs_c( 
  			oid, adm_code, adm_nom, adm_prenom, adm_nom_prenom, medecin_id, medecin_code, 
  			nom, prenom, nom_prenom, specialite_id, specialite_code, specialite_texte) 
  		SELECT 
  			t_medecins_administratifs.oid, t_medecins_administratifs.code, t_medecins_administratifs.nom, t_medecins_administratifs.prenom, 
  			(t_medecins_administratifs.nom::text || ' '::text) || t_medecins_administratifs.prenom::text, 
  			t_medecins_administratifs.medecin_id, COALESCE(t_medecins.code,''), t_medecins.nom, t_medecins.prenom, 
  			(t_medecins.nom::text || ' '::text) || t_medecins.prenom::text, 
  			t_medecins.specialite_id, t_specialites_medecin.code, t_specialites_medecin.texte_court 
  		FROM activite.t_medecins_administratifs, base.t_medecins, base.t_specialites_medecin 
  		WHERE t_medecins_administratifs.medecin_id = t_medecins.oid 
  			AND t_medecins.specialite_id = t_specialites_medecin.oid  
  			AND t_medecins_administratifs.oid NOT IN (SELECT oid FROM activite.t_medecins_administratifs_c)
  		ORDER BY t_medecins_administratifs.oid;
  		UPDATE activite.t_medecins_administratifs_c
  		SET medecin_code = subview.code
  		FROM 
  			(SELECT subview.medecin_id, t_medecins_administratifs.code
  			FROM activite.t_medecins_administratifs, 
  				(SELECT medecin_id, min(oid) AS oid FROM activite.t_medecins_administratifs WHERE medecin_id <> 0 GROUP BY 1) subview
  			WHERE t_medecins_administratifs.oid = subview.oid) subview
  		WHERE t_medecins_administratifs_c.medecin_id = subview.medecin_id AND medecin_code = '';
  		EXCEPTION 
  		WHEN others THEN RAISE NOTICE 'Erreur %' , '6'; 
  	END;
  	
  	RETURN 'OK';
  END;