Errores V18 reportes

cuando se genere el error, con la tabla lab242 se debe ejecutar el siguiente procedimiento 

Información de la excepción:
Tipo de excepción: System.Web.Services.Protocols.SoapException
Mensaje de la excepción: System.Web.Services.Protocols.SoapException: El servidor no puede procesar la solicitud. ---> Microsoft.Practices.Repository.RepositoryFailureException: Unexpected error occurred in the database server ---> System.Data.SqlClient.SqlException: El nombre de objeto 'Lab242' no es válido.
en System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
en System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
en System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsPars…

Ejecutar el procedimiento:

/********************************************************************************
ENTERPRISE - Todos los derechos reservados CLTech Ltda.
AUTOR: CPena
FECHA: 2021-12-16
********************************************************************************/

IF NOT EXISTS(SELECT 1 FROM Lab06 WHERE Lab06C1 = 2563)
INSERT INTO [dbo].[Lab06] ([Lab06C1],[Lab06C2],[Lab06C3],[Lab06C4],[Lab06C5],[Lab06C6],[Lab06C8])
VALUES (2563,'Tipo de Agrupación','Grouping Type','Tipo de Agrupamento','Type de regroupement','Gruppierungstyp',0)
GO

IF NOT EXISTS(SELECT 1 FROM Lab06 WHERE Lab06C1 = 2564)
INSERT INTO [dbo].[Lab06] ([Lab06C1],[Lab06C2],[Lab06C3],[Lab06C4],[Lab06C5],[Lab06C6],[Lab06C8])
VALUES (2564,'Validación Tecnólogo por Sede','Technologist Validation by Venue','Validação de tecnólogo por local','Validation du technologue par site','Technologenvalidierung nach Veranstaltungsort',0)
GO

IF NOT EXISTS(SELECT 1 FROM Lab06 WHERE Lab06C1 = 2565)
INSERT INTO [dbo].[Lab06] ([Lab06C1],[Lab06C2],[Lab06C3],[Lab06C4],[Lab06C5],[Lab06C6],[Lab06C8])
VALUES (2565,'Validación Medico por Sede','Physician Validation by Venue','Validação Médica por Local','Validation par le médecin par site','Ärztliche Validierung nach Veranstaltungsort',0)
GO

IF NOT EXISTS(SELECT 1 FROM Lab06 WHERE Lab06C1 = 2566)
INSERT INTO [dbo].[Lab06] ([Lab06C1],[Lab06C2],[Lab06C3],[Lab06C4],[Lab06C5],[Lab06C6],[Lab06C8])
VALUES (2566,'Validación Técnica','Technical validation','Validação técnica','Validation technique','Technische Validierung',0)
GO

IF NOT EXISTS(SELECT 1 FROM Lab06 WHERE Lab06C1 = 2567)
INSERT INTO [dbo].[Lab06] ([Lab06C1],[Lab06C2],[Lab06C3],[Lab06C4],[Lab06C5],[Lab06C6],[Lab06C8])
VALUES (2567,'Validación Medica','Medical validation','Validação médica','Validation médicale','Medizinische Validierung',0)
GO

IF NOT EXISTS (SELECT * FROM Lab85 WHERE Lab85C01 = 247)
INSERT INTO Lab85 VALUES(247, '~/Admin/Maestros/TipoAgrupacion/TipoAgrupacion.aspx',29,2563,NULL, NULL,1,3)
GO

