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

GO

CREATE VIEW V_GLB_NFCE_BENNER AS 
SELECT
	PDV_VD_NFCE.CD_EMP AS CODIGOEMPRESA,
	PDV_VD_NFCE.CD_FILIAL AS CODIGOFILIAL,
	PDV_VD_NFCE.CD_NFCE AS CODIGOREGISTRO,
	-- <v2:dataEmissao>
	PDV_VD_NFCE.DT_ENVIO AS DATAEMISSAO,
	-- <v2:dataEntrada>
	PDV_VD_NFCE.DT_ENVIO AS DATAENTRADA,
	-- <v2:numeroDocumento>
	PDV_VD_NFCE.NR_NF AS NUMERODOCUMENTO,
	-- <v2:serieDocumento>
	PDV_VD_NFCE.SERIE AS SERIEDOCUMENTO,
	-- <v2:valorLiquido>
	PDV_VD.VLR_VD AS VALORLIQUIDO,
	-- <v2:valorNominal>
	PDV_VD.VLR_VD AS VALORNOMINAL,
	-- <v2:valorDesconto>
	PDV_VD.VLR_DESC AS VALORDESCONTO,
	-- <v2:origemEmissao>
	'Emissão Própria' as ORIGEMEMISSAO,
	-- <v2:situacaoFiscal>
	'Documento regular' AS SITUACAOFISCAL,
	-- <v2:chaveEletronica>
	PDV_VD_NFCE.CH_NFE AS CHAVEELETRONICA,
	-- <v2:numeroProtocolo>
	PDV_VD_NFCE.NR_PROT AS NUMEROPROTOCOLO,
	-- <v2:tipoDocumento>
	'FiscalNfceFarmacia' AS TIPODOCUMENTO,
	-- <v2:filial>
	PRC_FILIAL.CGC AS FILIAL,
	-- <v2:pessoa>
	RC_CLI.CGC_CPF AS PESSOA,
	-- <v2:entidade>
	'SESI' AS ENTIDADE,
	-- <v2:observacaoCodigo>
	1 AS OBSERVACAOCODIGO,
	-- <v2:observacaoComplementar>
	PDV_VD_NFCE.DS_MOTIVO as OBSERVACAOCOMPLEMENTAR,
	-- <v2:statusNfe>
	'Autorizado' AS STATUSNFE,
	-- Canceladas
	-- <v2:dataCancelamento>
	NULL AS dataCancelamento,
	-- <v2:motivoCancelamento>
	'' AS motivoCancelamento,
	-- <v2:operacaoCancelamento>
	0 AS operacaoCancelamento,
	-- <v2:integrarCancelado>
	'' AS INTEGRARCANCELADO
FROM
    PDV_VD INNER JOIN PDV_VD_PDV_VD_NFCE ON
        PDV_VD.CD_EMP = PDV_VD_PDV_VD_NFCE.CD_EMP
        AND PDV_VD.CD_FILIAL = PDV_VD_PDV_VD_NFCE.CD_FILIAL
        AND PDV_VD.CD_VD = PDV_VD_PDV_VD_NFCE.CD_VD
    INNER JOIN PDV_VD_NFCE ON
        PDV_VD_PDV_VD_NFCE.CD_EMP = PDV_VD_NFCE.CD_EMP
        AND PDV_VD_PDV_VD_NFCE.CD_FILIAL = PDV_VD_NFCE.CD_FILIAL
        AND PDV_VD_PDV_VD_NFCE.CD_NFCE = PDV_VD_NFCE.CD_NFCE
    INNER JOIN PDV_CX ON PDV_VD.CD_EMP = PDV_CX.CD_EMP
        AND PDV_VD.CD_FILIAL = PDV_CX.CD_FILIAL
        AND PDV_VD.CD_CX = PDV_CX.CD_CX
	INNER JOIN PRC_FILIAL ON
		PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
		AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
    LEFT JOIN RC_CLI
        ON RC_CLI.CD_EMP = PDV_VD.CD_EMP
        AND RC_CLI.CD_CLI = PDV_VD.CD_CLI
WHERE
    PDV_VD.ST_VD = 0
