SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

--- SIN LOS CEROSSSS, ARREGLADOS LOS PARENTESIS
--- con suma okokokokokokok

CREATE PROCEDURE WINV_InvActual
					@Cod_Desde varchar(20), 
					@Cod_Hasta varchar(20), 
					@List_Alma varchar(8000), 
					@List_Secc varchar(8000), 
					@FDesde datetime, 
					@FHasta datetime
AS
BEGIN
/*
   Date: Apr 13,2016

*/

    Declare	@Cad varchar(8000),
			@filtro_arti varchar(200),
			@filtro_destino varchar(8000)

/* ======================================
   Region 01
   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 = ''

   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 + ')) '

   Else

      Begin 

         If @list_alma is not NULL
            Select @filtro_destino = ' AND smay.id_alma in (' 
			     + @list_alma + ') '
         Else

	If @list_secc is not NULL
       Select @filtro_destino = ' AND smay.id_secc in (' 
	        + @list_secc + ') '
      End

/* ======================================
   Region 02
   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(12,4),
	   importe numeric(12,2),
	   id_dmov int, 
	   fec_oper datetime,  
	   id_unme int,
	   cant_um numeric(9,4) )

/* ======================================
   Region 03
   Comienzo a seleccionar el invactual
   Inventario Inicial   
   ======================================
*/
	Select @Cad = 
         'INSERT #invactual 
			Select smay.id_alma, smay.id_secc, smay.id_arti, 
					cod_arti = dbo.FullArtiCode(smay.id_arti), 
					desc_arti = arti.descripcion,
					cantidad=convert(numeric(12,4),coalesce(sini.cantidad,0)),
					importe=convert(numeric(12,2),coalesce(sini.importe,0)),
					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 = Case When smay.id_alma is not null 
								   Then arti.cant_unme_al 
								   Else arti.cant_unme_se End 
		  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 Datediff(dy, fec_oper, '
		      + coalesce(''''
			  + convert(char(10),@fhasta,101)
			  + '''','fec_oper')
			  + ') >= 0 and 
		   smay.id_dmov=0 ' 
		   + @filtro_arti 
		   + @filtro_destino

	EXECUTE (@CAD)


/* ======================================
   Region 04
	Compras
   ======================================
*/
    Select @Cad = 
	   'INSERT #invactual 
		  Select smay.id_alma, smay.id_secc, smay.id_arti, 
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(cmpl.cantidad_D,0),
				  importe = coalesce(cmpl.importe,0),
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se End 
			From stoksmay smay
				  INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
				  INNER JOIN stokscmp scmp ON scmp.id_smay=smay.id_smay
				  INNER JOIN stokcmpl cmpl ON scmp.id_cmpl=cmpl.id_cmpl
				  INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
		 Where Datediff(dy, oper.fec_oper, '
		     + coalesce(''''
			 + convert(char(10),@fhasta,101)
			 + '''','oper.fec_oper')
			 + ') >= 0 and
		  smay.id_dmov = 1  ' 
		  + @filtro_arti 
		  + @filtro_destino 

	EXECUTE (@CAD)

/* ======================================
   Region 05
	Reversiones de Compras
   ======================================
*/

      Select @Cad = 
	    'INSERT #invactual 
		 Select smay.id_alma, smay.id_secc, smay.id_arti, 
			  cod_arti = dbo.FullArtiCode(smay.id_arti), 
			  desc_arti = arti.descripcion,
			  cantidad = coalesce(-cmpl.cantidad_D,0),
			  importe = coalesce(-cmpl.importe,0),
			  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 = Case When smay.id_alma is not null 
			                 Then arti.cant_unme_al 
							 Else arti.cant_unme_se End 
		 From stoksmay smay
			  INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			  INNER JOIN stokscmp scmp ON scmp.id_smay=smay.id_smay
			  INNER JOIN stokcmpl cmpl ON scmp.id_cmpl=cmpl.id_cmpl
			  INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
		 Where Datediff(dy, oper.fec_oper, '
		     + coalesce(''''
			 + convert(char(10),@fhasta,101)
			 + '''','oper.fec_oper')
			 + ') >= 0 and
		  smay.id_dmov = 2  ' 
		  + @filtro_arti 
		  + @filtro_destino 

	EXECUTE (@CAD)


/* ======================================
   Region 06
	Devoluciones
   ======================================
*/
     Select @Cad = 
	    'INSERT #invactual 
		  Select smay.id_alma, smay.id_secc, smay.id_arti, 
			  cod_arti = dbo.FullArtiCode(smay.id_arti), 
			  desc_arti = arti.descripcion,
			  cantidad = coalesce(-cmpl.cantidad_D,0),
			  importe = coalesce(-cmpl.importe,0),
			  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 = Case When smay.id_alma is not null 
			                 Then arti.cant_unme_al 
							 Else arti.cant_unme_se End 
		 From stoksmay smay
			  INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			  INNER JOIN stoksdev scmp ON scmp.id_smay=smay.id_smay
			  INNER JOIN stokdevl cmpl ON scmp.id_devl=cmpl.id_devl
			  INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
		 Where Datediff(dy, oper.fec_oper, '
		     + coalesce(''''
			 + convert(char(10),@fhasta,101)
			 + '''','oper.fec_oper')
			 + ') >= 0 and
		  smay.id_dmov = 320  ' 
		  + @filtro_arti 
		  + @filtro_destino 

	EXECUTE (@CAD)


/* ======================================
   Region 07
	Traslados
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = 
				      coalesce(CASE WHEN id_dmov = 31 
					                THEN tral.cantidad
					                WHEN id_dmov = 32 
									THEN tral.cantidad_D END,0),
				  importe = 
				      coalesce(CASE WHEN id_dmov = 31 
					                THEN tral.importe + tral.imp_desgaste
					                WHEN id_dmov = 32 
									THEN tral.importe + tral.imp_desgaste END,0),
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se End  
			 From stoksmay smay
				  INNER JOIN stokstra stra ON stra.id_smay = smay.id_smay
				  INNER JOIN stoktral tral ON stra.id_tral = tral.id_tral
				  INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				  INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			  smay.id_dmov IN (31,32) ' 
			  +  @filtro_arti 
			  + @filtro_destino 

			EXECUTE (@CAD)     

/* ======================================
   Region 08
	-- Reversiones de Traslados
   ======================================
*/
     Select @Cad = 
	    'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = 
				       coalesce(CASE WHEN id_dmov = 41 
					                 THEN -tral.cantidad
					                 WHEN id_dmov = 42 
									 THEN -tral.cantidad_D 
									 END,0),
				  importe = 
				       coalesce(CASE WHEN id_dmov = 41 
					                 THEN -tral.importe - tral.imp_desgaste
					                 WHEN id_dmov = 42 
									 THEN -tral.importe - tral.imp_desgaste 
									 END,0),
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End  
			 From stoksmay smay
				  INNER JOIN stokstra stra ON stra.id_smay = smay.id_smay
				  INNER JOIN stoktral tral ON stra.id_tral = tral.id_tral
				  INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				  INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov IN (41,42) ' 
			   + @filtro_arti 
			   + @filtro_destino 

			EXECUTE (@CAD)     

/* ======================================
   Region 09
	-- Ajustes
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(ajul.cantidad,0),
				  importe = coalesce(ajul.importe,0), 
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End 
			 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 stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			 smay.id_dmov = 5 ' 
			 + @filtro_arti 
			 + @filtro_destino 

		EXECUTE (@CAD)     


/* ======================================
   Region 10
	--Reversiones de Ajustes
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(-ajul.cantidad,0),
				  importe = coalesce(-ajul.importe,0), 
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End 
			 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 stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			    smay.id_dmov = 6 ' 
				+ @filtro_arti 
				+ @filtro_destino 
     
		  Execute(@Cad)

/* ======================================
   Region 11
	-- Fijación de inventarios
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(invl.toma-invl.existencia,0),
				  importe = coalesce(invl.importe,0),
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End 
			 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 stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 +') >= 0 and
				smay.id_dmov = 9  ' 
				+ @filtro_arti 
				+ @filtro_destino 
    
			EXECUTE (@CAD)

/* ======================================
   Region 12
	-- Despieces
      -- (art. origen)
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(dpc.cantidad,0),
				  importe = coalesce(dpc.importe,0),
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End  
			 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 stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov = 101 ' 
			   + @filtro_arti 
			   + @filtro_destino 
     
			EXECUTE (@CAD)


/* ======================================
   Region 13
	-- Despieces
       -- (arts. destino) 
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(desl.cantidad,0),
				  importe = coalesce(desl.importe,0),
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End
			 From stoksmay smay
				  INNER JOIN stoksdel sdel ON sdel.id_smay = smay.id_smay
				  INNER JOIN stokdesl desl ON desl.id_desl = sdel.id_desl
				  INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				  INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
				 + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
				smay.id_dmov = 102 ' 
				+ @filtro_arti 
				+ @filtro_destino 

			EXECUTE (@CAD)


/* ======================================
   Region 14
   Reversiones de Despieces
      -- (art. origen)
   ======================================
*/
     Select @Cad = 
	    'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(-dpc.cantidad,0),
				  importe = coalesce(-dpc.importe,0),
				  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 = Case When smay.id_alma is not null 
								 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End 
			 From stoksmay smay
					INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
					INNER JOIN stokdesc dpc ON dpc.id_operacion = oper.id_revertido
					INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
			 Where Datediff(dy, oper.fec_oper, '
				 + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov = 111 ' 
			   + @filtro_arti 
			   + @filtro_destino 
     
			EXECUTE (@CAD)

/* ======================================
   Region 15
   Reversiones de Despieces
      -- (arts. destino) 
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(-desl.cantidad,0),
				  importe = coalesce(-desl.importe,0),
				  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 = Case When smay.id_alma is not null 
								 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End
			 From stoksmay smay
				  INNER JOIN stoksdel sdel ON sdel.id_smay = smay.id_smay
				  INNER JOIN stokdesl desl ON desl.id_desl = sdel.id_desl
				  INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				  INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov = 112 ' 
			   + @filtro_arti 
			   + @filtro_destino 

			EXECUTE (@CAD)


/* ======================================
   Region 16
    Rebaja Automatica
      -- principal
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(rebp.cantidad,0),
				  importe = coalesce(rebp.importe,0),
				  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 = Case When smay.id_alma is not null 
				                 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End 
			 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 stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov = 121 ' 
			   + @filtro_arti 
			   + @filtro_destino
     
			EXECUTE (@CAD)

/* ======================================
   Region 17
      -- alternativo
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				  cantidad = coalesce(reba.cantidad,0),
				  importe = coalesce(reba.importe,0),
				  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 = Case When smay.id_alma is not null 
								 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End
			 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 stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov = 122  ' 
			   + @filtro_arti 
			   + @filtro_destino 

			EXECUTE (@CAD)

/* ======================================
   Region 18
    Cambio de código
      -- destino
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), desc_arti = arti.descripcion,
				  cantidad = coalesce(camr.cantidad,0),
				  importe = coalesce(camr.importe,0),
				  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 = Case When smay.id_alma is not null Then arti.cant_unme_al Else arti.cant_unme_se End 
			 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 Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov = 131  '
			   + @filtro_arti 
			   + @filtro_destino 
     
			EXECUTE (@CAD)


/* ======================================
   Region 19
      -- orígenes
   ======================================
*/
     Select @Cad = 
	    'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
			  cod_arti = dbo.FullArtiCode(smay.id_arti), 
			  desc_arti = arti.descripcion,
			  cantidad = coalesce(carl.cantidad,0),
			  importe = coalesce(carl.importe,0),
			  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 = Case When smay.id_alma is not null 
							 Then arti.cant_unme_al 
							 Else arti.cant_unme_se 
							 End
			 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 Datediff(dy, oper.fec_oper, '
			     + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov  =132 ' 
			   + @filtro_arti 
			   + @filtro_destino 

			EXECUTE (@CAD)


/* ======================================
   Region 20
    Cambios de UM, orígenes y destinos
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
			  cod_arti = dbo.FullArtiCode(smay.id_arti), 
			  desc_arti = arti.descripcion,
			  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),
			  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 = Case When smay.id_alma is not null 
							 Then arti.cant_unme_al 
							 Else arti.cant_unme_se 
							 End 
			 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 Datediff(dy, oper.fec_oper, '
				 + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 +') >= 0 and
				smay.id_dmov IN (141,142) ' 
				+ @filtro_arti 
				+ @filtro_destino 
     

		  Execute(@Cad)


/* ======================================
   Region 21
    --Traslados multialmacen
   ======================================
*/
      Select @Cad = 
	     'INSERT #invactual 
			 Select smay.id_alma, smay.id_secc, smay.id_arti,
				  cod_arti = dbo.FullArtiCode(smay.id_arti), 
				  desc_arti = arti.descripcion,
				   cantidad = coalesce(CASE WHEN id_dmov = 71 
										    THEN tral.cantidad
											WHEN id_dmov = 72 
											THEN tral.cantidad_D 
											END,0),
				  importe = coalesce(CASE WHEN id_dmov = 71 
										  THEN tral.importe + tral.imp_desgaste
										  WHEN id_dmov = 72 
										  THEN tral.importe + tral.imp_desgaste 
										  END,0),
				  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 = Case When smay.id_alma is not null 
								 Then arti.cant_unme_al 
								 Else arti.cant_unme_se 
								 End  
			 From stoksmay smay
				  INNER JOIN stoksmal stra ON stra.id_smay = smay.id_smay
				  INNER JOIN stokmall tral ON stra.id_mall = tral.id_mall
				  INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				  INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			 Where Datediff(dy, oper.fec_oper, '
				 + coalesce(''''
				 + convert(char(10),@fhasta,101)
				 + '''','oper.fec_oper')
				 + ') >= 0 and
			   smay.id_dmov IN (71,72)' 
			   +  @filtro_arti 
			   + @filtro_destino 

			EXECUTE (@CAD)     

/* ======================================
   Region 22
    -- Elaboraciones
	-- elaborados
   ======================================
*/
	Select @Cad = 
	   'INSERT #invactual 
			Select smay.id_alma, smay.id_secc, smay.id_arti,
					cod_arti = dbo.FullArtiCode(smay.id_arti), 
					desc_arti = arti.descripcion,
					cantidad = coalesce(dpc.cant_r,0),
					importe = coalesce(dpc.importe,0),
					smay.id_dmov, smay.fec_oper,  
					id_unme = arti.id_unme_se,
					cant_um = arti.cant_unme_se
			From stoksmay smay
				INNER JOIN stokeelo dpc ON dpc.id_operacion = smay.id_operacion
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			Where Datediff(dy, oper.fec_oper, '
				+ coalesce(''''
				+ convert(char(10),@fhasta,101)
				+ '''','oper.fec_oper')
				+ ') >= 0 and
				smay.id_dmov = 152 ' 
				+ @filtro_arti 
				+ @filtro_destino 

		EXECUTE (@CAD)


/* ======================================
   Region 23
	-- ingredientes 
   ======================================
*/
	Select @Cad = 
		'INSERT #invactual 
			Select smay.id_alma, smay.id_secc, smay.id_arti,
				cod_arti = dbo.FullArtiCode(smay.id_arti), 
				desc_arti = arti.descripcion,
				cantidad = coalesce(desl.cant_r,0),
				importe = coalesce(desl.importe,0),
				smay.id_dmov, smay.fec_oper,  
				id_unme = arti.id_unme_se,
				cant_um = arti.cant_unme_se
			From stoksmay smay
				INNER JOIN stokseel sdel ON sdel.id_smay = smay.id_smay
				INNER JOIN stokeell desl ON desl.id_eell = sdel.id_eell
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			Where Datediff(dy, oper.fec_oper, '
				+ coalesce(''''
				+ convert(char(10),@fhasta,101)
				+ '''','oper.fec_oper')
				+ ') >= 0 and
				smay.id_dmov = 151 ' 
				+  @filtro_arti 
				+ @filtro_destino 

		EXECUTE (@CAD)

/* ======================================
   Region 23
	-- Reversion de Elaboraciones
	-- elaborado
   ======================================
*/
	Select @Cad = 
		'INSERT #invactual 
			Select smay.id_alma, smay.id_secc, smay.id_arti,
				cod_arti = dbo.FullArtiCode(smay.id_arti), 
				desc_arti = arti.descripcion,
				cantidad = coalesce(-dpc.cant_r,0),
				importe = coalesce(-dpc.importe,0),
				smay.id_dmov, smay.fec_oper,  
				id_unme = arti.id_unme_se,
				cant_um = arti.cant_unme_se
			From stoksmay smay
				INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
				INNER JOIN stokeelo dpc ON dpc.id_operacion = oper.id_revertido
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
			Where Datediff(dy, oper.fec_oper, '
				+ coalesce(''''
				+ convert(char(10),@fhasta,101)
				+ '''','oper.fec_oper')
				+ ') >= 0 and
				smay.id_dmov = 162 ' 
				+ @filtro_arti 
				+ @filtro_destino 

		EXECUTE (@CAD)

/* ======================================
   Region 24
	-- ingredientes 
   ======================================
*/
	Select @Cad = 
	   'INSERT #invactual 
			Select smay.id_alma, smay.id_secc, smay.id_arti,
				cod_arti = dbo.FullArtiCode(smay.id_arti), 
				desc_arti = arti.descripcion,
				cantidad = coalesce(-desl.cant_r,0),
				importe = coalesce(-desl.importe,0),
				smay.id_dmov, smay.fec_oper,  
				id_unme = arti.id_unme_se ,
				cant_um = arti.cant_unme_se 
			From stoksmay smay
				INNER JOIN stokseel sdel ON sdel.id_smay = smay.id_smay
				INNER JOIN stokeell desl ON desl.id_eell = sdel.id_eell
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stokoper oper ON smay.id_operacion = oper.id_operacion
			Where Datediff(dy, oper.fec_oper, '
				+ coalesce(''''
				+ convert(char(10),@fhasta,101)
				+ '''','oper.fec_oper')
				+') >= 0 and
				smay.id_dmov = 161 ' 
				+ @filtro_arti 
				+ @filtro_destino 

			EXECUTE (@CAD)

/* ======================================
   Region 25
    Buscar saldos anteriores
   ======================================
*/
 
   Select id_alma, id_secc, id_arti, 
		  cod_arti,desc_arti,id_unme,cant_um,
		  sant_cantidad = SUM(case when 
		                           id_dmov in (1,2,32,42,72,102,112,131,142,0,5,6,9,320,152,162) 
								   then cantidad 
								    else -cantidad 
									end),
		  sant_importe =  SUM(case when 
								   id_dmov in (1,2,32,42,72,102,112,131,142,0,5,6,9,320,152,162) 
								   then importe 
								   else -importe 
								   end)
		  INTO #saldosant
		  From #invactual
		  Where Datediff(dy,fec_oper,
		        coalesce(@fdesde,fec_oper)) >0
		  GROUP BY id_alma,id_arti, 
		           id_secc,cod_arti,
				   desc_arti,id_unme,cant_um


  Select * 
     into #invactualA 
	 From #invactual 
	 Where id_alma is not null 
	   and Datediff(dy,coalesce(@fdesde,fec_oper),fec_oper)>=0  

   Select * 
      into #saldosantA From #saldosant 
	  Where id_alma is not null 
	   and (sant_cantidad !=0 
	        or sant_importe !=0)

	Select distinct id_alma,id_secc,id_arti 
	   into #combiA
	   From #invactualA
	  UNION 
	Select distinct id_alma,id_secc,id_arti 
	   From #saldosantA

	Select * 
	   into #invactualS 
	   From #invactual 
	   Where id_secc is not null 
		 and Datediff(dy,coalesce(@fdesde,fec_oper),fec_oper)>=0  

	Select * 
	   into #saldosantS 
	   From #saldosant 
	   Where id_secc is not null 
	     and (sant_cantidad !=0 
		      or sant_importe !=0)

	Select distinct id_alma,id_secc,id_arti 
	  into #combiS
	  From #invactualS
	UNION 
	Select distinct id_alma,id_secc,id_arti 
	  From #saldosantS


/* ======================================
   Region 26
    -- Para almacen
   ======================================
*/
  Select com.id_alma,com.id_secc,com.id_arti,
		  cod_arti=coalesce(su.cod_arti,sant.cod_arti),
		  desc_arti=coalesce(su.desc_arti,sant.desc_arti),  
		  cantidad=coalesce(su.cantidad,0.0000), importe=coalesce(su.importe,0.00), 
		  id_dmov=coalesce(su.id_dmov,-1),su.fec_oper,
		  id_unme=coalesce(su.id_unme,sant.id_unme),
		  cant_um=coalesce(su.cant_um,sant.cant_um),
		  cod_alma = alma.codigo, desc_alma = alma.descripcion, 
		  cod_secc = convert(char(3),null), desc_secc = convert(char(40),null) ,
		  cod_unme = unme.codigo, desc_unme = unme.descripcion,
		  stock_max = Coalesce(alex.stock_max, 0),
		  stock_min = Coalesce(alex.stock_min, 0),
		  existencia = Coalesce(alex.existencia, 0),
		  imp_actual = Coalesce(alex.importe, 0),
		  cod_dest =  'A' + alma.codigo,
		  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)

		From #combiA com
			left join #invactualA su on com.id_arti=su.id_arti 
			     and com.id_alma=su.id_alma 
			left join #saldosantA sant on com.id_arti=sant.id_arti 
			     and com.id_alma=sant.id_alma 
			inner Join stokalma alma On com.id_alma = alma.id_alma 
			inner Join stokunme unme On coalesce(su.id_unme,sant.id_unme) = unme.id_unme
			Left Join stokalex alex On com.id_arti = alex.id_arti 
			     and com.id_alma = alex.id_alma
			left Join stokdmov dmov On su.id_dmov = dmov.id_dmov
			inner join stokarti arti on com.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

	UNION ALL

/* ======================================
   Region 27
   -- Para seccion
   ======================================
*/
  Select com.id_alma,com.id_secc,com.id_arti,
		  cod_arti=coalesce(su.cod_arti,sant.cod_arti),
		  desc_arti=coalesce(su.desc_arti,sant.desc_arti),  
		  cantidad=coalesce(su.cantidad,0.0000), 
		  importe=coalesce(su.importe,0.00), 
		  id_dmov=coalesce(su.id_dmov,-1),su.fec_oper,
		  id_unme=coalesce(su.id_unme,sant.id_unme),
		  cant_um=coalesce(su.cant_um,sant.cant_um),
		  cod_alma = convert(char(3),null), desc_alma = convert(char(40),null), 
		  cod_secc = secc.codigo, desc_secc = secc.descripcion,
		  cod_unme = unme.codigo, desc_unme = unme.descripcion,
		  stock_max = Coalesce(seex.stock_max, 0),
		  stock_min = Coalesce(seex.stock_min, 0),
		  existencia = Coalesce(seex.existencia, 0),
		  imp_actual = Coalesce(seex.importe, 0),
		  cod_dest =  'S' + secc.codigo,
		  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)
	 From #combiS com
		left join #invactualS su on com.id_arti=su.id_arti 
		     and com.id_secc=su.id_secc 
		left join #saldosantS sant on com.id_arti=sant.id_arti 
			 and com.id_secc=sant.id_secc
		inner Join stoksecc secc On com.id_secc = secc.id_secc 
		inner Join stokunme unme On coalesce(su.id_unme,sant.id_unme) = unme.id_unme
		Left Join stokseex seex On com.id_arti = seex.id_arti 
		     and com.id_secc = seex.id_secc
		left Join stokdmov dmov On su.id_dmov = dmov.id_dmov
		inner join stokarti arti on com.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 cod_dest, coalesce(su.cod_arti,sant.cod_arti)


   drop table #invactual
   drop table #saldosant
   drop table #invactualA
   drop table #saldosantA
   drop table #combiA
   drop table #invactualS
   drop table #saldosantS
   drop table #combiS

END







