update invitation_templates set messagebody='
Hello $Caregiver,
Thank you for purchasing the [Name of CareGuide] Care Guide from [$SiteTitle]!
Our Care Guides are your caregiving toolkit, giving you premium access to our specialists, and a place to turn for deeper information on your loved one''s specific condition.
Best wishes from all of us at [$SiteTitle], a place for caregivers.
' where template_type='Purchase_Careguide'; update invitation_templates set messagebody='Hello [$CareGiver],
You are asked to please do the following:
[$Name of prescription],[$dosage] for [$Care Recipient] 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.
' where template_type='Add_Prescription'; update site_settings set value='' where variable_name='html_footer'; update invitation_templates set messagebody='Hello [$CareGiver],
New emergency Procedure 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.
' where template_type = 'Add_Emergency_Procedure'; update invitation_templates set messagebody='Hello [$PrimaryCaregiver],
We are happy to tell you that [$SecondaryCaregiver] has accepted your invitation to join the Caregiver Network for $Care Recipient.
Best wishes from all of us at [$SiteTitle], a place for caregivers.
',subject='Invitation Accepted' where template_type='Accept Invites'; CREATE OR REPLACE FORCE VIEW "VW_USER_ROLE_PATIENT_CONTACT" AS SELECT this_.User_Role_Patient_ID AS USER_ROLE_PATIENT_ID, userprofil1_.User_ID AS USER_ID, userprofil1_.User_Email AS USER_EMAIL, userprofil1_.User_Name AS USER_NAME , userprofil1_.First_Name AS FIRST_NAME , userprofil1_.Last_Name AS LAST_NAME , userprofil1_.Middle_Initial AS MIDDLE_INITIAL , userprofil1_.mobile AS MOBILE, networkrol2_.Network_Role_ID AS NETWORK_ROLE_ID, this_.Patient_ID AS PATIENT_ID, NVL(contactlkp_.contact_lkp_id, 0) AS contact_lkp_id, networkrol2_.Network_Role_Name AS NETWORK_ROLE_NAME FROM User_Role_Patient_Lnk this_ LEFT OUTER JOIN User_Profile userprofil1_ ON this_.User_ID=userprofil1_.User_ID LEFT Outer JOIN Network_Role_Lkp networkrol2_ ON this_.Network_Role_ID=networkrol2_.Network_Role_ID LEFT OUTER JOIN contact_lkp contactlkp_ ON (this_.user_id =contactlkp_.member_id and contactlkp_.patient_id = this_.patient_id) WHERE (this_.Is_Active =1 AND (networkrol2_.Network_Role_Name!='Primary Caregiver')); 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 union all select patient_id, max(updated_date) as updated_date from user_calender_header_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,
Thank you for purchasing the [Name of CareGuide] Care Guide from [$SiteTitle]!
Our Care Guides are your caregiving toolkit, giving you premium access to our specialists, and a place to turn for deeper information on your loved one''s specific condition.
Best wishes from all of us at [$SiteTitle], a place for caregivers.
' , subject='Welcome to [$SiteTitle]''s [Name of CareGuide] Care Guide!' where template_type='Purchase_Careguide'; update invitation_templates set messagebody='Hello [$CareGiver],
This is a to-do reminder from your [$SiteTitle] Organizer for [$Care Recipient]:[$what] 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'; commit;