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


CREATE VIEW [dbo].[V_GLB_PIX_BENNER] AS
SELECT
    --top 1
	  CODIGOREGISTRO 
	, CD_FILIAL 
    , IDENTIFICADOREXTERNO 
    , TIPOCONTA 
    , CODIGOBANCO 
    , IDCONTABANCARIA 
    , NUMEROAGENCIA 
    , NUMEROCONTA 
    , DIGITOVERIFICADOR 
    , FORMAT([DATA], 'yyyy-MM-ddTHH:mm:ss.mssZ' ) AS [DATA]  
    --, RIGHT(REPLICATE('0', 35) + TXID, 35)	AS TXID
	, TXID
    , SUM(VALOR) AS VALOR
    , TIPOMOVIMENTO  
	, DOCUMENTO 
    , HISTORICO 
    , CNPJFILIAL 
    , CNPJEMPRESA 
    , CODIGOCONFIGURACAO 
    , TABELA_REGISTRO 
    , TOKEN_ID
	FROM (
select 
DISTINCT
      PVT.CD_CTR            AS CODIGOREGISTRO 
    , PVT.CD_FILIAL         AS CD_FILIAL 
    , 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 
    , PV.DT_VD              AS [DATA] 
    , COALESCE(QR_LINX.ID_TRANSACTION COLLATE SQL_Latin1_General_CP1_CI_AS, 
         REPLACE(RIGHT(REPLICATE('0', 26) + PVT.NR_AUTORIZACAO, 26) COLLATE SQL_Latin1_General_CP1_CI_AS, CHAR(13), '')) 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
    INNER JOIN PDV_VD_IT ON 
        PDV_VD_IT.CD_EMP = PV.CD_EMP
        AND PDV_VD_IT.CD_FILIAL = PV.CD_FILIAL
        AND PDV_VD_IT.CD_VD = PV.CD_VD
    LEFT JOIN pdv_vd_pdv_nsu_qr_linx VINC_QRLINX ON 
        PV.CD_EMP = VINC_QRLINX.CD_EMP
        AND PV.CD_FILIAL = VINC_QRLINX.CD_FILIAL
        AND PV.CD_VD = VINC_QRLINX.CD_VD
    LEFT JOIN PDV_NSU_QR_LINX QR_LINX ON
        VINC_QRLINX.CD_CTR_ID = QR_LINX.CD_CTR_ID
WHERE
    RAC.TP_ADM = 7
    AND PDV_VD_IT.TP_PROD <> 10 

UNION ALL 

SELECT
	DISTINCT
	  PVT.CD_CTR			AS CODIGOREGISTRO 
	, PVT.CD_FILIAL			AS CD_FILIAL 
    , CONCAT(PV.CD_EMP,'-', PV.CD_FILIAL,'-',PVT.CD_CTR,'-SERVICOS') 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 
    , PV.DT_VD				AS [DATA] 
    , COALESCE(QR_LINX.ID_TRANSACTION COLLATE SQL_Latin1_General_CP1_CI_AS, 
         REPLACE(RIGHT(REPLICATE('0', 26) + PVT.NR_AUTORIZACAO, 26) COLLATE SQL_Latin1_General_CP1_CI_AS, CHAR(13), '')) 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_SERVICOS'		AS HISTORICO 
    , PF.CGC				AS CNPJFILIAL 
    , EMP.CGC_EMP			AS CNPJEMPRESA 
    , 'pix_fm_servicos' 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
	INNER JOIN PDV_VD_IT ON 
		PDV_VD_IT.CD_EMP = PV.CD_EMP
		AND PDV_VD_IT.CD_FILIAL = PV.CD_FILIAL
		AND PDV_VD_IT.CD_VD = PV.CD_VD
	LEFT JOIN pdv_vd_pdv_nsu_qr_linx VINC_QRLINX ON 
        PV.CD_EMP = VINC_QRLINX.CD_EMP
        AND PV.CD_FILIAL = VINC_QRLINX.CD_FILIAL
        AND PV.CD_VD = VINC_QRLINX.CD_VD
    LEFT JOIN PDV_NSU_QR_LINX QR_LINX ON
        VINC_QRLINX.CD_CTR_ID = QR_LINX.CD_CTR_ID
WHERE
	RAC.TP_ADM = 7
	AND PDV_VD_IT.TP_PROD = 10
UNION ALL 

-- NAO FISCAL

SELECT
	  A.CD_RECTO_TEF AS CODIGOREGISTRO 
	, PV.CD_FILIAL			AS CD_FILIAL 
    , 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 
    , PV.HR_CUPOM			AS [DATA] 
    , COALESCE(QR_LINX.ID_TRANSACTION COLLATE SQL_Latin1_General_CP1_CI_AS, 
         REPLACE(RIGHT(REPLICATE('0', 26) + A.NR_AUTORIZACAO, 26) COLLATE SQL_Latin1_General_CP1_CI_AS, CHAR(13), ''))		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
	LEFT JOIN pdv_cupom_nao_fiscal_pdv_nsu_qr_linx VINC_QRLINX ON 
        PV.CD_EMP = VINC_QRLINX.CD_EMP
        AND PV.CD_FILIAL = VINC_QRLINX.CD_FILIAL
        AND PV.CD_CTR = VINC_QRLINX.CD_CTR
	LEFT JOIN PDV_NSU_QR_LINX QR_LINX ON
        VINC_QRLINX.CD_CTR_ID = QR_LINX.CD_CTR_ID
WHERE 
	PV.TP_CUPOM_NAO_FISCAL <> 0


UNION ALL 
SELECT
	  A.CD_RECTO_TEF AS CODIGOREGISTRO 
	, PV.CD_FILIAL			AS CD_FILIAL 
    , CONCAT(PV.CD_EMP,'-', PV.CD_FILIAL,'-',A.CD_RECTO_TEF,'-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 
    , PV.HR_CUPOM			AS [DATA] 
    , COALESCE(QR_LINX.ID_TRANSACTION COLLATE SQL_Latin1_General_CP1_CI_AS, 
         REPLACE(RIGHT(REPLICATE('0', 26) + A.NR_AUTORIZACAO, 26) COLLATE SQL_Latin1_General_CP1_CI_AS, CHAR(13), ''))		AS TXIDSS
    , A.VL_CARTAO			AS VALOR 
    , 0						AS TIPOMOVIMENTO 
    , CONCAT( PF.CD_FILIAL, FORMAT(PV.HR_CUPOM, 'yyyyMMdd'), pv.CD_CTR ) AS DOCUMENTO 
    , 'PIX_FM_MEDICAMENTOS'	AS HISTORICO 
    , PF.CGC				AS CNPJFILIAL 
    , EMP.CGC_EMP			AS CNPJEMPRESA 
    , 'pix_fm_medicamentos'		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
	LEFT JOIN pdv_cupom_nao_fiscal_pdv_nsu_qr_linx VINC_QRLINX ON 
        PV.CD_EMP = VINC_QRLINX.CD_EMP
        AND PV.CD_FILIAL = VINC_QRLINX.CD_FILIAL
        AND PV.CD_CTR = VINC_QRLINX.CD_CTR
	LEFT JOIN PDV_NSU_QR_LINX QR_LINX ON
        VINC_QRLINX.CD_CTR_ID = QR_LINX.CD_CTR_ID
WHERE 
	PV.TP_CUPOM_NAO_FISCAL = 0



) AS PIX_BENNER
GROUP BY 
	  CODIGOREGISTRO 
	, CD_FILIAL 
    , IDENTIFICADOREXTERNO 
    , TIPOCONTA 
    , CODIGOBANCO 
    , IDCONTABANCARIA 
    , NUMEROAGENCIA 
    , NUMEROCONTA 
    , DIGITOVERIFICADOR 
    , [DATA] 
    , TXID
    , TIPOMOVIMENTO 
    , DOCUMENTO 
    , HISTORICO 
    , CNPJFILIAL 
    , CNPJEMPRESA 
    , CODIGOCONFIGURACAO 
    , TABELA_REGISTRO 
    , TOKEN_ID

GO


