-- apps_master.getCurrentCartOwnerFinalChangePrice (PROCEDURE)
BEGIN
	SELECT 
		mp.ProductID, 
		mp.ProductName, 
		CASE 
			WHEN pop.ProductID IS NOT NULL 
			THEN pop.ProductPriceAfterDiscount 
			ELSE mp.ProductPriceAfterDiscount 
		END AS ProductPriceAfterDiscount,
		dco.CartProductQty,
		dco.CartProductPriceAfterDiscount,
		mp.GenericID,
		mg.GenericName, 
		mp.ProductTypeClass,
		mpp.PackName,
		IFNULL(mp.ProductSaleUnitConverted,0) AS PackUnit,
		IFNULL(ps.ProductStock,0) AS ProductStock 
	FROM apps_transaction.detail_cart_owner dco
	LEFT JOIN apps_transaction.header_cart_owner hco 	ON dco.CartID 		= hco.CartID
	LEFT JOIN apps_master.m_product1 mp 			ON dco.ProductID 	= mp.ProductID
	JOIN apps_master.m_prod_packing mpp 			ON mpp.PackID 		= mp.MedPack
	LEFT JOIN apps_master.m_generic mg 			ON mp.GenericID 	= mg.GenericID 
	LEFT JOIN apps_master.product_outlet_promo pop 		ON mp.ProductID 	= pop.ProductID AND pop.OutletID = outletID1
	LEFT JOIN apps_master.pharmanet_stock ps		ON ps.ProductID 	= mp.ProductID 
	LEFT JOIN 
		(
			SELECT 
				ProductID,
				SUM(OrderProductQty) AS TtlQty 
			FROM apps_transaction.detail_transaction_owner dto 
			JOIN apps_transaction.header_transaction_owner1 hto 	ON dto.OrderID 	= hto.OrderID
			WHERE 
				DATE(dto.lastupdated) = DATE(NOW()) 
				AND
				hto.OutletID NOT IN ('000','001','002') 
				AND 
				dto.OrderID NOT IN
				( 
					SELECT OrderID
					FROM apps_transaction.Log_DownloadOrder
				) 
			GROUP BY ProductID
		) AS TD ON dco.ProductID = TD.ProductID
	WHERE 
		hco.CartID = CartID2 
		AND 
		(
			(
				CASE 
					WHEN pop.ProductID IS NOT NULL 
					THEN pop.ProductPriceAfterDiscount 
					ELSE mp.ProductPriceAfterDiscount 
				END
			) != dco.CartProductPriceAfterDiscount 
			OR 
			IFNULL(ps.ProductStock,0) = 0
		)
	ORDER BY ProductStock DESC;
  	
END