
/****** Object:  StoredProcedure [dbo].[sp_REAP1_GetRetailerDataItem]   Script Date: 2020/07/02 09:10:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_REAP1_GetRetailerDataItem]	
	@EAN VARCHAR(MAX) = '',	
	@RetailerName VARCHAR(MAX) = '',
	@RowNum INT = 0	
AS
BEGIN
	--For testing:
	--DECLARE
	-- @EAN VARCHAR(MAX) = '',	
	-- @RetailerName VARCHAR(MAX) = 'TestRetailer',
	-- @RowNum int = 5		

	SET NOCOUNT ON;
	DECLARE @itemID INT = 0, @SurfaceID INT,@isPublished BIT = 0, @tableName VARCHAR(500) = '', 
	@query NVARCHAR(max) = '';
	SELECT @isPublished = isPublished FROM pal_Surface
	WHERE name = 'ProductAlternateEAN';
	IF @isPublished = 0
	BEGIN
		SET @tableName = 'qt_ProductAlternateEAN';
	END
	ELSE
	BEGIN
		SET @tableName = 'publ_ProductAlternateEAN';
	END;

	SET @query =  
	CONCAT('select distinct top(', CONVERT(varchar,@RowNum) ,') * ',
	'from publ_RetailerDataItems where RetailerDataItems_RetailerDataItems_Barcode in ',
	'(select ProductAlternateEAN_AlternateEANDetails_AlternateEAN from ', @tableName, 
	' where ParentSurfaceItemId in (select ParentSurfaceItemId from ', @tableName,
	' where ProductAlternateEAN_AlternateEANDetails_AlternateEAN = ', CHAR(39), @EAN, CHAR(39), ')) ',
	' and RetailerDataItems_RetailerDataItems_RetailerName = ', CHAR(39), @RetailerName,
	 CHAR(39), ' and isActive = 1');

	EXECUTE (@query);	
END
GO


