IF OBJECT_ID ('V_EST_PROD_TRANSF_IT_RESSUP_CUBO') IS NOT NULL
BEGIN 
    DROP VIEW V_EST_PROD_TRANSF_IT_RESSUP_CUBO
END
GO
CREATE VIEW V_EST_PROD_TRANSF_IT_RESSUP_CUBO AS

SELECT it.CD_FILIAL,
       it.CD_FIL_DEST,
       it.DT_TRANSF,
       it.CD_CTR,
       it.CD_PROD,
       EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA,
       it.DS_PROD,
       EST_ARV_MERC_LINHA.DS_ARV_MERC_LINHA,
       it.QT_SOL,
       it.QT_UNI_RESSUPRIMENTO,
       CASE
           WHEN it.STS_TRANSF IN(0, 4)
           THEN 0
           ELSE it.QT_AT
       END AS QT_AT,
       CASE
           WHEN(CASE
                    WHEN it.STS_TRANSF IN(0, 4)
                    THEN 0
                    ELSE it.QT_AT
                END) >= it.QT_SOL
               AND it.QT_SOL > 0
           THEN 1
           ELSE 0
       END PTOS,
       CASE
           WHEN(CASE
                    WHEN it.STS_TRANSF IN(0, 4)
                    THEN 0
                    ELSE it.QT_AT
                END) >= it.QT_SOL
               AND it.QT_SOL > 0
           THEN it.QT_SOL
           ELSE CASE
                    WHEN it.STS_TRANSF IN(0, 4)
                    THEN 0
                    WHEN it.QT_SOL = 0
                    THEN 0
                    ELSE it.QT_AT
                END
       END QT_ATEND_NECES,
       1 AS QTDE_PROD,
       CONVERT(VARCHAR(10), ISNULL(REPLICATE('0', 2-LEN(ISNULL(cMatriz.PRAT, 0))), 0))+CONVERT(VARCHAR(3), ISNULL(cMatriz.PRAT, 0))+'.'+CONVERT(VARCHAR(10), ISNULL(REPLICATE('0', 3-LEN(ISNULL(cMatriz.GAV, 0))), 0))+CONVERT(VARCHAR(3), ISNULL(cMatriz.GAV, 0)) AS ENDERECO,
       CASE
           WHEN it.COMPRA_POR_SUPRIMENTO = 0
           THEN 'RESSUPRIMENTO'
           WHEN it.COMPRA_POR_SUPRIMENTO = 1
           THEN 'SUPRIMENTO'
           WHEN it.COMPRA_POR_SUPRIMENTO = 2
           THEN 'SUP-RESSUP'
       END AS TIPO_PRODUTO,
       (it.QT_SOL * it.VLR_CUSTO) AS VLR_SOL,
       CASE
           WHEN it.STS_TRANSF IN(0, 4)
           THEN 0
           ELSE(it.QT_AT * it.VLR_CUSTO)
       END AS VLR_AT,
       CASE
           WHEN it.STS_TRANSF = 0
           THEN CASE
                    WHEN F.CD_EMP IS NULL
                    THEN 'PENDENTE'
                    ELSE 'CONFIRMADO(CHECKOUT)'
                END
           WHEN it.STS_TRANSF = 1
           THEN 'PRÉ-FATURA'
           WHEN it.STS_TRANSF = 2
           THEN 'FATURADO'
           WHEN it.STS_TRANSF = 3
           THEN 'CONFIRMADO(CHECKOUT)'
           WHEN it.STS_TRANSF = 4
           THEN 'CANCELADO'
           WHEN it.STS_TRANSF = 5
           THEN 'CHECKOUT INICIADO'
           WHEN it.STS_TRANSF = 6
           THEN 'SALDO PENDENTE'
           ELSE 'INDEFINIDO'
       END AS STATUS,
       CASE
           WHEN it.QT_UNI_RESSUPRIMENTO > 1
           THEN ROUND((it.QT_SOL / it.QT_UNI_RESSUPRIMENTO), 0)
           ELSE 0
       END AS QT_CAIXA_FECHADA,
       CASE
           WHEN it.QT_UNI_RESSUPRIMENTO > 1
           THEN 'CAIXA FECHADA'
           ELSE 'UNIDADE'
       END AS UNID_RESSUP,
       cfilial.MEDIAF,
       ROUND(((cfilial.MEDIAF / 30) * cfilial.EMAX), 0) AS EMAX,
       ROUND(((cfilial.MEDIAF / 30) * cfilial.ESEG), 0) AS ESEG,
       cfilial.QTDE_FINANCIADO,
       cfilial.QTDE_LIMITADOR,
       cfilial.QTDE_FACEAMENTO,
       cfilial.QT_EST,
       (cMatriz.QT_EST + cMatriz.QT_EST_EM_TERC) AS QT_EST_MTZ,
       EST_PROD_CURVA_SIGLA.DS_SIGLA,
       CASE
           WHEN it.TP_TRANSF = 0
           THEN 'RESSUPRIMENTO AUTOMÁTICO'
           WHEN it.TP_TRANSF = 1
           THEN 'SOLICITAÇÃO (ONLINE)'
           WHEN it.TP_TRANSF = 2
           THEN 'GRUPO ESPECIAL POR ESTOQUE PADRÃO'
           WHEN it.TP_TRANSF = 3
           THEN 'SIMPLES REMESSA OPERADOR LOGISTICO'
           WHEN it.TP_TRANSF = 4
           THEN 'DESOVA\CROSS-DOCKING'
           ELSE 'NÃO DEFINIDO'
       END AS ORIGEM_RESSUP,
       it.DT_ULT_ATU,
       GLB_USU.NM_USU,
       CASE
           WHEN EST_PROD_TRANSF_EST_WMS_SAI.CD_CTR IS NULL
           THEN '0'
           ELSE EST_PROD_TRANSF_EST_WMS_SAI.CD_PED_WMS
       END AS PEDIDO_WMS,
       it.QT_EST_DEPO_RES,
       ISNULL(it.QT_NECESSIDADE, 0) AS QT_NECESSIDADE
