USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[sp_LabLoadExamEntry]    Script Date: 11/21/2017 11:25:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_LabLoadExamEntry]
	@SearchCriteria varchar(50)
AS
Begin
  select	isnull(E.LabExamID,'') as [ExamID], 
			IsNull(E.LabExam,'') as [Exam],
			IsNull(E.RateA,'0') as RateA, 
			IsNull(E.RateB,'0') as RateB, 
			IsNull(E.RateC,'0') as RateC, 
			IsNull(E.RateD,'0') as RateD,
			IsNull(E.RateE,'0') as RateE,
			IsNull(E.RateF,'0') as RateF,
			IsNull(E.RateG,'0') as RateG,
			IsNull(E.RateH,'0') as RateH,
			IsNull(E.RateI,'0') as RateI,
			IsNull(E.Status,'') as Status,
			IsNull(E.Classification,'C') as Classification,	
			E.WithPF,
			cast(IsNull(E.ProfFeeA, 0.00) as money) as ProfFeeA,
			cast(IsNull(E.ProfFeeB, 0.00) as money) as ProfFeeB,
			cast(IsNull(E.ProfFeeC, 0.00) as money) as ProfFeeC,
			cast(IsNull(E.ProfFeeD, 0.00) as money) as ProfFeeD,
			cast(IsNull(E.ProfFeeE, 0.00) as money) as ProfFeeE,
			E.OpenQuantity,
			E.OpenProfFee,
			IsNull(E.FixRate, 'Y') as FixRate,
			E.IsProcedure,
			IsNull(E.ProfID1,'') as ProfID1,
			IsNull(V.ShortName,'') as ShortName,
			IsNull(D.DivisionCode,'') as DivisionCode,
			IsNull(D.Division,'') as Division,
			IsNull(E.Stat,'') as ProcedureType,
			IsNull(E.LabSectionID, '') as SectionId,
			IsNull(S.LabSection,'') as [Section],
			IsNull(E.AutoMarkUp,'') as AutoMarkUp,
			IsNull(E.Form, '') as Form,
			IsNull(E.LabGrp,'') as LabGrp,
			IsNull(E.Common, 'N') as Common,
			IsNull(E.ResultConfidential,'N') as Confidential,
			E.MedicareType,
			IsNull(E.ISOFormCode,'') as ISOCode,
			isnull(E.HolidayMarkUp,'') as HolidayMarkUp,
			IsNull(E.SupplementalExam,'') as SupplementalExamID,
			IsNull(A.LabExam,'') as SupplementalExam,
			IsNull(V.FormType,'') as FormType,
			IsNull(V.ValueType,'') as ValueType,
			IsNull(V.ConversionFactor,0) as ConversionFactor,
			IsNull(V.Unit, '') As cuUnit,
    		IsNull(V.ConversionUnit, '')  As siUnit,
			IsNull(V.DescriptiveMaxValue,'') As DescriptiveMaxValue,
			IsNull(V.DescriptiveMinValue, '') As DescriptiveMinValue,
    		IsNull(V.AdditionalDescription, '') As AdditionalDescription,
			IsNull(V.MaleMinValue,'') As MaleMinValue,
    		IsNull(V.MaleMaxValue,'') As MaleMaxValue,
    		IsNull(V.FeMaleMinValue, '') As FeMaleMinValue,
    		IsNull(V.FeMaleMaxValue,'') As FeMaleMaxValue,
    		IsNull(V.PromptData,'') As PromptData,
    		IsNull(V.Remarks,'') As Remarks,
			IsNull(V.ResultName,'') as ResultName,
			V.WithReagent,
    		IsNull(V.PromptData2,'') As PromptData2,
			E.BreakdownInDetail as Detail,
			IsNull(E.MarkupPercentage,0) as MarkupPercentage,
			IsNull(V.Operator, '') as Operator,	
			IsNull(E.MaxQty,1) as MaxQty,
			IsNull(E.EditNurseRequest,'') as EditNurseRequest,
			IsNull(E.PathoSelection,'') as PathoSelection,
			Isnull(E.WholePage,'N') as WholePage,
			IsNull(V.DefaultValue,'') as DefaultValue,
			isnull(TV.Tvalue,'') as ThresholdValue
	from BUILD_FILE..tbCoLabExam E
			left outer join BUILD_FILE..tbCoLabValues V on V.Code = E.LabExamID
			Left Outer Join BUILD_FILE..tbCoLabSection S on S.LabSectionId = E.LabSectionID
			Left Outer Join BUILd_FILE..tbCoDivision D on D.DivisionCode = S.DivisionCode
			Left Outer Join BUILD_FILE..tbCoLabExam A on A.LabExamId = E.SupplementalExam
			Left outer join laboratory..tblab_buildtvalues TV on E.labexamid=TV.code
	where E.LabExamID = @SearchCriteria 
End