[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
);
pos_hz_contact_point_pkg.update_party_alt_phone
(
p_party_id => l_rel_party_id,
p_country_code => NULL,
p_area_code => p_alt_area_code,
p_number => p_alt_phone_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_url
(
p_party_id => l_rel_party_id,
p_url => p_url,
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
**/
/*Added one argument p_department for BUG 7938942 */
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,
p_department IN VARCHAR2 DEFAULT NULL,
p_alt_contact_name IN VARCHAR2 DEFAULT NULL,
p_alt_area_code IN VARCHAR2 DEFAULT NULL,
p_alt_phone_number IN VARCHAR2 DEFAULT NULL,
p_url IN VARCHAR2 DEFAULT NULL,
p_url_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
p_altphone_obj_version_num IN NUMBER DEFAULT fnd_api.G_NULL_NUM
)
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,
known_as
FROM hz_parties
WHERE party_id = p_contact_party_id;
SELECT hoc.org_contact_id,
hoc.job_title,
hoc.department,
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;
/*BUG 7938942: Added code to update department field along with job title if need to update*/
IF ((l_rec2.job_title IS NULL AND p_job_title IS NULL) OR
(l_rec2.job_title IS NOT NULL AND l_rec2.job_title = p_job_title))
AND ((l_rec2.department IS NULL AND p_department IS NULL) OR
(l_rec2.department IS NOT NULL AND l_rec2.department = p_department))
AND (not l_enddate_changed)
THEN
NULL;
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
);
pos_hz_contact_point_pkg.update_party_alt_phone
(
p_party_id => l_rec2.party_id,
p_country_code => NULL,
p_area_code => p_alt_area_code ,
p_number => p_alt_phone_number,
p_extension => NULL,
p_phone_object_version_number => l_altphone_obj_version_num,
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_url
(
p_party_id => l_rec2.party_id,
p_url => p_url,
p_url_object_version_number => l_url_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_contact_sp;
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
);
We need to update status flag of HZ_RELATIONSHIPS table for this person party to 'A' irrespective
of whether contact is active or not as in R12 we are making use of end_date to check whether contact
is active or not.
We need to update the status flag in HZ_ORG_CONTACTS also to 'A' for the relationship_id
*/
UPDATE HZ_RELATIONSHIPS
SET
STATUS='A'
WHERE
RELATIONSHIP_ID=(SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS WHERE
SUBJECT_TABLE_NAME='HZ_PARTIES'
AND
SUBJECT_ID=p_contact_party_id
AND
SUBJECT_TYPE='PERSON'
AND
OBJECT_TABLE_NAME='HZ_PARTIES'
AND
OBJECT_ID=p_vendor_party_id
AND
OBJECT_TYPE='ORGANIZATION'
AND
RELATIONSHIP_TYPE='CONTACT'
AND
RELATIONSHIP_CODE='CONTACT_OF'
);
UPDATE HZ_RELATIONSHIPS
SET
STATUS='A'
WHERE
RELATIONSHIP_ID=(SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS WHERE
OBJECT_TABLE_NAME='HZ_PARTIES'
AND
OBJECT_ID=p_contact_party_id
AND
OBJECT_TYPE='PERSON'
AND
SUBJECT_TABLE_NAME='HZ_PARTIES'
AND
SUBJECT_ID=p_vendor_party_id
AND
SUBJECT_TYPE='ORGANIZATION'
AND
RELATIONSHIP_TYPE='CONTACT'
AND
RELATIONSHIP_CODE='CONTACT'
);
UPDATE HZ_ORG_CONTACTS
SET
STATUS='A'
WHERE
PARTY_RELATIONSHIP_ID=(SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS WHERE
SUBJECT_TABLE_NAME='HZ_PARTIES'
AND
SUBJECT_ID=p_contact_party_id
AND
SUBJECT_TYPE='PERSON'
AND
OBJECT_TABLE_NAME='HZ_PARTIES'
AND
OBJECT_ID=p_vendor_party_id
AND
OBJECT_TYPE='ORGANIZATION'
AND
RELATIONSHIP_TYPE='CONTACT'
AND
RELATIONSHIP_CODE='CONTACT_OF');
UPDATE HZ_ORG_CONTACTS
SET
STATUS='A'
WHERE
PARTY_RELATIONSHIP_ID=(SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS WHERE
OBJECT_TABLE_NAME='HZ_PARTIES'
AND
OBJECT_ID=p_contact_party_id
AND
OBJECT_TYPE='PERSON'
AND
SUBJECT_TABLE_NAME='HZ_PARTIES'
AND
SUBJECT_ID=p_vendor_party_id
AND
SUBJECT_TYPE='ORGANIZATION'
AND
RELATIONSHIP_TYPE='CONTACT'
AND
RELATIONSHIP_CODE='CONTACT'
);
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;