-- supplier level:
SELECT distinct 'Bank Account at Supplier Level',ss.org_id
, sup.vendor_id vendor_id,
sup.party_id party_id,
sup.segment1 vendor_code,
sup.vendor_name vendor_name,
sup.vendor_name vendor_short_name,
sup.vendor_type_lookup_code vendor_type,
/* ss.org_id,
ss.vendor_site_id vendor_site_id,
ss.vendor_id vendor_id,
ss.vendor_site_code vendor_code,
ss.vendor_site_code address_short_name,
ss.address_line1 address_line1,
ss.address_line2 address_line2,
ss.address_line3 address_line3,
ss.address_line4 address_line4,
ss.org_id org_id,
ss.country country,
ss.province province,
ss.city city,
ss.county county,
ss.zip zip,
ss.pay_site_flag pay_site_flag,
ss.purchasing_site_flag purchasing_site_flag,
ss.inactive_date inactive_date,
ss.creation_date creation_date,
ss.created_by created_by,
ss.last_update_date last_update_date,
ss.last_updated_by last_updated_by,
ss.last_update_login last_update_login
, */eba.bank_account_num ,
eba.MASKED_BANK_ACCOUNT_NUM,
eba.BA_MASK_SETTING,
eba.BA_UNMASK_LENGTH,
eba.CURRENCY_CODE,
eba.IBAN,
/*eba.IBAN_HASH1,
eba.IBAN_HASH2,*/
eba.SALT_VERSION,
eba.MASKED_IBAN,
eba.CHECK_DIGITS,
eba.BANK_ACCOUNT_TYPE,
eba.ACCOUNT_CLASSIFICATION,
eba.ACCOUNT_SUFFIX,
eba.AGENCY_LOCATION_CODE,
eba.PAYMENT_FACTOR_FLAG,
eba.FOREIGN_PAYMENT_USE_FLAG,
eba.EXCHANGE_RATE_AGREEMENT_NUM,
eba.EXCHANGE_RATE_AGREEMENT_TYPE,
eba.EXCHANGE_RATE,
eba.HEDGING_CONTRACT_REFERENCE,
eba.SECONDARY_ACCOUNT_REFERENCE,
eba.ENCRYPTED,
eba.START_DATE,
eba.END_DATE,
eba.CREATED_BY,
eba.CREATION_DATE,
eba.LAST_UPDATED_BY,
eba.LAST_UPDATE_DATE,
eba.LAST_UPDATE_LOGIN,/**/
eba.BANK_ACCOUNT_NAME,
eba.BANK_ACCOUNT_NAME_ALT,
ebb.bank_branch_name,ebb.*,
eba.SHORT_ACCT_NAME,
eba.DESCRIPTION,
eba.BANK_ACCOUNT_NUM_ELECTRONIC,
eba.CONTACT_NAME,
eba.CONTACT_PHONE,
eba.CONTACT_FAX,
eba.CONTACT_EMAIL ,
piu.order_of_preference priority
, eba.ext_bank_account_id
FROM ap_suppliers sup,
ap.ap_supplier_sites_all ss
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba,
iby_ext_bank_branches_v ebb
WHERE sup.party_id = epa.payee_party_id
and sup.vendor_id=ss.vendor_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
and epa.PAYMENT_FUNCTION=piu.PAYMENT_FUNCTION
AND piu.instrument_id = eba.ext_bank_account_id
AND epa.supplier_site_id IS NULL
ANd epa.party_site_id IS NULL
and ebb.branch_party_id=eba.branch_id
--and segment1=E501552
and ss.org_id in(110,120) and ss.inactive_date is null
order by ss.org_id, sup.segment1;
--supplier site level:
SELECT 'Bank Account At Supplier Site Level' Bank_Account_Level
, sup.VENDOR_ID,
sup.PARTY_ID,
sup.VENDOR_NAME,
sup.vendor_type_lookup_code,
SS.ORG_ID,
sup.VENDOR_NAME_ALT,
sup.SEGMENT1,
sup.PAY_GROUP_LOOKUP_CODE,
sup.INVOICE_CURRENCY_CODE,
sup.PAYMENT_CURRENCY_CODE,
sup.NUM_1099,
sup.PAYMENT_METHOD_LOOKUP_CODE,
sup.BANK_ACCOUNT_NAME,
sup.BANK_ACCOUNT_NUM,
sup.BANK_NUM,
sup.BANK_ACCOUNT_TYPE,
sup.TERMS_DATE_BASIS,
sup.VAT_REGISTRATION_NUM,
sup.TCA_SYNC_NUM_1099,
sup.TCA_SYNC_VENDOR_NAME,
ss.VENDOR_SITE_ID,
ss.VENDOR_SITE_CODE,
ss.VENDOR_SITE_CODE_ALT,
ss.ADDRESS_LINE1,
ss.ADDRESS_LINES_ALT,
ss.ADDRESS_LINE2,
ss.ADDRESS_LINE3,
ss.CITY,
ss.STATE,
ss.ZIP,
ss.PROVINCE,
ss.COUNTRY,
ss.AREA_CODE,
ss.PHONE,
ss.FAX,
ss.FAX_AREA_CODE,
ss.PAYMENT_METHOD_LOOKUP_CODE,
ss.VAT_CODE,
ss.PAY_GROUP_LOOKUP_CODE,
ss.INVOICE_CURRENCY_CODE,
ss.PAYMENT_CURRENCY_CODE,
ss.COUNTY,
ss.ADDRESS_STYLE,
ss.EMAIL_ADDRESS,
ss.REMITTANCE_EMAIL,
ss.PRIMARY_PAY_SITE_FLAG,
ss.SHIPPING_CONTROL,
ss.SELLING_COMPANY_IDENTIFIER,
ss.GAPLESS_INV_NUM_FLAG,
ss.DUNS_NUMBER,
ss.TOLERANCE_ID,
ss.LOCATION_ID,
ss.PARTY_SITE_ID,
ss.LEGAL_BUSINESS_NAME,
eba.BANK_ACCOUNT_NUM,
eba.MASKED_BANK_ACCOUNT_NUM,
eba.BANK_ACCOUNT_NAME,
eba.BANK_ACCOUNT_NAME_ALT,
ebb.BANK_BRANCH_NAME,
ebb.BRANCH_PARTY_ID,
ebb.HOME_COUNTRY,
ebb.BANK_BRANCH_NAME,
ebb.BANK_BRANCH_NAME_ALT,
ebb.BRANCH_NUMBER,
ebb.EFT_SWIFT_CODE,
ebb.EFT_USER_NUMBER,
ebb.BANK_PARTY_ID,
ebb.CREATED_BY,
eba.BANK_ACCOUNT_NUM_ELECTRONIC,
eba.EXT_BANK_ACCOUNT_ID
FROM ap_suppliers sup
, ap_supplier_sites_all ss
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba,
iby_ext_bank_branches_v ebb
WHERE sup.vendor_id = ss.vendor_id
AND ss.vendor_site_id = epa.supplier_site_id(+)
AND epa.ext_payee_id = piu.ext_pmt_party_id (+)
AND piu.instrument_id = eba.ext_bank_account_id(+)
and ebb.branch_party_id(+)=eba.branch_id
and ss.org_id in(64,2014) and ss.inactive_date is null
-- and piu.payment_function(+)='PAYABLES_DISB'
and epa.PAYMENT_FUNCTION=piu.PAYMENT_FUNCTION (+)
-- and sup.segment1='K68323'
order by ss.org_id