DROP PROCEDURE if EXISTS	sp_get_item_stock_details;

CREATE  PROCEDURE sp_get_item_stock_details(IN a_item_id INT, IN a_batch_no VARCHAR(50), IN a_flag VARCHAR(50))
BEGIN
	/*a_good_inout	=>	R-Good Receipt/Purchase, E-Return of Receipt/Purchase, T-Return from Pharmacy item / sell return, S-Sell of Pharmacy items, 
	ST-Stock Transfer for loose sell, SR-Stock Receipt for loose sell, SE-Loose Stock return to whole*/
	/*a_flag = B-Whole Batch, S-Whole Stock, LS-Loose stock & LB - Loose batch
	a_batch_no = X for receive all stock exclusive of any batch*/

	DECLARE v_item_qty_int DECIMAL (16, 4);
	DECLARE v_item_qty_out DECIMAL (16, 4);
	DECLARE v_item_mrp DECIMAL (12, 2);
	DECLARE v_item_indv_qty DECIMAL (12, 2);
	DECLARE v_item_expire_on DATE;
	DECLARE v_item_expire_note VARCHAR (1000);

	IF a_flag = 'B' Then

		Select 	distinct batch_number
		/*, po_receipts_id, good_receipt_serial_number*/
		FROM	good_receipt_details
		WHERE	item_id = a_item_id
		AND		inout_status = 'R'
		AND		stock_close_date is null
		AND		reserve_for_loose_sale = 'N'
		Order by batch_number;

	ELSEIF a_flag = 'LB' Then

		Select 	distinct batch_number
		/*, po_receipts_id, good_receipt_serial_number*/
		FROM	good_receipt_details
		WHERE	item_id = a_item_id
		AND		inout_status = 'SR'
		AND		stock_close_date is null
		AND		reserve_for_loose_sale = 'Y'
		Order by batch_number;

	ELSEIF a_flag = 'S' Then

		Select	IFNULL(sum(item_qty), 0), max(item_mrp), max(expire_on), max(expire_note), min(indv_item_qty)
		INTO		v_item_qty_int, v_item_mrp, v_item_expire_on, v_item_expire_note, v_item_indv_qty
		FROM		good_receipt_details, items
		WHERE		good_receipt_details.item_id = items.id
		AND			item_id = a_item_id
		AND			(batch_number = a_batch_no or a_batch_no = 'X')
		AND			inout_status In ('R', 'T')
		AND			reserve_for_loose_sale = 'N'
		AND			stock_close_date is null;

		Select	IFNULL(sum(item_qty), 0)
		INTO		v_item_qty_out
		FROM		good_receipt_details
		WHERE		item_id = a_item_id
		AND			(batch_number = a_batch_no or a_batch_no = 'X')
		AND			inout_status In ('E', 'S', 'ST')
		AND			reserve_for_loose_sale = 'N'
		AND			stock_close_date is null;

		SELECT	v_item_qty_int - v_item_qty_out stock_qty, v_item_mrp mrp, v_item_expire_on expire_date, v_item_expire_note expire_note, v_item_indv_qty item_indv_qty;

	ELSEIF a_flag = 'LS' Then

		Select	IFNULL(sum(loose_item_qty), 0), max(item_mrp), max(expire_on), max(expire_note), min(indv_item_qty)
		INTO	v_item_qty_int, v_item_mrp, v_item_expire_on, v_item_expire_note, v_item_indv_qty
		FROM	good_receipt_details, items
		WHERE	good_receipt_details.item_id = items.id
		AND		item_id = a_item_id
		AND		(batch_number = a_batch_no or a_batch_no = 'X')
		AND		inout_status In ('SR', 'T')
		AND		reserve_for_loose_sale = 'Y'
		AND		stock_close_date is null;

		Select	IFNULL(sum(loose_item_qty), 0)
		INTO	v_item_qty_out
		FROM	good_receipt_details
		WHERE	item_id = a_item_id
		AND		(batch_number = a_batch_no or a_batch_no = 'X')
		AND		reserve_for_loose_sale = 'Y'
		AND		inout_status In ('E', 'S', 'SE')
		AND		stock_close_date is null;

		SELECT	v_item_qty_int - v_item_qty_out stock_qty, v_item_mrp mrp, v_item_expire_on expire_date, v_item_expire_note expire_note, v_item_indv_qty item_indv_qty;

	End IF;

END