/****** Object:  View [dbo].[V_PDV_VD_IT_VD_PBM]    Script Date: 04/11/2013 10:05:53 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PDV_VD_IT_VD_PBM]'))
DROP VIEW [dbo].[V_PDV_VD_IT_VD_PBM]
GO
CREATE VIEW V_PDV_VD_IT_VD_PBM AS
SELECT 
	v.CD_EMP, 
	v.CD_FILIAL, 
	v.NR_ECF, 
	v.CD_CX, 
	v.DT_VD, 
	RC_CLI.NM_FANT, 
	i.CD_PROD, 
	EST_PROD.DS_PROD, 
	EST_PROD_FABRIC.NM_FABRIC, 
	i.QT_IT, 
	PDV_VD_IT_VD_PBM.NR_AUTORIZACAO, 
	PDV_VD_IT_VD_PBM.VLR_PROD_CLI, 
	PDV_VD_IT_VD_PBM.VLR_REEMBOL, 
	PDV_VD_IT_VD_PBM.VLR_CONVENIO, 
	PDV_VD_IT_VD_PBM.CD_USU_LIBERA_POS_PBM,
	PDV_VD_IT_VD_PBM.VALOR_DESCONTO_POS_PBM,
	((I.QT_IT * I.VLR_IT)) AS VLR_VD,
	CASE WHEN (I.TOT_IT - I.VLR_DESC_TOT_RAT) > 0 THEN 
		I.TOT_IT - I.VLR_DESC_TOT_RAT
	ELSE
		0
	END AS TOT_IT,   
	ROUND(i.QT_IT * i.VLR_IT * i.TX_DESC / 100, 2)  AS VLR_DESC_IT, 
	ROUND(i.QT_IT * i.VLR_IT * i.TX_DESC / 100, 2) - ISNULL(i.VLR_DESC_VERBA, 0) AS VLR_DESC_IT_LIQ, 
	i.TX_DESC,
	i.VLR_DESC_VERBA,
			(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_IT,     
			(CASE WHEN V.VLR_LIQ_VD = V.VLR_CHQS 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_CHQS AS NUMERIC(18,2))) AS NUMERIC(18,2))   
				END) AS VLR_CHQS_IT,  
	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_CHQS_IT_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) AS VLR_CHQS_IT_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_IT,   
	(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_TICKETS_IT,          
	(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_DUP_IT,                      
	(CASE WHEN V.VLR_LIQ_VD = V.VLR_CONV 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 AS NUMERIC(18,4))) AS NUMERIC(18,4))   
	END) AS VLR_CONV_IT,  
	(CASE WHEN V.VLR_TROCO_CHQ = 0  THEN  
		0  
	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_TROCO_CHQ AS NUMERIC(18,2))) AS NUMERIC(18,2))   
	END) AS VLR_TROCO_CHQ_IT,  
	(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_IT,
    ISNULL(PDV_VD_IT_VD_PBM.VLR_REEMBOL,0)+ISNULL(PDV_VD_IT_VD_PBM.VLR_CONVENIO,0) AS VLR_CONV_PBM,
    ISNULL(EST_PBM_PROJETO.CD_PROJETO,0 ) CD_PROJETO,    
    ISNULL(DS_PROJETO,'' ) DS_PROJETO   

FROM 
	PDV_VD v INNER 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 
	INNER JOIN PDV_VD_IT_VD_PBM ON 
	i.CD_EMP = PDV_VD_IT_VD_PBM.CD_EMP AND 
	i.CD_FILIAL = PDV_VD_IT_VD_PBM.CD_FILIAL AND 
	i.CD_VD = PDV_VD_IT_VD_PBM.CD_VD AND 
	i.CD_IT = PDV_VD_IT_VD_PBM.CD_IT 
	INNER JOIN EST_PROD ON 
	i.CD_EMP = EST_PROD.CD_EMP AND 
	i.CD_PROD = EST_PROD.CD_PROD 
	INNER JOIN RC_CLI ON 
	PDV_VD_IT_VD_PBM.CD_EMP = RC_CLI.CD_EMP AND 
	PDV_VD_IT_VD_PBM.CD_CLI_PBM = RC_CLI.CD_CLI 
	INNER JOIN EST_PROD_FABRIC ON 
	EST_PROD.CD_FABRIC = EST_PROD_FABRIC.CD_FABRIC 
	    
	LEFT JOIN EST_PBM_PROJETO ON 
	i.CD_EMP = EST_PBM_PROJETO.CD_EMP
	AND PDV_VD_IT_VD_PBM.CD_PROJETO = EST_PBM_PROJETO.CD_PROJETO

WHERE  
	v.ST_VD=0  
	AND i.ST_IT=1
 
