DROP PROCEDURE if EXISTS	sp_transfer_for_loose_sell;
/*
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
*/
CREATE PROCEDURE sp_transfer_for_loose_sell(good_receipt_id INT, trf_qty int, strip_qty int, a_user_id INT, a_good_inout varchar(2))
BEGIN

	If a_good_inout = 'ST' Then		
		INSERT INTO good_receipt_details
				(
				item_id, item_code, po_receipts_id, po_receipts_item_id, 
				good_receipt_date, batch_number, lot_number,
				grn_number, good_receipt_serial_number, inout_status,
				manufactured_date, entered_on, expire_on, expire_note, 
				item_qty, item_mrp, loose_item_qty, reserve_for_loose_sale,
				updated_at, updated_by
				)
		SELECT	item_id, item_code, po_receipts_id, po_receipts_item_id, 
				CURRENT_DATE, batch_number, lot_number,
				grn_number, good_receipt_serial_number, 'ST',
				manufactured_date, entered_on, expire_on, expire_note, 
				trf_qty, item_mrp, 0, 'N',
				CURRENT_TIMESTAMP, a_user_id
		FROM 	good_receipt_details
		WHERE	id = good_receipt_id;

		INSERT INTO good_receipt_details
				(
				item_id, item_code, po_receipts_id, po_receipts_item_id, 
				good_receipt_date, batch_number, lot_number,
				grn_number, good_receipt_serial_number, inout_status,
				manufactured_date, entered_on, expire_on, expire_note, 
				item_qty, item_mrp, loose_item_qty,	reserve_for_loose_sale,
				updated_at, updated_by
				)
		SELECT	item_id, item_code, po_receipts_id, po_receipts_item_id, 
				CURRENT_DATE, batch_number, lot_number,
				grn_number, good_receipt_serial_number, 'SR',
				manufactured_date, entered_on, expire_on, expire_note, 
				trf_qty, item_mrp, trf_qty * strip_qty, 'Y',
				CURRENT_TIMESTAMP, a_user_id
		FROM 	good_receipt_details
		WHERE	id = good_receipt_id;

	ElseIf	a_good_inout = 'SE' Then		
		INSERT INTO good_receipt_details
				(
				item_id, item_code, po_receipts_id, po_receipts_item_id, 
				good_receipt_date, batch_number, lot_number,
				grn_number, good_receipt_serial_number, inout_status,
				manufactured_date, entered_on, expire_on, expire_note, 
				item_qty, item_mrp, loose_item_qty, reserve_for_loose_sale,
				updated_at, updated_by
				)
		SELECT	item_id, item_code, po_receipts_id, po_receipts_item_id, 
				CURRENT_DATE, batch_number, lot_number,
				grn_number, good_receipt_serial_number, 'R',
				manufactured_date, entered_on, expire_on, expire_note, 
				trf_qty, item_mrp, 0, 'N',
				CURRENT_TIMESTAMP, a_user_id
		FROM 	good_receipt_details
		WHERE	id = good_receipt_id;

		INSERT INTO good_receipt_details
				(
				item_id, item_code, po_receipts_id, po_receipts_item_id, 
				good_receipt_date, batch_number, lot_number,
				grn_number, good_receipt_serial_number, inout_status,
				manufactured_date, entered_on, expire_on, expire_note, 
				item_qty, item_mrp, loose_item_qty,	reserve_for_loose_sale,
				updated_at, updated_by
				)
		SELECT	item_id, item_code, po_receipts_id, po_receipts_item_id, 
				CURRENT_DATE, batch_number, lot_number,
				grn_number, good_receipt_serial_number, 'SE',
				manufactured_date, entered_on, expire_on, expire_note, 
				trf_qty, item_mrp, trf_qty * strip_qty, 'Y',
				CURRENT_TIMESTAMP, a_user_id
		FROM 	good_receipt_details
		WHERE	id = good_receipt_id;
	END If;
END;