The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM pos_supplier_registrations
WHERE supplier_reg_id = p_supplier_reg_id FOR UPDATE;
SELECT ext_attr_1
FROM pos_bus_class_reqs pbcr
, pos_supplier_mappings psm
, pos_supplier_registrations psr
WHERE psm.supplier_reg_id = psr.supplier_reg_id
AND psr.supplier_reg_id = p_supplier_reg_id
AND pbcr.mapping_id = psm.mapping_id
AND pbcr.request_type = 'ADD'
AND pbcr.request_status = 'PENDING'
AND pbcr.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
AND pbcr.lookup_code = p_bus_class_code;
SELECT party_id, object_version_number
FROM hz_parties
WHERE party_id =
(SELECT party_id
FROM ap_suppliers
WHERE vendor_id = p_vendor_id);
hz_party_v2pub.update_organization
(p_init_msg_list => fnd_api.g_false,
p_organization_rec => l_org_rec,
p_party_object_version_number => l_rec.object_version_number,
x_profile_id => l_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT vendor_id
FROM ap_suppliers
WHERE vendor_id = x_vendor_id;
l_step := 'update pos_supplier_mappings with ids';
UPDATE pos_supplier_mappings
SET vendor_id = x_vendor_id,
party_id = x_party_id,
last_updated_by = fnd_global.user_id,
last_update_date = Sysdate,
last_update_login = fnd_global.login_id
WHERE supplier_reg_id = p_supplier_reg_rec.supplier_reg_id;
l_step := 'update pos_supplier_registrations with ids';
UPDATE pos_supplier_registrations
SET registration_status = 'APPROVED',
po_vendor_id = x_vendor_id,
vendor_party_id = x_party_id,
last_updated_by = fnd_global.user_id,
last_update_date = Sysdate,
last_update_login = fnd_global.login_id
WHERE supplier_reg_id = p_supplier_reg_rec.supplier_reg_id;
SELECT par.address_request_id
FROM pos_address_requests par
, pos_supplier_mappings psm
WHERE par.request_type = 'ADD'
AND par.request_status = 'PENDING'
AND par.mapping_id = psm.mapping_id
AND psm.supplier_reg_id = p_supplier_reg_id;
SELECT pcr.contact_request_id, pcr.email_address, psm.vendor_id, create_user_account
FROM pos_contact_requests pcr
, pos_supplier_mappings psm
WHERE pcr.request_type = 'ADD'
AND pcr.request_status = 'PENDING'
AND pcr.mapping_id = psm.mapping_id
AND psm.supplier_reg_id = p_supplier_reg_id;
SELECT user_id
FROM fnd_user
WHERE user_name = Upper(p_user_name);
SELECT registration_type
FROM pos_supplier_registrations
WHERE supplier_reg_id = p_supplier_reg_id;
SELECT pbcr.bus_class_request_id
FROM pos_bus_class_reqs pbcr
, pos_supplier_mappings psm
WHERE pbcr.request_type = 'ADD'
AND pbcr.request_status = 'PENDING'
AND pbcr.mapping_id = psm.mapping_id
AND psm.supplier_reg_id = p_supplier_reg_id;
SELECT ppsr.ps_request_id
FROM pos_product_service_requests ppsr
, pos_supplier_mappings psm
WHERE ppsr.request_type = 'ADD'
AND ppsr.request_status = 'PENDING'
AND ppsr.mapping_id = psm.mapping_id
AND psm.supplier_reg_id = p_supplier_reg_id;
SELECT fu.user_name, fu.user_id
FROM pos_contact_requests pcr, fnd_user fu
WHERE pcr.mapping_id =
(SELECT mapping_id
FROM pos_supplier_mappings
WHERE supplier_reg_id = p_supplier_reg_id
)
AND pcr.request_status = 'APPROVED'
AND pcr.do_not_delete = 'Y'
AND fu.user_name = Upper(pcr.email_address);
SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_party_id
AND party_type_code = 'THIRD_PARTY'
AND ROWNUM = 1;
zx_party_tax_profile_pkg.update_row
(p_party_tax_profile_id => l_party_tax_profile_id,
p_collecting_authority_flag => NULL,
p_provider_type_code => NULL,
p_create_awt_dists_type_code => NULL,
p_create_awt_invoices_type_cod => NULL,
p_tax_classification_code => NULL,
p_self_assess_flag => NULL,
p_allow_offset_tax_flag => NULL,
p_rep_registration_number => l_supplier_reg_rec.tax_registration_number,
p_effective_from_use_le => NULL,
p_record_type_code => NULL,
p_request_id => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute_category => NULL,
p_party_id => NULL,
p_program_login_id => NULL,
p_party_type_code => NULL,
p_supplier_flag => NULL,
p_customer_flag => NULL,
p_site_flag => NULL,
p_process_for_applicability_fl => NULL,
p_rounding_level_code => NULL,
p_rounding_rule_code => NULL,
p_withholding_start_date => NULL,
p_inclusive_tax_flag => NULL,
p_allow_awt_flag => NULL,
p_use_le_as_subscriber_flag => NULL,
p_legal_establishment_flag => NULL,
p_first_party_le_flag => NULL,
p_reporting_authority_flag => NULL,
x_return_status => x_return_status,
p_registration_type_code => l_supplier_reg_rec.tax_reg_type,
p_country_code => l_supplier_reg_rec.tax_reg_country_code
);
x_msg_data := 'call to zx_party_tax_profile_pkg.update_row failed';
update ap_suppliers
set bus_class_last_certified_by = l_user_id,
bus_class_last_certified_date = (select creation_date
from pos_supplier_registrations
where supplier_reg_id = p_supplier_reg_id ),
last_updated_by = l_user_id,
last_update_date = sysdate
where vendor_id=l_vendor_id;
UPDATE pos_supplier_registrations
SET registration_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE supplier_reg_id = p_supplier_reg_id;
UPDATE pos_address_requests
SET request_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE mapping_id =
(SELECT mapping_id FROM pos_supplier_mappings
WHERE supplier_reg_id = p_supplier_reg_id)
AND request_status = 'PENDING';
UPDATE pos_contact_requests
SET request_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE mapping_id =
(SELECT mapping_id FROM pos_supplier_mappings
WHERE supplier_reg_id = p_supplier_reg_id)
AND request_status = 'PENDING';
UPDATE pos_cont_addr_requests
SET request_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE mapping_id =
(SELECT mapping_id FROM pos_supplier_mappings
WHERE supplier_reg_id = p_supplier_reg_id)
AND request_status = 'PENDING';
UPDATE pos_supplier_registrations
SET registration_status = 'PENDING_APPROVAL',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE supplier_reg_id = p_supplier_reg_id;
SELECT organization_id
FROM hr_operating_units
WHERE organization_id = p_ou_id
AND ( date_to IS NULL OR
( date_to > sysdate AND date_to > date_from ) );
FOR x IN (SELECT supplier_number
FROM pos_supplier_registrations
WHERE supplier_reg_id <> p_supp_regid
--AND registration_status <> 'REJECTED'
-- the unique key POS_SUPPLIER_REG_U2 is on supplier_number only
-- without considering the registration_status
AND p_supp_number = supplier_number
AND ROWNUM < 2
)
LOOP
RETURN 'N';
FOR x IN (SELECT segment1
FROM ap_suppliers
WHERE segment1 = p_supp_number
AND ROWNUM < 2
)
LOOP
RETURN 'N';
SELECT -1
FROM pos_supplier_registrations psr
WHERE psr.supplier_reg_id <> p_supp_regid
AND psr.taxpayer_id = p_taxpayer_id
AND ((psr.tax_reg_country_code is not null and p_country is not null and psr.tax_reg_country_code = p_country) OR
(p_country is null))
AND psr.registration_status = 'PENDING_APPROVAL';
SELECT pv.vendor_id
FROM ap_suppliers pv, zx_party_tax_profile zxpr
WHERE pv.num_1099 = p_taxpayer_id
AND zxpr.party_id = pv.party_id
AND ((zxpr.country_code is not null and p_country is not null and zxpr.country_code = p_country) OR
(p_country is null));
SELECT -1
FROM pos_supplier_registrations psr
WHERE psr.supplier_reg_id <> p_supp_regid
AND psr.duns_number = p_duns_num
AND psr.registration_status = 'PENDING_APPROVAL';
SELECT party_id
FROM hz_parties
WHERE duns_number_c = p_duns_num
AND party_type = 'ORGANIZATION';
SELECT vendor_id
FROM ap_suppliers
WHERE party_id = p_party_id;
SELECT -1
FROM pos_supplier_registrations psr
WHERE psr.supplier_reg_id <> p_supp_regid
AND psr.tax_registration_number = p_taxreg_num
AND ((psr.tax_reg_country_code is not null and p_country is not null and psr.tax_reg_country_code = p_country) OR
(p_country is null))
AND psr.registration_status = 'PENDING_APPROVAL';
SELECT pv.vendor_id
FROM ap_suppliers pv, zx_party_tax_profile zxpr
WHERE zxpr.party_id = pv.party_id
AND zxpr.rep_registration_number = p_taxreg_num
AND ((zxpr.country_code is not null and p_country is not null and zxpr.country_code = p_country) OR
(p_country is null));
SELECT pvsa.vendor_id
FROM ap_supplier_sites_all pvsa, zx_party_tax_profile zxpr
WHERE zxpr.rep_registration_number = p_taxreg_num
AND zxpr.site_flag = 'Y'
AND zxpr.party_id = pvsa.party_site_id
AND ((zxpr.country_code is not null and p_country is not null and zxpr.country_code = p_country) OR
(p_country is null));