/****** Object:  StoredProcedure [dbo].[P_RC_DEB_HISTORICO_CHQ_LANC_CPL]    Script Date: 03/26/2014 10:46:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_RC_DEB_HISTORICO_CHQ_LANC_CPL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_RC_DEB_HISTORICO_CHQ_LANC_CPL]
GO
/****** Object:  StoredProcedure [dbo].[P_RC_DEB_HISTORICO_CHQ_LANC_CPL]    Script Date: 17/02/2016 16:55:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec P_RC_DEB_HISTORICO_CHQ_LANC_CPL 1,'2014-06-01'
CREATE PROCEDURE [dbo].[P_RC_DEB_HISTORICO_CHQ_LANC_CPL] @CD_EMP INT=1, @DT_PROC DATETIME=NULL WITH ENCRYPTION AS 
SET NOCOUNT ON
DECLARE @CD_CTR INT
DECLARE @DT_ATUAL DATETIME 
SET @DT_ATUAL=GETDATE()

IF @DT_PROC IS NULL 
begin
	SET @DT_PROC = CONVERT(CHAR(10),GETDATE(),112)
end

CREATE TABLE #RSCHEQUES (CD_EMP INT, CD_CHQ INT, VLR_CHQ_SLD MONEY)

--Gerando contas a pagar da filial
INSERT INTO #RSCHEQUES
SELECT     
	CHQ_LANC_CPL.CD_EMP, 
	CHQ_LANC_CPL.CD_CHQ,
	CHQ_LANC_CPL.VLR_CHQ
FROM          
	CHQ_LANC_CPL
WHERE    
	 DT_EMI<=@DT_PROC
	AND (NOT EXISTS (SELECT     
					CHQ_DESC_CPL.CD_CHQ
                FROM          
						CHQ_DESC_CPL INNER JOIN CHQ_DESC ON
						CHQ_DESC_CPL.CD_EMP = CHQ_DESC.CD_EMP AND
						CHQ_DESC_CPL.CD_DESC = CHQ_DESC.CD_DESC 
                WHERE      
					CHQ_DESC_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP AND 
					CHQ_DESC_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ
					AND CHQ_DESC.DT_DESC<=@DT_PROC)) 
	AND (NOT EXISTS (SELECT     
							CHQ_DEPOS_CPL.CD_CHQ
                      FROM          
						CHQ_DEPOS_CPL INNER JOIN CHQ_DEPOS ON
						CHQ_DEPOS_CPL.CD_EMP = CHQ_DEPOS.CD_EMP AND
						CHQ_DEPOS_CPL.ANO = CHQ_DEPOS.ANO AND
						CHQ_DEPOS_CPL.CD_DEPOS = CHQ_DEPOS.CD_DEPOS 
                      WHERE      
							CHQ_DEPOS_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP AND 
							CHQ_DEPOS_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ
							AND CHQ_DEPOS.DT_DEPOS<=@DT_PROC)) 
	AND (NOT EXISTS (SELECT     
						CD_CHQ
					FROM          
						
						CHQ_DEVO 
					WHERE      
						CHQ_DEVO.CD_EMP = CHQ_LANC_CPL.CD_EMP AND 
						CHQ_DEVO.CD_CHQ = CHQ_LANC_CPL.CD_CHQ
						AND CHQ_DEVO.DT_DEVO<=@DT_PROC)) 
UNION
SELECT     
	CHQ_LANC_CPL.CD_EMP, 
	CHQ_LANC_CPL.CD_CHQ,
	CHQ_LANC_CPL.VLR_CHQ
FROM          
	CHQ_LANC_CPL INNER JOIN CHQ_DEVO ON
	CHQ_LANC_CPL.CD_EMP = CHQ_DEVO.CD_EMP AND
	CHQ_LANC_CPL.CD_CHQ = CHQ_DEVO.CD_CHQ
WHERE    
	 CHQ_DEVO.DT_DEVO<=@DT_PROC
UNION
SELECT     
	CHQ_LANC_CPL.CD_EMP, 
	CHQ_LANC_CPL.CD_CHQ,
	CHQ_LANC_CPL.VLR_CHQ
FROM          
	CHQ_LANC_CPL INNER JOIN CHQ_REDEPOS ON
	CHQ_LANC_CPL.CD_EMP = CHQ_REDEPOS.CD_EMP AND
	CHQ_LANC_CPL.CD_CHQ = CHQ_REDEPOS.CD_CHQ
WHERE    
	 DT_REDEPOS<=@DT_PROC

SET @CD_CTR=ISNULL((SELECT 
	MAX(CD_CTR)
FROM
	RC_DEB_HISTORICO
WHERE
	DT_PROC=@DT_PROC),0)

IF @CD_CTR > 0
begin
	DELETE 
		FROM RC_DEB_HISTORICO_CPL_CHQ_LANC_CPL
	WHERE
		CD_EMP =@CD_EMP
		AND CD_CTR=@CD_CTR

	PRINT 'Lote atual.: ' + CONVERT(VARCHAR, @CD_CTR) + ' cheques em aberto ' + convert(varchar,getdate(),108)
end

IF @CD_CTR=0
begin
	INSERT INTO RC_DEB_HISTORICO (DT_PROC,DT_CAD)
	SELECT
		@DT_PROC,
		@DT_ATUAL

	SET @CD_CTR = IDENT_CURRENT( 'RC_DEB_HISTORICO' )
	PRINT 'Lote novo.: ' + CONVERT(VARCHAR, @CD_CTR) + ' cheques em aberto ' + convert(varchar,getdate(),108)
end

--Analisando cheques cobranca
UPDATE
	a
SET 
	a.VLR_CHQ_SLD= (a.VLR_CHQ_SLD - e.VLR_RECB) 
FROM         
	#RSCHEQUES a INNER JOIN CHQ_DEST_ENV_CPL e ON
	a.CD_EMP = e.CD_EMP AND
	a.CD_CHQ = e.CD_CHQ
WHERE
	e.DT_RECB<=@DT_PROC

--Deletando cheques com saldo zerado
DELETE FROM #RSCHEQUES WHERE VLR_CHQ_SLD <=0

INSERT INTO RC_DEB_HISTORICO_CPL_CHQ_LANC_CPL (
	CD_CTR,
	CD_EMP,
	CD_CHQ,
	VLR_CHQ_SLD)
SELECT
	@CD_CTR,      
	a.CD_EMP,
	a.CD_CHQ,
	a.VLR_CHQ_SLD
FROM         
	#RSCHEQUES a

DROP TABLE #RSCHEQUES
