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

GO

CREATE VIEW
	V_PDV_PONTOS_FIDEL_IBOBPE
WITH ENCRYPTION
AS
SELECT
	M.CD_EMP,
	M.DT_BLQ_ALT_FECHA,

	P.CD_CX,
	P.NR_ECF,

    CASE WHEN LEFT(P.ID_CLI, 2) != '00' THEN
		'NULL'
    ELSE
        P.ID_CLI
    END AS CPF,

    CASE WHEN LEFT(P.ID_CLI, 2) = '00' THEN
        'NULL'
    ELSE
        P.ID_CLI 
    END	AS NR_CARTAO,

    CASE WHEN F.NM_FANT LIKE 'FMLF%' THEN
        'FARMALIFE'
    ELSE
        COALESCE(B.NM_BANDEIRA, 'NAO DEFINIDO')
    END AS BANDEIRA,
        
    F.CD_FILIAL AS CD_FILIAL,
    COALESCE(PDV_VD.DT_VD, P.DT_VD) AS DT_VD,
	P.DT_VD AS DT_VD_PONTO,
    SUM(QT_PONTOS + QT_PONTOS_ADICIONAIS) AS QT_PRONTOS,
	P.VLR_VD
FROM
    PDV_VD_FECHA_MOV M
    INNER JOIN PDV_PONTOS_FIDEL P ON
        M.CD_EMP = P.CD_EMP
        AND M.CD_FILIAL = P.CD_FILIAL
        AND M.DT_MOV = P.DT_VD
	LEFT JOIN PDV_VD ON
		P.CD_EMP = PDV_VD.CD_EMP
		AND P.CD_FILIAL = PDV_VD.CD_FILIAL
		AND P.CD_VD = PDV_VD.CD_VD
    INNER JOIN PRC_FILIAL F ON 
        P.CD_EMP = F.CD_EMP 
        AND P.CD_FILIAL = F.CD_FILIAL 
    INNER JOIN PRC_BANDEIRA_PRC_FILIAL FB ON
        P.CD_EMP = FB.CD_EMP
        AND P.CD_FILIAL = FB.CD_FILIAL
    LEFT JOIN PRC_BANDEIRA B ON
        FB.CD_BANDEIRA = B.CD_BANDEIRA
    LEFT JOIN CAD_UNICO_DEPARAS D ON
        F.CD_FILIAL = D.CD_CTR_PK
        AND TABELA = 'PRC_FILIAL'
        AND FB.CD_BANDEIRA = D.BANDEIRA
WHERE
	FB.CD_BANDEIRA = 1
GROUP BY
	M.CD_EMP,
	F.CD_FILIAL,
	M.DT_BLQ_ALT_FECHA,
    CASE WHEN LEFT(P.ID_CLI, 2) != '00' THEN
		'NULL'
    ELSE
        P.ID_CLI
    END,
    CASE WHEN LEFT(P.ID_CLI, 2) = '00' THEN
        'NULL'
    ELSE
        P.ID_CLI 
    END,
    CASE WHEN F.NM_FANT LIKE 'FMLF%' THEN
        'FARMALIFE'
    ELSE
        COALESCE(B.NM_BANDEIRA, 'NAO DEFINIDO')
    END,
    COALESCE(D.CD_CTR_FK, F.CD_FILIAL),
    COALESCE(PDV_VD.DT_VD, P.DT_VD),
	P.DT_VD,
	P.CD_CX,
	P.NR_ECF,
	P.VLR_VD
		