/****** Object:  StoredProcedure [dbo].[P_PG_CRED_ANALISE_SALDOS]    Script Date: 09/04/2013 18:36:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PG_CRED_ANALISE_SALDOS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PG_CRED_ANALISE_SALDOS]
GO
--EXEC P_PG_CRED_ANALISE_SALDOS  1,12,7471
CREATE PROCEDURE [dbo].[P_PG_CRED_ANALISE_SALDOS] @CD_EMP INT=1,@CD_FILIAL INT=0, @CD_PG_CRED INT=0 AS  
CREATE TABLE #RsDup (CD_EMP INT,
					 CD_FILIAL INT,
					 CD_PG_CRED INT,
					 VLR_DP MONEY,
					 VLR_DESP_OUTR MONEY,
					 SLD_DP MONEY,
					 STS_DP INT)
CREATE TABLE #RsDupCRED (CD_EMP INT,
					 CD_FILIAL INT,
					 CD_PG_CRED INT,
					 VL_PGTO MONEY)
/*
	Ignora Status 3,4,6 
	3 - Cancelada
	4 - Renegociada
	6 - Estornada
*/
IF @CD_PG_CRED = 0 
BEGIN
	--Todas as duplicatas
	INSERT INTO #RsDup
		(CD_EMP,
		CD_FILIAL,
		CD_PG_CRED,
		VLR_DP,
		VLR_DESP_OUTR,
		SLD_DP,
		STS_DP)
	SELECT
		CD_EMP,
		CD_FILIAL,
		CD_PG_CRED,
		VLR_DP,
		ISNULL(VLR_DESP_OUTR,0),
		SLD_DP,
		STS_DP
	FROM
		PG_CRED
	WHERE
		STS_DP <> 3	
END
ELSE
BEGIN
	--Passou para analise
	INSERT INTO #RsDup
		(CD_EMP,
		CD_FILIAL,
		CD_PG_CRED,
		VLR_DP,
		VLR_DESP_OUTR,
		SLD_DP,
		STS_DP)
	SELECT
		CD_EMP,
		CD_FILIAL,
		CD_PG_CRED,
		VLR_DP,
		ISNULL(VLR_DESP_OUTR,0),
		SLD_DP,
		STS_DP
	FROM
		PG_CRED
	WHERE
		CD_EMP =@CD_EMP
		AND CD_FILIAL=@CD_FILIAL 
		AND CD_PG_CRED=@CD_PG_CRED
		AND  STS_DP <>3	
END


UPDATE
	PG_CRED
SET
	STS_DP=0
FROM
	PG_CRED LEFT OUTER JOIN PG_DEB_PAGFOR_CONTROLE ON
	PG_CRED.CD_EMP = PG_DEB_PAGFOR_CONTROLE.CD_EMP AND
	PG_CRED.CD_FILIAL = PG_DEB_PAGFOR_CONTROLE.CD_FILIAL AND
	PG_CRED.CD_PG_CRED = PG_DEB_PAGFOR_CONTROLE.CD_PG_CRED 
	INNER JOIN #RsDup C ON  
	 PG_CRED.CD_EMP = C.CD_EMP AND  
	 PG_CRED.CD_FILIAL = C.CD_FILIAL AND  
	 PG_CRED.CD_PG_CRED  = C.CD_PG_CRED  	
WHERE
	PG_CRED.CD_EMP =1
	AND PG_CRED.STS_DP=5
	AND PG_DEB_PAGFOR_CONTROLE.CD_PG_CRED IS NULL



--Buscando credito ha realizado (baixa)
INSERT INTO #RsDupCRED (CD_EMP,
					 CD_FILIAL,
					 CD_PG_CRED,
					 VL_PGTO)
SELECT 
	PG_DEB.CD_EMP, 
	PG_DEB.CD_FILIAL, 
	PG_DEB.CD_PG_CRED, 
	SUM(PG_DEB.VL_PGTO- PG_DEB.VL_IMP) AS VL_PGTO
FROM         
	PG_DEB INNER JOIN #RsDup DUP ON
	PG_DEB.CD_EMP = DUP.CD_EMP AND
	PG_DEB.CD_FILIAL = DUP.CD_FILIAL AND
	PG_DEB.CD_PG_CRED = DUP.CD_PG_CRED
GROUP BY
	PG_DEB.CD_EMP, 
	PG_DEB.CD_FILIAL, 
	PG_DEB.CD_PG_CRED
	  
--Tratando status de duplicata baixada parcialmente  
UPDATE  
 PG_CRED  
SET   
 PG_CRED.STS_DP = 2   
FROM           
 PG_CRED INNER JOIN #RsDupCRED bx ON  
 PG_CRED.CD_EMP = bx.CD_EMP AND  
 PG_CRED.CD_FILIAL = bx.CD_FILIAL AND  
 PG_CRED.CD_PG_CRED  = bx.CD_PG_CRED  
WHERE  
 round(bx.VL_PGTO,2) <> round(PG_CRED.VLR_DP+PG_CRED.VLR_DESP_OUTR,2)
 AND PG_CRED.STS_DP IN (0,6)  
  
 
--Tratando status de duplicata baixada totalmente  
UPDATE  
 PG_CRED  
SET   
	PG_CRED.STS_DP = 1,
    PG_CRED.SLD_DP=0
FROM           
 PG_CRED INNER JOIN #RsDupCRED bx ON  
 PG_CRED.CD_EMP = bx.CD_EMP AND  
 PG_CRED.CD_FILIAL = bx.CD_FILIAL AND  
 PG_CRED.CD_PG_CRED  = bx.CD_PG_CRED  
WHERE  
 bx.VL_PGTO > 0  
 AND round(bx.VL_PGTO,2) = round(PG_CRED.VLR_DP+PG_CRED.VLR_DESP_OUTR,2)   
 AND PG_CRED.STS_DP IN (0,2,6)  
  
--Tratando status de duplicata baixada totalmente  
UPDATE  
 PG_CRED  
SET   
 PG_CRED.SLD_DP =0  
FROM           
 PG_CRED INNER JOIN #RsDup C ON  
 PG_CRED.CD_EMP = C.CD_EMP AND  
 PG_CRED.CD_FILIAL = C.CD_FILIAL AND  
 PG_CRED.CD_PG_CRED  = C.CD_PG_CRED     
WHERE  
 PG_CRED.SLD_DP > 0   
 AND PG_CRED.STS_DP = 1  
    
--Analisando o saldo da Duplicata pagamento parcial.  
UPDATE  
 PG_CRED  
SET  
 PG_CRED.SLD_DP = ((PG_CRED.VLR_DP+PG_CRED.VLR_DESP_OUTR) - VL_PGTO)   
FROM           
	PG_CRED INNER JOIN #RsDupCRED bx ON  
	PG_CRED.CD_EMP = bx.CD_EMP AND  
	PG_CRED.CD_FILIAL = bx.CD_FILIAL AND  
	PG_CRED.CD_PG_CRED  = bx.CD_PG_CRED  
WHERE  
 VL_PGTO > 0  
 AND round((PG_CRED.VLR_DP - VL_PGTO),2) <> round(PG_CRED.SLD_DP,2)
 AND STS_DP = 2
GO


