ALTER PROCEDURE [dbo].[sp_invAuxiliar_Existencia_Stim_StockValorizado]
(
@_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  bigint=0,
@omitircero int=0,
@stock INT --Stock 0=todos, 1= positivos , 2 = negativos
) 




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 bigint


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 invDetalle.cantidad END AS entrada, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN invDetalle.cantidad ELSE 0 END AS salida, 
	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 invDetalle.stockGral ELSE invDetalle.stockBodega END AS stock,
	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
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, 
	invDetalle.cantidad AS salida, 
	#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 invDetalle.stockGralOrigen ELSE invDetalle.stockBodegaOrigen END AS stock,
	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
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, 
	0 AS salida, 
	#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 invDetalle.stockGralDestino ELSE invDetalle.stockBodegaDes END AS stock,
	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
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, 
	venDetalle.cantidad  AS salida, 
	(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 venDetalle.stockGral ELSE venDetalle.stockBodega END AS stock,
	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
 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, 
	0 AS salida, 
	(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 venDetalle.stockGral ELSE venDetalle.stockBodega END AS stock,
	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
 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, 
	venDetalle.cantidad AS salida, 
	(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 venDetalle.stockGral ELSE venDetalle.stockBodega END AS stock,
	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
 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, 
	venDetalle.cantidad  AS salida, 
	(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 venDetalle.stockGral ELSE venDetalle.stockBodega END AS stock,
	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
 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 sum(entrada) FROM #A where #a.codbus=#B.codbus) as entrada,
(select sum(salida) FROM #A where #a.codbus=#B.codbus) as salida,
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,
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
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

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, 
	#TABLAARTICULOS.abruni, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN 0 ELSE invDetalle.cantidad END AS entrada, 
	CASE #DOCUMENTOINV.operac WHEN '-' THEN invDetalle.cantidad ELSE 0 END AS salida, 
	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 invDetalle.stockGral ELSE invDetalle.stockBodega END AS stock,
	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
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, 
	#TABLAARTICULOS.abruni, 
	0 AS entrada, 
	invDetalle.cantidad AS salida, 
	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 invDetalle.stockGralOrigen ELSE invDetalle.stockBodegaOrigen END AS stock,
	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
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, 
	#TABLAARTICULOS.abruni, 
	invDetalle.cantidad AS entrada, 
	0 AS salida, 
	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 invDetalle.stockGralDestino ELSE invDetalle.stockBodegaDes END AS stock,
	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
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, 
	venDetalle.cantidad  AS salida, 
	(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 venDetalle.stockGral ELSE venDetalle.stockBodega END AS stock,
	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
 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, 
	0 AS salida, 
	(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 venDetalle.stockGral ELSE venDetalle.stockBodega END AS stock,
	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
 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, 
	venDetalle.cantidad AS salida, 
	(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 venDetalle.stockGral ELSE venDetalle.stockBodega END AS stock,
	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
 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, 
	venDetalle.cantidad  AS salida, 
	(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 venDetalle.stockGral ELSE venDetalle.stockBodega END AS stock,
	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
 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,salida,
preuni ,costo,coalesce(debe,0) as debe, COALESCE(haber,0) as haber ,Alias_doc,
operac,stock,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
INTO #TODO
FROM #DOCUMENTOINICIAL

UNION ALL 

SELECT codbus,codfam,nomfam,codsub,nomsub,grudoc,coddoc,fecdoc,numinv,nomart,abruni,entrada,salida,
preuni,costo,coalesce(debe,0) as debe,COALESCE(haber,0) as haber,Alias_doc,
operac,stock,
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
FROM #TABLA2 T2


--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
		INTO #STOCKARTICULOS
		FROM #TODO
		GROUP BY CODBUS,inicial

		IF @stock = 0
			BEGIN
				SELECT * FROM #TODO
				WHERE 
				 exists ( SELECT codbus
				FROM #STOCKARTICULOS
				GROUP BY CODBUS
				having #TODO.codbus = #STOCKARTICULOS.codbus)

				ORDER BY codbus,fecha,orden,fecdoc,orden2,linea
			END
		ELSE
			IF @stock = 1
				BEGIN
					SELECT * 
					INTO #FINAL1
					FROM #TODO		
					WHERE 
					 exists ( SELECT codbus
					FROM #STOCKARTICULOS
					GROUP BY CODBUS
					having sum(stockfinal) <> 0 AND #TODO.codbus = #STOCKARTICULOS.codbus)
					ORDER BY codbus,fecha,orden,fecdoc,orden2,linea

					SELECT * 
					FROM #FINAL1
					WHERE stock > 0
				END
			ELSE
				IF @stock = 2
					BEGIN
						SELECT * 
						INTO #FINAL2
						FROM #TODO
						WHERE 
						 exists ( SELECT codbus
						FROM #STOCKARTICULOS
						GROUP BY CODBUS
						having sum(stockfinal) <> 0 AND #TODO.codbus = #STOCKARTICULOS.codbus)
						ORDER BY codbus,fecha,orden,fecdoc,orden2,linea

						SELECT *
						FROM #FINAL2
						WHERE stock < 0
					END

END


END


IF @toma='1'
BEGIN

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


SELECT * FROM #invarticulostoma


END