SELECT Agency_ID, Agency_Name, Agent_FEIN, Natl_Provider_ID, Agency_Medicaid_ID, timezone FROM #Request.prefix_db_lookup#.Agency WHERE Agency_ID = AND status = 'active' LIMIT 1 SELECT Description FROM #Request.prefix_db_agency#.pProblem WHERE Assmt_ID = AND Status = 0 AND (M0_No LIKE 'M1021%' OR M0_No LIKE 'M1023%') ORDER BY dx_order, RIGHT(M0_No, 1) ASC LIMIT 5 SELECT DISTINCT LEFT(pProgress.Description, 3) AS taskCode FROM #Request.prefix_db_agency#.pProgress WHERE Schedule_ID = AND Status = 0 AND Description IS NOT NULL AND TRIM(Description) != '' AND LEFT(Description, 3) REGEXP '^[0-9]{3}$' LIMIT 10 SELECT e.*, p.Phys_NPI FROM #Request.prefix_db_lookup#.pEmployee e LEFT JOIN #Request.prefix_db_lookup#.pPhysicians p ON p.Emp_ID = e.Emp_ID AND p.Status = 0 WHERE e.Status = 0 AND e.Emp_ID = LIMIT 1 SELECT p.Patient_ID, p.Pt_First, p.Pt_Middle, p.Pt_Last, p.Pt_Agy_ID, p.Pt_Street, p.Pt_City, p.Pt_State, p.Pt_Zip, p.Pt_Phone, p.Pt_DOB, p.Pt_Gender, p.Pt_SSN, p.Pt_Latitude, p.Pt_Longitude, p.Email, pp.Sub_HIC, pp.PtPayer_ID, pp.Pt_Relationship, py.EVV_Payer_ID, py.Pay_Name, py.Pay_ID, py.PayerSource AS Payer_Source FROM #Request.prefix_db_agency#.pPatients p JOIN #Request.prefix_db_agency#.pPayer py ON py.Pay_ID = AND py.Status = 0 LEFT JOIN #Request.prefix_db_agency#.pPtPayer pp ON pp.Patient_ID = p.Patient_ID AND pp.Payer_ID = py.Pay_ID AND pp.Status = 0 WHERE p.Patient_ID = AND p.Status IN (0,1,4) ORDER BY pp.PtPayer_ID DESC LIMIT 1 SELECT p.Patient_ID, p.Pt_First, p.Pt_Middle, p.Pt_Last, p.Pt_Agy_ID, p.Pt_Street, p.Pt_City, p.Pt_State, p.Pt_Zip, p.Pt_Phone, p.Pt_DOB, p.Pt_Gender, p.Pt_SSN, p.Pt_Latitude, p.Pt_Longitude, p.Email, pp.Sub_HIC, pp.PtPayer_ID, pp.Pt_Relationship, py.EVV_Payer_ID, py.Pay_Name, py.Pay_ID, py.PayerSource AS Payer_Source FROM #Request.prefix_db_agency#.pPatients p LEFT JOIN #Request.prefix_db_agency#.pPtPayer pp ON pp.Patient_ID = p.Patient_ID AND pp.Status = 0 LEFT JOIN #Request.prefix_db_agency#.pPayer py ON py.Pay_ID = pp.Payer_ID AND py.Status = 0 WHERE p.Patient_ID = AND p.Status IN (0,1,4) ORDER BY pp.PtPayer_ID DESC LIMIT 1 SELECT Start_Date, Expiration_Date FROM #Request.prefix_db_agency#.Eligibility WHERE Patient_ID = AND PtPayer_ID = AND Status = 0 ORDER BY Elig_ID DESC LIMIT 1

HHA Exchange #HTMLEditFormat(arguments.methodName)# #statusWord#

Method: #HTMLEditFormat(arguments.methodName)#

Agency ID: #variables.ALLOWED_AGENCY_ID#

Record: #HTMLEditFormat(arguments.recordLabel)#

Call Date: #DateFormat(Now(), "yyyy-mm-dd")# #TimeFormat(Now(), "HH:mm:ss")#

API URL: #HTMLEditFormat(arguments.apiUrl)#

Transaction ID: #HTMLEditFormat(arguments.transactionId)#

EVV Status: #HTMLEditFormat(arguments.currentStatus)#

Details:

#HTMLEditFormat(local.detailKey)#: #HTMLEditFormat(ToString(arguments.details[local.detailKey]))#

Error Details:

#HTMLEditFormat(arguments.errorMessage)#

API Request:

#HTMLEditFormat(requestText)#

API Response:

#HTMLEditFormat(responseText)#