FROM V_EST_PROD_TRANSF_IT it
     INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON it.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP
                                                  AND it.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD
     INNER JOIN EST_ARV_MERC_LINHA ON EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP
                                      AND EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA
     INNER JOIN EST_PROD_CPL cfilial ON it.CD_EMP = cfilial.CD_EMP
                                        AND it.CD_PROD = cfilial.CD_PROD
                                        AND it.CD_FIL_DEST = cfilial.CD_FILIAL
     INNER JOIN EST_PROD_CURVA_SIGLA ON cfilial.CD_EMP = EST_PROD_CURVA_SIGLA.CD_EMP
                                        AND cfilial.CD_SIGLA = EST_PROD_CURVA_SIGLA.CD_SIGLA
     INNER JOIN EST_PROD_CPL cMatriz ON it.CD_EMP = cMatriz.CD_EMP
                                        AND it.CD_PROD = cMatriz.CD_PROD
                                        AND it.CD_FILIAL = cMatriz.CD_FILIAL
     INNER JOIN GLB_USU ON it.CD_USU = GLB_USU.CD_USU
     LEFT OUTER JOIN EST_PROD_TRANSF_EST_WMS_SAI ON EST_PROD_TRANSF_EST_WMS_SAI.CD_EMP = it.CD_EMP
                                                    AND EST_PROD_TRANSF_EST_WMS_SAI.CD_FILIAL = it.CD_FILIAL
                                                    AND EST_PROD_TRANSF_EST_WMS_SAI.CD_CTR = it.CD_CTR
     LEFT JOIN
(
    SELECT DISTINCT
           CD_EMP,
           CD_FILIAL,
           CD_CTR
    FROM EST_PROD_TRANSF_CONF_FASES
) F ON IT.CD_EMP = F.CD_EMP
       AND IT.CD_FILIAL = F.CD_FILIAL
       AND IT.CD_CTR = F.CD_CTR
WHERE it.CD_EMP = 1;