dbo.screening_proveedores_2018

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE OR ALTER PROCEDURE dbo.screening_proveedores_2018
@IDCliente int,
@StepID int,
@fechaProceso date,
@Proceso smallint,
@Tipo varchar(3)

AS

/*

exec screening_proveedores_2018 7777,0,NULL,5,'FAM'

exec screening_proveedores_2018 7777,1,null,5,'PRV'
exec screening_proveedores_2018 7777,2,null,5,'PRV'
exec screening_proveedores_2018 7777,55,null,5,'PRV'


*/

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_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 =  7777 and nro_proceso = 5
 and cuit not in (select distinct cuit_prov from screening_prov_out2 where cliente =  @IDCliente and nro_proceso = @Proceso)
END





if(@StepID=0)
BEGIN
/*
declare @IDCliente int = 7777
declare @StepID int = 0
declare @fechaProceso date = '2018-06-04'
declare @Proceso smallint = 5
declare @Tipo varchar(3) = 'PRV'
*/
/* REGISTROS DE EMPLEADOS DE LOS PROVEEDORES */
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
/*
declare @IDCliente int = 7777
declare @StepID int = 0
declare @fechaProceso date = '2018-06-04'
declare @Proceso smallint = 5
declare @Tipo varchar(3) = 'PRV'
*/
/* 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 = 7777
declare @StepID int = 0
declare @fechaProceso date = '2018-06-04'
declare @Proceso smallint = 5
declare @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',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,

fecha_defuncion as FECHADEFUNCION /*, + 'DATO AFIP' AS FUENTE*/

,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 
left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit  AND screening_prov.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 = 'AFIP' 
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 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,'DUPLICADO3',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION,

iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,

fecha_defuncion as FECHADEFUNCION/*, + 'SOC_CONS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.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 = 'SOC_CONS_1' 
/*AND cuit_integrante is not null*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.nro_proceso = @Proceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.nro_proceso = @Proceso AND SCREENING_PROV.tipo_reg = @Tipo)

union

/* EMPRESAS ACCIONISTAS DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,
IIF(screening_prov_out2.esFamiliar=1,'RELATED','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,'DUPLICADO4',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,

fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.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',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,


fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.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',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,


fecha_defuncion as FECHADEFUNCION/*, + 'ACCIONISTAS' AS FUENTE*/

,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.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',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,

fecha_defuncion as FECHADEFUNCION/*, + 'SOCIOS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.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',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,

fecha_defuncion as FECHADEFUNCION/*, + 'SOCIOS' AS FUENTE*/

,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit AND screening_prov.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 = -2)
begin

/*
declare @IDCliente int = 8888
declare @StepID tinyint = 1
declare @fechaProceso date ='2018-04-05'

*/

/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS [TAX ID],razon_social_prov AS [LEGAL NAME],

iif(substring(CUIT_PROV,1,1)='3',nombre_integrante,soc_vinculada) as [LINKED PERSON OR ENTITY],
iif(substring(CUIT_PROV,1,1)='3',cuit_integrante,cuit_soc_vinculada) as [LINK's TAX ID],

iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'PART.EN EMPRESAS',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as [POSITION / RELATION],

iif(SPWSBRG.cuit is null,'NO','YES') AS [PEP/SANCTIONS LIST],
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS [POSITION / ROLE], 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS [STATUS],

fecha_defuncion as [FECHA DEFUNCION]/*, + 'SOC_CONS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit and screening_prov.created >= @fechaProceso)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON  (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'SOC_CONS_1' 
/*
AND cuit_integrante is not null
*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.created >= @fechaProceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.created >= @fechaProceso)




/*
SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='27065538275'
SELECT * FROM PADRONES WHERE CONTAINS(*,'MEDIAMAN')

SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='20200150431'
SELECT * FROM PADRONES WHERE CONTAINS(*,'"RUBICAM"')

SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='20214297044'
SELECT * FROM PADRONES WHERE CONTAINS(*,'"ABATE"') AND TIPO_PERSONA='J'


SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='20290408262'
SELECT * FROM PADRONES WHERE CONTAINS(*,'"ILHABELLA"') AND TIPO_PERSONA='J'

SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='23233269794'
SELECT * FROM PADRONES WHERE CONTAINS(*,'"ILHABELLA"') AND TIPO_PERSONA='J'


SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='20083259788'
SELECT * FROM PADRONES WHERE CONTAINS(*,'"CUARZO"') AND TIPO_PERSONA='J'

UPDATE SCREENING_PROV_OUT2 SET cuit_soc_vinculada='30663197971',soc_vinculada='SIAFA SRL' WHERE id_screening_prov_out=119774



SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='20127830542'
SELECT * FROM PADRONES WHERE CONTAINS(*,'"ALDO TOLIN AGROMAQUINARIAS"') AND TIPO_PERSONA='J'


SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='20079792285'
SELECT * FROM PADRONES WHERE CONTAINS(*,'"FERNANDO TRIQUELL Y CIA."') AND TIPO_PERSONA='J'




SELECT * FROM SCREENING_PROV_OUT2 WHERE CLIENTE = 8888 AND cuit_prov='20160620049'
SELECT * FROM PADRONES WHERE CONTAINS(*,'"SIAFA"') AND TIPO_PERSONA='J'


*/




/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,

iif(substring(CUIT_PROV,1,1)='3',nombre_integrante,soc_vinculada) as LINKEDPERSONORENTITY,
iif(substring(CUIT_PROV,1,1)='3',cuit_integrante,cuit_soc_vinculada) as LINKsTAXID,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'PART.EN EMPRESAS',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION,

iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,

fecha_defuncion as FECHADEFUNCION/*, + 'SOC_CONS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit and screening_prov.created >= @fechaProceso)
LEFT JOIN screening_pep_ws_brg SPWSBRG ON  (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'SOC_CONS_1' 
/*
AND cuit_integrante is not null
*/
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.created >= @fechaProceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.created >= @fechaProceso)
ORDER BY razon_social_prov ASC,LINKEDPERSONORENTITY asc



