Feature - Prevent Duplicate Invoice Numbers with Invoice Cross Reference File

Release Date - 3/25/19

Version: 8.5 and Up

This feature uses a new table, the Invoice Cross Reference table, to determine if an invoice has already been used by another unposted or posted order.  The Invoice Cross Reference is updated at the time of invoice printing as soon as the order record is rewritten. Keep in mind the first rewrite is to update the status from "S" (Selected for Billing) to "N" (Printed but Not OK). Once the user answers "Yes" to the printed OK prompt, the record is rewritten a second time. This rewrite will change the status from "N" (Printed but Not OK) to "X" (Printed OK/Invoiced). If the user answers "No," then the second rewrite will change the status from "N" (Printed but Not OK) to "S" (Selected for Billing). If the user chooses to void the invoice later on through the "Void Invoice" function, the status will change from "X" (Printed OK/Invoiced) to "S" (Selected) as well. Once the invoice is posted, the status field is updated to "Z" (Posted).

So the "Status" field could have a value of "S," "N," "X" and "Z." We keep the record for "S" because an actual invoice hard copy was created. If the user questions why there's a hard copy invoice, but there's no record in Elliott, it could be because they answered "N" to the printed OK prompt, or it could be because they voided the invoice. Having a table that shows a record as soon as the invoice copy is printed will give the user the ability to perform better auditing at a later time.

When printing invoices, the application will retrieve the next invoice number. Once retrieved, the application will check if the invoice number is on file in the new Invoice Cross Reference with a status of “N” or “X.” If the invoice number is found with a status of “N” or “X,” the application will get the next invoice number and try again. If the invoice number is not found, the application will check if the invoice number is on file in the Invoice Header file. If it is, the application will get the next invoice number and try again.

Because an order can be invoiced multiple times, the following fields are stored in the Invoice Cross Reference file for better auditing:

  • Date Picked
  • Date Selected for Billing
  • Invoice Date
  • Ship Via
  • Invoice Amount
  • Freight Amount
  • Misc. Charge Amount
  • Sales Tax Amount 1
  • Sales Tax Amount 2
  • Sales Tax Amount 3
  • Payment Amount
  • User Name
  • Source

 

The Source field is the application that generated the record. It can contain one of the following values:

  • CP0100 – Printed immediately from Order Entry
  • CP0300 – Printed immediately from Order Billing
  • CPSLSDSK – Printed immediately from Sales Desk
  • CPSBMENU – Printed immediately from Fill Back Orders
  • CPSHPVER – Printed immediately from Shipping Verification
  • CP03S2 – Printed from Print Invoices (standard invoices)
  • XCP03S2 – Printed from Print Invoices (DYO invoices)
  • CPONEINV – Printed from Print One Invoice

When voiding invoices or posting invoices to Accounts Receivable, the user name and source fields are retained with the information from the original user and source that generated the invoice.

Below is an example of three records generated for one order number.

Activity

Status

Update User?

Update Source?

a.      First print (creates new record)

"N"

Y

Y

b.      Answer Y to Print OK (updates record)

"X"

N

N

c.      Void (updates record)

"S"

N

N

d.      Reprint After Void (creates new record with a new sequence number)

"N"

Y

Y

e.      Answer N To Print OK (updates record)

"S"

N

N

f.        Reprint again (creates new record with a new sequence number)

“N”

Y

Y

g.      Answer Y to Print OK (updates record)

"X"

N

N

h.      Post Invoices (updates record)

"Z"

N

N

a.      Printing an invoice (not a duplicate) for an order will always generate a new record. The user and source are recorded.

b.      Answering Y to the “Print Ok?” prompt will change the status from “N” to “X.” The user and source are not updated.

c.      Voiding the invoice changes the status from “X” to “S.” The user and source are not updated.

d.      Printing the invoice will generate a new record with a status of “N.” The user and source are recorded.

e.      Answering “N” to the “Print OK?” prompt will change the status from “N” to “S.” The user and source are not updated.

f.        Printing the invoice will generate a new record with a status of “N.” The user and source are recorded.

g.      Answering “Y” to the “Print OK?” prompt will change the status from “N” to “X.” The user and source are not updated.

h.      Posting the invoice will change the status from “X” to “Z.” The user and source are not updated.

A new utility program is available to generate the data for Invoice Cross Reference file. This application is available from Global Setup-> Utilities-> COP Utilities-> Utilities-> Generate Invoice Xref File.


This utility program will generate records for any orders/invoices that do not exist in the invoice cross reference file.  A report is provided with a list of records that are created. If an order number/invoice number combination exists in the invoice cross reference file, the record is not updated.

 

With this feature, you might ask if there is still value with creating invoice log CSV file through Global Setup -> Cop-Func -> Invoice Printing -> 18. Append Printed Invoices to CSV Log File?

The answer is, that depends:

  • First of all, the invoice cross reference table is only available after 8.5.  If you are not on 8.5 or after, and want invoice printing auditing capability, you will need to use the invoice CSV log file option.
  • The CSV Log File only contains the printed invoices that you answered "Y" to "Are Invoices Printed OK?" question.  On the other hand, the Invoice Cross Reference database contains all printed invoices, including those you answered "N" to above question. So the Invoice Cross Reference table is more comprehensive than the CSV log file.
  • On the other hand, if you try to get data from the Invoice Cross Reference table, you can print it but there's currently no CSV output option at this moment and the table does not have all invoice's data (you can join with other tables to retrieve this information, though.)  If your purpose is to use a CSV file, then the CSV Log File is directly created and easily available to the user. 
  • Also, if you want to use the invoice CSV log file to drive some sort of automation processing, then you will need to create the CSV file as well.


