Sunday, September 05, 2010

Deleting JTF Notes using a pl/sql Script

I tried to delete notes using a pl/sql script. i got following errors


  1. JTF_FUNCTION_NOT_GRANTED (FUNCTION=JTF_NOTE_DELETE)

  2. User-Defined Exception in Package JTF_NOTES_PUB Procedure Delete_note



I looked at various metalink note and found this a note How to delete a note from a quote in HTML Quoting ? [ID 1149108.1]. I used Fnd Security Administration responsibility and tried to creat grants, i did something else too. i could not view what i did. I tried to delete a note but still got same error. I had already added JTF_NOTE_DELETE function to menu CSX_CUSTOMER_SUPPORT. Since it failed to Delete, i deleted the new GRANT that i created.

After it failed to delete a note, i added sub function "JTF_NOTE_DELETE" to menu JTF_NOTES_USER. This time i succeeded in JTF NOTE Deletion. the script i used is given below


set serveroutput on
DECLARE
CURSOR c_notes IS
SELECT jtf_note_id
FROM jtf.jtf_notes_b
where jtf_note_id = 94492027
;
l_ret_status VARCHAR2(3);
l_msg_data VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_index NUMBER;
PROCEDURE write_msg(p_msg VARCHAR2) Is
BEGIN
-- fnd_file.put_line(fnd_file.LOG, TO_CHAR(sysdate,'MM/DD/YYYY HH24:MI:SS') || p_msg);
dbms_output.put_line(p_msg);
END;
PROCEDURE get_user_id ( p_user_name VARCHAR2
, p_user_id IN OUT NUMBER
, p_err_msg IN OUT VARCHAR2
) IS
BEGIN
p_err_msg := null;
SELECT user_id
INTO p_user_id
FROM fnd_user
WHERE user_name = p_user_name;
--
--
EXCEPTION
WHEN OTHERS THEN
p_user_id := -1;
p_err_msg := 'procedure Get_user_Id error:'||sqlerrm;
END;
PROCEDURE get_resp_id ( p_resp_key VARCHAR2
, p_resp_id IN OUT NUMBER
, p_resp_appl_id IN OUT NUMBER
, p_err_msg IN OUT VARCHAR2
) IS
BEGIN
SELECT responsibility_id
, application_id
INTO p_resp_id
, p_resp_appl_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key;
--
--
EXCEPTION
WHEN OTHERS THEN
p_resp_id := -1;
p_resp_appl_id := -1;
p_err_msg := 'procedure Get_resp_Id error:'||sqlerrm;
END;
PROCEDURE init
( p_user_name VARCHAR2
, p_resp_key VARCHAR2
, px_err_msg IN OUT VARCHAR2
) Is
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
BEGIN
px_err_msg := null;
get_user_id(p_user_name, l_user_id, px_err_msg);
IF px_err_msg IS NOT NULL
THEN
RETURN;
END IF;
--
get_resp_id(p_resp_key, l_resp_id, l_appl_id, px_err_msg);
IF px_err_msg IS NOT NULL
THEN
RETURN;
END IF;
--
--
FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_appl_id);
write_msg(l_user_id || ','|| l_resp_id || ',' || l_appl_id);
END;
BEGIN
init('SMISRA','ORACLE_SUPPORT', l_msg_data);
FOR l_note_rec in c_notes
LOOP
write_msg('Inside for Loop');
jtf_notes_pub.secure_delete_note
( p_api_version => 1.0
, p_init_msg_list => 'T'
, p_commit => 'F'
, p_validation_level => 100
, x_return_status => l_ret_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_jtf_note_id => l_note_rec.jtf_note_id
, p_use_AOL_security => 'T'
) ;
write_msg('Return Status :' || l_ret_status);
IF l_ret_status <> FND_API.G_RET_STS_SUCCESS
THEN
IF l_msg_count >= 1
THEN
FOR l_count IN 1..l_msg_count
LOOP
fnd_msg_pub.get(l_count, 'F', l_msg_data, l_msg_index);
write_msg('Error While deleting note ' || l_note_rec.jtf_note_id || ' ' || l_msg_data);
END LOOP;
ELSE
write_msg('Undocumented error from jtf_notes_pub.create_note, return status is: '
|| l_ret_status
|| ' MSG COUNT IS '
|| l_msg_count
|| ' MESSAGE IS '|| l_msg_data
);
END IF;
END IF; -- END RETURN NOT SUCCESS
END LOOP;
END;
/

No comments:

Post a Comment