-- apps_master.spSelectProductPromoOTCRS (PROCEDURE)
BEGIN
	SELECT IFNULL(SupplyFrom, '981') 
	INTO @gudangid
	FROM apps_master.`m_outlet1` 
	WHERE OutletID = outletID1;
	SELECT 
		p.ProductName,
		p.ProductID,
		p.ProductPrice,
		p.ProductImage,
		pop.ProductPriceAfterDiscount, 
		p.ProductTypeClass AS ProductType,
		g.GenericName,
		-- IFNULL(ps.ProductStock,0) AS ProductStock,
		mpp.PackName,
		IFNULL(p.ProductSaleUnitConverted,0) AS PackUnit, 
		mpp1.PackName AS PackUnitName,
		CASE 
		WHEN IFNULL(p3.PharosYN, 'N') = 'Y' 
		THEN 
			CASE 
			WHEN ps.ProductStock IS NULL 
			THEN 9999
			WHEN ps.ProductStock - IFNULL(TD.TtlQty,0) <= 9999 
			THEN 9999
			ELSE ps.ProductStock - IFNULL(TD.TtlQty,0) 
			END 
		ELSE 
			CASE 
			WHEN ps.ProductStock IS NULL 
			THEN 0
			WHEN ps.ProductStock - IFNULL(TD.TtlQty,0) <= 0 
			THEN 0
			ELSE ps.ProductStock - IFNULL(TD.TtlQty,0)
			END 
		END AS ProductStock,
		p3.PharosYN
	FROM apps_master.product_outlet_promo_otc_rs pop 
	LEFT JOIN apps_master.m_product1 p 		ON pop.ProductID 	= p.ProductID
	LEFT JOIN apps_master.`m_product3` p3		ON p3.ProductID 	= p.ProductID
	LEFT JOIN apps_master.`m_prod_packing` mpp	ON mpp.PackID 		= p.MedPack
	LEFT JOIN apps_master.`m_prod_packing` mpp1	ON mpp1.PackID 		= p.ProductSalePack
	LEFT JOIN m_generic g 				ON p.GenericID 		= g.GenericID 
	LEFT JOIN apps_master.pharmanet_stock ps 	ON ps.OutletID = @gudangid AND ps.ProductID 	= pop.ProductID
	LEFT JOIN apps_master.`m_outlet1`o 		ON pop.`OutletID`	= o.`OutletID`
	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 p.ProductID = TD.productId
	WHERE 	
		p.ProductPrice != pop.ProductPriceAfterDiscount 
		AND 
		pop.OutletID = OutletID1
		AND
		CURDATE() BETWEEN CAST(pop.PeriodeStart AS DATE) AND CAST(pop.PeriodeEnd AS DATE)
		AND 
		(
			(
				o.Outlet_Type_ID != 1
				AND 
				p.`ProductID` NOT LIKE '01%'
				AND 
				p.`ProductID` NOT LIKE '02%'
				OR 
				p.`ProductID` IN(SELECT ProductID FROM apps_master.`product_white_list`)
			) 
			OR
			(
				o.Outlet_Type_ID = 1
			)
		)
	
	ORDER BY 
		RAND(), 
		(
			CASE 
			WHEN p.Divisi LIKE '%OT%' 
			THEN 1 
			ELSE 2 
			END
		)
	LIMIT 20 OFFSET NUMBER;
	
END