Can't have same invoiceID for invoices created in different years

[Andrei Stan]

Hello everyone,

I want to import some of my old invoices into ofbiz, and some of those
invoices have

the same id, but were created in different time periods, different years.

So, for example, i have *EU-1 *created in 2019**and *EU-1 *created in 2020.

When importing the xml file with data for company, invoices etc., only
one of those two duplicates is imported, without erros from ofbiz's side.

Have anybody tried this in the Accounting Preferences by set the *Old
Invoice Sequence Enum ID *to *Restart on Fiscal Year (no gaps, per org,
reset to one each year) *??

Thank you,

A.

[Michael Brohl-3]

Hi Andrei,

if you load data through XML files, there is no application logic in
play. It's a plain import to the database.

So if you load invoices with the same invoice id (which is the primary
key for the invoice table), the second dataset will overwrite the first.

The mentioned setting will not help with the problem during import.
Because of the primary key, you cannot have the same invoice id twice so
*I guess* the setting will generate an invoice id with the year and id
(like 2021-1 or similar). But I'm not sure though...

Regards,

Michael Brohl

ecomify GmbH - www.ecomify.de


Am 20.02.21 um 09:48 schrieb Andrei Stan:

> Hello everyone,
>
> I want to import some of my old invoices into ofbiz, and some of those
> invoices have
>
> the same id, but were created in different time periods, different years.
>
> So, for example, i have *EU-1 *created in 2019**and *EU-1 *created in
> 2020.
>
> When importing the xml file with data for company, invoices etc., only
> one of those two duplicates is imported, without erros from ofbiz's side.
>
> Have anybody tried this in the Accounting Preferences by set the *Old
> Invoice Sequence Enum ID *to *Restart on Fiscal Year (no gaps, per
> org, reset to one each year) *??
>
> Thank you,
>
> A.
>
>

[Andrei Stan]

Hello Michael,
Thank you for reply.
Can you suggest any best practices when having multiple invoices and at
the end of the (fiscal) year one wishes to reset the invoice counter?
Is there an option in ofbiz to do that?
Thanks,
A.

On 21.02.2021 00:28, Michael Brohl wrote:

> Hi Andrei,
>
> if you load data through XML files, there is no application logic in
> play. It's a plain import to the database.
>
> So if you load invoices with the same invoice id (which is the primary
> key for the invoice table), the second dataset will overwrite the first.
>
> The mentioned setting will not help with the problem during import.
> Because of the primary key, you cannot have the same invoice id twice
> so *I guess* the setting will generate an invoice id with the year and
> id (like 2021-1 or similar). But I'm not sure though...
>
> Regards,
>
> Michael Brohl
>
> ecomify GmbH - www.ecomify.de
>
>
> Am 20.02.21 um 09:48 schrieb Andrei Stan:
>> Hello everyone,
>>
>> I want to import some of my old invoices into ofbiz, and some of
>> those invoices have
>>
>> the same id, but were created in different time periods, different
>> years.
>>
>> So, for example, i have *EU-1 *created in 2019**and *EU-1 *created in
>> 2020.
>>
>> When importing the xml file with data for company, invoices etc.,
>> only one of those two duplicates is imported, without erros from
>> ofbiz's side.
>>
>> Have anybody tried this in the Accounting Preferences by set the *Old
>> Invoice Sequence Enum ID *to *Restart on Fiscal Year (no gaps, per
>> org, reset to one each year) *??
>>
>> Thank you,
>>
>> A.
>>
>>

[adityasharma]

Hi Andrie,

Though accounting is not forte, I think you could use the reference number
field to store the external invoice id and let the invoiceId be the
system's auto-generated value.

HTH
Thanks and regards,
Aditya Sharma

On Sun, Feb 21, 2021 at 4:38 PM Andrei Stan <[hidden email]> wrote:

