Dynamic Mass-to-Volume Conversion for Oil and Gas Products with Varying Density in ERPNext

ERPNext
, , ,
1

Hello ERPNext community,

I’m in the process of implementing ERPNext for a company that buys and sells oil and gas products such as petrol, diesel, and other similar commodities. We face a unique challenge with converting mass (tonnes) to volume (liters) because the density of these products fluctuates based on factors like temperature, composition, etc., and cannot be predicted in advance. As a result, managing the correct unit conversion factor across various documents like quotations, sales orders, and delivery notes is complex.

The Process We Follow:

  1. Contract Creation: Our company and the buyer sign a contract, which forms the basis for all future orders. From this contract, we create a quotation for the buyer.
  2. Sales Order Creation: The buyer then places one or more sales orders based on the quotation. A single contract might generate multiple sales orders over time, depending on the total value of the quotation.
  3. Delivery Note Creation: For each sales order, we create a delivery note that represents the physical delivery of goods. At the time of delivery, we can determine the actual density of the oil products. However, the density value was not available when the quotation was originally created, and this leads to discrepancies when we try to apply the correct mass-to-volume conversion.

The Core Issue:

  • The density value, which is essential for converting tonnes to liters, is only known at the time of delivery, but it is needed for accurate unit conversion when creating both the quotation and the sales order.
  • If we create both the sales order and delivery note at the same time, we can use the same density value for both, but how do we apply the same density retroactively to the quotation, since the density is not known at that stage?

What I’ve Tried So Far:

  • I attempted to add a custom density field to the sales order and delivery note and used a script to change the unit conversion (from mass to volume) based on the density.
  • However, this approach doesn’t work because the quotation also needs the same conversion factor, and we do not have the density at the time the quotation is created. This causes inconsistencies in the unit conversion factor between the quotation, sales order, and delivery note.

Has anyone encountered a similar issue when dealing with fluctuating density for mass-to-volume conversions? If so, could you please share your experience or suggest a solution that would allow me to handle these conversions accurately across all documents in ERPNext?

Any advice or solutions would be greatly appreciated!

Thank you in advance!

2 Likes
2

@maliknaqibullah
Why you don’t use a field like Estimated Density instead of the actual density during the initial stages (Quotation and Sales Order)…?? This way, you can provide approximate calculations based on the estimated density.
Then in the final Delivery Note you can update it with the Actual Density value to ensure accurate volume calculations.

1 Like
3

Thank you for the suggestion! Using an estimated density for the quotation and sales order makes sense for approximate calculations. However, my concern is that I purchase in tonnes and sell in liters, and need to deduct the correct volume from stock. Since the actual density is only known at the time of delivery, how do I accurately adjust inventory during the contract and sales order stages, before the actual density is available? Any advice on managing this in ERPNext would be appreciated.

4

Since the density fluctuates it’s best you don’t try to calculate the actual density of the product at the time of purchase. Instead just bring everything into the inventory in tonnes and make delivery in tonnes as well.

The thing is you don’t need the customer to know that you’re keeping inventory in tonnes.

You just use the correct density calculation (by adding any custom fields you might need for calculation) and using a script to reverse calculate the actual tonnes consumed for certain litres of the product.

Example, you bought 10 tonnes of Petrol at 25°C (and any other metrics you want to calculate any losses with). You create a batch for this with the link to the purchase receipt for fetching the density details if required for any reports.

Then you receive an order for 10000 litres of Petrol.
At the time of delivery you calculate the density and convert 10000 litres to the equivalent tonnes and reduce that qty from the inventory. The 10000 litres itself would be a custom field in the sales side which will be printed in all the customer facing documents, but the actual stock updated would be in tonnes.

This way, batch wise you’ll also be able to see the accurate gross profit for the product.

1 Like
5

Override Delivery Note validate event : import frappe
from erpnext.stock.doctype.delivery_note.delivery_note import DeliveryNote
from frappe import _

class CustomDeliveryNote(DeliveryNote):
def validate(self):
print(‘delivery note is called’)
self.validate_posting_time()

    super(DeliveryNote, self).validate()
    self.validate_references()
    self.set_status()
    self.so_required()
    self.validate_proj_cust()
    self.check_sales_order_on_hold_or_close("against_sales_order")
    self.validate_warehouse()
    self.validate_uom_is_integer("stock_uom", "stock_qty")
    self.validate_uom_is_integer("uom", "qty")
    self.set_serial_and_batch_bundle_from_pick_list()

    from erpnext.stock.doctype.packed_item.packed_item import make_packing_list

    make_packing_list(self)
    self.update_current_stock()

    if not self.installation_status:
        self.installation_status = "Not Installed"

    self.validate_against_stock_reservation_entries()
    self.reset_default_field_value("set_warehouse", "items", "warehouse");; it ignore the uom and conversion factor between both Sales Order and Delivery Note and add field of density in delivery note items after that write client script for calculation of density (kg/m^3) :

frappe.ui.form.on(‘Delivery Note Item’, {
uom: function(frm, cdt, cdn) {
update_conversion_factor(cdt, cdn);
},

qty: function(frm, cdt, cdn) {
    update_conversion_factor(cdt, cdn);
},

custom_density: function(frm, cdt, cdn) {
    update_conversion_factor(cdt, cdn);
}

});

// Function to calculate and set the conversion factor
function update_conversion_factor(cdt, cdn) {
let item = locals[cdt][cdn];
let uom = item.uom;
let density = item.custom_density; // Ensure this field exists

let conversion_factor = 0;

// Perform conversion logic based on UOM and density
if (uom === "Tonne") {
    conversion_factor = 1000 / density;
} else if (uom === "Litre") {
    conversion_factor = 1; // Assuming 1 for Litres
} else if (uom === "Kg") {
    conversion_factor = 1 / density;
}



// Set the calculated conversion factor to the field
frappe.model.set_value(cdt, cdn, 'conversion_factor', conversion_factor);

}
;;; this will caclulate qty out from stock it convert Mass(Tonne) to Volume (Litre)

1 Like