IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_GERA_RC_CREDIARIO_FECHA]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_GERA_RC_CREDIARIO_FECHA]
GO
GO

/****** OBJECT:  STOREDPROCEDURE [DBO].[P_GERA_RC_CREDIARIO_PARC]    SCRIPT DATE: 08/04/2015 15:49:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [DBO].[P_GERA_RC_CREDIARIO_FECHA]( @CD_EMP INT,  @CD_FILIAL INT, @CD_VD INT )
WITH ENCRYPTION 
AS
SET NOCOUNT ON;
  
/*****************************************************************************************************************/
--	VALIDACOES INICIAIS
/*****************************************************************************************************************/

-------------- SE EXISTIR STATUS -1 OU 2 NÃO EXIBE ERRO E PROCEDURE É CONCLUÍDA ---------------
-------------- CASO CONTRÁRIO TODOS OS PASSOS DA PROCEDURE SERÃO EXECUTADOS     ---------------

IF NOT EXISTS  (SELECT TOP 1 C.CD_EMP  
				  FROM RC_CREDIARIO_PARC  C  
				 WHERE C.CD_EMP		=@CD_EMP
				   AND C.CD_FILIAL =@CD_FILIAL
				   AND C.CD_VD		=@CD_VD
				   AND C.STS_LANC_CRED IN(-1,2)
				   )
BEGIN
	
------------------------------------------------------------------------------------------------
IF NOT EXISTS(SELECT TOP 1 C.CD_EMP  
				FROM 
					RC_CREDIARIO_PARC  C 
					INNER JOIN  RC_CLI_CREDIARIO L ON 
					C.CD_EMP  =L.CD_EMP AND
					C.CD_CLI  =L.CD_CLI
					
					INNER JOIN RC_MDL_VENDA M ON 
					L.CD_EMP	=M.CD_EMP AND
					L.CD_MDL_VD =M.CD_MDL_VD  
				WHERE
					C.CD_EMP		=@CD_EMP
					AND C.CD_FILIAL =@CD_FILIAL
					AND C.CD_VD		=@CD_VD )
	BEGIN
		RETURN;
	END

IF EXISTS(SELECT TOP 1 C.CD_EMP FROM
			RC_CREDIARIO_PARC  C
 			INNER JOIN RC_CREDIARIO_FECHA V ON 
			C.CD_CTR = V.CD_CTR AND
			C.CD_EMP = V.CD_EMP  
		   WHERE
		   	C.CD_EMP		=@CD_EMP
		   	AND C.CD_FILIAL =@CD_FILIAL
		   	AND C.CD_VD		=@CD_VD )
BEGIN
	
	RAISERROR ( 'VENDA JA CADASTRADA. FECHAMENTO CREDIARIO', 16, 1); 
	RETURN;
END

IF EXISTS(SELECT TOP 1 C.CD_EMP FROM
			RC_CREDIARIO_PARC  C 
		WHERE
			C.CD_EMP		=@CD_EMP
			AND C.CD_FILIAL =@CD_FILIAL
			AND C.CD_VD		=@CD_VD
			AND C.DT_VENCTO_CRED IS NULL)
BEGIN 
	RAISERROR ( 'DATA DE VENCIMENTO NAO PODE ESTAR EM BRANCO. FECHAMENTO CREDIARIO', 16, 1); 
	RETURN;
END 

IF EXISTS(SELECT TOP 1 C.CD_EMP FROM
			RC_CREDIARIO_PARC  C 
		WHERE
			C.CD_EMP		=@CD_EMP
			AND C.CD_FILIAL =@CD_FILIAL
			AND C.CD_VD		=@CD_VD
			AND LEN(ISNULL(NR_FATURA,''))= 0)
BEGIN 
	RAISERROR ('NR FATURA NAO PODE ESTAR EM BRANCO. FECHAMENTO CREDIARIO', 16,1); 
	RETURN;
END

IF NOT EXISTS(SELECT TOP 1 C.CD_EMP  
		FROM 
			RC_CREDIARIO_PARC  C  
		WHERE
			C.CD_EMP		=@CD_EMP
			AND C.CD_FILIAL =@CD_FILIAL
			AND C.CD_VD		=@CD_VD
			AND C.STS_LANC_CRED=0 )
	BEGIN
		RAISERROR ('LANCAMENTO CREDIARIO JA EFETUADO. FECHAMENTO CREDIARIO', 16, 1); 
		RETURN;
	END

IF NOT EXISTS(SELECT C.CD_EMP
				FROM 
					RC_CREDIARIO_PARC  C 
					INNER JOIN  RC_CLI_CREDIARIO L ON 
					C.CD_EMP =L.CD_EMP AND
					C.CD_CLI =L.CD_CLI

					INNER JOIN RC_MDL_VENDA M ON 
					L.CD_EMP	=M.CD_EMP AND
					L.CD_MDL_VD =M.CD_MDL_VD 
 
					LEFT JOIN RC_CREDIARIO_FECHA V ON 
					C.CD_CTR = V.CD_CTR AND
					C.CD_EMP = V.CD_EMP   
				WHERE
					C.CD_EMP		=@CD_EMP
					AND C.CD_FILIAL =@CD_FILIAL
					AND C.CD_VD		=@CD_VD 
					AND C.STS_LANC_CRED=0
					AND M.TP_FECHA =4
					AND V.CD_EMP IS NULL)
	BEGIN
		PRINT 'TIPO FECHAMENTO CREDIARIO INVALIDO. FECHAMENTO CREDIARIO'
		RETURN;
	END

