Thursday, June 4, 2015

AP_VENDOR_PUB_PKG - VENDOR/SUPPLIER MIGRATION IN ORACLE APPS R12

 

 

 

 

 

 

 

 

 

As usual, Supplier Migration can be carried out either using Standard Interface tables or API’s. In this article, you will find sample Scripts for API’s required for Creating and updating Vendor information in Oracle Apps R12.

Step

Action

API

Sample Script

1

Create Vendor

AP_VENDOR_PUB_PKG.CREATE_VENDOR

Click Here for Test Scripts

2

Create Vendor Site

AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE

Click Here for Test Script

3

Create Vendor Contact

AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT

Click Here for Test Scripts

4

Create External Bank

IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK

Click Here for Test Scripts

5

Create External Bank Branch

IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH

Click Here for Test Scripts

6

Create Bank Branch Address

HZ_LOCATION_V2PUB.CREATE_LOCATION

Click Here for Test Scripts

7

Assign Address to Bank Branch

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE

Click Here for Test Scripts

8

Create Vendor Bank Account

IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT

Click Here for Test Scripts

9

Assign Vendor Bank Account to Vendor

IBY_DISBURSEMENT_SETUP_PUB.

SET_PAYEE_INSTR_ASSIGNMENT

Click Here for Test Scripts

 

In some cases, we may need to End Date the vendor details in the source system. In case, if the source system is also Oracle Apps R12, then below API’S would be useful.

Step

Action

API

Sample Script

1

End Date Vendor

AP_VENDOR_PUB_PKG.UPDATE_VENDOR

Click Here for Test Scripts

2

End Date Vendor Site

AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE

Click Here for Test Scripts

 

Hopefully, This helps!! Smile

Thursday, June 4, 2015 by Team search · 2

AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE - API to update Vendor/Supplier Site in Oracle Apps R12

 

 

 

 

 

 

Script

SET SERVEROUTPUT ON;

 

DECLARE

  p_api_version               NUMBER;

  p_init_msg_list             VARCHAR2(200);

  p_commit                    VARCHAR2(200);

  p_validation_level          NUMBER;

  x_return_status             VARCHAR2(200);

  x_msg_count                 NUMBER;

  x_msg_data                  VARCHAR2(200);

  lr_vendor_site_rec          apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

  lr_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;

  p_vendor_site_id            NUMBER;

  p_calling_prog              VARCHAR2(200);

 

BEGIN

 

  -- Initialize apps session

  fnd_global.apps_initialize(1119, 50833, 200);

  mo_global.init('SQLAP');

  fnd_client_info.set_org_context(101);

 

  -- Assign Basic Values

  p_api_version      := 1.0;

  p_init_msg_list    := fnd_api.g_true;

  p_commit           := fnd_api.g_true;

  p_validation_level := fnd_api.g_valid_level_full;

  p_vendor_site_id   := 2040; -- to be end dated

  p_calling_prog     := 'XXCUSTOM';

 

  BEGIN

    SELECT *

      INTO lr_existing_vendor_site_rec

      FROM ap_supplier_sites_all assa

     WHERE assa.vendor_site_id = p_vendor_site_id;

  EXCEPTION

    WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Unable to derive the supplier site information for site id:' ||

                           p_vendor_site_id);

  END;

 

  -- Assign Vendor Site Details

  lr_vendor_site_rec.vendor_site_id   := lr_existing_vendor_site_rec.vendor_site_id;

  lr_vendor_site_rec.last_update_date := SYSDATE;

  lr_vendor_site_rec.last_updated_by  := 1119;

  lr_vendor_site_rec.vendor_id        := lr_existing_vendor_site_rec.vendor_id;

  lr_vendor_site_rec.org_id           := lr_existing_vendor_site_rec.org_id;

  lr_vendor_site_rec.inactive_date    := SYSDATE;

 

  AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE(p_api_version      => p_api_version,

                                       p_init_msg_list    => p_init_msg_list,

                                       p_commit           => p_commit,

                                       p_validation_level => p_validation_level,

                                       x_return_status    => x_return_status,

                                       x_msg_count        => x_msg_count,

                                       x_msg_data         => x_msg_data,

                                       p_vendor_site_rec  => lr_vendor_site_rec,

                                       p_vendor_site_id   => p_vendor_site_id,

                                       p_calling_prog     => p_calling_prog);

                                      

  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);

 

END;

/

Test Results:

clip_image001[4]

clip_image003[4]

 

by Team search · 2

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.