|
SELECT a.scr_id , a.scr_seq_num , a.vndr_loc , c.effdt , a.scr_acct_seq_nbr , a.scr_effdt , a.setid , a.vendor_id , a.bank_acct_seq_nbr , a.scr_chg_eff_when , a.scr_chng_type , (CASE WHEN a.descr <> b.descr THEN a.descr ELSE c.descr END) , (CASE WHEN a.default_ind <> b.default_ind THEN a.default_ind ELSE c.default_ind END) , (CASE WHEN a.country <> b.country THEN a.country ELSE c.country END) , (CASE WHEN a.address1 <> b.address1 THEN a.address1 ELSE c.address1 END) , (CASE WHEN a.address2 <> b.address2 THEN a.address2 ELSE c.address2 END) , (CASE WHEN a.address3 <> b.address3 THEN a.address3 ELSE c.address3 END) , (CASE WHEN a.address4 <> b.address4 THEN a.address4 ELSE c.address4 END) , (CASE WHEN a.city <> b.city THEN a.city ELSE c.city END) , (CASE WHEN a.num1 <> b.num1 THEN a.num1 ELSE c.num1 END) , (CASE WHEN a.num2 <> b.num2 THEN a.num2 ELSE c.num2 END) , (CASE WHEN a.house_type <> b.house_type THEN a.house_type ELSE c.house_type END) , (CASE WHEN a.addr_field1 <> b.addr_field1 THEN a.addr_field1 ELSE c.addr_field1 END) , (CASE WHEN a.addr_field2 <> b.addr_field2 THEN a.addr_field2 ELSE c.addr_field2 END) , (CASE WHEN a.addr_field3 <> b.addr_field3 THEN a.addr_field3 ELSE c.addr_field3 END) , (CASE WHEN a.county <> b.county THEN a.county ELSE c.county END) , (CASE WHEN a.state <> b.state THEN a.state ELSE c.state END) , (CASE WHEN a.postal <> b.postal THEN a.postal ELSE c.postal END) , (CASE WHEN a.geo_code <> b.geo_code THEN a.geo_code ELSE c.geo_code END) , (CASE WHEN a.in_city_limit <> b.in_city_limit THEN a.in_city_limit ELSE c.in_city_limit END) , (CASE WHEN a.bank_id_qual <> b.bank_id_qual THEN a.bank_id_qual ELSE c.bank_id_qual END) , (CASE WHEN a.bnk_id_nbr <> b.bnk_id_nbr THEN a.bnk_id_nbr ELSE c.bnk_id_nbr END) , (CASE WHEN a.branch_id <> b.branch_id THEN a.branch_id ELSE c.branch_id END) , (CASE WHEN a.bank_acct_type <> b.bank_acct_type THEN a.bank_acct_type ELSE c.bank_acct_type END) , (CASE WHEN a.bank_account_num <> b.bank_account_num THEN a.bank_account_num ELSE c.bank_account_num END) , (CASE WHEN a.check_digit <> b.check_digit THEN a.check_digit ELSE c.check_digit END) , (CASE WHEN a.dfi_id_qual <> b.dfi_id_qual THEN a.dfi_id_qual ELSE c.dfi_id_qual END) , (CASE WHEN a.dfi_id_num <> b.dfi_id_num THEN a.dfi_id_num ELSE c.dfi_id_num END) , (CASE WHEN a.beneficiary_bank <> b.beneficiary_bank THEN a.beneficiary_bank ELSE c.beneficiary_bank END) , (CASE WHEN a.beneficiary_bnk_ac <> b.beneficiary_bnk_ac THEN a.beneficiary_bnk_ac ELSE c.beneficiary_bnk_ac END) , (CASE WHEN a.benef_branch <> b.benef_branch THEN a.benef_branch ELSE c.benef_branch END) , (CASE WHEN a.benef_branch_ac <> b.benef_branch_ac THEN a.benef_branch_ac ELSE c.benef_branch_ac END) , (CASE WHEN a.country_code <> b.country_code THEN a.country_code ELSE c.country_code END) , (CASE WHEN a.phone <> b.phone THEN a.phone ELSE c.phone END) , (CASE WHEN a.extension <> b.extension THEN a.extension ELSE c.extension END) , (CASE WHEN a.fax <> b.fax THEN a.fax ELSE c.fax END) , (CASE WHEN a.iban_check_digit <> b.iban_check_digit THEN a.iban_check_digit ELSE c.iban_check_digit END) , (CASE WHEN a.iban_id <> b.iban_id THEN a.iban_id ELSE c.iban_id END) , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) , a.scr_remove FROM ps_scr_bnk_act_dft a , ps_scr_bnk_act_mst b , PS_VNDR_BANK_ACCT c WHERE a.scr_id = b.scr_id AND a.scr_seq_num = b.scr_seq_num AND a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.effdt = b.effdt AND a.VNDR_LOC = b.vndr_loc AND a.scr_chng_type = b.scr_chng_type AND a.bank_acct_seq_nbr = b.bank_acct_seq_nbr AND a.scr_chng_type IN ( 'U' ,'D') AND b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.VNDR_LOC = c.vndr_loc AND b.bank_acct_seq_nbr = c.bank_acct_seq_nbr AND %EffdtCheck(VNDR_BANK_ACCT d, c, %currentdatein) UNION SELECT a.scr_id , a.scr_seq_num , a.vndr_loc , a.effdt , a.scr_acct_seq_nbr , a.scr_effdt , a.setid , a.vendor_id , a.bank_acct_seq_nbr , a.scr_chg_eff_when , a.scr_chng_type , a.descr , a.default_ind , a.country , a.address1 , a.address2 , a.address3 , a.address4 , a.city , a.num1 , a.num2 , a.house_type , a.addr_field1 , a.addr_field2 , a.addr_field3 , a.county , a.state , a.postal , a.geo_code , a.in_city_limit , a.bank_id_qual , a.bnk_id_nbr , a.branch_id , a.bank_acct_type , a.bank_account_num , a.check_digit , a.dfi_id_qual , a.dfi_id_num , a.beneficiary_bank , a.beneficiary_bnk_ac , a.benef_branch , a.benef_branch_ac , a.country_code , a.phone , a.extension , a.fax , a.iban_check_digit , a.iban_id , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) , a.scr_remove FROM ps_scr_bnk_act_dft a WHERE a.scr_chng_type = 'A' AND a.msg_severity <> 'E'
|
SCR_ID