/*****************************************************************************************************************/
--	GERACAO FECHAMENTO CREDIARIO
/*****************************************************************************************************************/ 

		INSERT INTO RC_CREDIARIO_FECHA (CD_EMP,CD_CLI,
											DT_VENCTO,DT_CAD,VLR_FECHA,CD_USU,
											STS_FECH_CRED,VLR_MINIMO,DT_LIMITE_PGTO,
											CD_CTR_FECHA_ANT,DT_FECHA,FLAG_NAO_FECHAR,CD_CTR, PERC_DESC_PONTUAL)
		SELECT 
			C.CD_EMP,
			C.CD_CLI CD_CLI,
			C.DT_VENCTO_CRED DT_VENCTO, 
			GETDATE() DT_CAD,
			C.SLD_PARC_CRED VLR_FECHA,
			1 CD_USU,
			0 STS_FECH_CRED,
			0 VLR_MINIMO,
			C.DT_VENCTO_CRED DT_LIMITE_PGTO,
			0 CD_CTR_FECHA_ANT,
			CONVERT(DATE,GETDATE()) DT_FECHA,
			0 FLAG_NAO_FECHAR,
			C.CD_CTR,
			ISNULL(M.PERC_DESC_PONTUAL,0) PERC_DESC_PONTUAL
		FROM 
			RC_CREDIARIO_PARC  C
 			
			INNER JOIN  RC_CLI_CREDIARIO L ON 
			C.CD_EMP  =L.CD_EMP AND
			C.CD_CLI =L.CD_CLI

			INNER JOIN RC_MDL_VENDA M ON 
			L.CD_EMP  =M.CD_EMP AND
			L.CD_MDL_VD =M.CD_MDL_VD 
 
			LEFT JOIN RC_CREDIARIO_FECHA V ON 
			C.CD_CTR = V.CD_CTR AND
			C.CD_EMP = V.CD_EMP   
		WHERE
			C.CD_EMP		=@CD_EMP
			AND C.CD_FILIAL =@CD_FILIAL
			AND C.CD_VD		=@CD_VD 
			AND C.STS_LANC_CRED IN (0,2)
			AND M.TP_FECHA =4
			AND V.CD_EMP IS NULL;
	    
		IF @@ROWCOUNT =0 
		BEGIN	
			PRINT 'NENHUMA VENDA ENCONTRADA'
			RETURN
		END

		INSERT INTO  RC_CREDIARIO_PARC_RC_CREDIARIO_FECHA (CD_CTR,CD_CTR_FECHA)
		SELECT
			V.CD_CTR,
			V.CD_CTR_FECHA
		FROM 
			RC_CREDIARIO_PARC  A
 			INNER JOIN RC_CREDIARIO_FECHA V ON 
			A.CD_CTR = V.CD_CTR AND
			A.CD_EMP = V.CD_EMP  
			LEFT JOIN RC_CREDIARIO_PARC_RC_CREDIARIO_FECHA F ON 
			V.CD_CTR = F.CD_CTR AND
			V.CD_CTR_FECHA = F.CD_CTR_FECHA
		WHERE
			A.CD_EMP		=@CD_EMP
			AND A.CD_FILIAL =@CD_FILIAL
			AND A.CD_VD		=@CD_VD
			AND A.STS_LANC_CRED IN (0,2) 
			AND F.CD_CTR_FECHA IS NULL; 

		IF @@ROWCOUNT>0
		BEGIN
			UPDATE A 
			SET
				A.STS_LANC_CRED=1
			FROM 
				RC_CREDIARIO_PARC  A  
			WHERE
				A.CD_EMP		=@CD_EMP
				AND A.CD_FILIAL =@CD_FILIAL
				AND A.CD_VD		=@CD_VD 
		END
