[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_last_update_date OUT NOCOPY DATE);
| Step 2: | Call ap_pay_update_payment_schedule: | PAY |
| | | |
+----------+----------------------------------------------------+-------------+
| Step 3: | Call ap_pay_update_ap_invoices: | PAY |
| | | |
+----------+----------------------------------------------------+-------------+
| Step 4: | Call ap_pay_insert_invoice_payments: | PAY/REV |
| | | |
+----------+----------------------------------------------------+-------------+
*========================================================================*/
PROCEDURE ap_pay_invoice(
P_invoice_id IN NUMBER,
P_check_id IN NUMBER,
P_payment_num IN NUMBER,
P_invoice_payment_id IN NUMBER,
P_old_invoice_payment_id IN NUMBER Default NULL,
P_period_name IN VARCHAR2,
P_invoice_type IN VARCHAR2 Default NULL,
P_accounting_date IN DATE,
P_amount IN NUMBER,
P_discount_taken IN NUMBER,
P_discount_lost IN NUMBER Default NULL,
P_invoice_base_amount IN NUMBER Default NULL,
P_payment_base_amount IN NUMBER Default NULL,
P_accrual_posted_flag IN VARCHAR2,
P_cash_posted_flag IN VARCHAR2,
P_posted_flag IN VARCHAR2,
P_set_of_books_id IN NUMBER,
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER Default NULL,
P_currency_code IN VARCHAR2 Default NULL,
P_base_currency_code IN VARCHAR2 Default NULL,
P_exchange_rate IN NUMBER Default NULL,
P_exchange_rate_type IN VARCHAR2 Default NULL,
P_exchange_date IN DATE Default NULL,
P_ce_bank_acct_use_id IN NUMBER Default NULL,
P_bank_account_num IN VARCHAR2 Default NULL,
P_bank_account_type IN VARCHAR2 Default NULL,
P_bank_num IN VARCHAR2 Default NULL,
P_future_pay_posted_flag IN VARCHAR2 Default NULL,
P_exclusive_payment_flag IN VARCHAR2 Default NULL,
P_accts_pay_ccid IN NUMBER Default NULL,
P_gain_ccid IN NUMBER Default NULL,
P_loss_ccid IN NUMBER Default NULL,
P_future_pay_ccid IN NUMBER Default NULL,
P_asset_ccid IN NUMBER Default NULL,
P_payment_dists_flag IN VARCHAR2 Default NULL,
P_payment_mode IN VARCHAR2 Default NULL,
P_replace_flag IN VARCHAR2 Default NULL,
P_attribute1 IN VARCHAR2 Default NULL,
P_attribute2 IN VARCHAR2 Default NULL,
P_attribute3 IN VARCHAR2 Default NULL,
P_attribute4 IN VARCHAR2 Default NULL,
P_attribute5 IN VARCHAR2 Default NULL,
P_attribute6 IN VARCHAR2 Default NULL,
P_attribute7 IN VARCHAR2 Default NULL,
P_attribute8 IN VARCHAR2 Default NULL,
P_attribute9 IN VARCHAR2 Default NULL,
P_attribute10 IN VARCHAR2 Default NULL,
P_attribute11 IN VARCHAR2 Default NULL,
P_attribute12 IN VARCHAR2 Default NULL,
P_attribute13 IN VARCHAR2 Default NULL,
P_attribute14 IN VARCHAR2 Default NULL,
P_attribute15 IN VARCHAR2 Default NULL,
P_attribute_category IN VARCHAR2 Default NULL,
P_global_attribute1 IN VARCHAR2 Default NULL,
P_global_attribute2 IN VARCHAR2 Default NULL,
P_global_attribute3 IN VARCHAR2 Default NULL,
P_global_attribute4 IN VARCHAR2 Default NULL,
P_global_attribute5 IN VARCHAR2 Default NULL,
P_global_attribute6 IN VARCHAR2 Default NULL,
P_global_attribute7 IN VARCHAR2 Default NULL,
P_global_attribute8 IN VARCHAR2 Default NULL,
P_global_attribute9 IN VARCHAR2 Default NULL,
P_global_attribute10 IN VARCHAR2 Default NULL,
P_global_attribute11 IN VARCHAR2 Default NULL,
P_global_attribute12 IN VARCHAR2 Default NULL,
P_global_attribute13 IN VARCHAR2 Default NULL,
P_global_attribute14 IN VARCHAR2 Default NULL,
P_global_attribute15 IN VARCHAR2 Default NULL,
P_global_attribute16 IN VARCHAR2 Default NULL,
P_global_attribute17 IN VARCHAR2 Default NULL,
P_global_attribute18 IN VARCHAR2 Default NULL,
P_global_attribute19 IN VARCHAR2 Default NULL,
P_global_attribute20 IN VARCHAR2 Default NULL,
P_global_attribute_category IN VARCHAR2 Default NULL,
P_calling_sequence IN VARCHAR2 Default NULL,
P_accounting_event_id IN NUMBER Default NULL,
P_org_id IN NUMBER Default NULL)
IS
current_calling_sequence VARCHAR2(2000);
C_last_update_date DATE;
C_last_update_date);
* -- Step 2 : case for all : Update AP_PAYMENT_SCHEDULES
* Call ap_pay_update_payment_schedules :
*
*--------------------------------------------------------------------------*/
ap_pay_invoice_pkg.ap_pay_update_payment_schedule(
P_invoice_id,
P_payment_num,
P_check_id,
P_amount,
P_discount_taken,
P_payment_dists_flag,
P_payment_mode,
P_replace_flag,
P_last_updated_by,
C_last_update_date,
Current_calling_sequence);
* -- Step 3 : case for all: Update AP_INVOICES
* Call ap_pay_update_ap_invoices :
*
*--------------------------------------------------------------------------*/
ap_pay_invoice_pkg.ap_pay_update_ap_invoices(
P_invoice_id,
P_check_id,
P_amount,
P_discount_taken,
P_payment_dists_flag,
P_payment_mode,
P_replace_flag,
C_last_update_date,
P_last_updated_by,
Current_calling_sequence);
* -- Step 4 : case for all : Insert AP_INVOICE_PAYMENTS
* Call ap_pay_insert_invoice_payments :
*
*--------------------------------------------------------------------------*/
ap_pay_invoice_pkg.ap_pay_insert_invoice_payments(
P_invoice_id,
P_check_id,
P_payment_num,
P_invoice_payment_id,
P_old_invoice_payment_id,
C_period_name,
C_accounting_date,
P_amount,
P_discount_taken,
C_discount_lost,
C_invoice_base_amount,
C_payment_base_amount,
P_accrual_posted_flag,
P_cash_posted_flag,
P_posted_flag,
P_set_of_books_id,
P_last_updated_by,
P_last_update_login,
C_last_update_date,
P_currency_code,
P_base_currency_code,
P_exchange_rate,
P_exchange_rate_type,
P_exchange_date,
P_ce_bank_acct_use_id,
P_bank_account_num,
P_bank_account_type,
P_bank_num,
P_future_pay_posted_flag,
P_exclusive_payment_flag,
P_accts_pay_ccid,
C_gain_ccid,
C_loss_ccid,
P_future_pay_ccid,
P_asset_ccid,
P_payment_dists_flag,
P_payment_mode,
P_replace_flag,
P_attribute1,
P_attribute2,
P_attribute3,
P_attribute4,
P_attribute5,
P_attribute6,
P_attribute7,
P_attribute8,
P_attribute9,
P_attribute10,
P_attribute11,
P_attribute12,
P_attribute13,
P_attribute14,
P_attribute15,
P_attribute_category,
P_global_attribute1,
P_global_attribute2,
P_global_attribute3,
P_global_attribute4,
P_global_attribute5,
P_global_attribute6,
P_global_attribute7,
P_global_attribute8,
P_global_attribute9,
P_global_attribute10,
P_global_attribute11,
P_global_attribute12,
P_global_attribute13,
P_global_attribute14,
P_global_attribute15,
P_global_attribute16,
P_global_attribute17,
P_global_attribute18,
P_global_attribute19,
P_global_attribute20,
P_global_attribute_category,
Current_calling_sequence,
P_accounting_event_id,
P_org_id);
P_last_update_date OUT NOCOPY DATE) IS
debug_info VARCHAR2(100);
SELECT G.period_name
INTO P_period_name
FROM gl_period_statuses G, ap_system_parameters P
WHERE G.application_id = 200
AND G.set_of_books_id = P.set_of_books_id
AND DECODE(P_accounting_date, '',
sysdate, P_accounting_date) between G.start_date and G.end_date
AND G.closing_status in ('O', 'F')
AND NVL(G.adjustment_period_flag, 'N') = 'N';
-- Bug 825450. Added select statement so that if the current period is
-- not 'open' or 'future-entry' then select the next such available period.
EXCEPTION WHEN NO_DATA_FOUND THEN
BEGIN
SELECT G.start_date, G.period_name
INTO l_gl_date, P_period_name
FROM gl_period_statuses G, ap_system_parameters P
WHERE G.application_id = 200
AND G.set_of_books_id = P.set_of_books_id
AND G.start_date = (SELECT min(G1.start_date)
FROM gl_period_statuses G1
WHERE G1.application_id = 200
AND G1.set_of_books_id = P.set_of_books_id
AND G1.start_date > DECODE(P_accounting_date, '',
sysdate, P_accounting_date)
AND G1.closing_status in ('O', 'F')
AND NVL(G1.adjustment_period_flag, 'N') = 'N'
)
AND G.closing_status in ('O', 'F')
AND NVL(G.adjustment_period_flag, 'N') = 'N';
P_last_update_date := sysdate;
SELECT PS.payment_cross_rate,
AI.payment_cross_rate_date,
AI.payment_cross_rate_type,
AI.exchange_rate,
AI.exchange_date,
AI.exchange_rate_type,
AI.invoice_currency_code
INTO PS_payment_cross_rate,
AI_payment_cross_rate_date,
AI_payment_cross_rate_type,
AI_exchange_rate,
AI_exchange_date,
AI_exchange_rate_type,
c_inv_currency_code
FROM ap_payment_schedules PS, ap_invoices AI
WHERE PS.invoice_id = P_invoice_id
AND PS.payment_num = P_payment_num
AND AI.invoice_id = P_invoice_id;
SELECT gain_code_combination_id, loss_code_combination_id
INTO P_gain_ccid,
P_loss_ccid
FROM ce_gl_accounts_ccid CGAC
WHERE CGAC.bank_acct_use_id = P_ce_bank_acct_use_id;
SELECT DECODE(invoice_base_amount ,'','',
0-NVL(invoice_base_amount,0)),
DECODE(payment_base_amount ,'','',
0-NVL(payment_base_amount,0))
INTO P_invoice_base_amount, P_payment_base_amount
FROM ap_invoice_payments
WHERE invoice_payment_id = P_old_invoice_payment_id;
SELECT greatest (nvl(PS.discount_amount_available,0),
nvl(PS.second_disc_amt_available,0),
nvl(PS.third_disc_amt_available,0)),
ps.gross_amount
INTO PS_disc_amt_available,
PS_gross_amount
FROM ap_payment_schedules ps
WHERE invoice_id = P_invoice_id
AND payment_num = P_payment_num;
* update amount_paid, discount_amount_taken and payment_status_flag for
* ap_invoices Update AP_INVOICES
==========================================================================*/
PROCEDURE ap_pay_update_ap_invoices(
P_invoice_id IN NUMBER,
P_check_id IN NUMBER,
P_amount IN NUMBER,
P_discount_taken IN NUMBER,
P_payment_dists_flag IN VARCHAR2,
P_payment_mode IN VARCHAR2,
P_replace_flag IN VARCHAR2,
P_last_update_date IN DATE,
P_last_updated_by IN NUMBER,
P_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
current_calling_sequence := 'ap_pay_update_ap_invoices<-'||P_calling_sequence;
debug_info := 'Update ap_invoices (pay)';
UPDATE ap_invoices
SET amount_paid = NVL(amount_paid, 0) + NVL(P_amount, 0),
discount_amount_taken = NVL(discount_amount_taken, 0) +
NVL(P_discount_taken, 0),
payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id ),
last_update_date = P_last_update_date,
last_updated_by = P_last_updated_by
WHERE invoice_id = P_invoice_id;
debug_info := 'Update ap_invoices (reissue)';
UPDATE ap_invoices
SET last_update_date = P_last_update_date,
last_updated_by = P_last_updated_by
WHERE invoice_id = P_invoice_id;
debug_info := 'Update ap_invoices (reverse)';
UPDATE ap_invoices AI
SET (amount_paid
, discount_amount_taken
, payment_status_flag
, last_update_date
, last_updated_by)
= (SELECT AI.amount_paid - SUM(AIP.amount)
, NVL(AI.discount_amount_taken,0) -
SUM(NVL(AIP.discount_taken,0))
, AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id )
, P_last_update_date
, P_last_updated_by
FROM ap_invoice_payments AIP
WHERE AIP.invoice_id = P_invoice_id
AND AIP.check_id = P_check_id
GROUP BY AI.invoice_id
, AI.amount_paid
, AI.discount_amount_taken
, AI.invoice_amount )
WHERE AI.invoice_id = P_invoice_id;
debug_info := 'Update ap_invoices (replace)';
UPDATE ap_invoices
SET last_update_date = P_last_update_date,
last_updated_by = P_last_updated_by
WHERE invoice_id = P_invoice_id;
||', Last_updated_by = '||TO_CHAR(P_last_updated_by)
||', Last_update_date = '||TO_CHAR(P_last_update_date)
||', payment_dists_flag = '||P_payment_dists_flag
||', payment_mode = '||P_payment_mode
||', replace_flag = '||P_replace_flag);
END ap_pay_update_ap_invoices;
* This function: Update AP_INVOICE_PAYMENTS
* Inserts a new invoice payment line
*========================================================================*/
PROCEDURE ap_pay_insert_invoice_payments(
P_invoice_id IN NUMBER,
P_check_id IN NUMBER,
P_payment_num IN NUMBER,
P_invoice_payment_id IN NUMBER,
P_old_invoice_payment_id IN NUMBER,
P_period_name IN VARCHAR2,
P_accounting_date IN DATE,
P_amount IN NUMBER,
P_discount_taken IN NUMBER,
P_discount_lost IN NUMBER,
P_invoice_base_amount IN NUMBER,
P_payment_base_amount IN NUMBER,
P_accrual_posted_flag IN VARCHAR2,
P_cash_posted_flag IN VARCHAR2,
P_posted_flag IN VARCHAR2,
P_set_of_books_id IN NUMBER,
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_last_update_date IN DATE,
P_currency_code IN VARCHAR2,
P_base_currency_code IN VARCHAR2,
P_exchange_rate IN NUMBER,
P_exchange_rate_type IN VARCHAR2,
P_exchange_date IN DATE,
P_ce_bank_acct_use_id IN NUMBER,
P_bank_account_num IN VARCHAR2,
P_bank_account_type IN VARCHAR2,
P_bank_num IN VARCHAR2,
P_future_pay_posted_flag IN VARCHAR2,
P_exclusive_payment_flag IN VARCHAR2,
P_accts_pay_ccid IN NUMBER,
P_gain_ccid IN NUMBER,
P_loss_ccid IN NUMBER,
P_future_pay_ccid IN NUMBER,
P_asset_ccid IN NUMBER,
P_payment_dists_flag IN VARCHAR2,
P_payment_mode IN VARCHAR2,
P_replace_flag IN VARCHAR2,
P_attribute1 IN VARCHAR2,
P_attribute2 IN VARCHAR2,
P_attribute3 IN VARCHAR2,
P_attribute4 IN VARCHAR2,
P_attribute5 IN VARCHAR2,
P_attribute6 IN VARCHAR2,
P_attribute7 IN VARCHAR2,
P_attribute8 IN VARCHAR2,
P_attribute9 IN VARCHAR2,
P_attribute10 IN VARCHAR2,
P_attribute11 IN VARCHAR2,
P_attribute12 IN VARCHAR2,
P_attribute13 IN VARCHAR2,
P_attribute14 IN VARCHAR2,
P_attribute15 IN VARCHAR2,
P_attribute_category IN VARCHAR2,
P_global_attribute1 IN VARCHAR2 Default NULL,
P_global_attribute2 IN VARCHAR2 Default NULL,
P_global_attribute3 IN VARCHAR2 Default NULL,
P_global_attribute4 IN VARCHAR2 Default NULL,
P_global_attribute5 IN VARCHAR2 Default NULL,
P_global_attribute6 IN VARCHAR2 Default NULL,
P_global_attribute7 IN VARCHAR2 Default NULL,
P_global_attribute8 IN VARCHAR2 Default NULL,
P_global_attribute9 IN VARCHAR2 Default NULL,
P_global_attribute10 IN VARCHAR2 Default NULL,
P_global_attribute11 IN VARCHAR2 Default NULL,
P_global_attribute12 IN VARCHAR2 Default NULL,
P_global_attribute13 IN VARCHAR2 Default NULL,
P_global_attribute14 IN VARCHAR2 Default NULL,
P_global_attribute15 IN VARCHAR2 Default NULL,
P_global_attribute16 IN VARCHAR2 Default NULL,
P_global_attribute17 IN VARCHAR2 Default NULL,
P_global_attribute18 IN VARCHAR2 Default NULL,
P_global_attribute19 IN VARCHAR2 Default NULL,
P_global_attribute20 IN VARCHAR2 Default NULL,
P_global_attribute_category IN VARCHAR2 Default NULL,
P_calling_sequence IN VARCHAR2,
P_accounting_event_id IN NUMBER Default NULL,
P_org_id IN NUMBER Default NULL) IS
current_calling_sequence VARCHAR2(2000);
current_calling_sequence := 'AP_PAY_INVOICE_PKG.ap_pay_insert_invoice_payments<-'||P_calling_sequence;
debug_info := 'Insert ap_invoice_payments';
AP_AIP_TABLE_HANDLER_PKG.Insert_Row(
P_invoice_id,
P_check_id,
P_payment_num,
P_invoice_payment_id,
P_old_invoice_payment_id,
P_period_name,
P_accounting_date,
P_amount,
P_discount_taken,
P_discount_lost,
P_invoice_base_amount,
P_payment_base_amount,
P_accrual_posted_flag,
P_cash_posted_flag,
P_posted_flag,
P_set_of_books_id,
P_last_updated_by,
P_last_update_login,
P_last_update_date,
P_currency_code,
P_base_currency_code,
P_exchange_rate,
P_exchange_rate_type,
P_exchange_date,
P_ce_bank_acct_use_id,
P_bank_account_num,
P_bank_account_type,
P_bank_num,
P_future_pay_posted_flag,
P_exclusive_payment_flag,
P_accts_pay_ccid,
P_gain_ccid,
P_loss_ccid,
P_future_pay_ccid,
P_asset_ccid,
P_payment_dists_flag,
P_payment_mode,
P_replace_flag,
P_attribute1,
P_attribute2,
P_attribute3,
P_attribute4,
P_attribute5,
P_attribute6,
P_attribute7,
P_attribute8,
P_attribute9,
P_attribute10,
P_attribute11,
P_attribute12,
P_attribute13,
P_attribute14,
P_attribute15,
P_attribute_category,
P_global_attribute1,
P_global_attribute2,
P_global_attribute3,
P_global_attribute4,
P_global_attribute5,
P_global_attribute6,
P_global_attribute7,
P_global_attribute8,
P_global_attribute9,
P_global_attribute10,
P_global_attribute11,
P_global_attribute12,
P_global_attribute13,
P_global_attribute14,
P_global_attribute15,
P_global_attribute16,
P_global_attribute17,
P_global_attribute18,
P_global_attribute19,
P_global_attribute20,
P_global_attribute_category,
Current_calling_sequence,
P_accounting_event_id,
P_org_id);
||', Last_update_by = '||TO_CHAR(P_last_updated_by)
||', Last_update_date = '||TO_CHAR(P_last_update_date)
||', Last_update_login = '||TO_CHAR(P_last_update_login)
||', payment_dists_flag = '||P_payment_dists_flag
||', payment_mode = '||P_payment_mode
||', replace_flag = '||P_replace_flag);
end ap_pay_insert_invoice_payments;
Update AP_PAYMENT_SCHEDULE
*========================================================================*/
PROCEDURE ap_pay_update_payment_schedule(
P_invoice_id IN NUMBER,
P_payment_num IN NUMBER,
P_check_id IN NUMBER,
P_amount IN NUMBER,
P_discount_taken IN NUMBER,
P_payment_dists_flag IN VARCHAR2,
P_payment_mode IN VARCHAR2,
P_replace_flag IN VARCHAR2,
P_last_updated_by IN NUMBER,
P_last_update_date IN DATE,
P_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
current_calling_sequence := 'ap_pay_update_payment_schedule<-'||P_calling_sequence;
debug_info := 'Update ap_payment_schedules (pay)';
UPDATE ap_payment_schedules
SET amount_remaining = amount_remaining - P_amount -
NVL(P_discount_taken, 0),
discount_amount_remaining = 0,
payment_status_flag = DECODE(amount_remaining -
P_amount -
NVL(P_discount_taken, 0),
0, 'Y',
amount_remaining, payment_status_flag,
'P'),
last_update_date = P_last_update_date,
last_updated_by = P_last_updated_by
WHERE invoice_id = P_invoice_id
AND payment_num = P_payment_num;
debug_info := 'Update ap_payment_schedules (reissue)';
UPDATE ap_payment_schedules
SET last_update_date = P_last_update_date,
last_updated_by = P_last_updated_by
WHERE invoice_id = P_invoice_id
AND payment_num = P_payment_num;
debug_info := 'Update ap_payment_schedules (reverse, non-prepayment)';
SELECT DECODE( NVL( SUM(AIP.amount), 0 ), 0, 'N', 'Y' )
INTO l_another_pmt
FROM ap_invoice_payments AIP
WHERE AIP.invoice_id = p_invoice_id
AND AIP.payment_num = p_payment_num
AND AIP.check_id <> p_check_id ;
SELECT DECODE( NVL( SUM(AID.amount), 0 ), 0, 'N', 'Y' )
INTO l_prepay_applied
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = p_invoice_id
AND AID.line_type_lookup_code = 'PREPAY';
SELECT DECODE(APS.gross_amount, APS.amount_remaining
+ SUM(AIP.amount)
+ SUM(NVL(AIP.discount_taken,0)), 'N', 'P')
INTO l_pmt_status_flag
FROM ap_invoice_payments AIP
, ap_payment_schedules APS
WHERE AIP.invoice_id = P_invoice_id
AND AIP.payment_num = P_payment_num
AND AIP.check_id = P_check_id
AND AIP.invoice_id = APS.invoice_id
AND AIP.payment_num = APS.payment_num
GROUP BY AIP.invoice_id
, AIP.payment_num
, APS.gross_amount
, APS.amount_remaining;
UPDATE ap_payment_schedules APS
SET (amount_remaining
, discount_amount_remaining
, payment_status_flag
, last_update_date
, last_updated_by)
= (SELECT nvl(APS.amount_remaining,0) + SUM(AIP.amount)
+ SUM(NVL(AIP.discount_taken,0))
, 0
, l_pmt_status_flag
/* Bug 8300099 : Commented the DECODE being used earlier
DECODE(APS.gross_amount, APS.amount_remaining -- Bug 8300099 Commented the fix for 2182168
+ SUM(AIP.amount)
+ SUM(NVL(AIP.discount_taken,0)), 'N', 'P')/*DECODE(AI.amount_paid,SUM(AIP.amount),'N','P')*/
-- 2182168 modified the decode statement to compare amount_paid to amount cancelled
, P_last_update_date
, P_last_updated_by
FROM ap_invoice_payments AIP,ap_invoices AI --bug2182168 added ap_invoices AI
WHERE AIP.invoice_id = P_invoice_id
AND AIP.payment_num = P_payment_num
AND AIP.check_id = P_check_id
AND AI.invoice_id=P_invoice_id --bug2182168 added condition
GROUP BY AIP.invoice_id
, AIP.payment_num
, APS.gross_amount
, APS.amount_remaining
, APS.discount_amount_remaining
, AI.amount_paid --bug2182168 added amount_paid in group by clause
)
WHERE (invoice_id, payment_num) IN
(SELECT P_invoice_id
, P_payment_num
FROM ap_invoices AI
WHERE AI.invoice_id = P_invoice_id
AND AI.invoice_type_lookup_code <> 'PREPAYMENT');
debug_info := 'Update ap_payment_schedules (reverse, prepayment)';
UPDATE ap_payment_schedules APS
SET (amount_remaining
, payment_status_flag
, last_update_date
, last_updated_by)
/* Added for bug 10372009
APS.amount_remaining should be added to the amount which is being reversed
= (SELECT SUM(AIP.amount) + SUM(NVL(AIP.discount_taken, 0)) */
= (SELECT nvl(APS.amount_remaining,0) + SUM(AIP.amount) +
SUM(NVL(AIP.discount_taken, 0))
/* Replaced N with payment status based on amount in AIP for bug 10372009
, 'N' */
, l_pmt_status_flag
, P_last_update_date
, P_last_updated_by
FROM ap_invoice_payments AIP
WHERE AIP.invoice_id = P_invoice_id
AND AIP.check_id = P_check_id
AND AIP.payment_num = APS.payment_num -- Bug 7184181
GROUP BY AIP.invoice_id)
WHERE payment_num = P_payment_num -- Bug 4701565
AND (invoice_id) IN
(SELECT P_invoice_id
FROM ap_invoices AI
WHERE AI.invoice_id = P_invoice_id
AND AI.invoice_type_lookup_code = 'PREPAYMENT');
debug_info := 'Update ap_payment_schedules (replace)';
UPDATE ap_payment_schedules
SET last_update_date = P_last_update_date,
last_updated_by = P_last_updated_by
WHERE invoice_id = P_invoice_id
AND payment_num = P_payment_num;
||', Last_update_by = '||TO_CHAR(P_last_updated_by)
||', Last_update_date = '||TO_CHAR(P_last_update_date)
||', payment_dists_flag = '||P_payment_dists_flag
||', payment_mode = '||P_payment_mode
||', replace_flag = '||P_replace_flag);
END ap_pay_update_payment_schedule;
FUNCTION ap_pay_update_check_amount(x_check_id IN NUMBER)
RETURN NUMBER
IS
check_amount NUMBER;
SELECT sum(amount)
INTO check_amount
FROM ap_invoice_payments aip
WHERE aip.check_id = x_check_id;
UPDATE ap_checks ac
set amount = check_amount
where ac.check_id = x_check_id;
END ap_pay_update_check_amount;
It is used to update the ap_invoices table and ap_payment_schedules
table when reversing an invoice payment. It is called from the
payment workbench.
******************************************************************/
PROCEDURE ap_inv_pay_update_invoices (
P_org_invoice_pay_id NUMBER,
P_invoice_id NUMBER,
P_payment_line_number NUMBER,
P_last_update_date DATE,
P_last_updated_by NUMBER,
P_calling_sequence VARCHAR2) IS
current_calling_sequence VARCHAR2(2000);
current_calling_sequence := 'ap_inv_pay_update_invoices<-'||P_calling_sequence;
SELECT amount, discount_taken
INTO p_amount, p_discount
FROM ap_invoice_payments
WHERE invoice_payment_id = p_org_invoice_pay_id;
UPDATE ap_payment_schedules
SET amount_remaining = amount_remaining + P_amount +
nvl(P_discount,0),
discount_amount_remaining = 0,
payment_status_flag = DECODE(amount_remaining +
P_amount +
NVL(P_discount, 0),
0, 'Y',
gross_amount, 'N',
'P'),
last_update_date = P_last_update_date,
last_updated_by = P_last_updated_by
WHERE invoice_id = P_invoice_id
AND payment_num = P_payment_line_number;
UPDATE ap_invoices
SET amount_paid = nvl(amount_paid,0) - P_amount ,
discount_amount_taken =
nvl(discount_amount_taken,0) - nvl(P_discount,0) ,
payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id ),
last_update_date = P_last_update_date,
last_updated_by = P_last_updated_by
WHERE invoice_id = P_invoice_id;
||', Last_update_by = '||TO_CHAR(P_last_updated_by)
||', Last_update_date = '||TO_CHAR(P_last_update_date));
END ap_inv_pay_update_invoices;