/****** Object:  View [dbo].[V_PAINEL_NFE_EST_PROD_TRANSF_PRE_FATURA_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_PRE_FATURA_ANALITICO]')) 
  DROP VIEW [dbo].[V_PAINEL_NFE_EST_PROD_TRANSF_PRE_FATURA_ANALITICO] 

GO 

--SELECT * FROM V_PAINEL_NFE_EST_PROD_TRANSF_PRE_FATURA_ANALITICO 
CREATE VIEW V_PAINEL_NFE_EST_PROD_TRANSF_PRE_FATURA_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 ,
	   EST_PROD_TRANSF_PED_VD.CD_PED  as NR_PRE_FATURA
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_PED_VD ON
	   EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PED_VD.CD_EMP AND 
	   EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PED_VD.CD_FILIAL AND
	   EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PED_VD.CD_CTR 
	    
WHERE  EST_PROD_TRANSF.DT_TRANSF = CONVERT(CHAR(10), Getdate(), 112) 
       AND EST_PROD_TRANSF.STS_TRANSF = 1 
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,
		   EST_PROD_TRANSF_PED_VD.CD_PED