> Hello Michael,
> Thank you for reply.
> Can you suggest any best practices when having multiple invoices and at
> the end of the (fiscal) year one wishes to reset the invoice counter?
> Is there an option in ofbiz to do that?
> Thanks,
> A.
>
> On 21.02.2021 00:28, Michael Brohl wrote:
> > Hi Andrei,
> >
> > if you load data through XML files, there is no application logic in
> > play. It's a plain import to the database.
> >
> > So if you load invoices with the same invoice id (which is the primary
> > key for the invoice table), the second dataset will overwrite the first.
> >
> > The mentioned setting will not help with the problem during import.
> > Because of the primary key, you cannot have the same invoice id twice
> > so *I guess* the setting will generate an invoice id with the year and
> > id (like 2021-1 or similar). But I'm not sure though...
> >
> > Regards,
> >
> > Michael Brohl
> >
> > ecomify GmbH - www.ecomify.de
> >
> >
> > Am 20.02.21 um 09:48 schrieb Andrei Stan:
> >> Hello everyone,
> >>
> >> I want to import some of my old invoices into ofbiz, and some of
> >> those invoices have
> >>
> >> the same id, but were created in different time periods, different
> >> years.
> >>
> >> So, for example, i have *EU-1 *created in 2019**and *EU-1 *created in
> >> 2020.
> >>
> >> When importing the xml file with data for company, invoices etc.,
> >> only one of those two duplicates is imported, without erros from
> >> ofbiz's side.
> >>
> >> Have anybody tried this in the Accounting Preferences by set the *Old
> >> Invoice Sequence Enum ID *to *Restart on Fiscal Year (no gaps, per
> >> org, reset to one each year) *??
> >>
> >> Thank you,
> >>
> >> A.
> >>
> >>
>

[Andrei Stan]

Hello Aditya,

Thank you for the advice, i think is a good idea.

Regards,

A.


On 22.02.2021 07:45, Aditya Sharma wrote:

> Hi Andrie,
>
> Though accounting is not forte, I think you could use the reference number
> field to store the external invoice id and let the invoiceId be the
> system's auto-generated value.
>
> HTH
> Thanks and regards,
> Aditya Sharma
>
> On Sun, Feb 21, 2021 at 4:38 PM Andrei Stan <[hidden email]> wrote:
>
>> Hello Michael,
>> Thank you for reply.
>> Can you suggest any best practices when having multiple invoices and at
>> the end of the (fiscal) year one wishes to reset the invoice counter?
>> Is there an option in ofbiz to do that?
>> Thanks,
>> A.
>>
>> On 21.02.2021 00:28, Michael Brohl wrote:
>>> Hi Andrei,
>>>
>>> if you load data through XML files, there is no application logic in
>>> play. It's a plain import to the database.
>>>
>>> So if you load invoices with the same invoice id (which is the primary
>>> key for the invoice table), the second dataset will overwrite the first.
>>>
>>> The mentioned setting will not help with the problem during import.
>>> Because of the primary key, you cannot have the same invoice id twice
>>> so *I guess* the setting will generate an invoice id with the year and
>>> id (like 2021-1 or similar). But I'm not sure though...
>>>
>>> Regards,
>>>
>>> Michael Brohl
>>>
>>> ecomify GmbH - www.ecomify.de
>>>
>>>
>>> Am 20.02.21 um 09:48 schrieb Andrei Stan:
>>>> Hello everyone,
>>>>
>>>> I want to import some of my old invoices into ofbiz, and some of
>>>> those invoices have
>>>>
>>>> the same id, but were created in different time periods, different
>>>> years.
>>>>
>>>> So, for example, i have *EU-1 *created in 2019**and *EU-1 *created in
>>>> 2020.
>>>>
>>>> When importing the xml file with data for company, invoices etc.,
>>>> only one of those two duplicates is imported, without erros from
>>>> ofbiz's side.
>>>>
>>>> Have anybody tried this in the Accounting Preferences by set the *Old
>>>> Invoice Sequence Enum ID *to *Restart on Fiscal Year (no gaps, per
>>>> org, reset to one each year) *??
>>>>
>>>> Thank you,
>>>>
>>>> A.
>>>>
>>>>

[Suraj Khurana-2]

Hi Andrie,

You can also use prefix in invoice id's to always make them unique, if you
want to generate the sequence every year then the current year can be made
as InvoiceIdPrefix in PartyAcctgPreference entity.

HTH.
--
Best Regards,
Suraj Khurana
Senior Technical Consultant


On Mon, Feb 22, 2021 at 11:16 AM Aditya Sharma <[hidden email]>
wrote:

