/****** Object:  View [dbo].[V_EST_NF_NFE_PENDENTE_EMISSAO]    Script Date: 08/13/2013 09:44:51 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_NFE_PENDENTE_EMISSAO]'))
DROP VIEW [dbo].[V_EST_NF_NFE_PENDENTE_EMISSAO]
GO

/****** Object:  View [dbo].[V_EST_NF_NFE_PENDENTE_EMISSAO]    Script Date: 08/13/2013 09:44:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--SELECT * FROM V_EST_NF_NFE_PENDENTE_EMISSAO WHERE PEND_SOLUC = 0
CREATE VIEW [dbo].[V_EST_NF_NFE_PENDENTE_EMISSAO] AS   
-- 0 - ENTRADA   
-- 1 - SAIDA   
-- 2 - CANCELAMENTO   
	SELECT
		P.CD_EMP
		, P.CD_FILIAL
		, P.CD_NF
	    , 0 AS CD_CTR_CARTA_CORRECAO
		, 0 AS CD_INU
		, P.PEND_SOLUC
		, 0 AS TIPO_NF
		, 'ENTRADA' AS DS_TIPO
	FROM
		EST_NF_ENT_NFE_PEND P
	LEFT OUTER JOIN EST_NF_ENT_NFE_CONTG CONTG ON
			P.CD_EMP = CONTG.CD_EMP
		AND	P.CD_FILIAL = CONTG.CD_FILIAL
		AND P.CD_NF = CONTG.CD_NF
	INNER JOIN PRC_FILIAL F ON
			P.CD_EMP = F.CD_EMP
		AND P.CD_FILIAL = F.CD_FILIAL
		AND F.FLAG_NFE_HABILITADA = 1
	WHERE
		CONTG.CD_NF IS NULL
	UNION ALL
	
	SELECT    
		P.CD_EMP
		, P.CD_FILIAL
		, P.CD_NF
		,0 AS CD_CTR_CARTA_CORRECAO
		, 0 AS CD_INU
		, P.PEND_SOLUC
		, 1 AS TIPO_NF
		, 'SAIDA' AS DS_TIPO
	FROM
		EST_NF_SAI_NFE_PEND P
	LEFT OUTER JOIN EST_NF_SAI_NFE_CONTG CONTG ON
			P.CD_EMP = CONTG.CD_EMP
		AND P.CD_FILIAL = CONTG.CD_FILIAL
		AND P.CD_NF = CONTG.CD_NF
	INNER JOIN PRC_FILIAL F ON
			P.CD_EMP = F.CD_EMP
		AND P.CD_FILIAL = F.CD_FILIAL
		AND F.FLAG_NFE_HABILITADA = 1
	WHERE
		CONTG.CD_NF IS NULL

	UNION ALL

	SELECT    
		P.CD_EMP
		, P.CD_FILIAL
		, P.CD_NF
		,0 AS CD_CTR_CARTA_CORRECAO
		, 0 AS CD_INU
		, P.PEND_SOLUC
		, 2 AS TIPO_NF
		, 'SAIDA CANCELAMENTO' AS DS_TIPO
	FROM
		EST_NF_SAI_NFE_CANCEL_PEND P 
	INNER JOIN PRC_FILIAL F ON
			P.CD_EMP = F.CD_EMP
		AND P.CD_FILIAL = F.CD_FILIAL
		AND F.FLAG_NFE_HABILITADA = 1

	UNION ALL   

	SELECT    
		P.CD_EMP
		, P.CD_FILIAL
		, P.CD_NF
		,0 AS CD_CTR_CARTA_CORRECAO
		, 0 AS CD_INU
		, P.PEND_SOLUC
		, 3 AS TIPO_NF
		, 'ENTRADA CANCELAMENTO' AS DS_TIPO
	FROM
		EST_NF_ENT_NFE_CANCEL_PEND P
	INNER JOIN PRC_FILIAL F ON
			P.CD_EMP = F.CD_EMP
		AND P.CD_FILIAL = F.CD_FILIAL
		AND F.FLAG_NFE_HABILITADA = 1

	UNION 

	SELECT    
		CC_SAI.CD_EMP
		, CC_SAI.CD_FILIAL
		, CC_SAI.CD_NF
		, CC.CD_CTR_CARTA_CORRECAO
		, 0 AS CD_INU
		, 0 AS PEND_SOLUC
		, 4 AS TIPO_NF
		, 'CARTA CORRECAO SAIDA' AS DS_TIPO
	FROM
		EST_NFE_CARTA_CORRECAO_PEND CC
	INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_SAI CC_SAI ON 
		CC_SAI.CD_CTR_CARTA_CORRECAO = CC.CD_CTR_CARTA_CORRECAO
	INNER JOIN PRC_FILIAL F ON
			CC_SAI.CD_EMP = F.CD_EMP
		AND CC_SAI.CD_FILIAL = F.CD_FILIAL
		AND F.FLAG_NFE_HABILITADA = 1

	UNION 

	SELECT    
		CC_ENT.CD_EMP
		, CC_ENT.CD_FILIAL
		, CC_ENT.CD_NF
		,CC.CD_CTR_CARTA_CORRECAO
		, 0 AS CD_INU
		, 0 AS PEND_SOLUC
		, 5 AS TIPO_NF
		, 'CARTA CORRECAO ENTRADA' AS DS_TIPO
	FROM
		EST_NFE_CARTA_CORRECAO_PEND CC
	INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_ENT CC_ENT ON 
		CC.CD_CTR_CARTA_CORRECAO = CC_ENT.CD_CTR_CARTA_CORRECAO     
	INNER JOIN PRC_FILIAL F ON
			CC_ENT.CD_EMP = F.CD_EMP
		AND CC_ENT.CD_FILIAL = F.CD_FILIAL
		AND F.FLAG_NFE_HABILITADA = 1
	UNION    

	SELECT    
		P.CD_EMP
		, P.CD_FILIAL
		, P.CD_NF
		,0 AS CD_CTR_CARTA_CORRECAO
		, 0 AS CD_INU
		, CASE WHEN P.GERAR_ARQUIVO = 1 THEN 0 ELSE 1 END AS PEND_SOLUC
		, 7 AS TIPO_NF
		, 'MANIFESTO DESTINATARIO' AS DS_TIPO
	FROM
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_MANIFESTO_PENDENTE P
	INNER JOIN PRC_FILIAL F ON
			P.CD_EMP = F.CD_EMP
		AND P.CD_FILIAL = F.CD_FILIAL
		AND F.FLAG_NFE_HABILITADA = 1

	UNION

	SELECT
		P.CD_EMP
		, P.CD_FILIAL
		, 0 AS CD_NF
		,0 AS CD_CTR_CARTA_CORRECAO
		, P.CD_INU
		, EST_NF_NFE_INU_PEND.PEND_SOLUC
		, 8 AS TIPO_NF
		, 'INUTILIZAÇÂO' AS DS_TIPO
	FROM
		EST_NF_NFE_INU P
	INNER JOIN EST_NF_NFE_INU_PEND ON
		P.CD_INU = EST_NF_NFE_INU_PEND.CD_INU
	INNER JOIN PRC_FILIAL F ON
			P.CD_EMP = F.CD_EMP
		AND P.CD_FILIAL = F.CD_FILIAL
		AND F.FLAG_NFE_HABILITADA = 1

	UNION 

		SELECT    
			VINC.CD_EMP
			, VINC.CD_FILIAL
			, VINC.CD_NF
			,CC.CD_CTR_INF_EFET_PGTO_INTEG
			, 0 AS CD_INU
			, 0 AS PEND_SOLUC
			, 9 AS TIPO_NF
			, 'INFORMACAO DE EFETIVO PAGAMENTO INTEGRAL' AS DS_TIPO
		FROM
			EST_NFE_INF_EFET_PGTO_INTEG_PEND CC
		INNER JOIN EST_NFE_INF_EFET_PGTO_INTEG CC_ENT ON 
			CC.CD_CTR_INF_EFET_PGTO_INTEG = CC_ENT.CD_CTR_INF_EFET_PGTO_INTEG
		INNER JOIN EST_NFE_INF_EFET_PGTO_INTEG_EST_NF_ENT VINC ON VINC.CD_CTR_INF_EFET_PGTO_INTEG = CC_ENT.CD_CTR_INF_EFET_PGTO_INTEG
		INNER JOIN PRC_FILIAL F ON
				VINC.CD_EMP = F.CD_EMP
			AND VINC.CD_FILIAL = F.CD_FILIAL
			AND F.FLAG_NFE_HABILITADA = 1
