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;

Supplier Payment Methods in Oracle

In Oracle, whenever a  supplier or supplier site is Created in oracle , Oracle will automatically create a payee in the system and depending on the data provided a payment method.

Each payee will define the different attributes which are related to all the aspects of payments for that supplier or supplier site.

For each supplier or supplier site the business might want to define the payment methods i.e. mechanism in which the payment will be made, this might the EFT,WIRE,CHECK etc.

When creating a supplier the API will automatically create the payee and payment method for the supplier will be created, provided a payment method is passed to the API . If we pass NULL to the API in the payment method then no payment method will be created.

Even if the payment method is created in the system but if the PRIMARY_FLAG = 'N' then this payment method will not be visible in the front end.

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