CREATE DEFINER=CURRENT_USER PROCEDURE `supplier_balance_rpt`(IN `start_date_filter` DATE, IN `end_date_filter` DATE, IN `supplier_id_filter` INT)
BEGIN

select  ROW_NUMBER() OVER (ORDER BY sum(scm_purchase_order_details.quantity)) AS Sqn, scm_suppliers.name ,
format(
 (sum(scm_purchase_order_details.quantity*scm_purchase_order_details.unit_price)) - ifnull(sum(case when scm_po_payments.currency =2 then scm_po_payments.payment_amount / scm_po_payments.exchange_rate else scm_po_payments.payment_amount end),0),2) as `Total` from scm_purchase_orders
join scm_purchase_order_details on scm_purchase_order_details.po_id = scm_purchase_orders.id
left join scm_po_payments on scm_po_payments.po_id = scm_purchase_orders.id
JOIN scm_products ON scm_products.id = scm_purchase_order_details.product
join scm_suppliers on scm_suppliers.id = scm_purchase_orders.supplier
where scm_purchase_orders.date between start_date_filter and end_date_filter
and (supplier_id_filter = 0 or scm_purchase_orders.supplier = supplier_id_filter)
group by supplier
union all 
select  '' AS Sqn, '' name ,
format(
 (sum(scm_purchase_order_details.quantity*scm_purchase_order_details.unit_price)) - ifnull(sum(case when scm_po_payments.currency =2 then scm_po_payments.payment_amount / scm_po_payments.exchange_rate else scm_po_payments.payment_amount end),0),2) as `Total` from scm_purchase_orders
join scm_purchase_order_details on scm_purchase_order_details.po_id = scm_purchase_orders.id
left join scm_po_payments on scm_po_payments.po_id = scm_purchase_orders.id
join scm_suppliers on scm_suppliers.id = scm_purchase_orders.supplier
JOIN scm_products ON scm_products.id = scm_purchase_order_details.product
where scm_purchase_orders.date between start_date_filter and end_date_filter
and (supplier_id_filter = 0 or scm_purchase_orders.supplier = supplier_id_filter);


END
