SET @from_date= CURRENT_DATE;
SET @to_date= CURRENT_DATE;

SELECT 
	DATE(purchase_order_returns.return_date) as date,
	get_gst_in_out(0,0,'I',DATE(purchase_order_returns.return_date),'A') as gst_in,
	get_gst_in_out(0,0,'O',DATE(purchase_order_returns.return_date),'A') as gst_out

FROM 
	purchase_order_return_items	

INNER JOIN 
	purchase_order_returns 
	ON purchase_order_returns.id= purchase_order_return_items.purchase_order_return_id
	AND purchase_order_returns.`status`='A'

WHERE 
	purchase_order_return_items.`status`='Y'
	AND DATE(purchase_order_returns.return_date)>=@from_date
	AND DATE(purchase_order_returns.return_date)<=@to_date

UNION

SELECT 
	DATE(pharmacy_billing_header.bill_date) as date,
	get_gst_in_out(0,0,'I',DATE(pharmacy_billing_header.bill_date), 'A') as gst_in,
	get_gst_in_out(0,0,'O',DATE(pharmacy_billing_header.bill_date), 'A') as gst_out
	
FROM 
	pharmacy_billing_item	
	
INNER JOIN 
	pharmacy_billing_header 
	ON pharmacy_billing_header.id= pharmacy_billing_item.pharmacy_billing_header_id
	AND (pharmacy_billing_header.`status`='B' OR pharmacy_billing_header.`status`='P')
	
WHERE 
	pharmacy_billing_item.`status`='A'
	AND DATE(pharmacy_billing_header.bill_date)>=@from_date
	AND DATE(pharmacy_billing_header.bill_date)<=@to_date

UNION

SELECT 
	DATE(purchase_order_receipts.purchase_date) as date,
	get_gst_in_out(0,0,'I',DATE(purchase_order_receipts.purchase_date), 'A') as gst_in,
	get_gst_in_out(0,0,'O',DATE(purchase_order_receipts.purchase_date), 'A') as gst_out
	
FROM 
	purchase_order_receipt_items
	
INNER JOIN
	purchase_order_receipts 
	ON purchase_order_receipts.id= purchase_order_receipt_items.po_receipt_id
	AND purchase_order_receipts.`status`='A'
	
WHERE 
	purchase_order_receipt_items.`status`='Y'
	AND DATE(purchase_order_receipts.purchase_date)>=@from_date
	AND DATE(purchase_order_receipts.purchase_date)<=@to_date

UNION

SELECT 
	DATE(pharmacy_return_header.return_date) as date,
	get_gst_in_out(0,0,'I',DATE(pharmacy_return_header.return_date), 'A') as gst_in,
	get_gst_in_out(0,0,'O',DATE(pharmacy_return_header.return_date), 'A') as gst_out
	
FROM 
	pharmacy_return_items
	
INNER JOIN 
	pharmacy_return_header 
	ON pharmacy_return_header.id= pharmacy_return_items.pharmacy_return_header_id
	AND pharmacy_return_header.`status`='A'
	
WHERE 
	pharmacy_return_items.`status`='Y'
	AND DATE(pharmacy_return_header.return_date)>=@from_date
	AND DATE(pharmacy_return_header.return_date)<=@to_date

