Thursday, May 28, 2015

API to assign External Bank Account to Supplier (Create Instrument) in Oracle Apps R12 - iby_disbursement_setup_pub














Script:

DECLARE
  p_api_version          NUMBER;
  p_init_msg_list        VARCHAR2(200);
  p_commit               VARCHAR2(200);
  x_return_status        VARCHAR2(200);
  x_msg_count            NUMBER;
  x_msg_data             VARCHAR2(200);
  p_payee                apps.iby_disbursement_setup_pub.payeecontext_rec_type;
  p_assignment_attribs   apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
  lr_ext_bank_acct_dtl   iby_ext_bank_accounts%ROWTYPE;
  x_assign_id            NUMBER;
  x_response             apps.iby_fndcpt_common_pub.result_rec_type;
  lv_vendor_site_code    VARCHAR2(100);
  lv_vendor_name         VARCHAR2(100);
  lv_bank_acct_name      VARCHAR2(100);
  lv_supp_site_id        VARCHAR2(100);
  lv_supp_party_site_id  VARCHAR2(100);
  lv_acct_owner_party_id VARCHAR2(100);
  lv_org_id              VARCHAR2(100);
  l_msg                  VARCHAR2(200);
BEGIN
  -- Initialize apps session
  fnd_global.apps_initialize(1119, 50833, 200);
  mo_global.init('SQLAP');
  fnd_client_info.set_org_context(101);
  -- Input values
  lv_vendor_site_code := 'TEST SUPPLIER SITE';
  lv_vendor_name      := 'TEST SUPPLIER';
  lv_bank_acct_name   := 'TEST SUPP BANK ACCT';
  -- Assign API parameters
  p_api_version   := 1.0;
  p_init_msg_list := fnd_api.g_true;
  p_commit        := fnd_api.g_true;

  -- get ext bank account details
  BEGIN
    SELECT *
      INTO lr_ext_bank_acct_dtl
      FROM iby_ext_bank_accounts
     WHERE bank_account_name = lv_bank_acct_name;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('Unable to derive the external bank details:' ||
                           SQLERRM);
  END;

  -- get supplier details
  BEGIN
    SELECT assa.vendor_site_id,
           assa.party_site_id,
           aps.party_id,
           assa.org_id
      INTO lv_supp_site_id,
           lv_supp_party_site_id,
           lv_acct_owner_party_id,
           lv_org_id
      FROM ap_suppliers aps, ap_supplier_sites_all assa
     WHERE aps.vendor_id = assa.vendor_id
       AND aps.vendor_name = lv_vendor_name
       AND assa.vendor_site_code = lv_vendor_site_code;
  EXCEPTION   
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(Error- Get supp_site_id and supp_party_site_id' ||
                           SQLCODE || SQLERRM);
  END;

  -- Assign payee values
  p_payee.supplier_site_id := lv_supp_site_id;
  p_payee.party_id         := lv_acct_owner_party_id;
  p_payee.party_site_id    := lv_supp_party_site_id;
  p_payee.payment_function := 'PAYABLES_DISB';
  p_payee.org_id           := lv_org_id;
  p_payee.org_type         := 'OPERATING_UNIT';
  -- Assignment Values
  p_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';
  p_assignment_attribs.instrument.instrument_id   := lr_ext_bank_acct_dtl.ext_bank_account_id;
  -- External Bank Account ID
  p_assignment_attribs.priority   := 1;
  p_assignment_attribs.start_date := SYSDATE;

  iby_disbursement_setup_pub.set_payee_instr_assignment
    (p_api_version        => p_api_version,
     p_init_msg_list      => p_init_msg_list,
     p_commit             => p_commit,
     x_return_status      => x_return_status,
     x_msg_count          => x_msg_count,
     x_msg_data           => x_msg_data,
     p_payee              => p_payee,
     p_assignment_attribs => p_assignment_attribs,
     x_assign_id          => x_assign_id,
     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_ASSIGN_ID = ' || x_assign_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_return_status <> fnd_api.g_ret_sts_success) THEN
    FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
      l_msg := fnd_msg_pub.get(p_msg_index => i,
                               p_encoded   => fnd_api.g_false);
      DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);
    END LOOP;
  ELSE
    DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
  END IF;
END;

Test Results:


Thursday, May 28, 2015 by Team search · 2

API to Update External Bank Account in Oracle Apps R12 (iby_ext_bankacct_pub.update_ext_bank_acct)














Script:

DECLARE
  p_api_version        NUMBER;
  p_init_msg_list      VARCHAR2(200);
  p_ext_bank_acct_rec  apps.iby_ext_bankacct_pub.extbankacct_rec_type;
  lr_ex_bk_acnt        iby_ext_bank_accounts%ROWTYPE;
  x_return_status      VARCHAR2(200);
  x_msg_count          NUMBER;
  x_msg_data           VARCHAR2(200);
  x_response           apps.iby_fndcpt_common_pub.result_rec_type;
  lv_bank_acct_name    VARCHAR2(100);
  lv_new_bank_acct_num VARCHAR2(100);
  l_msg                VARCHAR2(200);
BEGIN
  -- Initialize apps session
  fnd_global.apps_initialize(1119, 50833, 200);
  mo_global.init('SQLAP');
  fnd_client_info.set_org_context(101);
  --input values
  lv_bank_acct_name    := 'TEST SUPP BANK ACCT';
  lv_new_bank_acct_num := '1234567890';

  -- get ext bank account details
  BEGIN
    SELECT *
      INTO lr_ex_bk_acnt
      FROM iby_ext_bank_accounts
     WHERE bank_account_name = lv_bank_acct_name;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('Unable to derive the external bank details:' ||
                           SQLERRM);
  END;

  -- Assign API parameters
  p_api_version                             := 1.0;
  p_init_msg_list                           := fnd_api.g_true;
  p_ext_bank_acct_rec.bank_account_id       := lr_ex_bk_acnt.ext_bank_account_id;
  p_ext_bank_acct_rec.bank_account_num      := lv_new_bank_acct_num;
  p_ext_bank_acct_rec.bank_account_name     := lv_bank_acct_name;
  p_ext_bank_acct_rec.object_version_number := lr_ex_bk_acnt.object_version_number;
 
  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);
                                           
  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_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_return_status <> fnd_api.g_ret_sts_success) THEN
    FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
      l_msg := fnd_msg_pub.get(p_msg_index => i,
                               p_encoded   => fnd_api.g_false);
      DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);
    END LOOP;
  ELSE
    DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
  END IF;
END;


Test Results:


by Team search · 4

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.