Errors Running Table to Table (T2Ts) for Profitability Analytics (PFTBI) 5.1 Setup (Doc ID 1286879.1)

Last updated on JANUARY 27, 2011

Applies to:

Oracle Financial Services Profitability Analytics - Version: 5.1 and later   [Release: 5 and later ]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) - Version 7.1
Oracle Financial Services Analytical Applications (OFSAA) - Version 5.1

Symptoms

On Profitability Analytics (PFTBI) 5.1, when attempting to run Step 3. Table To Table (T2Ts) to move data from the Instrument tables to the FCT_< >_ACCOUNT_SUMMARY tables as documented in the Oracle Financial Services Profitability Analytics User Manual, Version: 5.1, Release 4 the following error occurs:

ERROR
2010-10-05 | 11:22:07 | t2tserver | DEBUG | | | ClsUTableToTableLoad::directLoad, From clause : FROM FSI_D_RETIREMENT_ACCOUNTS INNER JOIN DIM_COMMON_COA ON DIM_COMMON_COA.N_COMMON_COA_ID = FSI_D_RETIREMENT_ACCOUNTS.COMMON_COA_ID INNER JOIN DIM_CURRENCY ON DIM_CURRENCY.V_ISO_CURRENCY_CD = FSI_D_RETIREMENT_ACCOUNTS.ISO_CURRENCY_CD INNER JOIN DIM_GL_ACCOUNT ON DIM_GL_ACCOUNT.N_GL_ACCOUNT_ID = FSI_D_RETIREMENT_ACCOUNTS.GL_ACCOUNT_ID INNER JOIN DIM_ORG_UNIT ON DIM_ORG_UNIT.N_ORG_UNIT_ID = FSI_D_RETIREMENT_ACCOUNTS.ORG_UNIT_ID INNER JOIN DIM_PRODUCT ON DIM_PRODUCT.N_PRODUCT_ID = FSI_D_RETIREMENT_ACCOUNTS.PRODUCT_ID LEFT OUTER JOIN DIM_GEOGRAPHY ON DIM_GEOGRAPHY.v_acct_branch_code=FSI_D_RETIREMENT_ACCOUNTS.BRANCH_CD LEFT OUTER JOIN FSI_EXCHANGE_RATE_HIST ON FSI_EXCHANGE_RATE_HIST.FROM_CURRENCY_CD = FSI_D_RETIREMENT_ACCOUNTS.ISO_CURRENCY_CD AND FSI_EXCHANGE_RATE_HIST.EFFECTIVE_DATE = FSI_D_RETIREMENT_ACCOUNTS.AS_OF_DATE AND FSI_EXCHANGE_RATE_HIST.TO_CURRENCY_CD = [DRCY] WHERE 1=1 AND FSI_D_RETIREMENT_ACCOUNTS.AS_OF_DATE=to_date('20101005','YYYYMMDD') AND DIM_COMMON_COA.F_LATEST_RECORD_INDICATOR='Y' AND DIM_GL_ACCOUNT.F_LATEST_RECORD_INDICATOR='Y' AND DIM_ORG_UNIT.F_LATEST_RECORD_INDICATOR='Y' AND DIM_PRODUCT.F_LATEST_RECORD_INDICATOR='Y'
2010-10-05 | 11:22:07 | t2tserver | DEBUG | | | ClsUTableToTableLoad::directLoad, Query for fetching the number of rows returned : select case when count(*) = 1 then sum(inline_view.rec_count) else count(*) end from ( SELECT COUNT(*) rec_count FROM FSI_D_RETIREMENT_ACCOUNTS INNER JOIN DIM_COMMON_COA ON DIM_COMMON_COA.N_COMMON_COA_ID = FSI_D_RETIREMENT_ACCOUNTS.COMMON_COA_ID INNER JOIN DIM_CURRENCY ON DIM_CURRENCY.V_ISO_CURRENCY_CD = FSI_D_RETIREMENT_ACCOUNTS.ISO_CURRENCY_CD INNER JOIN DIM_GL_ACCOUNT ON DIM_GL_ACCOUNT.N_GL_ACCOUNT_ID = FSI_D_RETIREMENT_ACCOUNTS.GL_ACCOUNT_ID INNER JOIN DIM_ORG_UNIT ON DIM_ORG_UNIT.N_ORG_UNIT_ID = FSI_D_RETIREMENT_ACCOUNTS.ORG_UNIT_ID INNER JOIN DIM_PRODUCT ON DIM_PRODUCT.N_PRODUCT_ID = FSI_D_RETIREMENT_ACCOUNTS.PRODUCT_ID LEFT OUTER JOIN DIM_GEOGRAPHY ON DIM_GEOGRAPHY.v_acct_branch_code=FSI_D_RETIREMENT_ACCOUNTS.BRANCH_CD LEFT OUTER JOIN FSI_EXCHANGE_RATE_HIST ON FSI_EXCHANGE_RATE_HIST.FROM_CURRENCY_CD = FSI_D_RETIREMENT_ACCOUNTS.ISO_CURRENCY_CD AND FSI_EXCHANGE_RATE_HIST.EFFECTIVE_DATE = FSI_D_RETIREMENT_ACCOUNTS.AS_OF_DATE AND FSI_EXCHANGE_RATE_HIST.TO_CURRENCY_CD = 'USD' WHERE 1=1 AND FSI_D_RETIREMENT_ACCOUNTS.AS_OF_DATE=to_date('20101005','YYYYMMDD') AND DIM_COMMON_COA.F_LATEST_RECORD_INDICATOR='Y' AND DIM_GL_ACCOUNT.F_LATEST_RECORD_INDICATOR='Y' AND DIM_ORG_UNIT.F_LATEST_RECORD_INDICATOR='Y' AND DIM_PRODUCT.F_LATEST_RECORD_INDICATOR='Y' ) inline_view
2010-10-05 | 11:22:07 | t2tserver | SEVERE | | | ClsUDBConnect::executeCountQuery, Error, unable to execute count(*) query. ERROR Message: ORA-00942: table or view does not exist

2010-10-05 | 11:22:07 | t2tserver | SEVERE | | | ClsUTableToTableLoad::directLoad, unable to fetch the count of rows for loading
2010-10-05 | 11:22:07 | t2tserver | SEVERE | | | ClsUTableToTableLoad::ExtractLoad, Error, unable to perform direct table to table load
2010-10-05 | 11:22:07 | t2tserver | INFO | | | Status from extract load ... 1
2010-10-05 | 11:22:07 | t2tserver | DEBUG | | | T2TLoad::main, INFO, Status Returned from Table To Table Loading: 1

STEPS
The issue can be reproduced at will with the following steps:
1. Attempt to run a T2T batch with the parameters documented on Page 29
2. Find the errors in the resulting log file

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms