[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT temp_ext_bank_acct_id
FROM iby_temp_ext_bank_accts
WHERE calling_app_unique_ref2 = p_unique_ref
AND nvl(status, 'NEW') <> 'PROCESSED';
SELECT org_id
INTO ext_payee_rec.payer_org_id
FROM po_vendor_sites_all
WHERE vendor_site_id = l_vendor_site_id;
SELECT party_id,
'PAYABLES_DISB'
INTO ext_payee_rec.payee_party_id,
ext_payee_rec.payment_function
FROM po_vendors
WHERE vendor_id = p_site_rec.vendor_id;
UPDATE iby_temp_ext_bank_accts
SET ext_payee_id = l_ext_payee_id,
account_owner_party_id = ext_payee_rec.payee_party_id --bug 6753331
WHERE calling_app_unique_ref2 =
p_site_rec.vendor_site_interface_id;
UPDATE iby_temp_ext_bank_accts
SET status = 'PROCESSED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
SELECT pos_supplier_sites_all_bo(aps.vendor_site_id,
aps.last_update_date,
aps.last_updated_by,
aps.vendor_id,
aps.vendor_site_code,
aps.vendor_site_code_alt,
aps.last_update_login,
aps.creation_date,
aps.created_by,
aps.purchasing_site_flag,
aps.rfq_only_site_flag,
aps.pay_site_flag,
aps.attention_ar_flag,
aps.address_line1,
aps.address_lines_alt,
aps.address_line2,
aps.address_line3,
aps.city,
aps.state,
aps.zip,
aps.province,
aps.country,
aps.area_code,
aps.phone,
aps.customer_num,
aps.ship_to_location_id,
aps.bill_to_location_id,
aps.ship_via_lookup_code,
aps.freight_terms_lookup_code,
aps.fob_lookup_code,
aps.inactive_date,
aps.fax,
aps.fax_area_code,
aps.telex,
aps.payment_method_lookup_code,
aps.bank_account_name,
aps.bank_account_num,
aps.bank_num,
aps.bank_account_type,
aps.terms_date_basis,
aps.current_catalog_num,
aps.vat_code,
aps.distribution_set_id,
aps.accts_pay_code_combination_id,
aps.prepay_code_combination_id,
aps.pay_group_lookup_code,
aps.payment_priority,
aps.terms_id,
aps.invoice_amount_limit,
aps.pay_date_basis_lookup_code,
aps.always_take_disc_flag,
aps.invoice_currency_code,
aps.payment_currency_code,
aps.hold_all_payments_flag,
aps.hold_future_payments_flag,
aps.hold_reason,
aps.hold_unmatched_invoices_flag,
aps.ap_tax_rounding_rule,
aps.auto_tax_calc_flag,
aps.auto_tax_calc_override,
aps.amount_includes_tax_flag,
aps.exclusive_payment_flag,
aps.tax_reporting_site_flag,
aps.attribute_category,
aps.attribute1,
aps.attribute2,
aps.attribute3,
aps.attribute4,
aps.attribute5,
aps.attribute6,
aps.attribute7,
aps.attribute8,
aps.attribute9,
aps.attribute10,
aps.attribute11,
aps.attribute12,
aps.attribute13,
aps.attribute14,
aps.attribute15,
aps.request_id,
aps.program_application_id,
aps.program_id,
aps.program_update_date,
aps.validation_number,
aps.exclude_freight_from_discount,
aps.vat_registration_num,
aps.offset_vat_code,
aps.org_id,
aps.check_digits,
aps.bank_number,
aps.address_line4,
aps.county,
aps.address_style,
aps.language,
aps.allow_awt_flag,
aps.awt_group_id,
aps.global_attribute1,
aps.global_attribute2,
aps.global_attribute3,
aps.global_attribute4,
aps.global_attribute5,
aps.global_attribute6,
aps.global_attribute7,
aps.global_attribute8,
aps.global_attribute9,
aps.global_attribute10,
aps.global_attribute11,
aps.global_attribute12,
aps.global_attribute13,
aps.global_attribute14,
aps.global_attribute15,
aps.global_attribute16,
aps.global_attribute17,
aps.global_attribute18,
aps.global_attribute19,
aps.global_attribute20,
aps.global_attribute_category,
aps.edi_transaction_handling,
aps.edi_id_number,
aps.edi_payment_method,
aps.edi_payment_format,
aps.edi_remittance_method,
aps.bank_charge_bearer,
aps.edi_remittance_instruction,
aps.bank_branch_type,
aps.pay_on_code,
aps.default_pay_site_id,
aps.pay_on_receipt_summary_code,
aps.tp_header_id,
aps.ece_tp_location_code,
aps.pcard_site_flag,
aps.match_option,
aps.country_of_origin_code,
aps.future_dated_payment_ccid,
aps.create_debit_memo_flag,
aps.offset_tax_flag,
aps.supplier_notif_method,
aps.email_address,
aps.remittance_email,
aps.primary_pay_site_flag,
aps.shipping_control,
aps.selling_company_identifier,
aps.gapless_inv_num_flag,
aps.duns_number,
aps.tolerance_id,
aps.location_id,
aps.party_site_id,
NULL,
NULL,
aps.services_tolerance_id,
aps.retainage_rate,
aps.tca_sync_state,
aps.tca_sync_province,
aps.tca_sync_county,
aps.tca_sync_city,
aps.tca_sync_zip,
aps.tca_sync_country,
aps.pay_awt_group_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
terms.name, -- Bug 12979867: Add term's name
NULL,
NULL,
/* NULL,*/
pos_supplier_uda_bo_pkg.get_uda_for_supplier_site(l_party_id,
--Bug 15992883: change the positions of the following two arguments
aps.party_site_id,
aps.vendor_site_id,
--end of Bug 15992883
'SUPP_ADDR_SITE_LEVEL')) BULK COLLECT
INTO x_ap_supplier_sites_bo
FROM ap_supplier_sites_all aps,
ap_suppliers ap,
ap_terms terms
WHERE ap.party_id = l_party_id
AND aps.vendor_id = ap.vendor_id
AND aps.terms_id = terms.term_id(+); -- Bug 12979867/16067074: OUTER Join ap_terms to get term's name
p_create_update_flag IN VARCHAR2,
x_vendor_site_id OUT NOCOPY NUMBER,
x_party_site_id OUT NOCOPY NUMBER,
x_location_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
v_row_exists NUMBER := 0;
SELECT 1
INTO v_row_exists
FROM ap_supplier_sites_all
WHERE vendor_id = p_vendor_site_rec.vendor_id
AND rownum < 2;
vendor_site_rec.last_update_date := p_pos_supp_sites_all_bo_tbl(i)
.last_update_date;
vendor_site_rec.last_updated_by := p_pos_supp_sites_all_bo_tbl(i)
.last_updated_by;
SELECT vendor_id
INTO vendor_site_rec.vendor_id
FROM ap_suppliers supp
WHERE supp.party_id = l_party_id;
SELECT owner_table_id
INTO vendor_site_rec.party_site_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTY_SITES'
AND hr.orig_system = p_pos_supp_sites_all_bo_tbl(i)
.party_site_orig_system
AND hr.orig_system_reference = p_pos_supp_sites_all_bo_tbl(i)
.party_site_orig_sys_ref
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE;
IF p_create_update_flag = 'C' THEN
create_vendor_site(vendor_site_rec,
vendor_site_rec.ext_payee_rec,
x_return_status,
x_msg_count,
x_msg_data);
ELSIF p_create_update_flag = 'U' THEN
ap_vendor_pub_pkg.update_vendor_site(p_api_version,
p_init_msg_list,
fnd_api.g_false,
fnd_api.g_valid_level_full,
x_return_status,
x_msg_count,
x_msg_data,
vendor_site_rec,
vendor_site_rec.vendor_site_id,
'NOT ISETUP');
x_msg_data := 'Create update flag is neither C nor U, exiting';