My Oracle Support Banner

Running T2T MANAGEMENT_LEDGER in Batch Mode Does Not Insert Records in the FSI_DATA_IDENTITY Table (Doc ID 2439413.1)

Last updated on MARCH 05, 2019

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 8.0.5 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Profitability Management (PFT)
Table to Table (T2T)
Data Mapping

Symptoms

On OFSAA 8.0.5 when executing the T2T MANAGEMENT_LEDGER using 'Batch' Mode instead of 'Bulk' Loading Mode, the insert statement is incorrectly executed and the Expression with function is not executed as expected.

<SOURCE TABLENAME="EXPRESSION" TYPE="FIELD">
<COLUMN FIELDORDER="28" ID="IDENTITY_CODE">
<LENGTH>0</LENGTH>
<DATATYPE>Number</DATATYPE>
<PRECISION/>
<SCALE/>
<NULLIF/>
<DEFAULTIF/>
<STARTPOSITION>1</STARTPOSITION>
<EXPRESSION>FN_POPULATE_FSI_DATA_IDENTITY(TO_CHAR(STG_GL_DATA.FIC_MIS_DATE, 'YYYYMMDD'),ROWNUM,'FSI_D_MANAGEMENT_LEDGER') </EXPRESSION>
</COLUMN>
</SOURCE>
<DESTINATION>
<COLUMN ID="IDENTITY_CODE">

ACTUAL BEHAVIOR

T2T MANAGEMENT_LEDGER does not insert expected row into fsi_data_identity table as per the defined function. Function is not executed during insert:

Log Message here 1: Thu 14 Jun 2018 09:46:49 AM EDT||DEBUG||ClsUBatchLoader::LoadData, INFO, insert query for load data formulated : insert into FSI_D_MANAGEMENT_LEDGER(AS_OF_DATE,FUNCTIONAL_BALANCE,ENTERED_BALANCE,ISO_CURRENCY_CD,ALLOC_TYPE_ID,BALANCE_TYPE_CD,CHANNEL_ID,COMMON_COA_ID,CONSOLIDATION_CD,COST_ACTV_ID,COST_CLS_ID,COST_DRIVER_ID,COST_OBJ_ID,COST_POOL_ID,COST_TYPE_ID,CUST_TIER_ID,DBT_ORG_UNT_ID,EIS_APPL_ID,FINANCIAL_ELEM_ID,FISCAL_MONTH,FISCAL_YEAR,GEOG_UNIT_ID,GL_ACCOUNT_ID,IDENTITY_CODE,LEGAL_ENTITY_ID,OFFST_ORG_UNT_ID,ORG_UNIT_ID,PRODUCT_ID,PS_GL_ACCOUNT_ID,RATE_ID,RATE_TYPE_ID,RSRC_ID,STRATEGY_ID,SVC_ID,TRGT_ACCT_ID,TRGT_ORG_ID) values(:AS_OF_DATE,:FUNCTIONAL_BALANCE,:ENTERED_BALANCE,:ISO_CURRENCY_CD,:ALLOC_TYPE_ID,:BALANCE_TYPE_CD,:CHANNEL_ID,:COMMON_COA_ID,:CONSOLIDATION_CD,:COST_ACTV_ID,:COST_CLS_ID,:COST_DRIVER_ID,:COST_OBJ_ID,:COST_POOL_ID,:COST_TYPE_ID,:CUST_TIER_ID,:DBT_ORG_UNT_ID,:EIS_APPL_ID,:FINANCIAL_ELEM_ID,:FISCAL_MONTH,:FISCAL_YEAR,:GEOG_UNIT_ID,:GL_ACCOUNT_ID,:IDENTITY_CODE,:LEGAL_ENTITY_ID,:OFFST_ORG_UNT_ID,:ORG_UNIT_ID,:PRODUCT_ID,:PS_GL_ACCOUNT_ID,:RATE_ID,:RATE_TYPE_ID,:RSRC_ID,:STRATEGY_ID,:SVC_ID,:TRGT_ACCT_ID,:TRGT_ORG_ID)LOG ERRORS INTO FSI_D_MANAGEMENT_LEDGER$ ('OFSPFTINFO_MGMT_LEDGER_T2T_20181130_15_Task1') REJECT LIMIT UNLIMITED
Log Message here 1: Thu 14 Jun 2018 09:46:50 AM EDT||INFO||No. of records failed for other reasons (INSERT statement preparation, Datatype mismatch etc.) : 0

EXPECTED BEHAVIOR
Expect insert to also execute the defined Expression and function: FN_POPULATE_FSI_DATA_IDENTITY(TO_CHAR(STG_GL_DATA.FIC_MIS_DATE, 'YYYYMMDD'). This is displayed if run in Bulk Mode:

Log Message here 1: Fri 15 Jun 2018 10:01:07 AM EDT||DEBUG||ClsUBatchLoader::Bulk, DEBUG INFO,insert query for load data formulated =insert into FSI_D_MANAGEMENT_LEDGER(AS_OF_DATE,FUNCTIONAL_BALANCE,ENTERED_BALANCE,ISO_CURRENCY_CD,ALLOC_TYPE_ID,BALANCE_TYPE_CD,CHANNEL_ID,COMMON_COA_ID,CONSOLIDATION_CD,COST_ACTV_ID,COST_CLS_ID,COST_DRIVER_ID,COST_OBJ_ID,COST_POOL_ID,COST_TYPE_ID,CUST_TIER_ID,DBT_ORG_UNT_ID,EIS_APPL_ID,FINANCIAL_ELEM_ID,FISCAL_MONTH,FISCAL_YEAR,GEOG_UNIT_ID,GL_ACCOUNT_ID,IDENTITY_CODE,LEGAL_ENTITY_ID,OFFST_ORG_UNT_ID,ORG_UNIT_ID,PRODUCT_ID,PS_GL_ACCOUNT_ID,RATE_ID,RATE_TYPE_ID,RSRC_ID,STRATEGY_ID,SVC_ID,TRGT_ACCT_ID,TRGT_ORG_ID)SELECT STG_GL_DATA.FIC_MIS_DATE,to_char(STG_GL_DATA.N_AMOUNT_YTD_ACY),to_char(STG_GL_DATA.N_AMOUNT_YTD_LCY),STG_GL_DATA.V_CCY_CODE,nvl(DIM_ALLOC_TYPE_B.ALLOC_TYPE_ID,

...

DIM_SVC_B.SVC_CODE = stg_gl_data.V_SVC_CODE left join DIM_TRGT_ACCT_B on DIM_TRGT_ACCT_B.TRGT_ACCT_CODE = stg_gl_data.V_TRGT_ACCT_CODE left join DIM_TRGT_ORG_B on DIM_TRGT_ORG_B.TRGT_ORG_CODE = stg_gl_data.V_TRGT_ORG_CODE left join DIM_CHANNEL_B on DIM_CHANNEL_B.CHANNEL_CODE = stg_gl_data.V_CHANNEL_CODE WHERE 1=1 AND STG_GL_DATA.FIC_MIS_DATE=to_date('20181130','YYYYMMDD') LOG ERRORS INTO FSI_D_MANAGEMENT_LEDGER$ ('OFSPFTINFO_MGMT_LEDGER_T2T_20181130_17_Task1') REJECT LIMIT UNLIMITED

The issue can be reproduced at will with the following steps:
1. Run the T2T_MANAGEMENT_LEDGER with seeded Bulk mode, then in Batch mode.
2. Check FSI_DATA_IDENTITY rows

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


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