Re: Invoice sequence ids

[Jacques Le Roux <ja...@les7arts.com>]

Hi BJ,

I don't think it's a security issue, but opening a Jira for this seems appropriate to me. Please give as much as details, links and 
requirements. Maybe we could even open a wiki page for this like the VAT page http://docs.ofbiz.org/display/OFBIZ/VAT

Jacques

From: "BJ Freeman" <bj...@free-man.net>
> in bumping this up to refresh the conversation, it is impartitive in
> HIPPA that there is an audit to make sure there are no gap so someone
> can not change or substitute records.
> I have looked at the SequenceUtil.java.
> It is still what I consider out of my league
> any thoughts or energy would be greatly appreciated.
> you could consider it a security issue.
> :D
>
>
> David E Jones sent the following on 7/18/2008 12:44 AM:
>>
>> On Jul 11, 2008, at 8:07 AM, Jacopo Cappellato wrote:
>>
>>> Hi all,
>>>
>>> Based on the feedback I got from various Italian customers/contacts it
>>> seems that enhancing the way invoice sequence ids are managed is one
>>> of the most desired features.
>>> The main requirements can be summarized with:
>>> * strictly sequential invoice ids (no gaps): this is required (or just
>>> common practice) in some countries, including Italy
>>> * the ability to reset the invoice id (i.e. "starting again from 1")
>>> when a new financial period starts; this is typically done at the
>>> beginning of the year in Italy
>>> * the ability to define more than one sequences; for example: one for
>>> invoices for UE customers, one for invoices for extra UE customers etc...
>>>
>>> I know there is already some support for this stuff in the existing
>>> codebase, but I think there are still issues (some technical and some
>>> functional) and so there is room for enhancements to the existing code.
>>>
>>> In its current implementation the system allows you to maintain the
>>> following sequences:
>>>
>>> A) "Standard (faster, may have gaps, per system)"
>>> B) "Enforced Sequence (no gaps, per organization)"
>>> C) "Restart on Fiscal Year (no gaps, per org, reset to 1 each year)"
>>>
>>> Technical issue (after a review of the "getNextInvoiceId" service):
>>> method A is implemented in the right way, i.e. using the
>>> SequenceValueItem and related service; on the other hand method B and
>>> C don't use the SequenceValueItem entity/service and instead handle
>>> the sequence generation using the getNextInvoiceId service and the
>>> field PartyAcctgPreference.lastInvoiceNumber: this is an issue because
>>> these methods are not thread safe and there is no reason to not use
>>> the SequenceValueItem stuff for them too; for example we could
>>> maintain in the SequenceValueItem entity one sequence for each of the
>>> divisions or methods that we need ("Invoice_Company",
>>> "Invoice_SubDivisionA" etc...) and then just call the
>>> sequenced-id-to-env passing the right sequence name and
>>> stagger-max="1" (to avoid gaps).
>>
>> Unfortunately even with stagger-max set to 1 there is still a likely
>> problem with gaps. Some scenarios:
>>
>> 1. the server is restarted and the remaining numbers in the ID bank
>> would not be used; we could reduce the bank-size to 1 which would force
>> the entity engine to go to the database for every sequence number, but
>> this still isn't perfect...
>>
>> 2. like you mentioned below after the ID is retrieved the transaction
>> for the invoice creation could be rolled back, leading to the invoice ID
>> not being used and therefore a gap
>>
>>> And we could easily implement the "Restart on Fiscal Year" method by
>>> resetting the proper sequence in the SequenceValueItem entity (and
>>> this could be done by the service that closes a Financial time period).
>>>
>>> However also this method will not guarantee a strictly sequential id
>>> series: for example, if an error happens during the invoice
>>> generation, everything is rolled back, but not the id in the
>>> SequenceValueItem, because it is committed in a separate
>>> transaction... hmmmm... any ideas on how to resolve this issue?
>>
>> I'm not sure if there is a good way to solve this problem. In order to
>> guarantee a strict ID sequence with no gaps you would have to create
>> only 1 invoice at a time because of the chance that an invoice creation
>> will fail, and thus leave a gap in the sequence.
>>
>> One possibility is to create one Invoice header record at a time without
>> any of the detail, and if anything fails in that it will rollback
>> immediately and reset the sequence before the next Invoice record needs
>> to be created. Once the initial record is created we can move on and
>> then details about the invoice can be added later. If a problem comes up
>> with those details being added we would leave the original Invoice
>> record there but just change the status on it, if the error is
>> unrecoverable of course.
>>
>>> Functional issue:
>>> the same division may have to maintain more than one sequence for
>>> invoices; I still don't have all the requirements/details about the
>>> way the right sequence is used (by customer classification? by billing
>>> address? by product store? by sales channel?) and I would like to get
>>> some requirements by you? Do you have similar needs? Speak up!
>>>
>>> After that we have better defined all the requirements we can draft a
>>> plan.
>>
>> However we do this we'll just have to see which factors result in
>> differentiating multiple sequences, versus which factors are used to
>> reset a given sequence. Then along with the above it's a matter of
>> putting all of the pieces together, which isn't necessarily easy... :)
>>
>> -David
>>
>>
>>
>>
>>
>>
>>
>>
>
> -- 
> BJ Freeman
> http://www.businessesnetwork.com/automation
> http://bjfreeman.elance.com
> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
> Systems Integrator.
> 

