Oracle DB 12.1.0.2
Hello Team,
can you pls advise on error below?
select * from ALL_SCHEDULER_JOB_RUN_DETAILS where owner='OFSTRIM';
1007336 10-JUN-21 03.10.01.523924000 PM +04:00 OFSTRIM JOBBIGLBALANCE3 FAILED 6550 10-JUN-21 03.10.00.000000000 PM ASIA/DUBAI 10-JUN-21 03.10.01.482474000 PM ASIA/DUBAI +00 00:00:00.000000 1 56,40120 39911460 +00 00:00:00.000000 "ORA-06550: line 1, column 775:
PLS-00357: Table,View Or Sequence reference 'MV_BI_GL_BALANCES' not allowed in this context
ORA-06550: line 1, column 756:
PL/SQL: Statement ignored
" "ORA-06550: line 1, column 775:
PLS-00357: Table,View Or Sequence reference 'MV_BI_GL_BALANCES' not allowed in this context
ORA-06550: line 1, column 756:
PL/SQL: Statement ignored
" (BLOB) (BLOB)
MV:
CREATE MATERIALIZED VIEW OFSTRIM.MV_BI_GL_BALANCES
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS(
SELECT (budget_version || COMPANY || COSTCENTRE || ACCOUNT || SUBACCOUNT || PROJECT || LOB || PARTNER || INTERCOMPANY || DM_FLEX_COMPANY.DESCRIPTION || DM_GLBALANCES.FLOW || DM_GLBALANCES.SPARE || DM_GLBALANCES.ACTUAL_FLAG || DM_GLBALANCES.CURRENCY_CODE || DM_GLBALANCES.ACCOUNT_TYPE || DM_GLBALANCES.PERIOD_NAME || DM_GLBALANCES.PERIOD_NUM || DM_GLBALANCES.PERIOD_YEAR || DM_GLBALANCES.SUMMARY_FLAG || DM_FLEX_COSTCENTRE.DESCRIPTION || DM_FLEX_PROJECT.DESCRIPTION || DM_FLEX_PARTNER.DESCRIPTION || DM_FLEX_LOB.DESCRIPTION || DM_FLEX_INTERCOMPANY.DESCRIPTION || DM_FLEX_FLOW.DESCRIPTION) ID,
DM_FLEX_FLOW.ROWID AS DM_FLEX_FLOW_ROWID ,DM_FLEX_COMPANY.ROWID AS DM_FLEX_COMPANY_ROWID ,DM_FLEX_INTERCOMPANY.ROWID AS DM_FLEX_INTERCOMPANY_ROWID ,DM_FLEX_LOB.ROWID AS DM_FLEX_LOB_ROWID ,DM_FLEX_PARTNER.ROWID AS DM_FLEX_PARTNER_ROWID ,DM_FLEX_COSTCENTRE.ROWID AS DM_FLEX_COSTCENTRE_ROWID ,DM_FLEX_PROJECT.ROWID AS DM_FLEX_PROJECT_ROWID ,DM_FLEX_ACCOUNT.ROWID AS DM_FLEX_ACCOUNT_ROWID ,DM_FLEX_SUBACCOUNT.ROWID AS DM_FLEX_SUBACCOUNT_ROWID ,DM_GLBALANCES.ROWID AS DM_GLBALANCES_ROWID ,
DM_GLBALANCES.BUDGET_VERSION,
DM_GLBALANCES.COMPANY,
DM_GLBALANCES.COSTCENTRE,
DM_GLBALANCES.ACCOUNT,
DM_GLBALANCES.SUBACCOUNT,
DM_GLBALANCES.PROJECT,
DM_GLBALANCES.LOB,
DM_GLBALANCES.PARTNER,
DM_GLBALANCES.INTERCOMPANY,
DM_FLEX_COMPANY.DESCRIPTION COMPANY_DESCRIPTION,
DM_GLBALANCES.FLOW,
DM_GLBALANCES.SPARE,
DM_GLBALANCES.ACTUAL_FLAG,
DM_GLBALANCES.CURRENCY_CODE,
DM_GLBALANCES.ACCOUNT_TYPE,
DM_GLBALANCES.PERIOD_NAME,
DM_GLBALANCES.PERIOD_NUM,
DM_GLBALANCES.PERIOD_YEAR,
DM_GLBALANCES.SUMMARY_FLAG,
DM_FLEX_COSTCENTRE.DESCRIPTION COSTCENTRE_DESCRIPTION,
DM_FLEX_ACCOUNT.DESCRIPTION ACCOUNT_DESCRIPTION,
DM_FLEX_SUBACCOUNT.DESCRIPTION SUBACCOUNT_DESCRIPTION,
DM_FLEX_PROJECT.DESCRIPTION PROJECT_DESCRIPTION,
DM_FLEX_PARTNER.DESCRIPTION PARTNER_DESCRIPTION,
DM_FLEX_LOB.DESCRIPTION LOB_DESCRIPTION,
DM_FLEX_INTERCOMPANY.DESCRIPTION INTERCOMPANY_DESCRIPTION,
DM_FLEX_FLOW.DESCRIPTION FLOW_DESCRIPTION,
NVL (DM_GLBALANCES.BEGINBALANCE, 0) BEGINBALANCE,
NVL (DM_GLBALANCES.PTDBALANCE, 0) PTDBALANCE,
NVL (DM_GLBALANCES.YTDBALANCE, 0) YTDBALANCE
FROM OFSTRIM.MV_DM_FLEX_FLOW DM_FLEX_FLOW,OFSTRIM.MV_DM_FLEX_COMPANY DM_FLEX_COMPANY,OFSTRIM.MV_DM_FLEX_INTERCOMPANY DM_FLEX_INTERCOMPANY,OFSTRIM.MV_DM_FLEX_LOB DM_FLEX_LOB,OFSTRIM.MV_DM_FLEX_PARTNER DM_FLEX_PARTNER,OFSTRIM.MV_DM_FLEX_COSTCENTRE DM_FLEX_COSTCENTRE,OFSTRIM.MV_DM_FLEX_PROJECT DM_FLEX_PROJECT,OFSTRIM.MV_DM_FLEX_ACCOUNT DM_FLEX_ACCOUNT,OFSTRIM.MV_DM_FLEX_SUBACCOUNT DM_FLEX_SUBACCOUNT,OFSTRIM.MV_BI_GL_BALANCES_V DM_GLBALANCES WHERE (DM_FLEX_COMPANY.FLEX_VALUE = DM_GLBALANCES.COMPANY)
AND (DM_FLEX_ACCOUNT.FLEX_VALUE = DM_GLBALANCES.ACCOUNT)
AND (DM_FLEX_PROJECT.FLEX_VALUE = DM_GLBALANCES.PROJECT)
AND (DM_FLEX_COSTCENTRE.FLEX_VALUE = DM_GLBALANCES.COSTCENTRE)
AND (DM_GLBALANCES.LOB = DM_FLEX_LOB.FLEX_VALUE)
AND (DM_FLEX_PARTNER.FLEX_VALUE = DM_GLBALANCES.PARTNER)
AND (DM_FLEX_SUBACCOUNT.FLEX_VALUE = DM_GLBALANCES.SUBACCOUNT)
AND (DM_GLBALANCES.INTERCOMPANY = DM_FLEX_INTERCOMPANY.FLEX_VALUE)
AND (DM_FLEX_FLOW.FLEX_VALUE = DM_GLBALANCES.FLOW))
;
-- DROP MATERIALIZED VIEW LOG ON OFSTRIM.MV_BI_GL_BALANCES;
CREATE MATERIALIZED VIEW LOG ON OFSTRIM.MV_BI_GL_BALANCES
WITH
ROWID ,
SEQUENCE
(DM_FLEX_FLOW_ROWID,DM_FLEX_COMPANY_ROWID,DM_FLEX_INTERCOMPANY_ROWID,DM_FLEX_LOB_ROWID,DM_FLEX_PARTNER_ROWID,DM_FLEX_COSTCENTRE_ROWID,DM_FLEX_PROJECT_ROWID,DM_FLEX_ACCOUNT_ROWID,DM_FLEX_SUBACCOUNT_ROWID,DM_GLBALANCES_ROWID,BUDGET_VERSION,COMPANY,COSTCENTRE,ACCOUNT,SUBACCOUNT,PROJECT,LOB,PARTNER,INTERCOMPANY,COMPANY_DESCRIPTION,FLOW,SPARE,ACTUAL_FLAG,CURRENCY_CODE,ACCOUNT_TYPE,PERIOD_NAME,PERIOD_NUM,PERIOD_YEAR,SUMMARY_FLAG,COSTCENTRE_DESCRIPTION,ACCOUNT_DESCRIPTION,SUBACCOUNT_DESCRIPTION,PROJECT_DESCRIPTION,PARTNER_DESCRIPTION,LOB_DESCRIPTION,INTERCOMPANY_DESCRIPTION,FLOW_DESCRIPTION,BEGINBALANCE,PTDBALANCE,YTDBALANCE )
INCLUDING NEW VALUES;
Thanks,
Roshan