ALTER  PROCEDURE [dbo].[sp_invTrazaOC_PE_Comparativo]
(
@rut int ,
@fecini DATE,
@fecfin DATE,
@diferencias nvarchar(10) = 'TRUE',
@tipo int=1,
@codbusi 	varchar(200)='0',
@codbusf 	varchar(200)='',
@codfami 	varchar(200)='0',
@codfamf 	varchar(200)='',
@codsubi 	varchar(200)='0',
@codsubf 	varchar(200)='',
@numdoc  BIGINT = 0,
@unineg INT =0,
@item INT = 0
)

AS 

IF @codbusf=''
BEGIN 
SET @codbusf= REPLICATE('Z',(SELECT TOP 1 (larart+larfam+larsub) FROM invParametros))
END

IF @codfamf=''
BEGIN 
SET @codfamf= REPLICATE('Z',(SELECT TOP 1 (larfam) FROM invParametros))
END


IF @codsubf=''
BEGIN 
SET @codsubf= REPLICATE('Z',(SELECT TOP 1 (larsub) FROM invParametros))
END


SET @diferencias = UPPER(@diferencias)

	SELECT    ENCOC.numinv AS numOC, ENCOC.fecdoc, conCtacte.rutcte,conCtacte.digcte, conCtacte.nomcte, DETOC.codbus, DETOC.nomart, DETOC.cantidad, DETOC.preuni, DETOC.fecent, 
	dbo.numPE(ENCOC.coddoc,ENCOC.numinv) AS numPE, SUM(DETPE.cantidad) AS cantidadPE, MAX(DETPE.preuni) AS preuniPE, MAX(ENCPE.fecdoc) AS fecrecPE, 
						  SUM(DETOC.cantidad) - SUM(DETPE.cantidad)   AS dif_cant, MAX(DETPE.preuni) - DETOC.preuni AS dif_precio, DATEDIFF(day, ENCOC.fecdoc, MAX(ENCPE.fecdoc)) 
						  AS dif_entrega, invArticulos.codalt, CONVERT(VARCHAR(50),ENCOC.codsuc) AS codsuc
	INTO #A1
    FROM            invDetalle AS DETPE INNER JOIN
                         invEncabezado AS ENCPE ON DETPE.coddoc = ENCPE.coddoc AND DETPE.numinv = ENCPE.numinv INNER JOIN
                         invDetalle AS DETOC INNER JOIN
                         invEncabezado AS ENCOC ON DETOC.coddoc = ENCOC.coddoc AND DETOC.numinv = ENCOC.numinv ON ENCPE.tipref1 = ENCOC.coddoc AND 
                         ENCPE.docref1 = ENCOC.numinv AND DETPE.codbus = DETOC.codbus INNER JOIN
                         conCtacte ON ENCOC.rutcte = conCtacte.rutcte INNER JOIN
                         invArticulos ON DETOC.codbus = invArticulos.codbus
	WHERE (ENCOC.coddoc = 6 AND ENCPE.coddoc = 1)
	AND  (ENCOC.cerrado <> 1)
	AND	 (ENCOC.rutcte =@rut OR  @rut=0)
	AND  (ENCOC.fecdoc BETWEEN  @fecini AND @fecfin)
	AND  (invArticulos.codbus BETWEEN @codbusi AND @codbusf)
	AND  (invArticulos.codfam BETWEEN @codfami AND @codfamf)
	AND  (invArticulos.codsub BETWEEN @codsubi AND @codsubf)
	AND  (@numdoc=0 OR ENCOC.numinv= @numdoc)
	AND  (@unineg=0 OR ENCOC.codcen=@unineg)
    AND  (@item=0 OR ENCOC.item=@item)

	GROUP BY ENCOC.codsuc, ENCOC.coddoc,ENCOC.numinv, ENCOC.fecdoc, DETOC.codbus, DETOC.nomart, DETOC.cantidad, DETOC.preuni, DETOC.fecent, conCtacte.rutcte,conCtacte.digcte, conCtacte.nomcte, invArticulos.codalt
	--HAVING (SUM(DETPE.cantidad) - DETOC.cantidad) <> 0 
	--OR (MAX(DETPE.preuni) - DETOC.preuni) <> 0  
	--OR DATEDIFF(day, ENCOC.fecdoc, MAX(ENCPE.fecdoc)) <> 0 
	--SELECT * FROM #A1

	SELECT     ENCOC.numinv AS numOC, ENCOC.fecdoc, CC.rutcte, CC.digcte, CC.nomcte, DETOC.codbus, DETOC.nomart, DETOC.cantidad, DETOC.preuni, DETOC.fecent, 
				'' AS numPE, 0 AS cantidadPE, 0 AS preuniPE, '01-01-1900' AS fecrecPE, DETOC.cantidad AS dif_cant, DETOC.preuni AS dif_precio, DATEDIFF(day, ENCOC.fecdoc, 
				GETDATE()) AS dif_entrega, invArticulos.codalt, CONVERT(VARCHAR(50),ENCOC.codsuc) AS codsuc
	INTO #B
	FROM         invDetalle AS DETOC INNER JOIN
				  invEncabezado AS ENCOC ON DETOC.coddoc = ENCOC.coddoc AND DETOC.numinv = ENCOC.numinv INNER JOIN
				  conCtacte AS CC ON ENCOC.rutcte = CC.rutcte	INNER JOIN
                         invArticulos ON DETOC.codbus = invArticulos.codbus
	WHERE (ENCOC.coddoc=6  and ENCOC.cerrado <> 1)
	AND	  (@rut=0 OR ENCOC.RUTCTE = @rut)
	AND   (ENCOC.fecdoc BETWEEN @FECINI AND @FECFIN)
	AND   (NOT EXISTS (SELECT numOC FROM #A1 where #A1.codbus =DETOC.codbus AND #A1.numOC = CONVERT (VARCHAR,ENCOC.numinv ))) 
	AND   (invArticulos.codbus BETWEEN @codbusi AND @codbusf)
	AND   (invArticulos.codfam BETWEEN @codfami AND @codfamf)
	AND   (invArticulos.codsub BETWEEN @codsubi AND @codsubf)
	AND   (@numdoc=0 OR ENCOC.numinv= @numdoc)
	AND   (@unineg=0 OR ENCOC.codcen=@unineg)
    AND   (@item=0 OR ENCOC.item=@item)

	GROUP BY ENCOC.codsuc, ENCOC.numinv, ENCOC.fecdoc, DETOC.codbus, DETOC.nomart, DETOC.cantidad, DETOC.preuni, DETOC.fecent, CC.rutcte, CC.digcte, CC.nomcte, invArticulos.codalt
	ORDER BY 1


	--SELECT * FROM #A1
	
	--SELECT * FROM #B



	SELECT * 
	INTO #A FROM #A1
	UNION ALL
	SELECT * FROM #B

	
	SET @diferencias=UPPER(@diferencias)


     --PENDIENTES 
	IF @Tipo=1 AND @diferencias = 'TRUE' BEGIN
	    SELECT * FROM #A
		WHERE dif_cant <> 0
		RETURN
	END

		
	IF @Tipo=1 AND @diferencias = 'FALSE' BEGIN
	    SELECT * FROM #A
		WHERE dif_cant = 0
		RETURN
	END

	
	--COMPARATIVOS POR PRECIO Y FECHA DE ENTREGA
	IF @Tipo=2 BEGIN
		SELECT * FROM #A
		WHERE (dif_precio <> 0 AND UPPER(@diferencias) = 'TRUE') OR UPPER(@diferencias) = 'FALSE'
		RETURN
	END
		

	IF @Tipo=3 BEGIN
		SELECT * FROM #A
		WHERE (dif_entrega <> 0 AND UPPER(@diferencias) = 'TRUE') OR UPPER(@diferencias) = 'FALSE'
		RETURN
	END