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_nuevo
/*
exec screening_out_nuevo
*/
AS
BEGIN
/*
select distinct tipo_reg from screening_prov_out where cliente = 843015515
select distinct tipo_reg from screening_empleados_out where cliente = 843015515 and tipo_reg like '%_F'
*/
declare @tabla table (parte varchar(100), vinculacion varchar(100), nombre varchar(100), cuit varchar(20), nombre_viculado varchar(100), cuit_vinculado varchar(100), otros varchar(100))
declare @IDCliente int = 843015515
insert into @tabla
-- EMPLEADOS
select distinct 'EMPLEADOS' as parte, 'EMPLEADO: PARTICIPACION EN EMPRESA' as vinculacion ,padrones.nombre_persona as nombre /*nombre_integrante as nombre*/, cuit_int as cuit, soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado, null as otros
from screening_empleados_out
inner join padrones on padrones.identificacion = screening_empleados_out.cuit_int
where tipo_reg in ( 'SOC_CONS_1','ACCIONISTA','SOCIOS') and cliente = @IDCliente
UNION
select distinct 'EMPLEADOS' as parte, 'FAMILIAR EMPLEADO' as vinculacion, soc_vinculada as nombre, cuit_soc_vinculada as cuit, nombre_integrante as nombre_vinculado, cuit_int as cuit_vinculado, null as otros
from screening_empleados_out a where tipo_reg in ( 'FAMIL_EMP') and cliente = @IDCliente
UNION
select distinct 'EMPLEADOS' as parte, 'FAMILIAR EMPLEADO: PARTICIPACION EN EMPRESA' as vinculacion, nombre_integrante as nombre, cuit_int as cuit, soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado, null as otros
from screening_empleados_out a where tipo_reg in ( 'ACCIONIS_F','EDICTOS_F','SOCIEDAD_F','SOCIOS_F') and cliente = @IDCliente
UNION
select distinct 'EMPLEADOS' as parte, 'EMPLEADO: OTRO EMPLEO' as vinculacion,padrones.nombre_persona as nombre,/* nombre_integrante as nombre,*/ cuit_int as cuit, soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado,
e.remimp as otros
from screening_empleados_out a
left join empleados e
on e.cuit = a.cuit_soc_vinculada and e.cuil = a.cuit_int and remimp > 0 and e.periodo = (select max (periodo) from empleados b
where b.cuit = e.cuit and b.cuil = e.cuil)
inner join padrones on padrones.identificacion = a.cuit_int
where tipo_reg in ( '2EMPLEO') and cliente = @IDCliente
--declare @IDCliente int = 843015515
/*
5) Listar PERSONAS con otro empleador que SÍ figure en EMPRESAS (si es 0, verificar último monto del período en que haya percibido ingresos).
*/
select distinct 'EMPLEADOS' as parte, 'EMPLEADO: OTRO EMPLEO DE LISTA DE PROVEEDORES ' as vinculacion,
padrones.nombre_persona as nombre,/* nombre_integrante as nombre,*/ cuit_int as cuit,
soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado,
e.remimp as otros
from screening_empleados_out a
left join empleados e on (e.cuit = a.cuit_soc_vinculada and e.cuil = a.cuit_int and remimp > 0 and e.periodo in ('dbo.PeriodoMenosDos()','201711','201710','201709','201708') /* = (select max (periodo) from empleados b
where b.cuit = e.cuit and b.cuil = e.cuil)*/)
inner join padrones on padrones.identificacion = a.cuit_int
where tipo_reg in ( '2EMPLEO') and cliente = @IDCliente
and a.cuit_soc_vinculada in (select distinct cuit from screening_prov where cliente = @IDCliente)
AND (A.cuit_int IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente) OR A.cuit_integrante IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente) )
UNION
--declare @IDCliente int = 843015515
/*
4) Listar PERSONAS con otro empleador que NO figure en EMPRESAS indicando último sueldo percibido y fecha (si es 0, verificar último monto del período en que haya percibido ingresos).
*/
select distinct 'EMPLEADOS' as parte, 'EMPLEADO: OTRO EMPLEO OTROS PROVEEDORES ' as vinculacion,
padrones.nombre_persona as nombre,/* nombre_integrante as nombre,*/ cuit_int as cuit,
soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado,
e.remimp as otros
from screening_empleados_out a
left join empleados e on (e.cuit = a.cuit_soc_vinculada and e.cuil = a.cuit_int and remimp > 0 and e.periodo in ('dbo.PeriodoMenosDos()','201711','201710','201709','201708') /* = (select max (periodo) from empleados b
where b.cuit = e.cuit and b.cuil = e.cuil)*/)
inner join padrones on padrones.identificacion = a.cuit_int
where tipo_reg in ( '2EMPLEO') and cliente = @IDCliente
and a.cuit_soc_vinculada not in (select distinct cuit from screening_prov where cliente = @IDCliente)
AND (A.cuit_int IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente) OR A.cuit_integrante IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente) )
union
-- PROVEEDORES
select distinct 'PROVEEDORES' as parte, 'PROVEEDOR: DIRECTIVO' as vinculacion, soc_vinculada as nombre, cuit_soc_vinculada as cuit, nombre_integrante as nombre_vinculado, iif(isnull(cuit_integrante,'')='',cuit_int, cuit_integrante) as cuit_vinculado, null as otros
from screening_prov_out a where tipo_reg in ('DIRECTOR','SOC_CONS_1','ACCIONISTA') and cliente = @IDCliente
and (isnull(cuit_int,'') != '' or isnull(cuit_integrante,'') != '')
union
select distinct 'PROVEEDORES' as parte, 'FAMILIAR PROVEEDOR' as vinculacion, soc_vinculada as nombre, cuit_soc_vinculada as cuit, nombre_integrante as nombre_vinculado, iif(isnull(cuit_integrante,'')='',cuit_int, cuit_integrante) as cuit_vinculado , null as otros
from screening_prov_out a where tipo_reg in ('FAMILIAR') and cliente = @IDCliente
and (isnull(cuit_int,'') != '' or isnull(cuit_integrante,'') != '')
union
select distinct 'PROVEEDORES' as parte, 'FAMILIAR DIRECTIVO' as vinculacion, soc_vinculada as nombre, cuit_soc_vinculada as cuit, nombre_integrante as nombre_vinculado, iif(isnull(cuit_integrante,'')='',cuit_int, cuit_integrante) as cuit_vinculado , null as otros
from screening_prov_out a where tipo_reg in ('FAMIL_INT') and cliente = @IDCliente
and (isnull(cuit_int,'') != '' or isnull(cuit_integrante,'') != '')
union
select distinct 'PROVEEDORES' as parte, 'FAMILIAR PROVEEDOR / FAMILIAR DIRECTIVO: ACTUACION' as vinculacion, nombre_integrante as nombre, iif(isnull(cuit_integrante,'')='',cuit_int, cuit_integrante) as cuit , soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_vinculado, null as otros
from screening_prov_out a where tipo_reg in ('FAM_SOC') and cliente = @IDCliente
and (isnull(cuit_int,'') != '' or isnull(cuit_integrante,'') != '')
--- COINCIDENCIAS
union
select distinct 'COINCIDENCIAS', 'CUIT PROVISTO COMO EMPLEADO Y PROVEEDOR' as TIPO, e.nombre_empleado, e.cuil, p.razon_social, p.cuit, null as otros
from screening_empleados e
inner join screening_prov p on p.cliente = 843015515 and p.cuit = e.cuil
where e.cliente = 843015515
select 'General' as TIPO, * from @tabla t where parte in ('PROVEEDORES','EMPLEADOS')
union
select 'ESTA en Empleados' as TIPO, * from @tabla t where parte = 'PROVEEDORES' and exists (select * from screening_empleados e where e.cuil = t.cuit_vinculado and e.cliente = @IDCliente)
union
select 'ESTA en Proveedores' as TIPO, * from @tabla t where parte = 'EMPLEADOS' and exists (select * from screening_prov e where e.cuit = t.cuit_vinculado and e.cliente = @IDCliente)
union
select 'COINCIDENCIAS: CUIT PROVISTO COMO EMPLEADO Y PROVEEDOR' as TIPO, * from @tabla t where parte = 'COINCIDENCIAS'
END
GO
GO
CREATE OR ALTER PROCEDURE dbo.screening_out_nuevo
/*
exec screening_out_nuevo
*/
AS
BEGIN
/*
select distinct tipo_reg from screening_prov_out where cliente = 843015515
select distinct tipo_reg from screening_empleados_out where cliente = 843015515 and tipo_reg like '%_F'
*/
declare @tabla table (parte varchar(100), vinculacion varchar(100), nombre varchar(100), cuit varchar(20), nombre_viculado varchar(100), cuit_vinculado varchar(100), otros varchar(100))
declare @IDCliente int = 843015515
insert into @tabla
-- EMPLEADOS
select distinct 'EMPLEADOS' as parte, 'EMPLEADO: PARTICIPACION EN EMPRESA' as vinculacion ,padrones.nombre_persona as nombre /*nombre_integrante as nombre*/, cuit_int as cuit, soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado, null as otros
from screening_empleados_out
inner join padrones on padrones.identificacion = screening_empleados_out.cuit_int
where tipo_reg in ( 'SOC_CONS_1','ACCIONISTA','SOCIOS') and cliente = @IDCliente
UNION
select distinct 'EMPLEADOS' as parte, 'FAMILIAR EMPLEADO' as vinculacion, soc_vinculada as nombre, cuit_soc_vinculada as cuit, nombre_integrante as nombre_vinculado, cuit_int as cuit_vinculado, null as otros
from screening_empleados_out a where tipo_reg in ( 'FAMIL_EMP') and cliente = @IDCliente
UNION
select distinct 'EMPLEADOS' as parte, 'FAMILIAR EMPLEADO: PARTICIPACION EN EMPRESA' as vinculacion, nombre_integrante as nombre, cuit_int as cuit, soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado, null as otros
from screening_empleados_out a where tipo_reg in ( 'ACCIONIS_F','EDICTOS_F','SOCIEDAD_F','SOCIOS_F') and cliente = @IDCliente
UNION
select distinct 'EMPLEADOS' as parte, 'EMPLEADO: OTRO EMPLEO' as vinculacion,padrones.nombre_persona as nombre,/* nombre_integrante as nombre,*/ cuit_int as cuit, soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado,
e.remimp as otros
from screening_empleados_out a
left join empleados e
on e.cuit = a.cuit_soc_vinculada and e.cuil = a.cuit_int and remimp > 0 and e.periodo = (select max (periodo) from empleados b
where b.cuit = e.cuit and b.cuil = e.cuil)
inner join padrones on padrones.identificacion = a.cuit_int
where tipo_reg in ( '2EMPLEO') and cliente = @IDCliente
--declare @IDCliente int = 843015515
/*
5) Listar PERSONAS con otro empleador que SÍ figure en EMPRESAS (si es 0, verificar último monto del período en que haya percibido ingresos).
*/
select distinct 'EMPLEADOS' as parte, 'EMPLEADO: OTRO EMPLEO DE LISTA DE PROVEEDORES ' as vinculacion,
padrones.nombre_persona as nombre,/* nombre_integrante as nombre,*/ cuit_int as cuit,
soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado,
e.remimp as otros
from screening_empleados_out a
left join empleados e on (e.cuit = a.cuit_soc_vinculada and e.cuil = a.cuit_int and remimp > 0 and e.periodo in ('dbo.PeriodoMenosDos()','201711','201710','201709','201708') /* = (select max (periodo) from empleados b
where b.cuit = e.cuit and b.cuil = e.cuil)*/)
inner join padrones on padrones.identificacion = a.cuit_int
where tipo_reg in ( '2EMPLEO') and cliente = @IDCliente
and a.cuit_soc_vinculada in (select distinct cuit from screening_prov where cliente = @IDCliente)
AND (A.cuit_int IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente) OR A.cuit_integrante IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente) )
UNION
--declare @IDCliente int = 843015515
/*
4) Listar PERSONAS con otro empleador que NO figure en EMPRESAS indicando último sueldo percibido y fecha (si es 0, verificar último monto del período en que haya percibido ingresos).
*/
select distinct 'EMPLEADOS' as parte, 'EMPLEADO: OTRO EMPLEO OTROS PROVEEDORES ' as vinculacion,
padrones.nombre_persona as nombre,/* nombre_integrante as nombre,*/ cuit_int as cuit,
soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_viculado,
e.remimp as otros
from screening_empleados_out a
left join empleados e on (e.cuit = a.cuit_soc_vinculada and e.cuil = a.cuit_int and remimp > 0 and e.periodo in ('dbo.PeriodoMenosDos()','201711','201710','201709','201708') /* = (select max (periodo) from empleados b
where b.cuit = e.cuit and b.cuil = e.cuil)*/)
inner join padrones on padrones.identificacion = a.cuit_int
where tipo_reg in ( '2EMPLEO') and cliente = @IDCliente
and a.cuit_soc_vinculada not in (select distinct cuit from screening_prov where cliente = @IDCliente)
AND (A.cuit_int IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente) OR A.cuit_integrante IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente) )
union
-- PROVEEDORES
select distinct 'PROVEEDORES' as parte, 'PROVEEDOR: DIRECTIVO' as vinculacion, soc_vinculada as nombre, cuit_soc_vinculada as cuit, nombre_integrante as nombre_vinculado, iif(isnull(cuit_integrante,'')='',cuit_int, cuit_integrante) as cuit_vinculado, null as otros
from screening_prov_out a where tipo_reg in ('DIRECTOR','SOC_CONS_1','ACCIONISTA') and cliente = @IDCliente
and (isnull(cuit_int,'') != '' or isnull(cuit_integrante,'') != '')
union
select distinct 'PROVEEDORES' as parte, 'FAMILIAR PROVEEDOR' as vinculacion, soc_vinculada as nombre, cuit_soc_vinculada as cuit, nombre_integrante as nombre_vinculado, iif(isnull(cuit_integrante,'')='',cuit_int, cuit_integrante) as cuit_vinculado , null as otros
from screening_prov_out a where tipo_reg in ('FAMILIAR') and cliente = @IDCliente
and (isnull(cuit_int,'') != '' or isnull(cuit_integrante,'') != '')
union
select distinct 'PROVEEDORES' as parte, 'FAMILIAR DIRECTIVO' as vinculacion, soc_vinculada as nombre, cuit_soc_vinculada as cuit, nombre_integrante as nombre_vinculado, iif(isnull(cuit_integrante,'')='',cuit_int, cuit_integrante) as cuit_vinculado , null as otros
from screening_prov_out a where tipo_reg in ('FAMIL_INT') and cliente = @IDCliente
and (isnull(cuit_int,'') != '' or isnull(cuit_integrante,'') != '')
union
select distinct 'PROVEEDORES' as parte, 'FAMILIAR PROVEEDOR / FAMILIAR DIRECTIVO: ACTUACION' as vinculacion, nombre_integrante as nombre, iif(isnull(cuit_integrante,'')='',cuit_int, cuit_integrante) as cuit , soc_vinculada as nombre_vinculado, cuit_soc_vinculada as cuit_vinculado, null as otros
from screening_prov_out a where tipo_reg in ('FAM_SOC') and cliente = @IDCliente
and (isnull(cuit_int,'') != '' or isnull(cuit_integrante,'') != '')
--- COINCIDENCIAS
union
select distinct 'COINCIDENCIAS', 'CUIT PROVISTO COMO EMPLEADO Y PROVEEDOR' as TIPO, e.nombre_empleado, e.cuil, p.razon_social, p.cuit, null as otros
from screening_empleados e
inner join screening_prov p on p.cliente = 843015515 and p.cuit = e.cuil
where e.cliente = 843015515
select 'General' as TIPO, * from @tabla t where parte in ('PROVEEDORES','EMPLEADOS')
union
select 'ESTA en Empleados' as TIPO, * from @tabla t where parte = 'PROVEEDORES' and exists (select * from screening_empleados e where e.cuil = t.cuit_vinculado and e.cliente = @IDCliente)
union
select 'ESTA en Proveedores' as TIPO, * from @tabla t where parte = 'EMPLEADOS' and exists (select * from screening_prov e where e.cuit = t.cuit_vinculado and e.cliente = @IDCliente)
union
select 'COINCIDENCIAS: CUIT PROVISTO COMO EMPLEADO Y PROVEEDOR' as TIPO, * from @tabla t where parte = 'COINCIDENCIAS'
END
GO
Depends On
6Used By
No items found