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


CREATE VIEW [dbo].[V_PDV_CUPOM_NAO_FISCAL_REC_DIVERSOS]
AS
/* 0 QUITACAO DE FATURAS E ANTECIPACAO DE COMPRAS*/ 
SELECT 
	CD_EMP, 
	CD_FILIAL, 
	RZ_CLI AS NM_RECB, 
	NR_COO, 
	CD_CX, 
	'' AS NR_CUPOM, 
	DT_CUPOM AS DT_RECB,
	VLR_PAGO AS VALOR, 
	'' AS DT_VENDA, 
	DT_VENCTO_FAT AS DT_VENCTO, 
	NR_FATURA,
	SUM(VLR_DINH) AS VLR_DINH,
	SUM(VLR_CARTAO) AS VLR_CARTAO,
	SUM(VLR_CHQS) AS VLR_CHQS, 
	0 AS CD_TIPO, 
	TP_PAGAMENTO AS TIPO,HR_CUPOM,
	SITUACAO, 
	CD_OPER
FROM         
	V_QUITACOES
WHERE
	STS_PGTO = 0
GROUP BY CD_EMP, 
		 CD_FILIAL, 
	     RZ_CLI,
		 NR_COO, 
		 CD_CX,	
		 DT_CUPOM,
		 VLR_PAGO,
		 DT_VENCTO_FAT,
		 NR_FATURA,
		 TP_PAGAMENTO,
		 HR_CUPOM,
		 SITUACAO,
		 CD_OPER
UNION ALL
/* 1 - RECEBIMENTO DE DOACAO*/
SELECT
	PDV_CUPOM_NAO_FISCAL.CD_EMP AS CD_EMP,
	PDV_CUPOM_NAO_FISCAL.CD_FILIAL AS CD_FILIAL, 
	ISNULL(RC_CLI.RZ_CLI,'CONSUMIDOR FINAL') COLLATE LATIN1_GENERAL_CI_AI AS NM_RECB, 
	PDV_CUPOM_NAO_FISCAL.NR_COO AS NR_COO, 
	PDV_CUPOM_NAO_FISCAL.CD_CX AS CD_CX, 
	'' AS NR_CUPOM, 
	PDV_CUPOM_NAO_FISCAL.DT_CUPOM AS DT_RECB, 
	PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM AS VALOR, 
	'' AS DT_VENDA, 
	'' AS DT_VENCTO, 
	'' AS NR_FATURA,
	VLR_DINH,
	VLR_CARTAO,
	VLR_CHQS, 
	1 AS CD_TIPO, 
	EST_ENTID_DOACAO.NM_ENTID COLLATE LATIN1_GENERAL_CI_AI AS TIPO, 
	HR_CUPOM,
	'' AS SITUACAO,
	PDV_CUPOM_NAO_FISCAL.CD_USU
FROM         
	PDV_CUPOM_NAO_FISCAL 
	INNER JOIN PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO ON 
		PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_EMP AND 
		PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_FILIAL AND 
		PDV_CUPOM_NAO_FISCAL.CD_CTR = PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_CTR 
	INNER JOIN EST_ENTID_DOACAO ON 
		PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_EMP = EST_ENTID_DOACAO.CD_EMP AND 
		PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_ENTID = EST_ENTID_DOACAO.CD_ENTID
	LEFT JOIN RC_CLI ON 
		RC_CLI.CD_EMP = PDV_CUPOM_NAO_FISCAL.CD_EMP 
		AND RC_CLI.CD_CLI = PDV_CUPOM_NAO_FISCAL.CD_CLI
WHERE
	ST_CUPOM NOT IN(1,2,3)
UNION ALL
/* 2 - RECEBIMENTO DE TERCEIROS/DIVERSOS*/ 
SELECT 1 AS 
	CD_EMP, 
	CD_FILIAL, 
	IDENT_RECB_PDV AS NM_RECB, 
	NR_COO, 
	CD_CX, 
	'' AS NR_CUPOM, 
	DT_CUPOM AS DT_RECB,
	VLR_TOT_CUPOM AS VALOR, 
	'' AS DT_VENDA,
	DT_VENCTO AS DT_VENCTO,
	'' AS NR_FATURA,
	VLR_DINH,
	VLR_CARTAO,
	VLR_CHQS, 
	2 AS CD_TIPO, 
	'RECEBIMENTO DE TERCEIROS' AS TIPO,
	HR_CUPOM,'' AS SITUACAO,
	CD_USU 