UNION ALL
SELECT
	PDV_VD_NFCE.CD_EMP AS CODIGOEMPRESA,
	PDV_VD_NFCE.CD_FILIAL AS CODIGOFILIAL,
	PDV_VD_NFCE.CD_NFCE AS CODIGOREGISTRO,
	-- <v2:dataEmissao>
	PDV_VD_NFCE.DT_ENVIO AS DATAEMISSAO,
	-- <v2:dataEntrada>
	PDV_VD_NFCE.DT_ENVIO AS DATAENTRADA,
	-- <v2:numeroDocumento>
	PDV_VD_NFCE.NR_NF AS NUMERODOCUMENTO,
	-- <v2:serieDocumento>
	PDV_VD_NFCE.SERIE AS SERIEDOCUMENTO,
	-- <v2:valorLiquido>
	PDV_VD.VLR_VD AS VALORLIQUIDO,
	-- <v2:valorNominal>
	PDV_VD.VLR_VD AS VALORNOMINAL,
	-- <v2:valorDesconto>
	PDV_VD.VLR_DESC AS VALORDESCONTO,
	-- <v2:origemEmissao>
	'Emissão Própria' as ORIGEMEMISSAO,
	-- <v2:situacaoFiscal>
	'Documento cancelado' AS SITUACAOFISCAL,
	-- <v2:chaveEletronica>
	PDV_VD_NFCE.CH_NFE AS CHAVEELETRONICA,
	-- <v2:numeroProtocolo>
	PDV_VD_NFCE.NR_PROT AS NUMEROPROTOCOLO,
	-- <v2:tipoDocumento>
	'FiscalNfceFarmacia' AS TIPODOCUMENTO,
	-- <v2:filial>
	PRC_FILIAL.CGC AS FILIAL,
	-- <v2:pessoa>
	RC_CLI.CGC_CPF AS PESSOA,
	-- <v2:entidade>
	'SESI' AS ENTIDADE,
	-- <v2:observacaoCodigo>
	1 AS OBSERVACAOCODIGO,
	-- <v2:observacaoComplementar>
	PDV_VD_NFCE.DS_MOTIVO as OBSERVACAOCOMPLEMENTAR,
	-- <v2:statusNfe>
	'Cancelado' AS STATUSNFE,
	-- Canceladas
	-- <v2:dataCancelamento>
	PDV_VD_NFCE_CANCEL.DT_RECBTO AS dataCancelamento,
	-- <v2:motivoCancelamento>
	'Documento cancelado' AS motivoCancelamento,
	-- <v2:operacaoCancelamento>
	2930 AS operacaoCancelamento,
	-- <v2:integrarCancelado>
	'SIM' AS INTEGRARCANCELADO
FROM
    PDV_VD INNER JOIN PDV_VD_PDV_VD_NFCE ON
        PDV_VD.CD_EMP = PDV_VD_PDV_VD_NFCE.CD_EMP
        AND PDV_VD.CD_FILIAL = PDV_VD_PDV_VD_NFCE.CD_FILIAL
        AND PDV_VD.CD_VD = PDV_VD_PDV_VD_NFCE.CD_VD
    INNER JOIN PDV_VD_NFCE ON
        PDV_VD_PDV_VD_NFCE.CD_EMP = PDV_VD_NFCE.CD_EMP
        AND PDV_VD_PDV_VD_NFCE.CD_FILIAL = PDV_VD_NFCE.CD_FILIAL
        AND PDV_VD_PDV_VD_NFCE.CD_NFCE = PDV_VD_NFCE.CD_NFCE
    INNER JOIN PDV_CX ON PDV_VD.CD_EMP = PDV_CX.CD_EMP
        AND PDV_VD.CD_FILIAL = PDV_CX.CD_FILIAL
        AND PDV_VD.CD_CX = PDV_CX.CD_CX
	INNER JOIN PRC_FILIAL ON
		PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
		AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
    LEFT JOIN PDV_VD_NFCE_CANCEL ON
        PDV_VD_NFCE.CD_NFCE = PDV_VD_NFCE_CANCEL.CD_NFCE
        AND PDV_VD_NFCE_CANCEL.TP_EMIS <> 1
    LEFT JOIN RC_CLI
        ON RC_CLI.CD_EMP = PDV_VD.CD_EMP
        AND RC_CLI.CD_CLI = PDV_VD.CD_CLI
WHERE
    PDV_VD.ST_VD = 1
