Skip to main content
Solved

How to retrieve content of the NOTES via SQL query

  • October 27, 2021
  • 4 replies
  • 821 views

Forum|alt.badge.img+15

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

Hi @Manoj Balasooriya 

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.

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

4 replies

Randini Jayasundara
Superhero (Employee)
Forum|alt.badge.img+12

Hi @Manoj Balasooriya 

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.


Forum|alt.badge.img+2
  • Do Gooder (Customer)
  • 4 replies
  • October 28, 2021

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.


Randini Jayasundara
Superhero (Employee)
Forum|alt.badge.img+12

Hi @CYoung 

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’.


Forum|alt.badge.img+15

Hi @CYoung, were you able to get it done as Randini suggested?


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings