/********************************************************************************
*PURPOSE: To Customer Locations information from back-end *
*AUTHOR: Shailender Thallam *
*********************************************************************************/
SET SERVEROUTPUT ON;
DECLARE
—
–Cursor to fetch location details
—
cursor cur_loc
IS
select hps.location_id ,la.address1,
la.address2,
la.address3,
la.address4
from apps.hz_party_sites hps,XXDMC_LOCATION_ADDRESS la
where hps.PARTY_SITE_NUMBER=la.PARTY_SITE_NUMBER
and hps.party_id =la.party_id
–and location_id in( select hps.location_id from apps.hz_party_sites hps,xxdmc_location_address la where hps.party_id =la.party_id)
; — Modify this query as per your requirements
—
p_location_rec hz_location_v2pub.location_rec_type;
p_object_version_number NUMBER;
—
l_user_id NUMBER;
l_responsibility_id NUMBER;
l_application_id NUMBER;
—
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
—
— Setting the Context
—
mo_global.init (‘AR’);
—
–Finding user_id, responsibility_id and application_id
—
SELECT fu.user_id, frt.responsibility_id, frt.application_id–,fu.user_name
INTO l_user_id, l_responsibility_id, l_application_id
FROM fnd_user fu,
fnd_user_resp_groups_direct furgd,
fnd_responsibility_tl frt
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frt.responsibility_id
AND UPPER (fu.user_name) = UPPER (‘CONVERSION’)
AND UPPER (frt.responsibility_name) = UPPER (‘Receivables Manager’);
—
–Apps Initialize
—
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_responsibility_id,
resp_appl_id => l_application_id
);
—
–Setting Org Context
—
mo_global.set_policy_context (‘S’, 3);
—
FOR rec IN cur_loc
LOOP
BEGIN
—
— Initializing the API parameters
—
p_location_rec.location_id := rec.location_id;
— p_location_rec.state := fnd_api.g_miss_char; –making state as NULL
p_object_version_number := rec.object_version_number;
—
p_location_rec.address1 := rec.address1;–‘test address 1’;
p_location_rec.address2 := rec.address2;–‘test address 2’;
p_location_rec.address3 :=rec.address3; –‘test address 3’;
p_location_rec.address4 :=rec.address4;– ‘test address 4’;
–You can add any column from hz_location_v2pub.location_rec_type to update them
—
DBMS_OUTPUT.put_line (‘Calling the API for location_id: ‘||rec.location_id);
—
hz_location_v2pub.update_location
(p_init_msg_list => fnd_api.g_true,
p_location_rec => p_location_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
ELSE
DBMS_OUTPUT.put_line (‘Updation of Location failed: ‘ || x_msg_data);
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => ‘F’);
DBMS_OUTPUT.put_line (i || ‘) ‘ || x_msg_data);
END LOOP;
END IF;
EXCEPTION WHEN OTHERS THEN
—
DBMS_OUTPUT.put_line (‘Un handled exception for location_id: ‘||rec.location_id||’ – Error: ‘||substr(SQLERRM,1,250));
—
END;
END LOOP;
DBMS_OUTPUT.put_line (‘Completion of API’);
END;
/
