Portfolio Table Allocation Fails With ORA-00942: Table Or View Does Not Exist Error (Doc ID 2731246.1)

Last updated on NOVEMBER 24, 2020

Applies to:

Symptoms

On PFT 8.1, after running a static driver allocation where the source is on portfolio table and the output is on management ledger, below error was received in the PFT debug log:

ERROR

Oracle Error: ORA-00942: table or view does not exists

Issue appears to occur for ALL source Portfolio table allocations, however if specific table is selected as source in place of Portfolio, allocation is successful and merge statement is correct.

The error is due to PORTFOLIO being used in the Merge statement instead of correct FSI_D source tables selected in the allocation:

Module Logging OFS Access module data: MERGE INTO FSI_D_MANAGEMENT_LEDGER
TARGET USING (
SELECT *
FROM (
SELECT (SOURCE_AMOUNT * 1)ENTERED_BALANCE, (FUN_AMT_V *
1)FUNCTIONAL_BALANCE, SRC_IOVW.*
FROM (
SELECT SUM(IVWSRC_AMOUNT) SOURCE_AMOUNT,
SUM(IVWSRC_AMOUNT)FUN_AMT_V, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, LEGAL_ENTITY_ID, CHANNEL_ID,
CUST_INFO_ID, CUST_SUB_SEGMENT_ID, PROFIT_CENTER_ID,
CUSTOMER_REP_ID, PORTFOLIOS_ID, ORIG_CURRENCY_ID
FROM (
SELECT SUM(IVWSRC_AMOUNT) IVWSRC_AMOUNT, m.ORG_UNIT_ID,
m.GL_ACCOUNT_ID, m.COMMON_COA_ID, m.PRODUCT_ID,
m.LEGAL_ENTITY_ID, m.CHANNEL_ID, m.CUST_INFO_ID,
m.CUST_SUB_SEGMENT_ID, m.PROFIT_CENTER_ID, m.CUSTOMER_REP_ID,
m.PORTFOLIOS_ID, m.ORIG_CURRENCY_ID, m.ISO_CURRENCY_CD
FROM ((SELECT (( NVL(m.INT_ACCRUAL_LCY, 0)+
NVL(m.INT_PENALTY_LCY, 0)+ NVL(m.INT_OVERDUE_LCY, 0)+
NVL(m.INT_COLLECTED_LCY, 0)+ NVL(m.IRR_LCY, 0)+
NVL(m.INT_NPL_LCY, 0)))* 1 IVWSRC_AMOUNT, m.ORG_UNIT_ID,
m.GL_ACCOUNT_ID, m.COMMON_COA_ID, m.PRODUCT_ID,
m.LEGAL_ENTITY_ID, m.CHANNEL_ID, m.CUST_INFO_ID,
m.CUST_SUB_SEGMENT_ID, m.PROFIT_CENTER_ID, m.CUSTOMER_REP_ID,
m.FSI_D_CREDIT_LINESS_ID, m.ORIG_CURRENCY_ID, m.ISO_CURRENCY_CD
FROM PORTFOLIO m
WHERE m.AS_OF_DATE = '04/ 30/ 2020') UNION ALL (SELECT ((
NVL(m.INT_ACCRUAL_LCY, 0)+ NVL(m.INT_PENALTY_LCY, 0)+
NVL(m.INT_OVERDUE_LCY, 0)+ NVL(m.INT_COLLECTED_LCY, 0)+
NVL(m.IRR_LCY, 0)+ NVL(m.INT_NPL_LCY, 0)))* 1 IVWSRC_AMOUNT,
m.ORG_UNIT_ID, m.GL_ACCOUNT_ID, m.COMMON_COA_ID, m.PRODUCT_ID,
m.LEGAL_ENTITY_ID, m.CHANNEL_ID, m.CUST_INFO_ID,
m.CUST_SUB_SEGMENT_ID, m.PROFIT_CENTER_ID, m.CUSTOMER_REP_ID,
m.FSI_D_LOAN_CONTRACTSS_ID, m.ORIG_CURRENCY_ID, m.ISO_CURRENCY_CD
FROM PORTFOLIO m <= wrong, no actual PORTFOLIO table exists

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.