dbo.screening_empleados_new

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE OR ALTER PROCEDURE dbo.screening_empleados_new

AS

BEGIN




/*obtenemos los empleados del cliente y lo insertamos en la tabla de screening empleados */

/*BUSCAMOS DATOS DE SOCIEDADES CONSTITUCIONES QUE FIGUREN A NOMBRE DE LOS EMPLEADOS DEL CLIENTE*/

declare @IDCliente int = 843015515 /* 843015515 */ /* 5546348 */

/*
delete screening_empleados_out where cliente = @IDCliente
*/

/* 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,
tp1.relacion as titulo, tsp1.subrelacion as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,
p3.nombre_persona as nombre_integrante,p3.identificacion as documento_integrante,
p3.cuerpo_clave as nrdoc_int,
p3.identificacion as cuit_int,p3.identificacion as cuit_integrante,
null as contenido_archivo,relaciones.fecha_relacion
,tipo_reg = 'SOC_CONS_1'
from screening_empleados 
inner join relaciones on screening_empleados.cuil = relaciones.cuitb
inner join padrones p2 on relaciones.cuita = p2.identificacion 
inner join padrones p3 on relaciones.cuitb = p3.identificacion 
inner join tipos_relaciones tp1 on tp1.id_tipo_relacion = relaciones.id_tipo_relacion
inner join tipos_subrelaciones tsp1 on tsp1.id_subrelacion = relaciones.id_tipo_subrelacion

where cliente =  @IDCliente and relaciones.id_tipo_relacion = 1
union all
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 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 
order by razon_social asc,nombre_integrante ASC


--declare @IDCliente int = 843015515 /* 843015515 */ /* 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,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 

--declare @IDCliente int = 843015515 /* 5546348 */

/*  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,'ACCIONISTA' 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 


--declare @IDCliente int = 843015515 /* 5546348 */
/* Buscamos empleos */

declare @ultimo_mes_empleados date,
     @mes_empleados date,
  @periodo_empleados_desde  int,
  @periodo_empleados_hasta int,
  @cantidad int

set @ultimo_mes_empleados = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
set @mes_empleados = DATEADD(M,-2,@ultimo_mes_empleados)
set @periodo_empleados_desde = convert(int,substring(convert(char,@mes_empleados),3,2)+substring(convert(char,@mes_empleados),6,2))
set @periodo_empleados_hasta = convert(int,substring(convert(char,@ultimo_mes_empleados),3,2)+substring(convert(char,@ultimo_mes_empleados),6,2))

set @ultimo_mes_empleados = '2017-12-01'
set @mes_empleados = '2017-08-01'
set @periodo_empleados_desde = 1708
set @periodo_empleados_hasta = 1712

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 se.nombre_empleado as razon_social,se.cuil as cuit,
se.cliente,getdate() as created,
NULL as titulo,'' 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,
'2EMPLEO' as tipo_reg  

from screening_empleados se

inner join empleados on (empleados.cuil = se.cuil and periodo between @periodo_empleados_desde and @periodo_empleados_hasta  
                        and empleados.cuit not in (select cuit from clientes where cliente = @IDCliente))
inner join PADRONES  on padrones.identificacion = empleados.cuit 
where se.cliente = @IDCliente

union all
select distinct se.nombre_empleado as razon_social,se.cuil as cuit,
se.cliente,getdate() as created,
NULL as titulo,'' 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,
'2EMPLEO' as tipo_reg  

from screening_empleados se

inner join empleados_temp on (empleados_temp.cuil = se.cuil and periodo between @periodo_empleados_desde and @periodo_empleados_hasta  
                        and empleados_temp.cuit not in (select cuit from clientes where cliente = @IDCliente))
inner join PADRONES  on padrones.identificacion = empleados_temp.cuit 
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 '30504163543'  = 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)
*/
/*
declare @IDCliente int = 843015515 /* 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,screening_empleados.cliente 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 screening_empleados
inner join obras_sociales_beneficiarios OSB on OSB.CUILTI = screening_empleados.cuil
inner join padrones P1 on P1.identificacion = OSB.CUITEMP
inner join padrones P2 on OSB.CUILBE = P2.identificacion
inner join padrones P3 on OSB.CUILTI = P3.identificacion
where screening_empleados.cliente = @IDCliente
  and OSB.CUILBE<>OSB.cuilti

union all

select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,screening_empleados.cliente 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 screening_empleados
inner join obras_sociales_beneficiarios OSB on OSB.CUILBE = screening_empleados.cuil
inner join padrones P1 on P1.identificacion = OSB.CUITEMP
inner join padrones P3 on OSB.CUILBE = P3.identificacion
inner join padrones P2 on OSB.CUILTI = P2.identificacion
where screening_empleados.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*/
/*
declare @IDCliente int = 843015515 /* 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,visible,tipo_reg)

select distinct seo.nombre_integrante as razon_social,seo.cuit_int as cuit,
seo.cliente 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,'SOCIOS_F' as tipo_reg  
 from screening_empleados_out seo
inner join socios soc on soc.c01_cuit_soc = seo.cuit_int and soc.visible = 1
inner join cargos on cargos.car_codigo = soc.car_codigo
inner join padrones on padrones.identificacion = soc.c01_cuit 
where seo.cliente = @IDCliente 
and tipo_reg = 'FAMIL_EMP'


--declare @IDCliente int = 843015515 /* 5546348 */
/* 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,
seo.cliente 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,'ACCIONIS_F' as tipo_reg  from screening_empleados_out seo
inner join accionistas acc on acc.cuit_acc = seo.cuit_int  and acc.visible = 1 
inner join padrones on padrones.identificacion = acc.cuit_sociedad 
where seo.cliente = @IDCliente 
and tipo_reg = 'FAMIL_EMP'
/*
declare @IDCliente int = 843015515 /* 5546348 */
*/
/* 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, seo.cliente as cliente,getdate() as created,
sc.titulo as titulo,
sc.cargo 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,'SOCIEDAD_F' 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 sc.visible = 1 
inner join padrones on padrones.identificacion = sc.documento_soc 
where seo.cliente = @IDCliente 
and tipo_reg = 'FAMIL_EMP'


--declare @IDCliente int = 843015515 /* 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,visible,tipo_reg)
select seo.nombre_integrante as razon_social,
seo.cuit_int as cuit, 
seo.cliente as 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,1,
tipo_reg = 'EDICTOS_F'
from screening_empleados_out seo
inner join edictos on (edictos.cuit_integrante = seo.cuit_int)
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where seo.cliente =  @IDCliente 
and seo.tipo_reg = 'FAMIL_EMP'


/*
select * from fuentes_procesadas where fuente = 'empleados'
*/





/*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 'PIERO SOCIEDAD ANONIMA INDUSTRIAL Y COMERCIAL' as razon_social,'30504163543' 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(1712) and e.cuit != '30504163543'
 union all
  select distinct 'PIERO SOCIEDAD ANONIMA INDUSTRIAL Y COMERCIAL' as razon_social,'30504163543' 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(1712) and e.cuit != '30504163543'
 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