END



/* version corregida el 5/42018 */

if (@StepID = -1)
begin


/* EMPRESAS DIRECTORES DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS TAXID,razon_social_prov AS LEGALNAME,
soc_vinculada as LINKEDPERSONORENTITY,cuit_soc_vinculada as LINKsTAXID,
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'PART. EN EMPRESAS',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as POSITIONRELATION
,
iif(SPWSBRG.cuit is null,'NO','YES') AS PEPSANCTIONSLIST,
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS POSITIONROLE, 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS ESTATUS,

fecha_defuncion as FECHADEFUNCION/*, + 'SOCIOS' AS FUENTE*/

,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on (screening_prov_out2.cuit_prov = screening_prov.cuit and screening_prov.created >= @fechaProceso)
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.created >= @fechaProceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.created >= @fechaProceso)

ORDER BY razon_social_prov ASC,LINKEDPERSONORENTITY asc


end








IF (@StepID = 100) /*steo 0 anterior */
BEGIN


/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid, CUIT_PROV AS [TAX ID],razon_social_prov AS [LEGAL NAME],
nombre_integrante as [LINKED PERSON OR ENTITY],cuit_integrante as [LINK's TAX ID],
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as [POSITION / RELATION],

iif(SPWSBRG.cuit is null,'NO','YES') AS [PEP/SANCTIONS LIST],
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS [POSITION / ROLE], 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS [STATUS],

fecha_defuncion as [FECHA DEFUNCION] /*, + 'DATO AFIP' AS FUENTE*/

,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on screening_prov_out2.cuit_prov = screening_prov.cuit
LEFT JOIN screening_pep_ws_brg SPWSBRG ON  (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'AFIP' 
AND cuit_integrante is not null
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.created >= @fechaProceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.created >= @fechaProceso)


UNION 
/* PERSONAS INTEGRANTES DE SOCIEDADES PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS [TAX ID],razon_social_prov AS [LEGAL NAME],
nombre_integrante as [LINKED PERSON OR ENTITY],cuit_integrante as [LINK's TAX ID],
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as [POSITION / RELATION],

iif(SPWSBRG.cuit is null,'NO','YES') AS [PEP/SANCTIONS LIST],
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS [POSITION / ROLE], 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS [STATUS],

fecha_defuncion as [FECHA DEFUNCION]/*, + 'SOC_CONS' AS FUENTE*/
,SPWSBRG.cuit,SPWSBRG.razon_social,rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar)) as cargo,SPWSBRG.estado,SPWSBRG.url2 as fecha_desde,SPWSBRG.column9 as fecha_hasta
FROM screening_prov_out2 left join screening_prov on screening_prov_out2.cuit_prov = screening_prov.cuit
LEFT JOIN screening_pep_ws_brg SPWSBRG ON  (screening_prov_out2.cuit_integrante = SPWSBRG.cuit OR screening_prov_out2.cuit_prov = SPWSBRG.cuit OR screening_prov_out2.cuit_soc_vinculada = SPWSBRG.cuit)
where screening_prov_out2.tipo_reg = 'SOC_CONS_1' 
AND cuit_integrante is not null
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.created >= @fechaProceso
AND CUIT_PROV IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.created >= @fechaProceso)

union

