IF  ( NOT EXISTS ( SELECT OBJECT_ID FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('PDV_CUPOM_NAO_FISCAL') AND NAME LIKE 'ST_PEND_ESTORNO' ) )
BEGIN
	ALTER TABLE PDV_CUPOM_NAO_FISCAL ADD ST_PEND_ESTORNO INT NULL
END
GO

IF EXISTS ( SELECT NAME FROM SYS.OBJECTS WHERE NAME = 'V_CUPOMNAOFISCAL')
BEGIN
	DROP VIEW V_CUPOMNAOFISCAL
END
GO

CREATE VIEW V_CUPOMNAOFISCAL
AS
SELECT CUPOM.CD_EMP,CUPOM.CD_FILIAL,CUPOM.CD_CX,CUPOM.DT_CUPOM
	,CASE WHEN DATEPART(YEAR,CUPOM.HR_CUPOM) = 1900 THEN CUPOM.DT_CUPOM + CUPOM.HR_CUPOM ELSE CUPOM.HR_CUPOM END AS HR_VD
	,CUPOM.TP_CUPOM_NAO_FISCAL AS TP_CUPOM,CUPOM.CD_CTR,CUPOM.NR_COO,CUPOM.ST_CUPOM, CUPOM.CONT_CUPOM_FISC AS CONTADOR_CUPOM_FISCAL
	,FILIAL.CGC AS CGC_FILIAL,FILIAL.NM_FANT AS NM_FILIAL,CUPOM.CD_CLI,CLI.CGC_CPF AS CGC_CLI,CLI.RZ_CLI AS NM_CLI
	,CUPOM.VLR_TOT_CUPOM AS VLR_TOT_CUPOM,CUPOM.VLR_DINH,CUPOM.VLR_CARTAO,CUPOM.VLR_CHQS_A_VISTA+CUPOM.VLR_CHQS_A_PRZ AS VLR_CHQS
	,0 AS NR_OPER_CEL,0 AS NR_CEL_RECARGA,0 AS VLR_RECARGA_CEL,NULL AS NM_OPER_CEL
	,NULL AS NR_SERIE_ECF, CUPOM.VS_PDV AS VERSAO_PDV,  CUPOM.VS_PDV AS VERSAO_PDV_RC
	,OPERADOR.CD_USU AS CD_OPERADOR,OPERADOR.NM_USU AS NM_OPERADOR
	,NULLIF(COALESCE(CUPOM.VLR_DINH,0), 0) AS CONTEM_DINHEIRO
	,NULLIF(CUPOM.ST_CUPOM,0) AS IS_CANCELADO
	,CASE WHEN CUPOM.TP_CUPOM_NAO_FISCAL = 0 THEN 1 ELSE 0 END AS IS_FATURA
	,CASE WHEN CUPOM.TP_CUPOM_NAO_FISCAL = 1 THEN 1 ELSE 0 END AS IS_DOACAO
	,CASE WHEN CUPOM.TP_CUPOM_NAO_FISCAL = 2 THEN 1 ELSE 0 END AS IS_DIVERSOS
	,CASE WHEN CUPOM.TP_CUPOM_NAO_FISCAL = 3 THEN 1 ELSE 0 END AS IS_RECARGA
        ,RECEBIMENTO.CD_CTR_PGTO AS FATURA_REC_ID
	,FATURA.CD_RC_DEB AS FATURA_ID,FATURA_DEBITO.VLR_DP AS FATURA_VLR,FATURA_DEBITO.VLR_DP AS FATURA_VLR_MIN, FATURA_CREDITO.VL_PGTO AS FATURA_VLR_CRED
	,PARCELA_CREDITO.CD_CTR AS PARCELA_ID,PARCELA_DEBITO.VLR_PARC_CRED AS PARCELA_VLR,PARCELA_CREDITO.VLR_CRED AS PARCELA_VLR_CRED
        ,DIVERSOS.DT_VENCTO AS DIVERSOS_VENC, DIVERSOS.CD_RECB_PDV AS DIVERSOS_ID, DIVERSOS.IDENT_RECB_PDV AS DIVERSOS_CODIGO, CUPOM.ST_PEND_ESTORNO
