Regular Expressions 101

Save & Share

Flavor

  • PCRE2 (PHP >=7.3)
  • PCRE (PHP <7.3)
  • ECMAScript (JavaScript)
  • Python
  • Golang
  • Java 8
  • .NET 7.0 (C#)
  • Rust
  • Regex Flavor Guide

Function

  • Match
  • Substitution
  • List
  • Unit Tests

Tools

Sponsors
There are currently no sponsors. Become a sponsor today!
An explanation of your regex will be automatically generated as you type.
Detailed match information will be displayed here automatically.
  • All Tokens
  • Common Tokens
  • General Tokens
  • Anchors
  • Meta Sequences
  • Quantifiers
  • Group Constructs
  • Character Classes
  • Flags/Modifiers
  • Substitution
  • A single character of: a, b or c
    [abc]
  • A character except: a, b or c
    [^abc]
  • A character in the range: a-z
    [a-z]
  • A character not in the range: a-z
    [^a-z]
  • A character in the range: a-z or A-Z
    [a-zA-Z]
  • Any single character
    .
  • Alternate - match either a or b
    a|b
  • Any whitespace character
    \s
  • Any non-whitespace character
    \S
  • Any digit
    \d
  • Any non-digit
    \D
  • Any word character
    \w
  • Any non-word character
    \W
  • Non-capturing group
    (?:...)
  • Capturing group
    (...)
  • Zero or one of a
    a?
  • Zero or more of a
    a*
  • One or more of a
    a+
  • Exactly 3 of a
    a{3}
  • 3 or more of a
    a{3,}
  • Between 3 and 6 of a
    a{3,6}
  • Start of string
    ^
  • End of string
    $
  • A word boundary
    \b
  • Non-word boundary
    \B

Regular Expression
No Match

/
/
g

Test String

Code Generator

Language

Generated Code

// include the latest version of the regex crate in your Cargo.toml extern crate regex; use regex::Regex; fn main() { let regex = Regex::new(r"(?:FROM|JOIN)\s*(IDP_PRD_.+?)(?:\s|\))").unwrap(); let string = "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))); "; // result will be an iterator over tuples containing the start and end indices for each match in the string let result = regex.captures_iter(string); for mat in result { println!("{:?}", mat); } }

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 Rust, please visit: https://docs.rs/regex/latest/regex/