
/****** Object:  UserDefinedFunction [dbo].[FSaldo_CantidadN]    Script Date: 05/16/2016 16:51:44 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP FUNCTION FSaldo_CantidadW
GO

CREATE FUNCTION dbo.FSaldo_CantidadW(
				@id_arti INT, 
				@id_alma INT, 
				@id_secc INT, 
				@fecha DATETIME)
RETURNS NUMERIC(20, 4)
AS
-- Devuelve el saldo en cantidad de un artículo
-- en una sección o almacén dados, para el día anterior
-- a la fecha dada.
BEGIN

/*	Hecho por: Luis Abarca
	Fecha: 22 Junio 2011
	Detalle: Crea una tabla [@TablaTemp],
			para accelerar el calculo de las 
			cantidades 	
*/

	DECLARE @Saldo NUMERIC(20, 4) = 0
	DECLARE @Tabla TABLE
	(
		id_dMov INT, 
		Cantidad NUMERIC(20, 4)
	)
	
	DECLARE @TablaTemp TABLE
	(
		id_smay INT, 
		id_operacion INT, 
		id_arti INT, 
		consecutivo_act INT, 
		id_alma INT, 
		id_secc INT NULL, 
		fec_oper DATETIME, 
		id_dmov INT, 
		unico UNIQUEIDENTIFIER
	)

	SET @fecha = @fecha
	-- - 1

	-- Chequeo de parámetros, devuelve NULL en caso de error
	IF @id_alma IS NULL
       AND @id_secc IS NULL
	    OR @id_alma IS NOT NULL
       AND @id_secc IS NOT NULL
	    OR @id_arti IS NULL
	    OR @id_arti NOT IN(SELECT id_arti FROM STOKARTI)
	BEGIN
		RETURN NULL
	END


	INSERT INTO @TablaTemp
	SELECT *
	FROM StoksMay sm
	WHERE id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND id_secc = @id_secc)
	  AND DATEDIFF(dd, fec_oper, @fecha) > 0

	-- Saldo inicial
	---------------------------------------------------------------------	
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   cantidad = CONVERT(NUMERIC(12, 4), COALESCE(ii.cantidad, 0))
	FROM       @TablaTemp sm
	INNER JOIN stoksini   ii ON ii.id_smay = sm.id_smay
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 0
		
	--Compras y reversiones
	---------------------------------------------------------------------	 
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(CASE WHEN id_dmov = 1 THEN cml.cantidad_D
							 ELSE - cml.cantidad_D
						     END, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokscmp   scm ON scm.id_smay = sm.id_smay
	INNER JOIN stokcmpl   cml ON scm.id_cmpl = cml.id_cmpl
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov IN(1, 2)
	GROUP BY sm.id_dmov
	
	-- Devoluciones
	---------------------------------------------------------------------		
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(-cml.cantidad_D, 0))
	FROM       @TablaTemp sm
	INNER JOIN stoksdev   scm ON scm.id_smay = sm.id_smay
	INNER JOIN stokdevl   cml ON scm.id_devl = cml.id_devl
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 320
	GROUP BY sm.id_dmov
			
	-- Traslados y reversiones
	---------------------------------------------------------------------		
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(CASE WHEN id_dmov = 31 THEN - trl.cantidad
							 WHEN id_dmov = 32 THEN trl.cantidad_D
							 WHEN id_dmov = 41 THEN trl.cantidad
							 ELSE - trl.cantidad_D
						     END, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokstra   stl ON stl.id_smay = sm.id_smay
	INNER JOIN stoktral   trl ON stl.id_tral = trl.id_tral
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov IN(31, 32, 41, 42)
	GROUP BY sm.id_dmov
			
	-- Ajustes y reversiones
	---------------------------------------------------------------------		
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(CASE WHEN id_dmov = 5 THEN ajl.cantidad
							 ELSE -ajl.cantidad
						     END, 0))
	FROM       @TablaTemp sm
	INNER JOIN stoksaju   saj ON saj.id_smay = sm.id_smay
	INNER JOIN stokajul   ajl ON saj.id_ajul = ajl.id_ajul
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov IN(5, 6)
	GROUP BY sm.id_dmov
			
	-- Fijación de inventarios
	---------------------------------------------------------------------		
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(ivl.toma - ivl.existencia, 0))
	FROM       @TablaTemp sm
	INNER JOIN stoksinv   siv ON siv.id_smay = sm.id_smay
	INNER JOIN stokinvl   ivl ON siv.id_invl = ivl.id_invl
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 9
	GROUP BY sm.id_dmov

	-- Despieces (art. origen)
	---------------------------------------------------------------------		
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(-dpc.cantidad, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokdesc   dpc ON dpc.id_operacion = sm.id_operacion
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 101
	GROUP BY sm.id_dmov

	-- Reversión de Despieces (art. origen)
	---------------------------------------------------------------------		
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(dpc.cantidad, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokoper   oper ON oper.id_operacion = sm.id_operacion
	INNER JOIN stokdesc   dpc ON dpc.id_operacion = oper.id_revertido
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 111
	GROUP BY sm.id_dmov

	-- Despieces y reversiones (arts. destino)
	---------------------------------------------------------------------		
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(CASE WHEN id_dmov = 102 THEN dpl.cantidad
							 ELSE -dpl.cantidad
						     END, 0))
	FROM       @TablaTemp sm
	INNER JOIN stoksdel   sdl ON sdl.id_smay = sm.id_smay
	INNER JOIN stokdesl   dpl ON dpl.id_desl = sdl.id_desl
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov IN(102, 112)
	GROUP BY sm.id_dmov
			
	-- Rebaja, principal
	---------------------------------------------------------------------			
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(-rbp.cantidad, 0))
	FROM       @TablaTemp sm
	INNER JOIN stoksrep   srp ON srp.id_smay = sm.id_smay
	INNER JOIN stokrebp   rbp ON srp.id_rebp = rbp.id_rebp
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 121
	GROUP BY sm.id_dmov
			
	-- Rebaja, alternativo
	---------------------------------------------------------------------			
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(-rba.cantidad, 0))
	FROM       @TablaTemp sm
	INNER JOIN stoksrea   sra ON sra.id_smay = sm.id_smay
	INNER JOIN stokreba   rba ON sra.id_reba = rba.id_reba
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 122
	GROUP BY sm.id_dmov
			
	-- Cambio de código, destino
	---------------------------------------------------------------------			
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(ccd.cantidad, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokscmr   scd ON scd.id_smay = sm.id_smay
	INNER JOIN stokcamr   ccd ON scd.id_camr = ccd.id_camr
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 131
	GROUP BY sm.id_dmov
			
	-- Cambio de código, orígenes
	---------------------------------------------------------------------			
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(-cco.cantidad, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokscml   sco ON sco.id_smay = sm.id_smay
	INNER JOIN stokcarl   cco ON sco.id_carl = cco.id_carl
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 132
	GROUP BY sm.id_dmov
			
	-- Cambios de UM, orígenes y destinos
	---------------------------------------------------------------------			
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(CASE WHEN id_dmov = 141 THEN-scu.cantidad
							 ELSE scu.cantidad
						     END, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokscmu   scu ON scu.id_smay = sm.id_smay
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov IN(141, 142)
	GROUP BY sm.id_dmov
	
	-- Elaboraciones (elaborado)
	---------------------------------------------------------------------			
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(dpc.cant_r, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokeelo   dpc ON dpc.id_operacion = sm.id_operacion
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 152
	GROUP BY sm.id_dmov

	-- Reversión de Elaboraciones (elaborado)
	---------------------------------------------------------------------			
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(-dpc.cant_r, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokoper   oper ON sm.id_operacion  = oper.id_operacion
	INNER JOIN stokeelo   dpc  ON dpc.id_operacion = oper.id_revertido
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov = 162
	GROUP BY sm.id_dmov

	-- Elaboraciones y reversiones (ingredientes)
	---------------------------------------------------------------------			
	INSERT INTO @Tabla
	SELECT sm.id_dmov, 
		   SUM(COALESCE(CASE WHEN id_dmov = 161 THEN dpl.cant_r
							 ELSE-dpl.cant_r
						     END, 0))
	FROM       @TablaTemp sm
	INNER JOIN stokseel   sdl ON sdl.id_smay = sm.id_smay
	INNER JOIN stokeell   dpl ON dpl.id_eell = sdl.id_eell
	WHERE sm.id_arti = @id_arti
	  AND (@id_alma IS NOT NULL
	   AND sm.id_alma = @id_alma
		OR @id_secc IS NOT NULL
	   AND sm.id_secc = @id_secc)
	  AND DATEDIFF(dd, sm.fec_oper, @fecha) > 0
	  AND sm.id_dmov IN(151, 161)
	GROUP BY sm.id_dmov
	---------------------------------------------------------------------		

	/*	 Saldo anterior*/

	SELECT @saldo = SUM(cantidad)
	FROM   @Tabla
	RETURN @saldo
END
