[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pos_ap_supplier_bo(vendor_id,
last_update_date,
last_updated_by,
vendor_name,
vendor_name_alt,
segment1,
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
last_update_login,
creation_date,
created_by,
employee_id,
vendor_type_lookup_code,
customer_num,
one_time_flag,
parent_vendor_id,
min_order_amount,
ship_to_location_id,
bill_to_location_id,
ship_via_lookup_code,
freight_terms_lookup_code,
fob_lookup_code,
terms_id,
set_of_books_id,
credit_status_lookup_code,
credit_limit,
always_take_disc_flag,
pay_date_basis_lookup_code,
pay_group_lookup_code,
payment_priority,
invoice_currency_code,
payment_currency_code,
invoice_amount_limit,
exchange_date_lookup_code,
hold_all_payments_flag,
hold_future_payments_flag,
hold_reason,
distribution_set_id,
accts_pay_code_combination_id,
disc_lost_code_combination_id,
disc_taken_code_combination_id,
expense_code_combination_id,
prepay_code_combination_id,
num_1099,
type_1099,
withholding_status_lookup_code,
withholding_start_date,
organization_type_lookup_code,
vat_code,
start_date_active,
end_date_active,
minority_group_lookup_code,
payment_method_lookup_code,
bank_account_name,
bank_account_num,
bank_num,
bank_account_type,
women_owned_flag,
small_business_flag,
-- Bug 9509201, 9535269
-- Added the decode funtion to avoid the ASCII 0 character present in the Standard industry class column for some suppliers,
-- as it is creating problems in the XML payload creation for SDH Generate Report and Publish Supplier functionality.
-- Bug 8246403 addressed this exact issue. The error is considered to be a correct behavior, but a DB level option can be set by alter session to encode invalid characters.
decode(standard_industry_class, chr(0), NULL, standard_industry_class),
hold_flag,
purchasing_hold_reason,
hold_by,
hold_date,
terms_date_basis,
price_tolerance,
inspection_required_flag,
receipt_required_flag,
qty_rcv_tolerance,
qty_rcv_exception_code,
enforce_ship_to_location_code,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code,
receiving_routing_id,
allow_substitute_receipts_flag,
allow_unordered_receipts_flag,
hold_unmatched_invoices_flag,
exclusive_payment_flag,
ap_tax_rounding_rule,
auto_tax_calc_flag,
auto_tax_calc_override,
amount_includes_tax_flag,
tax_verification_date,
name_control,
state_reportable_flag,
federal_reportable_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
offset_vat_code,
vat_registration_num,
auto_calculate_interest_flag,
validation_number,
exclude_freight_from_discount,
tax_reporting_name,
check_digits,
bank_number,
allow_awt_flag,
awt_group_id,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
edi_transaction_handling,
edi_payment_method,
edi_payment_format,
edi_remittance_method,
edi_remittance_instruction,
bank_charge_bearer,
bank_branch_type,
match_option,
future_dated_payment_ccid,
create_debit_memo_flag,
offset_tax_flag,
party_id,
parent_party_id,
-- nvl(ni_number, ' '),
tca_sync_num_1099,
tca_sync_vendor_name,
tca_sync_vat_reg_num,
individual_1099,
unique_tax_reference_num,
partnership_utr,
partnership_name,
cis_enabled_flag,
first_name,
second_name,
last_name,
salutation,
trading_name,
work_reference,
company_registration_number,
national_insurance_number,
verification_number,
verification_request_id,
match_status_flag,
cis_verification_date,
pay_awt_group_id,
cis_parent_vendor_id,
NULL,
pos_supplier_uda_bo_pkg.get_uda_for_supplier_site(l_party_id,
NULL,
NULL,
'SUPP_LEVEL'))
INTO l_pos_ap_supplier_bo
FROM ap_suppliers
WHERE party_id = l_party_id;
SELECT 'Y'
FROM hz_orig_sys_references
WHERE owner_table_id = p_party_id
AND owner_table_name = 'HZ_PARTIES'
AND orig_system = p_orig_system
AND orig_system_reference = p_orig_system_reference
AND trunc(nvl(end_date_active, SYSDATE)) >= trunc(SYSDATE)
AND status = 'A';
* @param p_create_update_flag The Create Update flag
* @param x_vendor_id The Supplier Created
* @param x_party_id The Party Created
* @param x_return_status The return status
* @param x_msg_count The message count
* @param x_msg_data The message data
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Create Supplier
* @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
*/
PROCEDURE create_pos_ap_supplier
(
p_api_version IN NUMBER DEFAULT NULL,
p_init_msg_list IN VARCHAR2 DEFAULT NULL,
p_pos_ap_supplier_bo IN pos_ap_supplier_bo,
p_party_id IN NUMBER,
p_orig_system IN VARCHAR2,
p_orig_system_reference IN VARCHAR2,
p_create_update_flag IN VARCHAR2,
x_vendor_id OUT NOCOPY NUMBER,
x_party_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_ap_suplier_bo ap_vendor_pub_pkg.r_vendor_rec_type;
IF p_create_update_flag = 'U' THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data := 'Party to be updated doesnot exist.';
IF p_create_update_flag = 'C' THEN
x_msg_count := 0;
ELSIF p_create_update_flag = 'U' THEN
IF p_pos_ap_supplier_bo.p_pos_external_payee_bo.payee_party_id IS NULL THEN
l_ext_payee_rec.payee_party_id := l_party_id;
SELECT vendor_id
INTO l_vendor_id
FROM ap_suppliers
WHERE party_id = l_party_id;
ap_vendor_pub_pkg.update_vendor(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => l_ap_suplier_bo,
p_vendor_id => l_vendor_id);
SELECT nvl(owner_table_id, -9999)
INTO l_party_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTIES'
AND hr.orig_system = p_orig_system
AND hr.orig_system_reference = p_orig_system_reference
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE;
SELECT nvl(party_id, -9999)
INTO l_party_id_supp
FROM ap_suppliers
WHERE party_id = l_party_id;
/*PROCEDURE update_pos_ap_supplier(p_api_version IN NUMBER DEFAULT NULL,
p_init_msg_list IN VARCHAR2 DEFAULT NULL,
p_pos_ap_supplier_bo IN pos_ap_supplier_bo,
p_pos_external_payee_bo IN pos_external_payee_bo,
p_orig_system IN VARCHAR2,
p_orig_system_reference IN VARCHAR2,
p_vendor_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_exists_row NUMBER;
SELECT vendor_id
INTO l_vendor_id
FROM ap_suppliers
WHERE party_id = l_party_id;
ap_vendor_pub_pkg.update_vendor(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => l_ap_suplier_bo,
p_vendor_id => l_vendor_id);
END update_pos_ap_supplier;