IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_RC_DEB_ANALISE_SALDOS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_RC_DEB_ANALISE_SALDOS]
GO

--EXEC P_RC_DEB_ANALISE_SALDOS 1,999,75453
CREATE PROCEDURE [dbo].[P_RC_DEB_ANALISE_SALDOS] @CD_EMP INT=0, @CD_FILIAL INT=0, @CD_RC_DEB INT=0  AS
SET NOCOUNT ON;
CREATE TABLE #RsDup (CD_EMP INT,
					 CD_FILIAL INT,
					 CD_RC_DEB INT,
					 VLR_DP MONEY,
					 VLR_DESP_OUTR MONEY,
					 SLD_DP MONEY,
					 STS_DP INT)

CREATE TABLE #RsDupCRED (CD_EMP INT,
					 CD_FILIAL INT,
					 CD_RC_DEB INT,
					 VL_PGTO MONEY)


/*
	Ignora Status 3,4,6 
	3 - Cancelada
	4 - Renegociada
	6 - Estornada
*/
IF @CD_RC_DEB = 0 
BEGIN
	--Todas as duplicatas
	INSERT INTO #RsDup
		(CD_EMP,
		CD_FILIAL,
		CD_RC_DEB,
		VLR_DP,
		VLR_DESP_OUTR,
		SLD_DP,
		STS_DP)
	SELECT
		CD_EMP,
		CD_FILIAL,
		CD_RC_DEB,
		VLR_DP,
		ISNULL(VLR_DESP_OUTR,0),
		SLD_DP,
		STS_DP
	FROM
		RC_DEB
	WHERE
		NOT STS_DP IN (3,4,6)	
END
ELSE
BEGIN
	--Passou para analise
	INSERT INTO #RsDup
		(CD_EMP,
		CD_FILIAL,
		CD_RC_DEB,
		VLR_DP,
		VLR_DESP_OUTR,
		SLD_DP,
		STS_DP)
	SELECT
		CD_EMP,
		CD_FILIAL,
		CD_RC_DEB,
		VLR_DP,
		ISNULL(VLR_DESP_OUTR,0),
		SLD_DP,
		STS_DP
	FROM
		RC_DEB
	WHERE
		CD_EMP =@CD_EMP
		AND CD_FILIAL=@CD_FILIAL 
		AND CD_RC_DEB=@CD_RC_DEB
		AND NOT STS_DP IN (3,4,6)	
END
--Buscando credito ha realizado (baixa)
INSERT INTO #RsDupCRED (CD_EMP,
					 CD_FILIAL,
					 CD_RC_DEB,
					 VL_PGTO)
SELECT 
	RC_CRED.CD_EMP, 
	RC_CRED.CD_FILIAL, 
	RC_CRED.CD_RC_DEB, 
	SUM(RC_CRED.VL_PGTO) AS VL_PGTO
FROM         
	RC_CRED INNER JOIN #RsDup DUP ON
	RC_CRED.CD_EMP = DUP.CD_EMP AND
	RC_CRED.CD_FILIAL = DUP.CD_FILIAL AND
	RC_CRED.CD_RC_DEB = DUP.CD_RC_DEB
GROUP BY
	RC_CRED.CD_EMP, 
	RC_CRED.CD_FILIAL, 
	RC_CRED.CD_RC_DEB

--Tratando status de duplicata baixada parcialmente
UPDATE
	RC_DEB
SET 
	STS_DP = CASE WHEN RC_DEB.STS_DP <> 5 THEN 2 ELSE RC_DEB.STS_DP END,
	RC_DEB.SLD_DP =(RC_DEB.VLR_DP - RC_DEB.VLR_DESP_OUTR)-VL_PGTO
FROM         
	#RsDup RC_DEB INNER JOIN  #RsDupCRED bx ON
	RC_DEB.CD_EMP = bx.CD_EMP AND
	RC_DEB.CD_FILIAL = bx.CD_FILIAL AND
	RC_DEB.CD_RC_DEB = bx.CD_RC_DEB 
WHERE
	(RC_DEB.VLR_DP - RC_DEB.VLR_DESP_OUTR) <> VL_PGTO

--Tratando status de duplicata baixada totalmente
UPDATE
	RC_DEB
SET 
	RC_DEB.STS_DP = CASE WHEN RC_DEB.STS_DP <> 5 THEN 1 ELSE RC_DEB.STS_DP END,
	RC_DEB.SLD_DP =0
FROM         
	#RsDup RC_DEB INNER JOIN  #RsDupCRED bx ON
	RC_DEB.CD_EMP = bx.CD_EMP AND
	RC_DEB.CD_FILIAL = bx.CD_FILIAL AND
	RC_DEB.CD_RC_DEB = bx.CD_RC_DEB 
WHERE
	VL_PGTO = RC_DEB.VLR_DP

--Voltando estatus e valor original quando a duplicata nao possui mais baixa
UPDATE
	RC_DEB
SET 
	STS_DP = CASE WHEN RC_DEB.STS_DP <> 5 THEN 0 ELSE RC_DEB.STS_DP END,
	RC_DEB.SLD_DP =RC_DEB.VLR_DP
FROM         
	#RsDup RC_DEB LEFT OUTER JOIN  #RsDupCRED bx ON
	RC_DEB.CD_EMP = bx.CD_EMP AND
	RC_DEB.CD_FILIAL = bx.CD_FILIAL AND
	RC_DEB.CD_RC_DEB = bx.CD_RC_DEB 
WHERE
	bx.CD_RC_DEB IS NULL

--Aplicando na duplicata
UPDATE
	RC_DEB
SET 
	RC_DEB.SLD_DP=Dup.SLD_DP,
	RC_DEB.STS_DP = Dup.STS_DP
FROM
	RC_DEB INNER JOIN #RsDup Dup on
	RC_DEB.CD_EMP = Dup.CD_EMP AND
	RC_DEB.CD_FILIAL = Dup.CD_FILIAL AND
	RC_DEB.CD_RC_DEB = Dup.CD_RC_DEB 
WHERE
	(RC_DEB.SLD_DP<>Dup.SLD_DP)
	OR (RC_DEB.STS_DP <> Dup.STS_DP)

DROP TABLE #RsDup;
DROP TABLE #RsDupCRED;