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.