dbo.LN_enriesgo

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

/*
-- =============================================
-- Author: María Inés Soria
-- Create date: 04/01/2021
-- Description: Card en riesgo - Indicador 
-- =============================================

-- Si no tiene nada defino en dias_alertas para tabla like 'riesgo%', se calcula tomando todos los defaults
-- Si lo tiene se toma los datos definidos para el cliente
-- Si tiene cosas en dias_alertas, pero no especificamente para alguno de los items --> no lo va a tomar en cuenta

CONTRAPARTES EN RIESGO
El botón debe filtrar y mostrar el panel con las contrapartes que cumpla con cualquiera y al menos una de estas condiciones
Situación en Bancos <> 1
Mas de 3 cheques rechazados x Sin Fondos en los últimos 60 días
Uno o más Juicios ejecutivos o pedidos de quiebra en los últimos 180 días
Rentabilidad negativa Y Solvencia menor a 0,3 en el último ejercicio
Score de Balance menor a 40
Score de Dossier < 4
Score de Informe Comercial < 40

DECLARE @enriesgo bit 
DECLARE @porque varchar(max) 
EXEC [dbo].[LN_enriesgo] 31296, @enriesgo output, @porque output 
select @enriesgo as riesgo, @porque as porque
*/

CREATE OR ALTER PROCEDURE dbo.LN_enriesgo 
  @contraparte int,
  @enriesgo1 bit = null output, @porque1 varchar(max) output 
AS
BEGIN
 SET NOCOUNT ON;

 declare @enriesgo bit = 0
 declare @porque varchar(max) = '' 

 declare @cliente int = null
 DECLARE @p_5anios smalldatetime = getdate() - 1825
    DECLARE @p_2anios smalldatetime = getdate() - 730
    DECLARE @p_1anio  smalldatetime = getdate() - 365
 declare @cuit varchar(20)
 declare @cheques table (causal int, fecha_rechazo date, fecha_levantamiento date, importe money) 

    set @cuit = (select identificacion from padrones where id_padron = (select id_padron from ln_contrapartes where contraparte = @contraparte))
 set @cliente = (select cliente from ln_contrapartes where contraparte = @contraparte)

 declare @gestion int = (select gestion 
       from LN_gestiones 
       where contraparte = @contraparte 
         AND LN_gestiones.desde <= GETDATE()
       AND LN_gestiones.hasta >= GETDATE()
       AND LN_gestiones.vigente = 1)

 declare @etiqueta varchar(100) = null
 
 declare @ind_enriesgo  int = (select indicador from ln_indicadores where clave_indicador = 'ENRIESGO')
 
 /* Podemos tener definidos montos, y cantidad en la tabla ln_dias_alerta, si hay los tomamos de ahi, sino usamos los defaults */
 declare @p_montodeu  bigint = null 
 declare @p_diasdeu   int = null 
 declare @p_cantdeu   int = null 
 declare @p_causaldeu   int = null 
 declare @p_levantados char(1) = null
 declare @p_puntosdeu decimal(5,2) = null 

 declare @p_montodeu3  bigint = null 
 declare @p_diasdeu3   int = null 
 declare @p_cantdeu3   int = null 
 declare @p_causaldeu3   int = null 
 declare @p_levantados3 char(1) = null
 declare @p_puntosdeu3 decimal(5,2) = null 

 declare @p_montoch  bigint = null 
 declare @p_diasch   int = null 
 declare @p_cantch   int = null 
 declare @levantados char(1) 
 declare @p_diaspq   int = null 
 declare @p_cantpq   int = null
 declare @p_diasjo   int = null 
 declare @p_cantjo   int = null
 declare @p_valorren decimal(10,2) 
 declare @p_valorsol decimal(10,2) 
 declare @p_valorsb  decimal(10,2) 
 declare @p_valordos decimal(10,2) 
 declare @p_valoric  decimal(10,2) 
 

 declare @tiene int = (select COUNT(*) from ln_dias_alertas where tabla like 'riesgo_%' and cliente = @Cliente)

 if (@tiene = 0) -- Toma los defaults 
 begin 
  -- Cheques sin fondo 
  set @p_diasch   = 60
  set @p_montoch  = null 
  set @p_cantch   = 3
  set @levantados = 'N' 
  -- Bancos 
  set @p_montodeu  = null 
  set @p_diasdeu   = 720
  set @p_cantdeu   = 0
  set @p_causaldeu = 2 -- mayor o igual a 2 
  -- Pedidos Quiebra
  set @p_diaspq = 180
  set @p_cantpq = 1
  -- Juicios 
  set @p_diasjo = 180
  set @p_cantjo = 1
  -- Rentabilidad
  set @p_valorren = 0
  -- Solvencia 
  set @p_valorsol = 0.3
  -- Score Balance  
  set @p_valorsb = 40
  -- Score Dossier  
  set @p_valordos = 4
  -- Score Informe comercial   
  set @p_valoric = 40
 end 

