Adding new OU's to existing Supplier Site in Oracle R12.1.3 Payables

1. For single Supplier .
2. For All suppliers based on single OU adding to other new OU.


--------------------------------------------------------------------------------------------
 1. For single Supplier .

DECLARE
CURSOR c_vendor_sites IS
SELECT vendor_site_code,
vendor_id,
org_id,
party_site_id,
address_line1,
address_line2,
address_line3,
address_line4,
city,
state,
zip,
province,
county,
country,
area_code,
phone,
fax_area_code,
fax,
email_address,
purchasing_site_flag,
pay_site_flag,
rfq_only_site_flag,
hold_all_payments_flag,
duns_number,location_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = 590615; -- used to create a new vendor site in a different OU

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_vendor_rec_type ap_vendor_pub_pkg.r_vendor_rec_type;
p_api_version NUMBER := 1;
p_commit VARCHAR2(1) := FND_API.G_TRUE;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_vendor_site_id NUMBER;
x_party_site_id NUMBER;
x_location_id NUMBER;
v_vendor_id NUMBER;

BEGIN
FOR r_site IN c_vendor_sites LOOP
v_vendor_id := r_site.vendor_id;

l_vendor_site_rec.vendor_id := r_site.vendor_id;
l_vendor_site_rec.vendor_site_code := r_site.vendor_site_code;
l_vendor_site_rec.org_id := 246; -- Target ORG ID where the new Site will be created
l_vendor_site_rec.party_site_id := r_site.party_site_id;
l_vendor_site_rec.location_id := r_site.location_id ;
l_vendor_site_rec.address_line1 := r_site.address_line1;
l_vendor_site_rec.address_line2 := r_site.address_line2;
l_vendor_site_rec.address_line3 := r_site.address_line3;
l_vendor_site_rec.address_line4 := r_site.address_line4;
l_vendor_site_rec.state := r_site.state;
l_vendor_site_rec.zip := r_site.zip;
l_vendor_site_rec.province := r_site.province;
l_vendor_site_rec.county := r_site.county;
l_vendor_site_rec.country := r_site.country;
l_vendor_site_rec.area_code := r_site.area_code;
l_vendor_site_rec.phone := r_site.phone;
l_vendor_site_rec.fax_area_code := r_site.fax_area_code;
l_vendor_site_rec.fax := r_site.fax;
l_vendor_site_rec.email_address := r_site.email_address;
l_vendor_site_rec.purchasing_site_flag := r_site.purchasing_site_flag;
l_vendor_site_rec.pay_site_flag := r_site.pay_site_flag;
l_vendor_site_rec.rfq_only_site_flag := r_site.rfq_only_site_flag;
l_vendor_site_rec.hold_all_payments_flag := r_site.hold_all_payments_flag;
l_vendor_site_rec.duns_number := r_site.duns_number;
--l_vendor_site_rec.language := 'AMERICAN'; --- Language
--l_vendor_site_rec.address_style := 'POSTAL_ADDR_US'; --- Address Style of the country which needs to be done. Take address style code of the specific country
--DBMS_OUTPUT.PUT_LINE( l_vendor_site_rec.party_site_id );
ap_vendor_pub_pkg.create_vendor_site(p_api_version => p_api_version,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => x_party_site_id,
x_location_id => x_location_id);

IF x_return_status = fnd_api.g_ret_sts_success THEN
DBMS_OUTPUT.PUT_LINE('Vendor Site Created.');
DBMS_OUTPUT.PUT_LINE('New Vendor Site id is : ' || x_vendor_site_id);
DBMS_OUTPUT.PUT_LINE('New Party Site id is : ' || x_party_site_id);
DBMS_OUTPUT.PUT_LINE('New Location Id is : ' || x_location_id);
ELSE
DBMS_OUTPUT.PUT_LINE('Return Status ' || x_return_status );
DBMS_OUTPUT.PUT_LINE('Error Details');
DBMS_OUTPUT.PUT_LINE( x_msg_count );
DBMS_OUTPUT.PUT_LINE( x_msg_data );
END IF;
END LOOP; END;

--------------------------------------------------------------------










