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_proveedores_new
AS
BEGIN
/*
truncate table screening_prov_out2
*/
--------------------------------------------------------------------------------------------------------------------
/* Paso 1: Busca integrantes en las tablas sociedades_constituciones y Edictos, que el cuit del edicto sea el proveedor : */
--------------------------------------------------------------------------------------------------------------------
/* 1.1.- solo cuit que comienzan con 3*/
declare @IDCliente int = 7777 /*843015515*/
/* BUSCAMOS SOBRE LA TABLA RELACIONES QUE SE ALIMENTA POR WEBSERVICE DE AFIP */
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg)
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.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 cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null as contenido_archivo,relaciones.fecha_relacion,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join relaciones on screening_prov.cuit = relaciones.cuita
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
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES */
/*
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg)
*/
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,
sociedades_constituciones.nombre_integrante,sociedades_constituciones.cuit_int AS cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS */
/*
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg)
*/
select screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,screening_prov.cliente,getdate() as created,
NULL as titulo, NULL as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,
padrones.nombre_persona as nombre_integrante,edictos.cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join edictos on screening_prov.cuit = edictos.cuit_sociedad
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
/*
union all
/*
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg)
*/
select screening_prov.razon_social,screening_prov.cuit,
screening_prov.cliente,
getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 3
and cliente = @IDCliente
/*AND cuit in (
select distinct cuit_sociedad as cuit_sociedad from edictos where cuit_sociedad in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 3)
) */
*/
--------------------------------------------------------------------------------------------------------------------
/* Paso 1.2: Busca integrantes en las tablas sociedades_constituciones y Edictos, que el cuit del edicto sea el proveedor : */
--------------------------------------------------------------------------------------------------------------------
/* 1.2.- solo cuit que comienzan con 2*/
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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_prov.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,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join relaciones on screening_prov.cuit = relaciones.cuita
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
and substring(screening_prov.cuit,1,1) = 2
union all
select screening_prov.razon_social,screening_prov.cuit,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.cuit_int
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where substring(cuit,1,1) = 2
and cliente = @IDCliente
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
/*AND cuit in (
select distinct cuit_int from sociedades_constituciones where cuit_int in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 2)
) */
union all
select screening_prov.razon_social,screening_prov.cuit,
screening_prov.cliente,
getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 2
and cliente = @IDCliente
/*AND cuit in (
select distinct cuit_integrante from edictos where cuit_integrante in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 2)
) */
--order by screening_prov.razon_social asc, nombre_integrante ASC
--------------------------------------------------------------------------------------------------------------------
/* Paso 2: SEGUNDO NIVEL DE BUSQUEDA BASÁNDOSE EN LAS SOCIEDADES VINCULADAS */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777
insert into screening_prov_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,
@IDCliente 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 and cliente = @IDCliente) and cliente = @IDCliente
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 and cliente = @IDCliente) and cliente = @IDCliente
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 ¿¿¿¿???? */
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
/* Paso 3: buscar accionistas */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
-- Si necesitamos un segundo nivel busca accionistas de las empresas vinculadas
insert into screening_prov_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,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'ACCIONISTA'
from screening_prov_out
inner join accionistas on screening_prov_out.cuit_soc_vinculada = accionistas.cuit_sociedad
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_sociedad in (select distinct cuit from screening_prov_out where cliente = @IDCliente) and cliente = @IDCliente
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'ACCIONISTA'
from screening_prov
inner join accionistas on screening_prov.cuit = accionistas.cuit_sociedad and accionistas.visible = 1
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_sociedad in (select distinct cuit from screening_prov where cliente = @IDCliente) and cliente = @IDCliente
union
select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'ACCIONISTA'
from screening_prov
inner join accionistas on screening_prov.cuit = accionistas.cuit_acc and accionistas.visible = 1
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_acc in (select distinct cuit from screening_prov where substring(cuit,1,1) = 2 and cliente = @IDCliente) and cliente = @IDCliente
order by razon_social asc,nombre_integrante ASC
--------------------------------------------------------------------------------------------------------------------
/* Paso 4: buscar socios en tabla socios de cuit que no tengamos hasta este momento por sociedades constituciones */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
-- Si necesitamos un segundo nivel busca socios de las empresas vinculadas
insert into screening_prov_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,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR'
from screening_prov_out
inner join socios on screening_prov_out.cuit_soc_vinculada = socios.c01_cuit and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion
inner join padrones p1 on socios.c01_cuit = p1.identificacion
where socios.c01_cuit in (select distinct cuit from screening_prov_out where cliente = @IDCliente) and cliente = @IDCliente
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR'
from screening_prov
inner join socios on screening_prov.cuit = socios.c01_cuit and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion
inner join padrones p1 on socios.c01_cuit = p1.identificacion
where socios.c01_cuit in (select distinct cuit from screening_prov where cliente = @IDCliente) and cliente = @IDCliente
union
select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR'
from screening_prov
inner join socios on screening_prov.cuit = socios.c01_cuit_soc and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion
inner join padrones p1 on socios.c01_cuit = p1.identificacion
where socios.c01_cuit in (select distinct cuit from screening_prov where substring(cuit,1,1) = 2 and cliente = @IDCliente) and cliente = @IDCliente
order by razon_social asc,nombre_integrante ASC
--------------------------------------------------------------------------------------------------------------------
/* Paso 5: Bucamos familiares de los CUITs de los proveedores */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente 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 = 'FAMILIAR'
from screening_prov
inner join obras_sociales_beneficiarios OSB on OSB.CUILTI = screening_prov.cuit
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 cliente = @IDCliente
and OSB.CUILBE<>OSB.cuilti
union all
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 = 'FAMILIAR'
from screening_prov
inner join obras_sociales_beneficiarios OSB on OSB.CUILBE = screening_prov.cuit
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P3 on OSB.CUILBE = P3.identificacion
inner join padrones P2 on OSB.CUILTI = P2.identificacion
where cliente = @IDCliente
and OSB.CUILBE<>OSB.cuilti
--------------------------------------------------------------------------------------------------------------------
/* Paso 8: buscamos familiares de los integrantes */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente 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 screening_prov_out
inner join obras_sociales_beneficiarios OSB on OSB.CUILTI = screening_prov_out.cuit_int or OSB.CUILTI = screening_prov_out.cuit_integrante
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 cliente = @IDCliente and (isnull(ltrim(rtrim(cuit_int)),'') <> '' or isnull(ltrim(rtrim(cuit_integrante)),'') <> '')
and tipo_reg != 'FAMILIAR'
and OSB.CUILBE<>OSB.cuilti
union all
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 screening_prov_out
inner join obras_sociales_beneficiarios OSB on OSB.CUILBE = screening_prov_out.cuit_int or OSB.CUILBE = screening_prov_out.cuit_integrante
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P3 on OSB.CUILBE = P3.identificacion
inner join padrones P2 on OSB.CUILTI = P2.identificacion
where cliente = @IDCliente and (isnull(ltrim(rtrim(cuit_int)),'') <> '' or isnull(ltrim(rtrim(cuit_integrante)),'') <> '')
and tipo_reg != 'FAMILIAR'
and OSB.CUILBE<>OSB.cuilti
--------------------------------------------------------------------------------------------------------------------
/* Paso 6: Busco todos los empleados de los proveedores */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
declare @ultimo_mes_empleados date
declare @periodo_empleados int
declare @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 @periodo_empleados = 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_prov_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 screening_prov.razon_social as razon_social,screening_prov.cuit as cuit,
@IDCliente as cliente,
getdate() as created,NULL as titulo,
NULL as cargo, NULL as soc_vinculada,NULL as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,padrones.identificacion as cuit_int,padrones.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'PROV_EMP' as tipo_reg
from screening_prov
/* inner join empleados on empleados.cuit = screening_prov.cuit */
inner join empleados on (empleados.cuit = screening_prov.cuit 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.cuil
where empleados.periodo = @periodo_empleados
AND screening_prov.cliente = @IDCliente
union all
select distinct screening_prov.razon_social as razon_social,screening_prov.cuit as cuit,
@IDCliente as cliente,
getdate() as created,NULL as titulo,
NULL as cargo, NULL as soc_vinculada,NULL as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,padrones.identificacion as cuit_int,padrones.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'PROV_EMP' as tipo_reg
from screening_prov
/* inner join empleados on empleados.cuit = screening_prov.cuit */
inner join empleados_temp empleados on (empleados.cuit = screening_prov.cuit 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.cuil
where empleados.periodo = @periodo_empleados
AND screening_prov.cliente = @IDCliente
/*************************************************/
--------------------------------------------------------------------------------------------------------------------
/* Paso 7: buscar empleos de los integrantes */
--------------------------------------------------------------------------------------------------------------------
/*
declare @IDCliente int = 7777 /*7777*/
declare @ultimo_mes_empleados date
declare @periodo_empleados int
*/
declare @ultimo_mes_empleados_1 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 @periodo_empleados = convert(int,substring(convert(char,@ultimo_mes_empleados),3,2)+substring(convert(char,@ultimo_mes_empleados),6,2))
set @ultimo_mes_empleados_1 = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
set @mes_empleados = DATEADD(M,-2,@ultimo_mes_empleados_1)
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_1),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_prov_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,
@IDCliente 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 between @periodo_empleados_desde and @periodo_empleados_hasta )
inner join padrones p2 on (empleados.cuil = p2.identificacion and empleados.periodo between @periodo_empleados_desde and @periodo_empleados_hasta)
where empleados.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> '')
)
and empleados.periodo in (@periodo_empleados_hasta))
union all
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 between @periodo_empleados_desde and @periodo_empleados_hasta )
inner join padrones p2 on (EE.cuil = p2.identificacion and EE.periodo between @periodo_empleados_desde and @periodo_empleados_hasta )
where EE.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> '')
)
and EE.periodo in (@periodo_empleados_hasta) )
order by p2.nombre_persona
--------------------------------------------------------------------------------------------------------------------
/* Paso 9: screening TERCER CICLO DE BUSCANDO SOCIEDADES A PARTIR DE LOS CUIT INTEGRANTES*/
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777
insert into screening_prov_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 * from screening_prov_out where tipo_reg='INT_SOC_1' and cliente=@IDCliente
*/
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,
screening_prov_out.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,'INT_SOC_1' as tipo_reg
from screening_prov_out
inner join relaciones on (screening_prov_out.cuit_int = relaciones.cuitb OR screening_prov_out.cuit_integrante = 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 screening_prov_out.cliente = @IDCliente and relaciones.id_tipo_relacion = 1
and screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out where cliente = @IDCliente)
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente)
union all
select P2.nombre_persona AS razon_social,P2.identificacion AS cuit,
@IDCliente 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 = 'INT_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 where cliente = @IDCliente)
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente)
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 = 'INT_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 where cliente = @IDCliente)
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente)
--------------------------------------------------------------------------------------------------------------------
/* Paso 10:BUSCANDO SOCIEDADES A PARTIR DE LOS CUIT DE FAMILIARES */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente 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 = 'FAM_SOC'
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 cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 = 'FAM_SOC'
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 cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
union all
select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,
p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'FAM_SOC'
from screening_prov_out
inner join socios on (screening_prov_out.cuit_integrante = socios.c01_cuit OR screening_prov_out.cuit_int = socios.c01_cuit) and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion
inner join padrones p1 on socios.c01_cuit = p1.identificacion
where cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
union all
select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'FAM_SOC'
from screening_prov_out
inner join accionistas on (accionistas.cuit_acc = screening_prov_out.cuit_integrante
OR accionistas.cuit_acc = screening_prov_out.cuit_int)
and accionistas.visible = 1
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
/*
declare @IDCliente int = 7777
*/
/*
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=66365843015515,cuit_int=230663658430155159,cuit_integrante=230663658430155159 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_proveedores_new
AS
BEGIN
/*
truncate table screening_prov_out2
*/
--------------------------------------------------------------------------------------------------------------------
/* Paso 1: Busca integrantes en las tablas sociedades_constituciones y Edictos, que el cuit del edicto sea el proveedor : */
--------------------------------------------------------------------------------------------------------------------
/* 1.1.- solo cuit que comienzan con 3*/
declare @IDCliente int = 7777 /*843015515*/
/* BUSCAMOS SOBRE LA TABLA RELACIONES QUE SE ALIMENTA POR WEBSERVICE DE AFIP */
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg)
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.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 cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null as contenido_archivo,relaciones.fecha_relacion,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join relaciones on screening_prov.cuit = relaciones.cuita
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
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES */
/*
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg)
*/
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,
sociedades_constituciones.nombre_integrante,sociedades_constituciones.cuit_int AS cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS */
/*
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg)
*/
select screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,screening_prov.cliente,getdate() as created,
NULL as titulo, NULL as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,
padrones.nombre_persona as nombre_integrante,edictos.cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join edictos on screening_prov.cuit = edictos.cuit_sociedad
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
/*
union all
/*
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg)
*/
select screening_prov.razon_social,screening_prov.cuit,
screening_prov.cliente,
getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 3
and cliente = @IDCliente
/*AND cuit in (
select distinct cuit_sociedad as cuit_sociedad from edictos where cuit_sociedad in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 3)
) */
*/
--------------------------------------------------------------------------------------------------------------------
/* Paso 1.2: Busca integrantes en las tablas sociedades_constituciones y Edictos, que el cuit del edicto sea el proveedor : */
--------------------------------------------------------------------------------------------------------------------
/* 1.2.- solo cuit que comienzan con 2*/
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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_prov.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,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join relaciones on screening_prov.cuit = relaciones.cuita
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
and substring(screening_prov.cuit,1,1) = 2
union all
select screening_prov.razon_social,screening_prov.cuit,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.cuit_int
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where substring(cuit,1,1) = 2
and cliente = @IDCliente
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
/*AND cuit in (
select distinct cuit_int from sociedades_constituciones where cuit_int in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 2)
) */
union all
select screening_prov.razon_social,screening_prov.cuit,
screening_prov.cliente,
getdate() as created,
'' as titulo, '' as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,'' as documento_integrante,
'' as nrdoc_int,'' as cuit_int,edictos.cuit_integrante,edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
,'SOC_CONS_1' as tipo_reg
from screening_prov
inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
where substring(cuit,1,1) = 2
and cliente = @IDCliente
/*AND cuit in (
select distinct cuit_integrante from edictos where cuit_integrante in (
select screening_prov.cuit from screening_prov where substring(cuit,1,1) = 2)
) */
--order by screening_prov.razon_social asc, nombre_integrante ASC
--------------------------------------------------------------------------------------------------------------------
/* Paso 2: SEGUNDO NIVEL DE BUSQUEDA BASÁNDOSE EN LAS SOCIEDADES VINCULADAS */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777
insert into screening_prov_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,
@IDCliente 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 and cliente = @IDCliente) and cliente = @IDCliente
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 and cliente = @IDCliente) and cliente = @IDCliente
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 ¿¿¿¿???? */
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
/* Paso 3: buscar accionistas */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
-- Si necesitamos un segundo nivel busca accionistas de las empresas vinculadas
insert into screening_prov_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,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'ACCIONISTA'
from screening_prov_out
inner join accionistas on screening_prov_out.cuit_soc_vinculada = accionistas.cuit_sociedad
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_sociedad in (select distinct cuit from screening_prov_out where cliente = @IDCliente) and cliente = @IDCliente
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'ACCIONISTA'
from screening_prov
inner join accionistas on screening_prov.cuit = accionistas.cuit_sociedad and accionistas.visible = 1
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_sociedad in (select distinct cuit from screening_prov where cliente = @IDCliente) and cliente = @IDCliente
union
select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'ACCIONISTA'
from screening_prov
inner join accionistas on screening_prov.cuit = accionistas.cuit_acc and accionistas.visible = 1
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_acc in (select distinct cuit from screening_prov where substring(cuit,1,1) = 2 and cliente = @IDCliente) and cliente = @IDCliente
order by razon_social asc,nombre_integrante ASC
--------------------------------------------------------------------------------------------------------------------
/* Paso 4: buscar socios en tabla socios de cuit que no tengamos hasta este momento por sociedades constituciones */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
-- Si necesitamos un segundo nivel busca socios de las empresas vinculadas
insert into screening_prov_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,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR'
from screening_prov_out
inner join socios on screening_prov_out.cuit_soc_vinculada = socios.c01_cuit and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion
inner join padrones p1 on socios.c01_cuit = p1.identificacion
where socios.c01_cuit in (select distinct cuit from screening_prov_out where cliente = @IDCliente) and cliente = @IDCliente
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR'
from screening_prov
inner join socios on screening_prov.cuit = socios.c01_cuit and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion
inner join padrones p1 on socios.c01_cuit = p1.identificacion
where socios.c01_cuit in (select distinct cuit from screening_prov where cliente = @IDCliente) and cliente = @IDCliente
union
select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR'
from screening_prov
inner join socios on screening_prov.cuit = socios.c01_cuit_soc and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion
inner join padrones p1 on socios.c01_cuit = p1.identificacion
where socios.c01_cuit in (select distinct cuit from screening_prov where substring(cuit,1,1) = 2 and cliente = @IDCliente) and cliente = @IDCliente
order by razon_social asc,nombre_integrante ASC
--------------------------------------------------------------------------------------------------------------------
/* Paso 5: Bucamos familiares de los CUITs de los proveedores */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente 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 = 'FAMILIAR'
from screening_prov
inner join obras_sociales_beneficiarios OSB on OSB.CUILTI = screening_prov.cuit
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 cliente = @IDCliente
and OSB.CUILBE<>OSB.cuilti
union all
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 = 'FAMILIAR'
from screening_prov
inner join obras_sociales_beneficiarios OSB on OSB.CUILBE = screening_prov.cuit
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P3 on OSB.CUILBE = P3.identificacion
inner join padrones P2 on OSB.CUILTI = P2.identificacion
where cliente = @IDCliente
and OSB.CUILBE<>OSB.cuilti
--------------------------------------------------------------------------------------------------------------------
/* Paso 8: buscamos familiares de los integrantes */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente 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 screening_prov_out
inner join obras_sociales_beneficiarios OSB on OSB.CUILTI = screening_prov_out.cuit_int or OSB.CUILTI = screening_prov_out.cuit_integrante
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 cliente = @IDCliente and (isnull(ltrim(rtrim(cuit_int)),'') <> '' or isnull(ltrim(rtrim(cuit_integrante)),'') <> '')
and tipo_reg != 'FAMILIAR'
and OSB.CUILBE<>OSB.cuilti
union all
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 screening_prov_out
inner join obras_sociales_beneficiarios OSB on OSB.CUILBE = screening_prov_out.cuit_int or OSB.CUILBE = screening_prov_out.cuit_integrante
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P3 on OSB.CUILBE = P3.identificacion
inner join padrones P2 on OSB.CUILTI = P2.identificacion
where cliente = @IDCliente and (isnull(ltrim(rtrim(cuit_int)),'') <> '' or isnull(ltrim(rtrim(cuit_integrante)),'') <> '')
and tipo_reg != 'FAMILIAR'
and OSB.CUILBE<>OSB.cuilti
--------------------------------------------------------------------------------------------------------------------
/* Paso 6: Busco todos los empleados de los proveedores */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
declare @ultimo_mes_empleados date
declare @periodo_empleados int
declare @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 @periodo_empleados = 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_prov_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 screening_prov.razon_social as razon_social,screening_prov.cuit as cuit,
@IDCliente as cliente,
getdate() as created,NULL as titulo,
NULL as cargo, NULL as soc_vinculada,NULL as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,padrones.identificacion as cuit_int,padrones.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'PROV_EMP' as tipo_reg
from screening_prov
/* inner join empleados on empleados.cuit = screening_prov.cuit */
inner join empleados on (empleados.cuit = screening_prov.cuit 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.cuil
where empleados.periodo = @periodo_empleados
AND screening_prov.cliente = @IDCliente
union all
select distinct screening_prov.razon_social as razon_social,screening_prov.cuit as cuit,
@IDCliente as cliente,
getdate() as created,NULL as titulo,
NULL as cargo, NULL as soc_vinculada,NULL as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,padrones.identificacion as cuit_int,padrones.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'PROV_EMP' as tipo_reg
from screening_prov
/* inner join empleados on empleados.cuit = screening_prov.cuit */
inner join empleados_temp empleados on (empleados.cuit = screening_prov.cuit 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.cuil
where empleados.periodo = @periodo_empleados
AND screening_prov.cliente = @IDCliente
/*************************************************/
--------------------------------------------------------------------------------------------------------------------
/* Paso 7: buscar empleos de los integrantes */
--------------------------------------------------------------------------------------------------------------------
/*
declare @IDCliente int = 7777 /*7777*/
declare @ultimo_mes_empleados date
declare @periodo_empleados int
*/
declare @ultimo_mes_empleados_1 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 @periodo_empleados = convert(int,substring(convert(char,@ultimo_mes_empleados),3,2)+substring(convert(char,@ultimo_mes_empleados),6,2))
set @ultimo_mes_empleados_1 = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
set @mes_empleados = DATEADD(M,-2,@ultimo_mes_empleados_1)
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_1),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_prov_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,
@IDCliente 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 between @periodo_empleados_desde and @periodo_empleados_hasta )
inner join padrones p2 on (empleados.cuil = p2.identificacion and empleados.periodo between @periodo_empleados_desde and @periodo_empleados_hasta)
where empleados.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> '')
)
and empleados.periodo in (@periodo_empleados_hasta))
union all
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 between @periodo_empleados_desde and @periodo_empleados_hasta )
inner join padrones p2 on (EE.cuil = p2.identificacion and EE.periodo between @periodo_empleados_desde and @periodo_empleados_hasta )
where EE.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> ''
union all
select distinct cuit_integrante as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_integrante is not null and cuit_integrante <> ''
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> '')
)
and EE.periodo in (@periodo_empleados_hasta) )
order by p2.nombre_persona
--------------------------------------------------------------------------------------------------------------------
/* Paso 9: screening TERCER CICLO DE BUSCANDO SOCIEDADES A PARTIR DE LOS CUIT INTEGRANTES*/
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777
insert into screening_prov_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 * from screening_prov_out where tipo_reg='INT_SOC_1' and cliente=@IDCliente
*/
select p2.nombre_persona as razon_social,p2.identificacion AS cuit,
screening_prov_out.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,'INT_SOC_1' as tipo_reg
from screening_prov_out
inner join relaciones on (screening_prov_out.cuit_int = relaciones.cuitb OR screening_prov_out.cuit_integrante = 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 screening_prov_out.cliente = @IDCliente and relaciones.id_tipo_relacion = 1
and screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out where cliente = @IDCliente)
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente)
union all
select P2.nombre_persona AS razon_social,P2.identificacion AS cuit,
@IDCliente 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 = 'INT_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 where cliente = @IDCliente)
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente)
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 = 'INT_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 where cliente = @IDCliente)
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente)
--------------------------------------------------------------------------------------------------------------------
/* Paso 10:BUSCANDO SOCIEDADES A PARTIR DE LOS CUIT DE FAMILIARES */
--------------------------------------------------------------------------------------------------------------------
--declare @IDCliente int = 7777 /*7777*/
insert into screening_prov_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,
@IDCliente 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 = 'FAM_SOC'
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 cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
union all
select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente 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 = 'FAM_SOC'
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 cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
union all
select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,
p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'FAM_SOC'
from screening_prov_out
inner join socios on (screening_prov_out.cuit_integrante = socios.c01_cuit OR screening_prov_out.cuit_int = socios.c01_cuit) and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion
inner join padrones p1 on socios.c01_cuit = p1.identificacion
where cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
union all
select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'FAM_SOC'
from screening_prov_out
inner join accionistas on (accionistas.cuit_acc = screening_prov_out.cuit_integrante
OR accionistas.cuit_acc = screening_prov_out.cuit_int)
and accionistas.visible = 1
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
/*
declare @IDCliente int = 7777
*/
/*
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=66365843015515,cuit_int=230663658430155159,cuit_integrante=230663658430155159 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
16- dbo.accionistas
- dbo.CARGOS
- dbo.clientes
- dbo.EDICTOS
- dbo.empleados
- dbo.fuentes_procesadas
- dbo.obras_sociales_beneficiarios
- dbo.padrones
- dbo.relaciones
- dbo.screening_prov
- dbo.screening_prov_out
- dbo.screening_prov_out2
- dbo.sociedades_constituciones
- dbo.socios
- dbo.tipos_relaciones
- dbo.tipos_subrelaciones
Used By
No items found