/****** OBJECT:  STOREDPROCEDURE [DBO].[P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER]    SCRIPT DATE: 04/08/2014 09:57:57 ******/
IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER]
GO
--P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER 1,2
CREATE PROCEDURE P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER(@CD_EMP INT, @CD_FND_VERBA INT, @VLR_DUP MONEY)
AS
begin
SET NOCOUNT ON 

CREATE TABLE #RsDup (PARC INT,
					 DT_VENCTO DATE,
					 VLR_DP MONEY,
					 VLR_COMIS_CARTAO MONEY,
					 HIST VARCHAR(150),
					 CD_BC INT,
					 NR_BOLETA VARCHAR(150),
					 CD_HIST INT,
					 VLR_DESC MONEY)

CREATE TABLE #RsDoc (CD_FILIAL INT,
					 CD_FND_VERBA INT)

CREATE TABLE #RsNf (CD_FILIAL INT,
					NR_NF INT,
					DT_EMI_NF DATE,
					VLR_NF MONEY)

CREATE TABLE #RsDebImp (CD_IMP INT,
						VLR_BASE MONEY,
						PERC_IMP MONEY,
						VLR_IMP MONEY)

CREATE TABLE #RsDebReceita (CD_RECEITA INT,
						   PERC_RECEITA MONEY,
						   VLR_RECEITA MONEY,
						   MES_ANO_REF DATE,
						   CD_RAT_RECEITA INT)

DECLARE @CD_FORN INT
DECLARE @CD_CLI INT
DECLARE @CD_FILIAL_FAT INT
DECLARE @CD_RECEITA INT
DECLARE @CD_BANCO INT
DECLARE @CD_HISTORICO INT
DECLARE @CD_PORTADOR INT
DECLARE @TP_CONTRATO INT
DECLARE @DT_VENCTO DATE
DECLARE @DIA_VENCTO INT
DECLARE @DT_LANC DATE
DECLARE @NR_DUP VARCHAR(50)
DECLARE @CD_RC_DEB INT
DECLARE @CD_RAT_RECTA INT
DECLARE @ANO INT

SELECT
	@CD_FORN = CD_FORN,
	@TP_CONTRATO = TP_LANC,
	@CD_FILIAL_FAT = CD_FILIAL_FAT,
	@DT_LANC = DT_LANCTO,
	@DIA_VENCTO = DIA_VENCTO,
	@DT_VENCTO = DT_VENCTO
FROM
	CG_FND_VERBA
WHERE
	CD_EMP = @CD_EMP
	AND CD_FND_VERBA = @CD_FND_VERBA


IF @TP_CONTRATO <> 0
BEGIN
	SET @DT_VENCTO = CONVERT(DATE, CAST(YEAR(DATEADD(MONTH, 1, GETDATE())) AS VARCHAR) + RIGHT('00' + CAST(MONTH(DATEADD(MONTH, 1, GETDATE())) AS VARCHAR),2) + RIGHT('00' + CAST(@DIA_VENCTO AS VARCHAR),2))
END

--PEGANDO O CÓDIGO DO CLIENTE JUNTO AO FORNECEDOR--
SET @CD_CLI = 0
SELECT @CD_CLI = CD_CLI FROM PG_FORN_RC_CLI WHERE CD_FORN = @CD_FORN

