update invitation_templates set messagebody='
Hello [$CareGiver],
The following group alert message has been posted by [$Caregiver Message sender]
[$Group Alert Message].
Click here to access [$SiteTitle]: Your Caregiving Companion''s site.
' ,subject='URGENT Group Alert Message added for [$Care Recipient]' where template_type='Add_Group_Message_Alert'; update invitation_templates set messagebody='Hello [$CareGiver],
The following group message has been posted by [$Caregiver Message sender]
[$Group Message].
Click here to access [$SiteTitle]: Your Caregiving Companion''s site.
',subject='Group Message added for [$Care Recipient]' where template_type='Add_Group_Message'; update invitation_templates set messagebody='Hello [$CareGiver],
This is an appointment reminder from your [$SiteTitle] Organizer for [$Care Recipient][$what] for [$Care Recipient] on [$date] at [$time].
Best wishes from all of us at [$SiteTitle], a place for caregivers.
' , subject='Appointment reminder for [$Care Giver]' where template_type='Appointment Reminder'; update invitation_templates set messagebody='Hello [$CareGiver],
This is a to-do reminder from your [$SiteTitle] Organizer for [$Care Recipient]:[$Name of to-do] on [$date] at [$time].
Click here to access [$SiteTitle]: Your Caregiving Companion''s site.
Best wishes from all of us at [$SiteTitle], a place for caregivers.
' , subject='To-do reminder for [$Care Giver]' where template_type='TODO List Reminder'; update invitation_templates set messagebody='Hello [$CareGiver],
This is a shopping list reminder from your [$SiteTitle] CarePlanner for [$CareGiver]
Remember to pick up [$what] for [$Care Recipient] on [$date] at [$time].
Best wishes from all of us at [$SiteTitle], a place for caregivers.
' , subject='Shopping list reminder for [$Care Giver]' where template_type='Reminder_Shopping_List'; update invitation_templates set messagebody='Hello $Caregiver,
As requested, here''s a link to a page where you can reset your [$SiteTitle] password: ChangePassword
If you cannot click on the link above, please highlight the link, copy it, and paste it into your browser.
All the best from [$SiteTitle] -- let us know how we can be helpful.
' , subject='Here''s how to reset your Strength for Caring password' where template_type='Forgot Password'; CREATE OR REPLACE FORCE VIEW "VW_USER_PATIENT_COUNT_SUMMARY" AS SELECT DISTINCT ( (SELECT COUNT(uch1.user_calender_header_id) FROM user_calender_header uch1, event_type_lkp etl, vw_user_role_permission vp WHERE etl.event_type = 'To Do' AND uch1.event_type_lkp_id = etl.event_type_lkp_id AND uch1.patient_id =up.patient_id AND up.patient_id = vp.patient_id AND up.user_id = vp.user_id AND vp.module_name = 'To Do List' AND (vp.permission_id >= 9 or uch1.assigned_to = up.user_id) )) AS todo_count, (SELECT COUNT(uch2.user_calender_header_id) FROM vw_user_calendar_iteration uch2 left join event_type_lkp etl on etl.event_type = 'Appointment', vw_user_role_permission vp WHERE (uch2.event_type_lkp_id = etl.event_type_lkp_id or uch2.add_to_calendar =1) AND uch2.patient_id =up.patient_id AND up.patient_id = vp.patient_id AND up.user_id = vp.user_id AND vp.module_name = 'Schedule' AND (vp.permission_id >= 9 or uch2.assigned_to = up.user_id) ) AS event_count, (SELECT COUNT(itd1.itemdetail_id) FROM item_detail itd1, vw_user_role_permission vp WHERE itd1.patient_id =up.patient_id AND up.patient_id = vp.patient_id AND up.user_id = vp.user_id AND vp.module_name = 'Shopping List' AND (vp.permission_id >= 9 or itd1.assigned_user_id = up.user_id) ) AS item_count, (SELECT COUNT(p1.prescription_id) FROM prescription_lnk p1, vw_user_role_permission vp WHERE p1.created_by = up.user_id AND p1.patient_id =up.patient_id AND up.patient_id = vp.patient_id AND up.user_id = vp.user_id AND vp.module_name = 'Prescriptions' AND vp.permission_id >= 9 ) AS prescription_count, (SELECT COUNT(m1.message_id) FROM messages m1, vw_user_role_permission vp WHERE m1.created_by = up.user_id AND m1.patient_id =up.patient_id AND up.patient_id = vp.patient_id AND up.user_id = vp.user_id AND vp.module_name = 'Group Messages' AND vp.permission_id >= 9 ) AS message_count, up.user_id AS user_id, up.patient_id AS patient_id FROM user_role_patient_lnk up WHERE up.is_active=1; CREATE OR REPLACE FORCE VIEW "VW_MODULE_CACHE_PATIENT" AS SELECT 'Appointment' AS module_name, PATIENT_ID , MAX(updated_date) AS lastdate FROM (SELECT uch.patient_id AS patient_id, MAX(uch.updated_date) AS updated_date FROM user_calender_header uch, event_type_lkp el WHERE el.event_type ='Appointment' AND uch.event_type_lkp_id = el.event_type_lkp_id GROUP BY uch.patient_id UNION ALL SELECT ucha.patient_id AS patient_id, MAX(ucha.updated_date) AS updated_date FROM user_calender_header_archive ucha, event_type_lkp el WHERE el.event_type = 'Appointment' AND ucha.event_type_lkp_id = el.event_type_lkp_id GROUP BY ucha.patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Todo', PATIENT_ID , MAX(updated_date) FROM (SELECT uch.patient_id AS patient_id, MAX(uch.updated_date) AS updated_date FROM user_calender_header uch, event_type_lkp el WHERE el.event_type ='To Do' AND uch.event_type_lkp_id = el.event_type_lkp_id GROUP BY uch.patient_id UNION ALL SELECT ucha.patient_id AS patient_id, MAX(ucha.updated_date) AS updated_date FROM user_calender_header_archive ucha, event_type_lkp el WHERE el.event_type = 'To Do' AND ucha.event_type_lkp_id = el.event_type_lkp_id GROUP BY ucha.patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Prescription', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM prescription_lnk GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM prescription_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Shoppinglist', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM item_detail GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM item_detail_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Allergies', PATIENT_ID , MAX(updated_date) FROM (SELECT pil.patient_id AS patient_id, MAX(pil.updated_date) AS updated_date FROM patient_issues_lnk pil, medical_issue_types mit WHERE mit.issue_type_name = 'Allergy' AND pil.issue_type_id = mit.issue_type_id GROUP BY pil.patient_id UNION ALL SELECT pila.patient_id, MAX(pila.updated_date) AS updated_date FROM patient_issues_lnk_archive pila, medical_issue_types mit WHERE mit.issue_type_name = 'Allergy' AND pila.issue_type_id = mit.issue_type_id GROUP BY pila.patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Conditions', PATIENT_ID , MAX(updated_date) FROM (SELECT pil.patient_id AS patient_id, MAX(pil.updated_date) AS updated_date FROM patient_issues_lnk pil, medical_issue_types mit WHERE mit.issue_type_name = 'Condition' AND pil.issue_type_id = mit.issue_type_id GROUP BY pil.patient_id UNION ALL SELECT pila.patient_id, MAX(pila.updated_date) AS updated_date FROM patient_issues_lnk_archive pila, medical_issue_types mit WHERE mit.issue_type_name = 'Condition' AND pila.issue_type_id = mit.issue_type_id GROUP BY pila.patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Procedures', PATIENT_ID , MAX(updated_date) FROM (SELECT pil.patient_id AS patient_id, MAX(pil.updated_date) AS updated_date FROM patient_issues_lnk pil, medical_issue_types mit WHERE mit.issue_type_name = 'Procedures' AND pil.issue_type_id = mit.issue_type_id GROUP BY pil.patient_id UNION ALL SELECT pila.patient_id, MAX(pila.updated_date) AS updated_date FROM patient_issues_lnk_archive pila, medical_issue_types mit WHERE mit.issue_type_name = 'Procedures' AND pila.issue_type_id = mit.issue_type_id GROUP BY pila.patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Treatments', PATIENT_ID , MAX(updated_date) FROM (SELECT pil.patient_id AS patient_id, MAX(pil.updated_date) AS updated_date FROM patient_issues_lnk pil, medical_issue_types mit WHERE mit.issue_type_name = 'Treatment' AND pil.issue_type_id = mit.issue_type_id GROUP BY pil.patient_id UNION ALL SELECT pila.patient_id, MAX(pila.updated_date) AS updated_date FROM patient_issues_lnk_archive pila, medical_issue_types mit WHERE mit.issue_type_name = 'Treatment' AND pila.issue_type_id = mit.issue_type_id GROUP BY pila.patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Personaldata', p.PATIENT_ID , MAX(p.updated_date) FROM patient_profile p GROUP BY p.PATIENT_ID UNION SELECT 'Immunizations', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM immunization_patient_lnk GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM immunization_patient_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Contacts', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM contact_lkp GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM contact_lkp_archive GROUP BY patient_id UNION ALL SELECT cil.patient_id AS patient_id, MAX(ca.updated_date) AS updated_date FROM contact_lkp cil, contact_address ca WHERE ca.contact_lkp_id = cil.contact_lkp_id GROUP BY cil.patient_id UNION ALL SELECT cil.patient_id AS patient_id, MAX(cp.updated_date) AS updated_date FROM contact_lkp cil, contact_phone cp WHERE cp.contact_lkp_id = cil.contact_lkp_id GROUP BY cil.patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Insurance', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM patient_insurance_lnk GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM patient_insurance_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Journal', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM journal_lnk GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM journal_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Legal', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM patient_legal_lnk GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM patient_legal_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Financial', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM patient_financial_lnk GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM patient_financial_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'EmergencyProcedure', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM emergency_procedure_lnk GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM emergency_procedure_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'GroupMessages', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM messages GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM messages_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Invitation', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM user_profile_invites GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM user_profile_invites_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Schedule', PATIENT_ID , MAX(updated_date) FROM (SELECT patient_id, MAX(updated_date) AS updated_date FROM vw_user_calendar_iteration GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM item_detail_archive GROUP BY patient_id UNION ALL SELECT patient_id, MAX(updated_date) AS updated_date FROM prescription_archive GROUP BY patient_id ) GROUP BY PATIENT_ID UNION SELECT 'Notes', uch.patient_id, greatest(MAX(n.updated_date),NVL(MAX(na.updated_date),'27-Oct-2010'),NVL(MAX(uch.updated_date),'27-Oct-2010')) FROM notes_lnk n LEFT JOIN notes_archive na ON n.user_calender_header_id = na.user_calender_header_id LEFT JOIN user_calender_header uch ON n.user_calender_header_id = uch.user_calender_header_id GROUP BY uch.patient_id UNION SELECT 'PatientPicture', pp.patient_id, MAX(pp.updated_date) FROM patient_profile_picture pp GROUP BY pp.patient_id UNION SELECT 'Permissions', urp.patient_id, MAX(urp.updated_date) FROM user_role_patient_lnk urp GROUP BY urp.patient_id UNION SELECT 'Permissions', nrl.patient_id, MAX(nrm.updated_date) FROM network_role_module_permission nrm, network_role_lkp nrl WHERE nrl.network_role_id = nrm.network_role_id GROUP BY nrl.patient_id, nrm.network_role_id; update invitation_templates set messagebody='Hello [$CareGiver],
New contact information has been posted for [$Care Recipient].
Click here to access [$SiteTitle]: Your Caregiving Companion''s site.
Best wishes from all of us at [$SiteTitle], a place for caregivers.
', subject='Contact information added for [$Care Recipient]' where template_type='Add_Contacts'; commit;