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

SELECT 
	DATE(purchase_order_receipts.purchase_date) as date,
	suppliers.supplier_name,
	items.item_name,
	get_gst_in_out(items.id, purchase_order_receipts.supplier_id, 'O', DATE(purchase_order_receipts.purchase_date), 'P') 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'

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

LEFT JOIN 
	suppliers
	ON suppliers.id=purchase_order_receipts.supplier_id

WHERE 
	purchase_order_receipt_items.`status`='Y'
	AND purchase_order_receipts.purchase_date>= @from_date
	AND purchase_order_receipts.purchase_date<= @to_date

UNION

SELECT 
	DATE(pharmacy_return_header.return_date) as date,
	vw_pharmacy_patient.patient_name,
	items.item_name,
	get_gst_in_out(items.id, pharmacy_return_header.patient_id, 'O',DATE(pharmacy_return_header.return_date), 'PH') 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'

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

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

WHERE 
	pharmacy_return_items.`status`='Y'
	AND pharmacy_return_header.return_date>= @from_date
	AND pharmacy_return_header.return_date<= @to_date