The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT party_site_use_id
FROM hz_party_site_uses
WHERE party_site_id = p_party_site_id
AND site_use_type = p_address_type
AND status = 'A';
SELECT party_site_use_id, object_version_number
FROM hz_party_site_uses
WHERE party_site_id = p_party_site_id
AND site_use_type = p_address_type
AND status = 'A';
l_step := 'call hz_party_site_v2pub.update_party_site_use';
hz_party_site_v2pub.update_party_site_use
( p_init_msg_list => FND_API.G_FALSE,
p_party_site_use_rec => l_party_site_use_rec,
p_object_version_number => l_rec.object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FOR x IN (SELECT name
FROM hr_operating_units o
WHERE mo_global.check_access(organization_id) = 'Y'
AND NOT exists
(SELECT 1
FROM ap_system_parameters_all
WHERE o.organization_id = org_id
)
)
LOOP
l_found := TRUE;
pos_hz_contact_point_pkg.update_party_site_phone
(
p_party_site_id => l_party_site_id,
p_country_code => NULL,
p_area_code => p_phone_area_code ,
p_number => p_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_site_fax
(
p_party_site_id => l_party_site_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_site_email
(
p_party_site_id => l_party_site_id,
p_email => p_email_address,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PROCEDURE update_supplier_address
(p_vendor_id IN NUMBER,
p_vendor_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_party_site_name IN VARCHAR2,
p_address_line1 IN VARCHAR2,
p_address_line2 IN VARCHAR2,
p_address_line3 IN VARCHAR2,
p_address_line4 IN VARCHAR2,
p_country IN VARCHAR2,
p_city IN VARCHAR2,
p_state IN VARCHAR2,
p_province IN VARCHAR2,
p_postal_code IN VARCHAR2,
p_county IN VARCHAR2,
p_rfq_flag IN VARCHAR2,
p_pur_flag IN VARCHAR2,
p_pay_flag IN VARCHAR2,
p_primary_pay_flag IN VARCHAR2,
p_phone_area_code IN VARCHAR2,
p_phone_number IN VARCHAR2,
p_phone_extension IN VARCHAR2,
p_fax_area_code IN VARCHAR2,
p_fax_number IN VARCHAR2,
p_email_address IN VARCHAR2,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2
)
IS
l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
SELECT object_version_number,location_id
from hz_locations
where location_id =
(SELECT location_id
FROM hz_party_sites
WHERE party_site_id = p_party_site_id
) FOR UPDATE;
select object_version_number, party_site_name
from hz_party_sites
where party_site_id = p_party_site_id FOR UPDATE;
SAVEPOINT update_supplier_address_sp;
hz_location_v2pub.update_location
( p_init_msg_list => fnd_api.g_true,
p_location_rec => l_location_rec,
p_object_version_number => l_obj_ver,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_address_sp;
hz_party_site_v2pub.update_party_site
(p_init_msg_list => fnd_api.g_false,
p_party_site_rec => l_party_site_rec,
p_object_version_number => l_rec2.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_address_sp;
pos_hz_contact_point_pkg.update_party_site_phone
(
p_party_site_id => p_party_site_id,
p_country_code => NULL,
p_area_code => p_phone_area_code ,
p_number => p_phone_number,
p_extension => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_address_sp;
pos_hz_contact_point_pkg.update_party_site_fax
(
p_party_site_id => p_party_site_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
);
ROLLBACK TO update_supplier_address_sp;
pos_hz_contact_point_pkg.update_party_site_email
(
p_party_site_id => p_party_site_id,
p_email => p_email_address,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_address_sp;
ROLLBACK TO update_supplier_address_sp;
ROLLBACK TO update_supplier_address_sp;
ROLLBACK TO update_supplier_address_sp;
END update_supplier_address;
select ASCS.vendor_site_id, ASCS.relationship_id, ASCS.org_contact_id,
ASCS.rel_party_id, ASCS.party_site_id, ASCS.vendor_contact_id
from ap_supplier_contacts ASCS
where (ASCS.inactive_date is null OR ASCS.inactive_date > sysdate)
AND ASCS.org_party_site_id = p_org_party_site_id
AND ASCS.per_party_id = p_contact_party_id;
' Before Calling pos_vendor_pub_pkg.update_vendor_contact');
AP_VENDOR_PUB_PKG.Update_Vendor_Contact
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_vendor_contact_rec => l_vendor_contact_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
' After Calling pos_vendor_pub_pkg.update_vendor_contact');
SELECT 1
FROM ap_supplier_contacts
WHERE org_party_site_id = p_org_party_site_id
AND per_party_id = p_contact_party_id
AND (inactive_date is null OR inactive_date >= sysdate)
AND rownum = 1;
SELECT hzr.relationship_id, hzr.party_id rel_party_id, hoc.org_contact_id
FROM ap_suppliers pv, hz_relationships hzr, hz_org_contacts hoc
WHERE pv.vendor_id = p_vendor_id
AND hzr.relationship_type = 'CONTACT'
AND hzr.relationship_code = 'CONTACT_OF'
AND hzr.subject_id = p_contact_party_id
AND hzr.subject_type = 'PERSON'
AND hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_type = 'ORGANIZATION'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.object_id = pv.party_id
AND hzr.status = 'A'
AND trunc(SYSDATE) between Trunc(hzr.START_DATE) AND
NVL(Trunc(hzr.END_DATE),trunc(SYSDATE + 1))
AND hzr.relationship_id = hoc.party_relationship_id;
PROCEDURE update_address_assignment_dff
(x_contact_party_id IN NUMBER,
x_org_party_site_id IN NUMBER,
x_vendor_id IN NUMBER,
x_attribute_category IN VARCHAR2 default null,
x_attribute1 IN VARCHAR2 default null,
x_attribute2 IN VARCHAR2 default null,
x_attribute3 IN VARCHAR2 default null,
x_attribute4 IN VARCHAR2 default null,
x_attribute5 IN VARCHAR2 default null,
x_attribute6 IN VARCHAR2 default null,
x_attribute7 IN VARCHAR2 default null,
x_attribute8 IN VARCHAR2 default null,
x_attribute9 IN VARCHAR2 default null,
x_attribute10 IN VARCHAR2 default null,
x_attribute11 IN VARCHAR2 default null,
x_attribute12 IN VARCHAR2 default null,
x_attribute13 IN VARCHAR2 default null,
x_attribute14 IN VARCHAR2 default null,
x_attribute15 IN VARCHAR2 default null,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2
) IS
BEGIN
/* Here We Need To Call AP Package For Updating The Address Assignments */
AP_VENDOR_PUB_PKG.Update_Address_Assignments_DFF
(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contact_party_id => x_contact_party_id,
p_org_party_site_id => x_org_party_site_id,
p_attribute_category=> x_attribute_category,
p_attribute1 => x_attribute1,
p_attribute2 => x_attribute2,
p_attribute3 => x_attribute3,
p_attribute4 => x_attribute4,
p_attribute5 => x_attribute5,
p_attribute6 => x_attribute6,
p_attribute7 => x_attribute7,
p_attribute8 => x_attribute8,
p_attribute9 => x_attribute9,
p_attribute10 => x_attribute10,
p_attribute11 => x_attribute11,
p_attribute12 => x_attribute12,
p_attribute13 => x_attribute13,
p_attribute14 => x_attribute14,
p_attribute15 => x_attribute15,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
' After Calling AP_VENDOR_PUB_PKG.Update_Address_Assignments_DFF ');
END update_address_assignment_dff;
PROCEDURE buyer_update_address_details
(p_party_site_id IN NUMBER,
p_rfqFlag IN VARCHAR2,
p_purFlag IN VARCHAR2,
p_payFlag IN VARCHAR2,
p_primaryPayFlag IN VARCHAR2,
p_note IN VARCHAR2,
p_phone_area_code IN VARCHAR2 DEFAULT NULL,
p_phone IN VARCHAR2 DEFAULT NULL,
p_phone_contact_id IN NUMBER DEFAULT NULL,
p_phone_obj_ver_num IN NUMBER DEFAULT NULL,
p_fax_area_code IN VARCHAR2 DEFAULT NULL,
p_fax IN VARCHAR2 DEFAULT NULL,
p_fax_contact_id IN NUMBER DEFAULT NULL,
p_fax_obj_ver_num IN NUMBER DEFAULT NULL,
p_email IN VARCHAR2 DEFAULT NULL,
p_email_contact_id IN NUMBER DEFAULT NULL,
p_email_obj_ver_num IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_status VARCHAR2(1);
SAVEPOINT update_supplier_address_sp;
pos_hz_contact_point_pkg.update_party_site_phone
(
p_party_site_id => p_party_site_id,
p_country_code => NULL,
p_area_code => p_phone_area_code ,
p_number => p_phone,
p_extension => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_address_sp;
pos_hz_contact_point_pkg.update_party_site_fax
(
p_party_site_id => p_party_site_id,
p_country_code => NULL,
p_area_code => p_fax_area_code ,
p_number => p_fax,
p_extension => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_address_sp;
pos_hz_contact_point_pkg.update_party_site_email
(
p_party_site_id => p_party_site_id,
p_email => p_email,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_supplier_address_sp;
ROLLBACK TO update_supplier_address_sp;
ROLLBACK TO update_supplier_address_sp;
ROLLBACK TO update_supplier_address_sp;
pos_address_notes_pkg.update_note
( p_party_site_id => p_party_site_id,
p_note => p_note,
x_status => l_status,
x_exception_msg => l_msg
);
END buyer_update_address_details;