IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CP_SCA_ValidarProducVta') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.CP_SCA_ValidarProducVta 
END
GO

CREATE PROCEDURE [dbo].[CP_SCA_ValidarProducVta] @pId_Cliente		INT, 
												@pId_Acceso			SMALLINT, 
												@pFecha				DATETIME, 
												@pId_Cliente_Ref	INT, 
												@pHorario			TINYINT, 
												@Id_Producto		VARCHAR(14) OUTPUT
AS
BEGIN
DECLARE @Flag_PermiteProdPorPed	INT
DECLARE @Est_Pedido_Habili		VARCHAR(8000)
DECLARE @SqlStr					NVARCHAR(MAX)
DECLARE @SqlStrNotNull			NVARCHAR(MAX)
DECLARE @Parametros				NVARCHAR(100)
DECLARE @DiaActual				TINYINT

--[CD_ACCESOS_HORARIOS].[Dia] = 0 -> Domingo ,1->Lunes ....
SET @DiaActual = DATEPART(DW,@pFecha)
IF (@@DATEFIRST = 7)
BEGIN
   SET @DiaActual = @DiaActual - 1
END

IF (@DiaActual = 7) 
BEGIN
	SET @DiaActual = 0
END

SET @Parametros = N'@Id_ProductoOUT VARCHAR(14) OUTPUT'; 

SET @Id_Producto	= ''

SET @Flag_PermiteProdPorPed = (dbo.CF_xParam('SCA_Perm_ProdXPed'))
SET @Est_Pedido_Habili		= RTRIM(LTRIM((dbo.CF_xParam('SCA_Est_Ped_Habil'))))

IF (ISNULL(@pId_Cliente_Ref,0) <> 0)
BEGIN
   SET @pId_Cliente = @pId_Cliente_Ref
END

