Saturday, July 15, 2017

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect