The following lines contain the word 'select', 'insert', 'update' or 'delete':
select vendor_id, vendor_name, party_id, segment1 from ap_suppliers
where vendor_id = l_vendor_id;
select employee_id, user_name,email_address into
l_employee_id, l_username, l_email_address
from fnd_user
where user_id = p_user_id;
FUNCTION get_update_date_from_contact (
p_contact_id IN NUMBER
) RETURN DATE
IS
l_date DATE;
SELECT last_update_date
INTO l_date
FROM hz_contact_points
WHERE contact_point_id = p_contact_id;
END get_update_date_from_contact;
SELECT count(contact_point_id)
INTO l_count
FROM hz_contact_points hcp
WHERE hcp.owner_table_name = owner_table_name
AND hcp.owner_table_id = p_party_id
AND hcp.contact_point_type = p_contact_point_type
AND hcp.phone_line_type = p_phone_line_type
AND hcp.status = 'A' ;
SELECT count(contact_point_id)
INTO l_count
FROM hz_contact_points hcp
WHERE hcp.owner_table_name = p_owner_table_name
AND hcp.owner_table_id = p_party_id
AND hcp.contact_point_type = p_contact_point_type
AND hcp.primary_flag = 'Y'
AND hcp.status = 'A';
SELECT count(contact_point_id)
INTO l_count
FROM hz_contact_points hcp
WHERE hcp.owner_table_name = p_owner_table_name
AND hcp.owner_table_id = p_party_id
AND hcp.contact_point_type = p_contact_point_type
AND hcp.web_type = p_web_type
AND hcp.primary_flag = 'Y'
AND hcp.status = 'A';
PROCEDURE update_address_note (
p_party_site_id IN NUMBER
, p_note IN VARCHAR2
, x_status out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS
l_step NUMBER;
POS_ADDRESS_NOTES_PKG.update_note(
p_party_site_id
,p_note
,x_status
,x_exception_msg
);
END update_address_note;
PROCEDURE update_address_type (
p_party_site_use_id IN NUMBER
, p_status IN VARCHAR2
, p_object_version_number IN NUMBER
, x_status out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS
l_msg_count NUMBER;
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_obj_no,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => x_exception_msg
);
END update_address_type;
PROCEDURE update_all_address_type (
p_party_site_id IN NUMBER
, p_rfq IN VARCHAR2
, p_pur IN VARCHAR2
, p_pay IN VARCHAR2
, p_primaryPay IN VARCHAR2
, x_status out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS
l_step NUMBER;
select pay.party_site_use_id, pur.party_site_use_id, rfq.party_site_use_id,
pay.object_version_number, pur.object_version_number, rfq.object_version_number,
pay.status, pur.status, rfq.status, hps.party_site_name
into l_pay_use_id, l_pur_use_id, l_rfq_use_id, l_pay_obj_no, l_pur_obj_no, l_rfq_obj_no,
l_pay_status, l_pur_status, l_rfq_status , l_party_site_name
from hz_party_sites hps ,hz_party_site_uses pay, hz_party_site_uses pur, hz_party_site_uses rfq
where hps.party_site_id = p_party_site_id
--and hps.created_by_module like 'POS%'
and pay.party_site_id(+) = hps.party_site_id
and pur.party_site_id(+) = hps.party_site_id
and rfq.party_site_id(+) = hps.party_site_id
and pay.status(+) = 'A'
and pur.status(+) = 'A'
and rfq.status(+) = 'A'
and nvl(pay.end_date(+), sysdate) >= sysdate
and nvl(pur.end_date(+), sysdate) >= sysdate
and nvl(rfq.end_date(+), sysdate) >= sysdate
and nvl(pay.begin_date(+), sysdate) <= sysdate
and nvl(pur.begin_date(+), sysdate) <= sysdate
and nvl(rfq.begin_date(+), sysdate) <= sysdate
and pay.site_use_type(+) = 'PAY'
and pur.site_use_type(+) = 'PURCHASING'
and rfq.site_use_type(+) = 'RFQ';
POS_PROFILE_PKG.update_address_type (
p_party_site_use_id => l_rfq_use_id
, p_status => l_status
, p_object_version_number => l_rfq_obj_no
, x_status => x_status
, x_exception_msg => x_exception_msg
);
POS_PROFILE_PKG.update_address_type (
p_party_site_use_id => l_pur_use_id
, p_status => l_status
, p_object_version_number => l_pur_obj_no
, x_status => x_status
, x_exception_msg => x_exception_msg
);
POS_PROFILE_PKG.update_address_type (
p_party_site_use_id => l_pay_use_id
, p_status => l_status
, p_object_version_number => l_pay_obj_no
, x_status => x_status
, x_exception_msg => x_exception_msg
);
END update_all_address_type;
PROCEDURE update_party_email(
p_party_id IN NUMBER
, p_party_type IN VARCHAR2
, p_email IN VARCHAR2
, x_status OUT NOCOPY VARCHAR2
, x_exception_msg OUT NOCOPY VARCHAR2
)
IS
l_contact_point_id NUMBER;
l_update_date DATE;
x_exception_msg := 'BEGIN: update_party_email';
SELECT hcp.contact_point_id, nvl(hcp.object_version_number,0), hcp.email_address
INTO l_contact_point_id, l_object_version_number,l_old_email
FROM hz_contact_points hcp
WHERE hcp.owner_table_name = p_party_type
AND hcp.owner_table_id = p_party_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.primary_flag = 'Y'
AND HCP.STATUS = 'A';
l_update_date := get_update_date_from_contact(l_contact_point_id);
HZ_CONTACT_POINT_V2PUB.update_contact_point(
--p_api_version => 1.0,
--p_commit => fnd_api.g_false,
p_contact_point_rec => l_contact_points_rec,
p_email_rec => l_email_rec,
p_object_version_number => l_object_version_number,
--p_last_update_date => l_update_date, --get_update_date_from_contact(l_contact_point_id),
x_return_status => return_status,
x_msg_count => msg_count,
x_msg_data => msg_data);
END update_party_email;
PROCEDURE update_party_phone(
p_party_id IN NUMBER
, p_party_type IN VARCHAR2
, p_country_code IN VARCHAR2
, p_area_code IN VARCHAR2
, p_number IN VARCHAR2
, p_extension IN VARCHAR2
, x_status OUT NOCOPY VARCHAR2
, x_exception_msg OUT NOCOPY VARCHAR2
)
IS
l_contact_point_id NUMBER;
l_update_date DATE;
x_exception_msg := 'BEGIN: update_party_phone';
x_exception_msg := 'SELECT: contact_point_id';
SELECT hcp.contact_point_id, nvl(hcp.object_version_number,0), hcp.phone_number
INTO l_contact_point_id, l_object_version_number, l_old_number
FROM hz_contact_points hcp
WHERE hcp.owner_table_name = p_party_type
AND hcp.owner_table_id = p_party_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.primary_flag = 'Y'
AND hcp.status = 'A';
l_update_date := get_update_date_from_contact(l_contact_point_id);
x_exception_msg := 'CALL: HZ_CONTACT_POINT_V2PUB.update_contact_point';
HZ_CONTACT_POINT_V2PUB.update_contact_point(
--p_api_version => 1.0,
--p_commit => fnd_api.g_false,
p_contact_point_rec => l_contact_points_rec,
p_phone_rec => l_phone_rec,
p_object_version_number => l_object_version_number,
--p_last_update_date => l_update_date, --get_update_date_from_contact(l_contact_point_id),
x_return_status => return_status,
x_msg_count => msg_count, x_msg_data => msg_data);
raise_application_error(-20005,'Update phone number failed: '||x_exception_msg, true);
END update_party_phone;
PROCEDURE update_party_fax(
p_party_id IN NUMBER
, p_party_type IN VARCHAR2
, p_country_code IN VARCHAR2
, p_area_code IN VARCHAR2
, p_number IN VARCHAR2
, p_extension IN VARCHAR2
, x_status OUT NOCOPY VARCHAR2
, x_exception_msg OUT NOCOPY VARCHAR2
)
IS
l_contact_point_id NUMBER;
l_update_date DATE;
x_exception_msg := 'BEGIN: update_party_fax';
SELECT hcp.contact_point_id, nvl( hcp.object_version_number,0), hcp.phone_number
INTO l_contact_point_id, l_object_version_number, l_old_number
FROM hz_contact_points hcp
WHERE hcp.owner_table_name = p_party_type
AND hcp.owner_table_id = p_party_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'FAX'
AND hcp.status = 'A' ;
l_update_date := get_update_date_from_contact(l_contact_point_id);
HZ_CONTACT_POINT_V2PUB.update_contact_point(
--p_api_version => 1.0,
--p_commit => fnd_api.g_false,
p_contact_point_rec => l_contact_points_rec,
p_phone_rec => l_phone_rec,
p_object_version_number =>l_object_version_number,
x_return_status => return_status,
x_msg_count => msg_count, x_msg_data => msg_data);
END update_party_fax;
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_status out nocopy VARCHAR2,
x_exception_msg out nocopy VARCHAR2
)
IS
l_step NUMBER;
lv_proc_name VARCHAR2(30) := 'buyer_update_address_site';
POS_PROFILE_PKG.update_all_address_type(
p_party_site_id => p_party_site_id
, p_rfq => p_rfqFlag
, p_pur => p_purFlag
, p_pay => p_payFlag
, p_primaryPay => p_primaryPayFlag
, x_status => x_status
, x_exception_msg => x_exception_msg
);
POS_PROFILE_PKG.update_address_note(
p_party_site_id => p_party_site_id
, p_note => p_note
, x_status => x_status
, x_exception_msg => x_exception_msg
);
update_party_phone(
p_party_id => p_party_site_id,
p_party_type => 'HZ_PARTY_SITES',
p_country_code => null,
p_area_code => p_phone_area_code ,
p_number => p_phone,
p_extension => null,
x_status => x_status,
x_exception_msg => x_exception_msg
);
raise_application_error(-20006,'Update Address: Failed to add phone for party site:'||p_party_site_id, true);
update_party_fax(
p_party_id => p_party_site_id,
p_party_type => 'HZ_PARTY_SITES',
p_country_code => null,
p_area_code => p_fax_area_code ,
p_number => p_fax,
p_extension => null,
x_status => x_status,
x_exception_msg => x_exception_msg
);
raise_application_error(-20006,'Update Address: Failed to add fax for party site:'||p_party_site_id, true);
update_party_email(
p_party_id => p_party_site_id,
p_party_type => 'HZ_PARTY_SITES',
p_email => p_email,
x_status => x_status,
x_exception_msg => x_exception_msg
);
raise_application_error(-20006,'Update Address: Failed to add phone for party site:'||p_party_site_id, true);
END buyer_update_address_details;
select vendor_site_id, party_site_id, vendor_id from ap_supplier_sites_all where party_site_id = p_party_site_id;
select distinct ASCS.per_party_id
from ap_supplier_contacts ASCS
where (ASCS.inactive_date is null OR ASCS.inactive_date > sysdate)
AND ASCS.org_party_site_id = p_party_site_id;
select uses.instrument_id, uses.order_of_preference, uses.start_date,
payee.supplier_site_id, payee.org_id, payee.org_type,
hps.party_id, uses.instrument_payment_use_id
from iby_pmt_instr_uses_all uses, iby_external_payees_all payee, hz_party_sites hps
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.payee_party_id = hps.party_id
and payee.payment_function = 'PAYABLES_DISB'
and payee.party_site_id = hps.party_site_id
and hps.party_site_id = p_party_site_id
and (uses.end_date is null OR uses.end_date > sysdate)
order by uses.order_of_preference;
select object_version_number, created_by_module
into l_obj_ver, l_created_by_module
from hz_party_sites
where party_site_id = p_party_site_id;
hz_party_site_v2pub.update_party_site(FND_API.G_FALSE,
l_party_site_rec,
l_obj_ver,
x_status,
l_msg_count,
x_exception_msg);
POS_VENDOR_PUB_PKG.Update_Vendor_Site
(
l_vendor_site_rec,
l_return_status,
l_msg_count,
l_msg_data
);
' AP Update Vendor Site Status ' || l_return_status);
' AP Update Vendor Site Count ' || l_msg_count);
' AP Update Vendor Site exception msg ' || l_msg_data);
' AP Update Vendor Contact Status ' || l_return_status);
' AP Update Vendor Contact Count ' || l_msg_count);
' AP Update Vendor Contact exception msg ' || l_msg_data);