/****************CAMPOS**************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lab24' AND COLUMN_NAME ='Lab242C1')
BEGIN
ALTER TABLE dbo.Lab24 ADD Lab242C1 INT NULL
END
GO



/****** Object: Table [dbo].[Lab242] ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lab242]') AND type in (N'U'))
DROP TABLE [dbo].[Lab242]
GO

/****** Object: Table [dbo].[La242] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Lab242](
[Lab242C1] [int] IDENTITY(1,1) NOT NULL,
[Lab242C2] [varchar](32) NOT NULL,
[Lab242C3] [varchar](400) NULL,
[Lab242C4] [datetime] NOT NULL,
[Lab07C1] [int] NOT NULL,
CONSTRAINT [PK_Lab242] PRIMARY KEY CLUSTERED
(
[Lab242C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Lab242] ADD DEFAULT (getdate()) FOR [Lab242C4]
GO

ALTER TABLE [dbo].[Lab242] ADD DEFAULT ((1)) FOR [Lab07C1]
GO


/****** Object: Table [dbo].[Lab243] Script Date: 17/12/2021 11:21:05 a. m. ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lab243]') AND type in (N'U'))
DROP TABLE [dbo].[Lab243]
GO

/****** Object: Table [dbo].[Lab243] Script Date: 17/12/2021 11:21:05 a. m. ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Lab243](
[Lab63C1] [smallint] NOT NULL,
[Lab04C1] [int] NOT NULL,
[Lab243C2] [datetime] NOT NULL,
[Lab07C1] [int] NOT NULL,
CONSTRAINT [PK_Lab243] PRIMARY KEY CLUSTERED
(
[Lab63C1] ASC,
[Lab04C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


/****** Object: Table [dbo].[Lab244] Script Date: 17/12/2021 11:21:05 a. m. ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lab244]') AND type in (N'U'))
DROP TABLE [dbo].[Lab244]
GO

/****** Object: Table [dbo].[Lab243] Script Date: 17/12/2021 11:21:05 a. m. ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Lab244](
[Lab63C1] [smallint] NOT NULL,
[Lab04C1] [int] NOT NULL,
[Lab244C2] [datetime] NOT NULL,
[Lab07C1] [int] NOT NULL,
CONSTRAINT [PK_Lab244] PRIMARY KEY CLUSTERED
(
[Lab63C1] ASC,
[Lab04C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


------------------------------PROCEDURE----------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00251]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00251]
GO

CREATE PROCEDURE [dbo].[Lab00251]

WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SELECT
Lab242C1,
Lab242C2,
Lab242C3,
Lab242C4,
Lab07C1
FROM Lab242
END
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00252]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00252]
GO

CREATE PROCEDURE [dbo].[Lab00252] @Lab242C2 VARCHAR(32)
, @Lab242C3 VARCHAR(400)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Lab242(Lab242C2,Lab242C3)VALUES(@Lab242C2,@Lab242C3)
END
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00253]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00253]
GO

CREATE PROCEDURE [dbo].[Lab00253] @Lab242C1 int,
@Lab242C2 VarChar(32),
@Lab242C3 VarChar(400),
@Lab07C1 TinyInt

WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON

UPDATE Lab242
SET Lab242C2 = @Lab242C2, Lab242C3 = @Lab242C3, Lab07C1 = @Lab07C1
WHERE Lab242C1 = @Lab242C1
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00254]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00254]
GO

CREATE PROCEDURE dbo.[Lab00254]
@Lab242C1 int

WITH ENCRYPTION
AS
SET NOCOUNT ON

UPDATE Lab242
SET Lab07C1 =2,
Lab242C4 = getdate()

WHERE
Lab242C1 = @Lab242C1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab24_Set]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab24_Set]
GO


CREATE PROCEDURE [dbo].[Lab24_Set]
@Lab24C2 VarChar(64),
@Lab24C3 int,
@Lab24C4 int,
@Lab24C5 int,
@Lab24C6 VarChar(1000),
@Lab24C7 int,
@Lab56C1 int,
@Lab24C9 int,
@Lab24C10 tinyint,
@Lab24C11 Smallint,
@Lab24C12 int,
@Lab242C1 int
WITH ENCRYPTION
AS
INSERT INTO Lab24
(
Lab24C2,
Lab24C3,
Lab24C4,
Lab24C5,
Lab24C6,
Lab24C7,
Lab56C1,
Lab24C9,
Lab24C10,
Lab24C11,
Lab24C12,
Lab242C1
)
VALUES
(
@Lab24C2,
@Lab24C3,
@Lab24C4,
@Lab24C5,
@Lab24C6,
@Lab24C7,
@Lab56C1,
@Lab24C9,
@Lab24C10,
@Lab24C11,
@Lab24C12,
@Lab242C1
)


GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab24_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab24_Get]
GO


CREATE PROCEDURE dbo.[Lab24_Get]

WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SELECT
Lab24C1,
Lab24C2,
Lab24C3,
convert(bit,Lab24C4) Lab24C4,
convert(bit,Lab24C5) Lab24C5,
Lab24C6,
left(Lab24C6,50),
Lab24C7,
Lab24C8,
Lab07C1,
Lab56C1,
Lab24C9,
convert(bit,Lab24C10) Lab24C10,
Lab24C11,
convert(bit,Lab24C12) Lab24C12,
Lab242C1
FROM Lab24
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab24_Upd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab24_Upd]
GO

CREATE PROCEDURE [dbo].[Lab24_Upd]
@Lab24C1 int,
@Lab24C2 VarChar(64),
@Lab24C3 int,
@Lab24C4 int,
@Lab24C5 int,
@Lab24C6 VarChar(1000),
@Lab24C7 int,
@Lab07C1 int,
@Lab56C1 int,
@Lab24C9 int,
@Lab24C10 tinyint,
@Lab24C11 Smallint,
@Lab24C12 int,
@Lab242C1 int
WITH ENCRYPTION
AS

UPDATE Lab24
SET
Lab24C2 = @Lab24C2,
Lab24C3 = @Lab24C3,
Lab24C4 = @Lab24C4,
Lab24C5 = @Lab24C5,
Lab24C6 = @Lab24C6,
Lab24C7 = @Lab24C7,
Lab24C8 = getdate(),
Lab07C1 = @Lab07C1,
Lab56C1 = @Lab56C1,
Lab24C9 = @Lab24C9,
Lab24C10 = @Lab24C10,
Lab24C11 = @Lab24C11,
Lab24C12 = @Lab24C12,
Lab242C1 = @Lab242C1
WHERE
Lab24C1 = @Lab24C1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00255]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00255]
GO


CREATE PROCEDURE [dbo].[Lab00255]
@Lab63C1 SMALLINT,
@Lab82c01 INT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT U.Lab04C1,
ISNULL(U.Lab04C2, '') + ' ' + ISNULL(Lab04C3, '')
FROM Lab04 U WITH(NOLOCK)
INNER JOIN Lab84 UR WITH(NOLOCK) ON UR.Lab04C1 = U.Lab04c1
WHERE U.Lab04C21 = 1
AND UR.Lab82C01 = @Lab82c01
AND U.Lab04C1 NOT IN (SELECT Lab04C1 FROM Lab243 WHERE Lab243.Lab63C1 = @Lab63C1 AND Lab07C1 = 1)
OPTION (OPTIMIZE FOR UNKNOWN);

SET NOCOUNT OFF
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00256]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00256]
GO


CREATE PROCEDURE [dbo].[Lab00256]
@Lab63C1 SMALLINT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT U.Lab04C1,
ISNULL(U.Lab04C2, '') + ' ' + ISNULL(Lab04C3, '')
FROM Lab04 U WITH(NOLOCK)
INNER JOIN Lab243 US WITH(NOLOCK) ON US.Lab04C1 = U.Lab04c1
WHERE U.Lab04C21 = 1
AND US.Lab07C1 = 1
AND US.Lab63C1 = @Lab63C1
OPTION (OPTIMIZE FOR UNKNOWN);


SET NOCOUNT OFF
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00257]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00257]
GO

CREATE PROCEDURE [dbo].[Lab00257]
@Lab63C1 SMALLINT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE Lab243
SET Lab07C1 = 2
WHERE Lab63C1 = @Lab63C1

SET NOCOUNT OFF
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00258]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00258]
GO

CREATE PROCEDURE [dbo].[Lab00258]
@Lab63C1 SMALLINT,
@Lab04C1 INT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF EXISTS (SELECT 1 FROM Lab243 WHERE Lab63C1 = @Lab63C1 AND Lab04C1 = @Lab04C1)
BEGIN
UPDATE Lab243
SET Lab07C1 = 1
WHERE Lab63C1 = @Lab63C1
AND Lab04C1 = @Lab04C1
END
ELSE
BEGIN
INSERT INTO Lab243
(
Lab63C1,
Lab04C1,
Lab243C2,
Lab07C1
)
VALUES
(
@Lab63C1,
@Lab04C1,
GETDATE(),
1
)
END

SET NOCOUNT OFF
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00259]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00259]
GO


CREATE PROCEDURE [dbo].[Lab00259]
@Lab63C1 SMALLINT,
@Lab82c01 INT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT U.Lab04C1,
ISNULL(U.Lab04C2, '') + ' ' + ISNULL(Lab04C3, '')
FROM Lab04 U WITH(NOLOCK)
INNER JOIN Lab84 UR WITH(NOLOCK) ON UR.Lab04C1 = U.Lab04c1
WHERE U.Lab04C21 = 1
AND UR.Lab82C01 = @Lab82c01
AND U.Lab04C1 NOT IN (SELECT Lab04C1 FROM Lab244 WHERE Lab244.Lab63C1 = @Lab63C1 AND Lab07C1 = 1)
OPTION (OPTIMIZE FOR UNKNOWN);

SET NOCOUNT OFF
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00260]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00260]
GO


CREATE PROCEDURE [dbo].[Lab00260]
@Lab63C1 SMALLINT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT U.Lab04C1,
ISNULL(U.Lab04C2, '') + ' ' + ISNULL(Lab04C3, '')
FROM Lab04 U WITH(NOLOCK)
INNER JOIN Lab244 US WITH(NOLOCK) ON US.Lab04C1 = U.Lab04c1
WHERE U.Lab04C21 = 1
AND US.Lab07C1 = 1
AND US.Lab63C1 = @Lab63C1
OPTION (OPTIMIZE FOR UNKNOWN);


SET NOCOUNT OFF
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00261]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00261]
GO

CREATE PROCEDURE [dbo].[Lab00261]
@Lab63C1 SMALLINT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE Lab244
SET Lab07C1 = 2
WHERE Lab63C1 = @Lab63C1

SET NOCOUNT OFF
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Lab00262]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Lab00262]
GO

CREATE PROCEDURE [dbo].[Lab00262]
@Lab63C1 SMALLINT,
@Lab04C1 INT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF EXISTS (SELECT 1 FROM Lab244 WHERE Lab63C1 = @Lab63C1 AND Lab04C1 = @Lab04C1)
BEGIN
UPDATE Lab244
SET Lab07C1 = 1
WHERE Lab63C1 = @Lab63C1
AND Lab04C1 = @Lab04C1
END
ELSE
BEGIN
INSERT INTO Lab244
(
Lab63C1,
Lab04C1,
Lab244C2,
Lab07C1
)
VALUES
(
@Lab63C1,
@Lab04C1,
GETDATE(),
1
)
END

SET NOCOUNT OFF
END
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[LabRptInfHdrGetByLab22C1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [LabRptInfHdrGetByLab22C1]
GO

CREATE PROCEDURE dbo.LabRptInfHdrGetByLab22C1 @Lab22C1 BIGINT
, @Lab105C13 SMALLINT
, @Lab22C8 DATETIME
, @Lab98CC VARCHAR(4)
, @Lab63C1 INT
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @Lab21C1 INT
DECLARE @Lab21C2 VARCHAR(32)
DECLARE @Lab21C4 VARCHAR(64)
DECLARE @Lab21C5 VARCHAR(64)
DECLARE @Lab21C10A VARCHAR(9)
DECLARE @Lab21C10D DATETIME
DECLARE @Lab22C8C DATETIME
DECLARE @Lab22C14 DATETIME
DECLARE @Lab22C12 CHAR(1)
DECLARE @Lab102C2 INT
DECLARE @Lab57C18 DATETIME
DECLARE @Lab57C3 DATETIME
DECLARE @Lab60C1 VARCHAR(MAX)
DECLARE @TechnicalValidation VARCHAR(MAX)
DECLARE @MedicallValidation VARCHAR(MAX)

CREATE TABLE #Lab04C12T (Lab04C12 IMAGE)

DECLARE @Lab63D4 VARCHAR(64)

IF EXISTS(SELECT 1 FROM Lab22 where LAb22C1 = @Lab22C1)
BEGIN
SELECT @Lab21C1 = Lab21.Lab21C1
, @Lab21C2 = TI.Lab232C2 + '-' + dbo.LabFn01(Lab21C2,@Lab98CC)
, @Lab21C5 = dbo.LabFn27(Lab21.Lab21C5, @Lab98CC,Lab21.Lab21C6)
, @Lab21C4 = dbo.LabFn01(Lab21.Lab21C4,@Lab98CC)
, @Lab21C10A = dbo.LabFn05(Lab21.Lab21C10, Lab22C14)
, @Lab21C10D = Lab21.Lab21C10
, @Lab22C8C = Lab22.Lab22C8
, @Lab22C14 = Lab22.Lab22C14
, @Lab63D4 = dbo.Labfn02(9, Lab21C6)
, @Lab22C12 = Lab22C12
, @Lab60C1 = Lab60C1
FROM Lab21
INNER JOIN Lab22 ON (Lab22.Lab21C1 = Lab21.Lab21C1)
INNER JOIN Lab232 TI ON TI.Lab232C1 = Lab21.Lab21C14
LEFT JOIN Lab60 WITH(NOLOCK) ON(Lab60.Lab22C1 = Lab22.Lab22C1)
WHERE Lab22.Lab22C1 = @Lab22C1

INSERT INTO #Lab04C12T
SELECT Lab04C12
FROM Lab04 US
INNER JOIN Lab220 URS WITH(NOLOCK) ON US.Lab04C1 = URS.Lab04C1
WHERE URS.Lab63C1 = (SELECT Lab63C1 FROM Lab22 WITH(NOLOCK) WHERE Lab22.Lab22C1 = @Lab22C1)

SELECT @Lab57C18 = MIN(Lab57C18)
,@Lab57C3 = MIN(Lab57C3)
FROM Lab57 WITH(NOLOCK)
WHERE Lab22C1 = @Lab22C1
OPTION (OPTIMIZE FOR UNKNOWN)

SELECT @TechnicalValidation = COALESCE(@TechnicalValidation + ', ', '') + ISNULL(Lab04C2, '') + ' ' + ISNULL(Lab04C3, '')
FROM Lab04 WITH(NOLOCK)
INNER JOIN Lab243 WITH(NOLOCK) ON Lab243.Lab04C1 = Lab04.Lab04C1
WHERE Lab243.Lab07C1 = 1
AND Lab243.Lab63C1 IN((SElECT Lab63C1 FROM Lab22 WHERE Lab22C1 = @Lab22C1))
OPTION (OPTIMIZE FOR UNKNOWN)

SELECT @MedicallValidation = COALESCE(@MedicallValidation + ', ', '') + ISNULL(Lab04C2, '') + ' ' + ISNULL(Lab04C3, '')
FROM Lab04 WITH(NOLOCK)
INNER JOIN Lab244 WITH(NOLOCK) ON Lab244.Lab04C1 = Lab04.Lab04C1
WHERE Lab244.Lab07C1 = 1
AND Lab244.Lab63C1 IN((SElECT Lab63C1 FROM Lab22 WHERE Lab22C1 = @Lab22C1))
OPTION (OPTIMIZE FOR UNKNOWN)


END
ELSE
BEGIN
SELECT @Lab21C1 = Lab21.Lab21C1
, @Lab21C2 = TI.Lab232C2 + '-' + dbo.LabFn01(Lab21C2,@Lab98CC)
, @Lab21C5 = dbo.LabFn27(Lab21.Lab21C5, @Lab98CC,Lab21.Lab21C6)
, @Lab21C4 = dbo.LabFn01(Lab21.Lab21C4,@Lab98CC)
, @Lab21C10A = dbo.LabFn05(Lab21.Lab21C10, Lab22C14)
, @Lab21C10D = Lab21.Lab21C10
, @Lab22C8C = Lab22.Lab22C8
, @Lab22C14 = Lab22.Lab22C14
, @Lab63D4 = dbo.Labfn02(9, Lab21C6)
, @Lab22C12 = Lab22C12
, @Lab60C1 = Lab60C1
FROM Lab21
INNER JOIN EnterpriseH.dbo.Lab22 ON (Lab22.Lab21C1 = Lab21.Lab21C1)
INNER JOIN Lab232 TI ON TI.Lab232C1 = Lab21.Lab21C14
LEFT JOIN EnterpriseH.dbo.Lab60 WITH(NOLOCK) ON(Lab60.Lab22C1 = Lab22.Lab22C1)
WHERE Lab22.Lab22C1 = @Lab22C1

INSERT INTO #Lab04C12T
SELECT Lab04C12
FROM Lab04 US
INNER JOIN Lab220 URS WITH(NOLOCK) ON US.Lab04C1 = URS.Lab04C1
WHERE URS.Lab63C1 = (SELECT Lab63C1 FROM EnterpriseH.dbo.Lab22 WITH(NOLOCK) WHERE Lab22.Lab22C1 = @Lab22C1)

SELECT @Lab57C18 = MIN(Lab57C18)
,@Lab57C3 = MIN(Lab57C3)
FROM EnterpriseH.dbo.Lab57 WITH(NOLOCK)
WHERE Lab22C1 = @Lab22C1
OPTION (OPTIMIZE FOR UNKNOWN)

SELECT @TechnicalValidation = COALESCE(@TechnicalValidation + ', ', '') + ISNULL(Lab04C2, '') + ' ' + ISNULL(Lab04C3, '')
FROM Lab04 WITH(NOLOCK)
INNER JOIN Lab243 WITH(NOLOCK) ON Lab243.Lab04C1 = Lab04.Lab04C1
WHERE Lab243.Lab07C1 = 1
AND Lab243.Lab63C1 IN((SElECT Lab63C1 FROM EnterpriseH.dbo.Lab22 WHERE Lab22C1 = @Lab22C1))
OPTION (OPTIMIZE FOR UNKNOWN)

SELECT @MedicallValidation = COALESCE(@MedicallValidation + ', ', '') + ISNULL(Lab04C2, '') + ' ' + ISNULL(Lab04C3, '')
FROM Lab04 WITH(NOLOCK)
INNER JOIN Lab244 WITH(NOLOCK) ON Lab244.Lab04C1 = Lab04.Lab04C1
WHERE Lab244.Lab07C1 = 1
AND Lab244.Lab63C1 IN((SElECT Lab63C1 FROM EnterpriseH.dbo.Lab22 WHERE Lab22C1 = @Lab22C1))
OPTION (OPTIMIZE FOR UNKNOWN)

END

IF (@Lab105C13 = 1)
SET @Lab22C8C = @Lab22C8

SELECT 1003 Lab06C1, CAST(@Lab22C1 AS VARCHAR) Lab, 'Prescription' ParamName
UNION ALL
SELECT 1025, @Lab21C4, 'Name'
UNION ALL
SELECT 1025, @Lab21C5, 'LastName'
UNION ALL
SELECT 602, @Lab21C2, 'ClinicalHistory'
UNION ALL
SELECT 1211, @Lab63D4, 'Gender'
UNION ALL
SELECT 386, @Lab21C10A, 'Age'
UNION ALL
SELECT 541, CONVERT(VARCHAR, @Lab21C10D, 21), 'BirthDate'
UNION ALL
SELECT 539, CONVERT(VARCHAR, @Lab22C14, 21), 'EntryDate'
UNION ALL
SELECT 1581, CONVERT(VARCHAR, @Lab22C8C, 21), 'PrintDate'
UNION ALL
SELECT 1281, @Lab22C12, 'PrescriptionType'
UNION ALL
SELECT 567, CONVERT(VARCHAR,@Lab57C18, 21), 'SampleDate'
UNION ALL
SELECT 1580, CONVERT(VARCHAR,@Lab57C3, 21), 'ResultDate'
UNION ALL
SELECT 107, @Lab60C1, 'Comment'
UNION ALL
SELECT 2566, @TechnicalValidation, 'TechnicalValidation'
UNION ALL
SELECT 2567, @MedicallValidation, 'MedicalValidation'



-- FATLA CONTROLAR LA SEDE, O DEMOGRAFICO... LAB63C1 EN 102
SET @Lab102C2 = 0
IF EXISTS (SELECT 1 FROM Lab102 WHERE Lab102C2 = @Lab63C1 AND Lab102C1 = 3)
BEGIN
SET @Lab102C2 = @Lab63C1
END

IF EXISTS(SELECT 1 FROM Lab22 where LAb22C1 = @Lab22C1)
BEGIN
SELECT Lab62.Lab62C2, ISNULL(Lab63C4, Lab20C1) Lab63C4, REPLACE(Lab62.Lab62C2, ' ', '') Label
FROM Lab102
INNER JOIN Lab62 ON (Lab62.Lab62C1 = Lab102.Lab62C1)
LEFT OUTER JOIN (Lab20 LEFT OUTER JOIN Lab63 ON (Lab63.Lab63C1 = Lab20.Lab63C1)) ON (Lab20.Lab62C1 = Lab62.Lab62C1 AND Lab22C1 = @Lab22C1)
WHERE Lab102C2 = @Lab102C2
AND Lab102C1 = 3
AND Lab62C3 = 'O'
UNION ALL
SELECT Lab62.Lab62C2, ISNULL(Lab63C4, Lab19C2), REPLACE(Lab62.Lab62C2, ' ', '')
FROM Lab102
INNER JOIN Lab62 ON (Lab62.Lab62C1 = Lab102.Lab62C1)
LEFT OUTER JOIN (Lab19 LEFT OUTER JOIN Lab63 ON (Lab63.Lab63C1 = Lab19.Lab63C1)) ON (Lab19.Lab62C1 = Lab62.Lab62C1 AND Lab21C1 = @Lab21C1)
WHERE Lab102C2 = @Lab102C2
AND Lab102C1 = 3
AND Lab62C3 = 'H'
END
ELSE
BEGIN
SELECT Lab62.Lab62C2, ISNULL(Lab63C4, Lab20C1) Lab63C4, REPLACE(Lab62.Lab62C2, ' ', '') Label
FROM Lab102
INNER JOIN Lab62 ON (Lab62.Lab62C1 = Lab102.Lab62C1)
LEFT OUTER JOIN (EnterpriseH.dbo.Lab20 LEFT OUTER JOIN Lab63 ON (Lab63.Lab63C1 = Lab20.Lab63C1)) ON (Lab20.Lab62C1 = Lab62.Lab62C1 AND Lab22C1 = @Lab22C1)
WHERE Lab102C2 = @Lab102C2
AND Lab102C1 = 3
AND Lab62C3 = 'O'
UNION ALL
SELECT Lab62.Lab62C2, ISNULL(Lab63C4, Lab19C2), REPLACE(Lab62.Lab62C2, ' ', '')
FROM Lab102
INNER JOIN Lab62 ON (Lab62.Lab62C1 = Lab102.Lab62C1)
LEFT OUTER JOIN (Lab19 LEFT OUTER JOIN Lab63 ON (Lab63.Lab63C1 = Lab19.Lab63C1)) ON (Lab19.Lab62C1 = Lab62.Lab62C1 AND Lab21C1 = @Lab21C1)
WHERE Lab102C2 = @Lab102C2
AND Lab102C1 = 3
AND Lab62C3 = 'H'
END

EXEC LabRptInfHdrGet

SELECT 0 Lab06C1, Lab28C1 Lab, 'Photo' ParamName
FROM Lab28
WHERE Lab21C1 = @Lab21C1

SELECT 2463 as Lab06C1, Lab04C12, 'FirmResponsible' ParamName FROM #Lab04C12T

DROP TABLE #Lab04C12T
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[LabRptInfGetByLab22C1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [LabRptInfGetByLab22C1]
GO

CREATE PROCEDURE dbo.LabRptInfGetByLab22C1 @Lab22C1 BIGINT
, @Lab105C8 SMALLINT
, @Lab105C9 VARCHAR(2000)
, @Lab105C13 SMALLINT
, @Lab98 VARCHAR(64)
, @Lab98CC VARCHAR(4)
, @Lab98RR VARCHAR(64)
, @Lab98PC BIT
, @Lab39C11 BIT
, @Lab43C1 INT = 0
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @Lab39C11T INT
DECLARE @LabTable TABLE(Lab22C1 BIGINT)
DECLARE @recallOrder VARCHAR(100), @recallId BIGINT
DECLARE @AnalitosConcurrentesxPerfil INT
DECLARE @Historico int

DECLARE @Lab98C2TO AS VARCHAR(64);
DECLARE @Lab98C2TS AS VARCHAR(64);
DECLARE @Lab06C2 AS VARCHAR(200);
DECLARE @IniUserVali AS VARCHAR(100);

SET @Lab98C2TO = (SELECT Lab98C2 FROM Lab98 WHERE Lab98C1 = 'LaboratorioPorTipoOrden')

SET @Lab98C2TS = (SELECT Lab98C2 FROM Lab98 WHERE Lab98C1 = 'TrabajoPorSede')

SET @Lab06C2 = (SELECT Lab06C2 FROM Lab06 WHERE Lab06C1 = 816)

SELECT (@Lab06C2 + ': ' + CHAR(10) + Lab30C2 + ': ' + Lab144C2) Lab144C2T, Lab24C1, Lab144C1 INTO #LabTable144 FROM Lab144 INNER JOIN Lab30 ON Lab30.Lab30C1 = Lab144.Lab30C1 WHERE Lab22C1 = @Lab22C1

set @Historico=0

IF @Lab105C8 = 3
SET @Lab39C11T = 1
ELSE
SET @Lab39C11T = 0

DECLARE @Instr NVARCHAR(max)
DECLARE @Instrper NVARCHAR(max)
DECLARE @Instrtem NVARCHAR(max)
SELECT @AnalitosConcurrentesxPerfil=CASE WHEN LAB98C2='TRUE' THEN 1 ELSE 0 END FROM LAB98 WHERE Lab98C1='AnalitosConcurrentesxPerfiles'
create table #tmp_table (
TestId int ,
TestName varchar(64),
Pathologic int ,
PathologicText varchar(50) ,
Result varchar(16),
ResultDate datetime,
Restricted varchar(50),
Unit varchar(50),
RefMin varchar(50),
RefMax varchar(50),
LiteralResult varchar(50) ,
ResultType SMALLINT ,
RefText varchar(530) ,
Comment varchar(7000) ,
SectionId int ,
Section varchar(128) ,
TestParent int,
ProfileName varchar(64) ,
ProfileId int,
PrintComment varchar(4000) ,
Method varchar(256) ,
[Image] image ,
ValidateUser INT ,
ValidateDate datetime ,
UserName varchar(50) ,
UserImage image,
ProfileTitle varchar(50),
Title varchar(50),
Antibiogram TINYINT,
Graphic int ,
MicSample VARCHAR(64),
History int,
[Order] bigint,
AnatomicSite VARCHAR(100),
DecimalDigits int,
SampleDate datetime,
Confidential int,
[State] int,
CollectProcedure varchar(100),
ItIsAttached smallint,
AbbreviationMethod varchar(10),
Lab43C5 int,
Lab39C48p int ,Lab39C48e int, lab39c2 varchar(16), Laboratory varchar(64),datetakesample datetime,usertakesample varchar(50), PackageName varchar(64), PackageId int, IniUserValidate VARCHAR(10), SectionUserImage image, TipoMuestra VARCHAR(100), Observations varchar(4000), GroupingType varchar(4000), ExternalResult varchar(200), MethodNotes varchar(300))




IF NOT EXISTS(Select 1 from Lab22 where Lab22C1 = @Lab22C1)
begin
set @Historico=1
end
IF(@AnalitosConcurrentesxPerfil=1)
BEGIN
if(@Historico=0)
BEGIN
set @Instrtem =' declare @tmpc table (perfil int,analito int,existe int,nombre varchar(64),Lab39C48 int, Lab46C4P INT) declare @tmp table (perfil int, Lab46C4P INT)
insert into @tmp select a.lab39c1, a.Lab46C4P from lab57 a inner join lab39 b on a.lab39c1=b.lab39c1 where lab22c1= @Lab22C1 and b.lab39c7 in (1)
insert into @tmpc select lab46c4,u.lab39c1,0,t.lab39c3,t.Lab39C48 , tt.Lab46C4P from lab46 u inner join lab39 t on u.Lab46C4=t.lab39c1 inner join @tmp tt on tt.perfil = u.lab46c4 where u.lab46c4 in (select perfil from @tmp) and u.lab07c1=1
update @tmpc set existe=1 from Lab57 a inner join @tmpc b on a.lab39c1=b.analito and Lab46C4 =b.perfil where lab22c1= @Lab22C1 '
END
ELSE
BEGIN
set @Instrtem =' declare @tmpc table (perfil int,analito int,existe int,nombre varchar(64),Lab39C48 int, Lab46C4P INT) declare @tmp table (perfil int, Lab46C4P INT)
insert into @tmp select a.lab39c1, a.Lab46C4P from EnterpriseH.dbo.Lab57 a inner join lab39 b on a.lab39c1=b.lab39c1 where lab22c1= @Lab22C1 and b.lab39c7 in (1)
insert into @tmpc select lab46c4,u.lab39c1,0,t.lab39c3,t.Lab39C48 , tt.Lab46C4P from lab46 u inner join lab39 t on u.Lab46C4=t.lab39c1 inner join @tmp tt on tt.perfil = u.lab46c4 where u.lab46c4 in (select perfil from @tmp) and u.lab07c1=1
update @tmpc set existe=1 from EnterpriseH.dbo.Lab57 a inner join @tmpc b on a.lab39c1=b.analito and Lab46C4 =b.perfil where lab22c1= @Lab22C1 '

END
END




SET @Instr = 'SELECT Lab39.Lab39C1 TestId, Lab39.Lab39C3 TestName, Lab57C10 Pathologic,' + CASE @Lab105C13
WHEN 4 THEN 'CASE WHEN Lab57C8 > 3 THEN dbo.LabFn02(111, Lab57C10) ELSE '''' END AS PathologicText, '
WHEN 2 THEN 'CASE WHEN Lab57C8 > 5 THEN dbo.LabFn02(111, Lab57C10) ELSE '''' END AS PathologicText, '
WHEN 6 THEN 'CASE WHEN Lab57C8 > 5 THEN dbo.LabFn02(111, Lab57C10) ELSE '''' END AS PathologicText, '
ELSE 'dbo.Labfn02(111, Lab57C10) AS PathologicText, ' END + CASE @Lab105C13
WHEN 4 THEN 'CASE WHEN Lab57C8 > 3 THEN CASE WHEN Lab57C38 = 1 AND Lab57C8 > 5 THEN @Lab98RR ELSE dbo.LabFn01(Lab57C2,Lab57.Lab39C1) END ELSE @Lab98 END AS Result'
WHEN 2 THEN 'CASE WHEN Lab57C8 > 5 THEN CASE WHEN Lab57C38 = 1 THEN @Lab98RR ELSE dbo.LabFn01(Lab57C2,Lab57.Lab39C1) END ELSE @Lab98 END AS Result'
WHEN 6 THEN 'CASE WHEN Lab57C8 > 5 THEN CASE WHEN Lab57C38 = 1 THEN @Lab98RR ELSE dbo.LabFn01(Lab57C2,Lab57.Lab39C1) END ELSE @Lab98 END AS Result'
ELSE 'CASE WHEN Lab57C38 = 1 THEN @Lab98RR ELSE dbo.LabFn01(Lab57C2,Lab57.Lab39C1) END AS Result' END +', Lab57C3 AS ResultDate, Lab57C38 Restricted, Lab45C2 Unit, dbo.LabFn44(LTRIM(STR(Lab48C4, 55, Lab39.Lab39C19)),Lab39.Lab39C19) RefMin, dbo.LabFn44(LTRIM(STR(Lab48C5, 55, Lab39.Lab39C19)), Lab39.Lab39C19) RefMax, Lab50C2 LiteralResult, ISNULL(Lab48.Lab39C18,Lab39.Lab39C18) ResultType, Lab48C11 RefText, '


SET @Instr = @Instr + CASE @Lab105C13 WHEN 4 THEN 'CASE WHEN Lab57C8 > 3 AND Lab57C8 <> 6 THEN (CASE WHEN Lab57C38 = 1 AND Lab57C8 > 6 THEN NULL ELSE Lab95C1 END) ELSE (CASE WHEN Lab57C8 = 6 THEN (SELECT TOP 1 Lab144C2T FROM #LabTable144 W WHERE W.Lab24C1 = Lab39.Lab24C1) ELSE NULL END) END AS Comment'
WHEN 2 THEN 'CASE WHEN Lab57C8 > 6 THEN (CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab95C1 END) ELSE (CASE WHEN Lab57C8 = 6 THEN (SELECT TOP 1 Lab144C2T FROM #LabTable144 Z WHERE Z.Lab24C1 = Lab39.Lab24C1) ELSE NULL END) END AS Comment'
WHEN 6 THEN 'CASE WHEN Lab57C8 > 6 THEN (CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab95C1 END) ELSE (CASE WHEN Lab57C8 = 6 THEN (SELECT TOP 1 Lab144C2T FROM #LabTable144 Y WHERE Y.Lab24C1 = Lab39.Lab24C1) ELSE NULL END) END AS Comment'
ELSE 'CASE WHEN Lab57C38 = 1 THEN NULL ELSE (CASE WHEN Lab57C8 = 6 THEN (SELECT TOP 1 Lab144C2T FROM #LabTable144 X WHERE X.Lab24C1 = Lab39.Lab24C1) ELSE Lab95C1 END) END AS Comment' END + ', Lab39.Lab43C1 SectionId, Lab43C2 Section, CASE WHEN P.Lab39C17 = 0 AND Lab57.Lab46C4 > 0 THEN 0 ELSE ISNULL(Lab57.Lab46C4,0) END TestParent, ISNULL(P.Lab39C3, Lab39.Lab39C3) ProfileName, ISNULL(P.Lab39C1, Lab39.Lab39C1) ProfileId, '

IF @Lab98PC = 1
BEGIN
SET @Instr = @Instr + CASE @Lab105C13 WHEN 4 THEN 'CASE WHEN Lab57C8 > 3 THEN CASE WHEN Lab57C38 = 1 AND Lab57C8 > 6 THEN NULL ELSE Lab95C5 END ELSE NULL END AS PrintComment'
WHEN 2 THEN 'CASE WHEN Lab57C8 > 6 THEN CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab95C5 END ELSE NULL END AS PrintComment'
WHEN 6 THEN 'CASE WHEN Lab57C8 > 6 THEN CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab95C5 END ELSE NULL END AS PrintComment'
ELSE 'CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab95C5 END AS PrintComment' END
END
ELSE
SET @Instr = @Instr + 'NULL PrintComment'


SET @Instr = @Instr + CASE @Lab105C13 WHEN 4 THEN ', CASE WHEN Lab57C8 > 3 THEN CASE WHEN Lab57C38 = 1 AND Lab57C8 > 5 THEN NULL ELSE Lab64C3 END ELSE NULL END AS Method'
WHEN 2 THEN ', CASE WHEN Lab57C8 > 5 THEN CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab64C3 END ELSE NULL END AS Method'
WHEN 6 THEN ', CASE WHEN Lab57C8 > 5 THEN CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab64C3 END ELSE NULL END AS Method'
ELSE ', CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab64C3 END AS Method' END

SET @Instr = @Instr + CASE @Lab105C13 WHEN 4 THEN ', CASE WHEN Lab57C8 > 3 THEN CASE WHEN Lab57C38 = 1 AND Lab57C8 > 6 THEN NULL ELSE Lab95C2 END ELSE NULL END AS [Image]'
WHEN 2 THEN ', CASE WHEN Lab57C8 > 6 THEN CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab95C2 END ELSE NULL END AS [Image]'
WHEN 6 THEN ', CASE WHEN Lab57C8 > 6 THEN CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab95C2 END ELSE NULL END AS [Image]'
ELSE ', CASE WHEN Lab57C38 = 1 THEN NULL ELSE Lab95C2 END AS [Image]' END + ', Lab57C28 ValidateUser , CASE WHEN Lab57C8 = 6 THEN (SELECT TOP 1 Lab144C1 FROM #LabTable144 V WHERE V.Lab24C1 = Lab39.Lab24C1) ELSE Lab57C9 END ValidateDate, LTRIM(Rtrim(Lab04C2)) + '' '' + LTRIM(Rtrim(Lab04C3)) AS UserName, Lab04C12 UserImage, P.Lab39C4 ProfileTitle, Lab39.Lab39C4 Title, Lab57C23 Antibiogram, ISNULL(Lab147.Lab39C1,0) Graphic, Lab24.Lab24C2 MicSample'

SET @Instr = @Instr + CASE @Lab105C13 WHEN 4 THEN ', CASE WHEN Lab57C8 > 3 THEN CASE WHEN Lab57C38 = 1 AND Lab57C8 > 5 THEN 0 ELSE Lab39.Lab39C37 END ELSE 0 END AS History'
WHEN 2 THEN ', CASE WHEN Lab57C8 > 5 THEN CASE WHEN Lab57C38 = 1 THEN 0 ELSE Lab39.Lab39C37 END ELSE 0 END AS History'
WHEN 6 THEN ', CASE WHEN Lab57C8 > 5 THEN CASE WHEN Lab57C38 = 1 THEN 0 ELSE Lab39.Lab39C37 END ELSE 0 END AS History'
ELSE ', CASE WHEN Lab57C38 = 1 THEN 0 ELSE Lab39.Lab39C37 END AS History' END+', Lab57.Lab22C1 as [Order], Lab158C3 AnatomicSite, Lab39.Lab39C19 DecimalDigits, Lab57.Lab57C18 SampleDate, Lab39.Lab39C11 Confidential, Lab57.Lab57C8 State, Lab200C2 CollectProcedure, Lab57.Lab57C46 ItIsAttached, Lab64.Lab64C2 AbbreviationMethod,lab43c5,ISNULL(P.Lab39C48, Lab39.Lab39C48) lab39c48p, Lab39.Lab39C48 lab39c48e, Lab39.Lab39C2'

IF(@Lab98C2TS = 'True')
BEGIN
IF(@Lab98C2TO = 'True')
BEGIN
SET @Instr = @Instr + ',(SELECT DISTINCT Lab40C2 FROM Lab40 INNER JOIN Lab145 ON lab145.Lab40c1 = Lab40.Lab40C1 WHERE Lab145.Lab39C1 = Lab39.Lab39C1 AND ISNULL(Lab145.Lab46C4, 0) = ISNULL(Lab57.Lab46C4, 0) AND Lab145.Lab63C1 = Lab22.Lab63C1 AND Lab145.Lab22C12 = CASE Lab22.Lab22C12 WHEN ''R'' THEN 1 WHEN ''U'' THEN 2 ELSE NULL END) Laboratory'
END
ELSE
BEGIN
SET @Instr = @Instr + ',(SELECT DISTINCT Lab40C2 FROM Lab40 INNER JOIN Lab145 ON lab145.Lab40c1 = Lab40.Lab40C1 WHERE Lab145.Lab39C1 = Lab39.Lab39C1 AND ISNULL(Lab145.Lab46C4, 0) = ISNULL(Lab57.Lab46C4, 0) AND Lab145.Lab63C1 = Lab22.Lab63C1 AND Lab145.Lab22C12 IS NULL) Laboratory'
END
END
ELSE
BEGIN
IF(@Lab98C2TO = 'True')
BEGIN
SET @Instr = @Instr + ',(SELECT DISTINCT Lab40C2 FROM Lab40 INNER JOIN Lab145 ON lab145.Lab40c1 = Lab40.Lab40C1 WHERE Lab145.Lab39C1 = Lab39.Lab39C1 AND ISNULL(Lab145.Lab46C4, 0) = ISNULL(Lab57.Lab46C4, 0) AND Lab145.Lab63C1 IS NULL AND Lab145.Lab22C12 = CASE Lab22.Lab22C12 WHEN ''R'' THEN 1 WHEN ''U'' THEN 2 ELSE NULL END) Laboratory'
END
ELSE
BEGIN
SET @Instr = @Instr + ',(SELECT DISTINCT Lab40C2 FROM Lab40 INNER JOIN Lab145 ON lab145.Lab40c1 = Lab40.Lab40C1 WHERE Lab145.Lab39C1 = Lab39.Lab39C1 AND ISNULL(Lab145.Lab46C4, 0) = ISNULL(Lab57.Lab46C4, 0) AND Lab145.Lab63C1 IS NULL AND Lab145.Lab22C12 IS NULL) Laboratory'
END
END

SET @Instr = @Instr + ',lab57c48 datetakesample'
SET @Instr = @Instr + ',(select LTRIM(Rtrim(Lab04C2)) + '' '' + LTRIM(Rtrim(Lab04C3)) from lab04 where lab04c1=lab57c49) usertakesample'
SET @Instr = @Instr + ', ISNULL(PQ.Lab39C3, '''') PackageName, CASE WHEN PQ.Lab39C17 = 0 AND Lab57.Lab46C4P > 0 THEN 0 ELSE ISNULL(Lab57.Lab46C4P,0) END PackageId '
SET @Instr = @Instr + ', SUBSTRING(LTRIM(Rtrim(Lab04C2)), 1, 1) + '''' + SUBSTRING(LTRIM(Rtrim(Lab04C3)), 1, 1) AS IniUserValidate'
SET @Instr = @Instr + ', (SELECT TOP 1 Lab04C12 FROM Lab04 US INNER JOIN Lab238 WITH(NOLOCK) ON Lab238.Lab04C1 = US.Lab04C1 WHERE Lab238.Lab63C1 = ISNULL(Lab22.Lab63C1, -1) AND Lab238.Lab43C1 = Lab39.Lab43C1 AND Lab238.Lab07C1 = 1) AS SectionUserImage'
SET @Instr = @Instr + ', MU.Lab24C2 AS TipoMuestra'
SET @Instr = @Instr + ', CASE WHEN Lab57C10 = 2 OR Lab57C10 = 3 THEN ''*'' WHEN Lab57C10 = 4 OR Lab57C10 = 5 THEN ''**'' ELSE '''' END AS Observations'
SET @Instr = @Instr + ', ISNULL((SELECT Lab242C2 FROM Lab242 WHERE Lab242C1 = MU.Lab242C1), '''') AS GroupingType'
SET @Instr = @Instr + ', CASE WHEN LAB57c31 IN (SELECT LAB04c1 from Lab04 WHERE LAB04C5 =''InterReferencia'') THEN ''E'' ELSE '''' END AS ExternalResult'
SET @Instr = @Instr + ', Lab64.Lab64C2 AS MethodNotes'

IF (@Historico=0)
BEGIN


SET @Instr = @Instr + ' FROM Lab57 INNER JOIN Lab22 ON (Lab22.Lab22C1 = Lab57.Lab22C1) INNER JOIN Lab39 ON (Lab39.Lab39C1 = Lab57.Lab39C1) INNER JOIN Lab43 ON (Lab43.Lab43C1 = Lab39.Lab43C1) INNER JOIN Lab24 MU WITH (NOLOCK) ON (MU.Lab24C1 = Lab39.Lab24C1) LEFT OUTER JOIN Lab95 ON (Lab95.Lab57C1 = Lab57.Lab57C1) LEFT OUTER JOIN Lab45 ON (Lab45.Lab45C1 = Lab57.Lab45C1) '
SET @Instr = @Instr + 'LEFT OUTER JOIN Lab48 ON (Lab48.Lab48C1 = Lab57.Lab48C1) LEFT OUTER JOIN Lab50 ON (Lab50.Lab50C1 = Lab48.Lab50C1) LEFT OUTER JOIN Lab39 P ON (P.Lab39C1 = Lab57.Lab46C4) LEFT OUTER JOIN Lab39 PQ ON (PQ.Lab39C1 = Lab57.Lab46C4P) '
SET @Instr = @Instr + 'LEFT OUTER JOIN Lab64 ON (Lab64.Lab64C1 = Lab57.Lab64C1) LEFT OUTER JOIN Lab04 ON (Lab04.Lab04C1 = Lab57.Lab57C28) LEFT OUTER JOIN Lab147 ON (Lab147.Lab39C1 = Lab57.Lab39C1 AND Lab147.Lab22C1 = Lab57.Lab22C1) LEFT OUTER JOIN Lab24 ON (Lab24.Lab24C1 = Lab57.Lab24C1) LEFT OUTER JOIN Lab158 ON (Lab158.Lab158C1 = Lab57.Lab158C1) LEFT OUTER JOIN Lab200 ON (Lab200.Lab200C1 = Lab57.Lab200C1) '
END
ELSE
BEGIN

SET @Instr = @Instr + ' FROM EnterpriseH.dbo.Lab57 INNER JOIN EnterpriseH.dbo.Lab22 ON (Lab22.Lab22C1 = Lab57.Lab22C1) INNER JOIN Lab39 ON (Lab39.Lab39C1 = Lab57.Lab39C1) INNER JOIN Lab43 ON (Lab43.Lab43C1 = Lab39.Lab43C1) INNER JOIN Lab24 MU WITH (NOLOCK) ON (MU.Lab24C1 = Lab39.Lab24C1) LEFT OUTER JOIN EnterpriseH.dbo.Lab95 ON (Lab95.Lab57C1 = Lab57.Lab57C1) '
SET @Instr = @Instr + 'LEFT OUTER JOIN Lab45 ON (Lab45.Lab45C1 = Lab57.Lab45C1) LEFT OUTER JOIN Lab48 ON (Lab48.Lab48C1 = Lab57.Lab48C1) '
SET @Instr = @Instr + 'LEFT OUTER JOIN Lab50 ON (Lab50.Lab50C1 = Lab48.Lab50C1) LEFT OUTER JOIN Lab39 P ON (P.Lab39C1 = Lab57.Lab46C4) LEFT OUTER JOIN Lab39 PQ ON (PQ.Lab39C1 = Lab57.Lab46C4P) '
SET @Instr = @Instr + 'LEFT OUTER JOIN Lab64 ON (Lab64.Lab64C1 = Lab57.Lab64C1) LEFT OUTER JOIN Lab04 ON (Lab04.Lab04C1 = Lab57.Lab57C28) LEFT OUTER JOIN Lab147 ON (Lab147.Lab39C1 = Lab57.Lab39C1 AND Lab147.Lab22C1 = Lab57.Lab22C1) LEFT OUTER JOIN Lab24 ON (Lab24.Lab24C1 = Lab57.Lab24C1) LEFT OUTER JOIN Lab158 ON (Lab158.Lab158C1 = Lab57.Lab158C1) LEFT OUTER JOIN Lab200 ON (Lab200.Lab200C1 = Lab57.Lab200C1) '

END
SET @Instr = @Instr + 'WHERE Lab39.Lab39C7 IN (0,2) '

IF (@Lab43C1 > 0)
SET @Instr = @Instr + 'AND Lab43.Lab43C1 = @Lab43C1 '

SET @Instr = @Instr + CASE @Lab105C13 WHEN 1 THEN 'AND Lab57C11 = 0 AND Lab57.Lab57C8 in (6,7) '
WHEN 3 THEN 'AND Lab57C11 = 0 AND Lab57C8 in (6,8)'
WHEN 5 THEN 'AND Lab57C11 = 0 AND Lab57C8 IN (6,7,8) '
WHEN 6 THEN 'AND Lab57C11 = 0 AND Lab57C8 IN (1,2,3,4,6,7,8) '
ELSE 'AND Lab57C11 = 0 AND Lab57C8 IN (2,3,4,6,7,8) ' END

IF (@Lab105C9 IS NOT NULL)
BEGIN
IF (@Lab105C8 > 1)
BEGIN
SET @Instr = @Instr + 'AND (Lab39.Lab39C1 IN (' + @Lab105C9 + ') OR Lab39.Lab39C1 IN (SELECT Lab39C1 FROM Lab46 WHERE Lab46C4 IN (' + @Lab105C9 + '))) AND Lab39.Lab39C11 = @Lab39C11T '
END
IF (@Lab105C8 = 1)
BEGIN
SET @Instr = @Instr + 'AND Lab43.Lab43C1 IN (' + @Lab105C9 + ') '
IF @Lab39C11 = 0
BEGIN
SET @Instr = @Instr + 'AND Lab39.Lab39C11 = 0 '
END
END

IF (@Lab105C8 = -1 AND @Lab39C11 = 0)
BEGIN
SET @Instr = @Instr + 'AND Lab39.Lab39C11 = 0 '
END

IF (@Lab105C8 = -2)
BEGIN
SET @Instr = @Instr + 'AND Lab39.Lab39C1 IN (' + @Lab105C9 + ') '
IF (@Lab39C11 = 0)
BEGIN
SET @Instr = @Instr + 'AND Lab39.Lab39C11 = 0 '
END
END
END
ELSE
BEGIN
IF (@Lab39C11 = 0)
BEGIN
SET @Instr = @Instr + 'AND Lab39.Lab39C11 = 0 '
END
END

INSERT INTO @LabTable
SELECT Lab22C1
FROM Lab22
WHERE Lab07C1 = 1
AND Lab22C17 = @Lab22C1

SET @recallOrder = ''

DECLARE cuCursor CURSOR
FOR
SELECT Lab22C1
FROM @LabTable
OPEN cuCursor
FETCH NEXT FROM cuCursor INTO @recallId
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @recallOrder = @recallOrder + CAST(@recallId AS VARCHAR) + ','
FETCH NEXT FROM cuCursor INTO @recallId
END
CLOSE cuCursor
DEALLOCATE cuCursor

if(@Lab105C8 = -1)
BEGIN
SET @Instr = @Instr + 'AND Lab57C35 = 1 AND Lab57.Lab22C1 = @Lab22C1 '
END
else
BEGIN
IF @recallOrder = ''
SET @Instr = @Instr + 'AND Lab57C35 = 1 AND Lab57.Lab22C1 = @Lab22C1 '
ELSE
BEGIN
SET @recallOrder = SUBSTRING(@recallOrder, 1, LEN(@recallOrder)-1)
SET @Instr = @Instr + 'AND Lab57C35 = 1 AND Lab57.Lab22C1 IN('+ @recallOrder + ') '
END
END
--___ Ver ordenamiento de perfiles.
SET @Instr = @Instr + 'ORDER BY Lab43.Lab43C5, ISNULL(P.Lab39C48, Lab39.Lab39C48), Lab39.Lab39C48, Lab39.Lab39C2'

IF(@AnalitosConcurrentesxPerfil=1)
BEGIN
set @Instrper =@Instr
--TestParent
set @Instrper = replace(@Instrper,'(Lab57.Lab46C4,0)','(t.perfil,0) ')
--ProfileId
set @Instrper = replace(@Instrper,'(P.Lab39C1, Lab39.Lab39C1)','(t.perfil, Lab39.Lab39C1) ')
--ProfileName
set @Instrper = replace(@Instrper,'(P.Lab39C3, Lab39.Lab39C3)','(t.nombre, Lab39.Lab39C3) ')
-- Ordernamiento
set @Instrper = replace(@Instrper,'ISNULL(P.Lab39C48, Lab39.Lab39C48) ','ISNULL(t.Lab39C48, Lab39.Lab39C48) ')
-- Paquete ID
set @Instrper = replace(@Instrper,'CASE WHEN PQ.Lab39C17 = 0 AND Lab57.Lab46C4P > 0 THEN 0 ELSE ISNULL(Lab57.Lab46C4P,0) END PackageId ', 'CASE WHEN PQ.Lab39C17 = 0 AND t.Lab46C4P > 0 THEN 0 ELSE ISNULL(t.Lab46C4P,0) END PackageId ')
--Where
if(@Historico=0)
begin
set @Instrper = replace(@Instrper,'WHERE',' left outer join @tmpc t on t.analito=lab57.lab39c1 and existe =0 WHERE' )
end
else
begin
set @Instrper = replace(@Instrper,'WHERE',' left outer join @tmpc t on t.analito=EnterpriseH.dbo.Lab57.lab39c1 and existe =0 WHERE' )
end
set @Instrper = @Instrtem +@Instrper
if(@Historico=0)
begin
set @Instrper=rtrim(ltrim(replace(@Instrper,'ORDER BY',' and lab57.lab39c1 in (select analito from @tmpc where existe =0) ORDER BY')))
end
else
begin
set @Instrper=rtrim(ltrim(replace(@Instrper,'ORDER BY',' and EnterpriseH.dbo.Lab57.lab39c1 in (select analito from @tmpc where existe =0) ORDER BY')))
end
END
print @instr
IF (@Lab105C9 IS NOT NULL AND @Lab105C8 > 1)
BEGIN
IF (@Lab105C13 = 2 OR @Lab105C13 = 4 OR @Lab105C13 = 6)
BEGIN
insert into #tmp_table
EXEC sp_executesql @Instr, N'@Lab22C1 BIGINT, @Lab98 VARCHAR(64), @Lab39C11T INT, @Lab98CC VARCHAR(4), @Lab98RR VARCHAR(64), @Lab43C1 INT', @Lab22C1, @Lab98, @Lab39C11T, @Lab98CC, @Lab98RR, @Lab43C1
insert into #tmp_table
EXEC sp_executesql @Instrper, N'@Lab22C1 BIGINT, @Lab98 VARCHAR(64), @Lab39C11T INT, @Lab98CC VARCHAR(4), @Lab98RR VARCHAR(64), @Lab43C1 INT', @Lab22C1, @Lab98, @Lab39C11T, @Lab98CC, @Lab98RR, @Lab43C1
SELECT @IniUserVali= COALESCE(@IniUserVali + '/ ', '') + IniUserValidate FROM #tmp_table GROUP BY ValidateUser, IniUserValidate
SELECT TestId,TestName,Pathologic,PathologicText,Result,ResultDate,Restricted,Unit,RefMin,RefMax,LiteralResult,ResultType,RefText,Comment,SectionId,Section,TestParent,ProfileName,ProfileId,PrintComment,Method,[Image],ValidateUser,ValidateDate,UserName,UserImage,ProfileTitle,Title,Antibiogram,Graphic,MicSample,History,[Order],AnatomicSite,DecimalDigits,SampleDate,Confidential,State,CollectProcedure,ItIsAttached,AbbreviationMethod, Laboratory,datetakesample,usertakesample, PackageName, PackageId, @IniUserVali AS IniUserValidate, SectionUserImage, TipoMuestra, Observations, GroupingType, ExternalResult, MethodNotes FROM #tmp_table order by PackageId asc,Lab43C5,lab39c48p,lab39c48e,lab39c2
END
ELSE
BEGIN
insert into #tmp_table
EXEC sp_executesql @Instr, N'@Lab22C1 BIGINT, @Lab98 VARCHAR(64), @Lab39C11T INT, @Lab98CC VARCHAR(4), @Lab98RR VARCHAR(64), @Lab43C1 INT', @Lab22C1, @Lab98, @Lab39C11T, @Lab98CC, @Lab98RR, @Lab43C1
insert into #tmp_table
EXEC sp_executesql @Instrper, N'@Lab22C1 BIGINT , @Lab98 VARCHAR(64), @Lab39C11T INT, @Lab98CC VARCHAR(4), @Lab98RR VARCHAR(64), @Lab43C1 INT', @Lab22C1, @Lab98, @Lab39C11T, @Lab98CC, @Lab98RR, @Lab43C1
SELECT @IniUserVali= COALESCE(@IniUserVali + '/ ', '') + IniUserValidate FROM #tmp_table GROUP BY ValidateUser, IniUserValidate
SELECT TestId,TestName,Pathologic,PathologicText,Result,ResultDate,Restricted,Unit,RefMin,RefMax,LiteralResult,ResultType,RefText,Comment,SectionId,Section,TestParent,ProfileName,ProfileId,PrintComment,Method,[Image],ValidateUser,ValidateDate,UserName,UserImage,ProfileTitle,Title,Antibiogram,Graphic,MicSample,History,[Order],AnatomicSite,DecimalDigits,SampleDate,Confidential,State,CollectProcedure,ItIsAttached,AbbreviationMethod, Laboratory,datetakesample,usertakesample, PackageName, PackageId, @IniUserVali AS IniUserValidate, SectionUserImage, TipoMuestra, Observations, GroupingType, ExternalResult, MethodNotes FROM #tmp_table order by PackageId asc,Lab43C5,lab39c48p,lab39c48e,lab39c2
END
END
ELSE
BEGIN
IF (@Lab105C13 = 2 OR @Lab105C13 = 4 OR @Lab105C13 = 6)
BEGIN
insert into #tmp_table
EXEC sp_executesql @Instr, N'@Lab22C1 BIGINT, @Lab98 VARCHAR(64), @Lab98CC VARCHAR(4), @Lab98RR VARCHAR(64), @Lab43C1 INT', @Lab22C1, @Lab98, @Lab98CC, @Lab98RR, @Lab43C1
insert into #tmp_table
EXEC sp_executesql @Instrper, N'@Lab22C1 BIGINT, @Lab98 VARCHAR(64), @Lab98CC VARCHAR(4), @Lab98RR VARCHAR(64), @Lab43C1 INT', @Lab22C1, @Lab98, @Lab98CC, @Lab98RR, @Lab43C1
SELECT @IniUserVali= COALESCE(@IniUserVali + '/ ', '') + IniUserValidate FROM #tmp_table GROUP BY ValidateUser,IniUserValidate
SELECT TestId,TestName,Pathologic,PathologicText,Result,ResultDate,Restricted,Unit,RefMin,RefMax,LiteralResult,ResultType,RefText,Comment,SectionId,Section,TestParent,ProfileName,ProfileId,PrintComment,Method,[Image],ValidateUser,ValidateDate,UserName,UserImage,ProfileTitle,Title,Antibiogram,Graphic,MicSample,History,[Order],AnatomicSite,DecimalDigits,SampleDate,Confidential,State,CollectProcedure,ItIsAttached,AbbreviationMethod, Laboratory ,datetakesample,usertakesample, PackageName, PackageId, @IniUserVali AS IniUserValidate, SectionUserImage, TipoMuestra, Observations, GroupingType, ExternalResult, MethodNotes FROM #tmp_table order by PackageId asc,Lab43C5,lab39c48p,lab39c48e,lab39c2
END
ELSE
BEGIN
insert into #tmp_table
EXEC sp_executesql @Instr, N'@Lab22C1 BIGINT, @Lab98 VARCHAR(64), @Lab98CC VARCHAR(4), @Lab98RR VARCHAR(64), @Lab43C1 INT', @Lab22C1, @Lab98, @Lab98CC, @Lab98RR, @Lab43C1
insert into #tmp_table
EXEC sp_executesql @Instrper, N'@Lab22C1 BIGINT, @Lab98 VARCHAR(64), @Lab98CC VARCHAR(4), @Lab98RR VARCHAR(64), @Lab43C1 INT', @Lab22C1 , @Lab98, @Lab98CC, @Lab98RR, @Lab43C1
SELECT @IniUserVali= COALESCE(@IniUserVali + '/ ', '') + IniUserValidate FROM #tmp_table GROUP BY ValidateUser, IniUserValidate
SELECT TestId,TestName,Pathologic,PathologicText,Result,ResultDate,Restricted,Unit,RefMin,RefMax,LiteralResult,ResultType,RefText,Comment,SectionId,Section,TestParent,ProfileName,ProfileId,PrintComment,Method,[Image],ValidateUser,ValidateDate,UserName,UserImage,ProfileTitle,Title,Antibiogram,Graphic,MicSample,History,[Order],AnatomicSite,DecimalDigits,SampleDate,Confidential,State,CollectProcedure,ItIsAttached,AbbreviationMethod, Laboratory,datetakesample,usertakesample, PackageName, PackageId, @IniUserVali AS IniUserValidate, SectionUserImage, TipoMuestra, Observations, GroupingType, ExternalResult, MethodNotes FROM #tmp_table order by PackageId asc,Lab43C5,lab39c48p,lab39c48e,lab39c2
END
END
END
GO