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


CREATE PROCEDURE [dbo].[CP_Picking_Preparar_Sinc]	@pId_Trans	INT
AS
/*Declaracion Variables*/
DECLARE @strSQL					NVARCHAR(MAX)
DECLARE @Picking_Est_Pend		INT
DECLARE @Picking_Est_Parc		INT
DECLARE @tiposCbtesPicking		VARCHAR(40)
DECLARE @filtro_Flag_QA			VARCHAR(40)
DECLARE @Id_Cliente				INT
/*FINDeclaracion Variables*/

SET @Picking_Est_Pend	= (dbo.CF_xParam('Picking_Est_Pend'))
SET @Picking_Est_Parc	= (dbo.CF_xParam('Picking_Est_Parc'))
SET @tiposCbtesPicking	= (dbo.CF_xParam('Cbtes_Picking'))
SET @filtro_Flag_QA		= (dbo.CF_xParam('Picking_EsQACajDisp'))
SET @Id_Cliente			= (SELECT ISNULL(Id_Cliente, 0) FROM Cbtes WHERE Id_Trans = @pId_Trans)


DELETE  Picking_Cajas_Disp WHERE Id_Producto IN (SELECT I.ID_PRODUCTO FROM Picking_Cbtes_Items I, Picking_Cbtes C
       WHERE I.ID_TRANS = C.ID_TRANS AND C.Id_Estado_Cbte = 3)

DELETE  Picking_Cbtes_Items	FROM Picking_Cbtes_Items I, Picking_Cbtes C WHERE I.ID_TRANS = C.ID_TRANS AND C.Id_Estado_Cbte = 3

DELETE  Picking_Cbtes  WHERE Id_Estado_Cbte = 3

SET @strSQL =	' INSERT INTO Picking_Cbtes ' +
				' ( ' +
				' Id_Trans, Comprobante_Nro, Id_Estado_Cbte, Id_Tipo_Cbte, ' +
				' Id_Dep_Fisico, Id_Dep_Logico, Destinatario, ' +
				' Id_Cliente, NroImpresion' +
				' ) ' +
				' SELECT ' +
				' Id_Trans, Comprobante_Nro, 31 AS Id_Estado_Cbte, Id_Tipo_Cbte, ' +
				' Id_Dep_Fisico, Id_Dep_Logico, Destinatario, '+
				' ISNULL(Id_Cliente, 0) AS Id_Cliente, ' +
				' ISNULL(NroImpresion, 0) AS NroImpresion ' +
				' FROM Cbtes ' +
				' WHERE ' +
				' Id_Estado_Cbte IN (' + CONVERT(VARCHAR(10), @Picking_Est_Pend) +', ' + CONVERT(VARCHAR(10), @Picking_Est_Parc) + ') ' +
				' AND Id_Tipo_Cbte IN ' + @tiposCbtesPicking +
				' AND Id_Trans = ' + CONVERT(VARCHAR(10), @pId_Trans)

EXEC sp_executesql @strSQL
     
SET @strSQL =   ' UPDATE CBTES SET ID_ESTADO_CBTE = 29 WHERE Id_Trans = ' + CONVERT(VARCHAR(10), @pId_Trans)

EXEC sp_executesql @strSQL

SET @strSQL =	' INSERT INTO Picking_Cajas_Disp ' +
				' ( ' +
				' Id_Cliente, Id_Producto, Analisis, ' +
				' Lote, Serie, Stock ' +
				' ) ' +
				' SELECT ISNULL(I.Id_Cliente, 0) AS Id_Cliente, I.Id_Producto, I.Analisis, I.Lote, I.Serie, SUM(I.Cantidad * T.Compromete_Stock) AS Stock ' +
				' FROM Cbtes_Items I, Cbtes C, Cbtes_tipos T ' + 
				' WHERE ' +
				' C.Id_Trans = I.Id_Trans ' +
				' AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte ' +
				' AND T.Compromete_Stock IN (1,-1) ' +
				' AND I.Id_Producto IN (SELECT DISTINCT Id_Producto FROM Cbtes_Items P WHERE P.Id_Estado_Cbte Not in (32,2,3) And Id_Trans = ' + CONVERT(VARCHAR(10), @pId_Trans) + ') ' +
				' AND (ISNULL(I.Id_Cliente,0) = 0 OR I.Id_Cliente = ' + CONVERT(VARCHAR(10), @Id_Cliente) + ') ' +
				' AND I.Id_Estado_Cbte <> ' + CONVERT(VARCHAR(10), @Picking_Est_Parc) +
				' AND ((C.Id_Tipo_Cbte NOT IN (''PICK'',''PIKT'',''PIKB'') AND C.Id_Estado_Cbte NOT IN (3, 31)) OR (C.Id_Tipo_Cbte IN (''PICK'',''PIKT'',''PIKB'') AND I.Id_Estado_Cbte = 2)) ' +
				' AND C.Id_Estado_Cbte <> 3 ' +
				' AND I.Flag_QA IN (' + @filtro_Flag_QA +') ' +
				' GROUP BY ISNULL(I.Id_Cliente, 0), I.Id_Producto, I.Analisis, I.Lote, I.Serie ' +
				' HAVING Sum(I.Cantidad * T.Compromete_Stock) > 0'

EXEC sp_executesql @strSQL

SET @strSQL =	' INSERT INTO Picking_Cbtes_Items ' +				
				' ( ' +
				' Id_Trans, Item, Id_Estado_Cbte, ' +
				' Id_Producto, Cantidad, Analisis, ' +
				' Observacion, Id_Unidad_Medida, Calle, ' +
				' Columna, Fila, Lote, Serie ' +
				' ) ' +
				' SELECT ' +
				' Id_Trans, Item, Id_Estado_Cbte, ' +
				' Id_Producto, Cantidad, LTRIM(RTRIM(Analisis)) AS Analisis, ' +
				' Observacion, Id_Unidad_Medida, Calle, ' +
				' Columna, Fila, Lote, LTRIM(RTRIM(Serie)) AS Serie ' +
				' FROM Cbtes_Items ' +
				' WHERE ' +
				' Id_Estado_Cbte = 1 ' +
				' AND Id_Trans = ' + CONVERT(VARCHAR(10), @pId_Trans)

EXEC sp_executesql @strSQL		


--UPDATE CBTES SET Id_Estado_Cbte = 31 WHERE ID_TRANS = @pId_Trans 


--update PICKING_cbtes set id_estado_cbte = 3 where id_trans = 184684

--exec CP_Picking_Preparar_Sinc 184684

--select * from Picking_Cbtes

--select * from Picking_Cbtes_Items

--select * from Picking_Cajas_Disp

--delete Picking_Cbtes

--delete Picking_Cbtes_Items

--delete Picking_Cajas_Disp