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

SELECT 
	DATE(purchase_order_returns.return_date) as date,
	suppliers.supplier_name,
	items.item_name,
	get_gst_in_out(items.id, purchase_order_receipts.supplier_id, 'I', DATE(purchase_order_returns.return_date), 'P') as gst_in

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'

INNER JOIN 
	items 
	ON items.id= purchase_order_return_items.item_id
	AND items.is_active='Y'

INNER JOIN 
	purchase_order_receipts 
	ON purchase_order_receipts.id= purchase_order_returns.po_receipt_id
	AND purchase_order_receipts.`status`='A'

LEFT JOIN 
	suppliers
	ON suppliers.id=purchase_order_receipts.supplier_id

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,
	vw_pharmacy_patient.patient_name,
	items.item_name,
	get_gst_in_out(items.id, pharmacy_billing_header.patient_id, 'I',DATE(pharmacy_billing_header.bill_date), 'PH') as gst_in

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')

INNER JOIN 
	items 
	ON items.id= pharmacy_billing_item.item_id
	AND items.is_active='Y'

INNER JOIN 
	vw_pharmacy_patient 
	ON vw_pharmacy_patient.id=pharmacy_billing_header.patient_id
	AND 
		CASE 
			WHEN pharmacy_billing_header.opd_ipd_ot_flag='W' THEN vw_pharmacy_patient.patient_type='W'
			WHEN pharmacy_billing_header.opd_ipd_ot_flag='O' THEN vw_pharmacy_patient.patient_type='E'
			WHEN pharmacy_billing_header.opd_ipd_ot_flag='I' THEN vw_pharmacy_patient.patient_type='E'
			WHEN pharmacy_billing_header.opd_ipd_ot_flag='T' THEN vw_pharmacy_patient.patient_type='E'
		END

WHERE 
	pharmacy_billing_item.`status`='A'
	AND DATE(pharmacy_billing_header.bill_date)>=@from_date
	AND DATE(pharmacy_billing_header.bill_date)<=@to_date