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


CREATE PROCEDURE [dbo].[CP_SCA_ValidarProducVta_Kit] @pId_Cliente		INT, 
													 @pId_Acceso		SMALLINT, 
													 @pFecha			DATETIME, 
													 @pId_Cliente_Ref	INT, 
													 @pHorario			TINYINT, 
													 @Id_Producto		VARCHAR(14) OUTPUT
AS
BEGIN

--======= Variables Locales =======--

DECLARE @Flag_PermiteProdPorPed	INT
DECLARE @Est_Pedido_Habili		VARCHAR(8000)
DECLARE @SqlStr					NVARCHAR(MAX)
DECLARE @SqlStrSelect			NVARCHAR(MAX)
DECLARE @SqlStrWhere			NVARCHAR(MAX)
DECLARE @Parametros				NVARCHAR(100)
DECLARE @DiaActual				TINYINT
DECLARE @ExisteRemito						INT
DECLARE @Cantidad_Saldo						INT
DECLARE @Fecha_QA							DATETIME
DECLARE @Fecha_Venc							DATETIME
DECLARE @Id_Tipo_Cbte						CHAR(4)
DECLARE @json								VARCHAR(8000)
DECLARE @Id_Trans_Rem						INT
DECLARE @Id_Trans_Fac						INT
DECLARE @Item								INT
DECLARE @Result_Stored_Invocado				INT
DECLARE @Result_Tabla_Stored_Invocado		TABLE (ErrorNumber	INT,
											ErrorSeverity	INT,
											ErrorState		INT,
											ErrorProcedure  VARCHAR(1000),
											ErrorLine		INT,
											ErrorMessage	VARCHAR(4000))
DECLARE @rtaErrorStored						INT
DECLARE @rtaMsjStored						VARCHAR(4000)
DECLARE @JSON_Parametros_Stored_Invocado	VARCHAR(8000)
DECLARE @rta_JSON_Stored_Invocado			VARCHAR(8000)

--======= Fin Variables Locales =======--

	PRINT 'Ingreso a Stored Procedure: CP_SCA_ValidarProducVta_Kit'

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

--===================================== Verifica si tiene un producto Pack Comprado =====================================--

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


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


SET @SqlStrWhere =	' 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 /*Se incluye pedido o no*/
BEGIN
   SET @SqlStrWhere =	RTRIM(LTRIM(@SqlStrWhere)) + 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))' + CHAR(13) +
					' AND dbo.CF_NC_A_FC(CB.Id_Trans) = 0'
END
ELSE
BEGIN
   SET @SqlStrWhere =	RTRIM(LTRIM(@SqlStrWhere)) + CHAR(13) + 
					' AND ((CT.Compromete_Factura = 1 AND CB.Id_Estado_Cbte IN (4, 2))' + CHAR(13) +
					' OR (CT.Compromete_Factura = 1 AND CB.Id_Estado_Cbte IN (1) AND CI.Imp_Final = 0)' + CHAR(13) +
					' OR (CT.Compromete_Pedido = 1 AND CB.Id_Estado_Cbte IN (' + @Est_Pedido_Habili + ')))'+ CHAR(13) +
					' AND ISNULL((SELECT MAX(canr.id_trans)
					  FROM Cbtes_Aplica CAFR, Cbtes_Aplica CANR, Cbtes_Tipos CTF, Cbtes_Tipos CTN, Cbtes CBF, Cbtes CBN
					  WHERE 
					  CAFR.Id_Trans = CB.ID_TRANS
					  AND CAFR.id_trans_d = CANR.id_trans_d
					  AND CAFR.Id_Trans = CBF.Id_Trans
					  AND CTF.Id_Tipo_Cbte = CBF.Id_Tipo_Cbte
					  AND CTF.Compromete_Factura = 1
					  AND CANR.Id_Trans = CBN.Id_Trans
					  AND CTN.Id_Tipo_Cbte = CBN.Id_Tipo_Cbte
					  AND CTN.Compromete_Factura = -1
					  AND CAFR.Importe = CANR.Importe),0) = 0'
END

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

IF (ISNULL(@pHorario,0) = 0)
BEGIN
	SET @SqlStrWhere =	RTRIM(LTRIM(@SqlStrWhere)) + CHAR(13) + 
						' AND CA.Flag_Consumible = 1'
