When do I use a conversion factor?

ERPNext Reports
,
1

I am creating a custom script report for my Sales Orders, so that I can see what is still outstanding to be delivered. When selecting a Sales Order Item, I can get the outstanding quantity by multiplying the ordered qty by the conversion_factor, then subtracting the delivered_qty:

so = frappe.qb.DocType('Sales Order')
so_item = frappe.qb.DocType('Sales Order Item')
item_query = (
    frappe.qb.from_(so)
    .inner_join(so_item).on(so.name == so_item.parent)
    .select(
       so.name.as_('sales_order'),
       Coalesce(so_item.delivery_date, so.delivery_date).as_('delivery_date'),
       (so_item.conversion_factor * so_item.qty - so_item.delivered_qty).as_('qty'),
    )
    .where(so.docstatus == DocStatus.SUBMITTED)
    .where(so.status.notin(['Closed', 'Completed', 'Cancelled']))
)

But Sales Order Item also has a stock_qty field, which appears (for my data at least) to always be conversion_factor * qty. Do I need to ever use conversion_factor?

I am using Frappe 16.31.0 and ERPNext 16.32.3

2

Hi,

qty and delivered_qty are always in the same UOM, so you can directly use:

qty - delivered_qty

If you need it in stock UOM, convert after subtracting:

(qty - delivered_qty) * conversion_factor
3

Example:

import frappe
from frappe.query_builder import DocStatus
from frappe.query_builder.functions import Coalesce


def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    return columns, data


def get_columns():
    return [
        {"label": "Sales Order", "fieldname": "sales_order", "fieldtype": "Link", "options": "Sales Order", "width": 150},
        {"label": "Delivery Date", "fieldname": "delivery_date", "fieldtype": "Date", "width": 110},
        {"label": "Item Code", "fieldname": "item_code", "fieldtype": "Link", "options": "Item", "width": 150},
        {"label": "Outstanding Qty", "fieldname": "qty", "fieldtype": "Float", "width": 120},
        {"label": "Stock UOM", "fieldname": "stock_uom", "fieldtype": "Link", "options": "UOM", "width": 100},
    ]


def get_data(filters):
    so = frappe.qb.DocType("Sales Order")
    so_item = frappe.qb.DocType("Sales Order Item")

    outstanding_qty = (so_item.qty - so_item.delivered_qty) * so_item.conversion_factor

    query = (
        frappe.qb.from_(so)
        .inner_join(so_item)
        .on(so.name == so_item.parent)
        .select(
            so.name.as_("sales_order"),
            Coalesce(so_item.delivery_date, so.delivery_date).as_("delivery_date"),
            so_item.item_code,
            outstanding_qty.as_("qty"),
            so_item.stock_uom,
        )
        .where(so.docstatus == DocStatus.SUBMITTED)
        .where(so.status.notin(["Closed", "Completed", "Cancelled"]))
        .where(outstanding_qty > 0)  # drop fully-delivered lines
    )

    return query.run(as_dict=True)