I had been wondering how to insert sticky notes from an api. Sticky notes use two tables: FND_NOTE_BOOK_TAB and FND_NOTE_PAGE_TAB. There is one note book for an object that can have multiple note pages. Each page is connected to the note book via a note_id.

To test this I prepared the following:

1. A script to insert a new note book (this is when you don’t have sticky notes yet on an object)
2. A script to insert a new note page based upon the note id as prepared in step 1.

Script 1:

declare
  info_             varchar2(32000) := NULL;
  attr_             varchar2(32000) := NULL;
  objid_            fnd_note_book.objid%type;
  objversion_       fnd_note_book.objversion%type;
  lu_name_          fnd_note_book.lu_name%type;
  key_ref_          fnd_note_book.key_ref%type;
begin
  lu_name_ := 'SupplierBlanket';
  key_ref_ := 'BLANKET_ORDER=152^';
  Client_Sys.Clear_Attr(attr_);
  Client_Sys.Add_To_Attr('KEY_REF', key_ref_, attr_);
  Client_Sys.Add_To_Attr('LU_NAME', 'SupplierBlanket', attr_);
  Client_Sys.Add_To_Attr('PINNED_DB', 'FALSE', attr_);
  Client_Sys.Add_To_Attr('HEIGHT', 135, attr_);
  Client_Sys.Add_To_Attr('POS_X', 1015, attr_);
  Client_Sys.Add_To_Attr('POS_Y', 85, attr_);
  Client_Sys.Add_To_Attr('WIDTH', 200, attr_);

  ifsapp.fnd_note_book_api.New__(info_, objid_, objversion_, attr_, 'DO');
  commit;
end;

In this case a new sticky note is prepared for a Supplier Agreement with the number 152. If you are familiar with the key_ref format, keep reading else you have to search for an answer how to use it on other objects.

Use the following SQL statement to retrieve the note_id:

select * from fnd_note_book
where lu_name like 'SupplierB%';
Script 2 to is for the page:

declare
  info_             varchar2(32000) := NULL;
  attr_             varchar2(32000) := NULL;
  objid_            fnd_note_page.objid%type;
  objversion_       fnd_note_page.objversion%type;
  note_id_          fnd_note_page.note_id%type;
  rowid_            varchar2(1000);
  text_             varchar2(32000);
  new_line_         varchar2(20) := '\fs20\par ';
 
  cursor get_fnp is
  select objversion, rowid from fnd_note_page fn
   where note_id = note_id_
     and text is null;
begin
  note_id_ := '3B7284AD1C804A5EAFDE57CD93F71BA4';
  Client_Sys.Clear_Attr(attr_);
  Client_Sys.Add_To_Attr('NOTE_ID', note_id_, attr_);

  ifsapp.fnd_note_page_api.New__(info_, objid_, objversion_, attr_, 'DO');
 
  -- Sticky now available, insert the text
  open get_fnp;
  fetch get_fnp into objversion_, rowid_;
  close get_fnp;
  text_ := '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Trebuchet MS;}}{\colortbl ;\red55\green40\blue140;\red0\green0\blue0;}
\viewkind4\uc1\pard\cf1\f0\fs20 Text is not black' || new_line_ || '\viewkind4\uc1\pard\cf0\f0\fs20 Second line in black
}';
  ifsapp.fnd_note_page_api.write_text__(objversion_, rowid_, to_clob(text_));
  commit;
end;

Notice that the note_id is hard coded here (as retrieved via the sql statement).

I leave it to your creativity to use the above in your own api’s.

On the formating issue there are a lot of options. In the above example the first line is not blank. This is due to \colortbl ;\red55\green40\blue140 and before Text is not black you see cf1\

I have no clue, but think that cf stands for colour format. cf1 refers to the first colour formating. cf0 is black.

If you find other formating options and include those in your coding, could you share them here?