Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
2| Name | Data Type | Length | Description |
|---|---|---|---|
| @p_cuit | varchar | 20 | |
| @p_fisica | tinyint | 1 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
exec obtener_score '30711419272',0
*/
CREATE OR ALTER PROCEDURE dbo.obtener_score
@p_cuit varchar(20),
@p_fisica tinyint
AS
BEGIN
set @p_cuit = rtrim(ltrim(@p_cuit))
DECLARE @p_5anios smalldatetime = getdate() - 1825
DECLARE @p_2anios smalldatetime = getdate() - 730
DECLARE @p_1anio smalldatetime = getdate() - 365
declare @p_3meses date = getdate() - (90)
declare @fecha_inscripcion varchar(10) = (select min(fecha_inscripcion) from padron_impuestos
where id_padron = (select id_padron from padrones where identificacion = @p_cuit and habilitado = 1)
and fecha_inscripcion != '1901-01-01' and @p_fisica = 0)
-- G1 JUICIOS
select
'1' as grupo,
'ARBA' as codigo,
id_arba as id,
fecha_de_inicio_de_juicio as fecha,
0 as cantidades,
0 as cantidad,
null as relacionado
from arba where cuit = @p_cuit and visible = 1
AND (((((arba.fecha_de_inicio_de_juicio >= ( CONVERT(DATE,GETDATE()-1825) )) AND
(arba.estado_de_plan_de_pagos NOT IN ('2' ,'4' )) ) AND
(arba.cancelacion_del_juicio NOT IN ('S' ,'T' )) ) AND
(arba.fecha_de_cancelacion IS NULL ))
OR
((arba.fecha_de_inicio_de_juicio >= ( CONVERT(DATE,GETDATE()-730) )) AND
(((arba.estado_de_plan_de_pagos IN ('2' ,'4' ))
OR
(arba.cancelacion_del_juicio IN ('S' ,'T' )))
OR (arba.fecha_de_cancelacion IS NOT NULL ))))
union all
-- demandado
SELECT distinct '1' as grupo,IIF(tipos_causas.grupo_score = 'QUI','QDC',tipos_causas.grupo_score) as codigo,
null as id,
fecha_causa as fecha
, 0 as cantidades, 0 as cantidad,
null as relacionado
FROM juicios
left 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
left join padrones AS pf ON (pf.identificacion = cuit_actor and pf.habilitado = 1)
WHERE ( cuit_demandado = @p_cuit )
AND (juicios.visible = 1 and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365 ))
and
tiempo_causas.cau_codigo in(SELECT DISTINCT(id_tipo_causa) FROM TIPOS_CAUSAS)
/* SELECT '1' as grupo,IIF(tipos_causas.id_tipo_causa = 'QUI','QDC',tipos_causas.id_tipo_causa) as codigo,
id_juicio as id,
fecha_causa as fecha
, 0 as cantidades, 0 as cantidad,
null as relacionado
FROM juicios
left 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
left join padrones AS pf ON (pf.identificacion = cuit_actor)
WHERE ( cuit_demandado = @p_cuit )
AND (juicios.visible = 1 and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365 )) and tiempo_causas.cau_codigo in('JO','JE','PQ','CRC','PRQ','QDC','QUI')
*/
union all
-- G2 CHEQUES
select
'2' as grupo,
'CHEQUE_NL' as codigo,
ChequesBCRA.id_cheque as id,
ChequesBCRA.fecha_rechazo as fecha,
0 as cantidades,
0 as cantidad,
Cuit_relacionado as relacionado
from ChequesBCRA
WHERE ChequesBCRA.id_causa=2 and
(fecha_levantamiento IS NULL) and
(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_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)
)
)
union all
select
'2' as grupo,
'CHEQUE_L' as codigo ,
ChequesBCRA.id_cheque as id,
ChequesBCRA.fecha_levantamiento as fecha,
0 as cantidades,
0 as cantidad,
Cuit_relacionado as relacionado
from ChequesBCRA
WHERE ChequesBCRA.id_causa=2 and
(fecha_levantamiento IS NOT NULL) and
(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_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)
)
)
union all
--G3 Cendeu
select
'3' as grupo,
convert(varchar,convert(int,situacion)) as codigo,
cendeu.id_cendeu as id,
convert(DATE,fec_inf+'01') as fecha,
0 as cantidades,
0 as cantidad,
null as relacionado
from cendeu where cendeu.iden = @p_cuit and situacion > 1
and vigente = 1
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 = 1
/*and b.id_fuente = 2*/ and b.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 c where c.iden = cendeu.iden and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,c.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 all
select
'3' as grupo,
convert(varchar,convert(int,situacion)) as codigo,
cendeu.id_cendeu as id,
convert(DATE,fec_inf+'01') as fecha,
0 as cantidades,
0 as cantidad,
null as relacionado
from cendeu_temp as cendeu where cendeu.iden = @p_cuit and situacion > 1
and vigente = 1
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 = 1
/*and b.id_fuente = 2*/ and b.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 c where c.iden = cendeu.iden and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,c.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 all
--G7 COMEX
select '7' as grupo,'IMPO' as codigo ,1 as id,getdate() as fecha ,CAST(COUNT(DISTINCT(YEAR(fecha)+MONTH(fecha))) AS VARCHAR) as cantidades, 0 as cantidad,
null as relacionado
from comex_importaciones where cuit = @p_cuit and fecha between getdate()-365 and getdate()
UNION ALL
select '7' as grupo,'EXPO' as codigo ,1 as id,getdate() as fecha ,CAST(COUNT(DISTINCT(YEAR(fecha)+MONTH(fecha))) AS VARCHAR) as cantidades, 0 as cantidad,
null as relacionado
from comex_exportaciones where cuit = @p_cuit and fecha between getdate()-365 and getdate()
union all
--G8 Antigüedad
SELECT '8' as grupo,'ANTIGUEDAD' as codigo,padron_impuestos.id_impuestos as id,fecha_inscripcion as fecha , DATEDIFF(year, convert(datetime,fecha_inscripcion),getdate()) as cantidades, 0 as cantidad,
null as relacionado
from padron_impuestos
where id_padron = (select id_padron from padrones where identificacion = @p_cuit and habilitado = 1)
and fecha_inscripcion != '1901-01-01' and @p_fisica = 0 and fecha_inscripcion = @fecha_inscripcion
order by grupo,fecha,codigo
END
GO
GO
/*
exec obtener_score '30711419272',0
*/
CREATE OR ALTER PROCEDURE dbo.obtener_score
@p_cuit varchar(20),
@p_fisica tinyint
AS
BEGIN
set @p_cuit = rtrim(ltrim(@p_cuit))
DECLARE @p_5anios smalldatetime = getdate() - 1825
DECLARE @p_2anios smalldatetime = getdate() - 730
DECLARE @p_1anio smalldatetime = getdate() - 365
declare @p_3meses date = getdate() - (90)
declare @fecha_inscripcion varchar(10) = (select min(fecha_inscripcion) from padron_impuestos
where id_padron = (select id_padron from padrones where identificacion = @p_cuit and habilitado = 1)
and fecha_inscripcion != '1901-01-01' and @p_fisica = 0)
-- G1 JUICIOS
select
'1' as grupo,
'ARBA' as codigo,
id_arba as id,
fecha_de_inicio_de_juicio as fecha,
0 as cantidades,
0 as cantidad,
null as relacionado
from arba where cuit = @p_cuit and visible = 1
AND (((((arba.fecha_de_inicio_de_juicio >= ( CONVERT(DATE,GETDATE()-1825) )) AND
(arba.estado_de_plan_de_pagos NOT IN ('2' ,'4' )) ) AND
(arba.cancelacion_del_juicio NOT IN ('S' ,'T' )) ) AND
(arba.fecha_de_cancelacion IS NULL ))
OR
((arba.fecha_de_inicio_de_juicio >= ( CONVERT(DATE,GETDATE()-730) )) AND
(((arba.estado_de_plan_de_pagos IN ('2' ,'4' ))
OR
(arba.cancelacion_del_juicio IN ('S' ,'T' )))
OR (arba.fecha_de_cancelacion IS NOT NULL ))))
union all
-- demandado
SELECT distinct '1' as grupo,IIF(tipos_causas.grupo_score = 'QUI','QDC',tipos_causas.grupo_score) as codigo,
null as id,
fecha_causa as fecha
, 0 as cantidades, 0 as cantidad,
null as relacionado
FROM juicios
left 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
left join padrones AS pf ON (pf.identificacion = cuit_actor and pf.habilitado = 1)
WHERE ( cuit_demandado = @p_cuit )
AND (juicios.visible = 1 and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365 ))
and
tiempo_causas.cau_codigo in(SELECT DISTINCT(id_tipo_causa) FROM TIPOS_CAUSAS)
/* SELECT '1' as grupo,IIF(tipos_causas.id_tipo_causa = 'QUI','QDC',tipos_causas.id_tipo_causa) as codigo,
id_juicio as id,
fecha_causa as fecha
, 0 as cantidades, 0 as cantidad,
null as relacionado
FROM juicios
left 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
left join padrones AS pf ON (pf.identificacion = cuit_actor)
WHERE ( cuit_demandado = @p_cuit )
AND (juicios.visible = 1 and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365 )) and tiempo_causas.cau_codigo in('JO','JE','PQ','CRC','PRQ','QDC','QUI')
*/
union all
-- G2 CHEQUES
select
'2' as grupo,
'CHEQUE_NL' as codigo,
ChequesBCRA.id_cheque as id,
ChequesBCRA.fecha_rechazo as fecha,
0 as cantidades,
0 as cantidad,
Cuit_relacionado as relacionado
from ChequesBCRA
WHERE ChequesBCRA.id_causa=2 and
(fecha_levantamiento IS NULL) and
(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_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)
)
)
union all
select
'2' as grupo,
'CHEQUE_L' as codigo ,
ChequesBCRA.id_cheque as id,
ChequesBCRA.fecha_levantamiento as fecha,
0 as cantidades,
0 as cantidad,
Cuit_relacionado as relacionado
from ChequesBCRA
WHERE ChequesBCRA.id_causa=2 and
(fecha_levantamiento IS NOT NULL) and
(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_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)
)
)
union all
--G3 Cendeu
select
'3' as grupo,
convert(varchar,convert(int,situacion)) as codigo,
cendeu.id_cendeu as id,
convert(DATE,fec_inf+'01') as fecha,
0 as cantidades,
0 as cantidad,
null as relacionado
from cendeu where cendeu.iden = @p_cuit and situacion > 1
and vigente = 1
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 = 1
/*and b.id_fuente = 2*/ and b.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 c where c.iden = cendeu.iden and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,c.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 all
select
'3' as grupo,
convert(varchar,convert(int,situacion)) as codigo,
cendeu.id_cendeu as id,
convert(DATE,fec_inf+'01') as fecha,
0 as cantidades,
0 as cantidad,
null as relacionado
from cendeu_temp as cendeu where cendeu.iden = @p_cuit and situacion > 1
and vigente = 1
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 = 1
/*and b.id_fuente = 2*/ and b.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 c where c.iden = cendeu.iden and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,c.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 all
--G7 COMEX
select '7' as grupo,'IMPO' as codigo ,1 as id,getdate() as fecha ,CAST(COUNT(DISTINCT(YEAR(fecha)+MONTH(fecha))) AS VARCHAR) as cantidades, 0 as cantidad,
null as relacionado
from comex_importaciones where cuit = @p_cuit and fecha between getdate()-365 and getdate()
UNION ALL
select '7' as grupo,'EXPO' as codigo ,1 as id,getdate() as fecha ,CAST(COUNT(DISTINCT(YEAR(fecha)+MONTH(fecha))) AS VARCHAR) as cantidades, 0 as cantidad,
null as relacionado
from comex_exportaciones where cuit = @p_cuit and fecha between getdate()-365 and getdate()
union all
--G8 Antigüedad
SELECT '8' as grupo,'ANTIGUEDAD' as codigo,padron_impuestos.id_impuestos as id,fecha_inscripcion as fecha , DATEDIFF(year, convert(datetime,fecha_inscripcion),getdate()) as cantidades, 0 as cantidad,
null as relacionado
from padron_impuestos
where id_padron = (select id_padron from padrones where identificacion = @p_cuit and habilitado = 1)
and fecha_inscripcion != '1901-01-01' and @p_fisica = 0 and fecha_inscripcion = @fecha_inscripcion
order by grupo,fecha,codigo
END
GO
Depends On
12Used By
No items found