FROM 	PDV_CUPOM_NAO_FISCAL CUPOM

LEFT 	JOIN PRC_FILIAL FILIAL ON CUPOM.CD_EMP = FILIAL.CD_EMP AND CUPOM.CD_FILIAL = FILIAL.CD_FILIAL

LEFT 	JOIN RC_CLI	CLI ON CUPOM.CD_EMP = CLI.CD_EMP AND CUPOM.CD_CLI = CLI.CD_CLI

LEFT    JOIN PDV_CUPOM_NAO_FISCAL_RC_PGTO_PDV RECEBIMENTO ON CUPOM.CD_EMP = RECEBIMENTO.CD_EMP AND CUPOM.CD_FILIAL = RECEBIMENTO.CD_FILIAL AND CUPOM.CD_CTR = RECEBIMENTO.CD_CTR

LEFT	JOIN RC_PGTO_PDV_RC_CRED FATURA ON RECEBIMENTO.CD_EMP = FATURA.CD_EMP AND RECEBIMENTO.CD_FILIAL = FATURA.CD_FILIAL AND RECEBIMENTO.CD_CTR_PGTO = FATURA.CD_CTR_PGTO
LEFT	JOIN RC_CRED FATURA_CREDITO ON FATURA.CD_EMP = FATURA_CREDITO.CD_EMP AND FATURA.CD_FILIAL = FATURA_CREDITO.CD_FILIAL AND FATURA.CD_RC_DEB = FATURA_CREDITO.CD_RC_DEB
LEFT	JOIN RC_DEB FATURA_DEBITO ON FATURA.CD_EMP = FATURA_DEBITO.CD_EMP AND FATURA_CREDITO.CD_FILIAL = FATURA_DEBITO.CD_FILIAL AND FATURA_CREDITO.CD_RC_DEB = FATURA_DEBITO.CD_RC_DEB

LEFT	JOIN RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED PARCELA ON RECEBIMENTO.CD_CTR_PGTO = PARCELA.CD_CTR_PGTO
LEFT	JOIN RC_CREDIARIO_PARC_CRED PARCELA_CREDITO ON PARCELA.CD_CTR = PARCELA_CREDITO.CD_CTR AND PARCELA.CD_CTR_CRED = PARCELA_CREDITO.CD_CTR_CRED
LEFT	JOIN RC_CREDIARIO_PARC PARCELA_DEBITO ON PARCELA_DEBITO.CD_CTR = PARCELA.CD_CTR

LEFT 	JOIN PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV DIVERSOS ON CUPOM.CD_EMP = DIVERSOS.CD_EMP AND CUPOM.CD_FILIAL = DIVERSOS.CD_FILIAL  AND CUPOM.CD_CTR = DIVERSOS.CD_CTR
LEFT	JOIN PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO DOACAO ON CUPOM.CD_EMP = DOACAO.CD_EMP AND CUPOM.CD_FILIAL = DOACAO.CD_FILIAL AND CUPOM.CD_CTR = DOACAO.CD_CTR
LEFT	JOIN EST_ENTID_DOACAO ENTIDADE ON DOACAO.CD_EMP = ENTIDADE.CD_EMP AND DOACAO.CD_ENTID = ENTIDADE.CD_ENTID
--LEFT 	JOIN GLB_OPER_CEL OPERCEL ON CUPOM.CD_EMP = OPERCEL.CD_EMP AND CUPOM.NR_OPER_CEL = OPERCEL.NR_OPER_CEL AND OPERCEL.NR_OPER_CEL != 0
LEFT 	JOIN GLB_USU OPERADOR ON CUPOM.CD_USU = OPERADOR.CD_USU;