IF OBJECT_ID('V_PDV_CONFERE_VD') IS NOT NULL
BEGIN
	DROP VIEW V_PDV_CONFERE_VD
END

GO
--SELECT * FROM V_PDV_CONFERE_VD WHERE DT_VD BETWEEN '20141101' AND '20141102' AND ST_VD = 0 AND (VLR_DIF_ITEM !=0 OR VLR_DIF_REC !=0 OR VLR_DIF_LANC != 0)
--DROP VIEW V_PDV_CONFERE_VD
CREATE VIEW V_PDV_CONFERE_VD
AS
SELECT
VD.CD_EMP,
VD.CD_FILIAL,
VD.CD_VD,
VD.NR_ECF,
VD.CD_CX,
VD.DT_VD,
VD.DT_CONSD,
VD.ST_VD,
VD.VLR_DESC,
VD.VLR_DESC_ITENS,
VD.VLR_DINH,
VD.VLR_CHQS,
ISNULL(VD_CHEQUE.VLR_TOT_CHEQUE,0.00) AS VLR_TOT_CHEQUE,
VD.VLR_CARTAO,
VD.VLR_CREDIARIO,
VD.VLR_CONV,
VD.VLR_DUP,
VD.VLR_NCC,
VD.VLR_TICKETS,
ISNULL(VD_VALECOMPRA.VLR_TOT_VALECOMPRA,0.00) AS VLR_TOT_VALECOMPRA,
VD.VLR_CONV_PBM,
VD.VS_CONSOLIDADOR,
VD.VLR_LIQ_VD,
ISNULL(VD_IT.VLR_TOT_IT,0.00) AS VLR_TOT_IT,
VD.VLR_LIQ_VD		+ VD.VLR_DESC - ISNULL(VD_IT.VLR_TOT_IT,0.00) AS VLR_DIF_ITEM,
VD.VLR_CARTAO		- VD.VLR_CREDIARIO - ISNULL(VD_TEF.VLR_TOT_CARTAO,0.00) AS VLR_DIF_CARTAO,
VD.VLR_CONV			- ISNULL(VD_CONV.VLR_TOT_CONV,0.00) AS VLR_DIF_CONVENIO,             
VD.VLR_CREDIARIO	- ISNULL(VD_CREDIARIO.VLR_TOT_CREDIARIO,0.00) AS VLR_DIF_CREDIARIO,
VD.VLR_CONV_PBM		- ISNULL(VD_PBM.VLR_TOT_PBM,0.00) AS VLR_DIF_PBM,
VD.VLR_CHQS			- ISNULL(VD_CHEQUE.VLR_TOT_CHEQUE,0.00) AS VLR_DIF_CHEQUE,

--VD.VLR_NCC - ISNULL(VD_NCC.VLR_TOT_NCC,0.00) AS VLR_DIF_NCC,
0.0 AS VLR_DIF_NCC,
VD.VLR_TICKETS		- ISNULL(VD_VALECOMPRA.VLR_TOT_VALECOMPRA,0.00) AS VLR_DIF_VALECOMPRA,
VD.VLR_DINH			+ VLR_CHQS + VD.VLR_CARTAO + VLR_CONV + VLR_DUP + VLR_NCC + VLR_TICKETS + VLR_CONV_PBM 
					- VD.VLR_TROCO_CHQ AS VLR_TOT_REC,

(VD.VLR_LIQ_VD		+ VD.VLR_TROCO_CHQ )
					- ( VD.VLR_DINH + VD.VLR_CHQS + VD.VLR_CARTAO + VD.VLR_CONV 
						+ VD.VLR_DUP + VD.VLR_NCC + VD.VLR_TICKETS + VD.VLR_CONV_PBM) AS VLR_DIF_REC,

(VD.VLR_LIQ_VD		+ VD.VLR_TROCO_CHQ )
					- (	VD.VLR_DINH + ISNULL(VD_CHEQUE.VLR_TOT_CHEQUE,0.00) 
						+ ISNULL(VD_TEF.VLR_TOT_CARTAO,0.00) + ISNULL(VD_CONV.VLR_TOT_CONV,0.00) 
						+ VD.VLR_DUP + ISNULL(VD_NCC.VLR_TOT_NCC ,0.00)+ ISNULL(VD_VALECOMPRA.VLR_TOT_VALECOMPRA,0.00) 
						+ ISNULL(VD_PBM.VLR_TOT_PBM,0.00)	+ ISNULL(VD_CREDIARIO.VLR_TOT_CREDIARIO,0.00) ) AS VLR_DIF_LANC


FROM
PDV_VD VD

LEFT JOIN (
SELECT		V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD,SUM(T.VL_CARTAO) AS VLR_TOT_CARTAO
FROM		PDV_VD V		
INNER JOIN	PDV_VD_TEF T	
ON			V.CD_EMP = T.CD_EMP AND V.CD_FILIAL = T.CD_FILIAL AND V.CD_VD = T.CD_VD
INNER JOIN	RC_ADM_CARTAO A 
ON			A.CD_EMP = T.CD_EMP AND A.CD_ADM_CARTAO = T.CD_ADM_CARTAO 
WHERE		A.TP_ADM <>6
GROUP BY	V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD
)			VD_TEF 
ON			VD.CD_EMP = VD_TEF.CD_EMP AND VD.CD_FILIAL = VD_TEF.CD_FILIAL AND VD.CD_VD = VD_TEF.CD_VD AND VD.DT_VD = VD_TEF.DT_VD

