VIEW_MATERALIZADA ONDEMAND.sql

DBMS_SYNC_REFRESH
EXECUTE DBMS_SYNC_REFRESH.REGISTER_MVIEWS('MV1');
DBMS_MVIEW.REFRESH

DBMS_MVIEW.REFRESH('MVIEW_TGFNST', 'C', '', TRUE, FALSE, 0,0,0, 
   FALSE, FALSE);

CREATE MATERIALIZED VIEW product_sales_mv
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 8M)
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales
FROM sales s, products p WHERE s.prod_id = p.prod_id
GROUP BY p.prod_name;

CREATE MATERIALIZED VIEW unionall_inside_view_mv
REFRESH FAST ON DEMAND AS
SELECT * FROM view_with_unionall;


select *
    from (table(TGFNST_PKG.FCN_TGFNST($P{CODEMP},$P{DTINICIAL} ,$P{DTFINAL}))) O
WHERE O.VLRSUBSTSAI >0
order by O.NUNOTA,O.NUMNOTA,O.CODPARC

CREATE MATERIALIZED VIEW MVIEW_TGFNST
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND AS
(select *
    from (table(TGFNST_PKG.FCN_TGFNST(1,TRUNC(SYSDATE,'MM') ,LAST_DAY(TRUNC(SYSDATE,'MM'))))) O
WHERE O.VLRSUBSTSAI >0
UNION ALL
select *
    from (table(TGFNST_PKG.FCN_TGFNST(1,ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1) ,LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1)) ))) O
WHERE O.VLRSUBSTSAI >0
UNION ALL
select *
    from (table(TGFNST_PKG.FCN_TGFNST(1,ADD_MONTHS(TRUNC(SYSDATE,'MM'),-2) ,LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM'),-2)) ))) O
WHERE O.VLRSUBSTSAI >0
UNION ALL
select *
    from (table(TGFNST_PKG.FCN_TGFNST(2,TRUNC(SYSDATE,'MM') ,LAST_DAY(TRUNC(SYSDATE,'MM'))))) O
WHERE O.VLRSUBSTSAI >0
UNION ALL
select *
    from (table(TGFNST_PKG.FCN_TGFNST(2,ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1) ,LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1)) ))) O
WHERE O.VLRSUBSTSAI >0
UNION ALL
select *
    from (table(TGFNST_PKG.FCN_TGFNST(2,ADD_MONTHS(TRUNC(SYSDATE,'MM'),-2) ,LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM'),-2)) ))) O
WHERE O.VLRSUBSTSAI >0)

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE,LAST_REFRESH_DATE
FROM USER_MVIEWS ORDER BY MVIEW_NAME;

DBMS_MVIEW.REFRESH('MVIEW_TGFNST', 'C', '', TRUE, FALSE, 0,0,0, 
   TRUE, FALSE);