for external banks,and branchs and account and associatn the account to supplier at site lever

CREATE OR REPLACE
PACKAGE BODY CH_AP_SUPBANKCONV_PKG
IS
  /****************************************************
  File Name       : CH_AP_SUP_BANK_CONV_b_pkg.pkb
  Description     : Supplier Banks Conversion Package Body
  Author's Name   : K Mahesh Babu
  Date Written    : 06-Sep-2011
  Maintenance History :
  Date          Version#       Name           Remarks
  -----         -------      ------------     --------
  06-Sep-2011     1.0         K Mahesh Babu   Initial Development
  *******************************************************/
 g_USER_ID         NUMBER := FND_GLOBAL.USER_ID;
  G_RESP_ID         NUMBER := FND_GLOBAL.RESP_ID;
  g_RESP_APPL_ID    NUMBER := FND_GLOBAL.RESP_APPL_ID;
  g_LOGIN_ID        NUMBER := FND_GLOBAL.LOGIN_ID;
  g_conc_request_id NUMBER := fnd_global.CONC_REQUEST_ID;
  --Global Variables for Error handling and program status
  g_loc               NUMBER :=0;
  g_msg               varchar2(500);
  g_error_description varchar2(2000):= null;
 
procedure ch_ap_createbank_pr(
    x_err_msg OUT VARCHAR2)
IS

  x_status            VARCHAR2(20);
  --PROCEDURE CH_AP_CREATEBANK_PR (y_err_msg out varchar2)
  --IS
  l_response_rec apps.IBY_FNDCPT_COMMON_PUB.Result_rec_type;
  l_ext_bank_account_rec apps.IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
  l_ext_bank_rec iby_ext_bankacct_pub.ExtBank_rec_type;
  l_bank_id hz_parties.PARTY_ID%TYPE;
  l_bank_party_id hz_parties.PARTY_ID%TYPE;
  l_bank_account_id iby_ext_bank_accounts.EXT_BANK_ACCOUNT_ID%TYPE;
  bch_object_version_number hz_parties.OBJECT_VERSION_NUMBER%TYPE;
  l_ext_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
  l_branch_id hz_parties.party_id%type;
  l_party_id          number:=0;
  l_vendor_site_id number:=0;
   l_party_site_id number:=0;
  l_return_status VARCHAR2(10);
  l_msg_count     NUMBER;
  l_msg_data      VARCHAR2(2000);
  L_MSG_INDEX      number := 0;
 
  CURSOR ch_sup_bank_cur
  is
    SELECT * FROM CH_AP_SUPBANK_STG
    WHERE record_status='NEW';
