Add global margin and distribute it to items

ERPNext Selling/CRM
1

In quotations and invoices, I want to add a global margin (e.g. 1500€) that then should be distributed to the items appearing in the invoice or quotation, proportional to their buying price. This results in a very individual item selling price that depends strongly on the total of the quotation or invoice.
Probably this feature is not available out of the box. I think of writing Jinja templates for quotations and invoices, because this would provide an easy solution. However, I have the feeling that delegating this entirely to the view level is no right, because this is business logic, and hence some controller code should be written. On the other hand, I am not sure if it is useful to store all these individual prices in the database.
Any advice?

2

Hi,

It may be possible to achieve that through the use of a Pricing Rule.

3

Hi tillmo,

Welcome to ERPNext. Hope you have a great experience with ERPNext and here on the community.

Won’t a custom script work for you? Like you could have a custom field in the Parent Document into which the user inputs the Margin (1500 Euros) amount that needs to be distributed to the items, and you can upon save or refresh distribute that margin to the item prices based on a Purchase Price list. Or last purchase price, etc. Since the document will record the price you are quoting, that could be record enough. If you want to insert a bit of QC, you could compute the margin separately and ensure that the Margin amount is close to what was entered in the custom field in the first instance.

Hope this helps.

Thanks

Jay

4

Hi Jay,
many thanks for your answer. Yes, indeed, I plan to use a custom script (a pricing rules does not work, as it applies to individual items only). However, I do not want to change the price lists, because the global margin leads to different items prices for each new opportunity. So I plan to compute these individual item prices on the fly in a Jinja script for the quotation print format. However, the net total (and total?) should be updated using a custom script, for later use in the accounting. But I am bit lost with all the different kind of totals being there in a quotation. I think of something like

frappe.ui.form.on('Quotation', {
refresh(frm) {
	frm.doc.net_total = frm.doc.base_net_total+frm.doc.global_margin
}
})

where global_margin is a new custom field, and while I ignore the conversion rate for now (which I do not need). However, this has not effect. What has an effect is
frm.doc.grand_total = frm.doc.net_total+frm.doc.global_margin
but this is obviously wrong, since taxes are ignored. Moreover, the global_margin should be added before taxes (thereby also increasing the taxes). It seems that I need to trigger the computation of a lot of fields, like taxes, grand_total etc. But I do not want to include all this in the script, but just let it be done in the standard way, given my modified net_total (or perhaps total). Is this possible?

All the best,
Till

5

Hi Till,

I’m not a developer, so perhaps a developer will be able to weigh in on this.

But I’m guessing that if you have 3 items, A, B, & C each 1Nos and each costing 1500, 2000 & 2500 Euros and you want to add a margin of 1500 Euros to this, you’d want the rate in the quotation to be 1500*(1500+2000+2500+1500)/(1500+2000+2500), 2000*(1500+2000+2500+1500)/(1500+2000+2500) and 2500*(1500+2000+2500+1500)/(1500+2000+2500). If you do that your quotation will turn out okay. So, you need the custom script that makes these insertions into the item rate and you need to maintain a Price List that tracks cost. Or pull the Last Purchase price of the items.

Just managing it in the Print Format will make it harder when the quotation fructifies into an order. I suggest that the quotation itself needs to come out according to the logic you want to use.

Hope this helps.

Thanks

Jay

6

Hi Jay,

thanks for your answer. Indeed, I have used a formula similar to yours. But my problem is to find out where exactly to store the resulting values. Do you really think that I should create a new price list for each quotation and then use that price list in the quotation? I think then all the other values like taxes and totals would be automatically computed in a correct way. However, the system will be flooded with lots of price lists, one per quotation. Maybe this is not a problem if I mark these price lists as auxiliary (with a new custom flag) and filter them out by default when showing price lists.
That said, I still hope that there is an easier way, namely by just adjusting the different totals and taxes, while computing the item prices only on the fly in the print format.

Best, Till

7

The following code solves my problem:

frappe.ui.form.on('Quotation', {
  refresh(frm) {
      if (frm.doc.global_margin) {
        var total = 0
        var i = 0
        var items = frm.doc.items
        for (i = 0; i < items.length; i++) {   
          total += items[i].price_list_rate * items[i].qty
        }  
        var margin_factor = (total+frm.doc.global_margin)/total
        for (i = 0; i < items.length; i++) {   
            items[i].rate = Math.round((items[i].price_list_rate * margin_factor + Number.EPSILON) * 100) / 100
            items[i].amount = items[i].rate * items[i].qty
        } 
        frm.doc.total_qty = frm.doc.total = frm.doc.base_total = frm.doc.net_total = frm.doc.base_net_total = 0.0;

		$.each(items || [], function(i, item) {
			frm.doc.total += item.amount;
			frm.doc.total_qty += item.qty;
			frm.doc.base_total += item.base_amount;
			frm.doc.net_total += item.net_amount;
			frm.doc.base_net_total += item.base_net_amount;
			});

		frappe.model.round_floats_in(frm.doc, ["total", "base_total", "net_total", "base_net_total"]);
      }
  }
})

Now for the last part, it would be easier to call calculate_net_total from erpnext/public/js/controllers/taxes_and_totals.js. How do I do that? Likewise, I also would like to call apply_pricing_rule_on_item on the items.