/*****************************************************************************************************************/
-- GERACAO DA DUPLICATA
/*****************************************************************************************************************/
	
		DECLARE @NR_DUP				AS INT,
				@CD_RC_DEB			AS INT,
				@ANO				AS INT; 

		--SE NAO EXISTE TITULO
		IF NOT EXISTS (SELECT TOP 1 D.CD_EMP 
							FROM  
								RC_CREDIARIO_FECHA V
								INNER JOIN RC_CREDIARIO_FECHA_RC_DEB D ON
								V.CD_CTR_FECHA  = D.CD_CTR_FECHA AND
								V.CD_EMP		= D.CD_EMP   
 								INNER JOIN RC_CREDIARIO_PARC A ON 
								V.CD_CTR = A.CD_CTR AND
								V.CD_EMP = A.CD_EMP  
							WHERE
								A.CD_EMP		=@CD_EMP
								AND A.CD_FILIAL =@CD_FILIAL
								AND A.CD_VD		=@CD_VD )
		BEGIN

			SET @NR_DUP=0
			SET @ANO=ISNULL(( SELECT  TOP 1 YEAR(DT_FECHA_CRED) 
						FROM RC_CREDIARIO_PARC A
						WHERE
							A.CD_EMP		=@CD_EMP
							AND A.CD_FILIAL =@CD_FILIAL
							AND A.CD_VD		=@CD_VD ),YEAR(GETDATE())); 
										 
			SET @NR_DUP = ISNULL((SELECT 
									 MAX(NR_DP) NR_DP_ULT 
								FROM
									RC_DEB_DUP_NR_CTR
								WHERE
									ANO = @ANO
									AND CD_EMP=1
									AND CD_FILIAL =@CD_FILIAL),RIGHT(@ANO * 1000000,LEN(@ANO * 1000000)-2));
			SET @NR_DUP= ISNULL(@NR_DUP,0)+1;

			 -- GRAVAR NUMERO
			 INSERT INTO RC_DEB_DUP_NR_CTR (CD_EMP,CD_FILIAL,NR_DP,ANO,TP_DOC) 
			 VALUES (@CD_EMP,@CD_FILIAL,@NR_DUP,@ANO,0)			 

			--OBTER O PROXIMO REGISTRO RC_DEB
			SELECT @CD_RC_DEB=ISNULL(MAX(CD_RC_DEB),0)   
				FROM 
					RC_DEB 
				WHERE 
					CD_EMP=1 
					AND CD_FILIAL =@CD_FILIAL

			--REGISTRANDO O TITULO
			INSERT INTO RC_DEB (CD_EMP,	CD_FILIAL, CD_RC_DEB, CD_CLI,VLR_DP, PARC, DT_VENCTO,
								DT_DP,NR_DP,COB,SLD_DP,  CD_BC,ANO,ORIG_LANC, CD_USU,HIST, CD_HIST,VLR_COMIS_CARTAO,
								NR_BOLETA,	VLR_DESC,DT_CAD, NR_DOC,NR_FATURA)
			SELECT
				A.CD_EMP, 
				A.CD_FILIAL, 
				@CD_RC_DEB + A.PARC_CRED CD_RC_DEB,
				A.CD_CLI, 
				A.SLD_PARC_CRED VLR_DP,
				A.PARC_CRED, 
				A.DT_VENCTO_CRED DT_VENCTO, 
				A.DT_LANC_CRED DT_DP, 
				@NR_DUP NR_DP, 
				1 COB, 
				ISNULL(A.SLD_PARC_CRED,0) SLD_DP, 
				1 CD_BC, 
				@ANO ANO, 
				1 ORIG_LANC,
				1 CD_USU, 
				'FECHAMENTO CREDIARIO AUTO - PARC. ' + CONVERT(VARCHAR(10),A.PARC_CRED)  + ' CTR ' + CONVERT(VARCHAR(135),A.CD_CTR) + ' - FILIAL ' + CONVERT(VARCHAR(5), A.CD_FILIAL) + ' CD_VD ' +CONVERT(VARCHAR(35),A.CD_VD) HIST, 
				21 CD_HIST, 
				0 VLR_COMIS_CARTAO,
				'' NR_BOLETA,
				ROUND(( ( (ISNULL(V.PERC_DESC_PONTUAL,0)/100.0000))*A.SLD_PARC_CRED),2)  VLR_DESC,
				GETDATE() DT_CAD, 
				A.NR_FATURA NR_DOC,
				CAST(@ANO AS VARCHAR)+'-'+CAST(A.CD_FILIAL AS VARCHAR)+'-'+CAST(@NR_DUP AS VARCHAR)+'-'+CAST(A.PARC_CRED AS VARCHAR) AS NR_FATURA
				
			FROM 
			  	RC_CREDIARIO_PARC A 
 				INNER JOIN RC_CREDIARIO_FECHA V ON 
				A.CD_CTR = V.CD_CTR AND
				A.CD_EMP = V.CD_EMP  
			WHERE
				A.CD_EMP		=@CD_EMP
				AND A.CD_FILIAL =@CD_FILIAL
				AND A.CD_VD		=@CD_VD	; 

			INSERT INTO RC_CREDIARIO_FECHA_RC_DEB (CD_EMP,CD_FILIAL, CD_RC_DEB,CD_CTR_FECHA)
			SELECT
				A.CD_EMP,
				A.CD_FILIAL,
				@CD_RC_DEB + A.PARC_CRED CD_RC_DEB,
				V.CD_CTR_FECHA 
			FROM 
			  	RC_CREDIARIO_PARC A 
 				INNER JOIN RC_CREDIARIO_FECHA V ON 
				A.CD_CTR = V.CD_CTR AND
				A.CD_EMP = V.CD_EMP  
			WHERE
				A.CD_EMP		=@CD_EMP
				AND A.CD_FILIAL =@CD_FILIAL
				AND A.CD_VD		=@CD_VD	
		END 
END
