For many years one of our companies in Italy has been changing their starting invoice number at the beginning of every year (Government Regulation). They simply go to Company Configuration and update the starting AR Invoice Number. We went to do it this year and it seems that Epicor has changed the schema to make the max this field can hold be 6 digits (999,999) and our current Invoice Number is 7 digits (1,700,713). We get the error “Starting Invoice is too large for its format of ‘>>>>>9’” if we try to change it to 1800001. It doesn’t work if we just set it to 180001 because that number is lower than our current so it doesn’t use it.
I have tried doing a updateable BAQ on the ERP.ARSyst table and it also will not let us go higher than 6 digits. What I did do on our test system was modify the ERP.ARSyst table via a SQL statement run in the SSMS. This seems to work on my test system, but I am worried that there will be unforeseen consequences of updating this table manually and not using the business objects.
Has anybody had to do this before or any insight into how safe it would be to just manually update it? I am in a pinch since thy need to start invoicing.
Ouch, without a lot of testing there is no telling where else this might bite you.
—Before going custom I might ask Epicor for a fix…bug
Now I wonder if Epicor really changed the schema between versions…
Or only added business logic based on the description in the data dictionary…now in E10 they check the proposed field length before letting you commit?
In E9 I see I can customize the entry form, increase the masks for that field form and enter starting invoice numbers like 1700713… did seem to follows thru to invoice entry OK.
You’d better check the other tables where the AR Invoice number is
referenced… TranGLC, Payment Detail, etc etc…
I guess run a bunch of end to end testing on this. Personally, I’d fear
future issues arising from changing default DB schema, but you might be
ok?..?.. just risky IMHO
Andrew Fagan
Sonas Group, LLC
Sonas Intelligence LLC
617-335-0505
The thing is this is all of our current invoices have 7 digits and are working fine and the InvcHead.InvoiceNum field is ">>>>>>>>9’ which is nine digits. I get that there are tons of other tables that store the invoice number but those are seemingly working now with a 7 digit number in the ARSyst table and 7 digit numbers in the InvcHead tables. I would literally just use SQL to change the ARSyst table from 1700713 to 1800001, it really shouldn’t change our operating. From what the documentation says this field is read when creating an invoice and the system uses the ARSyst.StartInvoiceNumber or Max(InvcHead.InvoiceNum)+1 whichever is higher.
The other option would be to somehow set the number of an invoice manually instead of having it automatically generate a number. If I can create an invoice with the number 1800001 then the invoice after it would be 1800002.
Well Epicor got back to me with a possible fix. Which is to update the format of ARSyst.StartInvoiceNumber via the extended property maintenance. Going to test that out and see if it works.
Just an FYI…
The format change seems to work on a test system.
Note that I also ran a db regen
and increased the size of the mask on the field, company cofig entry form.
@jhawt - We just ran into the same issue when testing 10.2 - We want to set our starting invoice number to 40,000,000 so Avalara has a 20 year gap on test invoice numbers and no chance of an overlap (until I’m long retired!).
Did you enter >>>>>>>9 into the empty Format box? The default is greyed out (which already has >>>>>>>9, so it should work!).
What’s odd, is that in 10.0 we were able to customize the CompanyConfig form with 8 digits and set the starting number. It didn’t display correctly, but the pop-up (circle with an i in it) showed the correct 40,000,000. 10.2 just errors out.
We bump our starting Order number every Jan 1, to use the year (kind of) as a prefix
First order of 2017 is 2170001, first of 2018 is 2180001, and so on.
The Starting Order number is limited to 6 chars. So I set it to 0, then use DMT to create record 2180000. The next one a user makes will be 2180001.
Jason - Thanks! That worked great. Company Config accepted the new num, and it was there for our next invoice. I entered Case CS0001135010 w/ Epicor as well, so it can be fixed by default.
FWIW - I just updated the Extended Property for the format of the StartingOrderNum, and did not cycle the App pool. - And it worked.
From the help:
Extended Property Maintenance
Use Extended Property Maintenance to define additional, or extended, properties for selected fields (columns) within a dataView (table).
These extended properties become active the next time a user launches a program that uses fields from the selected table. These properties can also be leveraged when you customize a program which uses the same table.
