Substitute the #ORD_NO# with Elliott's order number.  We assume #ORD_NO# is in numeric or integer format. Keep in mind we store order numbers with padded leading zeroes in NOTE_FILE_REF_NO to 6 digits. That is to say, order# 1234 is stored in 001234 in NOTE_FILE_REF_NO, which is a string field. So the RIGHT(CONCAT('000000',CONVERT(#ORD_NO#,SQL_CHAR)),6) will pad leading zeroes to #ORD_NO# and make it a string value to match the data type of NOTE_FILE_REF_NO.

Also, keep in mind that some users' nightly deferred processing routine will post and purge invoiced orders and move them to invoice history. So the above procedure may only work from the time that the order is manifest (during the day) to the time when the order is posted and purged (somewhere around midnight). After mid-night, the order could be moved to two places: (1) Invoice History; (2) Order History. Invoice History is referenced by invoice number.

If you have the order number, then the Order History is a better candidate. So if the query above does not work, you will use the following query:

SELECT * FROM NOTES
WHERE NOTE_FILE_NAME = 'CPHODHDR' AND
NOTE_FILE_REF_NO = RIGHT(CONCAT('000000',CONVERT(#ORD_NO#,SQL_CHAR)),6) AND
NOTE_CREATE_BY_USER = 'STARSHIP';

If you have the invoice number, then the invoice history is a better choice. So you can use the following query:

SELECT * FROM NOTES
WHERE NOTE_FILE_NAME = 'CPINVHDR' AND
NOTE_FILE_REF_NO = RIGHT(CONCAT('000000',CONVERT(#INV_NO#,SQL_CHAR)),6) AND
NOTE_CREATE_BY_USER = 'STARSHIP';

The following Knowledge Base article may relate to you if you wish to join the NOTES and CPINVHDR table to retrieve both tracking and invoice header info:

    http://support.elliott.com/knowledgebase/articles/1828897-how-to-improve-query-performance-when-retrieving-d

EMK