Saturday, May 22, 2010

TCA API Example: HZ_CONTACT_POINT_V2PUB.update_contact_point


DECLARE
l_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
l_email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type;
l_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;

l_contact_point_type VARCHAR2(100) := 'EMAIL' ;--'PHONE';
l_primary_flag VARCHAR2(1) := 'Y';
l_area_code HZ_CONTACT_POINTS.phone_area_code % TYPE := '953';
l_phone_number HZ_CONTACT_POINTS.phone_number % TYPE := '9667255';
l_email HZ_CONTACT_POINTS.email_address % TYPE := 's3@b.com';
l_obj_num NUMBER := 3;

p_party_id HZ_PARTIES.party_id % TYPE;
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_return_status VARCHAR2(1000);
BEGIN

l_email_rec.email_address := l_email;

l_phone_rec.phone_area_code := l_area_code;
l_phone_rec.phone_number := l_phone_number;
l_phone_rec.phone_line_type := 'PHONE';

l_contact_point_rec.contact_point_type := l_contact_point_type; --PHONE primary and secondary
l_contact_point_rec.status := 'A';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.owner_table_id := p_party_id;
l_contact_point_rec.primary_flag := l_primary_flag;
l_contact_point_rec.content_source_type := 'USER_ENTERED';
l_contact_point_rec.contact_point_id := 9645530;

HZ_CONTACT_POINT_V2PUB.update_contact_point
( p_init_msg_list => fnd_api.g_false
, p_contact_point_rec => l_contact_point_rec
, p_email_rec => l_email_rec
, p_phone_rec => l_phone_rec
, p_object_version_number => l_obj_num
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
dbms_output.put_line('Return Status :' || x_return_status);
IF x_return_status <> 'S'
THEN
FOR k in 1 .. x_msg_count loop
x_msg_data := fnd_msg_pub.get
( p_msg_index => k
, p_encoded => 'F'
) ;
dbms_output.put_line('Error:' || x_msg_data);
END LOOP;
END IF;
END;

7 comments:

  1. Thank you so much.
    Its great... absolutely ready to use... Amazing.

    ReplyDelete
  2. Its great but i am getting the below error.

    Error:This record in table hz_contact_points cannot be locked as it has been updated by another user.

    How can we resolve this

    ReplyDelete
    Replies
    1. This probably means that the value you are trying to update is getting updated twice,i.e., there is a duplicate record.

      Delete
    2. I think the object version number should be current (or maximum object version number) of the contact point I'd.

      Delete
    3. Object_version_number column value. Check the hz_contact_points table column Object_version_number value in l_obj_num.

      By
      CP

      Delete
  3. Error:This record in table hz_contact_points cannot be locked as it has been updated by another user.

    How can we resolve this

    ReplyDelete