#915 AR invoice numbers are not unique and don't need to be.

1.4
closed-fixed
Chris Travers
1
2014-01-21
2013-12-04
turtle
No

The AR table has an id which is unique there is no need to force invnumber to be unique or create another key.
Users importing data may not be allowed legally to renumber old existing invoices.
Nothing in the code seems to require unique invoice numbers.
This could be an option in system defaults.
To fix the problem before an import
ALTER TABLE ONLY ar drop constraint ar_invnumber_key ;
works.

Related

Bugs: #915

Discussion

  • turtle
    turtle
    2013-12-04

    Here is a patch to remove the unique invnumber

     
    Last edit: turtle 2013-12-04
  • My opinion, this is nothing to do with the code, but forced by the international rules. It is not allowed to have two sales invoices or any other invoices with identical numbers.

    I cannot imagine that situation, when two invoices could be the same invoice number.
    Again, this is not a code consideration, but simply rules by the local/global rules.
    Without guarantee this unique invoice number, this software is not allowed to use anywhere.
    You have to lead the user in a minimum level, otherwise all the books will be a complete mess and the company will fail in the first inspection by the government.

     
  • Chris Travers
    Chris Travers
    2013-12-04

    On Dec 4, 2013 7:50 AM, "turtle" turt1e@users.sf.net wrote:


    [bugs:#915] AR invoice numbers are not unique and don't need to be.

    Status: open
    Labels: unique constraint
    Created: Wed Dec 04, 2013 07:50 AM UTC by turtle
    Last Updated: Wed Dec 04, 2013 07:50 AM UTC
    Owner: nobody

    The AR table has an id which is unique there is no need to force
    invnumber to be unique or create another key.
    Users importing data may not be allowed legally to renumber old existing
    invoices.
    Nothing in the code seems to require unique invoice numbers.
    This could be an option in system defaults.
    To fix the problem before an import
    ALTER TABLE ONLY ar drop constraint ar_invnumber_key ;
    works.

    I think it is a good idea to discuss the reasons behind the constraint and
    what tradeoffs are involved as we look at the best ways going forward.

    You are right that nothing in our code requires unique invoice numbers.
    There are however many things outside our code that does. Istvan already
    mentioned legal requirements in some countries. However that only
    scratches the surface. Many other accounting systems assume that all
    vendor invoices use unique numbers per vendor. So in any sort of B2B
    application this is an important constraint, and the default of having it
    is sane.

    The question is what we do about those cases where this gets in the way. I
    see a few options.

    1 We document this and suggest dropping or modifying the constraint as
    needed.

    2 We could have an addon that removes or modifies the constraint.

    What do you think?


    Sent from sourceforge.net because you indicated interest in
    https://sourceforge.net/p/ledger-smb/bugs/915/

    To unsubscribe from further messages, please visit
    https://sourceforge.net/auth/subscriptions/

     

    Related

    Bugs: #915

  • Easy switch on/off this constraint could be fine. Addon or a tuning option in ledgersmb.conf.

     
  • turtle
    turtle
    2013-12-04

    Sure thats fine, i could do it in system defaults or I could do a warning on post as well.. I am not trying to be a pain and make more work for you guys. It was just one more thing that broke data import from a 1.2 to 1.4 system. When we upgrade this database its got years and years of old data almost a decade. I cant go in and change old books even if there is old duplicate invoice numbers. My job actually is to make sure no data changes on an upgrade :)
    I also just wanted to make sure we are using the id's as our unique number internally.

    Dupe invoice numbers can happen in temporary situations, in restaurants it was customers splitting a 'ticket' or could be something to do with coupons who knows? Somebody might want to add "id" as a suffix to the invoice numbers that are duplicates. An authority or accountant might just go right to the "id" as the unique invoice identifier in any case since it cant be changed easily.
    Cheers
    Turtle

     
  • Chris Travers
    Chris Travers
    2014-01-17

    For now (1.4) I am going to suggest that this gets handled by the addon process and what we really need is for an easy way for addons to specify fixes to be loaded before data import. Additionally I don't mind documenting how to drop the constraint.

     
    • turtle
      turtle
      2014-01-17

      How to drop the constraint sounds good for closing the bug.
      I have experimented with adding a hyphen and the id to all the duplicates
      in 1.2 and that seems to please the accountant and everyone.

      For now (1.4) I am going to suggest that this gets handled by the addon
      process and what we really need is for an easy way for addons to specify
      fixes to be loaded before data import. Additionally I don't mind
      documenting how to drop the constraint.


      [bugs:#915] AR invoice numbers are not unique and don't need to be.

      Status: open
      Labels: unique constraint
      Created: Wed Dec 04, 2013 07:50 AM UTC by turtle
      Last Updated: Wed Dec 04, 2013 05:58 PM UTC
      Owner: nobody

      The AR table has an id which is unique there is no need to force invnumber
      to be unique or create another key.
      Users importing data may not be allowed legally to renumber old existing
      invoices.
      Nothing in the code seems to require unique invoice numbers.
      This could be an option in system defaults.
      To fix the problem before an import
      ALTER TABLE ONLY ar drop constraint ar_invnumber_key ;
      works.


      Sent from sourceforge.net because you indicated interest in
      https://sourceforge.net/p/ledger-smb/bugs/915/

      To unsubscribe from further messages, please visit
      https://sourceforge.net/auth/subscriptions/

       

      Related

      Bugs: #915

  • Chris Travers
    Chris Travers
    2014-01-20

    • assigned_to: Chris Travers
     
  • Chris Travers
    Chris Travers
    2014-01-20

    I will be adding a documented hook for schema changes on db load/upgrade.

     
  • Chris Travers
    Chris Travers
    2014-01-21

    • status: open --> closed-fixed
     
  • Chris Travers
    Chris Travers
    2014-01-21

    as of svn 9568, you can add scripts to modify these to the sql/on_load directory and have them run after the base schema is loaded but before the modules. This should work for upgrades as well.