New Programs: CPINVXRF.SL, CPINVXRF.FD, CPINVXRF.WS, CPINVXRF.DS, CPINVXRF.IO, CPINVXRF.PL, CPXRFSCN, CPXRFGEN

Programs Modified: CPINVPRT.PL, CPMASVNS.PL, CP0305, CP0400, CPINVPRH, CPINVPRP, CPINVPRT, CPMASVPT, CPMASVPV, CPSHPVER, NSCTLUT4, XCP0106

CLS

 

 

 

 


Elliott Version 8.5

  1. V8.5 Alpha Document Number Support
  2. Crystal Report Conversion for V8.5
  3. The Run Local or Hybrid Feature in Elliott 8.5
  4. How to Use EL850DB.EXE Utility to Create PSQL Databases
  5. Showing Document Date in Links After Elliott 8.5
  6. The Lessons We Learned from Upgrading Web Service 8.2 to 8.5 in eStore
  7. Feature - ASCII File Filter Condition in Mass Email and Export Processor
  8. Feature - List Users Acively Logged into Elliott and Optionally Send Email
  9. Feature - Allow Print One Invoice to Print Duplicate Invoices for Order Not Posted
  10. Feature - Print Range of Duplicate Unposted Orders
  11. Feature - Prevent Duplicate Invoice Numbers with Invoice Cross Reference File
  12. Feature - Last Year Net Income Layout Support
  13. Receive Message "Not Found" When Trying to Set Up Run Local
  14. Receive 'Invalid Column Name' Message After Starting to Use Alphabetic Document Numbers
  15. Feature - Invoice Log Report
  16. Feature - Add Sales or Usage to Where-Used inquiry
  17. Feature - Case Size Support for Quantity Per Box
  18. Feature - Exclude Obsolete Customers from Customer Generic Search
  19. Feature - Refresh VICS Bill of Lading
  20. Feature - Support Item Case Size in New Item CSV Import and Change Existing Item Import
  21. Feature - Copy Notes, Attributes, and Links When Copying Item Through the New Item CSV Import
  22. Feature - Enhanced Cash Receipts Processing Apply-To Range
  23. Feature - Add Links Import to System Files Setup
  24. Feature - Shipment Email Acknowledgement for Bill-To Customers
  25. Feature - Restrict Sending Tracking Number to Bill-To Customer in Shipment Email Acknowledgement
  26. Feature - Allow Source Range on Credit Card Log Report
  27. Feature - Indented Bill of Material CSV
  28. Feature - Stock Status Reports Add Include with Alloc or On Order Quantity Option
  29. Feature - Release Manually Held Orders
  30. Feature - Create Shipment Records When Complete Bill Of Lading
  31. Feature - Print Warning Msg for Cancelled PO Line Item
  32. Feature - Export 25-Character PO Number in Expanded Aging Report CSV File
  33. Feature - Show Pending Cash Receipts in Payment History Inquiry
  34. Feature - Show Customer Note Fields in Payment History Inquiry
  35. Receive "Error Getting List of Override Reports" When Printing Report
  36. Feature - Fields Added to User-Defined CSV to Support EDI 846
  37. Feature - Credit Memo Warning When Fully Paid
  38. Feature - Create Shipment Verification Box Records Through Billing CSV Interface
  39. Feature - Show Box ID on Order Inquiry Shipment Data Screen
  40. Feature - Print Error Message If Inventory Transaction Distributions Are Missing
  41. Feature - Customer CSV Import
  42. Feature - Alleviate Error 25 When Two Users Import Sales Orders Simultaneously
  43. Feature - Use Note Type for Special Instructions on VICS BOL
  44. Feature - VICS BOL to Enforce Shipment Verification Before Printing of BOL
  45. Feature - Order/Invoice Inquiry Box Serial Number Drill Down
  46. Feature - Delete Order Tracking Note When Void Completed VICS BOL
  47. Feature - Picking Ticket For Customer 2 Event
  48. Feature - Set All Options to Allow or Disable in User Global Security
  49. Feature - Stock Analysis Creation by Customer Range
  50. Feature - Print Work Order Planned Hours on Plus Work Order Traveler
  51. How to Ensure Local Elliott Folder Is Up to Date
  52. Feature - Global Setup Flag to Update Tracking Note from Box Data Window
  53. Feature - Packing List by Box One Box Per Page
  54. Feature - Move EDI Profile Maintenance Maximum # of Item Per Label
  55. How to Update Elliott 8.5 License Key Code
  56. Installing Elliott 8.5 and Received "PSQL v11, v12 or V13 Is Required" Message
  57. Feature - Warehouse Receiving Missing Item Attribute Event
  58. Feature - Added a Drill Down to Order by Item Inquiry in Newly Received Items Inquiry
  59. Feature - Customer Type Display in Pick Order Processing
  60. Elliott V8.5 Configuration Utility (EL850CF.EXE)
  61. Feature - Add Customer Type to Pick Ticket Selection
  62. Feature - Void Checks after Posting Warning for Multiple Checks
  63. Error 1315 Unable to Write to the Specified Folder When Installing Elliott 8.5

Feedback and Knowledge Base