Re: Invoice sequence ids

[Jacques Le Roux <ja...@free.fr>]

Hi BJ,

Some months ago Deydan Tvetanov suggested to open a Jira about this subject. He did not yet, but you may find his proposition in use 
ML. Search MarkMail for "Custom entity ID generator".

Jacques

From: "Jacques Le Roux" <ja...@les7arts.com>
> Hi BJ,
>
> I don't think it's a security issue, but opening a Jira for this seems appropriate to me. Please give as much as details, links
> and requirements. Maybe we could even open a wiki page for this like the VAT page http://docs.ofbiz.org/display/OFBIZ/VAT
>
> Jacques
>
> From: "BJ Freeman" <bj...@free-man.net>
>> in bumping this up to refresh the conversation, it is impartitive in
>> HIPPA that there is an audit to make sure there are no gap so someone
>> can not change or substitute records.
>> I have looked at the SequenceUtil.java.
>> It is still what I consider out of my league
>> any thoughts or energy would be greatly appreciated.
>> you could consider it a security issue.
>> :D
>>
>>
>> David E Jones sent the following on 7/18/2008 12:44 AM:
>>>
>>> On Jul 11, 2008, at 8:07 AM, Jacopo Cappellato wrote:
>>>
>>>> Hi all,
>>>>
>>>> Based on the feedback I got from various Italian customers/contacts it
>>>> seems that enhancing the way invoice sequence ids are managed is one
>>>> of the most desired features.
>>>> The main requirements can be summarized with:
>>>> * strictly sequential invoice ids (no gaps): this is required (or just
>>>> common practice) in some countries, including Italy
>>>> * the ability to reset the invoice id (i.e. "starting again from 1")
>>>> when a new financial period starts; this is typically done at the
>>>> beginning of the year in Italy
>>>> * the ability to define more than one sequences; for example: one for
>>>> invoices for UE customers, one for invoices for extra UE customers etc...
>>>>
>>>> I know there is already some support for this stuff in the existing
>>>> codebase, but I think there are still issues (some technical and some
>>>> functional) and so there is room for enhancements to the existing code.
>>>>
>>>> In its current implementation the system allows you to maintain the
>>>> following sequences:
>>>>
>>>> A) "Standard (faster, may have gaps, per system)"
>>>> B) "Enforced Sequence (no gaps, per organization)"
>>>> C) "Restart on Fiscal Year (no gaps, per org, reset to 1 each year)"
>>>>
>>>> Technical issue (after a review of the "getNextInvoiceId" service):
>>>> method A is implemented in the right way, i.e. using the
>>>> SequenceValueItem and related service; on the other hand method B and
>>>> C don't use the SequenceValueItem entity/service and instead handle
>>>> the sequence generation using the getNextInvoiceId service and the
>>>> field PartyAcctgPreference.lastInvoiceNumber: this is an issue because
>>>> these methods are not thread safe and there is no reason to not use
>>>> the SequenceValueItem stuff for them too; for example we could
>>>> maintain in the SequenceValueItem entity one sequence for each of the
>>>> divisions or methods that we need ("Invoice_Company",
>>>> "Invoice_SubDivisionA" etc...) and then just call the
>>>> sequenced-id-to-env passing the right sequence name and
>>>> stagger-max="1" (to avoid gaps).
>>>
>>> Unfortunately even with stagger-max set to 1 there is still a likely
>>> problem with gaps. Some scenarios:
>>>
>>> 1. the server is restarted and the remaining numbers in the ID bank
>>> would not be used; we could reduce the bank-size to 1 which would force
>>> the entity engine to go to the database for every sequence number, but
>>> this still isn't perfect...
>>>
>>> 2. like you mentioned below after the ID is retrieved the transaction
>>> for the invoice creation could be rolled back, leading to the invoice ID
>>> not being used and therefore a gap
>>>
>>>> And we could easily implement the "Restart on Fiscal Year" method by
>>>> resetting the proper sequence in the SequenceValueItem entity (and
>>>> this could be done by the service that closes a Financial time period).
>>>>
>>>> However also this method will not guarantee a strictly sequential id
>>>> series: for example, if an error happens during the invoice
>>>> generation, everything is rolled back, but not the id in the
>>>> SequenceValueItem, because it is committed in a separate
>>>> transaction... hmmmm... any ideas on how to resolve this issue?
>>>
>>> I'm not sure if there is a good way to solve this problem. In order to
>>> guarantee a strict ID sequence with no gaps you would have to create
>>> only 1 invoice at a time because of the chance that an invoice creation
>>> will fail, and thus leave a gap in the sequence.
>>>
>>> One possibility is to create one Invoice header record at a time without
>>> any of the detail, and if anything fails in that it will rollback
>>> immediately and reset the sequence before the next Invoice record needs
>>> to be created. Once the initial record is created we can move on and
>>> then details about the invoice can be added later. If a problem comes up
>>> with those details being added we would leave the original Invoice
>>> record there but just change the status on it, if the error is
>>> unrecoverable of course.
>>>
>>>> Functional issue:
>>>> the same division may have to maintain more than one sequence for
>>>> invoices; I still don't have all the requirements/details about the
>>>> way the right sequence is used (by customer classification? by billing
>>>> address? by product store? by sales channel?) and I would like to get
>>>> some requirements by you? Do you have similar needs? Speak up!
>>>>
>>>> After that we have better defined all the requirements we can draft a
>>>> plan.
>>>
>>> However we do this we'll just have to see which factors result in
>>> differentiating multiple sequences, versus which factors are used to
>>> reset a given sequence. Then along with the above it's a matter of
>>> putting all of the pieces together, which isn't necessarily easy... :)
>>>
>>> -David
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>> -- 
>> BJ Freeman
>> http://www.businessesnetwork.com/automation
>> http://bjfreeman.elance.com
>> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
>> Systems Integrator.
>>
>
>

