Register Table and its column

Register Table and its column:

create or replace procedure XXIMR_REGISTER_TABLE(APPL_SHORT_NAME varchar2,
                                                 TABLE_OWNER     VARCHAR2,
                                                 TABLE_NAME      VARCHAR2) is

  cursor tbl_column(p_owner varchar2, p_Table varchar2) is
    select clmn.COLUMN_NAME,
           clmn.DATA_TYPE,
           clmn.DATA_LENGTH,
           clmn.DATA_PRECISION,
           clmn.DATA_SCALE,
           clmn.NULLABLE,
           clmn.COLUMN_ID
      from ALL_TAB_COLUMNS clmn
     where clmn.OWNER = p_owner
       and clmn.TABLE_NAME = p_Table;

begin

  ad_dd.register_table(p_appl_short_name => APPL_SHORT_NAME, p_tab_name => TABLE_NAME, p_tab_type => 'T');

  Commit;
  for i in tbl_column(TABLE_OWNER, TABLE_NAME) loop
    ad_dd.register_column(p_appl_short_name => APPL_SHORT_NAME,
                          p_tab_name        => TABLE_NAME,
                          p_col_name        => i.column_name,
                          p_col_seq         => i.column_id,
                          p_col_type        => i.data_type,
                          p_col_width       => i.data_length,
                          p_nullable        => i.nullable,
                          p_translate       => 'N',
                          p_precision       => i.data_precision,
                          p_scale           => i.data_scale);
  end loop;

  commit;
end;

Supplier's Contact Query of R12's TCA Architecture:

select hzr.object_id        party_id_for_vendor,
       hp.party_id          party_id_for_person,
       hp.person_first_name,
       hp.person_last_name,
       hcpp.phone_number    primary_phone_number,
       hcpe.email_address,
       hcpp.PHONE_AREA_CODE,
       hcpp.PHONE_NUMBER,
       hcpp.PHONE_EXTENSION,
       hp.party_name,
       fu.user_name
  from hz_parties               hp,
       fnd_user                 fu,
       hz_relationships         hzr,
       hz_parties               hzr_hp,
       hz_party_usg_assignments hpua,
       HZ_CONTACT_POINTS        hcpp,
       HZ_CONTACT_POINTS        hcpe
 where hp.party_id = hzr.subject_id
   and hzr.object_id = 431710 -- Supplier's party_id
   and hzr.relationship_type = 'CONTACT'
   and hzr.relationship_code = 'CONTACT_OF'
   and hzr.subject_type = 'PERSON'
   and hzr.object_type = 'ORGANIZATION'
   and hzr_hp.party_id = hzr.party_id
   and fu.person_party_id(+) = hp.party_id
   and hpua.party_id = hp.party_id
   and hpua.status_flag = 'A'
   and hpua.party_usage_code = 'SUPPLIER_CONTACT'
   And hcpp.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
   And hcpp.OWNER_TABLE_ID(+) = hzr.PARTY_ID
   And hcpp.PHONE_LINE_TYPE(+) = 'GEN'
   And hcpp.CONTACT_POINT_TYPE(+) = 'PHONE'
   And hcpe.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
   and hcpe.OWNER_TABLE_ID(+) = hzr.PARTY_ID
   And hcpe.CONTACT_POINT_TYPE(+) = 'EMAIL'
   And hcpe.status(+) = 'A'
   And hcpp.status(+) = 'A'

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.

The life of an SQL statement

What happens when Oracle processes an SQL Statement? SQL statement must always be parsed. Then, it can be executed any number of times. If it is a select statement, the result-data needs be fetched for each execution. Parses One of the goals of the parse phase is to generate a query execution plan (QEP). Does the statement correspond to an open cursor, ie, does the statement already exist in the library cache. If yes, the statement needs not be parsed anymore and can directly be executed. If the cursor is not open, it might still be that it is cached in the cursor cache. If yes, the statement needs not be parsed anymore and can directly be executed. If not, the statement has to be verified syntactically and semantically: Syntax This step checks if the syntax of the statement is correct. For example, a statement like select foo frm bar is syntactically not correct (frm instead of from). Semantic A statement might be invalid even if the syntax is correct. For example select foo from bar is invalid if there is no table or view named bar, or if there is such a table, but without a column named foo. Also, the table might exist, but the user trying to execute the query does not have the necessary object privileges. If the statement is syntactically and semantically correct, it is placed into the library cache (which is part of the Shared Pool). Opening the cursor A cursor for the statement is opened. The statement is hashed and compared with the hashed values in the sql area. If it is in the sql area, it is a soft parse, otherwise, it's a hard parse. Only in the case of a hard parse, the statement undergoes the following steps (view merging, statement transformation, optimization): View merging If the query contains views, the query might be rewritten to join the view's base tables instead of the views. Statement Transformation Transforms complex statements into simpler ones through subquery unnesting or in/or transformations Optimization The CBO uses "gathered??" statistics to minimize the cost to execute the query. The result of the optimization is the query evaluation plan (QEP). If bind variable peeking is used, the resulting execution plan might be dependant on the first bound bind-value. Execute Memory for bind variables is allocated and filled with the actual bind-values. The execution plan is executed. Oracle checks if the data it needs for the query are already in the buffer cache. If not, it reads the data off the disk into the buffer cache. The record(s) that are changed are locked. No other session must be able to change the record while they're updated. Also, before and after images describing the changes are written to the redo log buffer and the rollback segments. The original block receives a pointer to the rollback segment. Then, the data is changed. Fetch Data is fetched from database blocks. Rows that don't match the predicate are removed. If needed (for example in an order by statement), the data is sorted. The data is then returned to the application. The execution plan The query execution plan is also stored in the library cache. EXPLAIN PLAN can be used to see how the statement is executed. Private SQL Area Each session that issues an SQL statement has a private SQL area associated with this statement. The first step for Oracle when it executes an SQL statement is to establish a run time area (within the private SQL area) for the statement.