/****** Object:  View [dbo].[V_EST_PED_VD_STATUS]    Script Date: 02/24/2014 15:29:12 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PED_VD_STATUS]'))
DROP VIEW [dbo].[V_EST_PED_VD_STATUS]
GO
GO
GO
GO
--versao 2884
--SELECT * FROM V_EST_PED_VD_STATUS
CREATE VIEW [dbo].[V_EST_PED_VD_STATUS] AS
SELECT
	SUM(QTDE_PEDIDO) AS QTDE_PEDIDO,
	SUM(QTDE_PEDIDO_PENDENTE) AS QTDE_PEDIDO_PENDENTE,
	SUM(QTDE_PEDIDO_PENDENTE_OUTRO_DIA) AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA,
	SUM(QTDE_CANCEL) AS QTDE_CANCEL,
	SUM(QTDE_FATURADO) AS QTDE_FATURADO,
	SUM(QTDE_FATURADO_CANCEL) AS QTDE_FATURADO_CANCEL,
	SUM(QTDE_ERROS) AS QTDE_ERROS
FROM
(SELECT
	COUNT(EST_PED_VD.CD_PED) AS QTDE_PEDIDO,
	0 AS QTDE_PEDIDO_PENDENTE,
	0 AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA,
	0 AS QTDE_CANCEL,
	0 AS QTDE_FATURADO,
	0 AS QTDE_FATURADO_CANCEL,
	0 AS QTDE_ERROS
FROM
	EST_PED_VD  
WHERE
	EST_PED_VD.DT_EMI_PED  = CONVERT(CHAR(10),GETDATE(),112)
UNION ALL
SELECT
	0 AS QTDE_PEDIDO,
	COUNT(EST_PED_VD.CD_PED) AS QTDE_PEDIDO_PENDENTE,
	0 AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA,
	0 AS QTDE_CANCEL,
	0 AS QTDE_FATURADO,
	0 AS QTDE_FATURADO_CANCEL,
	0 AS QTDE_ERROS	
FROM
	EST_PED_VD INNER JOIN EST_PED_VD_PENDENCIA   ON
	EST_PED_VD.CD_EMP = EST_PED_VD_PENDENCIA.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_PED_VD_PENDENCIA.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_PED_VD_PENDENCIA.CD_PED 
WHERE	
EST_PED_VD.DT_EMI_PED  = CONVERT(CHAR(10),GETDATE(),112)	
UNION ALL
SELECT
	0 AS QTDE_PEDIDO,
	0 AS QTDE_PEDIDO_PENDENTE,
	COUNT(EST_PED_VD.CD_PED) AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA,
	0 AS QTDE_CANCEL,
	0 AS QTDE_FATURADO,
	0 AS QTDE_FATURADO_CANCEL,
	0 AS QTDE_ERROS	
FROM
	EST_PED_VD INNER JOIN EST_PED_VD_PENDENCIA   ON
	EST_PED_VD.CD_EMP = EST_PED_VD_PENDENCIA.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_PED_VD_PENDENCIA.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_PED_VD_PENDENCIA.CD_PED 
WHERE
	EST_PED_VD.DT_EMI_PED  <> CONVERT(CHAR(10),GETDATE(),112)
UNION ALL
SELECT
	0 AS QTDE_PEDIDO,
	0 AS QTDE_PEDIDO_PENDENTE,
	0 AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA,	
	COUNT(EST_PED_VD.CD_PED) AS QTDE_CANCEL,
	0 AS QTDE_FATURADO,
	0 AS QTDE_FATURADO_CANCEL,
	0 AS QTDE_ERROS
FROM
	EST_PED_VD  
WHERE
	EST_PED_VD.DT_EMI_PED  = CONVERT(CHAR(10),GETDATE(),112)
	AND EST_PED_VD.STS_PED in (2,3)
UNION ALL
/*
SELECT
	0 AS QTDE_PEDIDO,
	0 AS QTDE_PEDIDO_PENDENTE,
	0 AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA,	
	0 AS QTDE_CANCEL,
	COUNT(EST_PED_VD.CD_PED) AS QTDE_FATURADO,
	0 AS QTDE_FATURADO_CANCEL,
	0 AS QTDE_ERROS
FROM
	EST_PED_VD INNER JOIN EST_NF_SAI_PED_VD  ON
	EST_PED_VD.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_NF_SAI_PED_VD.CD_PED 
	INNER JOIN EST_NF_SAI  ON
	EST_NF_SAI_PED_VD.CD_EMP = EST_NF_SAI.CD_EMP AND
	EST_NF_SAI_PED_VD.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
	EST_NF_SAI_PED_VD.CD_NF = EST_NF_SAI.CD_NF 
WHERE
	EST_PED_VD.STS_PED = 1
	AND EST_PED_VD.DT_EMI_PED = CONVERT(CHAR(10),GETDATE(),112)
	AND EST_NF_SAI.DT_EMI_NF = CONVERT(CHAR(10),GETDATE(),112)
	AND EST_NF_SAI.STS_NF = 0	
*/
SELECT
	0 AS QTDE_PEDIDO,
	0 AS QTDE_PEDIDO_PENDENTE,
	0 AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA, 
	0 AS QTDE_CANCEL,
	COUNT(EST_PED_VD.CD_PED) AS QTDE_FATURADO,
	0 AS QTDE_FATURADO_CANCEL,
	0 AS QTDE_ERROS
