[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_update_login IN NUMBER,
p_payment_method_code IN VARCHAR2, --4552701
p_external_bank_account_id IN NUMBER,
p_calling_sequence IN VARCHAR2,
p_sub_total IN OUT NOCOPY NUMBER,
p_sub_total_inv IN OUT NOCOPY NUMBER);
p_last_update_login IN NUMBER,
p_payment_method_code IN VARCHAR2, --4552701
p_external_bank_account_id IN NUMBER,
p_percent_remain_vs_gross IN NUMBER,
p_calling_sequence IN VARCHAR2);
PROCEDURE Insert_Pay_Sched(p_invoice_id IN NUMBER,
p_ps_pay_num IN NUMBER,
p_system_user IN NUMBER,
p_payment_cross_rate IN NUMBER,
p_due_date IN DATE,
p_1st_discount_date IN DATE,
p_2nd_discount_date IN DATE,
p_3rd_discount_date IN DATE,
p_gross_amount IN NUMBER,
p_inv_curr_gross_amount IN NUMBER, -- R11: Xcurr
p_1st_disc_amt_available IN NUMBER,
p_2nd_disc_amt_available IN NUMBER,
p_3rd_disc_amt_available IN NUMBER,
p_payment_priority IN NUMBER,
p_hold_flag IN VARCHAR2,
p_payment_status_flag IN VARCHAR2,
p_batch_id IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_login IN NUMBER,
p_payment_method_code IN VARCHAR2, --4552701
p_external_bank_account_id IN NUMBER,
p_percent_remain_vs_gross IN NUMBER,
p_calling_sequence IN VARCHAR2);
PROCEDURE Delete_PaySchd_Wth_PayNum_Gtr(p_invoice_id IN NUMBER,
p_payment_num IN NUMBER,
p_calling_sequence IN VARCHAR2);
PROCEDURE Update_Pay_Sched(p_invoice_id IN NUMBER,
p_payment_num IN NUMBER,
p_ppa_due_date IN DATE,
p_1st_disc_amt_available IN NUMBER,
p_2nd_disc_amt_available IN NUMBER,
p_3rd_disc_amt_available IN NUMBER,
p_1st_discount_date IN DATE,
p_2nd_discount_date IN DATE,
p_3rd_discount_date IN DATE,
p_system_user IN NUMBER,
p_gross_amount IN NUMBER,
p_inv_curr_gross_amount IN NUMBER, -- R11: Xcurr
p_percent_remain_vs_gross IN NUMBER,
p_calling_sequence IN VARCHAR2);
SELECT DISTINCT ps.invoice_id,
ps.payment_num,
i.invoice_date,
t.term_id,
t.name,
nvl(t.rank,999),
nvl(ps.payment_cross_rate,1),
i.invoice_num,
nvl(ps.payment_priority,0),
ps.hold_flag,
ps.payment_status_flag,
nvl(ps.batch_id,0),
ps.creation_date,
nvl(ps.created_by,0),
nvl(ps.last_update_login,0),
ps.payment_method_code, --4552701
ps.external_bank_account_id,
nvl(i.payment_currency_code,i.invoice_currency_code),
i.terms_date,
i.goods_received_date
FROM ap_terms t,
ap_invoice_distributions d,
ap_invoices i,
ap_payment_schedules ps
WHERE i.payment_status_flag = 'N'
AND ps.amount_remaining > 0
AND ps.invoice_id = i.invoice_id
AND i.invoice_id = d.invoice_id
AND i.terms_id = t.term_id
AND i.invoice_type_lookup_code <> 'INTEREST'
AND (i.invoice_id = p_invoice_id
AND d.invoice_id = p_invoice_id
AND NOT EXISTS
(SELECT h.invoice_id
FROM ap_holds h, ap_hold_codes c
WHERE h.hold_lookup_code = c.hold_lookup_code
AND h.release_lookup_code is null
AND c.user_releaseable_flag = 'N'
AND h.invoice_id=p_invoice_id))
ORDER BY ps.invoice_id, ps.payment_num;
l_last_update_login NUMBER(15);
l_last_update_login,
l_payment_method_code, --4552701
l_external_bank_account_id,
l_payment_currency_code,
l_terms_date,
l_goods_received_date;
l_last_update_login,
l_payment_method_code, --4552701
l_external_bank_account_id,
l_curr_calling_sequence,
l_sub_total,
l_sub_total_inv);
SELECT MAX(greatest(to_date(p_inv_date),
i.terms_date,
least(to_date(nvl(i.goods_received_date +
nvl(p_receipt_acc_days,0),
nvl(rt.transaction_date,p_inv_date))),
to_date(nvl(rt.transaction_date,
nvl(i.goods_received_date +
nvl(p_receipt_acc_days,0),p_inv_date))))))
INTO p_start_date
FROM ap_invoices i, rcv_transactions rt, rcv_shipment_lines rsl,
ap_invoice_distributions ad, po_distributions_ap_v d
WHERE i.invoice_id = p_invoice_id
AND i.invoice_id = ad.invoice_id(+)
AND ad.po_distribution_id = d.po_distribution_id
AND d.po_header_id = rsl.po_header_id(+)
AND rsl.shipment_line_id = rt.shipment_line_id(+)
AND decode(rt.transaction_type(+), 'ACCEPT', '1',
'REJECT', '1', '0') = '1';
SELECT MIN(rt.transaction_date)
INTO l_transaction_date
FROM rcv_transactions rt, rcv_shipment_lines rsl,
ap_invoice_distributions ad, po_distributions d
WHERE ad.invoice_id = p_invoice_id
AND ad.po_distribution_id = d.po_distribution_id
AND d.po_header_id = rsl.po_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_type IN ('ACCEPT','REJECT','RECEIVE','DELIVER');
SELECT max(greatest(p_inv_date,
p_terms_date,
greatest(nvl(l_goods_received_date + nvl(p_receipt_acc_days,0),
nvl(l_transaction_date,p_inv_date)),
nvl(l_transaction_date,
nvl(l_goods_received_date + nvl(p_receipt_acc_days,0),p_inv_date)))))
INTO p_start_date
FROM dual;
SELECT
greatest(p_inv_date,
i.terms_date,
nvl(i.goods_received_date +
nvl(p_receipt_acc_days,0), p_inv_date))
INTO p_start_date
FROM ap_invoices i
WHERE i.invoice_id = p_invoice_id;
SELECT min(h.terms_id)
INTO p_po_term_id
FROM po_headers h,po_distributions_ap_v d,
ap_invoice_distributions id
WHERE id.invoice_id = p_invoice_id
AND id.po_distribution_id = d.po_distribution_id
AND d.po_header_id = h.po_header_id;
SELECT nvl(t.rank, 999), t.name
INTO p_po_rank, p_po_terms_name
FROM ap_terms t
WHERE t.term_id = p_po_term_id;
p_last_update_login IN NUMBER,
p_payment_method_code IN VARCHAR2, --4552701
p_external_bank_account_id IN NUMBER,
p_calling_sequence IN VARCHAR2,
p_sub_total IN OUT NOCOPY NUMBER,
p_sub_total_inv IN OUT NOCOPY NUMBER) IS
l_total_gross_amount NUMBER;
l_debug_info := 'Update Payment Schedules';
Update_Pay_Sched(p_invoice_id,
p_curr_ps_pay_num,
l_ppa_due_date,
l_1st_disc_amt_available,
l_2nd_disc_amt_available,
l_3rd_disc_amt_available,
l_1st_discount_date,
l_2nd_discount_date,
l_3rd_discount_date,
p_system_user,
l_gross_amount,
l_inv_curr_gross_amount, -- R11: Xcurr
l_percent_remain_vs_gross,
l_curr_calling_sequence);
l_debug_info := 'Delete Pay Schedules form the invoice';
Delete_PaySchd_Wth_PayNum_Gtr(p_invoice_id, p_curr_ps_pay_num,
l_curr_calling_sequence);
p_last_update_login,
p_payment_method_code, --4552701
p_external_bank_account_id,
l_percent_remain_vs_gross,
l_curr_calling_sequence);
SELECT invoice_amount,
nvl(pay_curr_invoice_amount,invoice_amount) pay_curr_invoice_amount, -- R11: Xcurr
amount_applicable_to_discount
INTO p_invoice_amount,
p_pay_curr_invoice_amount, -- R11: Xcurr
p_amount_applicable_to_disc
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
SELECT count(*), sum(gross_amount),
sum(nvl(inv_curr_gross_amount, gross_amount)), -- R11: Xcurr
sum(nvl(amount_remaining, gross_amount))
INTO p_last_inv_ps_pay_num, p_ps_total_gross_amount,
p_ps_total_inv_curr_gross_amt, -- R11: Xcurr
l_amount_remaining
FROM ap_payment_schedules
WHERE invoice_id = p_invoice_id;
SELECT count(*)
INTO p_last_term_ps_pay_num
FROM ap_terms_lines
WHERE term_id = p_term_id;
PROCEDURE Update_Pay_Sched(p_invoice_id IN NUMBER,
p_payment_num IN NUMBER,
p_ppa_due_date IN DATE,
p_1st_disc_amt_available IN NUMBER,
p_2nd_disc_amt_available IN NUMBER,
p_3rd_disc_amt_available IN NUMBER,
p_1st_discount_date IN DATE,
p_2nd_discount_date IN DATE,
p_3rd_discount_date IN DATE,
p_system_user IN NUMBER,
p_gross_amount IN NUMBER,
p_inv_curr_gross_amount IN NUMBER, -- R11: Xcurr
p_percent_remain_vs_gross IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_debug_loc VARCHAR2(30) := 'Update_Pay_Sched';
SELECT payment_currency_code
INTO l_pay_curr_code
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
UPDATE ap_payment_schedules
SET due_date = p_ppa_due_date,
discount_amount_available = p_1st_disc_amt_available,
discount_amount_remaining = p_1st_disc_amt_available,
second_disc_amt_available = p_2nd_disc_amt_available,
third_disc_amt_available = p_3rd_disc_amt_available,
discount_date = p_1st_discount_date,
second_discount_date = p_2nd_discount_date,
third_discount_date = p_3rd_discount_date,
last_update_date = SYSDATE,
last_updated_by = p_system_user,
gross_amount = p_gross_amount,
inv_curr_gross_amount = p_inv_curr_gross_amount, -- R11: Xcurr
amount_remaining = ap_utilities_pkg.ap_round_currency(
p_gross_amount * nvl(p_percent_remain_vs_gross,1),
l_pay_curr_code)
WHERE invoice_id = p_invoice_id
AND payment_num = p_payment_num;
SELECT SUM(gross_amount)
INTO l_pay_sched_total
FROM ap_payment_schedules
WHERE invoice_id = P_Invoice_Id;
END Update_Pay_Sched;
PROCEDURE Delete_PaySchd_Wth_PayNum_Gtr(p_invoice_id IN NUMBER,
p_payment_num IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_debug_loc VARCHAR2(30) := 'Delete_PaySchd_Wth_PayNum_Gtr';
DELETE FROM ap_payment_schedules
WHERE invoice_id = p_invoice_id
AND payment_num > p_payment_num;
END Delete_PaySchd_Wth_PayNum_Gtr;
SELECT nvl(minimum_accountable_unit,0), precision
INTO l_min_unit, l_precision
FROM fnd_currencies
WHERE currency_code = ( SELECT payment_currency_code -- R11: Xcurr
FROM ap_invoices
WHERE invoice_id = p_invoice_id);
SELECT calendar
INTO l_terms_calendar
FROM ap_terms_lines
WHERE term_id = p_term_id
AND sequence_num = p_ps_pay_num;
SELECT
-- for first discount date
decode(atl.fixed_date, NULL,
decode(atl.discount_days, NULL,
decode(atl.discount_day_of_month,
null, null,
to_date(to_char(
least(nvl(atl.discount_day_of_month,32), --2936672
to_number(to_char(
last_day(
add_months(p_start_date,
nvl(atl.discount_months_forward ,0)+ --2936672
decode(t.due_cutoff_day,NULL,0, --2936672
decode(
greatest(
least(NVL(t.due_cutoff_day, 32),
to_number(to_char(last_day(p_start_date),'DD'))
),
to_number(to_char(p_start_date,'DD'))
),
to_number(to_char(p_start_date,'DD')),
1, 0)))
), 'DD')))) || '-'
||to_char(add_months(p_start_date,
nvl(atl.discount_months_forward ,0)+ --2936672
decode(t.due_cutoff_day,NULL,0, --2936672
decode(
greatest(
least(NVL(t.due_cutoff_day, 32),
to_number(to_char(last_day(p_start_date),'DD'))
),
to_number(to_char(p_start_date, 'DD'))),
to_number(to_char(p_start_date, 'DD')),
1, 0))), 'MON-RR'),'DD/MM/RRRR') --Bug 7534693
),
p_start_date + atl.discount_days),
atl.fixed_date) DISCOUNT1,
-- for second discount date
decode(atl.fixed_date, NULL,
decode(atl.discount_days_2, null,
decode(atl.discount_day_of_month_2, null, null,
to_date(to_char(
least(nvl(atl.discount_day_of_month_2,32), --2936672
to_number(to_char(
last_day(
add_months(p_start_date,
nvl(atl.discount_months_forward_2 ,0)+ --2936672
decode(t.due_cutoff_day,NULL,0, --2936672
decode(
greatest(
least(NVL(t.due_cutoff_day, 32),
to_number(to_char(last_day(p_start_date), 'DD'))),
to_number(to_char(p_start_date, 'DD'))),
to_number(to_char(p_start_date, 'DD')),
1, 0)))), 'DD')))) || '-'
|| to_char(add_months(p_start_date,
nvl(atl.discount_months_forward_2 ,0)+ --2936672
decode(t.due_cutoff_day,NULL,0, --2936672
decode(
greatest(
least(NVL(t.due_cutoff_day, 32),
to_number(to_char(last_day(p_start_date),'DD'))),
to_number(to_char(p_start_date, 'DD'))),
to_number(to_char(p_start_date,'DD')), 1, 0))), 'MON-RR'),'DD/MM/RRRR')), --Bug 7534693
p_start_date + atl.discount_days_2),
atl.fixed_date) DISCOUNT2,
-- for the third discount date
decode(atl.fixed_date, NULL,
decode(atl.discount_days_3, null,
decode(atl.discount_day_of_month_3, null, null,
to_date(to_char(
least(nvl(atl.discount_day_of_month_3,32), --2936672
to_number(to_char(
last_day(
add_months(p_start_date,
NVL(atl.discount_months_forward_3,0) + --2936672
decode(t.due_cutoff_day,NULL,0, --2936672
decode(
greatest(
least(NVL(t.due_cutoff_day, 32),
to_number(to_char(last_day(p_start_date), 'DD'))),
to_number(to_char(p_start_date, 'DD'))),
to_number(to_char(p_start_date, 'DD')),
1, 0)))), 'DD')))) || '-'
|| to_char(add_months(p_start_date,
nvl(atl.discount_months_forward_3 ,0)+ --2936672
decode(t.due_cutoff_day,NULL,0, --2936672
decode(
greatest(
least(NVL(t.due_cutoff_day, 32),
to_number(to_char(last_day(p_start_date),'DD'))),
to_number(to_char(p_start_date, 'DD'))),
to_number(to_char(p_start_date,'DD')), 1, 0))), 'MON-RR'),'DD/MM/RRRR')), --Bug 7534693
p_start_date + atl.discount_days_3),
atl.fixed_date) DISCOUNT3,
-- for discount_amt_available
(DECODE(l_min_unit,0,
ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent/100,
atl.due_amount) *
atl.discount_percent/100, 0),l_precision),
ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
atl.due_amount) *
atl.discount_percent/100, 0) / l_min_unit) * l_min_unit)),
-- for secnd_disc_amt_available
(DECODE(l_min_unit,0,
ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
atl.due_amount) *
atl.discount_percent_2/100, 0),l_precision),
ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
atl.due_amount) *
atl.discount_percent_2/100, 0) / l_min_unit) * l_min_unit)),
-- for third_disc_amt_available
(DECODE(l_min_unit,0,
ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
atl.due_amount) *
atl.discount_percent_3/100, 0),l_precision),
ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
atl.due_amount) *
atl.discount_percent_3/100, 0) / l_min_unit) * l_min_unit)),
/* Bug fix:1237758 */
DECODE(l_min_unit,0,
ROUND(NVL((p_total_pay_curr_amount * due_percent/100),
due_amount),l_precision),
ROUND(NVL((p_total_pay_curr_amount * due_percent/100),
due_amount)/l_min_unit) * l_min_unit),
/* Bug fix:1237758 */
DECODE(l_min_unit,0,
ROUND(NVL((p_total_amount * due_percent/100),
due_amount / p_payment_cross_rate),l_precision),
ROUND(NVL((p_total_amount * due_percent/100),
due_amount / p_payment_cross_rate)/ l_min_unit) * l_min_unit),
/* nvl((p_total_pay_curr_amount * due_percent/100), due_amount),
nvl((p_total_amount * due_percent/100), due_amount / p_payment_cross_rate),
*/
nvl(atl.discount_percent,0),
nvl( atl.discount_percent_2,0),
nvl( atl.discount_percent_3,0),
nvl(due_amount,0),
atl.due_percent
INTO
l_discount_date,
l_second_discount_date,
l_third_discount_date,
l_discount_amt_available,
l_secnd_disc_amt_available,
l_third_disc_amt_available,
p_gross_amount,
p_inv_curr_gross_amount, -- R11: Xcurr
p_discount_percent_1,
p_discount_percent_2,
p_discount_percent_3,
p_due_amount,
p_due_percent
FROM ap_terms t, ap_terms_lines atl
WHERE t.term_id = atl.term_id
AND atl.term_id = p_term_id
AND atl.sequence_num = p_ps_pay_num;
Select sum(DECODE(l_min_unit,0,
ROUND(due_amount,l_precision),
ROUND(due_amount/l_min_unit) * l_min_unit)),
sum(DECODE(l_min_unit,0,
ROUND(due_amount / p_payment_cross_rate,l_precision),
ROUND((due_amount / p_payment_cross_rate)/ l_min_unit) * l_min_unit))
into p_gross_amount,
p_inv_curr_gross_amount
FROM ap_terms t, ap_terms_lines atl
WHERE t.term_id = atl.term_id
AND atl.term_id = p_term_id
AND atl.sequence_num < p_ps_pay_num;
PROCEDURE Insert_Pay_Sched(p_invoice_id IN NUMBER,
p_ps_pay_num IN NUMBER,
p_system_user IN NUMBER,
p_payment_cross_rate IN NUMBER,
p_due_date IN DATE,
p_1st_discount_date IN DATE,
p_2nd_discount_date IN DATE,
p_3rd_discount_date IN DATE,
p_gross_amount IN NUMBER,
p_inv_curr_gross_amount IN NUMBER, -- R11: Xcurr
p_1st_disc_amt_available IN NUMBER,
p_2nd_disc_amt_available IN NUMBER,
p_3rd_disc_amt_available IN NUMBER,
p_payment_priority IN NUMBER,
p_hold_flag IN VARCHAR2,
p_payment_status_flag IN VARCHAR2,
p_batch_id IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_login IN NUMBER,
p_payment_method_code IN VARCHAR2, --4552701
p_external_bank_account_id IN NUMBER,
p_percent_remain_vs_gross IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_debug_loc VARCHAR2(30) := 'Insert_Pay_Sched';
SELECT payment_currency_code, org_id
INTO l_pay_curr_code, l_org_id /* Bug 3700128. MOAC Project */
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
INSERT INTO ap_payment_schedules
(invoice_id,
payment_num,
last_updated_by,
last_update_date,
payment_cross_rate,
due_date,
discount_date,
gross_amount,
inv_curr_gross_amount, -- R11: Xcurr
discount_amount_available,
amount_remaining,
discount_amount_remaining,
payment_priority,
hold_flag,
payment_status_flag,
batch_id,
creation_date,
created_by,
last_update_login,
payment_method_code, --4552701
external_bank_account_id,
second_discount_date,
third_discount_date,
second_disc_amt_available,
third_disc_amt_available,
org_id ) /* Bug 3700128. MOAC Project */
VALUES (p_invoice_id,
p_ps_pay_num,
p_system_user,
sysdate,
p_payment_cross_rate,
p_due_date,
p_1st_discount_date,
p_gross_amount,
p_inv_curr_gross_amount, -- R11: Xcurr
p_1st_disc_amt_available,
ap_utilities_pkg.ap_round_currency(
p_gross_amount * nvl(p_percent_remain_vs_gross,1),
l_pay_curr_code ),
p_1st_disc_amt_available,
p_payment_priority,
p_hold_flag,
p_payment_status_flag,
p_batch_id,
p_creation_date,
p_created_by,
p_last_update_login,
p_payment_method_code, --4552701
p_external_bank_account_id,
p_2nd_discount_date,
p_3rd_discount_date,
p_2nd_disc_amt_available,
p_3rd_disc_amt_available,
l_org_id); /* Bug 3700128. MOAC Project */
|| 'Last Update Login = '|| to_char(p_last_update_login)
|| 'Payment Method = '|| p_payment_method_code);
END Insert_Pay_Sched;
p_last_update_login IN NUMBER,
p_payment_method_code IN VARCHAR2, --4552701
p_external_bank_account_id IN NUMBER,
p_percent_remain_vs_gross IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_last_inv_ps_pay_num NUMBER;
l_debug_info := 'Insert Payment Schedules';
Insert_Pay_Sched(p_invoice_id,
l_curr_ps_pay_num,
p_system_user,
p_payment_cross_rate,
l_ppa_due_date,
l_1st_discount_date,
l_2nd_discount_date,
l_3rd_discount_date,
l_gross_amount,
l_inv_curr_gross_amount, -- R11: Xcurr
l_1st_disc_amt_available,
l_2nd_disc_amt_available,
l_3rd_disc_amt_available,
p_payment_priority,
p_hold_flag,
p_payment_status_flag,
p_batch_id,
p_creation_date,
p_created_by,
p_last_update_login,
p_payment_method_code, --4552701
p_external_bank_account_id,
p_percent_remain_vs_gross,
l_curr_calling_sequence);
|| 'Last Update Login = '|| to_char(p_last_update_login)
|| 'Payment Method Lookup = '|| p_payment_method_code
||' p_external_bank_account_id = '||to_char(p_external_bank_account_id));
l_debug_loc := 'Update ap_payment_schedules - set gross_amount';
SELECT SUM(gross_amount)
INTO l_pay_sched_total
FROM ap_payment_schedules
WHERE invoice_id = P_Invoice_Id;
SELECT nvl(pay_curr_invoice_amount,invoice_amount) pay_curr_invoice_amount
INTO l_pay_curr_invoice_amount
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
UPDATE AP_PAYMENT_SCHEDULES
SET gross_amount = gross_amount + TO_NUMBER(l_Pay_Curr_Invoice_Amount) -
TO_NUMBER(l_pay_sched_total),
amount_remaining=amount_remaining + TO_NUMBER(l_Pay_Curr_Invoice_Amount) -
TO_NUMBER(l_pay_sched_total)
WHERE invoice_id = P_Invoice_Id
AND payment_num = (SELECT MAX(payment_num)
FROM ap_payment_schedules
WHERE invoice_id = P_Invoice_Id);