/****** Object:  View [dbo].[V_PDV_VD_IT_TOT_IT]    Script Date: 06/05/15 06:01:17 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PDV_VD_IT_TOT_IT]'))
DROP VIEW [dbo].[V_PDV_VD_IT_TOT_IT]
GO
GO
GO
CREATE VIEW [dbo].[V_PDV_VD_IT_TOT_IT] AS
	SELECT	v.CD_EMP, 		
			v.CD_VD, 		
			v.CD_FILIAL, 
			v.DT_VD, 
			i.CD_PROD, 
			i.QT_IT, 
			i.TOT_IT - i.TOT_IT * v.VLR_DESC / SUM (d.TOT_IT) AS TOT_IT,
			ROUND(i.QT_IT * i.VLR_IT * i.TX_DESC / 100, 2) AS VLR_DESC_IT, 
			i.TOT_IT * v.VLR_DESC / SUM (d.TOT_IT) AS VLR_DESC_TOT_RAT,
			i.QT_IT * ISNULL(AVG(I.VLR_CUSTO),0) AS VLR_CUSTO_TOT, 
			i.QT_IT * ISNULL(AVG(I.VLR_CUSTO_MEDIO),0) AS VLR_CUSTO_MEDIO_TOT
	FROM PDV_VD v JOIN PDV_VD_IT i ON 
			v.CD_EMP=i.CD_EMP AND
			v.CD_FILIAL=i.CD_FILIAL AND 
			v.CD_VD=i.CD_VD AND
			v.DT_VD=i.DT_VD
		JOIN PDV_VD_IT d ON 
			v.CD_EMP=d.CD_EMP AND
			v.CD_FILIAL=d.CD_FILIAL AND 
			v.CD_VD=d.CD_VD AND
			v.DT_VD=d.DT_VD
	WHERE 
		v.ST_VD=0 AND i.ST_IT=1
	GROUP BY 
			v.CD_EMP, 		
			v.CD_VD, 		
			v.CD_FILIAL, 
			v.DT_VD, 
			v.VLR_DESC,
			i.CD_IT,
			i.CD_PROD, 
			i.QT_IT, 
			i.TOT_IT,
			i.VLR_IT,
			i.TX_DESC


GO


