How can we retrieve content of the yellow sticky notes (i.e. tool strip, notes icon) via SQL query for any entity that could have yellow sticky notes such as Customer, Customer Order, Shipment, Invoice, Supplier, etc.? Searched PL/SQL for API's and the database for tables/views but could not find any viable candidates.
We’d like to be able to retrieve the sticky note text for reporting and analysis purposes.
Best answer by Janitha Jinarajadasa
Hi
Note field is a CLOB value in the table, so you will not be able to query it directly from the SQL query tool, so it needs to be modified. Even though it modified it will come as a rich text field.
Try the bellow query for your customer info note
select DBMS_LOB.Substr( text, 4000, 1 ) from IFSAPP.FND_NOTE_BOOK t1,IFSAPP.FND_NOTE_PAGE t2
where t1.note_id = t2.note_id
and t1.lu_name ='CustomerInfo'
and t1.key_ref like '%CUSTOMER_ID=94267%'
You can only take 4000 characters here. In order to see the data, copy the output into a notepad and save it with a extension ‘rtf’. [ You can not see it from the SQL query tool]
For other instances like Customer Order, Shipment..
LU_NAME should be replaced by the correct LU name
and KEY_REF should be replaced by the correct keys
Hi
Note field is a CLOB value in the table, so you will not be able to query it directly from the SQL query tool, so it needs to be modified. Even though it modified it will come as a rich text field.
Try the bellow query for your customer info note
select DBMS_LOB.Substr( text, 4000, 1 ) from IFSAPP.FND_NOTE_BOOK t1,IFSAPP.FND_NOTE_PAGE t2
where t1.note_id = t2.note_id
and t1.lu_name ='CustomerInfo'
and t1.key_ref like '%CUSTOMER_ID=94267%'
You can only take 4000 characters here. In order to see the data, copy the output into a notepad and save it with a extension ‘rtf’. [ You can not see it from the SQL query tool]
For other instances like Customer Order, Shipment..
LU_NAME should be replaced by the correct LU name
and KEY_REF should be replaced by the correct keys
Thanks I appreciate the quick feedback. I tried the above for LU’s CustomerOrder and CustomerInfo and it did work for both of them… if only we could get past the copy/paste to notepad, save as .rtf then open document to get straight to the text we care about to allow analysis of the relevant text content.
select DBMS_LOB.Substr( text, 4000, 1 ) from &AO.FND_NOTE_BOOK t1,&AO.FND_NOTE_PAGE t2
where t1.note_id = t2.note_id
and t1.lu_name ='CustomerOrder'
and t1.key_ref like '%ORDER_NO=B126469%'
Copy/paste to notepad, save as .rtf then open this is what you see when open the .rtf:
Hi
Since notes saves as a rich text, editors should support it to show it correctly. That’s why in the 1st image it appears differently and when open as a .rtf it shows the correct values.
It should be possible to convert the rich text values in the varchar2 fields, but it will need more coding.
Something like bellow:
https://stackoverflow.com/questions/6265233/oracle-plsql-rtf-varchar2-field-to-plain-text-format
Hope this helps.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
