 IF EXISTS (SELECT * FROM SYS.VIEWS	WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_GLB_PIX_BENNER]'))
	DROP VIEW [DBO].[V_GLB_PIX_BENNER]

GO
--=======================================================================================
--Atualizada por..........: Juan Mello
--Data Alteração .........: 30-06-2023
--Alteração...............: Acrescentado campo + ISNULL(SUM(EST_NF_SAI_IT.VLR_FECOP_ST),0) ao somatório do VLR_CONTAB e VLR_OUTRAS_DESPESAS 
--=======================================================================================

CREATE VIEW [DBO].[V_GLB_PIX_BENNER] AS
SELECT
    --top 1
    PVT.CD_CTR AS CODIGOREGISTRO 
    , CONCAT(PV.CD_EMP,'-', PV.CD_FILIAL,'-',PVT.CD_CTR,'-MEDICAMENTO') AS IDENTIFICADOREXTERNO 
    , IBD.CD_CNT AS TIPOCONTA 
    , 1 AS CODIGOBANCO 
    , 0 AS IDCONTABANCARIA 
    , IBD.AGENCIA AS NUMEROAGENCIA 
    , IBD.NUMERO AS NUMEROCONTA 
    , IBD.DV AS DIGITOVERIFICADOR 
    , FORMAT( PV.DT_VD, 'yyyy-MM-ddTHH:mm:ss.mssZ' ) AS [DATA] 
    , RIGHT(REPLICATE('0', 26) + PVT.NR_AUTORIZACAO, 26) AS TXID
    , PVT.VL_CARTAO AS VALOR 
    , 0 AS TIPOMOVIMENTO 
    , CONCAT( PF.CD_FILIAL, FORMAT(PV.DT_VD, 'yyyyMMdd'), pv.CD_VD ) AS DOCUMENTO 
    , 'PIX_FM_MEDICAMENTOS' AS HISTORICO 
    , PF.CGC AS CNPJFILIAL 
    , EMP.CGC_EMP AS CNPJEMPRESA 
    , 'pix_fm_medicamentos' AS CODIGOCONFIGURACAO 
    , 'PDV_VD' AS TABELA_REGISTRO 
    , '' AS TOKEN_ID
FROM
    PDV_VD PV
     INNER JOIN PRC_EMP EMP ON 
        (PV.CD_EMP = EMP.CD_EMP)
    INNER JOIN PRC_FILIAL PF ON 
        PV.CD_EMP = PF.CD_EMP
        AND PV.CD_FILIAL = PF.CD_FILIAL
    INNER JOIN INTEGRA_BENNER_DADOSPIX IBD ON 
        IBD.CD_FILIAL = PV.CD_FILIAL
        AND PV.CD_EMP = IBD.CD_EMP
    LEFT JOIN RC_CLI CLI ON 
        PV.CD_CLI = CLI.CD_CLI
        AND PV.CD_EMP = CLI.CD_EMP
    LEFT JOIN PDV_VD_TEF PVT ON 
        PVT.CD_EMP = PV.CD_EMP
        AND PVT.CD_FILIAL = PV.CD_FILIAL
        AND PVT.CD_VD = PV.CD_VD
    INNER JOIN RC_ADM_CARTAO RAC ON 
        RAC.CD_EMP = PVT.CD_EMP
        AND PVT.CD_ADM_CARTAO = RAC.CD_ADM_CARTAO
WHERE
	RAC.TP_ADM = 7
 
UNION ALL

SELECT
    A.CD_RECTO_TEF AS CODIGOREGISTRO 
    , CONCAT(PV.CD_EMP,'-', PV.CD_FILIAL,'-',A.CD_RECTO_TEF,'-RECARGA') AS IDENTIFICADOREXTERNO 
    , IBD.CD_CNT AS TIPOCONTA 
    , 1 AS CODIGOBANCO 
    , 0 AS IDCONTABANCARIA 
    , IBD.AGENCIA AS NUMEROAGENCIA 
    , IBD.NUMERO AS NUMEROCONTA 
    , IBD.DV AS DIGITOVERIFICADOR 
    , FORMAT( PV.HR_CUPOM, 'yyyy-MM-ddTHH:mm:ss.mssZ' ) AS [DATA] 
    , RIGHT(REPLICATE('0', 26) + A.NR_AUTORIZACAO, 26) AS TXIDSS
    , A.VL_CARTAO AS VALOR 
    , 0 AS TIPOMOVIMENTO 
    , CONCAT( PF.CD_FILIAL, FORMAT(PV.HR_CUPOM, 'yyyyMMdd'), pv.CD_CTR ) AS DOCUMENTO 
    , 'Venda PIX Recarga' AS HISTORICO 
    , PF.CGC AS CNPJFILIAL 
    , EMP.CGC_EMP AS CNPJEMPRESA 
    , 'pix_fm_recarga' AS CODIGOCONFIGURACAO 
    , 'PDV_RECBTO_TEF' AS TABELA_REGISTRO 
    , '' AS TOKEN_ID
FROM
    PDV_CUPOM_NAO_FISCAL PV
    INNER JOIN PRC_EMP EMP ON 
        (PV.CD_EMP = EMP.CD_EMP)
    INNER JOIN PRC_FILIAL PF ON 
        ( PV.CD_EMP = PF.CD_EMP
            AND PV.CD_FILIAL = PF.CD_FILIAL )
    INNER JOIN INTEGRA_BENNER_DADOSPIX IBD ON 
        ( IBD.CD_FILIAL = PV.CD_FILIAL
            AND PV.CD_EMP = IBD.CD_EMP )
    INNER JOIN PDV_CUPOM_NAO_FISCAL_TEF CNFT ON 
        PV.CD_EMP = CNFT.CD_EMP
        AND PV.CD_FILIAL = CNFT.CD_FILIAL
        AND PV.CD_CTR = CNFT.CD_CTR
    INNER JOIN PDV_RECBTO_TEF A ON 
        CNFT.CD_EMP = A.CD_EMP
        AND CNFT.CD_FILIAL = A.CD_FILIAL
        AND CNFT.CD_RECTO_TEF = A.CD_RECTO_TEF
        AND A.VL_CARTAO > 0
    INNER JOIN RC_ADM_CARTAO RAC ON 
        RAC.CD_EMP = A.CD_EMP 
        AND A.CD_ADM_CARTAO = RAC.CD_ADM_CARTAO
   