LEFT JOIN (
SELECT		I.CD_EMP,I.CD_FILIAL,I.CD_VD,I.DT_VD,SUM(TOT_IT) AS VLR_TOT_IT
FROM		PDV_VD_IT I
WHERE		I.ST_IT = 1
GROUP BY	I.CD_EMP,I.CD_FILIAL,I.CD_VD,I.DT_VD
)			VD_IT
ON			VD.CD_EMP = VD_IT.CD_EMP AND VD.CD_FILIAL = VD_IT.CD_FILIAL AND VD.CD_VD = VD_IT.CD_VD AND VD.DT_VD = VD_IT.DT_VD

LEFT JOIN (
SELECT		I.CD_EMP,I.CD_FILIAL,I.CD_VD,I.DT_VD,SUM(P.VLR_REEMBOL+P.VLR_CONVENIO) AS VLR_TOT_PBM
FROM		PDV_VD_IT I
INNER JOIN	PDV_VD_IT_VD_PBM P 
ON			I.CD_EMP = P.CD_EMP AND I.CD_FILIAL = P.CD_FILIAL AND I.CD_VD = P.CD_VD AND I.CD_IT = P.CD_IT
GROUP BY	I.CD_EMP,I.CD_FILIAL,I.CD_VD,I.DT_VD
)			VD_PBM
ON			VD.CD_EMP = VD_PBM.CD_EMP AND VD.CD_FILIAL = VD_PBM.CD_FILIAL AND VD.CD_VD = VD_PBM.CD_VD AND VD.DT_VD = VD_PBM.DT_VD

LEFT JOIN (
SELECT		V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD,SUM(R.VLR_CONV) AS VLR_TOT_CONV
FROM		PDV_VD V
INNER JOIN	PDV_VD_RC_VD_CONV P
ON			V.CD_EMP = P.CD_EMP AND V.CD_FILIAL = P.CD_FILIAL AND V.CD_VD = P.CD_VD
INNER JOIN	RC_VD_CONV R 
ON			P.CD_CTR = R.CD_CTR
GROUP BY	V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD
)			VD_CONV
ON			VD.CD_EMP = VD_CONV.CD_EMP AND VD.CD_FILIAL = VD_CONV.CD_FILIAL AND VD.CD_VD = VD_CONV.CD_VD AND VD.DT_VD = VD_CONV.DT_VD

LEFT JOIN (
SELECT		V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD,SUM(R.VLR_PARC_CRED) AS VLR_TOT_CREDIARIO
FROM		PDV_VD V
INNER JOIN	RC_CREDIARIO_PARC_PDV_VD P 
ON			V.CD_EMP = P.CD_EMP AND V.CD_FILIAL = P.CD_FILIAL AND V.CD_VD = P.CD_VD
INNER		JOIN RC_CREDIARIO_PARC R 
ON			P.CD_CTR = R.CD_CTR
GROUP BY	V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD
)			VD_CREDIARIO
ON			VD.CD_EMP = VD_CREDIARIO.CD_EMP AND VD.CD_FILIAL = VD_CREDIARIO.CD_FILIAL AND VD.CD_VD = VD_CREDIARIO.CD_VD AND VD.DT_VD = VD_CREDIARIO.DT_VD

LEFT JOIN (
SELECT		V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD,SUM(P.VL_CHQ) AS VLR_TOT_CHEQUE
FROM		PDV_VD V
INNER JOIN	PDV_VD_CHQS P
ON			V.CD_EMP = P.CD_EMP AND V.CD_FILIAL = P.CD_FILIAL AND V.CD_VD = P.CD_VD
GROUP BY	V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD
)			VD_CHEQUE
ON			VD.CD_EMP = VD_CHEQUE.CD_EMP AND VD.CD_FILIAL = VD_CHEQUE.CD_FILIAL AND VD.CD_VD = VD_CHEQUE.CD_VD AND VD.DT_VD = VD_CHEQUE.DT_VD

LEFT JOIN (
SELECT		V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD,SUM(R.VLR_NCC) AS VLR_TOT_NCC
FROM		PDV_VD V
INNER JOIN	PDV_VD_DEV_NCC_PDV_VD P
ON			V.CD_EMP = P.CD_EMP AND V.CD_FILIAL = P.CD_FILIAL AND V.CD_VD = P.CD_VD
INNER JOIN	PDV_VD_DEV_NCC R ON P.CD_CTR_NCC = R.CD_CTR_NCC
GROUP	BY	V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD
)			VD_NCC
ON			VD.CD_EMP = VD_NCC.CD_EMP AND VD.CD_FILIAL = VD_NCC.CD_FILIAL AND VD.CD_VD = VD_NCC.CD_VD AND VD.DT_VD = VD_NCC.DT_VD

LEFT JOIN (
SELECT		V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD,SUM(P.VLR_VALE_CP) AS VLR_TOT_VALECOMPRA
FROM		PDV_VD V
INNER JOIN	PDV_VD_RC_CLI_FIDELI_VALE_COMPRA P
ON			V.CD_EMP = P.CD_EMP AND V.CD_FILIAL = P.CD_FILIAL AND V.CD_VD = P.CD_VD
GROUP BY	V.CD_EMP,V.CD_FILIAL,V.CD_VD,V.DT_VD
)			VD_VALECOMPRA
ON			VD.CD_EMP = VD_VALECOMPRA.CD_EMP AND VD.CD_FILIAL = VD_VALECOMPRA.CD_FILIAL AND VD.CD_VD = VD_VALECOMPRA.CD_VD




