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

CREATE VIEW 
	[dbo].[V_PDV_VD_IT_CONV_MW]
AS
SELECT
	V.CD_EMP,
	V.CD_FILIAL,
	V.CD_VD,
	RC_CLI_CONV_VINC_RC_CLI.CD_CLI AS CD_CONV,

	CASE WHEN DEP.CD_CLI_TITULAR IS NOT NULL THEN
		COALESCE(DEP.CD_CLI_TITULAR,0)
	ELSE
		VD_CONV.CD_CLI
	END AS CD_CONVENIADO,

	CASE WHEN DEP.CD_CLI_DEPEND IS NOT NULL THEN
		COALESCE(DEP.CD_CLI_DEPEND,0)
	ELSE
		0
	END AS CD_DEPENDENTE,
	
	(SELECT MAX(PARC) FROM RC_VD_CONV WHERE CD_EMP = V.CD_EMP AND CD_FILIAL = V.CD_FILIAL AND NR_ECF= V.NR_ECF AND CD_CX = V.CD_CX AND DT_VD = V.DT_VD) AS NR_PARCELAS,
	VD_CONV.PARC AS PARCELA,
	VD_CONV.VLR_CONV,
	VD_CONV.DT_FECHA

FROM
	PDV_VD V
	INNER JOIN PDV_VD_RC_VD_CONV VINC ON
		V.CD_EMP = VINC.CD_EMP
		AND V.CD_FILIAL = VINC.CD_FILIAL
		AND V.CD_VD = VINC.CD_VD
	INNER JOIN RC_VD_CONV VD_CONV ON
		VINC.CD_EMP = VD_CONV.CD_EMP
		AND VINC.CD_CTR = VD_CONV.CD_CTR
	INNER JOIN RC_CLI_CONV_VINC_RC_CLI ON 
		VD_CONV.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP
		AND VD_CONV.CD_CONV = RC_CLI_CONV_VINC_RC_CLI.CD_CONV
	LEFT JOIN RC_CLI_CONV_DEPENDENTE DEP ON
		VD_CONV.CD_EMP = DEP.CD_EMP
		AND VD_CONV.CD_CLI = DEP.CD_CLI_DEPEND
WHERE
	VD_CONV.ST_FECHVD_CONV <> 2

UNION 
	
SELECT 
	V.CD_EMP,
	V.CD_FILIAL,
	V.CD_VD,
	V.CD_CLI_PBM AS CD_CONV,
	V.CD_CLI_PBM AS CD_CONVENIADO,
	0 AS CD_DEPENDENTE,
	1 AS NR_PARCELAS,
	1 AS PARCELA,
	V.VLR_CONV_PBM AS VLR_CONV,
	V.DT_VD

FROM 
	PDV_VD V

WHERE
	VLR_CONV_PBM > 0 
GO


