/****** Object:  View [dbo].[V_EST_NF_CONTROLE_NUMERACAO]    Script Date: 02/12/2014 12:04:37 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_CONTROLE_NUMERACAO]'))
DROP VIEW [dbo].[V_EST_NF_CONTROLE_NUMERACAO]
GO
GO
--SELECT * FROM V_EST_NF_CONTROLE_NUMERACAO WHERE CD_EMP =1 AND CD_FILIAL=17 AND CD_NF =31118
CREATE VIEW V_EST_NF_CONTROLE_NUMERACAO AS 
SELECT
	V_EST_NF_SAI_CAB.CD_EMP,
	V_EST_NF_SAI_CAB.CD_FILIAL,
	V_EST_NF_SAI_CAB.CD_NF,
	V_EST_NF_SAI_CAB.NF_NF AS NR_NF,
	V_EST_NF_SAI_CAB.DT_EMI_NF,
	V_EST_NF_SAI_CAB.CD_NF_SERIE,
	V_EST_NF_SAI_CAB.SERIE,
	V_EST_NF_SAI_CAB.NM_FANT,
	'SAIDA' AS TIPO_DOCUMENTO,
	CASE WHEN EST_NF_SAI_EST_NF_NFE_INU.CD_NF IS NULL THEN
		CASE WHEN V_EST_NF_SAI_CAB.STS_NF =0 THEN
			''
		ELSE
			'CANCELADO'
		END
	ELSE
		'INUTILIZADO'
	END AS INUTILIZADO,
	ISNULL(EST_NF_NFE_INU_VALIDADA.NR_PROTOCOLO,'') AS NR_PROTOCOLO_INU,
	GLB_USU.NM_USU,
	V_EST_NF_SAI_CAB.NR_PROTOCOLO AS NR_PROTOCOLO_VALIDACAO,
	ISNULL(EST_NF_SAI_NFE_CANCEL.NR_PROTOCOLO,'') AS NR_PROTOCOLO_CANCEL,
	V_EST_NF_SAI_CAB.STS_NF,
	V_EST_NF_SAI_CAB.BX_EST,
	0 AS ATU_EST,
	V_EST_NF_SAI_CAB.TP_ESTQ
FROM
	EF_NF_CTR_NR INNER JOIN V_EST_NF_SAI_CAB ON
	EF_NF_CTR_NR.CD_EMP = V_EST_NF_SAI_CAB.CD_EMP AND
	EF_NF_CTR_NR.CD_FILIAL = V_EST_NF_SAI_CAB.CD_FILIAL AND
	EF_NF_CTR_NR.NR_NF = V_EST_NF_SAI_CAB.NF_NF AND
	EF_NF_CTR_NR.CD_NF_SERIE = V_EST_NF_SAI_CAB.CD_NF_SERIE
	INNER JOIN EST_NF_SERIE ON
	EF_NF_CTR_NR.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
	LEFT OUTER JOIN EST_NF_SAI_EST_NF_NFE_INU ON
	V_EST_NF_SAI_CAB.CD_EMP = EST_NF_SAI_EST_NF_NFE_INU.CD_EMP AND
	V_EST_NF_SAI_CAB.CD_FILIAL = EST_NF_SAI_EST_NF_NFE_INU.CD_FILIAL AND
	V_EST_NF_SAI_CAB.CD_NF  = EST_NF_SAI_EST_NF_NFE_INU.CD_NF 
	LEFT OUTER JOIN EST_NF_NFE_INU_VALIDADA ON
	EST_NF_SAI_EST_NF_NFE_INU.CD_INU = EST_NF_NFE_INU_VALIDADA.CD_INU
	LEFT OUTER JOIN EST_NF_SAI_NFE_CANCEL ON
	V_EST_NF_SAI_CAB.CD_EMP = EST_NF_SAI_NFE_CANCEL.CD_EMP AND
	V_EST_NF_SAI_CAB.CD_FILIAL = EST_NF_SAI_NFE_CANCEL.CD_FILIAL AND
	V_EST_NF_SAI_CAB.CD_NF = EST_NF_SAI_NFE_CANCEL.CD_NF 
	INNER JOIN GLB_USU ON
	V_EST_NF_SAI_CAB.CD_USU = GLB_USU.CD_USU
UNION ALL
SELECT
	V_EST_NF_ENT_CAB.CD_EMP,
	V_EST_NF_ENT_CAB.CD_FILIAL,
	V_EST_NF_ENT_CAB.CD_NF,
	V_EST_NF_ENT_CAB.NR_NF,
	V_EST_NF_ENT_CAB.DT_EMI_NF,
	V_EST_NF_ENT_CAB.CD_NF_SERIE,
	V_EST_NF_ENT_CAB.SERIE,
	V_EST_NF_ENT_CAB.NM_FANT,
	'ENTRADA' AS TIPO_DOCUMENTO,
	CASE WHEN EST_NF_ENT_EST_NF_NFE_INU.CD_NF IS NULL THEN
		CASE WHEN V_EST_NF_ENT_CAB.STS_NF =0 THEN
			''
		ELSE
			'CANCELADO'
		END
	ELSE
		'INUTILIZADO'
	END,
	ISNULL(EST_NF_NFE_INU_VALIDADA.NR_PROTOCOLO,'') AS NR_PROTOCOLO_INU,
	GLB_USU.NM_USU,
	V_EST_NF_ENT_CAB.NR_PROTOCOLO AS NR_PROTOCOLO_VALIDACAO,
	ISNULL(EST_NF_ENT_NFE_CANCEL.NR_PROTOCOLO,'') AS NR_PROTOCOLO_CANCEL,
	V_EST_NF_ENT_CAB.STS_NF,
	0 AS BX_EST,
	V_EST_NF_ENT_CAB.ATU_EST,
	V_EST_NF_ENT_CAB.TP_ESTQ
FROM
	EF_NF_CTR_NR INNER JOIN EST_NF_ENT_EF_NF_CTR_NF ON
	EF_NF_CTR_NR.CD_EMP = EST_NF_ENT_EF_NF_CTR_NF.CD_EMP AND
	EF_NF_CTR_NR.CD_FILIAL = EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL AND
	EF_NF_CTR_NR.NR_NF  = EST_NF_ENT_EF_NF_CTR_NF.NR_NF AND
	EF_NF_CTR_NR.CD_NF_SERIE  = EST_NF_ENT_EF_NF_CTR_NF.CD_NF_SERIE
	INNER JOIN V_EST_NF_ENT_CAB ON
	EST_NF_ENT_EF_NF_CTR_NF.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP AND
	EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL = V_EST_NF_ENT_CAB.CD_FILIAL AND
	EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT = V_EST_NF_ENT_CAB.CD_NF
	INNER JOIN EST_NF_SERIE ON
	EF_NF_CTR_NR.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
	LEFT OUTER JOIN EST_NF_ENT_EST_NF_NFE_INU ON
	EST_NF_ENT_EF_NF_CTR_NF.CD_EMP = EST_NF_ENT_EST_NF_NFE_INU.CD_EMP AND
	EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL = EST_NF_ENT_EST_NF_NFE_INU.CD_FILIAL AND
	EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT  = EST_NF_ENT_EST_NF_NFE_INU.CD_NF 
	LEFT OUTER JOIN EST_NF_NFE_INU_VALIDADA ON
	EST_NF_ENT_EST_NF_NFE_INU.CD_INU = EST_NF_NFE_INU_VALIDADA.CD_INU
	INNER JOIN GLB_USU ON
	V_EST_NF_ENT_CAB.CD_USU = GLB_USU.CD_USU
	LEFT OUTER JOIN EST_NF_ENT_NFE_CANCEL ON
	V_EST_NF_ENT_CAB.CD_EMP = EST_NF_ENT_NFE_CANCEL.CD_EMP AND
	V_EST_NF_ENT_CAB.CD_FILIAL = EST_NF_ENT_NFE_CANCEL.CD_FILIAL AND
	V_EST_NF_ENT_CAB.CD_NF = EST_NF_ENT_NFE_CANCEL.CD_NF 
UNION ALL
SELECT
	EF_NF_CTR_NR.CD_EMP,
	EF_NF_CTR_NR.CD_FILIAL,
	0 AS CD_NF,
	EF_NF_CTR_NR.NR_NF,
	CONVERT(DATETIME,CONVERT(CHAR(10), EST_NF_NFE_INU.DT_CAD,120)) AS DT_EMI_NF,
	EF_NF_CTR_NR.CD_NF_SERIE,
	EST_NF_SERIE.SERIE,
	'INUTILIZACAO' AS NM_FANT,
	'INUTILIZACAO' AS TIPO_DOCUMENTO,
	'INUTILIZACAO',
	ISNULL(EST_NF_NFE_INU_VALIDADA.NR_PROTOCOLO,'') AS NR_PROTOCOLO,
	GLB_USU.NM_USU,
	'' AS NR_PROTOCOLO_VALIDACAO,
	'' AS NR_PROTOCOLO_CANCEL,
	0 STS_NF,
	0 AS BX_EST,
	0 AS ATU_EST,
	0 AS TP_ESTQ
FROM
	EF_NF_CTR_NR INNER JOIN EST_NF_NFE_INU_EF_NF_CTR_NR ON
	EF_NF_CTR_NR.CD_EMP = EST_NF_NFE_INU_EF_NF_CTR_NR.CD_EMP AND
	EF_NF_CTR_NR.CD_FILIAL = EST_NF_NFE_INU_EF_NF_CTR_NR.CD_FILIAL AND
	EF_NF_CTR_NR.NR_NF  = EST_NF_NFE_INU_EF_NF_CTR_NR.NR_NF AND
	EF_NF_CTR_NR.CD_NF_SERIE  = EST_NF_NFE_INU_EF_NF_CTR_NR.CD_NF_SERIE
	INNER JOIN EST_NF_NFE_INU ON
	EST_NF_NFE_INU_EF_NF_CTR_NR.CD_INU = EST_NF_NFE_INU.CD_INU
	LEFT OUTER JOIN EST_NF_NFE_INU_VALIDADA ON
	EST_NF_NFE_INU.CD_INU = EST_NF_NFE_INU_VALIDADA.CD_INU
	INNER JOIN EST_NF_SERIE ON
	EF_NF_CTR_NR.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
	
	LEFT OUTER JOIN EST_NF_ENT_EF_NF_CTR_NF ON
	EF_NF_CTR_NR.CD_EMP = EST_NF_ENT_EF_NF_CTR_NF.CD_EMP AND
	EF_NF_CTR_NR.CD_FILIAL = EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL AND
	EF_NF_CTR_NR.NR_NF  = EST_NF_ENT_EF_NF_CTR_NF.NR_NF AND
	EF_NF_CTR_NR.CD_NF_SERIE  = EST_NF_ENT_EF_NF_CTR_NF.CD_NF_SERIE

	LEFT OUTER JOIN EST_NF_ENT_EST_NF_NFE_INU ON
	EST_NF_ENT_EF_NF_CTR_NF.CD_EMP = EST_NF_ENT_EST_NF_NFE_INU.CD_EMP AND
	EST_NF_ENT_EF_NF_CTR_NF.CD_FILIAL = EST_NF_ENT_EST_NF_NFE_INU.CD_FILIAL AND
	EST_NF_ENT_EF_NF_CTR_NF.CD_NF_ENT  = EST_NF_ENT_EST_NF_NFE_INU.CD_NF 
	LEFT OUTER JOIN EST_NF_SAI ON
	EF_NF_CTR_NR.CD_EMP = EST_NF_SAI.CD_EMP AND
	EF_NF_CTR_NR.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
	EF_NF_CTR_NR.NR_NF = EST_NF_SAI.NF_NF AND
	EF_NF_CTR_NR.CD_NF_SERIE = EST_NF_SAI.CD_NF_SERIE
	LEFT OUTER JOIN EST_NF_SAI_EST_NF_NFE_INU ON
	EST_NF_SAI.CD_EMP = EST_NF_SAI_EST_NF_NFE_INU.CD_EMP AND
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_EST_NF_NFE_INU.CD_FILIAL AND
	EST_NF_SAI.CD_NF  = EST_NF_SAI_EST_NF_NFE_INU.CD_NF 
	INNER JOIN GLB_USU ON
	EST_NF_NFE_INU.CD_USU = GLB_USU.CD_USU
WHERE
	EST_NF_ENT_EST_NF_NFE_INU.CD_NF   IS NULL
	AND EST_NF_SAI_EST_NF_NFE_INU.CD_NF   IS NULL