IF OBJECT_ID ('P_EST_NF_ENT_CONSULTA_NFE') IS NOT NULL 
 BEGIN 
    DROP PROCEDURE P_EST_NF_ENT_CONSULTA_NFE 
 END 
 GO
 
/*
=======================================================================================
'Job.................:  
'Função..............: Relatório
'Objetivo............: Calcular o custo do produto 
'Tabelas.............: EST_NF_ENT
'Dt Modificação......: 22/10/2020
'Modificação.........: Tag #A001 a Tag #A002 -  Aumentar tamanho do campo NR_AUTORIZADOR
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/
 
CREATE PROCEDURE P_EST_NF_ENT_CONSULTA_NFE ( @CD_FILIAIS 			VARCHAR(MAX), 
											 @DT_INI				DATETIME,
											 @DT_FIM				DATETIME,
											 @MOV 					VARCHAR(MAX),
											 @VISUALIZAR_INTEGRACAO	BIT = NULL,
											 @SEM_CHAVE 			BIT = NULL,
											 @SEM_PRE_ENTRADA		BIT = NULL,
											 @SEM_NOTAS_CANCELADAS  BIT = NULL,
											 @TIPO_DE_PESQUISA 		TINYINT = NULL, /* Reclamem com os */
											 @TIPO_PESQUISA 		TINYINT = NULL, /* programadores da tela */
											 @NR_NF_INI 			INT = NULL,
											 @NR_NF_FIM 			INT = NULL)
AS 
	--P_EST_NF_ENT_CONSULTA_NFE '1','20190101', '20190101', '1',1,1,1,1,1,1,50
	SET NOCOUNT ON;
	DECLARE @TSQL VARCHAR(MAX);
-----------------------------------------------------------------------------------------------------------

	CREATE TABLE #FILIAIS (CD_EMP INT ,CD_FILIAL INT )
	INSERT INTO #FILIAIS 
	SELECT 1,ID FROM UFD_PRM_IN_STRING(@CD_FILIAIS)

	CREATE TABLE #MOV (CD_MOV_OP INT )
	INSERT INTO #MOV 
	SELECT ID FROM UFD_PRM_IN_STRING(@MOV)

-----------------------------------------------------------------------------------------------------------
-- Tag #A001 
	CREATE TABLE #EST_NF_ENT ( CD_EMP            INT
							, CD_FILIAL         INT
							, DT_ENT            DATETIME
							, NR_NF             INT
							, CD_ORIG           INT
							, DT_EMI_NF         DATETIME
							, NM_ORIG           VARCHAR(500)
							, VLR_NF            MONEY
							, DS_MOV_OP         VARCHAR(500)
							, VLR_DESC          MONEY
							, CD_NF             INT
							, NR_AUTORIZADOR    VARCHAR(200)
							, PEND_CROSS        INT
							, TP_MOV            INT
							, CD_PED            INT
							, ID_EVENTO         VARCHAR(10)
							, DS_ST_EVENTO      VARCHAR(10)
							, NM_USU            VARCHAR(10)
							, DT_ULT_ALT_EVENTO VARCHAR(10)
						);
-- Tag #A002
	CREATE TABLE #EST_NF_ENT_NR_PROTOCOLO ( CD_EMP         INT
										, CD_FILIAL      INT
										, CD_NF          INT
										, NR_AUTORIZADOR VARCHAR(200)
										);

