Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
10| Name | Data Type | Length | Description |
|---|---|---|---|
| @accion | varchar | 20 | |
| @p_set_doc | int | 4 | |
| @p_nombre_set | varchar | 50 | |
| @p_descripcion_set | varchar | -1 | |
| @p_cliente | int | 4 | |
| @p_hab_contratista | tinyint | 1 | |
| @p_hab_subcontratista | tinyint | 1 | |
| @p_user_id | int | 4 | |
| @plataforma | varchar | -1 | |
| @p_principal | bit | 1 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
/*
exec ln_set_docs_ 'cliente',null,null,null,5546348,null,null
select * from ln_set_docs where cliente = 5546348
*/
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.LN_set_docs_
-- Add the parameters for the stored procedure here
@accion varchar(20),
@p_set_doc int = null,
@p_nombre_set varchar(50) = null,
@p_descripcion_set varchar(max) = null,
@p_cliente int = null,
@p_hab_contratista tinyint = 1,
@p_hab_subcontratista tinyint = 0,
@p_user_id int = null,
@plataforma varchar(max) = null,
@p_principal bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/* if (@plataforma is null)
begin
select @plataforma = (SELECT top 1 STUFF((SELECT ',' + convert(varchar(max),producto) FROM productos FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)'), 1, LEN(','), '') AS XmlPathList FROM productos GROUP BY productos.producto)
end
*/
if (@plataforma is null and @accion != 'INSERT')
begin
set @plataforma = '47,73'
/*set @plataforma='10'*/
end
-- Insert statements for procedure here
declare @idSet int = null
IF ( @accion = 'SEARCH_FAM_SET' )
BEGIN
SELECT LN_familias.familia,LN_familias.nombre_familia,clientes.cliente,clientes.nombre_cliente FROM LN_set_docs
LEFT JOIN LN_familias ON LN_familias.set_doc=LN_set_docs.set_doc
LEFT JOIN clientes ON clientes.cliente=LN_familias.cliente
WHERE LN_set_docs.set_doc=@p_set_doc
RETURN
END
IF ( @accion = 'SEARCH_SET_NAME' )
BEGIN
IF ( @p_nombre_set IS NOT NULL )
BEGIN
SELECT count(*) FROM LN_set_docs WHERE nombre_set = @p_nombre_set;
END
ELSE
BEGIN
SELECT 0;
END
END
if (@accion = 'INSERT') begin
if (@plataforma is null)
set @plataforma = '47'
insert into LN_set_docs
(nombre_set, descripcion_set, cliente, hab_contratista, hab_subcontratista, created_by,servicio_contratado)
values
(@p_nombre_set, @p_descripcion_set, @p_cliente, @p_hab_contratista, @p_hab_subcontratista, @p_user_id,@plataforma)
set @idSet = @@IDENTITY
select @idSet as idSet
end else
if (@accion = 'UPDATE') begin
update LN_set_docs set
nombre_set = @p_nombre_set,
descripcion_set = @p_descripcion_set,
cliente = @p_cliente,
hab_contratista = @p_hab_contratista,
hab_subcontratista = @p_hab_subcontratista,
principal = @p_principal,
modified = GETDATE(),
modified_by = @p_user_id
where set_doc = @p_set_doc
end else
if (@accion = 'SELECT') begin
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
where LN_set_docs.set_doc = @p_set_doc
end else
if (@accion = 'DELETE') begin
DELETE FROM LN_set_tipo_doc WHERE set_doc = @p_set_doc
DELETE FROM LN_set_docs WHERE set_doc = @p_set_doc
end else
if (@accion = 'CLIENTE')
begin
/*select * from clientes where cliente = @p_cliente*/
if (@p_hab_contratista is not null AND @p_hab_subcontratista is not null)
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
where (LN_set_docs.cliente is null OR LN_set_docs.cliente = @p_cliente)
and LN_set_docs.hab_contratista = @p_hab_contratista
and LN_set_docs.hab_subcontratista = @p_hab_subcontratista
order by LN_set_docs.nombre_set
else
if (@p_hab_contratista is not null AND @p_hab_subcontratista is null)
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
where (LN_set_docs.cliente is null OR LN_set_docs.cliente = @p_cliente)
and LN_set_docs.hab_contratista = @p_hab_contratista
order by LN_set_docs.nombre_set
else
if (@p_hab_contratista is null AND @p_hab_subcontratista is not null)
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
where (LN_set_docs.cliente is null OR LN_set_docs.cliente = @p_cliente)
and LN_set_docs.hab_subcontratista = @p_hab_subcontratista
order by LN_set_docs.nombre_set
else
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
where (LN_set_docs.cliente is null OR LN_set_docs.cliente = @p_cliente)
order by LN_set_docs.nombre_set
end else
/* Search */
-- exec ln_set_docs_ 'search'
if (@p_cliente is null)
/* select LN_set_docs.*, clientes.nombre_cliente, clientes.cli_codigo,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
order by LN_set_docs.nombre_set
*/ begin
/* select distinct @plataforma, LN_set_docs.*, clientes.nombre_cliente, clientes.cli_codigo,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left join clientes_productos on clientes.cliente = clientes_productos.cliente
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
order by LN_set_docs.nombre_set
*/
SELECT DISTINCT LN_set_docs.set_doc, LN_set_docs.nombre_set,LN_set_docs.descripcion_set,
LN_set_docs.cliente , LN_set_docs.hab_contratista, LN_set_docs.hab_subcontratista,
LN_set_docs.created , LN_set_docs.created_by , LN_set_docs.modified , LN_set_docs.modified_by,
LN_set_docs.principal , LN_set_docs.servicio_contratado , LN_set_docs.grupo_set,
clientes.nombre_cliente, clientes.cli_codigo,
users1.first_name + ' ' + users1.last_name AS created_by_user,
users2.first_name + ' ' + users2.last_name AS modified_by_user,
COUNT(LN_familias.familia) AS familias_set_asignado , COUNT(DISTINCT LN_familias.cliente) AS clientes_set_asignado
FROM LN_set_docs
LEFT OUTER JOIN clientes ON LN_set_docs.cliente = clientes.cliente
LEFT JOIN clientes_productos ON clientes.cliente = clientes_productos.cliente
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = CONVERT(int,exceptuados.Item)
LEFT OUTER JOIN users AS users1 ON LN_set_docs.created_by = users1.user_id
LEFT OUTER JOIN users AS users2 ON LN_set_docs.modified_by = users2.user_id
LEFT JOIN LN_familias ON LN_familias.set_doc=LN_set_docs.set_doc
GROUP BY LN_set_docs.set_doc, LN_set_docs.nombre_set,LN_set_docs.descripcion_set,
LN_set_docs.cliente , LN_set_docs.hab_contratista, LN_set_docs.hab_subcontratista,
LN_set_docs.created , LN_set_docs.created_by , LN_set_docs.modified , LN_set_docs.modified_by,
LN_set_docs.principal , LN_set_docs.servicio_contratado , LN_set_docs.grupo_set,
clientes.nombre_cliente, clientes.cli_codigo , users1.first_name , users2.first_name , users1.last_name , users2.last_name
ORDER BY LN_set_docs.nombre_set
end
else
select LN_set_docs.*, clientes.nombre_cliente, clientes.cli_codigo,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
where LN_set_docs.cliente = @p_cliente
order by LN_set_docs.nombre_set
END
GO
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
/*
exec ln_set_docs_ 'cliente',null,null,null,5546348,null,null
select * from ln_set_docs where cliente = 5546348
*/
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.LN_set_docs_
-- Add the parameters for the stored procedure here
@accion varchar(20),
@p_set_doc int = null,
@p_nombre_set varchar(50) = null,
@p_descripcion_set varchar(max) = null,
@p_cliente int = null,
@p_hab_contratista tinyint = 1,
@p_hab_subcontratista tinyint = 0,
@p_user_id int = null,
@plataforma varchar(max) = null,
@p_principal bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/* if (@plataforma is null)
begin
select @plataforma = (SELECT top 1 STUFF((SELECT ',' + convert(varchar(max),producto) FROM productos FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)'), 1, LEN(','), '') AS XmlPathList FROM productos GROUP BY productos.producto)
end
*/
if (@plataforma is null and @accion != 'INSERT')
begin
set @plataforma = '47,73'
/*set @plataforma='10'*/
end
-- Insert statements for procedure here
declare @idSet int = null
IF ( @accion = 'SEARCH_FAM_SET' )
BEGIN
SELECT LN_familias.familia,LN_familias.nombre_familia,clientes.cliente,clientes.nombre_cliente FROM LN_set_docs
LEFT JOIN LN_familias ON LN_familias.set_doc=LN_set_docs.set_doc
LEFT JOIN clientes ON clientes.cliente=LN_familias.cliente
WHERE LN_set_docs.set_doc=@p_set_doc
RETURN
END
IF ( @accion = 'SEARCH_SET_NAME' )
BEGIN
IF ( @p_nombre_set IS NOT NULL )
BEGIN
SELECT count(*) FROM LN_set_docs WHERE nombre_set = @p_nombre_set;
END
ELSE
BEGIN
SELECT 0;
END
END
if (@accion = 'INSERT') begin
if (@plataforma is null)
set @plataforma = '47'
insert into LN_set_docs
(nombre_set, descripcion_set, cliente, hab_contratista, hab_subcontratista, created_by,servicio_contratado)
values
(@p_nombre_set, @p_descripcion_set, @p_cliente, @p_hab_contratista, @p_hab_subcontratista, @p_user_id,@plataforma)
set @idSet = @@IDENTITY
select @idSet as idSet
end else
if (@accion = 'UPDATE') begin
update LN_set_docs set
nombre_set = @p_nombre_set,
descripcion_set = @p_descripcion_set,
cliente = @p_cliente,
hab_contratista = @p_hab_contratista,
hab_subcontratista = @p_hab_subcontratista,
principal = @p_principal,
modified = GETDATE(),
modified_by = @p_user_id
where set_doc = @p_set_doc
end else
if (@accion = 'SELECT') begin
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
where LN_set_docs.set_doc = @p_set_doc
end else
if (@accion = 'DELETE') begin
DELETE FROM LN_set_tipo_doc WHERE set_doc = @p_set_doc
DELETE FROM LN_set_docs WHERE set_doc = @p_set_doc
end else
if (@accion = 'CLIENTE')
begin
/*select * from clientes where cliente = @p_cliente*/
if (@p_hab_contratista is not null AND @p_hab_subcontratista is not null)
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
where (LN_set_docs.cliente is null OR LN_set_docs.cliente = @p_cliente)
and LN_set_docs.hab_contratista = @p_hab_contratista
and LN_set_docs.hab_subcontratista = @p_hab_subcontratista
order by LN_set_docs.nombre_set
else
if (@p_hab_contratista is not null AND @p_hab_subcontratista is null)
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
where (LN_set_docs.cliente is null OR LN_set_docs.cliente = @p_cliente)
and LN_set_docs.hab_contratista = @p_hab_contratista
order by LN_set_docs.nombre_set
else
if (@p_hab_contratista is null AND @p_hab_subcontratista is not null)
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
where (LN_set_docs.cliente is null OR LN_set_docs.cliente = @p_cliente)
and LN_set_docs.hab_subcontratista = @p_hab_subcontratista
order by LN_set_docs.nombre_set
else
select LN_set_docs.*, clientes.nombre_cliente,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
where (LN_set_docs.cliente is null OR LN_set_docs.cliente = @p_cliente)
order by LN_set_docs.nombre_set
end else
/* Search */
-- exec ln_set_docs_ 'search'
if (@p_cliente is null)
/* select LN_set_docs.*, clientes.nombre_cliente, clientes.cli_codigo,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
order by LN_set_docs.nombre_set
*/ begin
/* select distinct @plataforma, LN_set_docs.*, clientes.nombre_cliente, clientes.cli_codigo,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left join clientes_productos on clientes.cliente = clientes_productos.cliente
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = convert(int,exceptuados.Item)
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
order by LN_set_docs.nombre_set
*/
SELECT DISTINCT LN_set_docs.set_doc, LN_set_docs.nombre_set,LN_set_docs.descripcion_set,
LN_set_docs.cliente , LN_set_docs.hab_contratista, LN_set_docs.hab_subcontratista,
LN_set_docs.created , LN_set_docs.created_by , LN_set_docs.modified , LN_set_docs.modified_by,
LN_set_docs.principal , LN_set_docs.servicio_contratado , LN_set_docs.grupo_set,
clientes.nombre_cliente, clientes.cli_codigo,
users1.first_name + ' ' + users1.last_name AS created_by_user,
users2.first_name + ' ' + users2.last_name AS modified_by_user,
COUNT(LN_familias.familia) AS familias_set_asignado , COUNT(DISTINCT LN_familias.cliente) AS clientes_set_asignado
FROM LN_set_docs
LEFT OUTER JOIN clientes ON LN_set_docs.cliente = clientes.cliente
LEFT JOIN clientes_productos ON clientes.cliente = clientes_productos.cliente
INNER JOIN dbo.DelimitedSplit8K(@plataforma, ',') exceptuados ON LN_set_docs.servicio_contratado = CONVERT(int,exceptuados.Item)
LEFT OUTER JOIN users AS users1 ON LN_set_docs.created_by = users1.user_id
LEFT OUTER JOIN users AS users2 ON LN_set_docs.modified_by = users2.user_id
LEFT JOIN LN_familias ON LN_familias.set_doc=LN_set_docs.set_doc
GROUP BY LN_set_docs.set_doc, LN_set_docs.nombre_set,LN_set_docs.descripcion_set,
LN_set_docs.cliente , LN_set_docs.hab_contratista, LN_set_docs.hab_subcontratista,
LN_set_docs.created , LN_set_docs.created_by , LN_set_docs.modified , LN_set_docs.modified_by,
LN_set_docs.principal , LN_set_docs.servicio_contratado , LN_set_docs.grupo_set,
clientes.nombre_cliente, clientes.cli_codigo , users1.first_name , users2.first_name , users1.last_name , users2.last_name
ORDER BY LN_set_docs.nombre_set
end
else
select LN_set_docs.*, clientes.nombre_cliente, clientes.cli_codigo,
users1.first_name + ' ' + users1.last_name as created_by_user,
users2.first_name + ' ' + users2.last_name as modified_by_user
from LN_set_docs
left outer join clientes on LN_set_docs.cliente = clientes.cliente
left outer join users as users1 on LN_set_docs.created_by = users1.user_id
left outer join users as users2 on LN_set_docs.modified_by = users2.user_id
where LN_set_docs.cliente = @p_cliente
order by LN_set_docs.nombre_set
END
GO
Depends On
7Used By
No items found