CREATE OR REPLACE FORCE VIEW "VW_PRICING_LIST" ("TYPE", "PRICING_ITEM_ID", "PRICING_CATALOUGE_ID", "PRICING_ITEM_NAME", "TYPE_ID", "PRICING_ITEM_TYPE_NAME", "USER_ID", "PAYMENT_STATUS", "PATIENT_ID") AS SELECT PC.PRICING_OBJECT_TYPE AS TYPE, PI.PRICING_ITEM_ID, PC.PRICING_CATALOUGE_ID, PI.PRICING_ITEM_NAME, PI.PRICING_ITEM_TYPE_ID AS TYPE_ID, PT.PRICING_ITEM_TYPE_NAME, UP.USER_ID, ph.payment_status, NVL(ppt.patient_id,0) AS patient_id FROM PRICING_ITEM PI, PRICING_CATALOUGE PC, PRICING_ITEM_TYPE_LKP PT, PRICING_PURCHASE_HISTORY PH, PRICING_PURCHASE_TRANSACTION PPT, USER_PROFILE UP WHERE PC.PRICING_OBJECT_ID = PI.PRICING_ITEM_ID AND PC.PRICING_OBJECT_TYPE = 'item' AND PI.PRICING_ITEM_TYPE_ID = PT.PRICING_ITEM_TYPE_ID AND PH.USER_ID = UP.USER_ID AND PH.PACKAGE_ID = PPT.PACKAGE_ID AND PPT.PRICING_CATALOGUE_ID = PC.PRICING_CATALOUGE_ID UNION SELECT PC.PRICING_OBJECT_TYPE, PP.PRICING_PACKAGE_ID, PC.PRICING_CATALOUGE_ID, PP.PRICING_PACKAGE_NAME, 0, PP.PRICING_PACKAGE_TYPE, UP.USER_ID, ph.payment_status, NVL(ppt.patient_id,0) AS patient_id FROM PRICING_PACKAGE PP, PRICING_CATALOUGE PC, PRICING_PURCHASE_HISTORY PH, PRICING_PURCHASE_TRANSACTION PPT, USER_PROFILE UP WHERE PC.PRICING_OBJECT_ID = PP.PRICING_PACKAGE_ID AND PC.PRICING_OBJECT_TYPE = 'plan' AND PC.PRICING_PLAN_STATUS = 1 AND PH.USER_ID = UP.USER_ID AND PH.PACKAGE_ID = PPT.PACKAGE_ID AND PPT.PRICING_CATALOGUE_ID = PC.PRICING_CATALOUGE_ID; CREATE TABLE "SITE_SETTINGS" ( "ID" NUMBER(10,0) NOT NULL ENABLE, "VARIABLE_NAME" VARCHAR2(500 CHAR), "VALUE" VARCHAR2(500 CHAR) ); / ALTER TABLE "SITE_SETTINGS" ADD CONSTRAINT "SITE_SETTINGS_PK" PRIMARY KEY ( "ID" ); / CREATE SEQUENCE "SITE_SETTINGS_ID_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ; CREATE OR REPLACE TRIGGER "SITE_SETTINGS_ID_TRG" before INSERT ON "SITE_SETTINGS" FOR EACH row BEGIN IF inserting THEN IF :NEW."ID" IS NULL THEN SELECT SITE_SETTINGS_ID_SEQ.nextval INTO :NEW."ID" FROM dual; END IF; END IF; END; / ALTER TRIGGER "SITE_SETTINGS_ID_TRG" ENABLE; insert into site_settings (variable_name, value) values('baseurl','http://strengthforcaring.jnj.cl.datapipe.net'); CREATE OR REPLACE FORCE VIEW "VW_USER_CALENDAR_ITERATION" ("USER_CALENDER_HEADER_ID", "PATIENT_ID", "EVENT_TYPE_LKP_ID", "TITLE", "CATEGORY_ID", "CATEGORY_NAME", "START_TIME", "QUANTITY", "COST", "TO_TIME", "WHEN_TO_END", "END_TIME", "RECURRENT_ID", "NOTES", "ASSIGNED_TO", "CONTACT_WITH", "CONTACT_LKP_ID", "CONTACT_WHERE", "CONTACT_NAME", "USER_NAME", "RECURRENT_TYPE", "EVENT_TYPE", "DRUG_ID", "SIDE_EFFECTS", "CURRENTLY_USED", "SPECIAL_INSTRUCTION", "DOSAGE", "TIMES_PERDAY", "TIMINGS", "STATUS", "CREATED_BY", "CREATED_DATE", "UPDATED_BY", "UPDATED_DATE", "WEEKDAY", "MONTHDAY", "FIRST_NAME", "MIDDLE_INITIAL", "LAST_NAME", "PICTURE_PATH", "PATIENT_NAME", "PATIENT_LAST_NAME", "NOTES_COUNT", "ADD_TO_CALENDAR", "ISTIMEBOUND") 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, (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, 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."END_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, 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'; CREATE OR REPLACE FORCE VIEW "VW_PATIENT_MEASUREMENT" AS SELECT l.patient_measurement_lnk_id, l.measured_by, l.measurement_name, l.measurement_time, l.notes, l.measurement, l.fetch_from_contact, measurement_type.measurement_type_lkp_id, measurement_type.measurement_type, measurement_unit.measurement_unit_lkp_id, measurement_unit.measurement_unit, p_profile.patient_id, p_profile.name, CASE WHEN (l.measured_by IS NOT NULL AND l.measured_by >0) THEN cl.first_name ELSE measure_by END AS measured_user_name FROM patient_measurement_lnk l LEFT JOIN contact_lkp cl ON cl.contact_lkp_id = l.measured_by LEFT JOIN MEASUREMENT_LNK m_lnk ON l.measurement_lnk_id=m_lnk.measurement_lnk_id LEFT JOIN MEASUREMENT_TYPE_LKP measurement_type ON m_lnk.measurement_type_lkp_id=measurement_type.measurement_type_lkp_id LEFT JOIN MEASUREMENT_UNIT_LKP measurement_unit ON m_lnk.measurement_unit_lkp_id=measurement_unit.measurement_unit_lkp_id LEFT JOIN PATIENT_PROFILE p_profile ON L.PATIENT_ID=p_profile.PATIENT_ID; CREATE OR REPLACE FORCE VIEW vw_user_role_patient_details as select urpl.user_role_patient_id, urpl.user_id, urpl.patient_id, urpl.network_role_id, up.user_email, up.first_name, p.name, urpl.is_active, p.is_active as patient_is_active, p.is_deleted from user_role_patient_lnk urpl left join user_profile up on up.user_id = urpl.user_id left join patient_profile p on p.patient_id = urpl.patient_id; insert into permission_lkp (permission_name, permission_description, created_by, created_date, updated_by, updated_date) values ('AssignedTo', 'Assigned To',1,sysdate,1,sysdate); commit;