return: text
lang: plpgsql
src: |
  DECLARE	
  	result TEXT;
  BEGIN
  		DELETE FROM activite.t_ghm_c 
  		WHERE ghm_id NOT IN (SELECT oid FROM base.t_ghm) AND ghm_id > 0;
  		UPDATE base.t_ghm SET severite = substr(code,6,1) WHERE severite ='' AND oid <> 0;
  		-- Standard
  
  		UPDATE activite.t_ghm_c SET
  			ghm_code = t_ghm.code,
  			ghm_texte = t_ghm.texte,
  			mco_id = t_mco.oid, 
  			mco_code = t_mco.code,
  			mco_texte = t_mco.texte,
  			cas_id = t_ghm.cas_id,
  			cas_code = t_cas.code,
  			cas_texte = t_cas.texte,
  			severite_ghm_id = t_severites_ghm.oid,
  			severite_ghm_code = t_ghm.severite,
  			severite_ghm_texte = t_severites_ghm.texte,
  			cmd_id = t_ghm.cmd_id,
  			cmd_code = t_cmd.code,
  			cmd_texte = t_cmd.texte,
  			ghm5_id = t_ghm.ghm5_id,
  			ghm5_code = t_ghm5.code, 
  			ghm5_texte = t_ghm5.texte
  		FROM base.t_ghm
  			JOIN base.t_cmd ON t_ghm.cmd_id = t_cmd.oid AND t_cmd.code <> '90'
  			JOIN base.t_mco ON t_mco.code::bpchar = CASE 
  						WHEN t_cmd.code = '28'::bpchar THEN 'S'
  						WHEN t_ghm.mco = 'M'::bpchar THEN 'M'
  						WHEN t_ghm.mco = 'C'::bpchar THEN 'C'
  						WHEN t_ghm.mco = 'O'::bpchar THEN 'O'
  						ELSE '*' END
  			JOIN base.t_severites_ghm ON t_ghm.severite = t_severites_ghm.code
  			JOIN base.t_ghm5 ON t_ghm.ghm5_id = t_ghm5.oid
  			JOIN base.t_cas ON t_ghm.cas_id = t_cas.oid
  		WHERE t_ghm.oid = t_ghm_c.ghm_id AND
  			(
  			t_ghm_c.ghm_code IS DISTINCT FROM t_ghm.code OR
  			t_ghm_c.ghm_texte IS DISTINCT FROM t_ghm.texte OR
  			t_ghm_c.mco_id IS DISTINCT FROM t_mco.oid OR 
  			t_ghm_c.mco_code IS DISTINCT FROM t_mco.code OR
  			t_ghm_c.mco_texte IS DISTINCT FROM t_mco.texte OR
  			t_ghm_c.cas_id IS DISTINCT FROM t_ghm.cas_id OR
  			t_ghm_c.cas_code IS DISTINCT FROM t_cas.code OR
  			t_ghm_c.cas_texte IS DISTINCT FROM t_cas.texte OR
  			t_ghm_c.severite_ghm_id IS DISTINCT FROM t_severites_ghm.oid OR
  			t_ghm_c.severite_ghm_code IS DISTINCT FROM t_ghm.severite OR
  			t_ghm_c.severite_ghm_texte IS DISTINCT FROM t_severites_ghm.texte OR
  			t_ghm_c.cmd_id IS DISTINCT FROM t_ghm.cmd_id OR
  			t_ghm_c.cmd_code IS DISTINCT FROM t_cmd.code OR
  			t_ghm_c.cmd_texte IS DISTINCT FROM t_cmd.texte OR
  			t_ghm_c.ghm5_id IS DISTINCT FROM t_ghm.ghm5_id OR
  			t_ghm_c.ghm5_code IS DISTINCT FROM t_ghm5.code OR 
  			t_ghm_c.ghm5_texte IS DISTINCT FROM t_ghm5.texte
  			);
  		INSERT INTO activite.t_ghm_c(
  			ghm_id, 
  			ghm_code, 
  			ghm_texte, 
  			mco_id, 
  			mco_code, 
  			mco_texte,
  			cas_id, 
  			cas_code, 
  			cas_texte, 
  			severite_ghm_id, 
  			severite_ghm_code, 
  			severite_ghm_texte, 
  			cmd_id, 
  			cmd_code, 
  			cmd_texte, 
  			ghm5_id, 
  			ghm5_code, 
  			ghm5_texte)
  		SELECT 
  			t_ghm.oid, 
  			t_ghm.code AS ghm_code, 
  			t_ghm.texte AS ghm_texte, 
  			t_mco.oid  AS mco_id,
  			t_mco.code  AS mco_code,
  			t_mco.texte AS mco_texte,
  			t_ghm.cas_id, 
  			t_cas.code AS cas_code, 
  			t_cas.texte AS cas_texte,
  			t_severites_ghm.oid AS severite_ghm_id,
  			t_ghm.severite AS severite_ghm_code, 
  			t_severites_ghm.texte AS severite_ghm_texte, 
  			t_ghm.cmd_id, 
  			t_cmd.code AS cmd_code, 
  			t_cmd.texte_court AS cmd_texte, 
  			t_ghm.ghm5_id AS ghm5_id, 
  			t_ghm5.code AS ghm5_code, 
  			t_ghm5.texte AS ghm5_texte
   		FROM base.t_ghm
  			JOIN base.t_cmd ON t_ghm.cmd_id = t_cmd.oid AND t_cmd.code <> '90'
  			JOIN base.t_mco ON t_mco.code::bpchar = CASE 
  						WHEN t_cmd.code = '28'::bpchar THEN 'S'
  						WHEN t_ghm.mco = 'M'::bpchar THEN 'M'
  						WHEN t_ghm.mco = 'C'::bpchar THEN 'C'
  						WHEN t_ghm.mco = 'O'::bpchar THEN 'O'
  						ELSE '*' END
  			JOIN base.t_severites_ghm ON t_ghm.severite = t_severites_ghm.code
  			JOIN base.t_ghm5 ON t_ghm.ghm5_id = t_ghm5.oid
  			JOIN base.t_cas ON t_ghm.cas_id = t_cas.oid
  		WHERE t_ghm.oid NOT IN (SELECT ghm_id FROM activite.t_ghm_c);
  		-- Codes spéciaux
  		UPDATE activite.t_ghm_c SET
  			ghm_code = t_ghm.code,
  			ghm_texte = t_ghm.texte,
  			mco_id = t_mco.oid, 
  			mco_code = t_mco.code,
  			mco_texte = t_mco.texte,
  			cas_id = t_cas.oid,
  			cas_code = t_cas.code,
  			cas_texte = t_cas.texte,
  			severite_ghm_id = t_severites_ghm.oid,
  			severite_ghm_code = t_severites_ghm.code,
  			severite_ghm_texte = t_severites_ghm.texte,
  			cmd_id = t_ghm.cmd_id,
  			cmd_code = t_ghm.cmd_code,
  			cmd_texte = t_ghm.cmd_texte,
  			ghm5_id = t_ghm.oid,
  			ghm5_code = t_ghm.code, 
  			ghm5_texte = t_ghm.texte
  		FROM (
  				SELECT -9991 AS oid, '99_SE1' AS code, 'Hors GHM. Forfaits SE1' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9992 AS oid, '99_SE2' AS code, 'Hors GHM. Forfaits SE2' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9993 AS oid, '99_SE3' AS code, 'Hors GHM. Forfaits SE3' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9994 AS oid, '99_SE4' AS code, 'Hors GHM. Forfaits SE4' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9995 AS oid, '99_ATU' AS code, 'Hors GHM. Forfaits ATU' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9996 AS oid, '99_FFM' AS code, 'Hors GHM. Forfaits FFM' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9990 AS oid, '99_ZZZ' AS code, 'Hors GHM. Divers' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9980 AS oid, '98_P' AS code, 'Champ GHM. Présents' AS texte, -9980 AS cmd_id, '98' AS cmd_code, 'Champ GHM. Non groupés' AS cmd_texte, -9980 AS groupe_id, 'ZY' AS groupe_code, 'Champ GHM. Non groupés' AS groupe_texte
  				UNION
  				SELECT -9981 AS oid, '98_NG' AS code, 'Champ GHM. Sortis non groupés' AS texte, -9980 AS cmd_id, '98' AS cmd_code, 'Champ GHM. Non groupés' AS cmd_texte, -9980 AS groupe_id, 'ZY' AS groupe_code, 'Champ GHM. Non groupés' AS groupe_texte) t_ghm
  			JOIN base.t_mco ON t_mco.code = '*' 
  			JOIN base.t_severites_ghm ON t_severites_ghm.code = '*' 
  			JOIN base.t_cas ON t_cas.code = '*'
  		WHERE t_ghm.oid = t_ghm_c.ghm_id AND
  			(
  			t_ghm_c.ghm_code IS DISTINCT FROM t_ghm.code OR
  			t_ghm_c.ghm_texte IS DISTINCT FROM t_ghm.texte OR
  			t_ghm_c.mco_id IS DISTINCT FROM t_mco.oid OR 
  			t_ghm_c.mco_code IS DISTINCT FROM t_mco.code OR
  			t_ghm_c.mco_texte IS DISTINCT FROM t_mco.texte OR
  			t_ghm_c.cas_id IS DISTINCT FROM t_cas.oid OR
  			t_ghm_c.cas_code IS DISTINCT FROM t_cas.code OR
  			t_ghm_c.cas_texte IS DISTINCT FROM t_cas.texte OR
  			t_ghm_c.severite_ghm_id IS DISTINCT FROM t_severites_ghm.oid OR
  			t_ghm_c.severite_ghm_code IS DISTINCT FROM t_severites_ghm.code OR
  			t_ghm_c.severite_ghm_texte IS DISTINCT FROM t_severites_ghm.texte OR
  			t_ghm_c.cmd_id IS DISTINCT FROM t_ghm.cmd_id OR
  			t_ghm_c.cmd_code IS DISTINCT FROM t_ghm.cmd_code OR
  			t_ghm_c.cmd_texte IS DISTINCT FROM t_ghm.cmd_texte OR
  			t_ghm_c.ghm5_id IS DISTINCT FROM t_ghm.oid OR
  			t_ghm_c.ghm5_code IS DISTINCT FROM t_ghm.code OR 
  			t_ghm_c.ghm5_texte IS DISTINCT FROM t_ghm.texte
  			);
  		INSERT INTO activite.t_ghm_c(
  			ghm_id, 
  			ghm_code, 
  			ghm_texte, 
  			mco_id, 
  			mco_code, 
  			mco_texte,
  			cas_id, 
  			cas_code, 
  			cas_texte, 
  			severite_ghm_id, 
  			severite_ghm_code, 
  			severite_ghm_texte, 
  			cmd_id, 
  			cmd_code, 
  			cmd_texte, 
  			ghm5_id, 
  			ghm5_code, 
  			ghm5_texte)
  		SELECT 
  			t_ghm.oid AS oid, 
  			t_ghm.code AS ghm_code, 
  			t_ghm.texte AS ghm_texte, 
  			t_mco.oid  AS mco_id,
  			t_mco.code  AS mco_code,
  			t_mco.texte AS mco_texte,
  			t_cas.oid AS cas_id, 
  			t_cas.code AS cas_code, 
  			t_cas.texte AS cas_texte,
  			t_severites_ghm.oid AS severite_ghm_id,
  			t_severites_ghm.code AS severite_ghm_code, 
  			t_severites_ghm.texte AS severite_ghm_texte, 
  			t_ghm.cmd_id AS cmd_id, 
  			t_ghm.cmd_code AS cmd_code, 
  			t_ghm.cmd_texte AS cmd_texte,
  			t_ghm.oid AS ghm5_id, 
  			t_ghm.code AS ghm5_code, 
  			t_ghm.texte AS ghm5_texte
   		FROM (
  				SELECT -9991 AS oid, '99_SE1' AS code, 'Hors GHM. Forfaits SE1' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9992 AS oid, '99_SE2' AS code, 'Hors GHM. Forfaits SE2' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9993 AS oid, '99_SE3' AS code, 'Hors GHM. Forfaits SE3' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9994 AS oid, '99_SE4' AS code, 'Hors GHM. Forfaits SE4' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION				
                  SELECT -9997 AS oid, '99_SE5' AS code, 'Hors GHM. Forfaits SE5' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION				
                  SELECT -9998 AS oid, '99_SE6' AS code, 'Hors GHM. Forfaits SE6' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9995 AS oid, '99_ATU' AS code, 'Hors GHM. Forfaits ATU' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9996 AS oid, '99_FFM' AS code, 'Hors GHM. Forfaits FFM' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9990 AS oid, '99_ZZZ' AS code, 'Hors GHM. Divers' AS texte, -9990 AS cmd_id, '99' AS cmd_code, 'Hors GHM' AS cmd_texte, -9990 AS groupe_id, 'ZZ' AS groupe_code, 'Hors GHM' AS groupe_texte
  				UNION
  				SELECT -9980 AS oid, '98_P' AS code, 'Champ GHM. Présents' AS texte, -9980 AS cmd_id, '98' AS cmd_code, 'Champ GHM. Non groupés' AS cmd_texte, -9980 AS groupe_id, 'ZY' AS groupe_code, 'Champ GHM. Non groupés' AS groupe_texte
  				UNION
  				SELECT -9981 AS oid, '98_NG' AS code, 'Champ GHM. Sortis non groupés' AS texte, -9980 AS cmd_id, '98' AS cmd_code, 'Champ GHM. Non groupés' AS cmd_texte, -9980 AS groupe_id, 'ZY' AS groupe_code, 'Champ GHM. Non groupés' AS groupe_texte) t_ghm
  			JOIN base.t_mco ON t_mco.code = '*' 
  			JOIN base.t_severites_ghm ON t_severites_ghm.code = '*' 
  			JOIN base.t_cas ON t_cas.code = '*'
  		WHERE t_ghm.oid NOT IN (SELECT ghm_id FROM activite.t_ghm_c);
  		-- Erreurs de groupage 
  		UPDATE activite.t_ghm_c SET
  			ghm_code = t_ghm.code,
  			ghm_texte = t_ghm.texte,
  			mco_id = t_mco.oid, 
  			mco_code = t_mco.code,
  			mco_texte = t_mco.texte,
  			cas_id = t_cas.oid,
  			cas_code = t_cas.code,
  			cas_texte = t_cas.texte,
  			severite_ghm_id = t_severites_ghm.oid,
  			severite_ghm_code = t_severites_ghm.code,
  			severite_ghm_texte = t_severites_ghm.texte,
  			cmd_id = t_ghm.cmd_id,
  			cmd_code = t_cmd.code,
  			cmd_texte = t_cmd.texte,
  			ghm5_id = t_ghm.ghm5_id,
  			ghm5_code = t_ghm5.code, 
  			ghm5_texte = t_ghm5.texte
  		FROM base.t_ghm
  			JOIN base.t_cmd ON t_ghm.cmd_id = t_cmd.oid AND t_cmd.code = '90'
  			JOIN base.t_mco ON t_mco.code = '*'
  			JOIN base.t_severites_ghm ON t_severites_ghm.code = '*' 
  			JOIN base.t_cas ON t_cas.code = '*'
  			JOIN base.t_ghm5 ON t_ghm.ghm5_id = t_ghm5.oid
  		WHERE t_ghm.oid = t_ghm_c.ghm_id AND
  			(
  			t_ghm_c.ghm_code IS DISTINCT FROM t_ghm.code OR
  			t_ghm_c.ghm_texte IS DISTINCT FROM t_ghm.texte OR
  			t_ghm_c.mco_id IS DISTINCT FROM t_mco.oid OR 
  			t_ghm_c.mco_code IS DISTINCT FROM t_mco.code OR
  			t_ghm_c.mco_texte IS DISTINCT FROM t_mco.texte OR
  			t_ghm_c.cas_id IS DISTINCT FROM t_cas.oid OR
  			t_ghm_c.cas_code IS DISTINCT FROM t_cas.code OR
  			t_ghm_c.cas_texte IS DISTINCT FROM t_cas.texte OR
  			t_ghm_c.severite_ghm_id IS DISTINCT FROM t_severites_ghm.oid OR
  			t_ghm_c.severite_ghm_code IS DISTINCT FROM t_severites_ghm.code OR
  			t_ghm_c.severite_ghm_texte IS DISTINCT FROM t_severites_ghm.texte OR
  			t_ghm_c.cmd_id IS DISTINCT FROM t_ghm.cmd_id OR
  			t_ghm_c.cmd_code IS DISTINCT FROM t_cmd.code OR
  			t_ghm_c.cmd_texte IS DISTINCT FROM t_cmd.texte OR
  			t_ghm_c.ghm5_id IS DISTINCT FROM t_ghm.ghm5_id OR
  			t_ghm_c.ghm5_code IS DISTINCT FROM t_ghm5.code OR 
  			t_ghm_c.ghm5_texte IS DISTINCT FROM t_ghm5.texte
  			);
  		INSERT INTO activite.t_ghm_c(
  			ghm_id, 
  			ghm_code, 
  			ghm_texte, 
  			mco_id, 
  			mco_code, 
  			mco_texte,
  			cas_id, 
  			cas_code, 
  			cas_texte, 
  			severite_ghm_id, 
  			severite_ghm_code, 
  			severite_ghm_texte, 
  			cmd_id, 
  			cmd_code, 
  			cmd_texte, 
  			ghm5_id, 
  			ghm5_code, 
  			ghm5_texte)
  		SELECT 
  			t_ghm.oid AS oid, 
  			t_ghm.code AS ghm_code, 
  			t_ghm.texte AS ghm_texte, 
  			t_mco.oid  AS mco_id,
  			t_mco.code  AS mco_code,
  			t_mco.texte AS mco_texte,
  			t_cas.oid AS cas_id, 
  			t_cas.code AS cas_code, 
  			t_cas.texte AS cas_texte,
  			t_severites_ghm.oid AS severite_ghm_id,
  			t_severites_ghm.code AS severite_ghm_code, 
  			t_severites_ghm.texte AS severite_ghm_texte, 
  			t_ghm.cmd_id AS cmd_id, 
  			t_cmd.code AS cmd_code, 
  			t_cmd.texte AS cmd_texte, 
  			t_ghm.ghm5_id AS ghm5_id, 
  			t_ghm5.code AS ghm5_code, 
  			t_ghm5.texte AS ghm5_texte
   		FROM base.t_ghm
  			JOIN base.t_cmd ON t_ghm.cmd_id = t_cmd.oid AND t_cmd.code = '90'
  			JOIN base.t_mco ON t_mco.code = '*'
  			JOIN base.t_severites_ghm ON t_severites_ghm.code = '*' 
  			JOIN base.t_cas ON t_cas.code = '*'
  			JOIN base.t_ghm5 ON t_ghm.ghm5_id = t_ghm5.oid
  		WHERE t_ghm.oid NOT IN (SELECT ghm_id FROM activite.t_ghm_c);
  		PERFORM base.cti_update_classes('activite', 't_ghm_c', 'GHM','ghm_section');
  
  	RETURN 'OK';
  END;