using System;
using System.Text.RegularExpressions;
public class Example
{
public static void Main()
{
string pattern = @"(?:FROM|JOIN)\s*(IDP_PRD_.+?)(?:\s|\))";
string input = @"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)));
";
foreach (Match m in Regex.Matches(input, pattern))
{
Console.WriteLine("'{0}' found at index {1}.", m.Value, m.Index);
}
}
}
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 C#, please visit: https://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex(v=vs.110).aspx