IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DW_MOVIMENTACAO_VENDAS]'))
DROP VIEW [dbo].[V_DW_MOVIMENTACAO_VENDAS]
GO
/*
SELECT  
	SUM(VLR_PMC),
	SUM(VLR_OFERTA), 
	SUM(TOT_IT_BRUTO),
	SUM(VLR_DESC_IT_LIQ),
	SUM(VLR_DESC_VERBA),	
	SUM(VLR_TOT),
	(SUM(VLR_MARGEM_CM)/SUM(VLR_TOT))*100,
	sum(vlr_conv)
FROM 
	V_DW_MOVIMENTACAO_VENDAS 
WHERE 
	CD_EMP =1 
	AND CD_FILIAL = 2 
	AND DT_VD BETWEEN '2015-08-20' AND '2015-08-20' 

*/

CREATE VIEW V_DW_MOVIMENTACAO_VENDAS AS
SELECT 
    V.CD_EMP,
	V.CD_FILIAL,
	V.DT_VD,
	v.NR_ECF,
	v.CD_CX,
	i.CD_IT,
    0 as CD_IMP,
	I.CD_PROD				COD_PRODUTO,
	A.CD_ARV_MERC_CATEG		COD_CATEGORIA,
	C.CD_ARV_MERC_DEPTO		COD_DEPARTAMENTO,
	P.CD_FABRIC				COD_FABRICANTE,
	I.CD_VEND				COD_FUNCIONARIO,
	V.CD_USU_OPE			COD_USUARIO,
	A.CD_ARV_MERC_LINHA		COD_LINHA,
	0						COD_TEMPO,
	Cd.CD_CID				COD_CIDADE,
	V.CD_FILIAL				COD_FILIAL,
	1						COD_TIPOMOVIMENTO,
	I.TIPO_DESCONTO			COD_TIPODESCONTO,
	
	CASE WHEN V.NR_ORC = 0 THEN 0  --DIRETO PDV
		 ELSE  CASE WHEN isnull(V.ORIG_ORC,0) =0 THEN 1 --ORCAMENTO
					WHEN V.ORIG_ORC =1			 THEN 2 --TELEVENDAS
                    WHEN V.ORIG_ORC =2			 THEN 3 --PBM
                    WHEN V.ORIG_ORC =3			 THEN 4 --DRIVE THRU
                END  
    END  COD_ORIGVENDA, 
	I.QT_IT QTD_IT,
	(I.QT_IT * I.VLR_IT) AS TOT_IT_BRUTO,
	CASE WHEN (I.TOT_IT - I.VLR_DESC_TOT_RAT) > 0 THEN 
			I.TOT_IT - I.VLR_DESC_TOT_RAT
    ELSE
			0
	END AS  VLR_TOT,
	ROUND(I.QT_IT * I.VLR_IT * I.TX_DESC / 100, 2)  AS  VLR_DESC,
	ROUND(I.QT_IT * I.VLR_IT * I.TX_DESC / 100, 2) - ISNULL(I.VLR_DESC_VERBA, 0) AS VLR_DESC_IT_LIQ,
	ISNULL(I.VLR_DESC_VERBA, 0)		VLR_DESC_VERBA, 
		CASE WHEN EST_PROD_CPL.TP_PROD IN (10,11) THEN 
			I.TOT_IT - (I.TOT_IT * (EST_PROD_CPL.PERC_COMISSAO_SERVICO / 100)) 
         ELSE 
			ISNULL(I.QT_IT * I.VLR_CUSTO_MEDIO,0)
         END AS VLR_CUSTO_MD,

		CASE WHEN EST_PROD_CPL.TP_PROD IN (10,11) THEN 
			I.TOT_IT - (I.TOT_IT * (EST_PROD_CPL.PERC_COMISSAO_SERVICO / 100)) 
         ELSE 
			ISNULL(I.QT_IT * I.VLR_CUSTO_GER,0) 
         END AS VLR_CUSTO_GR,
	
	CASE WHEN I.QT_IT > 0 THEN   
                (((I.TOT_IT - I.VLR_DESC_TOT_RAT) / I.QT_IT) * 
				CASE WHEN I.TX_RED_IT > 0 THEN
					((I.TX_ICMS_IT * I.TX_RED_IT) / 100.0000)
				ELSE
					I.TX_ICMS_IT
				END) / 100.0000   
        ELSE 
			0 
        END AS  VLR_ICMS,

	ISNULL(I.QT_IT * I.VLR_PIS,0) + ISNULL(I.VLR_COFINS,0)   VLR_PIS_COFINS,
	ISNULL(I.QT_IT * I.VLR_PMC, 0)  VLR_PMC,
	CASE WHEN ISNULL(I.VLR_PMC,0) > I.VLR_IT THEN  
		(I.VLR_PMC - I.VLR_IT) * I.QT_IT   
	ELSE  
		0  
	END  AS VLR_OFERTA,
	(CASE WHEN V.VLR_LIQ_VD > 0 THEN  
		CASE WHEN V.VLR_LIQ_VD = V.VLR_DINH THEN  
				CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(18,2))  
			ELSE  
				CAST(((CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(20,10)) / CAST(V.VLR_LIQ_VD AS NUMERIC(20,10))) * CAST(V.VLR_DINH AS NUMERIC(18,4))) AS NUMERIC(18,4))   
			END  
	ELSE  
		0  
	END) AS  VLR_DINH,
	ISNULL((CASE WHEN V.VL_CHQS_A_VISTA > 0 THEN  
				(CASE WHEN V.VLR_LIQ_VD = V.VL_CHQS_A_VISTA THEN  
					CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(18,2))  
				ELSE  
					CAST(((CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(20,10)) / CAST(V.VLR_LIQ_VD AS NUMERIC(20,10))) * CAST(V.VL_CHQS_A_VISTA AS NUMERIC(18,2))) AS NUMERIC(18,2))   
				END)  
			END),0) AS      VLR_CHQ_VISTA,

	ISNULL((CASE WHEN V.VL_CHQS_A_PRZ > 0 THEN  
				(CASE WHEN V.VLR_LIQ_VD = V.VL_CHQS_A_VISTA THEN  
					CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(18,2))  
				ELSE  
					CAST(((CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(20,10)) / CAST(V.VLR_LIQ_VD AS NUMERIC(20,10))) * CAST(V.VL_CHQS_A_PRZ AS NUMERIC(18,2))) AS NUMERIC(18,2))   
				END)  
			END),0) 	VLR_CHQ_PRAZO,


	(CASE WHEN V.VLR_LIQ_VD = V.VLR_CARTAO THEN  
            CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(18,2))  
        ELSE  
            CAST(((CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(20,10)) / CAST(V.VLR_LIQ_VD AS NUMERIC(20,10))) * CAST(V.VLR_CARTAO AS NUMERIC(18,2))) AS NUMERIC(18,2))   
        END) AS VLR_CARTAO,  
			  
    (CASE WHEN V.VLR_LIQ_VD = V.VLR_TICKETS THEN  
            CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(18,2))  
        ELSE  
            CAST(((CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(20,10)) / CAST(V.VLR_LIQ_VD AS NUMERIC(20,10))) * CAST(V.VLR_TICKETS AS NUMERIC(18,2))) AS NUMERIC(18,2))   
        END) AS  VLR_TICKET,
	
	 (CASE WHEN V.VLR_LIQ_VD = V.VLR_DUP THEN  
                        CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(18,2))  
                 ELSE  
                        CAST(((CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(20,10)) / CAST(V.VLR_LIQ_VD AS NUMERIC(20,10))) * CAST(V.VLR_DUP AS NUMERIC(18,2))) AS NUMERIC(18,2))   
                 END) AS  VLR_DUPLICATA,
			             
                 (CASE WHEN V.VLR_LIQ_VD = (V.VLR_CONV + ISNULL( VLR_CONV_PBM, 0)) THEN  
                        CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(18,2))  
                 ELSE  
                        CAST(((CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(20,10)) / CAST(V.VLR_LIQ_VD AS NUMERIC(20,10))) * CAST((V.VLR_CONV +ISNULL( VLR_CONV_PBM, 0))AS NUMERIC(18,4))) AS NUMERIC(18,4))   
                 END) AS  VLR_CONV,

	  (CASE WHEN V.VLR_LIQ_VD = ISNULL(V.VLR_NCC,0) THEN  
                        CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(18,2))  
                 ELSE  
                        CAST(((CAST((I.TOT_IT - I.VLR_DESC_TOT_RAT) AS NUMERIC(20,10)) / CAST(V.VLR_LIQ_VD AS NUMERIC(20,10))) * CAST(ISNULL(V.VLR_NCC,0) AS NUMERIC(18,2))) AS NUMERIC(18,2))   
                 END) AS VLR_NCC,
	
	isnull((CASE WHEN (I.TOT_IT - I.VLR_DESC_TOT_RAT) > 0 THEN 
			I.TOT_IT - I.VLR_DESC_TOT_RAT
		ELSE
			0
		END)*(I.PERC_COMIS/100.000),0)  +  isnull((I.QT_IT * I.VLR_COMISSAO_FABRIC) ,0)AS VLR_COMISSAO ,
		 
	 getdate() DT_INFO,
	 ISNULL(I.VLR_RECOMPOSICAO_MARGEM,0) AS VLR_RECOMPOSICAO_MARGEM,
	 mf.CD_SIGLA
