[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT due_date
INTO l_due_date
FROM ap_other_periods aop
WHERE aop.period_type = p_calendar
AND aop.module = 'PAYMENT TERMS'
AND TRUNC(P_Terms_Date) BETWEEN start_date AND end_date;
SELECT NVL(fixed_date,
(DECODE(ap_terms_lines.due_days,
NULL,TO_DATE(TO_CHAR(LEAST(NVL(ap_terms_lines.due_day_of_month,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.due_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.due_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-MON-RR'), /*bugfix:5647464 */
P_Terms_Date + NVL(ap_terms_lines.due_days,0))))
INTO l_due_date
FROM ap_terms,
ap_terms_lines
WHERE ap_terms.term_id = P_Terms_Id
AND ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.sequence_num = p_sequence_num;
P_Last_Updated_By IN number,
P_Created_By IN number,
P_Payment_Priority IN number,
P_Batch_Id IN number,
P_Terms_Date IN date,
P_Invoice_Amount IN number,
P_Pay_Curr_Invoice_Amount IN number,
P_payment_cross_rate IN number,
P_Amount_For_Discount IN number,
P_Payment_Method IN varchar2,
P_Invoice_Currency IN varchar2,
P_Payment_currency IN varchar2,
P_calling_sequence IN varchar2
) IS
l_payment_schedule_index BINARY_INTEGER := 0;
T_LAST_UPDATE_DATE LAST_UPDATE_DATE;
T_LAST_UPDATED_BY LAST_UPDATED_BY;
T_LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN;
SELECT 'Terms are percent type'
FROM ap_terms_lines
WHERE term_id = P_Terms_Id
AND sequence_num = 1
AND due_percent IS NOT NULL;
SELECT calendar, sequence_num
FROM ap_terms_lines
WHERE term_id = p_terms_id
ORDER BY sequence_num;
SELECT SIGN(ABS(P_Invoice_Amount))
, SIGN(due_amount)
, due_amount
, SIGN(ABS(l_remaining_amount) - ABS(due_amount))
, ABS(l_remaining_amount) - ABS(due_amount)
, calendar
FROM ap_terms_lines
WHERE term_id = P_Terms_Id
AND sequence_num = l_sequence_num;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO l_min_acc_unit_pay_curr,
l_precision_pay_curr
FROM fnd_currencies fc
WHERE fc.currency_code = P_Payment_Currency;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO l_min_acc_unit_inv_curr,
l_precision_inv_curr
FROM fnd_currencies fc
WHERE fc.currency_code = P_Invoice_Currency;
SELECT invoice_type_lookup_code,
vendor_site_id
INTO l_invoice_type,
l_vendor_site_id
FROM ap_invoices
WHERE invoice_id = P_Invoice_Id;
SELECT payment_priority
INTO l_payment_priority
FROM po_vendor_sites
WHERE vendor_site_id = l_vendor_site_id;
debug_info := 'Insert into ap_payment_schedules';
SELECT DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0, (l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent,0)/100 ,l_precision_pay_curr),
ROUND(( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent,0)/100)
/ l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr)
, DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent_2,0)/100 ,l_precision_pay_curr),
ROUND(( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent_2,0)/100)
/ l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr)
, DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent_3,0)/100 ,l_precision_pay_curr),
ROUND(( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent_3,0)/100)
/ l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr),
discount_amount,
discount_amount_2,
discount_amount_3
INTO
l_disc_amt_by_percent, l_disc_amt_by_percent_2, l_disc_amt_by_percent_3,
l_discount_amount, l_discount_amount_2, l_discount_amount_3
FROM ap_terms
, ap_terms_lines
, ap_invoices ai
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.term_id = P_Terms_Id
AND ap_terms_lines.sequence_num = l_sequence_num
AND ai.Invoice_Id = P_Invoice_Id;
SELECT P_Invoice_Id,
l_sequence_num
, l_due_date
, DECODE(ap_terms_lines.discount_days,
NULL,
DECODE(ap_terms_lines.discount_day_of_month, NULL, NULL,
TO_DATE(TO_CHAR(LEAST(NVL(ap_terms_lines.discount_day_of_month,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS
(P_Terms_Date, NVL(ap_terms_lines.discount_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-MON-RR')
),
P_Terms_Date + NVL(ap_terms_lines.discount_days,0)
)
, DECODE(ap_terms_lines.discount_days_2,
NULL,DECODE(ap_terms_lines.discount_day_of_month_2,NULL,NULL,
TO_DATE(TO_CHAR(LEAST
(NVL(ap_terms_lines.discount_day_of_month_2,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_2,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_2,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
P_Terms_Date + NVL(ap_terms_lines.discount_days_2,0))
, DECODE(ap_terms_lines.discount_days_3,
NULL, DECODE(ap_terms_lines.discount_day_of_month_3, NULL,
NULL,
TO_DATE(TO_CHAR(LEAST
(NVL(ap_terms_lines.discount_day_of_month_3,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_3,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_3,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-M0N-RR')),
P_Terms_Date + NVL(ap_terms_lines.discount_days_3,0))
, SYSDATE
, P_Last_Updated_By
, NULL
, SYSDATE
, P_Created_By
, l_payment_cross_rate
, DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND(l_ins_gross_amount,
l_precision_pay_curr),
ROUND(l_ins_gross_amount
/l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr)
,NULL,
--Bug 7357218 Quick Pay and Dispute Resolution Project
--Considering absolute amount and criteria for all three discounts
CASE
WHEN discount_criteria IS NULL OR discount_criteria = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount,0)) > abs(l_disc_amt_by_percent) THEN
l_discount_amount
ELSE l_disc_amt_by_percent
END
ELSE CASE WHEN abs(nvl(l_discount_amount,0)) < abs(l_disc_amt_by_percent) THEN
l_discount_amount
ELSE l_disc_amt_by_percent
END
END,
CASE
WHEN discount_criteria_2 IS NULL OR discount_criteria_2 = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount_2,0)) > abs(l_disc_amt_by_percent_2) THEN
l_discount_amount_2
ELSE l_disc_amt_by_percent_2
END
ELSE CASE WHEN abs(nvl(l_discount_amount_2,0)) < abs(l_disc_amt_by_percent_2) THEN
l_discount_amount_2
ELSE l_disc_amt_by_percent_2
END
END,
CASE
WHEN discount_criteria_3 IS NULL OR discount_criteria_3 = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount_3,0)) > abs(l_disc_amt_by_percent_3) THEN
l_discount_amount_3
ELSE l_disc_amt_by_percent_3
END
ELSE CASE WHEN abs(nvl(l_discount_amount_3,0)) < abs(l_disc_amt_by_percent_3) THEN
l_discount_amount_3
ELSE l_disc_amt_by_percent_3
END
END,
DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND(l_ins_gross_amount,
l_precision_pay_curr),
ROUND(l_ins_gross_amount
/l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr)
, 0
, 'N'
, 'N'
, P_Batch_Id
, NVL(P_Payment_Method, 'CHECK')
, ai.external_bank_account_id --4393358
,ai.org_id
,ai.remittance_message1
,ai.remittance_message2
,ai.remittance_message3
--third party payments
,ai.remit_to_supplier_name
,ai.remit_to_supplier_id
,ai.remit_to_supplier_site
,ai.remit_to_supplier_site_id
,ai.relationship_id
INTO
T_INVOICE_ID(l_payment_schedule_index),
T_PAYMENT_NUM(l_payment_schedule_index),
T_DUE_DATE(l_payment_schedule_index),
T_DISCOUNT_DATE(l_payment_schedule_index),
T_SECOND_DISCOUNT_DATE(l_payment_schedule_index),
T_THIRD_DISCOUNT_DATE(l_payment_schedule_index),
T_LAST_UPDATE_DATE(l_payment_schedule_index),
T_LAST_UPDATED_BY(l_payment_schedule_index),
T_LAST_UPDATE_LOGIN(l_payment_schedule_index),
T_CREATION_DATE(l_payment_schedule_index),
T_CREATED_BY(l_payment_schedule_index),
T_PAYMENT_CROSS_RATE(l_payment_schedule_index),
T_GROSS_AMOUNT(l_payment_schedule_index),
T_INV_CURR_GROSS_AMOUNT(l_payment_schedule_index),
T_DISCOUNT_AMOUNT_AVAILABLE(l_payment_schedule_index),
T_SECOND_DISC_AMT_AVAILABLE(l_payment_schedule_index),
T_THIRD_DISC_AMT_AVAILABLE(l_payment_schedule_index),
T_AMOUNT_REMAINING(l_payment_schedule_index),
T_DISCOUNT_AMOUNT_REMAINING(l_payment_schedule_index),
T_HOLD_FLAG(l_payment_schedule_index),
T_PAYMENT_STATUS_FLAG(l_payment_schedule_index),
T_BATCH_ID(l_payment_schedule_index),
T_PAYMENT_METHOD_CODE(l_payment_schedule_index),
T_EXTERNAL_BANK_ACCOUNT_ID(l_payment_schedule_index),
T_ORG_ID(l_payment_schedule_index),
T_REMITTANCE_MESSAGE1(l_payment_schedule_index),
T_REMITTANCE_MESSAGE2(l_payment_schedule_index),
T_REMITTANCE_MESSAGE3(l_payment_schedule_index),
--Third Party Payments
T_REMIT_TO_SUPPLIER_NAME(l_payment_schedule_index),
T_REMIT_TO_SUPPLIER_ID(l_payment_schedule_index),
T_REMIT_TO_SUPPLIER_SITE(l_payment_schedule_index),
T_REMIT_TO_SUPPLIER_SITE_ID(l_payment_schedule_index),
T_RELATIONSHIP_ID(l_payment_schedule_index)
FROM ap_terms
, ap_terms_lines
, ap_invoices ai
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.term_id = P_Terms_Id
AND ap_terms_lines.sequence_num = l_sequence_num
AND ai.Invoice_Id = P_Invoice_Id;
debug_info := 'Insert into ap_payment_schedules : term type is percent';
SELECT DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100, l_precision_pay_curr),
ROUND(( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100)
/ l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr)
, DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent_2,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100, l_precision_pay_curr),
ROUND(( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent_2,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100)
/ l_min_acc_unit_pay_curr)*l_min_acc_unit_pay_curr)
, DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent_3,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100, l_precision_pay_curr),
ROUND(( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent_3,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100)
/ l_min_acc_unit_pay_curr)*l_min_acc_unit_pay_curr),
discount_amount,
discount_amount_2,
discount_amount_3
INTO
l_disc_amt_by_percent, l_disc_amt_by_percent_2, l_disc_amt_by_percent_3,
l_discount_amount, l_discount_amount_2, l_discount_amount_3
FROM ap_terms,
ap_terms_lines,
ap_invoices ai
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.term_id = P_Terms_Id
AND ap_terms_lines.sequence_num = l_sequence_num
AND ai.invoice_id = P_Invoice_Id;
SELECT P_Invoice_Id,l_sequence_num
, l_due_date
, DECODE(l_amount_for_discount, NULL, NULL,
DECODE(ap_terms_lines.discount_days,
NULL, DECODE(ap_terms_lines.discount_day_of_month, NULL, NULL,
TO_DATE(TO_CHAR(LEAST(NVL(ap_terms_lines.discount_day_of_month,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS
(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-MON-RR')),
P_Terms_Date + NVL(ap_terms_lines.discount_days,0)))
, DECODE(l_amount_for_discount, NULL, NULL,
DECODE(ap_terms_lines.discount_days_2,
NULL,DECODE(ap_terms_lines.discount_day_of_month_2,NULL,NULL,
TO_DATE(TO_CHAR(LEAST(
NVL(ap_terms_lines.discount_day_of_month_2,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_2,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_2,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-MON-RR')),
P_Terms_Date + NVL(ap_terms_lines.discount_days_2,0)))
, DECODE(l_amount_for_discount, NULL, NULL,
DECODE(ap_terms_lines.discount_days_3,
NULL,DECODE(ap_terms_lines.discount_day_of_month_3,NULL,NULL,
TO_DATE(TO_CHAR(LEAST(
NVL(ap_terms_lines.discount_day_of_month_3,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_3,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_3,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-M0N-RR')),
P_Terms_Date + NVL(ap_terms_lines.discount_days_3,0)))
, SYSDATE
, P_Last_Updated_By
, NULL
, SYSDATE
, P_Created_By
, l_payment_cross_rate
, DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND(P_Pay_Curr_Invoice_Amount *
NVL(ap_terms_lines.due_percent,0)/100,l_precision_pay_curr),
ROUND((P_Pay_Curr_Invoice_Amount *
NVL(ap_terms_lines.due_percent,0)/100)
/ l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr)
, NULL ,
--Bug 7357218 Quick Pay and Dispute Resolution Project
--Considering absolute amount and criteria for all three discounts
CASE
WHEN discount_criteria IS NULL OR discount_criteria = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount,0)) > abs(l_disc_amt_by_percent) THEN
l_discount_amount
ELSE l_disc_amt_by_percent
END
ELSE CASE WHEN abs(nvl(l_discount_amount,0)) < abs(l_disc_amt_by_percent) THEN
l_discount_amount
ELSE l_disc_amt_by_percent
END
END,
CASE
WHEN discount_criteria_2 IS NULL OR discount_criteria_2 = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount_2,0)) > abs(l_disc_amt_by_percent_2) THEN
l_discount_amount_2
ELSE l_disc_amt_by_percent_2
END
ELSE CASE WHEN abs(nvl(l_discount_amount_2,0)) < abs(l_disc_amt_by_percent_2) THEN
l_discount_amount_2
ELSE l_disc_amt_by_percent_2
END
END,
CASE
WHEN discount_criteria_3 IS NULL OR discount_criteria_3 = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount_3,0)) > abs(l_disc_amt_by_percent_3) THEN
l_discount_amount_3
ELSE l_disc_amt_by_percent_3
END
ELSE CASE WHEN abs(nvl(l_discount_amount_3,0)) < abs(l_disc_amt_by_percent_3) THEN
l_discount_amount_3
ELSE l_disc_amt_by_percent_3
END
END,
DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND( P_Pay_Curr_Invoice_Amount *
NVL(ap_terms_lines.due_percent, 0)/100, l_precision_pay_curr),
ROUND(( P_Pay_Curr_Invoice_Amount *
NVL(ap_terms_lines.due_percent, 0)/100)
/ l_min_acc_unit_pay_curr)*l_min_acc_unit_pay_curr)
, 0
, 'N'
, 'N'
, P_Batch_Id
, NVL(P_Payment_Method, 'CHECK')
,ai.external_bank_account_id --4393358
,ai.org_id
,ai.remittance_message1
,ai.remittance_message2
,ai.remittance_message3
--third party payments
,ai.remit_to_supplier_name
,ai.remit_to_supplier_id
,ai.remit_to_supplier_site
,ai.remit_to_supplier_site_id
,ai.relationship_id
INTO
T_INVOICE_ID(l_payment_schedule_index),
T_PAYMENT_NUM(l_payment_schedule_index),
T_DUE_DATE(l_payment_schedule_index),
T_DISCOUNT_DATE(l_payment_schedule_index),
T_SECOND_DISCOUNT_DATE(l_payment_schedule_index),
T_THIRD_DISCOUNT_DATE(l_payment_schedule_index),
T_LAST_UPDATE_DATE(l_payment_schedule_index),
T_LAST_UPDATED_BY(l_payment_schedule_index),
T_LAST_UPDATE_LOGIN(l_payment_schedule_index),
T_CREATION_DATE(l_payment_schedule_index),
T_CREATED_BY(l_payment_schedule_index),
T_PAYMENT_CROSS_RATE(l_payment_schedule_index),
T_GROSS_AMOUNT(l_payment_schedule_index),
T_INV_CURR_GROSS_AMOUNT(l_payment_schedule_index),
T_DISCOUNT_AMOUNT_AVAILABLE(l_payment_schedule_index),
T_SECOND_DISC_AMT_AVAILABLE(l_payment_schedule_index),
T_THIRD_DISC_AMT_AVAILABLE(l_payment_schedule_index),
T_AMOUNT_REMAINING(l_payment_schedule_index),
T_DISCOUNT_AMOUNT_REMAINING(l_payment_schedule_index),
T_HOLD_FLAG(l_payment_schedule_index),
T_PAYMENT_STATUS_FLAG(l_payment_schedule_index),
T_BATCH_ID(l_payment_schedule_index),
T_PAYMENT_METHOD_CODE(l_payment_schedule_index),
T_EXTERNAL_BANK_ACCOUNT_ID(l_payment_schedule_index),
T_ORG_ID(l_payment_schedule_index),
T_REMITTANCE_MESSAGE1(l_payment_schedule_index),
T_REMITTANCE_MESSAGE2(l_payment_schedule_index),
T_REMITTANCE_MESSAGE3(l_payment_schedule_index),
--Third Party Payments
T_REMIT_TO_SUPPLIER_NAME(l_payment_schedule_index),
T_REMIT_TO_SUPPLIER_ID(l_payment_schedule_index),
T_REMIT_TO_SUPPLIER_SITE(l_payment_schedule_index),
T_REMIT_TO_SUPPLIER_SITE_ID(l_payment_schedule_index),
T_RELATIONSHIP_ID(l_payment_schedule_index)
FROM ap_terms
, ap_terms_lines
, ap_invoices ai
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.term_id = P_Terms_Id
AND ap_terms_lines.sequence_num = l_sequence_num
AND ai.invoice_id = P_Invoice_Id;
INSERT INTO ap_payment_schedules (
INVOICE_ID,
PAYMENT_NUM,
DUE_DATE,
DISCOUNT_DATE,
SECOND_DISCOUNT_DATE,
THIRD_DISCOUNT_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
PAYMENT_CROSS_RATE,
GROSS_AMOUNT,
INV_CURR_GROSS_AMOUNT,
DISCOUNT_AMOUNT_AVAILABLE,
SECOND_DISC_AMT_AVAILABLE,
THIRD_DISC_AMT_AVAILABLE,
AMOUNT_REMAINING,
DISCOUNT_AMOUNT_REMAINING,
PAYMENT_PRIORITY,
HOLD_FLAG,
PAYMENT_STATUS_FLAG,
BATCH_ID,
PAYMENT_METHOD_CODE,
EXTERNAL_BANK_ACCOUNT_ID,
ORG_ID,
REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3,
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID
) VALUES (
T_INVOICE_ID(i),
T_PAYMENT_NUM(i),
T_DUE_DATE(i),
T_DISCOUNT_DATE(i),
T_SECOND_DISCOUNT_DATE(i),
T_THIRD_DISCOUNT_DATE(i),
T_LAST_UPDATE_DATE(i),
T_LAST_UPDATED_BY(i),
T_LAST_UPDATE_LOGIN(i),
T_CREATION_DATE(i),
T_CREATED_BY(i),
T_PAYMENT_CROSS_RATE(i),
T_GROSS_AMOUNT(i),
T_INV_CURR_GROSS_AMOUNT(i),
T_DISCOUNT_AMOUNT_AVAILABLE(i),
T_SECOND_DISC_AMT_AVAILABLE(i),
T_THIRD_DISC_AMT_AVAILABLE(i),
T_AMOUNT_REMAINING(i),
T_DISCOUNT_AMOUNT_REMAINING(i),
T_PAYMENT_PRIORITY(i),
T_HOLD_FLAG(i),
T_PAYMENT_STATUS_FLAG(i),
T_BATCH_ID(i),
T_PAYMENT_METHOD_CODE(i),
T_EXTERNAL_BANK_ACCOUNT_ID(i),
T_ORG_ID(i),
T_REMITTANCE_MESSAGE1(i),
T_REMITTANCE_MESSAGE2(i),
T_REMITTANCE_MESSAGE3(i),
--Third Party Payments
T_REMIT_TO_SUPPLIER_NAME(i),
T_REMIT_TO_SUPPLIER_ID(i),
T_REMIT_TO_SUPPLIER_SITE(i),
T_REMIT_TO_SUPPLIER_SITE_ID(i),
T_RELATIONSHIP_ID(i)
)
RETURNING payment_num
BULK COLLECT INTO l_dbi_key_value_list;
||', Last_Updated_By = ' ||P_Last_Updated_By
||', Created_By = ' ||P_Created_By
||', Payment_Priority = ' ||P_Payment_Priority
||', Batch_Id = ' ||P_Batch_Id
||', Terms_Date = ' ||P_Terms_Date
||', Invoice_Amount = ' ||P_Invoice_Amount
||', Amount_for_discount = ' ||P_Amount_For_Discount
||', Payment_Method = ' ||P_Payment_Method
||', Currency = ' ||P_invoice_currency
);
P_Last_Updated_By IN NUMBER,
P_Created_By IN NUMBER,
P_Payment_Priority IN NUMBER,
P_Batch_Id IN NUMBER,
P_Terms_Date IN DATE,
P_Invoice_Amount IN NUMBER,
P_Pay_Curr_Invoice_Amount IN NUMBER,
P_Payment_Cross_Rate IN NUMBER,
P_Amount_For_Discount IN NUMBER,
P_Payment_Method IN VARCHAR2,
P_Invoice_Currency IN VARCHAR2,
P_Payment_Currency IN VARCHAR2,
P_calling_sequence IN VARCHAR2)
IS
-- Following is how the input amounts are interpreted:
-- Amount is in ....
-- ====== ==========
-- P_Invoice_amount invoice currency
-- P_Pay_Curr_Invoice_Amount payment currency
-- P_Amount_For_Discount invoice currency
-- All amounts in AP_TERMS_LINES will be interpreted to be in the payment
-- currency
l_payment_cross_rate ap_payment_schedules.payment_cross_rate%TYPE;
SELECT 'Terms are percent type'
FROM ap_terms_lines
WHERE term_id = P_Terms_Id
AND sequence_num = 1
AND due_percent IS NOT NULL;
SELECT calendar, sequence_num
FROM ap_terms_lines
WHERE term_id = p_terms_id
ORDER BY sequence_num;
SELECT SIGN(ABS(P_Invoice_Amount)) ,
SIGN(due_amount) ,
due_amount ,
SIGN(ABS(l_remaining_amount) - ABS(due_amount)) ,
ABS(l_remaining_amount) - ABS(due_amount) ,
calendar -- change for calendar based payment terms
FROM ap_terms_lines
WHERE term_id = P_Terms_Id
AND sequence_num = l_sequence_num;
SELECT SUM(gross_amount),
SIGN(SUM(gross_amount))
FROM ap_payment_schedules
WHERE invoice_id = P_Invoice_Id;
SELECT SUM(inv_curr_gross_amount),
SIGN(SUM(inv_curr_gross_amount))
FROM ap_payment_schedules
WHERE invoice_id = P_Invoice_Id;
SELECT ai.external_bank_account_id, 'Y' --modified for the bug 7437597
FROM ap_payment_schedules aps,
ap_invoices ai,
iby_payee_assigned_bankacct_v ipab, /* External Bank Uptake */
po_vendors pv
WHERE ai.invoice_id = p_invoice_id
AND ai.vendor_id = pv.vendor_id
AND pv.party_id = ipab.party_id(+)
AND ai.invoice_id = aps.invoice_id
AND ipab.ext_bank_account_id = aps.external_bank_account_id;
SELECT ai.external_bank_account_id, 'Y' --modified for the bug 7437597
FROM ap_payment_schedules aps,
ap_invoices ai,
iby_payee_assigned_bankacct_v ipab, /* External Bank Uptake */
po_vendors pv
WHERE ai.invoice_id = p_invoice_id
AND ai.vendor_id = pv.vendor_id
AND pv.party_id = ipab.party_id(+)
AND (ai.vendor_site_id = ipab.supplier_site_id
OR (ipab.supplier_site_id IS NULL
AND ipab.org_id = ai.org_id))
AND ai.invoice_id = aps.invoice_id
AND ipab.ext_bank_account_id = aps.external_bank_account_id;
SELECT fc.minimum_accountable_unit, fc.precision
INTO l_min_acc_unit_pay_curr, l_precision_pay_curr
FROM fnd_currencies fc
WHERE fc.currency_code = P_Payment_Currency;
SELECT fc.minimum_accountable_unit, fc.precision
INTO l_min_acc_unit_inv_curr, l_precision_inv_curr
FROM fnd_currencies fc
WHERE fc.currency_code = P_Invoice_Currency;
SELECT payment_priority
INTO l_Payment_Priority
FROM ap_payment_schedules
WHERE Invoice_id = P_invoice_id
AND Payment_num=1;
variable. Later in the code we then insert in the payment schedule
record. We have to do this because below the code fix for this bug
we delete the orignal payment schedule and create a new one. */
OPEN c_orig_bank_acct_vendor;
SELECT payment_num
BULK COLLECT INTO l_dbi_key_value_list1
FROM AP_PAYMENT_SCHEDULES
WHERE invoice_id = P_invoice_id;
debug_info := 'Delete from ap_payment_schedules';
DELETE
FROM ap_payment_schedules
WHERE invoice_id = P_invoice_id;
SELECT invoice_type_lookup_code
INTO l_invoice_type
FROM ap_invoices
WHERE invoice_id = P_Invoice_Id;
SELECT DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent,0)/100 ,
l_precision_pay_curr),
ROUND(( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent,0)/100)
/ l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr)
, DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent_2,0)/100 ,
l_precision_pay_curr),
ROUND(( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent_2,0)/100)
/ l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr)
, DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent_3,0)/100 ,
l_precision_pay_curr),
ROUND(( l_ins_gross_amount *
DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
(l_amount_for_discount/
DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
P_Pay_Curr_Invoice_Amount))) *
NVL(ap_terms_lines.discount_percent_3,0)/100)
/ l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr),
discount_amount,
discount_amount_2,
discount_amount_3
INTO
l_disc_amt_by_percent, l_disc_amt_by_percent_2, l_disc_amt_by_percent_3,
l_discount_amount, l_discount_amount_2, l_discount_amount_3
FROM ap_terms
, ap_terms_lines
, ap_invoices ai
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.term_id = P_Terms_Id
AND ap_terms_lines.sequence_num = l_sequence_num
AND ai.Invoice_Id = P_Invoice_Id;
debug_info := 'Insert into ap_payment_schedules';
* then the created-by and last-updated-by would be same i.e the person who
* recreated
* payment schedules and not the one who created the invoice*/
INSERT INTO ap_payment_schedules (
invoice_id,
payment_num,
due_date,
discount_date,
second_discount_date,
third_discount_date,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
payment_cross_rate,
gross_amount,
discount_amount_available,
second_disc_amt_available,
third_disc_amt_available,
amount_remaining,
discount_amount_remaining,
payment_priority,
hold_flag,
payment_status_flag,
batch_id,
payment_method_code,
external_bank_account_id,
org_id,
remittance_message1,
remittance_message2,
remittance_message3
--third party payments
,remit_to_supplier_name
,remit_to_supplier_id
,remit_to_supplier_site
,remit_to_supplier_site_id
,relationship_id)
SELECT
P_Invoice_Id,
l_sequence_num,
l_due_date, -- change for payment terms
DECODE(ap_terms_lines.discount_days,
NULL, DECODE(ap_terms_lines.discount_day_of_month, NULL, NULL,
TO_DATE(TO_CHAR(LEAST(NVL(ap_terms_lines.discount_day_of_month,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS
(P_Terms_Date, NVL(ap_terms_lines.discount_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))), 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
P_Terms_Date + NVL(ap_terms_lines.discount_days,0)),
DECODE(ap_terms_lines.discount_days_2,
NULL,DECODE(ap_terms_lines.discount_day_of_month_2,NULL,NULL,
TO_DATE(TO_CHAR(LEAST
(NVL(ap_terms_lines.discount_day_of_month_2,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_2,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_2,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))), 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
P_Terms_Date + NVL(ap_terms_lines.discount_days_2,0)),
DECODE(ap_terms_lines.discount_days_3,
NULL, DECODE(ap_terms_lines.discount_day_of_month_3, NULL,NULL,
TO_DATE(TO_CHAR(LEAST
(NVL(ap_terms_lines.discount_day_of_month_3,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_3,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_3,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))), 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
P_Terms_Date + NVL(ap_terms_lines.discount_days_3,0)),
SYSDATE,
P_Last_Updated_By,
NULL,
SYSDATE,
P_Last_Updated_By,--bug4563272
l_payment_cross_rate,
DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND(l_ins_gross_amount, l_precision_pay_curr),
ROUND(l_ins_gross_amount /l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr) ,
--Bug 7357218 Quick Pay and Dispute Resolution Project
--Considering absolute amount and criteria for all three discounts
CASE
WHEN discount_criteria IS NULL OR discount_criteria = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount,0)) > abs(l_disc_amt_by_percent) THEN
l_discount_amount
ELSE l_disc_amt_by_percent
END
ELSE CASE WHEN abs(nvl(l_discount_amount,0)) < abs(l_disc_amt_by_percent) THEN
l_discount_amount
ELSE l_disc_amt_by_percent
END
END,
CASE
WHEN discount_criteria_2 IS NULL OR discount_criteria_2 = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount_2,0)) > abs(l_disc_amt_by_percent_2) THEN
l_discount_amount_2
ELSE l_disc_amt_by_percent_2
END
ELSE CASE WHEN abs(nvl(l_discount_amount_2,0)) < abs(l_disc_amt_by_percent_2) THEN
l_discount_amount_2
ELSE l_disc_amt_by_percent_2
END
END,
CASE
WHEN discount_criteria_3 IS NULL OR discount_criteria_3 = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount_3,0)) > abs(l_disc_amt_by_percent_3) THEN
l_discount_amount_3
ELSE l_disc_amt_by_percent_3
END
ELSE CASE WHEN abs(nvl(l_discount_amount_3,0)) < abs(l_disc_amt_by_percent_3) THEN
l_discount_amount_3
ELSE l_disc_amt_by_percent_3
END
END,
DECODE(l_min_acc_unit_pay_curr,
NULL, ROUND(l_ins_gross_amount, l_precision_pay_curr),
ROUND(l_ins_gross_amount /l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr),
0,
NVL(l_Payment_Priority,P_Payment_Priority),
'N',
'N',
P_Batch_Id,
NVL(P_Payment_Method, 'CHECK'),
/*commented for bug 5332569
DECODE(l_orig_ext_bank_exists, 'Y',
l_orig_ext_bank_acct_id, --1274099
ai.external_bank_account_id), --4393358
*/
-- Added for Bug 5332569 for inserting external_bank_account_id correctly
DECODE(l_orig_ext_bank_exists,
'Y', l_orig_ext_bank_acct_id,
DECODE(ai.source,
'RECURRING INVOICE', arp.external_bank_account_id,
ai.external_bank_account_id)),
ai.org_id,
ai.remittance_message1,
ai.remittance_message2,
ai.remittance_message3
--third party payments
,ai.remit_to_supplier_name
,ai.remit_to_supplier_id
,ai.remit_to_supplier_site
,ai.remit_to_supplier_site_id
,ai.relationship_id
FROM ap_terms,
ap_terms_lines,
ap_invoices ai,
ap_recurring_payments arp --bug 5332569
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.term_id = P_Terms_Id
AND ap_terms_lines.sequence_num = l_sequence_num
AND ai.Invoice_Id = P_Invoice_Id
AND ai.recurring_payment_id = arp.recurring_payment_id(+); --bug 5332569
SELECT DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100,
l_precision_pay_curr),
ROUND(( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100)
/ l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr)
, DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent_2,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100,
l_precision_pay_curr),
ROUND(( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent_2,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100)
/ l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr)
,DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent_3,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100,
l_precision_pay_curr),
ROUND(( l_amount_for_discount *
NVL(ap_terms_lines.discount_percent_3,0)/100 *
NVL(ap_terms_lines.due_percent, 0)/100)
/ l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr),
discount_amount,
discount_amount_2,
discount_amount_3
INTO
l_disc_amt_by_percent, l_disc_amt_by_percent_2, l_disc_amt_by_percent_3,
l_discount_amount, l_discount_amount_2, l_discount_amount_3
FROM ap_terms,
ap_terms_lines,
ap_invoices ai,
ap_recurring_payments arp
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.term_id = P_Terms_Id
AND ap_terms_lines.sequence_num = l_sequence_num
AND ai.Invoice_Id = P_Invoice_Id
AND ai.recurring_payment_id = arp.recurring_payment_id(+);
debug_info := 'Insert into ap_payment_schedules : term type is percent';
INSERT INTO ap_payment_schedules (
invoice_id,
payment_num,
due_date,
discount_date,
second_discount_date,
third_discount_date,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
payment_cross_rate,
gross_amount,
discount_amount_available,
second_disc_amt_available,
third_disc_amt_available,
amount_remaining,
discount_amount_remaining,
payment_priority,
hold_flag,
payment_status_flag,
batch_id,
payment_method_code,
external_bank_account_id,
org_id,
remittance_message1,
remittance_message2,
remittance_message3
--third party payments
,remit_to_supplier_name
,remit_to_supplier_id
,remit_to_supplier_site
,remit_to_supplier_site_id
,relationship_id)
SELECT
P_Invoice_Id,
l_sequence_num, -- ap_terms_lines.sequence_num
l_due_date, -- change for payment terms
DECODE(l_amount_for_discount, NULL, NULL,
DECODE(ap_terms_lines.discount_days, NULL,
DECODE(ap_terms_lines.discount_day_of_month,
NULL, NULL, TO_DATE(TO_CHAR(LEAST(NVL(
ap_terms_lines.discount_day_of_month,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS
(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
P_Terms_Date + NVL(ap_terms_lines.discount_days,0))),
DECODE(l_amount_for_discount, NULL, NULL,
DECODE(ap_terms_lines.discount_days_2,
NULL,DECODE(ap_terms_lines.discount_day_of_month_2,NULL,NULL,
TO_DATE(TO_CHAR(LEAST(
NVL(ap_terms_lines.discount_day_of_month_2,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_2,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_2,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
P_Terms_Date + NVL(ap_terms_lines.discount_days_2,0))),
DECODE(l_amount_for_discount, NULL, NULL,
DECODE(ap_terms_lines.discount_days_3,
NULL,DECODE(ap_terms_lines.discount_day_of_month_3,NULL,NULL,
TO_DATE(TO_CHAR(LEAST(
NVL(ap_terms_lines.discount_day_of_month_3,32),
TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_3,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
, 1, 0)))), 'DD')))) || '-' ||
TO_CHAR(ADD_MONTHS(P_Terms_Date,
NVL(ap_terms_lines.discount_months_forward_3,0) +
DECODE(ap_terms.due_cutoff_day, NULL, 0,
DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
P_Terms_Date + NVL(ap_terms_lines.discount_days_3,0))),
SYSDATE,
P_Last_Updated_By,
NULL,
SYSDATE,
P_Last_Updated_By,--bug4563272
l_payment_cross_rate,
DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND(P_Pay_Curr_Invoice_Amount *
NVL(ap_terms_lines.due_percent,0)/100,
l_precision_pay_curr),
ROUND((P_Pay_Curr_Invoice_Amount *
NVL(ap_terms_lines.due_percent,0)/100)
/ l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr),
--Bug 7357218 Quick Pay and Dispute Resolution Project
--Considering absolute amount and criteria for all three discounts
CASE
WHEN discount_criteria IS NULL OR discount_criteria = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount,0)) > abs(l_disc_amt_by_percent) THEN
l_discount_amount
ELSE l_disc_amt_by_percent
END
ELSE CASE WHEN abs(nvl(l_discount_amount,0)) < abs(l_disc_amt_by_percent) THEN
l_discount_amount
ELSE l_disc_amt_by_percent
END
END,
CASE
WHEN discount_criteria_2 IS NULL OR discount_criteria_2 = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount_2,0)) > abs(l_disc_amt_by_percent_2) THEN
l_discount_amount_2
ELSE l_disc_amt_by_percent_2
END
ELSE CASE WHEN abs(nvl(l_discount_amount_2,0)) < abs(l_disc_amt_by_percent_2) THEN
l_discount_amount_2
ELSE l_disc_amt_by_percent_2
END
END,
CASE
WHEN discount_criteria_3 IS NULL OR discount_criteria_3 = 'H' THEN
CASE WHEN abs(nvl(l_discount_amount_3,0)) > abs(l_disc_amt_by_percent_3) THEN
l_discount_amount_3
ELSE l_disc_amt_by_percent_3
END
ELSE CASE WHEN abs(nvl(l_discount_amount_3,0)) < abs(l_disc_amt_by_percent_3) THEN
l_discount_amount_3
ELSE l_disc_amt_by_percent_3
END
END,
DECODE(l_min_acc_unit_pay_curr,NULL,
ROUND( P_Pay_Curr_Invoice_Amount *
NVL(ap_terms_lines.due_percent, 0)/100,
l_precision_pay_curr),
ROUND(( P_Pay_Curr_Invoice_Amount *
NVL(ap_terms_lines.due_percent, 0)/100)
/ l_min_acc_unit_pay_curr)
* l_min_acc_unit_pay_curr),
0,
NVL(l_Payment_Priority,P_Payment_priority),
'N',
'N',
P_Batch_Id,
NVL(P_Payment_Method, 'CHECK'),
/*commented for bug 5332569
DECODE(l_orig_ext_bank_exists, 'Y',
l_orig_ext_bank_acct_id, --1274099
ai.external_bank_account_id), --4393358
*/
-- Added for Bug 5332569 for inserting external_bank_account_id correctly
DECODE(l_orig_ext_bank_exists,
'Y', l_orig_ext_bank_acct_id,
DECODE(ai.source,
'RECURRING INVOICE', arp.external_bank_account_id,
ai.external_bank_account_id)),
ai.org_id,
ai.remittance_message1,
ai.remittance_message2,
ai.remittance_message3
--third party payments
,ai.remit_to_supplier_name
,ai.remit_to_supplier_id
,ai.remit_to_supplier_site
,ai.remit_to_supplier_site_id
,ai.relationship_id
FROM ap_terms,
ap_terms_lines,
ap_invoices ai,
ap_recurring_payments arp
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms_lines.term_id = P_Terms_Id
AND ap_terms_lines.sequence_num = l_sequence_num
AND ai.invoice_id = P_Invoice_Id
AND ai.recurring_payment_id = arp.recurring_payment_id(+);
debug_info := 'Update ap_payment_schedules - set gross_amount';
UPDATE AP_PAYMENT_SCHEDULES
SET gross_amount = gross_amount +
TO_NUMBER(P_Pay_Curr_Invoice_Amount) -
TO_NUMBER(l_pay_sched_total),
amount_remaining = amount_remaining +
TO_NUMBER(P_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);
debug_info := 'Update ap_payment_schedules - set discount amounts';
UPDATE ap_payment_schedules
SET discount_amount_available = DECODE(discount_date, '', '',
discount_amount_available),
second_disc_amt_available = DECODE(second_discount_date, '', '',
second_disc_amt_available),
third_disc_amt_available = DECODE(third_discount_date, '', '',
third_disc_amt_available)
WHERE invoice_id = P_Invoice_Id
RETURNING payment_num
BULK COLLECT INTO l_dbi_key_value_list2;
debug_info := 'Update ap_payment_schedules - set inv_curr_gross_amount';
UPDATE ap_payment_schedules
SET inv_curr_gross_amount = ROUND(gross_amount/P_Payment_Cross_Rate,
l_precision_inv_curr)
WHERE invoice_id = P_Invoice_Id;
UPDATE ap_payment_schedules
SET inv_curr_gross_amount = (ROUND(gross_amount/P_Payment_Cross_Rate
/l_min_acc_unit_inv_curr)
* l_min_acc_unit_inv_curr)
WHERE invoice_id = P_Invoice_Id;
UPDATE ap_payment_schedules
SET inv_curr_gross_amount = (
SELECT DECODE(F.minimum_accountable_unit,NULL,
ROUND( gross_amount / P_Payment_Cross_Rate
, F.precision),
ROUND( gross_amount / P_Payment_Cross_Rate
/F.minimum_accountable_unit)
* F.minimum_accountable_unit)
FROM fnd_currencies F
WHERE F.currency_code = P_Invoice_Currency)
WHERE invoice_id = P_Invoice_Id;
debug_info := 'Update ap_payment_schedules - set inv_curr_gross_amount';
UPDATE AP_PAYMENT_SCHEDULES
SET inv_curr_gross_amount = inv_curr_gross_amount +
TO_NUMBER(P_Invoice_Amount) -
TO_NUMBER(l_inv_curr_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);
||', Last_Updated_By = ' ||P_Last_Updated_By
||', Created_By = ' ||P_Created_By
||', Payment_Priority = ' ||P_Payment_Priority
||', Batch_Id = ' ||P_Batch_Id
||', Terms_Date = ' ||P_Terms_Date
||', Invoice_Amount = ' ||P_Invoice_Amount
||', Amount_for_discount = ' ||P_Amount_For_Discount
||', Payment_Method = ' ||P_Payment_Method
||', Currency = ' ||P_invoice_currency
);