Supplier and Supplier Site Level External Bank Queries in R12

— Supplier Level External Bank
SELECT
hzp.party_name “VENDOR NAME”,
aps.segment1 “VENDOR NUMBER”,
ieb.bank_account_num “ACCOUNT NUMBER”,
ieb.bank_account_name “ACCOUNT NAME”,
ieb.bank_account_type “Bank Account Type”,
ieb.ext_bank_account_id,
ieb.bank_account_num,
ieb.iban ext_bank_account_iban_number,
ieb.bank_account_name,
ieb.bank_account_name_alt,
ieb.bank_account_num_electronic,
ieb.end_date bank_account_end_date,
aps.party_id,
aps.vendor_name,
NULL,
NULL,
NULL,
ext_branch.bank_branch_name,
ext_branch.bank_branch_name_alt,
ext_branch.branch_number,
ext_branch.branch_party_id,
NULL,
NULL,
NULL,
ext_bank.bank_name_alt,
ext_bank.bank_name,
ext_bank.bank_number,
ipi.start_date supp_bank_ass_start_date,
ipi.end_date supp_bank_ass_end_date
FROM
apps.hz_parties hzp,
apps.ap_suppliers aps,
apps.iby_external_payees_all iep,
apps.iby_pmt_instr_uses_all ipi,
apps.iby_ext_bank_accounts ieb,
iby_ext_banks_v ext_bank,
iby_ext_bank_branches_v ext_branch
WHERE
hzp.party_id = aps.party_id
AND iep.payee_party_id = hzp.party_id
AND ieb.bank_id = ext_bank.bank_party_id (+)
AND ieb.branch_id = ext_branch.branch_party_id (+)
AND ipi.instrument_id = ieb.ext_bank_account_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
–AND aps.segment1 = ‘3811’

 

— Supplier Site Level External Bank
SELECT
hzp.party_name “VENDOR NAME”,
aps.segment1 “VENDOR NUMBER”,
assa.vendor_site_code “SITE CODE”,
ieb.bank_account_num “ACCOUNT NUMBER”,
ieb.bank_account_name “ACCOUNT NAME”,
ieb.bank_account_type “Bank Account Type”,
ieb.ext_bank_account_id,
ieb.bank_account_num,
ieb.iban ext_bank_account_iban_number,
ieb.bank_account_name,
ieb.bank_account_name_alt,
ieb.bank_account_num_electronic,
ieb.end_date bank_account_end_date,
aps.party_id,
aps.vendor_name,
NULL,
NULL,
NULL,
ext_branch.bank_branch_name,
ext_branch.bank_branch_name_alt,
ext_branch.branch_number,
ext_branch.branch_party_id,
NULL,
NULL,
NULL,
ext_bank.bank_name_alt,
ext_bank.bank_name,
ext_bank.bank_number,
ipi.start_date supp_bank_ass_start_date,
ipi.end_date supp_bank_ass_end_date
FROM
apps.hz_parties hzp,
apps.ap_suppliers aps,
apps.hz_party_sites site_supp,
apps.ap_supplier_sites_all assa,
apps.iby_external_payees_all iep,
apps.iby_pmt_instr_uses_all ipi,
apps.iby_ext_bank_accounts ieb,
iby_ext_banks_v ext_bank,
iby_ext_bank_branches_v ext_branch
WHERE
hzp.party_id = aps.party_id
AND hzp.party_id = site_supp.party_id
AND site_supp.party_site_id = assa.party_site_id
AND assa.vendor_id = aps.vendor_id
AND iep.payee_party_id = hzp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = assa.vendor_site_id
AND ieb.bank_id = ext_bank.bank_party_id
AND ieb.branch_id = ext_branch.branch_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
— AND aps.segment1 = ‘1311’
AND assa.org_id = 101

in case of supplier site level bank iby_external_payees_all will have link through supplier site id.

select * from apps.iby_pmt_instr_uses_all
where ext_pmt_party_id = 11402

select * from iby_external_payees_all
where ext_payee_id = 11402

select * from hz_parties
where party_id = 448346

select * from ap_suppliers
where party_id = 448346

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s