FROM 
	dbo.PDV_VD V
	INNER JOIN dbo.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
	AND V.ST_VD =0
	AND I.ST_IT =1
	INNER JOIN EST_PROD_CPL_MEDIAF mf ON
	i.CD_EMP = mf.CD_EMP AND
	i.CD_FILIAL = mf.CD_FILIAL AND
	i.CD_PROD = mf.CD_PROD 
	
	INNER JOIN dbo.PRC_FILIAL F ON 
	V.CD_EMP	= F.CD_EMP AND
	V.CD_FILIAL = F.CD_FILIAL 

	INNER JOIN dbo.GLB_CID Cd ON  
	F.CD_CID	= Cd.CD_CID

	INNER JOIN dbo.EST_PROD P ON 
	I.CD_EMP  = P.CD_EMP AND
	I.CD_PROD = P.CD_PROD

	INNER JOIN dbo.EST_PROD_EST_ARV_MERCADOLOGICA A ON 
	P.CD_EMP  =  A.CD_EMP AND
	P.CD_PROD = A.CD_PROD
	
	INNER JOIN dbo.EST_ARV_MERC_CATEGORIA C ON 
	A.CD_EMP  = C.CD_EMP AND
	A.CD_ARV_MERC_CATEG = C.CD_ARV_MERC_CATEG
	INNER JOIN EST_PROD_CPL ON
	I.CD_EMP = EST_PROD_CPL.CD_EMP AND
	I.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND
	I.CD_PROD = EST_PROD_CPL.CD_PROD 
