Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
5| Name | Data Type | Length | Description |
|---|---|---|---|
| @IDCliente | int | 4 | |
| @StepID | int | 4 | |
| @fechaProceso | date | 3 | |
| @Proceso | smallint | 2 | |
| @Tipo | varchar | 3 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE OR ALTER PROCEDURE dbo.screening_empleados_2018
@IDCliente int,
@StepID int,
@fechaProceso date,
@Proceso smallint,
@Tipo varchar(3)
AS
/*
exec screening_empleados_2018_DEV 7777,1,null,5,'EMP'
exec screening_empleados_2018_DEV 7777,2,null,5,'FAM'
*/
BEGIN
if(@StepID=55) /*INSERTO TODOS LOS EMPLEADOS QUE NO PUDIERON APERTURARSE COMO N/A PARA GARANTIZAR QUE TODOS HAYAN SIDO ANALIZADOS*/
BEGIN
insert into screening_empleados_out (cliente,razon_social,cuit,titulo,cargo,tipo_reg,nro_proceso)
select @IDCliente as cliente,nombre_empleado as razon_social_prov,cuil as cuit_prov,'N/A' as titulo,'N/A' as cargo,'N/A' as tipo_reg,@Proceso as nro_proceso
from screening_EMPLEADOS where cliente = 7777 and nro_proceso = 5 AND tipo_reg= @Tipo
AND CUIL NOT IN (select distinct cuit from screening_empleados_out where Cliente = @IDCliente and nro_proceso = @Proceso AND tipo_reg not like '%_F%')
END
if(@StepID=0)
BEGIN
/* REGISTROS QUE FUERON PROVISTOS POR EL CLIENTE Y NO PUDIERON SER APERTURADOS */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
IIF(nombre_integrante IS NULL, 'N/A',NOMBRE_INTEGRANTE) as LINKEDPERSONORENTITY,
IIF(cuit_integrante IS NULL,'N/A',cuit_integrante) as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'N/A',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION /*, + 'DATO AFIP' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 INNER join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND screening_prov.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'N/A'
/*
AND cuit_integrante is not null
*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND tipo_reg = @Tipo)
/*
ORDER BY razon_social_prov ASC,LINKEDPERSONORENTITY asc
*/
UNION
/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
nombre_integrante as LINKEDPERSONORENTITY,cuit_integrante as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO2',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION /*, + 'DATO AFIP' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'AFIP'
/*AND cuit_integrante is not null*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND tipo_reg = @Tipo)
UNION
/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
nombre_integrante as LINKEDPERSONORENTITY,cuit_integrante as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO3',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'SOC_CONS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'SOC_CONS_1'
/*AND cuit_integrante is not null*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
union
/* EMPRESAS ACCIONISTAS DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
soc_vinculada as LINKEDPERSONORENTITY,cuit_soc_vinculada as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO4',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'ACCIONISTA'
AND cuit_integrante is null
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
UNION
/* EMPRESAS ACCIONISTAS DE EMPRESAS NO PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
soc_vinculada as LINKEDPERSONORENTITY,cuit_soc_vinculada as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO5',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where cuit_integrante is null and screening_prov_out2.tipo_reg = 'ACCIONISTA'
AND cuit_prov NOT IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
UNION
/* PERSONAS ACCIONISTAS DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
nombre_integrante as LINKEDPERSONORENTITY,cuit_integrante as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO6',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where cuit_integrante is not null and screening_prov_out2.tipo_reg = 'ACCIONISTA'
AND cuit_soc_vinculada IS NULL
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
union
/* PERSONAS DIRECTORES DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
nombre_integrante as LINKEDPERSONORENTITY,cuit_integrante as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO7',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'SOCIOS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'DIRECTOR'
AND cuit_integrante is not null
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
UNION
/* EMPRESAS DIRECTORES DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
soc_vinculada as LINKEDPERSONORENTITY,cuit_soc_vinculada as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO8',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'SOCIOS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'DIRECTOR'
AND cuit_integrante is null
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
ORDER BY razon_social_prov ASC,LINKEDPERSONORENTITY asc
END
if (@StepID=1)
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 1: Busca PARTICIPACIONES de los empleados en sociedades en las tablas sociedades_constituciones,relaciones y edictos, que el cuit del edicto sea el del empleado : */
--------------------------------------------------------------------------------------------------------------------
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
select DISTINCT screening_empleados.nombre_empleado AS empleado,screening_empleados.cuil AS cuil,
screening_empleados.cliente as cliente,
getdate() as created,
null AS titulo,'ACCIONISTA' AS cargo,
/*
p1.nombre_persona as soc_vinculada,p1.identificacion as soc_vinculada,
*/
iif(substring(accionistas.cuit_acc,1,1)='2',p2.nombre_persona,null) as soc_vinculada,
iif(substring(accionistas.cuit_acc,1,1)='2',p2.identificacion,null) as cuit_soc_vinculada,
/*
P2.nombre_persona AS nombre_integrante,accionistas.cuit_acc as cuit_integrante,
*/
iif(substring(accionistas.cuit_acc,1,1)='3',p2.nombre_persona,null) as nombre_integrante,
iif(substring(accionistas.cuit_acc,1,1)='3',accionistas.cuit_acc,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'ACCIONISTA',@Proceso AS nro_proceso
from screening_empleados
inner join accionistas on screening_empleados.cuil = accionistas.cuit_acc
inner join padrones p1 on accionistas.cuit_acc = p1.identificacion
left join padrones p2 on accionistas.cuit_sociedad = p2.identificacion
where accionistas.cuit_acc in (select distinct ltrim(rtrim(cuil)) from screening_empleados where cliente = @IDCliente and screening_empleados.nro_proceso = @Proceso and screening_empleados.tipo_reg = @Tipo)
and screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS COMO EMPRESAS */
select screening_empleados.nombre_empleado AS empleado,screening_empleados.cuil AS cuit_prov,screening_empleados.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
iif(substring(edictos.cuit_integrante,1,1)='3',padrones.nombre_persona,null) as soc_vinculada,
iif(substring(edictos.cuit_integrante,1,1)='3',edictos.cuit_integrante,null) as cuit_soc_vinculada,
iif(substring(edictos.cuit_integrante,1,1)='2',padrones.nombre_persona,null) as nombre_integrante,
iif(substring(edictos.cuit_integrante,1,1)='2',edictos.cuit_integrante,null) as 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,@Proceso AS nro_proceso
from screening_empleados
inner join edictos on screening_empleados.cuil = edictos.cuit_sociedad
left join padrones on edictos.cuit_integrante = padrones.identificacion
left join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS PERSONAS */
select screening_empleados.nombre_empleado AS empleado,screening_empleados.cuil AS cuit_prov,screening_empleados.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
iif(substring(edictos.cuit_sociedad,1,1)='3',P2.nombre_persona,null) as soc_vinculada,
iif(substring(edictos.cuit_sociedad,1,1)='3',edictos.cuit_sociedad,null) as cuit_soc_vinculada,
iif(substring(edictos.cuit_integrante,1,1)='2',null,'a') as nombre_integrante,
iif(substring(edictos.cuit_integrante,1,1)='2',null,'a') as 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,@Proceso AS nro_proceso
from screening_empleados
inner join edictos on screening_empleados.cuil = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
left join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES COMO PERSONAS*/
select screening_empleados.nombre_empleado AS empleado ,screening_empleados.cuil AS cuil,
screening_empleados.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.razon_social,null) as soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.documento_soc,null) as cuit_soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.cuit_int,null) 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,@Proceso AS nro_proceso
from screening_empleados
inner join sociedades_constituciones on (screening_empleados.cuil = sociedades_constituciones.cuit_integrante)
left join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
/*
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
*/
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES COMO PERSONAS*/
select screening_empleados.nombre_empleado AS empleado ,screening_empleados.cuil AS cuil,
screening_empleados.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.razon_social,null) as soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.documento_soc,null) as cuit_soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.cuit_int,null) 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,@Proceso AS nro_proceso
from screening_empleados
inner join sociedades_constituciones on (screening_empleados.cuil = sociedades_constituciones.cuit_int)
left join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
/*
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
*/
union all
select p2.nombre_persona as empleado,p2.identificacion AS cuil,
screening_empleados.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
iif(substring(p3.identificacion,1,1)='3',p3.nombre_persona,null) as soc_vinculada,
iif(substring(p3.identificacion,1,1)='3',p3.identificacion,null) as cuit_soc_vinculada,
iif(substring(p3.identificacion,1,1)='2',p3.nombre_persona,null) as nombre_integrante,
iif(substring(p3.identificacion,1,1)='2',p3.identificacion,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null as contenido_archivo,relaciones.fecha_relacion,'AFIP' as tipo_reg /*SOC_CONS_1*/,@Proceso AS nro_proceso
from screening_empleados
inner join relaciones on screening_empleados.cuil = relaciones.cuita
inner join padrones p3 on relaciones.cuita = p3.identificacion
inner join padrones p2 on relaciones.cuitb = p2.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 screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
union all
/*buscamos sobre la tabla relaciones como personas */
select p2.nombre_persona as empleado,p2.identificacion AS cuil,
screening_empleados.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
iif(substring(p3.identificacion,1,1)='3',p3.nombre_persona,null) as soc_vinculada,
iif(substring(p3.identificacion,1,1)='3',p3.identificacion,null) as cuit_soc_vinculada,
iif(substring(p3.identificacion,1,1)='2',p3.nombre_persona,null) as nombre_integrante,
iif(substring(p3.identificacion,1,1)='2',p3.identificacion,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null as contenido_archivo,relaciones.fecha_relacion,'AFIP' as tipo_reg /*SOC_CONS_1*/,@Proceso AS nro_proceso
from screening_empleados
inner join relaciones on screening_empleados.cuil = relaciones.cuitb
inner join padrones p3 on relaciones.cuita = p3.identificacion
inner join padrones p2 on relaciones.cuitb = p2.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 screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
order by empleado
end
if (@StepID=2) /* trabajo con los familiares de empleados*/
begin
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
/* relaciones */
select
ptit.nombre_persona as empleado,ptit.identificacion as cuil,
screening_empleados.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
iif(substring(p3.identificacion,1,1)='3',p3.nombre_persona,null) as soc_vinculada,
iif(substring(p3.identificacion,1,1)='3',p3.identificacion,null) as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,screening_empleados.cuil AS cuit_integrante,
p2.nombre_persona as nombre_familiar,screening_empleados.cuil AS cuit_familiar,
null as contenido_archivo,relaciones.fecha_relacion,'AFIP_F' as tipo_reg,@Proceso AS nro_proceso
from screening_empleados
inner join relaciones on screening_empleados.cuil = relaciones.cuitb
inner join padrones p3 on relaciones.cuita = p3.identificacion
inner join padrones p2 on screening_empleados.cuil = p2.identificacion
inner join padrones ptit on screening_empleados.cuit_titular = ptit.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 screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = 'FAM'
AND screening_empleados.cuil not in (select distinct cuil from screening_empleados where nro_proceso = @Proceso and tipo_reg = 'EMP')
/*
and relaciones.id_tipo_relacion in (1,2)
*/
union all
/* accionistas */
select DISTINCT
ptit.nombre_persona as empleado,ptit.identificacion as cuil,
screening_empleados.cliente as cliente,
getdate() as created,
null AS titulo,'ACCIONISTA' AS cargo,
iif(substring(accionistas.cuit_acc,1,1)='2',p2.nombre_persona,null) as soc_vinculada,
iif(substring(accionistas.cuit_acc,1,1)='2',p2.identificacion,null) as cuit_soc_vinculada,
p1.nombre_persona as nombre_integrante,screening_empleados.cuil AS cuit_integrante,
p1.nombre_persona as nombre_familiar,screening_empleados.cuil AS cuit_familiar,
null as contenido_archivo,null as fecha_relacion,
tipo_reg = 'ACCIONIS_F',@Proceso AS nro_proceso
from screening_empleados
inner join accionistas on screening_empleados.cuil = accionistas.cuit_acc
inner join padrones p1 on accionistas.cuit_acc = p1.identificacion
inner join padrones p2 on accionistas.cuit_sociedad = p2.identificacion
inner join padrones ptit on screening_empleados.cuit_titular = ptit.identificacion
where accionistas.cuit_acc in (select distinct ltrim(rtrim(cuil)) from screening_empleados where cliente = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = 'FAM')
and screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = 'FAM'
AND screening_empleados.cuil not in (select distinct cuil from screening_empleados where nro_proceso = @Proceso and tipo_reg = 'EMP')
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES COMO PERSONAS*/
select ptit.nombre_persona as empleado,ptit.identificacion as cuil,
/*
screening_empleados.nombre_empleado AS empleado ,screening_empleados.cuil AS cuil,
*/
screening_empleados.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
p2.nombre_persona as soc_vinculada,
p2.identificacion as cuit_soc_vinculada,
padrones.nombre_persona as nombre_integrante,screening_empleados.cuil AS cuit_integrante,
padrones.nombre_persona as nombre_familiar,screening_empleados.cuil AS cuit_familiar,
sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion,
'SOCCONS1_F' as tipo_reg,@Proceso AS nro_proceso
from screening_empleados
inner join sociedades_constituciones on (screening_empleados.cuil = sociedades_constituciones.cuit_int)
inner join padrones on screening_empleados.cuil = padrones.identificacion
left join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
inner join padrones ptit on screening_empleados.cuit_titular = ptit.identificacion
where screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = 'FAM'
AND screening_empleados.cuil not in (select distinct cuil from screening_empleados where cliente = @IDCliente and nro_proceso = @Proceso and tipo_reg = 'EMP')
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS PARA FAMILIARES */
select ptit.nombre_persona as empleado,ptit.identificacion as cuil,
screening_empleados.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,screening_empleados.cuil AS cuit_integrante,
padrones.nombre_persona as nombre_familiar,screening_empleados.cuil AS cuit_familiar,
edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
,'EDICTO_F' as tipo_reg,@Proceso AS nro_proceso
from screening_empleados
inner join edictos on screening_empleados.cuil = edictos.cuit_integrante
left join padrones on edictos.cuit_integrante = padrones.identificacion
left join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
inner join padrones ptit on screening_empleados.cuit_titular = ptit.identificacion
and cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = 'FAM'
AND screening_empleados.cuil not in (select distinct cuil from screening_empleados where cliente = @IDCliente and nro_proceso = @Proceso and tipo_reg = 'EMP')
end
END
GO
GO
CREATE OR ALTER PROCEDURE dbo.screening_empleados_2018
@IDCliente int,
@StepID int,
@fechaProceso date,
@Proceso smallint,
@Tipo varchar(3)
AS
/*
exec screening_empleados_2018_DEV 7777,1,null,5,'EMP'
exec screening_empleados_2018_DEV 7777,2,null,5,'FAM'
*/
BEGIN
if(@StepID=55) /*INSERTO TODOS LOS EMPLEADOS QUE NO PUDIERON APERTURARSE COMO N/A PARA GARANTIZAR QUE TODOS HAYAN SIDO ANALIZADOS*/
BEGIN
insert into screening_empleados_out (cliente,razon_social,cuit,titulo,cargo,tipo_reg,nro_proceso)
select @IDCliente as cliente,nombre_empleado as razon_social_prov,cuil as cuit_prov,'N/A' as titulo,'N/A' as cargo,'N/A' as tipo_reg,@Proceso as nro_proceso
from screening_EMPLEADOS where cliente = 7777 and nro_proceso = 5 AND tipo_reg= @Tipo
AND CUIL NOT IN (select distinct cuit from screening_empleados_out where Cliente = @IDCliente and nro_proceso = @Proceso AND tipo_reg not like '%_F%')
END
if(@StepID=0)
BEGIN
/* REGISTROS QUE FUERON PROVISTOS POR EL CLIENTE Y NO PUDIERON SER APERTURADOS */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
IIF(nombre_integrante IS NULL, 'N/A',NOMBRE_INTEGRANTE) as LINKEDPERSONORENTITY,
IIF(cuit_integrante IS NULL,'N/A',cuit_integrante) as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'N/A',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION /*, + 'DATO AFIP' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 INNER join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND screening_prov.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'N/A'
/*
AND cuit_integrante is not null
*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND tipo_reg = @Tipo)
/*
ORDER BY razon_social_prov ASC,LINKEDPERSONORENTITY asc
*/
UNION
/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
nombre_integrante as LINKEDPERSONORENTITY,cuit_integrante as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO2',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION /*, + 'DATO AFIP' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'AFIP'
/*AND cuit_integrante is not null*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND tipo_reg = @Tipo)
UNION
/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
nombre_integrante as LINKEDPERSONORENTITY,cuit_integrante as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO3',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'SOC_CONS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'SOC_CONS_1'
/*AND cuit_integrante is not null*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
union
/* EMPRESAS ACCIONISTAS DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
soc_vinculada as LINKEDPERSONORENTITY,cuit_soc_vinculada as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO4',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'ACCIONISTA'
AND cuit_integrante is null
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
UNION
/* EMPRESAS ACCIONISTAS DE EMPRESAS NO PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
soc_vinculada as LINKEDPERSONORENTITY,cuit_soc_vinculada as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO5',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where cuit_integrante is null and screening_prov_out2.tipo_reg = 'ACCIONISTA'
AND cuit_prov NOT IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
UNION
/* PERSONAS ACCIONISTAS DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
nombre_integrante as LINKEDPERSONORENTITY,cuit_integrante as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO6',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where cuit_integrante is not null and screening_prov_out2.tipo_reg = 'ACCIONISTA'
AND cuit_soc_vinculada IS NULL
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
union
/* PERSONAS DIRECTORES DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
nombre_integrante as LINKEDPERSONORENTITY,cuit_integrante as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO7',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'SOCIOS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'DIRECTOR'
AND cuit_integrante is not null
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
UNION
/* EMPRESAS DIRECTORES DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','EMPLOYEE') AS PERSON_TYPE,
CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
soc_vinculada as LINKEDPERSONORENTITY,cuit_soc_vinculada as LINKTAXID,screening_prov_out2.cuit_familiar as PARENT_TAX_ID,screening_prov_out2.nombre_familiar as PARENT_NAME,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO8',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + 'SOCIOS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'DIRECTOR'
AND cuit_integrante is null
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)
ORDER BY razon_social_prov ASC,LINKEDPERSONORENTITY asc
END
if (@StepID=1)
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 1: Busca PARTICIPACIONES de los empleados en sociedades en las tablas sociedades_constituciones,relaciones y edictos, que el cuit del edicto sea el del empleado : */
--------------------------------------------------------------------------------------------------------------------
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
select DISTINCT screening_empleados.nombre_empleado AS empleado,screening_empleados.cuil AS cuil,
screening_empleados.cliente as cliente,
getdate() as created,
null AS titulo,'ACCIONISTA' AS cargo,
/*
p1.nombre_persona as soc_vinculada,p1.identificacion as soc_vinculada,
*/
iif(substring(accionistas.cuit_acc,1,1)='2',p2.nombre_persona,null) as soc_vinculada,
iif(substring(accionistas.cuit_acc,1,1)='2',p2.identificacion,null) as cuit_soc_vinculada,
/*
P2.nombre_persona AS nombre_integrante,accionistas.cuit_acc as cuit_integrante,
*/
iif(substring(accionistas.cuit_acc,1,1)='3',p2.nombre_persona,null) as nombre_integrante,
iif(substring(accionistas.cuit_acc,1,1)='3',accionistas.cuit_acc,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'ACCIONISTA',@Proceso AS nro_proceso
from screening_empleados
inner join accionistas on screening_empleados.cuil = accionistas.cuit_acc
inner join padrones p1 on accionistas.cuit_acc = p1.identificacion
left join padrones p2 on accionistas.cuit_sociedad = p2.identificacion
where accionistas.cuit_acc in (select distinct ltrim(rtrim(cuil)) from screening_empleados where cliente = @IDCliente and screening_empleados.nro_proceso = @Proceso and screening_empleados.tipo_reg = @Tipo)
and screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS COMO EMPRESAS */
select screening_empleados.nombre_empleado AS empleado,screening_empleados.cuil AS cuit_prov,screening_empleados.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
iif(substring(edictos.cuit_integrante,1,1)='3',padrones.nombre_persona,null) as soc_vinculada,
iif(substring(edictos.cuit_integrante,1,1)='3',edictos.cuit_integrante,null) as cuit_soc_vinculada,
iif(substring(edictos.cuit_integrante,1,1)='2',padrones.nombre_persona,null) as nombre_integrante,
iif(substring(edictos.cuit_integrante,1,1)='2',edictos.cuit_integrante,null) as 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,@Proceso AS nro_proceso
from screening_empleados
inner join edictos on screening_empleados.cuil = edictos.cuit_sociedad
left join padrones on edictos.cuit_integrante = padrones.identificacion
left join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS PERSONAS */
select screening_empleados.nombre_empleado AS empleado,screening_empleados.cuil AS cuit_prov,screening_empleados.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
iif(substring(edictos.cuit_sociedad,1,1)='3',P2.nombre_persona,null) as soc_vinculada,
iif(substring(edictos.cuit_sociedad,1,1)='3',edictos.cuit_sociedad,null) as cuit_soc_vinculada,
iif(substring(edictos.cuit_integrante,1,1)='2',null,'a') as nombre_integrante,
iif(substring(edictos.cuit_integrante,1,1)='2',null,'a') as 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,@Proceso AS nro_proceso
from screening_empleados
inner join edictos on screening_empleados.cuil = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
left join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES COMO PERSONAS*/
select screening_empleados.nombre_empleado AS empleado ,screening_empleados.cuil AS cuil,
screening_empleados.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.razon_social,null) as soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.documento_soc,null) as cuit_soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.cuit_int,null) 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,@Proceso AS nro_proceso
from screening_empleados
inner join sociedades_constituciones on (screening_empleados.cuil = sociedades_constituciones.cuit_integrante)
left join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
/*
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
*/
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES COMO PERSONAS*/
select screening_empleados.nombre_empleado AS empleado ,screening_empleados.cuil AS cuil,
screening_empleados.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.razon_social,null) as soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.documento_soc,null) as cuit_soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.cuit_int,null) 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,@Proceso AS nro_proceso
from screening_empleados
inner join sociedades_constituciones on (screening_empleados.cuil = sociedades_constituciones.cuit_int)
left join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
/*
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
*/
union all
select p2.nombre_persona as empleado,p2.identificacion AS cuil,
screening_empleados.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
iif(substring(p3.identificacion,1,1)='3',p3.nombre_persona,null) as soc_vinculada,
iif(substring(p3.identificacion,1,1)='3',p3.identificacion,null) as cuit_soc_vinculada,
iif(substring(p3.identificacion,1,1)='2',p3.nombre_persona,null) as nombre_integrante,
iif(substring(p3.identificacion,1,1)='2',p3.identificacion,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null as contenido_archivo,relaciones.fecha_relacion,'AFIP' as tipo_reg /*SOC_CONS_1*/,@Proceso AS nro_proceso
from screening_empleados
inner join relaciones on screening_empleados.cuil = relaciones.cuita
inner join padrones p3 on relaciones.cuita = p3.identificacion
inner join padrones p2 on relaciones.cuitb = p2.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 screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
union all
/*buscamos sobre la tabla relaciones como personas */
select p2.nombre_persona as empleado,p2.identificacion AS cuil,
screening_empleados.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
iif(substring(p3.identificacion,1,1)='3',p3.nombre_persona,null) as soc_vinculada,
iif(substring(p3.identificacion,1,1)='3',p3.identificacion,null) as cuit_soc_vinculada,
iif(substring(p3.identificacion,1,1)='2',p3.nombre_persona,null) as nombre_integrante,
iif(substring(p3.identificacion,1,1)='2',p3.identificacion,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null as contenido_archivo,relaciones.fecha_relacion,'AFIP' as tipo_reg /*SOC_CONS_1*/,@Proceso AS nro_proceso
from screening_empleados
inner join relaciones on screening_empleados.cuil = relaciones.cuitb
inner join padrones p3 on relaciones.cuita = p3.identificacion
inner join padrones p2 on relaciones.cuitb = p2.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 screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
order by empleado
end
if (@StepID=2) /* trabajo con los familiares de empleados*/
begin
insert into screening_empleados_out (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
/* relaciones */
select
ptit.nombre_persona as empleado,ptit.identificacion as cuil,
screening_empleados.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
iif(substring(p3.identificacion,1,1)='3',p3.nombre_persona,null) as soc_vinculada,
iif(substring(p3.identificacion,1,1)='3',p3.identificacion,null) as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,screening_empleados.cuil AS cuit_integrante,
p2.nombre_persona as nombre_familiar,screening_empleados.cuil AS cuit_familiar,
null as contenido_archivo,relaciones.fecha_relacion,'AFIP_F' as tipo_reg,@Proceso AS nro_proceso
from screening_empleados
inner join relaciones on screening_empleados.cuil = relaciones.cuitb
inner join padrones p3 on relaciones.cuita = p3.identificacion
inner join padrones p2 on screening_empleados.cuil = p2.identificacion
inner join padrones ptit on screening_empleados.cuit_titular = ptit.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 screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = 'FAM'
AND screening_empleados.cuil not in (select distinct cuil from screening_empleados where nro_proceso = @Proceso and tipo_reg = 'EMP')
/*
and relaciones.id_tipo_relacion in (1,2)
*/
union all
/* accionistas */
select DISTINCT
ptit.nombre_persona as empleado,ptit.identificacion as cuil,
screening_empleados.cliente as cliente,
getdate() as created,
null AS titulo,'ACCIONISTA' AS cargo,
iif(substring(accionistas.cuit_acc,1,1)='2',p2.nombre_persona,null) as soc_vinculada,
iif(substring(accionistas.cuit_acc,1,1)='2',p2.identificacion,null) as cuit_soc_vinculada,
p1.nombre_persona as nombre_integrante,screening_empleados.cuil AS cuit_integrante,
p1.nombre_persona as nombre_familiar,screening_empleados.cuil AS cuit_familiar,
null as contenido_archivo,null as fecha_relacion,
tipo_reg = 'ACCIONIS_F',@Proceso AS nro_proceso
from screening_empleados
inner join accionistas on screening_empleados.cuil = accionistas.cuit_acc
inner join padrones p1 on accionistas.cuit_acc = p1.identificacion
inner join padrones p2 on accionistas.cuit_sociedad = p2.identificacion
inner join padrones ptit on screening_empleados.cuit_titular = ptit.identificacion
where accionistas.cuit_acc in (select distinct ltrim(rtrim(cuil)) from screening_empleados where cliente = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = 'FAM')
and screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = 'FAM'
AND screening_empleados.cuil not in (select distinct cuil from screening_empleados where nro_proceso = @Proceso and tipo_reg = 'EMP')
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES COMO PERSONAS*/
select ptit.nombre_persona as empleado,ptit.identificacion as cuil,
/*
screening_empleados.nombre_empleado AS empleado ,screening_empleados.cuil AS cuil,
*/
screening_empleados.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
p2.nombre_persona as soc_vinculada,
p2.identificacion as cuit_soc_vinculada,
padrones.nombre_persona as nombre_integrante,screening_empleados.cuil AS cuit_integrante,
padrones.nombre_persona as nombre_familiar,screening_empleados.cuil AS cuit_familiar,
sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion,
'SOCCONS1_F' as tipo_reg,@Proceso AS nro_proceso
from screening_empleados
inner join sociedades_constituciones on (screening_empleados.cuil = sociedades_constituciones.cuit_int)
inner join padrones on screening_empleados.cuil = padrones.identificacion
left join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
inner join padrones ptit on screening_empleados.cuit_titular = ptit.identificacion
where screening_empleados.cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = 'FAM'
AND screening_empleados.cuil not in (select distinct cuil from screening_empleados where cliente = @IDCliente and nro_proceso = @Proceso and tipo_reg = 'EMP')
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS PARA FAMILIARES */
select ptit.nombre_persona as empleado,ptit.identificacion as cuil,
screening_empleados.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,screening_empleados.cuil AS cuit_integrante,
padrones.nombre_persona as nombre_familiar,screening_empleados.cuil AS cuit_familiar,
edictos.texto as contenido_archivo,edictos.fecha_boletin as fecha_publicacion
,'EDICTO_F' as tipo_reg,@Proceso AS nro_proceso
from screening_empleados
inner join edictos on screening_empleados.cuil = edictos.cuit_integrante
left join padrones on edictos.cuit_integrante = padrones.identificacion
left join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
inner join padrones ptit on screening_empleados.cuit_titular = ptit.identificacion
and cliente = @IDCliente
and screening_empleados.nro_proceso = @Proceso
and screening_empleados.tipo_reg = 'FAM'
AND screening_empleados.cuil not in (select distinct cuil from screening_empleados where cliente = @IDCliente and nro_proceso = @Proceso and tipo_reg = 'EMP')
end
END
GO
Depends On
12Used By
No items found