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

$re = '/\s((?:\w|_)+\.(?:\w|_)+\.(?:\w|_)+)\s/'; $str = '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'; preg_match_all($re, $str, $matches, PREG_SET_ORDER, 0); // Print the entire match result var_dump($matches);

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 PHP, please visit: http://php.net/manual/en/ref.pcre.php