Sales Order with same Document number, same Business Partner and same document type in Different Year
Description
Environment
Activity
Carlos Ruiz October 30, 2023 at 10:43 AM
Hi - I understand your need but I think is OK that the default on iDempiere is to have unique numbers in Sales and Purchase Orders.
As you mention, it is possible specifically for your needs to remove the uniqueness of the index, but from what I see, your case is an exception, and the most common rule is to have unique numbers in orders.
In the case of sequences restarting every year, the common case is to use a sequence with the year as prefix.
About this:
because may be arrise also in Purchase order where you haven’t the control of sequence that business partner Vendor using
It’s also common that the purchase order document number is an internal number, and the external number (BP number) is written in another field, but even if you can write the BP document number in the DocumentNo field, the most common case is to have unique numbers also on BPs.
So, my suggestion about this: leave the default as it is, and if your specific need is to allow duplicates, then is OK for you to remove the uniqueness of the index in your implementation.
Regards,
Carlos Ruiz
Antonio Patarozzi October 30, 2023 at 7:45 AMEdited
Hi , the progressive document number format is the commonly format used in Italy. Only the number, because must be a progressive number in a year. I don’t have to be forced to use a specific format of sequence number to avoid the error mentioned in ticket. It’s wrong, the index must be set to unique only if you want have this control but in general its wrong, because may be arrise also in Purchase order where you haven’t the control of sequence that business partner Vendor using. And if you purchase from An Italian Business Partner you can have this problem; 2 document with the same Document Number in 2 different year, because the document number format is a progressive number in a year, restarted in the next year. It’s wrong the unique set to index table.
zuhri utama October 29, 2023 at 2:57 AM
hi , what is your document no format for sales order? commonly, the sequence is consist of month and year if restart every month or year only if restart every year.
Sales Order with same Document Number, same Business Partner and same document type in Different Year cannot be insert because there is a Table Index unique “c_order_documentno” with column DocumentNo, C_DocType_ID, C_BPartner_ID.
The Error “16:42:09.475===========> DB.saveError: SaveErrorNotUnique - ERRORE: un valore chiave duplicato viola il vincolo univoco "c_order_documentno"
Dettaglio: La chiave (documentno, c_doctype_id, c_bpartner_id)=(255, 1000032, 1000485) esiste già . [423]”
In Sales Order, in case when the sequence of document are reset every year, may be occur that the same Document Number can be assigned for the same Business Partner of precedent year
The problem can occur also in purchase Order.
The solution may be only toggle the check box unique of Index Table “c_order_documentno”.