return: text
lang: plpgsql
src: |
  DECLARE 
  	
  BEGIN
  	-- Précalcul table étude de l'impact CMA
  		
  
  	
  	-- Création d'une table d'association GHM <--> GHS
                  
                  DROP TABLE IF EXISTS w_ghm_ghs; CREATE TEMP TABLE w_ghm_ghs AS
                  WITH w_typets AS (
                      SELECT generate_series(1,3) AS typets
                  ),
                  -- GHS le plus fréquemment valorisé pour les GHM multi GHS d'après les infos DATIM, on prend le dernier en date
                  w_datim AS (
                      SELECT
                          t_ghm.code AS ghm_code,
                          w_typets.typets,
                          (max(ARRAY[to_char(t_datim_ghminfo.date_debut, 'YYYYMMDD'), CASE WHEN pvalo > 0 THEN pvalo::text ELSE NULL END]))[2] AS ghs_code
                      FROM
                          pmsi.t_ghm
                          JOIN w_typets ON 1=1
                          LEFT JOIN pmsi.t_datim_ghminfo ON t_ghm.code = t_datim_ghminfo.ghm AND w_typets.typets = t_datim_ghminfo.typets
                      GROUP BY 1,2
                      ORDER BY 1,2
                  ),
                  w_ghs AS (
                      SELECT
                          t_ghm.oid AS ghm_id,
                          w_typets.typets,
                          (min(ARRAY[CASE WHEN w_typets.typets = '1' THEN dernier_tarif_ghs_prive ELSE dernier_tarif_ghs_public END, t_ghs.code]))[2]::text AS ghs_code,
                          count(*) AS nb_ghs
                      FROM
                          pmsi.t_ghm
                          JOIN w_typets ON 1=1
                          LEFT JOIN pmsi.t_ghs ON t_ghs.ghm_id = t_ghm.oid
                      GROUP BY 1,2
                      ORDER BY 1,2
                  )
                  -- Pour chaque GHM, on prend par ordre de préférence
                  --    1. le GHS le moins onéreux
                  --    2. le GHS 9999 si aucun GHS n'est trouvé
                  --    Il était prévu initialement de prendre le GHS le plus souvent valorisé par DATIM (d'où la construction de la table w_datim) mais finalement, on prend le moins onéreux
                  SELECT
                      t_ghm.oid AS ghm_id,
                      t_ghm.code AS ghm_code,
                      nb_ghs,
                      substr(t_ghm.code,1,5) AS ghm5_code,
                      w_typets.typets,
                      --COALESCE(w_datim.ghs_code::text, w_ghs.ghs_code::text, '9999'::text) AS ghs_code
                      0::bigint AS ghs_id,
                      COALESCE(w_ghs.ghs_code::text, '9999'::text) AS ghs_code,
  		    0::bigint AS ghm1_id,
                      ''::text AS ghm1_code,
                      0::bigint AS ghs1_id,
                      ''::text AS ghs1_code,
                      0::bigint AS ghm2_id,
                      ''::text AS ghm2_code,
                      0::bigint AS ghs2_id,
                      ''::text AS ghs2_code
                  FROM
                      pmsi.t_ghm
                      JOIN w_typets ON 1=1
                      LEFT JOIN w_datim ON w_datim.ghm_code = t_ghm.code AND w_typets.typets = w_datim.typets
                      LEFT JOIN w_ghs ON w_ghs.ghm_id = t_ghm.oid AND w_typets.typets = w_ghs.typets
                  ;
  
                  UPDATE w_ghm_ghs SET
  			ghs_id = t_ghs.oid
  		FROM pmsi.t_ghs
  		WHERE ghs_code = t_ghs.code
  		;
  
                  UPDATE w_ghm_ghs SET
  			ghm1_id = w_ghm_ghs_1.ghm_id,
  			ghm1_code = w_ghm_ghs_1.ghm_code,
  			ghs1_id = w_ghm_ghs_1.ghs_id,
  			ghs1_code = w_ghm_ghs_1.ghs_code
  		FROM w_ghm_ghs w_ghm_ghs_1
  		WHERE substr(w_ghm_ghs.ghm_code,1,5)||'1' = w_ghm_ghs_1.ghm_code AND
  			w_ghm_ghs.typets = w_ghm_ghs_1.typets
  		;
  		UPDATE w_ghm_ghs SET
  			ghm2_id = w_ghm_ghs_1.ghm_id,
  			ghm2_code = w_ghm_ghs_1.ghm_code,
  			ghs2_id = w_ghm_ghs_1.ghs_id,
  			ghs2_code = w_ghm_ghs_1.ghs_code
  		FROM w_ghm_ghs w_ghm_ghs_1
  		WHERE substr(w_ghm_ghs.ghm_code,1,5)||'2' = w_ghm_ghs_1.ghm_code AND
  			w_ghm_ghs.typets = w_ghm_ghs_1.typets
  		;
  
                  ANALYSE w_ghm_ghs
                  ;
  
                  CREATE INDEX i_w_ghm_ghs_ghm_id
  		  ON w_ghm_ghs
  		  USING btree
  		  (ghm_id);
  
                  CREATE INDEX i_w_ghm_ghs_ghm_code
  		  ON w_ghm_ghs
  		  USING btree
  		  (ghm_code);
  
  		-- coefficients MCO 
  		DROP TABLE IF EXISTS w_coefficient_mco;
  		CREATE TEMP TABLE w_coefficient_mco AS
  		SELECT finess_coefficient_mco, date_debut_coefficient_mco, date_fin_coefficient_mco, (MAX(ARRAY[nb,coefficient_mco]))[2] AS coefficient_mco
  		FROM
  		(
  		SELECT 
  			p_rsf_detail.finess AS finess_coefficient_mco,
  			date(CASE WHEN date_part('month',p_rsf_detail.date_fin) <= 2 THEN date_trunc('year',p_rsf_detail.date_fin)-interval '10 month' ELSE date_trunc('year',p_rsf_detail.date_fin)+interval '2 month' END) AS date_debut_coefficient_mco,
  			date(CASE WHEN date_part('month',p_rsf_detail.date_fin) <= 2 THEN date_trunc('year',p_rsf_detail.date_fin)+interval '2 month'-interval '1 day' ELSE date_trunc('year',p_rsf_detail.date_fin)+interval '14 month'-interval '1 day' END) AS date_fin_coefficient_mco,
  			coefficient_mco, 
  			count(*) AS nb
  		FROM pmsi.p_rsf_detail
  		JOIN pmsi.t_prestations ON prestation_id = t_prestations.oid 
  		WHERE p_rsf_detail.date_fin >= '20160101' AND est_ligne_rss = '1' AND t_prestations.code = 'GHS'
  		GROUP BY 1,2,3,4
  		) subview
  		GROUP BY 1,2,3
  		ORDER BY 1,2 DESC
  		;
  
  		ANALYSE w_coefficient_mco
  		;
  
                  -- RSS à traiter
                  DROP TABLE IF EXISTS w_rss;
  		CREATE TEMP TABLE w_rss AS
  		SELECT
  			p_rss.oid,
  			p_rss.finess,
  			p_rss.mois_sortie,
  			p_rss.date_sortie,
  			p_rss.ghm_id,
            p_rss.ghs_id,
  			p_rss.age,
  			p_rss.duree_sejour,
  			t_finess.type_etablissement,
  			COALESCE(coefficient_mco,1) AS coefficient_mco
  		FROM pmsi.p_rss
                  JOIN base.t_finess ON t_finess.code = p_rss.finess
                  LEFT JOIN w_coefficient_mco ON p_rss.finess = finess_coefficient_mco AND
  				p_rss.date_sortie BETWEEN date_debut_coefficient_mco AND date_fin_coefficient_mco
                  WHERE 1=1
                      AND p_rss.ghm_id > 0
                      AND date_sortie >= date((date_trunc('year',now() - interval '6 month')) - interval '2 year')
                      AND p_rss.etat = ''
                  ;
  
                  ANALYSE w_rss
                  ;
  
                  CREATE INDEX i_w_rss_ghm_id
  		  ON w_rss
  		  USING btree
  		  (ghm_id);
  
          -- Dans le cas de plusieurs GHS, mettre le plus utilisé dans la base
          DROP TABLE IF EXISTS w_ghm_ghs_utilise;
          CREATE TEMP TABLE w_ghm_ghs_utilise AS
          SELECT subview.ghm_id, subview.ghs_id, t_ghs.code AS ghs_code
          FROM
          (
          SELECT ghm_id, (MAX(Array[nb,ghs_id]))[2] AS ghs_id
          FROM
          (
          SELECT ghm_id, ghs_id, count(*) AS nb
          FROM w_rss
          GROUP BY 1,2
          ) subview
          GROUP BY 1
          ) subview
          JOIN pmsi.t_ghs ON subview.ghs_id = t_ghs.oid
          ;
  
          ANALYSE w_ghm_ghs_utilise
          ;
  
          UPDATE w_ghm_ghs SET
              ghs_id = w_ghm_ghs_utilise.ghs_id,
              ghs_code = w_ghm_ghs_utilise.ghs_code
          FROM w_ghm_ghs_utilise
          WHERE w_ghm_ghs.ghm_id = w_ghm_ghs_utilise.ghm_id AND
              w_ghm_ghs_utilise.ghs_id <> w_ghm_ghs.ghs_id
          ;
  
          UPDATE w_ghm_ghs SET
              ghs1_id = w_ghm_ghs_utilise.ghs_id,
              ghs1_code = w_ghm_ghs_utilise.ghs_code
          FROM w_ghm_ghs_utilise
          WHERE w_ghm_ghs.ghm1_id = w_ghm_ghs_utilise.ghm_id AND
              w_ghm_ghs_utilise.ghs_id <> ghs1_id
          ;
  
          UPDATE w_ghm_ghs SET
              ghs2_id = w_ghm_ghs_utilise.ghs_id,
              ghs2_code = w_ghm_ghs_utilise.ghs_code
          FROM w_ghm_ghs_utilise
          WHERE w_ghm_ghs.ghm2_id = w_ghm_ghs_utilise.ghm_id AND
              w_ghm_ghs_utilise.ghs_id <> ghs2_id
          ;
  		
  
                  -- Attribution d'un GHS  "de base" à chaque dossier selon les règles suivantes :
                  --  1. GHS du dossier pour tous les dossiers qui n'ont pas un GHM de niv 1 2 3 ou 4
                  --  2. GHS du dossier pour tous les dossier ayant un GHM de niv 1 2 3 ou 4 mais dont la durée de séjour est inférieure à 3 nuits
                  --  3. GHS du GHM de niveau 2 dès lors que l'âge du patient est supérieur à l'âge prévu comme équivalent CMA
                  --  4. GHS du GHM de niveau 1 si ce GHS existe
                  --  5. GHS du dossier dans tous les autres cas
                  DROP TABLE IF EXISTS w_ghs_base; CREATE TEMP TABLE w_ghs_base AS
                  SELECT
                      p_rss.oid AS rss_id,
                      p_rss.date_sortie,
                      p_rss.duree_sejour,
                      p_rss.age,
                      p_rss.type_etablissement,
                      p_rss.coefficient_mco,
                      ghm_0.ghm_id AS ghm_dos_id,
                      ghm_0.ghm_code::text AS ghm_dos,
                      ghm_0.ghs_id AS ghs_dos_id,
                      ghm_0.ghs_code AS ghs_dos,
                      CASE 
                          WHEN racine.age2 > 0 AND racine.age69 IN (1,3) THEN '<2 ou >69'::text
                          WHEN racine.age2 > 0 AND racine.age69 IN (2,4,6) THEN '<2 ou >79'::text
                          WHEN racine.age2 > 0 THEN '<2'::text
                          WHEN racine.age69 IN (1,3) THEN '>69'::text
                          WHEN racine.age69 IN (2,4,6) THEN '>79'::text
                          ELSE NULL
                      END AS ghm_age_cma,
                      CASE 
                          WHEN substring(ghm_0.ghm_code from  '.$') NOT IN ('1','2','3','4') THEN ghm_0.ghm_id
                          WHEN substring(ghm_0.ghm_code from  '.$') IN ('1','2','3','4') AND p_rss.duree_sejour < 3 THEN ghm_0.ghm_id
                          WHEN (racine.age2 > 0 AND p_rss.age < 2)
                              OR (racine.age69 IN (1,3) AND p_rss.age > 69)
                              OR (racine.age69 IN (2,4,6) AND p_rss.age > 79) THEN ghm_0.ghm2_id
                          WHEN ghm_0.ghm1_code IS DISTINCT FROM NULL THEN ghm_0.ghm1_id
                          ELSE
                              ghm_0.ghm_id
                          END AS ghm_calc_id,
                      CASE 
                          WHEN substring(ghm_0.ghm_code from  '.$') NOT IN ('1','2','3','4') THEN ghm_0.ghm_code
                          WHEN substring(ghm_0.ghm_code from  '.$') IN ('1','2','3','4') AND p_rss.duree_sejour < 3 THEN ghm_0.ghm_code
                          WHEN (racine.age2 > 0 AND p_rss.age < 2)
                              OR (racine.age69 IN (1,3) AND p_rss.age > 69)
                              OR (racine.age69 IN (2,4,6) AND p_rss.age > 79) THEN ghm_0.ghm2_code
                          WHEN ghm_0.ghm1_code IS DISTINCT FROM NULL THEN ghm_0.ghm1_code
                          ELSE
                              ghm_0.ghm_code
                          END AS ghm_calc,
                      CASE 
                          WHEN substring(ghm_0.ghm_code from  '.$') NOT IN ('1','2','3','4') THEN ghm_0.ghs_id::text
                          WHEN substring(ghm_0.ghm_code from  '.$') IN ('1','2','3','4') AND p_rss.duree_sejour < 3 THEN ghm_0.ghs_id::text
                          WHEN (racine.age2 > 0 AND p_rss.age < 2)
                              OR (racine.age69 IN (1,3) AND p_rss.age > 69)
                              OR (racine.age69 IN (2,4,6) AND p_rss.age > 79) THEN ghm_0.ghs2_id::text
                          WHEN ghm_0.ghm1_code <> '' THEN ghm_0.ghs1_id::text
                          ELSE
                              ghm_0.ghs_id::text
                          END AS ghs_calc_id,
                      CASE 
                          WHEN substring(ghm_0.ghm_code from  '.$') NOT IN ('1','2','3','4') THEN ghm_0.ghs_code::text
                          WHEN substring(ghm_0.ghm_code from  '.$') IN ('1','2','3','4') AND p_rss.duree_sejour < 3 THEN ghm_0.ghs_code::text
                          WHEN (racine.age2 > 0 AND p_rss.age < 2)
                              OR (racine.age69 IN (1,3) AND p_rss.age > 69)
                              OR (racine.age69 IN (2,4,6) AND p_rss.age > 79) THEN ghm_0.ghs2_code::text
                          WHEN ghm_0.ghm1_code <> '' THEN ghm_0.ghs1_code::text
                          ELSE
                              ghm_0.ghs_code::text
                          END AS ghs_calc
                  FROM w_rss p_rss
                      JOIN w_ghm_ghs ghm_0 ON ghm_0.ghm_id = p_rss.ghm_id AND type_etablissement = ghm_0.typets
                      LEFT JOIN pmsi.t_datim_racineinfo racine on racine.racine = ghm_0.ghm5_code
                          AND ghm_0.typets = racine.typets
                          AND p_rss.date_sortie BETWEEN racine.date_debut AND racine.date_fin
                  ;
  
                  ANALYSE w_ghs_base
                  ;
  
                  CREATE INDEX i_w_ghs_base_ghs_dos_id
  		  ON w_ghs_base
  		  USING btree
  		  (ghs_dos_id);
  
  		CREATE INDEX i_w_ghs_base_ghs_calc_id
  		  ON w_ghs_base
  		  USING btree
  		  (ghs_calc_id);
  
  		-- Tarifs des GHS
  		DROP TABLE IF EXISTS w_ghs_tarif;
  		CREATE TEMP TABLE w_ghs_tarif AS
  		SELECT subview.oid, subview.code, subview.type_ets,
  			date_debut_array[i] AS date_debut,
  			date_fin_array[i] AS date_fin,
  			tarif_ghs_array[i] AS tarif_ghs,
  			tarif_exh_array[i] AS tarif_exh,
  			tarif_exb_array[i] AS tarif_exb,
  			forfait_exb_array[i] AS forfait_exb,
  			borne_basse_array[i] AS borne_basse,
  			borne_haute_array[i] AS borne_haute
  
  		FROM
  		(
  		SELECT 
  			t_ghs.*,
  			'3'::text AS type_ets,
  			ARRAY[date_debut_public_1,date_debut_public_2,date_debut_public_3,date_debut_public_4,date_debut_public_5,date_debut_public_6,date_debut_public_7,date_debut_public_8,date_debut_public_9] AS date_debut_array,
  			ARRAY[date_fin_public_1,date_fin_public_2,date_fin_public_3,date_fin_public_4,date_fin_public_5,date_fin_public_6,date_fin_public_7,date_fin_public_8,date_fin_public_9] AS date_fin_array,
  			ARRAY[tarif_ghs_public_1,tarif_ghs_public_2,tarif_ghs_public_3,tarif_ghs_public_4,tarif_ghs_public_5,tarif_ghs_public_6,tarif_ghs_public_7,tarif_ghs_public_8,tarif_ghs_public_9] AS tarif_ghs_array,
  			ARRAY[tarif_exh_public_1,tarif_exh_public_2,tarif_exh_public_3,tarif_exh_public_4,tarif_exh_public_5,tarif_exh_public_6,tarif_exh_public_7,tarif_exh_public_8,tarif_exh_public_9] AS tarif_exh_array,
  			ARRAY[tarif_exb_public_1,tarif_exb_public_2,tarif_exb_public_3,tarif_exb_public_4,tarif_exb_public_5,tarif_exb_public_6,tarif_exb_public_7,tarif_exb_public_8,tarif_exb_public_9] AS tarif_exb_array,
  			ARRAY[forfait_exb_public_1,forfait_exb_public_2,forfait_exb_public_3,forfait_exb_public_4,forfait_exb_public_5,forfait_exb_public_6,forfait_exb_public_7,forfait_exb_public_8,forfait_exb_public_9] AS forfait_exb_array,
  			ARRAY[borne_basse_public_1,borne_basse_public_2,borne_basse_public_3,borne_basse_public_4,borne_basse_public_5,borne_basse_public_6,borne_basse_public_7,borne_basse_public_8,borne_basse_public_9] AS borne_basse_array,
  			ARRAY[borne_haute_public_1,borne_haute_public_2,borne_haute_public_3,borne_haute_public_4,borne_haute_public_5,borne_haute_public_6,borne_haute_public_7,borne_haute_public_8,borne_haute_public_9] AS borne_haute_array
  		FROM pmsi.t_ghs
  		UNION
  		SELECT 
  			t_ghs.*,
  			'2'::text AS type_ets,
  			ARRAY[date_debut_public_1,date_debut_public_2,date_debut_public_3,date_debut_public_4,date_debut_public_5,date_debut_public_6,date_debut_public_7,date_debut_public_8,date_debut_public_9] AS date_debut_array,
  			ARRAY[date_fin_public_1,date_fin_public_2,date_fin_public_3,date_fin_public_4,date_fin_public_5,date_fin_public_6,date_fin_public_7,date_fin_public_8,date_fin_public_9] AS date_fin_array,
  			ARRAY[tarif_ghs_public_1,tarif_ghs_public_2,tarif_ghs_public_3,tarif_ghs_public_4,tarif_ghs_public_5,tarif_ghs_public_6,tarif_ghs_public_7,tarif_ghs_public_8,tarif_ghs_public_9] AS tarif_ghs_array,
  			ARRAY[tarif_exh_public_1,tarif_exh_public_2,tarif_exh_public_3,tarif_exh_public_4,tarif_exh_public_5,tarif_exh_public_6,tarif_exh_public_7,tarif_exh_public_8,tarif_exh_public_9] AS tarif_exh_array,
  			ARRAY[tarif_exb_public_1,tarif_exb_public_2,tarif_exb_public_3,tarif_exb_public_4,tarif_exb_public_5,tarif_exb_public_6,tarif_exb_public_7,tarif_exb_public_8,tarif_exb_public_9] AS tarif_exb_array,
  			ARRAY[forfait_exb_public_1,forfait_exb_public_2,forfait_exb_public_3,forfait_exb_public_4,forfait_exb_public_5,forfait_exb_public_6,forfait_exb_public_7,forfait_exb_public_8,forfait_exb_public_9] AS forfait_exb_array,
  			ARRAY[borne_basse_public_1,borne_basse_public_2,borne_basse_public_3,borne_basse_public_4,borne_basse_public_5,borne_basse_public_6,borne_basse_public_7,borne_basse_public_8,borne_basse_public_9] AS borne_basse_array,
  			ARRAY[borne_haute_public_1,borne_haute_public_2,borne_haute_public_3,borne_haute_public_4,borne_haute_public_5,borne_haute_public_6,borne_haute_public_7,borne_haute_public_8,borne_haute_public_9] AS borne_haute_array
  		FROM pmsi.t_ghs
  		UNION
  		SELECT 
  			t_ghs.*,
  			'1'::text AS type_ets,
  			ARRAY[date_debut_prive_1,date_debut_prive_2,date_debut_prive_3,date_debut_prive_4,date_debut_prive_5,date_debut_prive_6,date_debut_prive_7,date_debut_prive_8,date_debut_prive_9] AS date_debut_array,
  			ARRAY[date_fin_prive_1,date_fin_prive_2,date_fin_prive_3,date_fin_prive_4,date_fin_prive_5,date_fin_prive_6,date_fin_prive_7,date_fin_prive_8,date_fin_prive_9] AS date_fin_array,
  			ARRAY[tarif_ghs_prive_1,tarif_ghs_prive_2,tarif_ghs_prive_3,tarif_ghs_prive_4,tarif_ghs_prive_5,tarif_ghs_prive_6,tarif_ghs_prive_7,tarif_ghs_prive_8,tarif_ghs_prive_9] AS tarif_ghs_array,
  			ARRAY[tarif_exh_prive_1,tarif_exh_prive_2,tarif_exh_prive_3,tarif_exh_prive_4,tarif_exh_prive_5,tarif_exh_prive_6,tarif_exh_prive_7,tarif_exh_prive_8,tarif_exh_prive_9] AS tarif_exh_array,
  			ARRAY[tarif_exb_prive_1,tarif_exb_prive_2,tarif_exb_prive_3,tarif_exb_prive_4,tarif_exb_prive_5,tarif_exb_prive_6,tarif_exb_prive_7,tarif_exb_prive_8,tarif_exb_prive_9] AS tarif_exb_array,
  			ARRAY[forfait_exb_prive_1,forfait_exb_prive_2,forfait_exb_prive_3,forfait_exb_prive_4,forfait_exb_prive_5,forfait_exb_prive_6,forfait_exb_prive_7,forfait_exb_prive_8,forfait_exb_prive_9] AS forfait_exb_array,
  			ARRAY[borne_basse_prive_1,borne_basse_prive_2,borne_basse_prive_3,borne_basse_prive_4,borne_basse_prive_5,borne_basse_prive_6,borne_basse_prive_7,borne_basse_prive_8,borne_basse_prive_9] AS borne_basse_array,
  			ARRAY[borne_haute_prive_1,borne_haute_prive_2,borne_haute_prive_3,borne_haute_prive_4,borne_haute_prive_5,borne_haute_prive_6,borne_haute_prive_7,borne_haute_prive_8,borne_haute_prive_9] AS borne_haute_array
  		FROM pmsi.t_ghs
  		) subview
  		JOIN generate_series(1,9) i ON date_debut_array[i] <> '20991231' AND date_fin_array[i] >= '20160101'
  		;
  
  		ANALYSE w_ghs_tarif
                  ;
  
                  UPDATE w_ghs_tarif
  		SET date_debut = '00010101'
  		FROM
  		(
  		SELECT oid, type_ets, MIN(date_debut) AS date_debut
  		FROM w_ghs_tarif
  		GROUP BY 1,2
  		) subview
  		WHERE w_ghs_tarif.oid = subview.oid AND
  		w_ghs_tarif.type_ets = subview.type_ets AND
  		w_ghs_tarif.date_debut = subview.date_debut
  		;
  
                  CREATE INDEX i_w_ghs_tarif_oid
  		  ON w_ghs_tarif
  		  USING btree
  		  (oid);
                 
                  
                  -- Table de calcul des éléments par GHS
                  TRUNCATE pmsi.p_etude_impact_cma;
                  INSERT INTO pmsi.p_etude_impact_cma(
  			    rss_id, sev_ghm, duree_sejour, age, ghm_age_cma, coefficient_mco, ca_ghs_theorique, 
  			    ca_exh, ca_perte_exb, ca_ghs_dossier, ghm_dos, ghm_calc, ghs_dos, 
  			    ghs_calc, tarif_ghs_calc, impact_cma)
                  SELECT
                      w_ghs_base.rss_id,
                      substring(w_ghs_base.ghm_dos from  '.$') AS sev_ghm,
                      w_ghs_base.duree_sejour,
                      w_ghs_base.age,
                      w_ghs_base.ghm_age_cma,
                      w_ghs_base.coefficient_mco,
                      w_ghs_tarif0.tarif_ghs AS ca_ghs_theorique,
                      CASE WHEN w_ghs_base.duree_sejour > w_ghs_tarif0.borne_haute THEN w_ghs_tarif0.tarif_exh * (w_ghs_base.duree_sejour - w_ghs_tarif0.borne_haute) ELSE 0::numeric END AS ca_exh,
                      (CASE WHEN w_ghs_base.duree_sejour < w_ghs_tarif0.borne_basse THEN w_ghs_tarif0.tarif_exb * (w_ghs_tarif0.borne_basse - w_ghs_base.duree_sejour) ELSE 0::numeric END
                      +
                      CASE WHEN w_ghs_base.duree_sejour < w_ghs_tarif0.borne_basse THEN w_ghs_tarif0.forfait_exb ELSE 0::numeric END) AS ca_perte_exb,
                      (
                      w_ghs_tarif0.tarif_ghs 
                      +
                      CASE WHEN w_ghs_base.duree_sejour > w_ghs_tarif0.borne_haute THEN w_ghs_tarif0.tarif_exh * (w_ghs_base.duree_sejour - w_ghs_tarif0.borne_haute) ELSE 0::numeric END
                      -
                      CASE WHEN w_ghs_base.duree_sejour < w_ghs_tarif0.borne_basse THEN w_ghs_tarif0.tarif_exb * (w_ghs_tarif0.borne_basse - w_ghs_base.duree_sejour) ELSE 0::numeric END
                      -
                      CASE WHEN w_ghs_base.duree_sejour < w_ghs_tarif0.borne_basse THEN w_ghs_tarif0.forfait_exb ELSE 0::numeric END 
                      ) AS ca_ghs_dossier,
                      w_ghs_base.ghm_dos,
                      w_ghs_base.ghm_calc,
                      w_ghs_base.ghs_dos,
                      w_ghs_base.ghs_calc,
                      (
                      w_ghs_tarif.tarif_ghs 
                      +
                      CASE WHEN w_ghs_base.duree_sejour > w_ghs_tarif.borne_haute THEN w_ghs_tarif.tarif_exh * (w_ghs_base.duree_sejour - w_ghs_tarif.borne_haute) ELSE 0::numeric END
                      -
                      CASE WHEN w_ghs_base.duree_sejour < w_ghs_tarif.borne_basse THEN w_ghs_tarif.tarif_exb * (w_ghs_tarif.borne_basse - w_ghs_base.duree_sejour) ELSE 0::numeric END
                      -
                      CASE WHEN w_ghs_base.duree_sejour < w_ghs_tarif.borne_basse THEN w_ghs_tarif.forfait_exb ELSE 0::numeric END 
                      ) AS tarif_ghs_calc,
                      0::numeric AS impact_cma
                      
                  FROM w_ghs_base
                      JOIN w_ghs_tarif ON w_ghs_tarif.oid = w_ghs_base.ghs_calc_id AND w_ghs_tarif.type_ets = w_ghs_base.type_etablissement AND date_sortie BETWEEN w_ghs_tarif.date_debut AND w_ghs_tarif.date_fin
                      JOIN w_ghs_tarif w_ghs_tarif0 ON w_ghs_tarif0.oid = w_ghs_base.ghs_dos_id AND w_ghs_tarif0.type_ets = w_ghs_base.type_etablissement AND date_sortie BETWEEN w_ghs_tarif0.date_debut AND w_ghs_tarif0.date_fin
                  ;
  
                  ANALYSE pmsi.p_etude_impact_cma
                  ;
  
                  UPDATE pmsi.p_etude_impact_cma
                  SET impact_cma = ca_ghs_dossier - tarif_ghs_calc
                  WHERE ca_ghs_dossier <> tarif_ghs_calc
  		;
                  
  		RETURN 'OK'
  		;
  END;