END

SET @SqlStr = RTRIM(LTRIM(@SqlStrSelect)) + CHAR(13) + RTRIM(LTRIM(@SqlStrWhere))
PRINT @SqlStr 

EXECUTE sp_executesql @SqlStr, @Parametros, @Id_ProductoOUT = @Id_Producto OUTPUT;

--===================================== Verifica que exista un remito para ese d├¡a =====================================--

IF (@Id_Producto <> '')
BEGIN
	SET @ExisteRemito = ISNULL((SELECT COUNT(*) FROM Cbtes C, Cbtes_Tipos T, Cbtes_Items I 
									WHERE C.Id_Tipo_Cbte = T.Id_Tipo_Cbte 
									AND C.Id_Trans=I.Id_Trans
									AND T.Compromete_Remitos IN (1, -1)
									AND C.Id_Estado_Cbte = 2
									AND I.Id_Producto = @Id_Producto
									AND I.Id_Cliente = @pId_Cliente
									AND CONVERT(DATE, C.Fecha) = CONVERT(DATE, @pFecha)),0)
	IF (@ExisteRemito > 0)
	BEGIN
		PRINT 'Existe remito para el d├¡a actual'
		RETURN
	END
	ELSE
	BEGIN
		PRINT 'Ingreso a Generacion de Remito'
		
		SELECT TOP 1 @Cantidad_Saldo = ISNULL(MAX(CI.Cantidad_Saldo),0), 
					 @Id_Trans_Fac = CB.Id_Trans,
					 @Fecha_QA = ISNULL(CI.Fecha_QA,'01011900'),
					 @Fecha_Venc = ISNULL(CI.Fecha_Venc,'01011900'),
					 @Item = CI.Item
				FROM Cbtes_Items CI, Cbtes CB, Cbtes_Tipos CT
				WHERE CI.Id_Trans = CB.Id_Trans 
					AND CT.Id_Tipo_Cbte = CB.Id_Tipo_Cbte
					AND CI.Id_Cliente = @pId_Cliente
					AND CI.Id_Producto = @Id_Producto
					AND ((CT.Compromete_Factura = 1 AND CB.Id_Estado_Cbte IN (4, 2))
					OR (CB.Id_Estado_Cbte IN (1) AND CI.Imp_Final = 0))
					AND CI.Cantidad_Saldo > 0													/*Saldo mayor a cero*/
					AND CONVERT(DATE,CI.Fecha_Venc) >= CONVERT(DATE,@pFecha)					/*Dentro del periodo del item*/
					AND CONVERT(DATE,CI.Fecha_QA) <= CONVERT(DATE,@pFecha)						/*Dentro del periodo del item*/
				GROUP BY CI.Cantidad_Saldo, CB.Id_Trans, CI.Fecha_QA, CI.Fecha_Venc,CI.Item
				ORDER BY CB.Id_Trans ASC

				--====================== Verifica que Cantida Saldo sea mayor a Cero / Genera Recibo ======================--
		IF (@Cantidad_Saldo > 0)
		BEGIN
			SET @Id_Tipo_Cbte	= (SELECT Id_Tipo_Cbte FROM CD_Accesos_Prod WHERE Id_Acceso = @pId_Acceso AND Id_Producto = @Id_Producto)

			SET @json =	'{'
				SET @json =	@json + '"FlagUsarTrans":"1"'
				SET @json =	@json +	',"ModoDebug":"0"'
				SET @json =	@json +	',"RtaMsjStored":"0"'
				SET @json =	@json +	',"Id_Usuario":"-1"'
				SET @json =	@json +	',"Id_Tipo_Cbte":"' + CONVERT(VARCHAR(4), @Id_Tipo_Cbte ) +'"'
				SET @json =	@json +	',"Fecha":"' + CONVERT(VARCHAR(10), @pFecha, 112) +'"'
				SET @json =	@json +	',"Id_Cliente":"' + CONVERT(VARCHAR(10), @pId_Cliente) +'"'
				SET @json =	@json +	',"Fecha_Estimada":"' + CONVERT(VARCHAR(10), @pFecha, 112) +'"'
				SET @json =	@json +	',"Id_Cond_Pago":"10"'
				SET @json =	@json +	',"Lote":"DESCUENTO_PROD_SCA"'
				SET @json =	@json +	'}'

				SET @Id_Trans_Rem = 0
				DELETE @Result_Tabla_Stored_Invocado
	
				INSERT INTO @Result_Tabla_Stored_Invocado  
				EXEC @Result_Stored_Invocado = dbo.CPJ_Cbtes_Rem_Generar @json

				IF ISNULL(@Result_Stored_Invocado, 0) <> 0 
				BEGIN
					RAISERROR ('Error when calling CPJ_Cbtes_Rem_Generar', 16,  1 ) 
				END
				ELSE
				BEGIN
					SELECT TOP 1 @rta_JSON_Stored_Invocado = ErrorMessage FROM @Result_Tabla_Stored_Invocado
					SET @Id_Trans_Rem = ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@rta_JSON_Stored_Invocado,'Id_Trans')), 0)
				END

			PRINT '@Id_Trans_Rem: '+ CONVERT (VARCHAR (15), @Id_Trans_Rem)

			IF(@Id_Trans_Rem > 0)
				BEGIN
					SET @json =	'{'
					SET @json =	@json + '"FlagUsarTrans":"1"'
					SET @json =	@json +	',"ModoDebug":"0"'
					SET @json =	@json +	',"RtaMsjStored":"1"'
					SET @json =	@json +	',"Id_Trans":"' + CONVERT(VARCHAR(10), @Id_Trans_Rem) +'"'
					SET @json =	@json +	',"Id_Cliente":"' + CONVERT(VARCHAR(10), @pId_Cliente) +'"'
					SET @json =	@json +	',"Id_Producto":"' + CONVERT(VARCHAR(14), @Id_Producto) +'"'
					SET @json =	@json +	',"Cantidad":"1"'
					SET @json =	@json +	',"Fecha_QA":"' + CONVERT(VARCHAR(10), @pFecha, 112) +'"'
					SET @json =	@json +	',"Fecha_Venc":"' + CONVERT(VARCHAR(10), @pFecha, 112) +'"'
					SET @json =	@json +	',"Refacturar":"1"'
					SET @json =	@json +	'}'

					EXEC dbo.CPJ_Cbtes_Rem_Generar_Item @json

				END

			IF(@Id_Trans_Rem > 0)
				BEGIN
					SET @json =	'{'
					SET @json =	@json + '"FlagUsarTrans":"1"'
					SET @json =	@json +	',"ModoDebug":"0"'
					SET @json =	@json +	',"RtaMsjStored":"1"'
					SET @json =	@json +	',"Id_Trans":"' + CONVERT(VARCHAR(10), @Id_Trans_Rem) +'"'
					SET @json =	@json +	'}'

					EXEC dbo.CPJ_Cbtes_Rem_Fin @json

				END

			UPDATE Cbtes_Items SET Cantidad_Saldo -= 1 WHERE Id_Trans = @Id_Trans_Fac AND Id_Producto = @Id_Producto AND Id_Cliente = @pId_Cliente AND Item = @Item
			UPDATE Cbtes SET Id_Trans_Origen = @Id_Trans_Fac, Id_Estado_Cbte = 2 WHERE Id_Trans = @Id_Trans_Rem AND Id_Cliente = @pId_Cliente

																											

			INSERT INTO Cbtes_Aplica (Id_Trans,			Item,		Id_Trans_D,		Item_D,		
									  Cantidad,			Importe,	Fecha_Aplic)
							   VALUES(@Id_Trans_Rem,	1,			@Id_Trans_Fac,	1,
									  0,				0,			@pFecha)

			PRINT 'Fin Stored Procedure: CP_SCA_ValidarProducVta_Kit (Se Genera Remito Correctamente)'

			RETURN
		END
		ELSE
		BEGIN
			PRINT 'Fin Stored Procedure: CP_SCA_ValidarProducVta_Kit (No se genera remito; No tiene Cantidad Saldo Disponible o No se Encuentra en el Periodo)'
			SET @Id_Producto = ''
			RETURN
		END
	END
END

--============================================================================================================================================================

RETURN
END


/*
EXEC dbo.CP_SCA_ValidarProducVta_Kit 3840537, 
									 6, 
									 '20221128', 
									 0, 
									 0, 
									 ''
*/
