Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Logan Wacker
    Supplier Bank Account Detail Reporting
    Topic posted November 13, 2018 by Logan WackerSilver Trophy: 7,500+ Points, last edited January 30, 2019, tagged Financials, Payables, Reports 
    1097 Views, 5 Comments
    Title:
    Supplier Bank Account Detail Reporting
    Content:

    Is there a delivered report that could tell me what my active supplier bank account details are? If not, does anyone know the table at which these are stored in? We are implementing electronic payments for AP after an initial AP implementation last year, and would like to validate which suppliers have or don't have bank account details configured.

    Thanks.

    Logan

    Comment

     

    • Alexey Shtrakhov

      hi Logan,

      here is some simple query:

      SELECT iby.ext_bank_account_id,

      iby.BANK_ACCOUNT_NAME,

      iby.bank_account_number as "bank_account_num",

      iby.iban_number as "iban",

      iby.CURRENCY_CODE,

      vends.PRC_BU_ID,

      vends.LOCATION_ID,

      vend.vendor_id,

      vends.vendor_site_id,

      hou.bu_name as operating_unit

      FROM IBY_EXT_BANK_ACCOUNTS_V iby,

      poz_suppliers_v vend,

      POZ_SUPPLIER_SITES_v vends,

      FUN_ALL_BUSINESS_UNITS_V hou,

      FUSION.IBY_PMT_INSTR_USES_ALL ibiu,

      FUSION.IBY_EXTERNAL_PAYEES_ALL ibep

      WHERE vend.PARTY_ID = ibep.PAYEE_PARTY_ID

      AND iby.EXT_BANK_ACCOUNT_ID = ibiu.INSTRUMENT_ID

      AND ibep.EXT_PAYEE_ID = ibiu.EXT_PMT_PARTY_ID

      AND vends.VENDOR_ID = vend.VENDOR_ID

      AND vends.prc_bu_id = hou.bu_id

      AND ibep.PARTY_SITE_ID = vends.party_site_id

      --AND iby.currency_code = 'EUR'

      and sysdate between iby.start_date and iby.end_date

      and sysdate between vends.effective_start_date and vends.effective_end_date

      and sysdate between ibiu.START_DATE and ibiu.END_DATE

       

      ;

    • Logan Wacker

      Thanks all, but one question, this query is only returning a few results, even if I remove the currency restriction on EUR. I'm expect hundreds of rows and I am getting about 20-25. Thoughts on what in the query is limiting my results?

      Thanks

      Logan

    • Alexey Shtrakhov

      On which level you have your bank accounts: Supplier / Address / Site?
      This was my fast query which I had... so I can adjust it for your purposes.

      But I'm thinking that actually you can consider checking on OTBI.
      Looks like you can find same information from this subject area: Supplier - Supplier Real Time
      You will have to check Bank Accounts here as well from same 3 different levels.

      Alexey

    • Logan Wacker

      In one client instance its supplier, in the other its site