Vendor Site – Invoicing & Payments Query

Vendor Site query of invoicing and payment tabs is something you can’t run away in Fusion query. in this post we’ll discuss the query to fetch the details of both the sections. So first thing first and let me give you the invoicing query. This query will get you the payment method and default flag which means you’ll be able to tell which all payment methods are available at a particular vendor site and which of them is default.

Vendor Site Invoicing Query

SELECT ste.vendor_site_code,
pm.payment_method_code,
pm.primary_flag Default_flag
FROM poz_supplier_sites_all_m ste,
iby_ext_party_pmt_mthds pm,
iby_external_payees_all payee
WHERE 1=1
AND payee.supplier_site_id = ste.vendor_site_id
AND pm.ext_pmt_party_id = payee.ext_payee_id
AND ste.vendor_site_code =:vendor_site_code

Here :vendor_site_code is the vendor site code for which you want to fetch the details for.

Payments Query

And here is the query to fetch the bank details like bank name, branch, account number of a supplier site.

select ste.vendor_site_code,
cebranch.bank_name bank_name,
cebranch.bank_number bank_number,
cebranch.bank_branch_name branch_name,
bankacct.bank_account_num
from poz_supplier_sites_all_m ste,
iby_external_payees_all payee,
iby_pmt_instr_uses_all instr_assign,
iby_ext_bank_accounts bankacct,
ce_all_bank_branches_v cebranch
where 1=1
AND payee.supplier_site_Id = ste.VENDOR_SITE_ID
AND instr_assign.ext_pmt_party_id=payee.ext_payee_id
AND bankacct.ext_bank_account_id =instr_assign.instrument_id
AND Bankacct.branch_id=cebranch.branch_party_id(+)
AND instr_assign.instrument_type=’BANKACCOUNT’
AND instr_assign.payment_flow=’DISBURSEMENTS’
AND ste.vendor_site_code = :vendor_site_code

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Related Posts:
Supplier Site Query ( SQL) – General in Fusion
Supplier Details Query in Oracle Fusion – POZ_SUPPLIERS

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com