dbo.LN_control_etica_por_cuit_01062022

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
select * from LN_control_screening where cliente = 5546348 and visible = 1
exec [LN_control_etica_por_cuit] 5546348, '05099247', 'ar'
exec [LN_control_etica_por_cuit] 5546348, '1006730', 'PY'


*/
CREATE OR ALTER PROCEDURE dbo.LN_control_etica_por_cuit_01062022 
@p_cliente int, 
@p_cuit    varchar(20), 
@p_pais    varchar(10)  = null 
AS
BEGIN
    SET NOCOUNT ON;
 /* 31/05/2022: Se agrego el control de que el numero de tax-id sea del mismo pais 
  Antes de esa fecha se hacia asi:
 
  select distinct socios.c01_cuit_soc as cuit, padrones2.nombre_persona as nombre, car_nombre as cargo, 
    isnull(LN_control_screening.relacion,'') as relacion, 
    LN_control_screening.empleado as empleado_cuit, 
    padrones3.nombre_persona as empleado_nombre
    from padrones  
    inner join socios on socios.c01_cuit = padrones.identificacion 
    inner join padrones padrones2 on padrones2.identificacion = socios.c01_cuit_soc and padrones2.habilitado = 1 
    inner join LN_control_screening on LN_control_screening.identificacion = socios.c01_cuit_soc 
    left join padrones padrones3 on (padrones3.identificacion = LN_control_screening.empleado and padrones3.habilitado = 1)
    left join cargos on cargos.car_codigo = socios.car_codigo 
   where padrones.identificacion = @p_cuit
     and socios.visible = 1
     and isnull(socios.c04_ultimo,'') != 'N'
     and LN_control_screening.visible = 1
     and LN_control_screening.cliente = @p_cliente
 
    union all 
 
    select distinct padrones.identificacion as cuit, padrones.nombre_persona as nombre, '' as cargo, 
    isnull(LN_control_screening.relacion,'') as relacion, 
    LN_control_screening.empleado as empleado_cuit, 
    padrones3.nombre_persona as empleado_nombre  
    from padrones 
    inner join LN_control_screening on LN_control_screening.identificacion = padrones.identificacion
    left join padrones padrones3 on (padrones3.identificacion = LN_control_screening.empleado and padrones3.habilitado = 1)
   where padrones.identificacion = @p_cuit
     and LN_control_screening.visible = 1
     and LN_control_screening.cliente = @p_cliente
 
 
  */
 declare @nrodoc varchar(8) = ''
 declare @nrodoc2 varchar(8) = ''


 /*if (@p_pais = 'AR' and len(@p_cuit) <= 8) */
 if (len(@p_cuit) <= 8)
 begin
  set @nrodoc = right('00000000'+@p_cuit,8)
  set @p_cuit = convert(int,@p_cuit)
  set @nrodoc2 = @p_cuit

 end

 if (@p_pais is null)
 begin
  -- Buscamos los socios del cuit consultado, y vemos si alguno está en la lista de screening PERO QUE SEA DEL MISMO PAIS 
  select distinct '1', socios.c01_cuit_soc as cuit, padrones2.nombre_persona as nombre, car_nombre as cargo, 
    isnull(LN_control_screening.relacion,'') as relacion, 
    LN_control_screening.empleado as empleado_cuit, 
    padrones3.nombre_persona as empleado_nombre
    from padrones  
    inner join socios on socios.c01_cuit = padrones.identificacion 
    inner join padrones padrones2 on padrones2.identificacion = socios.c01_cuit_soc and padrones2.habilitado = 1 
      inner join identificadores on identificadores.identificador = padrones2.identificador  -- para ver el pais de ese socio
    inner join LN_control_screening on  (LN_control_screening.identificacion = socios.c01_cuit_soc and 
              LN_control_screening.country_code = identificadores.country_code)
    left join padrones padrones3 on (padrones3.identificacion = LN_control_screening.empleado and padrones3.habilitado = 1)
    left join cargos on cargos.car_codigo = socios.car_codigo 
   where (padrones.identificacion = @p_cuit or (padrones.cuerpo_clave in (@nrodoc,@nrodoc2) and identificadores.country_code = 'AR'))
     and socios.visible = 1
     and isnull(socios.c04_ultimo,'') != 'N'
     and LN_control_screening.visible = 1
     and LN_control_screening.cliente = @p_cliente
     
    union  

    select distinct '2', LN_control_screening.identificacion as cuit, padrones.nombre_persona as nombre, '' as cargo, 
    isnull(LN_control_screening.relacion,'') as relacion, 
    LN_control_screening.empleado as empleado_cuit, 
    padrones3.nombre_persona as empleado_nombre  
   from padrones 
    inner join identificadores on identificadores.identificador = padrones.identificador 
    inner join LN_control_screening on (LN_control_screening.identificacion = padrones.identificacion and
                                        LN_control_screening.country_code = identificadores.country_code)
    left join padrones padrones3 on (padrones3.identificacion = LN_control_screening.empleado and padrones3.habilitado = 1)
       
   where (LN_control_screening.identificacion = @p_cuit or (SUBSTRING(LN_control_screening.identificacion, 3, 8) = @nrodoc and LN_control_screening.country_code = 'AR'))

     and LN_control_screening.visible = 1
     and LN_control_screening.cliente = @p_cliente
     

   union  

    select distinct '3', LN_control_screening.identificacion as cuit, padrones.nombre_persona as nombre, '' as cargo, 
    isnull(LN_control_screening.relacion,'') as relacion, 
    LN_control_screening.empleado as empleado_cuit, 
    padrones3.nombre_persona as empleado_nombre  
    from LN_control_screening
    left join padrones on (padrones.identificacion = LN_control_screening.identificacion and padrones.habilitado = 1 
                           and padrones.identificador in (select identificador 
                                          from identificadores 
                 where country_code = LN_control_screening.country_code))
    left join padrones padrones3 on (padrones3.identificacion = LN_control_screening.empleado and padrones3.habilitado = 1)
       
   where (LN_control_screening.identificacion = @p_cuit or (SUBSTRING(LN_control_screening.identificacion, 3, 8) = @nrodoc and LN_control_screening.country_code = 'AR'))

     and LN_control_screening.visible = 1
     and LN_control_screening.cliente = @p_cliente
 end 
 else 
 begin 
 -- Buscamos los socios del cuit consultado, y vemos si alguno está en la lista de screening 
  select distinct socios.c01_cuit_soc as cuit, padrones2.nombre_persona as nombre, car_nombre as cargo, 
    isnull(LN_control_screening.relacion,'') as relacion, 
    LN_control_screening.empleado as empleado_cuit, 
    padrones3.nombre_persona as empleado_nombre
    from padrones  
    inner join socios on socios.c01_cuit = padrones.identificacion 
    inner join padrones padrones2 on padrones2.identificacion = socios.c01_cuit_soc and padrones2.habilitado = 1 
      inner join identificadores on identificadores.identificador = padrones2.identificador  -- para ver el pais de ese socio
    inner join LN_control_screening on  (LN_control_screening.identificacion = socios.c01_cuit_soc and 
              LN_control_screening.country_code = identificadores.country_code)
    left join padrones padrones3 on (padrones3.identificacion = LN_control_screening.empleado and padrones3.habilitado = 1)
    left join cargos on cargos.car_codigo = socios.car_codigo 
   where (padrones.identificacion = @p_cuit or (padrones.cuerpo_clave in (@nrodoc,@nrodoc2) and identificadores.country_code = 'AR'))

     and socios.visible = 1
     and isnull(socios.c04_ultimo,'') != 'N'
     and LN_control_screening.visible = 1
     and LN_control_screening.cliente = @p_cliente

     and identificadores.country_code = @p_pais
     
    union  

    select distinct LN_control_screening.identificacion as cuit, padrones.nombre_persona as nombre, '' as cargo, 
    isnull(LN_control_screening.relacion,'') as relacion, 
    LN_control_screening.empleado as empleado_cuit, 
    padrones3.nombre_persona as empleado_nombre  
   from padrones 
    inner join identificadores on identificadores.identificador = padrones.identificador 
    inner join LN_control_screening on (LN_control_screening.identificacion = padrones.identificacion and
                                        LN_control_screening.country_code = identificadores.country_code)


    left join padrones padrones3 on (padrones3.identificacion = LN_control_screening.empleado and padrones3.habilitado = 1)
       
   where (LN_control_screening.identificacion = @p_cuit or (SUBSTRING(LN_control_screening.identificacion, 3, 8) = @nrodoc and LN_control_screening.country_code = 'AR'))
     and LN_control_screening.visible = 1
     and LN_control_screening.cliente = @p_cliente
     and identificadores.country_code = @p_pais


   union  

    select distinct LN_control_screening.identificacion as cuit, padrones.nombre_persona as nombre, '' as cargo, 
    isnull(LN_control_screening.relacion,'') as relacion, 
    LN_control_screening.empleado as empleado_cuit, 
    padrones3.nombre_persona as empleado_nombre  
    from LN_control_screening
    left join padrones on (padrones.identificacion = LN_control_screening.identificacion and padrones.habilitado = 1 
                           and padrones.identificador in (select identificador 
                                          from identificadores 
                 where country_code = LN_control_screening.country_code))
    left join padrones padrones3 on (padrones3.identificacion = LN_control_screening.empleado and padrones3.habilitado = 1)
       
   where (LN_control_screening.identificacion = @p_cuit or (SUBSTRING(LN_control_screening.identificacion, 3, 8) = @nrodoc and LN_control_screening.country_code = 'AR'))

     and LN_control_screening.visible = 1
     and LN_control_screening.cliente = @p_cliente
     and LN_control_screening.country_code = @p_pais
 end 
END 
/*
select * from LN_control_screening where visible = 1 and country_code ='PY'
*/
GO