SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIfIER OFF
GO

IF EXISTS (Select * From sys.objects 
   Where object_id = OBJECT_ID(N'WINV_ResuxSeccion') )
DROP Procedure WINV_ResuxSeccion
GO

CREATE PROCEDURE WINV_ResuxSeccion
					@List_Artis varchar(8000), 
					@List_Alma varchar(8000), 
					@List_Secc varchar(8000), 
					@Fdesde datetime,
					@Fhasta datetime,
					@Tipo_Opers tinyint,
					@Reversiones bit,
					@OrderBy tinyint
AS

BEGIN

/*
	Date: Apr 13,2016


*/

/*
	--GISELA: tomar de oper para que incluya lo que no se guarda en submayor
	-- en las operaciones en que esto influye que son: compras y reversiones,
	-- devoluciones,	
	-- entradas por traslados y reversiones, entradas por multitraslado, 
	-- productos destinos en despieces y productos origenes en elaboraciones
*/
   DECLARE @Cad varchar(8000), 
			@Filtro_fecha varchar(200), 
			@Filtro_destino varchar(8000), 
			@Filtro_arti varchar(8000),
			@Filtro_opers varchar(200),
			@order_by varchar(200),
			@id_datos as integer,
			@base_datos as varchar(30),
			@desc_datos as varchar(50),
			@Filtro_destinoG varchar(8000)


/* ====================================================================
   Region 01
   Preparo los parámetros
   ====================================================================
*/

Select @Filtro_arti=''

If @list_artis is not NULL
	Select @Filtro_arti=' AND ' + @list_artis

   Select @Filtro_destino = ''

   If     @list_alma is not NULL 
      and @list_secc is not NULL
      Select @Filtro_destino = ' AND ( (smay.id_alma is not null and smay.id_alma in (' 
	                         + @list_alma + ')) or (smay.id_secc is not null and smay.id_secc in (' 
							 + @list_secc + ')) ) '
   Else
      Begin 
         If @list_alma is not NULL
            Select @Filtro_destino = ' AND (smay.id_alma is not null and smay.id_alma in (' 
			                       + @list_alma + ') )'
         Else
	If @list_secc is not NULL
	   Select @Filtro_destino = ' AND (smay.id_secc is not null and smay.id_secc in (' 
	                          + @list_secc + ') )'
      End


/* ====================================================================
   Region 02
	-- GISELA: Filtro destino adicional para usar en las Selects que cambie
	-- y q como no toman del smay, el alma y secc de este Filtro estará
	-- genericamente calIficado con C., para q me sirva para la
	-- cabecera de cualquiera de los movs que cambio
   ====================================================================
*/
   Select @Filtro_destinoG = ''

   If     @list_alma is not NULL 
      and @list_secc is not NULL
      Select @Filtro_destinoG = ' AND ( (C.id_alma is not null and C.id_alma in (' 
	                          + @list_alma 
							  + ')) or (C.id_secc is not null and C.id_secc in (' 
							  + @list_secc + ')) ) '
   Else
      Begin 
         If @list_alma is not NULL
            Select @Filtro_destinoG = ' AND (C.id_alma is not null and C.id_alma in (' 
									+ @list_alma + ') )'
         Else
				If @list_secc is not NULL
	   			Select @Filtro_destinoG = ' AND (C.id_secc is not null and C.id_secc in (' 
										+ @list_secc + ') )'
      End

	Set @Filtro_fecha=''

	If @fdesde is not null
		Select @Filtro_fecha = ' and DatedIff(dd, convert(datetime,"'
		                     + convert(varchar(20),@fdesde,101)
							 + '",101), smay.fec_oper) >= 0'

	If @fhasta is not null
		Select @Filtro_fecha = @Filtro_fecha 
							 + ' and DatedIff(dd, smay.fec_oper, convert(datetime,"'
							 + convert(varchar(20),@fhasta,101)+'",101)) >= 0'


