Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE OR ALTER PROCEDURE dbo.screening_empleados_dev
AS
BEGIN
/*obtenemos los empleados del cliente y lo insertamos en la tabla de screening empleados */
SELECT nombre_persona as nombre_empleado,identificacion as cuil from padrones where identificacion in (
select distinct cuil from empleados_temp where cuit = '30538274832'
and periodo = 1705 and cuil not in (select distinct cuil from empleados where cuit = '30538274832'
and periodo = 1704)
union all
select distinct cuil from empleados where cuit = '30538274832'
and periodo = 1704)
/* screening primer paso solo cuit que comienzan con 3*/
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion
from screening_prov inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where substring(cuit,1,1) = 3 AND cuit in (
select distinct documento_soc as cuit_sociedad from sociedades_constituciones where documento_soc in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 3)
) and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
from screening_prov inner join edictos on screening_prov.cuit = edictos.cuit_sociedad
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 3 AND cuit in (
select distinct cuit_sociedad as cuit_sociedad from edictos where cuit_sociedad in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 3)
)
union all
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
from screening_prov inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 3 AND cuit in (
select distinct cuit_sociedad as cuit_sociedad from edictos where cuit_sociedad in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 3)
)
union all
/* screening segundo paso solo cuit que comienzan con 2*/
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion
from screening_prov inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.cuit_int
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where substring(cuit,1,1) = 2 AND cuit in (
select distinct cuit_int from sociedades_constituciones where cuit_int in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 2)
)
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
from screening_prov inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 2 AND cuit in (
select distinct cuit_integrante from edictos where cuit_integrante in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 2)
)
order by screening_prov.razon_social asc,nombre_integrante ASC
/*
SEGUNDO NIVEL DE BUSQUEDA BASÁNDOSE EN LAS SOCIEDADES VINCULADAS
*/
select P2.nombre_persona AS razon_social,P2.identificacion AS cuit,NULL AS cliente,getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion,
tipo_reg = 'SOC_CONS_2'
from screening_prov_out inner join sociedades_constituciones on screening_prov_out.cuit_soc_vinculada = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov_out.cuit_soc_vinculada in (select DISTINCT cuit_soc_vinculada from screening_prov_out WHERE CUIT <> cuit_soc_vinculada)
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
NULL AS titulo, NULL AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,NULL AS documento_integrante,
NULL AS nrdoc_int,NULL AS cuit_int,EDICTOS.cuit_integrante,EDICTOS.texto AS contenido_archivo,EDICTOS.fecha_boletin AS fecha_publicacion,
tipo_reg = 'SOC_CONS_2'
from screening_prov_out inner join EDICTOS on screening_prov_out.cuit_soc_vinculada = edictos.cuit_sociedad
inner join padrones p2 on EDICTOS.cuit_integrante = p2.identificacion
inner join padrones p1 on EDICTOS.cuit_sociedad = p1.identificacion
where screening_prov_out.cuit_soc_vinculada in (select DISTINCT cuit_soc_vinculada from screening_prov_out WHERE CUIT <> cuit_soc_vinculada)
order by razon_social asc,nombre_integrante ASC
/* vemos de agotar los ciclos de sociedades vinculadas que ya no tenga como cuit provisto o detectado */
SELECT * FROM PADRONES WHERE identificacion IN (
select DISTINCT cuit_soc_vinculada from screening_prov_out
WHERE cuit_soc_vinculada not in (select distinct cuit from screening_prov_out))
/* buscar accionistas en tabla accionistas de cuit que no tengamos hasta este momento por sociedades constituciones */
select * from accionistas where cuit_sociedad in (select distinct cuit from screening_prov_out)
and cuit_acc not in (select cuit_int from screening_prov_out)
select * from accionistas where cuit_sociedad in (select distinct cuit from screening_prov_out)
and cuit_acc not in (select cuit_integrante from screening_prov_out)
/* buscar socios en tabla socios de cuit que no tengamos hasta este momento por sociedades constituciones*/
select * from socios where c01_cuit in (select distinct cuit from screening_prov_out) and c01_cuit_soc is not null and c01_cuit_soc <>''
and c01_cuit_soc not in (select cuit_int from screening_prov_out)
select * from socios where c01_cuit in (select distinct cuit from screening_prov_out) and c01_cuit_soc is not null and c01_cuit_soc <>''
and c01_cuit_soc not in (select cuit_integrante from screening_prov_out)
/* agregamos registros de empleos de los integrantes*/
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
'' as titulo, '' as cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo, null as fecha_publicacion,
tipo_reg = 'EMPLE_INT'
from empleados inner join PADRONES P1 on (empleados.cuit = p1.identificacion and empleados.periodo in (1703,1704,1705,1706))
inner join padrones p2 on (empleados.cuil = p2.identificacion and empleados.periodo in (1703,1704,1705,1706) )
where empleados.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> '' and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> '')
)
and empleados.periodo in (1703,1704,1705,1706) )
union all
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
'' as titulo, '' as cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'EMPLE_INT'
from empleados_temp EE inner join PADRONES P1 on (EE.cuit = p1.identificacion and EE.periodo in (1703,1704,1705,1706))
inner join padrones p2 on (EE.cuil = p2.identificacion and EE.periodo in (1703,1704,1705,1706) )
where EE.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> '' and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> '')
)
and EE.periodo in (1703,1704,1705,1706) )
order by p2.nombre_persona
/* buscamos familiares de los integrantes */
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
'' as titulo, '' as cargo,p3.nombre_persona as soc_vinculada,p3.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'FAMIL_INT'
from obras_sociales_beneficiarios OSB
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P2 on OSB.CUILBE = P2.identificacion
inner join padrones P3 on OSB.CUILTI = P3.identificacion
where (OSB.CUILTI in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''))
or OSB.CUILBE in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> '')))
and OSB.CUILBE<>OSB.cuilti
select * from obrassocial_activos where cuil_activo in ('23429818869','20251561320','30551720795')
select * from ObrasSociales where cuil_activo in ('23429818869','20251561320','30551720795')
select * from obras_sociales_beneficiarios
where (CUILTI in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''))
or CUILBE in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> '')))
and CUILBE<>cuilti
select * from obrassocial_activos where Cuil_Activo in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''))
/* screening TERCER CICLO DE BUSCANDO SOCIEDADES A PARTIR DE LOS CUIT INTEGRANTES*/
select P2.nombre_persona AS razon_social,P2.identificacion AS cuit,NULL AS cliente,getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion,
tipo_reg = 'INTEG_SOC_1'
from screening_prov_out inner join sociedades_constituciones on (screening_prov_out.cuit_integrante = sociedades_constituciones.cuit_int OR screening_prov_out.cuit_integrante = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out) OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out)
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
NULL AS titulo, NULL AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,NULL AS documento_integrante,
NULL AS nrdoc_int,NULL AS cuit_int,EDICTOS.cuit_integrante,EDICTOS.texto AS contenido_archivo,EDICTOS.fecha_boletin AS fecha_publicacion,
tipo_reg = 'INTEG_SOC_1'
from screening_prov_out inner join EDICTOS on (screening_prov_out.cuit_integrante = edictos.cuit_integrante OR screening_prov_out.cuit_int = edictos.cuit_integrante)
inner join padrones p2 on EDICTOS.cuit_integrante = p2.identificacion
inner join padrones p1 on EDICTOS.cuit_sociedad = p1.identificacion
where screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out) OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out)
/*
select * from screening_prov_out where cuit_integrante is null and cuit_int is not null and cuit_int <> ''
update screening_prov_out set cuit_integrante = cuit_int where cuit_integrante is null and cuit_int is not null and cuit_int <> ''
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from screening_prov_out
where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
select distinct id_screening_prov_out,nrdoc_int,cuit_int,cuit_integrante,nombre_integrante from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
order by nombre_integrante asc
select * from screening_prov_out
where nrdoc_int in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_integrante asc
select * from padrones where cuerpo_clave in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_persona asc
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from screening_prov_out where cuit_integrante is null AND documento_integrante IS NOT NULL ORDER BY documento_integrante DESC
UPDATE screening_prov_out SET nrdoc_int = NULL WHERE nrdoc_int = ''
UPDATE screening_prov_out SET documento_integrante = NULL WHERE documento_integrante = ''
UPDATE screening_prov_out SET cuit_int = NULL WHERE cuit_int = ''
select * from padrones where cuerpo_clave in (
select documento_integrante from screening_prov_out where cuit_integrante is null and documento_integrante IS NOT NULL)
order by nombre_persona asc
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select top 10 * from asignacion_cuits_sociedades_constituciones_log where documemto_limpio = '8456015'
select * from sociedades_constituciones where documento_soc='30711364710'
select * from empleados where cuit='33708800959'
select distinct screening_prov_out.nombre_integrante,cuit_int,padrones.identificacion,padrones.nombre_persona
from screening_prov_out inner join padrones on screening_prov_out.nombre_integrante = padrones.nombre_persona
inner join empleados on (empleados.cuit = screening_prov_out.cuit and empleados.periodo= 1704 and empleados.cuil = padrones.identificacion)
and cuit_integrante is null
select * from screening_prov_out where cuit_integrante is null and cuit_int is not null and cuit_int <> ''
update screening_prov_out set cuit_integrante = cuit_int where cuit_integrante is null and cuit_int is not null and cuit_int <> ''
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from screening_prov_out
where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
select distinct id_screening_prov_out,nrdoc_int,cuit_int,cuit_integrante,nombre_integrante from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from padrones where cuerpo_clave in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_persona asc
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from screening_prov_out where cuit_integrante is null AND documento_integrante IS NOT NULL ORDER BY documento_integrante DESC
select * from screening_prov_out
where nrdoc_int in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_integrante asc
select * from padrones where contains(*,'6482078')
update screening_prov_out set nombre_integrante='GLATSTEIN ARON',documento_integrante=2902140,cuit_int=20029021407,cuit_integrante=20029021407 where id_screening_prov_out=1881
update screening_prov_out set nombre_integrante='PELUDERO CARLOS HORACIO',documento_integrante=6636599,cuit_int=23066365999,cuit_integrante=23066365999 where id_screening_prov_out=1882
update screening_prov_out set nombre_integrante='TREBER SALVADOR',documento_integrante=6473098,cuit_int=20064730984,cuit_integrante=20064730984 where id_screening_prov_out=1883
update screening_prov_out set nombre_integrante='TREBER SALVADOR',documento_integrante=6473098,cuit_int=20064730984,cuit_integrante=20064730984 where id_screening_prov_out=1884
update screening_prov_out set nombre_integrante='GLATSTEIN DANIEL ADOLFO',documento_integrante=4986712,cuit_int=20049867124,cuit_integrante=20049867124 where id_screening_prov_out=1885
update screening_prov_out set nombre_integrante='GLATSTEIN JUAN OSCAR',documento_integrante=6489883,cuit_int=20064898834,cuit_integrante=20064898834 where id_screening_prov_out=1886
update screening_prov_out set nombre_integrante='CASTRO ROMEO',documento_integrante=7970023,cuit_int=20079700232,cuit_integrante=20079700232 where id_screening_prov_out=1887
update screening_prov_out set nombre_integrante='GLATSTEIN SANTIAGO',documento_integrante=6482078,cuit_int=20064820789,cuit_integrante=20064820789 where id_screening_prov_out=1888
UPDATE screening_prov_out SET nrdoc_int = NULL WHERE nrdoc_int = ''
UPDATE screening_prov_out SET documento_integrante = NULL WHERE documento_integrante = ''
UPDATE screening_prov_out SET cuit_int = NULL WHERE cuit_int = ''
select * from padrones where cuerpo_clave in (
select documento_integrante from screening_prov_out where cuit_integrante is null and documento_integrante IS NOT NULL)
order by nombre_persona asc
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select top 10 * from asignacion_cuits_sociedades_constituciones_log where documemto_limpio = '8456015'
select * from sociedades_constituciones where documento_soc='30711364710'
select * from empleados where cuit='33708800959'
select distinct screening_prov_out.nombre_integrante,cuit_int,padrones.identificacion,padrones.nombre_persona
from screening_prov_out inner join padrones on screening_prov_out.nombre_integrante = padrones.nombre_persona
inner join empleados on (empleados.cuit = screening_prov_out.cuit and empleados.periodo= 1704 and empleados.cuil = padrones.identificacion)
and cuit_integrante is null
update screening_prov_out set tipo_reg = 'SOC_CONS_1'
*/
END
GO
GO
CREATE OR ALTER PROCEDURE dbo.screening_empleados_dev
AS
BEGIN
/*obtenemos los empleados del cliente y lo insertamos en la tabla de screening empleados */
SELECT nombre_persona as nombre_empleado,identificacion as cuil from padrones where identificacion in (
select distinct cuil from empleados_temp where cuit = '30538274832'
and periodo = 1705 and cuil not in (select distinct cuil from empleados where cuit = '30538274832'
and periodo = 1704)
union all
select distinct cuil from empleados where cuit = '30538274832'
and periodo = 1704)
/* screening primer paso solo cuit que comienzan con 3*/
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion
from screening_prov inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where substring(cuit,1,1) = 3 AND cuit in (
select distinct documento_soc as cuit_sociedad from sociedades_constituciones where documento_soc in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 3)
) and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
from screening_prov inner join edictos on screening_prov.cuit = edictos.cuit_sociedad
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 3 AND cuit in (
select distinct cuit_sociedad as cuit_sociedad from edictos where cuit_sociedad in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 3)
)
union all
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
from screening_prov inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 3 AND cuit in (
select distinct cuit_sociedad as cuit_sociedad from edictos where cuit_sociedad in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 3)
)
union all
/* screening segundo paso solo cuit que comienzan con 2*/
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion
from screening_prov inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.cuit_int
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where substring(cuit,1,1) = 2 AND cuit in (
select distinct cuit_int from sociedades_constituciones where cuit_int in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 2)
)
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
select screening_prov.razon_social,screening_prov.cuit,screening_prov.cliente,getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
from screening_prov inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 2 AND cuit in (
select distinct cuit_integrante from edictos where cuit_integrante in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 2)
)
order by screening_prov.razon_social asc,nombre_integrante ASC
/*
SEGUNDO NIVEL DE BUSQUEDA BASÁNDOSE EN LAS SOCIEDADES VINCULADAS
*/
select P2.nombre_persona AS razon_social,P2.identificacion AS cuit,NULL AS cliente,getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion,
tipo_reg = 'SOC_CONS_2'
from screening_prov_out inner join sociedades_constituciones on screening_prov_out.cuit_soc_vinculada = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov_out.cuit_soc_vinculada in (select DISTINCT cuit_soc_vinculada from screening_prov_out WHERE CUIT <> cuit_soc_vinculada)
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
NULL AS titulo, NULL AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,NULL AS documento_integrante,
NULL AS nrdoc_int,NULL AS cuit_int,EDICTOS.cuit_integrante,EDICTOS.texto AS contenido_archivo,EDICTOS.fecha_boletin AS fecha_publicacion,
tipo_reg = 'SOC_CONS_2'
from screening_prov_out inner join EDICTOS on screening_prov_out.cuit_soc_vinculada = edictos.cuit_sociedad
inner join padrones p2 on EDICTOS.cuit_integrante = p2.identificacion
inner join padrones p1 on EDICTOS.cuit_sociedad = p1.identificacion
where screening_prov_out.cuit_soc_vinculada in (select DISTINCT cuit_soc_vinculada from screening_prov_out WHERE CUIT <> cuit_soc_vinculada)
order by razon_social asc,nombre_integrante ASC
/* vemos de agotar los ciclos de sociedades vinculadas que ya no tenga como cuit provisto o detectado */
SELECT * FROM PADRONES WHERE identificacion IN (
select DISTINCT cuit_soc_vinculada from screening_prov_out
WHERE cuit_soc_vinculada not in (select distinct cuit from screening_prov_out))
/* buscar accionistas en tabla accionistas de cuit que no tengamos hasta este momento por sociedades constituciones */
select * from accionistas where cuit_sociedad in (select distinct cuit from screening_prov_out)
and cuit_acc not in (select cuit_int from screening_prov_out)
select * from accionistas where cuit_sociedad in (select distinct cuit from screening_prov_out)
and cuit_acc not in (select cuit_integrante from screening_prov_out)
/* buscar socios en tabla socios de cuit que no tengamos hasta este momento por sociedades constituciones*/
select * from socios where c01_cuit in (select distinct cuit from screening_prov_out) and c01_cuit_soc is not null and c01_cuit_soc <>''
and c01_cuit_soc not in (select cuit_int from screening_prov_out)
select * from socios where c01_cuit in (select distinct cuit from screening_prov_out) and c01_cuit_soc is not null and c01_cuit_soc <>''
and c01_cuit_soc not in (select cuit_integrante from screening_prov_out)
/* agregamos registros de empleos de los integrantes*/
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
'' as titulo, '' as cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo, null as fecha_publicacion,
tipo_reg = 'EMPLE_INT'
from empleados inner join PADRONES P1 on (empleados.cuit = p1.identificacion and empleados.periodo in (1703,1704,1705,1706))
inner join padrones p2 on (empleados.cuil = p2.identificacion and empleados.periodo in (1703,1704,1705,1706) )
where empleados.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> '' and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> '')
)
and empleados.periodo in (1703,1704,1705,1706) )
union all
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
'' as titulo, '' as cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'EMPLE_INT'
from empleados_temp EE inner join PADRONES P1 on (EE.cuit = p1.identificacion and EE.periodo in (1703,1704,1705,1706))
inner join padrones p2 on (EE.cuil = p2.identificacion and EE.periodo in (1703,1704,1705,1706) )
where EE.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> '' and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> '')
)
and EE.periodo in (1703,1704,1705,1706) )
order by p2.nombre_persona
/* buscamos familiares de los integrantes */
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
'' as titulo, '' as cargo,p3.nombre_persona as soc_vinculada,p3.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'FAMIL_INT'
from obras_sociales_beneficiarios OSB
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P2 on OSB.CUILBE = P2.identificacion
inner join padrones P3 on OSB.CUILTI = P3.identificacion
where (OSB.CUILTI in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''))
or OSB.CUILBE in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> '')))
and OSB.CUILBE<>OSB.cuilti
select * from obrassocial_activos where cuil_activo in ('23429818869','20251561320','30551720795')
select * from ObrasSociales where cuil_activo in ('23429818869','20251561320','30551720795')
select * from obras_sociales_beneficiarios
where (CUILTI in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''))
or CUILBE in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> '')))
and CUILBE<>cuilti
select * from obrassocial_activos where Cuil_Activo in (select distinct cuit_int as cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cuit_int is not null and cuit_int <> ''))
/* screening TERCER CICLO DE BUSCANDO SOCIEDADES A PARTIR DE LOS CUIT INTEGRANTES*/
select P2.nombre_persona AS razon_social,P2.identificacion AS cuit,NULL AS cliente,getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion,
tipo_reg = 'INTEG_SOC_1'
from screening_prov_out inner join sociedades_constituciones on (screening_prov_out.cuit_integrante = sociedades_constituciones.cuit_int OR screening_prov_out.cuit_integrante = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out) OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out)
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,NULL AS cliente,getdate() as created,
NULL AS titulo, NULL AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,NULL AS documento_integrante,
NULL AS nrdoc_int,NULL AS cuit_int,EDICTOS.cuit_integrante,EDICTOS.texto AS contenido_archivo,EDICTOS.fecha_boletin AS fecha_publicacion,
tipo_reg = 'INTEG_SOC_1'
from screening_prov_out inner join EDICTOS on (screening_prov_out.cuit_integrante = edictos.cuit_integrante OR screening_prov_out.cuit_int = edictos.cuit_integrante)
inner join padrones p2 on EDICTOS.cuit_integrante = p2.identificacion
inner join padrones p1 on EDICTOS.cuit_sociedad = p1.identificacion
where screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out) OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out)
/*
select * from screening_prov_out where cuit_integrante is null and cuit_int is not null and cuit_int <> ''
update screening_prov_out set cuit_integrante = cuit_int where cuit_integrante is null and cuit_int is not null and cuit_int <> ''
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from screening_prov_out
where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
select distinct id_screening_prov_out,nrdoc_int,cuit_int,cuit_integrante,nombre_integrante from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
order by nombre_integrante asc
select * from screening_prov_out
where nrdoc_int in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_integrante asc
select * from padrones where cuerpo_clave in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_persona asc
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from screening_prov_out where cuit_integrante is null AND documento_integrante IS NOT NULL ORDER BY documento_integrante DESC
UPDATE screening_prov_out SET nrdoc_int = NULL WHERE nrdoc_int = ''
UPDATE screening_prov_out SET documento_integrante = NULL WHERE documento_integrante = ''
UPDATE screening_prov_out SET cuit_int = NULL WHERE cuit_int = ''
select * from padrones where cuerpo_clave in (
select documento_integrante from screening_prov_out where cuit_integrante is null and documento_integrante IS NOT NULL)
order by nombre_persona asc
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select top 10 * from asignacion_cuits_sociedades_constituciones_log where documemto_limpio = '8456015'
select * from sociedades_constituciones where documento_soc='30711364710'
select * from empleados where cuit='33708800959'
select distinct screening_prov_out.nombre_integrante,cuit_int,padrones.identificacion,padrones.nombre_persona
from screening_prov_out inner join padrones on screening_prov_out.nombre_integrante = padrones.nombre_persona
inner join empleados on (empleados.cuit = screening_prov_out.cuit and empleados.periodo= 1704 and empleados.cuil = padrones.identificacion)
and cuit_integrante is null
select * from screening_prov_out where cuit_integrante is null and cuit_int is not null and cuit_int <> ''
update screening_prov_out set cuit_integrante = cuit_int where cuit_integrante is null and cuit_int is not null and cuit_int <> ''
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from screening_prov_out
where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
select distinct id_screening_prov_out,nrdoc_int,cuit_int,cuit_integrante,nombre_integrante from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from padrones where cuerpo_clave in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_persona asc
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select * from screening_prov_out where cuit_integrante is null AND documento_integrante IS NOT NULL ORDER BY documento_integrante DESC
select * from screening_prov_out
where nrdoc_int in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_integrante asc
select * from padrones where contains(*,'6482078')
update screening_prov_out set nombre_integrante='GLATSTEIN ARON',documento_integrante=2902140,cuit_int=20029021407,cuit_integrante=20029021407 where id_screening_prov_out=1881
update screening_prov_out set nombre_integrante='PELUDERO CARLOS HORACIO',documento_integrante=6636599,cuit_int=23066365999,cuit_integrante=23066365999 where id_screening_prov_out=1882
update screening_prov_out set nombre_integrante='TREBER SALVADOR',documento_integrante=6473098,cuit_int=20064730984,cuit_integrante=20064730984 where id_screening_prov_out=1883
update screening_prov_out set nombre_integrante='TREBER SALVADOR',documento_integrante=6473098,cuit_int=20064730984,cuit_integrante=20064730984 where id_screening_prov_out=1884
update screening_prov_out set nombre_integrante='GLATSTEIN DANIEL ADOLFO',documento_integrante=4986712,cuit_int=20049867124,cuit_integrante=20049867124 where id_screening_prov_out=1885
update screening_prov_out set nombre_integrante='GLATSTEIN JUAN OSCAR',documento_integrante=6489883,cuit_int=20064898834,cuit_integrante=20064898834 where id_screening_prov_out=1886
update screening_prov_out set nombre_integrante='CASTRO ROMEO',documento_integrante=7970023,cuit_int=20079700232,cuit_integrante=20079700232 where id_screening_prov_out=1887
update screening_prov_out set nombre_integrante='GLATSTEIN SANTIAGO',documento_integrante=6482078,cuit_int=20064820789,cuit_integrante=20064820789 where id_screening_prov_out=1888
UPDATE screening_prov_out SET nrdoc_int = NULL WHERE nrdoc_int = ''
UPDATE screening_prov_out SET documento_integrante = NULL WHERE documento_integrante = ''
UPDATE screening_prov_out SET cuit_int = NULL WHERE cuit_int = ''
select * from padrones where cuerpo_clave in (
select documento_integrante from screening_prov_out where cuit_integrante is null and documento_integrante IS NOT NULL)
order by nombre_persona asc
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc
select top 10 * from asignacion_cuits_sociedades_constituciones_log where documemto_limpio = '8456015'
select * from sociedades_constituciones where documento_soc='30711364710'
select * from empleados where cuit='33708800959'
select distinct screening_prov_out.nombre_integrante,cuit_int,padrones.identificacion,padrones.nombre_persona
from screening_prov_out inner join padrones on screening_prov_out.nombre_integrante = padrones.nombre_persona
inner join empleados on (empleados.cuit = screening_prov_out.cuit and empleados.periodo= 1704 and empleados.cuil = padrones.identificacion)
and cuit_integrante is null
update screening_prov_out set tipo_reg = 'SOC_CONS_1'
*/
END
GO
Depends On
11Used By
No items found