SELECT 'I' as Ind, inv.Item_Id as ID,inv.Item_Name as Item, inv.Reorder_Level as Level,  coalesce(SO.qty,0) as 'Sales Order\n Pending',  inv.Reorder_Level + COALESCE(SO.qty,0) as 'Required\n Total',  coalesce(stock,0) as 'Current Stock'  FROM $master_db.Sales_Inventory_Items inv  left outer join ( select itemID, round(sum(debitQty - creditQty),3) as stock from (  select trans.VoucherDate, ItemID,  sum(trans.Cr_Qty) as creditQty, sum(trans.Dr_Qty) as debitQty, 'purchase' as type  from $trans_db.Transaction_Item_Helper trans where VoucherDate <= str_to_date('$fromDateTime','%Y-%m-%d') group by ItemID UNION ALL  select trans.VoucherDate, ItemID,  sum(trans.Cr_Qty), sum(trans.Dr_Qty), 'sales' as type  from $trans_db.Transaction_Bom_Helper trans where VoucherDate <= str_to_date('$toDateTime','%Y-%m-%d') group by ItemID ) A group by itemID ) stk  on inv.item_id = stk.itemID  left outer join  ( select inventory_item_id, sum(qty) as qty from  (select inventory_item_id , sum(quantity) as qty from $trans_db.sales_order_details  where itemProductionStatus = 110 group by inventory_item_id union select ing.purchase_item, sum(quantity) * ing.purchase_item_qty / ing.sales_item_qty from  $trans_db.sales_order_Details inner join  $master_db.ingredients  ing on inventory_item_id = ing.sales_item  where itemProductionStatus = 110 group by purchase_item) X  group by inventory_item_id ) SO on inv.Item_ID = SO.inventory_item_id WHERE isPurchaseItem = 1 and coalesce(Reorder_Level ,0) + coalesce(SO.qty ,0) - coalesce(stock ,0) > 0  ORDER BY Item