/*----------------------------------------------------------
CHEQUES RECHAZADOS SIN FONDOS 
----------------------------------------------------------*/
-- Vemos si tiene cosas definidas en ln_dias_alerta para riesgo con respecto a cheques:
if exists (select * from ln_dias_alertas where tabla = 'riesgo_cheques' and cliente = @Cliente and causal = 2)
begin 
 select @p_diasch   = dias, 
     @p_montoch  = monto, 
     @p_cantch   = cantidad,
     @levantados = levantados
     from ln_dias_alertas where tabla = 'riesgo_cheques' and cliente = @Cliente and causal = 2
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_cheques' and cliente = @Cliente and causal = 2))
begin 
 insert into @cheques
 select id_causa, fecha_rechazo, fecha_levantamiento, importe
 from ChequesBCRA 
    WHERE (Cuit_firmante = @cuit)
     AND (id_cheque = (select max(id_cheque) from chequesBCRA b 
                      where (b.cuit_firmante = chequesBCRA.cuit_firmante)
                       and (b.nro_cheque = chequesBCRA.nro_cheque)
                       and (b.visible = 1)))
        AND (id_causa in (1,2))
        AND (visible = 1)
     AND (
       (
        (
            (
                            (fecha_levantamiento IS NULL) 
                            AND (fecha_rechazo >= @p_5anios)
                        ) 
            OR 
            (
                (
                                (fecha_levantamiento >= @p_5anios) 
                                AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) <= 15)
                            ) 
                AND 
                (
                                (
                                    (fecha_pago IS NOT NULL) 
                                    AND (fecha_levantamiento >= (GETDATE() - 2))
                                ) 
                                OR 
                                (
                                    (
                                        (fecha_pago IS NULL) 
                                        AND (fecha_levantamiento >= (GETDATE() - 2))
                                    ) 
                                    AND (fecha_rechazo >= @p_5anios)
                                ) 
                ) 
            ) 
        ) 
        OR 
        (
            (
                            (fecha_levantamiento >= @p_5anios) 
                AND 
                            (
                                ((datediff(day,fecha_rechazo,fecha_levantamiento)) >= 16 ) 
                                AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) <= 90)
                            ) 
            ) 
            AND 
            (
                            (
                                (fecha_pago IS NOT NULL) 
                                AND (fecha_levantamiento >= @p_1anio)
                            )
                OR 
                            (
                                (
                                    (fecha_pago IS NULL) 
                                    AND (fecha_levantamiento >= @p_1anio)
                                ) 
                                AND 
                                (fecha_rechazo >= @p_5anios)
                            )
            ) 
        ) 
    )
    OR 
    (
        (
                        (
                            (fecha_levantamiento >= @p_5anios) 
                            AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) > 90)
                        ) 
                        AND 
                        (fecha_levantamiento >= @p_2anios) 
        ) 
        AND (fecha_rechazo >= @p_5anios) 
    )
   )

  -- Cantidad Cheques Sin Fondos 
  declare @p_total_dias int = 0 
  if (@p_diasch is not null and @levantados = 'N') 
  begin
     set @p_total_dias = (select count(*) from @cheques where causal = 2 and fecha_levantamiento is null and fecha_rechazo > GETDATE()-@p_diasch)
  end 
  if (@p_diasch is not null and @levantados is null) 
  begin
     set @p_total_dias = (select count(*) from @cheques where causal = 2 and fecha_rechazo > GETDATE()-@p_diasch)
  end 

  -- Montos Cheques Sin Fondos 
  declare @p_total_monto money = 0 
  if (@p_montoch is not null and @levantados = 'N') 
  begin
     set @p_total_monto = (select sum(importe) from @cheques where causal = 2 and fecha_levantamiento is null and fecha_rechazo > GETDATE()-@p_diasch)
  end 
  if (@p_diasch is not null and @levantados is null) 
  begin
     set @p_total_monto = (select sum(importe) from @cheques where causal = 2 and fecha_rechazo > GETDATE()-@p_diasch)
  end 


  if (@p_montoch is not null and @p_total_monto > @p_montoch) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque +
                'Condición 1 - Cheques rechazados Sin Fondos por más de ' + convert(varchar,@p_montoch) 
   --return  
  end 
  if (@p_diasch is not null and @p_total_dias > isnull(@p_cantch,0)) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque +
                 'Condición 1 -  Mas de ' + convert(varchar,@p_cantch) + 
                 ' cheques rechazados x Sin Fondos en los últimos ' + convert(varchar,@p_diasch) + 
        ' días'
   --return  
  end 
