USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_View_RequestNum]    Script Date: 9/23/2020 2:54:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Lab_View_RequestNum]
	@IdNum varchar(20),
	@HospNumber varchar(20) = '',
	@Refnum varchar(20) = ''
AS
SET NOCOUNT ON;

Declare @HospNum varchar(10)

Set @IdNum = rtrim(ltrim(@idNum));
Set @HospNum = @HospNumber


if len(rtrim(ltrim(@Refnum))) = 0
	Begin		
		Select top 1 L.Requestnum 
		from tbLabLogbook L
			Inner Join LABORATORY..tbLABMaster LabMaster 
				on L.RequestNum = LabMaster.RequestNum and IsNull(LabMaster.RequestStatus,'') <> 'R'
			left outer join Build_File..tbCoLabExam E on E.Labexamid = L.LabExamId
		where L.hospnum = @HospNum and isnull(L.verifydate,'') <> '' 
			and IsNull(E.ResultConfidential,'N')= 'N' 
			and L.IDNum = @Idnum;
	End
Else
	Begin
		Select top 1 L.Requestnum 
		from tbLabLogbook L
			Inner Join LABORATORY..tbLABMaster LabMaster 
				on L.RequestNum = LabMaster.RequestNum and IsNull(LabMaster.RequestStatus,'') <> 'R'
			left outer join Build_File..tbCoLabExam E on E.Labexamid = L.LabExamId
		where L.hospnum = @HospNum and isnull(L.verifydate,'') <> '' 
			and IsNull(E.ResultConfidential,'N')= 'N' 
			and L.IDNum = @Idnum 
			and L.RefNum = @Refnum;
	End