Updating External Bank Account Details

Update External Bank Account Details: In following example we are updating bank Account Name and bank account number.

Replace local variables l_vendor_id,l_ext_bank_account_id, l_bank_id and l_branch_id with your id's


CREATE OR REPLACE PROCEDURE xx_update_bank_detail
IS
   p_api_version         NUMBER                                    := 1.0;
   p_init_msg_list       VARCHAR2 (1)                              := 'F';
   x_return_status       VARCHAR2 (2000);
   x_msg_count           NUMBER (5);
   x_msg_data            VARCHAR2 (2000);
   x_response            iby_fndcpt_common_pub.result_rec_type;
   p_ext_bank_acct_rec   iby_ext_bankacct_pub.extbankacct_rec_type; 
   p_count               NUMBER;
BEGIN
-- In this update bank account of a supplier has been modified .
   p_ext_bank_acct_rec.object_version_number := 1.0;
   p_ext_bank_acct_rec.acct_owner_party_id := l_vendor_id;
   p_ext_bank_acct_rec.bank_account_id := l_ext_bank_account_id;
   p_ext_bank_acct_rec.bank_id := l_bank_id;
   p_ext_bank_acct_rec.branch_id := l_branch_id;

-- Data to be Updated
/*
Use following constant if need to update with Null or blank.
FND_API.G_MISS_NUM for NUMBER
FND_API.G_MISS_CHAR for VARCHAR2 
FND_API.G_MISS_DATE for DATE
*/
   p_ext_bank_acct_rec.bank_account_name := 'YOUR NEW BANK ACCOUNT NAME';

-- to update bank_account_num with blank then use FND_API.G_MISS_NUM instead of NULL or '' 

   p_ext_bank_acct_rec.bank_account_num := 123456;


   iby_ext_bankacct_pub.update_ext_bank_acct
                                 (p_api_version            => p_api_version,
                                  p_init_msg_list          => p_init_msg_list,
                                  p_ext_bank_acct_rec      => p_ext_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
                                 );

   IF x_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line ('Bank detail Updated.');
      COMMIT;
   ELSE
      IF x_msg_count = 1
      THEN
         DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
      ELSIF x_msg_count > 1
      THEN
         LOOP
            p_count := p_count + 1;
            x_msg_data :=
                        fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

            IF x_msg_data IS NULL
            THEN
               EXIT;
            END IF;

            DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
         END LOOP;
      END IF;
   END IF;
END;


Following query can be used to get 

Oracle Applications : Create Bank , Bank Branch, Supplier Bank Account and Account - Supplier Assignment using API

Using API to create Bank its Branch, supplier account and account linking to supplier.

The approach is to call main procedure on the package xx_create_bank_branch_acc.main and pass required parameter like Supplier name, Bank Name, Branch Name and Account number of Supplier.

In first process Package will check the existence of bank, if not exist then it will call function to create Bank, then main will check for Bank Branch, if not exist then create it. Now we will create Bank Account with name of Supplier and then on success call API to Assign Account with Supplier.

create or replace package xx_create_bank_branch_acc is

  function create_bank(p_bank_name varchar2) return number;

  Function create_bank_branch(p_bank_id number, p_bank_branch_name varchar2) return number;

  function create_bank_account(p_supplier_name     varchar2,

                               p_bank_id           number,

                               p_bank_branch_id    number,

                               p_bank_account_name varchar2,

                               p_bank_account_no   number) return number;

  procedure main(p_Supplier_Name varchar2, p_bank_name varchar2, p_bank_branch_name varchar2, p_account_number number);

end xx_create_bank_branch_acc;

/

create or replace package body xx_create_bank_branch_acc is

  function create_bank(p_bank_name varchar2) return number is

    lr_bank_type_rec iby_ext_bankacct_pub.extbank_rec_type;

    lrx_response     IBY_FNDCPT_COMMON_PUB.Result_rec_type;

    lnx_bank_id      NUMBER;

    lcx_bnk_status   VARCHAR2(10);

    lnx_bnk_msg_cnt  NUMBER;

    lcx_bnk_msg_data VARCHAR2(1000);

  

  begin

    lr_bank_type_rec.institution_type := 'BANK';

    lr_bank_type_rec.bank_name        := p_bank_name;

    lr_bank_type_rec.country_code     := 'US';

  

    lcx_bnk_msg_data := NULL;

    iby_ext_bankacct_pub.create_ext_bank(p_api_version   => 1.0,

                                         p_init_msg_list => FND_API.G_FALSE,

                                         p_ext_bank_rec  => lr_bank_type_rec,

                                         x_bank_id       => lnx_bank_id,

                                         x_return_status => lcx_bnk_status,

                                         x_msg_count     => lnx_bnk_msg_cnt,

                                         x_msg_data      => lcx_bnk_msg_data,

                                         x_response      => lrx_response);

    if lcx_bnk_status = 'S' then

      commit;

      DBMS_OUTPUT.put_line('BANK CREATED : ' || lnx_bank_id);

    

    else

    

      IF lnx_bnk_msg_cnt > 1 THEN

        dbms_output.put_line('Error while creating Bank.');

        FOR I IN 1 .. lnx_bnk_msg_cnt LOOP

          dbms_output.put_line(I || '.' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE), 1, 255));

        END LOOP;

      END IF;

    

    end if;

  

    return lnx_bank_id;

  end create_bank;

  function create_bank_branch(p_bank_id number, p_bank_branch_name varchar2) return number is

  

    lr_bank_branch_type_rec iby_ext_bankacct_pub.extbankbranch_rec_type; -- for bank branch creation --

    lrx_response            IBY_FNDCPT_COMMON_PUB.Result_rec_type;

  

    lnx_branch_id number;

    lcx_status    VARCHAR2(10);

    lnx_msg_cnt   NUMBER;

    lcx_msg_data  VARCHAR2(1000);

  

  begin

    lcx_status   := NULL;

    lcx_msg_data := NULL;

    lnx_msg_cnt  := NULL;

  

    --******** Creating Bank Branch ****************

    lr_bank_branch_type_rec.bank_party_id := p_bank_id;

  

    lr_bank_branch_type_rec.branch_name := p_bank_branch_name;

    lr_bank_branch_type_rec.branch_type := 'OTHER';

  

    iby_ext_bankacct_pub.CREATE_EXT_BANK_BRANCH(p_api_version         => 1.0,

                                                p_init_msg_list       => 'T',

                                                p_ext_bank_branch_rec => lr_bank_branch_type_rec,

                                                x_branch_id           => lnx_branch_id,

                                                x_return_status       => lcx_status,

                                                x_msg_count           => lnx_msg_cnt,

                                                x_msg_data            => lcx_msg_data,

                                                x_response            => lrx_response);

    if lcx_status = 'S' then

      commit;

      apps.fnd_file.put_line(apps.fnd_file.log, 'Branch ID     : ' || lnx_branch_id);

    

    else

    

      IF lnx_msg_cnt > 1 THEN

        dbms_output.put_line('Error while creating bank branch.');

        FOR I IN 1 .. lnx_msg_cnt LOOP

          dbms_output.put_line(I || '.' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE), 1, 255));

        END LOOP;

      END IF;

    end if;

  

    DBMS_OUTPUT.put_line('BANK BRANCH CREATED : ' || lnx_branch_id);

    return lnx_branch_id;

  end;

  function create_bank_account(p_supplier_name     varchar2,

                               p_bank_id           number,

                               p_bank_branch_id    number,

                               p_bank_account_name varchar2,

                               p_bank_account_no   number) return number is

    lr_extbankacct_rec_type        apps.iby_ext_bankacct_pub.extbankacct_rec_type;

    x_response                     IBY_FNDCPT_COMMON_PUB.Result_rec_type;

    x_response2                    IBY_FNDCPT_COMMON_PUB.Result_rec_type;

    lr_payeecontext_rec_type       apps.iby_disbursement_setup_pub.payeecontext_rec_type;

    lr_pmtinstrument_rec_type      apps.iby_fndcpt_setup_pub.pmtinstrument_rec_type;

    lr_pmtinstrassignment_rec_type apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;

  

    l_supp_vendor_id number;

    l_supp_party_id  number;

    gc_ret_status    char(1);

  

    x_acct_id    number;

    x_assign_id  number;

    x_msg_count  number;

    x_msg_count2 number;

    x_msg_data1  varchar2(1000);

    x_msg_data2  varchar2(1000);

  begin

    SELECT vendor_id, party_id

      into l_supp_vendor_id, l_supp_party_id

      FROM apps.po_vendors

     WHERE UPPER(vendor_name) = UPPER(p_supplier_name);

  

    lr_ExtBankAcct_rec_type.country_code        := 'US';

    lr_ExtBankAcct_rec_type.branch_id           := p_bank_branch_id;

    lr_ExtBankAcct_rec_type.bank_id             := p_bank_id;

    lr_ExtBankAcct_rec_type.acct_owner_party_id := l_supp_party_id;

    lr_ExtBankAcct_rec_type.bank_account_name   := p_bank_account_name;

    lr_ExtBankAcct_rec_type.bank_account_num    := p_bank_account_no;

    lr_ExtBankAcct_rec_type.currency            := 'USD';

    lr_ExtBankAcct_rec_type.start_date          := sysdate;

  

    gc_ret_status := 'X';

    x_acct_id     := NULL;

    x_msg_count   := 0;

    x_msg_data1   := NULL;

    x_msg_data2   := null;

    apps.iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version       => 1,

                                                   p_init_msg_list     => apps.fnd_api.g_true,

                                                   p_ext_bank_acct_rec => lr_extbankacct_rec_type,

                                                   x_acct_id           => x_acct_id,

                                                   x_return_status     => gc_ret_status,

                                                   x_msg_count         => x_msg_count,

                                                   x_msg_data          => x_msg_data1,

                                                   x_response          => x_response);

  

    --*

    if gc_ret_status = 'S' then

    

      lr_PayeeContext_rec_type.Party_Id         := l_supp_party_id;

      lr_PayeeContext_rec_type.Payment_Function := 'PAYABLES_DISB';

      lr_PmtInstrument_rec_type.Instrument_Id   := x_acct_id;

      lr_PmtInstrument_rec_type.Instrument_Type := 'BANKACCOUNT';

      lr_PmtInstrAssignment_rec_type.start_date := SYSDATE;

      lr_PmtInstrAssignment_rec_type.Instrument := lr_PmtInstrument_rec_type;

    

      BEGIN

        x_msg_data2  := NULL;

        x_assign_id  := NULL;

        x_msg_count2 := NULL;

        apps.iby_disbursement_setup_pub.set_payee_instr_assignment(p_api_version        => 1.0,

                                                                   p_init_msg_list      => apps.fnd_api.g_false,

                                                                   p_commit             => apps.fnd_api.g_true,

                                                                   x_return_status      => gc_ret_status,

                                                                   x_msg_count          => x_msg_count2,

                                                                   x_msg_data           => x_msg_data2,

                                                                   p_payee              => lr_payeecontext_rec_type,

                                                                   p_assignment_attribs => lr_pmtinstrassignment_rec_type,

                                                                   x_assign_id          => x_assign_id,

                                                                   x_response           => x_response2);

        if gc_ret_status = 'S' then

          commit;

          apps.fnd_file.put_line(apps.fnd_file.log, 'AssignID     : ' || x_assign_id);

        

        else

        

          IF x_msg_count2 > 1 THEN

            dbms_output.put_line('Error while assingning Account to supplier.');

            FOR I IN 1 .. x_msg_count2 LOOP

              dbms_output.put_line(I || '.' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE), 1, 255));

            END LOOP;

          END IF;

        end if;

      

        apps.fnd_file.put_line(apps.fnd_file.log, 'AssignID     : ' || x_assign_id);

      end;

    else

     

      

        IF x_msg_count > 1 THEN

          dbms_output.put_line('Error while supplier account.');

          FOR I IN 1 .. x_msg_count LOOP

            dbms_output.put_line(I || '.' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE), 1, 255));

          END LOOP;

        END IF;

      end if;

    return x_acct_id;

  end;

  procedure main(p_Supplier_Name varchar2, p_bank_name varchar2, p_bank_branch_name varchar2, p_account_number number) is

    l_bank_id        number;

    l_bank_branch_id number;

    l_account_id     number;

  begin

    begin

      SELECT hz_hp.party_id

        INTO l_bank_id

        FROM HZ_PARTIES hz_hp, HZ_ORGANIZATION_PROFILES hz_org, HZ_CODE_ASSIGNMENTS hz_ca

       WHERE hz_ca.owner_table_id = hz_hp.party_id

         AND hz_ca.owner_table_name = 'HZ_PARTIES'

         AND hz_ca.class_category = 'BANK_INSTITUTION_TYPE'

         AND hz_ca.class_code = 'BANK'

         AND hz_hp.party_id = hz_org.party_id

         AND SYSDATE BETWEEN TRUNC(hz_org.effective_start_date) AND NVL(TRUNC(hz_org.effective_end_date), SYSDATE + 1)

         AND UPPER(hz_org.home_country) = UPPER('US')

         AND UPPER(hz_hp.party_name) = UPPER(p_Supplier_Name);

    exception

      when no_data_found then

        l_bank_id := create_bank(p_bank_name);

    end;

    begin

      -- branch

      SELECT hz_branch.party_id

        INTO l_bank_branch_id

        FROM HZ_PARTIES hz_branch, HZ_RELATIONSHIPS hz_rel, HZ_CODE_ASSIGNMENTS hz_branchca

       WHERE hz_branchca.owner_table_name = 'HZ_PARTIES'

         AND hz_branchca.owner_table_id = hz_branch.party_id

         AND hz_branchca.class_category = 'BANK_INSTITUTION_TYPE'

         AND hz_branchca.class_code = 'BANK_BRANCH'

         AND NVL(hz_branchca.status, 'A') = 'A'

         AND hz_rel.object_id = l_bank_id

         AND hz_branch.party_id = hz_rel.subject_id

         AND hz_rel.relationship_type = 'BANK_AND_BRANCH'

         AND hz_rel.relationship_code = 'BRANCH_OF'

         AND hz_rel.status = 'A'

         AND hz_rel.subject_table_name = 'HZ_PARTIES'

         AND hz_rel.subject_type = 'ORGANIZATION'

         AND hz_rel.object_table_name = 'HZ_PARTIES'

         AND hz_rel.object_type = 'ORGANIZATION'

         AND UPPER(hz_branch.party_name) = UPPER(p_bank_branch_name);

    exception

      when no_data_found then

        l_bank_branch_id := create_bank_branch(l_bank_id, p_bank_branch_name);

    end; -- branch

  

    l_account_id := create_bank_account(p_Supplier_Name,

                                        l_bank_id,

                                        l_bank_branch_id,

                                        p_Supplier_Name, --p_bank_account_name 

                                        p_account_number);

  

    dbms_output.put_line('Bank Account Created with AccountID - ' || l_account_id);

  end;

end xx_create_bank_branch_acc;

/

TAGS---------------------------------------------------------------------------------

Oracle apps create bank API,

Oracle apps create bank branch API,

Oracle apps bank account API,

Oracle apps supplier account API,

Oracle ebs create bank API,

Oracle ebs create bank branch API,

Oracle ebs bank account API,

Oracle ebs supplier account API,

bank API,

branch API,

bank account API,

supplier account API,

iby_ext_bankacct_pub.create_ext_bank API,

iby_ext_bankacct_pub.CREATE_EXT_BANK_BRANCH API,

iby_ext_bankacct_pub.create_ext_bank_acct API,

iby_disbursement_setup_pub.set_payee_instr_assignment API,

create_ext_bank  API,

CREATE_EXT_BANK_BRANCH API,

create_ext_bank_acct API,

set_payee_instr_assignment API

Oracle workflow : Set Workflow System Administrator privileges to all users.

You can do it by login to application using SYSADMIN user name, or do the following

Login to Oracle application database using apps/@

Execute following update statement

SQL>select text from wf_resources 

  2 where name = 'WF_ADMIN_ROLE';

 
TEXT
--------------------------------------------------------------------
SYSADMIN

Result will be SYSADMIN, Update it to *.

Run the following update statement:

SQL> update wf_resources set text  = '*' 
  2  where name = 'WF_ADMIN_ROLE';

Screen 1 : No Admin Privilege to all users to run workflow from front end.

Screen 2 : After updating WF_ADMIN_ROLE to * we will get RUN button.

Screen 3 : Execution and parameter entry screen when we will click on run button.