Invoice sequence ids
Locked
 
|
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). 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? 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. Cheers, Jacopo |
smime.p7s (3K) |
|
I would think that using a link list of some sort would address most of
the issues. the SequenceValueItem entity could had last and next added however you would need a separate entity for each document that needs sequencing. you could add last next to each entity, like PartyAcctgPreference.lastInvoiceNumber and PartyAcctgPreference.thisInvoiceNumber PartyAcctgPreference.NextInvoiceNumber this would allow the services to implement different numbering systems so you would have a PartyAcctgPreference.InvoiceNumberSequenceSystem flag and to speed up the process you could convert the SequenceValueItem to add fields for all the different entities that would have the last Invoice Number like PartyAcctgPreference would be a field in SequenceValueItem Jacopo Cappellato sent the following on 7/11/2008 7:07 AM: > 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). 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? > > 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. > > Cheers, > > Jacopo > |
|
|
In reply to this post by Jacopo Cappellato-3
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 |
|
|
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. |
Administrator
|
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" <[hidden email]> > 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. > |
|
|
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" <[hidden email]> > 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" <[hidden email]> >> 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. >> > > |
|
|
In reply to this post by Jacques Le Roux
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" <[hidden email]> >> 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" <[hidden email]> >>> 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. |
«
Return to OFBiz - Dev
|
1 view|%1 views
| Free forum by Nabble | Edit this page |