ALTER PROCEDURE [dbo].[sp_invAuxiliar_Existencia_Stim] 
(
@_fechai		date,
@_fechaf		date,
@_codbusi	varchar(100) ='0',
@_codbusf	varchar(100) ='ZZZZZZZZZZZZZZZZZZZ', 
@_famini     varchar(10) ='0',
@_famfin     varchar(10) = 'ZZZZZZZZZZZZZZZZZZ',
@_subini     varchar(10) = '0',
@_subfin     varchar(10) ='ZZZZZZZZZZZZZZZZZZZ',
@_codbod int = 0,
@_toma  varchar(100) = '0',
@_fechatoma  datetime= null,
@_stockvalorizado  int=0,
@omitircero int=0
) 

AS 
DECLARE @StartTime AS DATETIME = GETDATE()
SET NOCOUNT on

DECLARE @mesinicio INT = MONTH(@_fechai)
DECLARE @periodo INT = YEAR(@_fechai)
DECLARE @mesdesde INT = MONTH(@_fechai)
DECLARE @meshasta INT = MONTH(@_fechaf)


DECLARE @fechai		date = '01-07-2015'
DECLARE @fechaf		date= '31-07-2015'
DECLARE @codbusi	char(100)='0'
DECLARE @codbusf	char(100)='ZZZZZZZZZZZZZZZZZZZ'
DECLARE @famini     varchar(10)='0'

DECLARE @famfin     varchar(10)='ZZZZZZZZZZZZZZZZZZZ'
DECLARE @subini     varchar(10)='0'
DECLARE @subfin     varchar(10)='ZZZZZZZZZZZZZZZZZZZ'

DECLARE @codbod int 
DECLARE @toma varchar(100) 
DECLARE @fechatoma datetime
DECLARE @stockvalorizado int


SET @fechai		=@_fechai	
SET @fechaf		=@_fechaf	
SET @codbusi	=@_codbusi
SET @codbusf	=@_codbusf
SET @famini     =@_famini
SET @famfin     =@_famfin 
SET @subini     =@_subini  
SET @subfin     =@_subfin  
SET @codbod = @_codbod
SET @toma  = @_toma
SET @fechatoma = @_fechatoma
SET @stockvalorizado = @_stockvalorizado


SELECT        invArticulos.codbus, invArticulos.codfam, invArticulos.codsub, invUnidadMedida.abruni, invArticulos.nomart
,costo,nomfam,nomsub,CONVERT(bigint,0) as saldoinicial
INTO #TABLAARTICULOS
FROM            invArticulos INNER JOIN
                         invSubFamilia ON invArticulos.codfam = invSubFamilia.codfam AND invArticulos.codsub = invSubFamilia.codsub INNER JOIN
                         invFamilia ON invArticulos.codfam = invFamilia.codfam AND invSubFamilia.codfam = invFamilia.codfam INNER JOIN
                         invUnidadMedida ON invArticulos.coduni = invUnidadMedida.coduni
WHERE	invarticulos.codfam BETWEEN  rtrim(@famini) AND rtrim(@famfin)
AND		invarticulos.codsub BETWEEN  rtrim(@subini)  AND rtrim(@subfin) 
AND 	invarticulos.codbus BETWEEN  rtrim(@codbusi) AND rtrim(@codbusf)
AND     invfamilia.controlstock=1





CREATE CLUSTERED INDEX ix_tempCIndexBef ON #TABLAARTICULOS (codbus)