SET @SqlStr = 'SELECT @Id_ProductoOUT = ISNULL(MAX(CI.Id_Producto),'''')'

IF(ISNULL(@pHorario,0) = 0)
BEGIN
   SET @SqlStr = RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
				' FROM Cbtes_Items CI, Cbtes CB, Cbtes_Tipos CT, CD_Accesos_Prod CA, Productos PR'
END
ELSE
BEGIN
   SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
					' FROM Cbtes_Items CI, Cbtes CB, Cbtes_Tipos CT, CD_Accesos_Horarios CA, Productos PR'
END


SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
				' WHERE' + CHAR(13) + 
				' CI.Id_Trans = CB.Id_Trans ' + CHAR(13) +
				' AND CT.Id_Tipo_Cbte = CB.Id_Tipo_Cbte' + CHAR(13) +
				' AND CA.Id_Acceso = '+ CAST(@pId_Acceso AS VARCHAR(5)) + CHAR(13) +
				' AND CA.Id_Producto = CI.Id_Producto' + CHAR(13) +
				' AND CI.Id_Cliente = '+ CAST(@pId_Cliente AS VARCHAR(10)) + CHAR(13) +
				' AND PR.Id_Producto = CA.Id_Producto '

IF @Flag_PermiteProdPorPed = 0                              ----incluismo pedidos o no
   SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
					' AND ((CT.Compromete_Factura = 1 ' + CHAR(13) + 
					' AND CB.Id_Estado_Cbte IN (4, 2))' + CHAR(13) + 
					' OR (CB.Id_Estado_Cbte IN (1) AND CI.Imp_Final = 0))' 
ELSE
   SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
					' AND ((CT.Compromete_Factura = 1 AND CB.Id_Estado_Cbte IN (4, 2))' +
					' OR (CT.Compromete_Factura = 1 AND CB.Id_Estado_Cbte IN (1) AND CI.Imp_Final = 0)' +
					' OR (CT.Compromete_Pedido = 1 AND CB.Id_Estado_Cbte IN (' + @Est_Pedido_Habili + ')))'

IF(ISNULL(@pHorario,0) = 0)
BEGIN
	IF(ISNULL(@pId_Cliente_Ref,0) = 0)
		SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
						' AND ISNULL(CA.Valida_En_Titular,0) = 0'
	ELSE
		SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
						' AND ISNULL(CA.Valida_En_Titular,0) = 1'
END



/*-----------------------------------------------------------------------------PRODUCTOS QUE NO VERIFICAN NI MES, NI PERIODO*/
SET @SqlStrNotNull =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
						' AND ISNULL(PR.Flag_Mes, 0) = 0 ' + CHAR(13) +  
						' AND ISNULL(PR.Flag_Periodo, 0) = 0'

IF (ISNULL(@pHorario,0)<>0)
	SET @SqlStrNotNull = RTRIM(LTRIM(@SqlStrNotNull)) + CHAR(13) + 
						 ' AND CA.Dia = ' + CAST(@DiaActual AS VARCHAR(1)) + CHAR(13) +
						 ' AND ((CA.Hora_Desde IS NULL AND CA.Hora_Hasta IS NULL) OR ( '''+ CONVERT(VARCHAR(8),@pFecha,108) +''' BETWEEN CAST(CA.Hora_Desde AS TIME) AND CAST(CA.Hora_Hasta AS TIME)))'
                       
PRINT @SqlStrNotNull
EXECUTE sp_executesql @SqlStrNotNull, @Parametros, @Id_ProductoOUT = @Id_Producto OUTPUT;
IF (@Id_Producto <> '')
   RETURN

/*--------------------------------------------------------------------------------------PRODUCTOS QUE VERIFICAN MES*/
SET @SqlStrNotNull =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
						' AND ISNULL(PR.Flag_Mes, 0) = 1'

IF (ISNULL(@pHorario,0)=0)
	SET @SqlStrNotNull = RTRIM(LTRIM(@SqlStrNotNull)) + CHAR(13) + 
						' AND dbo.SF_EOMONTH(CI.Fecha_QA) + CA.Dias_Gracia > ('''+ CONVERT(VARCHAR(8),@pFecha - 1,112) +''')'
ELSE
	SET @SqlStrNotNull = RTRIM(LTRIM(@SqlStrNotNull)) + CHAR(13) +
                         ' AND dbo.SF_EOMONTH(CI.Fecha_QA) > ('''+ CONVERT(VARCHAR(8),@pFecha - 1,112) +''')' + CHAR(13) +
                         ' AND CA.Dia = ' + CAST(@DiaActual AS VARCHAR(1)) + CHAR(13) +
						 ' AND ((CA.Hora_Desde IS NULL AND CA.Hora_Hasta IS NULL) OR ( '''+ CONVERT(VARCHAR(8),@pFecha,108) +''' BETWEEN CAST(CA.Hora_Desde AS TIME) AND CAST(CA.Hora_Hasta AS TIME)))'

PRINT @SqlStrNotNull
EXECUTE sp_executesql @SqlStrNotNull, @Parametros, @Id_ProductoOUT = @Id_Producto OUTPUT;
IF @Id_Producto <> ''
   RETURN


/*PRODUCTOS QUE VERIFICAN PERIODO*/
SET @SqlStrNotNull =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
						' AND ISNULL(PR.Flag_Periodo, 0) = 1'
						
IF (ISNULL(@pHorario,0)=0)
	SET @SqlStrNotNull =	RTRIM(LTRIM(@SqlStrNotNull)) + CHAR(13) + 
							' AND CI.Fecha_QA < ('''+ CONVERT(VARCHAR(8),@pFecha + 1,112) +''')' + CHAR(13) + 
							' AND CI.Fecha_Venc + CA.Dias_Gracia > ('''+ CONVERT(VARCHAR(8),@pFecha - 1,112) +''')'
ELSE
	SET @SqlStrNotNull = RTRIM(LTRIM(@SqlStrNotNull)) + CHAR(13) +
                         ' AND CI.Fecha_QA < ('''+ CONVERT(VARCHAR(8),@pFecha + 1,112) +''')' + CHAR(13) +
                         ' AND CI.Fecha_Venc > ('''+ CONVERT(VARCHAR(8),@pFecha - 1,112) +''')' + CHAR(13) +
                         ' AND CA.Dia = ' + CAST(@DiaActual AS VARCHAR(1)) + CHAR(13) +
						 ' AND ((CA.Hora_Desde IS NULL AND CA.Hora_Hasta IS NULL) OR ( '''+ CONVERT(VARCHAR(8),@pFecha,108) +''' BETWEEN CAST(CA.Hora_Desde AS TIME) AND CAST(CA.Hora_Hasta AS TIME)))'
                         

PRINT @SqlStrNotNull
EXECUTE sp_executesql @SqlStrNotNull, @Parametros, @Id_ProductoOUT = @Id_Producto OUTPUT;

RETURN
END