WHERE
	V.CD_EMP=1 and V.DT_VD >= '2015-01-01'

UNION ALL

SELECT 
	v.CD_EMP,	
	v.CD_FILIAL,
	d.DT_DEVO DT_VD,	
	v.NR_ECF,
	v.CD_CX,
	i.CD_IT,
	d.CD_IMP,
	d.CD_PROD COD_PRODUTO,	
	A.CD_ARV_MERC_CATEG		COD_CATEGORIA,	
	C.CD_ARV_MERC_DEPTO		COD_DEPARTAMENTO,	
	P.CD_FABRIC				COD_FABRICANTE,	
	I.CD_VEND				COD_FUNCIONARIO,
	V.CD_USU_OPE			COD_USUARIO,
	A.CD_ARV_MERC_LINHA		COD_LINHA,
	0						COD_TEMPO,
	Cd.CD_CID				COD_CIDADE,
	V.CD_FILIAL				COD_FILIAL,
	2						COD_TIPOMOVIMENTO,	
	i.TIPO_DESCONTO			COD_TIPODESCONTO	,
	d.CD_ORIG_VENDA			COD_ORIGVENDA, 
	(d.QT_IT*-1)			QTD_IT	,
	((d.VLR_IT  * d.QT_IT) * -1 )  AS TOT_IT_BRUTO,
	((d.VLR_IT  * d.QT_IT) * -1 )    VLR_TOT	,
	ISNULL( (d.VLR_DESC_IT * d.QT_IT),0) * -1 AS VLR_DESC,
	ISNULL( (((d.VLR_DESC_IT * d.QT_IT))- d.VLR_DESC_VERBA),0) * -1 AS VLR_DESC_IT_LIQ,
	ISNULL(d.VLR_DESC_VERBA, 0) * -1 AS  VLR_DESC_VERBA,	
	(ISNULL(I.VLR_CUSTO_MEDIO,0)  * D.QT_IT) *-1				AS VLR_CUSTO_MD,	
	(I.VLR_CUSTO_GER	* D.QT_IT) *-1							AS VLR_CUSTO_GR,	
	((d.VLR_IT * d.QT_IT) * (ISNULL(d.PERC_ICMS,0)/100))* -1	AS  VLR_ICMS,
	(ISNULL(d.VLR_PIS,0)+ISNULL(d.VLR_COFINS,0)) *-1	        AS VLR_PIS_COFINS,
	(d.VLR_PMC_IT * d.QT_IT) * -1 AS  VLR_PMC,
	
	CASE WHEN d.VLR_PMC_IT > d.VLR_IT_BRUTO THEN 
		(d.VLR_PMC_IT - d.VLR_IT_BRUTO) * d.QT_IT
    ELSE 0 END  * -1 AS  VLR_OFERTA	,

	(CASE WHEN V.VLR_LIQ_VD = V.VLR_DINH THEN  
					(d.VLR_IT* d.QT_IT)
				ELSE  
					((d.VLR_IT* d.QT_IT)/ V.VLR_LIQ_VD ) * V.VLR_DINH
				END) *-1 AS  VLR_DINH,
	(CASE WHEN V.VLR_LIQ_VD = V.VL_CHQS_A_VISTA THEN  
					(d.VLR_IT* d.QT_IT)
				ELSE  
					((d.VLR_IT* d.QT_IT)/ V.VLR_LIQ_VD ) * V.VL_CHQS_A_VISTA
				END) *-1 AS  VLR_CHQ_VISTA,
	(CASE WHEN V.VLR_LIQ_VD = V.VL_CHQS_A_PRZ THEN  
					(d.VLR_IT* d.QT_IT)
				ELSE  
					((d.VLR_IT* d.QT_IT)/ V.VLR_LIQ_VD ) * V.VL_CHQS_A_PRZ
				END)*-1 AS  VL_CHQS_A_PRZ,

	(CASE WHEN V.VLR_LIQ_VD = V.VLR_CARTAO THEN  
					(d.VLR_IT* d.QT_IT)
				ELSE  
					((d.VLR_IT* d.QT_IT)/ V.VLR_LIQ_VD ) * V.VLR_CARTAO
				END)*-1 AS  VLR_CARTAO,
				
	(CASE WHEN V.VLR_LIQ_VD = V.VLR_TICKETS THEN  
					(d.VLR_IT* d.QT_IT)
				ELSE  
					((d.VLR_IT* d.QT_IT)/ V.VLR_LIQ_VD ) * V.VLR_TICKETS
				END)*-1 AS  VLR_TICKET,

	(CASE WHEN V.VLR_LIQ_VD = V.VLR_DUP THEN  
					(d.VLR_IT* d.QT_IT)
				ELSE  
					((d.VLR_IT* d.QT_IT)/ V.VLR_LIQ_VD ) * V.VLR_DUP
				END)*-1 AS  VLR_DUPLICATA,
	(CASE WHEN V.VLR_LIQ_VD = (V.VLR_CONV + ISNULL(V.VLR_CONV_PBM, 0)) THEN  
					(d.VLR_IT* d.QT_IT)
				ELSE  
					((d.VLR_IT* d.QT_IT)/ V.VLR_LIQ_VD) * (V.VLR_CONV +ISNULL(V.VLR_CONV_PBM, 0))
				END)*-1 AS  VLR_CONV,

	(CASE WHEN V.VLR_LIQ_VD = ISNULL(V.VLR_NCC,0) THEN  
					(d.VLR_IT* d.QT_IT)
				ELSE  
					((d.VLR_IT* d.QT_IT)/ V.VLR_LIQ_VD)  * ISNULL(V.VLR_NCC,0) 
				END) *-1 AS VLR_NCC,

	(isnull((d.VLR_IT * d.QT_IT) 
			* (d.PERC_COMIS/100.000),0) + isnull((d.QT_IT * d.VLR_COMISSAO_FABRIC) ,0)) *-1 AS VLR_COMISSAO ,
	getdate() DT_INFO,
	0 AS VLR_RECOMPOSICAO_MARGEM,
	mf.CD_SIGLA
