DROP FUNCTION IF EXISTS get_item_stock;
CREATE FUNCTION get_item_stock(a_item_id INT,a_batch_no VARCHAR ( 50 ),a_flag VARCHAR ( 50 )) 
RETURNS INT 
DETERMINISTIC
BEGIN
	DECLARE 		v_item_qty_int DECIMAL ( 16, 4 );
	DECLARE	v_item_qty_out DECIMAL ( 16, 4 );
	IF
		a_flag = 'S' THEN
		SELECT
			IFNULL( sum( item_qty ), 0 ) INTO v_item_qty_int 
		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;
		
	ELSEIF a_flag = 'LS' THEN
		SELECT
			IFNULL( sum( loose_item_qty ), 0 ) INTO v_item_qty_int 
		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;
		
	END IF;
RETURN v_item_qty_int - v_item_qty_out;
END;