end 


/*----------------------------------------------------------
CENDEU:
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta para riesgo con respecto a cendeu:
if exists (select * from ln_dias_alertas where tabla = 'riesgo_cendeu' and cliente = @Cliente)
begin 

  select  @p_montodeu = monto, 
    @p_cantdeu = ISNULL(cantidad,0), 
    @p_diasdeu = dias, 
    @p_causaldeu = causal, 
    @p_levantados = levantados -- si dice 'N' es para que vea que no haya mejorado a 1 la situacion posteriormente
    from ln_dias_alertas where tabla = 'riesgo_cendeu' and cliente = @cliente

end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_cendeu' and cliente = @Cliente))
begin

  declare @p_meses  int = ((@p_diasdeu / 30) - 1) * (-1)
  declare @p_meses3 int = ((@p_diasdeu3 / 30) - 1) * (-1)

  declare @bancos table (fec_inf varchar(6), banco varchar(100), situacion int, deuda numeric(12,2))
  
  insert into @bancos 
         exec obtener_bancos_test @cuit
  declare @dtEnd date = (select max(fecha_informacion) from fuentes_procesadas where visible = '1' and fuente = 'cendeu')
  declare @dtEnd_cuit date = (select convert(date,isnull(max(fec_inf),'200112')+'01') from @bancos)
  if (@dtEnd_cuit > @dtEnd) set @dtEnd = @dtEnd_cuit
  /* Para saber si vino informado en los ultimos 3 meses */
  declare @ultimos_3_meses_date date = dateadd(MM,(-2),@dtEnd)
  declare @ultimos_3_meses varchar(6) = (convert(varchar(4),DATEPART(yyyy,@ultimos_3_meses_date))
           +
           right('0'+convert(varchar(2),DATEPART(mm,@ultimos_3_meses_date)),2))
  --select CAST(@ultimos_3_meses as int) 
  /* */ 
  declare @hoy date = dateadd(MM,@p_meses,@dtEnd) -- le resta 5, para tomar los ultimos 6 meses
  declare @xmeses2 varchar(6) = (convert(varchar(4),DATEPART(yyyy,@hoy))
          +
          right('0'+convert(varchar(2),DATEPART(mm,@hoy)),2))

  set @hoy = dateadd(MM,@p_meses3,@dtEnd) -- le resta 5, para tomar los ultimos 6 meses
  declare @xmeses3 varchar(6) = (convert(varchar(4),DATEPART(yyyy,@hoy))
           +
           right('0'+convert(varchar(2),DATEPART(mm,@hoy)),2))
  declare @cant_deu int = 0
  declare @monto_deu numeric(18,2) = 0

  if (@p_levantados is null or @p_levantados = 'S') 
  begin
   set @cant_deu  = (select COUNT(distinct banco) from @bancos where cast(fec_inf as int) >= cast(@xmeses2 as int) and situacion >= @p_causaldeu)
   set @monto_deu = (select sum(sbr.total*1000) from (select banco, max(deuda) as total from @bancos a where cast(a.fec_inf as int) >= cast(@xmeses2 as int) and a.situacion >= @p_causaldeu group by banco) sbr)
  end 
  else 
  begin 
   --> No Levantados ==> que no haya una situcion 1 posterior
   -->                   que haya deuda para ese banco en los ultimos 3 periodos 
     
   set @cant_deu  = (select COUNT(distinct banco) from @bancos bc where cast(bc.fec_inf as int) >= cast(@xmeses2 as int) and bc.situacion >= @p_causaldeu
                   and not exists (
       select * from @bancos bc2 where bc2.banco = bc.banco and cast(bc2.fec_inf as int) > cast(bc.fec_inf as int) and situacion = 1)
           and exists (
       select * from @bancos bc3 where bc3.banco = bc.banco and cast(bc3.fec_inf as int) >= cast(@ultimos_3_meses as int))
       )
   set @monto_deu = (select sum(sbr.total*1000) from (select banco, max(deuda) as total from @bancos a 
                                                     where cast(a.fec_inf as int) >= cast(@xmeses2 as int) and a.situacion >= @p_causaldeu
                 and not exists (
                 select * from @bancos bc2 where bc2.banco = a.banco and cast(bc2.fec_inf as int) > cast(a.fec_inf as int) and situacion = 1)
                 and exists (
                 select * from @bancos bc3 where bc3.banco = a.banco and cast(bc3.fec_inf as int) >= cast(@ultimos_3_meses as int))
                 group by banco
                 ) sbr)
  end  

  if (@p_montodeu is not null and @monto_deu > @p_montodeu) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque +
                'Condición 2 - Deudas por más de ' + CONVERT(varchar,@p_montodeu) 
   --return  
  end 
  if (@p_diasdeu is not null and @cant_deu > isnull(@p_cantdeu,0)) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque +
                 'Condición 2 - Central de deudores en Situación irregular'  
   --return  
  end 
