--USE [Inventory]
--GO
/****** Object:  StoredProcedure [dbo].[ConsSecc]    Script Date: 11/07/2016 14:50:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO












ALTER           PROCEDURE [dbo].[ConsSecc] @cod_desde varchar(20), 
												 @cod_hasta varchar(20), 
												 @list_alma varchar(8000), 
												 @list_secc varchar(8000), 
												 @fdesde datetime, 
												 @fhasta datetime, 
												 @consumo bit,
												 @resumido bit
AS

BEGIN
	-- GM:En vez de cantidad_d en compras y traslados, la calculé a partir
	-- de cantidad porque por un error en las actualizaciones de compras y
	-- traslados, no se actualiza la cantidad_d en todos los casos y de dejarlo así,
	-- las operaciones ya hechas nunca darían bien.

   -- sp que devuelve el cursor para el reporte Consumo Global de ALmacen / Secciones
   -- las cantidades van en um de almacen o seccion segun el caso
   -- no se puede tomar nada del submayor (salvo inv.inic., cambios de cod y de um),
   -- porque se perderian los consumos hacia secciones de gasto, de insumos, de NCE, etc

	-- Ver en Constotqisecc, los casos q se consideran consumo.
	-- Aqui se seleccionan los datos para las otras columnas del reporte. Solo
	-- no se incluyen aqui las rebajas, invent y aj neg, porque solo se cuentan
	-- en los consumos. El resto de las cosas de consumo, se cuenta en consumo
	-- y aqui (entrada-consumo)

   DECLARE @Cad varchar(8000),
	     	@filtro_arti varchar(200),
	       @filtro_destino varchar(8000),
	       @filtro_destino_multi varchar(8000),
	       @filtro_destino_multi1 varchar(8000),
	       @filtro_destino_elab varchar(8000),
			 @revertido int

   -- Preparo los parámetros
   Select @filtro_arti = ''
   If @cod_desde is not NULL and @cod_hasta is not NULL
      Select @filtro_arti = ' AND arti.FullArtiCode between "' + @cod_desde + '" and "' + @cod_hasta + '" '
   Else
      Begin 
         If @cod_desde is not NULL
            Select @filtro_arti = ' AND arti.FullArtiCode >= "' + @cod_desde + '" '
         Else
	   If @cod_hasta is not NULL
	      Select @filtro_arti = ' AND arti.FullArtiCode <= "' + @cod_hasta + '" '
      End
      
   Select @filtro_destino = '', @filtro_destino_multi = '', 
		@filtro_destino_multi1 = '' , @filtro_destino_elab = ''
	 if @list_alma = ''
		select @list_alma=null
	 if @list_secc = ''
		select @list_secc=null
   If @list_alma is not NULL and @list_secc is not NULL
      Select @filtro_destino = ' AND (smay.id_alma in (' + @list_alma + ') or smay.id_secc in (' + @list_secc + ')) ',
				@filtro_destino_elab = ' AND smay.id_secc in (' + @list_secc + ') ',
				@filtro_destino_multi = ' AND (smay.id_almad in (' + @list_alma + ') or smay.id_seccd in (' + @list_secc + ')) ',
				@filtro_destino_multi1 = ' AND (smay.id_almao in (' + @list_alma + ') or smay.id_secco in (' + @list_secc + ')) '
   Else
      Begin 
         If @list_alma is not NULL
            Select @filtro_destino = ' AND smay.id_alma in (' + @list_alma + ') ',
							@filtro_destino_multi = ' AND smay.id_almad in (' + @list_alma + ') ',
							@filtro_destino_multi1 = ' AND smay.id_almao in (' + @list_alma + ') '
         Else
	If @list_secc is not NULL
	   Select @filtro_destino = ' AND smay.id_secc in (' + @list_secc + ') ',
				@filtro_destino_elab = ' AND smay.id_secc in (' + @list_secc + ') ',
				@filtro_destino_multi = ' AND smay.id_seccd in (' + @list_secc + ') ',
				@filtro_destino_multi1 = ' AND smay.id_secco in (' + @list_secc + ') '
      End

	-- Crear tabla temporal
	CREATE TABLE #invactual (
			id_alma int, 
			id_secc int,
			id_arti int, 
			cod_arti varchar(20),
			desc_arti varchar(40),
			cantidad numeric(20,4),-- de 12 a 20
			importe numeric(18,2),-- de 12 a 18
			id_dmov int, 
			fec_oper datetime,  
			id_unme int,
			cant_um numeric(9,4) )

	-- Crear tabla temporal para saldos anteriores por arti y alma o secc 
CREATE TABLE #saldosant (
			id_alma int, 
			id_secc int,
			id_arti int, 
			sant_cantidad numeric(12,4),
			sant_importe numeric(12,2))

	-- Crear tabla temporal para consumos por arti y alma o secc 
	CREATE TABLE #consumos (
			id_alma int, 
			id_secc int,
			id_arti int, 
			cons_cant numeric(20,4), -- de 12 a 20
			cons_imp numeric(18,2))-- de 12 a 18

    -- Inventario Inicial (solo puede tomarse del submayor)
      Select @Cad = 'INSERT #invactual 
		SELECT smay.id_alma, smay.id_secc, smay.id_arti, 
			cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
			cantidad = coalesce(sini.cantidad,0.0000),
			importe=coalesce(sini.importe,0.00),
			smay.id_dmov, smay.fec_oper,  
			id_unme = case when smay.id_alma is not null 
										 then arti.id_unme_al else arti.id_unme_se end,
			cant_um = arti.cant_unme_al 
		FROM stoksmay smay
			INNER JOIN stoksini sini ON sini.id_smay = smay.id_smay
			INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
		WHERE smay.id_dmov=0 ' + @filtro_arti + @filtro_destino

	EXECUTE (@CAD)

	-- Compras
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, cmpl.id_arti, 
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(cmpl.cantidad*cant_unme_al*
										case when smay.id_alma is null 
											  then cant_unme_se else 1 end,0.0000),
		importe = coalesce(cmpl.importe,0.00)+coalesce(cmpl.imp_desgaste,0),
		id_dmov = 1, oper.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokcmpi smay
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
		INNER JOIN stokcmpl cmpl ON smay.id_cmpi=cmpl.id_cmpi
		INNER JOIN stokarti arti ON cmpl.id_arti = arti.id_arti 
		WHERE oper.estado=2 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Reversiones de Compras
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, cmpl.id_arti, 
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(-cmpl.cantidad*cant_unme_al*
										case when smay.id_alma is null 
											  then cant_unme_se else 1 end,0.0000),
		importe = coalesce(-cmpl.importe,0.00)+coalesce(-cmpl.imp_desgaste,0),
		id_dmov=2, rvtdor.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokcmpi smay
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
		INNER JOIN stokoper rvtdor on rvtdor.id_revertido=oper.id_operacion
		INNER JOIN stokcmpl cmpl ON smay.id_cmpi=cmpl.id_cmpi 
		INNER JOIN stokarti arti ON cmpl.id_arti = arti.id_arti
	WHERE cmpl.id_revertidor=rvtdor.id_operacion and rvtdor.estado=2 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Devoluciones
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, cmpl.id_arti, 
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(-cmpl.cantidad*
										case when smay.id_alma is null 
											  then cant_unme_se else 1 end,0.0000),
		importe = coalesce(-cmpl.importe,0.00)+coalesce(-cmpl.imp_desgaste,0),
		id_dmov=320, oper.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokdevi smay
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
		INNER JOIN stokdevl cmpl ON smay.id_devi=cmpl.id_devi
		INNER JOIN stokarti arti ON cmpl.id_arti = arti.id_arti 
		WHERE oper.estado=2 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	--Traslados recibidos
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_almad, smay.id_seccd, tral.id_arti,
		cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
		cantidad = coalesce(tral.cantidad*
									case when smay.id_almao is not null and
												 smay.id_seccd is not null
										  then cant_unme_se
										  when smay.id_almad is not null and
												 smay.id_secco is not null 
										  then 1/cant_unme_se else 1 end,0.0000),
		importe = coalesce(tral.importe,0.00),
		id_dmov = 32, oper.fec_oper,  
		id_unme = case when smay.id_almad is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stoktrac smay
		INNER JOIN stoktral tral ON smay.id_operacion = tral.id_operacion
		INNER JOIN stokarti arti ON tral.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE oper.estado=2 ' + @filtro_arti + @filtro_destino_multi 

	EXECUTE (@CAD)     

	--Reversiones de traslados recibidos
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_almad, smay.id_seccd, tral.id_arti,
		cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
		cantidad = coalesce(-tral.cantidad*
									case when smay.id_almao is not null and
												 smay.id_seccd is not null
										  then cant_unme_se
										  when smay.id_almad is not null and
												 smay.id_secco is not null 
										  then 1/cant_unme_se else 1 end,0.0000),
		importe = coalesce(-tral.importe,0.00),
		id_dmov = 42, rvtdor.fec_oper,  
		id_unme = case when smay.id_almad is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stoktrac smay
		INNER JOIN stoktral tral ON smay.id_operacion = tral.id_operacion
		INNER JOIN stokarti arti ON tral.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
		INNER JOIN stokoper rvtdor on rvtdor.id_revertido=oper.id_operacion
	WHERE tral.id_revertidor=rvtdor.id_operacion and rvtdor.estado=2 ' + @filtro_arti + @filtro_destino_multi 

	EXECUTE (@CAD)     

	--Traslados enviados 
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_almao, smay.id_secco, tral.id_arti,
		cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
		cantidad = coalesce(tral.cantidad,0.0000),
		importe = coalesce(tral.importe + tral.imp_desgaste	,0.00),
		id_dmov = 31, oper.fec_oper,  
		id_unme = case when smay.id_almad is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stoktrac smay
		INNER JOIN stoktral tral ON smay.id_operacion = tral.id_operacion
		INNER JOIN stokarti arti ON tral.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE oper.estado=2 ' + @filtro_arti + @filtro_destino_multi1 

	EXECUTE (@CAD)     

	--Reversiones de traslados enviados
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_almao, smay.id_secco, tral.id_arti,
		cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
		cantidad = coalesce(-tral.cantidad,0.0000),
		importe = coalesce(-tral.importe - tral.imp_desgaste	,0.00),
		id_dmov = 41, rvtdor.fec_oper,  
		id_unme = case when smay.id_almad is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stoktrac smay
		INNER JOIN stoktral tral ON smay.id_operacion = tral.id_operacion
		INNER JOIN stokarti arti ON tral.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
		INNER JOIN stokoper rvtdor on rvtdor.id_revertido=oper.id_operacion
	WHERE tral.id_revertidor=rvtdor.id_operacion and oper.estado=2 ' + @filtro_arti + @filtro_destino_multi1 

	EXECUTE (@CAD)     

	-- Ajustes positivos (los negativos van por cosumo)
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, ajul.id_arti,
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(ajul.cantidad,0.0000),
		importe = coalesce(ajul.importe,0.00), 
		id_dmov=5, oper.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokajuc smay
		INNER JOIN stokajul ajul ON smay.id_operacion = ajul.id_operacion
		INNER JOIN stokarti arti ON arti.id_arti = ajul.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE oper.estado=2 and ajul.importe > 0 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)     

	--Reversiones de Ajustes positivos (los negativos van por consumo)
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, ajul.id_arti,
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(-ajul.cantidad,0.0000),
		importe = coalesce(-ajul.importe,0), 
		id_dmov=6, rvtdor.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al 
	FROM stokajuc smay
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
		INNER JOIN stokoper rvtdor on rvtdor.id_revertido=oper.id_operacion
		INNER JOIN stokajul ajul ON smay.id_operacion = ajul.id_operacion
		INNER JOIN stokarti arti ON ajul.id_arti = arti.id_arti
	WHERE ajul.importe > 0 and ajul.id_revertidor=rvtdor.id_operacion
			and rvtdor.estado=2 ' + @filtro_arti + @filtro_destino 

    Execute(@Cad)

	-- Fijación de inventarios positivos (los negativos van por consumo)
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, invl.id_arti,
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(invl.toma-invl.existencia,0.0000),
		importe = coalesce(invl.importe,0.00),
		id_dmov=9, oper.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokinvc smay
		INNER JOIN stokinvl invl ON smay.id_operacion = invl.id_operacion
		INNER JOIN stokarti arti ON invl.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE oper.estado=2 and toma-existencia > 0 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Despieces resultantes 
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, desl.id_arti,
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(desl.cantidad,0.0000),
		importe = coalesce(desl.importe,0.00),
		id_dmov=102, oper.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokdesc smay
		INNER JOIN stokdesl desl ON desl.id_operacion = smay.id_operacion
		INNER JOIN stokarti arti ON desl.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE oper.estado=2 ' +  @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Despieces origenes
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, smay.id_arti,
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(smay.cantidad,0.0000),
		importe = coalesce(smay.importe,0.00),
		id_dmov=101, oper.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokdesc smay
		INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE oper.estado=2 ' +  @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Reversiones de Despieces resultantes
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, desl.id_arti,
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(-desl.cantidad,0.0000),
		importe = coalesce(-desl.importe,0.00),
		id_dmov = 112, rvtdor.fec_oper,  
		id_unme = case when smay.id_alma is not null 
							then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokdesc smay
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
		INNER JOIN stokoper rvtdor on rvtdor.id_revertido=oper.id_operacion
		INNER JOIN stokdesl desl ON desl.id_operacion = smay.id_operacion
		INNER JOIN stokarti arti ON desl.id_arti = arti.id_arti
	WHERE rvtdor.estado=2 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Reversiones de Despieces origenes
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, smay.id_arti,
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(-smay.cantidad,0.0000),
		importe = coalesce(-smay.importe,0.00),
		id_dmov = 111, rvtdor.fec_oper,  
		id_unme = case when smay.id_alma is not null 
							then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokdesc smay
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
		INNER JOIN stokoper rvtdor on rvtdor.id_revertido=oper.id_operacion
		INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
	WHERE rvtdor.estado=2 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Elaboraciones, hacer temporal con los ingredientes
	-- efectivamente rebajados
	select o.id_operacion,o.fec_oper,o.id_tmov,
			o.consecutivo,o.referencia
		into #oper
		from stokoper o 
			inner join stoktmov tm on o.id_tmov = tm.id_tmov
		where o.estado =2 and tm.id_cmov = 15	and (
			(@FDESDE IS NULL AND @FHASTA IS NOT NULL AND FEC_OPER <= @FHASTA) OR
			(@FDESDE IS NOT NULL AND @FHASTA IS NULL AND FEC_OPER >= @FDESDE) OR
			(@FDESDE IS NOT NULL AND @FHASTA IS NOT NULL AND FEC_OPER BETWEEN @FDESDE AND @FHASTA))
	if @@rowcount != 0
		begin		-- hay elaboraciones 
			-- temporal para insertar las líneas de ingredientes
			create table #ingred (
				id_operacion int,
				id_eell int, 
				id_arti int,
				cant_r numeric(12,4),
				costo numeric(10,4),
				importe numeric(12,4),
				id_sub int)							
			declare @operant int, @minoper int
			select @operant = -1
			-- ciclo para llenar la tabla de ingredientes
			while 1=1
				begin
					select @minoper=min(id_operacion) 
						from #oper
						where id_operacion > @operant
					if @minoper is null
						break
					insert #ingred
						execute GetLineasElaboracion @minoper
					select @operant = @minoper
					continue				
				end
			-- elaborados
		   Select @Cad = 'INSERT #invactual 
				SELECT null, smay.id_secc, smay.id_arti,
					cod_arti = arti.FullArtiCode, 
					desc_arti = arti.descripcion,
					cantidad = coalesce(smay.cant_r,0.0000),
					importe = coalesce(smay.importe,0.00),
					id_dmov=152, oper.fec_oper,  
					id_unme = id_unme_se,
					cant_um = cant_unme_al
				FROM STOKEELO smay 
					INNER JOIN #OPER oper ON oper.ID_OPERACION=smay.ID_OPERACION 
					INNER JOIN STOKARTI arti ON arti.id_arti=smay.id_arti
				WHERE 1=1 '  +  @filtro_arti + @filtro_destino_elab
			EXECUTE (@CAD)     
			-- ingredientes 
	      Select @Cad = 'INSERT #invactual 
				SELECT null, smay.id_secc, desl.id_arti,
					cod_arti = arti.FullArtiCode, 
					desc_arti = arti.descripcion,
					cantidad = coalesce(desl.cant_r,0.0000),
					importe = coalesce(desl.importe,0.00),
					id_dmov=151, oper.fec_oper,  
					id_unme = id_unme_se,
					cant_um = cant_unme_al
				FROM STOKEELO smay 
					INNER JOIN #ingred desl ON desl.id_operacion = smay.id_operacion
					INNER JOIN #OPER oper ON oper.ID_OPERACION=smay.ID_OPERACION 
					INNER JOIN STOKARTI arti ON arti.id_arti=desl.id_arti
				WHERE 1=1 '  +  @filtro_arti + @filtro_destino_elab
			EXECUTE (@CAD)
			drop table #ingred
		end		-- hay elaboraciones
	drop table #oper
	-- Reversion de elaboraciones, hacer un temporal con los ingredientes
	-- efectivamente rebajados
	select o.id_operacion,o.fec_oper,o.id_tmov,
			o.consecutivo,o.referencia, o.id_revertido
		into #operrev
		from stokoper o 
			inner join stoktmov tm on o.id_tmov = tm.id_tmov
		where o.estado =2 and tm.id_cmov = 16 and (
			(@FDESDE IS NULL AND @FHASTA IS NOT NULL AND FEC_OPER <= @FHASTA) OR
			(@FDESDE IS NOT NULL AND @FHASTA IS NULL AND FEC_OPER >= @FDESDE) OR
			(@FDESDE IS NOT NULL AND @FHASTA IS NOT NULL AND FEC_OPER BETWEEN @FDESDE AND @FHASTA))
	if @@rowcount != 0
		begin		-- hay reversion de elaboraciones 
			-- temporal para insertar las líneas de ingredientes
			create table #ingredrev (
				id_operacion int,
				id_eell int, 
				id_arti int,
				cant_r numeric(12,4),
				costo numeric(10,4),
				importe numeric(12,4),
				id_sub int)							
			select @operant = -1
			-- ciclo para llenar la tabla de ingredientes
			while 1=1
				begin
					select @minoper=min(id_operacion) 
						from #operrev
						where id_operacion > @operant
					if @minoper is null
						break
					select @revertido=id_revertido 
						from #operrev where id_operacion=@minoper
					insert #ingredrev
						execute GetLineasElaboracion @revertido
					select @operant = @minoper
					continue				
				end
			-- rversion de elaborados
			Select @Cad = 'INSERT #invactual 
				SELECT null, smay.id_secc, smay.id_arti,
					cod_arti = arti.FullArtiCode, 
					desc_arti = arti.descripcion,
					cantidad = coalesce(-smay.cant_r,0.0000),
					importe = coalesce(-smay.importe,0.00),
					id_dmov = 162, oper.fec_oper,  
					id_unme = id_unme_se,
					cant_um = cant_unme_al
				FROM STOKEELO smay 
					inner join stokoper rvtda on rvtda.id_operacion=smay.ID_OPERACION
					INNER JOIN #OPERREV oper ON oper.ID_revertido=rvtda.id_operacion
					INNER JOIN STOKARTI arti ON arti.id_arti=smay.id_arti
				WHERE 1=1 '  +  @filtro_arti + @filtro_destino_elab
			EXECUTE (@CAD)     
			-- ingredientes
			Select @Cad = 'INSERT #invactual 
				SELECT null, smay.id_secc, desl.id_arti,
					cod_arti = arti.FullArtiCode, 
					desc_arti = arti.descripcion,
					cantidad = coalesce(-desl.cant_r,0.0000),
					importe = coalesce(-desl.importe,0.00),
					id_dmov = 161, oper.fec_oper,  
					id_unme = id_unme_se,
					cant_um = cant_unme_al
				FROM stokeelo smay
					inner join stokoper rvtda on rvtda.id_operacion=smay.ID_OPERACION
					INNER JOIN #OPERREV oper ON oper.ID_revertido=rvtda.id_operacion
					INNER JOIN #ingredrev desl ON desl.id_operacion = smay.id_operacion
					INNER JOIN STOKARTI arti ON arti.id_arti=desl.id_arti
				WHERE 1=1 '  +  @filtro_arti + @filtro_destino_elab
			EXECUTE (@CAD)
			drop table #ingredrev
		end		-- hay revers de elaboraciones
	drop table #operrev	-- temporal de revers de elaboraciones

	-- Cambio de código destino, lo tomo del submayor porque en este movimiento todo lo que existe
	-- tiene que estar aquí.
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, smay.id_arti,
		cod_arti = arti.FullArtiCode, 
		desc_arti = arti.descripcion,
		cantidad = coalesce(camr.cantidad,0.0000),
		importe = coalesce(camr.importe,0.00),
		id_dmov = 131, smay.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stoksmay smay
		INNER JOIN stokscmr scmr ON scmr.id_smay = smay.id_smay
		INNER JOIN stokcamr camr ON scmr.id_camr = camr.id_camr
		INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE 0 = 0 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Cambio de código orígenes, lo tomo del submayor porque en este movimiento todo lo que existe
	-- tiene que estar aquí.
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, smay.id_arti,
		cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
		cantidad = coalesce(carl.cantidad,0.0000),
		importe = coalesce(carl.importe,0.00),
		id_dmov = 132, smay.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stoksmay smay
		INNER JOIN stokscml scml ON scml.id_smay = smay.id_smay
		INNER JOIN stokcarl carl ON scml.id_carl = carl.id_carl
		INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE 0 = 0 ' + @filtro_arti + @filtro_destino 

	EXECUTE (@CAD)

	-- Cambios de UM, orígenes y destinos, desde el submayor que es donde único existen
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_alma, smay.id_secc, smay.id_arti,
		cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
		cantidad = coalesce(scmu.cantidad,0.0000),
		importe = coalesce(scmu.importe,0.00),
		smay.id_dmov, smay.fec_oper,  
		id_unme = case when smay.id_alma is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al 
	FROM stoksmay smay
		INNER JOIN stokscmu scmu ON scmu.id_smay = smay.id_smay
		INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE 0 = 0 ' + @filtro_arti + @filtro_destino 

      Execute(@Cad)

	--Traslados multialmacen recibidos
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_almad, smay.id_seccd, tral.id_arti,
		cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
		cantidad = coalesce(tral.cantidad*
									case when smay.id_almao is not null and
												 smay.id_seccd is not null
										  then cant_unme_se
										  when smay.id_almad is not null and
												 smay.id_secco is not null 
										  then 1/cant_unme_se else 1 end,0.0000),
		importe = coalesce(tral.importe,0.00),
		id_dmov = 72, oper.fec_oper,  
		id_unme = case when smay.id_almad is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokmalc smay
		INNER JOIN stokmall tral ON smay.id_operacion = tral.id_operacion
		INNER JOIN stokarti arti ON tral.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE oper.estado=2 and smay.id_dato_de = dbo.GetCurrentDato() ' + @filtro_arti + @filtro_destino_multi 

	EXECUTE (@CAD)     

	--Traslados multialmacen enviados
      Select @Cad = 'INSERT #invactual 
	SELECT smay.id_almao, smay.id_secco, tral.id_arti,
		cod_arti = arti.FullArtiCode, desc_arti = arti.descripcion,
		cantidad = coalesce(tral.cantidad,0.0000),
		importe = coalesce(tral.importe + tral.imp_desgaste	,0.00),
		id_dmov = 71, oper.fec_oper,  
		id_unme = case when smay.id_almad is not null 
									 then arti.id_unme_al else arti.id_unme_se end,
		cant_um = arti.cant_unme_al
	FROM stokmalc smay
		INNER JOIN stokmall tral ON smay.id_operacion = tral.id_operacion
		INNER JOIN stokarti arti ON tral.id_arti = arti.id_arti
		INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
	WHERE oper.estado=2 and smay.id_dato_or = dbo.GetCurrentDato() ' + @filtro_arti + @filtro_destino_multi1 

	EXECUTE (@CAD)     

	-- Eliminar del temporal, los registros segun parámetros de fecha si alguno
	-- de ellos no es nulo. Si ambos son nulos se toma todo.
	-- No lo meto en las selects de arriba por miedo a que se pase de las 8000 char
	-- si hay lista de artis y secciones
	If @fdesde is not null or @fhasta is not null
		DELETE FROM #invactual
			WHERE NOT (
	 				(@FDESDE IS NULL AND @FHASTA IS NOT NULL AND FEC_OPER <= @FHASTA) OR
	 				(@FDESDE IS NOT NULL AND @FHASTA IS NULL AND FEC_OPER >= @FDESDE) OR
	 				(@FDESDE IS NOT NULL AND @FHASTA IS NOT NULL AND FEC_OPER BETWEEN @FDESDE AND @FHASTA)
						)

	-- Meterle los consumos a los registros, 
	-- para cada articulo y alma y secc de las listas
	-- o para todos si las listas estan vacias
		DECLARE @cons_cant numeric(12,4),@cons_imp numeric(12,2)
		declare @calma int, @csecc int, @carti int, @lalma char(12), @lsecc char(12)
   	declare cguia cursor for
	   	select distinct id_alma,id_secc,id_arti from #invactual 
   	open cguia
   	fetch next from cguia into @calma,@csecc,@carti
  	while @@fetch_status=0 
   		begin
				select @lalma = ltrim(rtrim(str(@calma,12,0))),
					@lsecc = ltrim(rtrim(str(@csecc,12,0)))
				EXECUTE dbo.ConsTotQISecc @carti,@fdesde,@fhasta,
					@lalma, @lsecc,
					@cons_cant OUTPUT,@cons_imp OUTPUT 
					insert #consumos (id_alma,id_secc,id_arti,cons_cant,cons_imp)
						values (@calma,@csecc,@carti,@cons_cant,@cons_imp)
		 	 	fetch next from cguia into @calma,@csecc,@carti
	   	end
   	close cguia
   	deallocate cguia

	-- Si parametro consumo=1, eliminar de invactual los articulos
	-- que tengan consumo cero
	IF @consumo = 1
		DELETE #invactual 
			FROM #invactual i 
				INNER JOIN #consumos c ON i.id_arti=c.id_arti AND
					(i.id_alma is not null and c.id_alma is not null and i.id_alma = c.id_alma
						or
					 i.id_secc is not null and c.id_secc is not null and i.id_secc = c.id_secc)
			WHERE cons_cant=0 and cons_imp=0

	-- Buscar saldos anteriores
	DECLARE @saldo_cant numeric(12,4),@saldo_imp numeric(12,2)
	declare @galma int, @gsecc int, @garti int
 	declare guia cursor for
   	select distinct id_alma,id_secc,id_arti from #invactual 
 	open guia
 	fetch next from guia into @galma,@gsecc,@garti
	while @@fetch_status=0 
 		begin
			EXECUTE dbo.saldos_ant @garti,@galma,@gsecc,@fdesde,@saldo_cant OUTPUT, @saldo_imp OUTPUT 
			insert #saldosant (id_alma,id_secc,id_arti,
				sant_cantidad,sant_importe)
				values (@galma,@gsecc,@garti,@saldo_cant,@saldo_imp)
	 	 	fetch next from guia into @galma,@gsecc,@garti
   	end
 	close guia
 	deallocate guia

	-- Combinación de todos los datos y ordenamiento
  Select su.*,
			cod_alma = alma.codigo, desc_alma = alma.descripcion, 
			cod_secc = secc.codigo, desc_secc = secc.descripcion ,
			cod_unme = unme.codigo, desc_unme = unme.descripcion,
	    existencia = Case When su.id_alma is not null Then Coalesce(alex.existencia, 0) Else Coalesce(seex.existencia, 0) End,
  	  imp_actual = Case When su.id_alma is not null Then Coalesce(alex.importe, 0) Else Coalesce(seex.importe, 0) End,
			cod_dest = Case When su.id_alma is not null Then 'A' + alma.codigo Else 'S' + secc.codigo End,
			cod_dmov = dmov.codigo, desc_dmov = dmov.descripcion,
			cod_fami=fami.codigo, desc_fami=fami.descripcion,
			cod_grup=grup.codigo, desc_grup=grup.descripcion,
			sant_cantidad=coalesce(sant_cantidad,0.0000),
			sant_importe=coalesce(sant_importe,0.00),
			cons_cant=coalesce(cons_cant,0.0000),
			cons_imp=coalesce(cons_imp,0.00)
	  From #invactual su
      Left Join stokalma alma On su.id_alma = alma.id_alma
      Left Join stoksecc secc On su.id_secc = secc.id_secc
      Left Join stokalex alex On su.id_arti = alex.id_arti and su.id_alma = alex.id_alma
      Left Join stokseex seex On su.id_arti = seex.id_arti and su.id_secc = seex.id_secc
      Inner Join stokunme unme On su.id_unme = unme.id_unme
      Inner Join stokdmov dmov On su.id_dmov = dmov.id_dmov
			inner join stokarti arti on su.id_arti = arti.id_arti
			inner join stokfami fami on arti.id_fami = fami.id_fami
			inner join stokgrup grup on fami.id_grup = grup.id_grup
			inner join #saldosant sant on su.id_arti=sant.id_arti and
				((su.id_alma is not null and sant.id_alma is not null and su.id_alma=sant.id_alma) or
				 (su.id_secc is not null and sant.id_secc is not null and su.id_secc=sant.id_secc))
			inner join #consumos cons on su.id_arti=cons.id_arti  and
				((su.id_alma is not null and cons.id_alma is not null and su.id_alma=cons.id_alma) or
				 (su.id_secc is not null and cons.id_secc is not null and su.id_secc=cons.id_secc))
	  Order By cod_dest, cod_arti

  drop table #invactual
  drop table #saldosant
  drop table #consumos

END





















