/****** Object:  View [dbo].[V_EST_NF_SAI_CAB]    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_CANCEL]'))
DROP VIEW [dbo].[V_PDV_VD_IT_CANCEL]
GO
GO
GO
  

--SELECT * FROM V_PDV_VD_IT_CANCEL WHERE CD_EMP = 1 AND CD_FILIAL = 124 AND CD_PROD = 9947
CREATE VIEW [dbo].[V_PDV_VD_IT_CANCEL]
AS
SELECT     
	i.CD_EMP, 
	i.CD_FILIAL, 
	i.CD_VD, 
	i.CD_VEND, 
	v.DT_CONSD, 
	v.CD_CX, 
	v.DT_VD, 
	i.CD_PROD, 
	i.QT_IT, 
	v.NR_ECF, 
	i.TOT_IT - i.VLR_DESC_TOT_RAT 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(VLR_DESC_VERBA,0) AS VLR_DESC_IT_LIQ, 
	ISNULL(VLR_DESC_VERBA,0) AS VLR_DESC_VERBA,
	i.QT_IT * i.VLR_IT AS TOT_IT_BRUTO, 
	i.VLR_DESC_TOT_RAT, 
	i.VLR_IT, 
	i.TX_DESC, 
	i.CD_TRIB_FC, 
    i.TX_ICMS_IT, 
	v.VLR_DESC, 
	CONVERT(FLOAT, i.VLR_DESC_TOT_RAT / i.TOT_IT) AS PERC_DESC, 
	v.VLR_DINH, 
	v.VLR_CHQS, 
	v.VLR_CARTAO, 
	v.VLR_TICKETS, 
    v.VLR_DUP, 
	v.VLR_CONV, 
	v.VLR_TROCO_CHQ, 
	v.VLR_LIQ_VD, 
	i.CD_IT, 
	v.HR_VD, 
	v.CD_USU, 
	v.CD_CLI, 
    CASE WHEN i.QT_IT > 0 THEN 
		ROUND(ROUND(ROUND(i.TOT_IT / i.QT_IT, 2) * i.TX_ICMS_IT, 2) / 100, 2) 
	ELSE 
		0 
	END AS VLR_IMPOSTOS, 
	i.VLR_CUSTO AS VLR_CUSTO_UNI_LIQ, 
	i.VLR_CUSTO AS VLR_CUSTO_BRUTO, 
	i.TIPO_DESCONTO, 
    CASE i.TIPO_DESCONTO WHEN 0 THEN 
		'SEM DESCONTO' 
	WHEN 1 THEN 
		'DESCONTO CADASTRO PRODUTO' 
	WHEN 2 THEN 
		'DESCONTO LOJA' 
	WHEN 3 THEN 
		'DESCONTO CONVENIO'
	WHEN 4 THEN 
		'DESCONTO FIDELIDADE' 
	WHEN 5 THEN 
		'DESCONTO PBM' 
	WHEN 6 THEN 
		'DESCONTO OFERTA' 
	WHEN 7 THEN 
		'DESCONTO VALOR FIDELIDADE' 
	WHEN 8 THEN 
		'DESCONTO CUPONAGEM' 
	WHEN 9 THEN 
		'DESCONTO CRM' 
	WHEN 10 THEN 
		'DESCONTO POR TROCA DE PONTOS' 
	WHEN 11 THEN 
		'DESCONTO ANIVERSARI0' 
	WHEN 12 THEN
		'DESCONTO POR CLASSIFICACAO DE CLIENTE' 
	ELSE 
		'ERRO SEM CLASSIFICACAO' END AS DS_TIPO_DESCONTO, 
	v.NR_ORC, 
	v.VS_PDV, 
	v.ORIG_ORC, 
	ISNULL(v.NR_CARTAO_MARKA, 0) AS NR_CARTAO_MARKA, 
	i.PIS_COFINS, 
	ISNULL(i.VLR_PMC, 0) AS VLR_PMC, 
	ISNULL(i.VLR_PMC, 0) * i.QT_IT AS VLR_PMC_TOTAL, 
	ISNULL(i.VLR_OFERTA, 0) AS VLR_OFERTA, 
	ISNULL(i.VLR_DESC_CAD, 0) AS VLR_DESC_CAD, 
	ISNULL(i.DESC_FIXADO_CAD, 0) AS DESC_FIXADO_CAD, 
	ISNULL(i.PERC_DESC_FIDEL, 0) AS PERC_DESC_FIDEL, 
	ISNULL(i.PERC_DESC_FIDEL_MAX, 0) AS PERC_DESC_FIDEL_MAX, 
	CASE WHEN i.TIPO_DESCONTO = 6 THEN 
		(i.VLR_PMC - i.VLR_IT) * i.QT_IT 
	WHEN i.TIPO_DESCONTO = 5 THEN 
		(i.VLR_PMC - i.VLR_IT) * i.QT_IT 
	ELSE 
		0 
	END AS VLR_DESC_OFERTA, 
	CASE WHEN i.TIPO_DESCONTO = 6 OR i.TIPO_DESCONTO = 5 THEN 
		(i.VLR_PMC - i.VLR_IT) * i.QT_IT 
	WHEN i.TIPO_DESCONTO <> 5 OR i.TIPO_DESCONTO <> 6 THEN 
		ROUND(i.QT_IT * i.VLR_IT * i.TX_DESC / 100, 2) 
	ELSE 
		0 
	END AS VLR_DESC_REAL,
	CASE WHEN ISNULL(I.CD_USU_CANCEL,0)=0 THEN 
		 C.CD_USU ELSE ISNULL(I.CD_USU_CANCEL,0) END AS CD_USU_CANCEL,
	QT_EST_ANT,
	I.LISTA_PROD,
	GLB_CID.UF AS UF_CLI,
	I.VLR_PIS,
	I.VLR_COFINS,
	v.ST_VD,
	i.ST_IT,
	ISNULL(TIPO_IMPOSTO,0) AS TIPO_IMPOSTO,
	ISNULL(V.FLAG_NFCE,0) AS FLAG_NFCE,
	V.NR_ECF_UUID AS UUID
FROM         
	dbo.PDV_VD AS v 
	INNER JOIN dbo.PDV_VD_IT AS 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

	LEFT JOIN PDV_VD_CANCEL C ON
	v.CD_EMP = C.CD_EMP AND 
	v.CD_FILIAL = C.CD_FILIAL AND 
	v.CD_VD = C.CD_VD
	LEFT OUTER JOIN RC_CLI ON
	V.CD_EMP = RC_CLI.CD_EMP AND 
	V.CD_CLI = RC_CLI.CD_CLI 
    LEFT OUTER JOIN GLB_CID  ON 
    GLB_CID.CD_CID = RC_CLI.CD_CID  
WHERE    
    i.cd_emp=1 
	and (v.ST_VD = 1 or i.ST_IT = 2)


GO


