[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bank_number
FROM ce_bank_branches_v
WHERE bank_name = X_bank_name;
select bank_number, branch_number, bank_branch_name
into P_bank_number, P_branch_number, P_branch_name
from ce_bank_branches_v
where branch_party_id = p_bank_branch_id;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank_name = P_transferring_bank_name
and transferring_branch = P_transferring_branch
and receiving_bank_name = P_receiving_bank_name
and receiving_branch = P_receiving_branch
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank_name = P_transferring_bank_name
and transferring_branch = P_transferring_branch
and receiving_bank = P_receiving_bank
and receiving_branch = P_receiving_branch
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank = P_transferring_bank
and transferring_branch = P_transferring_branch
and receiving_bank_name = P_receiving_bank_name
and receiving_branch = P_receiving_branch
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank = P_transferring_bank
and transferring_branch = P_transferring_branch
and receiving_bank = P_receiving_bank
and receiving_branch = P_receiving_branch
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank_name = P_transferring_bank_name
and transferring_branch = P_transferring_branch
and receiving_bank_branch_id = P_receiving_bank_branch_id
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code
/* bug2191861 add check bank_name */
and receiving_bank_name = P_receiving_bank_name ;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank = P_transferring_bank
and transferring_branch = P_transferring_branch
and receiving_bank_branch_id = P_receiving_bank_branch_id
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code
/* bug2191861 add check bank_name */
and receiving_bank_name = P_receiving_bank_name ;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank_branch_id = P_transferring_bank_branch_id
and receiving_bank_name = P_receiving_bank_name
and receiving_branch = P_receiving_branch
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code
/* bug2191861 add check bank_name */
and transferring_bank_name = P_transferring_bank_name;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank_branch_id = P_transferring_bank_branch_id
and receiving_bank = P_receiving_bank
and receiving_branch = P_receiving_branch
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code
/* bug2191861 add check bank_name */
and transferring_bank_name = P_transferring_bank_name;
select count(*) into unique_check
from ap_bank_charges
where transferring_bank_branch_id = P_transferring_bank_branch_id
and receiving_bank_branch_id = P_receiving_bank_branch_id
and transfer_priority = P_transfer_priority
and currency_code = P_currency_code
/* bug2191861 add check bank_name */
and receiving_bank_name = P_receiving_bank_name
and transferring_bank_name = P_transferring_bank_name
;
select trans_amount_from, nvl(trans_amount_to, 99999999999999),
start_date,
nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY'))
from ap_bank_charge_lines
where bank_charge_id = P_bank_charge_id;
select count(*) INTO overlap
from ap_bank_charge_lines
where bank_charge_id = X_bank_charge_id
and ((trans_amount_from <= v_trans_amount_from
and nvl(trans_amount_to, 99999999999999)
> v_trans_amount_from)
or (trans_amount_from < v_trans_amount_to
and nvl(trans_amount_to, 99999999999999)
>= v_trans_amount_to))
and ((start_date <= v_start_date
and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
v_start_date)
or (start_date < v_end_date
and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >=
v_end_date));
select trans_amount_from, start_date,
nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY'))
from ap_bank_charge_lines
where bank_charge_id = P_bank_charge_id;
select count(*) INTO gap
from ap_bank_charge_lines
where bank_charge_id = X_bank_charge_id
and trans_amount_to = v_trans_amount_from
and ((start_date <= v_start_date
and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
v_start_date)
or (start_date < v_end_date
and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >=
v_end_date));
select start_date,
nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY'))
from ap_bank_charge_lines
where bank_charge_id = P_bank_charge_id
and trans_amount_to is null;
select trans_amount_from into v_trans_amount_from
from ap_bank_charge_lines
where bank_charge_id = X_bank_charge_id
and trans_amount_to is null
and ((start_date <= v_start_date
and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
v_start_date)
or (start_date < v_end_date
and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >=
v_end_date));
select max(trans_amount_to)
into v_trans_amount_to
from ap_bank_charge_lines
where bank_charge_id = X_bank_charge_id
and ((start_date <= v_start_date
and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
v_start_date)
or (start_date < v_end_date
and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >=
v_end_date));
SELECT bc.bank_charge_id,
bc.transferring_bank_branch_id,
bc.transferring_bank_name,
bc.transferring_bank,
bc.transferring_branch,
bc.receiving_bank_branch_id,
bc.receiving_bank_name,
bc.receiving_bank,
bc.receiving_branch,
bc.transfer_priority,
bc.currency_code
FROM ap_bank_charges bc, ap_bank_charge_lines bcl
WHERE ((bc.transferring_bank_branch_id = X_transferring_bank_branch_id
-- bug2242764 added bank_name condition
AND bc.transferring_bank_name = X_transferring_bank_name)
OR (bc.transferring_bank_name = X_transferring_bank_name
AND bc.transferring_branch = 'ALL')
OR (bc.transferring_bank = 'ALL'
AND bc.transferring_branch = 'ALL'))
AND ((bc.receiving_bank_branch_id = X_receiving_bank_branch_id
-- bug2242764 added bank_name condition
AND bc.receiving_bank_name = X_receiving_bank_name )
OR (bc.receiving_bank_name = X_receiving_bank_name
AND bc.receiving_branch in ('ALL', 'OTHER'))
OR (bc.receiving_bank in ('ALL', 'OTHER')
AND bc.receiving_branch = 'ALL'))
AND (bc.transfer_priority = X_transfer_priority
OR bc.transfer_priority = 'AR'
OR bc.transfer_priority = 'ANY')
AND bc.currency_code = X_currency_code
AND bc.bank_charge_id = bcl.bank_charge_id -- Bug 2073366
AND bcl.start_date <= P_transaction_date
AND nvl(bcl.end_date,
to_date('31-12-4712', 'DD-MM-YYYY')) > P_transaction_date;
SELECT bank_name
FROM ce_bank_branches_v
WHERE branch_party_id = X_bank_branch_id;
SELECT precision
FROM fnd_currencies
WHERE currency_code = X_currency_code;
SELECT trans_amount_from,
nvl(trans_amount_to, 99999999999999),
bank_charge_standard,
bank_charge_negotiated,
tolerance_limit
FROM ap_bank_charge_lines
WHERE bank_charge_id = X_bank_charge_id
AND (start_date <= X_transaction_date
AND nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
X_transaction_date)
ORDER BY trans_amount_from desc;
SELECT bank_charge_standard,
bank_charge_negotiated,
tolerance_limit
INTO P_bank_charge_standard,
P_bank_charge_negotiated,
P_tolerance_limit
FROM ap_bank_charge_lines
WHERE bank_charge_id = P_bank_charge_id
AND trans_amount_from <= v_transaction_amount
AND nvl(trans_amount_to, 99999999999999) > v_transaction_amount
AND start_date <= P_transaction_date
AND nvl(end_date,
to_date('31-12-4712', 'DD-MM-YYYY')) >
P_transaction_date;
SELECT bc.bank_charge_id,
bc.transferring_bank_branch_id,
bc.transferring_bank_name,
bc.transferring_bank,
bc.transferring_branch,
bc.receiving_bank_branch_id,
bc.receiving_bank_name,
bc.receiving_bank,
bc.receiving_branch,
bc.transfer_priority,
bc.currency_code
FROM ap_bank_charges bc, ap_bank_charge_lines bcl
WHERE ((bc.transferring_bank_branch_id = X_transferring_bank_branch_id
-- bug2242764 added bank_name condition
AND transferring_bank_name = X_transferring_bank_name)
OR (bc.transferring_bank_name = X_transferring_bank_name
AND bc.transferring_branch = 'ALL')
OR (bc.transferring_bank = 'ALL'
AND bc.transferring_branch = 'ALL'))
AND ((bc.receiving_bank_branch_id = X_receiving_bank_branch_id
-- bug2242764 added bank_name condition
AND receiving_bank_name = X_receiving_bank_name)
OR (bc.receiving_bank_name = X_receiving_bank_name
AND bc.receiving_branch in ('ALL', 'OTHER'))
OR (bc.receiving_bank in ('ALL', 'OTHER')
AND bc.receiving_branch = 'ALL'))
AND (bc.transfer_priority = X_transfer_priority
OR bc.transfer_priority = 'AR'
OR bc.transfer_priority = 'ANY')
AND bc.currency_code = X_currency_code
AND bc.bank_charge_id = bcl.bank_charge_id -- Bug 2177997
AND bcl.start_date <= P_transaction_date
AND nvl(bcl.end_date,
to_date('31-12-4712', 'DD-MM-YYYY')) > P_transaction_date;
SELECT bank_name
FROM ce_bank_branches_v
WHERE branch_party_id = X_bank_branch_id;
SELECT precision
FROM fnd_currencies
WHERE currency_code = X_currency_code;
SELECT trans_amount_from,
nvl(trans_amount_to, 99999999999999),
bank_charge_standard,
bank_charge_negotiated,
tolerance_limit
FROM ap_bank_charge_lines
WHERE bank_charge_id = X_bank_charge_id
AND (start_date <= X_transaction_date
AND nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
X_transaction_date)
ORDER BY trans_amount_from desc;
SELECT bank_charge_standard,
bank_charge_negotiated,
tolerance_limit
INTO v_bank_charge_standard,
v_bank_charge_negotiated,
v_tolerance_limit
FROM ap_bank_charge_lines
WHERE bank_charge_id = P_bank_charge_id
AND trans_amount_from <= v_transaction_amount
AND nvl(trans_amount_to, 99999999999999) > v_transaction_amount
AND start_date <= P_transaction_date
AND nvl(end_date,
to_date('31-12-4712', 'DD-MM-YYYY')) >
P_transaction_date;
p_selected_check_id IN NUMBER,
p_external_bank_account_id IN NUMBER,
p_currency_code IN VARCHAR2,
p_minimum_accountable_unit OUT nocopy NUMBER,
p_precision OUT nocopy NUMBER,
p_bank_charge_bearer OUT nocopy VARCHAR2,
p_transferring_bank_branch_id OUT nocopy NUMBER,
p_receiving_bank_branch_id OUT nocopy NUMBER,
p_transfer_priority OUT nocopy VARCHAR2,
p_num_of_invoices OUT nocopy NUMBER,
p_calling_sequence IN VARCHAR2,
p_internal_bank_account_id IN NUMBER,
p_supplier_site_id IN NUMBER) RETURN BOOLEAN IS
current_calling_sequence VARCHAR2(2000);
SELECT PVS.bank_charge_bearer
INTO p_bank_charge_bearer
FROM iby_hook_payments_t iby,
ap_supplier_sites_all PVS
WHERE iby.payment_id = p_selected_check_id
AND iby.supplier_site_id = PVS.vendor_site_id;
SELECT ABA.bank_branch_id
INTO p_transferring_bank_branch_id
FROM ce_bank_accounts ABA
WHERE aba.bank_account_id = p_internal_bank_account_id;
SELECT ieb.branch_id
INTO p_receiving_bank_branch_id
FROM iby_ext_bank_accounts ieb
WHERE ieb.ext_bank_account_id = p_external_bank_account_id;
SELECT AISC.transfer_priority
INTO p_transfer_priority
FROM ap_inv_selection_criteria_ALL AISC,
iby_hook_docs_in_pmt_t IBY
WHERE IBY.CALLING_APP_DOC_UNIQUE_REF1 = AISC.CHECKRUN_ID
and rownum=1;
SELECT count(*)
INTO p_num_of_invoices
FROM iby_hook_payments_t
WHERE payment_id = p_selected_check_id;
SELECT minimum_accountable_unit,
nvl(precision, 0)
INTO p_minimum_accountable_unit,
p_precision
FROM fnd_currencies_vl
WHERE currency_code = p_currency_code;
p_selected_check_id IN NUMBER,
p_amount_due OUT nocopy NUMBER,
p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
debug_info VARCHAR2(200);
SELECT sum(decode(dont_pay_flag, 'Y', 0,
document_amount + nvl(PAYMENT_CURR_DISCOUNT_TAKEN,0)))
INTO p_amount_due
FROM iby_hook_docs_in_pmt_t
WHERE payment_id= p_selected_check_id;
' p_selected_check_id = '||to_char(p_selected_check_id));
CURSOR selected_checks IS
SELECT iby.payment_id,
iby.payment_currency_code,
iby.payment_date,
iby.external_bank_account_id,
iby.dont_pay_flag,
iby.internal_bank_account_id,
iby.supplier_site_id
FROM iby_hook_payments_t iby,
ap_system_parameters_all asp --5007989
WHERE dont_pay_flag <> 'Y'
AND nvl(dont_pay_reason_code,'dummy') <> 'OVERFLOW'
AND asp.org_id = iby.org_id
AND nvl(asp.use_bank_charge_flag,'N') = 'Y'
ORDER BY payment_id;
CURSOR adjustment_for_rounding_error (c_selected_check_id NUMBER,
c_rounding_error NUMBER) IS
SELECT PAYMENT_CURR_DISCOUNT_TAKEN
FROM iby_hook_docs_in_pmt_t
WHERE payment_id = c_selected_check_id
AND ABS(document_amount) >= ABS(c_rounding_error)
ORDER BY PAYMENT_CURR_DISCOUNT_TAKEN desc;
l_selected_check_id NUMBER;
l_debug_info := 'Open selected_checks Cursor';
OPEN selected_checks;
l_debug_info := 'Fetch selected_checks Cursor';
FETCH selected_checks
INTO l_selected_check_id,
l_currency_code,
l_payment_date,
l_external_bank_account_id,
l_ok_to_pay_flag,
l_internal_bank_account_id,
l_supplier_site_id;
EXIT WHEN selected_checks%NOTFOUND;
l_selected_check_id,
l_external_bank_account_id,
l_currency_code,
l_min_account_unit, -- OUT
l_precision, -- OUT
l_bank_charge_bearer, -- OUT
l_transferring_bank_branch_id, -- OUT
l_receiving_bank_branch_id, -- OUT
l_transfer_priority, -- OUT
l_num_of_invoices, -- OUT, not currently used.
current_calling_sequence,
l_internal_bank_account_id,
l_supplier_site_id) <> TRUE) THEN
x_msg_data := 'Failed to derive transferring/receiving bank/branch info';
IF (bank_charge_get_amt_due(l_selected_check_id,
l_amt_due, -- OUT
current_calling_sequence) <> TRUE) THEN
x_msg_data := 'Failed to derive bank charge amount due';
l_debug_info := 'Update iby_hook_payments_t if ok_to_pay_flag is N';
UPDATE iby_hook_payments_t
SET payment_amount = 0,
dont_pay_flag = 'Y',
dont_pay_reason_code = l_bc_dont_pay_reason_code
WHERE payment_id = l_selected_check_id;
CLOSE selected_checks;
SELECT greatest( nvl(l_bank_charge_standard,0),
nvl(l_calc_bank_charge_standard,0))
INTO l_best_bank_charge
FROM sys.dual;
SELECT greatest( nvl(l_bank_charge_negotiated,0),
nvl(l_calc_bank_charge_negotiated,0))
INTO l_best_bank_charge
FROM sys.dual;
l_debug_info := 'Update iby_hook_payments_t';
UPDATE iby_hook_payments_t
SET DISCOUNT_AMOUNT_TAKEN= l_best_bank_charge,
payment_amount = l_amt_due - l_best_bank_charge
WHERE payment_id = l_selected_check_id;
l_debug_info := 'Update iby_hook_docs_in_pmt_t';
UPDATE iby_hook_docs_in_pmt_t
SET PAYMENT_CURR_DISCOUNT_TAKEN = decode(l_amt_due, 0, 0,
decode(l_min_account_unit,
null, round(l_best_bank_charge *
(document_amount/l_amt_due),
l_precision),
round(l_best_bank_charge *
(document_amount/l_amt_due)/l_min_account_unit) *
l_min_account_unit)
),
document_amount = (document_amount + nvl(PAYMENT_CURR_DISCOUNT_TAKEN, 0)) -
decode(l_amt_due, 0, 0,
decode(l_min_account_unit,
null, round(l_best_bank_charge *
(document_amount/l_amt_due),
l_precision),
round(l_best_bank_charge *
(document_amount/l_amt_due)/l_min_account_unit) *
l_min_account_unit)
)
WHERE payment_id = l_selected_check_id
AND nvl(dont_pay_flag, 'Y') = 'N';
SELECT l_amt_due - l_best_bank_charge - SUM(payment_amount)
INTO l_rounding_error
FROM iby_hook_payments_t
WHERE payment_id = l_selected_check_id;
SELECT max(ABS(PAYMENT_CURR_DISCOUNT_TAKEN))
INTO l_max_discount_amount
FROM iby_hook_docs_in_pmt_t
WHERE payment_id = l_selected_check_id
AND ABS(document_amount) >= ABS(l_rounding_error);
UPDATE iby_hook_docs_in_pmt_t
SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN - l_rounding_error,
document_amount = document_amount + l_rounding_error
WHERE payment_id = l_selected_check_id
AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
AND ABS(document_amount) >= ABS(l_rounding_error)
AND ROWNUM = 1;
OPEN adjustment_for_rounding_error(l_selected_check_id,
l_rem_rounding_error_amount);
/* Selecting the maximum discount amount again since the correction for the Rounding Difference
needs to be spread over multiple invoices. */
FETCH adjustment_for_rounding_error INTO l_max_discount_amount;
UPDATE iby_hook_docs_in_pmt_t
SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN - l_max_discount_amount,
document_amount = document_amount + l_max_discount_amount
WHERE payment_id = l_selected_check_id
AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
AND ABS(document_amount) >= ABS(l_rem_rounding_error_amount)
AND ROWNUM = 1;
UPDATE iby_hook_docs_in_pmt_t
SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN -
l_rem_rounding_error_amount,
document_amount = document_amount + l_rem_rounding_error_amount
WHERE payment_id = l_selected_check_id
AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
AND ABS(document_amount) >= ABS(l_rem_rounding_error_amount)
AND ROWNUM = 1;
l_debug_info := 'Close selected_checks Cursor';
CLOSE selected_checks;
CLOSE selected_checks;