How to Retrieve Tracking Number for an Order from Notes

Release Date: 12/13/2017

Q - Can you let me know how the tracking number is stored in Elliott so I can use query to retrieve the information?

A - Regarding how Elliott stores tracking numbers, please refer to the following Knowledge Base article:


You can retrieve the tracking number by using the SQL SELECT statement as follows:

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

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


Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master

Feedback and Knowledge Base