[Home] [Help]
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;
Following update will transfer the attachments
entered by supplier during registration time
to supplier entity upon approval
*/
l_step := 'assign registered supplier attachments to approved supplier';
UPDATE fnd_attached_documents
SET entity_name = 'PO_VENDORS',
pk1_value = x_vendor_id,
last_updated_by = fnd_global.user_id,
last_update_date = Sysdate,
last_update_login = fnd_global.login_id
WHERE entity_name = 'POS_SUPP_REG' and
pk1_value = p_supplier_reg_rec.supplier_reg_id;
UPDATE hz_party_usg_assignments
SET effective_end_date=sysdate,
status_flag = 'I',
last_updated_by = fnd_global.user_id,
last_update_date = Sysdate,
last_update_login = fnd_global.login_id
WHERE party_id= x_party_id
and party_usage_code='SUPPLIER_PROSPECT';
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'
OR pcr.request_type = 'ADD_PARTY_CONTACT')
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 Count(pagr.ACCOUNT_REQUEST_ID)
INTO l_count
FROM POS_ACNT_GEN_REQ pagr,pos_supplier_mappings psm
WHERE pagr.mapping_id = psm.mapping_id
AND psm.vendor_id = p_vendor_id;
PROCEDURE update_supplier_reg_uda
(p_supplier_reg_id IN NUMBER,
p_party_id IN NUMBER,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2
)
IS
CURSOR l_cur IS
SELECT par.address_request_id, par.party_site_id
FROM pos_address_requests par,
pos_supplier_mappings psm
WHERE par.request_type = 'ADD'
AND par.mapping_id = psm.mapping_id
AND par.party_site_id IS NOT NULL
AND psm.supplier_reg_id = p_supplier_reg_id;
SELECT data_level_id
INTO l_supp_level_id
FROM ego_data_level_b
WHERE application_id = 177
AND attr_group_type = 'POS_SUPP_PROFMGMT_GROUP'
AND data_level_name = 'SUPP_LEVEL';
SELECT data_level_id
INTO l_supp_addr_level_id
FROM ego_data_level_b
WHERE application_id = 177
AND attr_group_type = 'POS_SUPP_PROFMGMT_GROUP'
AND data_level_name = 'SUPP_ADDR_LEVEL';
UPDATE pos_supp_prof_ext_b
SET is_prospect = 'N',
party_id = p_party_id
WHERE is_prospect = 'Y'
AND party_id = p_supplier_reg_id
AND data_level_id = l_supp_level_id;
UPDATE pos_supp_prof_ext_tl
SET is_prospect = 'N',
party_id = p_party_id
WHERE is_prospect = 'Y'
AND party_id = p_supplier_reg_id
AND data_level_id = l_supp_level_id;
UPDATE pos_supp_prof_ext_b
SET is_prospect = 'N',
party_id = p_party_id,
pk1_value = l_rec.party_site_id
WHERE is_prospect = 'Y'
AND party_id = p_supplier_reg_id
AND pk1_value = l_rec.address_request_id
AND data_level_id = l_supp_addr_level_id;
UPDATE pos_supp_prof_ext_tl
SET is_prospect = 'N',
party_id = p_party_id,
pk1_value = l_rec.party_site_id
WHERE is_prospect = 'Y'
AND party_id = p_supplier_reg_id
AND pk1_value = l_rec.address_request_id
AND data_level_id = l_supp_addr_level_id;
END update_supplier_reg_uda;
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_supplier_reg_uda
(p_supplier_reg_id => p_supplier_reg_id,
p_party_id => l_vendor_party_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
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';
select count(*)
into l_party_usages
from hz_party_usg_assignments
where party_id = (select vendor_party_id
from pos_supplier_registrations
where supplier_reg_id = p_supplier_reg_id);
update hz_parties
set status = 'I',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where status = 'A' and
created_by_module = 'POS_SUPPLIER_MGMT' and
party_id = (select vendor_party_id
from pos_supplier_registrations
where supplier_reg_id = p_supplier_reg_id);
UPDATE hz_party_usg_assignments
SET effective_end_date=sysdate,
status_flag = 'I',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE party_id= (select vendor_party_id
from pos_supplier_registrations
where supplier_reg_id = p_supplier_reg_id)
and party_usage_code='SUPPLIER_PROSPECT';
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.registration_status = 'PENDING_APPROVAL';
SELECT pv.vendor_id
FROM ap_suppliers pv
WHERE pv.num_1099 = p_taxpayer_id;
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.vendor_party_id = p_party_id
AND psr.registration_status = 'PENDING_APPROVAL';
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));
SELECT mapping_id
FROM pos_supplier_mappings
WHERE supplier_reg_id = p_supp_reg_id;
SELECT vendor_party_id
FROM pos_supplier_registrations
WHERE supplier_reg_id = p_supp_reg_id;
SELECT supplier_type
FROM pos_supplier_registrations
WHERE supplier_reg_id = p_supp_reg_id;
SELECT 'BC:' || lookup_code AS code
FROM pos_bus_class_reqs
WHERE mapping_id = p_mapping_id;
SELECT *
FROM pos_product_service_requests
WHERE mapping_id = p_mapping_id;
SELECT 'HZ:' || REPLACE(hccr.class_category, ' ', '$')
|| ':'
|| hccr.class_code AS code
FROM hz_class_code_relations hccr,
(SELECT class_category, class_code, owner_table_id
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND start_date_active <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND status = 'A'
) v
WHERE hccr.class_category = v.class_category
START WITH hccr.class_code = v.class_code
CONNECT BY PRIOR hccr.class_code = hccr.sub_class_code
UNION
SELECT 'HZ:' || REPLACE(fnd.lookup_type, ' ', '$')
|| ':'
|| fnd.lookup_code AS code
FROM fnd_lookup_values_vl fnd,
(SELECT class_category, class_code, owner_table_id
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND start_date_active <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND status = 'A'
) v
WHERE fnd.lookup_type = v.class_category
AND fnd.lookup_code = v.class_code;
SELECT pac.page_id
FROM pos_attrpg_config pac,
pos_supplier_registrations psr
WHERE psr.supplier_reg_id = p_supp_reg_id
AND pac.org_id IN (-999, psr.ou_id)
AND DECODE(p_buyer_user, 'Y', pac.internal_update_flag,
'N', pac.supplier_update_flag) = 'Y'
AND pac.page_id NOT IN
(SELECT pac2.page_id
FROM pos_attrpg_config pac2
WHERE pac2.org_id = psr.ou_id
AND DECODE(p_buyer_user, 'Y', pac2.internal_update_flag,
'N', pac2.supplier_update_flag) = 'N'
);
'SELECT ptl.display_name, ' ||
' ag.attr_group_id, ' ||
' ag.attr_group_disp_name, ' ||
' attr.attr_name, ' ||
' attr.attr_display_name ' ||
'FROM ego_pages_b pb, ' ||
' ego_pages_tl ptl, ' ||
' ego_page_entries_b pe, ' ||
' ego_obj_ag_assocs_b a, ' ||
' ego_attr_groups_v ag, ' ||
' ego_attrs_v attr, ' ||
' ego_attr_group_dl agdl ' ||
'WHERE ptl.page_id = pb.page_id ' ||
' AND ptl.language = USERENV(''LANG'') ' ||
' AND pe.page_id = pb.page_id ' ||
' AND a.association_id = pe.association_id ' ||
' AND a.enabled_flag = ''Y'' ' ||
' AND ag.attr_group_id = a.attr_group_id ' ||
' AND attr.application_id = ag.application_id ' ||
' AND attr.attr_group_type = ag.attr_group_type ' ||
' AND attr.attr_group_name = ag.attr_group_name ' ||
' AND attr.enabled_flag = ''Y'' ' ||
' AND attr.required_flag = ''Y'' ' ||
' AND agdl.attr_group_id = ag.attr_group_id ' ||
' AND (agdl.edit_privilege_id IS NULL OR ' ||
' agdl.edit_privilege_id IN ( ' ||
' SELECT function_id ' ||
' FROM fnd_form_functions ' ||
' WHERE function_name IN (' || l_privileges || ')) ' ||
' ) ' ||
' AND pb.object_id = ' || l_object_id ||
' AND pb.data_level = ''SUPP_LEVEL'' ' ||
' AND pb.page_id IN (' || l_pages_list || ') ' ||
' AND pb.classification_code IN (' || l_class_codes_list || ') ' ||
'ORDER BY pb.sequence, pe.sequence, attr.sequence';