Query to get Internal Bank Details in R12

 SELECT items.item_number

      ,itemcats.category_id

  ,itemcats.sequence_number

  ,itemcats.alt_item_cat_code

  ,ect.description

  ,ect.category_name

  FROM egp_item_cat_assignments itemcats

      ,egp_category_sets_b catsets

  ,egp_system_items_b items

  ,inv_org_parameters org

  ,egp_categories_tl ect

 WHERE catsets.category_set_id = itemcats.category_set_id

   AND items.inventory_item_id = itemcats.inventory_item_id

   AND org.organization_id = items.organization_id

   AND itemcats.category_id=ect.category_id

   AND ( ( catsets.control_level = 1

AND itemcats.organization_id = org.master_organization_id 

     )

  OR ( catsets.control_level = 2

AND itemcats.organization_id = org.organization_id

    ) 

)

Query to get Vendor Bank Detail in Oracle Fusion

SELECT DISTINCT psv.vendor_id

      ,psv.vendor_name

  ,pssa.vendor_site_id

      ,accts.ext_bank_account_id

      ,bank.party_name bank_name

      ,branch.bank_branch_name

      ,accts.bank_account_type

      ,accts.country_code

      ,accts.bank_account_name

      ,accts.attribute1 ifsc_code

      ,accts.bank_account_num

      ,accts.currency_code

  FROM poz_suppliers_v psv

      ,poz_supplier_sites_all_m pssa

      ,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 psv.vendor_id      = pssa.vendor_id

   AND psv.party_id       = payee.payee_party_id

   AND payee.ext_payee_id = uses.ext_pmt_party_id

   AND uses.instrument_id = accts.ext_bank_account_id

   AND accts.bank_id      = bank.party_id

   AND accts.branch_id    = branch.branch_party_id

   --AND payee.payment_function = 'PAYABLES_DISB'

Query to get Supplier Site Level Bank Account in Oracle APPS

SELECT bank_account_name

              ,bank_account_num

  FROM iby_ext_bank_accounts

 WHERE ext_bank_account_id IN ( SELECT ext_bank_account_id

                                  FROM iby_account_owners

                                 WHERE account_owner_party_id

                                    IN ( SELECT party_id

      FROM hz_party_sites

   WHERE party_site_name = 'SUPPLIER_SITE_NAME'

)

)

Query to get Operating Unit wise Internal Bank, Bank Branch and Bank Account Information in Oracle Apps

SELECT cba.bank_account_name ,

  cba.bank_account_id,

  cba.bank_account_name_alt,

  cba.bank_account_num ,

  cba.multi_currency_allowed_flag ,

  cba.zero_amount_allowed ,

  cba.account_classification ,

  bb.bank_name ,

  cba.bank_id,

  bb.bank_number,

  bb.bank_branch_type ,

  bb.bank_branch_name ,

  cba.bank_branch_id ,

  bb.bank_branch_number ,

  bb.eft_swift_code ,

  bb.description BANK_DESCRIPTION,

  cba.currency_code ,

  bb.address_line1,

  bb.city,

  bb.county,

  bb.state,

  bb.zip_code,

  bb.country,

  ou.name ,

  gcf.concatenated_segments,

  cba.ap_use_allowed_flag,

  cba.ar_use_allowed_flag,

  cba.xtr_use_allowed_flag,

  cba.pay_use_allowed_flag

FROM apps.ce_bank_accounts cba,

  apps.ce_bank_acct_uses_all bau,

  apps.cefv_bank_branches bb,

  apps.hr_operating_units ou,

  apps.gl_code_combinations_kfv gcf

WHERE cba.bank_account_id         = bau.bank_account_id

AND cba.bank_branch_id            = bb.bank_branch_id

AND ou.organization_id            = bau.org_id

AND cba.asset_code_combination_id = gcf.code_combination_id

AND (cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE))

API to create Bank and Bank Branch in Oracle Apps

IBY_EXT_BANKACCT_PUB.create_ext_bank:

• It is used to create the External Bank, please note that the Bank name and Home Country Name are mandatory for creating an External Bank. 
• Once you create the Bank, Bank Party ID gets created and you can check it from IBY_EXT_BANKS_V view.

        IBY_EXT_BANKACCT_PUB.create_ext_bank (

p_api_version => 1.0 

,p_init_msg_list => FND_API.G_TRUE 

,p_ext_bank_rec => x_bank_rec 

,x_bank_id => x_bank_id 

,x_return_status => x_return_status 

,x_msg_count => x_msg_count 

,x_msg_data => x_msg_data 

,x_response => x_response_rec 

); 

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch: 

• It is used to create a Bank Branch, so that an account could be created in the same branch. 
• Once a Bank Branch is created, a record gets inserted into IBY_EXT_B ANK_BRANCHES_V view. 

    IBY_EXT_BANKACCT_PUB.create_ext_bank_branch(

p_api_version => 1.0 

,p_init_msg_list => FND_API.G_TRUE 

,p_ext_bank_branch_rec => x_bank_branch_rec 

,x_branch_id => x_branch_id 

,x_return_status => x_return_status 

,x_msg_count => x_msg_count 

,x_msg_data => x_msg_data 

,x_response => x_response_rec 

); 

• After the bank and branches are created, the table iby_temp_ext_bank_accounts can be populated to create the bank accounts and associate to the supplier or supplier site 
• Please Note that table must be populated prior to running the supplier interface 
• Below API's are used internally by oracle to create payee and associate bank acc ount to supplier or supplier site.

        IBY_EXT_BANKACCT_PUB.create_ext_bank_acct (

p_api_version => 1.0 

,p_init_msg_list => FND_API.G_TRUE 

,p_ext_bank_acct_rec => x_bank_acct_rec 

,x_acct_id => x_acct_id 

,x_return_status => x_return_status 

,x_msg_count => x_msg_count 

,x_msg_data => x_msg_data 

,x_response => x_response_rec 

); 

IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee (

p_api_version => 1.0

,p_init_msg_list => FND_API.G_TRUE

,p_ext_payee_tab => v_external_payee_tab_type

,x_return_status => v_return_status

,x_msg_count => v_msg_count

,x_msg_data => v_msg_data

,x_ext_payee_id_tab => x_ext_payee_id_tab

,x_ext_payee_status_tab => x_ext_payee_status_tab

);

IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment (

p_api_version => 1.0 

,p_init_msg_list => FND_API.G_TRUE 

,p_payee => x_rec 

,p_assignment_attribs => x_assign 

,x_assign_id => x_assign_id 

,x_return_status => x_return_status 

,x_msg_count => x_msg_count 

,x_msg_data => x_msg_data 

,x_response => x_response_rec

);