Search This Blog

Wednesday, April 3, 2013

API to get the formatted contact details of a Party in oracle apps R12 (HZ_FORMAT_PHONE_V2PUB.PHONE_DISPLAY)


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