IF @CD_CLI = 0
BEGIN
	RAISERROR ('P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER - CLIENTE VINCULADO AO FORNECEDOR NÃO ENCONTRADO.',16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
END


--PEGANDO O CÓDIGO DE RECEITA--
SET @CD_RECEITA = 0
SELECT @CD_RECEITA = VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'CD_RECEITA_VERBA_GERENCIAL'

IF @CD_RECEITA = 0
BEGIN
	RAISERROR ('P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER - CODIGO DA RECEITA PARA LANCAMENTO DO CONTAS A RECEBER NAO ENCONTRADO.',16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
END


--PEGANDO O CÓDIGO DO BANCO--
SET @CD_BANCO = 0
SELECT @CD_BANCO = VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'CD_BANCO_VERBA_GERENCIAL'

IF @CD_BANCO = 0
BEGIN
	RAISERROR ('P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER - CODIGO DA BANCO PARA LANCAMENTO DO CONTAS A RECEBER NAO ENCONTRADO.',16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
END


--PEGANDO O CÓDIGO DO HISTÓRICO--
SET @CD_HISTORICO = 0
SELECT @CD_HISTORICO = VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'CD_HISTORICO_VERBA_GERENCIAL'

IF @CD_HISTORICO = 0
BEGIN
	RAISERROR ('P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER - CODIGO DA HISTORICO PARA LANCAMENTO DO CONTAS A RECEBER NAO ENCONTRADO.',16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
END


--PEGANDO O CÓDIGO DO PORTADOR--
SET @CD_PORTADOR = 0
SELECT @CD_PORTADOR = CD_BC FROM PRC_FILIAL WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL_FAT

IF @CD_PORTADOR = 0
BEGIN
	RAISERROR ('P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER - CODIGO DA HISTORICO PARA LANCAMENTO DO CONTAS A RECEBER NAO ENCONTRADO.',16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
END


SELECT @ANO = YEAR(GETDATE())


--MONTANDO O NUMERO DA DUPLICATA
SET @NR_DUP = ''
SELECT 
	@NR_DUP = CAST(COALESCE(MAX(NR_DP),0) + 1 AS VARCHAR)
FROM 
	RC_DEB_DUP_NR_CTR 
WHERE 
	CD_EMP = @CD_EMP
	AND CD_FILIAL = @CD_FILIAL_FAT
	AND ANO = @ANO
	AND TP_DOC = 0

IF LEN(@NR_DUP) = 1
BEGIN
	SET @NR_DUP = CAST(RIGHT(@ANO ,2) AS VARCHAR) + RIGHT('00000' + CAST(@NR_DUP + 1 AS VARCHAR), 5)
END

IF @NR_DUP = ''
BEGIN
	RAISERROR ('P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER - MONTAR O NUMERO DA DUPLICATA.',16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
END



--POPULANDO AS TABELAS TEMPORARIAS
INSERT INTO
	#RsDup
SELECT
	1,
	@DT_VENCTO,
	@VLR_DUP,
	0,
	'CONTRATO DE VERBA GERENCIAL NR.: ' + CAST(@CD_FND_VERBA AS VARCHAR),
	@CD_BANCO,
	'',
	@CD_HISTORICO,
	0

INSERT INTO
	#RsDoc
SELECT
	@CD_FILIAL_FAT,
	@CD_FND_VERBA

INSERT INTO
	#RsNf
SELECT
	@CD_FILIAL_FAT,
	@CD_FND_VERBA,
	@DT_LANC,
	@VLR_DUP

INSERT INTO
	#RsDebImp
SELECT
	RC_CLI_IMPOSTO.CD_IMP,
	@VLR_DUP,
	GLB_IMPOSTOS.PERC_IMP,
	@VLR_DUP * GLB_IMPOSTOS.PERC_IMP / 100
FROM
	GLB_IMPOSTOS
	INNER JOIN RC_CLI_IMPOSTO ON
		GLB_IMPOSTOS.CD_CTR = RC_CLI_IMPOSTO.CD_IMP

INSERT INTO
	#RsDebReceita
SELECT
	@CD_RECEITA,
	100,
	@VLR_DUP,
	CONVERT(DATE, CAST(YEAR(@DT_LANC) AS VARCHAR) + RIGHT('00' + CAST(MONTH(@DT_LANC) AS VARCHAR), 2) + '01'),
	0




BEGIN TRY
	--INSERINDO O CONTROLE DE NUMERACAO DE DUPLICATA
	INSERT INTO
		RC_DEB_DUP_NR_CTR (CD_EMP, CD_FILIAL, NR_DP, ANO, TP_DOC)
	VALUES
		(@CD_EMP, @CD_FILIAL_FAT, @NR_DUP, @ANO, 0)


	--INSERINDO NA TABELA DO CONTAS A RECEBER
	SELECT @CD_RC_DEB = COALESCE(MAX(CD_RC_DEB), 0) + 1 FROM RC_DEB WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL_FAT

	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)
		SELECT
			@CD_EMP, @CD_FILIAL_FAT, @CD_RC_DEB, @CD_CLI,
			@VLR_DUP, PARC, DT_VENCTO, CONVERT(DATE, GETDATE()),
			@NR_DUP, 0, @VLR_DUP, CD_BC, 
			@ANO, 0, 1, HIST, 
			CD_HIST, VLR_COMIS_CARTAO, NR_BOLETA, VLR_DESC,
			CONVERT(DATE, GETDATE()), 0
		FROM
			#RsDup

	--INSERINDO O IMPOSTO, CASO TENHA
	INSERT INTO
		RC_DEB_IMPOSTO (CD_EMP, CD_FILIAL, CD_RC_DEB,
                        CD_IMP, VLR_BASE, PERC_IMP, VLR_IMP)
	SELECT
		@CD_EMP, @CD_FILIAL_FAT, @CD_RC_DEB,
		CD_IMP, VLR_BASE, PERC_IMP, VLR_IMP
	FROM
		#RsDebImp


	--INSERINDO O VÍNCULO ENTRE RECEBER E CONTRATO DE VERBA
	INSERT INTO
		CG_FND_VERBA_RC_DEB_DUP_NR_CTR (CD_EMP, CD_FND_VERBA, CD_FILIAL, NR_DP, ANO)
	SELECT
		@CD_EMP, @CD_FND_VERBA, @CD_FILIAL_FAT, @NR_DUP, @ANO


	SELECT @CD_RAT_RECTA = COALESCE(MAX(CD_RAT_RECTA),0) + 1 FROM RC_RECTA_RAT WHERE CD_EMP = @CD_EMP
	--INSERINDO OS RATEITOS DE DESPESA
	INSERT INTO
		RC_RECTA_RAT (CD_EMP, CD_FILIAL, CD_RAT_RECTA, CD_DESP_REC, PERC_RECTA, VLR_RECTA, MES_ANO_REF)
	SELECT
		@CD_EMP, @CD_FILIAL_FAT, @CD_RAT_RECTA, CD_RECEITA, PERC_RECEITA, VLR_RECEITA, MES_ANO_REF
	FROM
		#RsDebReceita

	--INSERINDO O VINCULO DO RATEITO DE DESPESA COM A DUPLICATA
	INSERT INTO
		RC_RECTA_RAT_RC_DEB (CD_EMP, CD_FILIAL, NR_DP, ANO, CD_RAT_RECTA)
	VALUES
		(@CD_EMP, @CD_FILIAL_FAT, @NR_DUP, @ANO, @CD_RAT_RECTA)

END TRY

BEGIN CATCH
	RAISERROR ('P_CG_FND_VERBA_FECHAMENTO_CONTRATO_CONTAS_RECEBER - ERRO AO GRAVAR O CONTAS A RECEBER.',16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
END CATCH


DROP TABLE #RsDup
DROP TABLE #RsDoc
DROP TABLE #RsNf
DROP TABLE #RsDebImp
DROP TABLE #RsDebReceita

RETURN

END
