Solved

Creating a sticky note using PLSQL

  • 20 February 2020
  • 5 replies
  • 956 views

Userlevel 7
Badge +21

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?

icon

Best answer by eqbstal 2 May 2020, 19:28

View original

5 replies

Userlevel 7
Badge +21

Be careful with this, as at this moment Aurena (up to update 8 ) doesn't support sticky notes. So it might already be called deprecated. IFS10 EE version still supports this functionality.

Userlevel 7

Be careful with this, as at this moment Aurena (up to update 8 ) doesn't support sticky notes. So it might already be called deprecated. IFS10 EE version still supports this functionality.

Don’t think sticky notes as such will make it into Aurena, but something similar possibly. Do you have some scenarios where you (and anyone else!) use sticky notes (automatically created or not) that you could share?

Userlevel 7
Badge +21

Customer of ours has nearly 20.000 sticky notes prepared in roughly 10 years of using IFS.

Majority of them is placed on one of the following LU's:

CustomerOrder
PurchaseOrder
ShopOrd

So most is transaction related and is used to communicate from one to many employees.

Something like a journal but then for notes would be a solution. Of course this means that the user doesn't see the remark immediately when opening a transaction.

Another user has something like this on work orders where an automated sticky note is in place if it concerns a special equipment object is connected (hazardous).

Steve

 

Userlevel 7

Customer of ours has nearly 20.000 sticky notes prepared in roughly 10 years of using IFS.

Majority of them is placed on one of the following LU's:

CustomerOrder
PurchaseOrder
ShopOrd

So most is transaction related and is used to communicate from one to many employees.

Something like a journal but then for notes would be a solution. Of course this means that the user doesn't see the remark immediately when opening a transaction.

Another user has something like this on work orders where an automated sticky note is in place if it concerns a special equipment object is connected (hazardous).

Steve

 

Thanks, that's similar to what I've seen as well. I'd like Aurena to get to where we have a good, generic journal/note facility but also a possibility to configure toasts for things, like in your example with the hazardous equipment. 

Userlevel 4
Badge +9

We have a similar situtaion as Steve described. Our users love the sticky notes. In 5 years using IFS 8, they created over 120000 sticky notes.

Majority of them is placed on one of the following LU's:

Manual Supplier Invoice
CustomerOrder
PurchaseOrder
ShopOrd
Order Quotation
Case

I think most important is the immediate visibility of the sticky note, which is the basic idea behind it.

Reply