SELECT patient.Patient_ID, patient.Status, patient.Last_Name_M0040 AS PatientLast, patient.First_Name_M0040 AS PatientFirst,
Assessment_Forms.Assessment_Forms_ID, Assessment_Forms.Form_Type, Assessment_Forms.Date_Sent,
Assessment_Forms.Cert_Start_Item_3a, Assessment_Forms.Cert_End_Item_3b, Assessment_Forms.Record_Mod_Date,
Physician.Physician_ID,
Employee.Last_Name as LastName, Employee.First_Name as FirstName, Employee.status AS DocStatus,
Assessment_Forms.contact_id,pc.ContactName,pc.ContactType,pc.ContactStreet,pc.ContactCity,pc.ContactState,pc.ContactZip,
Assessment_Forms.physician_id,
pc.Telephone1 as ContactTelephone1,pc.Telephone2 as ContactTelephone2,
Assessment_Forms.Addendum_Details_21,
Assessment_New.Deleted
FROM #Request.prefix_db_agency#.Assessment_Forms
LEFT OUTER JOIN #Request.prefix_db_agency#.patient on Assessment_Forms.Patient_ID = patient.Patient_ID
LEFT OUTER JOIN #Request.prefix_db_lookup#.Agency on patient.Agency_ID = Agency.Agency_ID
LEFT OUTER JOIN #Request.prefix_db_lookup#.Physician on Assessment_Forms.Physician_ID = Physician.Physician_ID
LEFT OUTER JOIN #Request.prefix_db_lookup#.Employee on Physician.Employee_ID = Employee.Employee_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.PatientContact pc on Assessment_Forms.Contact_ID = pc.Patientcontact_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.Assessment_New on Assessment_Forms.Assessment_Tracker_ID = Assessment_New.Assessment_Tracker_ID
WHERE patient.Agency_ID = '#session.AgencyID#'
AND Assessment_Forms.Form_Status NOT IN ('Signed','Delete')
AND Assessment_New.Deleted != 1
AND Assessment_Forms.Physician_ID != 0
AND patient.Status != "Deleted"
AND Assessment_Forms.Date_Sent is null
Group by Physician.Physician_ID, patient.Patient_ID, Assessment_Forms.Assessment_Forms_ID
ORDER BY Employee.Last_Name, Employee.First_Name, Physician.Physician_Id, patient.Last_Name_M0040, patient.First_Name_M0040, Assessment_Forms.Cert_End_Item_3b
Un-Signed Orders (by Physician)
___________________________________________
#LastName#, #FirstName# Physician Record is Inactive
#PatientLast#, #PatientFirst#
#Form_Type#
#Form_Type#
#DateFormat(Cert_Start_Item_3a,"mm/dd/yy")# - #DateFormat(Cert_End_Item_3b,"mm/dd/yy")#
Print
mailed #DateFormat(Date_Sent,"mm/dd/yyyy")#
NOT MAILED