IF  EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_PDV_VD_RC_CLI_CONV]'))

DROP VIEW [DBO].[V_PDV_VD_RC_CLI_CONV]    
GO


CREATE VIEW [dbo].[V_PDV_VD_RC_CLI_CONV]
AS 
(
SELECT        
	CLI.CD_EMP, 
	CLI.CD_CLI, 
	CLI.RZ_CLI, 
	CLI.NM_FANT, CLI.CGC_CPF, CONV.DS_CONV, CLI.ESP_CLI, CLI.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, 
--RC_CLI_CONV_RC_CLI.VLR_CRED, 
--RC_CLI_CONV_RC_CLI.VLR_DEB, 
    CONV.PERC_DESC, 
	CLI.CD_CONV, 
	ISNULL(RC_CLI_CONV_RC_CLI.STS_ATIVO, 0) AS STS_ATIVO, 
	CONV.TP_CONV, CLI.CONS_FINAL, 
	CONV.INSTRUCOES, 
	CONV.FLAG_REQUER_SENHA, 
	CLI.SENHA, CLI.ROT_CONV_EXT, 
	CONV.PERC_REC_MIN_CONV, 
	CONV.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, 
	ISNULL(CONV.VLR_MIN_PARC,0) VLR_MIN_PARC, 
	CONV.QTD_DIAS_PRORROG, 
	CONV.DIA_FAT, 
	CLI.STS_CLI,
--RC_CLI_CONV_RC_CLI.VLR_DEB_FUTURO,
	COALESCE(TITULAR.FLAG_DEPENDENTE_COMP_LIMITE_TITULAR, 0) AS FLAG_DEPENDENTE_COMP_LIMITE_TITULAR,
   (CASE
         COALESCE(TITULAR.FLAG_DEPENDENTE_COMP_LIMITE_TITULAR, 0) WHEN 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
		 COALESCE(TITULAR.FLAG_DEPENDENTE_COMP_LIMITE_TITULAR, 0) WHEN 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
		 COALESCE(TITULAR.FLAG_DEPENDENTE_COMP_LIMITE_TITULAR, 0) WHEN 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
	     COALESCE(DEPENDE.CD_CLI_TITULAR, 0) WHEN 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
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	)
	
/*
SELECT 
cli.cd_emp, cli.cd_cli, 
cli.rz_cli, cli.cgc_cpf, 
conv.ds_conv, 
cli.esp_cli, 
cli.matricula,  
CASE
    WHEN 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, 
cli.vlr_cred ,
cli.vlr_deb,
conv.perc_desc, 
cli.cd_conv, 
cli.sts_cli, 
conv.tp_conv, 
cli.cons_final, 
conv.instrucoes, 
conv.flag_requer_senha, 
cli.senha, 
cli.rot_conv_ext,
conv.PERC_REC_MIN_CONV,
conv.PERC_REC_MAX_CONV,
cli.PERC_REC_MIN_CONV AS PERC_REC_MIN_CONVENIADO,
cli.PERC_REC_MAX_CONV AS PERC_REC_MAX_CONVENIADO,
conv.QT_MAX_PARC,conv.VLR_MIN_PARC,conv.qtd_dias_prorrog,conv.DIA_FAT 
FROM v_rc_cli_pdv as cli
inner join v_rc_cli_conv_pdv_vd as conv
on cli.cd_emp=conv.cd_emp and 
cli.cd_conv = conv.cd_conv)
*/

GO