FROM         
	V_RC_RECTO_TERCEIROS
UNION ALL
/* 3 - RECARGA DE CELULAR*/ 
SELECT 
	PDV_CUPOM_NAO_FISCAL.CD_EMP,
	PDV_CUPOM_NAO_FISCAL.CD_FILIAL,
	ISNULL(RC_CLI.RZ_CLI, 'CLIENTE NAO IDENTIFICADO') AS NM_RECB,
	PDV_CUPOM_NAO_FISCAL.NR_COO,
	PDV_CUPOM_NAO_FISCAL.CD_CX,
	'' AS NR_CUPOM,
	PDV_CUPOM_NAO_FISCAL.DT_CUPOM AS DT_RECB, 
	PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM AS VALOR,
	'' AS DT_VENDA,
	'' AS DT_VENCTO,
	'' AS NR_FATURA,
	VLR_DINH,
	VLR_CARTAO,
	VLR_CHQS,
	3 AS CD_TIPO, 
	'RECARGA DE CELULAR (CUPOM NAO FISCAL RECEBIMENTO EM DINHEIRO / CARTAO)' AS TIPO, 
	HR_CUPOM,
	'' AS SITUACAO,
	PDV_CUPOM_NAO_FISCAL.CD_USU 
FROM         
	PDV_CUPOM_NAO_FISCAL 
	LEFT JOIN RC_CLI ON
		PDV_CUPOM_NAO_FISCAL.CD_EMP = RC_CLI.CD_EMP
		AND PDV_CUPOM_NAO_FISCAL.CD_CLI = RC_CLI.CD_CLI
WHERE     
	TP_CUPOM_NAO_FISCAL = 3 
	AND ST_CUPOM = 0
UNION ALL
/* 7 - RPS - RECIBO PROVISORIO DE SERVICO*/ 
SELECT 
	PDV_CUPOM_NAO_FISCAL.CD_EMP,
	PDV_CUPOM_NAO_FISCAL.CD_FILIAL,
	ISNULL(RC_CLI.RZ_CLI, 'CLIENTE NAO IDENTIFICADO') AS NM_RECB,
	PDV_CUPOM_NAO_FISCAL.NR_COO,
	PDV_CUPOM_NAO_FISCAL.CD_CX,
	'' AS NR_CUPOM,
	PDV_CUPOM_NAO_FISCAL.DT_CUPOM AS DT_RECB, 
	PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM AS VALOR,
	'' AS DT_VENDA,
	'' AS DT_VENCTO,
	'' AS NR_FATURA,
	VLR_DINH,
	VLR_CARTAO,
	VLR_CHQS,
	7 AS CD_TIPO,
	'RPS - RECIBO PROVISORIO DE SERVICO (RECEBIMENTO EM DINHEIRO / CARTAO)' AS TIPO, 
	HR_CUPOM,
	'' AS SITUACAO,
	PDV_CUPOM_NAO_FISCAL.CD_USU 
FROM         
	PDV_CUPOM_NAO_FISCAL 
	LEFT JOIN RC_CLI ON
		PDV_CUPOM_NAO_FISCAL.CD_EMP = RC_CLI.CD_EMP
		AND PDV_CUPOM_NAO_FISCAL.CD_CLI = RC_CLI.CD_CLI
WHERE     
	TP_CUPOM_NAO_FISCAL = 7 
	AND ST_CUPOM = 0
