/****** Object:  View [dbo].[V_PAINEL_NFE_EST_PROD_TRANSF_OUTROS_DIAS_ANALITICO]    Script Date: 02/24/2014 14:58:00 ******/ 
IF EXISTS (SELECT * 
           FROM   SYS.VIEWS 
           WHERE  OBJECT_ID = 
      Object_id(N'[dbo].[V_PAINEL_NFE_EST_PROD_TRANSF_OUTROS_DIAS_ANALITICO]')) 
  DROP VIEW [dbo].[V_PAINEL_NFE_EST_PROD_TRANSF_OUTROS_DIAS_ANALITICO] 

GO 

--SELECT * FROM V_PAINEL_NFE_EST_PROD_TRANSF_OUTROS_DIAS_ANALITICO 
CREATE VIEW V_PAINEL_NFE_EST_PROD_TRANSF_OUTROS_DIAS_ANALITICO 
AS 
SELECT EST_PROD_TRANSF.CD_FILIAL      AS filialOrigemId, 
       EST_PROD_TRANSF.CD_CTR         AS numero, 
       EST_PROD_TRANSF.DT_TRANSF      AS dataTransferencia, 
       EST_PROD_TRANSF.CD_FIL_DEST    AS filialDestinoId, 
       NM_FANT                        AS filialDestinoNome, 
       Sum(EST_PROD_TRANSF_IT.QT_SOL) AS quantidadeSolicitada, 
       Sum(CASE 
             WHEN EST_PROD_TRANSF.STS_TRANSF IN ( 0, 4 ) THEN 0 
             ELSE EST_PROD_TRANSF_IT.QT_AT 
           END)                       AS quantidadeAtendida, 
       CASE 
         WHEN EST_PROD_TRANSF.STS_TRANSF = 0 THEN 'PENDENTE' 
         WHEN EST_PROD_TRANSF.STS_TRANSF = 1 THEN 'PRE-FATURA' 
         WHEN EST_PROD_TRANSF.STS_TRANSF = 2 THEN 'FATURADO' 
         WHEN EST_PROD_TRANSF.STS_TRANSF = 3 THEN 'CHECKOUT FINALIZADO' 
         WHEN EST_PROD_TRANSF.STS_TRANSF = 4 THEN 'CANCELADO' 
         WHEN EST_PROD_TRANSF.STS_TRANSF = 5 THEN 'CHECKOUT INICIADO' 
         WHEN EST_PROD_TRANSF.STS_TRANSF = 6 THEN 'SALDO PENDENTE' 
         ELSE 'NAO DEFINIDO' 
       END                            AS status 
FROM   EST_PROD_TRANSF 
       INNER JOIN PRC_FILIAL 
               ON EST_PROD_TRANSF.CD_EMP = PRC_FILIAL.CD_EMP 
                  AND EST_PROD_TRANSF.CD_FIL_DEST = PRC_FILIAL.CD_FILIAL 
       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 
       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) 
GROUP  BY EST_PROD_TRANSF.CD_FILIAL, 
          EST_PROD_TRANSF.CD_CTR, 
          EST_PROD_TRANSF.CD_FIL_DEST, 
          NM_FANT, 
          EST_PROD_TRANSF.STS_TRANSF, 
          EST_PROD_TRANSF.DT_TRANSF 