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
/*
EXEC screening_empleados_junio_2018 7777, 0, null, 5, 'EMP'
*/
CREATE OR ALTER PROCEDURE dbo.screening_empleados_junio_2018
@IDCliente int,
@StepID int,
@fechaProceso date,
@Proceso smallint,
@Tipo varchar(3)
AS
/*
exec screening_empleados_junio_2018 7777,0,NULL,5,'EMP'
*/
BEGIN
/*
select 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_pep_ws_brg SPWSBRG
*/
if(@StepID=55) /*INSERTO TODOS LOS PROVEEDORES 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,cuil as cuit,'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 cuil not in (select distinct cuit from screening_empleados_out where cliente = @IDCliente and nro_proceso = @Proceso)
END
if(@StepID=0)
BEGIN
/* REGISTROS DE VINVULADOS POR AFIP DE FAMILIARES DE EMPLEADOS */
SELECT 'VENDORID' AS vendorid,
'ACCIONISTAS FAMILIARES' AS PERSON_TYPE,
screening_empleados_out.cuit_familiar as TAXID,screening_empleados_out.nombre_familiar as LEGALNAME,
/*SOC_VINCULADA as LINKEDPERSONORENTITY,*/
padrones.nombre_persona as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit AS PARENT_TAX_ID,screening_empleados_out.razon_social AS PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
LEFT JOIN padrones on screening_empleados_out.CUIT_SOC_VINCULADA = padrones.identificacion
where (screening_empleados_out.tipo_reg = 'EDICTO_F')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
/*
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
*/
UNION ALL
/* REGISTROS DE VINVULADOS POR AFIP DE FAMILIARES DE EMPLEADOS */
SELECT 'VENDORID' AS vendorid,
'VINCULACIONES AFIP FAMILIARES' AS PERSON_TYPE,
screening_empleados_out.cuit_familiar as TAXID,screening_empleados_out.nombre_familiar as LEGALNAME,
/*SOC_VINCULADA as LINKEDPERSONORENTITY,*/
padrones.nombre_persona as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit AS PARENT_TAX_ID,screening_empleados_out.razon_social AS PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
LEFT JOIN padrones on screening_empleados_out.CUIT_SOC_VINCULADA = padrones.identificacion
where (screening_empleados_out.tipo_reg = 'AFIP_F')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
/*
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
*/
UNION ALL
/* REGISTROS DE ACCIONISTAS DE FAMILIARES DE EMPLEADOS */
SELECT 'VENDORID' AS vendorid,
'ACCIONISTAS FAMILIARES' AS PERSON_TYPE,
screening_empleados_out.cuit_familiar as TAXID,screening_empleados_out.nombre_familiar as LEGALNAME,
/*SOC_VINCULADA as LINKEDPERSONORENTITY,*/
padrones.nombre_persona as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit AS PARENT_TAX_ID,screening_empleados_out.razon_social AS PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
LEFT JOIN padrones on screening_empleados_out.CUIT_SOC_VINCULADA = padrones.identificacion
where (screening_empleados_out.tipo_reg = 'ACCIONIS_F')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
/*
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
*/
UNION ALL
/* REGISTROS DE PARTICIPACIONES SOCIETARIAS DE FAMILIARES DE EMPLEADOS */
SELECT 'VENDORID' AS vendorid,
'VINCULACIONES SOCIETARIAS FAMILIARES' AS PERSON_TYPE,
screening_empleados_out.cuit_familiar as TAXID,screening_empleados_out.nombre_familiar as LEGALNAME,
/*SOC_VINCULADA as LINKEDPERSONORENTITY,*/
padrones.nombre_persona as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit AS PARENT_TAX_ID,screening_empleados_out.razon_social AS PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
LEFT JOIN padrones on screening_empleados_out.CUIT_SOC_VINCULADA = padrones.identificacion
where (screening_empleados_out.tipo_reg = 'SOCCONS1_F')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
/*
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
*/
union all
/* REGISTROS DE EMPLEADOS SI DATOS DE APERTURA*/
SELECT 'VENDORID' AS vendorid,
'N/A' AS PERSON_TYPE,
screening_empleados_out.cuit AS TAXID,screening_empleados_out.razon_social AS LEGALNAME,
SOC_VINCULADA as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit_familiar as PARENT_TAX_ID,screening_empleados_out.nombre_familiar as PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
where (screening_empleados_out.tipo_reg = 'N/A')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
union all
/* REGISTROS DE EMPLEADOS VINCULADOS A EMPRESAS POR AFIP*/
SELECT 'VENDORID' AS vendorid,
'VINCULACIONES SOCIETARIAS' AS PERSON_TYPE,
screening_empleados_out.cuit AS TAXID,screening_empleados_out.razon_social AS LEGALNAME,
SOC_VINCULADA as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit_familiar as PARENT_TAX_ID,screening_empleados_out.nombre_familiar as PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
where (screening_empleados_out.tipo_reg = 'SOC_CONS_1')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
union all
/* REGISTROS DE EMPLEADOS VINCULADOS A EMPRESAS POR AFIP*/
SELECT 'VENDORID' AS vendorid,
'AFIP' AS PERSON_TYPE,
screening_empleados_out.cuit AS TAXID,screening_empleados_out.razon_social AS LEGALNAME,
SOC_VINCULADA as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit_familiar as PARENT_TAX_ID,screening_empleados_out.nombre_familiar as PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
where (screening_empleados_out.tipo_reg = 'AFIP')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
union all
/* REGISTROS DE EMPLEADOS ACCIONISTAS DE EMPRESAS */
SELECT 'VENDORID' AS vendorid,
'ACCIONISTA' AS PERSON_TYPE,
screening_empleados_out.cuit AS TAXID,screening_empleados_out.razon_social AS LEGALNAME,
SOC_VINCULADA as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit_familiar as PARENT_TAX_ID,screening_empleados_out.nombre_familiar as PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
where (screening_empleados_out.tipo_reg = 'ACCIONISTA' )
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
order by LEGALNAME,LINKEDPERSONORENTITY,PERSON_TYPE
END
end
GO
GO
/*
EXEC screening_empleados_junio_2018 7777, 0, null, 5, 'EMP'
*/
CREATE OR ALTER PROCEDURE dbo.screening_empleados_junio_2018
@IDCliente int,
@StepID int,
@fechaProceso date,
@Proceso smallint,
@Tipo varchar(3)
AS
/*
exec screening_empleados_junio_2018 7777,0,NULL,5,'EMP'
*/
BEGIN
/*
select 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_pep_ws_brg SPWSBRG
*/
if(@StepID=55) /*INSERTO TODOS LOS PROVEEDORES 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,cuil as cuit,'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 cuil not in (select distinct cuit from screening_empleados_out where cliente = @IDCliente and nro_proceso = @Proceso)
END
if(@StepID=0)
BEGIN
/* REGISTROS DE VINVULADOS POR AFIP DE FAMILIARES DE EMPLEADOS */
SELECT 'VENDORID' AS vendorid,
'ACCIONISTAS FAMILIARES' AS PERSON_TYPE,
screening_empleados_out.cuit_familiar as TAXID,screening_empleados_out.nombre_familiar as LEGALNAME,
/*SOC_VINCULADA as LINKEDPERSONORENTITY,*/
padrones.nombre_persona as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit AS PARENT_TAX_ID,screening_empleados_out.razon_social AS PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
LEFT JOIN padrones on screening_empleados_out.CUIT_SOC_VINCULADA = padrones.identificacion
where (screening_empleados_out.tipo_reg = 'EDICTO_F')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
/*
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
*/
UNION ALL
/* REGISTROS DE VINVULADOS POR AFIP DE FAMILIARES DE EMPLEADOS */
SELECT 'VENDORID' AS vendorid,
'VINCULACIONES AFIP FAMILIARES' AS PERSON_TYPE,
screening_empleados_out.cuit_familiar as TAXID,screening_empleados_out.nombre_familiar as LEGALNAME,
/*SOC_VINCULADA as LINKEDPERSONORENTITY,*/
padrones.nombre_persona as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit AS PARENT_TAX_ID,screening_empleados_out.razon_social AS PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
LEFT JOIN padrones on screening_empleados_out.CUIT_SOC_VINCULADA = padrones.identificacion
where (screening_empleados_out.tipo_reg = 'AFIP_F')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
/*
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
*/
UNION ALL
/* REGISTROS DE ACCIONISTAS DE FAMILIARES DE EMPLEADOS */
SELECT 'VENDORID' AS vendorid,
'ACCIONISTAS FAMILIARES' AS PERSON_TYPE,
screening_empleados_out.cuit_familiar as TAXID,screening_empleados_out.nombre_familiar as LEGALNAME,
/*SOC_VINCULADA as LINKEDPERSONORENTITY,*/
padrones.nombre_persona as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit AS PARENT_TAX_ID,screening_empleados_out.razon_social AS PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
LEFT JOIN padrones on screening_empleados_out.CUIT_SOC_VINCULADA = padrones.identificacion
where (screening_empleados_out.tipo_reg = 'ACCIONIS_F')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
/*
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
*/
UNION ALL
/* REGISTROS DE PARTICIPACIONES SOCIETARIAS DE FAMILIARES DE EMPLEADOS */
SELECT 'VENDORID' AS vendorid,
'VINCULACIONES SOCIETARIAS FAMILIARES' AS PERSON_TYPE,
screening_empleados_out.cuit_familiar as TAXID,screening_empleados_out.nombre_familiar as LEGALNAME,
/*SOC_VINCULADA as LINKEDPERSONORENTITY,*/
padrones.nombre_persona as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit AS PARENT_TAX_ID,screening_empleados_out.razon_social AS PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
LEFT JOIN padrones on screening_empleados_out.CUIT_SOC_VINCULADA = padrones.identificacion
where (screening_empleados_out.tipo_reg = 'SOCCONS1_F')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
/*
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
*/
union all
/* REGISTROS DE EMPLEADOS SI DATOS DE APERTURA*/
SELECT 'VENDORID' AS vendorid,
'N/A' AS PERSON_TYPE,
screening_empleados_out.cuit AS TAXID,screening_empleados_out.razon_social AS LEGALNAME,
SOC_VINCULADA as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit_familiar as PARENT_TAX_ID,screening_empleados_out.nombre_familiar as PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
where (screening_empleados_out.tipo_reg = 'N/A')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
union all
/* REGISTROS DE EMPLEADOS VINCULADOS A EMPRESAS POR AFIP*/
SELECT 'VENDORID' AS vendorid,
'VINCULACIONES SOCIETARIAS' AS PERSON_TYPE,
screening_empleados_out.cuit AS TAXID,screening_empleados_out.razon_social AS LEGALNAME,
SOC_VINCULADA as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit_familiar as PARENT_TAX_ID,screening_empleados_out.nombre_familiar as PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
where (screening_empleados_out.tipo_reg = 'SOC_CONS_1')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
union all
/* REGISTROS DE EMPLEADOS VINCULADOS A EMPRESAS POR AFIP*/
SELECT 'VENDORID' AS vendorid,
'AFIP' AS PERSON_TYPE,
screening_empleados_out.cuit AS TAXID,screening_empleados_out.razon_social AS LEGALNAME,
SOC_VINCULADA as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit_familiar as PARENT_TAX_ID,screening_empleados_out.nombre_familiar as PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
where (screening_empleados_out.tipo_reg = 'AFIP')
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
union all
/* REGISTROS DE EMPLEADOS ACCIONISTAS DE EMPRESAS */
SELECT 'VENDORID' AS vendorid,
'ACCIONISTA' AS PERSON_TYPE,
screening_empleados_out.cuit AS TAXID,screening_empleados_out.razon_social AS LEGALNAME,
SOC_VINCULADA as LINKEDPERSONORENTITY,
CUIT_SOC_VINCULADA as LINKTAXID,
screening_empleados_out.cuit_familiar as PARENT_TAX_ID,screening_empleados_out.nombre_familiar as PARENT_NAME,
iif(IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo) is null,'N/A',IIF(screening_empleados_out.cargo is null,titulo,screening_empleados_out.cargo)) as POSITIONRELATION,
-- busca en la tabla de pep
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
null 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_empleados_out
INNER join screening_empleados on (screening_empleados_out.cuit = screening_empleados.cuil AND screening_empleados.cliente= @IDCliente and screening_empleados.nro_proceso = @Proceso AND screening_empleados.tipo_reg = @Tipo)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON (screening_empleados_out.cuit_integrante = SPWSBRG.cuit OR screening_empleados_out.cuit = SPWSBRG.cuit OR screening_empleados_out.cuit_soc_vinculada = SPWSBRG.cuit)
where (screening_empleados_out.tipo_reg = 'ACCIONISTA' )
AND screening_empleados_out.cliente = @IDCliente
and screening_empleados_out.nro_proceso = @Proceso
AND screening_empleados_out.cuit IN (SELECT DISTINCT CUIL FROM screening_empleados WHERE CLIENTE = @IDCliente and screening_empleados.nro_proceso = @Proceso AND tipo_reg = @Tipo)
order by LEGALNAME,LINKEDPERSONORENTITY,PERSON_TYPE
END
end
GO
Depends On
4Used By
No items found