[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hl.location_id , hps.party_site_id
FROM hz_locations hl,
hz_party_sites hps
WHERE hl.country =p_country
AND hl.address1 =p_address1
AND hl.address2 = decode(p_address2,NULL,hl.address2,p_address2)
AND hl.address3 = decode(p_address3,NULL,hl.address3,p_address3)
AND hl.address4 = decode(p_address4,NULL,hl.address4,p_address4)
AND hl.city = decode(p_city, NULL, hl.city,p_city)
AND hl.postal_code = decode(p_postal_code, NULL, hl.postal_code,p_postal_code)
AND hl.state = decode(p_state, NULL, hl.state,p_state)
AND hl.Province = decode(p_Province, NULL, hl.Province,p_Province)
AND hl.county = decode(p_county, NULL, hl.county,p_county)
AND hl.location_id = hps.location_id
AND hps.party_id = p_party_id
AND hps.party_site_number=p_location_code;
SELECT pv.vendor_name
FROM po_vendors pv
WHERE pv.vendor_id = p_vendor_id;
SELECT PARTY_ID INTO x_party_id
FROM PO_VENDORS
WHERE VENDOR_ID = p_vendor_id;
SELECT pv.vendor_name
FROM po_vendors pv
WHERE pv.vendor_id = p_vendor_id;
SELECT 'X'
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND class_category = 'POS_CLASSIFICATION'
AND class_code = 'PROCUREMENT_ORGANIZATION'
AND status = 'A'
AND (end_date_active IS NULL OR end_date_active > SYSDATE);
PROCEDURE Update_Hz_Location(
P_location_id IN number,
P_address1 IN varchar2,
P_address2 IN varchar2,
P_address3 IN varchar2,
P_address4 IN varchar2,
P_city IN varchar2,
P_postal_code IN varchar2,
P_state IN varchar2,
P_Province IN varchar2,
P_county IN varchar2,
p_country IN varchar2,
x_return_status OUT NOCOPY varchar2) IS
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Update_Hz_Location';
select object_version_number
from hz_locations
where location_id = p_location_id;
HZ_LOCATION_V2PUB.Update_Location
(
p_init_msg_list => FND_API.G_FALSE,
p_location_rec => l_loc_rec,
p_object_version_number => l_location_object_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
WSH_DEBUG_SV.log(l_module_name,'HZ_LOCATION_V2PUB.Update_Location l_return_status',l_return_status);
END Update_Hz_Location;
SELECT 1
FROM hz_relationships h2,
hz_parties hp,
fnd_user fu
WHERE h2.subject_type = 'ORGANIZATION'
and h2.object_type = 'PERSON'
and h2.relationship_type = 'POS_EMPLOYMENT'
and h2.relationship_code = 'EMPLOYER_OF'
and h2.subject_table_name = 'HZ_PARTIES'
and h2.object_table_name = 'HZ_PARTIES'
and h2.status = 'A'
and h2.start_date <= sysdate
and h2.end_date >= sysdate
and h2.object_id = fu.person_party_id -- IB-PHASE-2 Vendor merge
and h2.subject_id = hp.party_id
and hp.party_name = p_supplier_name
and fu.user_id = p_user_id
and h2.subject_id IN
(select owner_table_id
from hz_code_assignments
where owner_table_name='HZ_PARTIES'
and status = 'A'
and class_category ='POS_PARTICIPANT_TYPE'
and class_code='VENDOR');
SELECT 1
FROM pos_supplier_users_v pos, hz_parties hz
WHERE pos.user_id = p_user_id
AND pos.vendor_party_id = hz.party_id
AND hz.party_name = p_supplier_name;
SELECT vendor_id
FROM po_vendors
WHERE vendor_name =ltrim(rtrim(p_vendor_name))
AND (end_date_active IS NULL OR end_date_active >= SYSDATE); -- IB-phase-2 vendor merge
SELECT party_site_use_id
FROM hz_party_site_uses
WHERE party_site_id=p_party_site_id
AND site_use_type = p_site_use_type;
PROCEDURE Update_HZ_contact(
P_person_id IN NUMBER,
P_person_name IN VARCHAR2,
P_old_person_name IN VARCHAR2,
P_phone_contact_point_id IN NUMBER,
P_phone IN VARCHAR2,
P_old_phone IN VARCHAR2,
P_email_contact_point_id IN NUMBER,
P_email IN VARCHAR2,
P_old_email IN VARCHAR2,
p_owner_table_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_person_rec HZ_PARTY_V2PUB.person_rec_type;
select object_version_number
from hz_parties
where party_id = p_person_party_id;
select object_version_number
from hz_contact_points
where contact_point_id = p_contact_point_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Update_HZ_contact';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_V2PUB.Update_Person',WSH_DEBUG_SV.C_PROC_LEVEL);
HZ_PARTY_V2PUB.Update_Person (
p_init_msg_list => FND_API.G_FALSE,
p_person_rec => l_person_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.log(l_module_name,'HZ_PARTY_V2PUB.Update_Person l_return_status',l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program Unit HZ_CONTACT_POINT_V2PUB.Update_Contact_Point for Phone',WSH_DEBUG_SV.C_PROC_LEVEL);
HZ_CONTACT_POINT_V2PUB.Update_Contact_Point(
p_init_msg_list => FND_API.G_FALSE,
p_contact_point_rec => l_contact_points_rec_type,
p_phone_rec => l_phone_rec_type,
p_object_version_number => l_cont_point_version,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
WSH_DEBUG_SV.log(l_module_name,'HZ_CONTACT_POINT_V2PUB.Update_Contact_Point l_return_status',l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program Unit HZ_CONTACT_POINT_V2PUB.Update_Contact_Point',WSH_DEBUG_SV.
C_PROC_LEVEL);
HZ_CONTACT_POINT_V2PUB.Update_Contact_Point(
p_init_msg_list => FND_API.G_FALSE,
p_contact_point_rec => l_contact_points_rec_type,
p_email_rec => l_email_rec_type,
p_object_version_number => l_cont_point_version,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
WSH_DEBUG_SV.log(l_module_name,'HZ_CONTACT_POINT_V2PUB.Update_Contact_Point l_return_status',l_return_status);
END Update_HZ_contact;
select contact_person.party_id l_contact_person_id,
contact_person.party_name shipper_name,
phone_record.contact_point_id phone_contact_point_id,
phone_record.phone_number phone_number,
email_record.contact_point_id email_contact_point_id,
email_record.email_address,
email_record.owner_table_id,
hrel.relationship_id, -- IB-Phase-2
hrel.end_date end_date -- IB-Phase-2
from hz_party_sites hps,
hz_parties contact_person,
hz_org_contacts supplier_contact,
hz_contact_points phone_record,
hz_contact_points email_record,
hz_relationships hrel
where hrel.subject_id = contact_person.party_id
and hrel.subject_table_name = 'HZ_PARTIES'
and hrel.subject_type = 'PERSON'
and hrel.object_id = hps.party_id
and hrel.object_table_name = 'HZ_PARTIES'
and hrel.object_type = 'ORGANIZATION'
and hrel.relationship_code = 'CONTACT_OF'
and hrel.directional_flag = 'F'
and supplier_contact.party_relationship_id =hrel.relationship_id
and supplier_contact.party_site_id = hps.party_site_id
and phone_record.owner_table_name(+) = 'HZ_PARTIES'
and phone_record.owner_table_id(+) = hrel.party_id
and phone_record.contact_point_type(+) = 'PHONE'
and email_record.owner_table_name = 'HZ_PARTIES'
and email_record.owner_table_id = hrel.party_id
and email_record.contact_point_type = 'EMAIL'
and hps.party_site_id =p_party_site_id
and hps.party_id = p_party_id;
HZ_RELATIONSHIP_V2PUB.update_relationship(
p_init_msg_list => FND_API.g_false,
p_relationship_rec => l_relationship_rec,
p_object_version_number => l_object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
WSH_DEBUG_SV.log(l_module_name,'HZ_RELATIONSHIP_V2PUB.update_relationship l_return_status',l_return_status);
Update_HZ_contact(
P_person_id => l_person_id,
P_person_name => p_person_name,
P_old_person_name => l_person_name,
P_phone_contact_point_id => l_phone_contact_point_id,
P_phone => p_phone,
P_old_phone => l_phone,
P_email_contact_point_id => l_email_contact_point_id,
P_email => p_email,
P_old_email => l_email,
p_owner_table_id => l_owner_table_id,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Update_HZ_contact l_return_status',l_return_status);
PROCEDURE Update_Address(
P_location_id IN number,
P_party_id IN number,
P_party_site_id IN number,
P_address1 IN varchar2,
P_address2 IN varchar2,
P_address3 IN varchar2,
P_address4 IN varchar2,
P_city IN varchar2,
P_postal_code IN varchar2,
P_state IN varchar2,
P_Province IN varchar2,
P_county IN varchar2,
p_country IN varchar2,
p_shipper_name IN varchar2,
p_phone IN varchar2,
p_email IN varchar2,
x_return_status OUT NOCOPY varchar2) IS
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Update_Address';
Update_Hz_Location(
P_location_id => p_location_id,
P_address1 => P_address1,
P_address2 => P_address2,
P_address3 => P_address3,
P_address4 => P_address4,
P_city => P_city,
P_postal_code => P_postal_code,
P_state => P_state,
P_province => P_province,
P_county => P_county,
p_country => p_country,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Update_Hz_Location l_return_status',l_return_status);
END Update_Address;
SELECT ps.location_id,ps.party_site_id,ps.status -- IB-Phase-2 Vendor Merge
FROM hz_party_sites ps,hz_party_site_uses psu
WHERE ps.party_site_id = psu.party_site_id
AND psu.site_use_type = 'SUPPLIER_SHIP_FROM'
and party_site_number=p_location_code||'|'||p_party_id
and party_id =p_party_id;
g_error_tbl.delete;
Update_address(
P_location_id => l_location_id,
P_party_id => l_party_id,
P_party_site_id => l_party_site_id,
P_address1 => p_Address.ship_from_address1(l_index),
P_address2 => p_Address.ship_from_address2(l_index),
P_address3 => p_Address.ship_from_address3(l_index),
P_address4 => p_Address.ship_from_address4(l_index),
P_city => p_Address.ship_from_city(l_index),
P_postal_code => p_Address.ship_from_postal_code(l_index),
P_state => p_Address.ship_from_state(l_index),
P_province => p_Address.ship_from_province(l_index),
P_county => p_Address.ship_from_county(l_index),
p_country => p_Address.ship_from_country(l_index),
p_shipper_name => p_Address.shipper_name(l_index),
p_phone => p_Address.phone(l_index),
p_email => p_Address.email(l_index),
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Update_Address l_return_status',l_return_status);