/* EMPRESAS ACCIONISTAS DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS [TAX ID],razon_social_prov AS [LEGAL NAME],
soc_vinculada as [LINKED PERSON OR ENTITY],cuit_soc_vinculada as [LINK's TAX ID],
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as [POSITION / RELATION]
,
iif(SPWSBRG.cuit is null,'NO','YES') AS [PEP/SANCTIONS LIST],
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS [POSITION / ROLE], 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS [STATUS],

fecha_defuncion as [FECHA DEFUNCION]/*, + '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
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.created >= @fechaProceso)
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.created >= @fechaProceso

UNION 

/* EMPRESAS ACCIONISTAS DE EMPRESAS NO PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS [TAX ID],razon_social_prov AS [LEGAL NAME],
soc_vinculada as [LINKED PERSON OR ENTITY],cuit_soc_vinculada as [LINK's TAX ID],
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as [POSITION / RELATION]
,
iif(SPWSBRG.cuit is null,'NO','YES') AS [PEP/SANCTIONS LIST],
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS [POSITION / ROLE], 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS [STATUS],


fecha_defuncion as [FECHA DEFUNCION]/*, + '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
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.created >= @fechaProceso)
AND screening_prov_out2.cliente = @IDCliente
and screening_prov_out2.created >= @fechaProceso

UNION

/* PERSONAS ACCIONISTAS DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS [TAX ID],razon_social_prov AS [LEGAL NAME],
nombre_integrante as [LINKED PERSON OR ENTITY],cuit_integrante as [LINK's TAX ID],
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as [POSITION / RELATION]
,
iif(SPWSBRG.cuit is null,'NO','YES') AS [PEP/SANCTIONS LIST],
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS [POSITION / ROLE], 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS [STATUS],


fecha_defuncion as [FECHA DEFUNCION]/*, + '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
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.created >= @fechaProceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.created >= @fechaProceso)


union



/* PERSONAS DIRECTORES DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS [TAX ID],razon_social_prov AS [LEGAL NAME],
nombre_integrante as [LINKED PERSON OR ENTITY],cuit_integrante as [LINK's TAX ID],
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as [POSITION / RELATION]
,

iif(SPWSBRG.cuit is null,'NO','YES') AS [PEP/SANCTIONS LIST],
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS [POSITION / ROLE], 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS [STATUS],

fecha_defuncion as [FECHA DEFUNCION]/*, + '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
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.created >= @fechaProceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.created >= @fechaProceso)

UNION



/* EMPRESAS DIRECTORES DE EMPRESAS PROVISTAS POR EL CLIENTE */
SELECT screening_prov.vendorid,CUIT_PROV AS [TAX ID],razon_social_prov AS [LEGAL NAME],
soc_vinculada as [LINKED PERSON OR ENTITY],cuit_soc_vinculada as [LINK's TAX ID],
iif(IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo) is null,'DUPLICADO',IIF(screening_prov_out2.cargo is null,titulo,screening_prov_out2.cargo)) as [POSITION / RELATION]
,
iif(SPWSBRG.cuit is null,'NO','YES') AS [PEP/SANCTIONS LIST],
iif(SPWSBRG.cargo is null,'NO',rtrim(ltrim(SPWSBRG.cargo)) + ' ' + rtrim(ltrim(SPWSBRG.lugar))) AS [POSITION / ROLE], 
iif(SPWSBRG.estado is null,'NO', SPWSBRG.estado) AS [STATUS],

fecha_defuncion as [FECHA DEFUNCION]/*, + '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
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.created >= @fechaProceso
AND cuit_prov IN (SELECT DISTINCT CUIT FROM screening_prov WHERE CLIENTE = @IDCliente and screening_prov.created >= @fechaProceso)

ORDER BY razon_social_prov ASC,[LINKED PERSON OR ENTITY] asc


END



if (@StepID=1)
 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 = 11)
 begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 2: SEGUNDO NIVEL DE BUSQUEDA BASÁNDOSE EN LAS SOCIEDADES VINCULADAS */
