[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_cr_history_confirm( l_cr_rec,
p_confirm_gl_date,
p_confirm_date,
l_acctd_amount,
l_receipt_clearing_ccid);
update_cr_history_unconfirm( l_cr_rec,
p_confirm_gl_date,
p_confirm_date,
l_acctd_amount,
l_batch_id,
l_crh_id_rev);
SELECT cash_receipt_id
FROM AR_CASH_RECEIPT_HISTORY
WHERE current_record_flag = 'Y'
AND status='APPROVED'
AND batch_id = auto_batch_id;
UPDATE AR_CASH_RECEIPTS
SET confirmed_flag = 'Y'
WHERE cash_receipt_id = l_cash_receipt_rec.cash_receipt_id;
SELECT crh.status, cr.confirmed_flag
INTO l_status, l_confirmed_flag
FROM AR_CASH_RECEIPTS cr,
AR_CASH_RECEIPT_HISTORY crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND cr.cash_receipt_id = p_cr_id;
UPDATE AR_CASH_RECEIPTS
SET confirmed_flag = 'Y'
WHERE cash_receipt_id = p_cr_id;
| update associated invoice's payment schedule |
| update receivable_application |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_cr_rec - cash receipt to be confirmed |
| p_confirm_gl_date - Unconfirm GL date |
| p_confirm_date - Unconfirm Date |
| p_acctd_amount - accounted receipt amount |
| |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 18-AUG-95 OSTEINME created |
| 04-DEC-97 KLAWRANC Bug #590256. Modified call to |
| calc_acctd_amount. Now passes NULL for the |
| currency code parameter, therefore the acctd |
| amount will be calculated based on the |
| functional currency. |
| 11-JAN-98 JGDABIR Bug 768935. Initially set |
| acctd_amount_applied_from to NULL. |
| |
+===========================================================================*/
PROCEDURE do_confirm(
p_cr_rec IN ar_cash_receipts%ROWTYPE,
p_confirm_gl_date IN DATE,
p_confirm_date IN DATE,
p_acctd_amount IN NUMBER
) IS
-- Local variables:
l_dummy NUMBER;
SELECT invoice_currency_code,
exchange_rate
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id;
SELECT *
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND status = 'APP'
AND reversal_gl_date IS NULL;
l_inv_ps_rec.selected_for_receipt_batch_id := NULL;
arp_ps_util.update_invoice_related_columns(
'CASH',
NULL, -- No ps_id
l_ra_rec.amount_applied,
0, -- discounts taken
0, -- discounts earned
l_cnf_date,
l_cnf_gl_date,
l_acctd_app_amount_to,
l_dummy,
l_dummy,
l_line_applied,
l_tax_applied,
l_freight_applied,
l_charges_applied,
l_line_ediscounted,
l_tax_ediscounted,
l_freight_ediscounted,
l_charges_ediscounted,
l_line_uediscounted,
l_tax_uediscounted,
l_freight_uediscounted,
l_charges_uediscounted,
l_rule_set_id,
l_inv_ps_rec);
arp_standard.debug('-- invoice ps updated.');
UPDATE ar_receivable_applications
SET confirmed_flag = 'Y',
postable = 'Y',
gl_date = l_max_dates.max_ra_gl_date,
apply_date = l_max_dates.max_ra_apply_date,
acctd_amount_applied_to = l_acctd_app_amount_to,
acctd_amount_applied_from = l_acctd_app_amount_from,
line_applied = l_line_applied,
tax_applied = l_tax_applied,
freight_applied = l_freight_applied,
receivables_charges_applied = l_charges_applied,
line_ediscounted = l_line_ediscounted,
tax_ediscounted = l_tax_ediscounted,
freight_ediscounted = l_freight_ediscounted,
charges_ediscounted = l_charges_ediscounted,
line_uediscounted = l_line_uediscounted,
tax_uediscounted = l_tax_uediscounted,
freight_uediscounted = l_freight_uediscounted,
charges_uediscounted = l_charges_uediscounted,
rule_set_id = l_rule_set_id,
last_update_date = TRUNC(SYSDATE),
last_updated_by = FND_GLOBAL.user_id
WHERE
receivable_application_id = l_ra_rec.receivable_application_id;
arp_standard.debug('-- ra record updated.');
ar_mrc_engine3.confirm_ra_rec_update(
l_ra_rec.receivable_application_id);
arp_standard.debug('-- MRC ra record updated if necessary');
modify_update_ra_rec( p_cr_rec.cash_receipt_id,
p_cr_rec.amount,
p_acctd_amount,
p_confirm_gl_date,
p_confirm_date);
confirm_update_ps_rec( p_cr_rec,
l_max_dates.max_trx_date,
l_max_dates.max_gl_date);
| update associated invoice's payment schedule |
| update receivable_application |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_cr_rec - cash receipt to be confirmed |
| p_confirm_gl_date - Unconfirm GL date |
| p_confirm_date - Unconfirm Date |
| p_acctd_amount - accounted receipt amount |
| p_batch_id - batch id for receipt batch (needed to update inv. |
| payment schedule) |
| |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 28-AUG-95 OSTEINME created |
| |
+===========================================================================*/
PROCEDURE do_unconfirm(
p_cr_rec IN ar_cash_receipts%ROWTYPE,
p_confirm_gl_date IN DATE,
p_confirm_date IN DATE,
p_acctd_amount IN NUMBER,
p_batch_id
IN ar_payment_schedules.selected_for_receipt_batch_id%TYPE
) IS
-- Define cursor for applications:
CURSOR ar_receivable_applications_C (
p_cr_id ar_cash_receipts.cash_receipt_id%TYPE
) IS
SELECT *
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND status = 'APP'
AND reversal_gl_date IS NULL;
unconfirm_update_ps_rec( p_cr_rec,
p_confirm_gl_date,
p_confirm_date);
| update_cr_history_conf |
| |
| DESCRIPTION |
| Creates a new entry for the cash_receipt_history table. |
| It will have the updated receipt amount and the status |
| 'CONFIRMED'. Also creates an ar_distributions record for |
| the new history record. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_cr_rec - cash receipt for which the history entry |
| is to be created |
| p_confirm_gl_date - Unconfirm GL date |
| p_confirm_date - Unconfirm Date |
| p_acctd_amount - accounted cash receipt amount |
| p_receipt_clearing_ccid - code combination id |
| |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 18-AUG-95 OSTEINME created |
| |
+===========================================================================*/
PROCEDURE update_cr_history_confirm(
p_cr_rec IN ar_cash_receipts%ROWTYPE,
p_confirm_gl_date IN DATE,
p_confirm_date IN DATE,
p_acctd_amount IN NUMBER,
p_receipt_clearing_ccid IN
ar_receipt_method_accounts.receipt_clearing_ccid%TYPE
) IS
--
l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
arp_standard.debug('arp_confirmation.update_cr_history_confirm()+');
arp_cr_history_pkg.insert_p(l_crh_rec_new, l_crh_id_new);
arp_standard.debug('-- new crh record inserted. crh_id = ' ||
to_char(l_crh_id_new));
arp_cr_history_pkg.update_p(l_crh_rec_old);
arp_standard.debug('-- previous record updated');
l_dist_rec.last_update_date := SYSDATE;
l_dist_rec.last_updated_by := FND_GLOBAL.user_id;
arp_distributions_pkg.insert_p(l_dist_rec, l_dist_line_id);
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_line_id,
p_row_info => l_dist_rec);
arp_standard.debug('-- distribution record inserted. dist_line_id = '||
to_char(l_dist_line_id));
arp_standard.debug('update_cr_history_confirm()-');
arp_util.debug('EXCEPTION: update_cr_history_confirm()');
END; -- update_cr_history_confirm()
| confirm_update_ps_rec |
| |
| DESCRIPTION |
| This function updates the payment schedule record for a cash receipt |
| after all applications have been processed. It basically sets the |
| amount_due_remaining to zero, the amount_due_original to the receipt |
| amount, and the receipt_confirmed_flag to 'Y'. It also sets the |
| closed flag and the closed date and gl date. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_cr_rec receipt record |
| p_closed_date closed date |
| p_closed_gl_date closed gl date |
| |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 18-AUG-95 OSTEINME created |
| |
+===========================================================================*/
PROCEDURE confirm_update_ps_rec(
p_cr_rec ar_cash_receipts%ROWTYPE,
p_closed_date DATE,
p_closed_gl_date DATE
) IS
l_receipt_ps_rec ar_payment_schedules%ROWTYPE;
arp_standard.debug('arp_confirmation.confirm_update_ps_rec()+');
SELECT *
INTO l_receipt_ps_rec
FROM ar_payment_schedules
WHERE cash_receipt_id = p_cr_rec.cash_receipt_id;
arp_ps_util.update_receipt_related_columns(
NULL, -- no payment_schedule_id!
p_cr_rec.amount,
p_closed_date,
p_closed_gl_date,
l_dummy,
l_receipt_ps_rec);
arp_standard.debug('arp_confirmation.confirm_update_ps_rec()-');
arp_util.debug('EXCEPTION: arp_confirmation.confirm_update_ps_rec()');
END; -- confirm_update_ps_rec()
| modify_update_ra_rec |
| |
| DESCRIPTION |
| This function updates the original UNAPP record for the cash receipt |
| in ar_receivable_applications. |
| It also determines the payment schedule id for the receipt, which is |
| returned for future use. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_cr_id - cash receipt id |
| p_amount_applied - amount applied to invoices (= rec amount) |
| p_acctd_amount_applied - accounted amount applied to invoices |
| p_confirm_gl_date |
| p_confirm_date |
| |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 18-AUG-95 OSTEINME created |
| |
+===========================================================================*/
PROCEDURE modify_update_ra_rec(
p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_amount_applied IN NUMBER,
p_acctd_amount_applied IN NUMBER,
p_confirm_gl_date IN DATE,
p_confirm_date IN DATE
) IS
l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
SELECT app.receivable_application_id
INTO l_receivable_application_id
FROM ar_receivable_applications app
WHERE app.cash_receipt_id = p_cr_id
AND app.status = 'UNAPP'
AND app.confirmed_flag = 'N'
AND app.reversal_gl_date IS NULL
AND app.application_rule IN ('97.0', '40.0');
arp_standard.debug('arp_confirmation.modify_update_ra_rec()+');
UPDATE ar_receivable_applications
SET gl_date = p_confirm_gl_date,
apply_date = p_confirm_date,
amount_applied = p_amount_applied,
acctd_amount_applied_from = p_acctd_amount_applied,
confirmed_flag = 'Y',
postable = 'Y',
last_update_date = TRUNC(SYSDATE),
last_updated_by = FND_GLOBAL.user_id
WHERE receivable_application_id = l_receivable_application_id;
ar_mrc_engine3.confirm_ra_rec_update(
l_receivable_application_id);
arp_standard.debug('arp_confirmation.modify_update_ra_rec()+');
arp_util.debug('EXCEPTION: NO DATA FOUND, arp_confirmation.modify_update_ra_rec()');
arp_util.debug('EXCEPTION: arp_confirmation.modify_update_ra_rec()');
END; -- modify_update_ra_rec()
and inserting the UNAPP record and calling the accounting package
for every record that the cursor fetches. This is necessary in the
cases where there are multiple APP records for a given cash receipt, and
in such cases the previous insert was failing with a Unique constraint
voilation on the receivable application id.
With this modification the p_app_id passed as a parameter is effectively
unncessary, and instead using the rec_app_id of the APP record from the
cursor to PAIR the UNAPP record in ar_distributions to the APP record correctly
*/
CURSOR get_rec_records IS
SELECT app.receivable_application_id app_id,
-app.acctd_amount_applied_from acctd_amt_app_from,
-app.amount_applied amt_app,
app.application_type app_type,
app.apply_date app_date,
unapp.code_combination_id unapp_cc_id,
app.gl_date app_gl_date,
app.payment_schedule_id app_ps_id,
app.set_of_books_id app_sob,
app.cash_receipt_id app_cr_id,
app.comments app_comments,
app.days_late app_days_late,
app.org_id app_org_id
FROM ar_receivable_applications app,
ar_receivable_applications unapp
WHERE app.cash_receipt_id = p_cr_id
AND app.status||'' = 'APP'
AND app.reversal_gl_date IS NULL
AND app.cash_receipt_id = unapp.cash_receipt_id
AND unapp.application_rule = '97.0'
AND unapp.status||'' = 'UNAPP';
SELECT ar_receivable_applications_s.nextval
INTO l_unapp_id
FROM dual;
INSERT INTO ar_receivable_applications (
receivable_application_id,
acctd_amount_applied_from,
amount_applied,
application_rule,
application_type,
apply_date,
code_combination_id,
created_by,
creation_date,
display,
gl_date,
last_updated_by,
last_update_date,
payment_schedule_id,
set_of_books_id,
status,
acctd_amount_applied_to,
acctd_earned_discount_taken,
acctd_unearned_discount_taken,
applied_customer_trx_id,
applied_customer_trx_line_id,
applied_payment_schedule_id,
cash_receipt_id,
comments,
confirmed_flag,
customer_trx_id,
days_late,
earned_discount_taken,
freight_applied,
gl_posted_date,
last_update_login,
line_applied,
on_account_customer,
postable,
posting_control_id,
cash_receipt_history_id,
program_application_id,
program_id,
program_update_date,
receivables_charges_applied,
receivables_trx_id,
request_id,
tax_applied,
unearned_discount_taken,
unearned_discount_ccid,
earned_discount_ccid,
ussgl_transaction_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code_context,
reversal_gl_date,
org_id
)
VALUES (
l_unapp_id,
l_unapp_rec.acctd_amt_app_from,
l_unapp_rec.amt_app,
'40.4',
l_unapp_rec.app_type,
l_unapp_rec.app_date,
l_unapp_rec.unapp_cc_id,
FND_GLOBAL.user_id,
TRUNC(sysdate),
'N',
l_unapp_rec.app_gl_date,
FND_GLOBAL.user_id,
TRUNC(sysdate),
l_unapp_rec.app_ps_id,
l_unapp_rec.app_sob,
'UNAPP',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_unapp_rec.app_cr_id,
l_unapp_rec.app_comments,
'Y',
NULL,
l_unapp_rec.app_days_late,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Y',
-3,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_unapp_rec.app_org_id );
SELECT rma.receipt_clearing_ccid
INTO p_receipt_clearing_ccid
FROM ar_receipt_method_accounts rma
WHERE rma.remit_bank_acct_use_id = p_cr_rec.remit_bank_acct_use_id
AND rma.receipt_method_id = p_cr_rec.receipt_method_id;
| update_cr_history_unconfirm |
| |
| DESCRIPTION |
| Creates a new entry for the cash_receipt_history table. |
| It will have the updated receipt amount and the status |
| 'APPROVED'. Also creates an ar_distributions record for |
| the new history record. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| |
| p_cr_rec - cash receipt for which the history entry |
| is to be created |
| p_confirm_gl_date - Unconfirm GL date |
| p_confirm_date - Unconfirm Date |
| p_acctd_amount - accounted cash receipt amount |
| |
| OUT: |
| |
| p_batch_id - batch id of cash receipt (from crh table) |
| p_crh_id_rev - crh_id of record to be reversed |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 24-AUG-95 OSTEINME created |
| |
+===========================================================================*/
PROCEDURE update_cr_history_unconfirm(
p_cr_rec IN ar_cash_receipts%ROWTYPE,
p_confirm_gl_date IN DATE,
p_confirm_date IN DATE,
p_acctd_amount IN NUMBER,
p_batch_id OUT NOCOPY ar_cash_receipt_history.batch_id%TYPE,
p_crh_id_rev OUT NOCOPY
ar_cash_receipt_history.cash_receipt_history_id%TYPE
) IS
l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
arp_standard.debug('arp_confirmation.update_cr_history_unconfirm()+');
arp_cr_history_pkg.insert_p(l_crh_rec_new, l_crh_id_new);
arp_cr_history_pkg.update_p(l_crh_rec_old);
l_dist_rec.last_update_date := SYSDATE;
l_dist_rec.last_updated_by := FND_GLOBAL.user_id;
arp_distributions_pkg.insert_p(l_dist_rec, l_dist_line_id);
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_line_id,
p_row_info => l_dist_rec);
arp_standard.debug('arp_confirmation.update_cr_history_unconfirm()-');
arp_util.debug('EXCEPTION: arp_confirmation.update_cr_history_unconfirm()');
END; -- update_cr_history_unconfirm()
| added calls to ar_mrc_engine for updates to |
| ar_payment_schedules. |
+===========================================================================*/
PROCEDURE reverse_application_to_ps(
p_ra_id IN
ar_receivable_applications.receivable_application_id%TYPE,
p_confirm_gl_date IN DATE,
p_confirm_date IN DATE,
p_batch_id IN
ar_payment_schedules.selected_for_receipt_batch_id%TYPE
) IS
l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
UPDATE
ar_payment_schedules ps
SET (
status,
gl_date_closed,
actual_date_closed,
amount_applied,
amount_due_remaining,
acctd_amount_due_remaining,
amount_line_items_remaining,
tax_remaining,
freight_remaining,
receivables_charges_remaining,
selected_for_receipt_batch_id,
last_updated_by,
last_update_date,
last_update_login) = (
SELECT
decode(ps2.amount_due_remaining + ra.amount_applied,0,'CL','OP'),
decode(ps2.amount_due_remaining + ra.amount_applied,
0,
fnd_date.canonical_to_date(greatest(max(ra2.gl_date),
nvl(max(decode(adj2.status,
'A',adj2.gl_date,
nvl(ps2.gl_date,
ps2.trx_date))),
nvl(ps2.gl_date,ps2.trx_date)),
nvl(ps2.gl_date, ps2.trx_date))
),
''),
decode(ps2.amount_due_remaining + ra.amount_applied,
0,
fnd_date.canonical_to_date(greatest(max(ra2.apply_date),
nvl(max(decode(adj2.status,
'A',adj2.apply_date,
ps2.trx_date)),
ps2.trx_date),
ps2.trx_date)
),
''),
nvl(ps2.amount_applied,0) - ra.amount_applied,
ps2.amount_due_remaining + ra.amount_applied,
ps2.acctd_amount_due_remaining + nvl(ra.acctd_amount_applied_to,0),
nvl(ps2.amount_line_items_remaining,0) + nvl(ra.line_applied,0),
nvl(ps2.tax_remaining,0) + nvl(ra.tax_applied,0),
nvl(ps2.freight_remaining,0) + nvl(ra.freight_applied,0),
nvl(ps2.receivables_charges_remaining,0) +
nvl(ra.receivables_charges_applied,0),
p_batch_id,
FND_GLOBAL.user_id,
trunc(sysdate),
FND_GLOBAL.user_id
FROM
ar_receivable_applications ra,
ar_payment_schedules ps2,
ar_adjustments adj2,
ar_receivable_applications ra2
WHERE
ra.receivable_application_id = p_ra_id
AND ra.applied_payment_schedule_id = ps2.payment_schedule_id
AND ps2.payment_schedule_id =ps.payment_schedule_id
AND ps2.payment_schedule_id = adj2.payment_schedule_id(+)
AND ps2.payment_schedule_id = ra2.applied_payment_schedule_id
AND nvl(ra2.confirmed_flag,'Y')= 'Y'
GROUP BY
ps2.payment_schedule_id,
ra2.applied_payment_schedule_id,
adj2.payment_schedule_id,
ps2.amount_due_remaining,
ra.amount_applied,
ps2.gl_date,
ps2.trx_date,
ps2.amount_applied,
ps2.acctd_amount_due_remaining,
ra.acctd_amount_applied_to,
ps2.amount_line_items_remaining,
ra.line_applied,
ps2.tax_remaining,
ra.tax_applied,
ps2.freight_remaining,
ra.freight_applied,
ps2.receivables_charges_remaining,
ra.receivables_charges_applied)
WHERE ps.payment_schedule_id in ( SELECT
ra3.applied_payment_schedule_id
FROM
ar_receivable_applications ra3
WHERE
ra3.receivable_application_id =
p_ra_id)
RETURNING ps.payment_schedule_id
BULK COLLECT INTO l_ar_ps_key_value_list;
p_event_mode => 'UPDATE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'BATCH',
p_key_value_list => l_ar_ps_key_value_list);
SELECT app.receivable_application_id old_app_id
FROM ar_receivable_applications app
WHERE app.cash_receipt_id = p_cr_rec.cash_receipt_id
AND app.reversal_gl_date IS NULL
ORDER BY decode(app.status,
'APP' ,1,
'ACC' ,2,
'UNID' ,3,
'UNAPP',4); --This ordering is required for pairing UNAPP with APP record
SELECT ar_receivable_applications_s.nextval
INTO l_new_app_id
FROM dual;
INSERT INTO ar_receivable_applications
(receivable_application_id,
acctd_amount_applied_from,
amount_applied,
application_rule,
application_type,
apply_date,
code_combination_id,
created_by,
creation_date,
display,
gl_date,
last_updated_by,
last_update_date,
payment_schedule_id,
set_of_books_id,
status,
acctd_amount_applied_to,
acctd_earned_discount_taken,
acctd_unearned_discount_taken,
applied_customer_trx_id,
applied_customer_trx_line_id,
applied_payment_schedule_id,
cash_receipt_id,
comments,
confirmed_flag,
customer_trx_id,
days_late,
earned_discount_taken,
freight_applied,
gl_posted_date,
last_update_login,
line_applied,
on_account_customer,
postable,
posting_control_id,
cash_receipt_history_id,
program_application_id,
program_id,
program_update_date,
receivables_charges_applied,
receivables_trx_id,
request_id,
tax_applied,
unearned_discount_taken,
unearned_discount_ccid,
earned_discount_ccid,
ussgl_transaction_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code_context,
reversal_gl_date,
org_id
)
SELECT l_new_app_id,
-acctd_amount_applied_from,
-amount_applied,
'40.2',
application_type,
p_confirm_gl_date,
code_combination_id,
FND_GLOBAL.user_id,
TRUNC(SYSDATE),
'N',
p_confirm_gl_date,
FND_GLOBAL.user_id,
TRUNC(SYSDATE),
payment_schedule_id,
set_of_books_id,
status,
-acctd_amount_applied_to,
-acctd_earned_discount_taken,
-acctd_unearned_discount_taken,
applied_customer_trx_id,
applied_customer_trx_line_id,
applied_payment_schedule_id,
cash_receipt_id,
comments,
confirmed_flag,
customer_trx_id,
days_late,
-earned_discount_taken,
-freight_applied,
NULL,
last_update_login,
-line_applied,
on_account_customer,
postable,
-3,
NULL,
program_application_id,
program_id,
program_update_date,
-receivables_charges_applied,
receivables_trx_id,
request_id,
-tax_applied,
-unearned_discount_taken,
unearned_discount_ccid,
earned_discount_ccid,
ussgl_transaction_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code_context,
p_confirm_gl_date,
org_id
FROM ar_receivable_applications
WHERE receivable_application_id = l_app_rec.old_app_id;
SELECT
receivable_application_id,
ar_receivable_applications_s.nextval,
acctd_amount_applied_from,
amount_applied,
DECODE(status,
'UNAPP', '40.0',
'40.3'),
application_type,
p_confirm_gl_date,
code_combination_id,
FND_GLOBAL.user_id,
TRUNC(SYSDATE),
display,
p_confirm_gl_date,
FND_GLOBAL.user_id,
TRUNC(SYSDATE),
payment_schedule_id,
set_of_books_id,
status,
acctd_amount_applied_to,
DECODE(status,
'UNAPP', NULL,
acctd_earned_discount_taken),
DECODE(status,
'UNAPP', NULL,
acctd_unearned_discount_taken),
DECODE(status,
'UNAPP', NULL,
applied_customer_trx_id),
DECODE(status,
'UNAPP', NULL,
applied_customer_trx_line_id),
DECODE(status,
'UNAPP', NULL,
applied_payment_schedule_id),
cash_receipt_id,
comments,
'N',
customer_trx_id,
days_late,
DECODE(status,
'UNAPP', NULL,
earned_discount_taken),
DECODE(status,
'UNAPP', NULL,
freight_applied),
NULL,
last_update_login,
DECODE(status,
'UNAPP', NULL,
line_applied),
on_account_customer,
'N',
-3,
NULL,
program_application_id,
program_id,
program_update_date,
DECODE(status,
'UNAPP', NULL,
receivables_charges_applied),
receivables_trx_id,
request_id,
DECODE(status,
'UNAPP', NULL,
tax_applied),
DECODE(status,
'UNAPP', NULL,
unearned_discount_taken),
unearned_discount_ccid,
earned_discount_ccid,
ussgl_transaction_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code_context,
NULL,
org_id
BULK COLLECT INTO
n_new_con_data.l_old_rec_app_id,
n_new_con_data.l_new_rec_app_id,
n_new_con_data.l_acctd_amount_applied_from,
n_new_con_data.l_amount_applied,
n_new_con_data.l_application_rule,
n_new_con_data.l_application_type,
n_new_con_data.l_apply_date,
n_new_con_data.l_code_combination_id,
n_new_con_data.l_created_by,
n_new_con_data.l_creation_date,
n_new_con_data.l_display,
n_new_con_data.l_gl_date,
n_new_con_data.l_last_updated_by,
n_new_con_data.l_last_update_date,
n_new_con_data.l_payment_schedule_id,
n_new_con_data.l_set_of_books_id,
n_new_con_data.l_status,
n_new_con_data.l_acctd_amount_applied_to,
n_new_con_data.l_acctd_earned_discount_tkn,
n_new_con_data.l_acctd_unearned_discount_tkn,
n_new_con_data.l_applied_customer_trx_id,
n_new_con_data.l_applied_customer_trx_line_id,
n_new_con_data.l_applied_payment_schedule_id,
n_new_con_data.l_cash_receipt_id,
n_new_con_data.l_comments,
n_new_con_data.l_confirmed_flag,
n_new_con_data.l_customer_trx_id,
n_new_con_data.l_days_late,
n_new_con_data.l_earned_discount_taken,
n_new_con_data.l_freight_applied,
n_new_con_data.l_gl_posted_date,
n_new_con_data.l_last_update_login,
n_new_con_data.l_line_applied,
n_new_con_data.l_on_account_customer,
n_new_con_data.l_postable,
n_new_con_data.l_posting_control_id,
n_new_con_data.l_cash_receipt_history_id,
n_new_con_data.l_program_application_id,
n_new_con_data.l_program_id,
n_new_con_data.l_program_update_date,
n_new_con_data.l_receivables_charges_applied,
n_new_con_data.l_receivables_trx_id,
n_new_con_data.l_request_id,
n_new_con_data.l_tax_applied,
n_new_con_data.l_unearned_discount_taken,
n_new_con_data.l_unearned_discount_ccid,
n_new_con_data.l_earned_discount_ccid,
n_new_con_data.l_ussgl_transaction_code,
n_new_con_data.l_attribute_category,
n_new_con_data.l_attribute1,
n_new_con_data.l_attribute2,
n_new_con_data.l_attribute3,
n_new_con_data.l_attribute4,
n_new_con_data.l_attribute5,
n_new_con_data.l_attribute6,
n_new_con_data.l_attribute7,
n_new_con_data.l_attribute8,
n_new_con_data.l_attribute9,
n_new_con_data.l_attribute10,
n_new_con_data.l_attribute11,
n_new_con_data.l_attribute12,
n_new_con_data.l_attribute13,
n_new_con_data.l_attribute14,
n_new_con_data.l_attribute15,
n_new_con_data.l_ussgl_transaction_code_cntxt,
n_new_con_data.l_reversal_gl_date,
n_new_con_data.l_org_id
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_rec.cash_receipt_id
AND ( status = 'APP'
OR
( status = 'UNAPP'
AND application_rule in ('97.0', '40.0')
)
)
AND reversal_gl_date IS NULL;
INSERT INTO ar_receivable_applications
(receivable_application_id,
acctd_amount_applied_from,
amount_applied,
application_rule,
application_type,
apply_date,
code_combination_id,
created_by,
creation_date,
display,
gl_date,
last_updated_by,
last_update_date,
payment_schedule_id,
set_of_books_id,
status,
acctd_amount_applied_to,
acctd_earned_discount_taken,
acctd_unearned_discount_taken,
applied_customer_trx_id,
applied_customer_trx_line_id,
applied_payment_schedule_id,
cash_receipt_id,
comments,
confirmed_flag,
customer_trx_id,
days_late,
earned_discount_taken,
freight_applied,
gl_posted_date,
last_update_login,
line_applied,
on_account_customer,
postable,
posting_control_id,
cash_receipt_history_id,
program_application_id,
program_id,
program_update_date,
receivables_charges_applied,
receivables_trx_id,
request_id,
tax_applied,
unearned_discount_taken,
unearned_discount_ccid,
earned_discount_ccid,
ussgl_transaction_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code_context,
reversal_gl_date,
org_id
)
VALUES (
n_new_con_data.l_new_rec_app_id(i),
n_new_con_data.l_acctd_amount_applied_from(i),
n_new_con_data.l_amount_applied(i),
n_new_con_data.l_application_rule(i),
n_new_con_data.l_application_type(i),
n_new_con_data.l_apply_date(i),
n_new_con_data.l_code_combination_id(i),
n_new_con_data.l_created_by(i),
n_new_con_data.l_creation_date(i),
n_new_con_data.l_display(i),
n_new_con_data.l_gl_date(i),
n_new_con_data.l_last_updated_by(i),
n_new_con_data.l_last_update_date(i),
n_new_con_data.l_payment_schedule_id(i),
n_new_con_data.l_set_of_books_id(i),
n_new_con_data.l_status(i),
n_new_con_data.l_acctd_amount_applied_to(i),
n_new_con_data.l_acctd_earned_discount_tkn(i),
n_new_con_data.l_acctd_unearned_discount_tkn(i),
n_new_con_data.l_applied_customer_trx_id(i),
n_new_con_data.l_applied_customer_trx_line_id(i),
n_new_con_data.l_applied_payment_schedule_id(i),
n_new_con_data.l_cash_receipt_id(i),
n_new_con_data.l_comments(i),
n_new_con_data.l_confirmed_flag(i),
n_new_con_data.l_customer_trx_id(i),
n_new_con_data.l_days_late(i),
n_new_con_data.l_earned_discount_taken(i),
n_new_con_data.l_freight_applied(i),
n_new_con_data.l_gl_posted_date(i),
n_new_con_data.l_last_update_login(i),
n_new_con_data.l_line_applied(i),
n_new_con_data.l_on_account_customer(i),
n_new_con_data.l_postable(i),
n_new_con_data.l_posting_control_id(i),
n_new_con_data.l_cash_receipt_history_id(i),
n_new_con_data.l_program_application_id(i),
n_new_con_data.l_program_id(i),
n_new_con_data.l_program_update_date(i),
n_new_con_data.l_receivables_charges_applied(i),
n_new_con_data.l_receivables_trx_id(i),
n_new_con_data.l_request_id(i),
n_new_con_data.l_tax_applied(i),
n_new_con_data.l_unearned_discount_taken(i),
n_new_con_data.l_unearned_discount_ccid(i),
n_new_con_data.l_earned_discount_ccid(i),
n_new_con_data.l_ussgl_transaction_code(i),
n_new_con_data.l_attribute_category(i),
n_new_con_data.l_attribute1(i),
n_new_con_data.l_attribute2(i),
n_new_con_data.l_attribute3(i),
n_new_con_data.l_attribute4(i),
n_new_con_data.l_attribute5(i),
n_new_con_data.l_attribute6(i),
n_new_con_data.l_attribute7(i),
n_new_con_data.l_attribute8(i),
n_new_con_data.l_attribute9(i),
n_new_con_data.l_attribute10(i),
n_new_con_data.l_attribute11(i),
n_new_con_data.l_attribute12(i),
n_new_con_data.l_attribute13(i),
n_new_con_data.l_attribute14(i),
n_new_con_data.l_attribute15(i),
n_new_con_data.l_ussgl_transaction_code_cntxt(i),
n_new_con_data.l_reversal_gl_date(i),
n_new_con_data.l_org_id(i)
);
UPDATE ar_receivable_applications
SET reversal_gl_date = p_confirm_gl_date,
display = 'N',
last_update_date = TRUNC(SYSDATE),
last_updated_by = FND_GLOBAL.user_id
WHERE cash_receipt_id = p_cr_rec.cash_receipt_id
AND nvl(confirmed_flag,'Y') = 'Y'
AND reversal_gl_date IS NULL;
| unconfirm_update_ps_rec |
| |
| DESCRIPTION |
| This function updates the payment schedule record for a cash receipt |
| after all applications have been processed. It basically sets the |
| amount_due_remaining, the amount_due_original, and the |
| receipt_confirmed_flag to 'N'. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_cr_rec receipt record |
| p_closed_date closed date |
| p_closed_gl_date closed gl date |
| |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 01-SEP-95 OSTEINME created |
| |
+===========================================================================*/
PROCEDURE unconfirm_update_ps_rec(
p_cr_rec ar_cash_receipts%ROWTYPE,
p_closed_date DATE,
p_closed_gl_date DATE
) IS
l_receipt_ps_rec ar_payment_schedules%ROWTYPE;
arp_standard.debug('arp_confirmation.unconfirm_update_ps_rec()+');
SELECT *
INTO l_receipt_ps_rec
FROM ar_payment_schedules
WHERE cash_receipt_id = p_cr_rec.cash_receipt_id;
arp_ps_util.update_receipt_related_columns(
NULL, -- no payment_schedule_id!
-p_cr_rec.amount,
p_closed_date,
p_closed_gl_date,
l_dummy,
l_receipt_ps_rec);
arp_standard.debug('arp_confirmation.unconfirm_update_ps_rec()-');
arp_util.debug('EXCEPTION: NO DATA FOUND, arp_confirmation.unconfirm_update_ps_rec()');
arp_util.debug('EXCEPTION: arp_confirmation.unconfirm_update_ps_rec()');
END; -- unconfirm_update_ps_rec()
SELECT allow_overapplication_flag,
natural_application_only_flag,
creation_sign
INTO p_ao_flag,
p_nao_flag,
p_creation_sign
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_cust_trx_type_id;
| This function updates the MaxDatesType datastructure passed in. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_max_dates MaxDatesType datastructure to be updated |
| p_gl_date GL date |
| p_apply_date Apply date |
| p_confirm_date Confirm Date |
| p_confirm_gl_date Confirm GL Date |
| |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 30-AUG-95 OSTEINME created |
| |
+===========================================================================*/
PROCEDURE handle_max_dates(
p_max_dates IN OUT NOCOPY MaxDatesType,
p_gl_date IN DATE,
p_apply_date IN DATE,
p_confirm_date IN DATE,
p_confirm_gl_date IN DATE
) IS
BEGIN
p_max_dates.max_gl_date := GREATEST(p_max_dates.max_gl_date,
p_gl_date);