UNION ALL
/* 9 - VALE PRESENTE*/ 
SELECT 
	PDV_CUPOM_NAO_FISCAL.CD_EMP,
	PDV_CUPOM_NAO_FISCAL.CD_FILIAL,
	ISNULL(RC_CLI.RZ_CLI, 'CLIENTE NAO IDENTIFICADO') AS NM_RECB,
	PDV_CUPOM_NAO_FISCAL.NR_COO,
	PDV_CUPOM_NAO_FISCAL.CD_CX,
	'' AS NR_CUPOM,
	PDV_CUPOM_NAO_FISCAL.DT_CUPOM AS DT_RECB, 
	PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM AS VALOR,
	'' AS DT_VENDA,
	'' AS DT_VENCTO,
	'' AS NR_FATURA,
	VLR_DINH,
	VLR_CARTAO,
	VLR_CHQS,
	9 AS CD_TIPO,
	'VALE PRESENTE (RECEBIMENTO EM DINHEIRO / CARTAO)' AS TIPO, 
	HR_CUPOM,
	'' AS SITUACAO,
	PDV_CUPOM_NAO_FISCAL.CD_USU 
FROM         
	PDV_CUPOM_NAO_FISCAL 
	LEFT JOIN RC_CLI ON
		PDV_CUPOM_NAO_FISCAL.CD_EMP = RC_CLI.CD_EMP
		AND PDV_CUPOM_NAO_FISCAL.CD_CLI = RC_CLI.CD_CLI
WHERE     
	TP_CUPOM_NAO_FISCAL = 9
	AND ST_CUPOM = 0
	UNION ALL
/* 10 - SUPER TROCO*/ 
SELECT 
	PDV_CUPOM_NAO_FISCAL.CD_EMP,
	PDV_CUPOM_NAO_FISCAL.CD_FILIAL,
	ISNULL(RC_CLI.RZ_CLI, 'CLIENTE NAO IDENTIFICADO') AS NM_RECB,
	PDV_CUPOM_NAO_FISCAL.NR_COO,
	PDV_CUPOM_NAO_FISCAL.CD_CX,
	PDV_VD.NR_ECF AS NR_CUPOM,
	PDV_CUPOM_NAO_FISCAL.DT_CUPOM AS DT_RECB, 
	PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM AS VALOR,
	'' AS DT_VENDA,
	'' AS DT_VENCTO,
	'' AS NR_FATURA,
	PDV_CUPOM_NAO_FISCAL.VLR_DINH,
	PDV_CUPOM_NAO_FISCAL.VLR_CARTAO,
	PDV_CUPOM_NAO_FISCAL.VLR_CHQS,
	10 AS CD_TIPO,
	'SUPER TROCO (RECEBIMENTO EM DINHEIRO / CARTAO)' AS TIPO, 
	HR_CUPOM,
	'' AS SITUACAO,
	PDV_CUPOM_NAO_FISCAL.CD_USU 
FROM         
	PDV_CUPOM_NAO_FISCAL 
	LEFT JOIN RC_CLI ON
		PDV_CUPOM_NAO_FISCAL.CD_EMP = RC_CLI.CD_EMP
		AND PDV_CUPOM_NAO_FISCAL.CD_CLI = RC_CLI.CD_CLI
	INNER JOIN PDV_CUPOM_NAO_FISCAL_SUPER_TROCO ON
		PDV_CUPOM_NAO_FISCAL_SUPER_TROCO.CD_EMP = PDV_CUPOM_NAO_FISCAL.CD_EMP
		AND PDV_CUPOM_NAO_FISCAL_SUPER_TROCO.CD_FILIAL = PDV_CUPOM_NAO_FISCAL.CD_FILIAL
		AND PDV_CUPOM_NAO_FISCAL_SUPER_TROCO.CD_CTR = PDV_CUPOM_NAO_FISCAL.CD_CTR
		AND PDV_CUPOM_NAO_FISCAL_SUPER_TROCO.CD_CX = PDV_CUPOM_NAO_FISCAL.CD_CX
	INNER JOIN PDV_VD ON
		PDV_VD.CD_VD = PDV_CUPOM_NAO_FISCAL_SUPER_TROCO.CD_VD
WHERE     
	TP_CUPOM_NAO_FISCAL = 10
	AND ST_CUPOM = 0
GO