--------------------------------------------------------------------------------------------------------------------
  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)

  
  select p2.nombre_persona as razon_social_prov,p2.identificacion AS cuit_prov,
  @IDCliente as 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,
  'SOC_CONS_2' as tipo_reg /*SOC_CONS_1*/
  from relaciones 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 relaciones.id_tipo_relacion in (1,2)
  and relaciones.cuita in 
  /*
   (select DISTINCT rtrim(ltrim(cuit_soc_vinculada)) from screening_prov_out2 WHERE cuit_prov <> cuit_soc_vinculada and cliente = @IDCliente and len(rtrim(ltrim(cuit_soc_vinculada)))=11)
   */
   
   (select distinct ltrim(rtrim(cuit_soc_vinculada)) from screening_prov_out2  where cliente = @IDCliente 
   and ltrim(rtrim(cuit_soc_vinculada)) not in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.created >= @fechaProceso)
   and screening_prov_out2.tipo_reg = 'SOC_CONS_1' and len(rtrim(ltrim(cuit_soc_vinculada)))=11) 


  UNION ALL

  /* BUSCAMOS SOBRE LA TABLA SOCIEDADES CONSTITUCIONES */
  select  screening_prov_out2.razon_social_prov AS razon_social_prov ,screening_prov_out2.cuit_prov AS cuit_prov,
  screening_prov_out2.cliente as cliente,
  getdate() as created,
  sociedades_constituciones.titulo, sociedades_constituciones.cargo,
  iif(substring(sociedades_constituciones.cuit_int,1,1)='3',sociedades_constituciones.nombre_integrante,null) as soc_vinculada,
  iif(substring(sociedades_constituciones.cuit_int,1,1)='3',sociedades_constituciones.cuit_int,null) as cuit_soc_vinculada,
  iif(substring(sociedades_constituciones.cuit_int,1,1)='2',sociedades_constituciones.nombre_integrante,null) as nombre_integrante,
  iif(substring(sociedades_constituciones.cuit_int,1,1)='2',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_2' as tipo_reg

  from screening_prov_out2 left join sociedades_constituciones on screening_prov_out2.cuit_soc_vinculada = sociedades_constituciones.documento_soc
  left join padrones p2   on sociedades_constituciones.documento_soc = p2.identificacion 
  where sociedades_constituciones.documento_soc in 
  /*
   (select DISTINCT rtrim(ltrim(cuit_soc_vinculada)) from screening_prov_out2 
   WHERE cuit_prov <> cuit_soc_vinculada and cliente = @IDCliente and len(rtrim(ltrim(cuit_soc_vinculada)))=11)
   */
  (select distinct ltrim(rtrim(cuit_soc_vinculada)) from screening_prov_out2  where cliente = @IDCliente 
   and ltrim(rtrim(cuit_soc_vinculada)) not in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.created >= @fechaProceso)
   and screening_prov_out2.tipo_reg = 'SOC_CONS_1' and len(rtrim(ltrim(cuit_soc_vinculada)))=11) 


  and screening_prov_out2.cliente = @IDCliente


  union all

  /* BUSCAMOS SOBRE LA TABLA SOCIEDADES EDICTOS */

  
  select screening_prov_out2.soc_vinculada as razon_social_prov,screening_prov_out2.cuit_soc_vinculada AS cuit_prov,screening_prov_out2.cliente as 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)='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_2' as tipo_reg
  from screening_prov_out2 inner join EDICTOS on screening_prov_out2.cuit_soc_vinculada = edictos.cuit_sociedad
  inner join padrones    on edictos.cuit_integrante = padrones.identificacion
  inner join padrones p2 on EDICTOS.cuit_sociedad = p2.identificacion
  and edictos.cuit_sociedad in 
  /*
   (select DISTINCT rtrim(ltrim(cuit_soc_vinculada)) from screening_prov_out2 WHERE cuit_prov <> cuit_soc_vinculada and cliente = @IDCliente and len(rtrim(ltrim(cuit_soc_vinculada)))=11)
   */
  (select distinct ltrim(rtrim(cuit_soc_vinculada)) from screening_prov_out2  where cliente = @IDCliente 
   and ltrim(rtrim(cuit_soc_vinculada)) not in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.created >= @fechaProceso)
   and screening_prov_out2.tipo_reg = 'SOC_CONS_1' and len(rtrim(ltrim(cuit_soc_vinculada)))=11) 


  and screening_prov_out2.cliente = @IDCliente

 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 = 22)
 begin
--------------------------------------------------------------------------------------------------------------------
/* Paso 22: buscar accionistas en un segundo nivel con las vinculadas encontradas en el primer paso el paso 2*/
--------------------------------------------------------------------------------------------------------------------
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)

select DISTINCT screening_prov_out2.razon_social_prov AS razon_social_prov,screening_prov_out2.cuit_prov AS cuit_prov,
screening_prov_out2.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_2'
from screening_prov_out2 
inner join accionistas on screening_prov_out2.cuit_soc_vinculada = 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_soc_vinculada)) from screening_prov_out2  where cliente = @IDCliente 
and ltrim(rtrim(cuit_soc_vinculada)) not in (select distinct ltrim(rtrim(cuit)) from screening_prov where cliente = @IDCliente and screening_prov.created >= @fechaProceso)
and screening_prov_out2.tipo_reg = 'ACCIONISTA') 
and screening_prov_out2.cliente = @IDCliente


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 = 33) /*falta modificar*/
 begin

--------------------------------------------------------------------------------------------------------------------
/* Paso 33: buscar socios en tabla socios para las sociedades detectadas que no sean las provistas */
--------------------------------------------------------------------------------------------------------------------

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)

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'
from screening_prov
inner join socios on (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.created >= @fechaProceso ) and screening_prov.cliente = @IDCliente

