ALTER TABLE radiology..tbULRequest ADD [EditedByID] [varchar](20) NULL GO ALTER TABLE radiology..tbULWomensResultOB ADD [isReleased] [bit] NULL GO USE [Radiology] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[tbRadiologyRequests] AS SELECT CASE WHEN A.swfin = 'X' THEN 'X' WHEN A.swfin = 'Y' THEN CASE WHEN Res.verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.swfin IS NULL THEN 'S' WHEN A.swfin = 'C' THEN 'R' END AS [Status], A.RevenueID RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.Code ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.Sex, C.Sex) ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(EditedByID,'') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(editedbyid),'') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.XrayNum, Res.RadCode, P.DcrDate, Res.ResultDate, swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, A.FormType, IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedById)), '') as [RadTech], A.[By] AS Receptionist, CancelReason, A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, ProfID, --SEARCH FIELD FOR DEFAULT READER, -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end '' as MSSCLassification, /*--GOVERNMENT HOSPITAL CLASSIFICATION*/ --isnull(isImageAvailable,0) '0' as isImageAvailable, A.StudyUID, '0' as EmailResult, A.SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbXRRequest A LEFT OUTER JOIN RADIOLOGY..tbXRResult Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Billing..tbBillExamListing B ON A.Code = B.ItemID AND B.RevenueID = 'XR' LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE IsNull(A.RevenueID, 'XR') = 'XR' UNION ALL SELECT CASE WHEN A.swfin = 'X' THEN 'X' WHEN A.swfin = 'Y' THEN CASE WHEN verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.swfin IS NULL THEN 'S' WHEN A.swfin = 'C' THEN 'R' END AS [Status], A.RevenueID RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.Code ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.BirthDate ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.Sex ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(EditedByID, '') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(editedbyid), '') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.XrayNum, Res.RadCode, P.DcrDate, Res.ResultDate, swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, A.FormType, IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedById)), '') as [RadTech], A.[By] AS Receptionist, CancelReason, A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, ProfID, --SEARCH FIELD FOR DEFAULT READER -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end '' as MSSCLassification, /*--GOVERNMENT HOSPITAL CLASSIFICATION*/ --isnull(isImageAvailable,0) '0' as isImageAvailable, A.StudyUID, '0' as EmailResult, A.SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbCTRequest A LEFT OUTER JOIN RADIOLOGY..tbCTResult Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Billing..tbBillExamListing B ON A.Code = B.ItemID AND B.RevenueID = 'CT' LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE IsNull(A.RevenueID, 'CT') = 'CT' UNION ALL SELECT CASE WHEN A.swfin = 'X' THEN 'X' WHEN A.swfin = 'Y' THEN CASE WHEN verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.swfin IS NULL THEN 'S' WHEN A.swfin = 'C' THEN 'R' END AS [Status], A.RevenueID RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.Code ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.BirthDate ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.Sex ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(A.EditedByID, '') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(A.EditedByID), '') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.XrayNum, Res.RadCode, P.DcrDate, Res.ResultDate, swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, A.FormType, IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedById)), '') as [RadTech], A.[By] AS Receptionist, CancelReason, A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, ProfID, --SEARCH FIELD FOR DEFAULT READER -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end '' as MSSCLassification, /*--GOVERNMENT HOSPITAL CLASSIFICATION*/ --isnull(isImageAvailable,0) '0' as isImageAvailable, A.StudyUID, '0' as EmailResult, A.SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbULRequest A LEFT OUTER JOIN RADIOLOGY..tbULResult Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Radiology..tbBillExamListing B ON A.Code = B.ItemID AND B.RevenueID = 'US' LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE IsNull(A.RevenueID, 'US') = 'US' UNION ALL SELECT CASE WHEN A.swfin = 'X' THEN 'X' WHEN A.swfin = 'Y' THEN CASE WHEN verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.swfin IS NULL THEN 'S' WHEN A.swfin = 'C' THEN 'R' END AS [Status], A.RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.Code ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.BirthDate ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.Sex ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(EditedByID, '') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(editedbyid), '') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.XrayNum, Res.RadCode, P.DcrDate, Res.ResultDate, swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, '4', IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedById)), '') as [RadTech], A.[By] AS Receptionist, '', A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, ProfID, --SEARCH FIELD FOR DEFAULT READER -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end '' as MSSCLassification, /*--GOVERNMENT HOSPITAL CLASSIFICATION*/ --isnull(isImageAvailable,0) '0' as isImageAvailable, A.StudyUID, '0' as EmailResult, A.SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbMRIRequest A LEFT OUTER JOIN RADIOLOGY..tbMRIResult Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Radiology..tbBillExamListing B ON A.Code = B.ItemID AND B.RevenueID in ('MI','MS','RI') LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE IsNull(A.RevenueID, 'RI') = 'RI' UNION ALL SELECT CASE WHEN A.swmrk IN ('X', 'S') THEN 'X' WHEN A.swmrk = 'Y' THEN CASE WHEN verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.swmrk IS NULL THEN 'X' WHEN A.swmrk = 'C' THEN 'R' END AS [Status], A.RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.ItemID ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.BirthDate ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.Sex ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(EditedByID, '') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(editedbyid), '') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.XrayNum, Res.RadCode, P.DcrDate, Res.ResultDate, A.swmrk swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, A.FormType, IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedByID)), '') as [RadTech], A.UserID AS Receptionist, CancelReason, A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, ProfID, --SEARCH FIELD FOR DEFAULT READER -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end '' as MSSCLassification, /*--GOVERNMENT HOSPITAL CLASSIFICATION*/ --isnull(isImageAvailable,0) '0' as isImageAvailable, '' StudyUID, '0' as EmailResult, A.SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbMammoMaster A LEFT OUTER JOIN RADIOLOGY..tbMammoResult Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Radiology..tbBillExamListing B ON A.ItemID = B.ItemID AND B.RevenueID IN ('MM', 'MG') /* = 'MM'*/ LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE IsNull(A.RevenueID, '') <> 'MD' UNION ALL SELECT CASE WHEN A.swfin = 'X' THEN 'X' WHEN A.swfin = 'Y' THEN CASE WHEN verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.swfin IS NULL THEN 'S' WHEN A.swfin = 'C' THEN 'R' END AS [Status], A.RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.Code ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.BirthDate ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.Sex ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(EditedByID, '') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(editedbyid), '') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.XrayNum, Res.RadCode, P.DcrDate, Res.ResultDate, swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, A.FormType, IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedByID)), '') as [RadTech], A.[By] AS Receptionist, CancelReason, A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, '' AS ProfID, --SEARCH FIELD FOR DEFAULT READER -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end '' as MSSCLassification, /*--GOVERNMENT HOSPITAL CLASSIFICATION*/ '0' as isImageAvailable, A.StudyUID, '0' as EmailResult, A.SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbOBULRequest A LEFT OUTER JOIN RADIOLOGY..tbULWomensResultOB Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Radiology..tbBillExamListing B ON A.Code = B.ItemID AND B.RevenueID IN ('WH', 'PG', 'O1') LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate, RoomID,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate, 'OPD',age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE IsNull(A.RevenueID, 'WH') = 'WH' UNION ALL SELECT CASE WHEN A.RequestStatus IN ('X', 'S') THEN 'X' WHEN A.RequestStatus = 'Y' THEN CASE WHEN verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.RequestStatus IS NULL THEN 'X' WHEN A.RequestStatus = 'C' THEN 'R' END AS [Status], A.RevenueID RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.ItemID ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.BirthDate ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.Sex ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(EditedByID, '') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(editedbyid), '') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.XrayNum, Res.RadCode, P.DcrDate, Res.ResultDate, A.RequestStatus swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, A.FormType, IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedByID)), '') as [RadTech], A.[By] AS Receptionist, CancelReason, A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, '' AS ProfID, --SEARCH FIELD FOR DEFAULT READER -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end '' as MSSCLassification /*--GOVERNMENT HOSPITAL CLASSIFICATION*/, '0' as isImageAvailable, A.StudyUID, '0' as EmailResult, '' as SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbNucMaster A LEFT OUTER JOIN RADIOLOGY..tbNucLogBook Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Radiology..tbBillExamListing B ON A.ItemID = B.ItemID AND B.RevenueID = 'NU' LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE IsNull(A.RevenueID, 'NU') = 'NU' UNION ALL SELECT CASE WHEN A.swfin = 'X' THEN 'X' WHEN A.swfin = 'Y' THEN CASE WHEN verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.swfin IS NULL THEN 'S' WHEN A.swfin = 'C' THEN 'R' END AS [Status], A.RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.Code ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.BirthDate ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN Cash.Sex ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(EditedByID, '') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(editedbyid), '') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.XrayNum, Res.RadCode, P.DcrDate, Res.ResultDate, swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, '4', IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedById)), '') as [RadTech], A.[By] AS Receptionist, '', A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, ProfID, --SEARCH FIELD FOR DEFAULT READER -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end '' as MSSCLassification, /*--GOVERNMENT HOSPITAL CLASSIFICATION*/ '0' as isImageAvailable, '0' AS StudyUID, '0' as EmailResult, A.SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbMRIRequest1 A LEFT OUTER JOIN RADIOLOGY..tbMRIResult Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Radiology..tbBillExamListing B ON A.Code = B.ItemID AND B.RevenueID in ('MI','MS','RI') LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE IsNull(A.RevenueID, 'RI') = 'RI' UNION ALL SELECT CASE WHEN A.swfin = 'X' THEN 'X' WHEN A.swfin = 'Y' THEN CASE WHEN Res.verifydate IS NULL THEN 'Y' ELSE 'V' END WHEN A.swfin IS NULL THEN 'S' WHEN A.swfin = 'C' THEN 'R' END AS [Status], A.RevenueID RevenueID, A.TransDate, IsNull(A.IdNum, 'CASH') AS IDNum, A.Hospnum, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) + ', ' + IsNull(Cash.FirstName, C.FirstName) + ' ' + IsNull(Cash.MiddleName, C.MiddleName) ELSE C.LastName + ', ' + C.FirstName + ' ' + C.MiddleName END AS [Patient Name], A.Code ItemID, B.Description, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN 'Walk-in' ELSE A.RoomID END AS RoomID, A.RequestDoctorCode, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.Birthdate END AS BirthDate, Age = Laboratory.dbo.fn_LabComputeAge(CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.BirthDate, C.BirthDate) ELSE C.BirthDate END, A.transdate), CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.Sex, C.Sex) ELSE C.Sex END AS Sex, A.refnum, A.RequestNum, isnull(EditedByID, '') EditedByID, isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(editedbyid), '') [Editedby], isnull(radiology.dbo.fn_getemployeename(transcribedbyid), '') [Transcribedby], isnull(radiology.dbo.fn_getemployeename(verifybyid), '') [Verifiedby], CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.LastName, C.LastName) ELSE C.LastName END AS LastName, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.FirstName, C.FirstName) ELSE C.FirstName END AS FirstName, VerifyDate, Res.RequestNum, Res.RadCode, P.DcrDate, Res.ResultDate, swfin, CASE WHEN LEFT(A.HospNum, 1) = 'T' THEN IsNull(Cash.MiddleName, C.MiddleName) ELSE C.MiddleName END AS MiddleName, A.FormType, IsNull(A.Amount, '0.00') AS Amount, Radiology.dbo.fn_GetDoctorsName(A.RequestDoctorCode) AS DoctorName, A.Quantity, isnull(radiology.dbo.fn_getemployeename(isnull(Res.RadTech,Res.TranscribedById)), '') as [RadTech], A.[By] AS Receptionist, CancelReason, A.RevenueID DepartmentID, Res.isReleased ,Res.RadCode2 as resident, ISNULL(stat,'0') as isStat, ProfID, --SEARCH FIELD FOR DEFAULT READER -- ,Case when isnull(C.MSSDiscountExpiry,'') = '' OR C.MSSDiscountExpiry < GETDATE() then --'PAY' -- else --'SERVICE' -- end ''as MSSCLassification, /*--GOVERNMENT HOSPITAL CLASSIFICATION*/ --isnull(isImageAvailable,0) '0' as isImageAvailable, A.StudyUID, '0' as EmailResult, A.SlipNum ,A.ImagingReason, A.SurgicalReason FROM RADIOLOGY..tbRequestMaster A LEFT OUTER JOIN RADIOLOGY..tbResultMaster Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN Billing..tbBillExamListing B ON A.Code = B.ItemID AND B.RevenueID IN (SELECT RevenueID FROM tbRadiologyRevenues where Active = '1') LEFT OUTER JOIN PATIENT_DATA..tbmaster C ON A.HospNum = C.HospNum LEFT OUTER JOIN (SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbPatient where ISNUMERIC(IdNum) = 1 UNION ALL SELECT IDNum, AdmDate, DcrDate,age FROM Patient_Data..tbOutPatient where ISNUMERIC(IdNum) = 0) P ON A.IDNum = P.IDNum LEFT OUTER JOIN Patient_Data..tbCashPatient Cash ON Cash.HospNum = A.HospNum WHERE A.RevenueID IN (SELECT RevenueID FROM RADIOLOGY..tbRadiologyRevenues) GO alter table [Medicare].[dbo].[tbMedPackage] add CaseType integer GO CREATE INDEX [by_CaseType] ON MEDICARE..tbMedPackage([CaseType]) GO update [Medicare].[dbo].[tbMedPackage] set casetype=0 where PackageID<=30 go update [Medicare].[dbo].[tbMedPackage] set casetype=1 where PackageID>30 and PackageID<434 go update [Medicare].[dbo].[tbMedPackage] set casetype=2 where PackageID>=434 go alter table [Medicare].[dbo].[tbMedPackage] add CaseType integer GO CREATE INDEX [by_CaseType] ON MEDICARE..tbMedPackage([CaseType]) GO CREATE INDEX [by_Package] ON MEDICARE..tbMedPackage([Package]) GO CREATE INDEX [by_ICDCode] ON MEDICARE..[tbMed_MedicalCaseRates]([ICDCode]) GO CREATE INDEX [by_PackageID] ON MEDICARE..[tbMed_MedicalCaseRates]([PackageID]) GO update [Medicare].[dbo].[tbMedPackage] set casetype=1 where PackageID>30 and PackageID<434 go CREATE INDEX [by_RVSCode] ON MEDICARE..[tbMed_Procedures]([RVSCODE]) GO CREATE INDEX [by_PackageID] ON MEDICARE..[tbMed_Procedures]([PackageID]) GO use build_file Go Create Function dbo.SoundsLike(@OriginalText VarChar(8000)) Returns VarChar(8000) As Begin Declare @Output VarChar(8000) Set @Output = '' While Not @OriginalText Is NULL Begin Set @Output = @Output + Soundex(Left(@OriginalText, PatIndex('%[ ]%', @OriginalText + ' ')-1)) Set @OriginalText = Right(@OriginalText, NullIf(Len(@OriginalText) - PatIndex('%[ ]%', @OriginalText + ' '), -1)) End Return @Output End GO select * from Doctors.dbo.PatientStatus GO CREATE INDEX [by_StatusID] ON Doctors.dbo.PatientStatus ([StatusID]) GO delete from Doctors.dbo.PatientStatus INSERT INTO Doctors.dbo.PatientStatus (StatusID,StatusDesc) VALUES('0','UnAttended') INSERT INTO Doctors.dbo.PatientStatus (StatusID,StatusDesc) VALUES('1','Attended') INSERT INTO Doctors.dbo.PatientStatus (StatusID,StatusDesc) VALUES('2','Ongoing') INSERT INTO Doctors.dbo.PatientStatus (StatusID,StatusDesc) VALUES('3','Another') INSERT INTO Doctors.dbo.PatientStatus (StatusID,StatusDesc) VALUES('4','Referred') INSERT INTO Doctors.dbo.PatientStatus (StatusID,StatusDesc) VALUES('5','Failed') INSERT INTO Doctors.dbo.PatientStatus (StatusID,StatusDesc) VALUES('6','APE') /****** USE [Medicare] GO CREATE NONCLUSTERED INDEX [IX_tbMedPackage] ON [dbo].[tbMedPackage] ( [PackageID] ASC, [IsAllowSlashing] ASC, [Status] ASC, [isAllowSecondCR] ASC, [isACR] ASC, [canMultiple] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ******/ USE [Medicare] GO CREATE NONCLUSTERED INDEX [by_PackageID] ON [dbo].[tbMedPackage] ( [PackageID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [by_Status_PackageID] ON [dbo].[tbMedPackage] ( [Status] ASC, [PackageID] ASC ) GO USE [Doctors] GO /****** Object: Table [dbo].[SOAP] Script Date: 08/05/2014 18:57:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SOAP]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [HospNum] [varchar](10) NULL, [IDNum] [varchar](10) NULL, [DoctorID] [varchar](10) NULL, [SOAPtxt] [text] NULL, [SOAPDate] [datetime] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO USE [Doctors] GO /****** Object: Table [dbo].[SOAPTemplate] Script Date: 08/05/2014 18:57:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SOAPTemplate]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Description] [varchar](250) NULL, [Category] [varchar](10) NULL, [ISMain] [varchar](1) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[ObsHist] Script Date: 08/05/2014 18:59:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ObsHist]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [HospNum] [varchar](10) NULL, [IDNum] [varchar](10) NULL, [GNum] [varchar](10) NULL, [GYear] [varchar](10) NULL, [Gender] [varchar](10) NULL, [FTPT] [varchar](10) NULL, [NSD] [varchar](10) NULL, [Forecep] [varchar](10) NULL, [BWKg] [varchar](10) NULL, [CX] [varchar](10) NULL, [Living] [varchar](10) NULL ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [by_StatusID] ON [dbo].[PatientStatus] ( [StatusID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE INDEX [by_IDNum] ON Doctors..DocNotes ([IDNum]) GO CREATE INDEX [by_IDNum] ON Doctors..SOAP ([IDNum]) GO CREATE INDEX [by_IDNum] ON Doctors..DOC_Orders ([IDNum]) GO CREATE INDEX [by_IDNum] ON Doctors..DOC_Notes ([IDNum]) GO CREATE INDEX [by_HospNum] ON Doctors..Allergies ([HospNum]) GO CREATE INDEX [by_HospNum] ON Doctors..SocialHistory ([HospNum]) GO CREATE INDEX [by_HospNum] ON Doctors..VitalSigns ([HospNum]) GO CREATE INDEX [by_HospNum] ON Doctors..FamilyHistory ([HospNum]) GO CREATE INDEX [by_HospNum] ON Doctors..MedicalHistory ([HospNum]) GO CREATE INDEX [by_HospNum] ON Doctors..DOC_Immunization ([HospNum]) GO CREATE INDEX [by_HospNum] ON Doctors..ObsHist ([HospNum]) GO CREATE INDEX [by_HospNum] ON REGISTER.dbo.tbMedicalHistory ([HospNum]) GO CREATE INDEX [by_Common] ON [Build_FIle].[dbo].[tbIcdDiagMain]([ISCOMMON]) GO USE [STATION] GO /****** Object: Table [dbo].[tbNurseHomeMeds] Script Date: 03/13/2014 15:43:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbNurseHomeMeds]( [Hospnum] [varchar](10) NOT NULL, [IDnum] [varchar](10) NOT NULL, [PatientType] [varchar](1) NULL, [ItemID] [varchar](5) NULL, [Amount] [float] NULL, [Quantity] [float] NULL, [Dosage] [varchar](8) NULL, [SectionID] [varchar](4) NULL, [RequestDate] [datetime] NULL, [RevenueID] [varchar](2) NULL, [DoctorID] [varchar](10) NULL, [RecordStatus] [varchar](1) NULL, [UserID] [varchar](10) NULL, [LabNum] [varchar](10) NULL, [RequestNum] [varchar](10) NULL, [ReferenceNum] [varchar](10) NULL, [Remarks] [varchar](500) NULL, [StationID] [varchar](10) NULL, [Stat] [varchar](1) NULL, [dcrdate] [smalldatetime] NULL, [isGeneric] [bit] NULL, [SpecimenID] [varchar](4) NULL, [Dialysis] [varchar](1) NULL, [Transplant] [varchar](1) NULL, [AMPickup] [bit] NULL, [DoctorName] [varchar](50) NULL, [Printed] [varchar](1) NULL, [isOpened] [bit] NOT NULL, [Consumption_Date] [datetime] NULL, [ReaderID] [varchar](10) NULL, [Revoke_UserID] [varchar](10) NULL, [Revoke_StationID] [varchar](10) NULL, [Revoke_Date] [datetime] NULL, [IsPortable] [bit] NULL, [Reason] [varchar](100) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N''''' - Pending; X-Processed; R - Cancelled' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbNurseHomeMeds', @level2type=N'COLUMN',@level2name=N'RecordStatus' GO ALTER TABLE [dbo].[tbNurseHomeMeds] ADD CONSTRAINT [DF_tbNurseHomeMeds_RecordStatus] DEFAULT ('') FOR [RecordStatus] GO ALTER TABLE [dbo].[tbNurseHomeMeds] ADD CONSTRAINT [DF_tbNurseHomeMeds_Dialysis] DEFAULT ((0)) FOR [Dialysis] GO ALTER TABLE [dbo].[tbNurseHomeMeds] ADD CONSTRAINT [DF_tbNurseHomeMeds_Transplant] DEFAULT ((0)) FOR [Transplant] GO ALTER TABLE [dbo].[tbNurseHomeMeds] ADD CONSTRAINT [DF_tbNurseHomeMeds_AMPickup] DEFAULT ((0)) FOR [AMPickup] GO ALTER TABLE [dbo].[tbNurseHomeMeds] ADD CONSTRAINT [DF_tbNurseHomeMeds_isOpened] DEFAULT ((0)) FOR [isOpened] GO USE [STATION] GO /****** Object: StoredProcedure [dbo].[Nurse_AppendHomeMeds] Script Date: 03/13/2014 15:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /****** Object: Stored Procedure dbo.Nurse_AppendHomeMeds Script Date: 07/01/2000 1:19:56 AM ******/ CREATE PROCEDURE [dbo].[Nurse_AppendHomeMeds] @Hospnum As Varchar(10), @IDNum As Varchar(10), @PatientType As Varchar(1), @Item As Varchar(5), @Amount As Float, @Quantity As float, @Dosage As Varchar(8), @SectionID As Varchar(4), @RequestDate As Datetime, @RevenueID As Varchar(4), @DoctorID As Varchar(10), @RecordStatus As Varchar(1) = '', @UserID As Varchar(10), @Remarks As Varchar(100), @Description As Varchar(100), @StationID As Varchar(10), @Stat As Varchar(1), @IsGeneric As Bit, @SpecimenID As varchar(4) = '', @Transplant As varchar(1) = '', @Dialysis As varchar(1) = '', @IsAmPickUp as bit = 0, @DoctorName AS Varchar(50)=NULL, @IsPortable As Bit=0, @Reason as varchar(100) = NULL, @Reason2 as varchar(100) = NULL, @DOTSClass int = 0, @DOTSReason int = 0, @PulmonarySite varchar(100) = '' AS Declare @Refnum As Varchar(10); Declare @Labnum As Varchar(8); Declare @ChargeSlip As Varchar(8); Declare @ReasonRadio as varchar(100); set @ReasonRadio = @Reason if isnull(@ReasonRadio,'') = '' begin set @ReasonRadio = @Reason2 end; Begin Tran IF substring(@RevenueID,1,2) = 'PH' Begin Select @ChargeSlip = rtrim(Convert(Varchar(8),ChargeSlip)) from tbNursePHSlip Update tbNursePHSlip Set ChargeSlip = ChargeSlip + 1; Set @RefNum = 'PH'+@ChargeSlip; End; Insert STATION..tbNurseHomeMeds (Hospnum,IDNum ,PatientType ,ItemID ,Amount,Quantity ,Dosage,SectionID,RequestDate, RevenueID ,DoctorID ,RecordStatus,UserID ,RequestNum ,LabNum,Remarks,StationID,Stat,IsGeneric,SpecimenID,Dialysis,Transplant,AmPickUp,DoctorName,Reason) Values (@Hospnum,@IDNum ,@PatientType ,@Item ,@Amount,@Quantity ,@Dosage,@SectionID,GETDATE(), @RevenueID ,@DoctorID ,@RecordStatus,@UserID ,@Refnum ,@LabNum,@Remarks,@StationID,@Stat,@IsGeneric,@SpecimenID,@Dialysis,@Transplant,@IsAmPickUp,@DoctorName,@ReasonRadio) ; If @@error<>0 begin goto abort_save; end; Commit Tran; Return 0; abort_save: Rollback Tran; Return -10001; GO USE [STATION] GO /****** Object: StoredProcedure [dbo].[List_AllTakeHomeMeds] Script Date: 03/13/2014 15:37:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[List_AllTakeHomeMeds] @HospNum As Varchar(10), @IDNum As Varchar(10), @DepartmentCode As Varchar(3) AS Begin SELECT DISTINCT TBNURSEHOMEMEDS.RevenueID As Dept, TBNURSEHOMEMEDS.ItemID AS Item#, tbCoOtherRevenue.ItemName + ' (' + tbCoOtherRevenue.ItemDesc + ')' AS Description, convert(char,TBNURSEHOMEMEDS.Quantity,0) AS QTY, convert(varchar(1000),TBNURSEHOMEMEDS.AMOUNT) AS Amount, convert(varchar(25),TBNURSEHOMEMEDS.REQUESTDATE,0) AS [Charge Date/Time] , tbPassWordMain.Initial AS Initial, CASE when isnull(TBNURSEHOMEMEDS.recordstatus,'') = '' then 'PENDING' WHEN TBNURSEHOMEMEDS.RECORDSTATUS = 'R' THEN 'REVOKED' WHEN TBNURSEHOMEMEDS.RECORDSTATUS = 'X' THEN 'PROCESSED' END AS Status, tbnursehomemeds.requestnum FROM STATION..TBNURSEHOMEMEDS AS TBNURSEHOMEMEDS LEFT OUTER JOIN INVENTORY..tbInvMaster AS tbCoOtherRevenue ON TBNURSEHOMEMEDS.ItemID = tbCoOtherRevenue.ItemID LEFT OUTER JOIN PASSWORD..tbPasswordMaster AS tbPasswordMain ON RTRIM(TBNURSEHOMEMEDS.UserID) = RTRIM(tbPasswordMain.EmployeeID) WHERE TBNURSEHOMEMEDS.IDNum = @IDNum and TBNURSEHOMEMEDS.RevenueID = 'PH' ORDER By Description End GO USE [Radiology] GO /****** Object: Index [by_RefNum] Script Date: 04/01/2014 14:28:49 ******/ CREATE NONCLUSTERED INDEX [by_RefNum] ON [dbo].[tbRISPatientQueueNo] ( [Refnum] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO USE [Radiology] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbRISPatientQueueNo]( [Hospnum] [varchar](8) NULL, [IDNum] [varchar](50) NULL, [PatientNum] [int] NULL, [Status] [varchar](1) NULL, [UserCode] [varchar](30) NULL, [revenueid] [varchar](2) NULL, [Requestnum] [varchar](15) NULL, [Refnum] [varchar](15) NULL, [RequestDate] [varchar](15) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spRadio_SaveQueue] @HospNum as varchar(15), @Idnum as varchar(15), @Requestnum as varchar(15), @UserCode as varchar(15), @Revenueid as varchar(2), @Refnum as varchar(15) = '' as declare @NewPatientNum as int, @ExistingPatientNum as varchar(10), @PatientNum as varchar(10) set @NewPatientNum = (select MAX(PatientNum + 1) from tbRISPatientQueueNo where RequestDate between convert(varchar(10), getdate(), 101) and convert(varchar(10), getdate(), 101) ) set @ExistingPatientNum = (select isnull(PatientNum,'') from tbRISPatientQueueNo where refnum = @Refnum and RequestDate between convert(varchar(10), getdate(), 101) and convert(varchar(10), getdate(), 101) ) set @PatientNum = isnull(@ExistingPatientNum,@NewPatientNum) if ISNUMERIC(@idnum) = 0 begin If exists(select revenueid from Radiology..tbRadiologyRevenues where RevenueID = @Revenueid) if not exists(select * from Radiology..tbRISPatientQueueNo where --hospnum = @HospNum and Requestnum = @Requestnum and revenueid = @Revenueid) begin insert into tbRISPatientQueueNo(Hospnum,IDNum,PatientNum,Status,UserCode,Revenueid, requestnum,refnum,RequestDate) VALUES(@hospnum,@idnum,isnull(@PatientNum,1),'',@usercode,@revenueid, @Requestnum,@Refnum,CONVERT(varchar(10),getdate(),101)) end end GO GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spRadio_SaveOutCharge] @Type AS varChar(1), @IDNum AS varChar(10), @ItemID AS varChar(8), @Quantity as float, @Amount AS Float, @HospNum AS varChar(8), @DoctorID AS varChar(4), @UserID AS varChar(8), @RefNum AS varChar(10), @RoomID as varchar(10), @Film1 AS Int, @Film2 AS Int, @Film3 AS Int, @Film4 AS Int, @Film5 AS Int, @Film6 as int, @Film7 as int, @NurseRemarks as varchar(50), @DoctorName as varchar(50) = null, @Portable as varchar(1), @RevenueID AS varChar(2) = '', @NurseRequestNum as varchar(10) = null, @Media varchar(150) = '', @Creatinine varchar(100) = '', @EKG varchar(50) = '', @Oximeter varchar(50) = '', @Particular varchar(200) = '', @Relevant varchar (200) = '', @Precaution varchar(200) = '', @ImagingReason varchar(500) = '', @SurgicalReason varchar(500) = '', @Stat as varchar(1) = '0', @StatVal as varchar(20) = '0', @ReaderID as varchar(10) = '', @Transplant as varchar(5) = '', @isEmailResult as varchar(5) = '', @queuenum as varchar(5) = '' as DECLARE @RequestNum AS varChar(8), @DrCr AS varChar(1), @ItemDesc as varchar(50), @strPatientType as varchar(1), @AccountNum as varchar(10) set @AccountNum = isnull(dbo.fn_GetAccountNum(@IDNum),'') set @strPatientType = isnull(dbo.fn_PatientType(@IDNum),'P') If @DoctorID <> '0' Begin set @DoctorName = null; End set @ImagingReason = replace(cast(@ImagingReason as varchar(max)),'`','''') set @SurgicalReason = replace(cast(@SurgicalReason as varchar(max)),'`','''') IF @Type = '1' /* CT-Scan */ BEGIN Set @ItemDesc = (Select CTExam from Build_File..tbCoCTExam where CtExamID = @ItemID) SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM tbCTOpd UPDATE tbCTOpd SET RequestNum = RequestNum + 1 SET @DrCr = 'D' INSERT INTO tbCTRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, NurseRemarks, DoctorName, RevenueID, Portable, NurseRequest, Media,Creatinine, EKG, Oximeter,Particular, Relevant,ImagingReason,SurgicalReason, Stat, StatVal,ProfID) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @RefNum, @Quantity, @NurseRemarks, @DoctorName, @RevenueID, @Portable, @NurseRequestNum, @Media,@Creatinine, @EKG, @Oximeter, @Particular, @Relevant,@ImagingReason,@SurgicalReason, @Stat, @StatVal,@ReaderID) END ELSE IF @Type = '2' /* XRay */ BEGIN if @RevenueID = 'XR' Begin Set @ItemDesc = (Select XrayExam from Build_File..tbCoXrayExam where XrayExamID = @ItemID) End; else Begin Set @ItemDesc = (Select OtherRevenue from Build_File..tbCoOtherRevenue where OtherRevenueID = @ItemID) End; SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM tbXROpd UPDATE tbXROpd SET RequestNum = RequestNum + 1 -- SET @RevenueID = 'XR' SET @DrCr = 'D' INSERT INTO tbXRRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, Amount, SWFIN, [By], Film1, Film2, Film3, Film4, Film5, Film6, Film7, RoomID, Refnum, Quantity, NurseRemarks, DoctorName, RevenueID, Portable, NurseRequest,ImagingReason,SurgicalReason, Stat, StatVal,ProfID) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, @Amount, 'X', @UserID, @Film1, @Film2, @Film3, @Film4, @Film5, @Film6, @Film7, @RoomID, @RefNum, @Quantity, @NurseRemarks,@DoctorName, @RevenueID,@Portable,@NurseRequestNum,@ImagingReason,@SurgicalReason, @Stat, @StatVal,@ReaderID) END ELSE IF @Type = '3' /* Ultrasound */ BEGIN Set @ItemDesc = (Select UltraExam from Build_File..tbCoUltraExam where UltraExamID = @ItemID) SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM tbULOpd UPDATE tbULOpd SET RequestNum = RequestNum + 1 SET @DrCr = 'D' INSERT INTO tbULRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, NurseRemarks, DoctorName, RevenueID, Portable, NurseRequest, ImagingReason, SurgicalReason, Stat, StatVal,ProfID) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @NurseRemarks, @DoctorName, @RevenueID, @Portable, @NurseRequestNum, @ImagingReason, @SurgicalReason, @Stat, @StatVal,@ReaderID) END ELSE IF @Type = '4' /* MRI */ BEGIN Set @ItemDesc = (Select OtherRevenue from Build_File..tbCoOtherRevenue where OtherRevenueID = @ItemID and OtherRevenueID in ('MI','MS','RI')) --SELECT @RequestNum = RTRIM(Convert(varChar(8),RequestNum)) FROM tbMRIRequestNum --UPDATE tbMRIRequestNum SET RequestNum = RequestNum + 1 SELECT @RequestNum = RTRIM(Convert(varChar(8),RequestNum)) FROM tbMRIOpd UPDATE tbMRIOpd SET RequestNum = RequestNum + 1 SET @DrCr = 'D' INSERT INTO tbMRIRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, NurseRemarks, DoctorName, RevenueID, Portable, NurseRequest, ImagingReason, SurgicalReason, Stat, StatVal,ProfID) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @NurseRemarks, @DoctorName, @RevenueID, @Portable, @NurseRequestNum, @ImagingReason, @SurgicalReason, @Stat, @StatVal,@ReaderID) END ELSE IF @Type = '5' /* Mammogram */ BEGIN Set @ItemDesc = (Select OtherRevenue from Build_File..tbCoOtherRevenue where OtherRevenueID = @ItemID and OtherRevenueID in ('MM','MG')) SELECT @RequestNum = RTRIM(Convert(varChar(8),RequestNum)) FROM tbMammoSlip UPDATE tbMammoSlip SET RequestNum = RequestNum + 1 SET @DrCr = 'D' INSERT INTO tbMammoMaster (RequestNum, Transdate, HospNum, IDNum, ItemID, RequestDoctorCode, SWMRK, UserID, Amount, RoomID, RefNum, Quantity, NurseRemarks, DoctorName, RevenueID, Portable, NurseRequest, ImagingReason, SurgicalReason, Stat, StatVal,profID) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @NurseRemarks, @DoctorName, @RevenueID, @Portable, @NurseRequestNum, @ImagingReason, @SurgicalReason, @Stat, @StatVal,@ReaderID) END ELSE IF @Type = '6' /* OB Ultrasound */ BEGIN Set @ItemDesc = (Select OtherRevenue from Build_File..tbCoOtherRevenue where OtherRevenueID = @ItemID and OtherRevenueID in ('WH','PG','O1')) SELECT @RequestNum = RTRIM(Convert(varChar(8),RequestNum)) FROM tbOBULOpd UPDATE tbOBULOpd SET RequestNum = RequestNum + 1 SET @DrCr = 'D' INSERT INTO tbOBULRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, NurseRemarks, DoctorName, RevenueID, Portable, NurseRequest, ImagingReason, SurgicalReason, Stat, StatVal,profID) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @NurseRemarks, @DoctorName, @RevenueID, @Portable, @NurseRequestNum, @ImagingReason, @SurgicalReason, @Stat, @StatVal,@ReaderID) END ELSE IF @Type = '7' /* Nuclear */ BEGIN Set @ItemDesc = (Select Description from Radiology..tbBillExamListing where ItemID = @ItemID and RevenueID = 'NU') SELECT @RequestNum = RTRIM(Convert(varChar(8),RequestNum)) FROM tbNucSlip UPDATE tbNucSlip SET RequestNum = RequestNum + 1 SET @DrCr = 'D' INSERT INTO tbNucMaster (RequestNum, Transdate, HospNum, IDNum, ItemID, RequestDoctorCode, RequestStatus, UserID, Amount, RoomID, RefNum, Quantity, NurseRemarks, DoctorName, RevenueID, Portable, NurseRequest, ImagingReason, SurgicalReason, Stat, StatVal) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @NurseRemarks, @DoctorName, @RevenueID, @Portable, @NurseRequestNum, @ImagingReason, @SurgicalReason, @Stat, @StatVal) END ELSE --IF @Type = '8' /* OTHER REVENUE */ BEGIN Set @ItemDesc = (Select Description from Radiology..tbBillExamListing where ItemID = @ItemID and RevenueID = @RevenueID) SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM tbradiologyrevenues where RevenueId = (select RevenueID from Radiology..tbRadiologyRevenues where GType = @Type) UPDATE tbradiologyrevenues SET RequestNum = RequestNum + 1 where RevenueId = (select RevenueID from Radiology..tbRadiologyRevenues where GType = @Type) SET @DrCr = 'D' INSERT INTO TbRequestMaster (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, Amount, SWFIN, [By], RoomID, Refnum, Quantity, NurseRemarks, DoctorName, RevenueID, NurseRequest,ImagingReason,SurgicalReason, Stat, StatVal,profid) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, @Amount, 'X', @UserID, @RoomID, @RefNum, @Quantity, @NurseRemarks,@DoctorName, @RevenueID,@NurseRequestNum,@ImagingReason,@SurgicalReason, @Stat, @StatVal,@ReaderID) END IF LEFT(@RefNum,2) <> 'OR' begin INSERT INTO billing..tbBillOPDailyOut (HospNum, Refnum, IDNum, Transdate, ItemID, Amount, DrCr, RevenueID, RoomID, UserID, RequestDocID, Quantity, RequestNum, DoctorName) --PatientType, CDFAccountNum) --added 11.11.2003 11:34 VALUES (@Hospnum, @Refnum, @IDNum, Getdate(), @ItemID, @Amount, @DrCr, @RevenueID, @RoomID, @UserID, @DoctorID, @Quantity, @RequestNum, @DoctorName) --@strPatientType, @AccountNum) --added 11.11.2003 11:34) end -- * Insert only if post charges from Nursing Station IF EXISTS(SELECT * FROM station..tbNurseLogBook WHERE IDNum = @IDNum --HospNum = @HospNum AND AND RevenueID = @RevenueID AND ItemID = @ItemID and RequestNum = @NurseRequestNum) BEGIN UPDATE station..tbNurseLogBook SET RecordStatus = 'X', ProcessBy = @UserID, ProcessDate = GETDATE(), ReferenceNum = @RefNum WHERE IDNum = @IDNum --HospNum = @HospNum AND AND RevenueID = @RevenueID AND ItemID = @ItemID and RequestNum= @NurseRequestNum END ELSE BEGIN INSERT INTO station..tbNurseLogBook (HospNum, IDNum, PatientType, RevenueID, RequestDate, ItemID, Description, Quantity, Amount, RecordStatus, ReferenceNum, ProcessBy, ProcessDate, RequestNum, Remarks) VALUES (@HospNum, @IDNum, 'I', @RevenueID, GETDATE(), @ItemID, @ItemDesc, @Quantity, @Amount, 'X', @RefNum, @UserID, GETDATE(), @NurseRequestNum, 'Manually Posted') END --===ADDED BY JPB 06/26/2013 (FOR PROCESSED ER REQUESTS NOT REMOVED IN THE PENDING LIST) IF EXISTS(SELECT * FROM station..tbNurseCommunicationFile WHERE IDNum = @IDNum --HospNum = @HospNum AND AND RevenueID = @RevenueID AND ItemID = @ItemID) --and isnull(RecordStatus,'') = '') -- RequestNum = @NurseRequestNum) BEGIN UPDATE station..tbNurseCommunicationFile SET RecordStatus = 'X', ReferenceNum = @RefNum WHERE IDNum = @IDN um --HospNum = @HospNum AND AND RevenueID = @RevenueID AND ItemID = @ItemID and isnull(RecordStatus,'') in ('','P')-- RequestNum = @NurseRequestNum END --============================================================================================ if (select Settingvalue from tbRISSettings where SettingID = '10') = '1' begin --if @@ERROR <> 0 then goto exec spRadio_SAveQueue @HospNum,@IDNum,@RequestNum,@UserID,@RevenueID,@RefNum end GO USE [Billing] GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spCash_SaveRadioMaster] @AssessID int AS declare @IDNum AS varChar(10), @ItemID AS varChar(8), @Quantity as float, @Amount AS Float, @HospNum AS varChar(8), @DoctorID AS varChar(4), @UserID AS varChar(8), @RefNum AS varChar(15), @RoomID as varchar(10), @DepartmentID as varchar(2), @DoctorName as varchar(50), @ReaderID as varchar(50), @ReaderFee as float DECLARE @RequestNum AS varChar(8), @RevenueID AS varChar(2), @DrCr AS varChar(1), @SlipNum as varchar(10), @DiscountType as varchar(2), @NetAmount as money, @EmailResult as varchar(1) DECLARE @ErrorCode int SET @ErrorCode = 0 /* SELECT @DepartmentID = DepartmentID, @RevenueID = RevenueID, @ItemID = ItemID, @Quantity = Quantity, @Amount = Amount, @HospNum = HospNum, @DoctorID = RequestDocID, @UserID = UserID, @RefNum = RefNum, @RoomID = RoomID, @DiscountType = DiscountType, @NetAmount = NetAmount FROM BILLING..tbCashCommunication WHERE AssessID = @AssessID; */ SELECT @IDNum = IDNum, @DepartmentID = DepartmentID, @RevenueID = RevenueID, @ItemID = ItemID, @Quantity = Quantity, @Amount = Amount, @HospNum = HospNum, @DoctorID = RequestDocID, @UserID = UserID, @ReaderID = ProfID1, @ReaderFee = ProfFee1, @DiscountType = DiscountType, @NetAmount = NetAmount, @EmailResult = EmailResult FROM BILLING..tbCashAssessment WHERE AssessID = @AssessID; select @RefNum = Refnum From Billing..tbCashCommunication Where AssessID = @AssessID SELECT @DoctorName = DoctorName, @SlipNum = RefNum FROM BILLING..tbCashAssessment WHERE AssessID = @AssessID IF LEN(isnull(@DepartmentID, '')) = 0 BEGIN SET @DepartmentID = @RevenueID END IF @DepartmentID = 'CT' /* CT-Scan */ BEGIN SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM RADIOLOGY..tbCTOpd UPDATE RADIOLOGY..tbCTOpd SET RequestNum = RequestNum + 1 SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode SET @DrCr = 'D' INSERT INTO RADIOLOGY..tbCTRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, RevenueID, DoctorName, SlipNum, DiscountType, NetAmount,EmailResult) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @RefNum, @Quantity, @RevenueID, @DoctorName, @SlipNum, @DiscountType, @NetAmount,@EmailResult); SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode END ELSE IF @DepartmentID = 'XR' /* XRay */ BEGIN SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM RADIOLOGY..tbXROpd UPDATE RADIOLOGY..tbXROpd SET RequestNum = RequestNum + 1 SET @DrCr = 'D' SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode INSERT INTO RADIOLOGY..tbXRRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, Amount, SWFIN, [By], RoomID, Refnum, Quantity, RevenueID, DoctorName, SlipNum, DiscountType, NetAmount,emailresult) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, @Amount, 'X', @UserID, @RoomID, @RefNum, @Quantity, @RevenueID, @DoctorName, @SlipNum, @DiscountType, @NetAmount,@EmailResult) SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode END ELSE IF @DepartmentID = 'US' /* Ultrasound */ BEGIN SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM RADIOLOGY..tbULOpd UPDATE RADIOLOGY..tbULOpd SET RequestNum = RequestNum + 1 SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode SET @DrCr = 'D' INSERT INTO RADIOLOGY..tbULRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, RevenueID, DoctorName, SlipNum, DiscountType, NetAmount,EmailResult) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @RevenueID, @DoctorName, @SlipNum, @DiscountType, @NetAmount,@EmailResult) SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode END ELSE IF @DepartmentID = 'WH' /*WOMENS HEALTH IMAGING CENTER*/ BEGIN SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM RADIOLOGY..TBOBULOPD UPDATE RADIOLOGY..TBOBULOPD SET RequestNum = RequestNum + 1 SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode SET @DrCr = 'D' INSERT INTO RADIOLOGY..tbOBULRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, RevenueID, DoctorName, SlipNum, DiscountType, NetAmount,EmailResult) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @RevenueID, @DoctorName, @SlipNum, @DiscountType, @NetAmount,@EmailResult) SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode END ELSE /*GLOBAL REQUESTMASTER TABLE FOR R.I.S*/ BEGIN SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM RADIOLOGY..tbradiologyrevenues where RevenueId = @RevenueID UPDATE RADIOLOGY..tbradiologyrevenues SET RequestNum = RequestNum + 1 where RevenueId = @RevenueID SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode SET @DrCr = 'D' INSERT INTO RADIOLOGY..TbRequestMaster (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, RevenueID, DoctorName, SlipNum, DiscountType, NetAmount,EmailResult) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @RefNum, @Quantity, @RevenueID, @DoctorName, @SlipNum, @DiscountType, @NetAmount,@EmailResult); SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode END UPDATE TBCASHCOMMUNICATION SET RECORDSTATUS = 'Y' WHERE ASSESSID = @AssessID AND ISNULL(RECORDSTATUS, '') = ''; SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode --if (select Settingvalue from Radiology..tbRISSettings where SettingID = '10') = '1' --begin -- --if @@ERROR <> 0 then goto -- exec Radiology..spRadio_SaveQueue @HospNum,@IDNum,@RequestNum,@UserID,@RevenueID,@RefNum --end --SET @ErrorCode = @@ERROR --IF @ErrorCode <> 0 -- RETURN @ErrorCode GO USE [Billing] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spCash_SaveMRI] @AssessID int AS declare @IDNum AS varChar(10), @ItemID AS varChar(8), @Quantity as float, @Amount AS Float, @HospNum AS varChar(8), @DoctorID AS varChar(4), @UserID AS varChar(8), @RefNum AS varChar(10), @RoomID as varchar(10), @DepartmentID as varchar(2), @DoctorName as varchar(50) DECLARE @RequestNum AS varChar(8), @RevenueID AS varChar(2), @DrCr AS varChar(1), @SlipNum as varchar(10), @DiscountType as varchar(2), @NetAmount as money DECLARE @ErrorCode int SET @ErrorCode = 0 SELECT @RefNum = RefNum FROM BILLING..tbCashCommunication WHERE AssessID = @AssessID; SELECT @DepartmentID = DepartmentID, @RevenueID = RevenueID, @ItemID = ItemID, @Quantity = Quantity, @Amount = Amount, @HospNum = HospNum, @DoctorID = RequestDocID, @UserID = UserID, @RefNum = ISNULL(@RefNum, ORNumber), @RoomID = 'OPD', @DiscountType = DiscountType, @NetAmount = NetAmount, @IDNum = IDNum, @DoctorName = DoctorName, @SlipNum = RefNum FROM BILLING..tbCashAssessment WHERE AssessID = @AssessID; IF @DepartmentID= 'MI' BEGIN SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM RADIOLOGY..tbMRIOpd UPDATE RADIOLOGY..tbMRIOpd SET RequestNum = RequestNum + 1 SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode SET @DrCr = 'D' INSERT INTO RADIOLOGY..tbMRIRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, RevenueID, DoctorName, SlipNum, DiscountType, NetAmount) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @RevenueID, @DoctorName, @SlipNum, @DiscountType, @NetAmount) SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode END UPDATE TBCASHCOMMUNICATION SET RECORDSTATUS = 'Y' WHERE ASSESSID = @AssessID AND ISNULL(RECORDSTATUS, '') = ''; SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode if (select Settingvalue from Radiology..tbRISSettings where SettingID = '10') = '1' begin --if @@ERROR <> 0 then goto exec Radiology..spRadio_SaveQueue @HospNum,@IDNum,@RequestNum,@UserID,@RevenueID,@RefNum end --SET @ErrorCode = @@ERROR --IF @ErrorCode <> 0 -- RETURN @ErrorCode RETURN @ErrorCode GO USE [Billing] GO /****** Object: StoredProcedure [dbo].[spCash_SaveCenterForWomenMaster] Script Date: 01/20/2014 16:55:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spCash_SaveCenterForWomenMaster] @AssessID int AS declare @IDNum AS varChar(10), @ItemID AS varChar(8), @Quantity as float, @Amount AS Float, @HospNum AS varChar(8), @DoctorID AS varChar(4), @UserID AS varChar(8), @RefNum AS varChar(10), @RoomID as varchar(10), @DepartmentID as varchar(2), @DoctorName as varchar(50) DECLARE @RequestNum AS varChar(8), @RevenueID AS varChar(2), @DrCr AS varChar(1), @SlipNum as varchar(10), @DiscountType as varchar(2), @NetAmount as money DECLARE @ErrorCode int SET @ErrorCode = 0 SELECT @RefNum = RefNum FROM BILLING..tbCashCommunication WHERE AssessID = @AssessID; SELECT @DepartmentID = DepartmentID, @RevenueID = RevenueID, @ItemID = ItemID, @Quantity = Quantity, @Amount = Amount, @HospNum = HospNum, @DoctorID = RequestDocID, @UserID = UserID, @RefNum = ISNULL(@RefNum, ORNumber), @RoomID = 'OPD', @DiscountType = DiscountType, @NetAmount = NetAmount, @IDNum = IDNum, @DoctorName = DoctorName, @SlipNum = RefNum FROM BILLING..tbCashAssessment WHERE AssessID = @AssessID; SELECT @RequestNum = RTRIM(Convert(varChar(8), RequestNum)) FROM RADIOLOGY..tbOBULOpd UPDATE RADIOLOGY..tbOBULOpd SET RequestNum = RequestNum + 1 SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode SET @DrCr = 'D' INSERT INTO RADIOLOGY..tbOBULRequest (RequestNum, Transdate, HospNum, IDNum, Code, RequestDoctorCode, SWFIN, [By], Amount, RoomID, RefNum, Quantity, RevenueID, DoctorName, SlipNum, DiscountType, NetAmount) VALUES (@RequestNum, GetDate(), @Hospnum, @IDNum, @ItemID, @DoctorID, 'X', @UserID, @Amount, @RoomID, @Refnum, @Quantity, @RevenueID, @DoctorName, @SlipNum, @DiscountType, @NetAmount) SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode UPDATE TBCASHCOMMUNICATION SET RECORDSTATUS = 'Y' WHERE ASSESSID = @AssessID AND ISNULL(RECORDSTATUS, '') = ''; SET @ErrorCode = @@ERROR IF @ErrorCode <> 0 RETURN @ErrorCode if (select Settingvalue from Radiology..tbRISSettings where SettingID = '10') = '1' begin --if @@ERROR <> 0 then goto exec Radiology..spRadio_SaveQueue @HospNum,@IDNum,@RequestNum,@UserID,@RevenueID end --SET @ErrorCode = @@ERROR --IF @ErrorCode <> 0 -- RETURN @ErrorCode RETURN @ErrorCode GO USE [Radiology] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spRadio_SaveRetrieve] @Type AS varChar(1), @RequestNum AS varChar(10), @RadCode AS varChar(4), @DoctorID as varchar(5), @Interpretation AS Text, @Remarks as Text, @RadCode2 as varchar(4), @ResultDate as varchar(10), @ExamDate as DateTime = null, @EditedByID as varchar(20) = null, @VerifyByID as varchar(20) = null, @VerifyDate as DateTime = null, @RadCode3 as varchar(4), @RadCode4 as varchar(4), @PreviousInterpretation as text = null, @Resultcode as varchar(5) = null, @RadTech as varchar(10) = '', @InterpretationPureText as text = '' AS Declare @EditedDate as DateTime, @revenueid as varchar(2); set @EditedDate = getdate() set @revenueid = (select revenueid from Radiology..tbRadiologyRevenues where GType = @Type) If isnull(@VerifyByID,'') = '' Begin set @VerifyByID = null; set @VerifyDate = null; End; else Begin set @VerifyDate = getdate(); end; IF @Type = '1' /* CT-Scan Department */ BEGIN UPDATE tbCTResult SET RadCode = @RadCode, RequestDoctorCode = @DoctorID, Interpretation = @Interpretation, Remarks = @Remarks, RadCode2 = @RadCode2, ResultDate = @ResultDate, ExamDate = @ExamDate, EditedByID = @EditedByID, EditedDate = @EditedDate, VerifyByID = @VerifyByID, VerifyDate = @VerifyDate, RadCode3 = @RadCode3, RadCode4 = @RadCode4, Resultcode = @Resultcode, RadTech = @RadTech, InterpretationPureText = @InterpretationPureText WHERE RequestNum = @RequestNum; UPDATE tbCTRequest SET RequestDoctorCode = @DoctorID WHERE RequestNum = @RequestNum; if (select isnull(convert(varchar(10),Interpretation1),'') from RADIOLOGY..tbCTResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbCTResult set Interpretation1 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation2),'') from RADIOLOGY..tbCTResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbCTResult set Interpretation2 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation3),'') from RADIOLOGY..tbCTResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbCTResult set Interpretation3 = @PreviousInterpretation where RequestNum = @RequestNum; END; END ELSE IF @Type = '2' /* XRay Department */ BEGIN UPDATE tbXRResult SET RadCode = @RadCode, RequestDoctorCode = @DoctorID, Interpretation = @Interpretation, Remarks = @Remarks, RadCode2 = @RadCode2, ResultDate = @ResultDate, ExamDate = @ExamDate, EditedByID = @EditedByID, EditedDate = @EditedDate, VerifyByID = @VerifyByID, VerifyDate = @VerifyDate, RadCode3 = @RadCode3, RadCode4 = @RadCode4, Resultcode = @Resultcode, RadTech = @RadTech, InterpretationPureText = @InterpretationPureText WHERE RequestNum = @RequestNum; UPDATE tbXRRequest SET RequestDoctorCode = @DoctorID WHERE RequestNum = @RequestNum; if (select isnull(convert(varchar(10),Interpretation1),'') from RADIOLOGY..tbXRResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbXRResult set Interpretation1 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation2),'') from RADIOLOGY..tbXRResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbXRResult set Interpretation2 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation3),'') from RADIOLOGY..tbXRResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbXRResult set Interpretation3 = @PreviousInterpretation where RequestNum = @RequestNum; END; END ELSE IF @Type = '3' /* Ultrasound Department */ BEGIN UPDATE tbULResult SET RadCode = @RadCode, RequestDoctorCode = @DoctorID, Interpretation = @Interpretation, Remarks = @Remarks, RadCode2 = @RadCode2, ResultDate = @ResultDate, ExamDate = @ExamDate, EditedByID = @EditedByID, EditedDate = @EditedDate, VerifyByID = @VerifyByID, VerifyDate = @VerifyDate, RadCode3 = @RadCode3, RadCode4 = @RadCode4, Resultcode = @Resultcode, RadTech = @RadTech, InterpretationPureText = @InterpretationPureText WHERE RequestNum = @RequestNum; UPDATE tbULRequest SET RequestDoctorCode = @DoctorID WHERE RequestNum = @RequestNum; if (select isnull(convert(varchar(10),Interpretation1),'') from RADIOLOGY..tbULResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbULResult set Interpretation1 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation2),'') from RADIOLOGY..tbULResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbULResult set Interpretation2 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation3),'') from RADIOLOGY..tbULResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbULResult set Interpretation3 = @PreviousInterpretation where RequestNum = @RequestNum; END; END ELSE IF @Type = '4' /* MRI Department */ BEGIN UPDATE tbMRIResult SET RadCode = @RadCode, RequestDoctorCode = @DoctorID, Interpretation = @Interpretation, Remarks = @Remarks, RadCode2 = @RadCode2, ResultDate = @ResultDate, ExamDate = @ExamDate, EditedByID = @EditedByID, EditedDate = @EditedDate, VerifyByID = @VerifyByID, VerifyDate = @VerifyDate, RadCode3 = @RadCode3, RadCode4 = @RadCode4, Resultcode = @Resultcode, RadTech = @RadTech, InterpretationPureText = @InterpretationPureText WHERE RequestNum = @RequestNum; UPDATE tbMRIRequest SET RequestDoctorCode = @DoctorID WHERE RequestNum = @RequestNum; UPDATE tbMRIRequest1 SET RequestDoctorCode = @DoctorID WHERE RequestNum = @RequestNum; if (select isnull(convert(varchar(10),Interpretation1),'') from RADIOLOGY..tbMRIResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbMRIResult set Interpretation1 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation2),'') from RADIOLOGY..tbMRIResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbMRIResult set Interpretation2 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation3),'') from RADIOLOGY..tbMRIResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbMRIResult set Interpretation3 = @PreviousInterpretation where RequestNum = @RequestNum; END; END ELSE IF @Type = '5' /* Mammography Department */ BEGIN UPDATE tbMammoResult SET RadCode = @RadCode, RequestDoctorCode = @DoctorID, Interpretation = @Interpretation, Remarks = @Remarks, RadCode2 = @RadCode2, ResultDate = @ResultDate, ExamDate = @ExamDate, EditedByID = @EditedByID, EditedDate = @EditedDate, VerifyByID = @VerifyByID, VerifyDate = @VerifyDate, RadCode3 = @RadCode3, RadCode4 = @RadCode4, Resultcode = @Resultcode, RadTech = @RadTech, InterpretationPureText = @InterpretationPureText WHERE RequestNum = @RequestNum; UPDATE tbMammoMaster SET RequestDoctorCode = @DoctorID WHERE RequestNum = @RequestNum; if (select isnull(convert(varchar(10),Interpretation1),'') from RADIOLOGY..tbMammoResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbMammoResult set Interpretation1 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation2),'') from RADIOLOGY..tbMammoResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbMammoResult set Interpretation2 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation3),'') from RADIOLOGY..tbMammoResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbMammoResult set Interpretation3 = @PreviousInterpretation where RequestNum = @RequestNum; END; END ELSE IF @Type = '6' /* OB Ultrasound */ BEGIN UPDATE tbOBULResult SET RadCode = @RadCode, RequestDoctorCode = @DoctorID, Interpretation = @Interpretation, Remarks = @Remarks, RadCode2 = @RadCode2, ResultDate = @ResultDate, ExamDate = @ExamDate, EditedByID = @EditedByID, EditedDate = @EditedDate, VerifyByID = @VerifyByID, VerifyDate = @VerifyDate, RadCode3 = @RadCode3, RadCode4 = @RadCode4, Resultcode = @Resultcode, RadTech = @RadTech, InterpretationPureText = @InterpretationPureText WHERE RequestNum = @RequestNum; UPDATE tbOBULRequest SET RequestDoctorCode = @DoctorID WHERE RequestNum = @RequestNum; if (select isnull(convert(varchar(10),Interpretation1),'') from RADIOLOGY..tbOBULResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbOBULResult set Interpretation1 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation2),'') from RADIOLOGY..tbOBULResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbOBULResult set Interpretation2 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation3),'') from RADIOLOGY..tbOBULResult where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbOBULResult set Interpretation3 = @PreviousInterpretation where RequestNum = @RequestNum; END; END ELSE IF @Type = '7' /* Nuclear Department */ BEGIN UPDATE tbNucLogBook SET RadCode = @RadCode, RequestDoctorCode = @DoctorID, Interpretation = @Interpretation, Remarks = @Remarks, RadCode2 = @RadCode2, ResultDate = @ResultDate, ExamDate = @ExamDate, EditedByID = @EditedByID, EditedDate = @EditedDate, VerifyByID = @VerifyByID, VerifyDate = @VerifyDate, RadCode3 = @RadCode3, RadCode4 = @RadCode4, Resultcode = @Resultcode, RadTech = @RadTech, InterpretationPureText = @InterpretationPureText WHERE RequestNum = @RequestNum; UPDATE tbNucMaster SET RequestDoctorCode = @DoctorID WHERE RequestNum = @RequestNum; if (select isnull(convert(varchar(10),Interpretation1),'') from RADIOLOGY..tbNucLogBook where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbNucLogBook set Interpretation1 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation2),'') from RADIOLOGY..tbNucLogBook where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbNucLogBook set Interpretation2 = @PreviousInterpretation where RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation3),'') from RADIOLOGY..tbNucLogBook where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbNucLogBook set Interpretation3 = @PreviousInterpretation where RequestNum = @RequestNum; END; END ELSE BEGIN UPDATE tbResultMaster SET RadCode = @RadCode, RequestDoctorCode = @DoctorID, Interpretation = @Interpretation, Remarks = @Remarks, RadCode2 = @RadCode2, ResultDate = @ResultDate, ExamDate = @ExamDate, EditedByID = @EditedByID, EditedDate = @EditedDate, VerifyByID = @VerifyByID, VerifyDate = @VerifyDate, RadCode3 = @RadCode3, RadCode4 = @RadCode4, Resultcode = @Resultcode, RadTech = @RadTech, InterpretationPureText = @InterpretationPureText, revenueid = @revenueID WHERE RequestNum = @RequestNum and revenueid = @revenueid; UPDATE tbRequestMaster SET RequestDoctorCode = @DoctorID WHERE RevenueID = @revenueid and RequestNum = @RequestNum; if (select isnull(convert(varchar(10),Interpretation1),'') from RADIOLOGY..tbResultMaster where revenueid = @revenueid and RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbResultMaster set Interpretation1 = @PreviousInterpretation where revenueid = @revenueid and RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation2),'') from RADIOLOGY..tbNucLogBook where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbResultMaster set Interpretation2 = @PreviousInterpretation where revenueid = @revenueid and RequestNum = @RequestNum; END; else if (select isnull(convert(varchar(10),Interpretation3),'') from RADIOLOGY..tbNucLogBook where RequestNum = @RequestNum) = '' BEGIN UPDATE RADIOLOGY..tbResultMaster set Interpretation3 = @PreviousInterpretation where revenueid = @revenueid and RequestNum = @RequestNum; END; END BEGIN if (select Settingvalue from Radiology..tbRISSettings where SettingID = '9') = '1' EXEC spRadio_SaveDraftVersion @Type,@RequestNum,@RadCode,@DoctorID,@Interpretation,@RadCode2, @ResultDate,@ExamDate,@VerifyByID,@VerifyDate END; if (select Settingvalue from Radiology..tbRISSettings where SettingID = '10') = '1' --RESULT PRINTING QUEUE KIOSK begin If isnull(@VerifyByID,'') <> '' begin UPDATE Radiology..tbRISPatientQueueNo SET Status = 'W' WHERE requestnum = @RequestNum and Revenueid = @revenueid end; end; GO USE [Patient_Data] GO /****** Object: Table [dbo].[tbPDFConverterSettings] Script Date: 05/26/2014 18:21:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbPDFConverterSettings]( [HospitalID] [int] NULL, [Title] [varchar](60) NULL, [Company] [varchar](100) NULL, [Address1] [varchar](70) NULL, [TinNo] [varchar](30) NULL, [PhoneNum] [varchar](30) NULL, [RecordOfficer] [varchar](50) NULL, [PediaAgeLimit] [int] NULL, [NewbornServiceID] [varchar](2) NULL, [DiedResultID] [varchar](1) NULL, [AutopsyResultID] [varchar](1) NULL, [CompanyLogo] [image] NULL, [SeniorDiscount] [float] NULL, [MTSServerName] [varchar](50) NULL, [ServerName] [varchar](50) NULL, [ByGeneric] [bit] NOT NULL, [CashEmployeeDiscount] [float] NULL, [CashEmployeeDiscount2] [float] NULL, [IsNursePrint] [bit] NOT NULL, [IsDRPrint] [bit] NOT NULL, [IsERprint] [bit] NOT NULL, [IsICPrint] [bit] NOT NULL, [IsNUPrint] [bit] NOT NULL, [IsORPrint] [bit] NOT NULL, [IsDIPrint] [bit] NOT NULL, [IsLBDirectCharging] [bit] NOT NULL, [IsXRDirectCharging] [bit] NOT NULL, [IsUSDirectCharging] [bit] NOT NULL, [IsCTDirectCharging] [bit] NOT NULL, [IsHSDirectCharging] [bit] NOT NULL, [RecordOfficerPosition] [varchar](50) NULL, [isERDirectcharging] [bit] NULL, [ConversionPath] [varchar](100) NULL, [FaxPath] [varchar](100) NULL, [LocalAccessNum] [int] NULL, [FaxWaitingTime] [int] NOT NULL, [WaitingTime] [int] NOT NULL, [SMTP_Server] [varchar](100) NULL, [SenderName] [varchar](100) NULL, [SenderEmail] [varchar](100) NULL, [EmailUsername] [varchar](50) NULL, [EmailPassword] [varchar](20) NULL, [ResendTime] [int] NULL, [FaxResendTime] [int] NULL, [RefreshTime] [int] NULL, [FaxRefreshTime] [int] NULL, [NotConvertedTime] [int] NULL, [FaxUnchargedTime] [int] NULL, [DefaultMessage] [text] NULL, [SpecFloatingTime] [int] NULL, [ClientName] [varchar](50) NULL, [OPDPassword] [varchar](20) NULL, [ConvertCashPatient] [int] NULL, [AutoStart] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE LABORATORY..TBLABMASTER ADD QueueTag char(1), ConvertStatus varchar(1) go USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_ReportStatus] Script Date: 03/21/2014 10:40:58 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_ReportStatus] @StartDate as varchar(10), @EndDate as varchar(10) AS SELECT LM.QueueTag, LM.RefNum, VM.LastName, VM.Firstname, VM.MiddleName, E.LabExam, S.LabSection, convert(varchar(10),LB.VerifyDate,101) Verifydate FROM LABORATORY..tblablogbook LB left outer join tblabMaster LM on LM.requestnum = LB.requestNum left outer join dbo.vwPatientResultEntry VM on LM.Hospnum = VM.Hospnum left outer join BUILD_FILE..tbCoLabExam E on LM.ItemID = E.LabExamID left outer join BUILD_FILE..tbColabSection S on LM.SectionID = S.LabSectionID WHERE LM.ConvertStatus = 'S' AND (LB.Verifydate between @StartDate and @EndDate + ' 23:59:59.99') ORDER BY LB.VerifyDate,VM.LastName,VM.FirstName,VM.MiddleName GO ALTER TABLE LABORATORY..TBLABMASTER ADD EmailStatus varchar(1) GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_SetfgridQueue] Script Date: 03/21/2014 11:04:20 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_SetfgridQueue] @VerifyDate varchar(10) AS BEGIN SELECT TOP 10 LM.HospNum, LM.RequestNum, LM.RefNum, LM.SectionID, LM.QueueTag, LM.SpecimenID, CLE.LabExam, CLS.LabSection, V.LastName, V.FirstName, V.MiddleName, LLB.VerifyDate, LLB.AccessionNum, LM.UserId FROM Laboratory..tbLabLogBook LLB LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Laboratory.dbo.vwPatientResultEntry V ON LM.HospNum = V.hospnum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID WHERE (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.99') AND LM.RequestStatus = 'W' AND (LM.ConvertStatus = 'X' OR LM.ConvertStatus = 'R') ORDER BY LLB.VerifyDate END; GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF] Script Date: 03/21/2014 11:15:15 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF] @VerifyDate varchar(10), @VerifyDateEnd varchar(10) = @VerifyDate AS BEGIN SELECT LM.RequestNum, LM.RefNum, LM.UserID, LM.QueueTag, CLS.LabSection, CLE.LabExam, LLB.AccessionNum FROM Laboratory..tbLabLogBook LLB LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID WHERE (LLB.VerifyDate between @VerifyDate and @VerifyDateEnd + ' 23:59:59.99') AND LM.RequestStatus = 'W' and (LM.ConvertStatus = 'X' OR LM.ConvertStatus = 'P') ORDER BY LLB.VerifyDate END; GO ALTER TABLE LABORATORY..TBLABMASTER ADD ConvertQueueTag VARCHAR(1), EmailResult VARCHAR(1) go USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_SetlstSent_Search] Script Date: 03/21/2014 11:44:08 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_SetlstSent_Search] @VerifyDate varchar(10), @SearchKey varchar(50) AS BEGIN SELECT LM.HospNum, LM.UserID, LM.RefNum, LM.EmailResult, LLB.RequestNum, LLB.AccessionNum, CLS.LabSection, CLE.LabExam, LM.QueueTag, M.LastName, M.FirstName, M.MiddleName, C.lastname as MoaLastName, C.Firstname as MoaFirstname, C.Middlename as MoaMiddlename, convert(varchar(10),LLB.VerifyDate,101) VerifyDate FROM Laboratory..tbLabLogBook LLB LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Laboratory.dbo.vwPatientResultEntry M ON LM.HospNum = M.HospNum LEFT OUTER JOIN Patient_Data..tbcashpatient C ON LM.HospNum = C.HospNum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID WHERE LM.RequestStatus = 'W' and LM.ConvertStatus = 'S' AND (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.99') AND (M.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey or C.LastName LIKE @SearchKey+'%') AND (LM.ConvertQueueTag is null OR LM.COnvertQueueTag = 'L') ORDER BY LLB.VerifyDate, M.LastName, M.FirstName, M.MiddleName END; GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_SetlstSent_SearchAll] Script Date: 03/21/2014 11:46:00 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_SetlstSent_SearchAll] @SearchKey varchar(50) AS SELECT LM.HospNum, LLB.RequestNum, LM.RefNum, LM.EmailResult, CLS.LabSection, CLE.LabExam, LLB.AccessionNum, M.LastName, M.FirstName, M.MiddleName, LM.UserID, LM.QueueTag, convert(varchar(10),LLB.VerifyDate,101) VerifyDate FROM Laboratory..tbLabLogBook LLB LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Laboratory.dbo.vwPatientResultEntry M ON LM.HospNum = M.HospNum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID WHERE LM.RequestStatus = 'W' and LM.ConvertStatus = 'S' AND (M.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) AND (LM.QueueTag is null OR LM.QueueTag = 'L') ORDER BY LLB.VerifyDate, M.LastName, M.FirstName, M.MiddleName GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_SetlstUnSent] Script Date: 03/21/2014 11:46:24 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_SetlstUnSent] @VerifyDate varchar(10), @SearchKey varchar(50) AS BEGIN SELECT LM.UserID, LM.RefNum, LLB.RequestNum, LLB.AccessionNum, LM.QueueTag, CLS.LabSection, CLE.LabExam, V.LastName, V.FirstName, V.MiddleName, LLB.VerifyDate FROM Laboratory..tbLabLogBook LLB LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID LEFT OUTER JOIN Laboratory.dbo.vwPatientResultEntry V ON LM.HospNum = V.hospnum WHERE (LLB.VerifyDate between @VerifyDate and @VerifyDate + ' 23:59:59.99') AND LM.RequestStatus = 'W' AND (LM.ConvertStatus = 'X' OR LM.ConvertStatus = 'P') AND (V.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) ORDER BY LLB.VerifyDate END; GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_SetlstUnSentAll] Script Date: 03/21/2014 11:47:08 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_SetlstUnSentAll] @SearchKey varchar(50) AS BEGIN SELECT LM.UserID, LM.RefNum, LLB.RequestNum, LLB.AccessionNum, LM.QueueTag, CLS.LabSection, CLE.LabExam, M.LastName, M.FirstName, M.MiddleName, LLB.VerifyDate FROM Laboratory..tbLabLogBook LLB LEFT OUTER JOIN Laboratory..tbLabMaster LM ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Laboratory.dbo.vwPatientResultEntry M ON LM.HospNum = M.HospNum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID WHERE LM.RequestStatus = 'W' and (LM.ConvertStatus = 'X' OR LM.ConvertStatus = 'P') AND (M.LastName LIKE @SearchKey+'%' OR LM.RefNum = @SearchKey) ORDER BY LLB.VerifyDate END; GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_UpdateConvertStatus] Script Date: 03/21/2014 11:48:08 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_UpdateConvertStatus] @RequestNum varchar(8) AS UPDATE Laboratory..tbLabMaster SET ConvertStatus = 'S' WHERE RequestNum LIKE @RequestNum GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_UpdatePreferences] Script Date: 03/21/2014 11:48:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_UpdatePreferences] @ConversionPath varchar(100) = '', @DefaultMessage text = '', @ConversionWaitingTime int = 0, @ReConversionTime int = 0, @RefreshTime int = 0, @NotConvertedTime int = 0, @ConvertCashPatient int = 0, @AutoStart int = 0 AS UPDATE Patient_Data..tbPDFConverterSettings SET ConversionPath = @ConversionPath, DefaultMessage = @DefaultMessage, WaitingTime = @ConversionWaitingTime, ResendTime = @ReConversionTime, RefreshTime = @RefreshTime, NotConvertedTime = @NotConvertedTime, ConvertCashPatient = @ConvertCashPatient, AutoStart = @AutoStart WHERE HospitalID = 1 GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_Reconvert] Script Date: 03/21/2014 11:49:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_Reconvert] @RequestNum varchar(10) AS BEGIN SELECT LM.RequestNum, M.LastName, M.FirstName, M.MiddleName, PEA.EmailAddress, LLB.VerifyDate, CLS.LabSection, CLE.LabExam, LM.HospNum FROM Laboratory..tbLabMaster LM LEFT OUTER JOIN Laboratory.dbo.vwPatientResultEntry M ON LM.HospNum = M.HospNum LEFT OUTER JOIN Patient_Data..tbPatientEmailAddresses PEA ON LM.HospNum = PEA.HospNum LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID WHERE LM.RequestNum = @RequestNum and LM.RequestStatus = 'W' and LM.ConvertStatus = 'S' ORDER BY LLB.VerifyDate END; GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_PauseSending] Script Date: 03/21/2014 11:49:27 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_PauseSending] @RequestNum varchar(8) AS UPDATE Laboratory..tbLabMaster SET EmailStatus = 'P' WHERE RequestNum LIKE @RequestNum GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_Failed] Script Date: 03/21/2014 11:50:05 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_Failed] @RequestNum varchar(8) AS UPDATE Laboratory..tbLabMaster SET ConvertStatus = 'U' WHERE RequestNum LIKE @RequestNum GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_Resend_Unsent] Script Date: 03/21/2014 11:50:25 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_Resend_Unsent] @RequestNum varchar(10) AS SELECT LM.RequestNum, M.LastName, M.FirstName, M.MiddleName, PEA.EmailAddress, LLB.VerifyDate, CLS.LabSection, CLE.LabExam, LM.HospNum FROM Laboratory..tbLabMaster LM LEFT OUTER JOIN Laboratory.dbo.vwPatientResultEntry M ON LM.HospNum = M.HospNum LEFT OUTER JOIN Patient_Data..tbPatientEmailAddresses PEA ON LM.HospNum = PEA.HospNum LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID WHERE LM.RequestStatus = 'W' AND LM.RequestNum = @RequestNum AND LM.ConvertStatus = 'X' ORDER BY LLB.VerifyDate GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_ResumeSending] Script Date: 03/21/2014 11:50:39 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Lab_ConvertPDF_ResumeSending] @RequestNum varchar(8) AS UPDATE Laboratory..tbLabMaster SET ConvertStatus = 'X' WHERE RequestNum LIKE @RequestNum GO USE [Laboratory] GO /****** Object: StoredProcedure [dbo].[Lab_ConvertPDF_Resend_Unsent] Script Date: 03/21/2014 17:40:30 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[Lab_ConvertPDF_Resend_Unsent] @RequestNum varchar(10) AS SELECT LM.RequestNum, M.LastName, M.FirstName, M.MiddleName, --PEA.EmailAddress, LLB.VerifyDate, CLS.LabSection, CLE.LabExam, LM.HospNum FROM Laboratory..tbLabMaster LM LEFT OUTER JOIN Laboratory.dbo.vwPatientResultEntry M ON LM.HospNum = M.HospNum --LEFT OUTER JOIN Patient_Data..tbPatientEmailAddresses PEA ON LM.HospNum = PEA.HospNum LEFT OUTER JOIN Laboratory..tbLabLogBook LLB ON LM.RequestNum = LLB.RequestNum LEFT OUTER JOIN Build_File..tbCoLabExam CLE ON LM.ItemID = CLE.LabExamID LEFT OUTER JOIN Build_File..tbCoLabSection CLS ON LM.SectionID = CLS.LabSectionID WHERE LM.RequestStatus = 'W' AND LM.RequestNum = @RequestNum AND LM.ConvertStatus = 'X' ORDER BY LLB.VerifyDate GO USE [MIntranet] GO /****** Object: Table [dbo].[MessagesRead] Script Date: 05/28/2014 19:02:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MessagesRead]( [UserID] [varchar](50) NOT NULL, [LastWOMSID] [bigint] NULL, [LastChatID] [bigint] NULL, [LastNewsID] [bigint] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO USE [MIntranet] GO /****** Object: Index [byUserID] Script Date: 05/28/2014 19:02:27 ******/ CREATE NONCLUSTERED INDEX [byUserID] ON [dbo].[MessagesRead] ( [UserID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO