API to update supplier bank account in r12

Hi Friends, we are going to discuss about the API to update supplier bank account in r12. In this post, we will share the complete script to update the supplier bank account details in r12. If we have to mass update the supplier bank account information's then this api is the best thing we can use to develop the supplier bank account update script. For example , if we have an requirement to mass end the supplier bank account this this api is the best thing we can use to update supplier bank account to put end date. Or we can use this api to change the bank account name or bank account number or bank or branch details or any other bank currency related informations in r12. Oracle has provided the standard api to update supplier bank accounts in r12. Please find below the complete detail about API to update supplier bank account in r12.

API to update supplier bank account in r12

Standard API to update Supplier Bank Accounts

 iby_ext_bankacct_pub.UPDATE_ext_bank_acct

PLSQL Script using the API to update supplier bank account in r12

  DECLARE
         x_bank_rec                iby_ext_bankacct_pub.extbank_rec_type;
         x_bank_id                 NUMBER;
         x_return_status           VARCHAR2 (10);
         x_msg_count               NUMBER;
         x_msg_data                VARCHAR2 (256);
         x_response_rec            iby_fndcpt_common_pub.result_rec_type;
         x_bank_branch_rec         iby_ext_bankacct_pub.extbankbranch_rec_type;
         x_branch_id               NUMBER;
         x_acct_id                 NUMBER;
         x_bank_acct_rec           iby_ext_bankacct_pub.extbankacct_rec_type;
         p_ext_payee_tab           iby_disbursement_setup_pub.external_payee_tab_type;
         x_ext_payee_id_tab        iby_disbursement_setup_pub.ext_payee_id_tab_type;
         x_ext_payee_status_tab    iby_disbursement_setup_pub.ext_payee_create_tab_type;
         l_ext_payee_rec           iby_disbursement_setup_pub.external_payee_rec_type;
         l_ext_payee_id_rec_type   iby_disbursement_setup_pub.ext_payee_id_rec_type;
         p_payee                   iby_disbursement_setup_pub.payeecontext_rec_type;
         p_assignment_attribs      iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
         p_instrument              iby_fndcpt_setup_pub.pmtinstrument_rec_type;
         x_assign_id               NUMBER;
         v_bank_party_id           NUMBER;
         v_branch_party_id         NUMBER;
         v_vendor_id               NUMBER;
         v_vendor_site_id          NUMBER;
         V_OBJECT_VERSION_NUMBER  NUMBER;
         v_party_site_id           NUMBER;
         V_EXT_BANK_ACCOUNT_ID  NUMBER;
         v_bank_creation_flag      VARCHAR2 (40);
         v_error_flag              VARCHAR2 (40);
         v_error_message           VARCHAR2 (4000);
         v_party_id   number;

         CURSOR supplier_bank
         IS
            SELECT ROWID, xrs.*
              FROM XX_SUPP_BANK_STG xrs
             WHERE processed_flag = 'N';
      BEGIN
    fnd_global.apps_initialize(33422,44444,200);  --userid,respid,orgid--

  mo_global.init('SQLAP');

  fnd_client_info.set_org_context(85);

         FOR i IN supplier_bank
         LOOP
            v_bank_creation_flag := 'N';
            v_error_flag := 'N';
            v_error_message := NULL;
            v_bank_party_id:=NULL;
            V_branch_party_id:=NULL;
            v_ext_bank_account_id:=NULL;
            v_vendor_id:=NULL;
            v_party_id:=NULL;
            v_vendor_site_id:=NULL;
            v_party_site_id:=NULL;

            BEGIN
               SELECT bank_party_id
                 INTO v_bank_party_id
                 FROM ce_banks_v
                WHERE bank_name = i.bank_name;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  v_error_flag := 'Y';
                  v_error_message := 'BANK IS NOT EXIST IN THE SYSTEM';
            END;

            BEGIN
               SELECT branch_party_id
                 INTO v_branch_party_id
                 FROM ce_bank_branches_v
                WHERE bank_branch_name = i.branch_name
                  AND bank_name = i.bank_name;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  v_error_flag := 'Y';
                  v_error_message :=
                       v_error_message || 'BRANCH IS NOT EXIST IN THE SYSTEM';
            END;

            BEGIN
               SELECT ext_bank_account_id,OBJECT_VERSION_NUMBER
                 INTO v_ext_bank_account_id,V_OBJECT_VERSION_NUMBER
                 FROM iby_ext_bank_accounts
                WHERE bank_id = v_bank_party_id
                  AND branch_id = v_branch_party_id
                  AND bank_account_num = i.OLD_ACCOUNT_NUMBER;
                  
                  
                  
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
               v_error_flag := 'Y';
                  v_error_message :=
                        v_error_message
                     || 'OLD BANK ACCOUNT NUMBER NOT EXIST IN THE SYSTEM';  
            END;

            BEGIN
               SELECT vendor_id
                 INTO v_vendor_id
                 FROM po_vendors
                WHERE vendor_name = i.vendor_name;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  v_error_flag := 'Y';
                  v_error_message :=
                          v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM';
            END;

            BEGIN
               SELECT party_id
                 INTO v_party_id
                 FROM po_vendors
                WHERE vendor_name =i.vendor_name;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  v_error_flag := 'Y';
                  v_error_message :=
                          v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM';
            END;
            
            IF i.vendor_site_code  IS NOT NULL THEN

            BEGIN
               SELECT vendor_site_id,party_site_id
                 INTO v_vendor_site_id,v_party_site_id
                 FROM ap_supplier_sites_all
                WHERE vendor_site_code = i.vendor_site_code
                  AND vendor_id = v_vendor_id;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  v_error_flag := 'Y';
                  v_error_message :=
                     v_error_message || 'VENDOR SITE NOT EXIST IN THE SYSTEM';
            END;
            
            END IF;

          

            IF v_error_flag = 'N'
            THEN
x_bank_acct_rec.country_code  := I.COUNTRY ;
x_bank_acct_rec.BANK_ACCOUNT_ID:=v_ext_bank_account_id;
x_bank_acct_rec.OBJECT_VERSION_NUMBER:=V_OBJECT_VERSION_NUMBER;
               x_bank_acct_rec.branch_id := v_branch_party_id;
               x_bank_acct_rec.bank_id := v_bank_party_id;
               x_bank_acct_rec.acct_owner_party_id := v_party_id; --supplier party id---
               x_bank_acct_rec.iban := i.iban;
               x_bank_acct_rec.currency := i.currency;
               x_bank_acct_rec.bank_account_name := i.ACCOUNT_NAME;
              x_bank_acct_rec.bank_account_num := i.NEW_ACCOUNT_NUMBER;
               x_msg_count := 0;
               x_msg_data := NULL;
               x_return_status := NULL;
               iby_ext_bankacct_pub.UPDATE_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_return_status          => x_return_status,
                                      x_msg_count              => x_msg_count,
                                      x_msg_data               => x_msg_data,
                                      x_response               => x_response_rec
                                     );

               IF x_return_status = fnd_api.g_ret_sts_success
               THEN
                  DBMS_OUTPUT.put_line ('External bank account UPDATED.');
                  DBMS_OUTPUT.put_line ('x_acct_id' || x_acct_id);
                  DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);
                  DBMS_OUTPUT.put_line ('x_msg_count.' || x_msg_count);
                  DBMS_OUTPUT.put_line ('x_msg_data.' || x_msg_data);
                  
                  
               ELSE
               FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
                  DBMS_OUTPUT.put_line
                                      (   'Creation of BANK ACCCOUNT failed:'
                                       || x_msg_data
                                      );
                                      
                  ROLLBACK;
                  v_error_flag := 'Y';
                  v_error_message :=
                        v_error_message
                     || 'Creation of BANK ACCCOUNT failed:'
                     || x_msg_data;

              

                
               END IF;
               
               END IF;
               
               
IF   V_ERROR_FLAG='Y' THEN
ROLLBACK;
 UPDATE XX_SUPP_BANK_STG
                     SET PROCESSED_FLAG = 'N',
                     ERROR_DESCRIPTION = V_ERROR_MESSAGE
                   WHERE ROWID = I.ROWID;
                   COMMIT;
ELSE
 UPDATE XX_SUPP_BANK_STG
                     SET PROCESSED_FLAG = 'Y'
                   WHERE ROWID = I.ROWID;
                   COMMIT;
END IF;
            
               END LOOP;
              
              
              
               END;

API to update supplier bank account in r12