FROM
	dbo.PDV_VD_DEV_EST_PROD_IMP d
	INNER JOIN dbo.PDV_VD v	ON     
	d.CD_EMP    = v.CD_EMP AND    
	d.CD_FILIAL = v.CD_FILIAL AND    
	d.CD_VD     = v.CD_VD
                               
	INNER JOIN dbo.PDV_VD_IT I ON     
	d.CD_EMP    = i.CD_EMP AND    
	d.CD_FILIAL = i.CD_FILIAL AND    
	d.CD_VD     = i.CD_VD and
	d.CD_PROD    = i.CD_PROD and
	d.CD_IT	    = i.CD_IT
	INNER JOIN EST_PROD_CPL_MEDIAF mf ON
	i.CD_EMP = mf.CD_EMP AND
	i.CD_FILIAL = mf.CD_FILIAL AND
	i.CD_PROD = mf.CD_PROD 
	
	INNER JOIN dbo.PRC_FILIAL F ON 
	V.CD_EMP	= F.CD_EMP AND
	V.CD_FILIAL = F.CD_FILIAL 

	INNER JOIN dbo.GLB_CID Cd ON  
	F.CD_CID	= Cd.CD_CID

	INNER JOIN dbo.EST_PROD P ON 
	I.CD_EMP  = P.CD_EMP AND
	I.CD_PROD = P.CD_PROD

	INNER JOIN dbo.EST_PROD_EST_ARV_MERCADOLOGICA A ON 
	P.CD_EMP  = A.CD_EMP AND
	P.CD_PROD = A.CD_PROD	
	
	INNER JOIN dbo.EST_ARV_MERC_CATEGORIA C ON 
	A.CD_EMP  = C.CD_EMP AND
	A.CD_ARV_MERC_CATEG = C.CD_ARV_MERC_CATEG
	    		
WHERE 
	V.ST_VD =0
	AND I.ST_IT =1 and d.DT_DEVO >= '2015-01-01'