FROM
	EST_PED_VD 
WHERE 
	EST_PED_VD.STS_PED = 1 AND 
	EST_PED_VD.DT_EMI_PED = CONVERT(CHAR(10),GETDATE(),112) AND 
	EXISTS (SELECT EST_NF_SAI_PED_VD.CD_PED FROM EST_NF_SAI_PED_VD 
				INNER JOIN EST_NF_SAI ON
				EST_NF_SAI_PED_VD.CD_EMP = EST_NF_SAI.CD_EMP AND
				EST_NF_SAI_PED_VD.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
				EST_NF_SAI_PED_VD.CD_NF = EST_NF_SAI.CD_NF AND 
				EST_NF_SAI.DT_EMI_NF = CONVERT(CHAR(10),GETDATE(),112) AND
				EST_NF_SAI.STS_NF = 0 
				WHERE EST_PED_VD.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP AND
				EST_PED_VD.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL AND
				EST_PED_VD.CD_PED = EST_NF_SAI_PED_VD.CD_PED )
	
/*
UNION ALL
SELECT
	0 AS QTDE_PEDIDO,
	0 AS QTDE_PEDIDO_PENDENTE,
	0 AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA,	
	0 AS QTDE_CANCEL,
	0 AS QTDE_FATURADO,
	--COUNT(EST_PED_VD.CD_PED) AS QTDE_FATURADO_CANCEL,
	AS QTDE_FATURADO_CANCEL,
	0 AS QTDE_ERROS
FROM
	EST_PED_VD INNER JOIN EST_NF_SAI_PED_VD  ON
	EST_PED_VD.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_NF_SAI_PED_VD.CD_PED 
	INNER JOIN EST_NF_SAI  ON
	EST_NF_SAI_PED_VD.CD_EMP = EST_NF_SAI.CD_EMP AND
	EST_NF_SAI_PED_VD.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
	EST_NF_SAI_PED_VD.CD_NF = EST_NF_SAI.CD_NF 
WHERE
	EST_PED_VD.STS_PED = 1
	AND EST_PED_VD.DT_EMI_PED = CONVERT(CHAR(10),GETDATE(),112)
	AND EST_NF_SAI.DT_EMI_NF = CONVERT(CHAR(10),GETDATE(),112)
	AND EST_NF_SAI.STS_NF = 1
*/
UNION ALL
SELECT
	0 AS QTDE_PEDIDO,
	0 AS QTDE_PEDIDO_PENDENTE,
	0 AS QTDE_PEDIDO_PENDENTE_OUTRO_DIA,	
	0 AS QTDE_CANCEL,
	0 AS QTDE_FATURADO,
	0 AS QTDE_FATURADO_CANCEL,
	COUNT(EST_PED_VD.CD_PED) AS QTDE_ERROS	
FROM
	EST_PED_VD INNER JOIN EST_PED_VD_PENDENCIA   ON
	EST_PED_VD.CD_EMP = EST_PED_VD_PENDENCIA.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_PED_VD_PENDENCIA.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_PED_VD_PENDENCIA.CD_PED 
	INNER JOIN EST_PED_VD_ERROS_VALIDACAO ON
	EST_PED_VD.CD_EMP = EST_PED_VD_ERROS_VALIDACAO.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_PED_VD_ERROS_VALIDACAO.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_PED_VD_ERROS_VALIDACAO.CD_PED ) rs

