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

using System; using System.Text.RegularExpressions; public class Example { public static void Main() { string pattern = @"\s((?:\w|_)+\.(?:\w|_)+\.(?:\w|_)+)\s"; string input = @"SELECT T.IDP_WAREHOUSE_ID, T.IDP_AUDIT_ID, T.IDP_DATA_ID, T.IDP_DATA_DATE, T.TRADESYSTEM, T.SOURCESYSTEM, T.SUBSYSTEM, T.TRADEDATE, T.VALUATIONDATE, T.TRADEID, T.TRADETYPE, T.TRADESTATUS, T.MAPPEDTRADESTATUS, T.RAWBOOKINGLOCATION, T.MAPPEDBOOKINGLOCATION, T.BOOKINGLOCREGION, T.CMCIFBOOKINGLOCATION, T.CMCIFBOOKINGLOCATIONREGION, T.COUNTERPARTYACCOUNTID, T.COUNTERPARTYSHORTNAME, T.COUNTERPARTYTYPE, T.CUSTOMERNAME, T.CUSTOMERADDRESS, T.CUSTOMERCOUNTRYCODE, T.CUSTOMERCOUNTRYNAME, T.CAMLO_RISK, T.US_BSA_RISK, T.RISKRATING, T.PRODUCTTYPE, T.PRODUCT, T.CMCIFPRODUCTTYPE, T.QUANTITY, T.PRICE, T.CURRENCY, T.CURRENCY_ISO_CODE, T.CURRENCY_DESCRIPTION, T.""VALUE"", T.TRADERID, T.TRANSACTIONACTIVITY, T.SOURCETRADEID, T.TRADESEQUENCENUMBER, T.BASECURRENCY, T.BASECURRPRICE, T.BASECURRQUANTITY, T.BASECURRVALUE, T.ISIN, T.CUSIP, T.RESP, T.FACILITY_GLOBAL_COMMITMENT, T.FACILITY_HOST_BANK_NET_AMT, T.MAP_TRADESYSTEM, T.MAP_ACCOUNTFIELDNAME, T.MAP_COUNTERPARTYACCOUNTID, T.MAP_CUID, T.MAP_CIF, T.MAP_UEN, RC.RAW AS CHICAGO_RAW, RC.RESPONSIBILITY_CENTER AS CHICAGO_RESPONSIBILITY_CENTER, RC.DESK AS CHICAGO_DESK, RC.BUSINESS_SEGMENT AS CHICAGO_BUSINESS_SEGMENT, RC.LOB AS CHICAGO_LOB, RC.OPERATING_GROUP AS CHICAGO_OPERATING_GROUP, AFF.RAW AS AFF_RAW, ISS.RAW AS ISS_RAW, EC1.ENTITYCOUNTRY AS REGISTRATION_COUNTRY, EC2.ENTITYCOUNTRY AS PRINCIPAL_PLACE_OF_BUSINESS, ENT.CIF AS ENT_CIF, ENT.UEN AS ENT_UEN, ENT.ENTITYLEGALNAME AS ENT_ENTITYLEGALNAME, ENT.ENTITYADDRESS AS ENT_ENTITYADDRESS, ENT.ENTITYACCOUNTNUMBER AS ENT_ENTITYACCOUNTNUMBER, ENT.ENTITYSTATUS AS ENT_ENTITYSTATUS, ENT.IFI_FLAG AS ENT_IFI_FLAG, ENT.SICCAN AS ENT_SICCAN, ENT.SICUS AS ENT_SICUS, ENT.NBFI_312 AS ENT_NBFI_312, ENT.NBFI_NON_312 AS ENT_NBFI_NON_312, ENT.RMA AS ENT_RMA, ENT.SICCAN_DESC AS ENT_SICCAN_DESC, ENT.SICUS_DESC AS ENT_SICUS_DESC, ENT.P_AND_C AS ENT_P_AND_C, ENT.OFAC AS ENT_OFAC, ENT.RMA_ONLY AS ENT_RMA_ONLY, ENT.CONN_UEN AS ENT_CONN_UEN, ENT.CM_INDICATOR AS ENT_CM_INDICATOR, ENT.BMO_RESP AS ENT_BMO_RESP, ENT.HARRIS_RESP AS ENT_HARRIS_RESP, ENT.PRIM_IND AS ENT_PRIM_IND, ENT.PM_CODE AS ENT_PM_CODE, APPR.CUSTOMER_NAME AS APPR_CUSTOMER_NAME, APPR.UEN AS APPR_UEN, APPR.COUNTRY AS APPR_COUNTRY, APPR.CONDITIONAL_APPROVAL AS APPR_CONDITIONAL_APPROVAL, APPR.PRODUCTS AS APPR_PRODUCTS, APPR_FI.CUSTOMER_NAME AS APPR_FI_CUSTOMER_NAME, APPR_FI.UEN AS APPR_FI_UEN, APPR_FI.COUNTRY AS APPR_FI_COUNTRY, APPR_FI.CONDITIONAL_APPROVAL AS APPR_FI_CONDITIONAL_APPROVAL, APPR_FI.PRODUCTS AS APPR_FI_PRODUCTS, APMS.UEN AS APMS_UEN, APMS.CIF AS APMS_CIF, APMS.ENTITY_LEGAL_NAME AS APMS_ENTITY_LEGAL_NAME, APMS.ENTITY_SHORT_NAME AS APMS_ENTITY_SHORT_NAME, APMS.OWNERSHIP_CLASS AS APMS_OWNERSHIP_CLASS, APMS.ENTITY_CLASSIFICATION AS APMS_ENTITY_CLASSIFICATION, APMS.ASSET_CLASSIFICATION AS APMS_ASSET_CLASSIFICATION, APMS.BMO_RC AS APMS_BMO_RC, APMS.ENTITY_TYPE AS APMS_ENTITY_TYPE, APMS.TIN_SSN AS APMS_TIN_SSN FROM ((((((((((SELECT A.IDP_WAREHOUSE_ID, A.IDP_AUDIT_ID, A.IDP_DATA_ID, A.IDP_DATA_DATE, A.TRADESYSTEM, A.SOURCESYSTEM, A.SUBSYSTEM, A.TRADEDATE, A.VALUATIONDATE, A.TRADEID, A.TRADETYPE, A.TRADESTATUS, A.MAPPEDTRADESTATUS, A.RAWBOOKINGLOCATION, A.MAPPEDBOOKINGLOCATION, A.BOOKINGLOCREGION, A.CMCIFBOOKINGLOCATION, A.CMCIFBOOKINGLOCATIONREGION, A.COUNTERPARTYACCOUNTID, A.COUNTERPARTYSHORTNAME, A.COUNTERPARTYTYPE, A.CUSTOMERNAME, A.CUSTOMERADDRESS, A.CUSTOMERCOUNTRYCODE, A.CUSTOMERCOUNTRYNAME, A.CAMLO_RISK, A.US_BSA_RISK, A.RISKRATING, A.PRODUCTTYPE, A.PRODUCT, A.CMCIFPRODUCTTYPE, A.QUANTITY, A.PRICE, A.CURRENCY, A.CURRENCY_ISO_CODE, A.CURRENCY_DESCRIPTION, A.""VALUE"", A.TRADERID, A.TRANSACTIONACTIVITY, A.SOURCETRADEID, A.TRADESEQUENCENUMBER, A.BASECURRENCY, A.BASECURRPRICE, A.BASECURRQUANTITY, A.BASECURRVALUE, A.ISIN, A.CUSIP, A.RESP, A.FACILITY_GLOBAL_COMMITMENT, A.FACILITY_HOST_BANK_NET_AMT, MAP.TRADESYSTEM AS MAP_TRADESYSTEM, MAP.ACCOUNTFIELDNAME AS MAP_ACCOUNTFIELDNAME, MAP.COUNTERPARTYACCOUNTID AS MAP_COUNTERPARTYACCOUNTID, MAP.CUID AS MAP_CUID, MAP.CIF AS MAP_CIF, CASE WHEN (A.TRADESYSTEM = 'LIQ'::""VARCHAR"") THEN A.COUNTERPARTYACCOUNTID ELSE (""VARCHAR""(MAP.UEN))::VARCHAR(16) END AS MAP_UEN FROM (BLUEHOUSE.V_TRANSACTIONS A LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITYMAPPING MAP ON ((((A.COUNTERPARTYACCOUNTID = UPPER(BTRIM(MAP.COUNTERPARTYACCOUNTID))) AND (UPPER(A.TRADESYSTEM) = UPPER(MAP.TRADESYSTEM))) AND (MAP.IDP_DATA_ID = (SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITYMAPPING.IDP_DATA_ID) AS MAX FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITYMAPPING)))))) T LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_REF_CHICAGO_RCMAPPING RC ON (((RC.RAW = T.RESP) AND (RC.IDP_END_DATE = '2099-12-31'::DATE)))) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_REF_CHICAGO_AFFILIATED_RCS AFF ON (((AFF.RAW = T.RESP) AND (AFF.IDP_END_DATE = '2099-12-31'::DATE)))) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_REF_CHICAGO_BOOKING_ISSUES_RCS ISS ON (((ISS.RAW = T.RESP) AND (ISS.IDP_END_DATE = '2099-12-31'::DATE)))) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY EC1 ON ((((EC1.IDP_DATA_ID = (SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY.IDP_DATA_ID) AS MAX FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY)) AND ((""VARCHAR""(EC1.UEN))::VARCHAR(16) = T.MAP_UEN)) AND (UPPER(EC1.ENTITYADDRESSTYPE) = 'REGISTERED'::""VARCHAR"")))) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY EC2 ON ((((EC2.IDP_DATA_ID = (SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY.IDP_DATA_ID) AS MAX FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY)) AND ((""VARCHAR""(EC2.UEN))::VARCHAR(16) = T.MAP_UEN)) AND (UPPER(EC2.ENTITYADDRESSTYPE) = 'PRINCIPAL PLACE OF BUSINESS'::""VARCHAR"")))) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIES ENT ON (((ENT.IDP_DATA_ID = (SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIES.IDP_DATA_ID) AS MAX FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIES)) AND ((""VARCHAR""(ENT.UEN))::VARCHAR(16) = T.MAP_UEN)))) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_312 APPR ON (((APPR.IDP_DATA_ID = (SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_312.IDP_DATA_ID) AS MAX FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_312)) AND ((""VARCHAR""(APPR.UEN))::VARCHAR(16) = T.MAP_UEN)))) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_FI APPR_FI ON (((APPR_FI.IDP_DATA_ID = (SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_FI.IDP_DATA_ID) AS MAX FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_FI)) AND ((""VARCHAR""(APPR_FI.UEN))::VARCHAR(16) = T.MAP_UEN)))) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APMS_ENTITIES APMS ON (((APMS.IDP_DATA_ID = (SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APMS_ENTITIES.IDP_DATA_ID) AS MAX FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APMS_ENTITIES)) AND ((""VARCHAR""(APMS.UEN))::VARCHAR(16) = T.MAP_UEN)))) WHERE ((UPPER(T.CMCIFBOOKINGLOCATION) ~~ LIKE_ESCAPE('%CHICAGO%'::""VARCHAR"", '\'::""VARCHAR"")) OR (T.RESP = AFF.RAW)); SELECT A.RUN_TIMESTAMP AS RUN_DATE, A.SOURCE_TXN_UNIQUE_ID, A.SOURCE_TXN_NUM, A.ACCOUNT_SOURCE_UNIQUE_ID AS ACCOUNT_ID, A.ACCOUNT_SOURCE_REF_ID, A.CUSTOMER_SOURCE_UNIQUE_ID AS PRIMARY_CUSTOMER_ID, A.PRIMARY_CUST_SRCE_REF_ID, A.BRANCH_ID, A.TXN_SOURCE_TYPE_CODE, A.OWN_ACCOUNT_TRANSFER, A.CURRENCY_CODE_ORIG, A.CURRENCY_CODE_BASE, A.ORIGINATION_DATE, A.POSTING_DATE, A.VALUE_DATE, A.SYSTEM_TIMESTAMP, A.LOCAL_TIMESTAMP, A.PRODUCT_SOURCE_TYPE_CODE, A.TXN_VOLUME, A.DEVICE_ID, A.TXN_AMOUNT_ORIG, A.TXN_AMOUNT_BASE, A.CREDIT_DEBIT_CODE, A.TRANS_REF_DESC, A.TRANS_REF_DESC_2, A.TRANS_REF_DESC_3, A.TRANS_REF_DESC_4, A.TRANS_REF_DESC_5, A.TRANS_REF_DESC_6, A.CLIENT_DE_PASSAGE, A.TXN_STATUS_CODE, A.TXN_CHANNEL_CODE, A.SOURCE_SYSTEM_CODE, A.ERROR_CORRECT_FLAG, A.TRANSACTION_LOCATION, A.ORG_UNIT_CODE, A.TXN_USR_DTLS, A.DVC_POS_ENTRY_MODE, A.PIN_VERIFY_CD, A.CARD_ID, A.EMPLOYEE_ID, A.COUNTER_PARTY_NAME, A.COUNTER_PARTY_ADDRESS, A.COUNTER_PARTY_ZONE, A.COUNTER_PARTY_POSTAL_CODE, A.COUNTER_PARTY_CITY, A.COUNTER_PARTY_COUNTRY_CODE, A.COUNTER_PARTY_ACCOUNT_NUM, A.COUNTER_PARTY_ACCOUNT_NAME, A.COUNTER_PARTY_ACCOUNT_TYPE, A.COUNTER_PARTY_ACCOUNT_IBAN, A.COUNTER_PARTY_ACCOUNT_BIC, A.COUNTER_PARTY_BANK_NAME, A.COUNTER_PARTY_BANK_CODE, A.COUNTER_PARTY_BANK_ADDRESS, A.COUNTER_PARTY_BANK_CITY, A.COUNTER_PARTY_BANK_ZONE, A.COUNTER_PARTY_BANK_POSTAL_CODE, A.COUNTER_PARTY_BNK_CNTRY_CD, A.ORIGINATOR_NAME, A.BENEFICIARY_NAME, A.ORIGINATOR_BANK_NAME, A.BENEFICIARY_BANK_NAME, A.TELLER_ID, A.CARD_SOURCE_REF_ID, A.CHECK_NUMBER, A.CHECK_ACCOUNT_NUMBER, A.CHECK_AMOUNT, A.CASHBACK_AMT, A.ORIGINATOR_COUNTRY_CODE, C.TIER AS ORIGINATOR_COUNTRY_TIER, A.BENEFICIARY_COUNTRY_CODE, D.TIER AS BENEFICIARY_COUNTRY_TIER, A.ORIGINATOR_FLAG, A.CURRENCY_VAULT_TYPE, A.CHECK_TYPE, A.NUM_CHECKS, A.ACH_TYPE, A.SEC_CODE, A.ATM_NUMBER, A.CPCS_TRACE_NUMBER, A.CPCS_REF_NUMBER, A.MIXED_DEPOSIT_IND, A.TARGET_ROUTING_NUMBER, A.ORIG_ROUTING_NUMBER, A.PHONE_LOCATION_CODE, A.IDP_EFFECTIVE_TIMESTAMP, A.IDP_END_TIMESTAMP, A.IDP_DELETE_IND, A.PSEUDO_FLAG, B.INSTRUMENT AS ""TRANSACTION TYPE"" FROM (((IDP_PRD_LEVEL3.AML_L3_HPT_GRP.TRANSACTIONS A JOIN IDP_PRD_LEVEL3.AML_L3_HPT_GRP.TRANSACTION_TYPE B ON ((A.TXN_SOURCE_TYPE_CODE = B.TXN_TYPE_CODE))) LEFT JOIN IDP_PRD_LEVEL1.DETICA.L1_DETICA_COUNTRY_DLY C ON ((A.ORIGINATOR_COUNTRY_CODE = C.COUNTRY_CODE))) LEFT JOIN IDP_PRD_LEVEL1.DETICA.L1_DETICA_COUNTRY_DLY D ON ((A.BENEFICIARY_COUNTRY_CODE = D.COUNTRY_CODE))) WHERE ((A.CUSTOMER_SOURCE_UNIQUE_ID <> '-1'::""VARCHAR"") AND (A.ACCOUNT_SOURCE_UNIQUE_ID <> '-1'::""VARCHAR"")); WITH TDOB AS (SELECT AML_L3_REP.V_SAR_SUSPECT.SAR_SOURCE_ID, AML_L3_REP.V_SAR_SUSPECT.SUBJECT_ID, TO_DATE(((((""SUBSTRING""(AML_L3_REP.V_SAR_SUSPECT.DOB, 1, 2) || '-'::""VARCHAR"") || ""SUBSTRING""(AML_L3_REP.V_SAR_SUSPECT.DOB, 3, 2)) || '-'::""VARCHAR"") || ""SUBSTRING""(AML_L3_REP.V_SAR_SUSPECT.DOB, 5, 4)), 'MM-DD-YYYY'::""VARCHAR"") AS DOB FROM AML_L3_REP.V_SAR_SUSPECT WHERE (TOOLKIT..ISDATE((((((""SUBSTRING""(AML_L3_REP.V_SAR_SUSPECT.DOB, 1, 2) || '-'::""VARCHAR"") || ""SUBSTRING""(AML_L3_REP.V_SAR_SUSPECT.DOB, 3, 2)) || '-'::""VARCHAR"") || ""SUBSTRING""(AML_L3_REP.V_SAR_SUSPECT.DOB, 5, 4)))::VARCHAR(40), ('MM-DD-YYYY'::""VARCHAR"")::VARCHAR(40)) AND (AML_L3_REP.V_SAR_SUSPECT.SAR_SUSPECT_END_DATE = '9999-12-31'::DATE))) SELECT SA.SAR_SOURCE_ID, SS.SUBJECT_ID, COUNT(*) OVER (PARTITION BY SA.SAR_SOURCE_ID ORDER BY SA.SAR_SOURCE_ID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NUMBER_OF_SUSPECTS, ROW_NUMBER() OVER (PARTITION BY SA.SAR_SOURCE_ID ORDER BY SS.LAST_NAME, SS.FIRST_NAME, SS.MIDDLE_NAME ) AS SUSPECT_SEQUENCE, CASE WHEN (((SS.FIRST_NAME = 'NA'::""VARCHAR"") AND (SS.MIDDLE_NAME = 'NA'::""VARCHAR"")) AND (SS.LAST_NAME = 'NA'::""VARCHAR"")) THEN 'NA'::""VARCHAR"" ELSE ""SUBSTRING""(BTRIM((((CASE WHEN (SS.FIRST_NAME NOTNULL) THEN SS.FIRST_NAME WHEN ('' NOTNULL) THEN ''::""VARCHAR"" ELSE NULL::""VARCHAR"" END || CASE WHEN ((SS.MIDDLE_NAME NOTNULL) AND (SS.MIDDLE_NAME <> 'NA'::""VARCHAR"")) THEN (' '::""VARCHAR"" || SS.MIDDLE_NAME) ELSE ''::""VARCHAR"" END) || ' '::""VARCHAR"") || CASE WHEN (SS.LAST_NAME NOTNULL) THEN SS.LAST_NAME WHEN ('' NOTNULL) THEN ''::""VARCHAR"" ELSE NULL::""VARCHAR"" END)), 1, 301) END AS SAR_SUSPECT_FULL_NAME, SS.FIRST_NAME AS SAR_SUSPECT_FIRST_NAME, SS.MIDDLE_NAME AS SAR_SUSPECT_MIDDLE_NAME, SS.LAST_NAME AS SAR_SUSPECT_LAST_NAME, SS.GENDER AS SAR_SUSPECT_GENDER, SS.OCCUPATION AS SAR_SUSPECT_OCCUPATION, SS.TIN AS SAR_SUSPECT_TIN, SS.TIN_TYPE_CODE AS SAR_SUSPECT_TIN_TYPE_CODE, TDOB.DOB AS SAR_SUSPECT_DATE_OF_BIRTH, CASE WHEN (Q1.NUMBER_OF_ADDRESSES NOTNULL) THEN Q1.NUMBER_OF_ADDRESSES WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS NUMBER_OF_ADDRESSES, CASE WHEN (Q1.NUMBER_OF_ADDRESSES > 1) THEN 'Multiple'::""VARCHAR"" ELSE SSA.STREET_ADDRESS END AS SAR_SUSPECT_STREET_ADDRESS, SSA.CITY AS SAR_SUSPECT_CITY, SSA.STATE_CODE AS SAR_SUSPECT_STATE_CODE, SSA.COUNTRY_CODE AS SAR_SUSPECT_COUNTRY_CODE, SSA.POSTAL_CODE AS SAR_SUSPECT_POSTAL_CODE, CASE WHEN (Q2.NUMBER_OF_IDENTIFICATIONS NOTNULL) THEN Q2.NUMBER_OF_IDENTIFICATIONS WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS NUMBER_OF_IDENTIFICATIONS, SSI.IDENTIFICATION_TYPE_CODE AS SAR_SUSPECT_IDENTIFICATION_TYPE_CODE, CASE WHEN (Q2.NUMBER_OF_IDENTIFICATIONS > 1) THEN 'Multiple'::""VARCHAR"" ELSE SSI.IDENTIFICATION_NUMBER END AS SAR_SUSPECT_IDENTIFICATION_NUMBER FROM ((((((AML_L3_REP.V2_SAR SA JOIN AML_L3_REP.V_SAR_SUSPECT SS ON (((SS.SAR_SOURCE_ID = SA.SAR_SOURCE_ID) AND (SS.SAR_SUSPECT_END_DATE = '9999-12-31'::DATE)))) LEFT JOIN TDOB ON (((TDOB.SAR_SOURCE_ID = SS.SAR_SOURCE_ID) AND (TDOB.SUBJECT_ID = SS.SUBJECT_ID)))) LEFT JOIN (SELECT AML_L3_REP.V_SAR_SUSPECT_ADDRESS.SUBJECT_ID, COUNT(*) AS NUMBER_OF_ADDRESSES FROM AML_L3_REP.V_SAR_SUSPECT_ADDRESS WHERE (AML_L3_REP.V_SAR_SUSPECT_ADDRESS.SAR_SUSPECT_ADDR_END_DATE = DATE('9999-12-31'::""VARCHAR"")) GROUP BY AML_L3_REP.V_SAR_SUSPECT_ADDRESS.SUBJECT_ID) Q1 ON ((Q1.SUBJECT_ID = SS.SUBJECT_ID))) LEFT JOIN AML_L3_REP.V_SAR_SUSPECT_ADDRESS SSA ON ((((Q1.NUMBER_OF_ADDRESSES = 1) AND (SSA.SUBJECT_ID = SS.SUBJECT_ID)) AND (SSA.SAR_SUSPECT_ADDR_END_DATE = DATE('9999-12-31'::""VARCHAR""))))) LEFT JOIN (SELECT AML_L3_REP.V_SAR_SUSPECT_IDENTIFICATION.SUBJECT_ID, COUNT(*) AS NUMBER_OF_IDENTIFICATIONS FROM AML_L3_REP.V_SAR_SUSPECT_IDENTIFICATION WHERE (AML_L3_REP.V_SAR_SUSPECT_IDENTIFICATION.SAR_SUSPECT_IDENT_END_DATE = DATE('9999-12-31'::""VARCHAR"")) GROUP BY AML_L3_REP.V_SAR_SUSPECT_IDENTIFICATION.SUBJECT_ID) Q2 ON ((Q2.SUBJECT_ID = SS.SUBJECT_ID))) LEFT JOIN AML_L3_REP.V_SAR_SUSPECT_IDENTIFICATION SSI ON ((((Q2.NUMBER_OF_IDENTIFICATIONS = 1) AND (SSI.SUBJECT_ID = SS.SUBJECT_ID)) AND (SSI.SAR_SUSPECT_IDENT_END_DATE = DATE('9999-12-31'::""VARCHAR""))))); SELECT FSL.SAR_SK, DS.STATUS_CODE, DS.STATUS_NAME, FSL.SAR_STATUS_START_TS AS STATUS_START_TS, FSL.SAR_STATUS_END_TS AS STATUS_END_TS, DATE_PART('EPOCH'::""VARCHAR"", (CASE WHEN (FSL.SAR_STATUS_END_TS = '9999-12-31 23:59:59'::""TIMESTAMP"") THEN NOW() ELSE FSL.SAR_STATUS_END_TS END - FSL.SAR_STATUS_START_TS)) AS STATUS_DURATION_IN_SECONDS, (""NUMERIC""(DATE_PART('EPOCH'::""VARCHAR"", (CASE WHEN (FSL.SAR_STATUS_END_TS = '9999-12-31 23:59:59'::""TIMESTAMP"") THEN NOW() ELSE FSL.SAR_STATUS_END_TS END - FSL.SAR_STATUS_START_TS))) / '86400'::""NUMERIC"") AS STATUS_DURATION_IN_DAYS FROM (AML_L3_REP.V_FACT_SAR_LIFETIME FSL JOIN AML_L3_REP.V_DIM_STATUS DS ON ((DS.STATUS_SK = FSL.STATUS_SK))); SELECT SI.SAR_SOURCE_ID, IP.IP_ADDRESS FROM (AML_L3_REP.V_SAR_IP_ADDRESS IP JOIN AML_L3_REP.V_SAR_INFO SI ON ((IP.SUSPICIOUS_ACTIVITY_KEY = SI.SUSPICIOUS_ACTIVITY_KEY))) WHERE ((IP.SAR_IP_END_DATE = '9999-12-31'::DATE) AND (SI.SAR_INFO_END_DATE = '9999-12-31'::DATE)); SELECT SI.SAR_SOURCE_ID, MA.MARKET_CODE, MA.USER_FIELD FROM (AML_L3_REP.V_SAR_MARKET MA JOIN AML_L3_REP.V_SAR_INFO SI ON ((MA.SUSPICIOUS_ACTIVITY_KEY = SI.SUSPICIOUS_ACTIVITY_KEY))) WHERE ((MA.SAR_MARKETS_END_DATE = '9999-12-31'::DATE) AND (SI.SAR_INFO_END_DATE = '9999-12-31'::DATE)); SELECT SI.SAR_SOURCE_ID, PR.PRODUCT_DESCRIPTION, PR.USER_FIELD FROM (AML_L3_REP.V_SAR_PRODUCT PR JOIN AML_L3_REP.V_SAR_INFO SI ON ((PR.SUSPICIOUS_ACTIVITY_KEY = SI.SUSPICIOUS_ACTIVITY_KEY))) WHERE ((PR.SAR_PRODUCT_END_DATE = '9999-12-31'::DATE) AND (SI.SAR_INFO_END_DATE = '9999-12-31'::DATE)); SELECT FSL.SAR_SK, DS.STATUS_CODE, DS.STATUS_NAME, FSL.SAR_STATUS_START_TS AS STATUS_START_TS, FSL.SAR_STATUS_END_TS AS STATUS_END_TS, DATE_PART('EPOCH'::""VARCHAR"", (CASE WHEN (FSL.SAR_STATUS_END_TS = '9999-12-31 23:59:59'::""TIMESTAMP"") THEN NOW() ELSE FSL.SAR_STATUS_END_TS END - FSL.SAR_STATUS_START_TS)) AS STATUS_DURATION_IN_SECONDS, (""NUMERIC""(DATE_PART('EPOCH'::""VARCHAR"", (CASE WHEN (FSL.SAR_STATUS_END_TS = '9999-12-31 23:59:59'::""TIMESTAMP"") THEN NOW() ELSE FSL.SAR_STATUS_END_TS END - FSL.SAR_STATUS_START_TS))) / '86400'::""NUMERIC"") AS STATUS_DURATION_IN_DAYS FROM (AML_L3_REP.V_FACT_SAR_LIFETIME FSL JOIN AML_L3_REP.V_DIM_STATUS DS ON ((DS.STATUS_SK = FSL.STATUS_SK))); SELECT FI.SAR_SOURCE_ID, FI.FIN_INS_TYPE_CODE AS FINANCIAL_INSTITUTION_TYPE_CODE, FI.FIN_INST_OTHER_DESC AS FINANCIAL_INSTITUTION_TYPE_OTHER_DESCRIPTION, FI.PFR_CODE AS PRIMARY_FEDERAL_REGULATOR_CODE, FI.FIN_INST_IDENT_TYPE_CODE AS IDENTIFIER_TYPE_CODE, FI.FIN_INST_IDENT_NUMBER AS IDENTIFICATION_NUMBER, FI.DISC_FIN_INST_KEY, FI.LEGAL_NAME AS FINANCIAL_INSTITUTION_LEGAL_NAME, FI.ALTERNATE_NAME AS FINANCIAL_INSTITUTION_ALTERNATE_NAME, FI.STREET_ADDRESS AS FINANCIAL_INSTITUTION_STREET_ADDRESS, FI.CITY AS FINANCIAL_INSTITUTION_CITY, FI.STATE_CODE AS FINANCIAL_INSTITUTION_STATE_CODE, FI.POSTAL_CODE AS FINANCIAL_INSTITUTION_POSTAL_CODE, FI.COUNTRY_CODE AS FINANCIAL_INSTITUTION_COUNTRY_CODE, FI.FILE_NUMBER AS INTERNAL_FILE_NUMBER, FI.LOSS_TO_FIN_INST AS LOSS_TO_FINANCIAL_INSTITUTION, CASE WHEN (FIB.ROLE_IN_TXN = 'A'::BPCHAR) THEN 'Selling Location'::""VARCHAR"" WHEN (FIB.ROLE_IN_TXN = 'B'::BPCHAR) THEN 'Paying Location'::""VARCHAR"" WHEN (FIB.ROLE_IN_TXN = 'C'::BPCHAR) THEN 'Both'::""VARCHAR"" ELSE NULL::""VARCHAR"" END AS FINANCIAL_INSTITUTION_ROLE_IN_TRANSACTION, CASE WHEN (FIB.ROLE_IN_TXN = 'A'::BPCHAR) THEN 'Selling Location'::""VARCHAR"" WHEN (FIB.ROLE_IN_TXN = 'B'::BPCHAR) THEN 'Paying Location'::""VARCHAR"" WHEN (FIB.ROLE_IN_TXN = 'C'::BPCHAR) THEN 'Both'::""VARCHAR"" ELSE NULL::""VARCHAR"" END AS BRANCH_ROLE_IN_TRANSACTION, FIB.STREET_ADDRESS AS BRANCH_STREET_ADDRESS, FIB.CITY AS BRANCH_CITY, FIB.STATE_CODE AS BRANCH_STATE_CODE, FIB.COUNTRY_CODE AS BRANCH_COUNTRY_CODE, FIB.POSTAL_CODE AS BRANCH_POSTAL_CODE, FIB.RSSD_NUMBER AS BRANCH_RSSD_NUMBER FROM (AML_L3_REP.V_SAR_FINANCIAL_INSTITUTION FI LEFT JOIN AML_L3_REP.V_SAR_FINANCIAL_INSTITUTION_BRANCH FIB ON (((FI.DISC_FIN_INST_KEY = FIB.DISC_FIN_INST_KEY) AND (FIB.SAR_FI_BRANCH_END_DATE = '9999-12-31'::DATE)))) WHERE ((FI.SAR_SOURCE_ID <> -1) AND (FI.SAR_FI_END_DATE = '9999-12-31'::DATE)); (SELECT SI.SAR_SOURCE_ID, 30 AS SAR_SECTION_NUMBER, ('Terrorist Financing'::""VARCHAR"")::VARCHAR(28) AS SAR_SECTION_NAME, CASE WHEN (SI.TERRORIST_FIN_SA_TYPE_KNOWN = 'Y'::BPCHAR) THEN ('A'::""VARCHAR"")::VARCHAR(10) ELSE ('Z'::""VARCHAR"")::VARCHAR(10) END AS CODE, CASE WHEN (SI.TERRORIST_FIN_SA_TYPE_KNOWN = 'Y'::BPCHAR) THEN ('Known or suspected terrrorist / terrorist organization'::""VARCHAR"")::VARCHAR(100) ELSE ('Other'::""VARCHAR"")::VARCHAR(100) END AS ""DESCRIPTION"", CASE WHEN (SI.TERRORIST_FIN_SA_TYPE_OTHER = 'Y'::BPCHAR) THEN SI.TERRORIST_FIN_OTHER_DESC ELSE NULL::""VARCHAR"" END AS OTHER_DESCRIPTION FROM AML_L3_REP.V_SAR_INFO SI WHERE (((SI.TERRORIST_FIN_SA_TYPE_KNOWN = 'Y'::BPCHAR) OR (SI.TERRORIST_FIN_SA_TYPE_OTHER = 'Y'::BPCHAR)) AND (SI.SAR_INFO_END_DATE = '9999-12-31'::DATE))) UNION ALL (SELECT SFO.SAR_SOURCE_ID, CASE WHEN (SFO.""TYPE"" = 'STRUCTURING_SA_TYPES'::""VARCHAR"") THEN 29 WHEN (SFO.""TYPE"" = 'FRAUD_SA_TYPES'::""VARCHAR"") THEN 31 WHEN (SFO.""TYPE"" = 'CASINOS_SA_TYPES'::""VARCHAR"") THEN 32 WHEN (SFO.""TYPE"" = 'MONEY_LAUND_SA_TYPES'::""VARCHAR"") THEN 33 WHEN (SFO.""TYPE"" = 'IDENTIFICATION_SA_TYPES'::""VARCHAR"") THEN 34 WHEN (SFO.""TYPE"" = 'OTHER_SA_TYPES'::""VARCHAR"") THEN 35 WHEN (SFO.""TYPE"" = 'INSURANCE_SA_TYPES'::""VARCHAR"") THEN 36 WHEN (SFO.""TYPE"" = 'SFO_SA_TYPES'::""VARCHAR"") THEN 37 WHEN (SFO.""TYPE"" = 'MORTGAGE_FRAUD_TYPES'::""VARCHAR"") THEN 38 WHEN (SFO.""TYPE"" = 'PRODUCT_INVOLVEMENT_TYPES'::""VARCHAR"") THEN 39 WHEN (SFO.""TYPE"" = 'INSTRUMENT_TYPES'::""VARCHAR"") THEN 40 ELSE NULL::INT4 END AS SAR_SECTION_NUMBER, CASE WHEN (SFO.""TYPE"" = 'STRUCTURING_SA_TYPES'::""VARCHAR"") THEN 'Structuring'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'FRAUD_SA_TYPES'::""VARCHAR"") THEN 'Fraud Type'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'CASINOS_SA_TYPES'::""VARCHAR"") THEN 'Casinos'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'MONEY_LAUND_SA_TYPES'::""VARCHAR"") THEN 'Money Laundering'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'IDENTIFICATION_SA_TYPES'::""VARCHAR"") THEN 'Identification/Documentation'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'OTHER_SA_TYPES'::""VARCHAR"") THEN 'Other Suspicious Activities'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'INSURANCE_SA_TYPES'::""VARCHAR"") THEN 'Insurance'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'SFO_SA_TYPES'::""VARCHAR"") THEN 'Securities/Futures/Options'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'MORTGAGE_FRAUD_TYPES'::""VARCHAR"") THEN 'Mortgage Fraud'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'PRODUCT_INVOLVEMENT_TYPES'::""VARCHAR"") THEN 'Product Types'::""VARCHAR"" WHEN (SFO.""TYPE"" = 'INSTRUMENT_TYPES'::""VARCHAR"") THEN 'Instrument Types'::""VARCHAR"" ELSE NULL::""VARCHAR"" END AS SAR_SECTION_NAME, SFO.CODE, SFO.""DESCRIPTION"", CASE WHEN ((SFO.""TYPE"" = 'STRUCTURING_SA_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.STRUCTURING_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'FRAUD_SA_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.FRAUD_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'CASINOS_SA_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.CASINOS_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'MONEY_LAUND_SA_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.ML_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'IDENTIFICATION_SA_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.IDENTIFICATION_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'OTHER_SA_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.OTHER_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'INSURANCE_SA_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.INSURANCE_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'SFO_SA_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.SFO_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'MORTGAGE_FRAUD_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.MTG_FRAUD_SA_OTHER_DESC WHEN ((SFO.""TYPE"" = 'PRODUCT_INVOLVEMENT_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.PRODUCT_TYPES_INV_OTHER_DESC WHEN ((SFO.""TYPE"" = 'INSTRUMENT_TYPES'::""VARCHAR"") AND (SFO.CODE = 'Z'::""VARCHAR"")) THEN SI.INSTR_TYPES_INV_OTHER_DESC ELSE NULL::""VARCHAR"" END AS OTHER_DESCRIPTION FROM (AML_L3_REP.V_SAR_FILING_OPTIONS SFO JOIN AML_L3_REP.V_SAR_INFO SI ON ((SFO.SAR_SOURCE_ID = SI.SAR_SOURCE_ID))) WHERE (((((((SFO.""TYPE"" = 'STRUCTURING_SA_TYPES'::""VARCHAR"") OR (SFO.""TYPE"" = 'FRAUD_SA_TYPES'::""VARCHAR"")) OR ((SFO.""TYPE"" = 'CASINOS_SA_TYPES'::""VARCHAR"") OR (SFO.""TYPE"" = 'MONEY_LAUND_SA_TYPES'::""VARCHAR""))) OR (((SFO.""TYPE"" = 'IDENTIFICATION_SA_TYPES'::""VARCHAR"") OR (SFO.""TYPE"" = 'OTHER_SA_TYPES'::""VARCHAR"")) OR ((SFO.""TYPE"" = 'INSURANCE_SA_TYPES'::""VARCHAR"") OR (SFO.""TYPE"" = 'SFO_SA_TYPES'::""VARCHAR"")))) OR (((SFO.""TYPE"" = 'MORTGAGE_FRAUD_TYPES'::""VARCHAR"") OR (SFO.""TYPE"" = 'PRODUCT_INVOLVEMENT_TYPES'::""VARCHAR"")) OR (SFO.""TYPE"" = 'INSTRUMENT_TYPES'::""VARCHAR""))) AND (SFO.SAR_OPT_END_DATE = '9999-12-31'::DATE)) AND (SI.SAR_INFO_END_DATE = '9999-12-31'::DATE))); SELECT AML_L3_REP.V_SAR_FILING_INSTITUTION.SAR_SOURCE_ID, AML_L3_REP.V_SAR_FILING_INSTITUTION.FIN_INS_TYPE_CODE AS FILING_INSTITUTION_TYPE_CODE, AML_L3_REP.V_SAR_FILING_INSTITUTION.FIN_INS_OTHER_DESC AS FILING_INSTITUTION_TYPE_OTHER_DESCRIPTION, AML_L3_REP.V_SAR_FILING_INSTITUTION.PFR_CODE AS PRIMARY_FEDERAL_REGULATOR_CODE, AML_L3_REP.V_SAR_FILING_INSTITUTION.FILER_NAME, AML_L3_REP.V_SAR_FILING_INSTITUTION.TIN_TYPE_CODE, AML_L3_REP.V_SAR_FILING_INSTITUTION.TIN, AML_L3_REP.V_SAR_FILING_INSTITUTION.FIN_INST_IDENT_TYPE_CODE AS IDENTIFICATION_TYPE_CODE, AML_L3_REP.V_SAR_FILING_INSTITUTION.FIN_INST_IDENT_NUMBER AS IDENTIFICATION_NUMBER, AML_L3_REP.V_SAR_FILING_INSTITUTION.STREET_ADDRESS AS FILING_INSTITUTION_STREET_ADDRESS, AML_L3_REP.V_SAR_FILING_INSTITUTION.CITY AS FILING_INSTITUTION_CITY, AML_L3_REP.V_SAR_FILING_INSTITUTION.STATE_CODE AS FILING_INSTITUTION_STATE_CODE, AML_L3_REP.V_SAR_FILING_INSTITUTION.POSTAL_CODE AS FILING_INSTITUTION_POSTAL_CODE, AML_L3_REP.V_SAR_FILING_INSTITUTION.COUNTRY_CODE AS FILING_INSTITUTION_COUNTRY_CODE, AML_L3_REP.V_SAR_FILING_INSTITUTION.ALTERNATE_NAME AS FILING_INSTITUTION_ALTERNATE_NAME FROM AML_L3_REP.V_SAR_FILING_INSTITUTION; SELECT SFI.SAR_SOURCE_ID, SFO.CODE, SFO.""DESCRIPTION"", CASE WHEN (SFO.CODE = 'Z'::""VARCHAR"") THEN SFI.SFI_OTHER_DESC ELSE NULL::""VARCHAR"" END AS OTHER_DESCRIPTION FROM (AML_L3_REP.V_SAR_FILING_OPTIONS SFO JOIN AML_L3_REP.V_SAR_FILING_INSTITUTION SFI ON ((SFO.SAR_SOURCE_ID = SFI.SAR_SOURCE_ID))) WHERE (((SFO.""TYPE"" = 'FIL_SEC_FUT_INS_TYPE'::""VARCHAR"") AND (SFO.SAR_OPT_END_DATE = '9999-12-31'::DATE)) AND (SFI.SAR_FIL_INST_END_DATE = '9999-12-31'::DATE)); SELECT SI.SAR_SOURCE_ID, COM.""TYPE"" AS COMMODITY_TYPE FROM (AML_L3_REP.V_SAR_COMMODITY COM JOIN AML_L3_REP.V_SAR_INFO SI ON ((COM.SUSPICIOUS_ACTIVITY_KEY = SI.SUSPICIOUS_ACTIVITY_KEY))) WHERE ((SI.SAR_INFO_END_DATE = '9999-12-31'::DATE) AND (COM.SAR_COMMODITY_END_DATE = '9999-12-31'::DATE)); SELECT SI.SAR_SOURCE_ID, CU.CUSIP_NUMBER, CU.USER_FIELD FROM (AML_L3_REP.V_SAR_CUSIP CU JOIN AML_L3_REP.V_SAR_INFO SI ON ((CU.SUSPICIOUS_ACTIVITY_KEY = SI.SUSPICIOUS_ACTIVITY_KEY))) WHERE ((SI.SAR_INFO_END_DATE = '9999-12-31'::DATE) AND (CU.SAR_CUSIP_END_DATE = '9999-12-31'::DATE)); SELECT FCL.CASE_SK, DC.CASE_SOURCE_ID AS CASE_NUMBER, DS.STATUS_CODE, DS.STATUS_NAME, FCL.CASE_STATUS_START_TS AS STATUS_START_TS, FCL.CASE_STATUS_END_TS AS STATUS_END_TS, DATE_PART('EPOCH'::""VARCHAR"", (CASE WHEN (FCL.CASE_STATUS_END_TS = '9999-12-31 23:59:59'::""TIMESTAMP"") THEN NOW() ELSE FCL.CASE_STATUS_END_TS END - FCL.CASE_STATUS_START_TS)) AS STATUS_DURATION_IN_SECONDS, (""NUMERIC""(DATE_PART('EPOCH'::""VARCHAR"", (CASE WHEN (FCL.CASE_STATUS_END_TS = '9999-12-31 23:59:59'::""TIMESTAMP"") THEN NOW() ELSE FCL.CASE_STATUS_END_TS END - FCL.CASE_STATUS_START_TS))) / '86400'::""NUMERIC"") AS STATUS_DURATION_IN_DAYS FROM ((AML_L3_REP.V_FACT_CASE_LIFETIME FCL JOIN AML_L3_REP.V_DIM_STATUS DS ON ((DS.STATUS_SK = FCL.STATUS_SK))) JOIN AML_L3_REP.V_DIM_CASE DC ON (((FCL.CASE_SK = DC.CASE_SK) AND (DC.CASE_END_TS = '9999-12-31 23:59:59'::""TIMESTAMP"")))); SELECT CA.CASE_SK, CA.CASE_NUMBER, ROW_NUMBER() OVER (PARTITION BY CA.CASE_SK ORDER BY CU.CUSTOMER_LAST_NAME, CU.CUSTOMER_FIRST_NAME, CU.CUSTOMER_MIDDLE_NAME_INIT ) AS SUSPECT_SEQUENCE, CASE WHEN (CU.CUSTOMER_COMPANY_NAME > ''::""VARCHAR"") THEN (""SUBSTRING""(CU.CUSTOMER_COMPANY_NAME, 1, 251))::VARCHAR(251) WHEN (((CU.CUSTOMER_FIRST_NAME = 'NA'::""VARCHAR"") AND (CU.CUSTOMER_MIDDLE_NAME_INIT = 'NA'::""VARCHAR"")) AND (CU.CUSTOMER_LAST_NAME = 'NA'::""VARCHAR"")) THEN 'NA'::""VARCHAR"" ELSE BTRIM(((CU.CUSTOMER_FIRST_NAME || ' '::""VARCHAR"") || CU.CUSTOMER_LAST_NAME)) END AS CASE_SUSPECT_FULL_NAME, CU.CUSTOMER_FIRST_NAME AS CASE_SUSPECT_FIRST_NAME, CU.CUSTOMER_MIDDLE_NAME_INIT AS CASE_SUSPECT_MIDDLE_NAME, CU.CUSTOMER_LAST_NAME AS CASE_SUSPECT_LAST_NAME, CU.CUSTOMER_DATE_OF_BIRTH AS CASE_SUSPECT_DATE_OF_BIRTH, CU.CUSTOMER_GENDER_CODE AS CASE_SUSPECT_GENDER_CODE, CU.CUSTOMER_SOURCE_ID FROM ((AML_L3_REP.V2_CASE CA JOIN AML_L3_REP.V_BRIDGE_CUSTOMER_GROUP BCG ON (((BCG.CASE_SK = CA.CASE_SK) AND (BCG.CUSTOMER_GROUP_END_DATE = '9999-12-31'::DATE)))) JOIN AML_L3_REP.V_DIM_CUSTOMER CU ON (((CU.CUSTOMER_SK = BCG.CUSTOMER_SK) AND (CU.CUSTOMER_END_TS = '9999-12-31 23:59:59'::""TIMESTAMP"")))); SELECT AAL.REPORT_START_DATE, AAL.REPORT_END_DATE, AAL.ALERT_OWNER_NAME, AAL.ALERT_OWNER_FIRST_NAME, AAL.ALERT_OWNER_LAST_NAME, AAL.ALERT_OWNER_USER_SK, SUM(AAL.ALERT_INDICATOR) AS TOTAL_ALERTS, SUM(AAL.ALERT_WORKED_TO_CASE_INDICATOR) AS ALERTS_WORKED_TO_CASE, SUM(AAL.ALERT_WORKED_TO_NON_CASE_INDICATOR) AS ALERTS_WORKED_TO_NON_CASE, CASE WHEN (SUM(AAL.ALERT_WORKED_TO_CASE_INDICATOR) = '0'::""NUMERIC"") THEN NULL::""NUMERIC"" ELSE (SUM(AAL.ALERT_INDICATOR) / SUM(AAL.ALERT_WORKED_TO_CASE_INDICATOR)) END AS ALERT_TO_CASE_RATIO, CASE WHEN (MIN(UL.TOTAL_LOGIN_TIME) NOTNULL) THEN MIN(UL.TOTAL_LOGIN_TIME) WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS TOTAL_LOGIN_TIME FROM (AML_L3_REP.ALERT_ADJUDICATION_LEVEL_2_VIEW AAL LEFT JOIN AML_L3_REP.V2_USER_LOGIN_FOR_PERIOD UL ON ((((UL.USER_SK = AAL.ALERT_OWNER_USER_SK) AND (UL.REPORT_START_DATE = AAL.REPORT_START_DATE)) AND (UL.REPORT_END_DATE = AAL.REPORT_END_DATE)))) GROUP BY AAL.REPORT_START_DATE, AAL.REPORT_END_DATE, AAL.ALERT_OWNER_NAME, AAL.ALERT_OWNER_FIRST_NAME, AAL.ALERT_OWNER_LAST_NAME, AAL.ALERT_OWNER_USER_SK; SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, AL.ALERT_SK, AL.ALERT_SOURCE_ID, AL.ALERT_SOURCE, AL.ALERT_REASON_CODE, AL.ALERT_REASON_NAME, ASS1.DURATION_IN_DAYS AS TIME_IN_TRIAGE, AL.ALERT_CURRENT_STATUS, (DATE_PART('EPOCH'::""VARCHAR"", (""TIMESTAMP""(DATE('now(0)'::""VARCHAR"")) - AL.ALERT_CURRENT_STATUS_START_TS)) / 86400) AS AGE_OF_LAST_STATUS_CHANGE, AL.ALERT_CREATION_DATE, (RD.REPORT_END_DATE - AL.ALERT_CREATION_DATE) AS ALERT_AGE, AL.ALERT_SCENARIO_ID AS SCENARIO_SOURCE_ID, AL.ALERT_HRU_INDICATOR, AL.ALERT_CREATOR_NAME, AL.ALERT_CREATOR_FIRST_NAME, AL.ALERT_CREATOR_LAST_NAME, AL.ALERT_CREATOR_USER_SK, AL.ALERT_OWNER_USER_SK, AL.ALERT_OWNER_NAME, AL.ALERT_OWNER_FIRST_NAME, AL.ALERT_OWNER_LAST_NAME, 1 AS ALERT_INDICATOR, AC.ALERT_WORKED_TO_NON_CASE_INDICATOR, AC.ALERT_WORKED_TO_CASE_INDICATOR FROM (((AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V2_ALERT_COMPLETION AC ON (((AC.ALERT_WORKED_TO_COMPLETION_TS >= RD.REPORT_START_TS) AND (AC.ALERT_WORKED_TO_COMPLETION_TS <= RD.REPORT_END_TS)))) JOIN AML_L3_REP.V2_ALERT AL ON ((AL.ALERT_SK = AC.ALERT_SK))) JOIN AML_L3_REP.V2_ALERT_STATUS_SUMMARY ASS1 ON (((ASS1.ALERT_SK = AL.ALERT_SK) AND (ASS1.STATUS_NAME = 'Triage'::""VARCHAR"")))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, AL.ALERT_SOURCE_ID, AL.ALERT_SOURCE, AL.ALERT_REASON_NAME, AST.STATUS_NAME AS ALERT_STATUS_AT_END_DATE, (DATE_PART('EPOCH'::""VARCHAR"", (""TIMESTAMP""(RD.REPORT_END_DATE) - AL.ALERT_CURRENT_STATUS_START_TS)) / 86400) AS ALERT_STATUS_AGE_TO_END_DATE, AST2.STATUS_NAME AS ALERT_STATUS_CURRENT, (DATE_PART('EPOCH'::""VARCHAR"", (""TIMESTAMP""(DATE('now(0)'::""VARCHAR"")) - AL.ALERT_CURRENT_STATUS_START_TS)) / 86400) AS ALERT_STATUS_AGE_CURRENT, AL.ALERT_CREATION_DATE, (RD.REPORT_END_DATE - AL.ALERT_CREATION_DATE) AS ALERT_AGE_TO_END_DATE, (DATE('now(0)'::""VARCHAR"") - AL.ALERT_CREATION_DATE) AS ALERT_AGE_CURRENT, AL.ALERT_SCENARIO_ID AS SCENARIO_SOURCE_ID, AL.ALERT_HRU_INDICATOR, AL.ALERT_CREATOR_NAME, AL.ALERT_CREATOR_FIRST_NAME, AL.ALERT_CREATOR_LAST_NAME, AL.ALERT_OWNER_NAME, AL.ALERT_OWNER_FIRST_NAME, AL.ALERT_OWNER_LAST_NAME, 1 AS ALERT_IND, CASE WHEN (AL.ALERT_SOURCE = 'AUTOMATIC'::""VARCHAR"") THEN 1 ELSE 0 END AS AUTOMATIC_ALERT_IND, CASE WHEN (AL.ALERT_SOURCE = 'MANUAL'::""VARCHAR"") THEN 1 ELSE 0 END AS MANUAL_ALERT_IND, CASE WHEN (((AST.STATUS_NAME = 'New'::""VARCHAR"") OR (AST.STATUS_NAME = 'Triage'::""VARCHAR"")) OR (AST.STATUS_NAME = 'Assigned and Ready'::""VARCHAR"")) THEN 1 ELSE 0 END AS ALERT_TO_BE_ADJUDICATED_IND, CASE WHEN (((AST.STATUS_NAME = 'Linked Closed'::""VARCHAR"") OR (AST.STATUS_NAME = 'Linked'::""VARCHAR"")) OR ((AST.STATUS_NAME = 'Closed Non-Suspicious'::""VARCHAR"") OR (AST.STATUS_NAME = 'Closed Suspicious'::""VARCHAR""))) THEN 1 ELSE 0 END AS ALERT_COMPLETED_IND, AL.ALERT_SK FROM (((AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V2_ALERT AL ON (((AL.ALERT_CREATION_DATE >= RD.REPORT_START_DATE) AND (AL.ALERT_CREATION_DATE <= RD.REPORT_END_DATE)))) JOIN AML_L3_REP.V2_ALERT_STATUS AST ON ((((AST.ALERT_SK = AL.ALERT_SK) AND (AST.STATUS_START_TS <= RD.REPORT_END_TS)) AND (AST.STATUS_END_TS >= RD.REPORT_END_TS)))) JOIN AML_L3_REP.V2_ALERT_STATUS AST2 ON (((AST2.ALERT_SK = AL.ALERT_SK) AND (AST2.STATUS_END_DATE = DATE('9999-12-31'::""VARCHAR""))))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, AL.ALERT_SOURCE_ID, AL.ALERT_SOURCE, AL.ALERT_CURRENT_STATUS, AL.ALERT_CREATION_DATE, AL.ALERT_CURRENT_AGE AS ALERT_AGE_CURRENT, (RD.REPORT_END_DATE - AL.ALERT_CREATION_DATE) AS ALERT_AGE_TO_END_DATE, AL.ALERT_SCENARIO_ID AS SCENARIO_SOURCE_ID, AL.ALERT_HRU_INDICATOR, AL.ALERT_CREATOR_NAME, AL.ALERT_OWNER_NAME, CA.INVESTIGATOR_FULL_NAME AS CASE_INVESTIGATOR_NAME, CSF.CASE_NUMBER_OF_SUSPECTS, CSF.CASE_SUSPECT_FULL_NAME, AL.ALERT_SK FROM (((AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V2_ALERT AL ON (((AL.ALERT_CREATION_DATE >= RD.REPORT_START_DATE) AND (AL.ALERT_CREATION_DATE <= RD.REPORT_END_DATE)))) LEFT JOIN AML_L3_REP.V2_CASE CA ON ((AL.CASE_SK = CA.CASE_SK))) LEFT JOIN AML_L3_REP.V2_CASE_SUSPECT_FLATTENED CSF ON ((CSF.CASE_SK = CA.CASE_SK))); SELECT CAL.REPORT_START_DATE, CAL.REPORT_END_DATE, CAL.INVESTIGATOR_FULL_NAME, CAL.INVESTIGATOR_FIRST_NAME, CAL.INVESTIGATOR_LAST_NAME, CAL.INVESTIGATOR_USER_SK, COUNT(*) AS TOTAL_CASES, SUM(CAL.WORKED_TO_SAR_INDICATOR) AS CASES_WORKED_TO_SAR, SUM(CAL.WORKED_TO_NON_SAR_INDICATOR) AS CASES_WORKED_TO_NON_SAR, CASE WHEN (SUM(CAL.WORKED_TO_SAR_INDICATOR) = '0'::""NUMERIC"") THEN NULL::""NUMERIC"" ELSE (""NUMERIC""(COUNT(*)) / SUM(CAL.WORKED_TO_SAR_INDICATOR)) END AS CASE_TO_SAR_RATIO, CASE WHEN (MIN(UL.TOTAL_LOGIN_TIME) NOTNULL) THEN MIN(UL.TOTAL_LOGIN_TIME) WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS TOTAL_LOGIN_TIME FROM (AML_L3_REP.CASE_ADJUDICATION_LEVEL_2_VIEW CAL LEFT JOIN AML_L3_REP.V2_USER_LOGIN_FOR_PERIOD UL ON ((((UL.USER_SK = CAL.INVESTIGATOR_USER_SK) AND (UL.REPORT_START_DATE = CAL.REPORT_START_DATE)) AND (UL.REPORT_END_DATE = CAL.REPORT_END_DATE)))) GROUP BY CAL.REPORT_START_DATE, CAL.REPORT_END_DATE, CAL.INVESTIGATOR_FULL_NAME, CAL.INVESTIGATOR_FIRST_NAME, CAL.INVESTIGATOR_LAST_NAME, CAL.INVESTIGATOR_USER_SK; SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, CA.CASE_NUMBER, CA.INVESTIGATOR_FULL_NAME, CA.INVESTIGATOR_FIRST_NAME, CA.INVESTIGATOR_LAST_NAME, CA.INVESTIGATOR_USER_SK, CASE WHEN (CSS1.DURATION_IN_DAYS NOTNULL) THEN CSS1.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS TIME_UNDER_INV, CASE WHEN (CSS2.DURATION_IN_DAYS NOTNULL) THEN CSS2.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS TIME_AWAITING_INFO, CASE WHEN (CSS3.DURATION_IN_DAYS NOTNULL) THEN CSS3.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS TIME_RECOM_SAR, CASE WHEN (CSS4.DURATION_IN_DAYS NOTNULL) THEN CSS4.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS TIME_SAR_REV, CA.CASE_CATEGORY, CA.CASE_SUB_CATEGORY, CA.CASE_AGE, CA.CASE_CREATION_DATE, CC.CASE_WORKED_TO_CLOSED_STATUS_INDICATOR AS WORKED_TO_NON_SAR_INDICATOR, CASE WHEN ((CC.CASE_WORKED_TO_COMPLETION_STATUS = 'Reported'::""VARCHAR"") OR (CC.CASE_WORKED_TO_COMPLETION_STATUS = 'Reported/Closed'::""VARCHAR"")) THEN 1 ELSE 0 END AS WORKED_TO_SAR_INDICATOR, CA.CASE_SK FROM ((((((AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V2_CASE_COMPLETION CC ON (((CC.CASE_WORKED_TO_COMPLETION_TS >= RD.REPORT_START_TS) AND (CC.CASE_WORKED_TO_COMPLETION_TS <= RD.REPORT_END_TS)))) JOIN AML_L3_REP.V2_CASE CA ON ((CA.CASE_SK = CC.CASE_SK))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS1 ON (((CSS1.CASE_SK = CA.CASE_SK) AND (CSS1.STATUS_NAME = 'Under Investigation'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS2 ON (((CSS2.CASE_SK = CA.CASE_SK) AND (CSS2.STATUS_NAME = 'Awaiting Information'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS3 ON (((CSS3.CASE_SK = CA.CASE_SK) AND (CSS3.STATUS_NAME = 'SAR Recommended'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS4 ON (((CSS4.CASE_SK = CA.CASE_SK) AND (CSS4.STATUS_NAME = 'SAR Review'::""VARCHAR"")))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, CA.CASE_NUMBER, CASE WHEN (CSS1.DURATION_IN_DAYS NOTNULL) THEN CSS1.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS NEW_STATUS_TIME, CASE WHEN (CSS2.DURATION_IN_DAYS NOTNULL) THEN CSS2.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS UNDER_INV_STATUS_TIME, CASE WHEN (CSS3.DURATION_IN_DAYS NOTNULL) THEN CSS3.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS AWAIT_STATUS_TIME, CASE WHEN (CSS4.DURATION_IN_DAYS NOTNULL) THEN CSS4.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS RECOM_STATUS_TIME, CASE WHEN (CSS5.DURATION_IN_DAYS NOTNULL) THEN CSS5.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS REVIEW_STATUS_TIME, CASE WHEN (CSS6.DURATION_IN_DAYS NOTNULL) THEN CSS6.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS REP_STATUS_TIME, CASE WHEN (CSS7.DURATION_IN_DAYS NOTNULL) THEN CSS7.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS REP_CLOSED_STATUS_TIME, CASE WHEN (CSS8.DURATION_IN_DAYS NOTNULL) THEN CSS8.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS CLOSED_STATUS_TIME, CASE WHEN (CSS9.DURATION_IN_DAYS NOTNULL) THEN CSS9.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS QA_STATUS_TIME, CA.INVESTIGATOR_FULL_NAME AS INVESTIGATOR_NAME, CA.INVESTIGATOR_FIRST_NAME, CA.INVESTIGATOR_LAST_NAME, CA.INVESTIGATOR_USER_SK, CA.CASE_HRU_INDICATOR, CASE WHEN ((SSS2.MOST_RECENT_DATE NOTNULL) AND (CA.CASE_90_DAY_REVIEW_REMOVAL = 'N'::""VARCHAR"")) THEN 'Y'::""VARCHAR"" ELSE 'N'::""VARCHAR"" END AS CASE_REQUIRING_90_DAY_REVIEW, CASE WHEN (CSF.CASE_NUMBER_OF_SUSPECTS NOTNULL) THEN CSF.CASE_NUMBER_OF_SUSPECTS WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS CASE_NUMBER_OF_SUSPECTS, CSF.CASE_SUSPECT_FULL_NAME, CA.CASE_CREATION_DATE, CA.CASE_CATEGORY, CA.CASE_SUB_CATEGORY, SSS2.MOST_RECENT_DATE AS DISCLOSURE_FILED_DATE, (SSS2.MOST_RECENT_DATE + 90) AS FOLLOWUP_REVIEW_DATE, CA.CASE_CURRENT_STATUS, CS.STATUS_NAME AS CASE_STATUS_AT_REPORT_END_DATE, CA.CASE_SK FROM (((((((((((((((AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V2_CASE CA ON (((CA.CASE_CREATION_DATE >= RD.REPORT_START_DATE) AND (CA.CASE_CREATION_DATE <= RD.REPORT_END_DATE)))) LEFT JOIN AML_L3_REP.V2_CASE_SUSPECT_FLATTENED CSF ON ((CA.CASE_SK = CSF.CASE_SK))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS1 ON (((CA.CASE_SK = CSS1.CASE_SK) AND (CSS1.STATUS_NAME = 'New'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS2 ON (((CA.CASE_SK = CSS2.CASE_SK) AND (CSS2.STATUS_NAME = 'Under Investigation'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS3 ON (((CA.CASE_SK = CSS3.CASE_SK) AND (CSS3.STATUS_NAME = 'Awaiting Information'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS4 ON (((CA.CASE_SK = CSS4.CASE_SK) AND (CSS4.STATUS_NAME = 'SAR Recommended'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS5 ON (((CA.CASE_SK = CSS5.CASE_SK) AND (CSS5.STATUS_NAME = 'SAR Review'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS6 ON (((CA.CASE_SK = CSS6.CASE_SK) AND (CSS6.STATUS_NAME = 'Reported'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS7 ON (((CA.CASE_SK = CSS7.CASE_SK) AND (CSS7.STATUS_NAME = 'Reported/Closed'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS8 ON (((CA.CASE_SK = CSS8.CASE_SK) AND (CSS8.STATUS_NAME = 'Closed'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS9 ON (((CA.CASE_SK = CSS9.CASE_SK) AND (CSS9.STATUS_NAME = 'Case QA'::""VARCHAR"")))) JOIN AML_L3_REP.V2_CASE_STATUS CS ON ((((CS.CASE_SK = CA.CASE_SK) AND (CS.STATUS_START_TS <= RD.REPORT_END_TS)) AND (CS.STATUS_END_TS >= RD.REPORT_END_TS)))) LEFT JOIN (SELECT AML_L3_REP.V2_SAR.CASE_SK, MAX(AML_L3_REP.V2_SAR.SAR_SOURCE_ID) AS SAR_SOURCE_ID FROM AML_L3_REP.V2_SAR GROUP BY AML_L3_REP.V2_SAR.CASE_SK) QMOST_RECENT_SAR ON ((QMOST_RECENT_SAR.CASE_SK = CA.CASE_SK))) LEFT JOIN AML_L3_REP.V2_SAR SA ON ((SA.SAR_SOURCE_ID = QMOST_RECENT_SAR.SAR_SOURCE_ID))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS2 ON (((SSS2.SAR_SK = SA.SAR_SK) AND (SSS2.STATUS_NAME = 'Filed'::""VARCHAR"")))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, AL.ALERT_OWNER_NAME, AL.ALERT_OWNER_FIRST_NAME, AL.ALERT_OWNER_LAST_NAME, AL.ALERT_OWNER_USER_SK, AL.ALERT_SOURCE, AL.ALERT_SOURCE_ID, AL.ALERT_REASON_NAME, AL.ALERT_SCENARIO_ID, AL.ALERT_CREATION_DATE, AL.ALERT_HRU_INDICATOR, AL.ALERT_CREATOR_NAME, AL.ALERT_CREATOR_FIRST_NAME, AL.ALERT_CREATOR_LAST_NAME, AL.ALERT_CREATOR_USER_SK, Q1.CLOSED_DATE, AL.ALERT_CURRENT_AGE AS ALERT_AGE, AL.ALERT_SK FROM ((AML_L3_REP.V2_REPORT_DATES RD JOIN (SELECT AML_L3_REP.V2_ALERT_STATUS_SUMMARY.ALERT_SK, MAX(AML_L3_REP.V2_ALERT_STATUS_SUMMARY.MOST_RECENT_TIMESTAMP) AS CLOSED_DATE FROM AML_L3_REP.V2_ALERT_STATUS_SUMMARY WHERE (((AML_L3_REP.V2_ALERT_STATUS_SUMMARY.STATUS_NAME = ('Closed Suspicious'::""VARCHAR"")::VARCHAR(64)) OR (AML_L3_REP.V2_ALERT_STATUS_SUMMARY.STATUS_NAME = ('Linked Closed'::""VARCHAR"")::VARCHAR(64))) OR (AML_L3_REP.V2_ALERT_STATUS_SUMMARY.STATUS_NAME = ('Closed Non Suspicious'::""VARCHAR"")::VARCHAR(64))) GROUP BY AML_L3_REP.V2_ALERT_STATUS_SUMMARY.ALERT_SK) Q1 ON (((Q1.CLOSED_DATE >= ""TIMESTAMP""(RD.REPORT_START_DATE)) AND (Q1.CLOSED_DATE <= ""TIMESTAMP""(RD.REPORT_END_DATE))))) JOIN AML_L3_REP.V2_ALERT AL ON ((AL.ALERT_SK = Q1.ALERT_SK))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, CA.CASE_NUMBER, CA.INVESTIGATOR_FULL_NAME, CA.INVESTIGATOR_FIRST_NAME, CA.INVESTIGATOR_LAST_NAME, CA.INVESTIGATOR_USER_SK, CA.CASE_HRU_INDICATOR, CA.CASE_CATEGORY, CA.CASE_SUB_CATEGORY, CA.CASE_CURRENT_STATUS, CSF.CASE_SUSPECT_FULL_NAME, CSF.CASE_NUMBER_OF_SUSPECTS, CA.CASE_CREATION_DATE, Q1.CLOSED_DATE, CA.CASE_SK FROM (((AML_L3_REP.V2_REPORT_DATES RD JOIN (SELECT AML_L3_REP.V2_CASE_STATUS_SUMMARY.CASE_SK, MAX(AML_L3_REP.V2_CASE_STATUS_SUMMARY.MOST_RECENT_TIMESTAMP) AS CLOSED_DATE FROM AML_L3_REP.V2_CASE_STATUS_SUMMARY WHERE ((AML_L3_REP.V2_CASE_STATUS_SUMMARY.STATUS_NAME = ('Closed'::""VARCHAR"")::VARCHAR(64)) OR (AML_L3_REP.V2_CASE_STATUS_SUMMARY.STATUS_NAME = ('Reported/Closed'::""VARCHAR"")::VARCHAR(64))) GROUP BY AML_L3_REP.V2_CASE_STATUS_SUMMARY.CASE_SK) Q1 ON (((Q1.CLOSED_DATE >= ""TIMESTAMP""(RD.REPORT_START_DATE)) AND (Q1.CLOSED_DATE <= ""TIMESTAMP""(RD.REPORT_END_DATE))))) JOIN AML_L3_REP.V2_CASE CA ON ((CA.CASE_SK = Q1.CASE_SK))) LEFT JOIN AML_L3_REP.V2_CASE_SUSPECT_FLATTENED CSF ON ((CSF.CASE_SK = CA.CASE_SK))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, IPT.ACCOUNT_NUMBER, IPT.AMOUNT, IPT.DATE_POSTED, IPT.SOURCE_SYSTEM, IPT.SOURCE_TRANSACTION_ID FROM (AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V_FACT_INCORRECTLY_PROFILED_TXN IPT ON (((IPT.DATE_POSTED >= RD.REPORT_START_DATE) AND (IPT.DATE_POSTED <= RD.REPORT_END_DATE)))); SELECT CA.INVESTIGATOR_FULL_NAME AS CASE_INVESTIGATOR_NAME, CA.INVESTIGATOR_FIRST_NAME AS CASE_INVESTIGATOR_FIRST_NAME, CA.INVESTIGATOR_LAST_NAME AS CASE_INVESTIGATOR_LAST_NAME, CA.INVESTIGATOR_USER_SK AS CASE_INVESTIGATOR_USER_SK, CA.CASE_HRU_INDICATOR, CA.CASE_NUMBER, CA.CASE_CURRENT_STATUS, CA.CASE_CREATION_DATE, CA.CASE_CATEGORY, CA.CASE_SUB_CATEGORY, CSF.CASE_NUMBER_OF_SUSPECTS, CSF.CASE_SUSPECT_FULL_NAME, CSS1.MOST_RECENT_DATE AS REPORTED_CLOSED_DATE, (SSS2.MOST_RECENT_DATE + 90) AS FOLLOWUP_REVIEW_DATE, SSS1.MOST_RECENT_DATE AS SAR_SUBMITTED_DATE, SSS2.MOST_RECENT_DATE AS SAR_FILED_DATE, SA.SAR_SOURCE_ID, CA.CASE_SK, SA.SAR_SK FROM ((((((AML_L3_REP.V2_CASE CA LEFT JOIN AML_L3_REP.V2_CASE_SUSPECT_FLATTENED CSF ON ((CA.CASE_SK = CSF.CASE_SK))) JOIN (SELECT AML_L3_REP.V2_SAR.CASE_SK, MAX(AML_L3_REP.V2_SAR.SAR_SOURCE_ID) AS SAR_SOURCE_ID FROM AML_L3_REP.V2_SAR GROUP BY AML_L3_REP.V2_SAR.CASE_SK) QMOST_RECENT_SAR ON ((QMOST_RECENT_SAR.CASE_SK = CA.CASE_SK))) JOIN AML_L3_REP.V2_SAR SA ON ((SA.SAR_SOURCE_ID = QMOST_RECENT_SAR.SAR_SOURCE_ID))) JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS2 ON ((((SSS2.SAR_SK = SA.SAR_SK) AND (SSS2.STATUS_NAME = 'Filed'::""VARCHAR"")) AND (SSS2.MOST_RECENT_DATE >= (DATE('today'::""VARCHAR"") - 90))))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS1 ON (((CSS1.CASE_SK = CA.CASE_SK) AND (CSS1.STATUS_NAME = 'Reported/Closed'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS1 ON (((SSS1.SAR_SK = SA.SAR_SK) AND (SSS1.STATUS_NAME = 'Disclosure Pending Review'::""VARCHAR"")))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, CA.CASE_NUMBER, CA.CASE_CURRENT_STATUS, SSS6.MOST_RECENT_DATE AS DISCLOSURE_FILED_DATE, CSS1.MOST_RECENT_DATE AS REPORTED_DATE, CASE WHEN (SSS1.DURATION_IN_DAYS NOTNULL) THEN SSS1.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS NEW_STATUS_TIME, CASE WHEN (SSS2.DURATION_IN_DAYS NOTNULL) THEN SSS2.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS DISC_PEND_REVIEW_STATUS_TIME, CASE WHEN (SSS3.DURATION_IN_DAYS NOTNULL) THEN SSS3.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS DISC_PEND_APPR_STATUS_TIME, CASE WHEN (SSS4.DURATION_IN_DAYS NOTNULL) THEN SSS4.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS DISC_APPR_STATUS_TIME, CASE WHEN (SSS5.DURATION_IN_DAYS NOTNULL) THEN SSS5.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS EFILE_STATUS_TIME, CASE WHEN (SSS6.DURATION_IN_DAYS NOTNULL) THEN SSS6.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS FILED_STATUS_TIME, CASE WHEN (SSS7.DURATION_IN_DAYS NOTNULL) THEN SSS7.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS ACK_STATUS_TIME, CASE WHEN (SSS8.DURATION_IN_DAYS NOTNULL) THEN SSS8.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS ACK_ERR_STATUS_TIME, CASE WHEN (SSS9.DURATION_IN_DAYS NOTNULL) THEN SSS9.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS DISC_DELETED_STATUS_TIME, CASE WHEN (SSS10.DURATION_IN_DAYS NOTNULL) THEN SSS10.DURATION_IN_DAYS WHEN (0 NOTNULL) THEN '0'::""NUMERIC"" ELSE NULL::""NUMERIC"" END AS DISC_DECLINED_STATUS_TIME, CSS2.MOST_RECENT_DATE AS RECOMMEND_SAR_DATE, SA.SAR_INVESTIGATOR_FULL_NAME AS INVESTIGATOR_FULL_NAME, SA.SAR_INVESTIGATOR_FIRST_NAME AS INVESTIGATOR_FIRST_NAME, SA.SAR_INVESTIGATOR_LAST_NAME AS INVESTIGATOR_LAST_NAME, SA.SAR_INVESTIGATOR_USER_SK AS INVESTIGATOR_USER_SK, CA.CASE_HRU_INDICATOR, CASE WHEN (SSF.SAR_NUMBER_OF_SUSPECTS NOTNULL) THEN SSF.SAR_NUMBER_OF_SUSPECTS WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS ""NVL"", SSF.SAR_SUSPECT_FULL_NAME, SA.SAR_CREATION_DATE, SA.SAR_CURRENT_STATUS, SS.STATUS_NAME AS SAR_STATUS_AT_REPORT_END_DATE, CA.CASE_SK, SA.SAR_SK, SA.SAR_SOURCE_ID FROM (((((((((((((((((AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS2 ON ((((CSS2.MOST_RECENT_TIMESTAMP >= RD.REPORT_START_TS) AND (CSS2.MOST_RECENT_TIMESTAMP <= RD.REPORT_END_TS)) AND (CSS2.STATUS_NAME = 'SAR Recommended'::""VARCHAR"")))) JOIN AML_L3_REP.V2_CASE CA ON ((CA.CASE_SK = CSS2.CASE_SK))) JOIN (SELECT SA.CASE_SK, MAX(SA.SAR_SOURCE_ID) AS SAR_SOURCE_ID FROM AML_L3_REP.V2_SAR SA GROUP BY SA.CASE_SK) QMOST_RECENT_SAR ON ((QMOST_RECENT_SAR.CASE_SK = CA.CASE_SK))) JOIN AML_L3_REP.V2_SAR SA ON ((SA.SAR_SOURCE_ID = QMOST_RECENT_SAR.SAR_SOURCE_ID))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS1 ON (((CSS1.CASE_SK = SA.CASE_SK) AND (CSS1.STATUS_NAME = 'Reported'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS1 ON (((SSS1.SAR_SK = SA.SAR_SK) AND (SSS1.STATUS_NAME = 'New'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS2 ON (((SSS2.SAR_SK = SA.SAR_SK) AND (SSS2.STATUS_NAME = 'Disclosure Pending Review'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS3 ON (((SSS3.SAR_SK = SA.SAR_SK) AND (SSS3.STATUS_NAME = 'Disclosure Pending Approval'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS4 ON (((SSS4.SAR_SK = SA.SAR_SK) AND (SSS4.STATUS_NAME = 'Disclosure Approved'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS5 ON (((SSS5.SAR_SK = SA.SAR_SK) AND (SSS5.STATUS_NAME = 'Added to E-File'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS6 ON (((SSS6.SAR_SK = SA.SAR_SK) AND (SSS6.STATUS_NAME = 'Filed'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS7 ON (((SSS7.SAR_SK = SA.SAR_SK) AND (SSS7.STATUS_NAME = 'Acknowledged'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS8 ON (((SSS8.SAR_SK = SA.SAR_SK) AND (SSS8.STATUS_NAME = 'Acknowledged with Errors'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS9 ON (((SSS9.SAR_SK = SA.SAR_SK) AND (SSS9.STATUS_NAME = 'Disclosure Deleted'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SSS10 ON (((SSS10.SAR_SK = SA.SAR_SK) AND (SSS10.STATUS_NAME = 'Disclosure Declined'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_SAR_SUSPECT_FLATTENED SSF ON ((SSF.SAR_SOURCE_ID = SA.SAR_SOURCE_ID))) JOIN AML_L3_REP.V2_SAR_STATUS SS ON ((((SS.SAR_SK = SA.SAR_SK) AND (SS.STATUS_START_TS <= RD.REPORT_END_TS)) AND (SS.STATUS_END_TS >= RD.REPORT_END_TS)))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, CA.INVESTIGATOR_FULL_NAME, CA.INVESTIGATOR_FIRST_NAME, CA.INVESTIGATOR_LAST_NAME, CA.CASE_HRU_INDICATOR, CA.CASE_NUMBER, CA.CASE_CURRENT_STATUS, CASE WHEN (CSF.CASE_NUMBER_OF_SUSPECTS NOTNULL) THEN CSF.CASE_NUMBER_OF_SUSPECTS WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS CASE_NUMBER_OF_SUSPECTS, CSF.CASE_SUSPECT_FULL_NAME, CA.CASE_CATEGORY, CA.CASE_SUB_CATEGORY, CA.CASE_CREATION_DATE, CSS1.MOST_RECENT_DATE AS CASE_REPORTED_CLOSED_DATE, (SS2.MOST_RECENT_DATE + 90) AS CASE_FOLLOW_UP_REVIEW_DATE, SA.SAR_SOURCE_ID, SA.SAR_SK, SS1.MOST_RECENT_DATE AS SAR_SUBMISSION_DATE, SS2.MOST_RECENT_DATE AS SAR_FILE_DATE, F1.""DESCRIPTION"" AS TYPE_OF_FILING, SFI.FILER_NAME, SFI.FILING_INSTITUTION_TYPE_CODE, SFI.FILING_INSTITUTION_TYPE_OTHER_DESCRIPTION, SFI.PRIMARY_FEDERAL_REGULATOR_CODE, SFI.IDENTIFICATION_TYPE_CODE, SFI.IDENTIFICATION_NUMBER, FI.FINANCIAL_INSTITUTION_TYPE_CODE, FI.PRIMARY_FEDERAL_REGULATOR_CODE AS FI_PRIMARY_FEDERAL_REGULATOR_CODE, SFIST.CODE AS FI_TYPE_OF_SECURITIES_CODE, SFIST.""DESCRIPTION"" AS FI_TYPE_OF_SECURITIES_DESCRIPTION, SFIST.OTHER_DESCRIPTION AS FI_TYPE_OTHER_DESCRIPTION, FI.FINANCIAL_INSTITUTION_LEGAL_NAME, FI.FINANCIAL_INSTITUTION_ALTERNATE_NAME, FI.IDENTIFIER_TYPE_CODE AS FI_IDENTIFIER_TYPE_CODE, FI.IDENTIFICATION_NUMBER AS FI_IDENTIFICATION_NUMBER, FI.BRANCH_ROLE_IN_TRANSACTION AS FI_ROLE_IN_TRANSACTION, FI.FINANCIAL_INSTITUTION_STREET_ADDRESS, FI.FINANCIAL_INSTITUTION_CITY, FI.FINANCIAL_INSTITUTION_STATE_CODE, FI.FINANCIAL_INSTITUTION_COUNTRY_CODE, FI.FINANCIAL_INSTITUTION_POSTAL_CODE, FI.INTERNAL_FILE_NUMBER AS FI_INTERNAL_FILE_NUMBER, FI.LOSS_TO_FINANCIAL_INSTITUTION AS FI_LOSS_TO_FINANCIAL_INSTITUTION, FI.BRANCH_ROLE_IN_TRANSACTION, FI.BRANCH_STREET_ADDRESS, FI.BRANCH_CITY, FI.BRANCH_STATE_CODE, FI.BRANCH_COUNTRY_CODE, FI.BRANCH_POSTAL_CODE, FI.BRANCH_RSSD_NUMBER, SI.LE_CONTACT_AGENCY AS SAR_LAW_ENFORCEMENT_AGENCY, SS.SAR_SUSPECT_FULL_NAME, SS.SAR_SUSPECT_FIRST_NAME, SS.SAR_SUSPECT_MIDDLE_NAME, SS.SAR_SUSPECT_LAST_NAME, SS.SAR_SUSPECT_TIN, SS.SAR_SUSPECT_TIN_TYPE_CODE, SS.SAR_SUSPECT_DATE_OF_BIRTH, SS.SAR_SUSPECT_OCCUPATION, SSA.STREET_ADDRESS AS SAR_SUSPECT_STREET_ADDRESS, SSA.CITY AS SAR_SUSPECT_CITY, SSA.STATE_CODE AS SAR_SUSPECT_STATE_CODE, SSA.POSTAL_CODE AS SAR_SUSPECT_POSTAL_CODE, SSA.COUNTRY_CODE AS SAR_SUSPECT_COUNTRY_CODE, CASE WHEN (INT4(SI.CUMULATIVE_AMOUNT) > 0) THEN FLOAT8(CASE WHEN (SI.CUMULATIVE_AMOUNT NOTNULL) THEN SI.CUMULATIVE_AMOUNT WHEN ('0' NOTNULL) THEN '0'::""VARCHAR"" ELSE NULL::""VARCHAR"" END) ELSE FLOAT8(CASE WHEN (SI.AMOUNT_INVOLVED NOTNULL) THEN SI.AMOUNT_INVOLVED WHEN ('0' NOTNULL) THEN '0'::""VARCHAR"" ELSE NULL::""VARCHAR"" END) END AS SAR_FILED_AMOUNT, SI.SUSPICIOUS_ACTIVITY_FROM AS SAR_ACTIVITY_FROM_DATE, SI.SUSPICIOUS_ACTIVITY_TO AS SAR_ACTIVITY_TO_DATE, SFR.SAR_SECTION_NAME AS SAR_FILING_REASON_SECTION_NAME, SFR.SAR_SECTION_NUMBER AS SAR_FILING_REASON_SECTION_NUMBER, SFR.CODE AS SAR_FILING_REASON_CODE, SFR.""DESCRIPTION"" AS SAR_FILING_REASON_DESCRIPTION, SFR.OTHER_DESCRIPTION AS SAR_FILING_REASON_OTHER_DESCRIPTION, CA.CASE_SK FROM ((((((((((((((((AML_L3_REP.V2_REPORT_DATES RD LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS1 ON ((CSS1.STATUS_NAME = 'Reported/Closed'::""VARCHAR""))) JOIN AML_L3_REP.V2_CASE CA ON ((CA.CASE_SK = CSS1.CASE_SK))) JOIN (SELECT AML_L3_REP.V2_SAR.CASE_SK, MAX(AML_L3_REP.V2_SAR.SAR_SOURCE_ID) AS SAR_SOURCE_ID FROM AML_L3_REP.V2_SAR GROUP BY AML_L3_REP.V2_SAR.CASE_SK) QMOST_RECENT_SAR ON ((QMOST_RECENT_SAR.CASE_SK = CA.CASE_SK))) JOIN AML_L3_REP.V2_SAR SA ON ((SA.SAR_SOURCE_ID = QMOST_RECENT_SAR.SAR_SOURCE_ID))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SS1 ON (((SS1.SAR_SK = SA.SAR_SK) AND (SS1.STATUS_NAME = 'Added to E-File'::""VARCHAR"")))) JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SS2 ON (((((SS2.SAR_SK = SA.SAR_SK) AND (SS2.STATUS_NAME = 'Filed'::""VARCHAR"")) AND (SS2.MOST_RECENT_TIMESTAMP >= RD.REPORT_START_TS)) AND (SS2.MOST_RECENT_TIMESTAMP <= RD.REPORT_END_TS)))) JOIN AML_L3_REP.V_SAR_INFO SI ON (((SI.SAR_SOURCE_ID = SA.SAR_SOURCE_ID) AND (SI.SAR_INFO_END_DATE = '9999-12-31'::DATE)))) LEFT JOIN AML_L3_REP.V2_SAR_FILING_INSTITUTION SFI ON ((SFI.SAR_SOURCE_ID = SA.SAR_SOURCE_ID))) LEFT JOIN AML_L3_REP.V2_SAR_FILING_INSTITUTION_SFI_TYPE SFIST ON ((SFIST.SAR_SOURCE_ID = SA.SAR_SOURCE_ID))) LEFT JOIN AML_L3_REP.V_SAR_FILING_OPTIONS F1 ON ((((F1.SAR_SOURCE_ID = SA.SAR_SOURCE_ID) AND (F1.""TYPE"" = 'FILING_TYPE'::""VARCHAR"")) AND (F1.SAR_OPT_END_DATE = '9999-12-31'::DATE)))) LEFT JOIN AML_L3_REP.V2_CASE_SUSPECT_FLATTENED CSF ON ((CSF.CASE_SK = CA.CASE_SK))) LEFT JOIN AML_L3_REP.V2_SAR_SUSPECT SS ON ((SS.SAR_SOURCE_ID = SA.SAR_SOURCE_ID))) LEFT JOIN AML_L3_REP.V_SAR_SUSPECT_ADDRESS SSA ON ((SSA.SUBJECT_ID = SS.SUBJECT_ID))) LEFT JOIN AML_L3_REP.V2_SAR_FINANCIAL_INSTITUTION FI ON ((FI.SAR_SOURCE_ID = SA.SAR_SOURCE_ID))) LEFT JOIN AML_L3_REP.V2_SAR_FINANCIAL_INSTITUTION_SFI_TYPE FIT ON ((FIT.DISC_FIN_INST_KEY = FI.DISC_FIN_INST_KEY))) LEFT JOIN AML_L3_REP.V2_SAR_FILING_REASONS SFR ON ((SFR.SAR_SOURCE_ID = SA.SAR_SOURCE_ID))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, AL.ALERT_SK, CA.CASE_SK, AL.ALERT_SCENARIO_ID, AL.ALERT_SOURCE, AL.ALERT_REASON_NAME, AL.CUSTOMER_ID, AL.CUSTOMER_FULL_NAME, AL.CUSTOMER_FIRST_NAME, AL.CUSTOMER_MIDDLE_NAME, AL.ALERT_SOURCE_ID AS ALERT_ID, AL.ALERT_CREATION_DATE, CA.CASE_NUMBER, CA.CASE_NAME, CA.INVESTIGATOR_FULL_NAME, CA.INVESTIGATOR_FIRST_NAME, CA.INVESTIGATOR_LAST_NAME, CA.INVESTIGATOR_USER_SK, AL.ALERT_SCORE, DATE(ASS1.STATUS_START_TS) AS ALERT_LAST_ACTION_DATE, ASS1.STATUS_NAME AS ALERT_STATUS_AS_AT_END_DATE, AL.ALERT_CURRENT_STATUS, CASE WHEN (((CSS1.MOST_RECENT_DATE NOTNULL) AND ((CSS3.MOST_RECENT_DATE ISNULL) OR (CSS1.MOST_RECENT_DATE > CSS3.MOST_RECENT_DATE))) AND (((AL.ALERT_CURRENT_STATUS <> 'New'::""VARCHAR"") AND (AL.ALERT_CURRENT_STATUS <> 'Assigned and Ready'::""VARCHAR"")) AND ((AL.ALERT_CURRENT_STATUS <> 'Triage'::""VARCHAR"") AND (AL.ALERT_CURRENT_STATUS <> 'Linked'::""VARCHAR"")))) THEN 1 ELSE 0 END AS ALERT_CASE_CLOSED_INDICATOR, CASE WHEN (((CSS2.MOST_RECENT_DATE NOTNULL) AND ((CSS3.MOST_RECENT_DATE ISNULL) OR (CSS2.MOST_RECENT_DATE > CSS2.MOST_RECENT_DATE))) AND (((AL.ALERT_CURRENT_STATUS <> 'New'::""VARCHAR"") AND (AL.ALERT_CURRENT_STATUS <> 'Assigned and Ready'::""VARCHAR"")) AND ((AL.ALERT_CURRENT_STATUS <> 'Triage'::""VARCHAR"") AND (AL.ALERT_CURRENT_STATUS <> 'Linked'::""VARCHAR"")))) THEN 1 ELSE 0 END AS ALERT_CASE_REPORTED_INDICATOR, CASE WHEN (((CSS3.MOST_RECENT_DATE NOTNULL) AND ((CSS1.MOST_RECENT_DATE ISNULL) OR (CSS3.MOST_RECENT_DATE >= CSS1.MOST_RECENT_DATE))) AND (((AL.ALERT_CURRENT_STATUS <> 'New'::""VARCHAR"") AND (AL.ALERT_CURRENT_STATUS <> 'Assigned and Ready'::""VARCHAR"")) AND ((AL.ALERT_CURRENT_STATUS <> 'Triage'::""VARCHAR"") AND (AL.ALERT_CURRENT_STATUS <> 'Linked'::""VARCHAR"")))) THEN 1 ELSE 0 END AS ALERT_CASE_REPORTED_CLOSED_INDICATOR, CASE WHEN (((AL.ALERT_CURRENT_STATUS = 'New'::""VARCHAR"") OR (AL.ALERT_CURRENT_STATUS = 'Assigned and Ready'::""VARCHAR"")) OR ((AL.ALERT_CURRENT_STATUS = 'Triage'::""VARCHAR"") OR (AL.ALERT_CURRENT_STATUS = 'Linked'::""VARCHAR""))) THEN 1 ELSE 0 END AS ALERT_NOT_YET_CLOSED_INDICATOR, 'United States' AS ORGANIZATION FROM ((((((AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V2_ALERT AL ON (((AL.ALERT_CREATION_DATE >= RD.REPORT_START_DATE) AND (AL.ALERT_CREATION_DATE <= RD.REPORT_END_DATE)))) LEFT JOIN AML_L3_REP.V2_CASE CA ON ((AL.CASE_SK = CA.CASE_SK))) LEFT JOIN AML_L3_REP.V2_ALERT_STATUS ASS1 ON ((((ASS1.ALERT_SK = AL.ALERT_SK) AND (ASS1.STATUS_START_TS <= RD.REPORT_END_TS)) AND (ASS1.STATUS_END_TS >= RD.REPORT_END_TS)))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS1 ON (((CA.CASE_SK = CSS1.CASE_SK) AND (CSS1.STATUS_NAME = 'Closed'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS2 ON (((CA.CASE_SK = CSS2.CASE_SK) AND (CSS2.STATUS_NAME = 'Reported'::""VARCHAR"")))) LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS3 ON (((CA.CASE_SK = CSS3.CASE_SK) AND (CSS3.STATUS_NAME = 'Reported/Closed'::""VARCHAR"")))); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, AL.CUSTOMER_FULL_NAME, AL.CUSTOMER_FIRST_NAME, AL.CUSTOMER_MIDDLE_NAME, AL.CUSTOMER_LAST_NAME, AL.ALERT_SOURCE_ID, AL.ALERT_CURRENT_STATUS, AL.ALERT_CREATION_DATE, AL.ALERT_CURRENT_AGE, AL.ALERT_HRU_INDICATOR, AL.ALERT_SK FROM (AML_L3_REP.V2_REPORT_DATES RD JOIN AML_L3_REP.V2_ALERT AL ON (((AL.ALERT_CREATION_DATE >= RD.REPORT_START_DATE) AND (AL.ALERT_CREATION_DATE <= RD.REPORT_END_DATE)))) WHERE (AL.ALERT_REASON_NAME = 'UARF'::""VARCHAR""); SELECT RD.REPORT_START_DATE, RD.REPORT_END_DATE, CA.INVESTIGATOR_FULL_NAME, CA.INVESTIGATOR_FIRST_NAME, CA.INVESTIGATOR_LAST_NAME, CA.CASE_NUMBER, CA.CASE_HRU_INDICATOR, CA.CASE_CURRENT_STATUS, CASE WHEN (CSF.CASE_NUMBER_OF_SUSPECTS NOTNULL) THEN CSF.CASE_NUMBER_OF_SUSPECTS WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS CASE_NUMBER_OF_SUSPECTS, CSF.CASE_SUSPECT_FULL_NAME, CA.CASE_CATEGORY, CA.CASE_SUB_CATEGORY, CA.CASE_CREATION_DATE, CSS1.MOST_RECENT_DATE AS CASE_REPORTED_CLOSED_DATE, (SS2.MOST_RECENT_DATE + 90) AS CASE_FOLLOW_UP_REVIEW_DATE, CA.CASE_SK, SA.SAR_SOURCE_ID, SA.SAR_SK, SS1.MOST_RECENT_DATE AS SAR_SUBMISSION_DATE, SS2.MOST_RECENT_DATE AS SAR_FILE_DATE, CASE WHEN (SSF.SAR_NUMBER_OF_SUSPECTS NOTNULL) THEN SSF.SAR_NUMBER_OF_SUSPECTS WHEN (0 NOTNULL) THEN '0'::INT8 ELSE NULL::INT8 END AS SAR_NUMBER_OF_SUSPECTS, SSF.SAR_SUSPECT_FULL_NAME, SSF.SAR_SUSPECT_FIRST_NAME, SSF.SAR_SUSPECT_MIDDLE_NAME, SSF.SAR_SUSPECT_LAST_NAME, SSF.SAR_SUSPECT_TIN, SSF.SAR_SUSPECT_TIN_TYPE_CODE, SSF.SAR_SUSPECT_DATE_OF_BIRTH, SSF.SAR_SUSPECT_OCCUPATION, SSF.NUMBER_OF_ADDRESSES, SSF.SAR_SUSPECT_STREET_ADDRESS, SSF.SAR_SUSPECT_CITY, SSF.SAR_SUSPECT_STATE, SSF.SAR_SUSPECT_POSTAL_CODE, SSF.SAR_SUSPECT_COUNTRY_CODE, SSF.IDENTIFICATION_TYPE_CODE, SSF.IDENTIFICATION_NUMBER, CASE WHEN (INT4(SI.CUMULATIVE_AMOUNT) > 0) THEN SI.CUMULATIVE_AMOUNT ELSE SI.AMOUNT_INVOLVED END AS SAR_FILED_AMOUNT, SI.SUSPICIOUS_ACTIVITY_FROM AS SAR_ACTIVITY_FROM_DATE, SI.SUSPICIOUS_ACTIVITY_TO AS SAR_ACTIVITY_TO_DATE FROM (((((((((AML_L3_REP.V2_REPORT_DATES RD LEFT JOIN AML_L3_REP.V2_CASE_STATUS_SUMMARY CSS1 ON ((CSS1.STATUS_NAME = 'Reported/Closed'::""VARCHAR""))) JOIN AML_L3_REP.V2_CASE CA ON ((CA.CASE_SK = CSS1.CASE_SK))) JOIN (SELECT AML_L3_REP.V_FACT_SAR.CASE_SK, MAX(AML_L3_REP.V_FACT_SAR.SAR_SK) AS SAR_SK FROM AML_L3_REP.V_FACT_SAR GROUP BY AML_L3_REP.V_FACT_SAR.CASE_SK) QMOST_RECENT_SAR ON ((QMOST_RECENT_SAR.CASE_SK = CA.CASE_SK))) JOIN AML_L3_REP.V2_SAR SA ON ((SA.SAR_SK = QMOST_RECENT_SAR.SAR_SK))) LEFT JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SS1 ON (((SS1.SAR_SK = SA.SAR_SK) AND (SS1.STATUS_NAME = 'Added to E-File'::""VARCHAR"")))) JOIN AML_L3_REP.V2_SAR_STATUS_SUMMARY SS2 ON (((((SS2.SAR_SK = SA.SAR_SK) AND (SS2.STATUS_NAME = 'Filed'::""VARCHAR"")) AND (SS2.MOST_RECENT_TIMESTAMP >= RD.REPORT_START_TS)) AND (SS2.MOST_RECENT_TIMESTAMP <= RD.REPORT_END_TS)))) JOIN AML_L3_REP.V_SAR_INFO SI ON (((SI.SAR_SOURCE_ID = SA.SAR_SOURCE_ID) AND (SI.SAR_INFO_END_DATE = '9999-12-31'::DATE)))) LEFT JOIN AML_L3_REP.V2_SAR_SUSPECT_FLATTENED SSF ON ((SSF.SAR_SOURCE_ID = SA.SAR_SOURCE_ID))) LEFT JOIN AML_L3_REP.V2_CASE_SUSPECT_FLATTENED CSF ON ((CSF.CASE_SK = CA.CASE_SK))) WHERE (CA.CASE_CATEGORY = 'US Dollar Drafts'::""VARCHAR""); #N/A #N/A SELECT IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.SUBJECT_ADDR_KEY, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.SUBJECT_ID, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.STREET_ADDRESS, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.CITY, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.STATE_CODE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.POSTAL_CODE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.COUNTRY_CODE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.USER_FIELD, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.VALIDATION_REQUIRED, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.SAR_SUSPECT_ADDR_EFFECTIVE_DATE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.SAR_SUSPECT_ADDR_END_DATE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS.IDP_AUDIT_ID FROM IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_ADDRESS; SELECT IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.SUBJECT_IDENT_KEY, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.SUBJECT_ID, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.IDENTIFICATION_TYPE_CODE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.IDENTIFICATION_OTHER_DESC, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.IDENTIFICATION_NUMBER, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.ISSUING_STATE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.ISSUING_COUNTRY, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.USER_FIELD, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.VALIDATION_REQUIRED, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.SAR_SUSPECT_IDENT_EFFECTIVE_DATE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.SAR_SUSPECT_IDENT_END_DATE, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.IDP_WAREHOUSE_ID, IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION.IDP_AUDIT_ID FROM IDP_PRD_LEVEL3.AML_L3_HPT_GRP.SAR_SUSPECT_IDENTIFICATION; (SELECT CD.IDP_WAREHOUSE_ID, CD.IDP_DATA_DATE, CD.RECORD_TYPE, (CD.SEQUENCE_NUMBER)::CHAR(13) AS SEQUENCE_NUMBER, CD.TRANSACTION_TYPE, CD.RECORD_QUALIFIER, CD.COMMUNICATION_LOG_KEY, CD.COMMUNICATION_LINE, CD.DIRECTION, CD.FUNDS_FLAG, CD.COMMUNICATION_SEQUENCE, CD.COMMUNICATION_DATETIME, CD.CONTINUATION_SEQUENCE, CD.FEDWIRE_MESSAGE_TYPE, CD.MOI, CD.VALUE_DATE, CD.PRINCIPAL, CD.PRINCIPAL_CURRENCY_CODE, CD.TRAN_AMOUNT, CD.TRAN_AMOUNT_CURRENCY_CODE, CD.EXCHANGE_RATE, CD.EXCHANGE_RATE_TYPE, CD.TYPE_CODE, CASE WHEN (CD.TYPE_CODE = '10'::""VARCHAR"") THEN '10 Funds Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '15'::""VARCHAR"") THEN '15 Foreign Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '16'::""VARCHAR"") THEN '16 Settlement Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '100'::""VARCHAR"") THEN '100 Interbank Message'::""VARCHAR"" WHEN (CD.TYPE_CODE = '103'::""VARCHAR"") THEN '103 Single Customer Credit Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '202'::""VARCHAR"") THEN '202 General Financial Institution Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '205'::""VARCHAR"") THEN '205 Financial Institution Transfer Execution'::""VARCHAR"" ELSE ''::""VARCHAR"" END AS TYPE_CODE_MESSAGE, CD.FEE_ANALYSIS_CODE, CD.MSGSRC, CD.OCA, CD.DEBIT_AMOUNT, CD.DEBIT_CURRENCY, CD.CREDIT_AMOUNT, CD.CREDIT_CURRENCY, CD.COMMISSION_AMOUNT, CD.PASS_THRU, CD.BANK_TRANSFER_FLAG, CD.DEBIT_ACCOUNT_NUMBER, CD.DEBIT_ACCOUNT_TYPE, CD.DEBIT_ACCOUNT_BANK_NUMBER, CD.DEBIT_CURRENCY_CODE, CD.CREDIT_ACCOUNT_NUMBER, CD.CREDIT_ACCOUNT_TYPE, CD.CREDIT_ACCOUNT_BANK_NUMBER, CD.CREDIT_CURRENCY_CODE, CD.VIA_BRANCH, CD.OBI_LINE_1, CD.OBI_LINE_2, CD.OBI_LINE_3, CD.OBI_LINE_4, CD.BBI_LINE_1, CD.BBI_LINE_2, CD.BBI_LINE_3, CD.BBI_LINE_4, CD.BBI_LINE_5, CD.BBI_LINE_6, CD.SENDER_REFERENCE, CD.RELATED_REFERENCE, CD.SENDER_ABA, CD.SENDER_BIC, CD.SENDER_NAME, CD.RECEIVER_ABA, CD.RECEIVER_BIC, CD.RECEIVER_NAME, CD.ORIGINATOR_ACCOUNT, (CD.ORIGINATOR_ABA)::VARCHAR(40) AS ORIGINATOR_ABA, (CD.ORIGINATOR_BIC)::VARCHAR(30) AS ORIGINATOR_BIC, CD.ORIGINATOR_ID, CD.ORIGINATOR_NAME, CD.ORIGINATOR_ADDRESS, CD.ORIGINATOR_COUNTRY_CODE, CDF.ORIGINATOR_COUNTRY_CODE AS ORIGINATOR_COUNTRY_DERIVED_FINAL, CD.ORDERING_INSITUTION_COUNTRY_CODE, CDF.ORDERING_INSITUTION_COUNTRY_CODE AS ORDERING_INSITUTION_COUNTRY_CODE_DERIVED, CD.INTERMEDIARY_INSTITUTION_COUNTRY_CODE, CDF.INTERMEDIARY_INSTITUTION_COUNTRY_CODE AS INTERMEDIARY_INSTITUTION_COUNTRY_CODE_DERIVED, CD.BENEFICIARY_COUNTRY_CODE, CDF.BENEFICIARY_COUNTRY_CODE AS BENEFICIARY_COUNTRY_DERIVED_FINAL, CD.SENDER_CORRESPONDENT_COUNTRY_CODE, CDF.SENDER_CORRESPONDENT_COUNTRY_CODE AS SENDER_CORRESPONDENT_COUNTRY_CODE_DERIVED, CD.ACCOUNT_WITH_INST_COUNTRY_CODE, CDF.ACCOUNT_WITH_INST_COUNTRY_CODE AS ACCOUNT_WITH_INST_COUNTRY_CODE_DERIVED, CD.RECEIVER_CORRESPONDENT_COUNTRY_CODE, CDF.RECEIVER_CORRESPONDENT_COUNTRY_CODE AS RECEIVER_CORRESPONDENT_COUNTRY_CODE_DERVIED, CD.BENEFICIARY_NAME, (CD.BENEFICIARY_ADDRESS)::VARCHAR(310) AS BENEFICIARY_ADDRESS, CD.BENEFICIARY_ABA, (CD.BENEFICIARY_BIC)::VARCHAR(30) AS BENEFICIARY_BIC, CD.BENEFICIARY_ID, CD.ORDERING_INSITUTION_ACCOUNT, (CD.ORDERING_INSITUTION_ABA)::VARCHAR(40) AS ORDERING_INSITUTION_ABA, (CD.ORDERING_INSITUTION_BIC)::VARCHAR(30) AS ORDERING_INSITUTION_BIC, CD.ORDERING_INSITUTION_ID, CD.ORDERING_INSITUTION_NAME, (CD.ORDERING_INSITUTION_ADDRESS)::VARCHAR(240) AS ORDERING_INSITUTION_ADDRESS, CD.SENDER_CORRESPONDENT_ACCOUNT, CD.SENDER_CORRESPONDENT_ABA, CD.SENDER_CORRESPONDENT_BIC, CD.SENDER_CORRESPONDENT_ID, CD.SENDER_CORRESPONDENT_NAME, CD.SENDER_CORRESPONDENT_ADDRESS, CD.RECEIVER_CORRESPONDENT_ACCOUNT, CD.RECEIVER_CORRESPONDENT_ABA, CD.RECEIVER_CORRESPONDENT_BIC, CD.RECEIVER_CORRESPONDENT_ID, CD.RECEIVER_CORRESPONDENT_NAME, CD.RECEIVER_CORRESPONDENT_ADDRESS, CD.INTERMEDIARY_INSTITUTION_ACCOUNT, (CD.INTERMEDIARY_INSTITUTION_ABA)::VARCHAR(40) AS INTERMEDIARY_INSTITUTION_ABA, CD.INTERMEDIARY_INSTITUTION_BIC, CD.INTERMEDIARY_INSTITUTION_ID, CD.INTERMEDIARY_INSTITUTION_NAME, CD.INTERMEDIARY_INSTITUTION_ADDRESS, CD.ACCOUNT_WITH_INST_ACCOUNT, (CD.ACCOUNT_WITH_INST_ABA)::VARCHAR(40) AS ACCOUNT_WITH_INST_ABA, (CD.ACCOUNT_WITH_INST_BIC)::VARCHAR(30) AS ACCOUNT_WITH_INST_BIC, CD.ACCOUNT_WITH_INST_ID, CD.ACCOUNT_WITH_INST_NAME, CD.ACCOUNT_WITH_INST_ADDRESS, CD.BENEFICIARY_ACCOUNT, CASE WHEN (CD.CREDIT_CURRENCY = 'CAD'::BPCHAR) THEN CD.CREDIT_AMOUNT ELSE (CD.CREDIT_AMOUNT / E.EXCHANGE_RATE) END AS CREDIT_AMOUNT_IN_CAD, CASE WHEN (CD.CREDIT_CURRENCY = 'CAD'::BPCHAR) THEN '1'::""NUMERIC"" ELSE E.EXCHANGE_RATE END AS CREDIT_EXCHANGE_RATE_USED FROM ((PODR.V_L1_MNET_WIRE_PAYMENT CD JOIN PODR.V_L1_PODR_DERIVED_VALUES CDF ON ((CD.IDP_WAREHOUSE_ID = CDF.IDP_WAREHOUSE_ID))) LEFT JOIN IDP_INTERFACE.V_REF_EXCHANGE_RATE E ON (((CD.CREDIT_CURRENCY = E.TO_CURRENCY_CODE) AND (CASE WHEN (CD.VALUE_DATE NOTNULL) THEN CD.VALUE_DATE WHEN (CD.VALUE_DATE NOTNULL) THEN CD.VALUE_DATE ELSE NULL::DATE END = E.EXCHANGE_RATE_DATE))))) UNION ALL (SELECT CD.IDP_WAREHOUSE_ID, CD.IDP_DATA_DATE, (CD.RECORD_QUALIFIER)::CHAR(3) AS RECORD_QUALIFIER, CD.SEQUENCE_NUMBER, CD.TRANSACTION_TYPE, CD.RECORD_QUALIFIER, CD.COMMUNICATION_LOG_KEY, CD.COMMUNICATION_LINE, CD.DIRECTION, CD.FUNDS_FLAG, CD.COMMUNICATION_SEQUENCE, CD.COMMUNICATION_DATETIME, CD.CONTINUATION_SEQUENCE, CD.FEDWIRE_MESSAGE_TYPE, CD.MOI, CD.VALUE_DATE, CD.PRINCIPAL, CD.PRINCIPAL_CURRENCY_CODE, CD.TRAN_AMOUNT, CD.TRAN_AMOUNT_CURRENCY_CODE, CD.EXCHANGE_RATE, CD.EXCHANGE_RATE_TYPE, CD.TYPE_CODE, CASE WHEN (CD.TYPE_CODE = '10'::""VARCHAR"") THEN '10 Funds Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '15'::""VARCHAR"") THEN '15 Foreign Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '16'::""VARCHAR"") THEN '16 Settlement Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '100'::""VARCHAR"") THEN '100 Interbank Message'::""VARCHAR"" WHEN (CD.TYPE_CODE = '103'::""VARCHAR"") THEN '103 Single Customer Credit Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '202'::""VARCHAR"") THEN '202 General Financial Institution Transfer'::""VARCHAR"" WHEN (CD.TYPE_CODE = '205'::""VARCHAR"") THEN '205 Financial Institution Transfer Execution'::""VARCHAR"" ELSE ''::""VARCHAR"" END AS TYPE_CODE_MESSAGE, CD.FEE_ANALYSIS_CODE, CD.MSGSRC, CD.OCA, CD.DEBIT_AMOUNT, CD.DEBIT_CURRENCY, CD.CREDIT_AMOUNT, CD.CREDIT_CURRENCY, CD.COMMISSION_AMOUNT, CD.PASS_THRU, CD.BANK_TRANSFER_FLAG, CD.DEBIT_ACCOUNT_NUMBER, CD.DEBIT_ACCOUNT_TYPE, CD.DEBIT_ACCOUNT_BANK_NUMBER, CD.DEBIT_CURRENCY_CODE, CD.CREDIT_ACCOUNT_NUMBER, CD.CREDIT_ACCOUNT_TYPE, CD.CREDIT_ACCOUNT_BANK_NUMBER, CD.CREDIT_CURRENCY_CODE, CD.VIA_BRANCH, CD.OBI_LINE_1, CD.OBI_LINE_2, CD.OBI_LINE_3, CD.OBI_LINE_4, CD.BBI_LINE_1, CD.BBI_LINE_2, CD.BBI_LINE_3, CD.BBI_LINE_4, CD.BBI_LINE_5, CD.BBI_LINE_6, CD.SENDER_REFERENCE, CD.RELATED_REFERENCE, CD.SENDER_ABA, CD.SENDER_BIC, CD.SENDER_NAME, CD.RECEIVER_ABA, CD.RECEIVER_BIC, CD.RECEIVER_NAME, CD.ORIGINATOR_ACCOUNT, (CD.ORIGINATOR_ABA)::VARCHAR(40) AS ORIGINATOR_ABA, (CD.ORIGINATOR_BIC)::VARCHAR(30) AS ORIGINATOR_BIC, CD.ORIGINATOR_ID, CD.ORIGINATOR_NAME, CD.ORIGINATOR_ADDRESS, CD.ORIGINATOR_COUNTRY_CODE, CDF.ORIGINATOR_COUNTRY_CODE AS ORIGINATOR_COUNTRY_DERIVED_FINAL, CD.ORDERING_INSITUTION_COUNTRY_CODE, CDF.ORDERING_INSITUTION_COUNTRY_CODE AS ORDERING_INSITUTION_COUNTRY_CODE_DERIVED, CD.INTERMEDIARY_INSTITUTION_COUNTRY_CODE, CDF.INTERMEDIARY_INSTITUTION_COUNTRY_CODE AS INTERMEDIARY_INSTITUTION_COUNTRY_CODE_DERIVED, CD.BENEFICIARY_COUNTRY_CODE, CDF.BENEFICIARY_COUNTRY_CODE AS BENEFICIARY_COUNTRY_DERIVED_FINAL, CD.SENDER_CORRESPONDENT_COUNTRY_CODE, CDF.SENDER_CORRESPONDENT_COUNTRY_CODE AS SENDER_CORRESPONDENT_COUNTRY_CODE_DERIVED, CD.ACCOUNT_WITH_INST_COUNTRY_CODE, CDF.ACCOUNT_WITH_INST_COUNTRY_CODE AS ACCOUNT_WITH_INST_COUNTRY_CODE_DERIVED, CD.RECEIVER_CORRESPONDENT_COUNTRY_CODE, CDF.RECEIVER_CORRESPONDENT_COUNTRY_CODE AS RECEIVER_CORRESPONDENT_COUNTRY_CODE_DERVIED, CD.BENEFICIARY_NAME, CD.BENEFICIARY_ADDRESS, CD.BENEFICIARY_ABA, (CD.BENEFICIARY_BIC)::VARCHAR(30) AS BENEFICIARY_BIC, CD.BENEFICIARY_ID, CD.ORDERING_INSITUTION_ACCOUNT, (CD.ORDERING_INSITUTION_ABA)::VARCHAR(40) AS ORDERING_INSITUTION_ABA, (CD.ORDERING_INSITUTION_BIC)::VARCHAR(30) AS ORDERING_INSITUTION_BIC, CD.ORDERING_INSITUTION_ID, CD.ORDERING_INSITUTION_NAME, CD.ORDERING_INSITUTION_ADDRESS, CD.SENDER_CORRESPONDENT_ACCOUNT, CD.SENDER_CORRESPONDENT_ABA, CD.SENDER_CORRESPONDENT_BIC, CD.SENDER_CORRESPONDENT_ID, CD.SENDER_CORRESPONDENT_NAME, CD.SENDER_CORRESPONDENT_ADDRESS, CD.RECEIVER_CORRESPONDENT_ACCOUNT, CD.RECEIVER_CORRESPONDENT_ABA, CD.RECEIVER_CORRESPONDENT_BIC, CD.RECEIVER_CORRESPONDENT_ID, CD.RECEIVER_CORRESPONDENT_NAME, CD.RECEIVER_CORRESPONDENT_ADDRESS, CD.INTERMEDIARY_INSTITUTION_ACCOUNT, (CD.INTERMEDIARY_INSTITUTION_ABA)::VARCHAR(40) AS INTERMEDIARY_INSTITUTION_ABA, CD.INTERMEDIARY_INSTITUTION_BIC, CD.INTERMEDIARY_INSTITUTION_ID, CD.INTERMEDIARY_INSTITUTION_NAME, CD.INTERMEDIARY_INSTITUTION_ADDRESS, CD.ACCOUNT_WITH_INST_ACCOUNT, (CD.ACCOUNT_WITH_INST_ABA)::VARCHAR(40) AS ACCOUNT_WITH_INST_ABA, (CD.ACCOUNT_WITH_INST_BIC)::VARCHAR(30) AS ACCOUNT_WITH_INST_BIC, CD.ACCOUNT_WITH_INST_ID, CD.ACCOUNT_WITH_INST_NAME, CD.ACCOUNT_WITH_INST_ADDRESS, CD.BENEFICIARY_ACCOUNT, CASE WHEN (CD.CREDIT_CURRENCY = 'CAD'::BPCHAR) THEN CD.CREDIT_AMOUNT ELSE (CD.CREDIT_AMOUNT / E.EXCHANGE_RATE) END AS CREDIT_AMOUNT_IN_CAD, CASE WHEN (CD.CREDIT_CURRENCY = 'CAD'::BPCHAR) THEN '1'::""NUMERIC"" ELSE E.EXCHANGE_RATE END AS CREDIT_EXCHANGE_RATE_USED FROM ((EWP.V_L1_EWP_WIRE_PAYMENT CD JOIN EWP.V_L1_EWP_DERIVED_VALUES CDF ON ((CD.IDP_WAREHOUSE_ID = CDF.IDP_WAREHOUSE_ID))) LEFT JOIN IDP_INTERFACE.V_REF_EXCHANGE_RATE E ON (((CD.CREDIT_CURRENCY = E.TO_CURRENCY_CODE) AND (CASE WHEN (CD.VALUE_DATE NOTNULL) THEN CD.VALUE_DATE WHEN (CD.VALUE_DATE NOTNULL) THEN CD.VALUE_DATE ELSE NULL::DATE END = E.EXCHANGE_RATE_DATE))))); WITH COUNTRY_DERIVED AS (SELECT PODR.V_L1_PODR_WIRE_PAYMENT.IDP_WAREHOUSE_ID, PODR.V_L1_PODR_WIRE_PAYMENT.IDP_AUDIT_ID, PODR.V_L1_PODR_WIRE_PAYMENT.IDP_DATA_DATE, PODR.V_L1_PODR_WIRE_PAYMENT.CM_AML_EXTR_ID, PODR.V_L1_PODR_WIRE_PAYMENT.SOURCE_APPLICATION, PODR.V_L1_PODR_WIRE_PAYMENT.I_O_INDICATOR, PODR.V_L1_PODR_WIRE_PAYMENT.ICN_MAIN, PODR.V_L1_PODR_WIRE_PAYMENT.MSG_TYPE, PODR.V_L1_PODR_WIRE_PAYMENT.BOOK_PAYMENT_INDICATOR, PODR.V_L1_PODR_WIRE_PAYMENT.PASS_THRU_FLAG, PODR.V_L1_PODR_WIRE_PAYMENT.COVER_MESSAGE_INDICATOR, PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATION_DATE, PODR.V_L1_PODR_WIRE_PAYMENT.COMPLETION_DATE, PODR.V_L1_PODR_WIRE_PAYMENT.VALUE_DATE, PODR.V_L1_PODR_WIRE_PAYMENT.TXN_AMOUNT_ORIG, PODR.V_L1_PODR_WIRE_PAYMENT.CURRENCY_CODE_ORIG, PODR.V_L1_PODR_WIRE_PAYMENT.EXCHANGE_RATE, PODR.V_L1_PODR_WIRE_PAYMENT.CREDIT_DEBIT_CODE, PODR.V_L1_PODR_WIRE_PAYMENT.ACCT_SYS_ACCT_SRC_UNIQ_ID, PODR.V_L1_PODR_WIRE_PAYMENT.ACCOUNT_SOURCE_UNIQUE_ID, PODR.V_L1_PODR_WIRE_PAYMENT.SOURCE_SYSTEM_CODE, PODR.V_L1_PODR_WIRE_PAYMENT.SOURCE_TXN_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.TRANSACTION_RELATED_REFERENCE, PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE, PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ACCOUNT_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_BANK_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_BANK_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_BANK_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_BANK_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.BENEFICIARY_BANK_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.SENDING_INSTITUTION_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.SENDING_INSTITUTION_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.SENDING_INSTITUTION_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.SENDING_INSTITUTION_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.SENDING_INSTITUTION_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.ORDERING_INSTITUTION_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.ORDERING_INSTITUTION_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.ORDERING_INSTITUTION_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.ORDERING_INSTITUTION_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.ORDERING_INSTITUTION_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.SENDER_CORR_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.SENDER_CORR_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.SENDER_CORR_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.SENDER_CORR_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.SENDER_CORR_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.RECEIVER_CORR_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.RECEIVER_CORR_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.RECEIVER_CORR_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.RECEIVER_CORR_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.RECEIVER_CORR_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.THIRD_REIMBURSEMENT_INST_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.THIRD_REIMBURSEMENT_INST_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.THIRD_REIMBURSEMENT_INST_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.THIRD_REIMBURSEMENT_INST_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.THIRD_REIMBURSEMENT_INST_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.INTERMEDIARY_INST_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.INTERMEDIARY_INST_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.INTERMEDIARY_INST_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.INTERMEDIARY_INST_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.INTERMEDIARY_INST_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.ACCOUNT_WITH_INST_NAME, PODR.V_L1_PODR_WIRE_PAYMENT.ACCOUNT_WITH_INST_ADDRESS, PODR.V_L1_PODR_WIRE_PAYMENT.ACCOUNT_WITH_INST_COUNTRY, PODR.V_L1_PODR_WIRE_PAYMENT.ACCOUNT_WITH_INST_ACC_NUM, PODR.V_L1_PODR_WIRE_PAYMENT.ACCOUNT_WITH_INST_BIC, PODR.V_L1_PODR_WIRE_PAYMENT.SENDER, PODR.V_L1_PODR_WIRE_PAYMENT.RECEIVER, PODR.V_L1_PODR_WIRE_PAYMENT.TRANS_REF_DESC, PODR.V_L1_PODR_WIRE_PAYMENT.TRANS_REF_DESC_2, PODR.V_L1_PODR_WIRE_PAYMENT.MIR_MOR, CASE WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%AFGHANISTAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AF'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ALBANIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AL'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ALGERIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'DZ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%AMERICAN%SAMOA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AS'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ANDORRA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AD'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ANGOLA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ANGUILLA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AI'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ANTARCTICA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AQ'::""VARCHAR"" WHEN ((PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ANTIGUA&BARBUDA%'::""VARCHAR"", '\'::""VARCHAR"")) OR (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ANTIGUA'::""VARCHAR"", '\'::""VARCHAR""))) THEN 'AG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ARGENTINA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ARMENIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ARUBA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AW'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%AUSTRALIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AU'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%AUSTRIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%AZERBAIJAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AZ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BAHAMAS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BS'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BAHRAIN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BH'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BANGLADESH%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BD'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BARBADOS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BB'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BELARUS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BY'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BELGIUM%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BELIZE%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BZ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BENIN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BJ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BERMUDA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BHUTAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BOLIVIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BOSNIA%HERZEGOVINA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BOTSWANA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BW'::""VARCHAR"" WHEN ((PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BRAZIL%'::""VARCHAR"", '\'::""VARCHAR"")) OR (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BRASIL%'::""VARCHAR"", '\'::""VARCHAR""))) THEN 'BR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BRITISH%INDIAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BRITISH%VIRGIN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'VG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BRUNEI%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BULGARIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BURKINA%FASO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BF'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BURMA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%BURUNDI%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BI'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CAMBODIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KH'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CAMEROON%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CANADA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CAPE%VERDE%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CV'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CAYMAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KY'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CHAD%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'TD'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CHILE%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CL'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CHINA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CHRISTMAS%ISLAND%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CX'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%COCOS%ISLANDS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CC'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%KEELING%ISLANDS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CC'::""VARCHAR"" WHEN ((PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%COLOMBIA%'::""VARCHAR"", '\'::""VARCHAR"")) OR (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%COLUMBIA%'::""VARCHAR"", '\'::""VARCHAR""))) THEN 'CO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%COMOROS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%COOK%ISLANDS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CK'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%COSTA%RICA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CROATIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'HR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CUBA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CU'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CYPRUS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CY'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CZECH%REPUBLIC%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CZ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%CONGO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%DENMARK%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'DK'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%DJIBOUTI%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'DJ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%DOMINICA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'DM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%DOMINICAN%REPUBLIC%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'DO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ECUADOR%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'EC'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%EGYPT%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'EG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%EL%SALVADOR%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'SV'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%EQUATORIAL%GUINEA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GQ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ERITREA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'ER'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ESTONIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'EE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ETHIOPIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'ET'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%FALKLAND%IS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'FK'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%FAROE%IS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'FO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%FIJI%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'FJ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%FINLAND%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'FI'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%FRANCE%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'FR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%FRENCH%POLYNESIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PF'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GABON%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GAMBIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GEORGIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GERMANY%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'DE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GHANA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GH'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GIBRALTAR%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GI'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GREECE%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GREENLAND%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GL'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GRENADA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GD'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GUAM%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GU'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GUATEMALA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GUINEA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GUINEA%BISSAU%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GW'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%GUYANA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'GY'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%HAITI%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'HT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%VATICAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'VA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%HONDURAS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'HN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%HONG%KONG%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'HK'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%HUNGARY%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'HU'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ICELAND%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IS'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%INDIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%INDONESIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'ID'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%IRAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%IRAQ%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IQ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%IRELAND%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ISLE%MAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ISRAEL%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IL'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ITALY%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%IVORY%COAST%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'IVORY COAST'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%JAMAICA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'JM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%JAPAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'JP'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%JERSEY%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'JE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%JORDAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'JO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%KAZAKHSTAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KZ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%KENYA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%KIRIBATI%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KI'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%KUWAIT%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KW'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%KYRGYZSTAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LAOS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LATVIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LV'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LEBANON%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LB'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LESOTHO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LS'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LIBERIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LIBYA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LY'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LIECHTENSTEIN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LI'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LITHUANIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%LUXEMBOURG%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LU'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MACAU%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MACEDONIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MK'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MADAGASCAR%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MALAWI%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MW'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MALAYSIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MY'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MALDIVES%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MV'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MALI%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'ML'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MALTA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MARSHALL%IS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MH'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MAURITANIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MAURITIUS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MU'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MAYOTTE%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'YT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MEXICO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MX'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MICRONESIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'FM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MOLDOVA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MD'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MONACO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MC'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MONGOLIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MONTENEGRO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'ME'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MONTSERRAT%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MS'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MOROCCO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MOZAMBIQUE%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MZ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NAMIBIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NAURU%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NEPAL%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NP'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NETHERLANDS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NL'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NETHERLANDS&ANTILLES%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'AN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NEW%CALEDONIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NC'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NEW%ZEALAND%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NZ'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NICARAGUA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NI'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NIGER%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NIGERIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NIUE%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NU'::""VARCHAR"" WHEN ((PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NORTH%KOREA%'::""VARCHAR"", '\'::""VARCHAR"")) OR (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%KOREA%NORTH%'::""VARCHAR"", '\'::""VARCHAR""))) THEN 'KP'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%MARIANA%IS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MP'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NORWAY%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'NO'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%OMAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'OM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PAKISTAN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PK'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PALAU%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PW'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PANAMA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%NEW%GUINEA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PG'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PARAGUAY%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PY'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PERU%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PE'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PHILIPPINES%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PH'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PITCAIRN&IS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%POLAND%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PL'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PORTUGAL%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PT'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%PUERTO&RICO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PR'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%QATAR%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'QA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%REPUBLIC&CONGO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'CD'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ROMANIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'ROMANIA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%RUSSIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'RU'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%RWANDA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'RW'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%S%BARTHELEMY%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'BL'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%S%HELENA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'SH'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%KITTS%NEVIS%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'KN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%S%LUCIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'LC'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%ST%MARTIN%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'MF'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%S%PIERRE%MIQUELON%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'PM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%SAINT%VINCENT%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'VC'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%SAMOA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'WS'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%SAN&MARINO%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'SM'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%SAO%TOME%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'ST'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%SAUDI%ARABIA%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'SA'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.ORIGINATOR_ADDRESS_LINE ~~ LIKE_ESCAPE('%SENEGAL%'::""VARCHAR"", '\'::""VARCHAR"")) THEN 'SN'::""VARCHAR"" WHEN (PODR.V_L1_PODR_WIRE_PAYMENT.O"; foreach (Match m in Regex.Matches(input, pattern)) { Console.WriteLine("'{0}' found at index {1}.", m.Value, m.Index); } } }

Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for C#, please visit: https://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex(v=vs.110).aspx