Unable To Create DQ Check With Validation Against AS_OF_DATE Column (Doc ID 2961231.1)

Last updated on JULY 20, 2023

Applies to:

Goal

On AAI 8.1.x version, you created a specific DQ check to abort the batch based on AS_OF_DATE column validation.
However product is implicitly applying date filter to the underlying base table having AS_OF_DATE or FIC_MIS_DATE columns, hence DQ not failing for the expected scenarios.

You would like a solution to avoid data selection for the table based on above columns.

DQ_CHECK: DQDRM002
DQ_CHECK_TABLE:FSI_M_TP_REPLICATING_PORTFOLIO
DQ_COLUMN:AS_OF_DATE
DESC : DQDRM002 Completed - No records matching the Rule criteria
DQ_SQL: SELECT 'DQDRM002' dq_check_id, 0 COUNTER, NULL ranks, NULL row_num, '' v_GENERAL_def_val, 'CURRENT_NBI_DATE check on FSI_M_TP_REPLICATING_PORTFOLIO ' dq_check_desc, 'DQDRM' dq_group_id, 'DQ DRM' dq_group_desc, 'FSI_M_TP_REPLICATING_PORTFOLIO' dq_table_name, 'PATTERN_CD' field_name, 'E' dq_severity, '97' dq_category_counter, to_char(PKNAMES), to_char(PK1), to_char(PK2), to_char(PK3), to_char(PK4), to_char(PK5), to_char(PK6), to_char(PK7), to_char(PK8), to_char(ERRCOL), NULL Range_Case, NULL length_Case, NULL colref_Case, NULL lov_Case, NULL null_Case, NULL blank_Case, NULL ref_Case FROM (select PKNAMES,PK1,PK2,PK3,PK4,PK5,PK6,PK7,PK8,ERRCOL from (SELECT 'PATTERN_CD CORE_NUMBER STRIP_NUMBER' PKNAMES, NULL PK1, NULL PK2, NULL PK3, NULL PK4, NULL PK5, NULL PK6, NULL PK7, NULL PK8, '71003 rollover date is not correct' ERRCOL FROM (SELECT CASE WHEN AS_OF_DATE = to_date(20220930,'yyyymmdd') THEN 0 ELSE 1 END STATUS FROM <atomic>.FSI_M_TP_REPLICATING_PORTFOLIO WHERE PATTERN_CD = 71003 AND ENABLED_FLAG = 'Y' ) X WHERE X.STATUS <> 0) CUSTOM_SQL)
 

Solution

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