Re: Progress on Invoice webservice (discussion solicited)
Hi David,
On Mon, Jul 12, 2021 at 12:04 PM lsmbdev lsmbdev@sbts.com.au wrote:
Regarding my suggestion for 3 sets of price data to be stored against each invoice line. There is another reason (other than the obvious) I'm suggesting that. It allows accurate reporting on sales "variations"
Ok. To be able to do that, I don't see more than the requirement for 2 sets: the actual data used in the invoice (which is already there now) and the numbers calculated by the system. If the numbers have not been overridden by the user, the "variation" is zero.
For example a salesperson may vary the price by applying a discount or manually changing the price on an item. In general thats good sales practice as long as the net sales margin from a customer is above some target level.
Right. Or maybe other criteria that the sales has been mandated for.
Without storing all three items of data it becomes difficult, if not impossible, to correctly report on this with enough detail to be useful for basic oversight let alone malicious behaviour.
Ok. I can see how this would be convenient for oversight. However - assuming the business doesn't change its prices daily (but rather more quarterly or less often) - the data required to do oversight should be there when the oversight is effective: short after the actual sale. At that time, the data is all there. The pricematrix price derivation is even in the database now (see https://github.com/ledgersmb/LedgerSMB/blob/816e740f0702af2a2192dfe4832dc3e4...). So, assuming sufficiently constant prices, you could retrieve the oversight data nicely. All the data needed to report e.g. an average sales price (per part) is also there.
I've run into other use cases in the past as well.
Ok. I was thinking to introduce the ability to track the applicable pricing at a given point in time, so as to be able to provide an audit trail on how invoice prices were determined. With the schema currently proposed, there will at least be an indicator that the person compiling the invoice either set their own price, or that the price was inherited from an order/quote (as contrasted by "taken from the pricing mechanism/calculated by the software").
Regards,
Erik.
Sent from my Galaxy
-------- Original message -------- From: Erik Huelsmann ehuels@gmail.com Date: 12/7/21 15:09 (GMT+08:00) To: lsmbdev lsmbdev@sbts.com.au Cc: devel@lists.ledgersmb.org, John Locke john@freelock.com, "Yves Lavoie, GaYLi" ylavoie@yveslavoie.com Subject: Re: Progress on Invoice webservice (discussion solicited)
Hi David,
Thanks for your response. We've discussed my earlier mail on the chat channel as well; I'll summarize in response to your mail.
On Tue, Jul 6, 2021 at 4:22 AM lsmbdev lsmbdev@sbts.com.au wrote:
Shouldn't the linetotal be returned within the calculated block rather than the user supplied block? Doing otherwise seems like a massive rounding nightmare.
Yes, it should (and will); I was posting an unfortunate combination of request and response structures. (since the response is just a superset of the request, it's hard to separate the two in my mind).
As for handling db side changes during the life cycle of the invoice (eg: price and tax changes) I'm not sure I see an easy solution. We currently have a problem here anyway. If we follow a full quote => order => invoice workflow you expect a price as shown on a quote to be static throughout the sequence. However it will most likely change if any source info changes along the way.
That's a good point. Yes, I too expect the current implementation to apply price-changes during that life cycle. Thanks for pointing that out: Evaluating the solution to the original problem discussed on the chat channel, I think that with a few minor changes we can support this workflow the way it's expected to work.
Personally, I think a specific request to update a line is required. To that end, I think each line should (as the first element) contain a boolean "recalc" element and a "lastcalc" timestamp As should the entire invoice.
The solution we discussed on the chat channel is just as simple as effective: John proposed we add a boolean to each invoice line which indicates the price in that line should be excluded from pricematrix calculations; e.g. named 'pricefreeze'. That way, the line gets excluded from pricematrix calculations and won't be updated when the invoice gets saved. How the UI gets to set the 'pricefreeze' boolean, is out of scope of the current consideration. It didn't feel right to add another checkbox on the already crowded invoice input screen. We came up with other ways to set the value. But, as said, that's to be dealt with later.
Applying this solution to your "full quote > invoice" workflow, I think the expected behaviour can be had by setting the 'pricefreeze' parameter on orders spawned from quotes and invoices spawned from orders.
Regardless, there probably needs to be 3 sets of price data presented.
- user overrides
- system calculated
- actual data for use
The response is currently expected to hold just the price used for calculation. I was originally expecting to send the list price or the price matrix price too, but I found that to be confusing, because: which price do you send when there's a price matrix price for the current vendor and invoice line, but the user has overridden the price manually?
All of the above should be permanently stored in the db. (FYI, we should be doing this even for non API entries as well. This preserves the data. Allowing better auditing and diagnostics)
I was considering the option to add a time-dimension to the parts table, allowing various parts uses to reference explicit lines in the table, with the price data as it applied at the specific time of modification of the quote/order/invoice. It's quite complex though, because: do you create a full copy of the price matrix too? and what about the parts line itself when e.g. a line in the makemodel table is changed?
I decided that storing a full audit trail on modified parts data is probably a good idea, but not something to be addressed while creating the invoice API. For now, I'm really happy with John's "pricefreeze" suggestion as it allows us to move forward on the implementation without endless complexities.
There is probably more to consider, but there's a start for discussion
Regards,
Erik.
Regards David G
-------- Original message -------- From: Erik Huelsmann ehuels@gmail.com Date: 6/7/21 04:56 (GMT+08:00) To: devel@lists.ledgersmb.org
Hi,
Over the past months, I've been working on a webservice for LedgerSMB to post invoices. The current status can be viewed here: https://github.com/ehuelsmann/LedgerSMB/blob/webservices/lib/LedgerSMB/Route...
Summarizing what I have achieved so far:
- An endpoint /erp/api/v0/invoices/, which accepts POST requests
- The "specification" of the payload can be found here:
https://github.com/ehuelsmann/LedgerSMB/blob/webservices/lib/LedgerSMB/Route...
- The toplevel keys "eca", "account", "dates", "lines", "taxes",
"currency", "description", "invumber", "ordnumber", "ponumber" are supported
- The pricematrix is consulted when determining pricing
- Customers and vendors are supported (each with their own type of
pricematrix)
The endpoint has been coded defensively, throwing an "unacceptable input" error on anything that's not consistent with what the database should expect. A lot of things are currently not supported:
- Invoices will end up in "draft" state and cannot be Post-ed
- Payments are not processed
- Rounding (in the price matrix, but also of the line-totals) isn't in
place
- Complex taxes have limited support ("simple taxes only")
- No multi-currency support yet
- No support for "taxes included" tax extraction calculation (from the
gross invoice amount)
- I have no idea what happens with Cost Of Goods Sold calculations
- No support for shipping addresses
- No support for printing/mailing/etc.
- All invoices are posted into the 'ar' table (oops!)
That said, I have some questions with respect to what the functionality needs to be able to do, especially with respect to saving invoices in order to post them later. With respect to saving and posting invoices, I'm envisaging the workflow to require a number (lots) of save actions on the invoice in order to calculate its prices, i.e. to calculate the price matrix. There may be other reasons to save the invoice in the mean time. In the current invoice-entry process (running through "old code"), there's a lot of code which tries to determine if anything changed in the invoice based on the fields entered. If it did (or rather, if the deduction code thinks it did), the invoice will be recalculated either partial or in full. This code causes lots of headaches, because most of the time the user didn't expect their edits to trigger a full recalculation, or, the recalculation didn't happen the way it should e.g. due to the fact that prices had been overridden by the user. To prevent similar complexity in the webservice, I've created a structure where each line returns the data entered by the user (as provided on input) as well as calculated data. It is up to the front-end to present that data in a useful way to the user. Just as an example, the following structure could be a line in the invoice:
{ "part": { "number": "p1", "description": "p1 desc", "price": 1.23 }, "quantity": 5, "description": null, "price": null, "linetotal": 6.15, "discount": null }
The above line means to say that the user didn't enter a price, description or discount. The part has been configured with a price of $1.23 for the current customer/vendor and the quantity on the line is 5 (as entered by the user). If the user enters a description, the description field in the line itself gets filled with that, e.g. "bike rental fees". Assuming that the user saves the above line without further input and continues by posting the invoice, I'm thinking that the posting action should copy the description and price from the part configuration to the invoice. This poses a timing challenge: what if in the mean time the part configuration gets changed and the price is updated to 5.23? Then the actual invoice posted goes from a linetotal of 6.15$ to 25.15$. Seems unacceptable. So, the other option is: create a "calculate the price matrix for this invoice without actually storing it" endpoint and do the "copy data from part" step on save instead of on posting. Here, I'm running into a problem too: lets assume the user wants to save the invoice only to work on it more tomorrow? In that case, all the prices have been filled and the price matrix calculation won't work anymore, because it doesn't work due to the "price" fields no longer being 'null'.
The above problem exists not only for parts, but also for taxes; maybe other scenarios too.
By posting this, I hope to get some good discussion on the topic (and hopefully some good additional ideas)!
-- Bye,
Erik.
http://efficito.com -- Hosted accounting and ERP. Robust and Flexible. No vendor lock-in.
-- Bye,
Erik.
http://efficito.com -- Hosted accounting and ERP. Robust and Flexible. No vendor lock-in.
Hi Erik, I still believe the 3 sets of numbers are required.You need to be able to see the - actual price sold at (actual data for use)- system calculated @ time of sale- user overrides"User overrides" may well be zero, even when the other two numbers don't match.This would occur when using price freeze on a quote for example. With regards to rate of change of prices, there are enough vendors that issue monthly price list updates for that to be an issue.One of the things I'll be working on soon is pricelist import (direct to db) for a number of Andrews vendors that make monthly price changes.RegardsDavid GSent from my Galaxy -------- Original message --------From: Erik Huelsmann ehuels@gmail.com Date: 14/7/21 04:52 (GMT+08:00) To: lsmbdev lsmbdev@sbts.com.au Cc: devel@lists.ledgersmb.org, John Locke john@freelock.com, "Yves Lavoie, GaYLi" ylavoie@yveslavoie.com Subject: Re: Progress on Invoice webservice (discussion solicited) Hi David,On Mon, Jul 12, 2021 at 12:04 PM lsmbdev lsmbdev@sbts.com.au wrote:Regarding my suggestion for 3 sets of price data to be stored against each invoice line.There is another reason (other than the obvious) I'm suggesting that.It allows accurate reporting on sales "variations"Ok. To be able to do that, I don't see more than the requirement for 2 sets: the actual data used in the invoice (which is already there now) and the numbers calculated by the system. If the numbers have not been overridden by the user, the "variation" is zero. For example a salesperson may vary the price by applying a discount or manually changing the price on an item.In general thats good sales practice as long as the net sales margin from a customer is above some target level.Right. Or maybe other criteria that the sales has been mandated for. Without storing all three items of data it becomes difficult, if not impossible, to correctly report on this with enough detail to be useful for basic oversight let alone malicious behaviour. Ok. I can see how this would be convenient for oversight. However - assuming the business doesn't change its prices daily (but rather more quarterly or less often) - the data required to do oversight should be there when the oversight is effective: short after the actual sale. At that time, the data is all there. The pricematrix price derivation is even in the database now (see https://github.com/ledgersmb/LedgerSMB/blob/816e740f0702af2a2192dfe4832dc3e4...). So, assuming sufficiently constant prices, you could retrieve the oversight data nicely. All the data needed to report e.g. an average sales price (per part) is also there. I've run into other use cases in the past as well.Ok. I was thinking to introduce the ability to track the applicable pricing at a given point in time, so as to be able to provide an audit trail on how invoice prices were determined. With the schema currently proposed, there will at least be an indicator that the person compiling the invoice either set their own price, or that the price was inherited from an order/quote (as contrasted by "taken from the pricing mechanism/calculated by the software").Regards,Erik.Sent from my Galaxy-------- Original message --------From: Erik Huelsmann ehuels@gmail.com Date: 12/7/21 15:09 (GMT+08:00) To: lsmbdev lsmbdev@sbts.com.au Cc: devel@lists.ledgersmb.org, John Locke john@freelock.com, "Yves Lavoie, GaYLi" ylavoie@yveslavoie.com Subject: Re: Progress on Invoice webservice (discussion solicited) Hi David,Thanks for your response. We've discussed my earlier mail on the chat channel as well; I'll summarize in response to your mail.On Tue, Jul 6, 2021 at 4:22 AM lsmbdev lsmbdev@sbts.com.au wrote:Shouldn't the linetotal be returned within the calculated block rather than the user supplied block?Doing otherwise seems like a massive rounding nightmare.Yes, it should (and will); I was posting an unfortunate combination of request and response structures. (since the response is just a superset of the request, it's hard to separate the two in my mind). As for handling db side changes during the life cycle of the invoice (eg: price and tax changes) I'm not sure I see an easy solution. We currently have a problem here anyway.If we follow a full quote => order => invoice workflow you expect a price as shown on a quote to be static throughout the sequence.However it will most likely change if any source info changes along the way.That's a good point. Yes, I too expect the current implementation to apply price-changes during that life cycle. Thanks for pointing that out: Evaluating the solution to the original problem discussed on the chat channel, I think that with a few minor changes we can support this workflow the way it's expected to work. Personally, I think a specific request to update a line is required. To that end, I think each line should (as the first element) contain a boolean "recalc" element and a "lastcalc" timestampAs should the entire invoice.The solution we discussed on the chat channel is just as simple as effective: John proposed we add a boolean to each invoice line which indicates the price in that line should be excluded from pricematrix calculations; e.g. named 'pricefreeze'. That way, the line gets excluded from pricematrix calculations and won't be updated when the invoice gets saved. How the UI gets to set the 'pricefreeze' boolean, is out of scope of the current consideration. It didn't feel right to add another checkbox on the already crowded invoice input screen. We came up with other ways to set the value. But, as said, that's to be dealt with later.Applying this solution to your "full quote > invoice" workflow, I think the expected behaviour can be had by setting the 'pricefreeze' parameter on orders spawned from quotes and invoices spawned from orders. Regardless, there probably needs to be 3 sets of price data presented.- user overrides- system calculated- actual data for useThe response is currently expected to hold just the price used for calculation. I was originally expecting to send the list price or the price matrix price too, but I found that to be confusing, because: which price do you send when there's a price matrix price for the current vendor and invoice line, but the user has overridden the price manually? All of the above should be permanently stored in the db. (FYI, we should be doing this even for non API entries as well.This preserves the data. Allowing better auditing and diagnostics)I was considering the option to add a time-dimension to the parts table, allowing various parts uses to reference explicit lines in the table, with the price data as it applied at the specific time of modification of the quote/order/invoice. It's quite complex though, because: do you create a full copy of the price matrix too? and what about the parts line itself when e.g. a line in the makemodel table is changed?I decided that storing a full audit trail on modified parts data is probably a good idea, but not something to be addressed while creating the invoice API. For now, I'm really happy with John's "pricefreeze" suggestion as it allows us to move forward on the implementation without endless complexities. There is probably more to consider, but there's a start for discussion Regards,Erik. RegardsDavid G-------- Original message --------From: Erik Huelsmann ehuels@gmail.com Date: 6/7/21 04:56 (GMT+08:00) To: devel@lists.ledgersmb.orgHi,Over the past months, I've been working on a webservice for LedgerSMB to post invoices. The current status can be viewed here: https://github.com/ehuelsmann/LedgerSMB/blob/webservices/lib/LedgerSMB/Route... what I have achieved so far: * An endpoint /erp/api/v0/invoices/, which accepts POST requests * The "specification" of the payload can be found here: https://github.com/ehuelsmann/LedgerSMB/blob/webservices/lib/LedgerSMB/Route... The toplevel keys "eca", "account", "dates", "lines", "taxes", "currency", "description", "invumber", "ordnumber", "ponumber" are supported * The pricematrix is consulted when determining pricing * Customers and vendors are supported (each with their own type of pricematrix)The endpoint has been coded defensively, throwing an "unacceptable input" error on anything that's not consistent with what the database should expect. A lot of things are currently not supported: * Invoices will end up in "draft" state and cannot be Post-ed * Payments are not processed * Rounding (in the price matrix, but also of the line-totals) isn't in place * Complex taxes have limited support ("simple taxes only") * No multi-currency support yet * No support for "taxes included" tax extraction calculation (from the gross invoice amount) * I have no idea what happens with Cost Of Goods Sold calculations * No support for shipping addresses * No support for printing/mailing/etc. * All invoices are posted into the 'ar' table (oops!)That said, I have some questions with respect to what the functionality needs to be able to do, especially with respect to saving invoices in order to post them later. With respect to saving and posting invoices, I'm envisaging the workflow to require a number (lots) of save actions on the invoice in order to calculate its prices, i.e. to calculate the price matrix. There may be other reasons to save the invoice in the mean time.In the current invoice-entry process (running through "old code"), there's a lot of code which tries to determine if anything changed in the invoice based on the fields entered. If it did (or rather, if the deduction code thinks it did), the invoice will be recalculated either partial or in full.This code causes lots of headaches, because most of the time the user didn't expect their edits to trigger a full recalculation, or, the recalculation didn't happen the way it should e.g. due to the fact that prices had been overridden by the user.To prevent similar complexity in the webservice, I've created a structure where each line returns the data entered by the user (as provided on input) as well as calculated data. It is up to the front-end to present that data in a useful way to the user. Just as an example, the following structure could be a line in the invoice: { "part": { "number": "p1", "description": "p1 desc", "price": 1.23 }, "quantity": 5, "description": null, "price": null, "linetotal": 6.15, "discount": null }The above line means to say that the user didn't enter a price, description or discount. The part has been configured with a price of $1.23 for the current customer/vendor and the quantity on the line is 5 (as entered by the user). If the user enters a description, the description field in the line itself gets filled with that, e.g. "bike rental fees".Assuming that the user saves the above line without further input and continues by posting the invoice, I'm thinking that the posting action should copy the description and price from the part configuration to the invoice.This poses a timing challenge: what if in the mean time the part configuration gets changed and the price is updated to 5.23? Then the actual invoice posted goes from a linetotal of 6.15$ to 25.15$. Seems unacceptable.So, the other option is: create a "calculate the price matrix for this invoice without actually storing it" endpoint and do the "copy data from part" step on save instead of on posting. Here, I'm running into a problem too: lets assume the user wants to save the invoice only to work on it more tomorrow? In that case, all the prices have been filled and the price matrix calculation won't work anymore, because it doesn't work due to the "price" fields no longer being 'null'.The above problem exists not only for parts, but also for taxes; maybe other scenarios too.By posting this, I hope to get some good discussion on the topic (and hopefully some good additional ideas)!-- Bye,Erik.http://efficito.com%C2%A0-- Hosted accounting and ERP.Robust and Flexible. No vendor lock-in. -- Bye,Erik.http://efficito.com%C2%A0-- Hosted accounting and ERP.Robust and Flexible. No vendor lock-in. -- Bye,Erik.http://efficito.com%C2%A0-- Hosted accounting and ERP.Robust and Flexible. No vendor lock-in.
participants (2)
-
Erik Huelsmann -
lsmbdev