HOW TO delete JTF_NOTES

API to delete JTF_NOTES

JTF_NOTES_B

JTF_NOTES_B stores all non–translated information about Notes. Notes may be created for any object mapped for object usage in JTF_OBJECTS; the JTF_OBJECT that the note is linked to is stored as the source_object_code and source_object_id. The actual text of the note, which is stored in the JTF_NOTES_TL, is split into two parts under columns NOTES and NOTE_DETAILS. Typically, the NOTES should hold the short form or the first part of the note. NOTE_DETAILS for added details, or whenever the note is longer than the 2000 character limit for the NOTES column. The NOTE_STATUS flags the note as being personal (P), Internal (I), or Publish(E).

 

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('USERNAME','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;
/