update invitation_templates set messagebody = '
Hello $Caregiver,
You haven''t updated your password for past [$days] days. Please update your password.
Best wishes from all of us at [$SiteTitle], a place for caregivers.
' where template_type = 'Password_Reminder'; update Event_Type_Lkp set Event_Type='To Do' where Event_Type='Todo'; update site_settings set value='Strength For Caring' where variable_name='site_title'; update invitation_templates set subject='Reminder: Join $Care Recipient''s caregiver network' where template_type='Resend Invites'; insert into invitation_templates (template_type, messagebody, created_by, created_date, updated_by, updated_date, subject) values ('Resend_Invites_Accept','Hello [$SecondaryCaregiver],
Reminder:
You have been invited by [$PrimaryCaregiver] to join the Caregiver Network for [@Care Recipient] on [$SiteTitle], a place for caregivers.
[$messagefromUser]
',1,sysdate,1,sysdate,'Reminder: Join $Care Recipient''s caregiver network'); update invitation_templates set messagebody='Hello [$SecondaryCaregiver],
Reminder:
You have been invited by [$PrimaryCaregiver] to join the Caregiver Network for [@Care Recipient] on [$SiteTitle], a place for caregivers.
[$messagefromUser]
Register For [$SiteTitle] Reject
' where template_type='Resend Invites'; update invitation_templates set messagebody = 'Hello [$SecondaryCaregiver],
You have been invited by [$PrimaryCaregiver] to join the Caregiver Network for [@Care Recipient] on [$SiteTitle], a place for caregivers.
[$messagefromUser]
Register For [$SiteTitle] Reject
', subject = 'Join [@Care Recipient]''s caregiver network' where template_type='Send Invites'; update site_settings set value='Strength For Caring' where variable_name='site_title'; insert into invitation_templates (template_type, messagebody, created_by, created_date, updated_by, updated_date, subject) values ('Send_Invites_Accept','Hello [$SecondaryCaregiver],
You have been invited by [$PrimaryCaregiver] to join the Caregiver Network for [@Care Recipient] on [$SiteTitle], a place for caregivers.
[$messagefromUser]
',1,sysdate,1,sysdate,'Join [@Care Recipient]''s caregiver network'); update Event_Type_Lkp set Event_Type='To Do' where Event_Type='ToDo'; 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 ='Todo' 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 = 'Todo' 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; CREATE OR REPLACE FORCE VIEW "VW_USER_CALENDAR_ITERATION" AS SELECT UCH."USER_CALENDER_HEADER_ID", UCH."PATIENT_ID", NVL(UCH."EVENT_TYPE_LKP_ID", 0) AS EVENT_TYPE_LKP_ID, UCH."TITLE", 0 AS CATEGORY_ID, '' AS CATEGORY_NAME, NVL(UCH."START_TIME", '') AS START_TIME, 0 AS QUANTITY, 0 AS COST, NVL(UCH."TO_TIME", '') AS TO_TIME, NVL(UCH."WHEN_TO_END", '') AS WHEN_TO_END, NVL(UCH."END_TIME", '') AS END_TIME, NVL(UCH."RECURRENT_ID", 0) AS RECURRENT_ID, NVL(UCH."NOTES", '') AS NOTES, NVL(UCH."ASSIGNED_TO", 0) AS ASSIGNED_TO, NVL(UCH."CONTACT_WITH", '') AS CONTACT_WITH, NVL(UCH."CONTACT_LKP_ID", 0) AS CONTACT_LKP_ID, NVL(UCH."CONTACT_WHERE", '') AS CONTACT_WHERE, CL."FIRST_NAME" AS CONTACT_NAME, UP."FIRST_NAME" AS USER_NAME, NVL(RTL."RECURRENT_TYPE", '') AS RECURRENT_TYPE, NVL(ETL."EVENT_TYPE", '') AS EVENT_TYPE, 0 AS DRUG_ID, '' AS SIDE_EFFECTS, 0 AS CURRENTLY_USED, '' AS SPECIAL_INSTRUCTION, '' AS DOSAGE, 0 AS TIMES_PERDAY, '' AS TIMINGS, UCH."EVENT_LINE_STATUS" AS STATUS, UCH."CREATED_BY", UCH."CREATED_DATE", UCH."UPDATED_BY", UCH."UPDATED_DATE", TO_CHAR(UCH."START_TIME",'D') AS WEEKDAY, TO_CHAR(UCH."START_TIME",'MM') AS MONTHDAY, UP."FIRST_NAME" AS FIRST_NAME, UP."MIDDLE_INITIAL" AS MIDDLE_INITIAL, UP."LAST_NAME" AS LAST_NAME, P."PICTURE_PATH" AS PICTURE_PATH, P."NAME" AS PATIENT_NAME, P."LAST_NAME" AS PATIENT_LAST_NAME, p.nick_name as patient_nick_name, (SELECT COUNT(*) FROM NOTES_LNK WHERE USER_CALENDER_HEADER_ID = UCH.USER_CALENDER_HEADER_ID ) AS NOTES_COUNT, UCH.ADD_TO_CALENDAR, uch.istimebound FROM User_Calender_Header UCH LEFT OUTER JOIN CONTACT_LKP CL ON CL.CONTACT_LKP_ID = UCH.CONTACT_LKP_ID LEFT OUTER JOIN USER_PROFILE UP ON UP.USER_ID = UCH.ASSIGNED_TO LEFT OUTER JOIN RECURRENT_TYPE_LKP RTL ON RTL.RECURRENT_ID = UCH.RECURRENT_ID LEFT OUTER JOIN EVENT_TYPE_LKP ETL ON ETL.EVENT_TYPE_LKP_ID = UCH.EVENT_TYPE_LKP_ID LEFT OUTER JOIN PATIENT_PROFILE P ON UCH.PATIENT_ID = P.PATIENT_ID UNION SELECT ITD."ITEMDETAIL_ID", ITD."PATIENT_ID", -1 AS EVENT_TYPE_LKP_ID, IL."ITEM_NAME" AS TITLE, IL."ITEM_CATEGORY_ID" AS CATEGORY_ID, NVL(ICL."ITEM_CATEGORY_NAME", '') AS CATEGORY_NAME, NVL(ITD."START_DATE", '') AS START_DATE, ITD."QUANTITY" AS QUANTITY, ITD."ITEM_COST" AS COST, NVL(ITD."TO_TIME",'') AS TO_TIME, NVL(ITD."WHEN_TO_END", '') AS WHEN_TO_END, NVL(ITD."END_TIME", '') AS END_TIME, ITD."RECURRENT_ID" AS RECURRENT_ID, '' AS NOTES, NVL(ITD."ASSIGNED_USER_ID", 0) AS ASSIGNED_USER_ID, '' AS CONTACT_WITH, 0 AS CONTACT_LKP_ID, NVL(ITD."STORE_LOCATION", '') AS CONTACT_WHERE, '' AS CONTACT_NAME, UP."FIRST_NAME" AS USER_NAME, NVL(RTL."RECURRENT_TYPE", '') AS RECURRENT_TYPE, 'Item' AS EVENT_TYPE, 0 AS DRUG_ID, '' AS SIDE_EFFECTS, 0 AS CURRENTLY_USED, '' AS SPECIAL_INSTRUCTION, '' AS DOSAGE, 0 AS TIMES_PERDAY, '' AS TIMINGS, ITD."IS_ACTIVE" AS STATUS, ITD."CREATED_BY", ITD."CREATED_DATE", ITD."UPDATED_BY", ITD."UPDATED_DATE", TO_CHAR(ITD."START_DATE",'D') AS WEEKDAY, TO_CHAR(ITD."START_DATE",'MM') AS MONTHDAY, UP."FIRST_NAME" AS FIRST_NAME, UP."MIDDLE_INITIAL" AS MIDDLE_INITIAL, UP."LAST_NAME" AS LAST_NAME, P."PICTURE_PATH" AS PICTURE_PATH, P."NAME" AS PATIENT_NAME, P."LAST_NAME" AS PATIENT_LAST_NAME, p.nick_name as patient_nick_name, 0 AS NOTES_COUNT, ITD.ADD_TO_CALENDAR, itd.istimebound FROM ITEM_DETAIL ITD LEFT OUTER JOIN ITEM_LKP IL ON IL.ITEM_LKP_ID = ITD.ITEM_LKP_ID LEFT OUTER JOIN PATIENT_PROFILE P ON ITD.PATIENT_ID = P.PATIENT_ID LEFT OUTER JOIN ITEM_CATEGORY_LKP ICL ON ICL.ITEM_CATEGORY_ID = IL.ITEM_CATEGORY_ID LEFT OUTER JOIN USER_PROFILE UP ON UP.USER_ID = ITD.ASSIGNED_USER_ID LEFT OUTER JOIN RECURRENT_TYPE_LKP RTL ON RTL.RECURRENT_ID = ITD.RECURRENT_ID WHERE ITD.ISTIMEBOUND = 1 UNION SELECT PL.PRESCRIPTION_ID, PL.PATIENT_ID, -2 AS EVENT_TYPE_LKP_ID, PL."DRUG_NAME" AS TITLE, 0 AS CATEGORY_ID, '' AS CATEGORY_NAME, NVL(PL."START_DATE", '') AS START_DATE, 0 AS QUANTITY, 0 AS COST, NVL(PL."START_DATE", '') AS TO_TIME, 'Until' AS WHEN_TO_END, NVL(PL."END_DATE", '') AS END_TIME, RL.RECURRENT_ID AS RECURRENT_ID, NVL(PL."NOTES",'') AS NOTES, 0 AS ASSIGNED_USER_ID, '' AS CONTACT_WITH, 0 AS CONTACT_LKP_ID, NVL(PL."PHARMACY", '') AS CONTACT_WHERE, '' AS CONTACT_NAME, '' AS USER_NAME, RL.RECURRENT_TYPE AS RECURRENT_TYPE, 'Prescription' AS EVENT_TYPE, 0 AS DRUG_ID, '' AS SIDE_EFFECTS, PL.CURRENTLY_USED AS CURRENTLY_USED, '' AS SPECIAL_INSTRUCTION, PL."DOSAGE" AS DOSAGE, NVL(PL."TIMES_PERDAY",0) AS TIMES_PERDAY, NVL(PL."TIMINGS",'') AS TIMINGS, 0 AS STATUS, PL."CREATED_BY", PL."CREATED_DATE", PL."UPDATED_BY", PL."UPDATED_DATE", TO_CHAR(PL."START_DATE",'D') AS WEEKDAY, TO_CHAR(PL."START_DATE",'MM') AS MONTHDAY, '' AS FIRST_NAME, '' AS MIDDLE_INITIAL, '' AS LAST_NAME, P."PICTURE_PATH" AS PICTURE_PATH, P."NAME" AS PATIENT_NAME, P."LAST_NAME" AS PATIENT_LAST_NAME, p.nick_name as patient_nick_name, 0 AS NOTES_COUNT, PL.ADD_TO_CALENDAR, 0 AS istimebound FROM PRESCRIPTION_LNK PL LEFT OUTER JOIN PATIENT_PROFILE P ON PL.PATIENT_ID = P.PATIENT_ID LEFT OUTER JOIN RECURRENT_TYPE_LKP RL ON RL.RECURRENT_TYPE = 'Daily'; update invitation_templates set messagebody='Dear [$Caregiver],
Thank you for ordering an annual subscription to [$SiteTitle] Private Care Network!
Through this network package you can team up with your family and friends to take care of your loved ones.
Order Details:
[$Order form]
Please let us know how [$SiteTitle] can make taking care of your loved ones more effective. We are here to help.
Best wishes from all of us at [$SiteTitle], a place for caregivers.
' where template_type = 'PCN Order'; 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 [$PrimaryCaregiver],
We just wanted to inform you that [$SecondaryCaregiver] has chosen not to accept your invitation to join the Caregiver Network for $Care Recipient.
Best wishes from all of us at [$SiteTitle], a place for caregivers.
' where template_type='Reject Invites'; delete from SITE_SETTINGS where variable_name='provider_url'; Insert into SITE_SETTINGS (VARIABLE_NAME,VALUE) values ('provider_url','jnp://127.0.0.1:1099'); commit;