DROP PROCEDURE sp_stock_item_requisition;

CREATE PROCEDURE sp_stock_item_requisition(a_purchase_requisitions_id int)
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE	v_item_id INT;
	DECLARE	v_bal_date DATE;
	DECLARE	v_quantity DECIMAL(12, 2);
	DECLARE	v_prev_tot_quantity DECIMAL(12, 2);
	DECLARE	v_prev_req_qty DECIMAL(12, 2);
	DECLARE	v_prev_issue_qty	DECIMAL(12, 2);

	DECLARE cur_stock_item_requisition CURSOR FOR 
		SELECT	item_id, quantity
		FROM	purchase_requisitions pr, purchase_requisitions_details prd
		WHERE	pr.id = prd.purchase_requisition_id
		AND		pr.id = a_purchase_requisitions_id
		AND		status = 'U'
		AND		is_deleted = 'N';

	/*DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  */
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	OPEN cur_stock_item_requisition; 
	read_loop: LOOP
		FETCH cur_stock_item_requisition INTO	v_item_id, v_quantity;

		IF done = 1 THEN 
			LEAVE read_loop;
		END IF;

		SELECT 	Max(stock_date)
		INTO	v_bal_date
		FROM	stock_item_requisition
		WHERE	item_id = v_item_id;
		
		/*	select v_bal_date, v_item_id;*/
		IF v_bal_date IS NULL THEN
			SET v_prev_tot_quantity	= 0.00;
			SET	v_prev_issue_qty = 0.00;
			SET	v_prev_req_qty = 0.00;
		ELSE
			SELECT	tot_quantity, issue_qty, req_qty
			INTO	v_prev_tot_quantity, v_prev_issue_qty, v_prev_req_qty
			FROM	stock_item_requisition
			WHERE	item_id = v_item_id
			AND		stock_date = v_bal_date;
		END IF;

		IF CURRENT_DATE = v_bal_date THEN
			UPDATE 	stock_item_requisition
			SET		req_qty = req_qty + v_quantity,
					tot_quantity = tot_quantity + v_quantity
			WHERE	item_id = v_item_id
			AND		stock_date = v_bal_date;
		ELSE
			INSERT INTO	stock_item_requisition
			(stock_date, item_id, req_qty, 
			issue_qty, tot_quantity)
			SELECT	CURRENT_DATE, v_item_id, v_prev_req_qty + v_quantity, 
			v_prev_issue_qty, v_prev_tot_quantity + v_quantity;
		END IF;
	END LOOP read_loop;

	CLOSE cur_stock_item_requisition; 
END;