Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
1| Name | Data Type | Length | Description |
|---|---|---|---|
| @p_cuit | varchar | 12 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
[obtener_status_bancario] '30687288145'
*/
CREATE OR ALTER PROCEDURE dbo.obtener_status_bancario
@p_cuit varchar(12)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @p_5anios smalldatetime
DECLARE @p_2anios smalldatetime
DECLARE @p_1anio smalldatetime
declare @periodo_maximo date,@periodo_maximo_cuit date,@periodo_maximo_cuit2 date,@fecha_formateada varchar(10),@periodo_comparar varchar(6);
select @periodo_maximo = (select max(fecha_informacion) from fuentes_procesadas where visible = '1' and fuente ='cendeu')
select @periodo_maximo_cuit = convert(date,isnull(max(cendeu.fec_inf),'200112')+'01') from cendeu where iden = @p_cuit AND vigente = 1 and id_fuente = 2
select @periodo_maximo_cuit2 = convert(date,isnull(max(cendeu.fec_inf),'200112')+'01') from cendeu_temp cendeu where iden = @p_cuit AND vigente = 1 and id_fuente = 2
if @periodo_maximo_cuit2 > @periodo_maximo_cuit
begin
set @periodo_maximo_cuit = @periodo_maximo_cuit2
end
if (@periodo_maximo_cuit > @periodo_maximo)
begin
set @periodo_maximo = @periodo_maximo_cuit
end
set @p_5anios = getdate() - (5*365)
/*
05/04/2018:
Para que se vean en el grafico 2 años completos, se sacó la restriccion de los 2 años del dia de hoy, para deudas regularizadas.
declare @p_2anios date = getdate() - (2*365)
*/
set @p_2anios = getdate() - (5*365)
declare @p_3meses smalldatetime = getdate() - (90)
declare @cheques_rechazados bigint,@situaciones bigint,@ultima_posicion bigint,@max bigint,@fec_inf varchar(20),@situaciones_temp bigint,@max_temp bigint,@cheques_df bigint,@cheques_sf bigint
select @max_temp = isnull(max(subquery.maximo),0) from (select fec_inf,sum((prestamos+particip+garotor+o_cptos+deuda_total)*1000) as maximo
from cendeu_temp as cendeu
where iden = @p_cuit
and vigente =1
and convert(date,fec_inf+'01') > getdate() - 770
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
and not exists (select * from cendeu_temp 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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
group by fec_inf ) subquery
select @max = max(subquery.maximo) from (select fec_inf,sum((prestamos+particip+garotor+o_cptos+deuda_total)*1000) as maximo
from cendeu
where iden = @p_cuit
and vigente =1
and convert(date,fec_inf+'01') > getdate() - 770
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
group by fec_inf ) subquery
/*
select @fec_inf = (select top 1 fec_inf
from cendeu
where iden = @p_cuit
and vigente =1
and convert(date,fec_inf+'01') > getdate() - 770
and @max = (select max(subquery.maximo) from (select fec_inf,sum(prestamos+particip+garotor+o_cptos+deuda_total) as maximo
from cendeu
where iden = @p_cuit
and vigente =1
and convert(date,fec_inf+'01') > getdate() - 770
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
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 (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
group by fec_inf) subquery))*/
select @situaciones = count(situacion) from cendeu where iden = @p_cuit and situacion > 01 and vigente = 1
and convert(date,fec_inf+'01') > getdate() - 770
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
select @situaciones_temp = count(situacion) from cendeu_temp as cendeu
where iden = @p_cuit and situacion > 01 and vigente = 1
and convert(date,fec_inf+'01') > getdate() - 770
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
and not exists (select * from cendeu_temp 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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
set @situaciones = isnull(@situaciones,0) + isnull(@situaciones_temp,0)
set @max = iif(@max >= @max_temp,@max,@max_temp)
/*
select @ultima_posicion = isnull(sum(prestamos+particip+garotor+o_cptos+deuda_total),0) from cendeu where iden = @p_cuit and cendeu.vigente = 1 and fec_inf = (select max(fec_inf) from cendeu where iden = @p_cuit and cendeu.vigente = 1)
*/
declare @fec_inf_temp varchar(6) = null
set @fec_inf_temp = (select max(fec_inf) from cendeu_temp where iden = @p_cuit and cendeu_temp.vigente = 1 and convert(date,fec_inf+'01') > getdate() - 770)
declare @fec_inf_cendeu varchar(6) = null
set @fec_inf_cendeu = (select max(fec_inf) from cendeu where iden = @p_cuit and cendeu.vigente = 1 and convert(date,fec_inf+'01') > getdate() - 770)
if (isnull(@fec_inf_temp,'190001') > isnull(@fec_inf_cendeu,'190001'))
begin
select @ultima_posicion = isnull(sum((prestamos+particip+garotor+o_cptos+deuda_total)*1000),0)
from cendeu_temp cendeu
where iden = @p_cuit
and cendeu.vigente = 1
and fec_inf = (select max(fec_inf) from cendeu_temp where iden = @p_cuit and cendeu_temp.vigente = 1 and convert(date,fec_inf+'01') > getdate() - 770)
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
and not exists (select * from cendeu_temp 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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1')
))
end
else
begin
select @ultima_posicion = isnull(sum((prestamos+particip+garotor+o_cptos+deuda_total)*1000),0)
from cendeu
where iden = @p_cuit
and cendeu.vigente = 1
and fec_inf = (select max(fec_inf) from cendeu where iden = @p_cuit and cendeu.vigente = 1
and convert(date,fec_inf+'01') > getdate() - 770)
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
end
set @p_5anios = getdate() - 1825
set @p_2anios = getdate() - 730
set @p_1anio = getdate() - 365
--select @fec_inf = convert(varchar,convert(date,fec_inf+'01'),103) from cendeu where iden = @p_cuit and cendeu.vigente = 1 and convert(date,fec_inf+'01') > getdate() - 770
set @cheques_rechazados = (select count(*) 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_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)
)
)
)
set @cheques_df = (select count(*) 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 id_causa=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)
)
)
)
set @cheques_sf = (select count(*) 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 id_causa=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)
)
)
)
select top 1 @cheques_rechazados as cheques_rechazados,@situaciones as situaciones,@ultima_posicion as ultima_posicion, @max as maxima_posicion,@cheques_df as cheques_df,@cheques_sf as cheques_sf/*,@fec_inf as fec_inf*/
END
GO
GO
/*
[obtener_status_bancario] '30687288145'
*/
CREATE OR ALTER PROCEDURE dbo.obtener_status_bancario
@p_cuit varchar(12)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @p_5anios smalldatetime
DECLARE @p_2anios smalldatetime
DECLARE @p_1anio smalldatetime
declare @periodo_maximo date,@periodo_maximo_cuit date,@periodo_maximo_cuit2 date,@fecha_formateada varchar(10),@periodo_comparar varchar(6);
select @periodo_maximo = (select max(fecha_informacion) from fuentes_procesadas where visible = '1' and fuente ='cendeu')
select @periodo_maximo_cuit = convert(date,isnull(max(cendeu.fec_inf),'200112')+'01') from cendeu where iden = @p_cuit AND vigente = 1 and id_fuente = 2
select @periodo_maximo_cuit2 = convert(date,isnull(max(cendeu.fec_inf),'200112')+'01') from cendeu_temp cendeu where iden = @p_cuit AND vigente = 1 and id_fuente = 2
if @periodo_maximo_cuit2 > @periodo_maximo_cuit
begin
set @periodo_maximo_cuit = @periodo_maximo_cuit2
end
if (@periodo_maximo_cuit > @periodo_maximo)
begin
set @periodo_maximo = @periodo_maximo_cuit
end
set @p_5anios = getdate() - (5*365)
/*
05/04/2018:
Para que se vean en el grafico 2 años completos, se sacó la restriccion de los 2 años del dia de hoy, para deudas regularizadas.
declare @p_2anios date = getdate() - (2*365)
*/
set @p_2anios = getdate() - (5*365)
declare @p_3meses smalldatetime = getdate() - (90)
declare @cheques_rechazados bigint,@situaciones bigint,@ultima_posicion bigint,@max bigint,@fec_inf varchar(20),@situaciones_temp bigint,@max_temp bigint,@cheques_df bigint,@cheques_sf bigint
select @max_temp = isnull(max(subquery.maximo),0) from (select fec_inf,sum((prestamos+particip+garotor+o_cptos+deuda_total)*1000) as maximo
from cendeu_temp as cendeu
where iden = @p_cuit
and vigente =1
and convert(date,fec_inf+'01') > getdate() - 770
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
and not exists (select * from cendeu_temp 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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
group by fec_inf ) subquery
select @max = max(subquery.maximo) from (select fec_inf,sum((prestamos+particip+garotor+o_cptos+deuda_total)*1000) as maximo
from cendeu
where iden = @p_cuit
and vigente =1
and convert(date,fec_inf+'01') > getdate() - 770
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
group by fec_inf ) subquery
/*
select @fec_inf = (select top 1 fec_inf
from cendeu
where iden = @p_cuit
and vigente =1
and convert(date,fec_inf+'01') > getdate() - 770
and @max = (select max(subquery.maximo) from (select fec_inf,sum(prestamos+particip+garotor+o_cptos+deuda_total) as maximo
from cendeu
where iden = @p_cuit
and vigente =1
and convert(date,fec_inf+'01') > getdate() - 770
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
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 (
(cast(fec_inf+'01' as date) < @p_5anios)
OR
(cast(fec_inf+'01' as date) < @p_2anios
and
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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
group by fec_inf) subquery))*/
select @situaciones = count(situacion) from cendeu where iden = @p_cuit and situacion > 01 and vigente = 1
and convert(date,fec_inf+'01') > getdate() - 770
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
select @situaciones_temp = count(situacion) from cendeu_temp as cendeu
where iden = @p_cuit and situacion > 01 and vigente = 1
and convert(date,fec_inf+'01') > getdate() - 770
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
and not exists (select * from cendeu_temp 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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
set @situaciones = isnull(@situaciones,0) + isnull(@situaciones_temp,0)
set @max = iif(@max >= @max_temp,@max,@max_temp)
/*
select @ultima_posicion = isnull(sum(prestamos+particip+garotor+o_cptos+deuda_total),0) from cendeu where iden = @p_cuit and cendeu.vigente = 1 and fec_inf = (select max(fec_inf) from cendeu where iden = @p_cuit and cendeu.vigente = 1)
*/
declare @fec_inf_temp varchar(6) = null
set @fec_inf_temp = (select max(fec_inf) from cendeu_temp where iden = @p_cuit and cendeu_temp.vigente = 1 and convert(date,fec_inf+'01') > getdate() - 770)
declare @fec_inf_cendeu varchar(6) = null
set @fec_inf_cendeu = (select max(fec_inf) from cendeu where iden = @p_cuit and cendeu.vigente = 1 and convert(date,fec_inf+'01') > getdate() - 770)
if (isnull(@fec_inf_temp,'190001') > isnull(@fec_inf_cendeu,'190001'))
begin
select @ultima_posicion = isnull(sum((prestamos+particip+garotor+o_cptos+deuda_total)*1000),0)
from cendeu_temp cendeu
where iden = @p_cuit
and cendeu.vigente = 1
and fec_inf = (select max(fec_inf) from cendeu_temp where iden = @p_cuit and cendeu_temp.vigente = 1 and convert(date,fec_inf+'01') > getdate() - 770)
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
and not exists (select * from cendeu_temp 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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1')
))
end
else
begin
select @ultima_posicion = isnull(sum((prestamos+particip+garotor+o_cptos+deuda_total)*1000),0)
from cendeu
where iden = @p_cuit
and cendeu.vigente = 1
and fec_inf = (select max(fec_inf) from cendeu where iden = @p_cuit and cendeu.vigente = 1
and convert(date,fec_inf+'01') > getdate() - 770)
and convert(date,fec_inf+'01') <= @periodo_maximo
and 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 c.fec_inf = cendeu.fec_inf
and c.vigente = 1
and c.id_fuente = 2
and c.id_cendeu != cendeu.id_cendeu)
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 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 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 d
where d.iden = cendeu.iden
and cast(d.codigo_ent as int) = cast(cendeu.codigo_ent as int)
and cast(fec_inf+'01' as date) between convert(date,d.fec_inf_desde) and convert(date,d.fec_inf_hasta)
and d.vigente = '1'))
)
end
set @p_5anios = getdate() - 1825
set @p_2anios = getdate() - 730
set @p_1anio = getdate() - 365
--select @fec_inf = convert(varchar,convert(date,fec_inf+'01'),103) from cendeu where iden = @p_cuit and cendeu.vigente = 1 and convert(date,fec_inf+'01') > getdate() - 770
set @cheques_rechazados = (select count(*) 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_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)
)
)
)
set @cheques_df = (select count(*) 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 id_causa=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)
)
)
)
set @cheques_sf = (select count(*) 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 id_causa=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)
)
)
)
select top 1 @cheques_rechazados as cheques_rechazados,@situaciones as situaciones,@ultima_posicion as ultima_posicion, @max as maxima_posicion,@cheques_df as cheques_df,@cheques_sf as cheques_sf/*,@fec_inf as fec_inf*/
END
GO
Used By
No items found