Regular Expressions 101

Save & Share

Flavor

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

Function

  • Match
  • Substitution
  • List
  • Unit Tests

Tools

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

Regular Expression
No Match

/
/
g

Test String

Code Generator

Language

Generated Code

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

Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for Rust, please visit: https://docs.rs/regex/latest/regex/