[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pos_hz_contact_point_pkg.update_party_phone
(
p_party_id => l_rel_party_id,
p_country_code => NULL,
p_area_code => p_phone_area_code ,
p_number => p_phone_number,
p_extension => p_phone_extension,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pos_hz_contact_point_pkg.update_party_fax
(
p_party_id => l_rel_party_id,
p_country_code => NULL,
p_area_code => p_fax_area_code ,
p_number => p_fax_number,
p_extension => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pos_hz_contact_point_pkg.update_party_email
(
p_party_id => l_rel_party_id,
p_email => p_email_address,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
Name : update_supplier_contact
Description : This procedure is used to update the contact details
like name,email,phone number,fax etc.
Parameters :
IN :
p_contact_party_id - holds party id of the 'PERSON' party record in hz_parties.
p_vendor_party_id - holds party id of the 'ORGANIZATION' party record in hz_parties.
p_first_name - first name of the contact
p_last_name - last name of the contact
p_middle_name - middle name of the contact
p_contact_title - contact title of the contact
p_job_title - job title of the contact
p_phone_number - phone number of the contact
p_fax_number - fax number of the contact
p_email_address - email address of the contact
p_inactive_date - inactive date of the contact
p_party_object_version_number - object version number of the relationship record in hz_parties
p_email_object_version_number - object version number of the email contact in hz_contact_points
p_phone_object_version_number - object version number of the phone contact in hz_contact_points
p_fax_object_version_number - object version number of the fax contact in hz_contact_points
p_rel_object_version_number - object version number of the relationship record in hz_relationships
p_cont_object_version_number - object version number of the hz_org_contacts record
p_person_party_obversion_num - object version number of the 'PERSON' party record in hz_parties
OUT :
x_return_status - returns either success/failure
x_msg_count - returns the number of error messages
x_msg_data - returns error messages
**/
PROCEDURE update_supplier_contact
(p_contact_party_id IN NUMBER,
p_vendor_party_id IN NUMBER,
p_first_name IN VARCHAR2 DEFAULT NULL,
p_last_name IN VARCHAR2 DEFAULT NULL,
p_middle_name IN VARCHAR2 DEFAULT NULL,
p_contact_title IN VARCHAR2 DEFAULT NULL,
p_job_title IN VARCHAR2 DEFAULT NULL,
p_phone_area_code IN VARCHAR2 DEFAULT NULL,
p_phone_number IN VARCHAR2 DEFAULT NULL,
p_phone_extension IN VARCHAR2 DEFAULT NULL,
p_fax_area_code IN VARCHAR2 DEFAULT NULL,
p_fax_number IN VARCHAR2 DEFAULT NULL,
p_email_address IN VARCHAR2 DEFAULT NULL,
p_inactive_date IN DATE DEFAULT NULL,
--Start Bug 6620664 - Handling Concurrent Updates on ContactDirectory, BusinessClassifications ans Accounting pages
p_party_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
p_email_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
p_phone_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
p_fax_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
p_rel_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
p_cont_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
--End Bug 6620664 - Handling Concurrent Updates on ContactDirectory, BusinessClassifications ans Accounting pages
p_person_party_obversion_num IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2
)
IS
CURSOR l_contact_party_cur IS
SELECT person_first_name,
person_last_name,
person_middle_name,
person_pre_name_adjunct,
person_title,
object_version_number
FROM hz_parties
WHERE party_id = p_contact_party_id;
SELECT hoc.org_contact_id,
hoc.job_title,
hoc.object_version_number cont_object_version_number,
hzr.object_version_number rel_object_version_number,
hzr.party_id,
hzr.relationship_id,
hp.object_version_number rel_party_obj_ver_num
FROM hz_org_contacts hoc, hz_relationships hzr, hz_parties hp
WHERE hoc.party_relationship_id = hzr.relationship_id
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.object_id = p_vendor_party_id
AND hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.subject_id = p_contact_party_id
AND hzr.relationship_type = 'CONTACT'
AND hzr.relationship_code = 'CONTACT_OF'
AND hzr.party_id = hp.party_id;
select user_name
from fnd_user
WHERE person_party_id = p_contact_party_id;
SAVEPOINT update_supplier_contact_sp;
ROLLBACK TO update_supplier_contact_sp;
hz_party_contact_v2pub.update_org_contact
(p_init_msg_list => fnd_api.g_true,
p_org_contact_rec => l_org_contact_rec,
--Start Bug 6620664
p_cont_object_version_number => l_cont_object_version_number, --l_rec2.cont_object_version_number,
p_rel_object_version_number => l_rel_object_version_number, --l_rec2.rel_object_version_number,
p_party_object_version_number => l_party_object_version_number, --l_rec2.rel_party_obj_ver_num,
--End Bug 6620664
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_contact_sp;
ROLLBACK TO update_supplier_contact_sp;
hz_party_v2pub.update_person
(p_init_msg_list => fnd_api.g_true,
p_person_rec => l_person_rec,
p_party_object_version_number => l_person_party_obversion_num,
x_profile_id => l_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_contact_sp;
pos_hz_contact_point_pkg.update_party_phone
(
p_party_id => l_rec2.party_id,
p_country_code => NULL,
p_area_code => p_phone_area_code ,
p_number => p_phone_number,
p_extension => p_phone_extension,
--Start Bug 6620664
p_phone_object_version_number => l_phone_object_version_number,
--End Bug 6620664
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_contact_sp;
pos_hz_contact_point_pkg.update_party_fax
(
p_party_id => l_rec2.party_id,
p_country_code => NULL,
p_area_code => p_fax_area_code ,
p_number => p_fax_number,
p_extension => NULL,
--Start Bug 6620664
p_fax_object_version_number => l_fax_object_version_number,
--End Bug 6620664
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_contact_sp;
pos_hz_contact_point_pkg.update_party_email
(
p_party_id => l_rec2.party_id,
p_email => p_email_address,
--Start Bug 6620664
p_email_object_version_number => l_email_object_version_number,
--End Bug 6620664
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
fnd_user_pkg.updateuser
(
x_user_name => l_user_rec.user_name,
x_email_address => p_email_address,
x_owner => NULL,
x_end_date => p_inactive_date
);
ROLLBACK TO update_supplier_contact_sp;
ROLLBACK TO update_supplier_contact_sp;
pos_log.log_sqlerrm('POSCONTB', 'in update_supplier_contact');
END update_supplier_contact;