-- =CONCATENATE("insert_into_sys_domain(6,'";A2;"','";SUBSTITUTE(F2;"'";"''");"','";B2;"',";C2;",'";D2;"');")

CREATE OR REPLACE PROCEDURE insert_into_sys_domain
(
    i_lang          LANGUAGE.id_language%TYPE,
    i_code_domain   sys_domain.code_domain%TYPE,
    i_desc_val      sys_domain.desc_val%TYPE,
    i_val           sys_domain.val%TYPE,
    i_rank          sys_domain.rank%TYPE DEFAULT NULL,
    i_img_name      sys_domain.img_name%TYPE DEFAULT NULL,
    i_flg_available sys_domain.flg_available%TYPE DEFAULT 'Y'
) IS
BEGIN

    MERGE INTO sys_domain t
    USING (SELECT a.code_domain, --
                  a.id_language,
                  a.desc_val,
                  a.val,
                  coalesce(a.rank, b.rank, 0) rank,
                  nvl(a.img_name, b.img_name) img_name,
                  nvl(a.flg_available, b.flg_available) flg_available
             FROM (SELECT i_code_domain   code_domain, --
                          i_lang          id_language,
                          i_desc_val      desc_val,
                          i_val           val,
                          i_rank          rank,
                          i_img_name      img_name,
                          i_flg_available flg_available
                     FROM dual) a,
                  sys_domain b
            WHERE b.code_domain(+) = a.code_domain
              AND b.val(+) = a.val
              AND b.id_language(+) = 1) args
    ON (t.id_language = args.id_language AND t.code_domain = args.code_domain AND t.val = args.val)
    WHEN MATCHED THEN
        UPDATE
           SET t.desc_val      = args.desc_val,
               t.rank          = args.rank,
               t.img_name      = args.img_name,
               t.flg_available = args.flg_available
    WHEN NOT MATCHED THEN
        INSERT
            (code_domain, id_language, desc_val, val, rank, img_name, flg_available)
        VALUES
            (args.code_domain, args.id_language, args.desc_val, args.val, args.rank, args.img_name, args.flg_available);

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('ADDING ' || i_code_domain || ': ' || i_desc_val || ' / ' || SQLERRM);
END;
/
--=CONCATENATE("insert_into_sys_message(6,'";C2;"','";SUBSTITUTE(E2;"'";"''");"','A',";A2;",";B2;");")
--=CONCATENATE("insert_into_sys_message(1,'";C2;"','";SUBSTITUTE(SUBSTITUTE(E2;"'";"''");"@";"'||'@");"','A',";A2;",";B2;");")

CREATE OR REPLACE PROCEDURE insert_into_sys_message
(
    i_lang           LANGUAGE.id_language%TYPE,
    i_code_message   sys_message.code_message%TYPE,
    i_desc_message   sys_message.desc_message%TYPE,
    i_flg_type       sys_message.flg_type%TYPE default NULL,
    i_software       software.id_software%TYPE DEFAULT 0,
    i_institution    institution.id_institution%TYPE DEFAULT 0,
    i_img_name       sys_message.img_name%TYPE DEFAULT NULL,
    i_id_sys_message sys_message.id_sys_message%TYPE DEFAULT NULL
) IS
BEGIN

    MERGE INTO sys_message t
    USING (SELECT i_code_message code_message, --
                  i_desc_message desc_message, --
                  coalesce(i_flg_type,
                           (SELECT *
                              FROM (SELECT s.flg_type
                                      FROM sys_message s
                                     WHERE s.flg_type IS NOT NULL
                                       AND s.code_message = i_code_message
                                       AND s.id_software = i_software
                                       AND s.id_institution = i_institution
                                     ORDER BY s.id_language ASC)
                             WHERE rownum < 2),
                           'A') flg_type,
                  i_lang id_language,
                  'Y' flg_available,
                  nvl(i_img_name,
                      (SELECT *
                         FROM (SELECT s.img_name
                                 FROM sys_message s
                                WHERE s.img_name IS NOT NULL
                                  AND s.code_message = i_code_message
                                  AND s.id_software = i_software
                                  AND s.id_institution = i_institution
                                ORDER BY s.id_language ASC)
                        WHERE rownum < 2)) img_name,
                  i_software id_software,
                  i_institution id_institution
             FROM dual) args
    ON (t.id_language = args.id_language AND t.code_message = args.code_message AND t.id_institution = args.id_institution AND t.id_software = args.id_software)
    WHEN MATCHED THEN
        UPDATE
           SET t.desc_message = args.desc_message
    WHEN NOT MATCHED THEN
        INSERT
            (code_message,
             desc_message,
             flg_type,
             id_language,
             flg_available,
             img_name,
             id_sys_message,
             id_software,
             id_institution,
             adw_last_update)
        VALUES
            (args.code_message,
             args.desc_message,
             args.flg_type,
             args.id_language,
             args.flg_available,
             args.img_name,
             --se i_id_sys_message não é fornecido tem-se que colocar algo
             nvl(i_id_sys_message, seq_sys_message.NEXTVAL),
             args.id_software,
             args.id_institution,
             SYSDATE);

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('ADDING ' || i_code_message || ': ' || i_desc_message || ' / ' || SQLERRM);
END;
/
--=CONCATENATE("insert_into_translation(6,'";C2;"','";SUBSTITUTE(E2;"'";"''");"');")

CREATE OR REPLACE PROCEDURE insert_into_translation
(
        i_lang       IN LANGUAGE.id_language%TYPE,
        i_code_trans IN translation.code_translation%TYPE,
        i_desc_trans in translation.desc_translation%TYPE
) IS
    l_max translation.id_translation%TYPE;
BEGIN

    --SELECT MAX(id_translation) + 1
    --  INTO l_max
    --  FROM translation;
    --pk_utils.reset_sequence('seq_translation', l_max);

     MERGE INTO translation t
        USING (SELECT i_code_trans code_translation, --
                      i_desc_trans desc_translation,
                      i_lang       id_language
                 FROM dual) args
        ON (t.id_language = args.id_language AND t.code_translation = args.code_translation)
        WHEN MATCHED THEN
            UPDATE
               SET desc_translation = args.desc_translation
        WHEN NOT MATCHED THEN
            INSERT
                (id_translation, code_translation, desc_translation, id_language, adw_last_update)
            VALUES
                (seq_translation.nextval, args.code_translation, args.desc_translation, args.id_language, SYSDATE);

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('ADDING ' || i_code_trans || ': "' || i_desc_trans || '" / ' || SQLERRM);
END;
/