rendered paste body-- =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;
/