/****** Object:  View [dbo].[V_RELACIONAMENTO_CHEQUE_VENDA]    Script Date: 05/30/2013 15:29:32 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_RELACIONAMENTO_CHEQUE_VENDA]'))
DROP VIEW [dbo].[V_RELACIONAMENTO_CHEQUE_VENDA]
GO
 
/*SELECT TOP 10 * FROM V_RELACIONAMENTO_CHEQUE_VENDA WHERE CD_EMP = 1 AND CD_FILIAL = 90 AND DT_VD='20150331'*/  
CREATE VIEW [dbo].[V_RELACIONAMENTO_CHEQUE_VENDA]  
AS  
SELECT
	V.CD_EMP,
	V.CD_FILIAL,
	V.CD_CX,
	V.NR_ECF,
	V.CD_CLI,
	RC_CLI.RZ_CLI,
	V.DT_VD,
	V.VLR_LIQ_VD,
	V.VLR_CHQS,
	V.VLR_TROCO_CHQ,
	 
	ISNULL(V1.NR_ECF,0) AS NR_ECF_1,
	ISNULL(V1.VLR_LIQ_VD,0) AS VLR_LIQ_VD_1,
	 	
	ISNULL(V2.NR_ECF,0) AS NR_ECF_2,
	ISNULL(V2.VLR_LIQ_VD,0) AS VLR_LIQ_VD_2,
	 
	ISNULL(V3.NR_ECF,0) AS NR_ECF_3,
	ISNULL(V3.VLR_LIQ_VD,0) AS VLR_LIQ_VD_3,
	 
	ISNULL(V4.NR_ECF,0) AS NR_ECF_4,
	ISNULL(V4.VLR_LIQ_VD,0) AS VLR_LIQ_VD_4,
	 
	ISNULL(V5.NR_ECF,0) AS NR_ECF_5,
	ISNULL(V5.VLR_LIQ_VD,0) AS VLR_LIQ_VD_5,
	 
	ISNULL(V6.NR_ECF,0) AS NR_ECF_6,
	ISNULL(V6.VLR_LIQ_VD,0) AS VLR_LIQ_VD_6

FROM
	PDV_VD V
	INNER JOIN (SELECT 
		CD_EMP,  
		CD_FILIAL, 
		CD_VD, 
		ISNULL([1],0) CD_VINC_1, 
		ISNULL([2],0) CD_VINC_2, 
		ISNULL([3],0) CD_VINC_3, 
		ISNULL([4],0) CD_VINC_4, 
		ISNULL([5],0) CD_VINC_5,
		ISNULL([6],0) CD_VINC_6
	FROM
		(SELECT 
				REL.CD_EMP,  
				REL.CD_FILIAL, 
				REL.CD_VD, 
				REL.CD_VD_RELACIONADA ,
				ROW_NUMBER() OVER(PARTITION BY REL.CD_EMP, REL.CD_FILIAL, REL.CD_VD ORDER BY REL.CD_VD) VINC 
			FROM 
				PDV_VD_CHQS_TROCO_RELACIONADA REL 
				INNER JOIN PDV_VD PDV ON 
				REL.CD_EMP                 = PDV.CD_EMP
				AND REL.CD_FILIAL          = PDV.CD_FILIAL
				AND REL.CD_VD_RELACIONADA  = PDV.CD_VD
				INNER JOIN PDV_VD_CHQS_TROCO_RELACIONADA RELB ON
				REL.CD_EMP                 = RELB.CD_EMP
				AND REL.CD_FILIAL          = RELB.CD_FILIAL
				AND REL.CD_VD_RELACIONADA  <= RELB.CD_VD_RELACIONADA 
			--WHERE  
			--	REL.CD_EMP       = 1
			--	AND REL.CD_FILIAL= 518
			--	AND PDV.DT_VD='20150331'
			GROUP BY
				REL.CD_EMP,  
				REL.CD_FILIAL, 
				REL.CD_VD, 
				REL.CD_VD_RELACIONADA
				) AS V	
			PIVOT (min( CD_VD_RELACIONADA) FOR VINC IN ([1], [2],[3],[4],[5],[6]) ) AS PIVOTTABLE) AS T ON
	
	
	V.CD_EMP =T.CD_EMP AND
	V.CD_FILIAL=T.CD_FILIAL AND
	V.CD_VD=T.CD_VD 
		
	LEFT JOIN PDV_VD V1 ON
	T.CD_EMP	=V1.CD_EMP AND
	T.CD_FILIAL =V1.CD_FILIAL AND
	T.CD_VINC_1 =V1.CD_VD

	LEFT JOIN PDV_VD V2 ON
	T.CD_EMP	=V2.CD_EMP AND
	T.CD_FILIAL	=V2.CD_FILIAL AND
	T.CD_VINC_2 =V2.CD_VD

	LEFT JOIN PDV_VD V3 ON
	T.CD_EMP =V3.CD_EMP AND
	T.CD_FILIAL=V3.CD_FILIAL AND
	T.CD_VINC_3 =V3.CD_VD

	LEFT JOIN PDV_VD V4 ON
	T.CD_EMP =V4.CD_EMP AND
	T.CD_FILIAL=V4.CD_FILIAL AND
	T.CD_VINC_4 =V4.CD_VD

	LEFT JOIN PDV_VD V5 ON
	T.CD_EMP =V5.CD_EMP AND
	T.CD_FILIAL=V5.CD_FILIAL AND
	T.CD_VINC_5=V5.CD_VD

	LEFT JOIN PDV_VD V6 ON
	T.CD_EMP =V6.CD_EMP AND
	T.CD_FILIAL=V6.CD_FILIAL AND
	T.CD_VINC_6=V6.CD_VD

	INNER JOIN RC_CLI ON
	V.CD_EMP=RC_CLI.CD_EMP AND
	V.CD_CLI=RC_CLI.CD_CLI