API top Update Price list

Many times for bulk processing there will be a need to update price lists for Eg , when we want to end date lines in bulk. In such a scenario we can use the API.


gpr_price_list_line_tbl (l_num_count).list_line_id :=
            list_line_rec.list_line_id;
         gpr_price_list_line_tbl (l_num_count).operation :=
            QP_GLOBALS.G_OPR_UPDATE;
         gpr_price_list_line_tbl (l_num_count).start_date_active :=
            list_line_rec.START_DATE_ACTIVE;
         gpr_price_list_line_tbl (l_num_count).end_date_active :=
            '21-SEP-2016';
         -- call to API
         QP_PRICE_LIST_PUB.Process_Price_List (
            p_api_version_number        => 1,
            p_init_msg_list             => FND_API.G_FALSE,
            p_return_values             => FND_API.G_FALSE,
            p_commit                    => FND_API.G_FALSE,
            x_return_status             => gpr_return_status,
            x_msg_count                 => gpr_msg_count,
            x_msg_data                  => gpr_msg_data,
            p_PRICE_LIST_LINE_tbl       => gpr_price_list_line_tbl,
            x_PRICE_LIST_rec            => ppr_price_list_rec,
            x_PRICE_LIST_val_rec        => ppr_price_list_val_rec,
            x_PRICE_LIST_LINE_tbl       => ppr_price_list_line_tbl,
            x_PRICE_LIST_LINE_val_tbl   => ppr_price_list_line_val_tbl,
            x_QUALIFIERS_tbl            => ppr_qualifiers_tbl,
            x_QUALIFIERS_val_tbl        => ppr_qualifiers_val_tbl,
            x_PRICING_ATTR_tbl          => ppr_pricing_attr_tbl,
            x_PRICING_ATTR_val_tbl      => ppr_pricing_attr_val_tbl);


Some of these are record type variables and some table type variables


      gpr_price_list_line_tbl       QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
      gpr_price_list_line_val_tbl   QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
      ppr_price_list_rec            QP_PRICE_LIST_PUB.Price_List_Rec_Type;
      ppr_price_list_val_rec        QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
      ppr_price_list_line_tbl       QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
      ppr_price_list_line_val_tbl   QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
      ppr_qualifiers_tbl            QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
      ppr_qualifiers_val_tbl        QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
      ppr_pricing_attr_tbl          QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
      ppr_pricing_attr_val_tbl      QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
      p_api_version_number          NUMBER := 1;
      p_init_msg_list               VARCHAR2 (1) := Fnd_Api.G_FALSE;
      p_return_values               VARCHAR2 (1) := Fnd_Api.G_FALSE;
      p_commit                      VARCHAR2 (1) := Fnd_Api.G_TRUE;


If the script is run as an anonymous block then note that API will not do an implicit commit and user needs to do a COMMIT explicitly to ensure changes are reflected in the system

API to Create External Bank Branch

When performing Master Data Migration for Supplier or Customer business also needs the migration of Associated Banks and Bank Branches .

In order to perform this we need to migrate the Banks , Bank Branches and associated Bank Branches

In R12 to create an external bank branch Oracle has given an API :

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

API Signature :

create_ext_bank_branch (
   p_api_version                IN   NUMBER,
   p_init_msg_list              IN   VARCHAR2,
   p_ext_bank_branch_rec        IN   ExtBankBranch_rec_type,
   x_branch_id                  OUT  NOCOPY  NUMBER,
   x_return_status              OUT  NOCOPY  VARCHAR2,
   x_msg_count                  OUT  NOCOPY  NUMBER,
   x_msg_data                   OUT  NOCOPY  VARCHAR2,
   x_response                   OUT  NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  )

p_ext_bank_branch_rec         Bank branch record type
x_branch_id                     Id of the branch created
x_return_status                Return status of API

Sample code :

                         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   => l_bank_branch_rec,
                            x_branch_id             => l_num_branch_id,
                            x_return_status         => l_chr_return_status,
                            x_msg_count             => l_num_count,
                            x_msg_data              => l_msg_data,
                            x_response              => x_response_rec);


                         writelog (
                            'l_chr_return_status in bank branch '
                            || l_chr_return_status);
                         writelog ('Branch Id: ' || l_num_branch_id || SQLERRM);

                         --Check for Sucess API
                         IF (l_chr_return_status <> 'S')
                         THEN
                            FOR i IN 1 .. l_num_count
                            LOOP
                               fnd_msg_pub.get (
                                  p_msg_index       => -1,
                                  p_encoded         => 'F',
                                  p_data            => l_msg_data,
                                  p_msg_index_out   => l_msg_index_out);

                               writelog (
                                  'Error in Bank Branch Creation  ' || l_msg_data);
                               l_chr_val_ret_msg :=
                                  l_chr_val_ret_msg || '  ' || l_msg_data;
                               RAISE e_error;
                            END LOOP;
                         ELSE
                            writelog ('  Branch created successfully. ');
                         END IF;