BEGIN
  FOR ch_sup_bank_var IN ch_sup_bank_cur
  loop
    fnd_file.put_line(fnd_file.log,'converting the bank details ');
    l_ext_bank_rec.bank_name             := ch_sup_bank_var.bank_name;
    l_ext_bank_rec.bank_number           := ch_sup_bank_var.bank_number;
    l_ext_bank_rec.institution_type      := ch_sup_bank_var.institution_type;
    l_ext_bank_rec.country_code          := ch_sup_bank_var.HOME_COUNTRY;
    l_ext_bank_rec.object_version_number := 1;
    FND_FILE.PUT_LINE(FND_FILE.log,'Calling API create_ext_bank');
   
    IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK (
                                              p_api_version         => 1.0,
                                              p_init_msg_list       => apps.fnd_api.g_true,
                                              p_ext_bank_rec        => l_ext_bank_rec,
                                              x_bank_id             => l_bank_id,
                                              x_return_status       => l_return_status,
                                              x_msg_count           => l_msg_count,
                                              x_msg_data            => l_msg_data,
                                              x_response                => l_response_rec );
    fnd_file.put_line(fnd_file.log,'After API create_ext_bank'||l_bank_id);
    fnd_file.put_line(fnd_file.log,'APi message count :'||l_msg_count);
    fnd_file.put_line(fnd_file.log,'APi message data :'||l_msg_data);
   
    g_msg                :='external banks created successfully.................';
    IF l_return_status IN ('E', 'U') THEN
      FOR i              IN 1 .. l_msg_count
      LOOP
        apps.fnd_msg_pub.get (i, apps.fnd_api.g_false, l_msg_data, l_msg_index );
        l_msg_data := l_msg_data || 'Bank API Error ';
        fnd_file.put_line(fnd_file.log,l_msg_data);
      END LOOP;
    ELSIF l_return_status = 'S' THEN
      fnd_file.put_line(fnd_file.log,'Bank Created: and id: '||l_bank_id);
    END IF;
    FND_FILE.PUT_LINE(FND_FILE.log,G_LOC||':'||G_MSG);
     FND_FILE.PUT_LINE(FND_FILE.log,'Creating the bank branch.........');
     ---passing the values to create bank branch................
    l_ext_bank_branch_rec.branch_name               := ch_sup_bank_var.branch_name;
    l_ext_bank_branch_rec.branch_number             := ch_sup_bank_var.branch_num;
    l_ext_bank_branch_rec.branch_type               := ch_sup_bank_var.branch_type;
    l_ext_bank_branch_rec.bank_party_id             :=l_bank_id;
    L_EXT_BANK_BRANCH_REC.BCH_OBJECT_VERSION_NUMBER := 1;
   
    fnd_file.put_line(fnd_file.log,'Before calling API create_ext_bank_branch with bank_id'||l_bank_id);
    fnd_file.put_line(fnd_file.log,'Branch_name :'||ch_sup_bank_var.branch_name);
    fnd_file.put_line(fnd_file.log,'Branch_number :'||ch_sup_bank_var.branch_num);
    fnd_file.put_line(fnd_file.log,'Bank_type :'||ch_sup_bank_var.branch_type);
    fnd_file.put_line(fnd_file.log,'Bank_party_id :'||l_bank_id);
    FND_FILE.PUT_LINE(FND_FILE.log,'Calling API create_ext_bank_branch');
   
    IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH (
                                                      p_api_version         =>  1.0,
                                                      p_init_msg_list       =>  apps.fnd_api.g_true,
                                                      p_ext_bank_branch_rec =>  l_ext_bank_branch_rec,
                                                      x_branch_id           => l_branch_id,
                                                      x_return_status       => l_return_status,
                                                      x_msg_count           => l_msg_count,
                                                      x_msg_data            => l_msg_data,
                                                      x_response            => l_response_rec );
    fnd_file.put_line(fnd_file.log,'After API create_ext_bank_branch with branch_id:'||l_branch_id);
    fnd_file.put_line(fnd_file.log,'After API return status'||l_return_status);
    fnd_file.put_line(fnd_file.log,'APi message count :'||l_msg_count);
    fnd_file.put_line(fnd_file.log,'APi message data :'||l_msg_data);
       fnd_file.put_line(fnd_file.log,'After API create_ext_bank_branch');
      IF l_return_status IN ('E', 'U')
      THEN
      FOR i IN 1 .. l_msg_count
      LOOP
        apps.fnd_msg_pub.get (i, apps.fnd_api.g_false, l_msg_data, l_msg_index );
        l_msg_data := l_msg_data || 'Bank Branch API Error ';
        fnd_file.put_line(fnd_file.log,l_msg_data);
      END LOOP;
    ELSIF l_return_status = 'S' THEN
      fnd_file.put_line(fnd_file.log,'Bank Branch Created: and id: '||l_branch_id);
    END IF;
     fnd_file.put_line(fnd_file.log,'Bank Account Creation...............................');
    l_ext_bank_account_rec.country_code      :=ch_sup_bank_var.COUNTRY ;
    l_ext_bank_account_rec.branch_id         :=l_branch_id;
    l_ext_bank_account_rec.bank_id           := l_bank_id;
    l_ext_bank_account_rec.bank_account_name :=ch_sup_bank_var.account_name;
    l_ext_bank_account_rec.bank_account_num  :=ch_sup_bank_var.account_num;
    l_ext_bank_account_rec.currency          :=ch_sup_bank_var.currencey;
   
    IBY_EXT_BANKACCT_PUB.create_ext_bank_acct( p_api_version        => 1.0,
                                               p_init_msg_list      => FND_API.G_TRUE,
                                               p_ext_bank_acct_rec  => l_ext_bank_account_rec,
                                               x_acct_id            => l_bank_account_id,
                                               x_return_status      => l_return_status,
                                               x_msg_count          => l_msg_count,
                                               x_msg_data           => l_msg_data,
                                               x_response           => l_response_rec);
    fnd_file.put_line(fnd_file.log,'After API create_ext_bank_branch'||l_bank_account_id);
    IF l_return_status IN ('E', 'U')
    THEN
      FOR i IN 1 .. l_msg_count
      LOOP
        apps.fnd_msg_pub.get (i, apps.fnd_api.g_false, l_msg_data, l_msg_index );
        l_msg_data := l_msg_data || '~Bank Branch Account API Error ';
        fnd_file.put_line(fnd_file.log,l_msg_data);
      END LOOP;
    ELSIF l_return_status = 'S' THEN
      fnd_file.put_line(fnd_file.log,'Bank Branch Account Created: and id: '||l_branch_id);
    END IF;
    IF (SQLCODE = 0) THEN
      g_loc    := 1.3 ;
      g_msg    := 'After Successfully inserting Bank Details into Interface table and Updating Banks Staging Table';
      fnd_file.put_line(fnd_file.log,g_loc||':'||g_msg);
      --Updating Supplier Master Details staging table with record status as PROCESSESD
     
        UPDATE CH_SUP_BANK_STG
        SET record_status = 'PROCESSED'
        WHERE record_id   = ch_sup_bank_var.record_id;
    
    ELSE
      g_error_description := ('Error in Inserting bank record into Interface table for Record_id :'||ch_sup_bank_var.record_id||SQLCODE||'-'||sqlerrm);
      --Updating Supplier Sites staging table with interface status as ERROR
    
        UPDATE CH_SUP_BANK_STG
        SET record_status = 'ERROR'
        WHERE record_id   = ch_sup_bank_var.record_id;
    
    END IF;
  END LOOP;
  COMMIT;
END CH_AP_CREATEBANK_PR;
PROCEDURE CH_AP_BANKMAIN_PR(
    x_errbuf OUT NOCOPY  VARCHAR2 ,
    x_retcode OUT NOCOPY VARCHAR2)
IS
  x_err VARCHAR2(2);
BEGIN
  CH_AP_CREATEBANK_PR(x_err);
END;
END CH_AP_SUPBANKCONV_PKG;