Solved

How to retrieve content of the NOTES via SQL query

  • 27 October 2021
  • 4 replies
  • 598 views
M
Rank
Userlevel 6
Badge +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.

 

 



 

icon

Best answer by Randini Jayasundara 27 October 2021, 13:21

View original
This topic has been closed for comments

4 replies

Randini Jayasundara
Rank
Userlevel 6
Badge +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.

Best Regards, Randini
C
Rank
Userlevel 1
Badge +2

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
Rank
Userlevel 6
Badge +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’.

Best Regards, Randini
M
Rank
Userlevel 6
Badge +15

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

Terms & ConditionsCookie settings