SELECT s.org_id,a.vendor_name SUPPLIER_NAME,
a.segment1 SUPPLIER_NUMBER,
a.vendor_type_lookup_code SUPPLIER_TYPE,
s.invoice_currency_code CURRENCY_CODE,
s.vat_code TAX_RATE_CODE,
s.Vat_Registration_Num TAX_REGISTRATION_NUMBER,
rtv.name PAYMENT_TERM,
s.phone TELEPHONE_NUMBER,
s.fax FAX_NUMBER,
s.vendor_site_code SUPPLIER_SITE_CODE,
hl.address1 ADDRESS_LINE1,
hl.address2 ADDRESS_LINE2,
hl.address3 ADDRESS_LINE3,
hl.address4 ADDRESS_LINE4,
hl.postal_code POSTAL_CODE ,
hl.city CITY,
hl.county,
hl.country,
acc.country_code BANK_COUNTRY,
ieb.bank_name BANK_NAME,
iebb.Bank_Branch_Name BANK_BRANCH_NAME,
--SORT_CODE
acc.bank_account_num,
acc.bank_account_name,
acc.iban IBAN_NUMBER,
iebb.EFT_Swift_Code BIC_NUMBER,
ipm.payment_method_name PAYMENT_METHOD,
flvv.MEANING PAY_GROUP,
gcc_lia.concatenated_segments LIABILITY_ACCOUNT,
gcc_pre.concatenated_segments PREPAYMENT_ACCOUNT,
a.creation_date
FROM iby_external_payees_all pay,
iby_pmt_instr_uses_all uses,
ap_supplier_sites_all s,
ap_suppliers a,
iby_ext_bank_accounts acc,
iby_ext_bank_branches_v iebb,
ap_terms_vl rtv,
hz_party_sites hps,
HZ_LOCATIONS hl,
IBY_EXT_BANKS_V ieb,
fnd_lookup_values_vl flvv,
gl_code_combinations_kfv gcc_lia,
gl_code_combinations_kfv gcc_pre,
iby.iby_ext_party_pmt_mthds ieppm,
iby_payment_methods_vl ipm
WHERE a.vendor_id = s.vendor_id(+)
AND nvl(s.inactive_date, SYSDATE) >
= SYSDATE
AND pay.supplier_site_id(+) = s.vendor_site_id
AND uses.ext_pmt_party_id(+) = pay.ext_payee_id
AND uses.instrument_id = acc.ext_bank_account_id(+)
AND SYSDATE BETWEEN nvl(uses.start_date, SYSDATE) AND
nvl(uses.end_date, SYSDATE + 1)
AND iebb.branch_party_id(+) = acc.branch_id
and s.Terms_Id = rtv.term_id(+)
and s.party_site_id = hps.party_site_id
and hps.location_id = hl.location_id
and acc.bank_id = ieb.bank_party_id(+)
and s.pay_group_lookup_code = flvv.LOOKUP_CODE(+)
and nvl(flvv.lookup_type,'PAY GROUP') = 'PAY GROUP'
and s.Accts_Pay_Code_Combination_Id = gcc_lia.code_combination_id(+)
and s.prepay_code_combination_id = gcc_pre.code_combination_id(+)
--and pay.party_site_id(+) = s.party_site_id
--AND pay.payee_party_id(+) = a.party_id
AND pay.ext_payee_id = ieppm.ext_pmt_party_id(+)
AND ieppm.payment_method_code = ipm.payment_method_code(+)
and s.org_id = 181
;
--
--and a.segment1 = 'HUDS001'