return: text
lang: plpgsql
src: |
  DECLARE	
  	result TEXT;
  	nb_years_max_src NUMERIC;
  BEGIN
  
    ----- https://www.lespmsi.com/algorithme-de-calcul-du-supplement-rea-reanimation-en-mco/
    ----- https://www.lespmsi.com/supplement-src-nouvelles-regles-de-declenchement-et-modification-de-la-liste-des-codes-cim-10/
  
  	nb_years_max_src = 
      (
          SELECT COALESCE(valeur, '2') 
          FROM pmsi.t_divers 
          WHERE code = 'ANN_DEBUT_RECUP_SEJ_SRC'
      );
  	
  	RAISE NOTICE '%' , 'Parametres. Nombre d''annees a recuperer : ' || nb_years_max_src;
  
  	RAISE NOTICE '%' , 'Suppression des éléments de la table p_rss_src';
  
  	TRUNCATE pmsi.p_rss_src;
  	
  	RAISE NOTICE '%' , 'listes utilisées par l''étude SRC';
  
  	DROP TABLE IF EXISTS w_listes_src
  	; 
  	CREATE TEMP TABLE w_listes_src AS
  	SELECT liste_code, to_id  
      FROM pmsi.v_listes_3 
      WHERE v_listes_3.liste_code IN ('CTI_REA25_71A', 'CTI_REA25_72A', 'CTI_SRC18_82A', 'CTI_SRC18_81D', 'CTI_SRC18_83D', 'CTI_SRC19_82A', 'CTI_SRC19_81D', 'CTI_SRC19_83D', 'CTI_SRC20_82A', 'CTI_SRC20_81D', 'CTI_SRC20_83D', 'CTI_SRC21_82A', 'CTI_SRC21_81D', 'CTI_SRC21_83D', 'CTI_SRC22_82A', 'CTI_SRC22_81D', 'CTI_SRC22_83D', 'CTI_SRC23_82A', 'CTI_SRC23_81D', 'CTI_SRC23_83D', 'CTI_SRC24_82A', 'CTI_SRC24_81D', 'CTI_SRC24_83D', 'CTI_SRC25_82A', 'CTI_SRC25_81D', 'CTI_SRC25_83D')
  	;
  	
  	-----------------------	**
  	RAISE NOTICE '%' , 'Création table temp_pmsi000143_rss';
  	--Table temp_pmsi000143_rss :
  	--
  	--finess ................................	: Finess du RSS 
  	--no_rss ................................	: Numéro du RSS 
  	--oid ...................................	: Id du RSS 
  	--date_sortie ...........................	: Date de sortie du RSS
  	--age ...................................	: Age du patient
  	--src_facturee_nb_rss ...................	: SRC facturé (1 ou 0)
  	--src_facturee_nb_prestations ...........	: Nombre de SRC facturés
  	--src_facturee_base_remboursement .......	: Montant des SRC facturés
  	--src_facturable_nb_rss .................	: SRC facturable (1 ou 0)
  	--src_facturable_nb_prestations .........	: Nombre de SRC facturables
  	--src_facturable_base_remboursement .....	: Montant des SRC facturables
  	--src_facturable_regle ..................	: Numéro de la règle qui permet de facturer un SRC
  	--src_facturable_regle_justificatif .....	: Texte de la règle qui permet de facturer un SRC
  	--src_passage_nb_rss ....................	: Passage dans une UM avec autorisation '03' (SRC)
  	--src_passage_duree_sejour ..............	: Nombre de jours dans l'UM avec autorisation '03'
  	--src_passage_base_remboursement ........	: S/O
  	--src_facturable_sans_src_ni_igs_nb_rss .	:
  	--rea_passage_nb_rss ....................	: Passage dans une UM avec autorisation '01' (REA)
  	--igs2_rum ..............................	: IGS du RUM
  
  	DROP TABLE IF EXISTS temp_pmsi000143_rss;
  	CREATE TEMP TABLE temp_pmsi000143_rss(
  		finess character(9), 
  		no_rss bigint, 
  		oid bigint, 
  		date_sortie date, 
  		age integer,
  		src_facturee_nb_rss numeric(5) DEFAULT 0,
  		src_facturee_nb_prestations numeric(5) DEFAULT 0,
  		src_facturee_base_remboursement numeric(11, 2) DEFAULT 0,
  		src_facturable_nb_rss numeric(5) DEFAULT 0,
  		src_facturable_nb_prestations numeric(5) DEFAULT 0,
  		src_facturable_base_remboursement numeric(11, 2) DEFAULT 0,
  		src_facturable_regle character(1) DEFAULT '',
  		src_facturable_regle_justificatif character varying(255) DEFAULT '',
  		src_passage_nb_rss numeric(5) DEFAULT 0,
  		src_passage_duree_sejour numeric(5) DEFAULT 0,
  		src_passage_base_remboursement numeric(11, 2) DEFAULT 0,
  		src_facturable_sans_src_ni_igs_nb_rss numeric(5) DEFAULT 0,
  		rea_passage_nb_rss numeric(5) DEFAULT 0,
        igs2_rum integer DEFAULT 0
  	);
  	
  	CREATE INDEX temp_pmsi000143_rss_i1
  	ON temp_pmsi000143_rss
  	USING btree
  	(oid);
  
  	-----------------------	**
  	RAISE NOTICE '%' , 'Création table temp_pmsi000143_rsf_detail';		
  	--Table temp_pmsi000143_rsf_detail :
  	--
  	--date_debut ............	: Date d'entrée dans le RUM
  	--date_fin ..............	: Date de sortie du RUM
  	--nombre ................	: Nombre de journées dans le RUM (minimum 1)
  	--prix_unitaire .........	: Tarif unitaire du supplément juournalier
  	--base_remboursement ....	: Montant total du supplément journalier
  	--coefficient_mco .......	: Coefficient MCO
  	--prestation_id .........	: Identifiant de la prestation
  	--regle .................	: Numéro de la règle d'attribution d'un supplément SRC
  	--rss_id ................	: Identifiant du Séjour
  	
  	
  	DROP TABLE IF EXISTS temp_pmsi000143_rsf_detail;
  	CREATE TEMP TABLE temp_pmsi000143_rsf_detail
  	(
  		date_debut date,
  		date_fin date,
  		nombre numeric(13,2) DEFAULT 1,
  		prix_unitaire numeric(13,2) DEFAULT 0,
  		base_remboursement numeric(13,2) DEFAULT 0,
  		coefficient_mco numeric(5,4) DEFAULT 0,
  		prestation_id bigint DEFAULT 0,
  		regle character(1) DEFAULT '',
  		rss_id bigint DEFAULT 0
  	);
  
  	-----------------------	**
  	RAISE NOTICE '%' , 'Sélection de tous les dossiers';		
  
    INSERT INTO temp_pmsi000143_rss (
      finess
      ,no_rss
      ,oid
      ,date_sortie
      ,age
      )
    SELECT finess
      ,no_rss
      ,rss_id
      ,date_sortie
      ,age
    FROM pmsi.v_rss_1
    WHERE 1 = 1
      AND ghm_id > 0
      -- évite de récupérer la totalité des séjours (recup seulement N-2)
      AND date_sortie BETWEEN ((to_char(CURRENT_DATE, 'YYYY')::INT - nb_years_max_src)::TEXT || '0101')::DATE
        AND '20990228'::DATE
      AND duree_sejour >= 1;
  
    DELETE
    FROM temp_pmsi000143_rss
    WHERE oid IN (
        SELECT rss_id
        FROM pmsi.v_rsf_total_1
        WHERE 1 = 1
          AND code_pec_code <> 1
        GROUP BY rss_id
        );
  	-----------------------	**
  	RAISE NOTICE '%' , 'Dossiers avec supplément SRC facturé';
  		
  		UPDATE temp_pmsi000143_rss SET
  			src_facturee_nb_rss = nb_rss,
  			src_facturee_nb_prestations = nb_prestations,
  			src_facturee_base_remboursement = base_remboursement
  		
  		FROM
  			(
  				SELECT 
  					rss_id, 
  					1 as nb_rss, 
  					SUM(nombre) as nb_prestations, 
  					SUM(base_remboursement) as base_remboursement 
  				FROM 
  					pmsi.p_rsf_detail
  					JOIN temp_pmsi000143_rss ON temp_pmsi000143_rss.oid = p_rsf_detail.rss_id
  					JOIN pmsi.t_prestations ON t_prestations.oid = p_rsf_detail.prestation_id
  				WHERE 1=1
  					AND est_ligne_rss = '1' 
  					AND t_prestations.code = 'SRC'
  				GROUP BY rss_id
  			) subview
  		WHERE subview.rss_id = temp_pmsi000143_rss.oid;
  					
  
  	-----------------------	**
  	RAISE NOTICE '%' , 'Dossiers passés dans une UM qui possède une autorisation SRC puis ceux avec une autorisation REA';
  		
  							
  		UPDATE temp_pmsi000143_rss SET
  			src_passage_nb_rss = nb_rss,
  			src_passage_duree_sejour = duree_sejour,
            igs2_rum = igs2
  		FROM
  			(	SELECT 
  					rss_id, 
  					1 as nb_rss, 
  					SUM(duree_sejour) as duree_sejour,
                    MAX(igs2) AS igs2
  				FROM 
  					pmsi.p_rss_rum 
  					JOIN temp_pmsi000143_rss ON temp_pmsi000143_rss.oid = p_rss_rum.rss_id
  					JOIN pmsi.t_unites_medicales ON p_rss_rum.unite_medicale_id = t_unites_medicales.oid
  				WHERE
                  t_unites_medicales.type_autorisation LIKE '03%' OR t_unites_medicales.type_autorisation LIKE '14%'
  				GROUP BY rss_id
  			) subview
  		WHERE subview.rss_id = temp_pmsi000143_rss.oid;
  		
  		UPDATE temp_pmsi000143_rss SET
  			rea_passage_nb_rss = nb_rss
  		FROM
  			(	SELECT 
  					rss_id, 
  					1 as nb_rss, 
  					SUM(duree_sejour) as duree_sejour
  				FROM 
  					pmsi.p_rss_rum
  					JOIN temp_pmsi000143_rss ON temp_pmsi000143_rss.oid = p_rss_rum.rss_id
  					JOIN pmsi.t_unites_medicales ON p_rss_rum.unite_medicale_id = t_unites_medicales.oid
  				WHERE
  					t_unites_medicales.type_autorisation LIKE '01%' 
  				GROUP BY rss_id
  			) subview
  		WHERE subview.rss_id = temp_pmsi000143_rss.oid;
  					
  		
  	-----------------------	**
  	RAISE NOTICE '%' , 'Réinitialise les codes de supplément dans la table des RUMs';
  									
  		UPDATE pmsi.p_rss_rum SET 
  			supplement_code = '' 
  		FROM 
  			temp_pmsi000143_rss
  		WHERE 
  			p_rss_rum.rss_id = temp_pmsi000143_rss.oid
  			AND (supplement_code IS NULL OR supplement_code = '');
  		
  
  	-----------------------	**
  	RAISE NOTICE '%' , 'Suppression des tables temporaires';
  							
  		DROP TABLE IF EXISTS w_rss_1_12;
  		DROP TABLE IF EXISTS w_rss_2_12;
  		DROP TABLE IF EXISTS w_rss_3_12; 
  		DROP TABLE IF EXISTS w_rss_3B_12; 
  		DROP TABLE IF EXISTS w_rss_4_12; 
  		
  		
  	-----------------------	**
  	RAISE NOTICE '%' , 'Dossiers avec un acte dans la liste 1 de l''annexe 7';
  						
  		CREATE TEMP TABLE w_rss_1_12 AS  
  		SELECT 
  			rss_id, 
  			no_rum 
  		FROM 
  			pmsi.p_rss_actes 
  			JOIN temp_pmsi000143_rss ON p_rss_actes.rss_id = temp_pmsi000143_rss.oid
  			JOIN w_listes_src ON w_listes_src.to_id = p_rss_actes.acte_id
  		WHERE 1=1  
  			AND w_listes_src.liste_code = 'CTI_REA25_71A'; 
  						
  		
  	-----------------------	**
  	RAISE NOTICE '%' , 'Dossiers avec un acte de la liste 2 de l''annexe 7 realisé au moins 3 fois';
  				
  		CREATE TEMP TABLE w_rss_2_12 AS  
  		SELECT 
  			rss_id, 
  			no_rum, 
  			acte_id, 
  			count(*) 
  		FROM 
  			pmsi.p_rss_actes
  			JOIN temp_pmsi000143_rss ON p_rss_actes.rss_id = temp_pmsi000143_rss.oid
  			JOIN w_listes_src ON w_listes_src.to_id = p_rss_actes.acte_id
  		WHERE 1=1
  			AND w_listes_src.liste_code = 'CTI_REA25_72A'
  		GROUP BY rss_id, no_rum, acte_id 
  		HAVING count(*) >= 3; 
  		
  			
  	-----------------------	**
  	RAISE NOTICE '%' , 'RUMs dont l''association DP/acte apparait dans la liste 1 de l''annexe 8';
  		
  		CREATE TEMP TABLE w_rss_3_12 AS  
  		SELECT 
  			p_rss_diagnostics.rss_id::text || '|'::text || p_rss_diagnostics.no_rum::text AS rss_rum_id, 
  			p_rss_diagnostics.rss_id as rss_id, 
  			p_rss_diagnostics.no_rum as no_rum, 
  			p_rss_diagnostics.diagnostic_id, 
  			t_diagnostics.code as diagnostic_principal_code,
  			t_diagnostics.code as regle_justificatif
  		FROM 
  			pmsi.p_rss_diagnostics
  			JOIN temp_pmsi000143_rss ON p_rss_diagnostics.rss_id = temp_pmsi000143_rss.oid
  			JOIN pmsi.t_diagnostics ON p_rss_diagnostics.diagnostic_id = t_diagnostics.oid
  			JOIN pmsi.t_unites_medicales ON  p_rss_diagnostics.unite_medicale_id = t_unites_medicales.oid
  			JOIN w_listes_src ON w_listes_src.to_id = p_rss_diagnostics.diagnostic_id
  		WHERE 1=1  
  			AND ( 
  			     (temp_pmsi000143_rss.date_sortie < '20190301' AND w_listes_src.liste_code = 'CTI_SRC18_81D')  
  			     OR ( temp_pmsi000143_rss.date_sortie >='20190301' AND  temp_pmsi000143_rss.date_sortie < '20200301' AND w_listes_src.liste_code = 'CTI_SRC19_81D')
  			     OR ( temp_pmsi000143_rss.date_sortie >='20200301' AND  temp_pmsi000143_rss.date_sortie < '20210301' AND w_listes_src.liste_code = 'CTI_SRC20_81D')
  			     OR ( temp_pmsi000143_rss.date_sortie >='20210301' AND  temp_pmsi000143_rss.date_sortie < '20220301' AND w_listes_src.liste_code = 'CTI_SRC21_81D')
  			     OR ( temp_pmsi000143_rss.date_sortie >='20220301' AND  temp_pmsi000143_rss.date_sortie < '20230301' AND w_listes_src.liste_code = 'CTI_SRC22_81D')
  			     OR ( temp_pmsi000143_rss.date_sortie >='20230301' AND  temp_pmsi000143_rss.date_sortie < '20240301' AND w_listes_src.liste_code = 'CTI_SRC23_81D')
  			     OR ( temp_pmsi000143_rss.date_sortie >='20230301' AND  temp_pmsi000143_rss.date_sortie < '20250301' AND w_listes_src.liste_code = 'CTI_SRC24_81D')
  			     OR ( temp_pmsi000143_rss.date_sortie >='20250301' AND w_listes_src.liste_code = 'CTI_SRC25_81D')
  			)
           AND (type_autorisation LIKE '03%'OR type_autorisation LIKE '14%')
  		;
  			
  		CREATE INDEX w_rss_3_12_i1
  		ON w_rss_3_12
  		USING btree
  		(rss_id);
  		
  		
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('S2700', 'S2710', 'S2720', 'S2730') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes 
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE
  											t_actes.code IN ('AFLB007', 'AFLB017', 'GGJB001')
                  	                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]);
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('J90', 'J91', 'J930', 'J931', 'J938', 'J939', 'S2700', 'S2710', 'S2720', 'S2730') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes 
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'GGJB001'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code = 'T754'
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'DEQP007'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('R571', 'T311', 'T321')
  			AND rss_id != ALL(ARRAY(	SELECT
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE
  											t_actes.code IN ('ENLF001', 'EPLF002', 'EQLF002')
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('T311', 'T321') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code IN ('EQLF002', 'HSLD002')
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('R570', 'R578') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'EQLF001'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('D62', 'I850', 'I983', 'K250', 'K252', 'K254', 'K260', 'K262', 'K264', 'K920', 'K921', 'K922', 'O720', 'O721', 'O722') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE
  											t_actes.code = 'FELF011'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('J680','T202', 'T203', 'T206', 'T207') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'GELD005'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code = 'I501'
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code IN ('GLLD003', 'GLLD017')
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('I501', 'I5010', 'I5011', 'I5012', 'I5019', 'J960', 'J9600', 'J9601', 'J9609') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code IN ('GLLD003', 'GLLD015', 'GLLD017')
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  			
  		DELETE from w_rss_3_12 WHERE 
  			diagnostic_principal_code IN ('K500', 'K501', 'K508', 'K509', 'K510', 'K512', 'K513', 'K514', 'K515', 'K518', 'K519') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3_12 w_rss_3_12_bis ON w_rss_3_12_bis.rss_id = p_rss_actes.rss_id
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code IN ('HSLF001', 'HSLF002', 'HSLF003')
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]);
  	
  	-----------------------	**
  	RAISE NOTICE '%' , 'RUMs dont l''association DP/acte apparait dans la liste 3 de l''annexe 8';
  							
  		CREATE TEMP TABLE w_rss_3b_12 AS  
  		SELECT 
  			p_rss_diagnostics.rss_id::text || '|'::text || p_rss_diagnostics.no_rum::text AS rss_rum_id, 
  			p_rss_diagnostics.rss_id as rss_id, 
  			p_rss_diagnostics.no_rum as no_rum, 
  			p_rss_diagnostics.diagnostic_id, 
  			t_diagnostics.code as diagnostic_principal_code,
  			t_diagnostics.code as regle_justificatif
  		FROM 
  			pmsi.p_rss_diagnostics
  			JOIN temp_pmsi000143_rss ON p_rss_diagnostics.rss_id = temp_pmsi000143_rss.oid 
  			JOIN pmsi.t_diagnostics ON p_rss_diagnostics.diagnostic_id = t_diagnostics.oid 
  			JOIN pmsi.t_unites_medicales ON  p_rss_diagnostics.unite_medicale_id = t_unites_medicales.oid
  			JOIN w_listes_src ON w_listes_src.to_id = p_rss_diagnostics.diagnostic_id
  		WHERE 1=1
  			AND temp_pmsi000143_rss.age < 18
        AND  ( 
               ( temp_pmsi000143_rss.date_sortie < '20190301' AND w_listes_src.liste_code = 'CTI_SRC18_83D' ) 
               OR ( temp_pmsi000143_rss.date_sortie >='20190301' AND  temp_pmsi000143_rss.date_sortie < '20200301' AND w_listes_src.liste_code = 'CTI_SRC19_83D')
               OR ( temp_pmsi000143_rss.date_sortie >='20200301' AND  temp_pmsi000143_rss.date_sortie < '20210301' AND w_listes_src.liste_code = 'CTI_SRC20_83D')
               OR ( temp_pmsi000143_rss.date_sortie >='20210301' AND  temp_pmsi000143_rss.date_sortie < '20220301' AND w_listes_src.liste_code = 'CTI_SRC21_83D')
               OR ( temp_pmsi000143_rss.date_sortie >='20220301' AND  temp_pmsi000143_rss.date_sortie < '20230301' AND w_listes_src.liste_code = 'CTI_SRC22_83D')
               OR ( temp_pmsi000143_rss.date_sortie >='20230301' AND  temp_pmsi000143_rss.date_sortie < '20240301' AND w_listes_src.liste_code = 'CTI_SRC23_83D')
               OR ( temp_pmsi000143_rss.date_sortie >='20240301' AND  temp_pmsi000143_rss.date_sortie < '20250301' AND w_listes_src.liste_code = 'CTI_SRC24_83D')
               OR ( temp_pmsi000143_rss.date_sortie >='20250301' AND w_listes_src.liste_code = 'CTI_SRC25_83D')
              )
           AND (type_autorisation LIKE '03%'OR type_autorisation LIKE '14%')
      ;
    		
  		DELETE from w_rss_3b_12 WHERE 
  			diagnostic_principal_code IN ('E743', 'K523', 'K529', 'K560', 'K904', 'K908', 'K912', 'K93828', 'Q431', 'Q432', 'Q793') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3b_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes 
  											JOIN w_rss_3b_12 w_rss_3b_12_bis ON w_rss_3b_12_bis.rss_id = p_rss_actes.rss_id 
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'HSLF003'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  		
  		DELETE from w_rss_3b_12 WHERE 
  			diagnostic_principal_code IN ('J188', 'J840', 'J841','J848') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3b_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3b_12 w_rss_3b_12_bis ON w_rss_3b_12_bis.rss_id = p_rss_actes.rss_id 
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'GEQE004'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  		
  		DELETE from w_rss_3b_12 WHERE 
  			diagnostic_principal_code = 'K560' 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3b_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3b_12 w_rss_3b_12_bis ON w_rss_3b_12_bis.rss_id = p_rss_actes.rss_id 
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'HSLF002'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]);  
  		
  		DELETE from w_rss_3b_12 WHERE 
  			diagnostic_principal_code = 'R040' 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3b_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3b_12 w_rss_3b_12_bis ON w_rss_3b_12_bis.rss_id = p_rss_actes.rss_id 
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code IN ('GABD001', 'GASE001', 'GASE002')
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  		
  		DELETE from w_rss_3b_12 WHERE 
  			diagnostic_principal_code IN ('J960', 'J9600', 'J9601', 'J9609') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3b_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3b_12 w_rss_3b_12_bis ON w_rss_3b_12_bis.rss_id = p_rss_actes.rss_id 
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'GLLD019'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  		
  		DELETE from w_rss_3b_12 WHERE 
  			diagnostic_principal_code IN ('J960', 'J9600', 'J9601', 'J9609') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3b_12_bis.rss_id 
  										FROM
  											pmsi.p_rss_actes
  											JOIN w_rss_3b_12 w_rss_3b_12_bis ON w_rss_3b_12_bis.rss_id = p_rss_actes.rss_id 
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'GLLD019'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]);  
  		
  		DELETE from w_rss_3b_12 WHERE 
  			diagnostic_principal_code IN ('G934', 'K220', 'K224') 
  			AND rss_id != ALL(ARRAY(	SELECT 
  											DISTINCT w_rss_3b_12_bis.rss_id 
  										FROM 
  											pmsi.p_rss_actes
  											JOIN w_rss_3b_12 w_rss_3b_12_bis ON w_rss_3b_12_bis.rss_id = p_rss_actes.rss_id 
  											JOIN pmsi.t_actes ON t_actes.oid = p_rss_actes.acte_id
  			                                JOIN pmsi.t_unites_medicales ON t_unites_medicales.oid = p_rss_actes.unite_medicale_id
  										WHERE 
  											t_actes.code = 'HSLD002'
  	                                        AND (type_autorisation LIKE '03%' OR type_autorisation LIKE '14%')
  									)::bigint[]); 
  					
  	-----------------------	**
  	RAISE NOTICE '%' , 'RUMs avec acte dans la liste 2 de l''annexe 8';
  				
  		CREATE TEMP TABLE w_rss_4_12 AS  
  		SELECT 
  			p_rss_actes.rss_id, 
  			p_rss_actes.no_rss, 
  			p_rss_actes.no_rum, 
  			p_rss_rum.date_sortie, 
  			base.cti_group_concat(DISTINCT t_actes.code) as regle_justificatif
  		FROM 
  			pmsi.p_rss_actes
  			JOIN pmsi.t_actes ON p_rss_actes.acte_id = t_actes.oid
  			JOIN pmsi.p_rss_rum ON p_rss_actes.rss_id = p_rss_rum.rss_id AND p_rss_actes.no_rum = p_rss_rum.no_rum 
  			JOIN temp_pmsi000143_rss ON p_rss_actes.rss_id = temp_pmsi000143_rss.oid
  			JOIN w_listes_src ON w_listes_src.to_id = p_rss_actes.acte_id 
  		WHERE 1=1  
  			AND p_rss_actes.activite_ccam <> '4'
  			AND  ( 
                      ( temp_pmsi000143_rss.date_sortie < '20190301' AND w_listes_src.liste_code = 'CTI_SRC18_82A' ) 
                      OR ( temp_pmsi000143_rss.date_sortie >= '20190301' AND temp_pmsi000143_rss.date_sortie < '20200301' AND w_listes_src.liste_code = 'CTI_SRC19_82A')
                      OR ( temp_pmsi000143_rss.date_sortie >= '20200301' AND temp_pmsi000143_rss.date_sortie < '20210301' AND w_listes_src.liste_code = 'CTI_SRC20_82A')
                      OR ( temp_pmsi000143_rss.date_sortie >= '20210301' AND temp_pmsi000143_rss.date_sortie < '20220301' AND w_listes_src.liste_code = 'CTI_SRC21_82A')
                      OR ( temp_pmsi000143_rss.date_sortie >= '20220301' AND temp_pmsi000143_rss.date_sortie < '20230301' AND w_listes_src.liste_code = 'CTI_SRC22_82A')
                      OR ( temp_pmsi000143_rss.date_sortie >= '20230301' AND temp_pmsi000143_rss.date_sortie < '20240301' AND w_listes_src.liste_code = 'CTI_SRC23_82A')
                      OR ( temp_pmsi000143_rss.date_sortie >= '20240301' AND temp_pmsi000143_rss.date_sortie < '20250301' AND w_listes_src.liste_code = 'CTI_SRC24_82A')
                      OR ( temp_pmsi000143_rss.date_sortie >= '20250301' AND w_listes_src.liste_code = 'CTI_SRC25_82A')
                  )
  		GROUP BY 1,2,3,4; 
  					
  	
  	-----------------------	**
  	RAISE NOTICE '%' , 'Positionne un supplement REA si acte dans liste 1 annexe 7 et ((IGS2 >= 15 et age >= 18) ou (age < 18) et RUM avec autorisation ''01''';
  					
  		UPDATE pmsi.p_rss_rum SET 
  			supplement_code = 'REA'   
  		FROM 
  			temp_pmsi000143_rss
  			, w_rss_1_12  
  		WHERE 
  			temp_pmsi000143_rss.oid = p_rss_rum.rss_id 
  			AND supplement_code = ''  
  			AND ((p_rss_rum.igs2 >= 15 AND temp_pmsi000143_rss.age >= 18) OR temp_pmsi000143_rss.age < 18) 
  			AND unite_medicale_id IN (SELECT oid FROM pmsi.t_unites_medicales WHERE type_autorisation LIKE '01%') 
  			AND p_rss_rum.rss_id = w_rss_1_12.rss_id 
  			AND p_rss_rum.no_rum = w_rss_1_12.no_rum; 
  					
  	
  	-----------------------	**
  	RAISE NOTICE '%' , 'Positionne un supplement REA si acte dans liste 2 annexe 7 realise 3 fois et ((IGS2 >= 15 et age >= 18) ou (age < 18) et RUM avec autorisation ''01''';
  							
  		UPDATE pmsi.p_rss_rum SET 
  			supplement_code = 'REA'    
  		FROM 
  			temp_pmsi000143_rss
  			, w_rss_2_12   
  		WHERE 
  			temp_pmsi000143_rss.oid = p_rss_rum.rss_id 
  			AND supplement_code = ''   
  			AND ((p_rss_rum.igs2 >= 15 AND temp_pmsi000143_rss.age >= 18) OR temp_pmsi000143_rss.age < 18)  
  			AND unite_medicale_id IN (SELECT oid FROM pmsi.t_unites_medicales WHERE type_autorisation LIKE '01%')  
  			AND p_rss_rum.rss_id = w_rss_2_12.rss_id 
  			AND p_rss_rum.no_rum = w_rss_2_12.no_rum; 
  					
  
  	-----------------------	**
  	RAISE NOTICE '%' , 'Positionne un flag SRC sur les RUMS où :
  					. l''UM possède une autorisation SRC
  					. 
  						. l''IGS >= 7 et DP/acte dans liste 1 Annexe 8 et age >= 18
  						OU
  						. DP/acte dans liste 3 Annexe 8 et age < 18';
  						
  		UPDATE pmsi.p_rss_rum SET 
  			supplement_code = 'SRC2'  
  		FROM 
  			temp_pmsi000143_rss   
  		WHERE 1=1 
  		  AND temp_pmsi000143_rss.oid = p_rss_rum.rss_id 
  		  AND supplement_code = ''   
  		  AND (  
  		  	(temp_pmsi000143_rss.age < 40 AND p_rss_rum.igs2 >= 7)  
  		  	OR  
  		  	(temp_pmsi000143_rss.age BETWEEN 40 AND 59 AND p_rss_rum.igs2 >= 14)  
  		  	OR  
  		  	(temp_pmsi000143_rss.age BETWEEN 60 AND 69 AND p_rss_rum.igs2 >= 19)  
  		  	OR  
  		  	(temp_pmsi000143_rss.age BETWEEN 70 AND 74 AND p_rss_rum.igs2 >= 22)  
  		  	OR  
  		  	(temp_pmsi000143_rss.age BETWEEN 75 AND 79 AND p_rss_rum.igs2 >= 23)  
  		  	OR  
  		  	(temp_pmsi000143_rss.age >= 80 AND p_rss_rum.igs2 >= 25)  
  		  	)  
  		    AND unite_medicale_id IN (SELECT oid FROM pmsi.t_unites_medicales WHERE (type_autorisation LIKE '03%'OR type_autorisation LIKE '14%'))  
    			AND p_rss_rum.rss_id::text || '|'::text || p_rss_rum.no_rum::text IN (SELECT rss_rum_id FROM w_rss_3_12); 
  			
  		UPDATE pmsi.p_rss_rum SET 
  			supplement_code = 'SRC2'  
  		FROM 
  			temp_pmsi000143_rss   
  		WHERE 1=1 
    		AND temp_pmsi000143_rss.oid = p_rss_rum.rss_id 
    		AND supplement_code = ''   
    		AND temp_pmsi000143_rss.age < 18   
    		AND unite_medicale_id IN (SELECT oid FROM pmsi.t_unites_medicales WHERE (type_autorisation LIKE '03%'OR type_autorisation LIKE '14%'))  
    		AND p_rss_rum.rss_id::text || '|'::text || p_rss_rum.no_rum::text IN (SELECT rss_rum_id FROM w_rss_3b_12);
  					
  		
  	-----------------------	**
  	RAISE NOTICE '%' , 'Positionne un flag SRC sur les RUMS où :
  					. l''UM possède une autorisation SRC
  					. l''IGS >= 15';
  
  		UPDATE pmsi.p_rss_rum SET supplement_code = 'SRC3'   
  		FROM temp_pmsi000143_rss   
  		WHERE temp_pmsi000143_rss.oid = p_rss_rum.rss_id   
  		AND supplement_code = ''   
  		AND (  
  				(temp_pmsi000143_rss.age < 40 AND p_rss_rum.igs2 >= 15)  
  				OR  
  				(temp_pmsi000143_rss.age BETWEEN 40 AND 59 AND p_rss_rum.igs2 >= 22)  
  				OR  
  				(temp_pmsi000143_rss.age BETWEEN 60 AND 69 AND p_rss_rum.igs2 >= 27)  
  				OR  
  				(temp_pmsi000143_rss.age BETWEEN 70 AND 74 AND p_rss_rum.igs2 >= 30)  
  				OR  
  				(temp_pmsi000143_rss.age BETWEEN 75 AND 79 AND p_rss_rum.igs2 >= 31)  
  				OR  
  				(temp_pmsi000143_rss.age >= 80 AND p_rss_rum.igs2 >= 33)  
  			)  
  		AND unite_medicale_id IN (SELECT oid FROM pmsi.t_unites_medicales WHERE (type_autorisation LIKE '03%'OR type_autorisation LIKE '14%'));  
  					
  	
  	-----------------------	**
  	RAISE NOTICE '%' , 'Positionne un flag SRC sur les RUMS où :
  					. l''UM possède une autorisation SRC
  					. un des actes apparaît dans la liste 2 de l''annexe 8';
  
  		UPDATE pmsi.p_rss_rum SET supplement_code = 'SRC4'  
  		FROM temp_pmsi000143_rss, w_rss_4_12   
  		WHERE 
  			temp_pmsi000143_rss.oid = p_rss_rum.rss_id   
  			AND supplement_code = ''   
  			AND unite_medicale_id IN (SELECT oid FROM pmsi.t_unites_medicales WHERE (type_autorisation LIKE '03%'OR type_autorisation LIKE '14%'))  
  			AND p_rss_rum.rss_id = w_rss_4_12.rss_id AND (p_rss_rum.date_entree = w_rss_4_12.date_sortie OR p_rss_rum.no_rum = w_rss_4_12.no_rum);  
  					
  	
  	-----------------------	**
  	RAISE NOTICE 'Positionne un flag SRC sur les RUMS où :
  					. l''UM possède une autorisation SRC
  					. le RUM précédent a été flagué avec un supplément ''REA''
  					. Le séjour a une provenance ''R'' (Transfert depuis Réanimation) associé à un mode d''entrée 0 (Transfert inter-établissement) ou 7 (Transfert vers un autre établissement)';
  									
  		UPDATE pmsi.p_rss_rum SET supplement_code = 'SRC5'    
  		FROM temp_pmsi000143_rss, (SELECT rss_id, no_rum FROM pmsi.p_rss_rum WHERE supplement_code LIKE 'REA%' ) subview  
  		WHERE 
  			temp_pmsi000143_rss.oid = p_rss_rum.rss_id   
  			AND supplement_code = ''   
  			AND unite_medicale_id IN (SELECT oid FROM pmsi.t_unites_medicales WHERE type_autorisation LIKE '03%'OR type_autorisation LIKE '14%')  
  			AND p_rss_rum.rss_id = subview.rss_id AND p_rss_rum.no_rum = subview.no_rum + 1; 
  		
  		UPDATE pmsi.p_rss_rum SET supplement_code = 'SRC5'    
  		FROM temp_pmsi000143_rss
  		WHERE 
  			temp_pmsi000143_rss.oid = p_rss_rum.rss_id   
  			AND supplement_code = ''   
  			AND unite_medicale_id IN (SELECT oid FROM pmsi.t_unites_medicales WHERE type_autorisation LIKE '03%'OR type_autorisation LIKE '14%')  
  			AND p_rss_rum.no_rum = 1
  			AND p_rss_rum.provenance = 'R'
  			AND (p_rss_rum.mode_entree = '0' OR p_rss_rum.mode_entree = '7'); 
  	
  					
  	-----------------------	**
  	RAISE NOTICE 'Remplit la table avec tous les RUMs précédemment marqués comme facturables (SRCS, SRC3, SRC4,...)';
  
  		INSERT INTO temp_pmsi000143_rsf_detail(  
  		date_debut, date_fin, regle, nombre, prix_unitaire, base_remboursement, coefficient_mco, rss_id)  
  		
  		SELECT 
  			p_rss_rum.date_entree, 
  			p_rss_rum.date_sortie, 
  			SUBSTR(supplement_code, 4 , 1), 
  			CASE WHEN p_rss_rum.date_sortie > p_rss_rum.date_entree THEN 
  					CASE WHEN p_rss_rum.mode_sortie <> '9' THEN p_rss_rum.date_sortie - p_rss_rum.date_entree 
  					ELSE p_rss_rum.date_sortie - p_rss_rum.date_entree + 1 END 
  			ELSE 1 END, 
  			CASE 
  				WHEN t_finess.type_etablissement = '1' THEN
  					CASE
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_1 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_1 THEN t_prestations.tarif_prive_1 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_2 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_2 THEN t_prestations.tarif_prive_2
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_3 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_3 THEN t_prestations.tarif_prive_3 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_4 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_4 THEN t_prestations.tarif_prive_4
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_5 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_5 THEN t_prestations.tarif_prive_5 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_6 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_6 THEN t_prestations.tarif_prive_6 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_7 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_7 THEN t_prestations.tarif_prive_7 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_8 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_8 THEN t_prestations.tarif_prive_8 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_prive_9 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_prive_9 THEN t_prestations.tarif_prive_9
  						ELSE 0::numeric END
  				ELSE
  					CASE
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_1 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_1 THEN t_prestations.tarif_public_1 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_2 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_2 THEN t_prestations.tarif_public_2
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_3 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_3 THEN t_prestations.tarif_public_3 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_4 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_4 THEN t_prestations.tarif_public_4
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_5 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_5 THEN t_prestations.tarif_public_5 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_6 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_6 THEN t_prestations.tarif_public_6 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_7 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_7 THEN t_prestations.tarif_public_7 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_8 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_8 THEN t_prestations.tarif_public_8 
  						WHEN temp_pmsi000143_rss.date_sortie >= t_prestations.date_debut_public_9 AND temp_pmsi000143_rss.date_sortie <= t_prestations.date_fin_public_9 THEN t_prestations.tarif_public_9
  						ELSE 0::numeric END
  				END,
  			0, 
  			1, 
  			temp_pmsi000143_rss.oid 
  		FROM 
  			pmsi.p_rss_rum
  			JOIN temp_pmsi000143_rss ON temp_pmsi000143_rss.oid = p_rss_rum.rss_id
  			JOIN base.t_finess ON t_finess.code = temp_pmsi000143_rss.finess
  			,pmsi.t_prestations 
  		WHERE 1=1
  			AND supplement_code <> '' 
  			AND supplement_code LIKE 'SRC%'
  			AND t_prestations.code = 'SRC'; 
  			
  
  	-----------------------	**
  	RAISE NOTICE ' ';
  				
  		UPDATE temp_pmsi000143_rsf_detail SET nombre = temp_pmsi000143_rsf_detail.nombre - 1
  		FROM temp_pmsi000143_rss, temp_pmsi000143_rsf_detail temp_pmsi000143_rsf_detail2
  		WHERE 
  			temp_pmsi000143_rsf_detail.rss_id = temp_pmsi000143_rss.oid 
  			AND temp_pmsi000143_rsf_detail.date_debut <> temp_pmsi000143_rsf_detail.date_fin
  			AND temp_pmsi000143_rsf_detail2.rss_id = temp_pmsi000143_rss.oid 
  			AND temp_pmsi000143_rsf_detail2.date_debut = temp_pmsi000143_rsf_detail.date_debut 
  			AND temp_pmsi000143_rsf_detail2.date_fin = temp_pmsi000143_rsf_detail2.date_debut; 
  		
  	-----------------------	**
  	RAISE NOTICE ' ';					
  		
  		UPDATE temp_pmsi000143_rsf_detail 
  		SET 
  			base_remboursement = round(coefficient_mco_calcul * prix_unitaire * nombre, 2)
  		FROM pmsi.t_prestations, temp_pmsi000143_rss
  		JOIN base.v_coefficient_mco ON 
  			temp_pmsi000143_rss.finess = v_coefficient_mco.finess 
  			AND temp_pmsi000143_rss.date_sortie >= v_coefficient_mco.date_debut
  			AND temp_pmsi000143_rss.date_sortie <= v_coefficient_mco.date_fin
  		WHERE temp_pmsi000143_rsf_detail.prestation_id = t_prestations.oid 
  		AND temp_pmsi000143_rsf_detail.rss_id = temp_pmsi000143_rss.oid;
  					
  	-----------------------	**
  	RAISE NOTICE 'Ressence tous les SRC facturables à partir des règles calculées précédemment';
  					
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_nb_rss = nb_rss,
  			src_facturable_nb_prestations = nb_prestations,
  			src_facturable_base_remboursement = base_remboursement,
  			src_facturable_regle = regle
  		FROM
  			(SELECT rss_id, 1 as nb_rss, SUM(nombre) as nb_prestations, SUM(base_remboursement) as base_remboursement, MAX(regle) as regle
  			FROM temp_pmsi000143_rsf_detail
  			GROUP BY rss_id) subview
  		WHERE subview.rss_id = temp_pmsi000143_rss.oid;
  	
  
  	-----------------------	**
  	RAISE NOTICE ' ';				
  		
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_regle_justificatif = 'Passé en SRC non facturable ('::text || w_rss_3_12.regle_justificatif::text || ' éligible)'::text
  		FROM w_rss_3_12
  		WHERE w_rss_3_12.rss_id = temp_pmsi000143_rss.oid
  			AND src_facturee_nb_rss = 0 AND src_facturable_nb_rss = 0 AND src_passage_nb_rss > 0;
  
      UPDATE temp_pmsi000143_rss SET
  			src_facturable_regle_justificatif = 'Passé en SRC non facturable ('::text || w_rss_3b_12.regle_justificatif::text || ' éligible)'::text
  		FROM w_rss_3b_12
  		WHERE w_rss_3b_12.rss_id = temp_pmsi000143_rss.oid
  			AND src_facturee_nb_rss = 0 AND src_facturable_nb_rss = 0 AND src_passage_nb_rss > 0;
  		
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_regle_justificatif = 'Passé en SRC non facturable ('::text || w_rss_4_12.regle_justificatif::text || ' éligible)'::text
  		FROM w_rss_4_12
  		WHERE w_rss_4_12.rss_id = temp_pmsi000143_rss.oid
  			AND src_facturee_nb_rss = 0 AND src_facturable_nb_rss = 0 AND src_passage_nb_rss > 0;
  		
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_regle_justificatif = 'Passé en SRC non facturable'
  		WHERE src_facturee_nb_rss = 0 AND src_facturable_nb_rss = 0 AND src_passage_nb_rss > 0 AND src_facturable_regle_justificatif = '';
  		
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_regle_justificatif = 'Règle '::text || src_facturable_regle::text
  		WHERE src_facturable_regle IN ('1', '3', '5');
  		
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_regle_justificatif = 'Règle '::text || src_facturable_regle::text || ' '::text || w_rss_3_12.regle_justificatif::text
  		FROM w_rss_3_12
  		WHERE w_rss_3_12.rss_id = temp_pmsi000143_rss.oid
  			AND src_facturable_regle = '2';
  
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_regle_justificatif = 'Règle '::text || src_facturable_regle::text || ' '::text || w_rss_3b_12.regle_justificatif::text
  		FROM w_rss_3b_12
  		WHERE w_rss_3b_12.rss_id = temp_pmsi000143_rss.oid
  			AND src_facturable_regle = '2';
  			
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_regle_justificatif = 'Règle '::text || src_facturable_regle::text || ' '::text || w_rss_4_12.regle_justificatif::text
  		FROM w_rss_4_12
  		WHERE w_rss_4_12.rss_id = temp_pmsi000143_rss.oid
  			AND src_facturable_regle = '4';
  		
  
  	-----------------------	**
  	RAISE NOTICE ' ';
  
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_sans_src_ni_igs_nb_rss = 1,
  			src_facturable_regle_justificatif = 'Vérifier SRC ('::text || w_rss_3_12.regle_justificatif::text || ' éligible)'::text
  		FROM w_rss_3_12
  		WHERE w_rss_3_12.rss_id = temp_pmsi000143_rss.oid
  		AND src_passage_nb_rss = 0 AND rea_passage_nb_rss = 0;
  		
  		UPDATE temp_pmsi000143_rss SET
  			src_facturable_sans_src_ni_igs_nb_rss = 1,
  			src_facturable_regle_justificatif = 'Vérifier SRC ('::text || w_rss_4_12.regle_justificatif::text || ' éligible)'::text
  		FROM w_rss_4_12
  		WHERE w_rss_4_12.rss_id = temp_pmsi000143_rss.oid
  		AND src_passage_nb_rss = 0 AND rea_passage_nb_rss = 0;
  		
  		DELETE FROM temp_pmsi000143_rss
  		WHERE 
  			src_facturee_nb_rss = 0 
  			AND src_facturable_nb_rss = 0 
  			AND src_passage_nb_rss = 0 
  			AND src_facturable_sans_src_ni_igs_nb_rss = 0;
  
  -----------------------	**
  	RAISE NOTICE 'Remplissage table p_rss_src';
  		INSERT INTO pmsi.p_rss_src(
  		            finess, no_rss, oid, date_sortie, age, src_facturee_nb_rss, src_facturee_nb_prestations, 
  		            src_facturee_base_remboursement, src_facturable_nb_rss, src_facturable_nb_prestations, 
  		            src_facturable_base_remboursement, src_facturable_regle, src_facturable_regle_justificatif, 
  		            src_passage_nb_rss, src_passage_duree_sejour, src_passage_base_remboursement, 
  		            src_facturable_sans_src_ni_igs_nb_rss, rea_passage_nb_rss, igs2_rum)
  		SELECT * FROM temp_pmsi000143_rss 
  		;
  
  		
  	RETURN 'OK';
  END;