Tuning SQL using DBMS_SQLTUNE

DECLARE
  SQL_STRING  VARCHAR2(15000);
  TASK_NUMBER VARCHAR2(200);
BEGIN
  SQL_STRING  := '<<write your sql query>>';
  TASK_NUMBER := dbms_sqltune.create_tuning_task(sql_text => SQL_STRING, task_name=>'TASK_NUMBER1');
END;

EXEC dbms_sqltune.execute_tuning_task('TASK_NUMBER1');

SELECT DBMS_SQLTUNE.report_tuning_task('TASK_NUMBER1') FROM dual; 

... and follow the recommendation

Oracle BI/XML Publisher : format numbers as text so that leading zero don't disappear or excel will not convert large numbers in scientific format

To preserve leading zero in a number string, modify rtf file replace field code with following code.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?NUMBER_FIELD?>
</fo:bidi-override>

Update Supplier Site Information using API

CREATE OR REPLACE PROCEDURE xx_update_supplier_site AS

  lr_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

  p_vendor_site_id   NUMBER;

  x_msg_data         VARCHAR2(1000);

  x_msg_count        NUMBER;

  p_count            NUMBER;

  x_return_status    VARCHAR2(10);

  lc_msg_data        VARCHAR2(4000);

-- Modify cursor as per your requirement to select Vendor and site

  CURSOR lcu_head IS

    SELECT aps.vendor_id,

           apss.vendor_site_id,

           apss.org_id           

      FROM ap_suppliers aps, ap_supplier_sites_all apss

     WHERE 1 = 1      

       AND aps.vendor_id = apss.vendor_id     

       --AND aps.vendor_id = 10284 

       ;

BEGIN

  fnd_global.apps_initialize(1110, 50747, 200);

  mo_global.init('SQLAP');

  fnd_client_info.set_org_context(83);

  FOR i IN lcu_head LOOP

   

      p_vendor_site_id := NULL;

      x_msg_data       := NULL;

      x_msg_count      := NULL;

      p_count          := NULL;

      x_return_status  := NULL;

      lc_msg_data      := NULL;

      -- Assign Vendor Site Details      

      lr_vendor_site_rec.last_update_date := SYSDATE;

      lr_vendor_site_rec.last_updated_by  := 1110;

      lr_vendor_site_rec.vendor_id        := i.vendor_id;

      lr_vendor_site_rec.org_id           := i.org_id;   

      

      --lr_vendor_site_rec.pay_group_lookup_code := 'PAY GROUP';

     

   

      ap_vendor_pub_pkg.update_vendor_site(p_api_version      => 1.0

                                           x_return_status    => x_return_status,

                                           x_msg_count        => x_msg_count,

                                           x_msg_data         => x_msg_data,

                                           p_vendor_site_rec  => lr_vendor_site_rec,

                                           p_vendor_site_id   => i.vendor_site_id

                                           );

   

      Commit;

      IF x_return_status = 'S' THEN

        dbms_output.put_line('Success ' || x_return_status);

      ELSE

        dbms_output.put_line('Failed ' || x_return_status);

        IF (x_msg_count = 1) THEN

          dbms_output.put_line('x_msg_data ' || x_msg_data);

          lc_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);

            lc_msg_data := lc_msg_data || ' ' || x_msg_data;

          END LOOP;

        END IF;

     

      END IF;

    

 

  END LOOP;

END;

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