/* ====================================================================
   Region 03
   	-- Crear tabla temporal
   ====================================================================
*/
	CREATE TABLE #invactual (
				Cod_Dest char(4),
				Id_Fami int,
				Id_Dest int,
				IsAlma char(1),
				Id_Alma int, 
				Id_Secc int,
				Id_Arti int, 
				Cantidad numeric(12,4),
				Importe numeric(12,2),
				Oper_Entrada bit,
				Reversion bit,
				Fec_Oper datetime )

/* ====================================================================
   Region 04
  -- Comienzo a seleccionar el invactual
   -- Inventario Inicial   
   ====================================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = 
			            Case When smay.id_alma is not null 
			                 Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
							 Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
							 End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
				               Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad=convert(numeric(12,4),Coalesce(sini.cantidad,0)),
				importe=convert(numeric(12,2),Coalesce(sini.importe,0)),
				oper_entrada=convert(bit,1), reversion=convert(bit,0), 
				smay.fec_oper
			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
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 0 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino

		EXECUTE (@CAD)

/* ====================================================================
   Region 05
	-- Compras
   ====================================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = 
			          Case When c.id_alma is not null 
					       Then "A"+ dbo.GetCodigoAlmacen(c.id_alma) 
						   Else "S" + dbo.GetCodigoSeccion(c.id_secc) 
						   End, 
				arti.id_fami,
				id_dest = Case When c.id_alma is not null 
							   Then c.id_alma 
							   Else c.id_secc 
							   End, 
				isalma = Case When c.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				c.id_alma, c.id_secc, cmpl.id_arti,
				cantidad = Coalesce(cmpl.cantidad_D,0),
				importe = Coalesce(cmpl.importe,0),
				oper_entrada=convert(bit,1), reversion=convert(bit,0), 
				smay.fec_oper
			From stokoper smay
				INNER JOIN stokcmpi c ON smay.id_operacion=c.id_operacion
				INNER JOIN stokcmpl cmpl ON c.id_cmpi=cmpl.id_cmpi
				INNER JOIN stokarti arti ON cmpl.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.estado=2 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destinoG


		EXECUTE (@CAD)

/* ====================================================================
   Region 06
	-- Reversiones de Compras
   ====================================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = 
					  Case When c.id_alma is not null 
					       Then "A"+ dbo.GetCodigoAlmacen(c.id_alma) 
						   Else "S" + dbo.GetCodigoSeccion(c.id_secc) 
						   End, 
				arti.id_fami,
				id_dest = Case When c.id_alma is not null 
							   Then c.id_alma 
							   Else c.id_secc 
							   End, 
				isalma = Case When c.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				c.id_alma, c.id_secc, cmpl.id_arti,
				cantidad = Coalesce(-cmpl.cantidad_D,0),
				importe = Coalesce(-cmpl.importe,0),
				oper_entrada=convert(bit,0), reversion=convert(bit,1), 
				smay.fec_oper
			From stokoper smay
				INNER JOIN stokoper rvtda on smay.id_revertido=rvtda.id_operacion
				INNER JOIN stokcmpi c ON c.id_operacion=rvtda.id_operacion
				INNER JOIN stokcmpl cmpl ON c.id_cmpi=cmpl.id_cmpi
				INNER JOIN stokarti arti ON cmpl.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.estado=2 and cmpl.id_revertidor is not null' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destinoG

		EXECUTE (@CAD)

/* ====================================================================
   Region 07
	-- Devoluciones
   ====================================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = 
						Case When c.id_alma is not null 
							 Then "A"+ dbo.GetCodigoAlmacen(c.id_alma) 
							 Else "S" + dbo.GetCodigoSeccion(c.id_secc) 
							 End, 
				arti.id_fami,
				id_dest = Case When c.id_alma is not null 
							   Then c.id_alma 
							   Else c.id_secc 
							   End, 
				isalma = Case When c.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				c.id_alma, c.id_secc, cmpl.id_arti,
				cantidad = Coalesce(-cmpl.cantidad_D,0),
				importe = Coalesce(-cmpl.importe,0),
				oper_entrada=convert(bit,0), reversion=convert(bit,0), 
				smay.fec_oper
			From stokoper smay
				INNER JOIN stokdevi c ON smay.id_operacion=c.id_operacion
				INNER JOIN stokdevl cmpl ON c.id_devi=cmpl.id_devi
				INNER JOIN stokarti arti ON cmpl.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.estado=2 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destinoG

		EXECUTE (@CAD)

/* ====================================================================
   Region 08
	--Traslados
   ====================================================================
*/
    Select @Cad = 
	    'Insert #invactual 
			Select cod_dest = 
						Case When c.id_alma is not null 
						     Then "A"+ dbo.GetCodigoAlmacen(c.id_alma) 
							 Else "S" + dbo.GetCodigoSeccion(c.id_secc) 
							 End, 
				arti.id_fami,
				id_dest = Case When c.id_alma is not null 
							   Then c.id_alma 
							   Else c.id_secc 
							   End, 
				isalma = Case When c.id_alma is not null 
							  Then "A" Else "S" 
							  End,
				c.id_alma, c.id_secc, tral.id_arti,
				cantidad = Coalesce(CASE When c.id_dmov = 31 
				                         Then -tral.cantidad
									     When c.id_dmov = 32 
										 Then tral.cantidad_D 
										 End,0),
				importe = Coalesce(CASE When c.id_dmov = 31 
				                        Then -1*(tral.importe + tral.imp_desgaste)
								        When c.id_dmov = 32 
										Then tral.importe + tral.imp_desgaste 
										End,0),
				oper_entrada=convert(bit,
						case When c.id_dmov=31 
				             Then 0 
							 Else 1 
							 End), 
				reversion=convert(bit,0), smay.fec_oper
			From stokoper smay
				INNER JOIN (Select id_operacion, id_alma=id_almaO, 
				                   id_secc=id_seccO, id_dmov=31
							   From stoktrac
							union
							   Select id_operacion, id_alma=id_almaD, 
							          id_secc=id_seccD, id_dmov=32
								  From stoktrac) c on c.id_operacion = smay.id_operacion
				INNER JOIN stoktral tral ON c.id_operacion = tral.id_operacion
				INNER JOIN stokarti arti ON tral.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.estado=2 ' 
			+ @Filtro_fecha
			 + @Filtro_arti 
			 + @Filtro_destinoG

		EXECUTE (@CAD)     

