SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-- ====================================================================
-- Author:		Francis Bolaños Arias
-- CREATE date: 03/10/2012 05:06:22
-- Description:	Procedimiento 'Get_Submayor_Resumido' modificado 
--              para obtener el inventario de un almacen junto
--              con el de sus respectivas secciones
-- ====================================================================

IF EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'DSS_INV_GetInventario') )
DROP Procedure DSS_INV_GetInventario 
GO


CREATE PROCEDURE DSS_INV_GetInventario 
				@cod_desde VARCHAR(20), 
				@cod_hasta VARCHAR(20), 
				@list_alma VARCHAR(8000), 
				@list_secc VARCHAR(8000), 
				@fhasta    DATETIME, 
				@activos   BIT
AS

BEGIN

   DECLARE @Cad            VARCHAR(8000),
           @filtro_fecha   VARCHAR(8000) = '',
	       @filtro_arti    VARCHAR(8000),
	       @filtro_destino VARCHAR(8000),
	       @filtro_activos VARCHAR(8000),
	       @FechaInven     DATE
	       
   SELECT @FechaInven = fecha_dia FROM STOKTABL

	-- Creo tabla temporal
   CREATE TABLE #submayor 
			      (id_alma   INT, 
				   id_secc   INT,
				   id_arti   INT, 
				   cod_arti  VARCHAR(500),
				   desc_arti VARCHAR(500),
				   cantidad  NUMERIC(12,4),
				   importe   NUMERIC(12,2),
				   id_unme   INT,
				   id_fami   INT, 
				   fec_oper  DATETIME )
   
   CREATE CLUSTERED INDEX IDX_SubMayor_IdArti ON #submayor(id_arti)
   
   IF @fhasta IS NOT NULL
   BEGIN
       SELECT @filtro_fecha = ' AND Datediff(dy, smay.fec_oper, ' + CHAR(39)
                              + CONVERT(VARCHAR(4), YEAR(@fhasta)) + '-' 
                              + CONVERT(VARCHAR(2), MONTH(@fhasta)) + '-' 
                              + CONVERT(VARCHAR(2), DAY(@fhasta)) + CHAR(39) + ') >= 0 '
   END

   -- 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 = 'AND (smay.id_alma IN (SELECT id_alma FROM STOKALMA) or smay.id_secc IN (SELECT id_secc FROM STOKSECC)) '
   
   --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

   SELECT @filtro_activos = ''
   IF @activos = 1
       SELECT @filtro_activos = ' AND arti.activo = 1 '

   -- Comienzo a seleccionar el submayor
   -- Inventario Inicial   
   SELECT @Cad = 'INSERT #SUBMAYOR
	              SELECT smay.id_alma, 
	                     smay.id_secc, 
	                     smay.id_arti, 
	                     cod_arti  = arti.FullArtiCode, 
	                     desc_arti = arti.descripcion,
			             cantidad  = CONVERT(NUMERIC(12, 4), COALESCE(sini.cantidad, 0)),
			             importe   = CONVERT(NUMERIC(12, 2), COALESCE(sini.importe, 0)),
			             id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
			                         ELSE arti.id_unme_se 
			                         END,
			             id_fami, 
			             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
		          WHERE smay.id_dmov = 0 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- Compras
   SELECT @Cad = 'INSERT #SUBMAYOR 
	              SELECT smay.id_alma, 
	                     smay.id_secc, 
	                     smay.id_arti, 
		                 cod_arti  = arti.FullArtiCode, 
		                 desc_arti = arti.descripcion,
		                 cantidad  = COALESCE(cmpl.cantidad_D, 0),
		                 importe   = COALESCE(cmpl.importe, 0),
		                 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
		                             ELSE arti.id_unme_se 
		                             END,
		                 id_fami, 
		                 smay.fec_oper
	              FROM       stoksmay smay
		          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 smay.id_dmov = 1 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- Reversiones de compra
   SELECT @Cad = 'INSERT #SUBMAYOR 
	              SELECT smay.id_alma, 
	                     smay.id_secc, 
	                     smay.id_arti, 
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-cmpl.cantidad_D, 0),
						 importe   = COALESCE(-cmpl.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						             ELSE arti.id_unme_se 
						             END,
						 id_fami, 
						 smay.fec_oper
	              FROM       stoksmay smay
		          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 smay.id_dmov = 2  ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)


   -- Devoluciones
   SELECT @Cad = 'INSERT #SUBMAYOR
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti, 
				         cod_arti  = arti.FullArtiCode, 
				         desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-cmpl.cantidad_D, 0),
						 importe   = COALESCE(-cmpl.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						             ELSE arti.id_unme_se 
						             END,
						 id_fami, 
						 smay.fec_oper
			      FROM       stoksmay smay
				  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 smay.id_dmov = 320 ' +  @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
    EXECUTE(@Cad)

   --Traslados
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 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 
										           END, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 smay.fec_oper
				  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
				  WHERE smay.id_dmov IN (31, 32) ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- Reversiones de Traslados
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 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 
									               END, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 smay.fec_oper
				  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
				  WHERE smay.id_dmov IN (41, 42) ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- Ajustes
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(ajul.cantidad, 0),
						 importe   = COALESCE(ajul.importe, 0), 
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov = 5  ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   --Reversiones de Ajustes
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-ajul.cantidad, 0),
						 importe   = COALESCE(-ajul.importe, 0), 
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov = 6 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- Fijación de inventarios
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(invl.toma-invl.existencia, 0),
						 importe   = COALESCE(invl.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						             ELSE arti.id_unme_se 
						             END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov = 9  ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- Despieces
   -- (art. origen)
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-dpc.cantidad,0),
						 importe   = COALESCE(-dpc.importe,0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov = 101  ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- (arts. destino) 
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(desl.cantidad, 0),
						 importe   = COALESCE(desl.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						             ELSE arti.id_unme_se 
						             END,
						 id_fami, 
						 smay.fec_oper
				  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
				  WHERE smay.id_dmov = 102 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- Reversiones de Despieces
   -- (art. origen)
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,		
						 cantidad  = COALESCE(dpc.cantidad, 0),
						 importe   = COALESCE(dpc.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 smay.fec_oper
				  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 smay.id_dmov = 111 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- (arts. destino) 
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-desl.cantidad, 0),
						 importe   = COALESCE(-desl.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 smay.fec_oper
				  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
				  WHERE smay.id_dmov = 112 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- Rebaja Automatica
   -- principal
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-rebp.cantidad, 0),
						 importe   = COALESCE(-rebp.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov = 121 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- alternativo
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-reba.cantidad, 0),
						 importe   = COALESCE(-reba.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov = 122 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- Cambio de código
   -- destino
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(camr.cantidad, 0),
						 importe   = COALESCE(camr.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov = 131  ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- orígenes
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-carl.cantidad, 0),
						 importe   = COALESCE(-carl.importe, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov  =132 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- Cambios de UM, orígenes y destinos
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 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),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 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
				  WHERE smay.id_dmov IN (141,142) ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   --Traslados multialmacen
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 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 
											  END, 0),
						 id_unme   = CASE WHEN smay.id_alma IS NOT NULL THEN arti.id_unme_al 
						                  ELSE arti.id_unme_se 
						                  END,
						 id_fami, 
						 smay.fec_oper
				  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
				  WHERE smay.id_dmov IN (71,72)  ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- Elaboraciones
   -- elaborados
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(dpc.cant_r, 0),
						 importe   = COALESCE(dpc.importe, 0),
						 id_unme   = arti.id_unme_se,
						 id_fami, 
						 smay.fec_oper
				  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
				  WHERE smay.id_dmov = 152  ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha
     
   EXECUTE(@Cad)

   -- ingredientes
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(-desl.cant_r, 0),
						 importe   = COALESCE(-desl.importe, 0),
						 id_unme   = arti.id_unme_se,
						 id_fami, 
						 smay.fec_oper
				  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
				  WHERE smay.id_dmov = 151 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- Reversion de Elaboraciones
   -- elaborados
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,		
						 cantidad  = COALESCE(-dpc.cant_r, 0),
						 importe   = COALESCE(-dpc.importe, 0),
						 id_unme   = arti.id_unme_se,
						 id_fami, 
						 smay.fec_oper
				  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 smay.id_dmov = 162 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)

   -- ingredientes
   SELECT @Cad = 'INSERT #SUBMAYOR 
				  SELECT smay.id_alma, 
				         smay.id_secc, 
				         smay.id_arti,
						 cod_arti  = arti.FullArtiCode, 
						 desc_arti = arti.descripcion,
						 cantidad  = COALESCE(desl.cant_r, 0),
						 importe   = COALESCE(desl.importe, 0),
						 id_unme   = arti.id_unme_se,
						 id_fami, 
						 smay.fec_oper
				  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
				  WHERE smay.id_dmov = 161 ' + @filtro_arti + @filtro_destino + @filtro_activos + @filtro_fecha

   EXECUTE(@Cad)
   
   --select * from #submayor
   
   CREATE TABLE #Submayor2
			   (id_alma         INT,
				id_secc         INT,
				id_arti         INT,
				cod_arti        VARCHAR(500),
				desc_arti       VARCHAR(500),
				id_unme         INT,
				id_fami         INT,
				cantidad        NUMERIC(12, 4),
				importe         NUMERIC(12, 2),
				cod_alma        CHAR(100),
				desc_alma       VARCHAR(500),
				cod_secc        CHAR(100),
				desc_secc       VARCHAR(500),
				cod_unme        CHAR(100),
				desc_unme       VARCHAR(500),
				cod_dest        CHAR(100),
				cod_fami        CHAR(100),
				desc_fami       VARCHAR(500),
				id_grup         INT,
				cod_grup        CHAR(100),
				desc_grup       VARCHAR(500),
				existencia_act  NUMERIC(12, 4),
				importe_act     NUMERIC(12, 2))
   
   CREATE TABLE #Submayor3
			   (id_arti         INT,
				cod_arti        VARCHAR(500),
				desc_arti       VARCHAR(500),
				id_fami         INT,
				cantidad        NUMERIC(12, 4),
				importe         NUMERIC(12, 2),
				cod_unme        CHAR(100),
				desc_unme       VARCHAR(500),
				cod_fami        CHAR(100),
				desc_fami       VARCHAR(500),
				id_grup         INT,
				cod_grup        CHAR(100),
				desc_grup       VARCHAR(500),
				existencia_act  NUMERIC(12, 4),
				importe_act     NUMERIC(12, 2),
				fecha           DATE )
   
   LINENO 0

   IF @fhasta IS NOT NULL
   BEGIN
       -- almacenes
       INSERT #Submayor2
	   SELECT su.id_alma, 
	          id_secc = CONVERT(INT, NULL), 
	          su.id_arti, 
	          su.cod_arti, 
	          su.desc_arti, 
			  su.id_unme, 
			  su.id_fami,
			  cantidad  = SUM(su.cantidad),
			  importe   = SUM(su.importe),
			  cod_alma  = alma.codigo, 
			  desc_alma = alma.descripcion, 
			  cod_secc  = CONVERT(char(3),NULL), 
			  desc_secc = CONVERT(VARCHAR(40),NULL),
			  cod_unme  = unme.codigo, 
			  desc_unme = unme.descripcion,
			  cod_dest  = 'A' + alma.codigo,
			  cod_fami  = LTRIM(RTRIM(grup.codigo)) + RTRIM(LTRIM(fami.codigo)), 
			  desc_fami = fami.descripcion,
			  grup.id_grup, 
			  cod_grup  = grup.codigo, 
			  desc_grup = grup.descripcion,
			  existencia_act = COALESCE(alex.existencia, 0),
			  importe_act    = COALESCE(alex.importe, 0)
      FROM       #SUBMAYOR su
      INNER JOIN stokalma  alma ON su.id_alma   = alma.id_alma
      INNER JOIN stokunme  unme ON su.id_unme   = unme.id_unme
      INNER JOIN stokfami  fami ON su.id_fami   = fami.id_fami
      INNER JOIN stokgrup  grup ON fami.id_grup = grup.id_grup
      LEFT JOIN  stokalex  alex ON su.id_arti   = alex.id_arti AND su.id_alma = alex.id_alma
      WHERE su.id_alma IS NOT NULL 
        AND Datediff(dy, su.fec_oper, @fhasta) >= 0
      GROUP BY su.id_alma, su.id_arti, 
               su.cod_arti, su.desc_arti, 
               su.id_unme,su.id_fami, 
               alma.codigo, alma.descripcion,
               unme.codigo, unme.descripcion, 
			   fami.codigo, fami.descripcion,
			   grup.id_grup, grup.codigo, grup.descripcion,
			   alex.existencia, alex.importe 
	  --UNION ALL
	  -- secciones	
	  INSERT #Submayor2	
	  SELECT id_alma  = NULL, 
	         su.id_secc, 
	         su.id_arti, 
	         su.cod_arti, 
	         su.desc_arti, 
			 su.id_unme, 
			 su.id_fami,
			 cantidad  = SUM(su.cantidad),
			 importe   = SUM(su.importe),
			 cod_alma  = NULL, 
			 desc_alma = NULL, 
			 cod_secc  = secc.codigo, 
			 desc_secc = secc.descripcion,
			 cod_unme  = unme.codigo, 
			 desc_unme = unme.descripcion,
			 cod_dest  = 'S' + secc.codigo,
			 cod_fami  = LTRIM(RTRIM(grup.codigo)) + RTRIM(LTRIM(fami.codigo)), 
			 desc_fami = fami.descripcion,
			 grup.id_grup, 
			 cod_grup  = grup.codigo, 
			 desc_grup = grup.descripcion,
			 existencia_act = COALESCE(seex.existencia, 0),
			 importe_act    = COALESCE(seex.importe, 0)
      FROM       #SUBMAYOR su
      INNER JOIN stoksecc secc ON su.id_secc = secc.id_secc
      INNER JOIN stokunme unme ON su.id_unme = unme.id_unme
      INNER JOIN stokfami fami ON su.id_fami = fami.id_fami
      INNER JOIN stokgrup grup ON fami.id_grup = grup.id_grup
      LEFT JOIN  stokseex seex ON su.id_arti = seex.id_arti 
            AND  su.id_secc = seex.id_secc
	  WHERE  su.id_alma IS NULL 
	     AND Datediff(dy, su.fec_oper, @fhasta) >= 0
	  GROUP BY su.id_secc, su.id_arti, su.cod_arti,
	           su.desc_arti, su.id_unme, 
			   su.id_fami, secc.codigo,
			   secc.descripcion,
			   unme.codigo, unme.descripcion, 
			   fami.codigo, fami.descripcion,
			   grup.id_grup, grup.codigo, grup.descripcion,
			   seex.existencia, seex.importe
	  ORDER BY cod_dest, cod_arti
	  
	  --SELECT * FROM #Submayor2 
	  --INNER JOIN STOKARTI ON #Submayor2.id_arti = STOKARTI.id_arti
	  --ORDER BY #Submayor2.id_fami, #Submayor2.id_grup, #Submayor2.id_arti ASC
	  
	  INSERT #Submayor3
	  SELECT --su.id_alma,
			 --su.id_secc,
			 MAX(su.id_arti) AS id_arti,
			 MAX(su.cod_arti) AS cod_arti,
			 MAX(su.desc_arti) AS desc_arti,
			 --ar.id_unme_al AS id_unme,
			 MAX(su.id_fami) AS id_fami,
			 SUM(su.cantidad / CASE ISNULL(su.id_secc, 0) 
			                        WHEN 0 
			                        THEN 1
			                        ELSE ar.cant_unme_se
			                        END) AS cantidad,
			 SUM(su.importe) AS importe,
			 --su.cod_alma,
			 --su.desc_alma,
			 --su.cod_secc,
			 --su.desc_secc,
			 MAX(um.codigo) AS cod_unme,
			 MAX(um.descripcion) AS desc_unme,
			 --su.cod_dest,
			 MAX(su.cod_fami) AS cod_fami,
			 MAX(su.desc_fami) AS desc_fami,
			 MAX(su.id_grup) AS id_grup,
			 MAX(su.cod_grup) AS cod_grup,
			 MAX(su.desc_grup) AS desc_grup,
			 SUM(su.existencia_act / CASE ISNULL(su.id_secc, 0) 
			                              WHEN 0 
			                              THEN 1
			                              ELSE ar.cant_unme_se
			                              END) AS existencia_act,
			 SUM(su.importe_act) AS importe_act,
			 CONVERT(DATE, @FechaInven) AS fecha
			 --,MAX(ar.cant_unme_se) AS cant_unme_se
	  FROM #Submayor2 su
	  INNER JOIN STOKARTI ar ON su.id_arti    = ar.id_arti
	  INNER JOIN STOKUNME um ON ar.id_unme_al = um.id_unme
	  GROUP BY su.id_arti
	  ORDER BY id_fami, id_grup, id_arti ASC
	  
   END
   ELSE
      BEGIN
      
		  -- almacenes
		  INSERT #Submayor2
		  SELECT su.id_alma, 
		         id_secc = CONVERT(INT, NULL), 
		         su.id_arti, 
		         su.cod_arti, 
				 su.desc_arti, 
				 su.id_unme, 
				 su.id_fami,
				 cantidad  = SUM(su.cantidad),
				 importe   = SUM(su.importe),
				 cod_alma  = alma.codigo, 
				 desc_alma = alma.descripcion, 
				 cod_secc  = CONVERT(char(3), NULL), 
				 desc_secc = CONVERT(VARCHAR(40), NULL),
				 cod_unme  = unme.codigo, 
				 desc_unme = unme.descripcion,
				 cod_dest  = 'A' + alma.codigo,
				 cod_fami  = RTRIM(LTRIM(grup.codigo)) + LTRIM(RTRIM(fami.codigo)), 
				 desc_fami = fami.descripcion,
				 grup.id_grup, 
				 cod_grup  = grup.codigo, 
				 desc_grup = grup.descripcion,
				 existencia_act = COALESCE(alex.existencia, 0),
				 importe_act    = COALESCE(alex.importe, 0)
		  FROM       #SUBMAYOR su
		  INNER JOIN stokalma alma ON su.id_alma   = alma.id_alma
		  INNER JOIN stokunme unme ON su.id_unme   = unme.id_unme
		  INNER JOIN stokfami fami ON su.id_fami   = fami.id_fami
		  INNER JOIN stokgrup grup ON fami.id_grup = grup.id_grup
		  LEFT  JOIN stokalex alex ON su.id_arti   = alex.id_arti 
		        AND  su.id_alma = alex.id_alma
		  WHERE su.id_alma IS NOT NULL
		  GROUP BY su.id_alma, su.id_arti, 
		           su.cod_arti, su.desc_arti, 
				   su.id_unme, su.id_fami, 
				   alma.codigo, alma.descripcion,
				   unme.codigo, unme.descripcion, 
				   fami.codigo, fami.descripcion, 
				   grup.id_grup, grup.codigo, grup.descripcion,
				   alex.existencia, alex.importe
		  -- UNION ALL
		  -- secciones
		  INSERT #Submayor2
		  SELECT NULL, 
		         su.id_secc, 
		         su.id_arti, 
		         su.cod_arti, 
		         su.desc_arti, 
				 su.id_unme, 
				 su.id_fami,
				 cantidad  = SUM(su.cantidad),
				 importe   = SUM(su.importe),
				 cod_alma  = NULL, desc_alma = NULL, 
				 cod_secc  = secc.codigo, 
				 desc_secc = secc.descripcion,
				 cod_unme  = unme.codigo, 
				 desc_unme = unme.descripcion,
				 cod_dest  = 'S' + secc.codigo,
				 cod_fami  = RTRIM(LTRIM(grup.codigo)) + LTRIM(RTRIM(fami.codigo)), 
				 desc_fami = fami.descripcion,
				 grup.id_grup, 
				 cod_grup  = grup.codigo, 
				 desc_grup = grup.descripcion,
				 existencia_act = COALESCE(seex.existencia, 0),
				 importe_act = COALESCE(seex.importe, 0)
	      FROM       #SUBMAYOR su
		  INNER JOIN stoksecc  secc ON su.id_secc   = secc.id_secc
		  INNER JOIN stokunme  unme ON su.id_unme   = unme.id_unme
		  INNER JOIN stokfami  fami ON su.id_fami   = fami.id_fami
		  INNER JOIN stokgrup  grup ON fami.id_grup = grup.id_grup
		  LEFT  JOIN stokseex  seex ON su.id_arti   = seex.id_arti 
		        AND  su.id_secc = seex.id_secc
		  WHERE su.id_alma IS NULL
		  GROUP BY su.id_secc, su.id_arti, 
		           su.cod_arti, su.desc_arti, 
				   su.id_unme, su.id_fami, 
				   secc.codigo, secc.descripcion, 
				   unme.codigo, unme.descripcion, 
				   fami.codigo, fami.descripcion,
				   grup.id_grup, grup.codigo, grup.descripcion,
				   seex.existencia, seex.importe
		  ORDER BY cod_dest, cod_grup, cod_fami, cod_arti
		  
		  INSERT #Submayor3
		  SELECT --su.id_alma,
				 --su.id_secc,
				 MAX(su.id_arti) AS id_arti,
				 MAX(su.cod_arti) AS cod_arti,
				 MAX(su.desc_arti) AS desc_arti,
				 --ar.id_unme_al AS id_unme,
				 MAX(su.id_fami) AS id_fami,
				 SUM(su.cantidad / CASE ISNULL(su.id_secc, 0) 
										WHEN 0 
										THEN 1
										ELSE ar.cant_unme_se
										END) AS cantidad,
				 SUM(su.importe) AS importe,
				 --su.cod_alma,
				 --su.desc_alma,
				 --su.cod_secc,
				 --su.desc_secc,
				 MAX(um.codigo) AS cod_unme,
				 MAX(um.descripcion) AS desc_unme,
				 --su.cod_dest,
				 MAX(su.cod_fami) AS cod_fami,
				 MAX(su.desc_fami) AS desc_fami,
				 MAX(su.id_grup) AS id_grup,
				 MAX(su.cod_grup) AS cod_grup,
				 MAX(su.desc_grup) AS desc_grup,
				 SUM(su.existencia_act / CASE ISNULL(su.id_secc, 0) 
											  WHEN 0 
											  THEN 1
											  ELSE ar.cant_unme_se
											  END) AS existencia_act,
				 SUM(su.importe_act) AS importe_act,
			     CONVERT(DATE, @FechaInven) AS fecha
				 --,MAX(ar.cant_unme_se) AS cant_unme_se
		  FROM #Submayor2 su
		  INNER JOIN STOKARTI ar ON su.id_arti    = ar.id_arti
		  INNER JOIN STOKUNME um ON ar.id_unme_al = um.id_unme
		  GROUP BY su.id_arti
		  ORDER BY id_fami, id_grup, id_arti ASC
	  
      END
      
      SELECT id_arti,
			 LTRIM(RTRIM(cod_arti)) cod_arti,
			 LTRIM(RTRIM(desc_arti)) desc_arti,
			 id_fami,
			 cantidad,
			 importe,
			 LTRIM(RTRIM(cod_unme)) cod_unme,
			 LTRIM(RTRIM(desc_unme)) desc_unme,
			 LTRIM(RTRIM(cod_fami)) cod_fami,
			 LTRIM(RTRIM(desc_fami)) desc_fami,
			 id_grup,
			 cod_grup,
			 LTRIM(RTRIM(desc_grup)) desc_grup,
			 existencia_act,
			 importe_act,
			 fecha,
             CASE cantidad 
                  WHEN 0 
                  THEN 0
				  ELSE importe / cantidad
				  END AS coste_p_art,
			 CASE existencia_act 
			      WHEN 0 
			      THEN 0
				  ELSE importe_act / existencia_act
				  END AS coste_p_art_act	  
	  FROM #Submayor3

   DROP TABLE #SUBMAYOR
   
   DROP TABLE #SUBMAYOR2
   
   DROP TABLE #SUBMAYOR3

END













