Vendor Site – Invoicing & Payments Query

 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