SELECT pSchedules.*,Invoices.Invoice_ID,Invoices.DateSent,Invoices.InvoiceTotal,Invoices.Internal_control_number , pRevenue_Code.Visit_Type AS Revvisit_type,pRevenue_Code.Description AS Rev_Descritpion, pRevenue_Code.EClaim_Price AS Allowed_Amount,pRevenue_Code.Contract_Rate, pPayer.Output,pPayer.Payer_No,pPayer.Pay_Name, pPatients.Patient_ID, pPatients.Pt_Agy_ID, pPatients.Pt_Last, pPatients.Pt_First, pPtPayer.PtPayer_ID, (TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/3600) As totalhours, pAdmit.M0030_START_CARE_DT, CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN "FIRST FINAL" WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN "SECOND FINAL" ELSE "0" END as First_or_second_Period, CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN pAssessments.F177 WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) ELSE pAssessments.F177 END as start_date, CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 29 DAY) WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 59 DAY) ELSE pAssessments.F178 END as end_date, ROUND((DATEDIFF(NOW(),pAdmit.M0030_START_CARE_DT)/30),0) AS Total_Periods, 0 AS Days_in_Month, 0 AS HHRG, 0 AS Total_Visits, 0 AS Therapy_Visits, 0 AS Payment, 0 AS Daily_Rate, 0 AS CM_Gross, 0 AS CM_Sequester, 0 AS CM_VBP, 0 AS LUPA, 0 AS CM_LUPA, 0 AS CM_Outlier, 0 AS Total_CM, 0 AS Daily_Adjust, 0 AS CM_Net, IFNULL((SELECT SUM(Payments.Amount) FROM #Request.prefix_db_agency#.Payments WHERE Payments.Invoice_ID = Invoices.Invoice_ID AND Payments.PatientPayer_ID = pPayer.Pay_Id AND Payments.Deleted = 0 ),0.00) AS Actual_Reimbursement_Amount, IFNULL((SELECT SUM(Payments.Amount) FROM #Request.prefix_db_agency#.Payments WHERE Payments.Invoice_ID = Invoices.Invoice_ID AND Payments.PatientPayer_ID = pPayer.Pay_Id AND Payments.Deleted = 0 ),0.00) AS Payments, 0 AS GL_Code, now(), ROUND(CASE WHEN UCASE(pPayer.Pay_Name) LIKE '%AHCCCS%' OR UCASE(pPayer.Payer_No) LIKE '%AHCCCS%' THEN (CASE WHEN IFNULL(pSchedules.Total_Units,0) > 0 THEN (pSchedules.Total_Units * pRevenue_Code.EClaim_Price) WHEN pRevenue_Code.Measure = '.25' THEN (ROUND((TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/900), 0) * pRevenue_Code.EClaim_Price) WHEN pRevenue_Code.Measure = '1' THEN ((TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/3600) * pRevenue_Code.EClaim_Price) ELSE (1 * pRevenue_Code.EClaim_Price) END) WHEN Measure = 'visit' THEN (1*pRevenue_Code.EClaim_Price) WHEN pRevenue_Code.Measure = '.25' THEN (ROUND((TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/900), 0) * pRevenue_Code.EClaim_Price) WHEN pRevenue_Code.Measure = '1' THEN ((TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/3600) * pRevenue_Code.EClaim_Price) ELSE (pSchedules.Total_Units * pRevenue_Code.EClaim_Price) END, 2) AS Eclaim1, CASE WHEN UCASE(pPayer.Pay_Name) LIKE '%AHCCCS%' OR UCASE(pPayer.Payer_No) LIKE '%AHCCCS%' THEN ROUND((pRevenue_Code.Contract_Rate * CASE WHEN IFNULL(pSchedules.Total_Units,0) > 0 THEN pSchedules.Total_Units WHEN pRevenue_Code.Measure = '.25' THEN ROUND((TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/900), 0) WHEN pRevenue_Code.Measure = '1' THEN (TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/3600) ELSE 1 END), 2) WHEN pRevenue_Code.Measure = '.25' THEN ROUND((pRevenue_Code.Contract_Rate * CASE WHEN ROUND((TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/900), 0) >= 0.5 AND ROUND((TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/900), 0) < 1 THEN 1 ELSE ROUND((TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/900), 0) END), 2) WHEN pRevenue_Code.Measure = '1' THEN ROUND((pRevenue_Code.Contract_Rate * (TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime,pSchedules.StartTime))/3600)), 2) ELSE ROUND(pRevenue_Code.Contract_Rate, 2) END AS expected_reimbursement, pRevenue_Code.EClaim_Price AS EClaim2 FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pSchedules.Rev_ID AND pRevenue_Code.status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pAssessments.status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.status = 0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID JOIN #Request.prefix_db_agency#.pPtPayer ON pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status = 0 JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_Id = pPtPayer.Payer_ID AND pPayer.Status = 0 AND pPayer.Output != 'Medicare' LEFT OUTER JOIN #Request.prefix_db_agency#.Invoices ON (pSchedules.Invoice_ID = Invoices.Invoice_ID OR pSchedules.NOA = Invoices.Invoice_ID) AND Invoices.Deleted = 0 WHERE pPatients.Status In (0,1,4) AND pSchedules.status= 0 AND pSchedules.Emp_ID != 0 AND pSchedules.pgnotesdraft > 0 AND pSchedules.Missed = '0' AND MONTH(pSchedules.Visit_Date) = MONTH('#client.ffsmonth#') AND YEAR(pSchedules.Visit_Date) = YEAR('#client.ffsmonth#') AND MONTH(pSchedules.Visit_Date) = MONTH(now()) AND pPayer.Payer_No NOT IN ('Pre Paid') GROUP BY pSchedules.Schedule_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC, pSchedules.Visit_Date ASC SELECT p.Patient_ID, p.Pt_Last, p.Pt_First, p.Status FROM #Request.prefix_db_agency#.pPatients p WHERE p.Loc_ID = '#session.agencyid#' AND p.Status < 2 ORDER BY p.Pt_Last ASC, p.Pt_First ASC SELECT e.Emp_ID, e.Emp_Last, e.Emp_First, e.Status FROM #Request.prefix_db_lookup#.pEmployee e WHERE e.Loc_ID = '#session.agencyid#' AND e.Emp_Type = 'employee' ORDER BY e.Emp_Last ASC, e.Emp_First ASC
FFS Report
Fetch latest report Export Report

Payer ID Payer Patient Name MRN SOC Visit Date Visit Type Visit Description Visit Time Gross Charges Expected Reimbursement Contractual Adjustment Invoice Date Invoice ID GL Code
#Payer_No# #Pay_Name# #Pt_Last#, #Pt_First# #Pt_Agy_ID# #DateFormat(M0030_START_CARE_DT,"mm/dd/yyyy")# #DateFormat(Visit_Date,"mm/dd/yyyy")# #Skill# #Rev_Descritpion# #DecimalFormat(totalhours)# #DecimalFormat(Eclaim1)# #expected_reimbursement# #DecimalFormat(Contractual_Adjustment)# #DateFormat(DateSent,"mm/dd/yyyy")# #Invoice_ID#