/* ====================================================================
   Region 09
	-- Reversiones de Traslados
   ====================================================================
*/
     Select @Cad = 
	    'Insert #invactual 
			Select cod_dest = 
			            Case When c.id_alma is not null 
							 Then "A"+ dbo.GetCodigoAlmacen(c.id_alma)
							 Else "S" + dbo.GetCodigoSeccion(c.id_secc) 
							 End, 
				arti.id_fami,
				id_dest = Case When c.id_alma is not null 
							   Then c.id_alma 
							   Else c.id_secc 
							   End, 
				isalma = Case When c.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				c.id_alma, c.id_secc, tral.id_arti,
				 cantidad = Coalesce(CASE When c.id_dmov = 41 
				                          Then tral.cantidad
							              When c.id_dmov = 42 
										  Then -tral.cantidad_D 
										  End,0),
				importe = Coalesce(CASE When c.id_dmov = 41 
				                        Then tral.importe + tral.imp_desgaste
							            When c.id_dmov = 42 
										Then -1*(tral.importe + tral.imp_desgaste) 
										End,0),
				oper_entrada=convert(bit,case When c.id_dmov=41 
				                              Then 1 
											  Else 0 
											  End), 
				reversion=convert(bit,1), smay.fec_oper
			From stokoper smay
				INNER JOIN stokoper rvtda on rvtda.id_operacion=smay.id_operacion
				INNER JOIN (Select id_operacion, id_alma=id_almaO, 
				                   id_secc=id_seccO, id_dmov=41
							   From stoktrac
							union
							Select id_operacion, id_alma=id_almaD, 
							       id_secc=id_seccD, id_dmov=42
							   From stoktrac) c on c.id_operacion = rvtda.id_operacion
				INNER JOIN stoktral tral ON c.id_operacion = tral.id_operacion
				INNER JOIN stokarti arti ON tral.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.estado = 2 and tral.id_revertidor is not null '
			 + @Filtro_fecha 
			 + @Filtro_arti 
			 + @Filtro_destinoG

		EXECUTE (@CAD)     

