Materialized View not refreshing

[rumasinha]

Hi,
I created materialized view for complete refresh with the below syntax:

CREATE MATERIALIZED VIEW xyz_mv
TABLESPACE apps_ts_summary
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
NEXT (SYSDATE + 1)
AS
....

This involves count,union all...
When I create this materialized view, it displays data but next day, this view is null.. it donot have any data..

Not sure why as I have given sysdate+1 as next refresh date

when I create the view with the below syntax

CREATE MATERIALIZED VIEW XMCI_AP_PO_SCORECARD_MV
TABLESPACE apps_ts_summary
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE
NEXT (SYSDATE + 1)
AS

Then the view do not have any data after few seconds..

Can anybody help me out as I am totally lost here.

Thanks
Ruma

[pando]

what if you refresh manually and have you checked snp logs?

[rumasinha]

when refreshing using exec dbms_mview.refresh(xyz_MV','c'); the data comes..

I am using org striped views like po_headers etc hence first i set the set_client_info then I refresh else no data comes.

Does this org striped views creating refresh problem..

[pando]

if you are running Oracle E-Business Suite then you must set the environment in order to get data such as NLS_LANGUAGE, ORG ID etc

[rumasinha]

How can you set these values in case trying to refresh automatically?

[pando]

trace a user session and see what packages they run to set the variables

[rumasinha]

Hi,

I first created a materialized view that was based on the org striped views like po_headers hence the automatic refresh was not happening.
After automatic complete refresh, the materialized view was showing null records.

Hence I based the materialized view on "_all" tables and then based a view on this materialized view and this view I made org striped.

In the materialized view , I was refering to ap_invoices_pkg.get_approval_status() this pkg internally uses org striped views like ap_invoices so I made a wrapper on top of this where I set the org context first and then I call the ap_invoices_pkg.

This wrapper I call in my materialized view query..

Is this approach correct in oracle apps.. I am doing this for the first time in apps..

Thanks
Ruma

[pando]

sorry dont have Oracle eBiz to test that for you