end 


/*----------------------------------------------------------
JUDICIAL:
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta para riesgo con respecto a Pedidos de Quiebra:
if exists (select * from ln_dias_alertas where tabla = 'riesgo_pq' and cliente = @Cliente)
begin 
 select @p_diaspq = dias, 
     @p_cantpq = isnull(cantidad,1) 
   from ln_dias_alertas where tabla = 'riesgo_pq' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_pq' and cliente = @Cliente))
begin

   declare @cantidad_pq int = (SELECT COUNT(*) 
         FROM juicios 
         inner join tipos_causas on
              tipos_causas.id_tipo_causa = juicios.id_tipo_causa
         left join tiempo_causas on
             tiempo_causas.cau_codigo   = juicios.id_tipo_causa
         WHERE cuit_demandado = @cuit
         and juicios.visible = 1 and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365)
         and juicios.fecha_causa >= GETDATE() - isnull(@p_diaspq,5*365) 
         and tipos_causas.grupo = 'PQ')
  if (@cantidad_pq >= @p_cantpq) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque + 'Condición 3 - Pedidos de Quiebra en los últimos ' + CONVERT(varchar,@p_diaspq) + ' días'
   --return  
  end 
end 

-- Vemos si tiene cosas definidas en ln_dias_alerta para riesgo con respecto a Juicios:
if exists (select * from ln_dias_alertas where tabla = 'riesgo_jo' and cliente = @Cliente)
begin 
 select @p_diasjo = dias, 
     @p_cantjo = isnull(cantidad,1) 
   from ln_dias_alertas where tabla = 'riesgo_jo' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_jo' and cliente = @Cliente))
begin

   declare @cantidad_jo int = (SELECT COUNT(*) 
         FROM juicios 
         inner join tipos_causas on
              tipos_causas.id_tipo_causa = juicios.id_tipo_causa
         left join tiempo_causas on
             tiempo_causas.cau_codigo   = juicios.id_tipo_causa
         WHERE cuit_demandado = @cuit
         and juicios.visible = 1 and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365)
         and juicios.fecha_causa >= GETDATE() - isnull(@p_diaspq,5*365) 
         and tipos_causas.grupo = 'JO')
  if (@cantidad_jo >= @p_cantjo) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque + 'Condición 4 - Causas Juciales en los últimos ' + CONVERT(varchar, @p_diasjo) + ' días'
   --return  
  end 
end

/*----------------------------------------------------------
Rentablidad y Solvenvia 
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta para riesgo con respecto a Rentablidad y Solvencia:
declare @p_rentabilidad decimal(10,2)  
declare @p_solvencia decimal(10,2)  


if exists (select * from ln_dias_alertas where tabla = 'riesgo_rentablidad' and cliente = @Cliente)
begin 

 select @p_valorren = valor 
   from ln_dias_alertas where tabla = 'riesgo_rentablidad' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_rentablidad' and cliente = @Cliente))
begin

 select @p_rentabilidad = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador = 'RENTABILIDAD_OPERATIVA') 
end
else
begin
 set @p_rentabilidad = @p_valorren
end 

if exists (select * from ln_dias_alertas where tabla = 'riesgo_solvencia' and cliente = @Cliente)
begin 

 select @p_valorsol = valor 
   from ln_dias_alertas where tabla = 'riesgo_solvencia' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_solvencia' and cliente = @Cliente))
begin

 select @p_solvencia = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador = 'SOLVENCIA') 
end
else 
begin 
 set @p_solvencia = @p_valorsol
end 
   
  
if (@p_rentabilidad < @p_valorren and @p_solvencia < @p_valorsol) 
begin 
 set @enriesgo = 1
 if (@porque != '') set @porque = @porque + '; '
 set @porque = @porque + 'Condición 5 - Rentabilidad < ' + CONVERT(varchar, @p_valorren) + ' Y Solvencia < ' + CONVERT(varchar,@p_valorsol) 
 --return  
end 


/*----------------------------------------------------------
Score Balance:
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta para riesgo con respecto a SCORE_BAL:
if exists (select * from ln_dias_alertas where tabla = 'riesgo_score_bal' and cliente = @Cliente)
begin 

 select @p_valorsb = valor 
   from ln_dias_alertas where tabla = 'riesgo_score_bal' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_score_bal' and cliente = @Cliente))
begin

 declare @p_score_bal decimal(10,2)  
 select @p_score_bal = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador = 'SCORE_BAL') 
   
  
  if (@p_score_bal < @p_valorsb) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque + 'Condición 6 - Score Balance < ' + CONVERT(varchar, @p_valorsb)
   --return  
  end 
end


/*----------------------------------------------------------
Score Dossier:
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta para riesgo con respecto a Score Dossier:
if exists (select * from ln_dias_alertas where tabla = 'riesgo_score_dos' and cliente = @Cliente)
begin 

 select @p_valordos = valor 
   from ln_dias_alertas where tabla = 'riesgo_score_dos' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_score_dos' and cliente = @Cliente))
begin

 declare @p_score_dos decimal(10,2)  
 select @p_score_dos = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador = 'SCORE_DOSSIER') 
   
  
  if (@p_score_dos < @p_valordos) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque + 'Condición 7 - Score Dossier < ' + CONVERT(varchar, @p_valordos)
   --return  
  end 
end


/*----------------------------------------------------------
Score informe:
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta para riesgo con respecto a Informe:
if exists (select * from ln_dias_alertas where tabla = 'riesgo_score_ic' and cliente = @Cliente)
begin 

 select @p_valoric = valor 
   from ln_dias_alertas where tabla = 'riesgo_score_ic' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'riesgo_score_ic' and cliente = @Cliente))
begin

 declare @p_score_ic decimal(10,2)  
 select @p_score_ic = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador = 'SCORE_IC') 
   
  
  if (@p_score_ic < @p_valoric) 
  begin 
   set @enriesgo = 1
   if (@porque != '') set @porque = @porque + '; '
   set @porque = @porque + 'Condición 8 - Score Informe Comercial < ' + CONVERT(varchar, @p_valoric)
   --return  
  end 
end

if (@enriesgo = 0) 
begin
 if (@porque != '') set @porque = @porque + '; '
 set @porque = @porque + 'No cumple ninguna de las condiciones' 
end 

set @enriesgo1 = @enriesgo
set @porque1 = @porque


END

GO