IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VW_EST_PROD_IFOOD]'))
	DROP VIEW [dbo].[VW_EST_PROD_IFOOD]
GO

 /*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20221111
'Job.................:  
'Nome................: [VW_EST_PROD_IFOOD]
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 
'Dt Ult Alteracão....: 2022-11-11
'Alterado por........: Marcos Barros
'Criado por..........: 
'=======================================================================================
 */

CREATE VIEW [dbo].[VW_EST_PROD_IFOOD]  
AS
SELECT
    E.CD_FILIAL AS Id_Loja 
    , DEPTO.DS_ARV_MERC_DEPTO AS Departamento 
    , CATEG.DS_ARV_MERC_CATEG AS Categoria 
    , SUB.DS_ARV_MERC_CATEG_SUB AS Subcategoria 
    , MC.DS_MC AS Marca 
    , 'Un' AS Unidade 
    , '1Un' AS Volume 
    , EAN.EAN01 AS codigo_barra 
    , P.DS_PROD AS Nome 
    , P.DT_CAD AS dt_cadastro 
    , P.DT_ULT_ATU AS dt_ultima_alteracao 
    , PRE.VLR_TABELA AS vlr_produto 
    , CASE WHEN  PRE.VLR_OFERTA > 0 AND PRE.VLR_OFERTA < (PRE.VLR_TABELA - (TLB_DESC.PERC_DESC * PRE.VLR_TABELA / 100)) THEN
		PRE.VLR_OFERTA
	ELSE
		(PRE.VLR_TABELA - (TLB_DESC.PERC_DESC * PRE.VLR_TABELA / 100))
	END	AS vlr_promocao
    , E.QT_EST AS qtd_estoque_atual
    , ROUND(E.MEDIAF / 30 * E.ESEG, 0) AS qtd_estoque_minimo 
    , P.DS_PROD AS Descricao 
    , CASE WHEN p.STS_PROD = 0 THEN 's' ELSE 'n' 
    END AS ativo 
    , E.CD_PROD AS plu 
    , 0 AS vlr_compra 
    , 'N' AS validade_proxima 
    , 0 AS vlr_atacado 
    , 0 AS qtd_atacado 
    , '' AS image_url
FROM 
    dbo.V_EST_PROD_CPL_ESTOQUE AS E 
    INNER JOIN dbo.EST_PROD AS P ON 
        E.CD_PROD = P.CD_PROD 
    INNER JOIN dbo.EST_PROD_PRECO AS PRE ON 
        E.CD_EMP = PRE.CD_EMP 
        AND E.CD_FILIAL = PRE.CD_FILIAL 
        AND E.CD_PROD = PRE.CD_PROD 
    INNER JOIN dbo.EST_PROD_EST_ARV_MERCADOLOGICA AS A ON 
        P.CD_EMP = A.CD_EMP 
        AND P.CD_PROD = A.CD_PROD 
    INNER JOIN dbo.EST_ARV_MERC_CATEGORIA_SUB AS SUB ON 
        A.CD_EMP = SUB.CD_EMP 
        AND A.CD_ARV_MERC_CATEG_SUB = SUB.CD_ARV_MERC_CATEG_SUB 
        AND A.CD_ARV_MERC_CATEG = SUB.CD_ARV_MERC_CATEG 
    INNER JOIN dbo.EST_ARV_MERC_CATEGORIA AS CATEG ON 
        SUB.CD_EMP = CATEG.CD_EMP 
        AND SUB.CD_ARV_MERC_CATEG = CATEG.CD_ARV_MERC_CATEG 
    INNER JOIN dbo.EST_ARV_MERC_DEPTO AS DEPTO ON 
        CATEG.CD_EMP = DEPTO.CD_EMP 
        AND CATEG.CD_ARV_MERC_DEPTO = DEPTO.CD_ARV_MERC_DEPTO 
    INNER JOIN dbo.EST_MC AS MC ON 
        P.CD_EMP = MC.CD_EMP 
        AND P.CD_MC = MC.CD_MC 
    LEFT OUTER JOIN dbo.V_EST_PROD_EAN AS EAN ON 
		E.CD_EMP = EAN.CD_EMP
        AND E.CD_PROD = EAN.CD_PROD
	CROSS APPLY DBO.UFD_EST_PROD_TBL_DESC_CADASTRO_PROD (E.CD_EMP, E.CD_FILIAL, E.CD_PROD, 0, 1,0, 0) TLB_DESC
WHERE 
	E.CD_EMP = 1
    AND E.CD_FILIAL = 1
    AND P.STS_PROD IN (0, 2)
    AND E.QT_EST > 3
    AND (PRE.VLR_TABELA > 0 OR PRE.VLR_OFERTA > 0)
    AND A.CD_ARV_MERC_LINHA IN (003, 019, 010, 011, 012, 005, 007, 013, 001, 009, 014, 006, 004)
GO