end




if (@StepID = 5)
 begin

--------------------------------------------------------------------------------------------------------------------
/* Paso 5: Bucamos familiares de los CUITs de los proveedores  */ 
--------------------------------------------------------------------------------------------------------------------

--declare @IDCliente int = @IDCliente /*@IDCliente*/
insert into screening_prov_out (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,tipo_reg)
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,

getdate() as created,
'' as titulo, '' as cargo,
p3.nombre_persona as soc_vinculada,p3.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'FAMILIAR' 

from screening_prov 
inner join obras_sociales_beneficiarios OSB on OSB.CUILTI = screening_prov.cuit  
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P2 on OSB.CUILBE = P2.identificacion
inner join padrones P3 on OSB.CUILTI = P3.identificacion
where cliente = @IDCliente and screening_prov.created >= @fechaProceso
  and OSB.CUILBE<>OSB.cuilti

union all

select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' as titulo, '' as cargo,p3.nombre_persona as soc_vinculada,p3.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'FAMILIAR' 

from screening_prov 
inner join obras_sociales_beneficiarios OSB on OSB.CUILBE = screening_prov.cuit  
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P3 on OSB.CUILBE = P3.identificacion
inner join padrones P2 on OSB.CUILTI = P2.identificacion
where cliente = @IDCliente and screening_prov.created >= @fechaProceso
  and OSB.CUILBE<>OSB.cuilti
 
 end

if (@StepID = 8)
 begin

  --------------------------------------------------------------------------------------------------------------------
/* Paso 8:  buscamos familiares de los integrantes */
--------------------------------------------------------------------------------------------------------------------

--declare @IDCliente int = @IDCliente /*@IDCliente*/
insert into screening_prov_out (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,tipo_reg)
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' as titulo, '' as cargo,p3.nombre_persona as soc_vinculada,p3.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'FAMIL_INT' 
from screening_prov_out 
inner join obras_sociales_beneficiarios OSB on OSB.CUILTI = screening_prov_out.cuit_int or OSB.CUILTI = screening_prov_out.cuit_integrante
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P2 on OSB.CUILBE  = P2.identificacion
inner join padrones P3 on OSB.CUILTI  = P3.identificacion
where cliente = @IDCliente and (isnull(ltrim(rtrim(cuit_int)),'') <> '' or isnull(ltrim(rtrim(cuit_integrante)),'') <> '')
and tipo_reg != 'FAMILIAR'
and OSB.CUILBE<>OSB.cuilti

union all

select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' as titulo, '' as cargo,
p3.nombre_persona as soc_vinculada, p3.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'FAMIL_INT' 
from screening_prov_out 
inner join obras_sociales_beneficiarios OSB on OSB.CUILBE = screening_prov_out.cuit_int or OSB.CUILBE = screening_prov_out.cuit_integrante
inner join padrones P1 on OSB.CUITEMP = P1.identificacion
inner join padrones P3 on OSB.CUILBE = P3.identificacion
inner join padrones P2 on OSB.CUILTI = P2.identificacion
where cliente = @IDCliente and (isnull(ltrim(rtrim(cuit_int)),'') <> '' or isnull(ltrim(rtrim(cuit_integrante)),'') <> '')
and tipo_reg != 'FAMILIAR'
and OSB.CUILBE<>OSB.cuilti
end

if (@StepID = 4)
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 - 2
set @periodo_empleados_hasta = @periodo_empleados



/*
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,
NULL 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

if (@StepID = 7)
 begin

/*************************************************/
--------------------------------------------------------------------------------------------------------------------
/* Paso 7: buscar empleos de los integrantes */
--------------------------------------------------------------------------------------------------------------------
/*
declare @IDCliente int = @IDCliente /*@IDCliente*/
declare @ultimo_mes_empleados date
declare @periodo_empleados    int

