Update 07/07/2021: The audit extract report can now be done by the user via the UI. (System Page > Export Claims)
------------------------------------------------------------------------------------------------------------------------------------------
(Obsolete)
This is to extract claim and claim detail data out of Oncall Suite / eVMS system.
- Run the below query on the server vs MSSQL Management Studio
- This will produces two results and save the data (with header) to the XLSX file with two sheets (Claim and Detail respectively)
- Upload the file to the OnCallSuite IIS folder on Bhalivateh00 server for customer to download
- Notify customer when complete
select C.ID as ClaimID into #claims from Claims C join Users U on U.ID = C.UserID where C.ClaimStart > '2020-06-30' -- Update start date here and C.ClaimEnd < '2020-07-01' -- Update end date here order by U.LastName, U.FirstName select C.ID as ClaimID, C.UserID as EmployeeNumber, U.LastName, U.FirstName, S.Name as SpecialtyName, R_S.[Hours] as S_Hours, R_W.[Hours] as W_Hours, R_T.[Hours] as T_Hours, R_P.[Hours] as P_Hours, (select sum(value) from Claim_Codes where claimid = C.ID) as TotalCMBSValue, C.ClaimStart, C.ClaimEnd, datediff(mi, C.ClaimStart, C.ClaimEnd) / cast(60 as decimal(10,2)) as ClaimDurationHours from #claims X join Claims C on C.ID = X.ClaimID join Users U on U.ID = C.UserID left join Claim_Recall R_S on R_S.ClaimID = C.ID and R_S.RecallType = 'S' left join Claim_Recall R_W on R_W.ClaimID = C.ID and R_W.RecallType = 'W' left join Claim_Recall R_T on R_T.ClaimID = C.ID and R_T.RecallType = 'T' left join Claim_Recall R_P on R_P.ClaimID = C.ID and R_P.RecallType = 'P' --left join Claim_Codes CC -- on CC.ClaimID = C.ID left join Specialties S on S.ID = U.SpecialtyID order by U.LastName, U.FirstName, S.Name select X.ClaimID, CO.Code, CC.Value as AmountPaid, CO.IsTheatre, CO.IsAssist, CO.Category, CO.[Group], CO.[SubGroup],CO.Rate as ListedFullRate, Co.Created as CodeRateStartDate, CO.[Description] from #claims X join Claim_Codes CC on CC.ClaimID = X.ClaimID join CMBSCodes CO on CO.ID = CC.CMBSCodeID