/****** Object:  View [dbo].[V_EST_PROD_TRANSF_STATUS]    Script Date: 02/24/2014 14:58:00 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_TRANSF_STATUS]'))
DROP VIEW [dbo].[V_EST_PROD_TRANSF_STATUS]
GO
--vERSAO 2762
--SELECT * FROM V_EST_PROD_TRANSF_STATUS
CREATE VIEW [dbo].[V_EST_PROD_TRANSF_STATUS] AS
SELECT
	SUM(QTDE_RESSUP) AS QTDE_RESSUP,
	SUM(QTDE_PENDENTE_DIA) AS QTDE_PENDENTE,
	SUM(QTDE_PENDENTE_DIA_OUTROS) AS QTDE_PENDENTE_DIA_OUTROS,
	SUM(QTDE_CHECKOUT) AS QTDE_CHECKOUT,
	SUM(QTDE_PRE_FATURA) AS QTDE_PRE_FATURA,
	SUM(QTDE_FATURADO) AS QTDE_FATURADO,
	SUM(QTDE_CANCEL) AS QTDE_CANCEL,
	SUM(QTDE_CHECKOUT_PEND) AS QTDE_CHECKOUT_PEND,
	SUM(QTDE_SLD_PEND) AS QTDE_SLD_PEND,
	SUM(QTDE_ERROS) AS QTDE_ERROS,
	SUM(QT_SOL) AS QT_SOL,
	SUM(QT_AT) AS QT_AT,
	CASE WHEN SUM(QT_SOL) > 0 THEN
		(SUM(QT_AT)/SUM(QT_SOL)) * 100 
	ELSE
		0
	END AS PERC_EFICIENCIA
FROM
(SELECT
	COUNT(EST_PROD_TRANSF.CD_CTR) AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 		
FROM
	EST_PROD_TRANSF 
WHERE
	 EST_PROD_TRANSF.DT_TRANSF = CONVERT(CHAR(10),GETDATE(),112)
UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	COUNT(EST_PROD_TRANSF.CD_CTR) AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_PENDENCIA ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR  
WHERE
	EST_PROD_TRANSF.STS_TRANSF  = 0
	AND EST_PROD_TRANSF.DT_TRANSF = CONVERT(CHAR(10),GETDATE(),112)

UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	COUNT(EST_PROD_TRANSF.CD_CTR) AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_PENDENCIA ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR  
WHERE
	EST_PROD_TRANSF.STS_TRANSF  = 0
	AND EST_PROD_TRANSF.DT_TRANSF <> CONVERT(CHAR(10),GETDATE(),112)
UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	COUNT(EST_PROD_TRANSF.CD_CTR) AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_PENDENCIA ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR  
WHERE
	EST_PROD_TRANSF.STS_TRANSF  = 3
UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	COUNT(EST_PROD_TRANSF.CD_CTR)  AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF
WHERE
	EST_PROD_TRANSF.STS_TRANSF  = 1
	AND EST_PROD_TRANSF.DT_TRANSF = CONVERT(CHAR(10),GETDATE(),112)
UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	COUNT(EST_PROD_TRANSF.CD_CTR)  AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF 
WHERE
	EST_PROD_TRANSF.STS_TRANSF  = 2
	AND EST_PROD_TRANSF.DT_TRANSF = CONVERT(CHAR(10),GETDATE(),112)
UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	COUNT(EST_PROD_TRANSF.CD_CTR) AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF 
WHERE
	EST_PROD_TRANSF.STS_TRANSF  = 4
	AND EST_PROD_TRANSF.DT_TRANSF = CONVERT(CHAR(10),GETDATE(),112)
UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	COUNT(EST_PROD_TRANSF.CD_CTR) AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF 
WHERE
	EST_PROD_TRANSF.STS_TRANSF  = 5
	AND EST_PROD_TRANSF.DT_TRANSF = CONVERT(CHAR(10),GETDATE(),112)
UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	COUNT(EST_PROD_TRANSF.CD_CTR) AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_PENDENCIA ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR  
WHERE
	EST_PROD_TRANSF.STS_TRANSF  = 6
UNION ALL
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	COUNT(EST_PROD_TRANSF.CD_CTR) AS QTDE_ERROS,
	0 AS QT_SOL,
	0 AS QT_AT 	
FROM
	EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_ERROS_VALIDACAO  ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_ERROS_VALIDACAO.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_ERROS_VALIDACAO.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_ERROS_VALIDACAO.CD_CTR 
	INNER JOIN EST_PROD_TRANSF_PENDENCIA ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR
UNION ALL 	
SELECT
	0 AS QTDE_RESSUP,
	0 AS QTDE_PENDENTE_DIA,
	0 AS QTDE_PENDENTE_DIA_OUTROS,
	0 AS QTDE_CHECKOUT,
	0 AS QTDE_PRE_FATURA,
	0 AS QTDE_FATURADO,
	0 AS QTDE_CANCEL,
	0 AS QTDE_CHECKOUT_PEND,
	0 AS QTDE_SLD_PEND,
	0 AS QTDE_ERROS,
	SUM(EST_PROD_TRANSF_IT.QT_SOL) AS QT_SOL,
	SUM(CASE WHEN EST_PROD_TRANSF.STS_TRANSF IN (0,4) THEN 0 ELSE EST_PROD_TRANSF_IT.QT_AT END ) AS QT_AT 
FROM
	EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_IT ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_IT.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_IT.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_IT.CD_CTR 
WHERE
	 EST_PROD_TRANSF.DT_TRANSF = CONVERT(CHAR(10),GETDATE(),112)) rs