
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PDV_VD_DEV_CONVENIOS]'))
DROP VIEW [dbo].[V_PDV_VD_DEV_CONVENIOS]
GO

CREATE VIEW [dbo].[V_PDV_VD_DEV_CONVENIOS]
WITH ENCRYPTION  AS
SELECT     
	dbo.PDV_VD.CD_EMP, 
	dbo.PDV_VD.CD_FILIAL, 
	dbo.PDV_VD.CD_VD, 
	PDV_VD.DT_VD,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CONV,
	0 AS VLR_CONV_PBM,
	dbo.EST_NF_ENT.STS_NF, 
	dbo.EST_NF_ENT.DT_ENT,
	CASE WHEN PDV_VD_CANCEL_CONV.CD_VD IS NULL THEN
		0
	ELSE
		1
	END CANCEL_CONVENIO,
	PDV_VD.ST_VD,
	PDV_VD.CD_CX,
	PDV_VD.CD_USU
FROM         
	dbo.PDV_VD_EST_NF_ENT INNER JOIN dbo.EST_NF_ENT ON 
	dbo.PDV_VD_EST_NF_ENT.CD_EMP = dbo.EST_NF_ENT.CD_EMP AND 
	dbo.PDV_VD_EST_NF_ENT.CD_FILIAL = dbo.EST_NF_ENT.CD_FILIAL AND 
	dbo.PDV_VD_EST_NF_ENT.CD_NF = dbo.EST_NF_ENT.CD_NF 
	INNER JOIN dbo.PDV_VD ON 
	dbo.PDV_VD_EST_NF_ENT.CD_EMP = dbo.PDV_VD.CD_EMP AND 
	dbo.PDV_VD_EST_NF_ENT.CD_FILIAL = dbo.PDV_VD.CD_FILIAL AND 
    dbo.PDV_VD_EST_NF_ENT.CD_VD = dbo.PDV_VD.CD_VD
    LEFT OUTER JOIN PDV_VD_CANCEL_CONV ON
	PDV_VD.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP AND 
	PDV_VD.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND 
    PDV_VD.CD_VD = PDV_VD_CANCEL_CONV.CD_VD
WHERE     
	dbo.EST_NF_ENT.CD_NT_MOV = 3
GROUP BY
	dbo.PDV_VD.CD_EMP, 
	dbo.PDV_VD.CD_FILIAL, 
	dbo.PDV_VD.CD_VD, 
	PDV_VD.DT_VD,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CONV,
	dbo.EST_NF_ENT.STS_NF, 
	dbo.EST_NF_ENT.DT_ENT,
	PDV_VD_CANCEL_CONV.CD_VD,
	PDV_VD.ST_VD,
	PDV_VD.CD_CX,
	PDV_VD.CD_USU
		
UNION ALL

SELECT 
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
	PDV_VD_DEV_EST_PROD_IMP.CD_VD, 
	PDV_VD.DT_VD,
	PDV_VD.VLR_DINH,
	(CASE WHEN PDV_VD_CANCEL_CONV.CD_VD IS NULL THEN
		0
	ELSE
		PDV_VD.VLR_CONV
	END) AS VLR_CONV,
	0 AS VLR_CONV_PBM,
	0 AS STS_NF, 
	MAX(PDV_VD_DEV_EST_PROD_IMP.DT_DEVO) AS DT_ENT,
	CASE WHEN PDV_VD_CANCEL_CONV.CD_VD IS NULL THEN
		0
	ELSE
		1
	END CANCEL_CONVENIO,
	PDV_VD.ST_VD,
	PDV_VD.CD_CX,
	PDV_VD.CD_USU
FROM
	PDV_VD_DEV_EST_PROD_IMP LEFT OUTER JOIN PDV_VD_CANCEL_CONV ON
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP AND 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND 
    PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD_CANCEL_CONV.CD_VD
	INNER JOIN dbo.PDV_VD ON 
	dbo.PDV_VD_DEV_EST_PROD_IMP.CD_EMP = dbo.PDV_VD.CD_EMP AND 
	dbo.PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = dbo.PDV_VD.CD_FILIAL AND 
    dbo.PDV_VD_DEV_EST_PROD_IMP.CD_VD = dbo.PDV_VD.CD_VD AND
	dbo.PDV_VD_DEV_EST_PROD_IMP.DT_DEVO = dbo.PDV_VD.DT_VD
GROUP BY
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
	PDV_VD_DEV_EST_PROD_IMP.CD_VD, 
	PDV_VD.DT_VD,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CONV,
	PDV_VD_CANCEL_CONV.CD_VD,
	PDV_VD.ST_VD,
	PDV_VD.CD_CX,
	PDV_VD.CD_USU

	
UNION ALL

SELECT 
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
	PDV_VD_DEV_EST_PROD_IMP.CD_VD, 
	PDV_VD.DT_VD,
	PDV_VD.VLR_DINH,
	0 AS VLR_CONV,
	PDV_VD.VLR_CONV_PBM ,
	0 AS STS_NF, 
	MAX(PDV_VD_DEV_EST_PROD_IMP.DT_DEVO) AS DT_ENT,
	CASE WHEN PDV_VD_CANCEL_CONV.CD_VD IS NULL THEN
		0
	ELSE
		1
	END CANCEL_CONVENIO,
	PDV_VD.ST_VD,
	PDV_VD.CD_CX,
	PDV_VD.CD_USU
FROM
	PDV_VD_DEV_EST_PROD_IMP LEFT OUTER JOIN PDV_VD_CANCEL_CONV ON
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD_CANCEL_CONV.CD_EMP AND 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL AND 
    PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD_CANCEL_CONV.CD_VD
	INNER JOIN dbo.PDV_VD ON 
	dbo.PDV_VD_DEV_EST_PROD_IMP.CD_EMP = dbo.PDV_VD.CD_EMP AND 
	dbo.PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = dbo.PDV_VD.CD_FILIAL AND 
    dbo.PDV_VD_DEV_EST_PROD_IMP.CD_VD = dbo.PDV_VD.CD_VD AND
	dbo.PDV_VD_DEV_EST_PROD_IMP.DT_DEVO = dbo.PDV_VD.DT_VD
GROUP BY
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
	PDV_VD_DEV_EST_PROD_IMP.CD_VD, 
	PDV_VD.DT_VD,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CONV_PBM,
	PDV_VD_CANCEL_CONV.CD_VD,
	PDV_VD.ST_VD,
	PDV_VD.CD_CX,
	PDV_VD.CD_USU


GO


