scripts/LJCOPY/MDM/MDM-Post-Upgrade/D2_MEASR_QTY_AGR_MV.sql

set serveroutput on
DECLARE
CNT NUMBER(1);
BEGIN
	SELECT COUNT(*) INTO CNT FROM USER_MVIEWS WHERE MVIEW_NAME='D2_MEASR_QTY_AGR_MV';
	IF (CNT > 0) THEN
		EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW D2_MEASR_QTY_AGR_MV';
		DBMS_OUTPUT.PUT_LINE('D2_MEASR_QTY_AGR_MV View Dropped');
	END IF;	
END;
/

CREATE MATERIALIZED VIEW D2_MEASR_QTY_AGR_MV ORGANIZATION INDEX PARALLEL BUILD DEFERRED USING INDEX REFRESH FORCE ON DEMAND 
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS START WITH SYSDATE DISABLE QUERY REWRITE AS
SELECT
  MEASR_COMP_ID ,
  MSRMT_DTTM ,
  TRUNC(MSRMT_DTTM) AS MSRMT_DT ,
  MSRMT_LOCAL_DTTM,
  TRUNC(MSRMT_LOCAL_DTTM-(1/86400)) AS LOCAL_DT,
  MSRMT_VAL ,
  MSRMT_VAL1 ,
  MSRMT_VAL2 ,
  MSRMT_VAL3 ,
  MSRMT_VAL4 ,
  MSRMT_VAL5 ,
  MSRMT_VAL6 ,
  MSRMT_VAL7 ,
  MSRMT_VAL8 ,
  MSRMT_VAL9 ,
  MSRMT_VAL10
FROM D1_MSRMT
WHERE BUS_OBJ_CD = 'D2-MeasuredQuantityMsrmt'
  AND MEASR_COMP_ID LIKE '000%'
/


begin
DBMS_MVIEW.REFRESH('D2_MEASR_QTY_AGR_MV','C');
END;
/

ANALYZE TABLE D2_MEASR_QTY_AGR_MV COMPUTE STATISTICS
/