Implementing Materialized View Logs in the Oracle Database

To implement Materialized View Logs in the Oracle database, first determine if you have created customized (non PeopleSoft delivered) Materialized View Logs for a base table by entering the following SQL statement against the Oracle database:

SELECT * FROM USER_SNAPSHOT_LOGS WHERE MASTER='<BASE TABLE>’;

If a customized view log exists, you must configure it to support PeopleSoft requirements. You must also create a DUMMY materialized view to prevent the Materialized View Log data from being purged.

Note: If the existing view log is one of the PeopleSoft delivered view logs, you do not need to configure it. See the following section for a list of delivered Materialized View Logs for SCM Warehouse source tables.

Delivered Materialized View Logs for SCM Warehouse Source Tables

SCM Warehouse delivers Materialized View Logs for the following high volume source tables:

Supply Chain Management Source Table	Delivered Materialized View Log	Corresponding EPM Fact Table
PS_PO_HDR	MLOG$_PS_PO_HDR	PS_F_PO_DIST PS_F_PO_LINE PS_F_PO_SHIP_RCPT PS_F_PROC_CTLT PS_F_RTV PS_F_RTV_DIST
PS_PO_LINE	MLOG$_PS_PO_LINE	PS_F_PO_DIST PS_F_PO_LINE PS_F_PO_SHIP_RCPT PS_F_PROC_CTLT PS_F_RTV PS_F_RTV_DIST
PS_PO_LINE_DISTRIB	MLOG$_PS_PO_LINE_DISTRIB	PS_F_RTV_DIST
PS_PO_LINE_SHIP	MLOG$_PS_PO_LINE_SHIP	PS_F_RTV_DIST
PS_RECV_LN_SHIP	MLOG$_PS_RECV_LN_SHIP	PS_F_PO_SHIP_RCPT
PS_PAYMENT_TBL	MLOG$_PS_PAYMENT_TBL	PS_F_AP_TRAN

Configuring Non-PeopleSoft Delivered Materialized View Logs

If a non-PeopleSoft delivered Materialized View Log exists for the master table of a materialized view, you must configure the view log to support the following requirements:

All base table key columns
ROWID option
SEQUENCE option
INCLUDING NEW VALUES option

For example:

CREATE MATERIALIZED VIEW LOG ON <BASE TABLE> WITH ROWID, SEQUENCE (<BASE TABLE KEY COLUMNS>) INCLUDING NEW VALUES;

Additionally, you must create a DUMMY materialized view table for each materialized view you created. The fast refresh process automatically purges Materialized View Log data when the materialized view is refreshed, so creating a related dummy materialized view will prevent the Materialized View Log data from being purged.

To create a DUMMY materialized view, you can enter a SQL statement against the Oracle database similar to the following:

CREATE MATERIALIZED VIEW <BASE TABLE>_DMV
REFRESH FAST WITH ROWID AS
SELECT <BASE TABLE COLUMN NAME> FROM <BASE TABLE> WHERE 1=2;

OLTP Table Security

Materialized View Log ETL jobs delete Materialized View Log data once the job completes. This normally requires a user to have DELETE permissions, which may jeopardize source data. As such, PeopleSoft provides an additional user (SCHEMA), which has READ and DELETE rights to Materialized View Log data only. This user cannot select or delete records from master tables.

PeopleSoft also delivers two new environment variables, MLOG_USERNAME, MLOG_PASSWORD, and MLOG_SCHEMA for the new user.

Note: Since Materialized View Log ETL jobs delete Materialized View Log data once the job completes, you should refresh non-PeopleSoft materialized views before running the ETL jobs.