
/****** Object:  StoredProcedure [dbo].[Get_Submayor_Resumido]    Script Date: 05/02/2016 11:42:18 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER  PROCEDURE Get_Submayor_Resumido
                 @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_arti varchar(300),
	       @filtro_destino varchar(8000),
	       @filtro_activos varchar(8000)

	-- Creo tabla temporal
	create table #submayor (
							   id_alma int, 
							   id_secc int,
							   id_arti int, 
							   cod_arti varchar(20),
							   desc_arti varchar(40),
							   cantidad numeric(20,4),
							   importe numeric(20,4),
								id_unme int,
							   id_fami int, 
							   fec_oper datetime
									)

   -- 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

   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

   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 

   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 

   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 
     
    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 
     
   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 
     
   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 
     
   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 
     
   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 
     
   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 
     
  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 

   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 

   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 

   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
     
   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 

   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 
     
   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 

   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 
     
   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 
     
   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 
     
  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 

   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 

   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 

   Execute(@Cad)

   If @fhasta is not null
   Begin
		-- almacenes
		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		
		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
   End
   Else
      Begin
			-- almacenes
			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)
			--into #Temp_10	
			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
			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
      End

   drop table #SUBMAYOR

END













