BOM Stock Report wrong data mapping

ERPNext Customize ERPNext
,
#1

Hi,
I’m trying to customize BOM Stock Report - remove few and add few columns.
Here’s screenshot of test BOM. Stock Qty should be 0 and Required Qty should be 2. Also, there’s no data for UoM column.

image
image1156×159 4.24 KB

Query field from stock_bom_report.json

"query": "SELECT \n\tbom_item.item_code as \"Item:Link/Item:75\",\n\tbom_item.item_name as \"Name:Data:300\",\n\tbom_item.description as \"Description:Data:300\",\n\tbom_item.qty as \"Required Qty:Float:100\",\n\tledger.actual_qty as \"Stock Qty:Float:100\"\nFROM\n\ttabBOM ItemAS bom_item \n\tLEFT JOINtabBin AS ledger\t\n\t\tON bom_item.item_code = ledger.item_code \n\t\tAND ledger.warehouse = %(warehouse)s\nWHERE\n\tbom_item.parent=%(bom)s\n\nGROUP BY bom_item.item_code",

Get columns method from stock_bom_report.py

def get_columns():

    """return columns"""

    columns = [

        _("Item") + ":Link/Item:75",

        _("Name") + "::300",

        _("Description") + "::300",

        _("BOM Qty") + ":Float:100",

        _("Required Qty") + ":Float:100",

        _("Stock Qty") + ":Float:100",

        _("UoM") + "::160",

    ]

    return columns

SQL query from bom_stock_report.py

return frappe.db.sql("""

            SELECT

                bom_item.item_code,

                bom_item.item_name,

                bom_item.description ,

                bom_item.{qty_field},

                bom_item.stock_uom,

                bom_item.{qty_field} * {qty_to_produce} / bom.quantity,

                sum(ledger.actual_qty) as actual_qty,

                sum(FLOOR(ledger.actual_qty / (bom_item.{qty_field} * {qty_to_produce} / bom.quantity)))

            FROM

                `tabBOM` AS bom INNER JOIN {table} AS bom_item

                    ON bom.name = bom_item.parent

                LEFT JOIN `tabBin` AS ledger

                    ON bom_item.item_code = ledger.item_code

                {conditions}

            WHERE

                bom_item.parent = '{bom}' and bom_item.parenttype='BOM'

            GROUP BY bom_item.item_code""".format(

                qty_field=qty_field,

                table=table,

                conditions=conditions,

                bom=bom,

                qty_to_produce=qty_to_produce or 1)

            )

Table from bom_stock_report.html

<table class="table table-bordered">

    <thead>

        <tr>

            <th style="width: 15%">{%= __("Item") %}</th>

            <th style="width: 15%">{%= __("Name") %}</th>

            <th style="width: 35%">{%= __("Description") %}</th>

            <th style="width: 14%">{%= __("Required Qty") %}</th>

            <th style="width: 13%">{%= __("Stock Qty") %}</th>

        </tr>

    </thead>

    <tbody>

        {% for(var i=0, l=data.length; i<l; i++) { %}

        <tr>

            <td>{%= data[i][ __("Item")] %}</td>

            <td>{%= data[i][ __("Name")] %} </td>

            <td>{%= data[i][ __("Description")] %} </td>

            <td align="right">{%= data[i][ __("Required Qty")] %} </td>

            <td align="right">{%= data[i][ __("Stock Qty")] %} </td>

        </tr>

        {% } %}

    </tbody>

</table>

How to fix it?

#2

Anyone?