Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
4| Name | Data Type | Length | Description |
|---|---|---|---|
| @id_cliente | int | 4 | |
| @fecha_proceso | date | 3 | |
| @todo_retomar | char | 1 | |
| @cuit_procesar | varchar | 20 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- exec sp_procesos_cuit_enrichment 452189004, '2017-09-28', 'T'
CREATE OR ALTER PROCEDURE dbo.sp_procesos_cuit_enrichment2
@id_cliente int,
@fecha_proceso date,
@todo_retomar char(1), -- 'T' procesar todo el lote - 'R' retomar lote ya empezado - 'I' individual
@cuit_procesar varchar(20) = null
AS
BEGIN
SET NOCOUNT ON;
declare @p_Cuit varchar(20)
declare @W_Fecha_Juicio date = convert(date, getdate())
set @W_Fecha_Juicio = dateadd(dd,-365,@W_Fecha_Juicio)
declare @W_PETICION int = 0
declare @W_PROVEIDO int = 0
declare @W_CONCLUIDO int = 0
declare @W_CYQ int = 0
declare @W_PEDIDO_QUIEBRA int = 0
declare @W_QUIEBRA int = 0
declare @W_JEV int = 0
declare @W_JEH int = 0
declare @W_JEC int= 0
declare @W_cant_judicial int = 0
declare @ult_mes_bcra char(7)
declare @ult_mes_bcra2 char(7)
declare @cant_bcra int = 0
declare @monto_bcra decimal(16,2)
declare @monto decimal(16,2)
declare @bcra_temp table (codigo_ent int)
declare @bcra_peor table (codigo_ent int, fec_inf varchar(6), situacion int, total decimal(16,2))
declare @p_codigo_ent int
declare @score decimal (5,2)
declare @motivos varchar(max)
declare @p_nombre varchar(200)
declare @p_nrodoc varchar(10)
declare @p_causa varchar(5)
declare @p_fecha_causa date
declare @p_5anios date = getdate() - (5*365)
declare @p_2anios date = getdate() - (2*365)
declare @p_1anio smalldatetime = getdate() - 365
declare @p_3meses date = getdate() - (90)
declare @dtStart date, @dtEnd date, @dtEnd_cuit date
declare @peor int
declare @mes_peor varchar(6)
declare @monto_peor_bcra decimal(16,2)
DECLARE @total_cheques_SL int = 0
declare @monto_cheques_SL decimal(16,2) = 0
DECLARE @total_cheques_L int = 0
declare @monto_cheques_L decimal(16,2) = 0
DECLARE @total_cheques_ua_SL int = 0
declare @monto_cheques_ua_SL decimal(16,2) = 0
DECLARE @total_cheques_ua_L int = 0
declare @monto_cheques_ua_L decimal(16,2) = 0
if (@todo_retomar = 'T')
begin
delete from procesos_cuit_enrichment
where cliente = @id_cliente
and fecha_proceso = @fecha_proceso
DECLARE recorre1 CURSOR
FOR
SELECT distinct cuit FROM procesos_cuit
where cliente = @id_cliente
and fecha = @fecha_proceso
ORDER BY cuit
end
if (@todo_retomar = 'R')
begin
DECLARE recorre1 CURSOR
FOR
SELECT distinct cuit FROM procesos_cuit
where cliente = @id_cliente
and fecha = @fecha_proceso
and cuit > (select max(cuit) from procesos_cuit_enrichment where cliente = @id_cliente and fecha_proceso = @fecha_proceso)
/*
and cuit in (
'30715609653',
'30715642553',
'33715640649'
)
*/
ORDER BY cuit
end
if (@todo_retomar = 'I')
begin
DECLARE recorre1 CURSOR
FOR
SELECT distinct cuit FROM procesos_cuit
where cliente = @id_cliente
and fecha = @fecha_proceso
and cuit = @cuit_procesar
end
OPEN recorre1
Fetch next from recorre1 INTO @p_cuit
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
set @W_PETICION = 0
set @W_PROVEIDO = 0
set @W_CONCLUIDO= 0
set @W_CYQ = 0
set @W_PEDIDO_QUIEBRA = 0
set @W_QUIEBRA = 0
set @W_JEV = 0
set @W_JEH = 0
set @W_JEC = 0
set @W_cant_judicial = 0
set @ult_mes_bcra = null
set @ult_mes_bcra2 = null
set @cant_bcra = 0
set @monto_bcra= 0
set @monto = 0
set @score = 0
set @motivos = null
set @ult_mes_bcra = (select max(fec_inf) from cendeu where iden = @p_cuit and vigente = 1)
set @ult_mes_bcra2 = (select max(fec_inf) from cendeu_temp where iden = @p_cuit and vigente = 1)
if ( @ult_mes_bcra2 > @ult_mes_bcra) begin set @ult_mes_bcra = @ult_mes_bcra2 end
delete from @bcra_temp
insert into @bcra_temp
select distinct CONVERT(int,codigo_ent)
from cendeu where iden = @p_cuit and fec_inf = @ult_mes_bcra and vigente = 1
union
select distinct CONVERT(int,codigo_ent)
from cendeu_temp where iden = @p_cuit and fec_inf = @ult_mes_bcra and vigente = 1
DECLARE recorre2 CURSOR FOR
SELECT distinct codigo_ent
FROM @bcra_temp
set @monto_bcra = 0
set @cant_bcra = 0
OPEN recorre2
Fetch next from recorre2 INTO @p_codigo_ent
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
set @cant_bcra = @cant_bcra + 1
set @monto = 0
set @monto = (select top 1 ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu_temp
where iden = @p_cuit and fec_inf = @ult_mes_bcra and vigente = 1 and convert(int,codigo_ent) = @p_codigo_ent
order by id_cendeu desc)
if (@monto = 0 or @monto is null)
begin
set @monto = (select top 1 ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu
where iden = @p_cuit and fec_inf = @ult_mes_bcra and vigente = 1 and convert(int,codigo_ent) = @p_codigo_ent
order by id_cendeu desc)
end
if (@monto is not null)
begin
set @monto_bcra = @monto_bcra + (@monto * 1000)
end
Fetch next from recorre2 INTO @p_codigo_ent
END
CLOSE recorre2
DEALLOCATE recorre2
if (@ult_mes_bcra is not null) begin set @ult_mes_bcra = SUBSTRING(@ult_mes_bcra,1,4) + '-' + SUBSTRING(@ult_mes_bcra,5,2) end
-- Peor situacion en los ultimos 24 meses
select @dtEnd = (select max(fecha_informacion) from fuentes_procesadas where fuente = 'cendeu' and visible = '1')
select @dtEnd_cuit = convert(date,isnull(max(cendeu.fec_inf),'200112')+'01') from cendeu where iden = @p_cuit
if (@dtEnd_cuit > @dtEnd)
begin
set @dtEnd = @dtEnd_cuit
end
set @dtStart = dateadd(month, (-23), @dtEnd)
delete from @bcra_peor
insert into @bcra_peor
select distinct CONVERT(int,codigo_ent),
fec_inf,
substring(convert(char(2),convert(int,situacion)),1,1) as situacion,
((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu
where iden = @p_cuit
and vigente = 1
and fec_inf in (select convert(varchar(4),datepart(year,dateadd(month, number, @dtStart)))
+ right('0' + rtrim(datepart(MONTH,dateadd(month, number, @dtStart))),2)
as periodo
from
(select distinct number from master.dbo.spt_values where name is null) n
where dateadd(month, number, @dtStart) <= @dtEnd)
and not exists (select * from cendeu b
where b.iden = cendeu.iden
and cast(b.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and b.fec_inf = cendeu.fec_inf
and b.vigente = cendeu.vigente
and (isnull(b.id_fuente,1)) = (isnull(cendeu.id_fuente,1))
and b.id_cendeu < cendeu.id_cendeu)
and not exists (select * from cendeu b1 where b1.iden = cendeu.iden and cast(b1.codigo_ent as int) = cast(cendeu.codigo_ent as int) and b1.fec_inf = cendeu.fec_inf and b1.vigente = 1 and b1.id_fuente = 2 and b1.id_cendeu != cendeu.id_cendeu)
and not (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
--not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
not exists (select * from cendeu z where z.iden = cendeu.iden and cast(z.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
exists (select * from cendeu_cancela c
where c.iden = cendeu.iden
and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
and c.vigente = '1'))
)
union
select distinct CONVERT(int,codigo_ent),
fec_inf,
substring(convert(char(2),convert(int,situacion)),1,1) as situacion,
((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu_temp
where iden = @p_cuit
and vigente = 1
and fec_inf in (select convert(varchar(4),datepart(year,dateadd(month, number, @dtStart)))
+ right('0' + rtrim(datepart(MONTH,dateadd(month, number, @dtStart))),2)
as periodo
from
(select distinct number from master.dbo.spt_values where name is null) n
where dateadd(month, number, @dtStart) <= @dtEnd)
and not exists (select * from cendeu_temp b
where b.iden = cendeu_temp.iden
and cast(b.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int)
and b.fec_inf = cendeu_temp.fec_inf
and b.vigente = cendeu_temp.vigente
and (isnull(b.id_fuente,1)) = (isnull(cendeu_temp.id_fuente,1))
and b.id_cendeu < cendeu_temp.id_cendeu)
and not exists (select * from cendeu_temp b1 where b1.iden = cendeu_temp.iden and cast(b1.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int) and b1.fec_inf = cendeu_temp.fec_inf and b1.vigente = 1 and b1.id_fuente = 2 and b1.id_cendeu != cendeu_temp.id_cendeu)
and not (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
--not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
not exists (select * from cendeu_temp z where z.iden = cendeu_temp.iden and cast(z.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
exists (select * from cendeu_cancela c
where c.iden = cendeu_temp.iden
and cast(c.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
and c.vigente = '1'))
)
set @peor = (select MAX(situacion) from @bcra_peor)
set @mes_peor = (select max(fec_inf) from @bcra_peor where situacion = @peor)
DECLARE recorre21 CURSOR FOR
SELECT distinct codigo_ent
FROM @bcra_peor
where situacion = @peor
and fec_inf = @mes_peor
set @monto_peor_bcra = 0
OPEN recorre21
Fetch next from recorre21 INTO @p_codigo_ent
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
set @monto = 0
set @monto = (select top 1 ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu_temp cendeu
where iden = @p_cuit
and fec_inf = @mes_peor
and substring(convert(char(2),convert(int,situacion)),1,1) = @peor
and vigente = 1 and convert(int,codigo_ent) = @p_codigo_ent
and not exists (select * from cendeu_temp b1 where b1.iden = cendeu.iden and cast(b1.codigo_ent as int) = cast(cendeu.codigo_ent as int) and b1.fec_inf = cendeu.fec_inf and b1.vigente = 1 and b1.id_fuente = 2 and b1.id_cendeu != cendeu.id_cendeu)
and not (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
--not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
not exists (select * from cendeu_temp z where z.iden = cendeu.iden and cast(z.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
exists (select * from cendeu_cancela c
where c.iden = cendeu.iden
and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
and c.vigente = '1'))
)
order by id_cendeu desc)
if (@monto = 0 or @monto is null)
begin
set @monto = (select top 1 ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu
where iden = @p_cuit
and fec_inf = @mes_peor
and substring(convert(char(2),convert(int,situacion)),1,1) = @peor
and vigente = 1 and convert(int,codigo_ent) = @p_codigo_ent
and not exists (select * from cendeu b1 where b1.iden = cendeu.iden and cast(b1.codigo_ent as int) = cast(cendeu.codigo_ent as int) and b1.fec_inf = cendeu.fec_inf and b1.vigente = 1 and b1.id_fuente = 2 and b1.id_cendeu != cendeu.id_cendeu)
and not (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
--not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
not exists (select * from cendeu z where z.iden = cendeu.iden and cast(z.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
exists (select * from cendeu_cancela c
where c.iden = cendeu.iden
and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
and c.vigente = '1'))
)
order by id_cendeu desc)
end
if (@monto is not null)
begin
set @monto_peor_bcra = @monto_peor_bcra + (@monto * 1000)
end
Fetch next from recorre21 INTO @p_codigo_ent
END
CLOSE recorre21
DEALLOCATE recorre21
-- Cheques sin fondo sin levantar años anteriores
select @total_cheques_SL = count(*) ,
@monto_cheques_SL = SUM(ChequesBCRA.importe)
from ChequesBCRA
where (cuit_firmante = @p_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 visible = 1
AND fecha_levantamiento is null
AND fecha_rechazo < @p_1anio
AND ChequesBCRA.id_causa = 2
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)
)
)
-- Cheques sin fondo levantados años anteriores
select @total_cheques_L = count(*) ,
@monto_cheques_L = SUM(ChequesBCRA.importe)
from ChequesBCRA
where (cuit_firmante = @p_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 visible = 1
AND fecha_levantamiento is not null
AND fecha_rechazo < @p_1anio
AND ChequesBCRA.id_causa = 2
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)
)
)
-- Cheques sin fondo sin levantar ultimo año
select @total_cheques_ua_SL = count(*) ,
@monto_cheques_ua_SL = SUM(ChequesBCRA.importe)
from ChequesBCRA
where (cuit_firmante = @p_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 visible = 1
AND fecha_levantamiento is null
AND fecha_rechazo >= @p_1anio
AND ChequesBCRA.id_causa = 2
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)
)
)
-- Cheques sin fondo levantados años anteriores
select @total_cheques_ua_L = count(*) ,
@monto_cheques_ua_L = SUM(ChequesBCRA.importe)
from ChequesBCRA
where (cuit_firmante = @p_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 visible = 1
AND fecha_levantamiento is not null
AND fecha_rechazo >= @p_1anio
AND ChequesBCRA.id_causa = 2
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)
)
)
-- select top 100 fecha_causa, cau_codigo, id_tipo_causa, * from juicios where id_tipo_causa is null
-- select count(*) from juicios where id_tipo_causa is null
-- select * from juicios where cuit_demandado = '30504732831'
DECLARE recorre3 CURSOR FOR
select distinct iif(juicios.id_tipo_causa is null, juicios.cau_codigo, juicios.id_tipo_causa) as id_tipo_causa, fecha_causa
FROM juicios
left join tiempo_causas on
tiempo_causas.cau_codigo = iif(juicios.id_tipo_causa is null, juicios.cau_codigo, juicios.id_tipo_causa)
WHERE cuit_demandado = @p_cuit
and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365)
OPEN recorre3
Fetch next from recorre3 INTO @p_causa, @p_fecha_causa
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
if (@p_causa = 'CP') begin set @W_PETICION = @W_PETICION + 1 end
if (@p_causa = 'CRC') begin set @W_PROVEIDO = @W_PROVEIDO + 1 end
if (@p_causa = 'CC2') begin set @W_CONCLUIDO = @W_CONCLUIDO + 1 end
if (@p_causa = 'PQ') begin set @W_PEDIDO_QUIEBRA = @W_PEDIDO_QUIEBRA + 1 end
if (@p_causa = 'QDC') begin set @W_QUIEBRA = @W_QUIEBRA + 1 end
if (@p_causa in ('PQ', 'AP', 'AU', 'CC2', 'NCE', 'NCP', 'CRC', 'CAP',
'DS', 'DF', 'EQ', 'MF', 'CP', 'PRQ', 'QUI', 'QC', 'QCC',
'QDC', 'QDF', 'QDZ', 'RJ', 'IHR', 'RCP', 'VC', 'LF', 'LJ'))
begin
set @W_CYQ = @W_CYQ + 1
end
if (@p_causa = 'JE' and @p_fecha_causa >= @W_Fecha_Juicio)
begin
set @W_JEV = @W_JEV + 1
end
if (@p_causa = 'EH' and @p_fecha_causa >= @W_Fecha_Juicio)
begin
set @W_JEH = @W_JEH + 1
end
if (@p_fecha_causa >= @W_Fecha_Juicio)
begin
set @W_cant_judicial = @W_cant_judicial + 1
end
Fetch next from recorre3 INTO @p_causa,@p_fecha_causa
END
CLOSE recorre3
DEALLOCATE recorre3
set @p_nombre = (select top 1 nombre_persona from padrones where identificacion = @p_cuit and habilitado = 1)
set @p_nrodoc = (select top 1 convert(int,cuerpo_clave) from padrones where identificacion = @p_cuit and habilitado = 1 and identificacion like '2%')
set @score = null
set @motivos = null
----------------
insert into procesos_cuit_enrichment
(Cliente,
Fecha_Proceso,
Cuit,
Nro_documento,
Nombre,
Peticion_Concurso_Preventivo,
Concurso_Preventivo_Proveido,
Concurso_Concluido,
Instancias_Concursos_y_Quiebras,
Pedidos_de_Quiebras,
Quiebras,
Juicios_Ejecutivos_Ultimo_Anio,
Juicios_por_Honorarios_Ultimo_Anio,
Causas_Judiciales_Ultimo_Anio,
Ultimo_Mes_BCRA,
Cant_Bancos_Ultimo_Mes_BCRA,
Monto_Total_Ultimo_Mes_BCRA,
Peor_situacion_Ultimos_24_meses,
Monto_Peor_Situacion_Ultimos_24_meses,
Cant_Cheques_Sin_Fondos_Sin_Levantar_Ultimo_Anio,
Monto_Cheques_Sin_Fondos_Sin_Levantar_Ultimo_Anio,
Cant_Cheques_Sin_Fondos_Levantados_Ultimo_Anio,
Monto_Cheques_Sin_Fondos_Levantados_Ultimo_Anio,
Cant_Cheques_Sin_Fondos_Sin_Levantar_Anios_Anteriores,
Monto_Cheqeus_Sin_Fondos_Sin_Levantar_Anios_Anteriores,
Cant_Cheques_Sin_Fondos_Levantados_Anios_Anteriores,
Monto_Cheques_Sin_Fondos_Levantados_Anios_Anteriores,
score,
motivos_score)
values
(@id_cliente,
@fecha_proceso,
@p_Cuit,
@p_nrodoc,
@p_nombre,
@W_PETICION,
@W_PROVEIDO,
@W_CONCLUIDO,
@W_CYQ,
@W_PEDIDO_QUIEBRA,
@W_QUIEBRA,
@W_JEV,
@W_JEH,
@W_cant_judicial,
@ult_mes_bcra,
@cant_bcra,
@monto_bcra,
@peor,
@monto_peor_bcra,
@total_cheques_ua_SL,
@monto_cheques_ua_SL,
@total_cheques_ua_L,
@monto_cheques_ua_L,
@total_cheques_SL,
@monto_cheques_SL,
@total_cheques_L,
@monto_cheques_L,
@score,
@motivos)
Fetch next from recorre1 INTO @p_cuit
END
CLOSE recorre1
DEALLOCATE recorre1
select *
from procesos_cuit_enrichment
where cliente = @id_cliente and fecha_proceso = @fecha_proceso
END
GO
GO
-- exec sp_procesos_cuit_enrichment 452189004, '2017-09-28', 'T'
CREATE OR ALTER PROCEDURE dbo.sp_procesos_cuit_enrichment2
@id_cliente int,
@fecha_proceso date,
@todo_retomar char(1), -- 'T' procesar todo el lote - 'R' retomar lote ya empezado - 'I' individual
@cuit_procesar varchar(20) = null
AS
BEGIN
SET NOCOUNT ON;
declare @p_Cuit varchar(20)
declare @W_Fecha_Juicio date = convert(date, getdate())
set @W_Fecha_Juicio = dateadd(dd,-365,@W_Fecha_Juicio)
declare @W_PETICION int = 0
declare @W_PROVEIDO int = 0
declare @W_CONCLUIDO int = 0
declare @W_CYQ int = 0
declare @W_PEDIDO_QUIEBRA int = 0
declare @W_QUIEBRA int = 0
declare @W_JEV int = 0
declare @W_JEH int = 0
declare @W_JEC int= 0
declare @W_cant_judicial int = 0
declare @ult_mes_bcra char(7)
declare @ult_mes_bcra2 char(7)
declare @cant_bcra int = 0
declare @monto_bcra decimal(16,2)
declare @monto decimal(16,2)
declare @bcra_temp table (codigo_ent int)
declare @bcra_peor table (codigo_ent int, fec_inf varchar(6), situacion int, total decimal(16,2))
declare @p_codigo_ent int
declare @score decimal (5,2)
declare @motivos varchar(max)
declare @p_nombre varchar(200)
declare @p_nrodoc varchar(10)
declare @p_causa varchar(5)
declare @p_fecha_causa date
declare @p_5anios date = getdate() - (5*365)
declare @p_2anios date = getdate() - (2*365)
declare @p_1anio smalldatetime = getdate() - 365
declare @p_3meses date = getdate() - (90)
declare @dtStart date, @dtEnd date, @dtEnd_cuit date
declare @peor int
declare @mes_peor varchar(6)
declare @monto_peor_bcra decimal(16,2)
DECLARE @total_cheques_SL int = 0
declare @monto_cheques_SL decimal(16,2) = 0
DECLARE @total_cheques_L int = 0
declare @monto_cheques_L decimal(16,2) = 0
DECLARE @total_cheques_ua_SL int = 0
declare @monto_cheques_ua_SL decimal(16,2) = 0
DECLARE @total_cheques_ua_L int = 0
declare @monto_cheques_ua_L decimal(16,2) = 0
if (@todo_retomar = 'T')
begin
delete from procesos_cuit_enrichment
where cliente = @id_cliente
and fecha_proceso = @fecha_proceso
DECLARE recorre1 CURSOR
FOR
SELECT distinct cuit FROM procesos_cuit
where cliente = @id_cliente
and fecha = @fecha_proceso
ORDER BY cuit
end
if (@todo_retomar = 'R')
begin
DECLARE recorre1 CURSOR
FOR
SELECT distinct cuit FROM procesos_cuit
where cliente = @id_cliente
and fecha = @fecha_proceso
and cuit > (select max(cuit) from procesos_cuit_enrichment where cliente = @id_cliente and fecha_proceso = @fecha_proceso)
/*
and cuit in (
'30715609653',
'30715642553',
'33715640649'
)
*/
ORDER BY cuit
end
if (@todo_retomar = 'I')
begin
DECLARE recorre1 CURSOR
FOR
SELECT distinct cuit FROM procesos_cuit
where cliente = @id_cliente
and fecha = @fecha_proceso
and cuit = @cuit_procesar
end
OPEN recorre1
Fetch next from recorre1 INTO @p_cuit
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
set @W_PETICION = 0
set @W_PROVEIDO = 0
set @W_CONCLUIDO= 0
set @W_CYQ = 0
set @W_PEDIDO_QUIEBRA = 0
set @W_QUIEBRA = 0
set @W_JEV = 0
set @W_JEH = 0
set @W_JEC = 0
set @W_cant_judicial = 0
set @ult_mes_bcra = null
set @ult_mes_bcra2 = null
set @cant_bcra = 0
set @monto_bcra= 0
set @monto = 0
set @score = 0
set @motivos = null
set @ult_mes_bcra = (select max(fec_inf) from cendeu where iden = @p_cuit and vigente = 1)
set @ult_mes_bcra2 = (select max(fec_inf) from cendeu_temp where iden = @p_cuit and vigente = 1)
if ( @ult_mes_bcra2 > @ult_mes_bcra) begin set @ult_mes_bcra = @ult_mes_bcra2 end
delete from @bcra_temp
insert into @bcra_temp
select distinct CONVERT(int,codigo_ent)
from cendeu where iden = @p_cuit and fec_inf = @ult_mes_bcra and vigente = 1
union
select distinct CONVERT(int,codigo_ent)
from cendeu_temp where iden = @p_cuit and fec_inf = @ult_mes_bcra and vigente = 1
DECLARE recorre2 CURSOR FOR
SELECT distinct codigo_ent
FROM @bcra_temp
set @monto_bcra = 0
set @cant_bcra = 0
OPEN recorre2
Fetch next from recorre2 INTO @p_codigo_ent
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
set @cant_bcra = @cant_bcra + 1
set @monto = 0
set @monto = (select top 1 ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu_temp
where iden = @p_cuit and fec_inf = @ult_mes_bcra and vigente = 1 and convert(int,codigo_ent) = @p_codigo_ent
order by id_cendeu desc)
if (@monto = 0 or @monto is null)
begin
set @monto = (select top 1 ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu
where iden = @p_cuit and fec_inf = @ult_mes_bcra and vigente = 1 and convert(int,codigo_ent) = @p_codigo_ent
order by id_cendeu desc)
end
if (@monto is not null)
begin
set @monto_bcra = @monto_bcra + (@monto * 1000)
end
Fetch next from recorre2 INTO @p_codigo_ent
END
CLOSE recorre2
DEALLOCATE recorre2
if (@ult_mes_bcra is not null) begin set @ult_mes_bcra = SUBSTRING(@ult_mes_bcra,1,4) + '-' + SUBSTRING(@ult_mes_bcra,5,2) end
-- Peor situacion en los ultimos 24 meses
select @dtEnd = (select max(fecha_informacion) from fuentes_procesadas where fuente = 'cendeu' and visible = '1')
select @dtEnd_cuit = convert(date,isnull(max(cendeu.fec_inf),'200112')+'01') from cendeu where iden = @p_cuit
if (@dtEnd_cuit > @dtEnd)
begin
set @dtEnd = @dtEnd_cuit
end
set @dtStart = dateadd(month, (-23), @dtEnd)
delete from @bcra_peor
insert into @bcra_peor
select distinct CONVERT(int,codigo_ent),
fec_inf,
substring(convert(char(2),convert(int,situacion)),1,1) as situacion,
((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu
where iden = @p_cuit
and vigente = 1
and fec_inf in (select convert(varchar(4),datepart(year,dateadd(month, number, @dtStart)))
+ right('0' + rtrim(datepart(MONTH,dateadd(month, number, @dtStart))),2)
as periodo
from
(select distinct number from master.dbo.spt_values where name is null) n
where dateadd(month, number, @dtStart) <= @dtEnd)
and not exists (select * from cendeu b
where b.iden = cendeu.iden
and cast(b.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and b.fec_inf = cendeu.fec_inf
and b.vigente = cendeu.vigente
and (isnull(b.id_fuente,1)) = (isnull(cendeu.id_fuente,1))
and b.id_cendeu < cendeu.id_cendeu)
and not exists (select * from cendeu b1 where b1.iden = cendeu.iden and cast(b1.codigo_ent as int) = cast(cendeu.codigo_ent as int) and b1.fec_inf = cendeu.fec_inf and b1.vigente = 1 and b1.id_fuente = 2 and b1.id_cendeu != cendeu.id_cendeu)
and not (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
--not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
not exists (select * from cendeu z where z.iden = cendeu.iden and cast(z.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
exists (select * from cendeu_cancela c
where c.iden = cendeu.iden
and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
and c.vigente = '1'))
)
union
select distinct CONVERT(int,codigo_ent),
fec_inf,
substring(convert(char(2),convert(int,situacion)),1,1) as situacion,
((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu_temp
where iden = @p_cuit
and vigente = 1
and fec_inf in (select convert(varchar(4),datepart(year,dateadd(month, number, @dtStart)))
+ right('0' + rtrim(datepart(MONTH,dateadd(month, number, @dtStart))),2)
as periodo
from
(select distinct number from master.dbo.spt_values where name is null) n
where dateadd(month, number, @dtStart) <= @dtEnd)
and not exists (select * from cendeu_temp b
where b.iden = cendeu_temp.iden
and cast(b.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int)
and b.fec_inf = cendeu_temp.fec_inf
and b.vigente = cendeu_temp.vigente
and (isnull(b.id_fuente,1)) = (isnull(cendeu_temp.id_fuente,1))
and b.id_cendeu < cendeu_temp.id_cendeu)
and not exists (select * from cendeu_temp b1 where b1.iden = cendeu_temp.iden and cast(b1.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int) and b1.fec_inf = cendeu_temp.fec_inf and b1.vigente = 1 and b1.id_fuente = 2 and b1.id_cendeu != cendeu_temp.id_cendeu)
and not (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
--not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
not exists (select * from cendeu_temp z where z.iden = cendeu_temp.iden and cast(z.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
exists (select * from cendeu_cancela c
where c.iden = cendeu_temp.iden
and cast(c.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
and c.vigente = '1'))
)
set @peor = (select MAX(situacion) from @bcra_peor)
set @mes_peor = (select max(fec_inf) from @bcra_peor where situacion = @peor)
DECLARE recorre21 CURSOR FOR
SELECT distinct codigo_ent
FROM @bcra_peor
where situacion = @peor
and fec_inf = @mes_peor
set @monto_peor_bcra = 0
OPEN recorre21
Fetch next from recorre21 INTO @p_codigo_ent
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
set @monto = 0
set @monto = (select top 1 ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu_temp cendeu
where iden = @p_cuit
and fec_inf = @mes_peor
and substring(convert(char(2),convert(int,situacion)),1,1) = @peor
and vigente = 1 and convert(int,codigo_ent) = @p_codigo_ent
and not exists (select * from cendeu_temp b1 where b1.iden = cendeu.iden and cast(b1.codigo_ent as int) = cast(cendeu.codigo_ent as int) and b1.fec_inf = cendeu.fec_inf and b1.vigente = 1 and b1.id_fuente = 2 and b1.id_cendeu != cendeu.id_cendeu)
and not (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
--not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
not exists (select * from cendeu_temp z where z.iden = cendeu.iden and cast(z.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
exists (select * from cendeu_cancela c
where c.iden = cendeu.iden
and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
and c.vigente = '1'))
)
order by id_cendeu desc)
if (@monto = 0 or @monto is null)
begin
set @monto = (select top 1 ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
from cendeu
where iden = @p_cuit
and fec_inf = @mes_peor
and substring(convert(char(2),convert(int,situacion)),1,1) = @peor
and vigente = 1 and convert(int,codigo_ent) = @p_codigo_ent
and not exists (select * from cendeu b1 where b1.iden = cendeu.iden and cast(b1.codigo_ent as int) = cast(cendeu.codigo_ent as int) and b1.fec_inf = cendeu.fec_inf and b1.vigente = 1 and b1.id_fuente = 2 and b1.id_cendeu != cendeu.id_cendeu)
and not (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
--not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
not exists (select * from cendeu z where z.iden = cendeu.iden and cast(z.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
exists (select * from cendeu_cancela c
where c.iden = cendeu.iden
and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
and c.vigente = '1'))
)
order by id_cendeu desc)
end
if (@monto is not null)
begin
set @monto_peor_bcra = @monto_peor_bcra + (@monto * 1000)
end
Fetch next from recorre21 INTO @p_codigo_ent
END
CLOSE recorre21
DEALLOCATE recorre21
-- Cheques sin fondo sin levantar años anteriores
select @total_cheques_SL = count(*) ,
@monto_cheques_SL = SUM(ChequesBCRA.importe)
from ChequesBCRA
where (cuit_firmante = @p_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 visible = 1
AND fecha_levantamiento is null
AND fecha_rechazo < @p_1anio
AND ChequesBCRA.id_causa = 2
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)
)
)
-- Cheques sin fondo levantados años anteriores
select @total_cheques_L = count(*) ,
@monto_cheques_L = SUM(ChequesBCRA.importe)
from ChequesBCRA
where (cuit_firmante = @p_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 visible = 1
AND fecha_levantamiento is not null
AND fecha_rechazo < @p_1anio
AND ChequesBCRA.id_causa = 2
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)
)
)
-- Cheques sin fondo sin levantar ultimo año
select @total_cheques_ua_SL = count(*) ,
@monto_cheques_ua_SL = SUM(ChequesBCRA.importe)
from ChequesBCRA
where (cuit_firmante = @p_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 visible = 1
AND fecha_levantamiento is null
AND fecha_rechazo >= @p_1anio
AND ChequesBCRA.id_causa = 2
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)
)
)
-- Cheques sin fondo levantados años anteriores
select @total_cheques_ua_L = count(*) ,
@monto_cheques_ua_L = SUM(ChequesBCRA.importe)
from ChequesBCRA
where (cuit_firmante = @p_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 visible = 1
AND fecha_levantamiento is not null
AND fecha_rechazo >= @p_1anio
AND ChequesBCRA.id_causa = 2
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)
)
)
-- select top 100 fecha_causa, cau_codigo, id_tipo_causa, * from juicios where id_tipo_causa is null
-- select count(*) from juicios where id_tipo_causa is null
-- select * from juicios where cuit_demandado = '30504732831'
DECLARE recorre3 CURSOR FOR
select distinct iif(juicios.id_tipo_causa is null, juicios.cau_codigo, juicios.id_tipo_causa) as id_tipo_causa, fecha_causa
FROM juicios
left join tiempo_causas on
tiempo_causas.cau_codigo = iif(juicios.id_tipo_causa is null, juicios.cau_codigo, juicios.id_tipo_causa)
WHERE cuit_demandado = @p_cuit
and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365)
OPEN recorre3
Fetch next from recorre3 INTO @p_causa, @p_fecha_causa
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
if (@p_causa = 'CP') begin set @W_PETICION = @W_PETICION + 1 end
if (@p_causa = 'CRC') begin set @W_PROVEIDO = @W_PROVEIDO + 1 end
if (@p_causa = 'CC2') begin set @W_CONCLUIDO = @W_CONCLUIDO + 1 end
if (@p_causa = 'PQ') begin set @W_PEDIDO_QUIEBRA = @W_PEDIDO_QUIEBRA + 1 end
if (@p_causa = 'QDC') begin set @W_QUIEBRA = @W_QUIEBRA + 1 end
if (@p_causa in ('PQ', 'AP', 'AU', 'CC2', 'NCE', 'NCP', 'CRC', 'CAP',
'DS', 'DF', 'EQ', 'MF', 'CP', 'PRQ', 'QUI', 'QC', 'QCC',
'QDC', 'QDF', 'QDZ', 'RJ', 'IHR', 'RCP', 'VC', 'LF', 'LJ'))
begin
set @W_CYQ = @W_CYQ + 1
end
if (@p_causa = 'JE' and @p_fecha_causa >= @W_Fecha_Juicio)
begin
set @W_JEV = @W_JEV + 1
end
if (@p_causa = 'EH' and @p_fecha_causa >= @W_Fecha_Juicio)
begin
set @W_JEH = @W_JEH + 1
end
if (@p_fecha_causa >= @W_Fecha_Juicio)
begin
set @W_cant_judicial = @W_cant_judicial + 1
end
Fetch next from recorre3 INTO @p_causa,@p_fecha_causa
END
CLOSE recorre3
DEALLOCATE recorre3
set @p_nombre = (select top 1 nombre_persona from padrones where identificacion = @p_cuit and habilitado = 1)
set @p_nrodoc = (select top 1 convert(int,cuerpo_clave) from padrones where identificacion = @p_cuit and habilitado = 1 and identificacion like '2%')
set @score = null
set @motivos = null
----------------
insert into procesos_cuit_enrichment
(Cliente,
Fecha_Proceso,
Cuit,
Nro_documento,
Nombre,
Peticion_Concurso_Preventivo,
Concurso_Preventivo_Proveido,
Concurso_Concluido,
Instancias_Concursos_y_Quiebras,
Pedidos_de_Quiebras,
Quiebras,
Juicios_Ejecutivos_Ultimo_Anio,
Juicios_por_Honorarios_Ultimo_Anio,
Causas_Judiciales_Ultimo_Anio,
Ultimo_Mes_BCRA,
Cant_Bancos_Ultimo_Mes_BCRA,
Monto_Total_Ultimo_Mes_BCRA,
Peor_situacion_Ultimos_24_meses,
Monto_Peor_Situacion_Ultimos_24_meses,
Cant_Cheques_Sin_Fondos_Sin_Levantar_Ultimo_Anio,
Monto_Cheques_Sin_Fondos_Sin_Levantar_Ultimo_Anio,
Cant_Cheques_Sin_Fondos_Levantados_Ultimo_Anio,
Monto_Cheques_Sin_Fondos_Levantados_Ultimo_Anio,
Cant_Cheques_Sin_Fondos_Sin_Levantar_Anios_Anteriores,
Monto_Cheqeus_Sin_Fondos_Sin_Levantar_Anios_Anteriores,
Cant_Cheques_Sin_Fondos_Levantados_Anios_Anteriores,
Monto_Cheques_Sin_Fondos_Levantados_Anios_Anteriores,
score,
motivos_score)
values
(@id_cliente,
@fecha_proceso,
@p_Cuit,
@p_nrodoc,
@p_nombre,
@W_PETICION,
@W_PROVEIDO,
@W_CONCLUIDO,
@W_CYQ,
@W_PEDIDO_QUIEBRA,
@W_QUIEBRA,
@W_JEV,
@W_JEH,
@W_cant_judicial,
@ult_mes_bcra,
@cant_bcra,
@monto_bcra,
@peor,
@monto_peor_bcra,
@total_cheques_ua_SL,
@monto_cheques_ua_SL,
@total_cheques_ua_L,
@monto_cheques_ua_L,
@total_cheques_SL,
@monto_cheques_SL,
@total_cheques_L,
@monto_cheques_L,
@score,
@motivos)
Fetch next from recorre1 INTO @p_cuit
END
CLOSE recorre1
DEALLOCATE recorre1
select *
from procesos_cuit_enrichment
where cliente = @id_cliente and fecha_proceso = @fecha_proceso
END
GO
Depends On
10Used By
No items found