return: text
lang: plpgsql
src: |
  DECLARE	
  	result TEXT;
  BEGIN
  	
  	
  		IF NOT EXISTS (SELECT * FROM pg_tables WHERE schemaname = 'pmsi' AND tablename = 'p_rss') THEN
  			return 'Pas de PMSI';
  		END IF;
  		
  		-- Calcul simulation
  				
  		-- Initialisation du coefficient MCO non alimenté (exemple WEB100T)
  		UPDATE activite.p_factures_lignes_c
  		SET coefficient_mco = 
  			CASE 
  			WHEN date(date_fin) >= '20170301' THEN  0.993
  			WHEN date(date_fin) >= '20160301' THEN  0.995
  			WHEN date(date_fin) >= '20140301' THEN  0.9965
  			ELSE 1::numeric 
  			END
  		FROM activite.t_prestations
  		WHERE 
  			prestation_id = t_prestations.oid AND
  			t_prestations.code IN ('GHS', 'EXH') AND
  			coefficient_mco = 1
  		;
  
  
  		-- Initialisation des GHS bebes non alimentés depuis PMSI
  		UPDATE activite.p_sejours SET
  			ghs_bebe1_id = subview.ghs_bebe1_id,
  			ghs_bebe2_id = subview.ghs_bebe2_id,
  			ghs_bebe3_id = subview.ghs_bebe3_id
  		FROM
  		(
  		SELECT p_sejours.no_sejour, 
  			MAX(ghs_bebe1_id),
  			MAX(ghs_bebe2_id),
  			MAX(ghs_bebe3_id),
  			MAX(CASE WHEN row_number = 1 THEN ghs_bebe_id ELSE 0 END) AS ghs_bebe1_id, 
  			MAX(CASE WHEN row_number = 2 THEN ghs_bebe_id ELSE 0 END) AS ghs_bebe2_id, 
  			MAX(CASE WHEN row_number = 3 THEN ghs_bebe_id ELSE 0 END) AS ghs_bebe3_id
  		FROM activite.p_sejours
  		JOIN
  		(
  		SELECT p_sejour_pmsi.no_sejour, 
  			CASE WHEN p_rsf_detail.ghs_id <> p_rss.ghs_id THEN t_ghs_base.oid ELSE 0 END AS ghs_bebe_id,
  			row_number() OVER (PARTITION BY p_sejour_pmsi.no_sejour ORDER BY CASE WHEN p_rsf_detail.ghs_id <> p_rss.ghs_id THEN t_ghs_base.oid ELSE 999999999999 END) AS row_number
  		FROM pmsi.p_rsf_detail
  		JOIN pmsi.p_rss ON p_rsf_detail.rss_id = p_rss.oid
  		JOIN pmsi.t_ghm ON p_rss.ghm_id = t_ghm.oid
  		JOIN pmsi.t_prestations ON prestation_id = t_prestations.oid
  		JOIN pmsi.t_ghs ON p_rsf_detail.ghs_id = t_ghs.oid
  		JOIN base.t_ghs t_ghs_base ON t_ghs_base.code = t_ghs.code
  		JOIN activite.p_sejour_pmsi ON p_sejour_pmsi.rss_id = p_rss.oid
  		WHERE t_prestations.code = 'GHS' AND
  			t_ghm.code LIKE '14%' AND
  			p_rss.en_cours = '0' AND
  			est_ligne_rss = '1' 
  			order by 3 desc
  		) subview ON p_sejours.no_sejour = subview.no_sejour
  		GROUP BY 1
  		HAVING
  			(
  			MAX(ghs_bebe1_id) <> MAX(CASE WHEN row_number = 1 THEN ghs_bebe_id ELSE 0 END) OR
  			MAX(ghs_bebe2_id) <> MAX(CASE WHEN row_number = 2 THEN ghs_bebe_id ELSE 0 END) OR
  			MAX(ghs_bebe3_id) <> MAX(CASE WHEN row_number = 3 THEN ghs_bebe_id ELSE 0 END)
  			)
  		) subview
  		WHERE p_sejours.no_sejour = subview.no_sejour
  		;
  
  
  		-- Rechreche coefficient MCO par période pour simulation	
  		DROP TABLE IF EXISTS w_coefficient;
  		CREATE TEMP TABLE w_coefficient AS
  		SELECT *
  		FROM
  		(
  		SELECT date_debut, date_fin,coefficient_mco, nb, 
  			row_number() OVER (PARTITION BY date_debut ORDER BY nb  DESC) AS row_number 
  		FROM
  		(
  		SELECT date(date_trunc('month',p_factures.date_fin)) AS date_debut,
  			date(date_trunc('month',p_factures.date_fin)+interval '1 month'-interval '1 day') AS date_fin,
  			coefficient_mco,
  			count(*) AS nb
  		FROM activite.p_factures_lignes_c
  		JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture
  		JOIN activite.t_prestations ON prestation_id = t_prestations.oid
  		WHERE t_prestations.code = 'GHS' AND p_factures.date_fin >= '20140301'
  		GROUP BY 1,2,3
  		) subview
  		) subview
  		WHERE row_number = 1
  		;
  
  		
  		INSERT INTO w_coefficient
  		SELECT p_calendrier_mois.date_debut,
  		p_calendrier_mois.date_fin,
  		CASE 
  			WHEN date(p_calendrier_mois.date_fin) >= '20170301' THEN  0.993
  			WHEN date(p_calendrier_mois.date_fin) >= '20160301' THEN  0.995
  			WHEN date(p_calendrier_mois.date_fin) >= '20140301' THEN  0.9965
  			ELSE 1::numeric 
  			END
  		AS coefficient_mco,
  		1::numeric AS nb,
  		1::bigint AS row_number
  		FROm base.p_calendrier_mois
  		LEFT JOIN w_coefficient ON p_calendrier_mois.date_fin = w_coefficient.date_fin
  		WHERE p_calendrier_mois.date_debut BETWEEN '20170101' AND now() AND  w_coefficient IS NULL
  		;
  
  
  
  		-- Identification des séjours à traiter
  		DROP TABLE IF EXISTS w_simulation;
  		CREATE TEMP TABLE w_simulation AS
  		SELECT p_sejours.oid AS sejour_id,
  			p_sejours.no_sejour, 
  			type_sejour,
  			date_entree, 
  			date_sortie, 
  			mode_sortie,
  			date_sortie - date_entree AS duree_sejour, 
  			1::numeric AS nb,
  			p_sejours.ghm_id,
  			t_ghm.code AS ghm_code, 
  			t_ghm.texte As ghm_texte,
  			p_sejours.ghs_id,
  			t_ghs.code AS ghs_code,
  			0::numeric AS coefficient_f,
  			'1'::numeric AS ok_ghs_f,
  			COALESCE(w_coefficient.coefficient_mco,0) AS coefficient_c,
  			CASE WHEN t_ghs_tarifs.tarif_ghs IS NOT NULL THEN '1' ELSE '0' END AS ok_ghs_c,
  			COALESCE(t_ghs_tarifs.tarif_ghs,0) AS tarif_ghs_c,
  			COALESCE(t_ghs_tarifs.tarif_exh,0) AS tarif_exh_c,
  			COALESCE(t_ghs_tarifs.tarif_exb,0) AS tarif_exb_c,
  			COALESCE(t_ghs_tarifs.forfait_exb,0) AS forfait_exb_c,
  			COALESCE(t_ghs_tarifs.borne_basse,0) AS borne_basse_c,
  			COALESCE(t_ghs_tarifs.borne_haute,0) AS borne_haute_c,
  			p_sejours.ghs_bebe1_id,
  			0::numeric AS tarif_ghs_bb1_c,
  			p_sejours.ghs_bebe2_id,
  			0::numeric AS tarif_ghs_bb2_c,
  			p_sejours.ghs_bebe3_id,
  			0::numeric AS tarif_ghs_bb3_c,
  			COALESCE(w_coefficient_p.coefficient_mco,0) AS coefficient_p,
  			CASE WHEN t_ghs_tarifs_p.tarif_ghs IS NOT NULL THEN '1' ELSE '0' END AS ok_ghs_p,
  			COALESCE(t_ghs_tarifs_p.tarif_ghs,0) AS tarif_ghs_p,
  			COALESCE(t_ghs_tarifs_p.tarif_exh,0) AS tarif_exh_p,
  			COALESCE(t_ghs_tarifs_p.tarif_exb,0) AS tarif_exb_p,
  			COALESCE(t_ghs_tarifs_p.forfait_exb,0) AS forfait_exb_p,
  			COALESCE(t_ghs_tarifs_p.borne_basse,0) AS borne_basse_p,
  			COALESCE(t_ghs_tarifs_p.borne_haute,0) AS borne_haute_p,
  			0::numeric AS tarif_ghs_bb1_p,
  			0::numeric AS tarif_ghs_bb2_p,
  			0::numeric AS tarif_ghs_bb3_p,
  			0::numeric AS montant_ghs_f,
  			0::numeric AS montant_exh_f,
  			0::numeric AS montant_tot_f,
  			0::numeric AS montant_ghs_c,
  			0::numeric AS montant_exh_c,
  			0::numeric AS montant_exb_c,
  			0::numeric AS montant_ghs_bb1_c,
  			0::numeric AS montant_ghs_bb2_c,
  			0::numeric AS montant_ghs_bb3_c,
  			0::numeric AS montant_tot_c,
  			0::numeric AS montant_ghs_p,
  			0::numeric AS montant_exh_p,
  			0::numeric AS montant_exb_p,
  			0::numeric AS montant_tot_p,
  			0::numeric AS montant_ghs_bb1_p,
  			0::numeric AS montant_ghs_bb2_p,
  			0::numeric AS montant_ghs_bb3_p
  		FROM activite.p_sejours
  		JOIN base.t_ghs ON p_sejours.ghs_id = t_ghs.oid
  		JOIN base.t_ghm ON p_sejours.ghm_id = t_ghm.oid
  		LEFT JOIN base.t_ghs_tarifs ON t_ghs_tarifs.ghs_id = p_sejours.ghs_id AND
  			p_sejours.date_sortie BETWEEN t_ghs_tarifs.date_debut AND t_ghs_tarifs.date_fin AND
  			t_ghs_tarifs.secteur = '2'
  		LEFT JOIN w_coefficient ON p_sejours.date_sortie BETWEEN w_coefficient.date_debut AND w_coefficient.date_fin
  		LEFT JOIN base.t_ghs_tarifs t_ghs_tarifs_p ON t_ghs_tarifs_p.ghs_id = p_sejours.ghs_id AND
  			date(date_trunc('month',p_sejours.date_sortie)-interval '1 year') BETWEEN t_ghs_tarifs_p.date_debut AND t_ghs_tarifs_p.date_fin AND
  			t_ghs_tarifs_p.secteur = '2'
  		LEFT JOIN w_coefficient w_coefficient_p ON date(date_trunc('month',p_sejours.date_sortie)-interval '1 year') BETWEEN w_coefficient_p.date_debut AND w_coefficient_p.date_fin
  		WHERE date_sortie >= '20160301' AND
  			code_sorti = '1' AND
  			type_sejour IN ('1','2','5') AND
  			p_sejours.ghs_id > 0 AND
  			p_sejours.ghm_id > 0 AND
  			t_ghm.code <> ALL(Array['09Z02A','14Z08Z'])
  		;
  
  		-- Mise à jour des tarifs ghs bebes
  		UPDATE w_simulation SET
  			tarif_ghs_bb1_c = t_ghs_tarifs.tarif_ghs
  		FROM base.t_ghs_tarifs
  		WHERE ghs_bebe1_id = t_ghs_tarifs.ghs_id AND
  			ghs_bebe1_id <> 0 AND
  			ghm_code LIKE '14%' AND
  			date_sortie BETWEEN t_ghs_tarifs.date_debut AND t_ghs_tarifs.date_fin AND
  			secteur = '2'
  		;
  		UPDATE w_simulation SET
  			tarif_ghs_bb2_c = t_ghs_tarifs.tarif_ghs
  		FROM base.t_ghs_tarifs
  		WHERE ghs_bebe1_id = t_ghs_tarifs.ghs_id AND
  			ghs_bebe2_id <> 0 AND
  			ghm_code LIKE '14%' AND
  			date_sortie BETWEEN t_ghs_tarifs.date_debut AND t_ghs_tarifs.date_fin AND
  			secteur = '2'
  		;
  		UPDATE w_simulation SET
  			tarif_ghs_bb3_c = t_ghs_tarifs.tarif_ghs
  		FROM base.t_ghs_tarifs
  		WHERE ghs_bebe1_id = t_ghs_tarifs.ghs_id AND
  			ghs_bebe3_id <> 0 AND
  			ghm_code LIKE '14%' AND
  			date_sortie BETWEEN t_ghs_tarifs.date_debut AND t_ghs_tarifs.date_fin AND
  			secteur = '2'
  		;
  
  
  		UPDATE w_simulation SET
  			tarif_ghs_bb1_p = t_ghs_tarifs.tarif_ghs
  		FROM base.t_ghs_tarifs
  		WHERE ghs_bebe1_id = t_ghs_tarifs.ghs_id AND
  			ghs_bebe1_id <> 0 AND
  			ghm_code LIKE '14%' AND
  			date(date_trunc('month',date_sortie)-interval '1 year') BETWEEN t_ghs_tarifs.date_debut AND t_ghs_tarifs.date_fin AND
  			secteur = '2'
  		;
  		UPDATE w_simulation SET
  			tarif_ghs_bb2_p = t_ghs_tarifs.tarif_ghs
  		FROM base.t_ghs_tarifs
  		WHERE ghs_bebe1_id = t_ghs_tarifs.ghs_id AND
  			ghs_bebe2_id <> 0 AND
  			ghm_code LIKE '14%' AND
  			date(date_trunc('month',date_sortie)-interval '1 year') BETWEEN t_ghs_tarifs.date_debut AND t_ghs_tarifs.date_fin AND
  			secteur = '2'
  		;
  		UPDATE w_simulation SET
  			tarif_ghs_bb3_p = t_ghs_tarifs.tarif_ghs
  		FROM base.t_ghs_tarifs
  		WHERE ghs_bebe1_id = t_ghs_tarifs.ghs_id AND
  			ghs_bebe3_id <> 0 AND
  			ghm_code LIKE '14%' AND
  			date(date_trunc('month',date_sortie)-interval '1 year') BETWEEN t_ghs_tarifs.date_debut AND t_ghs_tarifs.date_fin AND
  			secteur = '2'
  		;
  
  		-- Nombre de séances
  		UPDATE w_simulation
  		SET nb = subview.nb
  		FROM
  		(
  		SELECT p_mouvements_sejour.no_sejour, COUNT(DISTINCT p_mouvements_sejour.date) AS nb
  		FROM activite.p_mouvements_sejour
  		JOIN w_simulation ON w_simulation.no_sejour = p_mouvements_sejour.no_sejour AND
  			w_simulation.type_sejour = '5'
  		WHERE nb_seances > 0
  		GROUP BY 1
  		) subview
  		WHERE w_simulation.no_sejour = subview.no_sejour
  		;
  
  		-- Nombre de séances selon PMSI
  		UPDATE w_simulation
  		SET nb = subview.nb
  		FROM
  		(
  		SELECT p_sejour_pmsi.no_sejour, COUNT(DISTINCT date_fin) AS nb
  		FROM pmsi.p_rsf_detail
  		JOIN pmsi.p_rss ON p_rsf_detail.rss_id = p_rss.oid AND en_cours = '0'
  		JOIN pmsi.t_ghs ON t_ghs.oid = p_rsf_detail.ghs_id AND t_ghs.code = ANY(Array[9606])
  		JOIN pmsi.t_prestations ON t_prestations.oid = p_rsf_detail.prestation_id AND t_prestations.code = 'GHS'
  		JOIN activite.p_sejour_pmsi ON p_sejour_pmsi.rss_id = p_rss.oid
  		JOIN w_simulation ON p_sejour_pmsi.no_sejour = w_simulation.no_sejour
  		GROUP BY 1
  		HAVING MAX(w_simulation.nb) <> COUNT(DISTINCT p_rsf_detail.date_fin) AND
  			MAX(ghm_code) LIKE '28%'
  		) subview
  		WHERE w_simulation.no_sejour = subview.no_sejour
  		;
  		-- Nombre de séances selon facturation
  		UPDATE w_simulation
  		SET nb= subview.nb
  		FROM
  		(
  		SELECT p_factures.no_sejour, SUM(nb_prestation) AS nb
  		FROM activite.p_factures_lignes_c
  		JOIN activite.t_prestations ON t_prestations.oid = prestation_id AND t_prestations.code = 'GHS'
  		JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture
  		JOIN w_simulation ON p_factures.no_sejour = w_simulation.no_sejour
  		WHERE w_simulation.nb = 1 AND w_simulation.ghm_code LIKE '28%'
  		GROUP BY 1
  		HAVING SUM(nb_prestation) > 1) subview
  		WHERE w_simulation.no_sejour = subview.no_sejour
  		;
  		-- Recherche montant réellement facturé
  		UPDATE w_simulation
  		SET coefficient_f = subview.coefficient_f,
  			montant_ghs_f = subview.montant_ghs_f,
  			montant_exh_f = subview.montant_exh_f
  		FROM
  		(
  		SELECT p_factures.no_sejour, 
  			MAX(CASE WHEN t_prestations.code = 'GHS' THEN coefficient_mco ELSE 0 END) AS coefficient_f,
  			round(SUM(CASE WHEN t_prestations.code = ANY(Array['GHS','FJ','FJM','FRL']) THEN montant_facture ELSE 0 END),2) AS montant_ghs_f,
  			round(SUM(CASE WHEN t_prestations.code = 'EXH' THEN montant_facture ELSE 0 END),2) AS montant_exh_f
  		FROM activite.p_factures_lignes_c
  		JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture
  		JOIN w_simulation ON w_simulation.no_sejour = p_factures.no_sejour
  		JOIN activite.t_prestations ON prestation_id = t_prestations.oid AND
  			t_prestations.code = ANY(Array['GHS','EXH','FJ','FJM','FRL']) AND
  			(
  			t_prestations.code NOT IN ('FJ','FJM') OR
  			p_factures_lignes_c.date_fin < w_simulation.date_sortie
  			)
  		GROUP BY 1
  		)
  		subview
  		WHERE w_simulation.no_sejour = subview.no_sejour
  		;
  
  		-- Si tarif ghs précédent inexistant (nouveau ghs), prendre tarif en cours
  		UPDATE w_simulation SET
  			tarif_ghs_p = tarif_ghs_c,
  			tarif_exh_p = tarif_exh_c,
  			tarif_exb_p = tarif_exb_c,
  			forfait_exb_p = forfait_exb_c,
  			borne_basse_p = borne_basse_c,
  			borne_haute_p = borne_haute_c
  		WHERE ok_ghs_p = '0'
  		;
  		
  		-- Simulation calcul
  		UPDATE w_simulation SET
  			montant_ghs_c = round(
  				tarif_ghs_c * coefficient_c * nb
  				,2),
  			montant_exh_c = round(
  				CASE 
  				WHEN duree_sejour + CASE WHEN mode_sortie IN ('6','7','9') THEN 1 ELSE 0 END > borne_haute_c AND borne_haute_c > 0 
  					THEN tarif_exh_c * (duree_sejour + CASE WHEN mode_sortie IN ('6','7','9') THEN 1 ELSE 0 END - borne_haute_c) * coefficient_c 
  				ELSE 0 END
  				,2),
  			montant_exb_c = round(
  				0 - CASE 
  				WHEN borne_basse_c = 0 OR duree_sejour >= borne_basse_c OR mode_sortie = '9' THEN 0
  				WHEN forfait_exb_c > 0 THEN forfait_exb_c * coefficient_c 
  				WHEN forfait_exb_c = 0 AND tarif_exb_c > 0 AND duree_sejour = 0 THEN (tarif_ghs_c * coefficient_c) - (tarif_exb_c / 2 * coefficient_c)
  				WHEN forfait_exb_c = 0 AND tarif_exb_c > 0 AND duree_sejour > 0 THEN (tarif_exb_c * (borne_basse_c-duree_sejour)) * coefficient_c
  				ELSE 0 END
  				,2),
  			montant_ghs_bb1_c = round(tarif_ghs_bb1_c * coefficient_c ,2),
  			montant_ghs_bb2_c = round(tarif_ghs_bb2_c * coefficient_c ,2),
  			montant_ghs_bb3_c = round(tarif_ghs_bb3_c * coefficient_c ,2),
  			montant_ghs_p = round(
  				tarif_ghs_p * coefficient_p * nb
  				,2),
  			montant_exh_p = round(
  				CASE 
  				WHEN duree_sejour + CASE WHEN mode_sortie IN ('6','7','9') THEN 1 ELSE 0 END > borne_haute_p AND borne_haute_p > 0 
  					THEN tarif_exh_p * (duree_sejour + CASE WHEN mode_sortie IN ('6','7','9') THEN 1 ELSE 0 END - borne_haute_p) * coefficient_p 
  				ELSE 0 END
  				,2),
  			montant_exb_p = round(
  				0 - CASE 
  				WHEN borne_basse_p = 0 OR duree_sejour >= borne_basse_p OR mode_sortie = '9' THEN 0
  				WHEN forfait_exb_p > 0 THEN forfait_exb_p * coefficient_p 
  				WHEN forfait_exb_p = 0 AND tarif_exb_p > 0 AND duree_sejour = 0 THEN (tarif_ghs_p * coefficient_p) - (tarif_exb_p / 2 * coefficient_p)
  				WHEN forfait_exb_p = 0 AND tarif_exb_p > 0 AND duree_sejour > 0 THEN (tarif_exb_p * (borne_basse_p-duree_sejour)) * coefficient_p
  				ELSE 0 END
  				,2),
  			montant_ghs_bb1_p = round(tarif_ghs_bb1_p * coefficient_p ,2),
  			montant_ghs_bb2_p = round(tarif_ghs_bb2_p * coefficient_p ,2),
  			montant_ghs_bb3_p = round(tarif_ghs_bb3_p * coefficient_p ,2)
  
  		;
  
  		-- Sur séances avec ucd enlever 40 euros
  		UPDATE w_simulation
  		SET montant_ghs_c = round(montant_ghs_c - 40*nb_ph8*coefficient_c,2),
  			montant_ghs_p = round(montant_ghs_p - 40*nb_ph8*coefficient_p,2)
  		FROM
  		(
  		SELECT no_sejour,
  			count(DISTINCT date_fin) AS nb_ph8
  		FROM
  		(
  		SELECT p_factures.no_sejour,
  			p_factures_lignes_c.date_fin,
  			SUM(montant_facture)
  			FROM activite.p_factures_lignes_c
  			JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture
  			JOIN w_simulation ON w_simulation.no_sejour = p_factures.no_sejour
  			JOIN activite.t_prestations ON prestation_id = t_prestations.oid AND
  				t_prestations.code = ANY(Array['PH8','UCD']) 
  			WHERE w_simulation.ghm_code = ANY(Array['28Z07Z','28Z17Z'])
  			GROUP BY 1,2
  			HAVING SUM(montant_facture) <> 0
  		) subview
  		GROUP BY 1
  		) subview
  		WHERE w_simulation.no_sejour = subview.no_sejour AND
  			ABS(montant_ghs_f - montant_ghs_c) > 2
  		;
  
  
  		-- total
  		UPDATE w_simulation SET
  			montant_tot_f = montant_ghs_f + montant_exh_f,
  			montant_tot_c = montant_ghs_c + montant_exb_c + montant_exh_c + montant_ghs_bb1_c + montant_ghs_bb2_c + montant_ghs_bb3_c,
  			montant_tot_p = montant_ghs_p + montant_exb_p + montant_exh_p + montant_ghs_bb1_p + montant_ghs_bb2_p + montant_ghs_bb3_p
  			;
  
  		-- Complement bebe si ghs_bebe non trouve
  		UPDATE w_simulation SET
  			ghs_bebe1_id = subview.ghs_bebe1_id,
  			tarif_ghs_bb1_c = subview.tarif_ghs_bb1_c,
  			montant_ghs_bb1_c = subview.montant_ghs_bb1_c,
  			montant_tot_c = montant_tot_c + subview.montant_ghs_bb1_c,
  			tarif_ghs_bb1_p = subview.tarif_ghs_bb1_p,
  			montant_ghs_bb1_p = subview.montant_ghs_bb1_p,
  			montant_tot_p = montant_tot_p + subview.montant_ghs_bb1_p
  		FROM
  		(
  		SELECT montant_ghs_bb1_c, 
  			(MAX(ARRAY[nb,ghs_bebe1_id]))[2] AS ghs_bebe1_id, 
  			(MAX(ARRAY[nb,tarif_ghs_bb1_c]))[2] AS tarif_ghs_bb1_c,
  			(MAX(ARRAY[nb,tarif_ghs_bb1_p]))[2] AS tarif_ghs_bb1_p,
  			(MAX(ARRAY[nb,montant_ghs_bb1_p]))[2] AS montant_ghs_bb1_p
  		FROM
  		(
  		SELECT montant_ghs_bb1_c, 
  			ghs_bebe1_id, 
  			MAX(tarif_ghs_bb1_c) AS tarif_ghs_bb1_c, 
  			MAX(tarif_ghs_bb1_p) AS tarif_ghs_bb1_p, 
  			MAX(montant_ghs_bb1_p) AS montant_ghs_bb1_p, 
  			count(*) AS nb
  		FROM w_simulation
  		WHERE ghm_code LIKE '14%' AND ghs_bebe1_id <> 0
  		GROUP BY 1,2
  		) subview
  		GROUP BY 1
  		) subview
  		WHERE w_simulation.ghm_code LIKE '14%' AND
  			w_simulation.ghs_bebe1_id = 0 AND
  			subview.montant_ghs_bb1_c BETWEEN (montant_tot_f - montant_tot_c) - 0.01 AND (montant_tot_f - montant_tot_c) + 0.01
  		;
  
  		-- Ajustement montant facture Forfait journalier JS ?
  		UPDATE w_simulation
  		SET montant_tot_f = montant_tot_c,
  			montant_ghs_f = montant_ghs_c + montant_exb_c + montant_ghs_bb1_c + montant_ghs_bb2_c + montant_ghs_bb3_c,
  			montant_exh_f = montant_exh_c
  		WHERE ABS(montant_tot_f - montant_tot_c) BETWEEN 17.99 AND 18.01
  		;
  
  		-- Ajustement montant facture Centimes ?
  		UPDATE w_simulation
  		SET montant_tot_f = montant_tot_c,
  			montant_ghs_f = montant_ghs_c + montant_exb_c + montant_ghs_bb1_c + montant_ghs_bb2_c + montant_ghs_bb3_c,
  			montant_exh_f = montant_exh_c
  		WHERE ABS(montant_tot_f - montant_tot_c) BETWEEN 0.01 AND 0.03
  		;
  
  		-- Pointeur séjour facturé	
  		UPDATE w_simulation SET
  			ok_ghs_f = '0'
  		WHERE montant_tot_f = 0
  		;
  
  		--Ajout à la table
  		TRUNCATE activite.p_sejour_effet_tarif;
  		INSERT INTO activite.p_sejour_effet_tarif
  		(
  			sejour_id,
  			no_sejour,
  			ok_ghs_f,
  			coefficient_c,
  			ok_ghs_c,
  			tarif_ghs_c,
  			tarif_exh_c,
  			tarif_exb_c,
  			forfait_exb_c,
  			borne_basse_c,
  			borne_haute_c,
  			tarif_ghs_bb1_c,
  			tarif_ghs_bb2_c,
  			tarif_ghs_bb3_c,
  			coefficient_p,
  			ok_ghs_p,
  			tarif_ghs_p,
  			tarif_exh_p,
  			tarif_exb_p,
  			forfait_exb_p,
  			borne_basse_p,
  			borne_haute_p,
  			tarif_ghs_bb1_p,
  			tarif_ghs_bb2_p,
  			tarif_ghs_bb3_p,
  			montant_ghs_f,
  			montant_exh_f,
  			montant_tot_f,
  			montant_ghs_c,
  			montant_exb_c,
  			montant_exh_c,
  			montant_ghs_bb1_c,
  			montant_ghs_bb2_c,
  			montant_ghs_bb3_c,
  			montant_tot_c,
  			montant_ghs_p,
  			montant_exh_p,
  			montant_exb_p,
  			montant_tot_p,
  			montant_ghs_bb1_p,
  			montant_ghs_bb2_p,
  			montant_ghs_bb3_p
  		)
  		SELECT
  			sejour_id,
  			no_sejour,
  			ok_ghs_f,
  			coefficient_c,
  			ok_ghs_c,
  			tarif_ghs_c,
  			tarif_exh_c,
  			tarif_exb_c,
  			forfait_exb_c,
  			borne_basse_c,
  			borne_haute_c,
  			tarif_ghs_bb1_c,
  			tarif_ghs_bb2_c,
  			tarif_ghs_bb3_c,
  			coefficient_p,
  			ok_ghs_p,
  			tarif_ghs_p,
  			tarif_exh_p,
  			tarif_exb_p,
  			forfait_exb_p,
  			borne_basse_p,
  			borne_haute_p,
  			tarif_ghs_bb1_p,
  			tarif_ghs_bb2_p,
  			tarif_ghs_bb3_p,
  			montant_ghs_f,
  			montant_exh_f,
  			montant_tot_f,
  			montant_ghs_c,
  			montant_exb_c,
  			montant_exh_c,
  			montant_ghs_bb1_c,
  			montant_ghs_bb2_c,
  			montant_ghs_bb3_c,
  			montant_tot_c,
  			montant_ghs_p,
  			montant_exh_p,
  			montant_exb_p,
  			montant_tot_p,
  			montant_ghs_bb1_p,
  			montant_ghs_bb2_p,
  			montant_ghs_bb3_p
  		FROM w_simulation
  		;
  		
  		
  	RETURN 'OK';
  END;