title: How to create a materialized view in Oracle EBS 12.2 that refreshed daily at 5PM
body: <p>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.</p>
  <p>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:</p>
  <pre><code>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;
  </code></pre>
  <p>Then you run <code>exec ad_zd_mview.upgrade('APPS', 'XXAR_INVOICE_TOTALS_MV')</code> to create a materialized view the way Oracle EBS wants it to be created.</p>
  <p>While this works and creates the materialized view, the view it creates uses <code>REFRESH FORCE ON DEMAND</code> and there is no parameters for <code>ad_zd_mview.upgrade</code> to specify a refresh interval. The Doc ID 1577661.1 also does not mention anything about refresh.</p>
  <p>My original materialized view that I wanted to create before finding out about Doc ID 1577661.1:</p>
  <pre><code>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;
  </code></pre>
  <p>How can I create a materialized view that refreshes daily at 5AM inside of EBS 12.2?</p>