IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_MOVIMENTACAO_NF]') AND type in (N'P', N'PC'))
begin 
DROP PROCEDURE P_MOVIMENTACAO_NF
end 
go
CREATE  PROCEDURE P_MOVIMENTACAO_NF  
AS  
     CREATE TABLE #MOVIMENTACAO_NF  
     (STATUS       VARCHAR(50),   
      MOVIMENTACAO VARCHAR(50),   
      QTDE         INT  
     );  
     --MOVIMENTAÇÃO VÁLIDA  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT 'VALIDADA' AS STATUS,   
                   'SAIDA' AS MOVIMENTACAO,   
                   COUNT(NF.CD_NF) AS QTDE  
            FROM EST_NF_SAI(NOLOCK) NF  
                 INNER JOIN EST_NF_SERIE SERIE(NOLOCK) ON NF.CD_NF_SERIE = SERIE.CD_NF_SERIE  
                                                          AND SERIE.ESPC = 'NFE'  
                 INNER JOIN EST_NF_SAI_NFE NFE(NOLOCK) ON NF.CD_EMP = NFE.CD_EMP  
                                                          AND NF.CD_FILIAL = NFE.CD_FILIAL  
                                                          AND NF.CD_NF = NFE.CD_NF  
            WHERE NF.DT_EMI_NF = CONVERT(CHAR(10), GETDATE(), 112)  
                  AND NFE.NR_PROTOCOLO IS NOT NULL  
                  AND NFE.NR_AUTORIZADOR IS NOT NULL;  
  
     --MOVIMENTAÇÃO NÃO VÁLIDA       
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT 'NAO_VALIDADA' AS STATUS,   
                   'SAIDA' AS MOVIMENTACAO,   
                   COUNT(EST_NFE_LOTE.CD_NFE_LOTE) AS QTDE  
            FROM EST_NFE_LOTE_SAI  
                 INNER JOIN EST_NF_SAI(NOLOCK) ON EST_NFE_LOTE_SAI.CD_EMP = EST_NF_SAI.CD_EMP  
                                                  AND EST_NFE_LOTE_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL  
                                                  AND EST_NFE_LOTE_SAI.CD_NF = EST_NF_SAI.CD_NF  
                 INNER JOIN EST_NFE_LOTE(NOLOCK) ON EST_NFE_LOTE_SAI.CD_NFE_LOTE = EST_NFE_LOTE.CD_NFE_LOTE  
                 INNER JOIN GLB_USU(NOLOCK) ON EST_NFE_LOTE.CD_USU = GLB_USU.CD_USU  
                 LEFT OUTER JOIN EST_NF_SAI_NFE_PEND(NOLOCK) ON EST_NFE_LOTE_SAI.CD_EMP = EST_NF_SAI_NFE_PEND.CD_EMP  
                                                                AND EST_NFE_LOTE_SAI.CD_FILIAL = EST_NF_SAI_NFE_PEND.CD_FILIAL  
                                                                AND EST_NFE_LOTE_SAI.CD_NF = EST_NF_SAI_NFE_PEND.CD_NF  
                 LEFT OUTER JOIN EST_NF_SAI_NFE(NOLOCK) ON EST_NFE_LOTE_SAI.CD_EMP = EST_NF_SAI_NFE.CD_EMP  
                                                           AND EST_NFE_LOTE_SAI.CD_FILIAL = EST_NF_SAI_NFE.CD_FILIAL  
                                                           AND EST_NFE_LOTE_SAI.CD_NF = EST_NF_SAI_NFE.CD_NF  
            WHERE EST_NFE_LOTE_SAI.CD_EMP = 1  
                  AND EST_NF_SAI.DT_EMI_NF >= CONVERT(CHAR(10), GETDATE() - 7, 112)  
                  AND EST_NF_SAI_NFE_PEND.CD_NF IS NULL  
                  AND EST_NF_SAI_NFE.CD_NF IS NULL  
                  AND EST_NF_SAI.STS_NF = 0;  
     INSERT INTO #MOVIMENTACAO_NF            
            --NOTAS PENDENTES DE EMISSÃO SAÍDAA     
            SELECT 'PENDENTE' AS STATUS,   
                   'SAIDA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM V_EST_NF_NFE_PENDENTE_EMISSAO  
            WHERE TIPO_NF = 1  
                  AND PEND_SOLUC = 0;  
     INSERT INTO #MOVIMENTACAO_NF                     
            --NOTAS PENDENTES DE EMISSÃO ENTRADA                                          
            SELECT 'COM_ERRO' AS STATUS,   
                   'SAIDA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM V_EST_NF_NFE_PENDENTE_EMISSAO  
            WHERE TIPO_NF IN(1, 2)  
            AND PEND_SOLUC = 1;  
     --ENTRADAS                                                
     INSERT INTO #MOVIMENTACAO_NF                                       
            --NOTAS VÁLIDAS ENTRADAS                            
            SELECT 'VALIDADA' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(NFE.NR_AUTORIZADOR) AS QTDE  
            FROM EST_NF_ENT NF  
                 INNER JOIN EST_NF_SERIE SERIE(NOLOCK) ON NF.CD_NF_SERIE = SERIE.CD_NF_SERIE  
                    AND SERIE.ESPC = 'NFE'  
                 INNER JOIN EST_NF_ENT_NFE NFE(NOLOCK) ON NF.CD_EMP = NFE.CD_EMP  
                                                          AND NF.CD_FILIAL = NFE.CD_FILIAL  
                                                          AND NF.CD_NF = NFE.CD_NF  
            WHERE NF.DT_ENT = CONVERT(CHAR(10), GETDATE(), 112)  
                  AND NFE.NR_PROTOCOLO IS NOT NULL  
                  AND NFE.NR_AUTORIZADOR IS NOT NULL;  
     INSERT INTO #MOVIMENTACAO_NF            
            --NOTAS NÃO VALIDAS ENTRADAS                                                       
            SELECT 'NAO_VALIDADA' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(EST_NFE_LOTE_ENT.CD_NFE_LOTE) AS QTDE  
            FROM EST_NFE_LOTE_ENT  
                 INNER JOIN EST_NF_ENT(NOLOCK) ON EST_NFE_LOTE_ENT.CD_EMP = EST_NF_ENT.CD_EMP  
                                                  AND EST_NFE_LOTE_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL  
                                                  AND EST_NFE_LOTE_ENT.CD_NF = EST_NF_ENT.CD_NF  
                 INNER JOIN EST_NFE_LOTE(NOLOCK) ON EST_NFE_LOTE_ENT.CD_NFE_LOTE = EST_NFE_LOTE.CD_NFE_LOTE  
                 INNER JOIN GLB_USU ON EST_NFE_LOTE.CD_USU = GLB_USU.CD_USU  
                 LEFT OUTER JOIN EST_NF_ENT_NFE_PEND(NOLOCK) ON EST_NFE_LOTE_ENT.CD_EMP = EST_NF_ENT_NFE_PEND.CD_EMP  
                                                                AND EST_NFE_LOTE_ENT.CD_FILIAL = EST_NF_ENT_NFE_PEND.CD_FILIAL  
                                                                AND EST_NFE_LOTE_ENT.CD_NF = EST_NF_ENT_NFE_PEND.CD_NF  
                 LEFT OUTER JOIN EST_NF_ENT_NFE(NOLOCK) ON EST_NFE_LOTE_ENT.CD_EMP = EST_NF_ENT_NFE.CD_EMP  
                                                           AND EST_NFE_LOTE_ENT.CD_FILIAL = EST_NF_ENT_NFE.CD_FILIAL  
                                                           AND EST_NFE_LOTE_ENT.CD_NF = EST_NF_ENT_NFE.CD_NF  
            WHERE EST_NFE_LOTE_ENT.CD_EMP = 1  
                  AND EST_NF_ENT.DT_EMI_NF >= CONVERT(CHAR(10), GETDATE() - 7, 112)  
                  AND EST_NF_ENT_NFE_PEND.CD_NF IS NULL  
                  AND EST_NF_ENT_NFE.CD_NF IS NULL  
                  AND EST_NF_ENT.STS_NF = 0;  
     INSERT INTO #MOVIMENTACAO_NF                                
            --PENDENTE DE EMISSÃO ENTRADA                                   
            SELECT 'PENDENTE' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM V_EST_NF_NFE_PENDENTE_EMISSAO  
            WHERE TIPO_NF = 0  
                  AND PEND_SOLUC = 0;  
     --COM ERRO DE EMISSÃO ENTRADA    
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT 'COM_ERRO' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM V_EST_NF_NFE_PENDENTE_EMISSAO  
            WHERE TIPO_NF IN(0, 3)  
            AND PEND_SOLUC = 1;  
     --XML_NAO_ENCONTRADO DE EMISSÃO ENTRADA    
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT 'XML_NAO_ENCONTRADO' AS STAUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(1) AS QUANTIDADE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_NAO_ENCONTRADO;        
     --STATUS     
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT STATUS,   
                   LOWER(TIPO) AS MOVIMENTACAO,   
                   QTDE  
            FROM  
            (  
                SELECT A_EMITIR,   
                       A_PROCESSAR,   
                       A_ENVIAR,   
                       A_RETORNAR,   
                       A_GRAVAR,   
                       TOTAL_PEND AS TOTAL_PENDENTE,   
                       TIPO  
                FROM V_NFE_STATUS  
            ) C UNPIVOT(QTDE FOR STATUS IN(A_EMITIR,   
                                           A_PROCESSAR,   
                                           A_ENVIAR,   
                                           A_RETORNAR,   
                                    A_GRAVAR,   
                                           TOTAL_PENDENTE)) AS U;  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT 'CANCELADOS' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_NFE_CANCEL  
            WHERE EST_NF_ENT_NFE_CANCEL.CD_EMP = 1  
                  AND EST_NF_ENT_NFE_CANCEL.DT_CAD >= CONVERT(CHAR(10), GETDATE(), 112);  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT 'CANCELADOS' AS STATUS,   
                   'SAIDA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_SAI_NFE_CANCEL  
            WHERE EST_NF_SAI_NFE_CANCEL.CD_EMP = 1  
                  AND EST_NF_SAI_NFE_CANCEL.DT_CAD >= CONVERT(CHAR(10), GETDATE(), 112);  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT '0-MAN-PEND' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_CONFIRMACAO_OPERACAO_PENDENTE A;  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT '2-MAN-PEND' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_DESCONHECIMENTO_OPERACAO_PENDENTE A;  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT '1-MAN-PEND' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_CIENCIA_OPERACAO_PENDENTE A;  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT '3-MAN-PEND' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_NAO_REALIZADA_OPERACAO_PENDENTE A;  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT '0-MAN' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_CONFIRMACAO_OPERACAO A;  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT '2-MAN' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_DESCONHECIMENTO_OPERACAO A;  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT '1-MAN' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_CIENCIA_OPERACAO A;  
     INSERT INTO #MOVIMENTACAO_NF  
            SELECT '3-MAN' AS STATUS,   
                   'ENTRADA' AS MOVIMENTACAO,   
                   COUNT(*) AS QTDE  
            FROM EST_NF_ENT_DANFE_PROVISORIO_NAO_REALIZADA_OPERACAO A  
            WHERE CAST(FLOOR(CAST(A.DATA_VALIDACAO AS FLOAT)) AS DATETIME) = GETDATE();  
      SELECT STATUS,   
            MOVIMENTACAO,   
            QTDE  
     FROM #MOVIMENTACAO_NF  
	 ORDER BY QTDE DESC
	 
 DROP TABLE #MOVIMENTACAO_NF;