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

	/*TIPO 30 - CONTAS A PAGAR*/
	
	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 30 AS tipoRegistro,
		   P.CD_CTR AS codigoDespesa,
		   CASE F.PESSOA 
					WHEN 1 THEN 1
					WHEN 0 THEN 2
			END AS tipoPessoa,
			F.CGC_CPF AS cpfCnpjFornecedor,
			CASE 
				WHEN ENT.CD_NT_MOV = 4 THEN 'Fornecedor'
			ELSE 'Despesa' END AS tipoDespesa,
			ISNULL(H.DS_HIST,'') AS descricaoDespesa,
			REPLACE(CONVERT(VARCHAR(10), p.dt_vencto, 103), '/', '') as dataVencimento,
			REPLACE(CONVERT(VARCHAR(10), p.dt_emis, 103), '/', '') as dataEmissao,
			ISNULL(REPLACE(CONVERT(VARCHAR(10), ISNULL(p.dt_ult_alt,p.dt_emis), 103), '/', ''),'') as dataUltimaAtualizacao,
			P.VLR_DP AS valorTotal,
			ISNULL(D.VLR_PGTO,0) AS valorPagamento,
			P.SLD_DP AS valorAPagar,
			ISNULL(P.VLR_DESC, 0) as valorDesconto,
			ISNULL(P.VLR_JUROS, 0) as valorJuros,
			ISNULL(p.VLR_DESP_OUTR, 0) as valorMulta,
			CASE P.STS_DP
					WHEN 3 THEN 2 /*CANCELADO*/
					WHEN 1 THEN 1 /*PAGO*/
					WHEN 2 THEN 3 /*PAGO PARCIAL*/
			ELSE 0 /*EM ABERTO*/ END AS status,
			CASE P.STS_DP
					WHEN 3 THEN 'CANCELADO'
					WHEN 1 THEN 'PAGO'
					WHEN 2 THEN 'PAGO PARCIAL'
			ELSE 'EM ABERTO' END as descricaoStatus,
			'' AS recorrencia,
			p.PARC as numeroParcela,
			parc.quantidade as totalParcela,
			ISNULL((
				SELECT TOP 1 PG_CRED_NF.NR_NF
				FROM PG_CRED_NF
				WHERE p.CD_EMP = PG_CRED_NF.CD_EMP
					AND p.CD_FILIAL = PG_CRED_NF.CD_FILIAL
					AND p.NR_DP = PG_CRED_NF.NR_DP
					AND p.ANO = PG_CRED_NF.ANO
				), 0) AS numeroNota,
			ISNULL((
				SELECT TOP 1 EST_NF_SERIE.SERIE
				FROM PG_CRED_NF
				INNER JOIN EST_NF_ENT SER ON SER.NR_NF = PG_CRED_NF.NR_NF
											AND SER.CD_FILIAL = PG_CRED_NF.CD_FILIAL
											AND SER.CD_EMP = PG_CRED_NF.CD_EMP
				INNER 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 p.CD_EMP = PG_CRED_NF.CD_EMP
					AND p.CD_FILIAL = PG_CRED_NF.CD_FILIAL
					AND p.NR_DP = PG_CRED_NF.NR_DP
					AND p.ANO = PG_CRED_NF.ANO
				), 0) AS serieNota
	INTO #Registros
	FROM PG_CRED P
	INNER JOIN PG_FORN F ON F.CD_FORN = P.CD_FORN
	LEFT JOIN PG_CRED_EST_NF_ENT PGENT ON PGENT.CD_EMP = P.CD_EMP
										  AND PGENT.CD_FILIAL = P.CD_FILIAL
										  AND PGENT.NR_DP = P.NR_DP
										  AND PGENT.ANO = P.ANO
										  AND PGENT.CD_NF = P.CD_NF
										  AND PGENT.PARC = P.PARC
	LEFT JOIN EST_NF_ENT ENT ON ENT.CD_EMP = PGENT.CD_EMP
								AND ENT.CD_FILIAL = PGENT.CD_FILIAL
								AND ENT.CD_NF = PGENT.CD_NF
	LEFT JOIN CTB_HIST H ON H.CD_HIST = P.CD_HIST
	OUTER APPLY (SELECT (SUM(ISNULL(D.VL_PGTO, 0)) + SUM(ISNULL(D.VL_JUR, 0))) - (SUM(ISNULL(D.VL_DESC, 0)) + SUM(ISNULL(D.VL_IMP, 0)) + SUM(ISNULL(D.VLR_DEVOLUCAO, 0))) AS VLR_PGTO
				 FROM PG_DEB D 
				 WHERE D.CD_EMP = P.CD_EMP
						AND D.CD_FILIAL = P.CD_FILIAL
						AND D.CD_PG_CRED = P.CD_PG_CRED
				 GROUP BY D.CD_EMP, D.CD_FILIAL, D.CD_PG_CRED) D
	OUTER APPLY (SELECT COUNT(DISTINCT PARCELA.PARC) AS Quantidade
				 FROM PG_CRED PARCELA
				 WHERE PARCELA.CD_EMP = P.CD_EMP
				 AND PARCELA.CD_FILIAL = P.CD_FILIAL
				 AND PARCELA.NR_DP = P.NR_DP) PARC
	WHERE (CONVERT(DATE,p.DT_EMIS) BETWEEN @DataInicio AND @DataFim
			OR EXISTS (SELECT 1 
						FROM PG_DEB D2 
						 WHERE D2.CD_EMP = P.CD_EMP
								AND D2.CD_FILIAL = P.CD_FILIAL
								AND D2.CD_PG_CRED = P.CD_PG_CRED 
								AND CONVERT(DATE,D2.DT_PAG) BETWEEN @DataInicio AND @DataFim) 
		  )
	AND p.CD_FILIAL = @filial
	AND p.CD_EMP = @CD_EMP


	IF @MandarApenasEmAberto = 1 
	BEGIN

		SELECT tipoRegistro
				,ROW_NUMBER() OVER(ORDER BY codigoDespesa ASC) AS numeroRegistro
				,codigoDespesa
				,tipoPessoa
				,cpfCnpjFornecedor
				,tipoDespesa
				,descricaoDespesa
				,dataVencimento
				,dataEmissao
				,dataUltimaAtualizacao
				,valorTotal
				,valorPagamento
				,valorAPagar
				,valorDesconto
				,valorJuros
				,valorMulta
				,status
				,descricaoStatus
				,recorrencia
				,numeroParcela
				,totalParcela
				,numeroNota
				,serieNota
		FROM #Registros
		WHERE [status] IN (0,3)
	END
	ELSE
	BEGIN
		SELECT tipoRegistro
				,ROW_NUMBER() OVER(ORDER BY codigoDespesa ASC) AS numeroRegistro
				,codigoDespesa
				,tipoPessoa
				,cpfCnpjFornecedor
				,tipoDespesa
				,descricaoDespesa
				,dataVencimento
				,dataEmissao
				,dataUltimaAtualizacao
				,valorTotal
				,valorPagamento
				,valorAPagar
				,valorDesconto
				,valorJuros
				,valorMulta
				,status
				,descricaoStatus
				,recorrencia
				,numeroParcela
				,totalParcela
				,numeroNota
				,serieNota
		FROM #Registros
	END
END