SET @TSQL = '
	INSERT INTO #EST_NF_ENT
       SELECT EST_NF_ENT.CD_EMP, 
              DBO.EST_NF_ENT.CD_FILIAL, 
              DBO.EST_NF_ENT.DT_ENT, 
              DBO.EST_NF_ENT.NR_NF, 
              DBO.EST_NF_ENT.CD_FORN AS CD_ORIG, 
              DBO.EST_NF_ENT.DT_EMI_NF, 
              V_GLB_PESSOA.RAZAO_SOCIAL_PESSOA AS NM_ORIG, 
              DBO.EST_NF_ENT.VLR_NF, 
              DBO.GLB_MOV_OP.DS_MOV_OP, 
              DBO.EST_NF_ENT.VLR_DESC, 
              DBO.EST_NF_ENT.CD_NF AS CD_NF, 
              '''', 
              ISNULL(EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_NF,0) AS PEND_CROSS,
              DBO.GLB_MOV_OP.TP_MOV, 
              ISNULL(CP.CD_PED, 0) AS CD_PED,';
IF (@VISUALIZAR_INTEGRACAO = 1)
	SET @TSQL = @TSQL + ' V_INTEGRA_PACOTES_EVENTOS.ID_EVENTO, 
	                     V_INTEGRA_PACOTES_EVENTOS.DS_ST_EVENTO,
	                     V_INTEGRA_PACOTES_EVENTOS.NM_USU,
	                     V_INTEGRA_PACOTES_EVENTOS.DT_ULT_ALT_EVENTO';
ELSE
	SET @TSQL = @TSQL + ''''' AS ID_EVENTO, 
              '''' AS DS_ST_EVENTO, 
              '''' AS NM_USU, 
              '''' AS DT_ULT_ALT_EVENTO'

SET @TSQL = @TSQL + '
       FROM DBO.EST_NF_ENT
		  INNER JOIN #FILIAIS ON 
		  DBO.EST_NF_ENT.CD_EMP = #FILIAIS.CD_EMP
		  AND  DBO.EST_NF_ENT.CD_FILIAL = #FILIAIS.CD_FILIAL
            INNER JOIN DBO.GLB_MOV_OP ON DBO.EST_NF_ENT.CD_NT_MOV = DBO.GLB_MOV_OP.CD_MOV_OP
		    INNER JOIN #MOV ON GLB_MOV_OP.CD_MOV_OP = #MOV.CD_MOV_OP
            INNER JOIN DBO.EST_NF_SERIE ON DBO.EST_NF_ENT.CD_EMP = DBO.EST_NF_SERIE.CD_EMP
                                           AND DBO.EST_NF_ENT.CD_NF_SERIE = DBO.EST_NF_SERIE.CD_NF_SERIE
          
            LEFT JOIN PRC_FILIAL_PG_FORN FF ON EST_NF_ENT.CD_EMP = FF.CD_EMP
                                                     AND EST_NF_ENT.CD_FORN = FF.CD_FORN
            LEFT JOIN GLB_MOV_OP_TP_MOV ON GLB_MOV_OP.TP_MOV = GLB_MOV_OP_TP_MOV.TP_MOV 
            OUTER APPLY UFD_GLB_PESSOA(EST_NF_ENT.CD_FORN, GLB_MOV_OP.DEST_ORIG) V_GLB_PESSOA
            LEFT JOIN
       (
           SELECT V_EST_NF_ENT_IT_PIS_COFINS.CD_EMP, 
                  V_EST_NF_ENT_IT_PIS_COFINS.CD_FILIAL, 
                  V_EST_NF_ENT_IT_PIS_COFINS.CD_NF, 
                  SUM(VLR_BASE_PIS_COFINS) AS VLR_BASE_PIS_COFINS, 
                  SUM(VLR_ICMS_ITENS) AS VLR_ICMS_ITENS
           FROM V_EST_NF_ENT_IT_PIS_COFINS
           GROUP BY V_EST_NF_ENT_IT_PIS_COFINS.CD_EMP, 
                    V_EST_NF_ENT_IT_PIS_COFINS.CD_FILIAL, 
                    V_EST_NF_ENT_IT_PIS_COFINS.CD_NF
       ) RSIMPOSTO ON EST_NF_ENT.CD_EMP = RSIMPOSTO.CD_EMP
                      AND EST_NF_ENT.CD_FILIAL = RSIMPOSTO.CD_FILIAL
                      AND EST_NF_ENT.CD_NF = RSIMPOSTO.CD_NF
            LEFT JOIN PG_CRED_EST_NF_ENT ON EST_NF_ENT.CD_EMP = PG_CRED_EST_NF_ENT.CD_EMP
                                                  AND EST_NF_ENT.CD_FILIAL = PG_CRED_EST_NF_ENT.CD_FILIAL
                                                  AND EST_NF_ENT.CD_NF = PG_CRED_EST_NF_ENT.CD_NF
            INNER JOIN PRC_FILIAL ON EST_NF_ENT.CD_EMP = PRC_FILIAL.CD_EMP
                                     AND EST_NF_ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL
            LEFT JOIN EST_NF_ENT_DAR ON EST_NF_ENT_DAR.CD_EMP = EST_NF_ENT.CD_EMP
                                              AND EST_NF_ENT_DAR.CD_FILIAL = EST_NF_ENT.CD_FILIAL
                                              AND EST_NF_ENT_DAR.CD_NF = EST_NF_ENT.CD_NF
            LEFT JOIN EST_NF_ENT_EF_NF_CTR_NF ON EST_NF_ENT_EF_NF_CTR_NF.CD_EMP = EST_NF_ENT.CD_EMP
                                                       AND EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL = EST_NF_ENT.CD_FILIAL
                                                       AND EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT = EST_NF_ENT.CD_NF
			LEFT JOIN EST_NF_ENT_CROSS_DOCKING_PENDENTE ON EST_NF_ENT.CD_EMP = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_EMP 
											AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_FILIAL 
											AND EST_NF_ENT.CD_NF = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_NF'

IF(@VISUALIZAR_INTEGRACAO = 1)
	SET @TSQL = @TSQL + ' LEFT JOIN V_INTEGRA_PACOTES_EVENTOS ON 
									EST_NF_ENT.CD_EMP = V_INTEGRA_PACOTES_EVENTOS.CD_EMP AND 
									EST_NF_ENT.CD_FILIAL = V_INTEGRA_PACOTES_EVENTOS.CD_FILIAL AND 
									EST_NF_ENT.CD_NF = V_INTEGRA_PACOTES_EVENTOS.CD_CTR AND 
									V_INTEGRA_PACOTES_EVENTOS.TABELA IN (''EST_NF_ENT'')'

IF(@SEM_PRE_ENTRADA = 1)
	SET @TSQL = @TSQL + ' LEFT OUTER JOIN EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT ON 
							EST_NF_ENT.CD_EMP = EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_EMP AND
							EST_NF_ENT.CD_FILIAL = EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_FILIAL AND
							EST_NF_ENT.CD_NF = EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_NF'
							
IF(@TIPO_DE_PESQUISA = 1)
	SET @TSQL = @TSQL + ' INNER'
	
IF @TIPO_DE_PESQUISA in (0,2)
	SET @TSQL = @TSQL + ' LEFT OUTER'
	

	SET @TSQL = @TSQL + ' JOIN EST_NF_ENT_PED_CP cp ON
								EST_NF_ENT.CD_EMP = cp.CD_EMP AND
								EST_NF_ENT.CD_FILIAL = cp.CD_FILIAL AND 
								EST_NF_ENT.CD_NF = cp.CD_NF'

SET @TSQL = @TSQL + ' WHERE EST_NF_ENT.CD_EMP = 1              
         AND EST_NF_ENT.DT_ENT BETWEEN '''+CONVERT(VARCHAR,@DT_INI,112) +''' AND '''+CONVERT(VARCHAR,@DT_FIM,112)+'''
		 AND EST_NF_ENT.STS_NF = 0'
		
IF (@SEM_NOTAS_CANCELADAS = 1)
	SET @TSQL = @TSQL + ' AND EST_NF_ENT.STS_NF = 0'
		
IF(@SEM_PRE_ENTRADA = 1)
	SET @TSQL = @TSQL + ' AND EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_NF IS NULL'

IF(@TIPO_DE_PESQUISA = 2)
	SET @TSQL = @TSQL + ' AND cp.CD_EMP IS NULL'
	
IF(@TIPO_PESQUISA = 0)
	SET @TSQL = @TSQL + ' AND EST_NF_ENT.NR_NF = '+CONVERT(VARCHAR,@NR_NF_INI);
ELSE IF(@TIPO_PESQUISA = 1)
	SET @TSQL = @TSQL + ' AND EST_NF_ENT.NR_NF != '+CONVERT(VARCHAR,@NR_NF_INI);
ELSE IF(@TIPO_PESQUISA = 2)
	SET @TSQL = @TSQL + ' AND EST_NF_ENT.NR_NF > '+CONVERT(VARCHAR,@NR_NF_INI);
ELSE IF(@TIPO_PESQUISA = 3)
	SET @TSQL = @TSQL + ' AND EST_NF_ENT.NR_NF < '+CONVERT(VARCHAR,@NR_NF_INI);
ELSE IF(@TIPO_PESQUISA = 4)
	SET @TSQL = @TSQL + ' AND EST_NF_ENT.NR_NF BETWEEN '+CONVERT(VARCHAR,@NR_NF_INI)+ ' AND ' + CONVERT(VARCHAR,@NR_NF_FIM);

EXEC	(@TSQL)
--select @TSQL

 
/****************************************************************************************************************/
--Gerando o nr protocolo autorizador
/****************************************************************************************************************/


	INSERT INTO #EST_NF_ENT_NR_PROTOCOLO
	SELECT
			EST_NF_ENT.CD_EMP
		, EST_NF_ENT.CD_FILIAL
		, EST_NF_ENT.CD_NF
		, NFE.NR_AUTORIZADOR
	FROM
		#EST_NF_ENT EST_NF_ENT
		INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON EST_NF_ENT.CD_EMP = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP
																AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL
																AND EST_NF_ENT.CD_NF = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF
		INNER JOIN EST_NF_ENT_DANFE_PROVISORIO NFE ON EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR = NFE.CD_CTR
		LEFT JOIN EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID ON EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP
															AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL
															AND EST_NF_ENT.CD_NF = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_NF
		LEFT JOIN EST_NF_ENT_NFE ON EST_NF_ENT.CD_EMP = EST_NF_ENT_NFE.CD_EMP
									AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_NFE.CD_FILIAL
									AND EST_NF_ENT.CD_NF = EST_NF_ENT_NFE.CD_NF
	WHERE EST_NF_ENT.CD_EMP = 1
			AND EST_NF_ENT_NFE.CD_EMP IS NULL
			AND EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP IS NULL;


	INSERT INTO #EST_NF_ENT_NR_PROTOCOLO
    SELECT
            EST_NF_ENT.CD_EMP
        , EST_NF_ENT.CD_FILIAL
        , EST_NF_ENT.CD_NF
        , NFE.NR_AUTORIZADOR
    FROM
        #EST_NF_ENT EST_NF_ENT
        INNER JOIN EST_NF_ENT_NFE NFE ON EST_NF_ENT.CD_EMP = NFE.CD_EMP
                                            AND EST_NF_ENT.CD_FILIAL = NFE.CD_FILIAL
                                            AND EST_NF_ENT.CD_NF = NFE.CD_NF
        LEFT OUTER JOIN EST_NF_ENT_EST_NF_SAI_TRANSF ON EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP
                                                        AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST
                                                        AND EST_NF_ENT.CD_NF = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT
    WHERE EST_NF_ENT.CD_EMP = 1
            AND EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT IS NULL 

	INSERT INTO #EST_NF_ENT_NR_PROTOCOLO
	SELECT
			EST_NF_ENT.CD_EMP
		, EST_NF_ENT.CD_FILIAL
		, EST_NF_ENT.CD_NF
		, NFE.NR_AUTORIZADOR
	FROM
		#EST_NF_ENT EST_NF_ENT
		INNER JOIN EST_NF_ENT_EST_NF_SAI_TRANSF ON EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP
													AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST
													AND EST_NF_ENT.CD_NF = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT
		INNER JOIN EST_NF_SAI_NFE NFE ON EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = NFE.CD_EMP
											AND EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = NFE.CD_FILIAL
											AND EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = NFE.CD_NF
	WHERE 
		EST_NF_ENT.CD_EMP = 1 
			 
	INSERT INTO #EST_NF_ENT_NR_PROTOCOLO
    SELECT
            EST_NF_ENT.CD_EMP
        , EST_NF_ENT.CD_FILIAL
        , EST_NF_ENT.CD_NF
        , EST_NF_ENT_NFE_FORN_VALID.NR_AUTORIZADOR
    FROM
        #EST_NF_ENT EST_NF_ENT
        INNER JOIN EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID ON EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP
                                                            AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL
                                                            AND EST_NF_ENT.CD_NF = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_NF
        INNER JOIN EST_NF_ENT_NFE_FORN_VALID ON EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP = EST_NF_ENT_NFE_FORN_VALID.CD_EMP
                                                AND EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL = EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL
                                                AND EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_CTR = EST_NF_ENT_NFE_FORN_VALID.CD_CTR
        LEFT JOIN EST_NF_ENT_NFE ON EST_NF_ENT.CD_EMP = EST_NF_ENT_NFE.CD_EMP
                                    AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_NFE.CD_FILIAL
                                    AND EST_NF_ENT.CD_NF = EST_NF_ENT_NFE.CD_NF
        LEFT OUTER JOIN EST_NF_ENT_EST_NF_SAI_TRANSF ON EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP
                                                        AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST
                                                        AND EST_NF_ENT.CD_NF = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT
    WHERE EST_NF_ENT.CD_EMP = 1
            AND EST_NF_ENT_NFE.CD_EMP IS NULL
            AND EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT IS NULL 

	-------------------------------------------------------------------------------------------------------------
	delete from #EST_NF_ENT_NR_PROTOCOLO where ISNULL(NR_AUTORIZADOR,'')  ='';

	UPDATE A
	SET  NR_AUTORIZADOR = B.NR_AUTORIZADOR
	FROM 
		#EST_NF_ENT A
		INNER JOIN #EST_NF_ENT_NR_PROTOCOLO B ON A.CD_EMP = B.CD_EMP
												AND A.CD_FILIAL = B.CD_FILIAL
												AND A.CD_NF = B.CD_NF;

	IF @SEM_CHAVE = 1
		  delete from #EST_NF_ENT where LEN(NR_AUTORIZADOR)>0;
	-------------------------------------------------------------------------------------------------------------


/*************************************************************************************************************************************/

--Resultado final
/*************************************************************************************************************************************/
	SELECT 
		CD_FILIAL, 
		DT_ENT, 
		NR_NF, 
		CD_ORIG, 
		DT_EMI_NF, 
		NM_ORIG, 
		VLR_NF, 
		DS_MOV_OP, 
		VLR_DESC, 
		CD_NF, 
		NR_AUTORIZADOR, 
		PEND_CROSS, 
		TP_MOV, 
		CD_PED, 
		ID_EVENTO, 
		DS_ST_EVENTO, 
		NM_USU, 
		DT_ULT_ALT_EVENTO
	FROM 
		#EST_NF_ENT ;
/*************************************************************************************************************************************/