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:


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

Alternative Standpoint said...
May 14, 2020 at 2:04 PM

Thank you !!


Anonymous said...
November 12, 2021 at 12:11 AM

Thanks a lot sir ji ...this help us ...


Post a Comment

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.