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
CREATE OR ALTER PROCEDURE dbo.obtener_score_dev
@p_cuit varchar(20),
@p_fisica tinyint
AS
BEGIN
DECLARE @p_5anios smalldatetime = getdate() - 1825
DECLARE @p_2anios smalldatetime = getdate() - 730
DECLARE @p_1anio smalldatetime = getdate() - 365
declare @p_3meses date = getdate() - (90)
-- 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 '1' as grupo,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 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('JO','JE','PQ','CRC','PRQ','QDC')
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,clae.id_clae as id,fecha_inicio_actividad as fecha , DATEDIFF(year, convert(datetime,fecha_inicio_actividad),getdate()) as cantidades, 0 as cantidad,
null as relacionado
from clae where cuit = @p_cuit and fecha_inicio_actividad is not null and @p_fisica = 0
order by grupo,fecha,codigo
END
GO
GO
CREATE OR ALTER PROCEDURE dbo.obtener_score_dev
@p_cuit varchar(20),
@p_fisica tinyint
AS
BEGIN
DECLARE @p_5anios smalldatetime = getdate() - 1825
DECLARE @p_2anios smalldatetime = getdate() - 730
DECLARE @p_1anio smalldatetime = getdate() - 365
declare @p_3meses date = getdate() - (90)
-- 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 '1' as grupo,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 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('JO','JE','PQ','CRC','PRQ','QDC')
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,clae.id_clae as id,fecha_inicio_actividad as fecha , DATEDIFF(year, convert(datetime,fecha_inicio_actividad),getdate()) as cantidades, 0 as cantidad,
null as relacionado
from clae where cuit = @p_cuit and fecha_inicio_actividad is not null and @p_fisica = 0
order by grupo,fecha,codigo
END
GO
Depends On
12Used By
No items found