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

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;