Regular Expressions 101

Save & Share

Regex Version: ver. 1
Fork Regex
ctrl+s
Go to community entry
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

Code Generator
Regex Debugger
Export Matches
Benchmark Regex
Regular Expression
No Match

Test String

Code Generator

Language

Generated Code

package main

import (
    "regexp"
    "fmt"
)

func main() {
    var re = regexp.MustCompile(`\s((?:\w|_)+\.(?:\w|_)+\.(?:\w|_)+)\s`)
    var 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`
    
    for i, match := range re.FindAllString(str, -1) {
        fmt.Println(match, "found at index", i)
    }
}
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 Golang, please visit: https://golang.org/pkg/regexp/
Regular Expressions 101

Save & Share

Flavor

Function

Tools

Explanation

Match Information

Quick Reference

Regular Expression
No Match

Test String

Code Generator

Language

Generated Code

Save & Share

Flavor

Function

Tools

Explanation

Match Information

Quick Reference

Regular ExpressionNo Match

Test String

Regular Expression
No Match