UNION ALL 
SELECT
	PDV_VD_NFCE.CD_EMP AS CODIGOEMPRESA,
	PDV_VD_NFCE.CD_FILIAL AS CODIGOFILIAL,
    PDV_VD_NFCE.CD_NFCE AS CODIGOREGISTRO,
	-- <v2:dataEmissao>
	PDV_VD_NFCE.DT_ENVIO AS DATAEMISSAO,
	-- <v2:dataEntrada>
	PDV_VD_NFCE.DT_ENVIO AS DATAENTRADA,
	-- <v2:numeroDocumento>
	PDV_VD_NFCE.NR_NF AS NUMERODOCUMENTO,
	-- <v2:serieDocumento>
	PDV_VD_NFCE.SERIE AS SERIEDOCUMENTO,
	-- <v2:valorLiquido>
	PDV_VD.VLR_VD AS VALORLIQUIDO,
	-- <v2:valorNominal>
	PDV_VD.VLR_VD AS VALORNOMINAL,
	-- <v2:valorDesconto>
	PDV_VD.VLR_DESC AS VALORDESCONTO,
	-- <v2:origemEmissao>
	'Emissão Própria' as ORIGEMEMISSAO,
	-- <v2:situacaoFiscal>
	'NFe - Numeração inutilizada' AS SITUACAOFISCAL,
	-- <v2:chaveEletronica>
	PDV_VD_NFCE.CH_NFE AS CHAVEELETRONICA,
	-- <v2:numeroProtocolo>
	PDV_VD_NFCE.NR_PROT AS NUMEROPROTOCOLO,
	-- <v2:tipoDocumento>
	'FiscalNfceFarmacia' AS TIPODOCUMENTO,
	-- <v2:filial>
	PRC_FILIAL.CGC AS FILIAL,
	-- <v2:pessoa>
	RC_CLI.CGC_CPF AS PESSOA,
	-- <v2:entidade>
	'SESI' AS ENTIDADE,
	-- <v2:observacaoCodigo>
	1 AS OBSERVACAOCODIGO,
	-- <v2:observacaoComplementar>
	PDV_VD_NFCE.DS_MOTIVO as OBSERVACAOCOMPLEMENTAR,
	-- <v2:statusNfe>
	'Inutilizado' AS STATUSNFE,
	-- Canceladas
	-- <v2:dataCancelamento>
	PDV_VD_NFCE_CANCEL.DT_RECBTO AS dataCancelamento,
	-- <v2:motivoCancelamento>
	'Documento inutilizado' AS motivoCancelamento,
	-- <v2:operacaoCancelamento>
	2930 AS operacaoCancelamento,
	-- <v2:integrarCancelado>
	'SIM' AS INTEGRARCANCELADO
FROM
    PDV_VD INNER JOIN PDV_VD_PDV_VD_NFCE ON
        PDV_VD.CD_EMP = PDV_VD_PDV_VD_NFCE.CD_EMP
        AND PDV_VD.CD_FILIAL = PDV_VD_PDV_VD_NFCE.CD_FILIAL
        AND PDV_VD.CD_VD = PDV_VD_PDV_VD_NFCE.CD_VD
    INNER JOIN PDV_VD_NFCE ON
        PDV_VD_PDV_VD_NFCE.CD_EMP = PDV_VD_NFCE.CD_EMP
        AND PDV_VD_PDV_VD_NFCE.CD_FILIAL = PDV_VD_NFCE.CD_FILIAL
        AND PDV_VD_PDV_VD_NFCE.CD_NFCE = PDV_VD_NFCE.CD_NFCE
    INNER JOIN PDV_CX ON PDV_VD.CD_EMP = PDV_CX.CD_EMP
        AND PDV_VD.CD_FILIAL = PDV_CX.CD_FILIAL
        AND PDV_VD.CD_CX = PDV_CX.CD_CX
    INNER JOIN PDV_VD_NFCE_CANCEL ON
        PDV_VD_NFCE.CD_NFCE = PDV_VD_NFCE_CANCEL.CD_NFCE
	INNER JOIN PRC_FILIAL ON
		PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
		AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
    LEFT JOIN RC_CLI
        ON RC_CLI.CD_EMP = PDV_VD.CD_EMP
        AND RC_CLI.CD_CLI = PDV_VD.CD_CLI
WHERE
    PDV_VD_NFCE_CANCEL.TP_EMIS=1;
