
-------------------------------------------------------------------------------------------------------------------------------------
--  surveys_responses_mv
-------------------------------------------------------------------------------------------------------------------------------------
DECLARE
 v_code             NUMBER;
 v_errm             VARCHAR2(64);
 no_object_found    EXCEPTION;
 no_sequence_found  EXCEPTION;
 PRAGMA EXCEPTION_INIT(no_object_found, -942);
 PRAGMA EXCEPTION_INIT(no_sequence_found, -2289);
BEGIN
 EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_RESPONSES_MV';
EXCEPTION
 WHEN no_object_found
 THEN
    NULL;
 WHEN no_sequence_found
 THEN
    NULL;
 WHEN OTHERS
 THEN
    v_code  := SQLCODE;
    v_errm  := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.put_line('Error code ' || v_code || ': ' || v_errm);
END;
/

--DROP MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_RESPONSES_MV;
CREATE MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_RESPONSES_MV (TITLE,VERSION,SURVEY_DATE,VISN,STATION_NO,RECORD_COUNT,MENTAL_SCORE_SUM,PHYSICAL_SCORE_SUM)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('22-08-2013 03:00:00','dd-mm-yyyy hh24:mi:ss')
NEXT TRUNC (SYSDATE + 1) + 3 / 24                
WITH PRIMARY KEY
-- Comment out the ENABLE QUERY REWRITE as there seems to be a problem with NVL function when it is enabled
--ENABLE QUERY REWRITE
AS 
SELECT   s.title, s.version, TRUNC (sr.completed_dt) survey_date, sf.visn, sf.station_no, COUNT (*) record_count, NVL (ROUND (SUM (sr.mental_score), 3), 0) mental_score_sum,
         NVL (ROUND (SUM (sr.physical_score), 3), 0) physical_score_sum
    FROM SURVEY.survey_response sr, SURVEY.survey s, SURVEY.facility sf
   WHERE sr.survey_id = s.survey_id AND sr.mental_score IS NOT NULL AND sr.physical_score IS NOT NULL AND sr.survey_accepted_status = 'ACCEPTED' AND sr.facility_id = sf.facility_id
GROUP BY TRUNC (sr.completed_dt), sf.station_no, sf.visn, s.version, s.title;


-------------------------------------------------------------------------------------------------------------------------------------
--  surveys_vendor_mv 
-------------------------------------------------------------------------------------------------------------------------------------
DECLARE
 v_code             NUMBER;
 v_errm             VARCHAR2(64);
 no_object_found    EXCEPTION;
 no_sequence_found  EXCEPTION;
 PRAGMA EXCEPTION_INIT(no_object_found, -942);
 PRAGMA EXCEPTION_INIT(no_sequence_found, -2289);
BEGIN
 EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_VENDOR_MV';
EXCEPTION
 WHEN no_object_found
 THEN
    NULL;
 WHEN no_sequence_found
 THEN
    NULL;
 WHEN OTHERS
 THEN
    v_code  := SQLCODE;
    v_errm  := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.put_line('Error code ' || v_code || ': ' || v_errm);
END;
/

--DROP MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_VENDOR_MV;
CREATE MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_VENDOR_MV (TITLE,VERSION,VENDOR_FACILITY,LAST_SURVEY_DATE,RECORD_COUNT)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('22-08-2013 03:00:00','dd-mm-yyyy hh24:mi:ss')
NEXT TRUNC (SYSDATE + 1) + 3 / 24       
WITH PRIMARY KEY
--ENABLE QUERY REWRITE
AS 
SELECT   s.title, s.version, v.vendor_facility, TO_CHAR (MAX (sr.completed_dt), 'YYYY-MM-DD HH24:MI:SS TZHTZM') last_survey_date, COUNT (*) record_count
    FROM SURVEY.survey_response sr, SURVEY.vendor v, SURVEY.survey s
   WHERE sr.vendor_id = v.vendor_id AND sr.survey_id = s.survey_id
GROUP BY s.title, s.version, v.vendor_facility;


-------------------------------------------------------------------------------------------------------------------------------------
--  surveys_survey_type_mv
-------------------------------------------------------------------------------------------------------------------------------------
DECLARE
 v_code             NUMBER;
 v_errm             VARCHAR2(64);
 no_object_found    EXCEPTION;
 no_sequence_found  EXCEPTION;
 PRAGMA EXCEPTION_INIT(no_object_found, -942);
 PRAGMA EXCEPTION_INIT(no_sequence_found, -2289);
BEGIN
 EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_SURVEY_TYPE_MV';
EXCEPTION
 WHEN no_object_found
 THEN
    NULL;
 WHEN no_sequence_found
 THEN
    NULL;
 WHEN OTHERS
 THEN
    v_code  := SQLCODE;
    v_errm  := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.put_line('Error code ' || v_code || ': ' || v_errm);
END;
/

--DROP MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_SURVEY_TYPE_MV;
CREATE MATERIALIZED VIEW SURVEYS_MV_SO.SURVEYS_SURVEY_TYPE_MV (TITLE,VERSION,SURVEY_ACCEPTED_STATUS,RECORD_COUNT)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('22-08-2013 03:00:00','dd-mm-yyyy hh24:mi:ss')
NEXT TRUNC (SYSDATE + 1) + 3 / 24       
WITH PRIMARY KEY
--ENABLE QUERY REWRITE
AS 
SELECT   s.title, s.version, sr.survey_accepted_status, COUNT (*) record_count
    FROM SURVEY.survey_response sr, SURVEY.survey s
   WHERE sr.survey_id = s.survey_id
GROUP BY s.title, s.version, sr.survey_accepted_status;


-- create indexes on materialized views
CREATE INDEX SURVEYS_MV_SO.SURVEYS_RESPONSES_MV_STATION_I ON SURVEYS_MV_SO.SURVEYS_RESPONSES_MV
(SURVEY_DATE, STATION_NO, TITLE, VERSION, VISN) ONLINE
LOGGING
NOPARALLEL;

CREATE INDEX SURVEYS_MV_SO.SURVEYS_RESPONSES_MV_TITLE_I ON SURVEYS_MV_SO.SURVEYS_RESPONSES_MV
(SURVEY_DATE, TITLE, VERSION, VISN, STATION_NO) ONLINE
LOGGING
NOPARALLEL;

CREATE INDEX SURVEYS_MV_SO.SURVEYS_RESPONSES_MV_VISN_I ON SURVEYS_MV_SO.SURVEYS_RESPONSES_MV
(SURVEY_DATE, VISN, STATION_NO, TITLE, VERSION) ONLINE
LOGGING
NOPARALLEL;

CREATE INDEX SURVEYS_MV_SO.SURVEYS_VENDOR_MV_I ON SURVEYS_MV_SO.SURVEYS_VENDOR_MV
(VENDOR_FACILITY, TITLE, VERSION, LAST_SURVEY_DATE) ONLINE
LOGGING
NOPARALLEL;

CREATE INDEX SURVEYS_MV_SO.SURVEYS_SURVEY_TYPE_MV_I ON SURVEYS_MV_SO.SURVEYS_SURVEY_TYPE_MV
(TITLE, VERSION, SURVEY_ACCEPTED_STATUS) ONLINE
LOGGING
NOPARALLEL;


-- grant select on materialized views to SURVEY
GRANT SELECT ON SURVEYS_MV_SO.SURVEYS_RESPONSES_MV TO SURVEY;

GRANT SELECT ON SURVEYS_MV_SO.SURVEYS_VENDOR_MV TO SURVEY;

GRANT SELECT ON SURVEYS_MV_SO.SURVEYS_SURVEY_TYPE_MV TO SURVEY;
