IF OBJECT_ID('p_plug_Farma_tipoRegistro_trintaEQuatro') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_trintaEQuatro
	END
 
GO 
CREATE PROCEDURE p_plug_Farma_tipoRegistro_trintaEQuatro (@filial INT , @data   DATE , @cd_emp INT  = 1, @CargaInicial tinyint=0) AS
BEGIN

	DECLARE @DataInicio DATE
	DECLARE @DataFim DATE
	DECLARE @NaoEnviarCargaInicial INT = 0
	
	IF @CargaInicial = 1
	BEGIN
		SET @DataInicio = CONVERT(DATE,GETDATE()-90)
		SET @DataFim = CONVERT(DATE,GETDATE()-1)
		SET @NaoEnviarCargaInicial = 1
	END
	ELSE
	BEGIN
		SET @DataInicio = @data
		SET @DataFim = @data
	END

	SELECT '34' as tipoRegistro,
			CONCAT(R.CD_EMP, R.CD_FILIAL, R.CD_RC_DEB) AS numeroRegistroContasReceber,
			CONCAT(R.CD_EMP, R.CD_FILIAL, R.CD_RC_DEB) AS codigoContasReceber,
			ISNULL(REPLACE(CONVERT(VARCHAR(10), R.dt_pag, 103), '/', ''),'') as dataUltimaAtualizacao,
			REPLACE(CONVERT(VARCHAR(10), D.DT_VENCTO, 103), '/', '') as dataVencimento,
			ISNULL(REPLACE(CONVERT(VARCHAR(10), R.dt_pag, 103), '/', ''),'') as dataRecebimento,
			ISNULL(REPLACE(CONVERT(VARCHAR(10), R.dt_pag, 103), '/', ''),'') as dataConciliacao,
			D.VLR_DP as valorTotalParcela,
			ISNULL((
				SELECT SUM(A1.VL_PGTO)
				FROM DBO.RC_CRED AS A1 WITH (NOLOCK)
				WHERE d.CD_EMP = A1.CD_EMP
					AND d.CD_FILIAL = A1.CD_FILIAL
					AND d.CD_RC_DEB = A1.CD_RC_DEB
					AND A1.DT_PAG <= r.DT_PAG
				), 0) as valorRecebidoParcela,
			d.VLR_DP - ISNULL((
				SELECT SUM(A1.VL_PGTO)
				FROM DBO.RC_CRED AS A1 WITH (NOLOCK)
				WHERE d.CD_EMP = A1.CD_EMP
					AND d.CD_FILIAL = A1.CD_FILIAL
					AND d.CD_RC_DEB = A1.CD_RC_DEB
					AND A1.DT_PAG <= r.DT_PAG
				), 0) as valorAReceberParcela,
			r.vl_Desc as valorDesconto,
			r.vl_jur as valorJuros,
			0 as valorMulta,
			case when DATEDIFF(DD, D.DT_VENCTO, r.dt_pag) < 0 then 0 else DATEDIFF(DD, D.DT_VENCTO, r.dt_pag) end AS quantidadeDiasAtraso,
			'08' tipoPagamento,
			'OUTROS' descricaoTipoPagamento,
			MAX(isnull(dbo.BC_BANCO.DS_BC,'')) as nomeBanco,
			MAX(isnull(dbo.BC_CONTAS.AGC_CNT,'')) as numeroAgencia,
			MAX(isnull(concat(dbo.BC_CONTAS.NR_CNT_EDI,dbo.BC_CONTAS.DV_NR_CNT_EDI),0)) as numeroConta,
			'Balcão' as canalContasReceber
	FROM RC_CRED r
	INNER JOIN RC_DEB D ON D.CD_EMP = R.CD_EMP
						AND D.CD_FILIAL = R.CD_FILIAL
						AND D.CD_RC_DEB = R.CD_RC_DEB
	LEFT OUTER JOIN dbo.BC_CONTAS ON 
	r.CD_EMP = dbo.BC_CONTAS.CD_EMP AND 
	r.CD_CNT = dbo.BC_CONTAS.CD_CNT 
	LEFT OUTER JOIN dbo.BC_BANCO ON 
	dbo.BC_CONTAS.CD_EMP = dbo.BC_BANCO.CD_EMP AND 
	dbo.BC_CONTAS.CD_BC = dbo.BC_BANCO.CD_BC
	WHERE r.CD_EMP = @cd_emp
	AND r.CD_FILIAL = @filial
	AND CONVERT(DATE,r.DT_PAG) BETWEEN @DataInicio AND @DataFim
	AND 0 = @NaoEnviarCargaInicial
	GROUP BY  d.CD_EMP, d.CD_FILIAL, d.CD_RC_DEB, R.CD_EMP, R.CD_FILIAL, R.CD_RC_DEB, R.dt_pag, D.DT_VENCTO, r.vl_Desc, r.vl_jur, D.VLR_DP
	
END