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.
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\t
tabBOM ItemAS bom_item \n\tLEFT JOIN
tabBin 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?