> Hi Andrie,
>
> Though accounting is not forte, I think you could use the reference number
> field to store the external invoice id and let the invoiceId be the
> system's auto-generated value.
>
> HTH
> Thanks and regards,
> Aditya Sharma
>
> On Sun, Feb 21, 2021 at 4:38 PM Andrei Stan <[hidden email]>
> wrote:
>
> > Hello Michael,
> > Thank you for reply.
> > Can you suggest any best practices when having multiple invoices and at
> > the end of the (fiscal) year one wishes to reset the invoice counter?
> > Is there an option in ofbiz to do that?
> > Thanks,
> > A.
> >
> > On 21.02.2021 00:28, Michael Brohl wrote:
> > > Hi Andrei,
> > >
> > > if you load data through XML files, there is no application logic in
> > > play. It's a plain import to the database.
> > >
> > > So if you load invoices with the same invoice id (which is the primary
> > > key for the invoice table), the second dataset will overwrite the
> first.
> > >
> > > The mentioned setting will not help with the problem during import.
> > > Because of the primary key, you cannot have the same invoice id twice
> > > so *I guess* the setting will generate an invoice id with the year and
> > > id (like 2021-1 or similar). But I'm not sure though...
> > >
> > > Regards,
> > >
> > > Michael Brohl
> > >
> > > ecomify GmbH - www.ecomify.de
> > >
> > >
> > > Am 20.02.21 um 09:48 schrieb Andrei Stan:
> > >> Hello everyone,
> > >>
> > >> I want to import some of my old invoices into ofbiz, and some of
> > >> those invoices have
> > >>
> > >> the same id, but were created in different time periods, different
> > >> years.
> > >>
> > >> So, for example, i have *EU-1 *created in 2019**and *EU-1 *created in
> > >> 2020.
> > >>
> > >> When importing the xml file with data for company, invoices etc.,
> > >> only one of those two duplicates is imported, without erros from
> > >> ofbiz's side.
> > >>
> > >> Have anybody tried this in the Accounting Preferences by set the *Old
> > >> Invoice Sequence Enum ID *to *Restart on Fiscal Year (no gaps, per
> > >> org, reset to one each year) *??
> > >>
> > >> Thank you,
> > >>
> > >> A.
> > >>
> > >>
> >
>

[Eugen Stan]

Hi,

On 22.02.2021 14:40, Suraj Khurana wrote:
> Hi Andrie,
>
> You can also use prefix in invoice id's to always make them unique, if you
> want to generate the sequence every year then the current year can be made
> as InvoiceIdPrefix in PartyAcctgPreference entity.

Interesting.

Does this mean that there is only one document series per party?

Does OFBIZ support a concept of document series?
A document series is similar to a database sequence + prefix and suffix
and it is used to assign the next id for a document type (invoice,
cotract, order, etc).


Regards,
Eugen
--
Eugen Stan
[phone] / netdava.com

[Jacques Le Roux]

Hi Eugen,

Do you mean a sequence by 3rd parties and not only by (OOTB) "company" ?

BTW did you read: https://cwiki.apache.org/confluence/display/OFBENDUSER/12.1.1+Accounting+Preferences ?

Also AFAIK there is no suffix available, but is that really needed when you have a prefix?

Jacques

Le 22/02/2021 à 23:11, Eugen Stan a écrit :

> Hi,
>
> On 22.02.2021 14:40, Suraj Khurana wrote:
>> Hi Andrie,
>>
>> You can also use prefix in invoice id's to always make them unique, if you
>> want to generate the sequence every year then the current year can be made
>> as InvoiceIdPrefix in PartyAcctgPreference entity.
>
> Interesting.
>
> Does this mean that there is only one document series per party?
>
> Does OFBIZ support a concept of document series?
> A document series is similar to a database sequence + prefix and suffix and it is used to assign the next id for a document type (invoice, cotract,
> order, etc).
>
>
> Regards,
> Eugen

[Eugen Stan]

Hi Jacques,

Thanks for the link.
I've read the documentation and found the entries in ofbiz demo instance.

I managed to change the prefix for the invoices.

I used to be more creative with invoice series.
Now with migration to OFBiz in the works I've channeled my creativity to
other fields... .

I'll talk to my accountant to see how often is to have multiple document
series (invoice, orders, proforma invoice, quotes, etc) per organization.

Until then, I think we are set.

All the best,
Eugen

On 07.03.2021 19:13, Jacques Le Roux wrote:

> Hi Eugen,
>
> Do you mean a sequence by 3rd parties and not only by (OOTB) "company" ?
>
> BTW did you read:
> https://cwiki.apache.org/confluence/display/OFBENDUSER/12.1.1+Accounting+Preferences 
> ?
>
> Also AFAIK there is no suffix available, but is that really needed when
> you have a prefix?
>
> Jacques

--
Eugen Stan
[phone] / netdava.com