/* ====================================================================
   Region 10
	--MultiTraslados
   ====================================================================
*/
     Select @Cad = 
		'Insert #invactual 
			Select cod_dest = Case When c.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(c.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(c.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When c.id_alma is not null 
							   Then c.id_alma 
							   Else c.id_secc 
							   End, 
				isalma = Case When c.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				c.id_alma, c.id_secc, mall.id_arti,
				 cantidad = Coalesce(CASE When id_dmov = 71 
										  Then -mall.cantidad
								          When id_dmov = 72 
										  Then mall.cantidad_D 
										  End,0),
				importe = Coalesce(CASE When id_dmov = 71 
				                        Then -1*(mall.importe + mall.imp_desgaste)
								        When id_dmov = 72 
										Then mall.importe + mall.imp_desgaste 
										End,0),
				oper_entrada=convert(bit,case When id_dmov=71 
											  Then 0 
											  Else 1 
											  End), 
				reversion=convert(bit,0), smay.fec_oper
			From stokoper smay
				INNER JOIN (Select id_operacion, id_alma=id_almaO, 
				                   id_secc=id_seccO, id_dmov=31
							   From stokmalc Where id_dato_or=dbo.GetId_Dato()
							union
							Select id_operacion, id_alma=id_almaD, 
							       id_secc=id_seccD, id_dmov=32
							   From stokmalc Where id_dato_or=dbo.GetId_Dato()) c 
					ON c.id_operacion = smay.id_operacion
				INNER JOIN stokmall mall ON c.id_operacion = mall.id_operacion
				INNER JOIN stokarti arti ON mall.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.estado=2 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destinoG

		EXECUTE (@CAD)     

/* ====================================================================
   Region 11
	-- Ajustes
   ====================================================================
*/
     Select @Cad = 
	    'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(ajul.cantidad,0),
				importe = Coalesce(ajul.importe,0), 
				oper_entrada=convert(bit,case When ajul.importe>=0 
											  Then 1 
											  Else 0 
											  End), 
	  		    reversion=convert(bit,0), smay.fec_oper
			From stoksmay smay
				INNER JOIN stoksaju saju ON saju.id_smay = smay.id_smay
				INNER JOIN stokajul ajul ON saju.id_ajul = ajul.id_ajul
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 5 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino

		EXECUTE (@CAD)     

/* ====================================================================
   Region 12
	--Reversiones de Ajustes
   ====================================================================
*/
     Select @Cad = 
	    'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(-ajul.cantidad,0),
				importe = Coalesce(-ajul.importe,0), 
				oper_entrada=convert(bit,case When ajul.importe>=0 
											  Then 0 
											  Else 1 
											  End), 
				reversion=convert(bit,1), smay.fec_oper
			From stoksmay smay
				INNER JOIN stoksaju saju ON saju.id_smay = smay.id_smay
				INNER JOIN stokajul ajul ON saju.id_ajul = ajul.id_ajul
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 6 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino
     
			  Execute(@Cad)

/* ====================================================
   Region 13
	-- Fijación de inventarios
   ====================================================
*/
     Select @Cad = 
		'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc)
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(invl.toma-invl.existencia,0),
				importe = Coalesce(invl.importe,0),
				oper_entrada=convert(bit,case When invl.toma-invl.existencia>=0 
											  Then 1 
											  Else 0 
											  End), 
				reversion=convert(bit,0), smay.fec_oper
			From stoksmay smay
				INNER JOIN stoksinv sinv ON sinv.id_smay = smay.id_smay
				INNER JOIN stokinvl invl ON sinv.id_invl = invl.id_invl
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 9 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino
    
		EXECUTE (@CAD)

/* ====================================================
   Region 14
	-- Despieces
      -- (art. origen)
   ====================================================
*/
    Select @Cad = 
	    'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(-dpc.cantidad,0),
				importe = Coalesce(-dpc.importe,0),
				oper_entrada=convert(bit,0), 
				reversion=convert(bit,0), smay.fec_oper
			From stoksmay smay
				INNER JOIN stokdesc dpc ON dpc.id_operacion = smay.id_operacion
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 101 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino
     
		EXECUTE (@CAD)

/* ====================================================
   Region 15
	-- Despieces
      -- (arts. destino) 
   ====================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = Case When c.id_alma is not null 
							       Then "A"+ dbo.GetCodigoAlmacen(c.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(c.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When c.id_alma is not null 
							   Then c.id_alma 
							   Else c.id_secc 
							   End, 
				isalma = Case When c.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				c.id_alma, c.id_secc, desl.id_arti,
				cantidad = Coalesce(desl.cantidad,0),
				importe = Coalesce(desl.importe,0),
				oper_entrada=convert(bit,1), 
				reversion=convert(bit,0), smay.fec_oper
			From stokoper smay
				INNER JOIN stokdesc c on c.id_operacion = smay.id_operacion
				INNER JOIN stokdesl desl ON desl.id_operacion = c.id_operacion
				INNER JOIN stokarti arti ON desl.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.estado=2 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destinoG

		EXECUTE (@CAD)

/* ====================================================
   Region 16
	--Reversiones de despieces
		-- artis origen
   ====================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null Then "A" Else "S" End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(dpc.cantidad,0),
				importe = Coalesce(dpc.importe,0),
				oper_entrada=convert(bit,1), reversion=convert(bit,1), smay.fec_oper
			From stoksmay smay
				INNER JOIN stokdesc dpc ON dpc.id_operacion = smay.id_operacion
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 111 ' + @Filtro_fecha + @Filtro_arti + @Filtro_destino

		EXECUTE (@CAD)

/* ====================================================
   Region 17
	--Reversiones de despieces
      -- (arts. destino) 
   ====================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = Case When c.id_alma is not null 
							       Then "A"+ dbo.GetCodigoAlmacen(c.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(c.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When c.id_alma is not null 
							   Then c.id_alma 
							   Else c.id_secc 
							   End, 
				isalma = Case When c.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				c.id_alma, c.id_secc, desl.id_arti,
				cantidad = Coalesce(-desl.cantidad,0),
				importe = Coalesce(-desl.importe,0),
				oper_entrada=convert(bit,0), 
				reversion=convert(bit,1), smay.fec_oper
			From stokoper smay
				INNER JOIN stokoper rvtda on smay.id_revertido=rvtda.id_operacion
				INNER JOIN stokdesc c on c.id_operacion = rvtda.id_operacion
				INNER JOIN stokdesl desl ON desl.id_operacion = c.id_operacion
				INNER JOIN stokarti arti ON desl.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.estado=2 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destinoG

	EXECUTE (@CAD)


/* ====================================================
   Region 18
	-- Rebaja Automatica
		  -- principal
   ====================================================
*/
   Select @Cad = 
		'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(-rebp.cantidad,0),
				importe = Coalesce(-rebp.importe,0),
				oper_entrada=convert(bit,0), 
				reversion=convert(bit,0), smay.fec_oper
			From stoksmay smay
				INNER JOIN stoksrep srep ON srep.id_smay = smay.id_smay
				INNER JOIN stokrebp rebp ON srep.id_rebp = rebp.id_rebp
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 121 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino
     
		EXECUTE (@CAD)

/* ====================================================
   Region 19
      -- alternativo
   ====================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(-reba.cantidad,0),
				importe = Coalesce(-reba.importe,0),
				oper_entrada=convert(bit,0), 
				reversion=convert(bit,0), smay.fec_oper
			From stoksmay smay
				INNER JOIN stoksrea srea ON srea.id_smay = smay.id_smay
				INNER JOIN stokreba reba ON srea.id_reba = reba.id_reba
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 122 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino

		EXECUTE (@CAD)

/* ====================================================
   Region 20
	-- Cambio de código
		  -- destino
   ====================================================
*/
   Select @Cad = 
      'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(camr.cantidad,0),
				importe = Coalesce(camr.importe,0),
				oper_entrada=convert(bit,1), 
				reversion=convert(bit,0), smay.fec_oper
			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 stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 131 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino
     
		EXECUTE (@CAD)

/* ====================================================
   Region 21
	-- Cambio de código
		-- orígenes
   ====================================================
*/
   Select @Cad = 
	  'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(-carl.cantidad,0),
				importe = Coalesce(-carl.importe,0),
				oper_entrada=convert(bit,0), 
				reversion=convert(bit,0), smay.fec_oper
			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 stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 132 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino

		EXECUTE (@CAD)

/* ====================================================
   Region 22
	-- Cambios de UM, orígenes y destinos
   ====================================================
*/
    Select @Cad = 
	   'Insert #invactual 
			Select cod_dest = Case When smay.id_alma is not null 
								   Then "A"+dbo.GetCodigoAlmacen(smay.id_alma) 
								   Else "S"+dbo.GetCodigoSeccion(smay.id_secc) 
								   End, 
				arti.id_fami,
				id_dest = Case When smay.id_alma is not null 
							   Then smay.id_alma 
							   Else smay.id_secc 
							   End, 
				isalma = Case When smay.id_alma is not null 
							  Then "A" 
							  Else "S" 
							  End,
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(CASE When id_dmov = 141 
									     Then -scmu.cantidad 
  	 				                     Else scmu.cantidad 
										 End,0),
				importe = Coalesce(CASE When id_dmov = 141 
									    Then -scmu.importe 
					                    Else scmu.importe 
										End,0),
				oper_entrada=convert(bit,case When id_dmov = 141 
				                              Then 0 
											  Else 1 
											  End), 
				reversion=convert(bit,0), smay.fec_oper
			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 stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov IN (141,142) ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino
     
			  Execute(@Cad)


/* ====================================================
   Region 23
	-- Elaboraciones y sus reversiones
	-- nuevo Filtro porque aquí la cabecera de elaboración sólo tiene sección
   ====================================================
*/
	declare @Filtro_destinoG1 varchar(8000)

	If @list_secc is not NULL
		Select @Filtro_destinoG1 = ' AND (C.id_secc is not null and C.id_secc in (' + @list_secc + ') )'

/* ====================================================
	Region 24
 	-- Elboraciones
	-- elaborados (se sacan de la cabecera del mvimiento)
   ====================================================
*/
	   Select @Cad = 
		  'Insert #invactual 
				Select cod_dest = "S" + dbo.GetCodigoSeccion(c.id_secc), 
					arti.id_fami,id_dest = c.id_secc, isalma = "S",
					NULL, c.id_secc, c.id_arti,
					cantidad = Coalesce(c.cant_r,0),
					importe = Coalesce(c.importe,0),
					oper_entrada=convert(bit,1), 
					reversion=convert(bit,0), smay.fec_oper
				From stokoper smay
					INNER JOIN stokeelo c on c.id_operacion = smay.id_operacion
					INNER JOIN stokarti arti ON c.id_arti = arti.id_arti
					INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
				Where smay.estado=2 ' 
				+ @Filtro_fecha 
				+ @Filtro_arti 
				+ @Filtro_destinoG1

		EXECUTE (@CAD)

/* ====================================================
	Region 25
 	-- ingredientes (se sacan del submayor) 
   ====================================================
*/
	Select @Cad = 
	   'Insert #invactual 
			Select cod_dest ="S" + dbo.GetCodigoSeccion(smay.id_secc), 
				arti.id_fami,id_dest = smay.id_secc, isalma =  "S",
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(-l.cant_r,0),
				importe = Coalesce(-l.importe,0),
				oper_entrada=convert(bit,0), 
				reversion=convert(bit,0), smay.fec_oper
			From stoksmay smay
				INNER JOIN stokseel dpc ON dpc.id_smay = smay.id_smay
				inner join stokeell l ON l.id_eell = dpc.id_eell
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 151 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino

		EXECUTE (@CAD)

/* ====================================================
	Region 26
	-- Reversión de Elboraciones
	-- elaborados (se sacan de la cabecera del movimiento)
   ====================================================
*/
   Select @Cad = 
      'Insert #invactual 
			Select cod_dest = "S" + dbo.GetCodigoSeccion(c.id_secc), 
				arti.id_fami,id_dest = c.id_secc, isalma = "S",
				NULL, c.id_secc, c.id_arti,cantidad = Coalesce(-c.cant_r,0),
				importe = Coalesce(-c.importe,0),
				oper_entrada=convert(bit,0), 
				reversion=convert(bit,1), smay.fec_oper
			From stokoper smay
				inner join stokoper oper on smay.id_revertido=oper.id_operacion
				INNER JOIN stokeelo c on c.id_operacion = oper.id_operacion
				INNER JOIN stokarti arti ON c.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_revertido is not null and smay.estado=2 '
			 + @Filtro_fecha 
			 + @Filtro_arti 
			 + @Filtro_destinoG1

		EXECUTE (@CAD)

/* ====================================================
	Region 27
     -- ingredientes (se sacan del submayor) 
   ====================================================
*/
	Select @Cad = 
	   'Insert #invactual 
			Select cod_dest ="S" + dbo.GetCodigoSeccion(smay.id_secc), 
				arti.id_fami,id_dest = smay.id_secc, isalma =  "S",
				smay.id_alma, smay.id_secc, smay.id_arti,
				cantidad = Coalesce(l.cant_r,0),
				importe = Coalesce(l.importe,0),
				oper_entrada=convert(bit,1), 
				reversion=convert(bit,1), smay.fec_oper
			From stoksmay smay
				INNER JOIN stokseel dpc ON dpc.id_smay = smay.id_smay
				inner join stokeell l ON l.id_eell = dpc.id_eell
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			Where smay.id_dmov = 161 ' 
			+ @Filtro_fecha 
			+ @Filtro_arti 
			+ @Filtro_destino

		EXECUTE (@CAD)

 Select @Filtro_opers=''

 If @reversiones=0
	Select @Filtro_opers=' Where reversion=0 '

  If @tipo_opers = 2
	begin
		If @Filtro_opers=''
			Select @Filtro_opers=' Where'

		Else
			Select @Filtro_opers=@Filtro_opers+' and'

		Select @Filtro_opers=@Filtro_opers+' oper_entrada=1 '
	End

  If @tipo_opers = 3
	begin
		If @Filtro_opers=''
			Select @Filtro_opers=' Where'
		Else
			Select @Filtro_opers=@Filtro_opers+' and'

		Select @Filtro_opers=@Filtro_opers+' oper_entrada=0 '
	End

  Select @order_by=''

  If @orderby = 1
	 Select @order_by=' order by cod_dest, SubString(Convert(char(19),fec_oper),1,11), cod_fami, cod_arti'

  If @orderby = 2
	 Select @order_by=' order by cod_dest, cod_fami,SubString(Convert(char(19),fec_oper),1,11)'

 If @orderby = 3
	Select @order_by=' order by cod_dest, SubString(Convert(char(19),fec_oper),1,11), cod_fami'


-- Crear tabla temporal
 If @orderby = 1
	CREATE TABLE #resum1 (
				cod_dest char(4),
				fec_oper datetime ,
				id_arti int,
						id_fami int,
				id_dest int,
				isalma char(1),
				cantidad numeric(12,4),
				importe numeric(12,2) )
Else
	CREATE TABLE #resum2 (
				cod_dest char(4),
				fec_oper datetime ,
				id_fami int,
				id_dest int,
				isalma char(1),
				cantidad numeric(12,4),
				importe numeric(12,2) )

   If @orderby = 1
	  Select @Cad = 'Insert #resum1 
	                    Select cod_dest, fec_oper=SubString(Convert(char(19),fec_oper),1,11), 
	                           id_arti, id_fami=MIN(id_fami), 
							   id_dest=MIN(id_dest), isalma=MIN(isalma), 
							   cantidad=SUM(cantidad), importe=SUM(importe) 
		                   From #invactual  ' 
						   + @Filtro_opers 
						   + ' group by cod_dest, SubString(Convert(char(19),fec_oper),1,11), id_arti  '

   If @orderby = 2
	  Select @Cad = 'Insert #resum2 
	                    Select cod_dest, fec_oper=SubString(Convert(char(19),fec_oper),1,11), 
						       id_fami, id_dest=MIN(id_dest), isalma=MIN(isalma), 
						       cantidad=SUM(cantidad), importe=SUM(importe) 
		                   From #invactual  ' 
						   + @Filtro_opers 
						   + ' group by cod_dest, id_fami, SubString(Convert(char(19),fec_oper),1,11)  '

   If @orderby = 3
	  Select @Cad = 'Insert #resum2 
	                    Select cod_dest, fec_oper=SubString(Convert(char(19),fec_oper),1,11), 
						       id_fami, id_dest=MIN(id_dest), 
							   isalma=MIN(isalma), cantidad=SUM(cantidad), 
							   importe=SUM(importe) 
		                   From #invactual  ' 
						   + @Filtro_opers 
						   + ' group by cod_dest, SubString(Convert(char(19),fec_oper),1,11), id_fami  '

   Execute(@Cad)

   If @orderby = 1
	   Select @Cad = 
	     'Select inv.*, 
				cod_arti=dbo.FullArtiCode(arti.id_arti), 
				desc_arti=arti.descripcion,
				cod_fami=dbo.FullFamiCode(inv.id_fami),
				cod_seccion = Case When inv.isalma="A" 
								   Then alma.codigo 
								   Else secc.codigo 
								   End, 
				desc_seccion = Case When inv.isalma="A" 
				                    Then alma.descripcion 
									Else secc.descripcion 
									End ,
				cod_unme = Case When inv.isalma="A" 
				                Then dbo.GetUMCode(arti.id_unme_al) 
								Else dbo.GetUMCode(arti.id_unme_se) 
								End,
				cant_unme = Case When inv.isalma="A" 
								 Then cant_unme_al 
								 Else cant_unme_se 
								 End,
				cod_grupo=grup.codigo, desc_grupo=grup.descripcion, 
				desc_fami=fami.descripcion
			From #resum1 inv
				Left Join stokalma alma On inv.id_dest = alma.id_alma
				Left Join stoksecc secc On inv.id_dest = secc.id_secc
				inner join stokarti arti on inv.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'  
			+ @order_by

  Else

	   Select @Cad = 
	      'Select inv.*,  
				  cod_fami=dbo.FullFamiCode(inv.id_fami),
				  cod_seccion = Case When inv.isalma="A" 
									 Then alma.codigo 
									 Else secc.codigo 
									 End, 
				  desc_seccion = Case When inv.isalma="A" 
									  Then alma.descripcion 
									  Else secc.descripcion 
									  End ,
				  cod_grupo=grup.codigo, desc_grupo=grup.descripcion, 
				  cod_short_fami=fami.codigo, desc_fami=fami.descripcion
				From #resum2 inv
					Left Join stokalma alma On inv.id_dest = alma.id_alma
					Left Join stoksecc secc On inv.id_dest = secc.id_secc
					inner join stokfami fami on inv.id_fami = fami.id_fami
					inner join stokgrup grup on fami.id_grup = grup.id_grup'  
				+ @order_by

   Execute(@Cad)

   drop table #invactual
    If @orderby = 1
	 drop table #resum1

    Else

	 drop table #resum2

End







