Create / Update Contact points

- This creates a contact point phone for the given party
 PROCEDURE createcontactpointphone(
  partyid          IN       hz_contact_points.owner_table_id%TYPE,
  countrycode      IN       hz_contact_points.phone_country_code%TYPE,
  phonenumber      IN       hz_contact_points.phone_number%TYPE,
  contacttype      IN       VARCHAR2,
  contactpurpose   IN       VARCHAR2,
  extn             IN       hz_contact_points.phone_extension%TYPE,
  isprimary        IN       CHAR,
  status           IN       CHAR,
  errormessage     OUT      VARCHAR2
 )
 IS
  currentdate           TIMESTAMP                             := SYSTIMESTAMP;
  x_return_status       VARCHAR2(1);
  x_msg_count           NUMBER;
  x_msg_data            VARCHAR2(2000);
  x_contact_point_id    NUMBER;
  objectversionnumber   NUMBER;
  contact_point_rec     hz_contact_point_v2pub.contact_point_rec_type;
  contacttypecode       hz_contact_points.contact_point_type%TYPE;
  purposetypecode       hz_contact_points.contact_point_purpose%TYPE;
  phonerec              hz_contact_point_v2pub.phone_rec_type;
 BEGIN
--     errormessage:=partyid;
--     return;
      -- This create Phone Contact point
  fnd_msg_pub.delete_msg;

  BEGIN
   SELECT lookup_code
     INTO contacttypecode
     FROM ar_lookups
    WHERE lookup_type = 'PHONE_LINE_TYPE' AND meaning = contacttype;
  EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
    errormessage := ' No Lookup value for the given meaning found in Lookups';
    RETURN;
  END;

  IF (contactpurpose IS NOT NULL)
  THEN
   BEGIN
    SELECT lookup_code
      INTO purposetypecode
      FROM ar_lookups
     WHERE lookup_type = 'CONTACT_POINT_PURPOSE' AND meaning = contactpurpose;
   EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
     errormessage :=
                    ' No Lookup value for the given meaning found in Lookups';
     RETURN;
   END;

   contact_point_rec.contact_point_purpose := purposetypecode;
  END IF;

  contact_point_rec.contact_point_type := 'PHONE';
  phonerec.phone_extension := extn;
  contact_point_rec.status := 'A';
  contact_point_rec.owner_table_name := 'HZ_PARTIES';
  contact_point_rec.owner_table_id := partyid;
  contact_point_rec.primary_flag := isprimary;
  contact_point_rec.created_by_module := l_created_by_module;

  IF (phonerec.phone_country_code IS NULL)
  THEN
   phonerec.phone_country_code := '91';
  ELSE
   phonerec.phone_country_code := countrycode;
  END IF;

  phonerec.phone_number := phonenumber;
  phonerec.phone_line_type := contacttypecode;
  hz_contact_point_v2pub.create_phone_contact_point
                                    (p_init_msg_list        => fnd_api.g_false,
                                     p_contact_point_rec    => contact_point_rec,
                                     p_phone_rec            => phonerec,
                                     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 (fnd_msg_pub.count_msg > 0)
  THEN
   FOR i IN 1 .. fnd_msg_pub.count_msg
   LOOP
    fnd_msg_pub.get(p_msg_index        => i,
                    p_encoded          => 'F',
                    p_data             => x_msg_data,
                    p_msg_index_out    => x_msg_count
                   );
   END LOOP;
  END IF;

  IF (x_return_status <> 'S')
  THEN
   errormessage := x_msg_data;
   ROLLBACK;
  ELSE
   COMMIT;
  END IF;
 EXCEPTION
  WHEN OTHERS
  THEN
   errormessage := SQLERRM;
 END createcontactpointphone;


 -- This updates contact point phone
PROCEDURE updatecontactpointphone(
  contactpointid   IN       NUMBER,
  countrycode      IN       hz_contact_points.phone_country_code%TYPE,
  phonenumber      IN       hz_contact_points.phone_number%TYPE,
  contacttype      IN       VARCHAR2,
  contactpurpose   IN       VARCHAR2,
  extn             IN       hz_contact_points.phone_extension%TYPE,
  isprimary        IN       CHAR,
  status           IN       CHAR,
  errormessage     OUT      VARCHAR2
 )
 IS
  currentdate           TIMESTAMP                             := SYSTIMESTAMP;
  x_return_status       VARCHAR2(1);
  x_msg_count           NUMBER;
  x_msg_data            VARCHAR2(2000);
  objectversionnumber   NUMBER;
  contact_point_rec     hz_contact_point_v2pub.contact_point_rec_type;
  contacttypecode       hz_contact_points.contact_point_type%TYPE;
  purposetypecode       hz_contact_points.contact_point_purpose%TYPE;
  phonerec              hz_contact_point_v2pub.phone_rec_type;
  x_edi_rec             hz_contact_point_v2pub.edi_rec_type;
  x_email_rec           hz_contact_point_v2pub.email_rec_type;
  x_web_rec             hz_contact_point_v2pub.web_rec_type;
  x_telex_rec           hz_contact_point_v2pub.telex_rec_type;
 BEGIN
  -- This create Phone Contact point
  fnd_msg_pub.delete_msg;

  BEGIN
   SELECT lookup_code
     INTO contacttypecode
     FROM ar_lookups
    WHERE lookup_type = 'PHONE_LINE_TYPE' AND meaning = contacttype;
  EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
    errormessage := ' No Lookup value for the given meaning found in Lookups';
    RETURN;
  END;

  -- Get object version number of contact point
  BEGIN
   SELECT object_version_number
     INTO objectversionnumber
     FROM hz_contact_points
    WHERE contact_point_id = contactpointid;
  EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
    errormessage := ' No Such contact pointcfound';
    RETURN;
  END;

  -- get the purpose meaning
  IF (contactpurpose IS NOT NULL)
  THEN
   BEGIN
    SELECT lookup_code
      INTO purposetypecode
      FROM ar_lookups
     WHERE lookup_type = 'CONTACT_POINT_PURPOSE' AND meaning = contactpurpose;
   EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
     errormessage :=
                    ' No Lookup value for the given meaning found in Lookups';
     RETURN;
   END;

   contact_point_rec.contact_point_purpose := purposetypecode;
  END IF;

  hz_contact_point_v2pub.get_contact_point_rec
                                    (p_contact_point_id     => contactpointid,
                                     x_contact_point_rec    => contact_point_rec,
                                     x_edi_rec              => x_edi_rec,
                                     x_email_rec            => x_email_rec,
                                     x_phone_rec            => phonerec,
                                     x_telex_rec            => x_telex_rec,
                                     x_web_rec              => x_web_rec,
                                     x_return_status        => x_return_status,
                                     x_msg_count            => x_msg_count,
                                     x_msg_data             => x_msg_data
                                    );
  phonerec.phone_extension := extn;
  contact_point_rec.status := status;
  phonerec.raw_phone_number := NULL;
  contact_point_rec.primary_flag := isprimary;

  IF (phonerec.phone_country_code IS NULL)
  THEN
   phonerec.phone_country_code := '91';
  ELSE
   phonerec.phone_country_code := countrycode;
  END IF;

  phonerec.phone_number := phonenumber;
  phonerec.phone_line_type := contacttypecode;
  hz_contact_point_v2pub.update_phone_contact_point
                              (p_init_msg_list            => fnd_api.g_false,
                               p_contact_point_rec        => contact_point_rec,
                               p_phone_rec                => phonerec,
                               p_object_version_number    => objectversionnumber,
                               x_return_status            => x_return_status,
                               x_msg_count                => x_msg_count,
                               x_msg_data                 => x_msg_data
                              );

  IF (fnd_msg_pub.count_msg > 0)
  THEN
   FOR i IN 1 .. fnd_msg_pub.count_msg
   LOOP
    fnd_msg_pub.get(p_msg_index        => i,
                    p_encoded          => 'F',
                    p_data             => x_msg_data,
                    p_msg_index_out    => x_msg_count
                   );
   END LOOP;
  END IF;

  IF (x_return_status <> 'S')
  THEN
   errormessage := x_msg_data;
   ROLLBACK;
  ELSE
   COMMIT;
  END IF;
 EXCEPTION
  WHEN OTHERS
  THEN
   errormessage := SQLERRM;
 END updatecontactpointphone;

How to give Debug Grants to a schema

GRANT DEBUG CONNECT SESSION TO <schema>

Find List of Referenced dependencies for a package or procedure or view

select referenced_owner,referenced_name from all_dependencies where  lower(name)='package Name'