IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_PDV_VD_RC_CLI_CONV]'))
	DROP VIEW V_PDV_VD_RC_CLI_CONV
GO

CREATE VIEW V_PDV_VD_RC_CLI_CONV
AS
SELECT
	CLI.CD_EMP										AS CD_EMP 
	,CLI.CD_CLI										AS CD_CLI 
	,CLI.RZ_CLI										AS RZ_CLI 
	,CLI.NM_FANT									AS NM_FANT
	,CLI.CGC_CPF									AS CGC_CPF
	,CONV.DS_CONV									AS DS_CONV
	,CLI.ESP_CLI									AS ESP_CLI
	,CLI.MATRICULA									AS MATRICULA
	,CASE WHEN RC_CLI_CONV_RC_CLI.STS_CRED = 1	THEN 'BLOQUEADO' ELSE 'LIBERADO' END AS STS_CRED_CONVENIADO 
	,CASE WHEN CONV.STS_CRED = 1				THEN 'BLOQUEADO' ELSE 'LIBERADO' END AS STS_CRED_CONVENIO
    ,CONV.PERC_DESC									AS PERC_DESC
	,CLI.CD_CONV									AS CD_CONV 
	,ISNULL(RC_CLI_CONV_RC_CLI.STS_ATIVO, 0)		AS STS_ATIVO 
	,CONV.TP_CONV									AS TP_CONV
	,CLI.CONS_FINAL									AS CONS_FINAL
	,CONV.INSTRUCOES								AS INSTRUCOES
	,CONV.FLAG_REQUER_SENHA							AS FLAG_REQUER_SENHA
	,CLI.SENHA										AS SENHA
	,CLI.ROT_CONV_EXT								AS ROT_CONV_EXT
	,CONV.PERC_REC_MIN_CONV							AS PERC_REC_MIN_CONV
	,CONV.PERC_REC_MAX_CONV							AS PERC_REC_MAX_CONV
	,RC_CLI_CONV_RC_CLI.PERC_REC_MIN_CONV			AS PERC_REC_MIN_CONVENIADO
	,RC_CLI_CONV_RC_CLI.PERC_REC_MAX_CONV			AS PERC_REC_MAX_CONVENIADO
	,CONV.QT_MAX_PARC								AS QT_MAX_PARC
	,ISNULL(CONV.VLR_MIN_PARC,0)					AS VLR_MIN_PARC
	,CONV.QTD_DIAS_PRORROG							AS QTD_DIAS_PRORROG
	,CONV.DIA_FAT									AS DIA_FAT
	,CLI.STS_CLI									AS STS_CLI
	,COALESCE(TITULAR.FLAG_DEPENDENTE_COMP_LIMITE_TITULAR, 0)	AS FLAG_DEPENDENTE_COMP_LIMITE_TITULAR
	,(CASE 
		WHEN COALESCE(TITULAR.FLAG_DEPENDENTE_COMP_LIMITE_TITULAR,0) = 1 THEN COALESCE(RC_CLI_CONV_RC_CLI_TITULAR.VLR_CRED,0)
		ELSE COALESCE(RC_CLI_CONV_RC_CLI.VLR_CRED,0)
	END) AS VLR_CRED
	,(CASE
		WHEN COALESCE(TITULAR.FLAG_DEPENDENTE_COMP_LIMITE_TITULAR,0) = 1 THEN COALESCE(RC_CLI_CONV_RC_CLI_TITULAR.VLR_DEB,0)
		ELSE COALESCE(RC_CLI_CONV_RC_CLI.VLR_DEB,0)
	 END) AS VLR_DEB
   ,(CASE
		WHEN COALESCE(TITULAR.FLAG_DEPENDENTE_COMP_LIMITE_TITULAR,0) = 1 THEN COALESCE(RC_CLI_CONV_RC_CLI_TITULAR.VLR_DEB_FUTURO,0)
		ELSE COALESCE(RC_CLI_CONV_RC_CLI.VLR_DEB_FUTURO,0)
	 END) AS VLR_DEB_FUTURO
   ,(CASE WHEN COALESCE(DEPENDE.CD_CLI_TITULAR,0) = 0 THEN 0 ELSE 1 END) AS EHDEPENDENTE
	,COALESCE(DEPENDE.CD_CLI_TITULAR, 0)			AS CD_CLI_TITULAR
	,ISNULL(CLI_CONV.FLAG_SOMENTE_DESC_CONTRATO,0)	AS FLAG_SOMENTE_DESC_CONTRATO
	,CONV.TP_LIMITE_ROTATIVO						AS TP_LIMITE_ROTATIVO
	,CONV.FLAG_LIBERA_LIMITE_COMPRA					AS FLAG_LIBERA_LIMITE_COMPRA
	,COALESCE(CLI_CONV.VLR_ACIMA_PARC_EXTRA,0)		AS VLR_ACIMA_PARC_EXTRA
	,COALESCE(CLI_CONV.NUM_MAX_PARC_EXTRA,0)		AS NUM_MAX_PARC_EXTRA
	,CLI_CONV.CONV_HABILITADO						AS CONV_HABILITADO
FROM            
	V_RC_CLI_PDV AS CLI 
	INNER JOIN RC_CLI_CONV_RC_CLI ON 
			CLI.CD_EMP  = RC_CLI_CONV_RC_CLI.CD_EMP 
		AND CLI.CD_CLI  = RC_CLI_CONV_RC_CLI.CD_CLI 
		AND CLI.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV 
	INNER JOIN V_RC_CLI_CONV_PDV_VD AS CONV ON 
			RC_CLI_CONV_RC_CLI.CD_EMP  = CONV.CD_EMP 
		AND RC_CLI_CONV_RC_CLI.CD_CONV = CONV.CD_CONV
	LEFT JOIN RC_CLI_CONV_DEPENDENTE DEPENDE ON 
			CLI.CD_CONV = DEPENDE.CD_CONV
		AND CLI.CD_CLI  = DEPENDE.CD_CLI_DEPEND
		AND CLI.CD_EMP  = DEPENDE.CD_EMP									  
	LEFT JOIN V_RC_CLI_PDV TITULAR ON 
			DEPENDE.CD_CONV         = TITULAR.CD_CONV
		AND DEPENDE.CD_CLI_TITULAR  = TITULAR.CD_CLI
		AND DEPENDE.CD_EMP          = TITULAR.CD_EMP 						
	LEFT JOIN RC_CLI_CONV_RC_CLI RC_CLI_CONV_RC_CLI_TITULAR ON 
			TITULAR.CD_CLI = RC_CLI_CONV_RC_CLI_TITULAR.CD_CLI
		AND TITULAR.CD_EMP = RC_CLI_CONV_RC_CLI_TITULAR.CD_EMP 
	LEFT JOIN RC_CLI_CONV	CLI_CONV  ON 
			CLI_CONV.CD_CLI   = CLI.CD_CLI	
		AND CLI_CONV.CD_CONV  = CLI.CD_CONV																									
WHERE 
	CLI.STS_CLI = 0


