/****** Object:  View [dbo].[V_PAINEL_NFE_EST_NF_SAI_CANCEL_PENDENCIA_QTDE]    Script Date: 17/11/2018 11:00:00 ******/ 

IF EXISTS (SELECT 1 FROM   SYS.VIEWS WHERE  OBJECT_ID = Object_id(N'[dbo].[V_PAINEL_NFE_EST_NF_SAI_CANCEL_PENDENCIA_QTDE]')) 
  DROP VIEW [dbo].[V_PAINEL_NFE_EST_NF_SAI_CANCEL_PENDENCIA_QTDE] 

GO
 
--SELECT * FROM  V_PAINEL_NFE_EST_NF_SAI_CANCEL_PENDENCIA_QTDE
CREATE VIEW [dbo].[V_PAINEL_NFE_EST_NF_SAI_CANCEL_PENDENCIA_QTDE]
AS

SELECT COUNT(CD_NF) AS QTDE_NF_SAI
FROM
  (SELECT EST_NF_SAI.CD_NF AS CD_NF
   FROM EST_NF_SAI
             INNER JOIN EST_NF_SAI_CANCEL              ON EST_NF_SAI_CANCEL.CD_NF     = EST_NF_SAI.CD_NF
			                                          AND EST_NF_SAI_CANCEL.CD_EMP    = EST_NF_SAI.CD_EMP 
                                                      AND EST_NF_SAI_CANCEL.CD_FILIAL = EST_NF_SAI.CD_FILIAL
                                                      AND EST_NF_SAI_CANCEL.CD_NF     = EST_NF_SAI.CD_NF 
	
             INNER JOIN EST_NF_SAI_NFE_CANCEL_PEND     ON EST_NF_SAI_NFE_CANCEL_PEND.PEND_SOLUC <> 2
			                                          AND EST_NF_SAI_NFE_CANCEL_PEND.CD_EMP     = EST_NF_SAI.CD_EMP
                                                      AND EST_NF_SAI_NFE_CANCEL_PEND.CD_FILIAL  = EST_NF_SAI.CD_FILIAL 
                                                      AND EST_NF_SAI_NFE_CANCEL_PEND.CD_NF      = EST_NF_SAI.CD_NF

             LEFT OUTER JOIN EST_NF_SAI_NFE_CANCEL_ERR ON EST_NF_SAI_NFE_CANCEL_ERR.CD_NF IS NULL
			                                          AND EST_NF_SAI_NFE_CANCEL_ERR.CD_EMP    = EST_NF_SAI.CD_EMP
                                                      AND EST_NF_SAI_NFE_CANCEL_ERR.CD_FILIAL = EST_NF_SAI.CD_FILIAL 
                                                      AND EST_NF_SAI_NFE_CANCEL_ERR.CD_NF     = EST_NF_SAI.CD_NF

             INNER JOIN NFE..EST_NF_NFE_CANCELAMENTO   ON (NFE..EST_NF_NFE_CANCELAMENTO.STS_CANCELAMENTO <> 1 AND NFE..EST_NF_NFE_CANCELAMENTO.DS_MOTIVO NOT LIKE ('%Confirmado o recebimento da NF-e pelo destinatario%') ) 
                                                      AND NFE..EST_NF_NFE_CANCELAMENTO.CD_LOTE           = EST_NF_SAI_NFE_CANCEL_ERR.CD_LOTE
                                                      AND NFE..EST_NF_NFE_CANCELAMENTO.CD_NF             = EST_NF_SAI_NFE_CANCEL_ERR.CD_NF 
                                                      AND NFE..EST_NF_NFE_CANCELAMENTO.CD_FILIAL         = EST_NF_SAI_NFE_CANCEL_ERR.CD_FILIAL, 

   
        (SELECT CD_EMP, CD_CHAVE,
		        CASE WHEN ISNULL(VALOR,0) = 0 THEN (SELECT MIN(ANO) AS ANO FROM EST_NF_NFE_INU WHERE CD_EMP = 1) ELSE (YEAR(GETDATE()) - 1) END AS ANO_INICIAL,
		        CASE WHEN ISNULL(VALOR,0) = 0 THEN (SELECT MIN(DT_EMI_NF) AS DT_EMI_NF FROM V_EST_NF_SAI_CAB WHERE CD_EMP = 1) ELSE (GETDATE() - 1) END AS DATA_INICIAL,
				YEAR(GETDATE()) AS ANO_FINAL,  
				GETDATE()  AS DATA_FINAL
		 FROM PRC_EMP_CONFIG 
		) as FLAG 

    WHERE FLAG.CD_CHAVE = 'FLAG_FLTRO_NF_SAIDA_CANCELAMENTO_INUTILIZACAO'
         AND FLAG.CD_EMP = EST_NF_SAI.CD_EMP
         AND EST_NF_SAI.DT_EMI_NF BETWEEN FLAG.DATA_INICIAL AND FLAG.DATA_FINAL 
         AND EST_NF_SAI.CD_EMP = 1   

   UNION ALL 

   SELECT EST_NF_NFE_INU.CD_INU AS CD_NF
   FROM EST_NF_NFE_INU

	         INNER JOIN PRC_FILIAL                     ON PRC_FILIAL.CD_FILIAL                            = EST_NF_NFE_INU.CD_FILIAL
		                                              AND PRC_FILIAL.CD_EMP                               = EST_NF_NFE_INU.CD_EMP 

             INNER JOIN NFE..EST_NF_NFE_INUTILIZACAO   ON (NFE..EST_NF_NFE_INUTILIZACAO.STS_INUTILIZACAO <> 1 AND NFE..EST_NF_NFE_INUTILIZACAO.DS_MOTIVO NOT LIKE ('%faixa ja foi utilizado%') ) 
		                                              AND NFE..EST_NF_NFE_INUTILIZACAO.ANO                = RIGHT(EST_NF_NFE_INU.ANO,2)
		                                              AND NFE..EST_NF_NFE_INUTILIZACAO.CNPJ               = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PRC_FILIAL.CGC, ' ', ''),'-', ''),'/', ''),'.', ''),'-', '')
		                                              AND NFE..EST_NF_NFE_INUTILIZACAO.INICIAL           >= EST_NF_NFE_INU.NR_NF 
												      AND NFE..EST_NF_NFE_INUTILIZACAO.FINAL             <= EST_NF_NFE_INU.NR_NF   

             INNER JOIN EST_NF_NFE_INU_PEND             ON EST_NF_NFE_INU_PEND.PEND_SOLUC                <> 2  
			                                           AND EST_NF_NFE_INU_PEND.CD_INU                     = EST_NF_NFE_INU.CD_INU
			                                           
	         LEFT OUTER JOIN EST_NF_SAI_EST_NF_NFE_INU  ON EST_NF_SAI_EST_NF_NFE_INU.CD_INU    = EST_NF_NFE_INU.CD_INU   
													   AND EST_NF_SAI_EST_NF_NFE_INU.CD_FILIAL = EST_NF_NFE_INU.CD_FILIAL
													   AND EST_NF_SAI_EST_NF_NFE_INU.CD_EMP    = EST_NF_NFE_INU.CD_EMP

	         INNER JOIN V_EST_NF_SAI_CAB                ON V_EST_NF_SAI_CAB.NR_PROTOCOLO IS NULL 
			                                           AND V_EST_NF_SAI_CAB.CD_NF              = EST_NF_SAI_EST_NF_NFE_INU.CD_NF
													   AND V_EST_NF_SAI_CAB.CD_FILIAL          = EST_NF_SAI_EST_NF_NFE_INU.CD_FILIAL
													   AND V_EST_NF_SAI_CAB.CD_EMP             = EST_NF_SAI_EST_NF_NFE_INU.CD_EMP,

        (SELECT CD_EMP, CD_CHAVE,
		        CASE WHEN ISNULL(VALOR,0) = 0 THEN (SELECT MIN(ANO) AS ANO FROM EST_NF_NFE_INU WHERE CD_EMP = 1) ELSE (YEAR(GETDATE()) - 1) END AS ANO_INICIAL,
		        CASE WHEN ISNULL(VALOR,0) = 0 THEN (SELECT MIN(DT_EMI_NF) AS DT_EMI_NF FROM V_EST_NF_SAI_CAB WHERE CD_EMP = 1) ELSE (GETDATE() - 1) END AS DATA_INICIAL,
				YEAR(GETDATE()) AS ANO_FINAL,  
				GETDATE()  AS DATA_FINAL
		 FROM PRC_EMP_CONFIG 
		) as FLAG 

WHERE FLAG.CD_CHAVE = 'FLAG_FLTRO_NF_SAIDA_CANCELAMENTO_INUTILIZACAO'
  AND FLAG.CD_EMP = EST_NF_NFE_INU.CD_EMP
  AND EST_NF_NFE_INU.ANO BETWEEN FLAG.ANO_INICIAL AND FLAG.ANO_FINAL 
  AND EST_NF_NFE_INU.CD_EMP = 1) AS RS