USE [RADIOLOGY]
GO

/****** Object:  View [dbo].[tbRadiologyRequests]    Script Date: 09/22/2023 18:06:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE 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' When ISNUMERIC(RIGHT(a.idnum,1)) = 0 THEN 'OPD' 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) 
                      A.DoctorName
                      AS DoctorName, A.Quantity,
                      isnull(radiology.dbo.fn_getemployeename(Res.RadTech), '') 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,
					a.isResultOngoing					      
FROM         RADIOLOGY..tbXRRequest A 
					LEFT OUTER JOIN RADIOLOGY..tbXRResult Res ON A.RequestNum = Res.RequestNum 
					LEFT OUTER JOIN RADIOLOGY..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' and B.issupply <> '1'

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' When ISNUMERIC(RIGHT(a.idnum,1)) = 0 THEN 'OPD' 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)
                      A.DoctorName
                      AS DoctorName, A.Quantity, 
                      isnull(radiology.dbo.fn_getemployeename(Res.RadTech), '') 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,
					a.isResultOngoing					  
FROM         RADIOLOGY..tbCTRequest A 
				      LEFT OUTER JOIN RADIOLOGY..tbCTResult Res ON A.RequestNum = Res.RequestNum 
				      LEFT OUTER JOIN RADIOLOGY..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' and B.issupply <> '1'
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' When ISNUMERIC(RIGHT(a.idnum,1)) = 0 THEN 'OPD' 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(res.EditedByID, '') EditedByID, 
                      isnull(TranscribedByID, '') TranscribedByID, isnull(VerifyByID, '') AS VerifyByID, isnull(radiology.dbo.fn_getemployeename(res.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)
                      A.DoctorName
                      AS DoctorName, A.Quantity, 
                      isnull(radiology.dbo.fn_getemployeename(Res.RadTech), '') 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,
					a.isResultOngoing					  
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' and B.issupply <> '1'
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' When ISNUMERIC(RIGHT(a.idnum,1)) = 0 THEN 'OPD' 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)
                      A.DoctorName 
                      AS DoctorName, A.Quantity, 
                      isnull(radiology.dbo.fn_getemployeename(Res.RadTech), '') 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,
					a.isResultOngoing					  
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, 'MI') IN ('MI','MR') and B.issupply <> '1'
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], ISNULL(A.RevenueID,'MM')[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' When ISNUMERIC(RIGHT(a.idnum,1)) = 0 THEN 'OPD' 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) 
                      A.DoctorName
                      AS DoctorName, A.Quantity, 
                      isnull(radiology.dbo.fn_getemployeename(Res.RadTech), '') 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,
					a.isResultOngoing					   	         
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' and B.issupply <> '1'
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' When ISNUMERIC(RIGHT(a.idnum,1)) = 0 THEN 'OPD' 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)
                      A.DoctorName
                      AS DoctorName, A.Quantity, 
                      isnull(radiology.dbo.fn_getemployeename(Res.RadTech), '') 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,
					a.isResultOngoing					  
FROM         RADIOLOGY..tbOBULRequest A LEFT OUTER JOIN
                      RADIOLOGY..tbOBULResult Res ON A.RequestNum = Res.RequestNum LEFT OUTER JOIN
                      Radiology..tbBillExamListing B ON A.Code = B.ItemID AND B.RevenueID IN ('WC', '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, 'WC') = 'WC' and B.issupply <> '1'
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' When ISNUMERIC(RIGHT(a.idnum,1)) = 0 THEN 'OPD' 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) 
					  A.DoctorName
                      AS DoctorName,
                       A.Quantity, 
                      isnull(radiology.dbo.fn_getemployeename(Res.RadTech), '') 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,
					a.isResultOngoing					  
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' and B.issupply <> '1'

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' When ISNUMERIC(RIGHT(a.idnum,1)) = 0 THEN 'OPD' 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)
                      A.DoctorName
                      AS DoctorName, A.Quantity, 
                      isnull(radiology.dbo.fn_getemployeename(Res.RadTech), '') 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,
					a.isResultOngoing					  
FROM         

RADIOLOGY..tbRequestMaster A 
LEFT OUTER JOIN
                      RADIOLOGY..tbResultMaster Res ON A.RequestNum = Res.RequestNum and A.RevenueID = res.revenueID 
                      left join 
                      RADIOLOGY..tbBillExamListing B ON A.RevenueID = B.RevenueID and A.Code = B.ItemID 
                      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     B.issupply <> '1'  and B.RevenueID IN (SELECT RevenueID FROM tbRadiologyRevenues where Active = '1')




--ORIGINAL
--FROM RADIOLOGY..tbRequestMaster A LEFT OUTER JOIN
--                      RADIOLOGY..tbResultMaster Res ON A.RequestNum = Res.RequestNum and A.RevenueID = res.revenueID LEFT OUTER JOIN
--                      RADIOLOGY..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     B.issupply <> '1' 




GO