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_proveedores_ok
@IDCliente int,
@StepID int,
@fechaProceso date,
@Proceso smallint,
@Tipo varchar(3)
AS
/*
Paso 1: Busca integrantes en las tablas sociedades_constituciones,relaciones y edictos, que el cuit del edicto sea el proveedor
Graba Tipo_reg = AFIP y SOC_CONS_1
Paso 2: Busca accionistas
Graba Tipo_reg = ACCIONISTA
Paso 3: Busca socios en tabla socios
Graba Tipo_reg = DIRECTOR
Paso 4: Busca todos los empleados de los proveedores
Graba Tipo_reg = PROV_EMP
Paso 5: Busca todos los proveedores de los que no se encontró nada en los pasos anteriores (no siempre hay que correrlo)
Graba Tipo_reg = N/A
exec screening_proveedores_OK 647224514,1,null,1,'PRV'
exec screening_proveedores_OK 647224514,2,null,1,'PRV'
exec screening_proveedores_OK 647224514,3,null,1,'PRV'
exec screening_proveedores_OK 647224514,4,null,1,'PRV'
exec screening_proveedores_OK 647224514,5,null,1,'PRV'
*/
BEGIN
if (@StepID = 5) /*INSERTO TODOS LOS PROVEEDORES QUE NO PUDIERON APERTURARSE COMO N/A PARA GARANTIZAR QUE TODOS HAYAN SIDO ANALIZADOS*/
BEGIN
insert into screening_prov_out2 (cliente,razon_social_prov,cuit_prov,titulo,cargo,tipo_reg,nro_proceso)
select @IDCliente as cliente,
razon_social as razon_social_prov,
cuit as cuit_prov,
'N/A' as titulo,'N/A' as cargo,'N/A' as tipo_reg,@Proceso as nro_proceso
from screening_prov
where cliente = @IDCliente and nro_proceso = @Proceso
and cuit not in (select distinct cuit_prov from screening_prov_out2 where cliente = @IDCliente and nro_proceso = @Proceso)
END
if (@StepID = 0) /* SALIDA FINAL */
BEGIN
SELECT screening_prov.vendorid,
'VENDOR 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,
-- 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,
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.cliente= @IDCliente 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 = 'PROV_EMP'
/*
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 all
/* REGISTROS QUE FUERON PROVISTOS POR EL CLIENTE Y NO PUDIERON SER APERTURADOS */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','VENDOR') 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.cliente = @IDCliente AND 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
/*
declare @IDCliente int = 1659303046,
@StepID int = 0,
@fechaProceso date = null,
@Proceso smallint = 1,
@Tipo varchar(3) = 'PRV'
*/
/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION /*, + screening_prov_out2.tipo_reg 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.cliente = @IDCliente 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 in ('AFIP', 'SOC_CONS_1')
AND (cuit_integrante is not null or nombre_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 PROVISTAS POR EL CLIENTE SON INTEGRANTES DE SOCIEDADES */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','VENDOR') 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,'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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + screening_prov_out2.tipo_reg 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.cliente = @IDCliente 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 in ('AFIP', 'SOC_CONS_1')
AND (cuit_soc_vinculada is not null or soc_vinculada 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','VENDOR') 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,'ACCIONISTA',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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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) /* sociedades_constituciones,relaciones y edictos */
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 1: Busca integrantes en las tablas sociedades_constituciones,relaciones y edictos, que el cuit del edicto sea el proveedor : */
--------------------------------------------------------------------------------------------------------------------
/* BUSCAMOS SOBRE LA TABLA RELACIONES QUE SE ALIMENTA POR WEBSERVICE DE AFIP */
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
/*buscamos sobre la tabla relaciones como personas */
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
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_prov
inner join relaciones on screening_prov.cuit = 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_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
UNION ALL
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
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_prov
inner join relaciones on screening_prov.cuit = 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_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
union all
/*buscamos sobre la tabla relaciones como empresa */
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
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_prov
inner join relaciones on screening_prov.cuit = relaciones.cuita
inner join padrones p2 on relaciones.cuita = p2.identificacion
inner join padrones p3 on relaciones.cuitb = p3.identificacion
inner join tipos_relaciones tp1 on tp1.id_tipo_relacion = relaciones.id_tipo_relacion
inner join tipos_subrelaciones tsp1 on tsp1.id_subrelacion = relaciones.id_tipo_subrelacion
where cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES COMO PERSONAS*/
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
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_prov
inner join sociedades_constituciones on (screening_prov.cuit = sociedades_constituciones.cuit_int)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
/*
and screening_prov.tipo_reg = @Tipo
*/
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
UNION ALL
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
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_prov
inner join sociedades_constituciones on (screening_prov.cuit = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES */
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.cuit_int,null) as cuit_soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' 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_prov
inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS COMO EMPRESAS*/
select screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,screening_prov.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
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_prov
inner join edictos on screening_prov.cuit = edictos.cuit_sociedad
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS PERSONAS */
select screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,screening_prov.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
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_prov
inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
/*
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
/*
null as soc_vinculada,null as cuit_soc_vinculada,
p2.nombre_persona as soc_vinculada,
p2.identificacion as cuit_soc_vinculada,
*/
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,
/*
p3.nombre_persona as nombre_integrante,
p3.identificacion as cuit_integrante,
*/
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*/
from screening_prov
inner join relaciones on screening_prov.cuit = relaciones.cuita
inner join padrones p2 on relaciones.cuita = p2.identificacion
inner join padrones p3 on relaciones.cuitb = p3.identificacion
inner join tipos_relaciones tp1 on tp1.id_tipo_relacion = relaciones.id_tipo_relacion
inner join tipos_subrelaciones tsp1 on tsp1.id_subrelacion = relaciones.id_tipo_subrelacion
where cliente = @IDCliente
and screening_prov.created >= @fechaProceso
and relaciones.id_tipo_relacion in (1,2)
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES */
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
/*
null as soc_vinculada,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 soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.cuit_int,null) as cuit_soc_vinculada,
/*
sociedades_constituciones.nombre_integrante,
sociedades_constituciones.cuit_int AS cuit_integrante,
*/
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' 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
from screening_prov
inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and screening_prov.created >= @fechaProceso
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS */
select screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,screening_prov.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
/*
null as soc_vinculada,null as cuit_soc_vinculada,
*/
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,
/*
padrones.nombre_persona as nombre_integrante,
edictos.cuit_integrante,
*/
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
from screening_prov
inner join edictos on screening_prov.cuit = edictos.cuit_sociedad
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
and screening_prov.created >= @fechaProceso
*/
end
if (@StepID = 2) /*ACCIONISTAS*/
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 2: buscar accionistas a partir de los cuit provistos */
--------------------------------------------------------------------------------------------------------------------
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
select DISTINCT screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,
screening_prov.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)='3',p2.nombre_persona,null) as soc_vinculada,
iif(substring(accionistas.cuit_acc,1,1)='3',accionistas.cuit_acc,null) as cuit_soc_vinculada,
/*
P2.nombre_persona AS nombre_integrante,accionistas.cuit_acc as cuit_integrante,
*/
iif(substring(accionistas.cuit_acc,1,1)='2',p2.nombre_persona,null) as nombre_integrante,
iif(substring(accionistas.cuit_acc,1,1)='2',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_prov
inner join accionistas on screening_prov.cuit = accionistas.cuit_sociedad
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_sociedad in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.nro_proceso = @Proceso)
and screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
UNION ALL
select DISTINCT screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,
screening_prov.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)='3',p2.nombre_persona,null) as soc_vinculada,
iif(substring(accionistas.cuit_acc,1,1)='3',accionistas.cuit_acc,null) as cuit_soc_vinculada,
/*
P2.nombre_persona AS nombre_integrante,accionistas.cuit_acc as cuit_integrante,
*/
iif(substring(accionistas.cuit_acc,1,1)='2',p2.nombre_persona,null) as nombre_integrante,
iif(substring(accionistas.cuit_acc,1,1)='2',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_prov
inner join accionistas on ltrim(rtrim(screening_prov.cuit)) = accionistas.cuit_acc
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_sociedad in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.nro_proceso = @Proceso)
and screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
end
if (@StepID = 3) /*SOCIOS*/
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 3: buscar socios en tabla socios */
--------------------------------------------------------------------------------------------------------------------
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
/* SOCIOS COMO EMPRESAS*/
select DISTINCT screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,
@IDCliente as cliente,
getdate() as created,
null AS titulo,
cargos.car_nombre AS cargo,
iif(substring(pSocio.identificacion,1,1)='3',pSocio.nombre_persona,null) as soc_vinculada,
iif(substring(pSocio.identificacion,1,1)='3',pSocio.identificacion,null) as cuit_soc_vinculada,
iif(substring(pSocio.identificacion,1,1)='2',pSocio.nombre_persona,null) as nombre_integrante,
iif(substring(pSocio.identificacion,1,1)='2',pSocio.identificacion,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR',@Proceso AS nro_proceso
from screening_prov
inner join socios on (ltrim(rtrim(screening_prov.cuit)) = socios.c01_cuit and socios.visible = 1)
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones pSocio on socios.c01_cuit_soc = pSocio.identificacion
where socios.c01_cuit in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.nro_proceso = @Proceso )
and screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
UNION ALL
/* SOCIOS INGRESANDO COMO PERSONA */
select DISTINCT screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,
@IDCliente as cliente,
getdate() as created,
null AS titulo,
cargos.car_nombre AS cargo,
iif(substring(Socios.c01_cuit,1,1)='3',pSocio.nombre_persona,null) as soc_vinculada,
iif(substring(Socios.c01_cuit,1,1)='3',pSocio.identificacion,null) as cuit_soc_vinculada,
iif(substring(pSocio.identificacion,1,1)='2',pSocio.nombre_persona,null) as nombre_integrante,
iif(substring(pSocio.identificacion,1,1)='2',pSocio.identificacion,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR',@Proceso AS nro_proceso
from screening_prov
inner join socios on (ltrim(rtrim(screening_prov.cuit)) = socios.c01_cuit_soc and isnull(socios.visible,0) = 1)
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones pSocio on socios.c01_cuit = pSocio.identificacion
where socios.c01_cuit_soc in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.nro_proceso = @Proceso )
and screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and iif(ltrim(rtrim(socios.c01_cuit)) = '',null,ltrim(rtrim(socios.c01_cuit))) is not null
end
if (@StepID = 4) /* EMPLEADOS */
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 6: Busco todos los empleados de los proveedores */
--------------------------------------------------------------------------------------------------------------------
declare @ultimo_mes_empleados date
declare @periodo_empleados int
declare @mes_empleados date,
@periodo_empleados_desde int,
@periodo_empleados_hasta int,
@cantidad int
set @ultimo_mes_empleados = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
set @periodo_empleados = convert(int,substring(convert(char,@ultimo_mes_empleados),3,2)+substring(convert(char,@ultimo_mes_empleados),6,2))
/*
select @ultimo_mes_empleados
select @periodo_empleados
select @periodo_empleados_desde
select @periodo_empleados_hasta
*/
/*
set @ultimo_mes_empleados = '2017-12-01'
set @mes_empleados = '2017-08-01'
*/
set @periodo_empleados_desde = @periodo_empleados - 7
set @periodo_empleados_hasta = @periodo_empleados
/*
select @ultimo_mes_empleados as ULT,@periodo_empleados as periodo,@periodo_empleados_desde as desde,@periodo_empleados_hasta as hasta
*/
/*
insert into screening_prov_out2 (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg,nro_proceso)
*/
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
select distinct screening_prov.razon_social as razon_social,screening_prov.cuit as cuit_prov,
@IDCliente as cliente,
getdate() as created,NULL as titulo,
'EMPLEADO' as cargo, NULL as soc_vinculada,NULL as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,padrones.identificacion as cuit_integrante,
NULL AS nombre_familiar,NULL as cuit_familiar,NULL as contenido_archivo ,NULL as fecha_publicacion,
'PROV_EMP' as tipo_reg,@Proceso as nro_proceso
from screening_prov
inner join empleados on (empleados.cuit = screening_prov.cuit and periodo between @periodo_empleados_desde and @periodo_empleados_hasta
and empleados.cuit not in (select cuit from clientes where cliente = @IDCliente))
inner join padrones on padrones.identificacion = empleados.cuil
where empleados.periodo = @periodo_empleados
AND screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
/*
union all
select distinct screening_prov.razon_social as razon_social,screening_prov.cuit as cuit,
@IDCliente as cliente,
getdate() as created,NULL as titulo,
NULL as cargo, NULL as soc_vinculada,NULL as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,padrones.identificacion as cuit_int,padrones.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'PROV_EMP' as tipo_reg,@Proceso as nro_proceso
from screening_prov
inner join empleados_temp empleados on (empleados.cuit = screening_prov.cuit and periodo between @periodo_empleados_desde and @periodo_empleados_hasta
and empleados.cuit not in (select cuit from clientes where cliente = @IDCliente))
inner join padrones on padrones.identificacion = empleados.cuil
where empleados.periodo = @periodo_empleados
AND screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
*/
end
END
GO
GO
CREATE OR ALTER PROCEDURE dbo.screening_proveedores_ok
@IDCliente int,
@StepID int,
@fechaProceso date,
@Proceso smallint,
@Tipo varchar(3)
AS
/*
Paso 1: Busca integrantes en las tablas sociedades_constituciones,relaciones y edictos, que el cuit del edicto sea el proveedor
Graba Tipo_reg = AFIP y SOC_CONS_1
Paso 2: Busca accionistas
Graba Tipo_reg = ACCIONISTA
Paso 3: Busca socios en tabla socios
Graba Tipo_reg = DIRECTOR
Paso 4: Busca todos los empleados de los proveedores
Graba Tipo_reg = PROV_EMP
Paso 5: Busca todos los proveedores de los que no se encontró nada en los pasos anteriores (no siempre hay que correrlo)
Graba Tipo_reg = N/A
exec screening_proveedores_OK 647224514,1,null,1,'PRV'
exec screening_proveedores_OK 647224514,2,null,1,'PRV'
exec screening_proveedores_OK 647224514,3,null,1,'PRV'
exec screening_proveedores_OK 647224514,4,null,1,'PRV'
exec screening_proveedores_OK 647224514,5,null,1,'PRV'
*/
BEGIN
if (@StepID = 5) /*INSERTO TODOS LOS PROVEEDORES QUE NO PUDIERON APERTURARSE COMO N/A PARA GARANTIZAR QUE TODOS HAYAN SIDO ANALIZADOS*/
BEGIN
insert into screening_prov_out2 (cliente,razon_social_prov,cuit_prov,titulo,cargo,tipo_reg,nro_proceso)
select @IDCliente as cliente,
razon_social as razon_social_prov,
cuit as cuit_prov,
'N/A' as titulo,'N/A' as cargo,'N/A' as tipo_reg,@Proceso as nro_proceso
from screening_prov
where cliente = @IDCliente and nro_proceso = @Proceso
and cuit not in (select distinct cuit_prov from screening_prov_out2 where cliente = @IDCliente and nro_proceso = @Proceso)
END
if (@StepID = 0) /* SALIDA FINAL */
BEGIN
SELECT screening_prov.vendorid,
'VENDOR 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,
-- 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,
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.cliente= @IDCliente 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 = 'PROV_EMP'
/*
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 all
/* REGISTROS QUE FUERON PROVISTOS POR EL CLIENTE Y NO PUDIERON SER APERTURADOS */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','VENDOR') 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.cliente = @IDCliente AND 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
/*
declare @IDCliente int = 1659303046,
@StepID int = 0,
@fechaProceso date = null,
@Proceso smallint = 1,
@Tipo varchar(3) = 'PRV'
*/
/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION /*, + screening_prov_out2.tipo_reg 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.cliente = @IDCliente 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 in ('AFIP', 'SOC_CONS_1')
AND (cuit_integrante is not null or nombre_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 PROVISTAS POR EL CLIENTE SON INTEGRANTES DE SOCIEDADES */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','VENDOR') 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,'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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',lugar)) AS POSITIONROLE,
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,
fecha_defuncion as FECHADEFUNCION/*, + screening_prov_out2.tipo_reg 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.cliente = @IDCliente 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 in ('AFIP', 'SOC_CONS_1')
AND (cuit_soc_vinculada is not null or soc_vinculada 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','VENDOR') 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,'ACCIONISTA',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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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','VENDOR') 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',isnull(rtrim(ltrim(SPWSBRG.cargo)),'') + ' ' + iif(rtrim(ltrim(SPWSBRG.lugar))='NULL','',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.cliente = @IDCliente 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) /* sociedades_constituciones,relaciones y edictos */
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 1: Busca integrantes en las tablas sociedades_constituciones,relaciones y edictos, que el cuit del edicto sea el proveedor : */
--------------------------------------------------------------------------------------------------------------------
/* BUSCAMOS SOBRE LA TABLA RELACIONES QUE SE ALIMENTA POR WEBSERVICE DE AFIP */
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
/*buscamos sobre la tabla relaciones como personas */
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
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_prov
inner join relaciones on screening_prov.cuit = 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_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
UNION ALL
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
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_prov
inner join relaciones on screening_prov.cuit = 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_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
union all
/*buscamos sobre la tabla relaciones como empresa */
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
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_prov
inner join relaciones on screening_prov.cuit = relaciones.cuita
inner join padrones p2 on relaciones.cuita = p2.identificacion
inner join padrones p3 on relaciones.cuitb = p3.identificacion
inner join tipos_relaciones tp1 on tp1.id_tipo_relacion = relaciones.id_tipo_relacion
inner join tipos_subrelaciones tsp1 on tsp1.id_subrelacion = relaciones.id_tipo_subrelacion
where cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and relaciones.id_tipo_relacion in (1,2)
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES COMO PERSONAS*/
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
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_prov
inner join sociedades_constituciones on (screening_prov.cuit = sociedades_constituciones.cuit_int)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
/*
and screening_prov.tipo_reg = @Tipo
*/
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
UNION ALL
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
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_prov
inner join sociedades_constituciones on (screening_prov.cuit = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES */
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.cuit_int,null) as cuit_soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' 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_prov
inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS COMO EMPRESAS*/
select screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,screening_prov.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
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_prov
inner join edictos on screening_prov.cuit = edictos.cuit_sociedad
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS PERSONAS */
select screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,screening_prov.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
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_prov
inner join edictos on screening_prov.cuit = edictos.cuit_integrante
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and screening_prov.tipo_reg = @Tipo
/*
select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
screening_prov.cliente,
getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,
/*
null as soc_vinculada,null as cuit_soc_vinculada,
p2.nombre_persona as soc_vinculada,
p2.identificacion as cuit_soc_vinculada,
*/
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,
/*
p3.nombre_persona as nombre_integrante,
p3.identificacion as cuit_integrante,
*/
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*/
from screening_prov
inner join relaciones on screening_prov.cuit = relaciones.cuita
inner join padrones p2 on relaciones.cuita = p2.identificacion
inner join padrones p3 on relaciones.cuitb = p3.identificacion
inner join tipos_relaciones tp1 on tp1.id_tipo_relacion = relaciones.id_tipo_relacion
inner join tipos_subrelaciones tsp1 on tsp1.id_subrelacion = relaciones.id_tipo_subrelacion
where cliente = @IDCliente
and screening_prov.created >= @fechaProceso
and relaciones.id_tipo_relacion in (1,2)
UNION ALL
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES */
select screening_prov.razon_social AS razon_social_prov ,screening_prov.cuit AS cuit_prov,
screening_prov.cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
/*
null as soc_vinculada,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 soc_vinculada,
iif(substring(sociedades_constituciones.cuit_int,1,1)='3' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.cuit_int,null) as cuit_soc_vinculada,
/*
sociedades_constituciones.nombre_integrante,
sociedades_constituciones.cuit_int AS cuit_integrante,
*/
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' and (len(sociedades_constituciones.cuit_int) > 8),sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
iif(substring(sociedades_constituciones.cuit_int,1,1)='2' 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
from screening_prov
inner join sociedades_constituciones on screening_prov.cuit = sociedades_constituciones.documento_soc
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion
where screening_prov.cliente = @IDCliente
and screening_prov.created >= @fechaProceso
and sociedades_constituciones.nombre_integrante <> '' and sociedades_constituciones.nombre_integrante is not null
union all
/* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS */
select screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,screening_prov.cliente,getdate() as created,
NULL as titulo, NULL as cargo,
/*
null as soc_vinculada,null as cuit_soc_vinculada,
*/
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,
/*
padrones.nombre_persona as nombre_integrante,
edictos.cuit_integrante,
*/
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
from screening_prov
inner join edictos on screening_prov.cuit = edictos.cuit_sociedad
inner join padrones on edictos.cuit_integrante = padrones.identificacion
inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
and cliente = @IDCliente
and screening_prov.created >= @fechaProceso
*/
end
if (@StepID = 2) /*ACCIONISTAS*/
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 2: buscar accionistas a partir de los cuit provistos */
--------------------------------------------------------------------------------------------------------------------
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
select DISTINCT screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,
screening_prov.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)='3',p2.nombre_persona,null) as soc_vinculada,
iif(substring(accionistas.cuit_acc,1,1)='3',accionistas.cuit_acc,null) as cuit_soc_vinculada,
/*
P2.nombre_persona AS nombre_integrante,accionistas.cuit_acc as cuit_integrante,
*/
iif(substring(accionistas.cuit_acc,1,1)='2',p2.nombre_persona,null) as nombre_integrante,
iif(substring(accionistas.cuit_acc,1,1)='2',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_prov
inner join accionistas on screening_prov.cuit = accionistas.cuit_sociedad
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_sociedad in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.nro_proceso = @Proceso)
and screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
UNION ALL
select DISTINCT screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,
screening_prov.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)='3',p2.nombre_persona,null) as soc_vinculada,
iif(substring(accionistas.cuit_acc,1,1)='3',accionistas.cuit_acc,null) as cuit_soc_vinculada,
/*
P2.nombre_persona AS nombre_integrante,accionistas.cuit_acc as cuit_integrante,
*/
iif(substring(accionistas.cuit_acc,1,1)='2',p2.nombre_persona,null) as nombre_integrante,
iif(substring(accionistas.cuit_acc,1,1)='2',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_prov
inner join accionistas on ltrim(rtrim(screening_prov.cuit)) = accionistas.cuit_acc
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion
where accionistas.cuit_sociedad in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.nro_proceso = @Proceso)
and screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
end
if (@StepID = 3) /*SOCIOS*/
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 3: buscar socios en tabla socios */
--------------------------------------------------------------------------------------------------------------------
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
/* SOCIOS COMO EMPRESAS*/
select DISTINCT screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,
@IDCliente as cliente,
getdate() as created,
null AS titulo,
cargos.car_nombre AS cargo,
iif(substring(pSocio.identificacion,1,1)='3',pSocio.nombre_persona,null) as soc_vinculada,
iif(substring(pSocio.identificacion,1,1)='3',pSocio.identificacion,null) as cuit_soc_vinculada,
iif(substring(pSocio.identificacion,1,1)='2',pSocio.nombre_persona,null) as nombre_integrante,
iif(substring(pSocio.identificacion,1,1)='2',pSocio.identificacion,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR',@Proceso AS nro_proceso
from screening_prov
inner join socios on (ltrim(rtrim(screening_prov.cuit)) = socios.c01_cuit and socios.visible = 1)
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones pSocio on socios.c01_cuit_soc = pSocio.identificacion
where socios.c01_cuit in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.nro_proceso = @Proceso )
and screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
UNION ALL
/* SOCIOS INGRESANDO COMO PERSONA */
select DISTINCT screening_prov.razon_social AS razon_social_prov,screening_prov.cuit AS cuit_prov,
@IDCliente as cliente,
getdate() as created,
null AS titulo,
cargos.car_nombre AS cargo,
iif(substring(Socios.c01_cuit,1,1)='3',pSocio.nombre_persona,null) as soc_vinculada,
iif(substring(Socios.c01_cuit,1,1)='3',pSocio.identificacion,null) as cuit_soc_vinculada,
iif(substring(pSocio.identificacion,1,1)='2',pSocio.nombre_persona,null) as nombre_integrante,
iif(substring(pSocio.identificacion,1,1)='2',pSocio.identificacion,null) as cuit_integrante,
null as nombre_familiar,null as cuit_familiar,
null AS contenido_archivo,getdate() AS fecha_publicacion,
tipo_reg = 'DIRECTOR',@Proceso AS nro_proceso
from screening_prov
inner join socios on (ltrim(rtrim(screening_prov.cuit)) = socios.c01_cuit_soc and isnull(socios.visible,0) = 1)
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones pSocio on socios.c01_cuit = pSocio.identificacion
where socios.c01_cuit_soc in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.nro_proceso = @Proceso )
and screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
and iif(ltrim(rtrim(socios.c01_cuit)) = '',null,ltrim(rtrim(socios.c01_cuit))) is not null
end
if (@StepID = 4) /* EMPLEADOS */
begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 6: Busco todos los empleados de los proveedores */
--------------------------------------------------------------------------------------------------------------------
declare @ultimo_mes_empleados date
declare @periodo_empleados int
declare @mes_empleados date,
@periodo_empleados_desde int,
@periodo_empleados_hasta int,
@cantidad int
set @ultimo_mes_empleados = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
set @periodo_empleados = convert(int,substring(convert(char,@ultimo_mes_empleados),3,2)+substring(convert(char,@ultimo_mes_empleados),6,2))
/*
select @ultimo_mes_empleados
select @periodo_empleados
select @periodo_empleados_desde
select @periodo_empleados_hasta
*/
/*
set @ultimo_mes_empleados = '2017-12-01'
set @mes_empleados = '2017-08-01'
*/
set @periodo_empleados_desde = @periodo_empleados - 7
set @periodo_empleados_hasta = @periodo_empleados
/*
select @ultimo_mes_empleados as ULT,@periodo_empleados as periodo,@periodo_empleados_desde as desde,@periodo_empleados_hasta as hasta
*/
/*
insert into screening_prov_out2 (razon_social,cuit,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,nombre_integrante,documento_integrante,nrdoc_int,cuit_int,cuit_integrante,contenido_archivo,fecha_publicacion,visible,tipo_reg,nro_proceso)
*/
insert into screening_prov_out2 (razon_social_prov,cuit_prov,cliente,created,titulo,cargo,soc_vinculada,cuit_soc_vinculada,
nombre_integrante,cuit_integrante,nombre_familiar,cuit_familiar,contenido_archivo,fecha_publicacion,tipo_reg,nro_proceso)
select distinct screening_prov.razon_social as razon_social,screening_prov.cuit as cuit_prov,
@IDCliente as cliente,
getdate() as created,NULL as titulo,
'EMPLEADO' as cargo, NULL as soc_vinculada,NULL as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,padrones.identificacion as cuit_integrante,
NULL AS nombre_familiar,NULL as cuit_familiar,NULL as contenido_archivo ,NULL as fecha_publicacion,
'PROV_EMP' as tipo_reg,@Proceso as nro_proceso
from screening_prov
inner join empleados on (empleados.cuit = screening_prov.cuit and periodo between @periodo_empleados_desde and @periodo_empleados_hasta
and empleados.cuit not in (select cuit from clientes where cliente = @IDCliente))
inner join padrones on padrones.identificacion = empleados.cuil
where empleados.periodo = @periodo_empleados
AND screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
/*
union all
select distinct screening_prov.razon_social as razon_social,screening_prov.cuit as cuit,
@IDCliente as cliente,
getdate() as created,NULL as titulo,
NULL as cargo, NULL as soc_vinculada,NULL as cuit_soc_vinculada,padrones.nombre_persona as nombre_integrante,
NULL AS documento_integrante,NULL as nrdoc_int,padrones.identificacion as cuit_int,padrones.identificacion as cuit_integrante,NULL as contenido_archivo ,NULL as fecha_publicacion,
1 as visible,'PROV_EMP' as tipo_reg,@Proceso as nro_proceso
from screening_prov
inner join empleados_temp empleados on (empleados.cuit = screening_prov.cuit and periodo between @periodo_empleados_desde and @periodo_empleados_hasta
and empleados.cuit not in (select cuit from clientes where cliente = @IDCliente))
inner join padrones on padrones.identificacion = empleados.cuil
where empleados.periodo = @periodo_empleados
AND screening_prov.cliente = @IDCliente
and screening_prov.nro_proceso = @Proceso
*/
end
END
GO
Depends On
15Used By
No items found