‘API to Create a Phone Number (Contacts) TCA R12 (HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT)’

API to Create a Phone Number (Contacts) TCA R12 (HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT)

                                                                           

DESCRIPTION:

This procedure creates a phone number for the same organization.

API:   HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

BASE TABLES AFFECTED :  HZ_CONTACT_POINTS

TEST INSTANCE : R12.1.3

NOTE:  

·         The Parameter Primary Flag, Contact Point Purpose and Phone Line Type can be entered in either lower case or upper case as the system will take care of changing the  case to upper

·         The values for owner_table_id and owner_table_name parameters can be seen from HZ_PARTIES table using the query :

SELECT owner_table_id,

       owner_table_name

FROM   hz_contact_points

WHERE  owner_table_name LIKE '%HZ_PARTIES%';

·         Enter the value for phone_country_code as a valid value from from hz_phone_country_codes using the query :

SELECT territory_code,

       phone_country_code

FROM   hz_phone_country_codes

WHERE  UPPER(territory_code) LIKE ‘%US%’;

·         Enter the value of area code using the query :

SELECT UPPER(area_code)

FROM   hz_timezone_mapping;

·         Value for contact_point_purpose must be a value defined in lookup type CONTACT_POINT_PURPOSE

·         Value for phone_line_type must be a value defined in lookup type PHONE_LINE_TYPE.

SCRIPT:

SET SERVEROUTPUT ON;

DECLARE

p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;

p_edi_rec           HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;

p_email_rec         HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;

p_phone_rec         HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;

p_telex_rec         HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;

p_web_rec           HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;

x_return_status     VARCHAR2(2000);

x_msg_count         NUMBER;

x_msg_data          VARCHAR2(2000);

x_contact_point_id  NUMBER;

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_contact_point_rec.contact_point_type    := 'PHONE';

p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';

p_contact_point_rec.owner_table_id        := 530682;

p_contact_point_rec.primary_flag          := 'Y';

p_contact_point_rec.contact_point_purpose := 'BUSINESS';

p_contact_point_rec.created_by_module     := 'BO_API';

p_phone_rec.phone_area_code               :=  NULL;

p_phone_rec.phone_country_code            := '1';

p_phone_rec.phone_number                  := '856-784-521';

p_phone_rec.phone_line_type               := 'MOBILE';

DBMS_OUTPUT.PUT_LINE('Calling the API hz_contact_point_v2pub.create_contact_point');

HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

                   (

                     p_init_msg_list      => FND_API.G_TRUE,

                     p_contact_point_rec  => p_contact_point_rec,

                     p_edi_rec            => p_edi_rec,

                     p_email_rec          => p_email_rec,

                     p_phone_rec          => p_phone_rec,

                     p_telex_rec          => p_telex_rec,

                     p_web_rec            => p_web_rec,

                     x_contact_point_id   => x_contact_point_id,

                     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 Contact Point is Successful ');

    DBMS_OUTPUT.PUT_LINE('Output information ....');   

    DBMS_OUTPUT.PUT_LINE('x_contact_point_id = '||x_contact_point_id);

     

ELSE

    DBMS_OUTPUT.put_line ('Creation of Contact Point got failed:'||x_msg_data);

    ROLLBACK;

    FOR i IN 1 .. x_msg_count

    LOOP

      x_msg_data := fnd_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;

/

VERIFICATION SCRIPT:

SELECT contact_point_id,

       contact_point_type,

       owner_table_name,

       owner_table_id,

       primary_flag,

       phone_area_code,

       phone_country_code,

       phone_number,

       phone_line_type,

       raw_phone_number

FROM   hz_contact_points

WHERE  contact_point_id = 1452 ;