CREATE PROCEDURE `sp_pop_good_receipt_details`(`a_PO_ReceiptID` INT, `a_user_id` INT, `a_good_inout` VARCHAR(2))
BEGIN

  SET sql_mode = '';
	
	If a_good_inout = 'R' 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,
				updated_at, updated_by
				)
		SELECT	pori.item_id, it.item_code, por.id, pori.id,
				por.purchase_date, pori.batch_no, pori.lot_no,
				DATE_FORMAT(CURRENT_DATE, '%Y%m'), 
					(
					Select 	IFNULL(Max(good_receipt_serial_number), 0) + 1
					FROM		good_receipt_details grd
					WHERE		grd.item_id  = it.id
					), a_good_inout,
				pori.manufacture_date, por.purchase_date, pori.expiry_date, pori.expiry_note,
				pori.receive_quantity, pori.mrp,
			CURRENT_TIMESTAMP, a_user_id
		FROM 	purchase_order_receipts por, purchase_order_receipt_items pori, items it
		WHERE	por.id = pori.po_receipt_id
		AND		pori.item_id = it.id
		AND 	por.id = a_PO_ReceiptID
		AND		por.status = 'U'
		AND		pori.status = 'Y';

	
	ElseIf a_good_inout = 'E' 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,
				updated_at, updated_by
				)
		SELECT	pori.item_id, it.item_code, por.id, pori.id,
				por.return_date, pori.batch_no, pori.lot_no,
				DATE_FORMAT(CURRENT_DATE, '%Y%m'), 
					(
					Select 	IFNULL(Max(good_receipt_serial_number), 0) + 1
					FROM		good_receipt_details grd
					WHERE		grd.item_id  = it.id
					), a_good_inout,
				ip.manufacture_date, CURRENT_DATE, ip.expiry_date, null,
				pori.quantity, ip.mrp,
			CURRENT_TIMESTAMP, a_user_id
		FROM 	purchase_order_returns por, purchase_order_return_items pori, items it, purchase_order_receipt_items ip
		WHERE	por.id = pori.purchase_order_return_id
		AND		pori.item_id = it.id
		AND		ip.id = pori.po_receipt_item_id
		AND 	por.id = a_PO_ReceiptID
		AND		por.status = 'U'
		AND		pori.status = 'Y';

	
	ElseIf a_good_inout = 'S' 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, reserve_for_loose_sale, loose_item_qty,
				updated_at, updated_by
				)
		SELECT	pori.item_id, it.item_code, por.id, pori.id,
				por.bill_date, pori.batch_no, '',
				DATE_FORMAT(CURRENT_DATE, '%Y%m'), 0, a_good_inout,
				null, por.bill_date, pori.expire_date, null,
				
				Case when is_loose_sold = 'N' then pori.sale_quantity else round(pori.sale_quantity/strip_qty, 4 ) end,	pori.mrp, is_loose_sold, 
				Case when is_loose_sold = 'N' then 0 else pori.sale_quantity end,
				
				CURRENT_TIMESTAMP, a_user_id
		FROM 	pharmacy_billing_header por, pharmacy_billing_item pori, items it, pharmacy_items pi
		WHERE	por.id = pori.pharmacy_billing_header_id
		AND		pori.item_id = it.id
		AND		pi.item_id = it.id
		AND 	por.id = a_PO_ReceiptID;
	
	
	ElseIf a_good_inout = 'T' 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, reserve_for_loose_sale, loose_item_qty,
				updated_at, updated_by
				)
		SELECT	pori.item_id, it.item_code, por.id, pori.id,
				por.return_date, pori.batch_no, '',
				DATE_FORMAT(CURRENT_DATE, '%Y%m'), 0, a_good_inout,
				null, por.return_date, null, null,
				
				Case when is_loose_sold = 'N' then pori.return_quantity else round(pori.return_quantity/strip_qty, 4 ) end, pori.mrp, is_loose_sold, 
				Case when is_loose_sold = 'N' then 0 else pori.return_quantity end,
				
				CURRENT_TIMESTAMP, a_user_id
		FROM 	pharmacy_return_header por, pharmacy_return_items pori, items it, pharmacy_items pi
		WHERE	por.id = pori.pharmacy_return_header_id
		AND		pori.item_id = it.id
		AND		pi.item_id = it.id
		AND 	por.id = a_PO_ReceiptID
		AND		pori.status In ('U', 'Y');
		
		ElseIf a_good_inout = 'C' 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,
				updated_at, updated_by
				)
		SELECT
			organization_assets.item_id,
			organization_assets.asset_code AS item_code,
			good_receipt_details.po_receipts_id,
			good_receipt_details.po_receipts_item_id,
			good_receipt_details.good_receipt_date,
			good_receipt_details.batch_number,
			good_receipt_details.lot_number,
			DATE_FORMAT( CURRENT_DATE, '%Y%m' ) AS grn_number,
			(
				Select 	IFNULL(Max(good_receipt_serial_number), 0) + 1
				FROM		good_receipt_details grd
				WHERE		grd.item_id  = organization_assets.item_id
			),
			a_good_inout AS inout_status,
			good_receipt_details.manufactured_date,
			CURRENT_DATE AS entered_on,
			good_receipt_details.expire_on,
			NULL AS expire_note,
			COUNT(*) AS item_qty,
			good_receipt_details.item_mrp,
			CURRENT_DATE AS updated_at,
			a_user_id
		FROM
			consumed_assets
			JOIN organization_assets ON organization_assets.id = consumed_assets.asset_id
			JOIN good_receipt_details ON good_receipt_details.id = organization_assets.good_receipt_details_id 
		WHERE
			consumed_assets.consumption_id = a_PO_ReceiptID 
			AND consumed_assets.type = 'O' 
			AND consumed_assets.STATUS = 'U' 
		GROUP BY
			good_receipt_details.po_receipts_id;
			
			ElseIf a_good_inout = 'CR' 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,
				updated_at, updated_by
				)
		SELECT
			organization_assets.item_id,
			organization_assets.asset_code AS item_code,
			good_receipt_details.po_receipts_id,
			good_receipt_details.po_receipts_item_id,
			good_receipt_details.good_receipt_date,
			good_receipt_details.batch_number,
			good_receipt_details.lot_number,
			DATE_FORMAT( CURRENT_DATE, '%Y%m' ) AS grn_number,
			(
				Select 	IFNULL(Max(good_receipt_serial_number), 0) + 1
				FROM		good_receipt_details grd
				WHERE		grd.item_id  = organization_assets.item_id
			),
			a_good_inout AS inout_status,
			good_receipt_details.manufactured_date,
			CURRENT_DATE AS entered_on,
			good_receipt_details.expire_on,
			NULL AS expire_note,
			COUNT(*) AS item_qty,
			good_receipt_details.item_mrp,
			CURRENT_DATE AS updated_at,
			a_user_id
		FROM
			consumed_assets
			JOIN organization_assets ON organization_assets.id = consumed_assets.asset_id
			JOIN good_receipt_details ON good_receipt_details.id = organization_assets.good_receipt_details_id 
		WHERE
			consumed_assets.consumption_return_id = a_PO_ReceiptID 
			AND consumed_assets.type = 'R' 
			AND consumed_assets.STATUS = 'U' 
		GROUP BY
			good_receipt_details.po_receipts_id;
	End If;

END