
/****** OBJECT:  VIEW [DBO].[V_PDV_CUPOM_NAO_FISCAL_REC_DIVERSOS]    SCRIPT DATE: 11/18/2013 15:44:52 ******/
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

/****** OBJECT:  VIEW [DBO].[V_PDV_CUPOM_NAO_FISCAL_REC_DIVERSOS]    SCRIPT DATE: 11/18/2013 15:44:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
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

GO
