Loading

Tuesday, March 8, 2011

API to Update Customer Address in Oracle TCA R12 (HZ_LOCATION_V2PUB.UPDATE_LOCATION)

                                                                                                                    

With the help of the below script, we can update the existing customer address stored in the table “HZ_LOCATIONS”.
API:  HZ_LOCATION_V2PUB.UPDATE_LOCATION
Test Instance:  R12.1.3

Script:

SET SERVEROUTPUT ON;
DECLARE
p_location_rec          HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_object_version_number NUMBER;
x_return_status         VARCHAR2(2000);
x_msg_count             NUMBER;
x_msg_data              VARCHAR2(2000);

BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id      => 1318
                            ,resp_id      => 50559
                            ,resp_appl_id => 222);
mo_global.set_policy_context('S',204);
fnd_global.set_nls_context('AMERICAN');

-- Initializing the Mandatory API parameters
p_location_rec.location_id := 28195;  -- HZ_LOCATIONS.LOCATION_ID
p_location_rec.address2    := FND_API.G_MISS_CHAR;
p_location_rec.address1    := 'test Oracle Apps 1';
p_object_version_number    := 1;

DBMS_OUTPUT.PUT_LINE('Calling the API hz_location_v2pub.update_location');

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;
    DBMS_OUTPUT.PUT_LINE('Creation of Location is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');
    DBMS_OUTPUT.PUT_LINE('New Address1: '||p_location_rec.address1);
ELSE
    DBMS_OUTPUT.put_line ('Creation 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;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
/

2 Responses to “API to Update Customer Address in Oracle TCA R12 (HZ_LOCATION_V2PUB.UPDATE_LOCATION)”

Amit Singh Sisodia said...
May 28, 2014 at 1:52 AM

Thank you! This helped me a lot. :)


SHIRAZZZZ!!!!!!!!!! said...
March 24, 2015 at 1:20 AM

Calling the API hz_location_v2pub.update_location
Creation of Location failed:The following SQL error occurred:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small.
Status:U
1)
Completion of API
_______________________
bewlow is the error it is giving. Please help. I tried everywhere to debig. couldn't find the exact solution


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.