RAISERROR('into #TABLAARTICULOS',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()

--ALTER TABLE #TABLAARTICULOS ADD PRIMARY KEY CLUSTERED (codbus)


RAISERROR('ALTER TABLE #TABLAARTICULOS',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()

--CREATE TABLE #REFERENCIAS (codsii INT, numinv BIGINT, tipref1 INT, docref1 VARCHAR(50))


--INSERT INTO #REFERENCIAS
--SELECT        ENC.codsii, venDetalle.numinv, VenReferencias.codsiiref AS tipref1, VenReferencias.numinvref AS docref1
--FROM            venEncabezado AS ENC INNER JOIN
--                         venDetalle ON ENC.coddoc = venDetalle.coddoc AND ENC.grudoc = venDetalle.grudoc AND ENC.numinv = venDetalle.numinv INNER JOIN
--                         #TABLAARTICULOS as invArticulos  ON venDetalle.codbus = invArticulos.codbus INNER JOIN
--                         VenReferencias ON ENC.numinv = VenReferencias.numinv AND ENC.codsii = VenReferencias.codsii
--WHERE ENC.grudoc <> 50 
--AND VenReferencias.codsiiref IN(50,51,52)
--AND (ENC.periodo = @periodo)
--AND 1=2

--UNION ALL

--SELECT codsii,venDetalle.numinv,tipref1,docref1 
--FROM            venEncabezado ENC INNER JOIN
--                         venDetalle ON ENC.coddoc = venDetalle.coddoc AND ENC.grudoc = venDetalle.grudoc AND 
--                         ENC.numinv = venDetalle.numinv INNER JOIN
--                         #TABLAARTICULOS as invArticulos  ON venDetalle.codbus = invArticulos.codbus
--WHERE ENC.periodo = @periodo
--AND ENC.grudoc <> 50 
--AND ENC.tipref1 IN(50,51,52)
--AND ENC.tipref1 <> 0
--AND 1=2



--return 0

SELECT  coddoc,operac,Alias_doc INTO #DOCUMENTOINV FROM vendocumentos WHERE grudoc=1

SELECT  codsii,operac,Alias_doc INTO #DOCUMENTOVEN FROM vendocumentos WHERE grudoc=10 
AND  NOT (codsii=55 OR codsii=56 OR codsii=60 OR codsii=61 OR codsii=104 OR codsii=106) 


SELECT  codsii,operac,Alias_doc INTO #DOCUMENTONDNC FROM vendocumentos WHERE grudoc=10 
AND  (codsii=55 OR codsii=56 OR codsii=60 OR codsii=61 OR codsii=104 OR codsii=106) 



SELECT  codsii,operac,Alias_doc INTO #DOCUMENTOGUI FROM vendocumentos WHERE grudoc=50




CREATE NONCLUSTERED  INDEX Indice1 ON #DOCUMENTOINV (coddoc)
CREATE NONCLUSTERED  INDEX Indice2 ON #DOCUMENTOVEN (codsii)
CREATE NONCLUSTERED  INDEX Indice3 ON #DOCUMENTONDNC (codsii)
CREATE NONCLUSTERED  INDEX Indice4 ON #DOCUMENTOGUI (codsii)


SELECT  invDetalle.codbus, --ENTRADAS-CONSUMOS-DEVOLUCIONES anterior
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	1 as grudoc,
	invDetalle.coddoc, 
	hordoc as fecdoc, 
	invDetalle.numinv, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN 0 ELSE cast(invDetalle.cantidad as decimal(18,2)) END AS entrada, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN 0 ELSE cast(invDetalle.canalt as decimal(18,2)) END AS entradaalt,
	CASE #DOCUMENTOINV.operac WHEN '-' THEN  cast(invDetalle.cantidad as decimal(18,2)) ELSE 0 END AS salida,  
	CASE #DOCUMENTOINV.operac WHEN '-' THEN  cast(invDetalle.canalt as decimal(18,2)) ELSE 0 END AS salidaalt, 
	CASE invDetalle.coddoc WHEN 1 THEN ROUND(invDetalle.preuni*invEncabezado.monpar,4) ELSE #TABLAARTICULOS.costo END AS preuni, 
	invDetalle.costo,
    CASE #DOCUMENTOINV.operac WHEN '-' THEN 0 ELSE CASE invDetalle.coddoc WHEN 1 THEN CAST(ROUND(invDetalle.pretot*invEncabezado.monpar,0) AS DECIMAL) ELSE CAST(ROUND(invDetalle.costo*invDetalle.cantidad,0) AS DECIMAL) END END AS debe, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN CAST(ROUND(invDetalle.costo*invDetalle.cantidad,0) AS DECIMAL) ELSE 0 END AS haber, 
	#DOCUMENTOINV.Alias_doc,
	#DOCUMENTOINV.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), invDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), invDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(invDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,invEncabezado.fecing,
	nomfam,
	nomsub,
	0 AS codsii,
	CASE  WHEN invDetalle.coddoc=1 THEN 1 ELSE 2 END AS orden
	,hordoc as horadoc,
	convert(date,invEncabezado.fecdoc) as fecha,
	numlin as linea,0 AS orden2,
	invEncabezado.codbod as bodega
INTO #A
FROM          #TABLAARTICULOS  INNER JOIN
                         invDetalle INNER JOIN
                         invEncabezado ON invDetalle.coddoc = invEncabezado.coddoc AND invDetalle.numinv = invEncabezado.numinv ON 
                         #TABLAARTICULOS.codbus = invDetalle.codbus INNER JOIN
                         #DOCUMENTOINV ON invEncabezado.coddoc = #DOCUMENTOINV.coddoc
WHERE   (invencabezado.periodo = @periodo AND invencabezado.fecdoc < @fechai)
AND 	(invEncabezado.coddoc=1 OR invEncabezado.coddoc=2 OR invEncabezado.coddoc=4)  
AND 	(invEncabezado.estado <> 9)
AND 	(invDetalle.cantidad > 0)
AND     (invEncabezado.codbod=@codbod OR @codbod=0)



UNION ALL



SELECT InvDetalle.codbus,     --- TRASPASO SALIDA
	#TABLAARTICULOS.codfam, 
	#TABLAARTICULOS.codsub, 
	1 as grudoc,
	invDetalle.coddoc, 
	hordoc as fecdoc, 
	invDetalle.numinv, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	0 AS entrada, 
	0 AS entradaalt, 
	invDetalle.cantidad AS salida, 
	invDetalle.canalt AS salidaalt, 
	#TABLAARTICULOS.costo AS preuni, 
	invDetalle.costo AS costo, 
    0 AS debe, 
    CAST(ROUND(invDetalle.costo*invDetalle.cantidad,0) AS DECIMAL) AS haber, 
	#DOCUMENTOINV.Alias_doc,
	#DOCUMENTOINV.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), invDetalle.stockGralOrigen) ELSE CONVERT(DECIMAL(18,2), invDetalle.stockBodegaOrigen) END AS stock,
	0 as stockalt,
	CAST(ROUND(invDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,invEncabezado.fecing,
	nomfam,
	nomsub,
	0 AS codsii,
	2 AS orden,
	hordoc as horadoc,
	INVEncabezado.fecdoc as fecha,
	numlin as linea,1 AS orden2,
	invEncabezado.codbod as bodega
FROM          #TABLAARTICULOS  INNER JOIN
                         invDetalle INNER JOIN
                         invEncabezado ON invDetalle.coddoc = invEncabezado.coddoc AND invDetalle.numinv = invEncabezado.numinv ON 
                         #TABLAARTICULOS.codbus = invDetalle.codbus INNER JOIN
                         #DOCUMENTOINV ON invEncabezado.coddoc = #DOCUMENTOINV.coddoc
WHERE   (invencabezado.periodo = @periodo AND invencabezado.fecdoc < @fechai)
AND 	(invencabezado.coddoc = 3)
AND     (invEncabezado.estado <> 9) 
AND     (invEncabezado.codbod=@codbod OR @codbod=0)
AND 	(invDetalle.cantidad > 0)

UNION ALL

SELECT InvDetalle.codbus,     --- TRASPASO ENTRADA
	#TABLAARTICULOS.codfam, 
	#TABLAARTICULOS.codsub, 
	1 as grudoc,
	invDetalle.coddoc, 
	hordoc as fecdoc, 
	invDetalle.numinv, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	invDetalle.cantidad AS entrada,
	invDetalle.canalt AS entradaalt, 
	0 AS salida, 
	0 AS salidaalt, 
	#TABLAARTICULOS.costo AS preuni, 
	invDetalle.costo AS costo, 
    CAST(ROUND(invDetalle.costo*invDetalle.cantidad,0) AS DECIMAL) AS debe, 
    0 AS haber, 
	#DOCUMENTOINV.Alias_doc,
	#DOCUMENTOINV.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), invDetalle.stockGralDestino) ELSE CONVERT(DECIMAL(18,2), invDetalle.stockBodegaDes) END AS stock,
	0 as stockalt,
	CAST(ROUND(invDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,invEncabezado.fecing,
	nomfam,
	nomsub,
	0 AS codsii,
	2 AS orden,
	hordoc as horadoc,
	invEncabezado.fecdoc as fecha,
	numlin as linea,2 AS orden2,
	invEncabezado.codbod as bodega
FROM          #TABLAARTICULOS  INNER JOIN
                         invDetalle INNER JOIN
                         invEncabezado ON invDetalle.coddoc = invEncabezado.coddoc AND invDetalle.numinv = invEncabezado.numinv ON 
                         #TABLAARTICULOS.codbus = invDetalle.codbus INNER JOIN
                         #DOCUMENTOINV ON invEncabezado.coddoc = #DOCUMENTOINV.coddoc
WHERE   (invencabezado.periodo = @periodo) AND (invencabezado.fecdoc < @fechai)
AND 	(invencabezado.coddoc = 3)
AND     (invEncabezado.estado <> 9) 
AND 	(invDetalle.cantidad > 0)
AND     (invEncabezado.boddes=@codbod OR @codbod=0)


UNION ALL

SELECT venDetalle.codbus,   ---FACTURAS
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	venDetalle.grudoc, 
	venDetalle.coddoc, 
	hordoc as fecdoc, 
	venDetalle.numinv, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	0  AS entrada, 
	0  AS entradaalt, 
	venDetalle.cantidad  AS salida, 
	venDetalle.canalt  AS salidaalt, 
	(venDetalle.preuni*venEncabezado.monpar) AS preuni, 
	venDetalle.costo,
	0 AS debe, 
	CAST(ROUND(venDetalle.costo * venDetalle.cantidad , 0) AS DECIMAL)  AS haber, 
	#DOCUMENTOVEN.Alias_doc,
	#DOCUMENTOVEN.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), venDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), venDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(venDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,venEncabezado.fecing,
    nomfam,
	nomsub,
	venEncabezado.codsii,
	2 AS orden,
	hordoc as horadoc,
	venEncabezado.fecdoc as fecha,
	numlin as linea,0 AS orden2,
	venEncabezado.codbod as bodega
 FROM            venEncabezado INNER JOIN
                       #DOCUMENTOVEN ON venEncabezado.codsii = #DOCUMENTOVEN.codsii INNER JOIN
                         #TABLAARTICULOS  INNER JOIN
                         venDetalle ON #TABLAARTICULOS.codbus = venDetalle.codbus ON venEncabezado.coddoc = venDetalle.coddoc 
						 AND venEncabezado.grudoc = venDetalle.grudoc AND 
                         venEncabezado.numinv = venDetalle.numinv
WHERE   (venEncabezado.grudoc=10) 
AND     (venEncabezado.periodo = @periodo AND venEncabezado.fecdoc < @fechai)
AND     (@codbod=0 OR venEncabezado.codbod=@codbod)
AND 	(venEncabezado.estado <> 9) 


UNION ALL 

SELECT venDetalle.codbus,   ---NC 
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	venDetalle.grudoc, 
	venDetalle.coddoc, 
	hordoc as fecdoc, 
	venDetalle.numinv, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	venDetalle.cantidad  AS entrada, 
	venDetalle.canalt  AS entradaalt, 
	0 AS salida, 
	0 AS salidaalt, 
	(venDetalle.preuni*venEncabezado.monpar) AS preuni, 
	venDetalle.costo,
	CAST(ROUND(dbo.CostoReferencia(venEncabezado.codsii,venEncabezado.numinv,vendetalle.codbus) * venDetalle.cantidad,0) AS DECIMAL) AS debe, 
    0 as haber, 
	#DOCUMENTONDNC.Alias_doc,
	#DOCUMENTONDNC.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), venDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), venDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(venDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,venEncabezado.fecing,
    nomfam,
	nomsub,
	venEncabezado.codsii,
	2 AS orden,
	hordoc as horadoc,
	venEncabezado.fecdoc as fecha,
	numlin as linea,0 AS orden2,
	venEncabezado.codbod as bodega
 FROM            venEncabezado INNER JOIN
                       #DOCUMENTONDNC ON venEncabezado.codsii = #DOCUMENTONDNC.codsii INNER JOIN
                         #TABLAARTICULOS  INNER JOIN
                         venDetalle ON #TABLAARTICULOS.codbus = venDetalle.codbus ON venEncabezado.coddoc = venDetalle.coddoc 
						 AND venEncabezado.grudoc = venDetalle.grudoc AND 
                         venEncabezado.numinv = venDetalle.numinv
WHERE (venEncabezado.grudoc=10) AND  (venEncabezado.periodo = @periodo AND venEncabezado.fecdoc < @fechai)
AND   (venEncabezado.codsii=60 OR venEncabezado.codsii=61 OR venEncabezado.codsii=104) 
AND   (motivo=1 or motivo = 4)
AND   (@codbod=0 OR venEncabezado.codbod=@codbod)
AND   (venEncabezado.estado <> 9) 

UNION ALL

SELECT venDetalle.codbus,   ---ND
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	venDetalle.grudoc, 
	venDetalle.coddoc, 
	hordoc as fecdoc, 
	venDetalle.numinv, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	0  AS entrada,
	0  AS entradaalt,  
	venDetalle.cantidad AS salida,
	venDetalle.canalt AS salidaalt,  
	(venDetalle.preuni*venEncabezado.monpar) AS preuni, 
	venDetalle.costo,
	0 AS debe, 
	CAST(ROUND(dbo.CostoReferencia(venEncabezado.codsii,venEncabezado.numinv,vendetalle.codbus) * venDetalle.cantidad,0) AS DECIMAL) as haber, 
	#DOCUMENTONDNC.Alias_doc,
	#DOCUMENTONDNC.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), venDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), venDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(venDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,venEncabezado.fecing,
    nomfam,
	nomsub,
	venEncabezado.codsii,
	2 AS orden,
	hordoc as horadoc,
	venEncabezado.fecdoc as fecha,
	numlin as linea,0 AS orden2,
	venEncabezado.codbod as bodega
 FROM            venEncabezado INNER JOIN
                       #DOCUMENTONDNC ON venEncabezado.codsii = #DOCUMENTONDNC.codsii INNER JOIN
                         #TABLAARTICULOS  INNER JOIN
                         venDetalle ON #TABLAARTICULOS.codbus = venDetalle.codbus ON venEncabezado.coddoc = venDetalle.coddoc 
						 AND venEncabezado.grudoc = venDetalle.grudoc AND 
                         venEncabezado.numinv = venDetalle.numinv
WHERE (venEncabezado.grudoc=10)
AND  (venEncabezado.periodo = @periodo AND venEncabezado.fecdoc < @fechai)
AND   (venEncabezado.codsii=55 OR venEncabezado.codsii=56 OR venEncabezado.codsii=108) 
AND   (motivo=1 or motivo = 4)
AND   (@codbod=0 OR venEncabezado.codbod=@codbod)
AND   (venEncabezado.estado <> 9) 


UNION ALL

SELECT  venDetalle.codbus,   ---GUIAS
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	venDetalle.grudoc, 
	venDetalle.coddoc, 
	hordoc as fecdoc, 
	venDetalle.numinv, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	0  AS entrada,
	0  AS entradaalt, 
	venDetalle.cantidad  AS salida, 
	venDetalle.canalt  AS salidaalt,
	(venDetalle.preuni*venEncabezado.monpar) AS preuni, 
	venDetalle.costo,
   	0 AS debe, 
	CAST(ROUND(venDetalle.costo * venDetalle.cantidad , 0) AS DECIMAL)  AS haber,  
	#DOCUMENTOVEN.Alias_doc,
	#DOCUMENTOVEN.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), venDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), venDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(venDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,venEncabezado.fecing,
    nomfam,
	nomsub,
	venEncabezado.codsii,
	2 AS orden,
	hordoc as horadoc,
	venEncabezado.fecdoc as fecha,
	numlin as linea,0 AS orden2,
	venEncabezado.codbod as bodega
 FROM            venEncabezado INNER JOIN
                       #DOCUMENTOGUI as #DOCUMENTOVEN ON venEncabezado.codsii = #DOCUMENTOVEN.codsii INNER JOIN
                         #TABLAARTICULOS  INNER JOIN
                         venDetalle ON #TABLAARTICULOS.codbus = venDetalle.codbus ON venEncabezado.coddoc = venDetalle.coddoc 
						 AND venEncabezado.grudoc = venDetalle.grudoc AND 
                         venEncabezado.numinv = venDetalle.numinv
WHERE   (venEncabezado.grudoc=50) AND (venEncabezado.periodo = @periodo) AND (venEncabezado.fecdoc < @fechai)
AND     (tip_con = 0)
AND     (venEncabezado.estado <> 9) 
AND     (@codbod=0 OR venEncabezado.codbod=@codbod)






RAISERROR('CREATE CLUSTERED',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()

--cREATE CLUSTERED INDEX Indice1 ON #A (codbus,fecha,orden,fecdoc,orden2,linea);


--SELECT top(1) codbus FROM #A WHERE 1=1 

RAISERROR('PERIODO ANTERIOR INSERTADO',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()


SELECT RANK() OVER (PARTITION BY codbus ORDER BY codbus,fecha,orden,fecdoc,orden2,linea) AS num_linea, *
INTO #BB
FROM #A





RAISERROR('PERIODO ANTERIOR INSERTADO INTO #BB',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()

SELECT *,
(SELECT COALESCE(SUM(debe)-sum(haber),0) from  #BB T1 WHERE T1.num_linea < T2.num_linea AND T2.codbus = T1.codbus) + debe - haber  as saldo
INTO #B
FROM #BB T2



RAISERROR('PERIODO ANTERIOR INSERTADO INTO #B',10,25) WITH NOWAIT



SELECT MAX(num_linea) as num_linea,codbus 
INTO #C
FROM #B
GROUP BY codbus

RAISERROR('PERIODO ANTERIOR INSERTADO INTO #C',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()


SELECT #B.codbus,
sum(#B.debe) as debe, 
SUM(#B.haber) as haber
INTO #D
FROM #B
GROUP BY #B.codbus


SELECT #B.codbus,
#B.codfam,
#B.codsub,
0 as grudoc,
0 as coddoc, 
'01-01-1900' as fecdoc, 
0 as numinv, 
max(#B.nomart) as nomart, 
'' as abruni, 
(select cast(sum(entrada) as decimal(18,2)) FROM #A where #a.codbus=#B.codbus) as entrada,
(select cast(sum(salida) as decimal(18,2)) FROM #A where #a.codbus=#B.codbus) as salida,
(select cast(sum(entradaalt) as decimal(18,2)) FROM #A where #a.codbus=#B.codbus) as entradaalt,
(select cast(sum(salidaalt) as decimal(18,2)) FROM #A where #a.codbus=#B.codbus) as salidaalt,
0 AS preuni, 
max(#B.costo) AS costo,
(SELECT debe FROM #D WHERE #B.codbus = #D.codbus) AS debe,
(SELECT haber FROM #D WHERE #B.codbus = #D.codbus) as haber, 
--sum(#B.debe) as debe, 
--0 as haber, 
'INICIAL' as Alias_doc,
'+' as operac,
sum(#B.stock) as stock,
0 as stockalt,
max(#B.saldoGral) as saldogral,'1900-01-01' as fecing,
nomfam,nomsub,0 as codsii,0 as orden,
max(saldo) as saldo,'01-01-1900' as fecha,
0 AS bodega
INTO #DOCUMENTOINICIAL
FROM #B INNER JOIN
	 #C ON #B.num_linea = #C.num_linea AND #B.codbus = #C.codbus
GROUP BY #B.num_linea,#B.codbus,codfam,codsub,nomfam,nomsub,bodega


RAISERROR('PERIODO ANTERIOR INSERTADO #DOCUMENTOINICIAL',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()


UPDATE #TABLAARTICULOS
SET saldoinicial=(SELECT COALESCE(SUM(saldo),0) FROM #DOCUMENTOINICIAL d WHERE  [#TABLAARTICULOS].codbus=d.codbus) 



--return 0 

RAISERROR('PDATE #TABLAARTICULO',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()





SELECT  invDetalle.codbus, --ENTRADAS-CONSUMOS-DEVOLUCIONES anterior
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	1 as grudoc,
	invDetalle.coddoc, 
	hordoc as fecdoc, 
	invDetalle.numinv, 
	--(#TABLAARTICULOS.nomart + ' ' +  invEncabezado.glosa ) AS nomart,
	#TABLAARTICULOS.nomart,  
	#TABLAARTICULOS.abruni, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN 0 ELSE invDetalle.cantidad END AS entrada, 
		CASE #DOCUMENTOINV.operac WHEN '-' THEN 0 ELSE invDetalle.canalt END AS entradaalt, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN invDetalle.cantidad ELSE 0 END AS salida, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN invDetalle.canalt ELSE 0 END AS salidaalt, 
	CASE invDetalle.coddoc WHEN 1 THEN ROUND(invDetalle.preuni*invEncabezado.monpar,4) ELSE #TABLAARTICULOS.costo END AS preuni, 
	invDetalle.costo,
    CASE #DOCUMENTOINV.operac WHEN '-' THEN 0 ELSE CASE invDetalle.coddoc WHEN 1 THEN CAST(ROUND(invDetalle.pretot*invEncabezado.monpar,0) AS DECIMAL) ELSE CAST(ROUND(invDetalle.costo*invDetalle.cantidad,0) AS DECIMAL) END END AS debe, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN CAST(ROUND(invDetalle.costo*invDetalle.cantidad,0) AS DECIMAL) ELSE 0 END AS haber, 
	#DOCUMENTOINV.Alias_doc,
	#DOCUMENTOINV.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), invDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), invDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(invDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,invEncabezado.fecing,
	nomfam,
	nomsub,
	0 AS codsii,
	CASE  WHEN invDetalle.coddoc=1 THEN 1 ELSE 2 END AS orden
	,hordoc as horadoc,#TABLAARTICULOS.saldoinicial,
	convert(date,invEncabezado.fecdoc) as fecha,
	numlin as linea,0 AS orden2,
	invEncabezado.codbod as bodega
INTO #DOCUMENTOS
FROM          #TABLAARTICULOS  INNER JOIN
                         invDetalle INNER JOIN
                         invEncabezado ON invDetalle.coddoc = invEncabezado.coddoc AND invDetalle.numinv = invEncabezado.numinv ON 
                         #TABLAARTICULOS.codbus = invDetalle.codbus INNER JOIN
                         #DOCUMENTOINV ON invEncabezado.coddoc = #DOCUMENTOINV.coddoc
WHERE   (invencabezado.periodo = @periodo AND invencabezado.fecdoc BETWEEN @fechai AND @fechaf)
AND 	(invEncabezado.coddoc=1 OR invEncabezado.coddoc=2 OR invEncabezado.coddoc=4)  
AND 	(invEncabezado.estado <> 9)
AND 	(invDetalle.cantidad > 0)
AND     (invEncabezado.codbod=@codbod OR @codbod=0)


UNION ALL



SELECT InvDetalle.codbus,     --- TRASPASO SALIDA
	#TABLAARTICULOS.codfam, 
	#TABLAARTICULOS.codsub, 
	1 as grudoc,
	invDetalle.coddoc, 
	hordoc as fecdoc, 
	invDetalle.numinv, 
	--(#TABLAARTICULOS.nomart + ' ' +  invEncabezado.glosa ) AS nomart, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	0 AS entrada, 
	0 AS entradaalt, 
	invDetalle.cantidad AS salida, 
	invDetalle.canalt AS salidaalt, 
	preuni, 
	invDetalle.costo AS costo, 
    0 AS debe, 
    CAST(ROUND(invDetalle.costo*invDetalle.cantidad,0) AS DECIMAL) AS haber, 
	#DOCUMENTOINV.Alias_doc,
	#DOCUMENTOINV.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), invDetalle.stockGralOrigen) ELSE CONVERT(DECIMAL(18,2), invDetalle.stockBodegaOrigen) END AS stock,
	0 as stockalt,
	CAST(ROUND(invDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,invEncabezado.fecing,
	nomfam,
	nomsub,
	0 AS codsii,
	2 AS orden,
	hordoc as horadoc,#TABLAARTICULOS.saldoinicial,
	INVEncabezado.fecdoc as fecha,
	numlin as linea,1 AS orden2,
	invEncabezado.codbod
FROM          #TABLAARTICULOS  INNER JOIN
                         invDetalle INNER JOIN
                         invEncabezado ON invDetalle.coddoc = invEncabezado.coddoc AND invDetalle.numinv = invEncabezado.numinv ON 
                         #TABLAARTICULOS.codbus = invDetalle.codbus INNER JOIN
                         #DOCUMENTOINV ON invEncabezado.coddoc = #DOCUMENTOINV.coddoc
WHERE   (invencabezado.periodo = @periodo AND invencabezado.fecdoc BETWEEN @fechai AND @fechaf)
AND 	(invencabezado.coddoc = 3)
AND     (invEncabezado.estado <> 9) 
AND     (invEncabezado.codbod=@codbod OR @codbod=0)
AND 	(invDetalle.cantidad > 0)

UNION ALL

SELECT InvDetalle.codbus,     --- TRASPASO ENTRADA
	#TABLAARTICULOS.codfam, 
	#TABLAARTICULOS.codsub, 
	1 as grudoc,
	invDetalle.coddoc, 
	hordoc as fecdoc, 
	invDetalle.numinv, 
	--(#TABLAARTICULOS.nomart + ' ' +  invEncabezado.glosa ) AS nomart, 
	#TABLAARTICULOS.nomart, 
	#TABLAARTICULOS.abruni, 
	invDetalle.cantidad AS entrada, 
	invDetalle.canalt AS entradaalt, 
	0 AS salida, 
	0 AS salidaalt, 
	preuni, 
	invDetalle.costo AS costo, 
    CAST(ROUND(invDetalle.costo*invDetalle.cantidad,0) AS DECIMAL) AS debe, 
    0 AS haber, 
	#DOCUMENTOINV.Alias_doc,
	#DOCUMENTOINV.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), invDetalle.stockGralDestino) ELSE CONVERT(DECIMAL(18,2), invDetalle.stockBodegaDes) END AS stock,
	0 as stockalt,
	CAST(ROUND(invDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,invEncabezado.fecing,
	nomfam,
	nomsub,
	0 AS codsii,
	2 AS orden,
	hordoc as horadoc,#TABLAARTICULOS.saldoinicial,
	invEncabezado.fecdoc as fecha,
	numlin as linea,2 AS orden2,
	invEncabezado.codbod
FROM          #TABLAARTICULOS  INNER JOIN
                         invDetalle INNER JOIN
                         invEncabezado ON invDetalle.coddoc = invEncabezado.coddoc AND invDetalle.numinv = invEncabezado.numinv ON 
                         #TABLAARTICULOS.codbus = invDetalle.codbus INNER JOIN
                         #DOCUMENTOINV ON invEncabezado.coddoc = #DOCUMENTOINV.coddoc
WHERE   (invencabezado.periodo = @periodo AND invencabezado.fecdoc BETWEEN @fechai AND @fechaf)
AND 	(invencabezado.coddoc = 3)
AND     (invEncabezado.estado <> 9) 
AND 	(invDetalle.cantidad > 0)
AND     (invEncabezado.boddes=@codbod OR @codbod=0)


UNION ALL

SELECT venDetalle.codbus,   ---FACTURAS
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	venDetalle.grudoc, 
	venDetalle.coddoc, 
	hordoc as fecdoc, 
	venDetalle.numinv, 
	 #TABLAARTICULOS.nomart as nomart,
	#TABLAARTICULOS.abruni, 
	0  AS entrada, 
	0  AS entradaalt, 
	venDetalle.cantidad  AS salida, 
	venDetalle.canalt  AS salidaalt, 
	(venDetalle.preuni*venEncabezado.monpar) AS preuni, 
	venDetalle.costo,
	0 AS debe, 
	CAST(ROUND(venDetalle.costo * venDetalle.cantidad , 0) AS DECIMAL)  AS haber, 
	#DOCUMENTOVEN.Alias_doc,
	#DOCUMENTOVEN.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), venDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), venDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(venDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,venEncabezado.fecing,
    nomfam,
	nomsub,
	venEncabezado.codsii,
	2 AS orden,
	hordoc as horadoc,#TABLAARTICULOS.saldoinicial,
	venEncabezado.fecdoc as fecha,
	numlin as linea,0 AS orden2,
	venEncabezado.codbod
 FROM            venEncabezado INNER JOIN
                       #DOCUMENTOVEN ON venEncabezado.codsii = #DOCUMENTOVEN.codsii INNER JOIN
                         #TABLAARTICULOS  INNER JOIN
                         venDetalle ON #TABLAARTICULOS.codbus = venDetalle.codbus ON venEncabezado.coddoc = venDetalle.coddoc 
						 AND venEncabezado.grudoc = venDetalle.grudoc AND 
                         venEncabezado.numinv = venDetalle.numinv
WHERE   (venEncabezado.grudoc=10) 
AND     (venEncabezado.periodo = @periodo AND venEncabezado.fecdoc between @fechai AND @fechaf)
AND     (@codbod=0 OR venEncabezado.codbod=@codbod)
AND 	(venEncabezado.estado <> 9) 



UNION ALL 

SELECT venDetalle.codbus,   ---NC 
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	venDetalle.grudoc, 
	venDetalle.coddoc, 
	hordoc as fecdoc, 
	venDetalle.numinv, 
	 #TABLAARTICULOS.nomart as nomart,
	#TABLAARTICULOS.abruni, 
	venDetalle.cantidad  AS entrada, 
	venDetalle.canalt  AS entradaalt, 
	0 AS salida, 
	0 AS salidaalt, 
	(venDetalle.preuni*venEncabezado.monpar) AS preuni, 
	venDetalle.costo,
	ROUND(dbo.CostoReferencia(venEncabezado.codsii,venEncabezado.numinv,vendetalle.codbus) * venDetalle.cantidad,0) AS debe, 
    0 as haber, 
	#DOCUMENTONDNC.Alias_doc,
	#DOCUMENTONDNC.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), venDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), venDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(venDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,venEncabezado.fecing,
    nomfam,
	nomsub,
	venEncabezado.codsii,
	2 AS orden,
	hordoc as horadoc,#TABLAARTICULOS.saldoinicial,
	venEncabezado.fecdoc as fecha,
	numlin as linea,0 AS orden2,
	venEncabezado.codbod
 FROM            venEncabezado INNER JOIN
                       #DOCUMENTONDNC ON venEncabezado.codsii = #DOCUMENTONDNC.codsii INNER JOIN
                         #TABLAARTICULOS  INNER JOIN
                         venDetalle ON #TABLAARTICULOS.codbus = venDetalle.codbus ON venEncabezado.coddoc = venDetalle.coddoc 
						 AND venEncabezado.grudoc = venDetalle.grudoc AND 
                         venEncabezado.numinv = venDetalle.numinv
WHERE (venEncabezado.grudoc=10)  
AND   (venEncabezado.periodo = @periodo AND venEncabezado.fecdoc between @fechai AND @fechaf)
AND   (venEncabezado.codsii=60 OR venEncabezado.codsii=61 OR venEncabezado.codsii=104) 
AND   (motivo=1 or motivo = 4)
AND   (@codbod=0 OR venEncabezado.codbod=@codbod)
AND   (venEncabezado.estado <> 9) 

UNION ALL

SELECT venDetalle.codbus,   ---ND
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	venDetalle.grudoc, 
	venDetalle.coddoc, 
	hordoc as fecdoc, 
	venDetalle.numinv, 
	 #TABLAARTICULOS.nomart as nomart,
	#TABLAARTICULOS.abruni, 
	0  AS entrada, 
	0  AS entradaalt, 
	venDetalle.cantidad AS salida, 
		venDetalle.canalt AS salidaalt, 
	(venDetalle.preuni*venEncabezado.monpar) AS preuni, 
	venDetalle.costo,
	0 AS debe, 
	ROUND(dbo.CostoReferencia(venEncabezado.codsii,venEncabezado.numinv,vendetalle.codbus) * venDetalle.cantidad,0) as haber, 
	#DOCUMENTONDNC.Alias_doc,
	#DOCUMENTONDNC.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), venDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), venDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(venDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,venEncabezado.fecing,
    nomfam,
	nomsub,
	venEncabezado.codsii,
	2 AS orden,
	hordoc as horadoc,#TABLAARTICULOS.saldoinicial,
	venEncabezado.fecdoc as fecha,
	numlin as linea,0 AS orden2,
	venEncabezado.codbod
 FROM            venEncabezado INNER JOIN
                       #DOCUMENTONDNC ON venEncabezado.codsii = #DOCUMENTONDNC.codsii INNER JOIN
                         #TABLAARTICULOS  INNER JOIN
                         venDetalle ON #TABLAARTICULOS.codbus = venDetalle.codbus ON venEncabezado.coddoc = venDetalle.coddoc 
						 AND venEncabezado.grudoc = venDetalle.grudoc AND 
                         venEncabezado.numinv = venDetalle.numinv
WHERE (venEncabezado.grudoc=10)
AND   (venEncabezado.periodo = @periodo AND venEncabezado.fecdoc between @fechai AND @fechaf)
AND   (venEncabezado.codsii=55 OR venEncabezado.codsii=56 OR venEncabezado.codsii=108) 
AND   (motivo=1 or motivo = 4)
AND   (@codbod=0 OR venEncabezado.codbod=@codbod)
AND   (venEncabezado.estado <> 9) 


UNION ALL

SELECT  venDetalle.codbus,   ---GUIAS
    #TABLAARTICULOS.codfam,
    #TABLAARTICULOS.codsub,
	venDetalle.grudoc, 
	venDetalle.coddoc, 
	hordoc as fecdoc, 
	venDetalle.numinv, 
	 #TABLAARTICULOS.nomart as nomart,
	#TABLAARTICULOS.abruni, 
	0  AS entrada, 
	0  AS entradaalt, 
	venDetalle.cantidad  AS salida, 
	venDetalle.canalt  AS salidaalt,
	(venDetalle.preuni*venEncabezado.monpar) AS preuni, 
	venDetalle.costo,
   	0 AS debe, 
	CAST(ROUND(venDetalle.costo * venDetalle.cantidad , 0) AS DECIMAL)  AS haber,  
	#DOCUMENTOVEN.Alias_doc,
	#DOCUMENTOVEN.operac,
	CASE @codbod WHEN 0 THEN CONVERT(DECIMAL(18,2), venDetalle.stockGral) ELSE CONVERT(DECIMAL(18,2), venDetalle.stockBodega) END AS stock,
	0 as stockalt,
	CAST(ROUND(venDetalle.saldoGral,0) AS DECIMAL) AS saldoGral,venEncabezado.fecing,
    nomfam,
	nomsub,
	venEncabezado.codsii,
	2 AS orden,
	hordoc as horadoc,#TABLAARTICULOS.saldoinicial,
	venEncabezado.fecdoc as fecha,
	numlin as linea,0 AS orden2,
	venEncabezado.codbod
 FROM            venEncabezado INNER JOIN
                   #DOCUMENTOGUI as    #DOCUMENTOVEN ON venEncabezado.codsii = #DOCUMENTOVEN.codsii INNER JOIN
                         #TABLAARTICULOS  INNER JOIN
                         venDetalle ON #TABLAARTICULOS.codbus = venDetalle.codbus ON venEncabezado.coddoc = venDetalle.coddoc 
						 AND venEncabezado.grudoc = venDetalle.grudoc AND 
                         venEncabezado.numinv = venDetalle.numinv
WHERE   (venEncabezado.grudoc=50) AND (venEncabezado.periodo = @periodo AND venEncabezado.fecdoc between @fechai AND @fechaf)
AND     (tip_con = 0)
AND     (venEncabezado.estado <> 9) 
--AND     (motivo=1)
AND     (@codbod=0 OR venEncabezado.codbod=@codbod)



RAISERROR('PERIODO ACTUAL',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()


SELECT  rank() OVER (PARTITION BY codbus ORDER BY codbus,fecha,orden,fecdoc,orden2,linea) as num_linea ,*
INTO #TABLAACUMULADO 
from  #DOCUMENTOS
order by orden,fecdoc

RAISERROR('#TABLAACUMULADO ',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()



--CREATE CLUSTERED INDEX Indice1 ON #DOCUMENTOS (codbus,fecha,orden,fecdoc,orden2,linea);

SELECT *
INTO #TABLA2
FROM #TABLAACUMULADO 

RAISERROR('#TABLA2 ',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE())PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()


--SELECT * FROM #DOCUMENTOINICIAL
--SELECT TOP 1 FROM #DOCUMENTOS

--SELECT (SELECT (SUM(debe - haber)) from  #TABLA2 T1 WHERE T1.num_linea < T2.num_linea AND T1.codbus = T2.codbus),
--debe, haber , saldoinicial ,

--COALESCE((SELECT (SUM(debe - haber)) from  #TABLA2 T1 WHERE T1.num_linea < T2.num_linea AND T1.codbus = T2.codbus) + debe - haber 
--+ saldoinicial
-- ,0) as saldo 
--FROM #TABLA2  T2



SELECT codbus, codfam, nomfam, codsub, nomsub, grudoc, coddoc, fecdoc, numinv, nomart, abruni, entrada, entradaalt, salida, salidaalt,
preuni, costo, COALESCE(debe,0) as debe, COALESCE(haber,0) as haber, Alias_doc,
operac, stock, 
(SELECT COALESCE(SUM(entradaalt), 0) - COALESCE(SUM(salidaalt), 0) FROM #DOCUMENTOINICIAL) as stockalt,
COALESCE(saldo,0) as saldo, orden, 0 as saldoinicial, fecha, 0 as num_linea, 0 as linea, 0 AS saldo2, 0 as orden2,
1 as inicial, bodega
INTO #TODO2
FROM #DOCUMENTOINICIAL



UNION ALL 

SELECT codbus,codfam,nomfam,codsub,nomsub,grudoc,coddoc,fecdoc,numinv,nomart,abruni,entrada,entradaalt,salida,salidaalt,
preuni,costo,coalesce(debe,0) as debe,COALESCE(haber,0) as haber,Alias_doc,
operac,stock,stockalt,COALESCE((SELECT (SUM(debe - haber)) from  #TABLA2 T1 WHERE T1.num_linea < T2.num_linea AND T1.codbus = T2.codbus),0) + debe - haber 
+ saldoinicial
  as saldo  , orden,saldoinicial,fecha,num_linea,linea, (debe - haber) as saldo2,orden2,0 as inicial, bodega
FROM #TABLA2 T2




 SELECT codbus,codfam,nomfam,codsub,nomsub,grudoc,coddoc,fecdoc,numinv,nomart,abruni,entrada,entradaalt,salida,salidaalt,
preuni,costo,coalesce(debe,0) as debe,COALESCE(haber,0) as haber,Alias_doc,
operac,stock,(COALESCE((SELECT (SUM(entradaalt - salidaalt)) from  #TODO2 T1 WHERE T1.num_linea < T2.num_linea AND T1.codbus = T2.codbus),0) + entradaalt - salidaalt ) as stockalt,COALESCE((SELECT (SUM(debe - haber)) from  #TODO2 T1 WHERE T1.num_linea < T2.num_linea AND T1.codbus = T2.codbus),0) + debe - haber 
+ saldoinicial
  as saldo  , orden,saldoinicial,fecha,num_linea,linea, (debe - haber) as saldo2,orden2,0 as inicial, bodega
INTO #TODO
FROM #TODO2 T2 

--(COALESCE((SELECT (SUM(entradaalt - salidaalt)) from  #TABLA2 T1 WHERE T1.num_linea < T2.num_linea AND T1.codbus = T2.codbus),0) + entradaalt - salidaalt ) as stockalt,
--return 0 

RAISERROR('#TODO ',10,25) WITH NOWAIT
PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()

--SELECT * FROM #TODO

IF @toma='0'
BEGIN

IF @stockvalorizado=0
BEGIN
SELECT *,ROW_NUMBER() OVER(order by (select 1))   as rownumber FROM #TODO
ORDER BY codbus,fecha,orden,fecdoc,orden2,linea
END



IF @stockvalorizado=1
BEGIN



SELECT CODBUS,CASE WHEN inicial=1 THEN SUM(STOCK) ELSE 0 END + CASE WHEN inicial=1 THEN 0 ELSE SUM(ENTRADA) - SUM(SALIDA) END as stockfinal,
CASE WHEN inicial=1 THEN SUM(stockalt) ELSE 0 END + CASE WHEN inicial=1 THEN 0 ELSE SUM(ENTRADAALT) - SUM(SALIDAALT) END as stockfinalalt
INTO #STOCKARTICULOS
FROM #TODO
GROUP BY CODBUS,inicial




SELECT * FROM #TODO
WHERE 
 exists ( SELECT codbus
FROM #STOCKARTICULOS
WHERE stockfinal < 0
GROUP BY CODBUS
having sum(stockfinal)  <> 0 AND #TODO.codbus = #STOCKARTICULOS.codbus)

ORDER BY codbus,fecha,orden,fecdoc,orden2,linea


END


END


IF @toma='1'
BEGIN

SELECT CODBUS,SUM(ENTRADA) - SUM(SALIDA)  as stock_logicoAux, SUM(ENTRADAalt) - SUM(SALIDAalt)  as stock_logicoAuxalt
INTO #invarticulostoma  FROM #TODO GROUP BY codbus


SELECT * FROM #invarticulostoma


END