# coding=utf8
# the above tag defines encoding for this document and is for Python 2.x compatibility
import re
regex = r"(?:FROM|JOIN)\s*(IDP_PRD_.+?)(?:\s|\))"
test_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)));\n\n"
"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;\n\n"
"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);\n\n"
"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);\n\n"
"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)))));\n\n"
"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;\n\n"
"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)));\n\n"
"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)));\n")
matches = re.finditer(regex, test_str)
for matchNum, match in enumerate(matches, start=1):
print ("Match {matchNum} was found at {start}-{end}: {match}".format(matchNum = matchNum, start = match.start(), end = match.end(), match = match.group()))
for groupNum in range(0, len(match.groups())):
groupNum = groupNum + 1
print ("Group {groupNum} found at {start}-{end}: {group}".format(groupNum = groupNum, start = match.start(groupNum), end = match.end(groupNum), group = match.group(groupNum)))
# Note: for Python 2.7 compatibility, use ur"" to prefix the regex and u"" to prefix the test string and substitution.
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 Python, please visit: https://docs.python.org/3/library/re.html