SELECT * FROM #Request.prefix_db_lookup#.Agency WHERE Agency_ID = '#session.agencyId#' AND EVV_prod_username IS NOT NULL AND EVV_prod_password IS NOT NULL AND EVV_username IS NOT NULL AND EVV_password IS NOT NULL SELECT * FROM #Request.prefix_db_agency#.pPatients WHERE pPatients.Status IN (0,1) AND Patient_ID = #Patient_ID# GROUP BY Patient_ID SELECT pPayer.M0150 AS paymentsource,pPtPayer.*,pPayer.*, (SELECT Eligibility.Start_Date FROM #Request.prefix_db_agency#.Eligibility WHERE Eligibility.Patient_ID = pPtPayer.Patient_ID AND Eligibility.PtPayer_ID = pPtPayer.PtPayer_ID AND Eligibility.Status = 0 ORDER BY Start_Date DESC LIMIT 0,1) AS Start_Date FROM #Request.prefix_db_agency#.pPtPayer JOIN #Request.prefix_db_agency#.pPayer ON pPtPayer.Payer_ID = pPayer.Pay_ID WHERE Patient_ID = #Patient_ID# AND pPtPayer.status = 0 AND pPayer.status = 0 ORDER BY pPtPayer.Payer_Responsibility ASC SELECT * FROM #Request.prefix_db_agency#.pPtContacts WHERE Patient_ID = #Patient_ID# AND status < 2 Order by Emergency DESC, status ASC SELECT (IFNULL(Max(Seq_ID),0)+1) as seq_id FROM #Request.prefix_db_lookup#.EVV WHERE API_type = 'clients' "SequenceID": "#Seq_ID#", "ClientCustomID": "#ClientMedicaidID#", "ClientOtherID": "#ClientMedicaidID#", "ClientTimezone": "#VisitTimeZone#", "ClientSSN": "999999999", "Coordinator": "123", "ProviderAssentContPlan": "No", "ClientPayerInformation": [ { "PayerID": "#PayerID#", "PayerProgram": "#PayerProgram#", "ProcedureCode": "#ProcedureCode#", "ClientPayerID": "#Getpayers.Sub_HIC#", "ClientEligibilityDateBegin": "#Start_Date#", "ClientEligibilityDateEnd": "#Todaydate#", "ClientStatus": "02", "EffectiveStartDate": "#EffectiveStartDate#", "EffectiveEndDate": "#EffectiveEndDate#" } ], "ClientAddress": [ { "ClientAddressType": "Home", "ClientAddressIsPrimary": true, "ClientAddressLine1": "#GetPat.Pt_Street#", "ClientAddressLine2": "", "ClientCounty": "USA", "ClientCity": "#GetPat.Pt_City#", "ClientState": "#GetPat.Pt_State#", "ClientZip": "#ClientZip#", "ClientAddressLongitude":"#GetPat.Pt_Longitude#", "ClientAddressLatitude": "#GetPat.Pt_Latitude#" } ], "ClientPhone": [ { "ClientPhoneType": "Home", "ClientPhone": "#ClientPhone#" } ], "ClientDesignee": [ { "ClientDesigneeFirstName": "#GetPat.Pt_First#", "ClientDesigneeLastName": "#GetPat.Pt_Last#", "ClientDesigneeEmail": "#client_email#" , "ClientDesigneeStatus": "02" , "ClientDesigneeStartDate": "#Start_Date#", "ClientDesigneeEndDate": "#Todaydate#", "ClientDesigneeRelationship": "" } ], "ClientResponsibleParty": [ { "ClientContactType": "#Contact_Type#", "ClientContactFirstName": "#GetContacts.First_Name#", "ClientContactLastName": "#GetContacts.Last_Name#", "ClientContactPhoneType": "Mobile", "ClientContactPhone": "#ClientContactPhone#", "ClientContactEmailAddress": "#GetContacts.Contact_Email#", "ClientContactAddressLine1": "", "ClientContactAddressLine2": "", "ClientContactCity": "", "ClientContactState": "", "ClientContactZip": "" } ] } ] > " /> REQUEST:
RESPONSE: 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 ('#UUID#', '#Seq_ID#','#return_status#', 'clients', '#session.agencyid#', #Patient_ID#, 0, 0, #now()#, #session.employeeid#) REQUEST:
RESPONSE: SELECT * FROM #Request.prefix_db_lookup#.Agency WHERE Agency_ID = '#session.agencyId#' AND EVV_prod_username IS NOT NULL AND EVV_prod_password IS NOT NULL AND EVV_username IS NOT NULL AND EVV_password IS NOT NULL SELECT * FROM #Request.prefix_db_lookup#.pEmployee WHERE Status = 0 AND Emp_ID = #Employee_ID# SELECT (IFNULL(Max(Seq_ID),0)+1) as seq_id FROM #Request.prefix_db_lookup#.EVV WHERE API_type = 'employee' " /> 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 ('#UUID#', '#Seq_ID#','#return_status#', 'employee', '#session.agencyid#', 0, #GetEmp.Emp_ID#, 0, #now()#, #session.employeeid#) REQUEST:
RESPONSE: " /> REQUEST:
RESPONSE: SELECT * FROM #Request.prefix_db_lookup#.Agency WHERE Agency_ID = '#session.agencyId#' AND EVV_prod_username IS NOT NULL AND EVV_prod_password IS NOT NULL AND EVV_username IS NOT NULL AND EVV_password IS NOT NULL SELECT Emp_First, Emp_Last FROM #Request.prefix_db_lookup#.pEmployee WHERE Emp_ID = '#Employee_ID#' SELECT UUID FROM #Request.prefix_db_lookup#.EVV WHERE API_Type = 'employee' AND Agency_ID = '#session.agencyid#' AND status = 0 AND Emp_ID = '#Employee_ID#' ORDER BY EVV_ID DESC LIMIT 1

Arizona Employee Status Check Results

Employee Name: #GetEmpInfo.Emp_First#, #GetEmpInfo.Emp_Last#

Employee ID: #Employee_ID#

UUID: #LOCAL.uuid#

Status Check Time: #now()#

API Response:

#httpStatusResponse.Filecontent#

Arizona Employee Status Check Error

Employee Name: #GetEmpInfo.Emp_First#, #GetEmpInfo.Emp_Last#

Employee ID: #Employee_ID#

Error: No UUID found in EVV table for this employee

Check Time: #now()#

Arizona Employee Status Check Error

Employee Name: #GetEmpInfo.Emp_First#, #GetEmpInfo.Emp_Last#

Employee ID: #Employee_ID#

Error: #cfcatch.message#

Check Time: #now()#

#cfcatch.detail#

SELECT * FROM #Request.prefix_db_lookup#.Agency WHERE Agency_ID = '#session.agencyId#' AND EVV_prod_username IS NOT NULL AND EVV_prod_password IS NOT NULL AND EVV_username IS NOT NULL AND EVV_password IS NOT NULL SELECT pSchedules.*, pPatients.Pt_First, pPatients.Pt_Last, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pPatients ON pSchedules.Patient_ID = pPatients.Patient_ID LEFT JOIN #Request.prefix_db_lookup#.pEmployee ON pSchedules.Emp_ID = pEmployee.Emp_ID WHERE pSchedules.Schedule_ID = '#Schedule_ID#' AND pSchedules.status = 0 AND pPatients.status IN (0,1,4) SELECT UUID FROM #Request.prefix_db_lookup#.EVV WHERE API_Type = 'visits' AND Agency_ID = '#session.agencyid#' AND status = 0 AND Schedule_ID = '#Schedule_ID#' ORDER BY EVV_ID DESC LIMIT 1

Arizona Visit Status Check Results

Patient Name: #GetVisitInfo.Pt_First#, #GetVisitInfo.Pt_Last#

Employee Name: #GetVisitInfo.Emp_First#, #GetVisitInfo.Emp_Last#

Schedule ID: #Schedule_ID#

Visit Date: #DateFormat(GetVisitInfo.Visit_Date, "mm/dd/yyyy")#

UUID: #LOCAL.uuid#

Status Check Time: #now()#

API Response:

#httpStatusResponse.Filecontent#

Arizona Visit Status Check Error

Patient Name: #GetVisitInfo.Pt_First#, #GetVisitInfo.Pt_Last#

Employee Name: #GetVisitInfo.Emp_First#, #GetVisitInfo.Emp_Last#

Schedule ID: #Schedule_ID#

Error: No UUID found in EVV table for this visit

Check Time: #now()#

Arizona Visit Status Check Error

Patient Name: #GetVisitInfo.Pt_First#, #GetVisitInfo.Pt_Last#

Employee Name: #GetVisitInfo.Emp_First#, #GetVisitInfo.Emp_Last#

Schedule ID: #Schedule_ID#

Error: #cfcatch.message#

Check Time: #now()#

#cfcatch.detail#

Arizona Client Status Check Results

Patient Name: #GetPatientInfo.Pt_First#, #GetPatientInfo.Pt_Last#

Patient ID: #Patient_ID#

UUID: #LOCAL.uuid#

Status Check Time: #now()#

API Response:

#httpStatusResponse.Filecontent#

Arizona Client Status Check Error

Patient Name: #GetPatientInfo.Pt_First#, #GetPatientInfo.Pt_Last#

Patient ID: #Patient_ID#

Error: No UUID found in EVV table for this client

Check Time: #now()#

Arizona Client Status Check Error

Patient Name: #GetPatientInfo.Pt_First#, #GetPatientInfo.Pt_Last#

Patient ID: #Patient_ID#

Error: #cfcatch.message#

Check Time: #now()#

#cfcatch.detail#

SELECT * FROM #Request.prefix_db_lookup#.Agency WHERE Agency_ID = '#session.agencyId#' AND EVV_prod_username IS NOT NULL AND EVV_prod_password IS NOT NULL AND EVV_username IS NOT NULL AND EVV_password IS NOT NULL SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast,pPatients.Pt_Middle, pPatients.Pt_First AS PatientFirst, pPatients.Pt_Agy_ID,pPatients.Status AS clientStatus,pPatients.Pt_SSN,pPatients.Pt_Street, pPatients.Pt_City,pPatients.Pt_State,pPatients.Pt_Zip,pPatients.Pt_Longitude,pPatients.Pt_Latitude,pPatients.Pt_Phone, pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Skill AS Type, pSchedules.Visit_Type, pSchedules.Total_Units, pSchedules.Mi_Pay,pSchedules.GPS_start_time,pSchedules.GPS_end_time, CASE WHEN length(pSchedules.patient_sign) > 0 THEN true ELSE false END AS patient_sign_indicator, CONCAT(RIGHT(pEmployee.Emp_SSN, 5), LEFT(Emp_Last,4)) AS EmployeeIdentifier, CONCAT(DATE_FORMAT(CONVERT_TZ(pSchedules.Date_Create, @@session.time_zone, '+00:00'),'%Y-%m-%dT%H:%i:%s'), 'Z') AS CallDateTime, pSchedules.Progress_Note, pSchedules.pgnotesdraft,pSchedules.GPS_start_latitude, pSchedules.GPS_start_longitude,pSchedules.GPS_end_latitude,pSchedules.GPS_end_longitude, pSchedules.Pay_ID, 0 AS No_Days_Progress, "" AS Note_QA , pSchedules.Notes AS Memo, IFNULL(pSchedules.Sch_Latitude,40.34455) AS Sch_Latitude, IFNULL(pSchedules.Sch_Longitude,-21.99383) AS Sch_Longitude, pSchedules.GPS_start_date,pSchedules.GPS_end_date, pSchedules.StartTime,pSchedules.EndTime,pSchedules.Date_Create AS CallDateTime_old, IFNULL(pPtPayer.Sub_HIC,pSchedules.Schedule_ID) AS Sub_HICs, pEmployee.*, pAssessments.Assmt_ID,pAuths.*,pPayer.*,pPtPayer.*,Agency.*,pRevenue_Code.HCPCS,pPhysicians.Phys_NPI,pRevenue_Code.HCPCS FROM #Request.prefix_db_agency#.pSchedules LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID 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 OUTER JOIN #Request.prefix_db_agency#.pAuths ON pAuths.Patient_ID = pSchedules.Patient_ID AND pAuths.Status =0 LEFT OUTER JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.Patient_ID = pPatients.Patient_ID AND pPtPayer.PtPayer_ID = pAuths.PtPayer_ID AND pPtPayer.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID AND pPayer.Status = 0 LEFT OUTER JOIN #Request.prefix_db_lookup#.Agency ON Agency.Agency_ID = pPatients.Loc_ID AND Agency.Status = 'active' LEFT OUTER 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_lookup#.pPhysicians ON pPhysicians.Emp_ID = pEmployee.Emp_ID AND pPhysicians.Status = 0 WHERE pSchedules.status= 0 AND pSchedules.Emp_ID != 0 AND pSchedules.Missed = '0' AND pSchedules.Visit_Date >= '2024-07-31' AND pSchedules.EVV_Export = 0 AND pSchedules.Schedule_ID IN () GROUP BY pSchedules.Schedule_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC, pEmployee.Emp_Last ASC, pEmployee.Emp_First ASC, pSchedules.Skill ASC, pSchedules.Visit_Date ASC SELECT (IFNULL(Max(Seq_ID),0)+1) as seq_id FROM #Request.prefix_db_lookup#.EVV WHERE API_type = 'visits' " /> REQUEST:
RESPONSE: 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 ('#UUID#', '#Seq_ID#','#return_status#', 'visits', '#session.agencyid#', #GtSched.Patient_ID#, #GtSched.Emp_ID#, #GtSched.Schedule_ID#, #now()#, #session.employeeid#) " /> Error: REQUEST:
RESPONSE: