IF OBJECT_ID('p_plug_Farma_tipoRegistro_trintaEDois') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_trintaEDois
	END
 
GO 
CREATE PROCEDURE p_plug_Farma_tipoRegistro_trintaEDois (@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 32 AS tipoRegistro,
		   ROW_NUMBER() OVER(ORDER BY P.CD_CTR ASC) AS numeroRegistro,
		   ISNULL(REPLACE(CONVERT(VARCHAR(10), p.dt_ult_alt, 103), '/', ''),'') as dataUltimaAtualizacao,
		   P.CD_CTR AS codigoDespesa,
		    CASE F.PESSOA 
					WHEN 1 THEN 1
					WHEN 0 THEN 2
			END AS tipoPessoa,
			F.CGC_CPF AS cpfCnpjFornecedor,
			ISNULL(GLB_DESP_REC.DS_DESP_REC,'') AS descricaoDespesa, /*confirmar a questão dos vazios*/
			REPLACE(CONVERT(VARCHAR(10), p.dt_emis, 103), '/', '') as dataEmissao,
			P.VLR_DP as valorTotal,
			P.vlr_devolucao as valorCredito,
			P.vlr_devolucao as valorUtilizado /*Ver com DJALMA*/,
			'0' as status /*Ver com DJALMA*/,
			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
		FROM PG_CRED P
		INNER JOIN PG_FORN F ON F.CD_FORN = P.CD_FORN
		OUTER APPLY (SELECT TOP 1 GLB_DESP_REC.DS_DESP_REC
				 FROM  PG_DESP_RAT_PG_CRED  
				LEFT OUTER JOIN PG_DESP_RAT ON PG_DESP_RAT_PG_CRED.CD_EMP = PG_DESP_RAT.CD_EMP
					AND PG_DESP_RAT_PG_CRED.Cd_Rat_Desp = PG_DESP_RAT.Cd_Rat_Desp
				LEFT OUTER JOIN GLB_DESP_REC ON PG_DESP_RAT.CD_EMP = GLB_DESP_REC.CD_EMP
					AND PG_DESP_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC
				 WHERE  P.CD_EMP = PG_DESP_RAT_PG_CRED.CD_EMP
						AND P.CD_FILIAL = PG_DESP_RAT_PG_CRED.CD_FILIAL
						AND P.NR_DP = PG_DESP_RAT_PG_CRED.NR_DP
						AND P.Ano = PG_DESP_RAT_PG_CRED.Ano) AS GLB_DESP_REC
		WHERE CONVERT(DATE,DT_EMIS) BETWEEN @DataInicio AND @DataFim
		AND p.CD_FILIAL = @filial
		AND p.CD_EMP = @cd_emp
		and vlr_devolucao > 0
		AND 0 = @NaoEnviarCargaInicial /*Solicitado pelo Julio*/
		ORDER BY DT_EMIS DESC

END