Monday, 7 July 2014

Supplier Sites Bank Query

The following query gives you the links required for matching a Bank Account to its Supplier Site Record:

/* Formatted on 7/7/2014 5:02:33 PM (QP5 v5.115.810.9015) */
SELECT party_supp.party_name "supplier_name",
       aps.segment1 "supplier_number",
       ass.vendor_site_code "supplier_site",
       party_bank.party_name bank_name,
       branch_prof.bank_or_branch_number bank_number,
       party_branch.party_name branch_name,
       branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp,
     ap_suppliers aps,
     hz_party_sites site_supp,
     ap_supplier_sites_all ass,
     iby_external_payees_all iep,
     iby_pmt_instr_uses_all ipi,
     iby_ext_bank_accounts ieb,
     hz_parties party_bank,
     hz_parties party_branch,
     hz_organization_profiles bank_prof,
     hz_organization_profiles branch_prof
WHERE     party_supp.party_id = aps.party_id
      AND party_supp.party_id = site_supp.party_id
      AND site_supp.party_site_id = ass.party_site_id
      AND ass.vendor_id = aps.vendor_id
      AND iep.payee_party_id = party_supp.party_id
      AND iep.party_site_id = site_supp.party_site_id
      AND iep.supplier_site_id = ass.vendor_site_id
      AND iep.ext_payee_id = ipi.ext_pmt_party_id
      AND ipi.instrument_id = ieb.ext_bank_account_id
      AND ieb.bank_id = party_bank.party_id
      AND ieb.bank_id = party_branch.party_id
      AND party_branch.party_id = branch_prof.party_id
      AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name, ass.vendor_site_code

1 comment:

  1. Hey, what kind of anti-spam plugin do you use for your blog.*`.`’ Technical Compliance Audits