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_OUT_new
AS
BEGIN
declare @IDCliente int = 843015515 /* 5546348*/
select DISTINCT
spo.nombre_integrante as EMPLEADO,
se.cuil as [CUIL EMPLEADO],
spo.soc_vinculada as [EMPPRESA VINCULADA],
spo.cuit_soc_vinculada AS [CUIT EMPRESA VINCULADA],
spo.cuit_int as [CUIT INTEGRANTE],
spo.nombre_integrante as [NOMBRE INTEGRANTE],
'EMPLEADO TIENE CARGO EN UN PROVEEDOR' as [COINCIDENCIA]
from screening_empleados se
inner join screening_prov_out spo
on se.cuil = spo.cuit_int
and spo.tipo_reg in ('ACCIONISTA','DIRECTOR','SOC_CONS_1')
and spo.cliente = @IDCliente
where se.cliente = @IDCliente
union
select DISTINCT
seo.nombre_integrante as EMPLEADO,
seo.cuit_integrante as [CUIL EMPLEADO],
seo.soc_vinculada as [EMPPRESA VINCULADA],
seo.cuit_soc_vinculada AS [CUIT EMPRESA VINCULADA],
seo.cuit_integrante as [CUIT INTEGRANTE],
seo.nombre_integrante as [NOMBRE INTEGRANTE],
'EMPLEADO TIENE CARGO EN UN PROVEEDOR' as [COINCIDENCIA]
from screening_empleados_out seo
inner join screening_prov sp
on seo.cuit_soc_vinculada = sp.cuit
and sp.cliente = @IDCliente
where seo.cliente = @IDCliente
and seo.tipo_reg in (
'ACCIONISTA',
'SOC_CONS_1',
'SOCIOS')
union all
/*
declare @IDCliente int = 843015515 /* 5546348*/
*/
select DISTINCT /*spo.id_screening_prov_out, */
spo.nombre_integrante as EMPLEADO,
spo.cuit_integrante as [CUIL EMPLEADO],
spo.soc_vinculada as [EMPPRESA VINCULADA],
spo.cuit_soc_vinculada AS [CUIT EMPRESA VINCULADA],
spo.cuit_integrante as [CUIT INTEGRANTE],
spo.nombre_integrante as [NOMBRE INTEGRANTE],
'FAMILIAR DE PROVEEDOR - FAMILIAR DE VINCULADO A UN PROVEEDOR ES EMPLEADO' as [COINCIDENCIA]
from screening_prov_out spo
inner join screening_empleados se
on spo.cuit_soc_vinculada = se.cuil
and spo.cliente = @IDCliente
where spo.cliente = @IDCliente
and spo.tipo_reg in ('FAMILIAR','FAMIL_INT')
order by empleado
/*EMPLEADOS OUT*/
/*
declare @IDCliente int = 5546348 /* 5546348*/
*/
select distinct razon_social,cuit,
/*soc_vinculada as EMPLEADO,*/
padrones.nombre_persona as EMPLEADO,
cuit_soc_vinculada as cuit_empleado,tipo_reg,Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_soc_vinculada = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = 'FAMIL_EMP'
and cuit_soc_vinculada in (select distinct cuil from screening_empleados where cliente=@IDCliente
)
union all
select distinct razon_social,cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = '2EMPLEOS' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente
)
union all
select distinct 'AMX ARGENTINA SOCIEDAD ANONIMA' as razon_social, '30663288497' as cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = 'Accio' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente
)
UNION ALL
select distinct 'AMX ARGENTINA SOCIEDAD ANONIMA' as razon_social, '30663288497' as cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = 'SOCIOS' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente)
UNION ALL
select distinct 'AMX ARGENTINA SOCIEDAD ANONIMA' as razon_social, '30663288497' as cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = 'SOCIOSF' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente)
UNION ALL
select distinct 'AMX ARGENTINA SOCIEDAD ANONIMA' as razon_social, '30663288497' as cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and tipo_reg LIKE 'SOC_CONS%' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente)
order by empleado asc,VINCULADO ASC
/*proveedores OUT*/
/*
declare @IDCliente int = 5546348 /* 5546348*/
*/
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'ACCIONISTA' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'DIRECTOR' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,nombre_integrante as EMPLEADO,cuit_int as cuit_empleado,'EMPLEADO DE PROVEEDORES' AS tipo_reg,'' as VINCULADO,'' as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'prov_emp' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,soc_vinculada as EMPLEADO,cuit_soc_vinculada as cuit_empleado,'FAMILIAR DE LOS INTEGRANTES DE SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'FAMIL_INT' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES DE PROVEEDORES' AS tipo_reg,Nombre_integrante as VINCULADO, cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'SOC_CONS_1' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES 2DO NIVEL' AS tipo_reg,Nombre_integrante as VINCULADO, cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'SOC_CONS_2' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
/*
select distinct cuiL from screening_empleados where cliente=@IDCliente AND CUIL in (23169734569) */
/*declare @IDCliente int = 5546348 */
/*
select distinct cuiL from screening_empleados where cliente=@IDCliente AND CUIL in (23169734569) */
/*AMX PROVEEDORES SIN LOS BLACKLIST*/
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'ACCIONISTA' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'DIRECTOR' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,nombre_integrante as EMPLEADO,cuit_int as cuit_empleado,'EMPLEADO DE PROVEEDORES' AS tipo_reg,'' as VINCULADO,
'' as cuit_vinculado
from screening_prov_out
where tipo_reg = 'prov_emp' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,soc_vinculada as EMPLEADO,cuit_soc_vinculada as cuit_empleado,'FAMILIAR DE LOS INTEGRANTES DE SOCIEDADES' AS tipo_reg,
Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'FAMIL_INT' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES DE PROVEEDORES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'SOC_CONS_1' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES 2DO NIVEL' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'SOC_CONS_2' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
/*AMX PROVEEDORES SIN LOS BLACKLIST*/
ORDER BY razon_social,EMPLEADO,VINCULADO,tipo_reg
/*AMX PROVEEDORES SOLO BLACKLIST*/
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'ACCIONISTA' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'DIRECTOR' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,nombre_integrante as EMPLEADO,cuit_int as cuit_empleado,'EMPLEADO DE PROVEEDORES' AS tipo_reg,'' as VINCULADO,
'' as cuit_vinculado
from screening_prov_out
where tipo_reg = 'prov_emp' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,soc_vinculada as EMPLEADO,cuit_soc_vinculada as cuit_empleado,'FAMILIAR DE LOS INTEGRANTES DE SOCIEDADES' AS tipo_reg,
Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'FAMIL_INT' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES DE PROVEEDORES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'SOC_CONS_1' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES 2DO NIVEL' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'SOC_CONS_2' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
/*AMX PROVEEDORES SOLO BLACKLIST*/
ORDER BY razon_social,EMPLEADO,VINCULADO,tipo_reg
/*
declare @IDCliente int = 5546348
*/
/*
select distinct cuiT from BLACKLIST_PROVEEDORES_AMX where CUIT in (20225637068)
select distinct cuiL from screening_empleados where CUIL in (20225637068) AND cliente=@IDCliente
SELECT * FROM screening_prov WHERE cuit= '30710566328'
SELECT * FROM EMPLEADOS WHERE CUIL in ('20386904422')
*/
/* CRUCE AMX PROVEEDORES - EMPLEADOS SIN BLACKLIST*/
select DISTINCT seo.razon_social as CLIENTE,seo.cuit as [CUIT CLIENTE],seo.soc_vinculada as [EMPLEADO DEL CLIENTE],
seo.cuit_soc_vinculada as [CUIT DEL EMPLEADO DEL CLIENTE],
CASE
WHEN seo.tipo_reg = 'FAMIL_EMP' THEN 'FAMILIAR DE EMPLEADO'
END AS VINCULACION,
spo.razon_social as EMPLEADOR,spo.cuit as [CUIT EMPLEADOR],padrones.nombre_persona as [EMPLEADO], /*seo.nombre_integrante as [EMPLEADO],*/
seo.cuit_int as [CUIT DEL EMPLEADO]
from screening_prov_out spo
--inner join screening_empleados_out seo on seo.cuit = spo.cuit
inner join screening_empleados_out seo on seo.cuit_int = spo.cuit_int
inner join padrones on seo.cuit_int = padrones.identificacion
where SEO.tipo_reg <> '2EMPLEOS' and seo.cliente=@IDCliente
and seo.tipo_reg is not null
and seo.razon_social is not null
and seo.cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
/*
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT CUIL FROM empleados WHERE CUIT='30663288497' AND PERIODO=1705) OR SEO.cuit_int IN (SELECT DISTINCT CUIL FROM empleados WHERE CUIT='30663288497' AND PERIODO=1705))
*/
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente ) OR SEO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente ))
union all
select DISTINCT seo.razon_social as CLIENTE,seo.cuit as [CUIT CLIENTE],
seo.nombre_integrante as [EMPLEADO DEL CLIENTE],
seo.cuit_int as [CUIT DEL EMPLEADO DEL CLIENTE],
CASE
WHEN seo.tipo_reg = '2EMPLEOS' THEN 'MULTIPLES EMPLEADORES'
END AS VINCULACION,
seo.soc_vinculada as EMPLEADOR,seo.cuit_soc_vinculada as [CUIT EMPLEADOR],
seo.nombre_integrante as [EMPLEADO],
seo.cuit_int as [CUIT DEL EMPLEADO]
from screening_prov_out spo
--inner join screening_empleados_out seo on seo.cuit = spo.cuit
right join screening_empleados_out seo on seo.cuit_int = spo.cuit_int
where SEO.tipo_reg = '2EMPLEOS' and seo.cliente=@IDCliente
and seo.tipo_reg is not null
and seo.razon_social is not null and seo.cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente ) OR SEO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente ))
order by seo.soc_vinculada ASC
/* CRUCE AMX PROVEEDORES - EMPLEADOS SIN BLACKLIST*/
/*declare @IDCliente int = 5546348 */
/*
SELECT * FROM screening_empleados_out WHERE cliente=@IDCliente AND (cuit IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)
OR cuit_int IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX) OR cuit_soc_vinculada IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX))
*/
select distinct cuiT from BLACKLIST_PROVEEDORES_AMX where CUIT in (20244242430,30712079335,30562252084)
select distinct cuiL from screening_empleados where CUIL in (20244242430,30712079335,30562252084) AND cliente=@IDCliente
SELECT * FROM screening_prov WHERE cuit IN ('20244242430','30712079335','30562252084')
/*
SELECT * FROM EMPLEADOS WHERE CUIL in ('20386904422')
*/
/* CRUCE AMX PROVEEDORES - EMPLEADOS CON BLACKLIST*/
select DISTINCT seo.razon_social as CLIENTE,seo.cuit as [CUIT CLIENTE],seo.soc_vinculada as [EMPLEADO DEL CLIENTE],
seo.cuit_soc_vinculada as [CUIT DEL EMPLEADO DEL CLIENTE],
CASE
WHEN seo.tipo_reg = 'FAMIL_EMP' THEN 'FAMILIAR DE EMPLEADO'
END AS VINCULACION,/*SEO.tipo_reg,SPO.tipo_reg AS TREGSPO,*/
spo.razon_social as EMPLEADOR,spo.cuit as [CUIT EMPLEADOR],padrones.nombre_persona as [EMPLEADO], /*seo.nombre_integrante as [EMPLEADO],*/
seo.cuit_int as [CUIT DEL EMPLEADO]
from screening_prov_out spo
--inner join screening_empleados_out seo on seo.cuit = spo.cuit
inner join screening_empleados_out seo on seo.cuit_int = spo.cuit_int
inner join padrones on seo.cuit_int = padrones.identificacion
where SEO.tipo_reg <> '2EMPLEOS' and seo.cliente=@IDCliente
and seo.tipo_reg is not null
and seo.razon_social is not null
and (seo.cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR (SEO.cuit IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)
OR SEO.cuit_int IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX) OR SEO.cuit_soc_vinculada IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)))
/*
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT CUIL FROM empleados WHERE CUIT='30663288497' AND PERIODO=1705) OR SEO.cuit_int IN (SELECT DISTINCT CUIL FROM empleados WHERE CUIT='30663288497' AND PERIODO=1705))
*/
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SEO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SPO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SPO.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
)
union all
select DISTINCT seo.razon_social as CLIENTE,seo.cuit as [CUIT CLIENTE],
seo.nombre_integrante as [EMPLEADO DEL CLIENTE],
seo.cuit_int as [CUIT DEL EMPLEADO DEL CLIENTE],
CASE
WHEN seo.tipo_reg = '2EMPLEOS' THEN 'MULTIPLES EMPLEADORES'
END AS VINCULACION,/*SEO.tipo_reg,SPO.tipo_reg AS TREGSPO,*/
seo.soc_vinculada as EMPLEADOR,seo.cuit_soc_vinculada as [CUIT EMPLEADOR],
seo.nombre_integrante as [EMPLEADO],
seo.cuit_int as [CUIT DEL EMPLEADO]
from screening_prov_out spo
--inner join screening_empleados_out seo on seo.cuit = spo.cuit
right join screening_empleados_out seo on seo.cuit_int = spo.cuit_int
where SEO.tipo_reg = '2EMPLEOS' and seo.cliente=@IDCliente
and seo.tipo_reg is not null
and seo.razon_social is not null
and (seo.cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR (SEO.cuit IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)
OR SEO.cuit_int IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX) OR SEO.cuit_soc_vinculada IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)))
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SEO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SPO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SPO.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
)
order by seo.soc_vinculada ASC
/* CRUCE AMX PROVEEDORES - EMPLEADOS CON BLACKLIST*/
select SPOBLAMX.*,SEOBLAMX.* from screening_empleados_out_blacklist_amx SEOBLAMX inner join screening_prov_out_blacklist_amx SPOBLAMX
on SEOBLAMX.cuit_int = SPOBLAMX.cuit_int
where SEOBLAMX.cuit_int in (select distinct cuit from screening_prov where cliente = 5546348 and cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
union all
select SPOBLAMX.*,SEOBLAMX.* from screening_empleados_out_blacklist_amx SEOBLAMX inner join screening_prov_out_blacklist_amx SPOBLAMX
on SEOBLAMX.cuit_soc_vinculada = SPOBLAMX.cuit_soc_vinculada
where SEOBLAMX.cuit_soc_vinculada in (select distinct cuit from screening_prov where cliente = 5546348 and cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
order by SEOBLAMX.nombre_persona
select * from screening_prov where cliente = 5546348 and cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
and cuit in (30521900470)
order by razon_social
select * from empleados where cuil ='27258888184'
select * from padrones where identificacion in (
SELECT cuit FROM BLACKLIST_PROVEEDORES_AMX where cuit in (27258888184,30711189692,20174242888))
select * from padrones where identificacion in (
select cuit from BLACKLIST_PROVEEDORES_AMX where cuit in (30521900470,27235374760,27258888184,30711189692,20174242888))
select * from padrones where identificacion='30711189692'
END
GO
GO
CREATE OR ALTER PROCEDURE dbo.screening_OUT_new
AS
BEGIN
declare @IDCliente int = 843015515 /* 5546348*/
select DISTINCT
spo.nombre_integrante as EMPLEADO,
se.cuil as [CUIL EMPLEADO],
spo.soc_vinculada as [EMPPRESA VINCULADA],
spo.cuit_soc_vinculada AS [CUIT EMPRESA VINCULADA],
spo.cuit_int as [CUIT INTEGRANTE],
spo.nombre_integrante as [NOMBRE INTEGRANTE],
'EMPLEADO TIENE CARGO EN UN PROVEEDOR' as [COINCIDENCIA]
from screening_empleados se
inner join screening_prov_out spo
on se.cuil = spo.cuit_int
and spo.tipo_reg in ('ACCIONISTA','DIRECTOR','SOC_CONS_1')
and spo.cliente = @IDCliente
where se.cliente = @IDCliente
union
select DISTINCT
seo.nombre_integrante as EMPLEADO,
seo.cuit_integrante as [CUIL EMPLEADO],
seo.soc_vinculada as [EMPPRESA VINCULADA],
seo.cuit_soc_vinculada AS [CUIT EMPRESA VINCULADA],
seo.cuit_integrante as [CUIT INTEGRANTE],
seo.nombre_integrante as [NOMBRE INTEGRANTE],
'EMPLEADO TIENE CARGO EN UN PROVEEDOR' as [COINCIDENCIA]
from screening_empleados_out seo
inner join screening_prov sp
on seo.cuit_soc_vinculada = sp.cuit
and sp.cliente = @IDCliente
where seo.cliente = @IDCliente
and seo.tipo_reg in (
'ACCIONISTA',
'SOC_CONS_1',
'SOCIOS')
union all
/*
declare @IDCliente int = 843015515 /* 5546348*/
*/
select DISTINCT /*spo.id_screening_prov_out, */
spo.nombre_integrante as EMPLEADO,
spo.cuit_integrante as [CUIL EMPLEADO],
spo.soc_vinculada as [EMPPRESA VINCULADA],
spo.cuit_soc_vinculada AS [CUIT EMPRESA VINCULADA],
spo.cuit_integrante as [CUIT INTEGRANTE],
spo.nombre_integrante as [NOMBRE INTEGRANTE],
'FAMILIAR DE PROVEEDOR - FAMILIAR DE VINCULADO A UN PROVEEDOR ES EMPLEADO' as [COINCIDENCIA]
from screening_prov_out spo
inner join screening_empleados se
on spo.cuit_soc_vinculada = se.cuil
and spo.cliente = @IDCliente
where spo.cliente = @IDCliente
and spo.tipo_reg in ('FAMILIAR','FAMIL_INT')
order by empleado
/*EMPLEADOS OUT*/
/*
declare @IDCliente int = 5546348 /* 5546348*/
*/
select distinct razon_social,cuit,
/*soc_vinculada as EMPLEADO,*/
padrones.nombre_persona as EMPLEADO,
cuit_soc_vinculada as cuit_empleado,tipo_reg,Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_soc_vinculada = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = 'FAMIL_EMP'
and cuit_soc_vinculada in (select distinct cuil from screening_empleados where cliente=@IDCliente
)
union all
select distinct razon_social,cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = '2EMPLEOS' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente
)
union all
select distinct 'AMX ARGENTINA SOCIEDAD ANONIMA' as razon_social, '30663288497' as cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = 'Accio' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente
)
UNION ALL
select distinct 'AMX ARGENTINA SOCIEDAD ANONIMA' as razon_social, '30663288497' as cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = 'SOCIOS' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente)
UNION ALL
select distinct 'AMX ARGENTINA SOCIEDAD ANONIMA' as razon_social, '30663288497' as cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and upper(tipo_reg) = 'SOCIOSF' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente)
UNION ALL
select distinct 'AMX ARGENTINA SOCIEDAD ANONIMA' as razon_social, '30663288497' as cuit,
/*Nombre_integrante as empleado,*/
padrones.nombre_persona as empleado,
cuit_int as cuit_empleado,
tipo_reg,
soc_vinculada as vinculado,cuit_soc_vinculada as cuit_vinculado
from screening_empleados_out INNER JOIN Padrones on screening_empleados_out.cuit_int = padrones.identificacion
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where cliente=@IDCliente and tipo_reg LIKE 'SOC_CONS%' and cuit_int in (select distinct cuil from screening_empleados where cliente=@IDCliente)
order by empleado asc,VINCULADO ASC
/*proveedores OUT*/
/*
declare @IDCliente int = 5546348 /* 5546348*/
*/
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'ACCIONISTA' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'DIRECTOR' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,nombre_integrante as EMPLEADO,cuit_int as cuit_empleado,'EMPLEADO DE PROVEEDORES' AS tipo_reg,'' as VINCULADO,'' as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'prov_emp' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,soc_vinculada as EMPLEADO,cuit_soc_vinculada as cuit_empleado,'FAMILIAR DE LOS INTEGRANTES DE SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'FAMIL_INT' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES DE PROVEEDORES' AS tipo_reg,Nombre_integrante as VINCULADO, cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'SOC_CONS_1' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES 2DO NIVEL' AS tipo_reg,Nombre_integrante as VINCULADO, cuit_int as cuit_vinculado
from screening_prov_out
/*
where cuit_soc_vinculada in (select distinct cuil from screening_empleados)
or (cuit_int in (select distinct cuil from screening_empleados))
*/
where tipo_reg = 'SOC_CONS_2' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
/*
select distinct cuiL from screening_empleados where cliente=@IDCliente AND CUIL in (23169734569) */
/*declare @IDCliente int = 5546348 */
/*
select distinct cuiL from screening_empleados where cliente=@IDCliente AND CUIL in (23169734569) */
/*AMX PROVEEDORES SIN LOS BLACKLIST*/
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'ACCIONISTA' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'DIRECTOR' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,nombre_integrante as EMPLEADO,cuit_int as cuit_empleado,'EMPLEADO DE PROVEEDORES' AS tipo_reg,'' as VINCULADO,
'' as cuit_vinculado
from screening_prov_out
where tipo_reg = 'prov_emp' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,soc_vinculada as EMPLEADO,cuit_soc_vinculada as cuit_empleado,'FAMILIAR DE LOS INTEGRANTES DE SOCIEDADES' AS tipo_reg,
Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'FAMIL_INT' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES DE PROVEEDORES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'SOC_CONS_1' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES 2DO NIVEL' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'SOC_CONS_2' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
/*AMX PROVEEDORES SIN LOS BLACKLIST*/
ORDER BY razon_social,EMPLEADO,VINCULADO,tipo_reg
/*AMX PROVEEDORES SOLO BLACKLIST*/
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'ACCIONISTA' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'PARTICIPACION EN SOCIEDADES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'DIRECTOR' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,nombre_integrante as EMPLEADO,cuit_int as cuit_empleado,'EMPLEADO DE PROVEEDORES' AS tipo_reg,'' as VINCULADO,
'' as cuit_vinculado
from screening_prov_out
where tipo_reg = 'prov_emp' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,soc_vinculada as EMPLEADO,cuit_soc_vinculada as cuit_empleado,'FAMILIAR DE LOS INTEGRANTES DE SOCIEDADES' AS tipo_reg,
Nombre_integrante as VINCULADO,cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'FAMIL_INT' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES DE PROVEEDORES' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'SOC_CONS_1' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
union all
select distinct razon_social,cuit,'' as EMPLEADO,'' as cuit_empleado,'SOCIEDADES/CONSTITUCIONES 2DO NIVEL' AS tipo_reg,Nombre_integrante as VINCULADO,
cuit_int as cuit_vinculado
from screening_prov_out
where tipo_reg = 'SOC_CONS_2' AND CUIT_INT IS NOT NULL and cliente=@IDCliente
and (cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX) OR cuit in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR cuit_soc_vinculada IN (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
AND CUIT<>cuit_int
/*
AND cuit in (SELECT CUIT FROM screening_prov WHERE cliente=@IDCliente)
*/
/*AMX PROVEEDORES SOLO BLACKLIST*/
ORDER BY razon_social,EMPLEADO,VINCULADO,tipo_reg
/*
declare @IDCliente int = 5546348
*/
/*
select distinct cuiT from BLACKLIST_PROVEEDORES_AMX where CUIT in (20225637068)
select distinct cuiL from screening_empleados where CUIL in (20225637068) AND cliente=@IDCliente
SELECT * FROM screening_prov WHERE cuit= '30710566328'
SELECT * FROM EMPLEADOS WHERE CUIL in ('20386904422')
*/
/* CRUCE AMX PROVEEDORES - EMPLEADOS SIN BLACKLIST*/
select DISTINCT seo.razon_social as CLIENTE,seo.cuit as [CUIT CLIENTE],seo.soc_vinculada as [EMPLEADO DEL CLIENTE],
seo.cuit_soc_vinculada as [CUIT DEL EMPLEADO DEL CLIENTE],
CASE
WHEN seo.tipo_reg = 'FAMIL_EMP' THEN 'FAMILIAR DE EMPLEADO'
END AS VINCULACION,
spo.razon_social as EMPLEADOR,spo.cuit as [CUIT EMPLEADOR],padrones.nombre_persona as [EMPLEADO], /*seo.nombre_integrante as [EMPLEADO],*/
seo.cuit_int as [CUIT DEL EMPLEADO]
from screening_prov_out spo
--inner join screening_empleados_out seo on seo.cuit = spo.cuit
inner join screening_empleados_out seo on seo.cuit_int = spo.cuit_int
inner join padrones on seo.cuit_int = padrones.identificacion
where SEO.tipo_reg <> '2EMPLEOS' and seo.cliente=@IDCliente
and seo.tipo_reg is not null
and seo.razon_social is not null
and seo.cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
/*
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT CUIL FROM empleados WHERE CUIT='30663288497' AND PERIODO=1705) OR SEO.cuit_int IN (SELECT DISTINCT CUIL FROM empleados WHERE CUIT='30663288497' AND PERIODO=1705))
*/
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente ) OR SEO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente ))
union all
select DISTINCT seo.razon_social as CLIENTE,seo.cuit as [CUIT CLIENTE],
seo.nombre_integrante as [EMPLEADO DEL CLIENTE],
seo.cuit_int as [CUIT DEL EMPLEADO DEL CLIENTE],
CASE
WHEN seo.tipo_reg = '2EMPLEOS' THEN 'MULTIPLES EMPLEADORES'
END AS VINCULACION,
seo.soc_vinculada as EMPLEADOR,seo.cuit_soc_vinculada as [CUIT EMPLEADOR],
seo.nombre_integrante as [EMPLEADO],
seo.cuit_int as [CUIT DEL EMPLEADO]
from screening_prov_out spo
--inner join screening_empleados_out seo on seo.cuit = spo.cuit
right join screening_empleados_out seo on seo.cuit_int = spo.cuit_int
where SEO.tipo_reg = '2EMPLEOS' and seo.cliente=@IDCliente
and seo.tipo_reg is not null
and seo.razon_social is not null and seo.cuit_int not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente ) OR SEO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente ))
order by seo.soc_vinculada ASC
/* CRUCE AMX PROVEEDORES - EMPLEADOS SIN BLACKLIST*/
/*declare @IDCliente int = 5546348 */
/*
SELECT * FROM screening_empleados_out WHERE cliente=@IDCliente AND (cuit IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)
OR cuit_int IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX) OR cuit_soc_vinculada IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX))
*/
select distinct cuiT from BLACKLIST_PROVEEDORES_AMX where CUIT in (20244242430,30712079335,30562252084)
select distinct cuiL from screening_empleados where CUIL in (20244242430,30712079335,30562252084) AND cliente=@IDCliente
SELECT * FROM screening_prov WHERE cuit IN ('20244242430','30712079335','30562252084')
/*
SELECT * FROM EMPLEADOS WHERE CUIL in ('20386904422')
*/
/* CRUCE AMX PROVEEDORES - EMPLEADOS CON BLACKLIST*/
select DISTINCT seo.razon_social as CLIENTE,seo.cuit as [CUIT CLIENTE],seo.soc_vinculada as [EMPLEADO DEL CLIENTE],
seo.cuit_soc_vinculada as [CUIT DEL EMPLEADO DEL CLIENTE],
CASE
WHEN seo.tipo_reg = 'FAMIL_EMP' THEN 'FAMILIAR DE EMPLEADO'
END AS VINCULACION,/*SEO.tipo_reg,SPO.tipo_reg AS TREGSPO,*/
spo.razon_social as EMPLEADOR,spo.cuit as [CUIT EMPLEADOR],padrones.nombre_persona as [EMPLEADO], /*seo.nombre_integrante as [EMPLEADO],*/
seo.cuit_int as [CUIT DEL EMPLEADO]
from screening_prov_out spo
--inner join screening_empleados_out seo on seo.cuit = spo.cuit
inner join screening_empleados_out seo on seo.cuit_int = spo.cuit_int
inner join padrones on seo.cuit_int = padrones.identificacion
where SEO.tipo_reg <> '2EMPLEOS' and seo.cliente=@IDCliente
and seo.tipo_reg is not null
and seo.razon_social is not null
and (seo.cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR (SEO.cuit IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)
OR SEO.cuit_int IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX) OR SEO.cuit_soc_vinculada IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)))
/*
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT CUIL FROM empleados WHERE CUIT='30663288497' AND PERIODO=1705) OR SEO.cuit_int IN (SELECT DISTINCT CUIL FROM empleados WHERE CUIT='30663288497' AND PERIODO=1705))
*/
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SEO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SPO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SPO.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
)
union all
select DISTINCT seo.razon_social as CLIENTE,seo.cuit as [CUIT CLIENTE],
seo.nombre_integrante as [EMPLEADO DEL CLIENTE],
seo.cuit_int as [CUIT DEL EMPLEADO DEL CLIENTE],
CASE
WHEN seo.tipo_reg = '2EMPLEOS' THEN 'MULTIPLES EMPLEADORES'
END AS VINCULACION,/*SEO.tipo_reg,SPO.tipo_reg AS TREGSPO,*/
seo.soc_vinculada as EMPLEADOR,seo.cuit_soc_vinculada as [CUIT EMPLEADOR],
seo.nombre_integrante as [EMPLEADO],
seo.cuit_int as [CUIT DEL EMPLEADO]
from screening_prov_out spo
--inner join screening_empleados_out seo on seo.cuit = spo.cuit
right join screening_empleados_out seo on seo.cuit_int = spo.cuit_int
where SEO.tipo_reg = '2EMPLEOS' and seo.cliente=@IDCliente
and seo.tipo_reg is not null
and seo.razon_social is not null
and (seo.cuit_int in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
OR (SEO.cuit IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)
OR SEO.cuit_int IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX) OR SEO.cuit_soc_vinculada IN (select distinct cuiT from BLACKLIST_PROVEEDORES_AMX)))
AND (seo.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SEO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SPO.cuit_int IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
OR SPO.cuit_soc_vinculada IN (SELECT DISTINCT cuit_int FROM screening_empleados_out WHERE cliente=@IDCliente )
)
order by seo.soc_vinculada ASC
/* CRUCE AMX PROVEEDORES - EMPLEADOS CON BLACKLIST*/
select SPOBLAMX.*,SEOBLAMX.* from screening_empleados_out_blacklist_amx SEOBLAMX inner join screening_prov_out_blacklist_amx SPOBLAMX
on SEOBLAMX.cuit_int = SPOBLAMX.cuit_int
where SEOBLAMX.cuit_int in (select distinct cuit from screening_prov where cliente = 5546348 and cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
union all
select SPOBLAMX.*,SEOBLAMX.* from screening_empleados_out_blacklist_amx SEOBLAMX inner join screening_prov_out_blacklist_amx SPOBLAMX
on SEOBLAMX.cuit_soc_vinculada = SPOBLAMX.cuit_soc_vinculada
where SEOBLAMX.cuit_soc_vinculada in (select distinct cuit from screening_prov where cliente = 5546348 and cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX))
order by SEOBLAMX.nombre_persona
select * from screening_prov where cliente = 5546348 and cuit not in (SELECT CUIT FROM BLACKLIST_PROVEEDORES_AMX)
and cuit in (30521900470)
order by razon_social
select * from empleados where cuil ='27258888184'
select * from padrones where identificacion in (
SELECT cuit FROM BLACKLIST_PROVEEDORES_AMX where cuit in (27258888184,30711189692,20174242888))
select * from padrones where identificacion in (
select cuit from BLACKLIST_PROVEEDORES_AMX where cuit in (30521900470,27235374760,27258888184,30711189692,20174242888))
select * from padrones where identificacion='30711189692'
END
GO
Depends On
8Used By
No items found