Thursday, January 1, 2015

API to Create External Bank Branch

Salam Alaikum,

   I share with you guys this API, that i use to create External Banks Branches , please note that external bank should exist and bank_id should be passed to this API.

DECLARE
   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_branch_rec   iby_ext_bankacct_pub.extbankbranch_rec_type;
   x_branch_id             NUMBER;
   p_count                 NUMBER;

   CURSOR C1
   IS
 SELECT DISTINCT BANK_COUNTRY, BANK_NUMBER, BANK_NAME
                FROM xx_customer_tmp_amm_t
               WHERE     BANK_COUNTRY IS NOT NULL
                     AND BANK_NUMBER IS NOT NULL
                     AND BANK_NAME IS NOT NULL
                     and O_BANK_UPLOAD_STATUS is null;
BEGIN
   FOR REC1 IN C1
   LOOP
      p_ext_bank_branch_rec.bch_object_version_number := 1.0;
      p_ext_bank_branch_rec.branch_name := rec1.BANK_BRANCH_NAME;
      p_ext_bank_branch_rec.branch_type := 'ABA';
      p_ext_bank_branch_rec.branch_number := rec1.BRANCH_NUMBER;
      p_ext_bank_branch_rec.bank_party_id := rec1.NEW_BANK_ID;


      APPS.FND_MSG_PUB.DELETE_MSG (NULL);
      APPS.FND_MSG_PUB.INITIALIZE ();


      IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH (
         p_api_version           => p_api_version,
         p_init_msg_list         => p_init_msg_list,
         p_ext_bank_branch_rec   => p_ext_bank_branch_rec,
         x_branch_id             => x_branch_id,
         x_return_status         => x_return_status,
         x_msg_count             => x_msg_count,
         x_msg_data              => x_msg_data,
         x_response              => x_response);

      DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
      DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
      DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);
      DBMS_OUTPUT.put_line ('x_branch_id = ' || x_branch_id);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Code = ' || x_response.result_code);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Category = ' || x_response.result_category);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Message = ' || x_response.result_message);

      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;

      IF (x_return_status = 'S')
      THEN
              --         COMMIT;
      END IF;
   END LOOP;
END;

     And  Here is a Query that you can use to see uploaded bank branches.

SELECT BranchParty.PARTY_ID Branch_Party_Id,
       BranchParty.PARTY_NAME Bank_Branch_Name,
       BranchParty.ORGANIZATION_NAME_PHONETIC Bank_Branch_Name_Alt,
       BranchCA.start_date_active Start_Date,
       BranchCA.end_date_active End_Date
  FROM HZ_PARTIES BranchParty, HZ_CODE_ASSIGNMENTS BranchCA
 WHERE     1 = 1
       AND BranchParty.PARTY_TYPE = 'ORGANIZATION'
       AND BranchParty.status = 'A'
       AND BranchCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
       AND BranchCA.CLASS_CODE IN ('BANK_BRANCH', 'CLEARINGHOUSE_BRANCH')
       AND BranchCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
       AND (BranchCA.STATUS = 'A' OR BranchCA.STATUS IS NULL)
       AND BranchCA.OWNER_TABLE_ID = BranchParty.PARTY_ID
       AND BranchParty.created_by = -1;

Hope you found it useful guys, feel free to leave a comment if have any note on the subject.       

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete