Below script will help you to fetch the formatted primary phone number of a Party.
Test Instance: R12
API Used: HZ_FORMAT_PHONE_V2PUB.PHONE_DISPLAY
Script:
SET serveroutput on;
DECLARE
v_party_id NUMBER := 81251; -- Party id
v_telno VARCHAR2 (100);
v_faxno VARCHAR2 (100);
v_contact_id NUMBER;
v_status VARCHAR2 (100);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (100);
BEGIN
SELECT contact_point_id
INTO v_contact_id
FROM hz_contact_points hcp, hz_parties hp
WHERE 1 = 1
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcp.owner_table_id = hp.party_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.status = 'A'
AND hcp.phone_line_type = 'GEN'
AND hp.party_id = v_party_id
AND hcp.primary_flag = 'Y'
AND ROWNUM = 1;
BEGIN
hz_format_phone_v2pub.phone_display
(p_init_msg_list => 'T',
p_contact_point_id => v_contact_id,
x_formatted_phone_number => v_faxno,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('x_msg_data ' || v_msg_data);
DBMS_OUTPUT.put_line ('x_msg_count ' || v_msg_count);
END;
DBMS_OUTPUT.put_line ('FAX NUMBER: ' || v_faxno);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO TELEPHONE NUMBER --> OTHER EXCEPTION ');
END;
No comments:
Post a Comment