How can we retrieve content via SQL query of the NOTES (not document text) for any entity that could have 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 viable candidates except for one API (basi1app.CUSTOMER_ORDER_API, that has several “get” options i.e. .Get_note_text(CO.ORDER_NO) or Get_Note_ID (CO.Order_no) . Was unable to find a similar API for CUSTOMER NOTES, or other entities.
Best answer by Randini Jayasundara
Most of the time you can use a SQL query as follows if a get method is not available.
e.g.
SELECT note_text FROM CUSTOMER_ORDER
SELECT note_text FROM CUSTOMER_ORDER_LINE
SELECT note_text FROM SHIPMENT
SELECT note_text FROM CUSTOMER_INFO_CONTACT
You can get the view name to query using the System Info of the screen where Notes check box or field located.
For invoice notes it seems bit different. You have to use the method INVOICE_NOTE_API.Get_Note_Id(company, invoice_id) to retrieve the Note ID and then query FIN_NOTE_TEXT view using that to get the note text.
Hope this helps.
+12Hi
Most of the time you can use a SQL query as follows if a get method is not available.
e.g.
SELECT note_text FROM CUSTOMER_ORDER
SELECT note_text FROM CUSTOMER_ORDER_LINE
SELECT note_text FROM SHIPMENT
SELECT note_text FROM CUSTOMER_INFO_CONTACT
You can get the view name to query using the System Info of the screen where Notes check box or field located.
For invoice notes it seems bit different. You have to use the method INVOICE_NOTE_API.Get_Note_Id(company, invoice_id) to retrieve the Note ID and then query FIN_NOTE_TEXT view using that to get the note text.
Hope this helps.
thanks for the feedback it worked great to retrieve Customer_Order.note_text and CUST_ORD_CUSTOMER_ENT.note_text directly. I did also locate an API for Customer Order Notes that works just as well (&AO.CUSTOMER_ORDER_API.Get_note_text(CO.ORDER_NO) "CO_NoteText"), but could not find a similar API for CUSTOMER notes.
+12Hi
I will be able to help you if you can attach a screen shot of the Customer notes you are referring. Meantime I can see Notes in Contact and Invoice tabs.
[Contact] - SELECT note_text FROM CUSTOMER_INFO_CONTACT
[Invoice] - Here it’s different. Use below query to fetch Note ID
SELECT note_id FROM IDENTITY_NOTE_TAB
WHERE company = <company>
AND identity = <customer_id>
AND party_type = 'CUSTOMER'
And then get note text using that note ID as follows.
SELECT text FROM FIN_NOTE_TEXT_TAB
WHERE note_id = <note_id>
If you are looking for Supplier Notes then party_type should be given as ‘SUPPLIER’.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
