Data Not Populating From STG_LOAN_CONTRACTS Table Into DIM_ACCOUNT Despite SCD Executing Successfully (Doc ID 2949932.1)

Last updated on MAY 26, 2023

Applies to:

Symptoms

On OFSAA 8.1 version, Data is not populating from STG_LOAN_CONTRACTS table into DIM_ACCOUNT table after successfully executing SCD 201 batch.

According to SCD log:

[Mon 24 Apr 2023 10:14:43 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] main : Map ref num = 201
[Mon 24 Apr 2023 10:14:43 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] main : FIC MIS Date = 20230413
[Mon 24 Apr 2023 10:14:43 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] main : Incremental SCD flag = N
(...)
[Mon 24 Apr 2023 10:14:43 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] ClsUObjectMap : Merge Query for SCD processing is : MERGE INTO DIM_ACCOUNT USING (SELECT DECODE(S.SCD_ROW_TYPE_ID,1,NULL,M.ROW_ID) ROW_ID,M.FIC_MIS_DATE, M.V_ACCOUNT_NUMBER, M.V_ORIGINAL_ACCOUNT_NUMBER, M.V_PRODUCT_PROCESSOR_NAME ,M.SCD_ROW_TYPE_ID FROM (SELECT DIM_ACCOUNT.ROWID ROW_ID,STG_LOAN_CONTRACTS_V.FIC_MIS_DATE, STG_LOAN_CONTRACTS_V.V_ACCOUNT_NUMBER, STG_LOAN_CONTRACTS_V.V_ORIGINAL_ACCOUNT_NUMBER, STG_LOAN_CONTRACTS_V.V_PRODUCT_PROCESSOR_NAME,CASE WHEN (DIM_ACCOUNT.ROWID IS NULL) THEN 1 WHEN (COALESCE(DIM_ACCOUNT.V_ORIGINAL_ACCOUNT_NUMBER,'*') != COALESCE(STG_LOAN_CONTRACTS_V.V_ORIGINAL_ACCOUNT_NUMBER,'*') OR COALESCE(DIM_ACCOUNT.V_PRODUCT_PROCESSOR_NAME,'*') != COALESCE(STG_LOAN_CONTRACTS_V.V_PRODUCT_PROCESSOR_NAME,'*') )THEN 3 END AS SCD_ROW_TYPE_ID FROM STG_LOAN_CONTRACTS_V LEFT JOIN DIM_ACCOUNT ON (DIM_ACCOUNT.V_ACCOUNT_NUMBER = STG_LOAN_CONTRACTS_V.V_ACCOUNT_NUMBER AND DIM_ACCOUNT.F_LATEST_RECORD_INDICATOR = 'Y') WHERE STG_LOAN_CONTRACTS_V.FIC_MIS_DATE = TO_DATE('20230413','YYYYMMDD') )M JOIN (SELECT 1 AS SCD_ROW_TYPE_ID FROM DUAL UNION ALL SELECT 2 AS SCD_ROW_TYPE_ID FROM DUAL UNION ALL SELECT 3 AS SCD_ROW_TYPE_ID FROM DUAL) S ON(S.SCD_ROW_TYPE_ID=M.SCD_ROW_TYPE_ID or (M.SCD_ROW_TYPE_ID=2 AND S.SCD_ROW_TYPE_ID=1)))B ON(DIM_ACCOUNT.ROWID = B.ROW_ID) WHEN MATCHED THEN UPDATE SET DIM_ACCOUNT.F_LATEST_RECORD_INDICATOR= DECODE(B.SCD_ROW_TYPE_ID,2,'N',DIM_ACCOUNT.F_LATEST_RECORD_INDICATOR),DIM_ACCOUNT.V_ORIGINAL_ACCOUNT_NUMBER=DECODE(B.SCD_ROW_TYPE_ID,3,B.V_ORIGINAL_ACCOUNT_NUMBER,DIM_ACCOUNT.V_ORIGINAL_ACCOUNT_NUMBER),DIM_ACCOUNT.V_PRODUCT_PROCESSOR_NAME=DECODE(B.SCD_ROW_TYPE_ID,3,B.V_PRODUCT_PROCESSOR_NAME,DIM_ACCOUNT.V_PRODUCT_PROCESSOR_NAME) WHEN NOT MATCHED THEN INSERT (DIM_ACCOUNT.FIC_MIS_DATE, V_ACCOUNT_NUMBER , DIM_ACCOUNT.V_ORIGINAL_ACCOUNT_NUMBER, DIM_ACCOUNT.V_PRODUCT_PROCESSOR_NAME, DIM_ACCOUNT.F_LATEST_RECORD_INDICATOR, N_ACCT_SKEY) VALUES ( B.FIC_MIS_DATE, B.V_ACCOUNT_NUMBER, B.V_ORIGINAL_ACCOUNT_NUMBER, B.V_PRODUCT_PROCESSOR_NAME, 'Y', SEQ_DIM_ACCOUNT_SCD.NEXTVAL)
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] ClsUObjectMap : Merge Query executed succesfully
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] ClsUObjectMap : Rows affected as part of merge query (includes both inserts and updates for type1 and type2) : 0
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] Processing : Information for Missing and Others entries
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][SEVERE][BACKEND][OFSAA][SCDCPP] ClsUSCDProcessing : Query Executed : SELECT N_ACCT_SKEY FROM DIM_ACCOUNT WHERE N_ACCT_SKEY in(0, -1)
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] ClsUSCDProcessing : Completed processing Mapping Reference Number : 201
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] ClsUSCDProcessing : -- - Summary report-- -
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] ClsUSCDProcessing : Map Reference Number Processing Status
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] ClsUSCDProcessing : 201 SUCCESS
[Mon 24 Apr 2023 10:14:44 AM GMT AM +0000][INFO][BACKEND][OFSAA][SCDCPP] main : SCD execution successfull

Cause

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