*/
declare @ultimo_mes_empleados_1 date
/*
,
    @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))

set @ultimo_mes_empleados_1 = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
set @mes_empleados = DATEADD(M,-2,@ultimo_mes_empleados_1)
set @periodo_empleados_desde = convert(int,substring(convert(char,@mes_empleados),3,2)+substring(convert(char,@mes_empleados),6,2))
set @periodo_empleados_hasta = convert(int,substring(convert(char,@ultimo_mes_empleados_1),3,2)+substring(convert(char,@ultimo_mes_empleados),6,2))
set @ultimo_mes_empleados = '2017-12-01'
set @mes_empleados = '2017-08-01'
set @periodo_empleados_desde = 1708
set @periodo_empleados_hasta = 1712



insert into screening_prov_out (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,tipo_reg)

select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,

getdate() as created,
'' as titulo, '' as cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo, null as fecha_publicacion,
tipo_reg = 'EMPLE_INT'
from empleados
inner join PADRONES P1 on (empleados.cuit = p1.identificacion and empleados.periodo between @periodo_empleados_desde and @periodo_empleados_hasta )
inner join padrones p2 on (empleados.cuil = p2.identificacion and empleados.periodo between @periodo_empleados_desde and @periodo_empleados_hasta)
where empleados.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> ''

union all 
select distinct cuit_integrante as cuit_int from screening_prov_out where cliente = @IDCliente and  cuit_integrante is not null and cuit_integrante <> '' 
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> '')
)
and empleados.periodo in (@periodo_empleados_hasta))

union all 
select distinct P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,

getdate() as created,
'' as titulo, '' as cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
p2.nombre_persona as nombre_integrante,null as documento_integrante,
'' as nrdoc_int,p2.identificacion as cuit_int,p2.identificacion as cuit_integrante,
'' as contenido_archivo,null as fecha_publicacion,
tipo_reg = 'EMPLE_INT'
from empleados_temp EE inner join PADRONES P1  on (EE.cuit = p1.identificacion and EE.periodo between @periodo_empleados_desde and @periodo_empleados_hasta )
inner join padrones p2 on (EE.cuil = p2.identificacion  and EE.periodo between @periodo_empleados_desde and @periodo_empleados_hasta  )
where EE.cuil in (select cuil from empleados where cuil in (select distinct cuit_int as cuit_int from screening_prov_out where cliente = @IDCliente and  cuit_int is not null and cuit_int <> ''
union all 
select distinct cuit_integrante as cuit_int from screening_prov_out where cliente = @IDCliente and cuit_integrante is not null and cuit_integrante <> '' 
and cuit_integrante not in (select distinct cuit_int from screening_prov_out where cliente = @IDCliente and cuit_int is not null and cuit_int <> '')
)
and EE.periodo in (@periodo_empleados_hasta) )

order by p2.nombre_persona

end

if (@StepID = 9)
 begin

--------------------------------------------------------------------------------------------------------------------
/* Paso 9: screening TERCER CICLO DE BUSCANDO SOCIEDADES A PARTIR DE LOS CUIT INTEGRANTES*/
--------------------------------------------------------------------------------------------------------------------


--declare @IDCliente int = @IDCliente



insert into screening_prov_out (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,tipo_reg)
/*
select * from screening_prov_out where tipo_reg='INT_SOC_1' and cliente=@IDCliente
*/

select p2.nombre_persona as razon_social,p2.identificacion AS cuit,
screening_prov_out.cliente,

getdate() as created,
tp1.relacion as titulo, tsp1.subrelacion as cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,
p3.nombre_persona as nombre_integrante,p3.identificacion as documento_integrante,
p3.cuerpo_clave as nrdoc_int,
p3.identificacion as cuit_int,p3.identificacion as cuit_integrante,
null as contenido_archivo,relaciones.fecha_relacion,'INT_SOC_1' as tipo_reg
from screening_prov_out 
inner join relaciones on (screening_prov_out.cuit_int = relaciones.cuitb OR screening_prov_out.cuit_integrante = relaciones.cuitb)
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 screening_prov_out.cliente =  @IDCliente and relaciones.id_tipo_relacion = 1
and screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out where cliente = @IDCliente) 
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente) 

union all

select P2.nombre_persona AS razon_social,P2.identificacion AS cuit,

@IDCliente as cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion,
tipo_reg = 'INT_SOC_1'
from screening_prov_out inner join sociedades_constituciones on (screening_prov_out.cuit_integrante = sociedades_constituciones.cuit_int OR screening_prov_out.cuit_integrante = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion 
where screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out where cliente = @IDCliente) 
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente) 

union all

select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,

getdate() as created,
NULL AS titulo, NULL AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,NULL AS documento_integrante,
NULL AS nrdoc_int,NULL AS cuit_int,EDICTOS.cuit_integrante,EDICTOS.texto AS contenido_archivo,EDICTOS.fecha_boletin AS fecha_publicacion,
tipo_reg = 'INT_SOC_1'
from screening_prov_out inner join EDICTOS on (screening_prov_out.cuit_integrante = edictos.cuit_integrante OR screening_prov_out.cuit_int = edictos.cuit_integrante)
inner join padrones p2 on EDICTOS.cuit_integrante = p2.identificacion 
inner join padrones p1 on EDICTOS.cuit_sociedad = p1.identificacion 
where screening_prov_out.cuit_int in (select DISTINCT cuit_int from screening_prov_out where cliente = @IDCliente) 
OR screening_prov_out.cuit_integrante in (select DISTINCT cuit_integrante from screening_prov_out where cliente = @IDCliente) 

end

