[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
debug_info := 'Select from ap_lookup_codes, po_lookup_codes, hr_locations, ....';
SELECT count(*)
INTO l_ap_options
FROM ap_system_parameters
WHERE nvl(org_id,-99) = nvl(x_org_id,-99);
SELECT count(*)
INTO l_fin_options
FROM financials_system_parameters
WHERE nvl(org_id,-99) = nvl(x_org_id,-99);
SELECT aps.supplier_numbering_method,
aps.supplier_num_type,
fin.rfq_only_site_flag,
fin.ship_to_location_id, -- ship_to_location_id
hl2.location_code, -- ship_to_location_code
nvl(hl2.inactive_date,sysdate+1), -- ship to inactive date
fin.bill_to_location_id, -- bill_to_location_id
hl1.location_code, -- bill_to_location_code
nvl(hl1.inactive_date,sysdate+1), -- bill_to_location inatcive date
fin.fob_lookup_code, -- fob_lookup_code
pc1.displayed_field, -- fob_lookup_disp
nvl(pc1.inactive_date,sysdate+1), -- fob inactive_date
fin.freight_terms_lookup_code, -- freight_terms_lookup_code
pc2.displayed_field, -- freight_terms_lookup_disp
nvl(pc2.inactive_date,sysdate+1), -- freight_terms inactive_date
aps.terms_id, -- terms_id
tm.name, -- terms_name
nvl(tm.end_date_active,sysdate+1), -- terms_inactive_date
aps.payment_method_lookup_code, -- payment_method_lookup_code
lc1.displayed_field, -- payment_method_disp
nvl(lc1.inactive_date,sysdate+1), -- payment_method inactve date
aps.always_take_disc_flag,
aps.pay_date_basis_lookup_code, -- pay_date_basis_lookup_code
lc2.displayed_field, -- pay_date_basis_disp
-- Invoice Currency
-- In R12, with the MOAC project the invoice currency was moved
-- product setup level. But payment currency was not thought
-- about properly. So modified the code such that
-- the defaulting to supplier will be the ledger currency
-- otherwise it will be derived from the invoice currency in the
-- product setup.
nvl(ap.base_currency_code,aps.invoice_currency_code),
fin.org_id,
fin.set_of_books_id,
gl.short_name,
-- Invoice Currency
-- In R12, with the MOAC project the invoice currency was moved
-- product setup level. But payment currency was not thought
-- about properly. So modified the code such that
-- the defaulting to supplier will be the ledger currency
-- otherwise it will be derived from the invoice currency in the
-- product setup.
nvl(ap.base_currency_code,aps.invoice_currency_code),
fin.accts_pay_code_combination_id,
fin.future_dated_payment_ccid,
fin.prepay_code_combination_id,
aps.supplier_pay_group_lookup_code,
pc3.lookup_code, --2122951 changed to lookup_code
aps.auto_calculate_interest_flag,
-- Bug 1492237 Get terms_date_basis from ap insead of fin
aps.terms_date_basis, -- terms_date_basis
lc3.displayed_field, -- terms_date_basis_disp
gl.chart_of_accounts_id,
aps.hold_unmatched_invoices_flag,
ap.hold_unmatched_invoices_flag,
fin.match_option,
fin.exclusive_payment_flag,
ap.vendor_auto_int_default,
fin.inventory_organization_id,
fin.ship_via_lookup_code, -- ship_via_lookup_code
ofr.description, -- ship_via_disp
nvl(ofr.disable_date,sysdate+1), -- ship_via inactive date
sysdate,
ap.base_currency_code,
fin.vat_country_code,
ap.default_awt_group_id,
awt.name,
nvl(ap.allow_awt_flag, 'N'),
ap.use_bank_charge_flag, --5007989
nvl(ap.bank_charge_bearer, 'I') --5007989
INTO x_user_defined_vendor_num_code,
x_manual_vendor_num_type,
x_rfq_only_site_flag,
x_ship_to_location_id,
x_ship_to_location_code,
l_ship_to_loc_inactive_date,
x_bill_to_location_id,
x_bill_to_location_code,
l_bill_to_loc_inactive_date,
x_fob_lookup_code,
x_fob_lookup_disp,
l_fob_inactive_date,
x_freight_terms_lookup_code,
x_freight_terms_lookup_disp,
l_freight_terms_inactive_date,
x_terms_id,
x_terms_disp,
l_terms_inactive_date,
x_payment_method_lookup_code,
x_payment_method_disp,
l_payment_method_inactive_date,
x_always_take_disc_flag,
x_pay_date_basis_lookup_code,
x_pay_date_basis_disp,
x_invoice_currency_code,
x_org_id,
x_set_of_books_id,
x_short_name,
x_payment_currency_code,
x_accts_pay_ccid,
x_future_dated_payment_ccid,
x_prepay_code_combination_id,
x_vendor_pay_group_lookup_code,
x_vendor_pay_group_disp,
x_sys_auto_calc_int_flag,
x_terms_date_basis,
x_terms_date_basis_disp,
x_chart_of_accounts_id,
x_fin_require_matching,
x_sys_require_matching,
x_fin_match_option,
x_exclusive_payment,
x_vendor_auto_int_default,
x_inventory_organization_id,
x_ship_via_lookup_code,
x_ship_via_disp,
l_ship_via_inactive_date,
x_sysdate,
x_base_currency_code,
x_home_country_code,
x_default_awt_group_id,
x_default_awt_group_name,
x_allow_awt_flag,
x_use_bank_charge_flag,
x_bank_charge_bearer
FROM ap_lookup_codes lc1,
ap_lookup_codes lc2,
ap_lookup_codes lc3,
po_lookup_codes pc1,
po_lookup_codes pc2,
po_lookup_codes pc3,
hr_locations_all hl1,
hr_locations_all hl2,
ap_terms_tl tm,
org_freight_tl ofr,
gl_ledgers gl,
financials_system_params_all fin,
ap_system_parameters_all ap,
ap_awt_groups awt,
ap_product_setup aps
WHERE gl.ledger_id = fin.set_of_books_id
AND lc1.lookup_type(+) = 'PAYMENT METHOD'
AND lc1.lookup_code(+) = aps.payment_method_lookup_code
AND lc2.lookup_type(+) = 'PAY DATE BASIS'
AND lc2.lookup_code(+) = aps.pay_date_basis_lookup_code
AND lc3.lookup_type(+) = 'TERMS DATE BASIS'
AND lc3.lookup_code(+) = aps.terms_date_basis
AND pc1.lookup_type(+) = 'FOB'
AND pc1.lookup_code(+) = fin.fob_lookup_code
AND pc2.lookup_type(+) = 'FREIGHT TERMS'
AND pc2.lookup_code(+) = fin.freight_terms_lookup_code
AND pc3.lookup_type(+) = 'PAY GROUP'
AND pc3.lookup_code(+) = aps.supplier_pay_group_lookup_code
AND hl1.location_id(+) = fin.bill_to_location_id
AND hl1.bill_to_site_flag(+) = 'Y'
AND hl2.location_id(+) = fin.ship_to_location_id
AND hl2.ship_to_site_flag(+) = 'Y'
AND ofr.freight_code(+) = fin.ship_via_lookup_code
AND ofr.organization_id(+) = fin.inventory_organization_id
AND ofr.language(+) = userenv('LANG')
AND awt.group_id(+) = ap.default_awt_group_id
AND aps.terms_id = tm.term_id(+)
AND tm.language(+) = userenv('LANG')
AND fin.org_id = x_org_id
AND fin.set_of_books_id = ap.set_of_books_id
AND fin.org_id = ap.org_id;
SELECT count(*)
INTO l_po_setup
FROM po_system_parameters
WHERE nvl(org_id,-99) = nvl(x_org_id,-99);
debug_info := 'Select from rcv_parameters, po_lookup_codes, po_system_parameters';
SELECT rp.enforce_ship_to_location_code,
rp.receiving_routing_id,
rp.qty_rcv_tolerance,
rp.qty_rcv_exception_code,
rp.days_early_receipt_allowed,
rp.days_late_receipt_allowed,
rp.allow_substitute_receipts_flag,
rp.allow_unordered_receipts_flag,
rp.receipt_days_exception_code,
pc1.displayed_field, -- enforce_ship_to_loc_disp
pc2.displayed_field, -- qty_rcv_exception_disp
pc3.displayed_field, -- receipt_days_exception_disp
po.receiving_flag,
po.inspection_required_flag,
po.create_debit_memo_flag
INTO x_enforce_ship_to_loc_code,
x_receiving_routing_id,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code ,
x_days_early_receipt_allowed,
x_days_late_receipt_allowed,
x_allow_sub_receipts_flag,
x_allow_unord_receipts_flag,
x_receipt_days_exception_code,
x_enforce_ship_to_loc_disp,
x_qty_rcv_exception_disp,
x_receipt_days_exception_disp,
x_receipt_required_flag,
x_inspection_required_flag,
x_po_create_dm_flag
FROM rcv_parameters rp,
po_lookup_codes pc1,
po_lookup_codes pc2,
po_lookup_codes pc3,
po_system_parameters po
WHERE rp.organization_id = x_inventory_organization_id
AND pc1.lookup_type(+) = 'RECEIVING CONTROL LEVEL'
AND pc1.lookup_code(+) = rp.enforce_ship_to_location_code
AND pc2.lookup_type(+) = 'RECEIVING CONTROL LEVEL'
AND pc2.lookup_code(+) = rp.qty_rcv_exception_code
AND pc3.lookup_type(+) = 'RECEIVING CONTROL LEVEL'
AND pc3.lookup_code(+) = rp.receipt_days_exception_code
--MO Access Control
AND nvl(po.org_id,-99) = nvl(x_org_id,-99);
debug_info := 'Select routing_name';
SELECT rh.routing_name
INTO x_receiving_routing_name
FROM rcv_Routing_Headers rh
WHERE rh.routing_header_id = x_receiving_routing_id;
select territory_short_name,
address_style
into x_default_country_disp,
x_address_style
from fnd_territories_vl
where territory_code = x_default_country_code
OR iso_territory_code = x_default_country_code; --Bug 5260178
debug_info := 'Select pay_date_basis displayed field';
SELECT lc.lookup_code,
lc.displayed_field
INTO x_pay_date_basis_lookup_code,
x_pay_date_basis_disp
FROM ap_lookup_codes lc
WHERE lc.lookup_type = 'PAY DATE BASIS'
AND lc.lookup_code = 'DISCOUNT';
debug_info := 'Select payment_method display field';
SELECT lc.lookup_code,
lc.displayed_field
INTO x_payment_method_lookup_code,
x_payment_method_disp
FROM ap_lookup_codes lc
WHERE lc.lookup_type = 'PAYMENT METHOD'
AND lc.lookup_code = 'CHECK';
:= 'Select from ap_lookup_codes, po_lookup_codes, hr_locations, ....';
SELECT aps.supplier_numbering_method,
aps.supplier_num_type,
aps.terms_id, -- terms_id
tm.name, -- terms_name
nvl(tm.end_date_active,sysdate+1), -- terms_inactive_date
aps.always_take_disc_flag,
aps.pay_date_basis_lookup_code, -- pay_date_basis_lookup_code
lc2.displayed_field, -- pay_date_basis_disp
aps.invoice_currency_code,
aps.supplier_pay_group_lookup_code,
pc3.lookup_code,
aps.auto_calculate_interest_flag,
aps.terms_date_basis, -- terms_date_basis
lc3.displayed_field, -- terms_date_basis_disp
aps.hold_unmatched_invoices_flag,
sysdate,
--5007989 ap.use_bank_charge_flag,
--5007989 nvl(ap.bank_charge_bearer, 'I')
aps.match_option --bug6075649
INTO x_user_defined_vendor_num_code,
x_manual_vendor_num_type,
x_terms_id,
x_terms_disp,
l_terms_inactive_date,
x_always_take_disc_flag,
x_pay_date_basis_lookup_code,
x_pay_date_basis_disp,
x_invoice_currency_code,
x_vendor_pay_group_lookup_code,
x_vendor_pay_group_disp,
x_sys_auto_calc_int_flag,
x_terms_date_basis,
x_terms_date_basis_disp,
x_fin_require_matching,
x_sysdate,
-- x_use_bank_charge_flag,
-- x_bank_charge_bearer
x_fin_match_option --bug6075649
FROM ap_lookup_codes lc2,
ap_lookup_codes lc3,
po_lookup_codes pc3,
ap_terms tm,
ap_product_setup aps
WHERE lc2.lookup_type(+) = 'PAY DATE BASIS'
AND lc2.lookup_code(+) = aps.pay_date_basis_lookup_code
AND lc3.lookup_type(+) = 'TERMS DATE BASIS'
AND lc3.lookup_code(+) = aps.terms_date_basis
AND pc3.lookup_type(+) = 'PAY GROUP'
AND pc3.lookup_code(+) = aps.supplier_pay_group_lookup_code
AND aps.terms_id = tm.term_id(+);
debug_info := 'Select pay_date_basis displayed field';
SELECT lc.lookup_code,
lc.displayed_field
INTO x_pay_date_basis_lookup_code,
x_pay_date_basis_disp
FROM ap_lookup_codes lc
WHERE lc.lookup_type = 'PAY DATE BASIS'
AND lc.lookup_code = 'DISCOUNT';