const regex = /(?:FROM|JOIN)\s*(IDP_PRD_.+?)(?:\s|\))/g;
// Alternative syntax using RegExp constructor
// const regex = new RegExp('(?:FROM|JOIN)\\s*(IDP_PRD_.+?)(?:\\s|\\))', 'g')
const str = `CREATE OR REPLACE VIEW IDP_L3_AMLCMTM.V_ALERT_AGING_REPORTS AS WITH CURRENT_KDD_REVIEW_OWNER AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.RPTG_GROUP_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.ACTV_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.EMAIL_ADDR_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_DSPLY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWN_ALERT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.LAST_FAILED_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.CURR_VALID_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.PREV_VALID_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWN_CASE_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.PWD_CHG_DT FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE = (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE) AS MAX FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER))), CURRENT_KDD_REVIEW_STATUS AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.REVIEW_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.CAN_NHRIT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.VIEWD_BY_OWNER_ACTVY_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.VIEWD_RESULT_STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.STATUS_DISPL_ORDER_NB, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.PRSDNC_ORDER_NB, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.CLOSED_STATUS_FL FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.IDP_DATA_DATE = (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS.IDP_DATA_DATE) AS MAX FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_STATUS))) SELECT R.IDP_DATA_DATE, R.REVIEW_ID, CASE WHEN (S.STATUS_CD = 'NW'::"VARCHAR") THEN '1 - Unassigned'::"VARCHAR" WHEN (S.STATUS_CD = 'OP'::"VARCHAR") THEN '2 - Assigned'::"VARCHAR" WHEN (S.STATUS_CD = 'RA'::"VARCHAR") THEN '2 - Assigned'::"VARCHAR" WHEN (S.STATUS_CD = 'FL'::"VARCHAR") THEN '3 - Follow Up'::"VARCHAR" WHEN (S.STATUS_CD = 'RO'::"VARCHAR") THEN '4 - Reopen'::"VARCHAR" WHEN (S.STATUS_CD = 'RCCC'::"VARCHAR") THEN '5 - Recommend Close'::"VARCHAR" WHEN (S.STATUS_CD = 'RPC'::"VARCHAR") THEN '6 - Recommend Promote to Case'::"VARCHAR" WHEN (S.STATUS_CD = 'APC'::"VARCHAR") THEN '7 - Closure Approved'::"VARCHAR" WHEN (S.STATUS_CD = 'CAP'::"VARCHAR") THEN '8 - Promote to Case Approved'::"VARCHAR" WHEN (R.STATUS_CD = 'CRJ'::"VARCHAR") THEN '9 - Promote to Case Rejected'::"VARCHAR" WHEN (S.STATUS_CD = 'CL'::"VARCHAR") THEN '10 - Closed'::"VARCHAR" WHEN (S.STATUS_CD = 'AQCO'::"VARCHAR") THEN '10 - Closed'::"VARCHAR" WHEN (S.STATUS_CD = 'AQCC'::"VARCHAR") THEN '10 - Closed'::"VARCHAR" WHEN (S.STATUS_CD = 'CLAC'::"VARCHAR") THEN '10 - Closed'::"VARCHAR" ELSE NULL::"VARCHAR" END AS "Status", R.STATUS_DT AS "Status Date", R.CREAT_TS AS "Created Date", R.AGE, O.OWNER_DSPLY_NM AS "Current Owner", R.FOCAL_NTITY_DSPLY_ID AS "Focal Entity", R.BUS_DMN_DSPLY_NM_ST AS "Business Domain", R.JRSDCN_CD AS "Jurisdiction", R.HILGT_TX AS "Highlight", R.SCNRO_DISPL_NM AS "Scenario Name", R.ALERT_CASE_CT AS "Linked Case", CASE WHEN ((R.IDP_DATA_DATE - R.CREAT_TS) < 6) THEN '00-05'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 6) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 11)) THEN '06-10'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 11) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 16)) THEN '11-15'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 16) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 21)) THEN '16-20'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 21) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 26)) THEN '21-25'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 26) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 31)) THEN '26-30'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 31) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 36)) THEN '31-35'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 36) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 45)) THEN '36-44'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 45) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 61)) THEN '45-60'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - R.CREAT_TS) >= 61) AND ((R.IDP_DATA_DATE - R.CREAT_TS) < 81)) THEN '61-80'::"VARCHAR" WHEN ((R.IDP_DATA_DATE - R.CREAT_TS) >= 81) THEN '81+'::"VARCHAR" ELSE NULL::"VARCHAR" END AS "Age Bucket" FROM (((CURRENT_KDD_REVIEW_STATUS S LEFT JOIN (SELECT KRRI.IDP_DATA_DATE, KRRI.IDP_WAREHOUSE_ID, KRRI.IDP_AUDIT_ID, KRRI.IDP_EFFECTIVE_DATE, KRRI.IDP_END_DATE, KRRI.IDP_DELETE_DATE, KRRI.REVIEW_ID, KRRI.CREAT_ID, KRRI.CREAT_TS, KRRI.LOCK_ID, KRRI.LOCK_TS, KRRI.STATUS_CD, KRRI.STATUS_DT, KRRI.CNTRY_ID, KRRI.CLS_ID, KRRI.CNTRY_KEY_ID, KRRI.SCNRO_ID, KRRI.SCNRO_CLASS_CD, KRRI.PTTRN_ID, KRRI.DMN_CD, KRRI.ALERT_CT, KRRI.REOPN_FL, KRRI.REASN_FL, KRRI.OWNER_SEQ_ID, KRRI.SCNRO_CT, KRRI.SCORE_CT, KRRI.SCORE_FL, KRRI.DUE_DT, KRRI.AUTO_CLS_CT, KRRI.CSTM_1_TX, KRRI.CSTM_2_TX, KRRI.CSTM_3_TX, KRRI.CSTM_4_TX, KRRI.CSTM_5_TX, KRRI.CSTM_1_DT, KRRI.CSTM_2_DT, KRRI.CSTM_3_DT, KRRI.CSTM_1_RL, KRRI.CSTM_2_RL, KRRI.CSTM_3_RL, KRRI.AUTO_REASN_FL, KRRI.LAST_ACTVY_TYPE_CD, KRRI.PREV_MATCH_CT_SM_SCNRO, KRRI.PREV_MATCH_CT_ALL, KRRI.OWNER_ORG, KRRI.AGE, KRRI.FOCAL_NTITY_DSPLY_NM, KRRI.FOCAL_NTITY_DSPLY_ID, KRRI.PREV_MATCH_CT_SM_SCNRO_CLASS, KRRI.SEND_NTFCTN_CD, KRRI.BUS_DMN_ST, KRRI.BUS_DMN_DSPLY_NM_ST, KRRI.JRSDCN_CD, KRRI.PRCSNG_BATCH_NM, KRRI.PRCSNG_BATCH_CMPLT_FL, KRRI.HILGT_TX, KRRI.ORIG_OWNER_SEQ_ID, KRRI.CLS_ACTVY_TYPE_CD, KRRI.CLS_CLASS_CD, KRRI.SCNRO_DISPL_NM, KRRI.EXTRL_REF_ID, KRRI.EXTRL_REF_LINK, KRRI.EXTRL_REF_SRC_ID, KRRI.RQST_AUDIT_SEQ_ID, KRRI.PRCSNG_DT, KRRI.REVIEW_TYPE_CD, KRRI.ALERT_CASE_CT, KRRI.LAST_LINK_FL, KRRI.LINK_UPDT_TS, KRRI.LINK_UPDT_ID, KRRI.REG_STATUS_LIST, KRRI.RK_REVIEW FROM (SELECT IDD.IDP_DATA_DATE, KR.IDP_WAREHOUSE_ID, KR.IDP_AUDIT_ID, KR.IDP_EFFECTIVE_DATE, KR.IDP_END_DATE, KR.IDP_DELETE_DATE, KR.REVIEW_ID, KR.CREAT_ID, KR.CREAT_TS, KR.LOCK_ID, KR.LOCK_TS, KR.STATUS_CD, KR.STATUS_DT, KR.CNTRY_ID, KR.CLS_ID, KR.CNTRY_KEY_ID, KR.SCNRO_ID, KR.SCNRO_CLASS_CD, KR.PTTRN_ID, KR.DMN_CD, KR.ALERT_CT, KR.REOPN_FL, KR.REASN_FL, KR.OWNER_SEQ_ID, KR.SCNRO_CT, KR.SCORE_CT, KR.SCORE_FL, KR.DUE_DT, KR.AUTO_CLS_CT, KR.CSTM_1_TX, KR.CSTM_2_TX, KR.CSTM_3_TX, KR.CSTM_4_TX, KR.CSTM_5_TX, KR.CSTM_1_DT, KR.CSTM_2_DT, KR.CSTM_3_DT, KR.CSTM_1_RL, KR.CSTM_2_RL, KR.CSTM_3_RL, KR.AUTO_REASN_FL, KR.LAST_ACTVY_TYPE_CD, KR.PREV_MATCH_CT_SM_SCNRO, KR.PREV_MATCH_CT_ALL, KR.OWNER_ORG, KR.AGE, KR.FOCAL_NTITY_DSPLY_NM, KR.FOCAL_NTITY_DSPLY_ID, KR.PREV_MATCH_CT_SM_SCNRO_CLASS, KR.SEND_NTFCTN_CD, KR.BUS_DMN_ST, KR.BUS_DMN_DSPLY_NM_ST, KR.JRSDCN_CD, KR.PRCSNG_BATCH_NM, KR.PRCSNG_BATCH_CMPLT_FL, KR.HILGT_TX, KR.ORIG_OWNER_SEQ_ID, KR.CLS_ACTVY_TYPE_CD, KR.CLS_CLASS_CD, KR.SCNRO_DISPL_NM, KR.EXTRL_REF_ID, KR.EXTRL_REF_LINK, KR.EXTRL_REF_SRC_ID, KR.RQST_AUDIT_SEQ_ID, KR.PRCSNG_DT, KR.REVIEW_TYPE_CD, KR.ALERT_CASE_CT, KR.LAST_LINK_FL, KR.LINK_UPDT_TS, KR.LINK_UPDT_ID, KR.REG_STATUS_LIST, ROW_NUMBER() OVER (PARTITION BY IDD.IDP_DATA_DATE, KR.REVIEW_ID ORDER BY KR.IDP_EFFECTIVE_DATE DESC ) AS RK_REVIEW FROM ((SELECT DISTINCT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS) IDD JOIN IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW KR ON ((IDD.IDP_DATA_DATE >= KR.IDP_EFFECTIVE_DATE)))) KRRI WHERE (KRRI.RK_REVIEW = 1)) R ON ((S.STATUS_CD = R.STATUS_CD))) JOIN IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_SCNRO RS ON ((RS.REVIEW_ID = R.REVIEW_ID))) JOIN CURRENT_KDD_REVIEW_OWNER O ON ((O.OWNER_SEQ_ID = R.OWNER_SEQ_ID))) WHERE (S.STATUS_CD IN (('APC'::"VARCHAR")::VARCHAR(10), ('RCCC'::"VARCHAR")::VARCHAR(10), ('OP'::"VARCHAR")::VARCHAR(10), ('RA'::"VARCHAR")::VARCHAR(10), ('NW'::"VARCHAR")::VARCHAR(10), ('CAP'::"VARCHAR")::VARCHAR(10), ('RPC'::"VARCHAR")::VARCHAR(10), ('FL'::"VARCHAR")::VARCHAR(10), ('RO'::"VARCHAR")::VARCHAR(10), ('CRJ'::"VARCHAR")::VARCHAR(10)));
CREATE OR REPLACE VIEW IDP_L3_AMLCMTM.V_ALERT_PERF_REPORTS AS SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Alert ID", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Created Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Status", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Status Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Scenario", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Alert Owner", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Owner Jurisdiction", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Business Domain", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Alert Jurisdiction", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Linked Case", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Focal Entity Name", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Focal Entity ID", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Linked Case ID", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."STR Filed", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Adverse Information Filed", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."EDD Completed", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Alert Action Rejected", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."QC Rework/Reassign", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."QC Reopen", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."QC Open Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."QC Closed Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Last QC Rework/Reassign Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Last QC Reopen Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."QC Closed Age Bucket", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."QC Alert Closed By", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."QC Alert Closed Jurisdiction", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."QC Closure Note", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Alert Closed Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Alert Closed Age Bucket", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Alert Closed By", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Alert Closed By Jurisdiction", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Close Owner Group", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Closure Note", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS."Highlight" FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.ALERT_PERF_REPORTS;
CREATE OR REPLACE VIEW IDP_L3_AMLCMTM.V_ALERT_CR_CLOSURE_SUMMARY AS WITH CURRENT_KDD_REVIEW AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_EFFECTIVE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_END_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_DELETE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REVIEW_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CREAT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CREAT_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LOCK_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LOCK_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.STATUS_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CNTRY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CLS_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CNTRY_KEY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCNRO_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCNRO_CLASS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PTTRN_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.DMN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.ALERT_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REOPN_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REASN_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.OWNER_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCNRO_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCORE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCORE_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.DUE_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.AUTO_CLS_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_1_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_2_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_3_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_4_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_5_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_1_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_2_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_3_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_1_RL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_2_RL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_3_RL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.AUTO_REASN_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LAST_ACTVY_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PREV_MATCH_CT_SM_SCNRO, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PREV_MATCH_CT_ALL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.OWNER_ORG, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.AGE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.FOCAL_NTITY_DSPLY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.FOCAL_NTITY_DSPLY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PREV_MATCH_CT_SM_SCNRO_CLASS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SEND_NTFCTN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.BUS_DMN_DSPLY_NM_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.JRSDCN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PRCSNG_BATCH_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PRCSNG_BATCH_CMPLT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.HILGT_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.ORIG_OWNER_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CLS_ACTVY_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CLS_CLASS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCNRO_DISPL_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.EXTRL_REF_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.EXTRL_REF_LINK, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.EXTRL_REF_SRC_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.RQST_AUDIT_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PRCSNG_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REVIEW_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.ALERT_CASE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LAST_LINK_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LINK_UPDT_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LINK_UPDT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REG_STATUS_LIST FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_END_DATE = '9999-12-31'::DATE)), MAXDATADATE (IDP_DATA_DATE) AS (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE) AS IDP_DATA_DATE FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS), CLOSEACTIVITY AS (SELECT A1.REVIEW_ID, A1.START_DT FROM (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.REVIEW_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.START_DT, ROW_NUMBER() OVER (PARTITION BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.REVIEW_ID ORDER BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.START_DT DESC ) AS RK_ACTIVITY FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.ACTVY_TYPE_CD IN (('CST006'::"VARCHAR")::VARCHAR(10), ('CST010'::"VARCHAR")::VARCHAR(10), ('CST015'::"VARCHAR")::VARCHAR(10), ('MTS050'::"VARCHAR")::VARCHAR(10), ('MTS051'::"VARCHAR")::VARCHAR(10), ('MTS052'::"VARCHAR")::VARCHAR(10), ('MTS053'::"VARCHAR")::VARCHAR(10), ('MTS203'::"VARCHAR")::VARCHAR(10), ('MTS203A'::"VARCHAR")::VARCHAR(10), ('MTS204'::"VARCHAR")::VARCHAR(10), ('MTS204A'::"VARCHAR")::VARCHAR(10), ('MTS500'::"VARCHAR")::VARCHAR(10), ('MTS370'::"VARCHAR")::VARCHAR(10), ('MTS232'::"VARCHAR")::VARCHAR(10), ('CST012'::"VARCHAR")::VARCHAR(10)))) A1 WHERE (A1.RK_ACTIVITY = 1)), ALERTCREATIONCLOSURESUMMARY (CALENDARDATE, TOTALCREATED, TOTALCLOSED, DAILYOUTSTANDING) AS (SELECT CASE WHEN (CREATED.CREATEDATE ISNULL) THEN CLOSED.CLOSEDDATE ELSE CREATED.CREATEDATE END AS CALENDARDATE, CASE WHEN (CREATED.CREATECOUNT NOTNULL) THEN CREATED.CREATECOUNT WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS TOTALCREATED, CASE WHEN (CLOSED.CLOSEDCOUNT NOTNULL) THEN CLOSED.CLOSEDCOUNT WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS TOTALCLOSED, (CASE WHEN (CREATED.CREATECOUNT NOTNULL) THEN CREATED.CREATECOUNT WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END - CASE WHEN (CLOSED.CLOSEDCOUNT NOTNULL) THEN CLOSED.CLOSEDCOUNT WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END) AS DAILYOUTSTANDING FROM ((SELECT TO_CHAR("TIMESTAMP"(RO.CREAT_TS), 'YYYY-MM-DD'::"VARCHAR") AS CREATEDATE, COUNT(RO.REVIEW_ID) AS CREATECOUNT FROM CURRENT_KDD_REVIEW RO GROUP BY TO_CHAR("TIMESTAMP"(RO.CREAT_TS), 'YYYY-MM-DD'::"VARCHAR")) CREATED FULL JOIN (SELECT TO_CHAR(RC_CA.CLOSEDDATE, 'YYYY-MM-DD'::"VARCHAR") AS CLOSEDDATE, COUNT(1) AS CLOSEDCOUNT FROM (SELECT RC.REVIEW_ID, CLOSEACTIVITY.START_DT AS CLOSEDDATE FROM (CURRENT_KDD_REVIEW RC JOIN CLOSEACTIVITY ON (((CLOSEACTIVITY.REVIEW_ID = RC.REVIEW_ID) AND (RC.STATUS_CD IN (('CL'::"VARCHAR")::VARCHAR(10), ('CLAC'::"VARCHAR")::VARCHAR(10), ('AQCO'::"VARCHAR")::VARCHAR(10), ('AQCC'::"VARCHAR")::VARCHAR(10))))))) RC_CA GROUP BY TO_CHAR(RC_CA.CLOSEDDATE, 'YYYY-MM-DD'::"VARCHAR")) CLOSED ON ((CREATED.CREATEDATE = CLOSED.CLOSEDDATE)))) ((SELECT MAXDATADATE.IDP_DATA_DATE, R.REVIEW_ID AS "Alert ID", (TO_CHAR("TIMESTAMP"(R.CREAT_TS), 'YYYY-MM-DD'::"VARCHAR"))::VARCHAR(200) AS "Date", ('1'::"NUMERIC")::NUMERIC(38,0) AS "Counter", ('Created'::"VARCHAR")::VARCHAR(11) AS "Data Type" FROM (CURRENT_KDD_REVIEW R LEFT JOIN MAXDATADATE ON ((1 = 1)))) UNION (SELECT MAXDATADATE.IDP_DATA_DATE, R2.REVIEW_ID AS "Alert ID", (TO_CHAR(CLOSEACTIVITY.START_DT, 'YYYY-MM-DD'::"VARCHAR"))::VARCHAR(200) AS "Date", ('1'::"NUMERIC")::NUMERIC(38,0) AS "Counter", ('Closed'::"VARCHAR")::VARCHAR(11) AS "Data Type" FROM ((CURRENT_KDD_REVIEW R2 JOIN CLOSEACTIVITY ON (((CLOSEACTIVITY.REVIEW_ID = R2.REVIEW_ID) AND (R2.STATUS_CD IN (('CL'::"VARCHAR")::VARCHAR(10), ('CLAC'::"VARCHAR")::VARCHAR(10), ('AQCO'::"VARCHAR")::VARCHAR(10), ('AQCC'::"VARCHAR")::VARCHAR(10)))))) LEFT JOIN MAXDATADATE ON ((1 = 1))))) UNION (SELECT MAXDATADATE.IDP_DATA_DATE, ('0'::"NUMERIC")::NUMERIC(10,0) AS "Alert ID", A.CALENDARDATE AS "Date", (SUM(B.DAILYOUTSTANDING))::NUMERIC(38,0) AS "Counter", ('Outstanding'::"VARCHAR")::VARCHAR(11) AS "Data Type" FROM ALERTCREATIONCLOSURESUMMARY A, ALERTCREATIONCLOSURESUMMARY B, MAXDATADATE WHERE (A.CALENDARDATE >= B.CALENDARDATE) GROUP BY A.CALENDARDATE, A.TOTALCREATED, A.TOTALCLOSED, A.DAILYOUTSTANDING, MAXDATADATE.IDP_DATA_DATE);
CREATE OR REPLACE VIEW IDP_L3_AMLCMTM.V_CASE_CR_CLOSURE_SUMMARY AS WITH CURRENT_KDD_REVIEW AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_EFFECTIVE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_END_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_DELETE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REVIEW_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CREAT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CREAT_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LOCK_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LOCK_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.STATUS_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CNTRY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CLS_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CNTRY_KEY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCNRO_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCNRO_CLASS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PTTRN_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.DMN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.ALERT_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REOPN_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REASN_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.OWNER_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCNRO_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCORE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCORE_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.DUE_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.AUTO_CLS_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_1_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_2_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_3_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_4_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_5_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_1_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_2_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_3_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_1_RL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_2_RL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CSTM_3_RL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.AUTO_REASN_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LAST_ACTVY_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PREV_MATCH_CT_SM_SCNRO, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PREV_MATCH_CT_ALL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.OWNER_ORG, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.AGE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.FOCAL_NTITY_DSPLY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.FOCAL_NTITY_DSPLY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PREV_MATCH_CT_SM_SCNRO_CLASS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SEND_NTFCTN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.BUS_DMN_DSPLY_NM_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.JRSDCN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PRCSNG_BATCH_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PRCSNG_BATCH_CMPLT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.HILGT_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.ORIG_OWNER_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CLS_ACTVY_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.CLS_CLASS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.SCNRO_DISPL_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.EXTRL_REF_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.EXTRL_REF_LINK, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.EXTRL_REF_SRC_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.RQST_AUDIT_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.PRCSNG_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REVIEW_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.ALERT_CASE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LAST_LINK_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LINK_UPDT_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.LINK_UPDT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.REG_STATUS_LIST FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW.IDP_END_DATE = '9999-12-31'::DATE)), MAXDATADATE (IDP_DATA_DATE) AS (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE) AS IDP_DATA_DATE FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS), CLOSEACTION AS (SELECT A1.CASE_INTRL_ID, A1.ACTION_TS FROM (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.CASE_INTRL_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_TS, ROW_NUMBER() OVER (PARTITION BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.CASE_INTRL_ID ORDER BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_SEQ_ID DESC ) AS RK_CA FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_ID IN (('43'::"NUMERIC")::NUMERIC(10,0), ('44'::"NUMERIC")::NUMERIC(10,0), ('45'::"NUMERIC")::NUMERIC(10,0), ('86'::"NUMERIC")::NUMERIC(10,0), ('88'::"NUMERIC")::NUMERIC(10,0), ('90'::"NUMERIC")::NUMERIC(10,0), ('92'::"NUMERIC")::NUMERIC(10,0), ('121'::"NUMERIC")::NUMERIC(10,0), ('124'::"NUMERIC")::NUMERIC(10,0), ('126'::"NUMERIC")::NUMERIC(10,0), ('128'::"NUMERIC")::NUMERIC(10,0), ('214'::"NUMERIC")::NUMERIC(10,0), ('2009'::"NUMERIC")::NUMERIC(10,0), ('2012'::"NUMERIC")::NUMERIC(10,0), ('47'::"NUMERIC")::NUMERIC(10,0), ('98'::"NUMERIC")::NUMERIC(10,0), ('134'::"NUMERIC")::NUMERIC(10,0), ('123'::"NUMERIC")::NUMERIC(10,0), ('2006'::"NUMERIC")::NUMERIC(10,0)))) A1 WHERE (A1.RK_CA = 1)), CASECREATIONCLOSURESUMMARY (CALENDARDATE, TOTALCREATED, TOTALCLOSED, DAILYOUTSTANDING) AS (SELECT CASE WHEN (CREATED.CREATEDATE ISNULL) THEN CLOSED.CLOSEDDATE ELSE CREATED.CREATEDATE END AS CALENDARDATE, CASE WHEN (CREATED.CREATECOUNT NOTNULL) THEN CREATED.CREATECOUNT WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS TOTALCREATED, CASE WHEN (CLOSED.CLOSEDCOUNT NOTNULL) THEN CLOSED.CLOSEDCOUNT WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS TOTALCLOSED, (CASE WHEN (CREATED.CREATECOUNT NOTNULL) THEN CREATED.CREATECOUNT WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END - CASE WHEN (CLOSED.CLOSEDCOUNT NOTNULL) THEN CLOSED.CLOSEDCOUNT WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END) AS DAILYOUTSTANDING FROM ((SELECT TO_CHAR("TIMESTAMP"(RO.CREATED_TS), 'YYYY-MM-DD'::"VARCHAR") AS CREATEDATE, COUNT(RO.CASE_INTRL_ID) AS CREATECOUNT FROM (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_EFFECTIVE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_END_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_DELETE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_INTRL_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_REVIEW_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TITL_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CREATED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CREATE_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_UPDATED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_UPDATED_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ASSIGNED_TO_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ASSIGNED_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CONFIDENTIAL_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_DUE_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_CLOSED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_ASSIGNED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_DESC_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SMRY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LOCK_BY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LOCK_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION_COMMENTS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_ACTION_NAME, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LINKED_ALERT_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LINKED_CASE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_KEY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ORIG_OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.OWNER_ORG, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.JRSDCN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SRC_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRIORITY_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.SCNRO_CLASS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRIOR_STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.SCORE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRCSNG_CMPLT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TYPE_SUBTYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBTYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBCLASS1_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBCLASS2_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.AGE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.RA_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_KEY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.KYC_RULE_TYPE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.KYC_CUST_TYPE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.DOC_CNTRL_NB FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_END_DATE = '9999-12-31'::DATE)) RO GROUP BY TO_CHAR("TIMESTAMP"(RO.CREATED_TS), 'YYYY-MM-DD'::"VARCHAR")) CREATED FULL JOIN (SELECT TO_CHAR("TIMESTAMP"(X.CLOSEDDATE), 'YYYY-MM-DD'::"VARCHAR") AS CLOSEDDATE, COUNT(1) AS CLOSEDCOUNT FROM (SELECT RC.CASE_INTRL_ID, CLOSEACTION.ACTION_TS AS CLOSEDDATE FROM ((SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_EFFECTIVE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_END_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_DELETE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_INTRL_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_REVIEW_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TITL_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CREATED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CREATE_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_UPDATED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_UPDATED_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ASSIGNED_TO_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ASSIGNED_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CONFIDENTIAL_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_DUE_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_CLOSED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_ASSIGNED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_DESC_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SMRY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LOCK_BY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LOCK_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION_COMMENTS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_ACTION_NAME, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LINKED_ALERT_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LINKED_CASE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_KEY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ORIG_OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.OWNER_ORG, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.JRSDCN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SRC_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRIORITY_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.SCNRO_CLASS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRIOR_STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.SCORE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRCSNG_CMPLT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TYPE_SUBTYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBTYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBCLASS1_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBCLASS2_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.AGE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.RA_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_KEY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.KYC_RULE_TYPE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.KYC_CUST_TYPE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.DOC_CNTRL_NB FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_END_DATE = '9999-12-31'::DATE)) RC JOIN CLOSEACTION ON (((RC.CASE_INTRL_ID = CLOSEACTION.CASE_INTRL_ID) AND (RC.STATUS_CD IN (('RECAP'::"VARCHAR")::VARCHAR(20), ('UARC'::"VARCHAR")::VARCHAR(20), ('SARC'::"VARCHAR")::VARCHAR(20), ('CL'::"VARCHAR")::VARCHAR(20), ('EDRC'::"VARCHAR")::VARCHAR(20), ('CCLD'::"VARCHAR")::VARCHAR(20), ('CCWA'::"VARCHAR")::VARCHAR(20), ('CCAC'::"VARCHAR")::VARCHAR(20), ('CCNC'::"VARCHAR")::VARCHAR(20), ('CQCO'::"VARCHAR")::VARCHAR(20), ('CQCC'::"VARCHAR")::VARCHAR(20))))))) X GROUP BY TO_CHAR("TIMESTAMP"(X.CLOSEDDATE), 'YYYY-MM-DD'::"VARCHAR")) CLOSED ON ((CREATED.CREATEDATE = CLOSED.CLOSEDDATE)))) ((SELECT MAXDATADATE.IDP_DATA_DATE, C.CASE_INTRL_ID AS "Case ID", (TO_CHAR("TIMESTAMP"(C.CREATED_TS), 'YYYY-MM-DD'::"VARCHAR"))::VARCHAR(200) AS "Date", ('1'::"NUMERIC")::NUMERIC(38,0) AS "Counter", ('Created'::"VARCHAR")::VARCHAR(11) AS "Data Type" FROM ((SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_EFFECTIVE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_END_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_DELETE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_INTRL_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_REVIEW_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TITL_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CREATED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CREATE_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_UPDATED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_UPDATED_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ASSIGNED_TO_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ASSIGNED_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CONFIDENTIAL_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_DUE_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_CLOSED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_ASSIGNED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_DESC_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SMRY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LOCK_BY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LOCK_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION_COMMENTS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_ACTION_NAME, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LINKED_ALERT_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LINKED_CASE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_KEY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ORIG_OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.OWNER_ORG, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.JRSDCN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SRC_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRIORITY_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.SCNRO_CLASS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRIOR_STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.SCORE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRCSNG_CMPLT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TYPE_SUBTYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBTYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBCLASS1_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBCLASS2_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.AGE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.RA_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_KEY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.KYC_RULE_TYPE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.KYC_CUST_TYPE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.DOC_CNTRL_NB FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_END_DATE = '9999-12-31'::DATE)) C LEFT JOIN MAXDATADATE ON ((1 = 1)))) UNION (SELECT MAXDATADATE.IDP_DATA_DATE, C.CASE_INTRL_ID AS "Case ID", (TO_CHAR("TIMESTAMP"(CLOSEACTION.ACTION_TS), 'YYYY-MM-DD'::"VARCHAR"))::VARCHAR(200) AS "Date", ('1'::"NUMERIC")::NUMERIC(38,0) AS "Counter", ('Closed'::"VARCHAR")::VARCHAR(11) AS "Data Type" FROM (((SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_EFFECTIVE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_END_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_DELETE_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_INTRL_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_REVIEW_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TITL_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CREATED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CREATE_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_UPDATED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_UPDATED_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ASSIGNED_TO_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ASSIGNED_BY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CONFIDENTIAL_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_DUE_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_CLOSED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_ASSIGNED_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_DESC_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SMRY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LOCK_BY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LOCK_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION_COMMENTS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_RESOLUTION_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LAST_ACTION_NAME, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LINKED_ALERT_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.LINKED_CASE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_KEY_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.ORIG_OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.OWNER_ORG, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.JRSDCN_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SRC_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRIORITY_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.SCNRO_CLASS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRIOR_STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.SCORE_CT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.PRCSNG_CMPLT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TYPE_SUBTYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBTYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBCLASS1_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CASE_SUBCLASS2_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.AGE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.RA_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.CNTRY_KEY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.KYC_RULE_TYPE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.KYC_CUST_TYPE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.DOC_CNTRL_NB FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES.IDP_END_DATE = '9999-12-31'::DATE)) C JOIN CLOSEACTION ON (((C.CASE_INTRL_ID = CLOSEACTION.CASE_INTRL_ID) AND (C.STATUS_CD IN (('RECAP'::"VARCHAR")::VARCHAR(20), ('UARC'::"VARCHAR")::VARCHAR(20), ('SARC'::"VARCHAR")::VARCHAR(20), ('CL'::"VARCHAR")::VARCHAR(20), ('EDRC'::"VARCHAR")::VARCHAR(20), ('CCLD'::"VARCHAR")::VARCHAR(20), ('CCWA'::"VARCHAR")::VARCHAR(20), ('CCAC'::"VARCHAR")::VARCHAR(20), ('CCNC'::"VARCHAR")::VARCHAR(20), ('CQCO'::"VARCHAR")::VARCHAR(20), ('CQCC'::"VARCHAR")::VARCHAR(20)))))) LEFT JOIN MAXDATADATE ON ((1 = 1))))) UNION (SELECT MAXDATADATE.IDP_DATA_DATE, ('0'::"VARCHAR")::VARCHAR(15) AS "Case ID", A.CALENDARDATE AS "Date", (SUM(B.DAILYOUTSTANDING))::NUMERIC(38,0) AS "Counter", ('Outstanding'::"VARCHAR")::VARCHAR(11) AS "Data Type" FROM CASECREATIONCLOSURESUMMARY A, CASECREATIONCLOSURESUMMARY B, MAXDATADATE WHERE (A.CALENDARDATE >= B.CALENDARDATE) GROUP BY A.CALENDARDATE, A.TOTALCREATED, A.TOTALCLOSED, A.DAILYOUTSTANDING, MAXDATADATE.IDP_DATA_DATE);
CREATE OR REPLACE VIEW IDP_L3_AMLCMTM.V_CASE_AGING_REPORTS AS WITH CURRENT_KDD_STATUS AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.CAN_NHRIT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.VIEWD_BY_OWNER_ACTVY_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.VIEWD_RESULT_STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.CLOSED_STATUS_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.STATUS_NM FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE = (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE) AS MAX FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS))), CURRENT_KDD_REVIEW_OWNER AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.RPTG_GROUP_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.ACTV_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.EMAIL_ADDR_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_DSPLY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWN_ALERT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.LAST_FAILED_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.CURR_VALID_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.PREV_VALID_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWN_CASE_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.PWD_CHG_DT FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE = (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE) AS MAX FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER))), CURRENT_KDD_ACTION AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_DESC, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.LAST_UPDATED_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.LAST_UPDATED_BY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.COMMENTS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_ORDER, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REQ_CMMNT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.DFLT_DUE_DT_LM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REQ_REASN_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REQ_DUE_DATE_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.NEXT_REVIEW_STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REG_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REQ_REASN_OWNER_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.LAST_ASSIGN_REQ, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.RESOLUTION_ACTION_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.EXPORT_DIR_REF, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_CATEGORY_CODE FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_DATA_DATE = (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_DATA_DATE) AS MAX FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION))) SELECT C.IDP_DATA_DATE, C.CASE_INTRL_ID AS "Case ID", C.CASE_TITL_NM AS "Case Name", C.CREATED_TS AS "Create Date", O1.OWNER_DSPLY_NM AS "Created By", O2.OWNER_DSPLY_NM AS "Current Owner", C.CASE_DESC_TX AS "Case Description", CASE WHEN (S.STATUS_CD = 'NW'::"VARCHAR") THEN '1 - New'::"VARCHAR" WHEN (S.STATUS_CD = 'NW90'::"VARCHAR") THEN '2 - New - 90 Day Review'::"VARCHAR" WHEN (S.STATUS_CD = 'INV'::"VARCHAR") THEN '3 - Investigation'::"VARCHAR" WHEN (S.STATUS_CD = 'ESC'::"VARCHAR") THEN '4 - Escalated'::"VARCHAR" WHEN (S.STATUS_CD = 'MON'::"VARCHAR") THEN '5 - Monitor'::"VARCHAR" WHEN (S.STATUS_CD = 'RO'::"VARCHAR") THEN '6 - Reopened'::"VARCHAR" WHEN (S.STATUS_CD = 'AR'::"VARCHAR") THEN '7 - Awaiting Response'::"VARCHAR" WHEN (S.STATUS_CD = 'RAC'::"VARCHAR") THEN '8 - Action Recommended'::"VARCHAR" WHEN (S.STATUS_CD = 'EDRR'::"VARCHAR") THEN '9 - EDD-Recommended'::"VARCHAR" WHEN (S.STATUS_CD = 'UARR'::"VARCHAR") THEN '10 - STR Recommended'::"VARCHAR" WHEN (S.STATUS_CD = 'RMON'::"VARCHAR") THEN '11 - Recommend Monitor'::"VARCHAR" WHEN (S.STATUS_CD = 'RCL'::"VARCHAR") THEN '12 - Recommend Closure'::"VARCHAR" WHEN (S.STATUS_CD = 'CLAP'::"VARCHAR") THEN '13 - Closure Approved'::"VARCHAR" WHEN (S.STATUS_CD = 'RECAP'::"VARCHAR") THEN '14 - Recommendation Approved'::"VARCHAR" WHEN (S.STATUS_CD = 'APP'::"VARCHAR") THEN '15 -Approved'::"VARCHAR" WHEN (S.STATUS_CD = 'REJ'::"VARCHAR") THEN '16 - Rejected'::"VARCHAR" WHEN (S.STATUS_CD = 'UARC'::"VARCHAR") THEN '17 - STR - Filed'::"VARCHAR" WHEN (S.STATUS_CD = 'SARC'::"VARCHAR") THEN '18 - Adverse Information - Filed'::"VARCHAR" WHEN (S.STATUS_CD = 'CL'::"VARCHAR") THEN '19 - Closed'::"VARCHAR" WHEN (S.STATUS_CD = 'EDRC'::"VARCHAR") THEN '20 - EDD Closed'::"VARCHAR" WHEN (S.STATUS_CD = 'CCLD'::"VARCHAR") THEN '21 - Closed - Duplicate Case'::"VARCHAR" WHEN (S.STATUS_CD = 'CCWA'::"VARCHAR") THEN '22 - Closed - Withheld Action'::"VARCHAR" WHEN (S.STATUS_CD = 'CCAC'::"VARCHAR") THEN '23 - Closed - Account(s) Closed'::"VARCHAR" WHEN (S.STATUS_CD = 'CCNC'::"VARCHAR") THEN '24 - Closed - New Case Opened'::"VARCHAR" WHEN (S.STATUS_CD = 'CQCO'::"VARCHAR") THEN '25 - QC Opened'::"VARCHAR" WHEN (S.STATUS_CD = 'CQCC'::"VARCHAR") THEN '26 - QC Closed'::"VARCHAR" ELSE S.STATUS_CD END AS "Status", C.AGE, C.CASE_RESOLUTION AS "Case Resolution", C.LAST_ACTION_NAME AS "Last Activity", C.JRSDCN_CD AS "Jurisdiction", C.PRIORITY_CD AS "Priority", CASE WHEN ((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 6)) THEN '00-05'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 6)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 11)) THEN '06-10'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 11)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 16)) THEN '11-15'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 16)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 21)) THEN '16-20'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 21)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 26)) THEN '21-25'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 26)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 31)) THEN '26-30'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 31)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 36)) THEN '31-35'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 36)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 45)) THEN '36-44'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 45)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 61)) THEN '45-60'::"VARCHAR" WHEN (((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 61)) AND ((C.IDP_DATA_DATE - C.CREATED_TS) < 81)) THEN '61-80'::"VARCHAR" WHEN ((S.STATUS_CD <> 'RECAP'::"VARCHAR") AND ((C.IDP_DATA_DATE - C.CREATED_TS) >= 81)) THEN '81+'::"VARCHAR" WHEN ((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 6)) THEN '00-05'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 6)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 11)) THEN '06-10'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 11)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 16)) THEN '11-15'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 16)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 21)) THEN '16-20'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 21)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 26)) THEN '21-25'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 26)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 31)) THEN '26-30'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 31)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 36)) THEN '31-35'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 36)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 45)) THEN '36-44'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 45)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 61)) THEN '45-60'::"VARCHAR" WHEN (((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 61)) AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) < 81)) THEN '61-80'::"VARCHAR" WHEN ((S.STATUS_CD = 'RECAP'::"VARCHAR") AND ((APPROVEDACTION.ACTION_TS - C.CREATED_TS) >= 81)) THEN '81+'::"VARCHAR" ELSE NULL::"VARCHAR" END AS "Age Bucket", A.ACTION_DESC AS "Recommend Action", CASE WHEN ((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 0)) THEN TO_CHAR("TIMESTAMP"(APPROVEDACTION.ACTION_TS), 'YYYY-MM-DD'::"VARCHAR") ELSE ''::"VARCHAR" END AS "Recommend Action Date", CASE WHEN ((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 0)) THEN ''::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 0)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 6)) THEN '00-05'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 5)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 11)) THEN '06-10'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 11)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 16)) THEN '11-15'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 16)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 21)) THEN '16-20'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 21)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 26)) THEN '21-25'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 26)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 31)) THEN '26-30'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 31)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 36)) THEN '31-35'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 36)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 45)) THEN '36-44'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 45)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 61)) THEN '45-60'::"VARCHAR" WHEN (((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 61)) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) < 81)) THEN '61-80'::"VARCHAR" WHEN ((APPROVEDACTION.ACTION_TS NOTNULL) AND ((C.IDP_DATA_DATE - APPROVEDACTION.ACTION_TS) >= 81)) THEN '81+'::"VARCHAR" ELSE NULL::"VARCHAR" END AS "STR/Adverse Information Timer" FROM ((((((CURRENT_KDD_STATUS S LEFT JOIN (SELECT KSKC.IDP_DATA_DATE, KSKC.RK_CASE, KSKC.IDP_WAREHOUSE_ID, KSKC.IDP_AUDIT_ID, KSKC.IDP_EFFECTIVE_DATE, KSKC.IDP_END_DATE, KSKC.IDP_DELETE_DATE, KSKC.CASE_INTRL_ID, KSKC.CASE_REVIEW_ID, KSKC.CASE_TITL_NM, KSKC.CREATED_TS, KSKC.CREATE_BY_ID, KSKC.LAST_UPDATED_TS, KSKC.LAST_UPDATED_BY_ID, KSKC.OWNER_ID, KSKC.ASSIGNED_TO_ID, KSKC.ASSIGNED_BY_ID, KSKC.CONFIDENTIAL_FL, KSKC.CASE_DUE_TS, KSKC.CASE_CLOSED_TS, KSKC.CASE_ASSIGNED_TS, KSKC.CASE_DESC_TX, KSKC.STATUS_CD, KSKC.CASE_SMRY, KSKC.LOCK_BY, KSKC.LOCK_TS, KSKC.CASE_RESOLUTION, KSKC.CASE_RESOLUTION_COMMENTS, KSKC.CASE_RESOLUTION_TS, KSKC.LAST_ACTION_NAME, KSKC.LINKED_ALERT_CT, KSKC.LINKED_CASE_CT, KSKC.CNTRY_ID, KSKC.CNTRY_KEY_ID, KSKC.ORIG_OWNER_ID, KSKC.OWNER_ORG, KSKC.JRSDCN_CD, KSKC.CASE_SRC_CD, KSKC.PRIORITY_CD, KSKC.SCNRO_CLASS_CD, KSKC.PRIOR_STATUS_CD, KSKC.BUS_DMN_ST, KSKC.SCORE_CT, KSKC.PRCSNG_CMPLT_FL, KSKC.CASE_TYPE_SUBTYPE_CD, KSKC.CASE_TYPE_CD, KSKC.CASE_SUBTYPE_CD, KSKC.CASE_SUBCLASS1_CD, KSKC.CASE_SUBCLASS2_CD, KSKC.AGE, KSKC.RA_ID, KSKC.CNTRY_KEY_NM, KSKC.KYC_RULE_TYPE, KSKC.KYC_CUST_TYPE, KSKC.DOC_CNTRL_NB FROM (SELECT KS.IDP_DATA_DATE, ROW_NUMBER() OVER (PARTITION BY KS.IDP_DATA_DATE, KC.CASE_INTRL_ID ORDER BY KC.IDP_EFFECTIVE_DATE DESC ) AS RK_CASE, KC.IDP_WAREHOUSE_ID, KC.IDP_AUDIT_ID, KC.IDP_EFFECTIVE_DATE, KC.IDP_END_DATE, KC.IDP_DELETE_DATE, KC.CASE_INTRL_ID, KC.CASE_REVIEW_ID, KC.CASE_TITL_NM, KC.CREATED_TS, KC.CREATE_BY_ID, KC.LAST_UPDATED_TS, KC.LAST_UPDATED_BY_ID, KC.OWNER_ID, KC.ASSIGNED_TO_ID, KC.ASSIGNED_BY_ID, KC.CONFIDENTIAL_FL, KC.CASE_DUE_TS, KC.CASE_CLOSED_TS, KC.CASE_ASSIGNED_TS, KC.CASE_DESC_TX, KC.STATUS_CD, KC.CASE_SMRY, KC.LOCK_BY, KC.LOCK_TS, KC.CASE_RESOLUTION, KC.CASE_RESOLUTION_COMMENTS, KC.CASE_RESOLUTION_TS, KC.LAST_ACTION_NAME, KC.LINKED_ALERT_CT, KC.LINKED_CASE_CT, KC.CNTRY_ID, KC.CNTRY_KEY_ID, KC.ORIG_OWNER_ID, KC.OWNER_ORG, KC.JRSDCN_CD, KC.CASE_SRC_CD, KC.PRIORITY_CD, KC.SCNRO_CLASS_CD, KC.PRIOR_STATUS_CD, KC.BUS_DMN_ST, KC.SCORE_CT, KC.PRCSNG_CMPLT_FL, KC.CASE_TYPE_SUBTYPE_CD, KC.CASE_TYPE_CD, KC.CASE_SUBTYPE_CD, KC.CASE_SUBCLASS1_CD, KC.CASE_SUBCLASS2_CD, KC.AGE, KC.RA_ID, KC.CNTRY_KEY_NM, KC.KYC_RULE_TYPE, KC.KYC_CUST_TYPE, KC.DOC_CNTRL_NB FROM ((SELECT DISTINCT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS) KS JOIN IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES KC ON ((KS.IDP_DATA_DATE >= KC.IDP_EFFECTIVE_DATE)))) KSKC WHERE (KSKC.RK_CASE = 1)) C ON ((C.STATUS_CD = S.STATUS_CD))) JOIN CURRENT_KDD_REVIEW_OWNER O1 ON ((O1.OWNER_SEQ_ID = C.CREATE_BY_ID))) JOIN CURRENT_KDD_REVIEW_OWNER O2 ON ((O2.OWNER_SEQ_ID = C.OWNER_ID))) LEFT JOIN (SELECT KCA.CASE_INTRL_ID, KCA.ACTION_ID FROM (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.CASE_INTRL_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_ID, ROW_NUMBER() OVER (PARTITION BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.CASE_INTRL_ID ORDER BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_SEQ_ID DESC ) AS RK_ACTION FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_ID IN (('2008'::"NUMERIC")::NUMERIC(10,0), ('2002'::"NUMERIC")::NUMERIC(10,0)))) KCA WHERE (KCA.RK_ACTION = 1)) RECOMMENDACTION ON ((C.CASE_INTRL_ID = RECOMMENDACTION.CASE_INTRL_ID))) LEFT JOIN (SELECT KCA.CASE_INTRL_ID, KCA.ACTION_TS FROM (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.CASE_INTRL_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_TS, ROW_NUMBER() OVER (PARTITION BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.CASE_INTRL_ID ORDER BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_SEQ_ID DESC ) AS RK_ACTION FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_ID IN (('2006'::"NUMERIC")::NUMERIC(10,0)))) KCA WHERE (KCA.RK_ACTION = 1)) APPROVEDACTION ON ((C.CASE_INTRL_ID = APPROVEDACTION.CASE_INTRL_ID))) LEFT JOIN CURRENT_KDD_ACTION A ON ((RECOMMENDACTION.ACTION_ID = A.ACTION_ID))) WHERE ((S.STATUS_CD IN (('NW'::"VARCHAR")::VARCHAR(10), ('NW90'::"VARCHAR")::VARCHAR(10), ('INV'::"VARCHAR")::VARCHAR(10), ('MON'::"VARCHAR")::VARCHAR(10), ('EDRR'::"VARCHAR")::VARCHAR(10), ('UARR'::"VARCHAR")::VARCHAR(10), ('RMON'::"VARCHAR")::VARCHAR(10), ('RCL'::"VARCHAR")::VARCHAR(10), ('CLAP'::"VARCHAR")::VARCHAR(10), ('APP'::"VARCHAR")::VARCHAR(10), ('REJ'::"VARCHAR")::VARCHAR(10), ('AR'::"VARCHAR")::VARCHAR(10), ('RO'::"VARCHAR")::VARCHAR(10), ('RAC'::"VARCHAR")::VARCHAR(10), ('RECAP'::"VARCHAR")::VARCHAR(10))) OR ((C.STATUS_CD NOTNULL) AND (S.STATUS_CD NOT IN (('CQCC'::"VARCHAR")::VARCHAR(10), ('CQCO'::"VARCHAR")::VARCHAR(10), ('CCNC'::"VARCHAR")::VARCHAR(10), ('CCAC'::"VARCHAR")::VARCHAR(10), ('CCLD'::"VARCHAR")::VARCHAR(10), ('CL'::"VARCHAR")::VARCHAR(10), ('UARC'::"VARCHAR")::VARCHAR(10), ('EDRC'::"VARCHAR")::VARCHAR(10), ('SARC'::"VARCHAR")::VARCHAR(10), ('CCWA'::"VARCHAR")::VARCHAR(10)))));
CREATE OR REPLACE VIEW IDP_L3_AMLCMTM.V_CASE_PERF_REPORTS AS SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case ID", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Title", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Create Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Created By", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Last Updated", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Owner", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Closed Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Last Case Closed Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Status", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Resolution", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Resolution Comments", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Resolution Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Last Activity", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Jurisdiction", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Sub Type", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Age", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Linked Alerts", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Linked Alert Count", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."STR Filed", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Adverse Information Filed", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."EDD Completed", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Rejected", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."QC Rework/Reassign", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Last QC Rework/Reassign Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."QC Reopen", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Last QC Reopen Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Closed By", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Closed By Jurisdiction", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Closure Age Bucket", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."Case Closed Comment", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."QC Open Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."QC Closed Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."QC Closed By", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."QC Closed By Jurisdiction", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."QC Closure Age Bucket", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."QC Closed Comment", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."File/EDD Completed Date", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."File/EDD Closure Age Bucket", IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS."File/EDD Closed Comment" FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.CASE_PERF_REPORTS;
CREATE OR REPLACE VIEW IDP_L3_AMLCMTM.V_ALERT_QC_REPORTS AS WITH CURRENT_KDD_REVIEW_OWNER AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.RPTG_GROUP_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.ACTV_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.EMAIL_ADDR_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_DSPLY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWN_ALERT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.LAST_FAILED_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.CURR_VALID_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.PREV_VALID_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWN_CASE_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.PWD_CHG_DT FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE = (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE) AS MAX FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER))) SELECT R.IDP_DATA_DATE, R.REVIEW_ID AS "Alert ID", TO_CHAR("TIMESTAMP"(R.CREAT_TS), 'YYYY-MM-DD'::"VARCHAR") AS "Alert Create Date", TO_CHAR(QC.START_DT, 'YYYY-MM-DD'::"VARCHAR") AS "QC Create Date", R.FOCAL_NTITY_DSPLY_NM AS "Business Name", R.OWNER_ORG AS "Owner Jurisdiction", U3.OWNER_DSPLY_NM AS "Assigned To", R.BUS_DMN_DSPLY_NM_ST AS "Category", R.JRSDCN_CD AS "Alert Jurisdiction", R.SCNRO_DISPL_NM AS "Scenario", U2.OWNER_DSPLY_NM AS "Alert Closed By", CASE WHEN ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 6) THEN '00-05'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 6) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 11)) THEN '06-10'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 11) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 16)) THEN '11-15'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 16) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 21)) THEN '16-20'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 21) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 26)) THEN '21-25'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 26) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 31)) THEN '26-30'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 31) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 36)) THEN '31-35'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 36) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 45)) THEN '36-44'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 45) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 61)) THEN '45-60'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 61) AND ((R.IDP_DATA_DATE - DATE(QC.START_DT)) < 81)) THEN '61-80'::"VARCHAR" WHEN ((R.IDP_DATA_DATE - DATE(QC.START_DT)) >= 81) THEN '81+'::"VARCHAR" ELSE NULL::"VARCHAR" END AS "Age Bucket", R.HILGT_TX AS "Highlight" FROM (((((SELECT KRRI.IDP_DATA_DATE, KRRI.IDP_WAREHOUSE_ID, KRRI.IDP_AUDIT_ID, KRRI.IDP_EFFECTIVE_DATE, KRRI.IDP_END_DATE, KRRI.IDP_DELETE_DATE, KRRI.REVIEW_ID, KRRI.CREAT_ID, KRRI.CREAT_TS, KRRI.LOCK_ID, KRRI.LOCK_TS, KRRI.STATUS_CD, KRRI.STATUS_DT, KRRI.CNTRY_ID, KRRI.CLS_ID, KRRI.CNTRY_KEY_ID, KRRI.SCNRO_ID, KRRI.SCNRO_CLASS_CD, KRRI.PTTRN_ID, KRRI.DMN_CD, KRRI.ALERT_CT, KRRI.REOPN_FL, KRRI.REASN_FL, KRRI.OWNER_SEQ_ID, KRRI.SCNRO_CT, KRRI.SCORE_CT, KRRI.SCORE_FL, KRRI.DUE_DT, KRRI.AUTO_CLS_CT, KRRI.CSTM_1_TX, KRRI.CSTM_2_TX, KRRI.CSTM_3_TX, KRRI.CSTM_4_TX, KRRI.CSTM_5_TX, KRRI.CSTM_1_DT, KRRI.CSTM_2_DT, KRRI.CSTM_3_DT, KRRI.CSTM_1_RL, KRRI.CSTM_2_RL, KRRI.CSTM_3_RL, KRRI.AUTO_REASN_FL, KRRI.LAST_ACTVY_TYPE_CD, KRRI.PREV_MATCH_CT_SM_SCNRO, KRRI.PREV_MATCH_CT_ALL, KRRI.OWNER_ORG, KRRI.AGE, KRRI.FOCAL_NTITY_DSPLY_NM, KRRI.FOCAL_NTITY_DSPLY_ID, KRRI.PREV_MATCH_CT_SM_SCNRO_CLASS, KRRI.SEND_NTFCTN_CD, KRRI.BUS_DMN_ST, KRRI.BUS_DMN_DSPLY_NM_ST, KRRI.JRSDCN_CD, KRRI.PRCSNG_BATCH_NM, KRRI.PRCSNG_BATCH_CMPLT_FL, KRRI.HILGT_TX, KRRI.ORIG_OWNER_SEQ_ID, KRRI.CLS_ACTVY_TYPE_CD, KRRI.CLS_CLASS_CD, KRRI.SCNRO_DISPL_NM, KRRI.EXTRL_REF_ID, KRRI.EXTRL_REF_LINK, KRRI.EXTRL_REF_SRC_ID, KRRI.RQST_AUDIT_SEQ_ID, KRRI.PRCSNG_DT, KRRI.REVIEW_TYPE_CD, KRRI.ALERT_CASE_CT, KRRI.LAST_LINK_FL, KRRI.LINK_UPDT_TS, KRRI.LINK_UPDT_ID, KRRI.REG_STATUS_LIST, KRRI.RK_REVIEW FROM (SELECT IDD.IDP_DATA_DATE, KR.IDP_WAREHOUSE_ID, KR.IDP_AUDIT_ID, KR.IDP_EFFECTIVE_DATE, KR.IDP_END_DATE, KR.IDP_DELETE_DATE, KR.REVIEW_ID, KR.CREAT_ID, KR.CREAT_TS, KR.LOCK_ID, KR.LOCK_TS, KR.STATUS_CD, KR.STATUS_DT, KR.CNTRY_ID, KR.CLS_ID, KR.CNTRY_KEY_ID, KR.SCNRO_ID, KR.SCNRO_CLASS_CD, KR.PTTRN_ID, KR.DMN_CD, KR.ALERT_CT, KR.REOPN_FL, KR.REASN_FL, KR.OWNER_SEQ_ID, KR.SCNRO_CT, KR.SCORE_CT, KR.SCORE_FL, KR.DUE_DT, KR.AUTO_CLS_CT, KR.CSTM_1_TX, KR.CSTM_2_TX, KR.CSTM_3_TX, KR.CSTM_4_TX, KR.CSTM_5_TX, KR.CSTM_1_DT, KR.CSTM_2_DT, KR.CSTM_3_DT, KR.CSTM_1_RL, KR.CSTM_2_RL, KR.CSTM_3_RL, KR.AUTO_REASN_FL, KR.LAST_ACTVY_TYPE_CD, KR.PREV_MATCH_CT_SM_SCNRO, KR.PREV_MATCH_CT_ALL, KR.OWNER_ORG, KR.AGE, KR.FOCAL_NTITY_DSPLY_NM, KR.FOCAL_NTITY_DSPLY_ID, KR.PREV_MATCH_CT_SM_SCNRO_CLASS, KR.SEND_NTFCTN_CD, KR.BUS_DMN_ST, KR.BUS_DMN_DSPLY_NM_ST, KR.JRSDCN_CD, KR.PRCSNG_BATCH_NM, KR.PRCSNG_BATCH_CMPLT_FL, KR.HILGT_TX, KR.ORIG_OWNER_SEQ_ID, KR.CLS_ACTVY_TYPE_CD, KR.CLS_CLASS_CD, KR.SCNRO_DISPL_NM, KR.EXTRL_REF_ID, KR.EXTRL_REF_LINK, KR.EXTRL_REF_SRC_ID, KR.RQST_AUDIT_SEQ_ID, KR.PRCSNG_DT, KR.REVIEW_TYPE_CD, KR.ALERT_CASE_CT, KR.LAST_LINK_FL, KR.LINK_UPDT_TS, KR.LINK_UPDT_ID, KR.REG_STATUS_LIST, ROW_NUMBER() OVER (PARTITION BY IDD.IDP_DATA_DATE, KR.REVIEW_ID ORDER BY KR.IDP_EFFECTIVE_DATE DESC ) AS RK_REVIEW FROM ((SELECT DISTINCT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS) IDD JOIN IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW KR ON ((IDD.IDP_DATA_DATE >= KR.IDP_EFFECTIVE_DATE)))) KRRI WHERE (KRRI.RK_REVIEW = 1)) R LEFT JOIN (SELECT A4.REVIEW_ID, A4.CREAT_ID FROM (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.REVIEW_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.CREAT_ID, ROW_NUMBER() OVER (PARTITION BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.REVIEW_ID ORDER BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.START_DT DESC ) AS RK_ACTIVITY FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.ACTVY_TYPE_CD IN (('CST006'::"VARCHAR")::VARCHAR(10), ('CST010'::"VARCHAR")::VARCHAR(10), ('CST015'::"VARCHAR")::VARCHAR(10), ('MTS050'::"VARCHAR")::VARCHAR(10), ('MTS051'::"VARCHAR")::VARCHAR(10), ('MTS052'::"VARCHAR")::VARCHAR(10), ('MTS053'::"VARCHAR")::VARCHAR(10), ('MTS203'::"VARCHAR")::VARCHAR(10), ('MTS203A'::"VARCHAR")::VARCHAR(10), ('MTS204'::"VARCHAR")::VARCHAR(10), ('MTS204A'::"VARCHAR")::VARCHAR(10), ('MTS500'::"VARCHAR")::VARCHAR(10), ('MTS370'::"VARCHAR")::VARCHAR(10)))) A4 WHERE (A4.RK_ACTIVITY = 1)) A6 ON ((R.REVIEW_ID = A6.REVIEW_ID))) LEFT JOIN CURRENT_KDD_REVIEW_OWNER U2 ON ((A6.CREAT_ID = U2.OWNER_SEQ_ID))) LEFT JOIN (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.REVIEW_ID, MIN(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.START_DT) AS START_DT FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.NEW_REVIEW_STATUS_CD = 'AQCO'::"VARCHAR") GROUP BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTIVITY.REVIEW_ID) QC ON ((R.REVIEW_ID = QC.REVIEW_ID))) LEFT JOIN CURRENT_KDD_REVIEW_OWNER U3 ON ((R.OWNER_SEQ_ID = U3.OWNER_SEQ_ID))) WHERE (R.STATUS_CD IN (('AQCO'::"VARCHAR")::VARCHAR(10)));
CREATE OR REPLACE VIEW IDP_L3_AMLCMTM.V_CASE_QC_REPORTS AS WITH CURRENT_KDD_REVIEW_OWNER AS (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_SEQ_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.RPTG_GROUP_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.ACTV_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.BUS_DMN_ST, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.EMAIL_ADDR_TX, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWNER_DSPLY_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWN_ALERT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.LAST_FAILED_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.CURR_VALID_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.PREV_VALID_LOGON_TS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.OWN_CASE_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.PWD_CHG_DT FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE = (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER.IDP_DATA_DATE) AS MAX FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_REVIEW_OWNER))) SELECT R.IDP_DATA_DATE, R.CASE_INTRL_ID AS "Case ID", R.CASE_TITL_NM AS "Case Title", R.CASE_DESC_TX AS "Case Description", TO_CHAR("TIMESTAMP"(R.CREATED_TS), 'YYYY-MM-DD'::"VARCHAR") AS "Case Create Date", TO_CHAR("TIMESTAMP"(QC.ACTION_TS), 'YYYY-MM-DD'::"VARCHAR") AS "QC Create Date", R.OWNER_ORG AS "Owner Jurisdiction", U3.OWNER_DSPLY_NM AS "Assigned To", R.JRSDCN_CD AS "Case Jurisdiction", U2.OWNER_DSPLY_NM AS "Closed By", CASE WHEN ((R.IDP_DATA_DATE - QC.ACTION_TS) < 6) THEN '00-05'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 6) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 11)) THEN '06-10'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 11) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 16)) THEN '11-15'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 16) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 21)) THEN '16-20'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 21) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 26)) THEN '21-25'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 26) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 31)) THEN '26-30'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 31) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 36)) THEN '31-35'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 36) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 45)) THEN '36-44'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 45) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 61)) THEN '45-60'::"VARCHAR" WHEN (((R.IDP_DATA_DATE - QC.ACTION_TS) >= 61) AND ((R.IDP_DATA_DATE - QC.ACTION_TS) < 81)) THEN '61-80'::"VARCHAR" WHEN ((R.IDP_DATA_DATE - QC.ACTION_TS) >= 81) THEN '81+'::"VARCHAR" ELSE NULL::"VARCHAR" END AS "Age Bucket" FROM (((((SELECT KSKC.IDP_DATA_DATE, KSKC.RK_CASE, KSKC.IDP_WAREHOUSE_ID, KSKC.IDP_AUDIT_ID, KSKC.IDP_EFFECTIVE_DATE, KSKC.IDP_END_DATE, KSKC.IDP_DELETE_DATE, KSKC.CASE_INTRL_ID, KSKC.CASE_REVIEW_ID, KSKC.CASE_TITL_NM, KSKC.CREATED_TS, KSKC.CREATE_BY_ID, KSKC.LAST_UPDATED_TS, KSKC.LAST_UPDATED_BY_ID, KSKC.OWNER_ID, KSKC.ASSIGNED_TO_ID, KSKC.ASSIGNED_BY_ID, KSKC.CONFIDENTIAL_FL, KSKC.CASE_DUE_TS, KSKC.CASE_CLOSED_TS, KSKC.CASE_ASSIGNED_TS, KSKC.CASE_DESC_TX, KSKC.STATUS_CD, KSKC.CASE_SMRY, KSKC.LOCK_BY, KSKC.LOCK_TS, KSKC.CASE_RESOLUTION, KSKC.CASE_RESOLUTION_COMMENTS, KSKC.CASE_RESOLUTION_TS, KSKC.LAST_ACTION_NAME, KSKC.LINKED_ALERT_CT, KSKC.LINKED_CASE_CT, KSKC.CNTRY_ID, KSKC.CNTRY_KEY_ID, KSKC.ORIG_OWNER_ID, KSKC.OWNER_ORG, KSKC.JRSDCN_CD, KSKC.CASE_SRC_CD, KSKC.PRIORITY_CD, KSKC.SCNRO_CLASS_CD, KSKC.PRIOR_STATUS_CD, KSKC.BUS_DMN_ST, KSKC.SCORE_CT, KSKC.PRCSNG_CMPLT_FL, KSKC.CASE_TYPE_SUBTYPE_CD, KSKC.CASE_TYPE_CD, KSKC.CASE_SUBTYPE_CD, KSKC.CASE_SUBCLASS1_CD, KSKC.CASE_SUBCLASS2_CD, KSKC.AGE, KSKC.RA_ID, KSKC.CNTRY_KEY_NM, KSKC.KYC_RULE_TYPE, KSKC.KYC_CUST_TYPE, KSKC.DOC_CNTRL_NB FROM (SELECT KS.IDP_DATA_DATE, ROW_NUMBER() OVER (PARTITION BY KS.IDP_DATA_DATE, KC.CASE_INTRL_ID ORDER BY KC.IDP_EFFECTIVE_DATE DESC ) AS RK_CASE, KC.IDP_WAREHOUSE_ID, KC.IDP_AUDIT_ID, KC.IDP_EFFECTIVE_DATE, KC.IDP_END_DATE, KC.IDP_DELETE_DATE, KC.CASE_INTRL_ID, KC.CASE_REVIEW_ID, KC.CASE_TITL_NM, KC.CREATED_TS, KC.CREATE_BY_ID, KC.LAST_UPDATED_TS, KC.LAST_UPDATED_BY_ID, KC.OWNER_ID, KC.ASSIGNED_TO_ID, KC.ASSIGNED_BY_ID, KC.CONFIDENTIAL_FL, KC.CASE_DUE_TS, KC.CASE_CLOSED_TS, KC.CASE_ASSIGNED_TS, KC.CASE_DESC_TX, KC.STATUS_CD, KC.CASE_SMRY, KC.LOCK_BY, KC.LOCK_TS, KC.CASE_RESOLUTION, KC.CASE_RESOLUTION_COMMENTS, KC.CASE_RESOLUTION_TS, KC.LAST_ACTION_NAME, KC.LINKED_ALERT_CT, KC.LINKED_CASE_CT, KC.CNTRY_ID, KC.CNTRY_KEY_ID, KC.ORIG_OWNER_ID, KC.OWNER_ORG, KC.JRSDCN_CD, KC.CASE_SRC_CD, KC.PRIORITY_CD, KC.SCNRO_CLASS_CD, KC.PRIOR_STATUS_CD, KC.BUS_DMN_ST, KC.SCORE_CT, KC.PRCSNG_CMPLT_FL, KC.CASE_TYPE_SUBTYPE_CD, KC.CASE_TYPE_CD, KC.CASE_SUBTYPE_CD, KC.CASE_SUBCLASS1_CD, KC.CASE_SUBCLASS2_CD, KC.AGE, KC.RA_ID, KC.CNTRY_KEY_NM, KC.KYC_RULE_TYPE, KC.KYC_CUST_TYPE, KC.DOC_CNTRL_NB FROM ((SELECT DISTINCT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS.IDP_DATA_DATE FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_STATUS) KS JOIN IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASES KC ON ((KS.IDP_DATA_DATE >= KC.IDP_EFFECTIVE_DATE)))) KSKC WHERE (KSKC.RK_CASE = 1)) R LEFT JOIN (SELECT AC5.CASE_INTRL_ID, AC5.ACTION_BY_ID FROM (SELECT A4.CASE_INTRL_ID, A4.ACTION_BY_ID, ROW_NUMBER() OVER (PARTITION BY A4.CASE_INTRL_ID ORDER BY A4.ACTION_TS DESC ) AS RK_ACTIONS FROM (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS A4 JOIN (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_AUDIT_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_DATA_DATE, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_ID, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_NM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_DESC, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.LAST_UPDATED_DT, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.LAST_UPDATED_BY, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.COMMENTS, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_ORDER, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REQ_CMMNT_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.DFLT_DUE_DT_LM, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REQ_REASN_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REQ_DUE_DATE_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.NEXT_REVIEW_STATUS_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REG_TYPE_CD, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.REQ_REASN_OWNER_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.LAST_ASSIGN_REQ, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.RESOLUTION_ACTION_FL, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.EXPORT_DIR_REF, IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_CATEGORY_CODE FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION WHERE ((IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_DATA_DATE = (SELECT MAX(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.IDP_DATA_DATE) AS MAX FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION)) AND (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_ACTION.ACTION_CD IN (('CA86S'::"VARCHAR")::VARCHAR(20), ('CA88S'::"VARCHAR")::VARCHAR(20), ('CA90S'::"VARCHAR")::VARCHAR(20), ('CA213SE'::"VARCHAR")::VARCHAR(20), ('CA2004'::"VARCHAR")::VARCHAR(20), ('CA2005'::"VARCHAR")::VARCHAR(20), ('CA2007'::"VARCHAR")::VARCHAR(20), ('CA2009'::"VARCHAR")::VARCHAR(20), ('CA2009A'::"VARCHAR")::VARCHAR(20), ('CA2007A'::"VARCHAR")::VARCHAR(20), ('CA2004A'::"VARCHAR")::VARCHAR(20), ('CA2005A'::"VARCHAR")::VARCHAR(20))))) AC4 ON ((A4.ACTION_ID = AC4.ACTION_ID)))) AC5 WHERE (AC5.RK_ACTIONS = 1)) A6 ON ((R.CASE_INTRL_ID = A6.CASE_INTRL_ID))) LEFT JOIN CURRENT_KDD_REVIEW_OWNER U2 ON (("NUMERIC"(A6.ACTION_BY_ID, 655372) = U2.OWNER_SEQ_ID))) LEFT JOIN (SELECT IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.CASE_INTRL_ID, MIN(IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.ACTION_TS) AS ACTION_TS FROM IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS WHERE (IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.STATUS_CD IN (('CQCO'::"VARCHAR")::VARCHAR(20))) GROUP BY IDP_PRD_LEVEL3.IDP_L3_AMLCMTM.KDD_CASE_ACTIONS.CASE_INTRL_ID) QC ON ((R.CASE_INTRL_ID = QC.CASE_INTRL_ID))) LEFT JOIN CURRENT_KDD_REVIEW_OWNER U3 ON ((R.OWNER_ID = U3.OWNER_SEQ_ID))) WHERE (R.STATUS_CD IN (('CQCO'::"VARCHAR")::VARCHAR(20)));
`;
// Reset `lastIndex` if this regex is defined globally
// regex.lastIndex = 0;
let m;
while ((m = regex.exec(str)) !== null) {
// This is necessary to avoid infinite loops with zero-width matches
if (m.index === regex.lastIndex) {
regex.lastIndex++;
}
// The result can be accessed through the `m`-variable.
m.forEach((match, groupIndex) => {
console.log(`Found match, group ${groupIndex}: ${match}`);
});
}
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for JavaScript, please visit: https://developer.mozilla.org/en/docs/Web/JavaScript/Guide/Regular_Expressions