Oracle Supplier Bank Account Extract Queries

------------------------------------------------------------

       /* Bank Branches Extract Query */

------------------------------------------------------------

-- Bank branches            

SELECT *

  FROM (SELECT abb.bank_branch_id,

               mdrx_get_bank_branch_id (abb.bank_branch_id)

                  bank_branch_id_new,

               abb.bank_name,

               abb.bank_branch_name,

               REPLACE (abb.description, CHR (10), ' ') description,

               abb.address_line1,

               abb.address_line2,

               abb.address_line3,

               abb.city,

               abb.state,

               abb.zip,

               abb.province,

               abb.country,

               abb.area_code,

               abb.phone,

               abb.contact_first_name,

               abb.contact_middle_name,

               abb.contact_last_name,

               abb.contact_prefix,

               abb.contact_title,

               abb.bank_num,

               abb.institution_type,

               abb.address_style,

               abb.bank_number,

               abb.address_line4,

               abb.county,

               abb.eft_user_number,

               abb.eft_swift_code,

               abb.end_date,

               abb.edi_id_number,

               abb.bank_branch_type,

               abb.bank_name_alt,

               abb.bank_branch_name_alt,

               abb.address_lines_alt,

               abb.active_date,

               abb.tp_header_id,

               abb.ece_tp_location_code,

               abb.rfc_identifier,

               abb.bank_admin_email

          FROM ap_bank_branches abb                                     --1723

         WHERE NVL (abb.end_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)

               AND EXISTS

                      (SELECT *

                         FROM ap_bank_accounts_all aba

                        WHERE NVL (aba.inactive_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)

                              AND aba.bank_branch_id = abb.bank_branch_id)) a

 WHERE a.bank_branch_id = a.bank_branch_id_new;

------------------------------------------------------------

       /* Bank Account Extract Query */

------------------------------------------------------------

  SELECT aba.bank_account_id,

         aba.bank_account_name,

         aba.bank_account_num,

         --aba.bank_branch_id,

         bb.bank_num,

         mdrx_get_bank_branch_id (aba.bank_branch_id) bank_branch_id,

         (SELECT hou.NAME

            FROM hr_operating_units hou

           WHERE hou.organization_id = aba.org_id)

            operating_unit_name,

         aba.set_of_books_id,

         aba.currency_code,

         aba.description,

         aba.contact_first_name,

         aba.contact_middle_name,

         aba.contact_last_name,

         aba.contact_prefix,

         aba.contact_title,

         aba.contact_area_code,

         aba.contact_phone,

         aba.bank_account_type,

         aba.attribute_category,

         aba.attribute1,

         aba.check_digits,

         aba.org_id,

         aba.bank_account_name_alt,

         aba.account_holder_name,

         aba.account_holder_name_alt,

         aba.allow_multi_assignments_flag,

         aba.iban_number,

         abau.vendor_id,

         abau.vendor_site_id,

         abau.primary_flag,

         abau.start_date,

         abau.end_date,

         pv.segment1,

         pvs.vendor_site_code

    FROM ap_bank_accounts_all aba,

         ap_bank_account_uses_all abau,

         po_vendors pv,

         po_vendor_sites_all pvs,

         ap_bank_branches bb

   WHERE NVL (aba.inactive_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)

         AND aba.bank_account_id = abau.external_bank_account_id

         AND TRUNC (SYSDATE) BETWEEN NVL (abau.start_date, TRUNC (SYSDATE)) AND NVL (abau.end_date, TRUNC (SYSDATE))

         AND abau.vendor_id = pv.vendor_id

         AND abau.vendor_site_id = pvs.vendor_site_id(+)

         AND aba.bank_branch_id = bb.bank_branch_id

--AND bb.bank_num = '5623532'

ORDER BY bank_branch_id;