IF OBJECT_ID('p_plug_Farma_tipoRegistro_trintaETres') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_trintaETres
	END
 
GO 
CREATE PROCEDURE [dbo].[p_plug_Farma_tipoRegistro_trintaETres] (@filial INT , @data   DATE , @cd_emp INT  = 1, @CargaInicial tinyint=0) AS
BEGIN
--p_plug_Farma_tipoRegistro_trintaETres 99, '2025-11-10'

	DECLARE @DataInicio DATE
	DECLARE @DataFim DATE
	DECLARE @MandarApenasEmAberto INT = 0

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

	SELECT '33' as tipoRegistro,
		   CONCAT(R.CD_EMP, R.CD_FILIAL, R.CD_RC_DEB) AS codigoContasReceber,
		   REPLACE(CONVERT(VARCHAR(10), R.DT_DP, 103), '/', '') as dataEmissao,
		 --  ISNULL(REPLACE(CONVERT(VARCHAR(10), ISNULL(R.dt_ult_alt,R.DT_DP), 103), '/', ''),'') as dataUltimaAtualizacao,
		  CASE WHEN D.DT_PAG IS NOT NULL AND D.DT_PAG = @data 
		  THEN REPLACE(CONVERT(VARCHAR(10), D.DT_PAG, 103), '/', '')
				ELSE CASE WHEN R.dt_ult_alt < R.DT_DP THEN REPLACE(CONVERT(VARCHAR(10),R.DT_DP,103), '/', '') ELSE ISNULL(REPLACE(CONVERT(VARCHAR(10),R.dt_ult_alt,103), '/', ''), REPLACE(CONVERT(VARCHAR(10),R.DT_DP,103), '/', '')) END END AS dataUltimaAtualizacao,
		   '' as categoriaPlanoContas /*Revisar com Djalma*/,
		   REPLACE(CONVERT(VARCHAR(10), R.DT_VENCTO, 103), '/', '') as dataVencimento,
		   R.VLR_DP AS valorTotal,
		   ISNULL(D.VLR_PGTO,0) AS valorTotalRecebido,
		   ISNULL(R.SLD_DP,0) AS valorTotalAReceber,
		   CASE r.STS_DP
			WHEN 0
				THEN 0
			WHEN 1
				THEN 1--'QUITADA'
			WHEN 2
				THEN 3--'PGTO PARCIAL'
			WHEN 3
				THEN 2--'CANCELADA'
			ELSE 0 END as status ,
			CASE r.STS_DP
			WHEN 0
				THEN 'ABERTO'
			WHEN 1
				THEN 'PAGO'--'QUITADA'
			WHEN 2
				THEN 'PAGAMENTO PARCIAL'--'PGTO PARCIAL'
			WHEN 3
				THEN 'CANCELADO'--'CANCELADA'
			ELSE 'ABERTO' END as statusDescricao,
		   '' as recorrencia,
		   R.PARC as numeroParcela,
		   ISNULL(PARCELA.quantidade,0) as totalParcela,
		   ISNULL(RC_DEB_NF.NR_NF, '') as numeroNota,
		   case when ISNULL(RC_DEB_NF.NR_NF, '') <> '' then '04' else '05' end as tipoNota,
		   case when ISNULL(RC_DEB_NF.NR_NF, '') <> '' then 'NF-e' else 'NFC-e' end as tipoNotaDescricao,
		   ISNULL(EST_NF_SERIE.SERIE,'') as serieNota,
		   'Balcão' as canal
	INTO #Registros
	FROM RC_DEB R
	OUTER APPLY (SELECT (SUM(ISNULL(D.VL_PGTO, 0)) + SUM(ISNULL(D.VL_JUR, 0))) - (SUM(ISNULL(D.VL_DESC, 0)) + SUM(ISNULL(D.VLR_GLOSA, 0)) + SUM(ISNULL(D.VLR_IMPOSTO, 0))) AS VLR_PGTO,  D.DT_PAG
					 FROM RC_CRED D 
					 WHERE D.CD_EMP = R.CD_EMP
							AND D.CD_FILIAL = R.CD_FILIAL
							AND D.CD_RC_DEB = R.CD_RC_DEB
					 GROUP BY D.CD_EMP, D.CD_FILIAL, D.CD_RC_DEB, D.DT_PAG) D
	OUTER APPLY (SELECT COUNT(DISTINCT PARCELA.PARC) AS Quantidade
					 FROM RC_DEB PARCELA
					 WHERE PARCELA.CD_EMP = R.CD_EMP
					 AND PARCELA.CD_FILIAL = R.CD_FILIAL
					 AND PARCELA.NR_DP = R.NR_DP) PARCELA
	LEFT JOIN DBO.RC_DEB_NF WITH (NOLOCK) ON RC_DEB_NF.CD_EMP = R.CD_EMP
											AND RC_DEB_NF.CD_FILIAL = R.CD_FILIAL
											AND RC_DEB_NF.NR_DP = R.NR_DP
											AND RC_DEB_NF.ANO = R.ANO
	LEFT JOIN EST_NF_SAI SER ON SER.NF_NF = RC_DEB_NF.NR_NF
												AND SER.CD_FILIAL = RC_DEB_NF.CD_FILIAL
												AND SER.CD_EMP = RC_DEB_NF.CD_EMP
	LEFT JOIN EST_NF_SERIE ON EST_NF_SERIE.CD_EMP =SER.CD_EMP
												AND EST_NF_SERIE.CD_NF_SERIE = SER.CD_NF_SERIE
	WHERE R.CD_EMP = @cd_emp
	AND R.CD_FILIAL = @filial
	AND (
	(CONVERT(DATE,DT_DP) BETWEEN @DataInicio AND @DataFim
			OR (R.dt_ult_alt BETWEEN @DataInicio AND @DataFim and  R.dt_ult_alt  >=  R.DT_DP))
  			
		 OR EXISTS (SELECT 1
					FROM RC_CRED D2
					WHERE D2.CD_EMP = R.CD_EMP
						AND D2.CD_FILIAL = R.CD_FILIAL
						AND D2.CD_RC_DEB = R.CD_RC_DEB
						AND CONVERT(DATE,D2.DT_PAG) BETWEEN @DataInicio AND @DataFim)
		 )
		 AND (R.dt_ult_alt IS NULL OR R.dt_ult_alt <= @DataFim)
		 
		 
		 
	IF @MandarApenasEmAberto = 1 
	BEGIN

		SELECT tipoRegistro
				,ROW_NUMBER() OVER(ORDER BY codigoContasReceber) AS numeroRegistro
				,codigoContasReceber
				,dataEmissao
				,dataUltimaAtualizacao
				,categoriaPlanoContas
				,dataVencimento
				,valorTotal
				,valorTotalRecebido
				,valorTotalAReceber
				,status
				,statusDescricao
				,recorrencia
				,numeroParcela
				,totalParcela
				,numeroNota
				,tipoNota
				,tipoNotaDescricao
				,serieNota
				,canal
		FROM #Registros
		WHERE [status] IN (0,3)
	END
	ELSE
	BEGIN
		SELECT tipoRegistro
				,ROW_NUMBER() OVER(ORDER BY codigoContasReceber) AS numeroRegistro
				,codigoContasReceber
				,dataEmissao
				,dataUltimaAtualizacao
				,categoriaPlanoContas
				,dataVencimento
				,valorTotal
				,valorTotalRecebido
				,valorTotalAReceber
				,status
				,statusDescricao
				,recorrencia
				,numeroParcela
				,totalParcela
				,numeroNota
				,tipoNota
				,tipoNotaDescricao
				,serieNota
				,canal
		FROM #Registros
	END

END