return: text lang: plpgsql src: | DECLARE result TEXT; BEGIN -- Standard UPDATE activite.t_ghs_c SET ghs_code = t_ghs.code, ghs_code_text = CASE WHEN t_ghs.code_text IS NOT NULL AND t_ghs.code_text <> '' THEN t_ghs.code_text ELSE to_char(t_ghs.code,'FM0000') END , ghs_texte = t_ghs.texte, ghm_id=t_ghm.oid, 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, cmd_id=t_cmd.oid, cmd_code=t_cmd.code, cmd_texte=t_cmd.texte FROM base.t_ghs, base.t_ghm, base.t_mco, base.t_cmd WHERE t_ghs_c.ghs_id = t_ghs.oid AND t_ghs.oid > 0 AND t_ghs.ghm_id = t_ghm.oid AND t_mco.code = t_ghm.mco AND t_cmd.oid = t_ghm.cmd_id AND ( t_ghs_c.ghs_code IS DISTINCT FROM t_ghs.code OR t_ghs_c.ghs_code_text IS DISTINCT FROM CASE WHEN t_ghs.code_text IS NOT NULL AND t_ghs.code_text <> '' THEN t_ghs.code_text ELSE to_char(t_ghs.code,'FM0000') END OR t_ghs_c.ghs_texte IS DISTINCT FROM t_ghs.texte OR t_ghs_c.ghm_id IS DISTINCT FROM t_ghm.oid OR t_ghs_c.ghm_code IS DISTINCT FROM t_ghm.code OR t_ghs_c.ghm_texte IS DISTINCT FROM t_ghm.texte OR t_ghs_c.mco_id IS DISTINCT FROM t_mco.oid OR t_ghs_c.mco_code IS DISTINCT FROM t_mco.code OR t_ghs_c.mco_texte IS DISTINCT FROM t_mco.texte OR t_ghs_c.cmd_id IS DISTINCT FROM t_cmd.oid OR t_ghs_c.cmd_code IS DISTINCT FROM t_cmd.code OR t_ghs_c.cmd_texte IS DISTINCT FROM t_cmd.texte ); INSERT INTO activite.t_ghs_c( ghs_id, ghs_code, ghs_code_text, ghs_texte, ghm_id, ghm_code, ghm_texte, mco_id, mco_code, mco_texte, cmd_id, cmd_code, cmd_texte) SELECT t_ghs.oid, t_ghs.code, CASE WHEN t_ghs.code_text IS NOT NULL AND t_ghs.code_text <> '' THEN t_ghs.code_text ELSE to_char(t_ghs.code,'FM0000') END AS ghs_code_text, t_ghs.texte, t_ghm.oid, t_ghm.code, t_ghm.texte, t_mco.oid, t_mco.code, t_mco.texte, t_cmd.oid, t_cmd.code, t_cmd.texte FROM base.t_ghs, base.t_ghm, base.t_mco, base.t_cmd WHERE t_ghs.ghm_id::text = t_ghm.oid::text AND t_ghs.oid >= 0 AND t_mco.code = t_ghm.mco AND t_cmd.oid = t_ghm.cmd_id AND t_ghs.oid NOT IN (SELECT ghs_id FROM activite.t_ghs_c) ORDER BY t_ghs.oid; -- Codes spéciaux UPDATE activite.t_ghs_c SET ghs_code = 0 - t_ghs.oid, ghs_code_text = (0 - t_ghs.oid)::text, ghs_texte = t_ghs.texte, ghm_id = t_ghs.oid, ghm_code = t_ghs.code, ghm_texte = t_ghs.texte, mco_id = t_mco.oid, mco_code = t_mco.code, mco_texte = t_mco.texte, cmd_id = t_ghs.cmd_id, cmd_code = t_ghs.cmd_code, cmd_texte = t_ghs.cmd_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_ghs 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_ghs_c.ghs_id = t_ghs.oid AND ( t_ghs_c.ghs_code IS DISTINCT FROM 0 - t_ghs.oid OR t_ghs_c.ghs_code_text IS DISTINCT FROM (0 - t_ghs.oid)::text OR t_ghs_c.ghs_texte IS DISTINCT FROM t_ghs.texte OR t_ghs_c.ghm_id IS DISTINCT FROM t_ghs.oid OR t_ghs_c.ghm_code IS DISTINCT FROM t_ghs.code OR t_ghs_c.ghm_texte IS DISTINCT FROM t_ghs.texte OR t_ghs_c.mco_id IS DISTINCT FROM t_mco.oid OR t_ghs_c.mco_code IS DISTINCT FROM t_mco.code OR t_ghs_c.mco_texte IS DISTINCT FROM t_mco.texte OR t_ghs_c.cmd_id IS DISTINCT FROM t_ghs.cmd_id OR t_ghs_c.cmd_code IS DISTINCT FROM t_ghs.cmd_code OR t_ghs_c.cmd_texte IS DISTINCT FROM t_ghs.cmd_texte ); INSERT INTO activite.t_ghs_c( ghs_id, ghs_code, ghs_code_text, ghs_texte, ghm_id, ghm_code, ghm_texte, mco_id, mco_code, mco_texte, cmd_id, cmd_code, cmd_texte) SELECT t_ghs.oid, 0 - t_ghs.oid, (0 - t_ghs.oid)::text, t_ghs.texte, t_ghs.oid AS ghm_id, t_ghs.code AS ghm_code, t_ghs.texte AS ghm_texte, t_mco.oid AS mco_id, t_mco.code AS mco_code, t_mco.texte AS mco_texte, cmd_id, cmd_code, cmd_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_ghs 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_ghs.oid NOT IN (SELECT ghs_id FROM activite.t_ghs_c) ORDER BY t_ghs.oid; RETURN 'OK'; END;