Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
13| Name | Data Type | Length | Description |
|---|---|---|---|
| @action | varchar | 30 | |
| @p_familia | int | 4 | |
| @p_nombre_familia | varchar | 100 | |
| @p_cliente | int | 4 | |
| @p_country_code | varchar | 2 | |
| @p_visible | tinyint | 1 | |
| @p_user_id | int | 4 | |
| @p_set_doc | int | 4 | |
| @p_subfamiia | tinyint | 1 | |
| @p_modalidad_login | varchar | 1 | |
| @p_uso_familia | varchar | 1 | |
| @p_id_producto | int | 4 | |
| @p_id_imperium | int | 4 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.LN_familias_sc
-- Add the parameters for the stored procedure here
@action varchar(30),
@p_familia int,
@p_nombre_familia varchar(100),
@p_cliente int,
@p_country_code varchar(2),
@p_visible tinyint,
@p_user_id int,
@p_set_doc int = NULL,
@p_subfamiia tinyint = 0,
@p_modalidad_login varchar(1) = 'I',
@p_uso_familia varchar(1) = 'P',
@p_id_producto int = 47,
@p_id_imperium int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF (@action = 'INSERT') BEGIN
DECLARE @familia INT
insert into LN_familias
(nombre_familia, cliente, country_code, set_doc, modalidad_login, subfamilia, uso_familia, visible, created_by, id_producto,servicio_contratado,id_imperium)
values
(@p_nombre_familia, @p_cliente, @p_country_code, @p_set_doc, @p_modalidad_login, @p_subfamiia, @p_uso_familia, @p_visible, @p_user_id, @p_id_producto,@p_id_producto,@p_id_imperium)
SET @familia = SCOPE_IDENTITY()
/* 10/03/2021:
Cuando se da de alta una familia, todos los usuarios del cliente no pueden verla, para ello se cargan en usuarios_restricciones
*/
insert into usuarios_restricciones (user_id, cliente, pais_servicio, servicio_contratado, familia, created, created_by)
select distinct users.user_id, @p_cliente, /*@p_country_code*/ NULL, @p_id_producto, @familia, getdate(), @p_user_id
from usuarios_productos
inner join users
on users.user_id = usuarios_productos.user_id
where usuarios_productos.cliente = @p_Cliente
and isnull(users.active_user,'Y') = 'Y'
and usuarios_productos.id_producto = @p_id_producto
/* */
SELECT @familia AS familia
END ELSE
IF (@action = 'UPDATE') BEGIN
UPDATE LN_familias set
nombre_familia = @p_nombre_familia,
cliente = @p_cliente,
country_code = @p_country_code,
set_doc = @p_set_doc,
modalidad_login = @p_modalidad_login,
subfamilia = @p_subfamiia,
visible = @p_visible,
uso_familia = @p_uso_familia,
id_producto = @p_id_producto,
modified = GETDATE(),
modified_by = @p_user_id,
id_imperium = @p_id_imperium,
servicio_contratado = @p_id_producto
where familia = @p_familia
END ELSE
IF (@action = 'DELETE') BEGIN
delete from LN_familias where familia = @p_familia
END ELSE
IF (@action = 'SELECT') BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
ln_familias.servicio_contratado,
ln_familias.id_imperium
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and
familia = @p_familia
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
where LN_familias.familia = @p_familia
order by country.country_name, LN_familias.nombre_familia
END ELSE
-- acción específica para familias de clientes de Inquiro Vitae uso_familia='I'
IF (@action = 'inquirovitae') BEGIN
if (@p_cliente is not null) begin
if (@p_visible is null)
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and
familia = @p_familia
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
where LN_familias.cliente = @p_cliente
and LN_familias.uso_familia = 'I'
order by country.country_name, LN_familias.nombre_familia
else
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and
familia = @p_familia
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
where LN_familias.cliente = @p_cliente
and LN_familias.visible = @p_visible
and LN_familias.uso_familia = 'I'
order by country.country_name, LN_familias.nombre_familia
end
END ELSE BEGIN
IF (@p_country_code IS NULL) BEGIN
IF (@p_cliente IS NULL and @p_visible IS NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 AND LN_contrapartes.cliente = @p_cliente
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
where ISNULL(LN_familias.subfamilia, 0) = 0
and LN_familias.uso_familia != 'I'
order by country.country_name, LN_familias.nombre_familia
end else
IF (@p_cliente IS NULL and @p_visible IS NOT NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 AND LN_contrapartes.cliente = @p_cliente
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.visible = @p_visible
AND ISNULL(LN_familias.subfamilia, 0) = 0
and LN_familias.uso_familia != 'I'
order by country.country_name, LN_familias.nombre_familia
end else
-- opcion general de familias por cliente
if (@p_cliente IS NOT NULL and @p_visible IS NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 AND LN_contrapartes.cliente = @p_cliente
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.cliente = @p_cliente
AND ISNULL(LN_familias.subfamilia, 0) = 0
--and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
END ELSE
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 AND LN_contrapartes.cliente = @p_cliente
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.cliente = @p_cliente
AND LN_familias.visible = @p_visible
AND ISNULL(LN_familias.subfamilia, 0) = 0
--and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
END ELSE BEGIN
/** SEARCH POR PAIS **/
IF (@p_cliente IS NULL and @p_visible IS NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias
inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE /*LN_familias.country_code = @p_country_code
AND*/ ISNULL(LN_familias.subfamilia, 0) = 0
-- and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
end else
IF (@p_cliente IS NULL and @p_visible IS NOT NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.visible = @p_visible
--AND LN_familias.country_code = @p_country_code
AND ISNULL(LN_familias.subfamilia, 0) = 0
-- and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
end else
if (@p_cliente IS NOT NULL and @p_visible IS NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.cliente = @p_cliente
--AND LN_familias.country_code = @p_country_code
AND ISNULL(LN_familias.subfamilia, 0) = 0
--and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
END ELSE
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.cliente = @p_cliente
AND LN_familias.visible = @p_visible
--AND LN_familias.country_code = @p_country_code
AND ISNULL(LN_familias.subfamilia, 0) = 0
--and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
END
END
END
GO
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.LN_familias_sc
-- Add the parameters for the stored procedure here
@action varchar(30),
@p_familia int,
@p_nombre_familia varchar(100),
@p_cliente int,
@p_country_code varchar(2),
@p_visible tinyint,
@p_user_id int,
@p_set_doc int = NULL,
@p_subfamiia tinyint = 0,
@p_modalidad_login varchar(1) = 'I',
@p_uso_familia varchar(1) = 'P',
@p_id_producto int = 47,
@p_id_imperium int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF (@action = 'INSERT') BEGIN
DECLARE @familia INT
insert into LN_familias
(nombre_familia, cliente, country_code, set_doc, modalidad_login, subfamilia, uso_familia, visible, created_by, id_producto,servicio_contratado,id_imperium)
values
(@p_nombre_familia, @p_cliente, @p_country_code, @p_set_doc, @p_modalidad_login, @p_subfamiia, @p_uso_familia, @p_visible, @p_user_id, @p_id_producto,@p_id_producto,@p_id_imperium)
SET @familia = SCOPE_IDENTITY()
/* 10/03/2021:
Cuando se da de alta una familia, todos los usuarios del cliente no pueden verla, para ello se cargan en usuarios_restricciones
*/
insert into usuarios_restricciones (user_id, cliente, pais_servicio, servicio_contratado, familia, created, created_by)
select distinct users.user_id, @p_cliente, /*@p_country_code*/ NULL, @p_id_producto, @familia, getdate(), @p_user_id
from usuarios_productos
inner join users
on users.user_id = usuarios_productos.user_id
where usuarios_productos.cliente = @p_Cliente
and isnull(users.active_user,'Y') = 'Y'
and usuarios_productos.id_producto = @p_id_producto
/* */
SELECT @familia AS familia
END ELSE
IF (@action = 'UPDATE') BEGIN
UPDATE LN_familias set
nombre_familia = @p_nombre_familia,
cliente = @p_cliente,
country_code = @p_country_code,
set_doc = @p_set_doc,
modalidad_login = @p_modalidad_login,
subfamilia = @p_subfamiia,
visible = @p_visible,
uso_familia = @p_uso_familia,
id_producto = @p_id_producto,
modified = GETDATE(),
modified_by = @p_user_id,
id_imperium = @p_id_imperium,
servicio_contratado = @p_id_producto
where familia = @p_familia
END ELSE
IF (@action = 'DELETE') BEGIN
delete from LN_familias where familia = @p_familia
END ELSE
IF (@action = 'SELECT') BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
ln_familias.servicio_contratado,
ln_familias.id_imperium
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and
familia = @p_familia
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
where LN_familias.familia = @p_familia
order by country.country_name, LN_familias.nombre_familia
END ELSE
-- acción específica para familias de clientes de Inquiro Vitae uso_familia='I'
IF (@action = 'inquirovitae') BEGIN
if (@p_cliente is not null) begin
if (@p_visible is null)
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and
familia = @p_familia
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
where LN_familias.cliente = @p_cliente
and LN_familias.uso_familia = 'I'
order by country.country_name, LN_familias.nombre_familia
else
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and
familia = @p_familia
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
where LN_familias.cliente = @p_cliente
and LN_familias.visible = @p_visible
and LN_familias.uso_familia = 'I'
order by country.country_name, LN_familias.nombre_familia
end
END ELSE BEGIN
IF (@p_country_code IS NULL) BEGIN
IF (@p_cliente IS NULL and @p_visible IS NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 AND LN_contrapartes.cliente = @p_cliente
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
where ISNULL(LN_familias.subfamilia, 0) = 0
and LN_familias.uso_familia != 'I'
order by country.country_name, LN_familias.nombre_familia
end else
IF (@p_cliente IS NULL and @p_visible IS NOT NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 AND LN_contrapartes.cliente = @p_cliente
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.visible = @p_visible
AND ISNULL(LN_familias.subfamilia, 0) = 0
and LN_familias.uso_familia != 'I'
order by country.country_name, LN_familias.nombre_familia
end else
-- opcion general de familias por cliente
if (@p_cliente IS NOT NULL and @p_visible IS NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 AND LN_contrapartes.cliente = @p_cliente
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.cliente = @p_cliente
AND ISNULL(LN_familias.subfamilia, 0) = 0
--and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
END ELSE
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 AND LN_contrapartes.cliente = @p_cliente
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.cliente = @p_cliente
AND LN_familias.visible = @p_visible
AND ISNULL(LN_familias.subfamilia, 0) = 0
--and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
END ELSE BEGIN
/** SEARCH POR PAIS **/
IF (@p_cliente IS NULL and @p_visible IS NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias
inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE /*LN_familias.country_code = @p_country_code
AND*/ ISNULL(LN_familias.subfamilia, 0) = 0
-- and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
end else
IF (@p_cliente IS NULL and @p_visible IS NOT NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.visible = @p_visible
--AND LN_familias.country_code = @p_country_code
AND ISNULL(LN_familias.subfamilia, 0) = 0
-- and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
end else
if (@p_cliente IS NOT NULL and @p_visible IS NULL) BEGIN
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.cliente = @p_cliente
--AND LN_familias.country_code = @p_country_code
AND ISNULL(LN_familias.subfamilia, 0) = 0
--and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
END ELSE
select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible,
LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
users.first_name + ' ' + users.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user,
clientes.nombre_cliente, country.country_name,
contrap_fam.total_contrapartes,
upper(productos.nombre_producto) as servicio_contratado_name,
iif(isnull(doc.hay,0) > 0, 'si','no') as carga_documentos
from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
inner join country on LN_familias.country_code = country.country_code
left outer join users on LN_familias.created_by = users.user_id
left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
-- 09/09/2020
left outer join (select LN_set_tipo_doc.set_doc, count(*) hay
from LN_set_tipo_doc
inner join LN_tipos_doc_carga_verif on LN_tipos_doc_carga_verif.tipo_doc = ln_set_tipo_doc.tipo_doc
where quien_carga = 'L'
group by LN_set_tipo_doc.set_doc
) doc
on doc.set_doc = LN_familias.set_doc
left outer join users AS users2 on LN_familias.modified_by = users2.user_id
left outer join productos on productos.producto = ln_familias.servicio_contratado
left outer join
(select familia, COUNT(*) AS total_contrapartes
from LN_contraparte_familias
inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
WHERE LN_familias.cliente = @p_cliente
AND LN_familias.visible = @p_visible
--AND LN_familias.country_code = @p_country_code
AND ISNULL(LN_familias.subfamilia, 0) = 0
--and LN_familias.uso_familia != 'I'
-- 24/06/2020 segun servicio
and LN_familias.servicio_contratado = @p_id_producto
order by country.country_name, LN_familias.nombre_familia
END
END
END
GO
Depends On
12Used By
No items found