[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT function_id
FROM fnd_form_functions
WHERE function_name = p_function_name;
SELECT vendor_name
FROM ap_suppliers
WHERE vendor_id = p_vendor_id;
SELECT ppf.first_name, ppf.last_name
FROM per_people_f ppf, fnd_user fu
WHERE fu.employee_id = ppf.person_id
AND fu.user_id = p_user_id;
SELECT hp.person_first_name, hp.person_last_name
FROM hz_parties hp, fnd_user fu
WHERE fu.person_party_id = hp.party_id
AND fu.user_id = p_user_id;
SELECT user_name FROM fnd_user WHERE user_id = p_user_id;
SELECT employee_id
FROM fnd_user
WHERE user_name = p_username;
SELECT user_name
FROM fnd_user fu, pos_spmntf_subscription sub
WHERE fu.user_id = sub.user_id
AND sub.event_type = p_event_type;
SELECT Decode(par.party_site_id, NULL, par.party_site_name,
(SELECT hps.party_site_name
FROM hz_party_sites hps
WHERE hps.party_site_id = par.party_site_id
)) address_name
FROM pos_address_requests par
WHERE par.address_request_id = p_address_request_id;
PROCEDURE notify_addr_updated
(p_vendor_id IN NUMBER,
p_address_request_id IN NUMBER,
x_itemtype OUT nocopy VARCHAR2,
x_itemkey OUT nocopy VARCHAR2,
x_receiver OUT nocopy VARCHAR2
)
IS
BEGIN
notify_addr_events
(p_vendor_id => p_vendor_id,
p_address_request_id => p_address_request_id,
p_wf_process => 'PADDR_UPDATED',
x_itemtype => x_itemtype,
x_itemkey => x_itemkey,
x_receiver => x_receiver
);
END notify_addr_updated;
SELECT flv.meaning
FROM fnd_lookup_values flv, pos_bus_class_reqs pbcr
WHERE flv.lookup_type = pbcr.lookup_type
AND flv.lookup_code = pbcr.lookup_code
AND flv.language = userenv('LANG')
AND flv.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
AND pbcr.bus_class_request_id = p_bus_class_request_id;
select classification_id
into l_class_id
from pos_bus_class_reqs
where bus_class_request_id = p_bus_class_request_id;
notify_bus_class_updated (p_vendor_id, p_bus_class_request_id,
x_itemtype, x_itemkey, x_receiver);
PROCEDURE notify_bus_class_updated
(p_vendor_id IN NUMBER,
p_bus_class_request_id IN NUMBER,
x_itemtype OUT nocopy VARCHAR2,
x_itemkey OUT nocopy VARCHAR2,
x_receiver OUT nocopy VARCHAR2
)
IS
BEGIN
notify_bus_class_changed
('PBUS_CLASS_UPDATED', p_vendor_id, p_bus_class_request_id, x_itemtype, x_itemkey, x_receiver);
END notify_bus_class_updated;
SELECT Decode(pcr.contact_party_id, NULL, pcr.first_name || ' ' || pcr.last_name,
(SELECT hp.party_name
FROM hz_parties hp
WHERE hp.party_id = pcr.contact_party_id
)) contact_name
FROM pos_contact_requests pcr
WHERE pcr.contact_request_id = p_contact_request_id;
PROCEDURE notify_contact_updated
(p_vendor_id IN NUMBER,
p_contact_request_id IN NUMBER,
x_itemtype OUT nocopy VARCHAR2,
x_itemkey OUT nocopy VARCHAR2,
x_receiver OUT nocopy VARCHAR2
)
IS
BEGIN
notify_contact_events
('PCONTACT_UPDATED', p_vendor_id, p_contact_request_id, x_itemtype, x_itemkey, x_receiver);
END notify_contact_updated;
SELECT fu.user_name
FROM hz_relationships hzr, hz_parties hp, fnd_user fu
WHERE
fu.person_party_id = hp.party_id
AND fu.email_address IS NOT NULL
AND fu.end_date IS NULL
AND hzr.object_id = p_supplier_party_id
AND hzr.subject_type = 'PERSON'
AND hzr.object_type = 'ORGANIZATION'
AND hzr.relationship_type = 'POS_EMPLOYMENT'
AND hzr.relationship_code = 'EMPLOYEE_OF'
AND hzr.status = 'A'
AND (hzr.start_date IS NULL OR
hzr.start_date <= Sysdate)
AND (hzr.end_date IS NULL OR
hzr.end_date >= Sysdate)
AND hzr.subject_id = hp.party_id
ORDER BY hp.creation_date asc;
SELECT fu.user_name
FROM hz_relationships hzr, hz_parties hp, fnd_user fu, ap_suppliers ap, hz_party_usg_assignments hpua
WHERE fu.person_party_id = hp.party_id
AND fu.email_address IS NOT NULL
AND fu.end_date IS NULL
AND ap.vendor_id = p_vendor_id
AND hzr.object_id = ap.party_id
AND hzr.subject_type = 'PERSON'
AND hzr.object_type = 'ORGANIZATION'
AND hzr.relationship_type = 'CONTACT'
AND hzr.relationship_code = 'CONTACT_OF'
AND hzr.status = 'A'
AND (hzr.start_date IS NULL OR
hzr.start_date <= Sysdate)
AND (hzr.end_date IS NULL OR
hzr.end_date >= Sysdate)
AND hzr.subject_id = hp.party_id
and hpua.party_id = hp.party_id
and hpua.status_flag = 'A'
and hpua.party_usage_code = 'SUPPLIER_CONTACT'
and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate);
SELECT pvsa.email_address, ft.nls_territory, pvsa.language
FROM ap_supplier_sites_all pvsa, fnd_territories ft
WHERE pvsa.email_address IS NOT NULL
AND pvsa.vendor_id = p_vendor_id
AND (pvsa.inactive_date IS NULL OR pvsa.inactive_date IS NOT NULL AND pvsa.inactive_date > Sysdate)
AND pvsa.country = ft.territory_code (+);
select hp.person_first_name, hp.person_last_name, hzr_hp.email_address
from hz_parties hp, hz_relationships hzr, hz_parties hzr_hp, hz_party_usg_assignments hpua, ap_suppliers apsupp
where hp.party_id = hzr.subject_id
and hzr.object_id = apsupp.party_id
and apsupp.vendor_id = p_vendor_id
and hzr.relationship_type = 'CONTACT'
and hzr.relationship_code = 'CONTACT_OF'
and hzr.subject_type ='PERSON'
and hzr.object_type = 'ORGANIZATION'
and (hzr.end_date is null or hzr.end_date > sysdate)
and hzr.status = 'A'
and hzr_hp.party_id = hzr.party_id
and hpua.party_id = hp.party_id
and hpua.status_flag = 'A'
and hpua.party_usage_code = 'SUPPLIER_CONTACT'
and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate)
and hp.party_id not in ( select pcr.contact_party_id
from pos_contact_requests pcr, pos_supplier_mappings psm
where pcr.request_status='PENDING'
and psm.mapping_id = pcr.mapping_id
and psm.PARTY_ID = apsupp.party_id
and pcr.contact_party_id is not null )
and hzr_hp.email_address is not null;
SELECT ou_id
FROM pos_supplier_registrations
WHERE supplier_reg_id = p_supplier_reg_id;
SELECT supplier_name
FROM pos_supplier_registrations
WHERE supplier_reg_id = p_supplier_reg_id;
SELECT fu.email_address
FROM fnd_user fu
WHERE fu.user_id = l_user_id;
SELECT ppf.first_name, ppf.last_name
FROM fnd_user fu, per_people_f ppf
WHERE fu.user_id = l_user_id AND
ppf.person_id = fu.employee_id;
SELECT email_address, first_name, last_name
FROM pos_contact_requests
WHERE mapping_id IN (SELECT mapping_id FROM pos_supplier_mappings WHERE supplier_reg_id = p_supplier_reg_id)
AND do_not_delete = 'Y';
SELECT note_to_supplier
FROM pos_supplier_registrations
WHERE supplier_reg_id = l_supplier_reg_id;
select DISTINCT fu.user_name
from fnd_user fu,
fnd_responsibility fr,
fnd_user_resp_groups_direct furg,
hz_relationships hr1, ap_suppliers ap_sup, hz_party_usg_assignments hpua
where fr.menu_id IN
(SELECT fme.menu_id
FROM fnd_menu_entries fme
START WITH fme.function_id = l_function_id
CONNECT BY PRIOR menu_id = sub_menu_id
)
AND ( furg.end_date is null or furg.end_date > sysdate )
AND furg.security_group_id = 0
AND fr.responsibility_id = furg.responsibility_id
AND fr.application_id = furg.responsibility_application_id
AND fu.user_id = furg.user_id
and fu.person_party_id = hr1.subject_id
and hr1.subject_type = 'PERSON'
and hr1.relationship_type = 'CONTACT'
and hr1.relationship_code = 'CONTACT_OF'
and hr1.object_type = 'ORGANIZATION'
and hr1.status = 'A'
and hr1.start_date <= sysdate
and ( hr1.end_date IS NULL OR hr1.end_date > sysdate)
and hr1.object_id = ap_sup.party_id
and ap_sup.vendor_id = l_vendor_id
and hpua.party_id = hr1.subject_id
and hpua.status_flag = 'A'
and hpua.party_usage_code = 'SUPPLIER_CONTACT'
and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate);
create_adhoc_role('PACCOUNT_UPDATED_'||l_bank_account_number, l_receiver);
select DISTINCT psuv.user_name
from fnd_responsibility fr,
fnd_user_resp_groups_direct furg,
pos_supplier_users_v psuv
where fr.menu_id IN
(SELECT fme.menu_id
FROM fnd_menu_entries fme
START WITH fme.function_id = l_function_id
CONNECT BY PRIOR menu_id = sub_menu_id
)
AND ( furg.end_date is null or furg.end_date > sysdate )
AND furg.security_group_id = 0
AND fr.responsibility_id = furg.responsibility_id
AND fr.application_id = furg.responsibility_application_id
AND psuv.user_id = furg.user_id
AND psuv.vendor_id = p_vendor_id;
PROCEDURE notify_account_update
(p_vendor_id IN NUMBER,
p_bank_name IN VARCHAR2,
p_bank_account_number IN VARCHAR2,
p_currency_code IN VARCHAR2,
p_bank_account_name IN VARCHAR2,
x_itemtype OUT nocopy VARCHAR2,
x_itemkey OUT nocopy VARCHAR2)
IS
l_itemtype wf_items.item_type%TYPE;
l_process := 'PACCOUNT_UPDATED';
wf_core.context(g_package_name,'NOTIFY_ACCOUNT_UPDATE',l_itemtype,l_itemkey);
END notify_account_update;
PROCEDURE notify_buyer_update_account
(p_vendor_id IN NUMBER,
p_bank_name IN VARCHAR2,
p_bank_account_number IN VARCHAR2,
p_currency_code IN VARCHAR2,
p_bank_account_name IN VARCHAR2,
x_itemtype OUT nocopy VARCHAR2,
x_itemkey OUT nocopy VARCHAR2)
IS
l_itemtype wf_items.item_type%TYPE;
l_process := 'PACCT_BUYER_UPDATE';
wf_core.context(g_package_name,'NOTIFY_BUYER_UPDATE_ACCOUNT',l_itemtype,l_itemkey);
END notify_buyer_update_account;
SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address
FROM pos_supplier_registrations psr,
pos_contact_requests pcr,
pos_supplier_mappings psm
WHERE psr.supplier_reg_id = psm.supplier_reg_id
AND psr.supplier_reg_id = p_supplier_reg_id
AND pcr.mapping_id = psm.mapping_id
AND pcr.do_not_delete = 'Y';
SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address, psr.supplier_name
FROM pos_supplier_registrations psr,
pos_contact_requests pcr,
pos_supplier_mappings psm
WHERE psr.supplier_reg_id = psm.supplier_reg_id
AND psr.supplier_reg_id = p_supplier_reg_id
AND pcr.mapping_id = psm.mapping_id
AND pcr.do_not_delete = 'Y';
SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address
FROM pos_supplier_registrations psr,
pos_contact_requests pcr,
pos_supplier_mappings psm
WHERE psr.supplier_reg_id = psm.supplier_reg_id
AND psr.supplier_reg_id = p_supplier_reg_id
AND pcr.mapping_id = psm.mapping_id
AND pcr.do_not_delete = 'Y';
SELECT USER_NAME INTO L_FROM_NAME FROM FND_USER WHERE USER_ID=FND_GLOBAL.USER_ID;
SELECT APS.VENDOR_ID
FROM AP_SUPPLIERS APS
WHERE
(
Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE) + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD')) = Trunc(SYSDATE) + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
OR
(
Trunc(SYSDATE) >= Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD')))
AND
Mod ( To_Number(Trunc(SYSDATE) - Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE)) - To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD'))
,To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
) = 0
)
OR APS.BUS_CLASS_LAST_CERTIFIED_DATE IS NULL
)
and
(
(aps.start_date_active IS NULL OR aps.start_date_active <= SYSDATE) and
(aps.end_date_active IS NULL OR aps.end_date_active >= SYSDATE) AND
(aps.vendor_type_lookup_code IS NULL OR aps.vendor_type_lookup_code <> 'EMPLOYEE')
);
SELECT fu.user_name, fu.email_address
FROM hz_relationships hzr, hz_parties hp, fnd_user fu, ap_suppliers ap, hz_party_usg_assignments hpua
WHERE
fu.user_id in (select spm.user_id from pos_spmntf_subscription spm
where spm.event_type = 'SUPP_BUS_CLASS_RECERT_NTF')
AND fu.person_party_id = hp.party_id
AND fu.email_address IS NOT NULL
--AND (fu.end_date IS NULL OR fu.end_date >= SYSDATE) /* bug 8647019 */
AND Nvl(fu.end_date, SYSDATE) >= sysdate
AND ap.vendor_id = l_vendor_id
AND hzr.object_id = ap.party_id
AND hzr.subject_type = 'PERSON'
AND hzr.object_type = 'ORGANIZATION'
AND hzr.relationship_type = 'CONTACT'
AND hzr.relationship_code = 'CONTACT_OF'
AND hzr.status = 'A'
--AND (hzr.start_date IS NULL OR hzr.start_date <= Sysdate) /* bug 8647019 */
--AND (hzr.end_date IS NULL OR hzr.end_date >= Sysdate)
AND Nvl(hzr.end_date, SYSDATE) >= sysdate
AND hzr.subject_id = hp.party_id
AND hpua.party_id = hp.party_id
AND hpua.status_flag = 'A'
AND hpua.party_usage_code = 'SUPPLIER_CONTACT'
--AND (hpua.effective_end_date IS NULL OR hpua.effective_end_date > Sysdate); /* bug 8647019 */
SELECT item_key
FROM wf_items
WHERE item_key LIKE l_purge_item_key_type
and item_type like l_itemtype;
/* SELECT count(APS.VENDOR_ID)
into l_supplier_count
FROM AP_SUPPLIERS APS
WHERE
(
Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE) + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD')) = Trunc(SYSDATE) + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
OR
(
Trunc(SYSDATE) >= Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD')))
AND
Mod ( To_Number(Trunc(SYSDATE) - Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE)) - To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD'))
,To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
) = 0
)
OR APS.BUS_CLASS_LAST_CERTIFIED_DATE IS NULL
)
and
(
(aps.start_date_active IS NULL OR aps.start_date_active <= SYSDATE) and
(aps.end_date_active IS NULL OR aps.end_date_active >= SYSDATE) AND
(aps.vendor_type_lookup_code IS NULL OR aps.vendor_type_lookup_code <> 'EMPLOYEE')
);
SELECT bus_class_last_certified_date, vendor_name, SYSDATE + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
INTO l_last_certification_date, l_role_display_name, l_expiration_date
FROM ap_suppliers
WHERE vendor_id = l_vendor_id;
select vendor_name
into l_role_display_name
from ap_suppliers
where vendor_id = l_vendor_id;
select SYSDATE + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
into l_expiration_date
from ap_suppliers
where vendor_id = l_vendor_id;
l_users.delete;