Re: Invoice sequence ids

[BJ Freeman <bj...@free-man.net>]

Jacques:
I just modified SequenceUtil.java, since all the consideration in the
original code were not my concern.
My modes are not compatible with the current code.

Jacques Le Roux sent the following on 8/22/2009 6:25 AM:
> Hi BJ,
> 
> Some months ago Deydan Tvetanov suggested to open a Jira about this
> subject. He did not yet, but you may find his proposition in use ML.
> Search MarkMail for "Custom entity ID generator".
> 
> Jacques
> 
> From: "Jacques Le Roux" <ja...@les7arts.com>
>> Hi BJ,
>>
>> I don't think it's a security issue, but opening a Jira for this seems
>> appropriate to me. Please give as much as details, links
>> and requirements. Maybe we could even open a wiki page for this like
>> the VAT page http://docs.ofbiz.org/display/OFBIZ/VAT
>>
>> Jacques
>>
>> From: "BJ Freeman" <bj...@free-man.net>
>>> in bumping this up to refresh the conversation, it is impartitive in
>>> HIPPA that there is an audit to make sure there are no gap so someone
>>> can not change or substitute records.
>>> I have looked at the SequenceUtil.java.
>>> It is still what I consider out of my league
>>> any thoughts or energy would be greatly appreciated.
>>> you could consider it a security issue.
>>> :D
>>>
>>>
>>> David E Jones sent the following on 7/18/2008 12:44 AM:
>>>>
>>>> On Jul 11, 2008, at 8:07 AM, Jacopo Cappellato wrote:
>>>>
>>>>> Hi all,
>>>>>
>>>>> Based on the feedback I got from various Italian customers/contacts it
>>>>> seems that enhancing the way invoice sequence ids are managed is one
>>>>> of the most desired features.
>>>>> The main requirements can be summarized with:
>>>>> * strictly sequential invoice ids (no gaps): this is required (or just
>>>>> common practice) in some countries, including Italy
>>>>> * the ability to reset the invoice id (i.e. "starting again from 1")
>>>>> when a new financial period starts; this is typically done at the
>>>>> beginning of the year in Italy
>>>>> * the ability to define more than one sequences; for example: one for
>>>>> invoices for UE customers, one for invoices for extra UE customers
>>>>> etc...
>>>>>
>>>>> I know there is already some support for this stuff in the existing
>>>>> codebase, but I think there are still issues (some technical and some
>>>>> functional) and so there is room for enhancements to the existing
>>>>> code.
>>>>>
>>>>> In its current implementation the system allows you to maintain the
>>>>> following sequences:
>>>>>
>>>>> A) "Standard (faster, may have gaps, per system)"
>>>>> B) "Enforced Sequence (no gaps, per organization)"
>>>>> C) "Restart on Fiscal Year (no gaps, per org, reset to 1 each year)"
>>>>>
>>>>> Technical issue (after a review of the "getNextInvoiceId" service):
>>>>> method A is implemented in the right way, i.e. using the
>>>>> SequenceValueItem and related service; on the other hand method B and
>>>>> C don't use the SequenceValueItem entity/service and instead handle
>>>>> the sequence generation using the getNextInvoiceId service and the
>>>>> field PartyAcctgPreference.lastInvoiceNumber: this is an issue because
>>>>> these methods are not thread safe and there is no reason to not use
>>>>> the SequenceValueItem stuff for them too; for example we could
>>>>> maintain in the SequenceValueItem entity one sequence for each of the
>>>>> divisions or methods that we need ("Invoice_Company",
>>>>> "Invoice_SubDivisionA" etc...) and then just call the
>>>>> sequenced-id-to-env passing the right sequence name and
>>>>> stagger-max="1" (to avoid gaps).
>>>>
>>>> Unfortunately even with stagger-max set to 1 there is still a likely
>>>> problem with gaps. Some scenarios:
>>>>
>>>> 1. the server is restarted and the remaining numbers in the ID bank
>>>> would not be used; we could reduce the bank-size to 1 which would force
>>>> the entity engine to go to the database for every sequence number, but
>>>> this still isn't perfect...
>>>>
>>>> 2. like you mentioned below after the ID is retrieved the transaction
>>>> for the invoice creation could be rolled back, leading to the
>>>> invoice ID
>>>> not being used and therefore a gap
>>>>
>>>>> And we could easily implement the "Restart on Fiscal Year" method by
>>>>> resetting the proper sequence in the SequenceValueItem entity (and
>>>>> this could be done by the service that closes a Financial time
>>>>> period).
>>>>>
>>>>> However also this method will not guarantee a strictly sequential id
>>>>> series: for example, if an error happens during the invoice
>>>>> generation, everything is rolled back, but not the id in the
>>>>> SequenceValueItem, because it is committed in a separate
>>>>> transaction... hmmmm... any ideas on how to resolve this issue?
>>>>
>>>> I'm not sure if there is a good way to solve this problem. In order to
>>>> guarantee a strict ID sequence with no gaps you would have to create
>>>> only 1 invoice at a time because of the chance that an invoice creation
>>>> will fail, and thus leave a gap in the sequence.
>>>>
>>>> One possibility is to create one Invoice header record at a time
>>>> without
>>>> any of the detail, and if anything fails in that it will rollback
>>>> immediately and reset the sequence before the next Invoice record needs
>>>> to be created. Once the initial record is created we can move on and
>>>> then details about the invoice can be added later. If a problem
>>>> comes up
>>>> with those details being added we would leave the original Invoice
>>>> record there but just change the status on it, if the error is
>>>> unrecoverable of course.
>>>>
>>>>> Functional issue:
>>>>> the same division may have to maintain more than one sequence for
>>>>> invoices; I still don't have all the requirements/details about the
>>>>> way the right sequence is used (by customer classification? by billing
>>>>> address? by product store? by sales channel?) and I would like to get
>>>>> some requirements by you? Do you have similar needs? Speak up!
>>>>>
>>>>> After that we have better defined all the requirements we can draft a
>>>>> plan.
>>>>
>>>> However we do this we'll just have to see which factors result in
>>>> differentiating multiple sequences, versus which factors are used to
>>>> reset a given sequence. Then along with the above it's a matter of
>>>> putting all of the pieces together, which isn't necessarily easy... :)
>>>>
>>>> -David
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> -- 
>>> BJ Freeman
>>> http://www.businessesnetwork.com/automation
>>> http://bjfreeman.elance.com
>>> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
>>>
>>> Systems Integrator.
>>>
>>
>>
> 
> 

-- 
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.