hello,
I want to create new custom query report, In which If there are 10 items in sales order of different quantities and in sales invoice of same sales order there is variation in quantity of items or lets say 7 or 8 items in sales invoice, in this situation i want to create custom report in which it should have following columns.
I wrote query which is partially working for following column but I’m having problem in SO quantity and Invoiced quantity. I want the a items of quantity which are in sales order in SO quantity column. the quantity of item which is invoiced among items which are in sales order and also the item which is present in sales order but not in sales invoice their quantity should be zero in Invoiced
quantity column
columns are
*Sr. no
*customer name
*customer PO number
*sales order number
*Item code
*description
*sales Order quantity
*sales Invoiced quantity
*pending quantity (SO qty-SI qty)
please suggest the changes in following query
thanks in advance
SELECT
`tabSales Order`.`customer_name` AS 'Customer Name::150',
`tabSales Order`.`po_no` AS 'Customer PO:130',
`tabSales Order`.`name` AS 'Sales Order:Link/Sales Order:120',
`tabSales Order Item`.item_code AS 'Item:Link/Item:120',
`tabSales Order Item`.description AS 'Description::200',
`tabSales Order Item`.qty AS 'Qty:Float:140',
`tabSales Invoice Item`.qty AS 'Invoiced Qty:Float:140',
(`tabSales Order Item`.qty - `tabSales Invoice Item`.qty) AS 'Pending Qty'
FROM
`tabSales Order`
JOIN
`tabSales Order Item`
JOIN
`tabSales Invoice Item`
LEFT JOIN
`tabBin` ON (`tabBin`.item_code = `tabSales Order Item`.item_code
AND `tabBin`.warehouse = `tabSales Order Item`.warehouse)
WHERE
`tabSales Invoice Item`.sales_order = `tabSales Order`.name
AND `tabSales Order Item`.`parent` = `tabSales Order`.`name`
AND `tabSales Invoice Item`.item_code = `tabSales Order Item`.item_code
AND `tabSales Order`.docstatus = 1