if (@StepID = 10)
 begin

---------------
-----------------------------------------------------------------------------------------------------
/* Paso 10:BUSCANDO SOCIEDADES A PARTIR DE LOS CUIT DE FAMILIARES */
--------------------------------------------------------------------------------------------------------------------

--declare @IDCliente int = @IDCliente /*@IDCliente*/


insert into screening_prov_out (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,tipo_reg)

select P2.nombre_persona AS razon_social,P2.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
sociedades_constituciones.titulo, sociedades_constituciones.cargo,
p2.nombre_persona as soc_vinculada,p2.identificacion as cuit_soc_vinculada,
sociedades_constituciones.nombre_integrante,sociedades_constituciones.documento_integrante,
sociedades_constituciones.nrdoc_int,sociedades_constituciones.cuit_int,
sociedades_constituciones.cuit_integrante,sociedades_constituciones.contenido_archivo,sociedades_constituciones.fecha_publicacion,
tipo_reg = 'FAM_SOC'
from screening_prov_out 
inner join sociedades_constituciones on (screening_prov_out.cuit_integrante = sociedades_constituciones.cuit_int OR screening_prov_out.cuit_integrante = sociedades_constituciones.cuit_integrante)
inner join padrones p2 on sociedades_constituciones.documento_soc = p2.identificacion 
where cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')

union all

select P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
NULL AS titulo, NULL AS cargo,
p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,NULL AS documento_integrante,
NULL AS nrdoc_int,NULL AS cuit_int,
EDICTOS.cuit_integrante,EDICTOS.texto AS contenido_archivo,EDICTOS.fecha_boletin AS fecha_publicacion,
tipo_reg = 'FAM_SOC'
from screening_prov_out 
inner join EDICTOS on (screening_prov_out.cuit_integrante = edictos.cuit_integrante OR screening_prov_out.cuit_int = edictos.cuit_integrante)
inner join padrones p2 on EDICTOS.cuit_integrante = p2.identificacion 
inner join padrones p1 on EDICTOS.cuit_sociedad = p1.identificacion 
where cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')

union all

select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,

getdate() as created,
'' AS titulo,cargos.car_nombre AS cargo,
p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,socios.c01_cuit_soc AS cuit_int,socios.c01_cuit_soc as cuit_integrante,'' AS contenido_archivo,getdate()  AS fecha_publicacion,
tipo_reg = 'FAM_SOC'
from screening_prov_out 
inner join socios on (screening_prov_out.cuit_integrante = socios.c01_cuit  OR screening_prov_out.cuit_int = socios.c01_cuit) and socios.visible = 1
inner join cargos on cargos.car_codigo = socios.car_codigo
inner join padrones p2 on socios.c01_cuit_soc = p2.identificacion 
inner join padrones p1 on socios.c01_cuit = p1.identificacion 
where cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')

union all

select DISTINCT P1.nombre_persona AS razon_social,P1.identificacion AS cuit,
@IDCliente as cliente,
getdate() as created,
'' AS titulo,'ACCIONISTA' AS cargo,p1.nombre_persona as soc_vinculada,p1.identificacion as cuit_soc_vinculada,
P2.nombre_persona AS nombre_integrante,'' AS documento_integrante,
'' AS nrdoc_int,accionistas.cuit_acc AS cuit_int,accionistas.cuit_acc as cuit_integrante,'' AS contenido_archivo,getdate()  AS fecha_publicacion,
tipo_reg = 'FAM_SOC'

from screening_prov_out 
inner join accionistas on (accionistas.cuit_acc = screening_prov_out.cuit_integrante  
                           OR accionistas.cuit_acc  = screening_prov_out.cuit_int) 
        and accionistas.visible = 1
inner join padrones p2 on accionistas.cuit_acc = p2.identificacion 
inner join padrones p1 on accionistas.cuit_sociedad = p1.identificacion 
where cliente = @IDCliente and tipo_reg in ('FAMILIAR','FAMIL_INT')
end


/*
declare @IDCliente int = @IDCliente
*/



