IF OBJECT_ID('V_PDV_VD_IT_IBOPE') IS NOT NULL
BEGIN
	DROP VIEW V_PDV_VD_IT_IBOPE
END

GO

CREATE VIEW
	[dbo].[V_PDV_VD_IT_IBOPE]
WITH ENCRYPTION
AS
SELECT
    V.CD_CX
    ,V.NR_ECF
    ,V.CD_PROD
    ,CASE WHEN COALESCE(V.CGC_CPF,'') = '' THEN C.CGC_CPF ELSE V.CGC_CPF END AS CGC_CPF
    ,F.CD_FILIAL AS CD_FILIAL
    ,V.DT_VD
    ,V.QT_IT
    ,V.VLR_PMC AS VLR_PMZ
	,((V.VLR_DESC_IT + V.VLR_DESC_VERBA + V.VLR_DESC_OFERTA + V.VLR_DESC_TOT_RAT) / CASE WHEN V.VLR_PMC = 0 THEN 1 ELSE V.VLR_PMC END) * 100 AS TX_DESC
    --,V.DESC_TOTAL_IT AS TX_DESC
    ,V.TOT_IT / CASE WHEN V.QT_IT = 0 THEN 1 ELSE V.QT_IT END AS VLR_IT
    ,CASE
        WHEN V.ORIG_ORC = 0 THEN 
            'BALCAO'
        WHEN V.ORIG_ORC = 2 THEN
            'BALCAO'
        WHEN V.ORIG_ORC = 1 THEN
            'TELEVENDAS' END
    AS CANAL_VENDAS
    ,CASE WHEN F.NM_FANT LIKE 'FMLF%' THEN
        'FARMALIFE'
    ELSE
        COALESCE(B.NM_BANDEIRA, 'NAO DEFINIDO')
    END AS BANDEIRA
    ,D.DS_TP_DESCONTO
	,V.VLR_CUSTO_MEDIO
	,((V.TOT_IT - (V.QT_IT * V.VLR_CUSTO_MEDIO)) / CASE WHEN V.TOT_IT = 0 THEN 1 ELSE V.TOT_IT END) * 100 AS MARGEM
	,V.TOT_IT - (V.QT_IT * V.VLR_CUSTO_MEDIO) AS VLR_MARGEM
FROM
    V_PDV_VD_IT V
    --INNER JOIN PDV_VD_IT IT ON 
    --    V.CD_EMP = V.CD_EMP 
    --    AND V.CD_FILIAL = V.CD_FILIAL 
    --    AND V.CD_VD = V.CD_VD
    --    --AND V.DT_VD = V.DT_VD
    INNER JOIN PRC_FILIAL F ON 
        V.CD_EMP = F.CD_EMP 
        AND V.CD_FILIAL = F.CD_FILIAL 
    INNER JOIN RC_CLI C ON 
        V.CD_EMP = C.CD_EMP 
        AND V.CD_CLI = C.CD_CLI
    LEFT  JOIN GLB_TP_DESCONTO D ON 
        V.TIPO_DESCONTO = D.CD_TP_DESCONTO
    INNER JOIN PRC_BANDEIRA_PRC_FILIAL BF ON 
        V.CD_EMP = BF.CD_EMP
        AND V.CD_FILIAL = BF.CD_FILIAL
    LEFT  JOIN PRC_BANDEIRA B ON 
        BF.CD_BANDEIRA   = B.CD_BANDEIRA
    LEFT JOIN CAD_UNICO_DEPARAS DP ON
        F.CD_FILIAL = DP.CD_CTR_PK
        AND TABELA = 'PRC_FILIAL'
        AND BF.CD_BANDEIRA = DP.BANDEIRA


GO
