My Oracle Support Banner

Purge Staging Tables Batch Is Not Purging The Data Related To Stg_watchlist_master. (Doc ID 2853922.1)

Last updated on MARCH 10, 2022

Applies to:

Oracle Financial Services Crime and Compliance Management Transaction Monitoring Cloud Service - Version 10.0.0.0.0 and later
Information in this document applies to any platform.

Symptoms

Purge Staging tables batch is not purging the data related to stg_watchlist_master.

steps to reproduce
Answer: 1. Run the AMLDATALOAD for 20220203.
2. TXN pipeline got failed with the below error.
[[DEBUG] 2022-02-10 06:07:15.827 [http-nio-8080-exec-7] DataLoadingService - ORA-26041: DATETIME/INTERVAL datatype conversion error
[DEBUG] 2022-02-10 06:07:15.827 [http-nio-8080-exec-7] DataLoadingService -
[DEBUG] 2022-02-10 06:07:15.827 [http-nio-8080-exec-7] DataLoadingService -
[DEBUG] 2022-02-10 06:07:15.827 [http-nio-8080-exec-7] DataLoadingService - Record 3: Rejected - Error on table STG_CASA_TXNS_TEMP_3551636818, column D_EXECUTION_TIME.
3. Data corrected in the column of D_EXECUTION_TIME in STG_CASA_TXNS table and run the Purge Staging tables for 20220203.
4. Then Run the AMLDATALOAD for 20220203. Batch failed for watchlist pipeline with below error.
[ERROR] 2022-02-10 06:24:18.336 [qtp1545644729-28] ExecutorDAOImpl - Exception in executePipeline
java.lang.Exception: Error occurred in Result Processing: StatementCallback; SQL [insert into STG_WATCHLIST_MASTER (select * from STG_WATCHLIST_MASTER_temp_1831698763)]; ORA-00001: unique constraint (AMLDD.PK_176) violated
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (AMLDD.PK_176) violated.
5. Run the same data after changing the FICMISDATE as 20220205.
6. TXN pipeline got failed with the below error.
[DEBUG] 2022-02-10 06:07:15.827 [http-nio-8080-exec-7] DataLoadingService - ORA-26041: DATETIME/INTERVAL datatype conversion error
[DEBUG] 2022-02-10 06:07:15.827 [http-nio-8080-exec-7] DataLoadingService -
[DEBUG] 2022-02-10 06:07:15.827 [http-nio-8080-exec-7] DataLoadingService -
[DEBUG] 2022-02-10 06:07:15.827 [http-nio-8080-exec-7] DataLoadingService - Record 3: Rejected - Error on table STG_CASA_TXNS_TEMP_3551636818, column D_EXECUTION_TIME.
7. This error was not highlighting in the batch what we run for second time of FICMISDATE 20220203.
8. Corrected the data and rerun the batch for 20220205.
9. Batch failed for watchlist pipeline with below error.
[ERROR] 2022

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
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.