Parameters to pass :

Bank Id
branch name
branch number
branch type
Swift code
alt_branch_name

Note : Branch can only be created if a bank exists make sure to use create bank before calling this API

A post for that too is available in the blog

Once the record is created information can be found in the view : iby_ext_bank_branches_v

API to Create External Bank

When performing Master Data Migration for Supplier or Customer business also needs the migration of Associated Banks and Bank Branches .

In order to perform this we need to migrate the Banks , Bank Branches and associated Bank Branches

In R12 to create an external bank Oracle has given an API :

IBY_EXT_BANKACCT_PUB.create_ext_bank

API signature :

PROCEDURE create_ext_bank (
        p_api_version              IN  NUMBER,
p_init_msg_list            IN  VARCHAR2,
p_ext_bank_rec             IN  ExtBank_rec_type,
x_bank_id                  OUT NOCOPY NUMBER,
x_return_status            OUT NOCOPY VARCHAR2,
x_msg_count                OUT NOCOPY NUMBER,
x_msg_data                 OUT NOCOPY VARCHAR2,
x_response                 OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  )

p_ext_bank_rec   is the record type variable with the infromation of the bank to be created in the system

x_bank_id                  is the id of the bank to be created
x_return_status           return status

Sample Code :

IBY_EXT_BANKACCT_PUB.create_ext_bank (
                            p_api_version     => 1.0,
                            p_init_msg_list   => FND_API.G_TRUE,
                            p_ext_bank_rec    => l_bank_rec,
                            x_bank_id         => l_num_bank_id,
                            x_return_status   => l_chr_return_status,
                            x_msg_count       => l_num_count,
                            x_msg_data        => l_msg_data,
                            x_response        => x_response_rec);

                         IF (l_chr_return_status <> 'S')
                         THEN
                            FOR i IN 1 .. l_num_count
                            LOOP
                               fnd_msg_pub.get (
                                  p_msg_index       => -1,
                                  p_encoded         => 'F',
                                  p_data            => l_msg_data,
                                  p_msg_index_out   => l_msg_index_out);
                               fnd_file.put_line (
                                  fnd_file.LOG,
                                  'Error in Bank  Creation  ' || l_msg_data);
                               l_chr_val_ret_msg :=
                                  l_chr_val_ret_msg || '  ' || l_msg_data;
                               RAISE e_error;
                            END LOOP;
                         ELSE
                            fnd_file.put_line (fnd_file.LOG,
                                               'Bank Created Sucessfully  ');
                         END IF;

Mandatory Parameters :
Bank Name
Bank Country
Bank Number
Currency Code

Combination of these is an Unique combination and API gives an error if we try to create duplicate records

Once the bank is created information can be searched in the view iby_ext_banks_v

API to Update External Bank Account Details

When an external bank is created in Oracle the user can access the bank details from the view : iby_ext_banks_v.

For each bank records created in the system Oracle will create a party and a party profile too in the tbale hz_oprganization_profiles. Depending on the business the users may be needed to update the bank details in the system , Oracle has provided us with an API which helps us to update banks in Oracle.

IBY_EXT_BANKACCT_PUB.update_ext_bank

   IBY_EXT_BANKACCT_PUB.update_ext_bank (
                          p_api_version     => 1.0,
                          p_init_msg_list   => FND_API.G_TRUE,
                          p_ext_bank_rec    => l_bank_rec,
                          x_return_status   => l_chr_return_status,
                          x_msg_count       => l_num_count,
                          x_msg_data        => l_msg_data,
                          x_response        => x_response_rec);

When calling the API to update the bank the important parameters to be passed are bank_id and object_Version number.

Bank Id is the Id of the bank which the user can get from iby_ext_banks_v.
Object_version_number : Query the table hz_organization_profiles from with the bank_id:

SELECT max(object_version_number)
     INTO l_num_version_number
     FROM apps.hz_organization_profiles
   WHERE party_id = l_num_bank_id;

Pass the object version number to Oracle , In case this value is not passed to the API , you will get an error message, Cannot lock the record in the table HZ_PARTIES , the record is being updated by another user.

In addition to the above values pass the varibales that you wish to update and the API will take care of it :

Sample code Snippet:

IBY_EXT_BANKACCT_PUB.update_ext_bank (
                          p_api_version     => 1.0,
                          p_init_msg_list   => FND_API.G_TRUE,
                          p_ext_bank_rec    => l_bank_rec,
                          x_return_status   => l_chr_return_status,
                          x_msg_count       => l_num_count,
                          x_msg_data        => l_msg_data,
                          x_response        => x_response_rec);   
             
                         --Check for Sucess API
            fnd_file.put_line(fnd_file.LOG,'x_response_rec.result_message '||        x_response_rec.result_message);    
            IF (l_chr_return_status <> 'S')
            THEN
               FOR i IN 1 .. l_num_count
               LOOP
                  fnd_msg_pub.get (
                                  p_msg_index       => -1,
                                  p_encoded         => 'F',
                                  p_data            => l_msg_data,
                                  p_msg_index_out   => l_msg_index_out);
                  fnd_file.put_line (
                                  fnd_file.LOG,
                                  'Error in Bank  Update  ' || l_msg_data);
                            END LOOP;
                ELSE
                   fnd_file.put_line (fnd_file.LOG,
                                               'Bank Updated Sucessfully  ');
              COMMIT;
              END IF;

API to create Code Combination and get CCID

It may not always be the case that Account codes will be created by user from the front end. There may be scenarios sometimes where we need to have programs that will be creating the code combinations.

Oracle has provided the API's that can be used to create code combinations , we will be discussing a couple of them here.

Pre-requisits

1. When trying to create the code combinations via the API is that in the Key Flexfields setup , for the KFF defined for your Accounting segments the check box Allow Dynamic Inserts need to be enabled, then only will the code combination be created else the API will never create the accounting segments.
2. User needs to do initialization
3. The Accounting flex-filed Value sets need to be populated with the values being used in the account being created

Now we will discuss the API's:

1. fnd_flex_ext.get_ccid , this API will take in the input parameters and return the code combination id of the account segment created. In case the API is not able to create the account segmewnts for any reason or in case of an error the API will return 0.

            API syntax:

            fnd_flex_ext.get_ccid

     (application_short_name      => 'SQLGL',

      key_flex_code               => 'GL#',

      structure_number            => p_chart_of_accounts_id,

      validation_date             => SYSDATE

      concatenated_segments       => allsegments

      );

The API takes in all these parameters as input:

   application_short_name : this is the short name of the General Ledger  Application

   key_flex_code : this is to be hardcoded as GL#

   structure_number : this is to be the chart of account id in which the account

   is to be created

   validation_date : Pass SYSDATE

   concatenated_segments : the account code segments in concatenated format

Once the API is run , if the account creation is successful then the CCID of the account is returned else the program will return 0.

    2. FND_FLEX_KEYVAL.VALIDATE_SEG , the API will take in the input parameters based

         on which the account code will be created , the API returns a BOOLEAN message of

         TRUE : signifying success or FALSE : in case API was not successful 

         API syntax:

         FND_FLEX_KEYVAL.VALIDATE_SEGS(

                                        ‘CREATE_COMBINATION’,
                                        ‘SQLGL’,
                                        ‘GL#’,
                                        chart_of_accts_id,
                                         concatenated_segments,
                                         ‘V’,
                                         SYSDATE,
                                         ‘ALL’, NULL, NULL, NULL, NULL,
                                         FALSE,FALSE, NULL, NULL, NULL);

       If the API is successful the the account segment will be created and the API will

       return a status TRUE , in case any error occurs he the account code creation will 

       fail and the API will return a status FALSE, In case of any error the API will

       populate the Error in the fnd_message queue.

  

       The user will have to retrieve the error message from there to see the error
       occurred:

       l_chr_error_msg := fnd_flex_keyval.error_message

       

API to Update External Payee

Whenever a supplier/supplier site is created oracle system will automatically create a payee for the supplier and supplier site. Once the Payee is created the user may want to update the payee details.
The API to update the payee is :

IBY_DISBURSEMENT_SETUP_PUB.update_external_vendor

iby_disbursement_setup_pub.update_external_payee 
 (p_api_version => 1.0, 
   p_init_msg_list => fnd_api.g_true, 
p_ext_payee_tab => p_external_payee_tab_type, 
p_ext_payee_id_tab => p_ext_payee_id_tab_type, 
x_return_status => x_return_status, 
x_msg_count => x_msg_count, 
x_msg_data => x_msg_data, 
x_ext_payee_status_tab => l_payee_upd_status 
);

p_external_payee_tab_type is a table type variable of external payees which will take in a number of fields of the table.

p_ext_payee_id_tab_type is a table type variable which will take in only variable : ext_payee_id

When updating an external payee the developer needs to consider the below:
   the api will update all the columns which defined in the record type variable which is passed to the API , thus the developer when assigning values to the record type variable needs to assigne the new values to be updated as well as the old values to the API rec type.

The data updated will be reflected in the table iby_external_payees_all