Skip to Main Content
Return to Navigation

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 PeopleSoft Financial Management source tables.

Delivered Materialized View Logs for PeopleSoft Financial Management Source Tables

The following table provides a list of delivered Materialized View Logs and their corresponding source tables for the FMS Warehouse. The table also provides the EPM fact tables related to a Materialized View Log; these fact tables track deleted source records with the help of the Materialized View Log.

Financial Management Source Table

Delivered Materialized View Log

Corresponding EPM Fact Table

PS_AP_MTCH_EXCPTN

MLOG$_PS_AP_MTCH_EXCPTN

PS_F_VCHR_MTCH_EXP

PS_CA_ACCTG_LINE

MLOG$_PS_CA_ACCTG_LINE

PS_F_CA_REV_RECOGN

PS_CA_ACCTG_LN_PC

MLOG$_PS_CA_ACCTG_LN_PC

PS_F_CA_REV_RECOGN

PS_CA_CONTR_HDR

MLOG$_PS_CA_CONTR_HDR

PS_F_CA_AMD

PS_F_CA_DTL_TRANS

PS_F_CA_REV_RECOGN

PS_F_CA_DTL_DIST

PS_F_CA_RNW

PS_CA_DETAIL

MLOG$_PS_CA_DETAIL

PS_F_CA_DTL_TRANS

PS_F_CA_REV_RECOGN

PS_F_CA_DTL_DIST

PS_F_CA_CUR_FRCST

PS_F_CA_PRDC_FRCST

PS_CA_DETAIL_PROJ

MLOG$_PS_CA_DETAIL_PROJ

PS_F_GM_AWARD

PS_F_GM_PRJ_TRAN

PS_CUST_HIST_E_TBL

MLOG$_PS_CUST_HIST_E_TBL

PS_F_AR_DSO_E

PS_DEPOSIT_CONTROL

MLOG$_PS_DEPOSIT_CONTROL

PS_F_AR_TRAN

PS_GM_BU_AWD_SETUP

MLOG$_PS_GM_BU_AWD_SETUP

PS_F_GM_AWARD

PS_ITEM

MLOG$_PS_ITEM

PS_F_AR_TRAN

PS_F_AR_ACCOUNT_LN

PS_ITEM_ACTIVITY

MLOG$_PS_ITEM_ACTIVITY

PS_F_AR_TRAN

PS_ITEM_DST

MLOG$_PS_ITEM_DST

PS_F_AR_ACCOUNT_LN

PS_KK_LIQUIDATION

MLOG$_PS_KK_LIQUIDATION

PS_F_KK_ENCUMBRAN

PS_KK_REFERENCED

MLOG$_PS_KK_REFERENCED

PS_F_KK_ENCUMBRAN

PS_KK_SOURCE_LN

MLOG$_PS_KK_SOURCE_LN

PS_F_KK_TRANS_LOG

PS_KK_TRANS_LOG

MLOG$_PS_KK_TRANS_LOG

PS_F_KK_TRANS_LOG

PS_PAY_MISC_DST

MLOG$_PS_PAY_MISC_DST

PS_F_AR_ACCOUNT_LN

PS_PAYMENT

MLOG$_PS_PAYMENT

PS_F_AR_TRAN

PS_PAYMENT_TBL

MLOG$_PS_PAYMENT_TBL

PS_F_AR_TRAN

PS_PC_BUD_DETAIL

MLOG$_PS_PC_BUD_DETAIL

PS_F_GM_AWARD

PS_PROJ_ACTIVITY

MLOG$_PS_PROJ_ACTIVITY

PS_F_PRJ_AC_CUR

PS_F_PRJ_AC_PRDC

PS_F_PRJ_AC_CUR

PS_F_PRJ_AC_PRDC

PS_PROJ_RESOURCE

MLOG$_PS_PROJ_RESOURCE

PS_F_GM_PRJ_TRAN

PS_F_PRJ_TRAN

PS_F_RSRC_RT

PS_PROJECT

MLOG$_PS_PROJECT

PS_F_PRJ_PRDC

PS_F_PRJ_CUR

PS_F_PRJ_PRDC

PS_F_PRJ_CUR

PS_PYMNT_VCHR_XREF

MLOG$_PS_PYMNT_VCHR_XREF

PS_F_AP_TRAN

PS_VOUCHER

MLOG$_PS_VOUCHER

PS_F_AP_TRAN

PS_F_VCHR_MTCH_EXP

PS_VOUCHER_LINE

MLOG$_PS_VOUCHER_LINE

PS_F_MTCH_ANLYS

PS_F_VCHR_DIST_LN

PS_F_VCHR_LN

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.