ALTER PROCEDURE [dbo].[sp_invConsumoListar]
(
	 @tipo AS    INT= 0,  --0=CONSUMO Y DEVOLUCION | 2=CONSUMO | 4=DEVOLUCION
	 @coduninegi INT = 0,
	 @coduninegf INT= 2147483647, --Maximo que soporta un int
	 @fecini	 DATE = '01-01-1900 00:00:00',
	 @fecfin	 DATE = '01-01-2050 00:00:00',
	 @numdoci	 BIGINT= 0,
     @numdocf	 BIGINT= 9223372036854775807, --Maximo que soporta un bigint
	 @codbusi	 VARCHAR(30)='0',
     @codbusf	 VARCHAR(30)='ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ',
	 @codsubi	 VARCHAR(10)='0',
     @codsubf	 VARCHAR(10)='ZZZZZZZZZZ',
	 @codfami	 VARCHAR(10)='0',
     @codfamf	 VARCHAR(10)='ZZZZZZZZZZ',
	 @codbodi	  INT=0,
	 @codbodf	  INT=2147483647,
	 @itemini	  INT= 1,
	 @itemfin	  INT= 99999999,
	 @marca varchar(100) = 'TODAS'
)

AS

--DECLARE @tipo AS INT= 0  --0=CONSUMO Y DEVOLUCION | 2=CONSUMO | 4=DEVOLUCION

SELECT
	1 as grudoc,
	invDetalle.coddoc,
	CASE invDetalle.coddoc  
		WHEN 2 THEN 'CON' 
		WHEN 4 THEN 'DEV' 
	END								AS Tipo, 
	
	invDetalle.numinv				AS Numero,  
	invEncabezado.fecdoc			AS Fecha, 
	invEncabezado.codbod			AS 'Cod. Bodega',
	ISNULL(invBodega.nombod,'S/D')	AS 'Des. Bodega',
	invEncabezado.codcen			AS 'Cod. U.Negocio', 
	ISNULL(CONVERT(varchar, invEncabezado.codcen) + ' - ' + CONVERT(varchar, conUnidadNegocio.descripcion), 'S/D') AS 'Des. U. Negocio',
	invEncabezado.item   		    AS 'Item de gasto',
	conItem.descripcion				AS 'Des. Item',
	invDetalle.codbus				AS Codigo, 
	invDetalle.codfam               AS 'Cod. Familia',
	invDetalle.codSub				AS 'Cod. SubFamilia',
	invDetalle.codArt				AS 'Cod. Articulo',
	invDetalle.nomart				AS Descripcion, 
	invUnidadMedida.abruni			AS UM, 
	
	CASE invEncabezado.tipref1
		WHEN 0 THEN 'SOL' 
		WHEN 1 THEN 'MER'
		WHEN 2 THEN 'S/R'
		WHEN 4 THEN	'KIT'
		WHEN 5 THEN	'OT'
		WHEN 6 THEN	'OP'

		ELSE CONVERT(VARCHAR, invEncabezado.tipref1)
	END								AS Referencia,

	invEncabezado.docref1			AS 'Num. Ref.',
	invDetalle.cantidad				AS Cantidad, 
	invDetalle.Costo				AS Costo, 
	
	CASE invDetalle.coddoc  
		WHEN 2 THEN (invDetalle.cantidad  * invDetalle.costo) 
		WHEN 4 THEN -(invDetalle.cantidad  * invDetalle.costo) 
	END								AS 'Total costo',
	invArticulos.marca
	
	FROM invDetalle
	LEFT JOIN invEncabezado		ON invEncabezado.numinv= invDetalle.numinv AND invEncabezado.coddoc=invDetalle.coddoc
	LEFT JOIN invBodega			ON invBodega.codbod=invEncabezado.codbod
	LEFT JOIN conUnidadNegocio  ON invEncabezado.codcen= conUnidadNegocio.idUniNeg
	LEFT JOIN invUnidadMedida   ON invDetalle.coduni = invUnidadMedida.coduni
	INNER JOIN invArticulos	    ON invDetalle.codbus = invArticulos.codbus
	LEFT JOIN conItem			ON invEncabezado.item   = conItem.codigo
	WHERE 
		(@tipo IN (2,4) OR invDetalle.coddoc IN (2,4))
	AND (@tipo IN (0,4) OR invDetalle.coddoc = 2)
	AND (@tipo IN (0,2) OR invDetalle.coddoc = 4)
	AND (invEncabezado.codcen BETWEEN @coduninegi AND @coduninegf)
	AND (invEncabezado.fecdoc BETWEEN @fecini AND @fecfin)
	AND (invDetalle.numinv BETWEEN @numdoci  AND @numdocf)
	AND (invDetalle.codbus BETWEEN @codbusi AND @codbusf)
	AND (invArticulos.codfam BETWEEN @codfami AND @codfamf)
	AND (invArticulos.codsub BETWEEN @codsubi AND @codsubf)
	AND (invEncabezado.codbod BETWEEN @codbodi AND @codbodf)
	AND (invEncabezado.item BETWEEN @itemini AND @itemfin)
	AND (invArticulos.marca = @marca or @marca = 'TODAS')
	ORDER BY invDetalle.coddoc, invDetalle.numinv, invEncabezado.fecdoc ASC