Query for Supplier details with banks

SELECT         

                aps.vendor_name|| ' ~ '|| ass.vendor_site_code "Payee_Identifier"

                ,accts.ext_bank_account_id "Payee_Bank_Account_Identifier"

                ,bank.party_name "bank_name"

                ,branch.bank_branch_name "Branch_Name"

                ,accts.country_code "account_country_code"

                ,accts.bank_account_name "Account_Name"

                ,accts.bank_account_num "Account_Number"

                ,accts.currency_code "Account_Currency_Code"

                ,NULL "Allow_International_Payments"

                ,uses.start_date "Account_start_date"

                ,uses.end_date "Account_end_date"

                ,accts.iban "IBAN"

                ,accts.check_digits "check_digits"

                ,accts.bank_account_name_alt "Account_Alternate_Name"

                ,accts.bank_account_type "Bank_account_type"

                ,accts.account_suffix "Account_Suffix"

                ,accts.description "DESCRIPTION"

                ,accts.agency_location_code "Agency_Location_Code"

                ,accts.exchange_rate_agreement_num   "Exchange_Rate_Agreement_Number"

                ,accts.exchange_rate_agreement_type "Exchange_Rate_Agreement_Type"

                ,accts.exchange_rate "EXCHANGE_RATE"

                ,accts.secondary_account_reference "SECONDARY_ACCOUNT_REFERENCE"

           FROM  ap_suppliers aps

                ,ap_supplier_sites_all ass

                ,iby_pmt_instr_uses_all uses

                ,iby_external_payees_all payee

                ,iby_ext_bank_accounts accts

                ,hz_parties bank

                ,ce_bank_branches_v branch

          WHERE uses.instrument_type   = 'BANKACCOUNT'

            AND aps.vendor_id          = ass.vendor_id

            AND aps.party_id           = payee.payee_party_id

            AND payee.ext_payee_id     = uses.ext_pmt_party_id

            AND payee.payment_function = 'PAYABLES_DISB'

            AND uses.instrument_id     = accts.ext_bank_account_id

            AND accts.bank_id          = bank.party_id

            --AND accts.bank_id          = bankprofile.party_id

            AND accts.branch_id         = branch.branch_party_id

            AND aps.end_date_active  IS NULL

            AND ass.inactive_date    IS NULL

Bank Accounts APPEND , MICR , End Date using API

AR Cash Management

Description

This blog is used to load Bank and Branch data from the load files. It end date the bank accounts which are invalid. Create new bank account codes to insert and append scenarios. The mandatory validations are handled by the API’s.

-- This API used to create an external bank.

iby_ext_bankacct_pub.create_ext_bank

(

    p_api_version        => g_api_version,

    p_init_msg_list      => fnd_api.g_true,

    p_ext_bank_rec       => Px_extbank_rec_type,

    x_bank_id            => px_bank_id,

    x_return_status      => Px_return_status,

    x_msg_count          => Px_msg_count,

    x_msg_data           => Px_msg_data,

    x_response           => px_response

);

-- This API used to create an external bank branch.

iby_ext_bankacct_pub.create_ext_bank_branch

(

    p_api_version           => g_api_version,

    p_init_msg_list         => fnd_api.g_true,

    p_ext_bank_branch_rec   => px_ext_bank_branch_rec,

    x_branch_id             => px_branch_id,

    x_return_status         => px_return_status,

    x_msg_count             => px_msg_count,

    x_msg_data              => px_msg_data,

    x_response              => px_response

);

-- API to create external bank account

iby_ext_bankacct_pub.create_ext_bank_acct
(
    g_api_version,
    fnd_api.g_true,                --p_init_msg_list,
    px_ext_bank_acct_rec,
    px_acct_id,
    px_return_status,
    px_msg_count,
    px_msg_data,
    px_response
);

-- This API used to set payer assignment.

iby_fndcpt_setup_pub.set_payer_instr_assignment

(

    g_api_version,

    fnd_api.g_true,   -- p_init_msg_list,

    fnd_api.g_true,   -- p_commit,

    px_return_status,

    px_msg_count,

    px_msg_data,

    px_payer,

    px_assignment_attribs,

    px_assign_id,

    px_response

);

-- By

-- Eswaramoorthi M