Transaction Status URL: #HTMLEditFormat(transactionStatus.url)#

Transaction Status Response:

#HTMLEditFormat(transactionStatusText)#

Transaction Status Error:

#HTMLEditFormat(transactionStatus.error)#

#local.emailBody# INSERT INTO #Request.prefix_db_lookup#.EVV (UUID, Seq_ID, San_status, API_type, Agency_ID, Patient_ID, Emp_ID, Schedule_ID, Date_Create, Created_by) VALUES ( , , , , , , , , , ) SELECT UUID, San_status, Date_Create FROM #Request.prefix_db_lookup#.EVV WHERE API_Type = 'clients' AND Agency_ID = AND Patient_ID = AND Status = 0 ORDER BY EVV_ID DESC LIMIT 1 SELECT UUID, San_status, Date_Create FROM #Request.prefix_db_lookup#.EVV WHERE API_Type = 'employee' AND Agency_ID = AND Emp_ID = AND Status = 0 ORDER BY EVV_ID DESC LIMIT 1 SELECT EVV_ID, UUID, Seq_ID, San_status, Patient_ID, Emp_ID, Schedule_ID FROM #Request.prefix_db_lookup#.EVV WHERE API_Type = 'visits' AND Agency_ID = AND Schedule_ID = AND Status = 0 ORDER BY EVV_ID DESC LIMIT 1 UPDATE #Request.prefix_db_lookup#.EVV SET San_status = , UUID = , Date_Update = , Status = 1 WHERE API_Type = 'visits' AND Agency_ID = AND Schedule_ID = AND Status = 0 SELECT pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pPatients.Pt_Agy_ID, pPatients.Pt_Street, pPatients.Pt_City, pPatients.Pt_State, pPatients.Pt_Zip, pPatients.Pt_Phone, pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.StartTime, pSchedules.EndTime, pSchedules.GPS_start_time, pSchedules.GPS_end_time, pSchedules.GPS_start_date, pSchedules.GPS_end_date, pSchedules.GPS_start_latitude, pSchedules.GPS_start_longitude, pSchedules.GPS_end_latitude, pSchedules.GPS_end_longitude, pSchedules.patient_sign, pSchedules.Emp_ID, pSchedules.Total_Units, pSchedules.Mi_Pay, pSchedules.Missed, pSchedules.Invoice_ID, pSchedules.Assmt_ID, pSchedules.Pay_ID, pSchedules.Auth_ID, pAuths.Date_Start AS Auth_Date_Start, pAuths.Date_End AS Auth_Date_End, IFNULL(pSchedules.Sch_Latitude, 0) AS Sch_Latitude, IFNULL(pSchedules.Sch_Longitude, 0) AS Sch_Longitude, (TIME_TO_SEC(TIMEDIFF(pSchedules.EndTime, pSchedules.StartTime)) / 3600) AS totalhours, pEmployee.Emp_First, pEmployee.Emp_Last, pEmployee.Emp_Custom_1, pEmployee.Emp_SSN, pRevenue_Code.HCPCS, pRevenue_Code.Rev_Code, pRevenue_Code.EClaim_Price, pRevenue_Code.Contract_Rate, Invoices.Internal_control_number FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID AND pPatients.Status IN (0,1,4) JOIN #Request.prefix_db_lookup#.pEmployee ON pSchedules.Emp_ID = pEmployee.Emp_ID AND pEmployee.Status < 2 LEFT JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pSchedules.HCPCS_ID AND pRevenue_Code.Status = 0 LEFT JOIN #Request.prefix_db_agency#.pAuths ON pAuths.Auth_ID = pSchedules.Auth_ID AND pAuths.Status = 0 LEFT JOIN #Request.prefix_db_agency#.Invoices ON Invoices.Invoice_ID = pSchedules.Invoice_ID AND Invoices.Deleted = 0 WHERE pSchedules.Status = 0 AND pSchedules.Emp_ID != 0 AND pSchedules.Schedule_ID = LIMIT 1 SELECT UUID, Seq_ID FROM #Request.prefix_db_lookup#.EVV WHERE API_Type = 'visits' AND Agency_ID = AND Schedule_ID = AND Status = 0 ORDER BY EVV_ID ASC LIMIT 1 SELECT UUID, San_status FROM #Request.prefix_db_lookup#.EVV WHERE API_Type = 'visits' AND Agency_ID = AND Schedule_ID = AND Status = 0 ORDER BY EVV_ID DESC LIMIT 1 UPDATE #Request.prefix_db_lookup#.EVV SET San_status = , Date_Update = WHERE API_Type = 'visits' AND Agency_ID = AND Schedule_ID = AND Status = 0