/*



select * from screening_prov_out where cuit_integrante is null and cuit_int is not null and cuit_int <> ''

update screening_prov_out set cuit_integrante = cuit_int where cuit_integrante is null and cuit_int is not null and cuit_int <> ''

select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc

select * from screening_prov_out 

where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''

select distinct id_screening_prov_out,nrdoc_int,cuit_int,cuit_integrante,nombre_integrante from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc


order by nombre_integrante asc


select * from screening_prov_out 
where nrdoc_int in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_integrante asc



select * from padrones where cuerpo_clave in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_persona asc
select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc






select * from screening_prov_out where cuit_integrante is null AND documento_integrante IS NOT NULL ORDER BY documento_integrante DESC



UPDATE screening_prov_out SET nrdoc_int = NULL WHERE nrdoc_int = ''
UPDATE screening_prov_out SET documento_integrante = NULL WHERE documento_integrante = ''
UPDATE screening_prov_out SET cuit_int = NULL WHERE cuit_int = ''


select * from padrones where cuerpo_clave in (
select documento_integrante from screening_prov_out where cuit_integrante is null and documento_integrante IS NOT NULL)
order by nombre_persona asc

select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc

select top 10 * from asignacion_cuits_sociedades_constituciones_log where documemto_limpio = '8456015'


select * from sociedades_constituciones where documento_soc='30711364710'

select * from empleados where cuit='33708800959'

select distinct screening_prov_out.nombre_integrante,cuit_int,padrones.identificacion,padrones.nombre_persona
from screening_prov_out inner join padrones on screening_prov_out.nombre_integrante = padrones.nombre_persona
inner join empleados on (empleados.cuit = screening_prov_out.cuit and empleados.periodo= 1704 and empleados.cuil = padrones.identificacion)
and cuit_integrante is null






select * from screening_prov_out where cuit_integrante is null and cuit_int is not null and cuit_int <> ''

update screening_prov_out set cuit_integrante = cuit_int where cuit_integrante is null and cuit_int is not null and cuit_int <> ''

select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc

select * from screening_prov_out 

where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''

select distinct id_screening_prov_out,nrdoc_int,cuit_int,cuit_integrante,nombre_integrante from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc





select * from padrones where cuerpo_clave in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_persona asc

select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc

select * from screening_prov_out where cuit_integrante is null AND documento_integrante IS NOT NULL ORDER BY documento_integrante DESC


select * from screening_prov_out 
where nrdoc_int in (
select nrdoc_int from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> '')
order by nombre_integrante asc

select * from padrones where contains(*,'6482078')

update screening_prov_out set nombre_integrante='GLATSTEIN ARON',documento_integrante=2902140,cuit_int=20029021407,cuit_integrante=20029021407 where id_screening_prov_out=1881
update screening_prov_out set nombre_integrante='PELUDERO CARLOS HORACIO',documento_integrante=66365843015515,cuit_int=230663658430155159,cuit_integrante=230663658430155159 where id_screening_prov_out=1882
update screening_prov_out set nombre_integrante='TREBER SALVADOR',documento_integrante=6473098,cuit_int=20064730984,cuit_integrante=20064730984 where id_screening_prov_out=1883
update screening_prov_out set nombre_integrante='TREBER SALVADOR',documento_integrante=6473098,cuit_int=20064730984,cuit_integrante=20064730984 where id_screening_prov_out=1884
update screening_prov_out set nombre_integrante='GLATSTEIN DANIEL ADOLFO',documento_integrante=4986712,cuit_int=20049867124,cuit_integrante=20049867124 where id_screening_prov_out=1885
update screening_prov_out set nombre_integrante='GLATSTEIN JUAN OSCAR',documento_integrante=6489883,cuit_int=20064898834,cuit_integrante=20064898834 where id_screening_prov_out=1886
update screening_prov_out set nombre_integrante='CASTRO ROMEO',documento_integrante=7970023,cuit_int=20079700232,cuit_integrante=20079700232 where id_screening_prov_out=1887
update screening_prov_out set nombre_integrante='GLATSTEIN SANTIAGO',documento_integrante=6482078,cuit_int=20064820789,cuit_integrante=20064820789 where id_screening_prov_out=1888





UPDATE screening_prov_out SET nrdoc_int = NULL WHERE nrdoc_int = ''
UPDATE screening_prov_out SET documento_integrante = NULL WHERE documento_integrante = ''
UPDATE screening_prov_out SET cuit_int = NULL WHERE cuit_int = ''


select * from padrones where cuerpo_clave in (
select documento_integrante from screening_prov_out where cuit_integrante is null and documento_integrante IS NOT NULL)
order by nombre_persona asc

select * from screening_prov_out where cuit_integrante is null and nrdoc_int is not null and nrdoc_int <> ''
order by nombre_integrante asc

select top 10 * from asignacion_cuits_sociedades_constituciones_log where documemto_limpio = '8456015'


select * from sociedades_constituciones where documento_soc='30711364710'

select * from empleados where cuit='33708800959'

select distinct screening_prov_out.nombre_integrante,cuit_int,padrones.identificacion,padrones.nombre_persona
from screening_prov_out inner join padrones on screening_prov_out.nombre_integrante = padrones.nombre_persona
inner join empleados on (empleados.cuit = screening_prov_out.cuit and empleados.periodo= 1704 and empleados.cuil = padrones.identificacion)
and cuit_integrante is null

update screening_prov_out set tipo_reg = 'SOC_CONS_1'









*/





END

GO