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_SP
AS
BEGIN
/*obtenemos los empleados del cliente y lo insertamos en la tabla de screening empleados */
declare @IDCliente int = 99 /*5546348*/
select * from screening_empleados where cliente = @IDCliente
/*
INSERT INTO screening_empleados (nombre_empleado,cuil)
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)
*/
/*BUSCAMOS DATOS DE SOCIEDADES CONSTITUCIONES QUE FIGUREN A NOMBRE DE LOS EMPLEADOS DEL CLIENTE*/
/*
declare @IDCliente int = 5546348
*/
/* screening empleados primer paso sociedades en las cuales encontremos a los empleados*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,tipo_reg)
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,screening_empleados.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_1'
from screening_empleados inner join sociedades_constituciones on (screening_empleados.cuil = sociedades_constituciones.cuit_int
or screening_empleados.cuil = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where cliente = @IDCliente and cuil in (
select distinct cuit_int as cuit_sociedad from sociedades_constituciones where cuit_int in (
select screening_empleados.cuil from screening_empleados where cliente = @IDCliente )
) and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,screening_empleados.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,edictos.cuit_integrante as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion,
tipo_reg = 'SOC_CONS_1'
from screening_empleados inner join edictos on screening_empleados.cuil = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where cliente = @IDCliente and cuil in (
select distinct cuit_integrante as cuit_sociedad from edictos where cuit_integrante in (
select screening_empleados.cuil from screening_empleados where cliente = @IDCliente)
)
order by razon_social asc,nombre_integrante ASC
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select se.nombre_empleado as razon_social,se.cuil as cuit,se.cliente,getdate() as created,NULL as titulo,
cargos.car_nombre as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,se.nombre_empleado as nombre_integrante, NULL AS documento_integrante,NULL as nrdoc_int,se.cuil as cuit_int,se.cuil as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,1 as visible,'Socios' as tipo_reg
from screening_empleados se
inner join socios soc
on soc.c01_cuit_soc = se.cuil
inner join CARGOS on CARGOS.car_codigo = soc.car_codigo
inner join padrones on padrones.identificacion = c01_cuit and visible = 1
where se.cliente = @IDCliente
/* buscamos las empresas donde los empleados sean accionistas */
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select se.nombre_empleado as razon_social,se.cuil as cuit,se.cliente,getdate() as created,NULL as titulo,
'ACCIONISTA' as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,se.nombre_empleado as nombre_integrante, NULL AS documento_integrante,NULL as nrdoc_int,se.cuil as cuit_int,se.cuil as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,1 as visible,'Accio' as tipo_reg
from screening_empleados se
inner join accionistas acc
on acc.cuit_acc = se.cuil
inner join padrones on padrones.identificacion = cuit_sociedad and visible = 1
where se.cliente = @IDCliente
/* buscamos familiares de los empleados LOS DOS PRIMEROS CAMPOS DEBEN SER RAZON SOCIAL Y CUIT DEL CLIENTE DEL SCREENING
PORQUE PUEDE SER QUE NOS INFORME QUE EN OTRO MOMENTO EL QUE PAGABA LAS CARGAS SOCIALES ERA UN EMPLEADOR DIFERENTE Y A NOSOTROS
SOLO NOS INTERESA EL VÍNCULO DE FAMILIARIDAD
*/
/*
declare @IDCliente int = 5546348
*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,tipo_reg)
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_EMP'
from obras_sociales_beneficiarios OSB
inner join padrones P1 on '30538274832' = P1.identificacion /*OSB.CUITEMP*/
inner join padrones P2 on OSB.CUILBE = P2.identificacion
inner join padrones P3 on OSB.CUILTI = P3.identificacion
where (OSB.CUILTI in (select distinct cuil from screening_empleados where cliente = @IDCliente)
and OSB.CUILBE<>OSB.cuilti)
/* busco por segunda vez sociedades a partir de los cuit de personas que sean familiares de los empleados*/
/* busco de los familiares de los cuits ya procesados (2do nivel) que sean socios de alguna empresa*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct seo.nombre_integrante as razon_social,seo.cuit_int as cuit,null as cliente,getdate() as created,NULL as titulo,
cargos.car_nombre as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,seo.soc_vinculada as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,seo.cuit_soc_vinculada as cuit_int,seo.cuit_soc_vinculada as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'sociosf' as tipo_reg from screening_empleados_out seo
inner join socios soc
inner join cargos on cargos.car_codigo = soc.car_codigo
on soc.c01_cuit_soc = seo.cuit_int and tipo_reg = 'FAMIL_EMP'
inner join padrones on padrones.identificacion = soc.c01_cuit and soc.visible = 1
where seo.cliente = @IDCliente
/* busco de los familiares de los cuits ya procesados (2do nivel) que sean accionistas de alguna empresa*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct seo.nombre_integrante as razon_social,seo.cuit_int as cuit,null as cliente,getdate() as created,NULL as titulo,
'ACCIONISTAF'as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,seo.soc_vinculada as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,seo.cuit_soc_vinculada as cuit_int,seo.cuit_soc_vinculada as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'accionistasf' as tipo_reg from screening_empleados_out seo
inner join accionistas acc
on acc.cuit_acc = seo.cuit_int and tipo_reg = 'FAMIL_EMP'
inner join padrones on padrones.identificacion = acc.cuit_sociedad and acc.visible = 1
where seo.cliente = @IDCliente
/* busco de los familiares de los cuits ya procesados (2do nivel) que esten en soc_constituciones*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct seo.nombre_integrante as razon_social,seo.cuit_int as cuit,null as cliente,getdate() as created,NULL as titulo,
'SOCIEDADESF'as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,seo.soc_vinculada as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,seo.cuit_soc_vinculada as cuit_int,seo.cuit_soc_vinculada as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'sociedadesf' as tipo_reg from screening_empleados_out seo
inner join sociedades_constituciones sc
on (sc.cuit_int = seo.cuit_int or sc.cuit_integrante = seo.cuit_int ) and tipo_reg = 'FAMIL_EMP'
inner join padrones on padrones.identificacion = sc.documento_soc and sc.visible = 1
where seo.cliente = @IDCliente
/*Busco todos los empleados que posean mas de un empleador*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct 'INTERCARGO S A C' as razon_social,'30538274832' as cuit,
@IDCliente as cliente,getdate() as created,NULL as titulo,
NULL as cargo,
pe.nombre_persona as soc_vinculada,pe.identificacion as cuit_soc_vinculada,
/*
NULL as soc_vinculada,NULL as cuit_soc_vinculada,
*/
pp.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,pp.identificacion as cuit_int,pp.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'2EMPLEOS' as tipo_reg from empleados e
inner join padrones pe on pe.identificacion = e.cuit
inner join padrones pp on pp.identificacion = e.cuil
where e.cuil in(
select distinct(cuil) from screening_empleados where cliente = @IDCliente ) and periodo in(1704) and e.cuit != '30538274832'
union all
select distinct 'INTERCARGO S A C' as razon_social,'30538274832' as cuit,@IDCliente as cliente,getdate() as created,NULL as titulo,
NULL as cargo, pe.nombre_persona as soc_vinculada,pe.identificacion as cuit_soc_vinculada,pp.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,pp.identificacion as cuit_int,pp.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'2EMPLEOS' as tipo_reg from empleados_temp e
inner join padrones pe on pe.identificacion = e.cuit
inner join padrones pp on pp.identificacion = e.cuil
where e.cuil in(
select distinct(cuil) from screening_empleados where cliente = @IDCliente) and periodo in(1705) and e.cuit != '30538274832'
order by nombre_integrante asc
/*esto es donde almacenamos los empleados de intercargo , porque no pudimos utilizar distinct.*/
--insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg
from temp_intercargo_emp
/* screening empleados segundo paso sociedades en las cuales encontremos a los familiares*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,
documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,tipo_reg)
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,screening_empleados_out.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_empleados_out inner join sociedades_constituciones on (screening_empleados_out.cuit_int = sociedades_constituciones.cuit_int or screening_empleados_out.cuit_int = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where cliente = @IDCliente and screening_empleados_out.cuit_int in (
select distinct cuit_int as cuit_sociedad from sociedades_constituciones where cuit_int in (
select screening_empleados_out.cuit_int from screening_empleados_out where cliente = @IDCliente )
) and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,screening_empleados_out.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,edictos.cuit_integrante as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion,
tipo_reg = 'SOC_CONS_2'
from screening_empleados_out inner join edictos on screening_empleados_out.cuit_int = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where cliente = @IDCliente and cuit_int in (
select distinct cuit_integrante as cuit_sociedad from edictos where cuit_integrante in (
select screening_empleados_out.cuit_int from screening_empleados_out where cliente = @IDCliente)
)
order by 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_SP
AS
BEGIN
/*obtenemos los empleados del cliente y lo insertamos en la tabla de screening empleados */
declare @IDCliente int = 99 /*5546348*/
select * from screening_empleados where cliente = @IDCliente
/*
INSERT INTO screening_empleados (nombre_empleado,cuil)
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)
*/
/*BUSCAMOS DATOS DE SOCIEDADES CONSTITUCIONES QUE FIGUREN A NOMBRE DE LOS EMPLEADOS DEL CLIENTE*/
/*
declare @IDCliente int = 5546348
*/
/* screening empleados primer paso sociedades en las cuales encontremos a los empleados*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,tipo_reg)
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,screening_empleados.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_1'
from screening_empleados inner join sociedades_constituciones on (screening_empleados.cuil = sociedades_constituciones.cuit_int
or screening_empleados.cuil = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where cliente = @IDCliente and cuil in (
select distinct cuit_int as cuit_sociedad from sociedades_constituciones where cuit_int in (
select screening_empleados.cuil from screening_empleados where cliente = @IDCliente )
) and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,screening_empleados.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,edictos.cuit_integrante as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion,
tipo_reg = 'SOC_CONS_1'
from screening_empleados inner join edictos on screening_empleados.cuil = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where cliente = @IDCliente and cuil in (
select distinct cuit_integrante as cuit_sociedad from edictos where cuit_integrante in (
select screening_empleados.cuil from screening_empleados where cliente = @IDCliente)
)
order by razon_social asc,nombre_integrante ASC
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select se.nombre_empleado as razon_social,se.cuil as cuit,se.cliente,getdate() as created,NULL as titulo,
cargos.car_nombre as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,se.nombre_empleado as nombre_integrante, NULL AS documento_integrante,NULL as nrdoc_int,se.cuil as cuit_int,se.cuil as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,1 as visible,'Socios' as tipo_reg
from screening_empleados se
inner join socios soc
on soc.c01_cuit_soc = se.cuil
inner join CARGOS on CARGOS.car_codigo = soc.car_codigo
inner join padrones on padrones.identificacion = c01_cuit and visible = 1
where se.cliente = @IDCliente
/* buscamos las empresas donde los empleados sean accionistas */
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select se.nombre_empleado as razon_social,se.cuil as cuit,se.cliente,getdate() as created,NULL as titulo,
'ACCIONISTA' as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,se.nombre_empleado as nombre_integrante, NULL AS documento_integrante,NULL as nrdoc_int,se.cuil as cuit_int,se.cuil as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,1 as visible,'Accio' as tipo_reg
from screening_empleados se
inner join accionistas acc
on acc.cuit_acc = se.cuil
inner join padrones on padrones.identificacion = cuit_sociedad and visible = 1
where se.cliente = @IDCliente
/* buscamos familiares de los empleados LOS DOS PRIMEROS CAMPOS DEBEN SER RAZON SOCIAL Y CUIT DEL CLIENTE DEL SCREENING
PORQUE PUEDE SER QUE NOS INFORME QUE EN OTRO MOMENTO EL QUE PAGABA LAS CARGAS SOCIALES ERA UN EMPLEADOR DIFERENTE Y A NOSOTROS
SOLO NOS INTERESA EL VÍNCULO DE FAMILIARIDAD
*/
/*
declare @IDCliente int = 5546348
*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,tipo_reg)
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_EMP'
from obras_sociales_beneficiarios OSB
inner join padrones P1 on '30538274832' = P1.identificacion /*OSB.CUITEMP*/
inner join padrones P2 on OSB.CUILBE = P2.identificacion
inner join padrones P3 on OSB.CUILTI = P3.identificacion
where (OSB.CUILTI in (select distinct cuil from screening_empleados where cliente = @IDCliente)
and OSB.CUILBE<>OSB.cuilti)
/* busco por segunda vez sociedades a partir de los cuit de personas que sean familiares de los empleados*/
/* busco de los familiares de los cuits ya procesados (2do nivel) que sean socios de alguna empresa*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct seo.nombre_integrante as razon_social,seo.cuit_int as cuit,null as cliente,getdate() as created,NULL as titulo,
cargos.car_nombre as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,seo.soc_vinculada as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,seo.cuit_soc_vinculada as cuit_int,seo.cuit_soc_vinculada as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'sociosf' as tipo_reg from screening_empleados_out seo
inner join socios soc
inner join cargos on cargos.car_codigo = soc.car_codigo
on soc.c01_cuit_soc = seo.cuit_int and tipo_reg = 'FAMIL_EMP'
inner join padrones on padrones.identificacion = soc.c01_cuit and soc.visible = 1
where seo.cliente = @IDCliente
/* busco de los familiares de los cuits ya procesados (2do nivel) que sean accionistas de alguna empresa*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct seo.nombre_integrante as razon_social,seo.cuit_int as cuit,null as cliente,getdate() as created,NULL as titulo,
'ACCIONISTAF'as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,seo.soc_vinculada as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,seo.cuit_soc_vinculada as cuit_int,seo.cuit_soc_vinculada as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'accionistasf' as tipo_reg from screening_empleados_out seo
inner join accionistas acc
on acc.cuit_acc = seo.cuit_int and tipo_reg = 'FAMIL_EMP'
inner join padrones on padrones.identificacion = acc.cuit_sociedad and acc.visible = 1
where seo.cliente = @IDCliente
/* busco de los familiares de los cuits ya procesados (2do nivel) que esten en soc_constituciones*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct seo.nombre_integrante as razon_social,seo.cuit_int as cuit,null as cliente,getdate() as created,NULL as titulo,
'SOCIEDADESF'as cargo, padrones.nombre_persona as soc_vinculada,padrones.identificacion as cuit_soc_vinculada,seo.soc_vinculada as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,seo.cuit_soc_vinculada as cuit_int,seo.cuit_soc_vinculada as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'sociedadesf' as tipo_reg from screening_empleados_out seo
inner join sociedades_constituciones sc
on (sc.cuit_int = seo.cuit_int or sc.cuit_integrante = seo.cuit_int ) and tipo_reg = 'FAMIL_EMP'
inner join padrones on padrones.identificacion = sc.documento_soc and sc.visible = 1
where seo.cliente = @IDCliente
/*Busco todos los empleados que posean mas de un empleador*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct 'INTERCARGO S A C' as razon_social,'30538274832' as cuit,
@IDCliente as cliente,getdate() as created,NULL as titulo,
NULL as cargo,
pe.nombre_persona as soc_vinculada,pe.identificacion as cuit_soc_vinculada,
/*
NULL as soc_vinculada,NULL as cuit_soc_vinculada,
*/
pp.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,pp.identificacion as cuit_int,pp.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'2EMPLEOS' as tipo_reg from empleados e
inner join padrones pe on pe.identificacion = e.cuit
inner join padrones pp on pp.identificacion = e.cuil
where e.cuil in(
select distinct(cuil) from screening_empleados where cliente = @IDCliente ) and periodo in(1704) and e.cuit != '30538274832'
union all
select distinct 'INTERCARGO S A C' as razon_social,'30538274832' as cuit,@IDCliente as cliente,getdate() as created,NULL as titulo,
NULL as cargo, pe.nombre_persona as soc_vinculada,pe.identificacion as cuit_soc_vinculada,pp.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,pp.identificacion as cuit_int,pp.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'2EMPLEOS' as tipo_reg from empleados_temp e
inner join padrones pe on pe.identificacion = e.cuit
inner join padrones pp on pp.identificacion = e.cuil
where e.cuil in(
select distinct(cuil) from screening_empleados where cliente = @IDCliente) and periodo in(1705) and e.cuit != '30538274832'
order by nombre_integrante asc
/*esto es donde almacenamos los empleados de intercargo , porque no pudimos utilizar distinct.*/
--insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg)
select distinct razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg
from temp_intercargo_emp
/* screening empleados segundo paso sociedades en las cuales encontremos a los familiares*/
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,
documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,tipo_reg)
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,screening_empleados_out.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_empleados_out inner join sociedades_constituciones on (screening_empleados_out.cuit_int = sociedades_constituciones.cuit_int or screening_empleados_out.cuit_int = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where cliente = @IDCliente and screening_empleados_out.cuit_int in (
select distinct cuit_int as cuit_sociedad from sociedades_constituciones where cuit_int in (
select screening_empleados_out.cuit_int from screening_empleados_out where cliente = @IDCliente )
) and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,screening_empleados_out.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,edictos.cuit_integrante as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion,
tipo_reg = 'SOC_CONS_2'
from screening_empleados_out inner join edictos on screening_empleados_out.cuit_int = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where cliente = @IDCliente and cuit_int in (
select distinct cuit_integrante as cuit_sociedad from edictos where cuit_integrante in (
select screening_empleados_out.cuit_int from screening_empleados_out where cliente = @IDCliente)
)
order by 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