2. For All suppliers based on single OU adding to other new Two OU's.

DECLARE

CURSOR c_vendor_sites IS

SELECT vendor_site_code,
apsa.vendor_id, vendor_site_id,
org_id,
party_site_id,
address_line1,
address_line2,
address_line3,
address_line4,
city,
state,
zip,
province,
county,
country,
area_code,
phone,
fax_area_code,
fax,
email_address,
purchasing_site_flag,
pay_site_flag,
rfq_only_site_flag,
apsa.hold_all_payments_flag,
duns_number,location_id
FROM ap_supplier_sites_all apsa, ap_suppliers aps
where apsa.vendor_id = aps.vendor_id--vendor_id = 577807
and apsa.org_id = 102
 --apsa.org_id not in (1601)
and aps.enabled_flag = 'Y';

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_vendor_rec_type ap_vendor_pub_pkg.r_vendor_rec_type;
p_api_version NUMBER := 1;
p_commit VARCHAR2(1) := FND_API.G_TRUE;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_vendor_site_id NUMBER;
x_party_site_id NUMBER;
x_location_id NUMBER;
v_vendor_id NUMBER;

BEGIN
FOR r_site IN c_vendor_sites LOOP
v_vendor_id := r_site.vendor_id;

l_vendor_site_rec.vendor_id := r_site.vendor_id;
l_vendor_site_rec.vendor_site_code := r_site.vendor_site_code;
l_vendor_site_rec.org_id := 246; -- Target ORG ID where the new Site will be created
l_vendor_site_rec.party_site_id := r_site.party_site_id;
l_vendor_site_rec.location_id := r_site.location_id ;
l_vendor_site_rec.address_line1 := r_site.address_line1;
l_vendor_site_rec.address_line2 := r_site.address_line2;
l_vendor_site_rec.address_line3 := r_site.address_line3;
l_vendor_site_rec.address_line4 := r_site.address_line4;
l_vendor_site_rec.state := r_site.state;
l_vendor_site_rec.zip := r_site.zip;
l_vendor_site_rec.province := r_site.province;
l_vendor_site_rec.county := r_site.county;
l_vendor_site_rec.country := r_site.country;
l_vendor_site_rec.area_code := r_site.area_code;
l_vendor_site_rec.phone := r_site.phone;
l_vendor_site_rec.fax_area_code := r_site.fax_area_code;
l_vendor_site_rec.fax := r_site.fax;
l_vendor_site_rec.email_address := r_site.email_address;
l_vendor_site_rec.purchasing_site_flag := r_site.purchasing_site_flag;
l_vendor_site_rec.pay_site_flag := r_site.pay_site_flag;
l_vendor_site_rec.rfq_only_site_flag := r_site.rfq_only_site_flag;
l_vendor_site_rec.hold_all_payments_flag := r_site.hold_all_payments_flag;
l_vendor_site_rec.duns_number := r_site.duns_number;
--l_vendor_site_rec.language := 'AMERICAN'; --- Language
--l_vendor_site_rec.address_style := 'POSTAL_ADDR_US'; --- Address Style of the country which needs to be done. Take address style code of the specific country
--DBMS_OUTPUT.PUT_LINE( l_vendor_site_rec.party_site_id );
ap_vendor_pub_pkg.create_vendor_site(p_api_version => p_api_version,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => x_party_site_id,
x_location_id => x_location_id);

IF x_return_status = fnd_api.g_ret_sts_success THEN
DBMS_OUTPUT.PUT_LINE('Vendor Site Created.');
DBMS_OUTPUT.PUT_LINE('New Vendor Site id is : ' || x_vendor_site_id);
DBMS_OUTPUT.PUT_LINE('New Party Site id is : ' || x_party_site_id);
DBMS_OUTPUT.PUT_LINE('New Location Id is : ' || x_location_id);
ELSE
DBMS_OUTPUT.PUT_LINE('Return Status ' || x_return_status );
DBMS_OUTPUT.PUT_LINE('Error Details');
DBMS_OUTPUT.PUT_LINE( x_msg_count );
DBMS_OUTPUT.PUT_LINE( x_msg_data );
END IF;
END LOOP;
END;