[SOLVED] How to create a materialized view in Oracle EBS 12.2 that refreshed daily at 5PM

How to create a materialized view in Oracle EBS 12.2 that refreshed daily at 5PM

I am trying to create a materialized view in the xxcus custom schema in an 11g Oracle database on a Oracle E-Business Suite 12.2 installation that is editioned. I would like to access this materialized view from an Oracle Apex installation on the xxcus schema and also via a database link. Oracle recommends that database links use their own schema called xxcus_ro.

With EBS you have to create materialized views in a special way according to Oracle Support Doc ID 1577661.1. First you create a view:

create or replace view apps.xxar_invoice_totals_mv# as       
   select rctv.trx_number invoice_number,
          rctv.rac_bill_to_customer_name,
          rctv.rac_bill_to_customer_num,
          sum(rctla.extended_amount) invoice_total
   from   apps.ra_customer_trx_v       rctv,
          ar.ra_customer_trx_lines_all rctla
   where  rctla.customer_trx_id = rctv.customer_trx_id
   group  by rctv.trx_number,
          rctv.rac_bill_to_customer_name,
          rctv.rac_bill_to_customer_num;

Then you run exec ad_zd_mview.upgrade('APPS', 'XXAR_INVOICE_TOTALS_MV') to create a materialized view the way Oracle EBS wants it to be created.

While this works and creates the materialized view, the view it creates uses REFRESH FORCE ON DEMAND and there is no parameters for ad_zd_mview.upgrade to specify a refresh interval. The Doc ID 1577661.1 also does not mention anything about refresh.

My original materialized view that I wanted to create before finding out about Doc ID 1577661.1:

create materialized view xxcus.xxar_invoice_totals_mv as
tablespace APPS_TS_TX_DATA
pctused    40
pctfree    10
initrans   2
maxtrans   255
storage    (
            initial          40k
            next             40k
            minextents       1
            maxextents       unlimited
            pctincrease      0
            freelists        1
            freelist groups  1
            buffer_pool      default
           )
nocache
logging
noparallel
build immediate
refresh complete
start with to_date('7-Sep-2020 10:25:00','dd-mon-yyyy hh24:mi:ss')
next to_date(to_char(sysdate+1,'yyyy-mon-dd')||' 20:10','yyyy-mon-dd HH24:MI')
as
select rctv.trx_number invoice_number,
       rctv.rac_bill_to_customer_name,
       rctv.rac_bill_to_customer_num,
       sum(rctla.extended_amount) invoice_total
from   apps.ra_customer_trx_v       rctv,
       ar.ra_customer_trx_lines_all rctla
where  rctla.customer_trx_id = rctv.customer_trx_id
group  by rctv.trx_number,
       rctv.rac_bill_to_customer_name,
       rctv.rac_bill_to_customer_num;

How can I create a materialized view that refreshes daily at 5AM inside of EBS 12.2?

Solution

Try this:

ALTER MATERIALIZED VIEW XXAR_INVOICE_TOTALS_MV
 REFRESH COMPLETE
 NEXT TRUNC(SYSDATE) + 5;

unless you don't have the following restrictions on your materialized view:

https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007007