[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_SELECT_FLAG IN BOOLEAN,
P_MODULE_NAME IN VARCHAR2,
P_MODULE_VERSION IN VARCHAR2 );
PROCEDURE reversal_insert_oppos_ra_recs (
p_ra_rec IN OUT NOCOPY ar_receivable_applications%ROWTYPE
, p_app_rec_trx_type IN VARCHAR
, p_reversal_gl_date IN DATE
, p_reversal_date IN DATE
, p_module_name IN VARCHAR2
, p_called_from IN VARCHAR2 DEFAULT NULL
, p_rec_app_id OUT NOCOPY NUMBER); /* jrautiai BR implementation */
PROCEDURE reversal_update_old_ra_rec(
p_reversal_gl_date DATE
, p_ra_rec IN OUT NOCOPY ar_receivable_applications%ROWTYPE
);
PROCEDURE reversal_update_ps_recs (
p_ra_rec IN ar_receivable_applications%ROWTYPE
, p_app_rec_trx_type IN VARCHAR
, p_reversal_gl_date IN DATE
, p_reversal_date IN DATE);
p_select_flag IN BOOLEAN );
PROCEDURE insert_trx_note(
p_customer_trx_id IN NUMBER
, p_receipt_number IN VARCHAR2
, p_claim_number IN VARCHAR2
, p_flag IN VARCHAR2);
| balance due on invoice to update the
| the field on the form.
| 12-JUL-2000 Jani Rautiainen Added parameter p_called_from to procedure |
| reverse. This is needed in the logic to |
| be able to create the transaction history |
| record for an Bills Receivable transaction |
| when its payment schedule is opened / closed |
| 06/12/2001 S.Nambiar Bug 1830483 - Activity application should not
| show error when there is no CB attached while
| doing the unapplication
+===========================================================================*/
PROCEDURE reverse (
p_ra_id IN NUMBER
, p_reversal_gl_date IN DATE
, p_reversal_date IN DATE
, p_module_name IN VARCHAR2
, p_module_version IN VARCHAR2
, p_bal_due_remaining OUT NOCOPY NUMBER
, p_called_from IN VARCHAR2) IS /* jrautiai BR implementation */
l_ra_rec ar_receivable_applications%ROWTYPE;
reversal_update_ps_recs ( l_ra_rec,
'AR_APP',
p_reversal_gl_date ,
p_reversal_date);
* Transaction payment schedule was not updated for activity application */
IF (l_payment_schedule_id IS NOT NULL and l_ra_rec.status <> 'ACTIVITY') THEN
SELECT amount_due_remaining
INTO l_bal_due_rem
from ar_payment_schedules
where payment_schedule_id = l_payment_schedule_id;
reversal_update_old_ra_rec( p_reversal_gl_date, l_ra_rec );
Update the UNAPP record which is paired with the APP record being reversed.
The reversal_gl_date needs to be populated */
update ar_receivable_applications
set reversal_gl_date = p_reversal_gl_date,
include_in_accumulation = 'N' -- bug 6924942 --> setting accumulation flag to 'N'
where receivable_application_id = (select source_id
from ar_distributions
where source_table = 'RA'
and source_type = 'UNAPP'
and source_id_secondary = l_ra_rec.receivable_application_id);
So if the above update can not update the paired UNAPP record, the following update
which uses the maximum matching criteria should be run to update the paired UNAPP record */
IF SQL%NOTFOUND THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_debug.debug( 'trans_to_receipt_rate = '||to_char(l_ra_rec.trans_to_receipt_rate));
update ar_receivable_applications
set reversal_gl_date = p_reversal_gl_date
where receivable_application_id = (select /*+ INDEX (AR_RECEIVABLE_APPLICATIONS_ALL AR_RECEIVABLE_APPLICATIONS_N1) */
max(receivable_application_id)
from ar_receivable_applications
where cash_receipt_id = l_ra_rec.cash_receipt_id
and status ='UNAPP'
and posting_control_id = l_ra_rec.posting_control_id
and nvl(gl_posted_date,sysdate) = nvl(l_ra_rec.gl_posted_date, sysdate)
------- bug fix 3000242+ ----------
and cash_receipt_history_id = l_ra_rec.cash_receipt_history_id
and (request_id = l_ra_rec.request_id
or
(request_id IS NULL and l_ra_rec.request_id IS NULL))
------- bug fix 3000242- ----------
and amount_applied_from = -l_ra_rec.amount_applied_from
and gl_date = l_ra_rec.gl_date
and apply_date = l_ra_rec.apply_date
and reversal_gl_date is NULL);
update ar_receivable_applications
set reversal_gl_date = p_reversal_gl_date
where receivable_application_id = (select /*+ INDEX (AR_RECEIVABLE_APPLICATIONS_ALL AR_RECEIVABLE_APPLICATIONS_N1) */
max(receivable_application_id)
from ar_receivable_applications
where cash_receipt_id = l_ra_rec.cash_receipt_id
and status ='UNAPP'
and posting_control_id = l_ra_rec.posting_control_id
and nvl(gl_posted_date,sysdate) = nvl(l_ra_rec.gl_posted_date, sysdate)
------- bug fix 3000242+ ----------
and cash_receipt_history_id = l_ra_rec.cash_receipt_history_id
and (request_id = l_ra_rec.request_id
or
(request_id IS NULL and l_ra_rec.request_id IS NULL))
------- bug fix 3000242- ----------
and amount_applied = -l_ra_rec.amount_applied
and gl_date = l_ra_rec.gl_date
and apply_date = l_ra_rec.apply_date
and reversal_gl_date is NULL);
reversal_insert_oppos_ra_recs(
l_ra_rec
, 'AR_APP'
, p_reversal_gl_date
, p_reversal_date
, p_module_name
, l_called_from --Bug7194951
, l_rec_app_id);
SELECT
crh.batch_id
INTO
ln_batch_id
FROM
ar_cash_receipt_history crh
, ar_receivable_applications ra
WHERE
ra.receivable_application_id = p_ra_id
AND ra.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y';
arp_rw_batches_check_pkg.update_batch_status(ln_batch_id,p_called_from); --Bug7194951
2. Update applied transaction row in AR_PAYMENT_SCHEDULES |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_app_pkg.fetch_p - Fetch a record from
| AR_RECEIVABLE_APPLICATIONS table
| |
| ARGUMENTS : IN: |
| pn_ra_id Id of application to be reversed
| pd_reversal_gl_date Reversal GL date
| pd_reversal_date Reversal Date
| pc_module_name Name of module that called this proc.
| pc_module_version Version of the module that called
| this procedure
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY
| 5/30/1996 Harri Kaukovuo Created
| |
+===========================================================================*/
PROCEDURE reverse_cm_app(
pn_ra_id IN NUMBER
, pn_applied_ps_id IN NUMBER
, pd_reversal_gl_date IN DATE
, pd_reversal_date IN DATE
, pc_module_name IN VARCHAR2
, pc_module_version IN VARCHAR2
, p_called_from IN VARCHAR2 ) IS
lr_ra_rec ar_receivable_applications%ROWTYPE;
reversal_update_ps_recs(
lr_ra_rec
, l_trx_type
, pd_reversal_gl_date
, pd_reversal_date );
reversal_update_old_ra_rec( pd_reversal_gl_date, lr_ra_rec );
reversal_insert_oppos_ra_recs(
lr_ra_rec
, 'AR_CM'
, pd_reversal_gl_date
, pd_reversal_date
, 'ARREREVB'
, null
, l_rec_app_id);
update_selected_transaction
DESCRIPTION
This procedure is used to update the applied amount
of an application. Usually receivable_applications row
is not updated directly, but this is the case when
we handle confirmed receipt applications that are not
actually officially applied.
SCOPE - PUBLIC
EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
ARGUMENTS : IN:
pn_ra_id Id of application to be reversed
this procedure
pn_amount_applied Amount which is going to be
updated on application row.
pc_invoice_currency_code Invoice currency code
pn_invoice_exchange_rate Invoice exchange rate
pc_receipt_currency_code Receipt currency code
pn_receipt_exchange_rate Receipt exchange rate
pb_fetch_from_db_flag Indicator which tells procedure
to get invoice and receipt info
from database instead of using
passed parameters.
pc_module_name Name of the calling module
pc_module_version Version number of the module
attribute_category
attribute1
attribute2
attribute3
attribute4
attribute5
attribute6
attribute7
attribute8
attribute9
attribute10
attribute11
attribute12
attribute13
attribute14
attribute15
global_attribute_category
global_attribute1
global_attribute2
global_attribute3
global_attribute4
global_attribute5
global_attribute6
global_attribute7
global_attribute8
global_attribute9
global_attribute10
global_attribute11
global_attribute12
global_attribute13
global_attribute14
global_attribute15
global_attribute16
global_attribute17
global_attribute18
global_attribute19
global_attribute20
OUT:
RETURNS : NONE
NOTES
I made this procedure to be ready for Rel 11 cross currency
feature (i.e included both invoice and receipt currency
information).
We don't use module and version information yet (not necessary
but might be used for customizations).
MODIFICATION HISTORY
6/18/1996 Harri Kaukovuo Created
9/02/1997 Tasman Tang Added global_attribute_category and
global_attribute[1-20] for global
descriptive flexfield
Fixed bug 546626: check if functional
currency is the same as invoice and receipt
currency and do the corresponding parameter
checking because rate can be null if receipt
currency equals to functional currency
10/22/1997 Karen Murphy Bug fix #567872. Added code to update the
UNAPP row when chaning the amount applied
of one of the APP rows.
12/04/1997 Karen Murphy Bug fix #567872. Added the setting of the
acctd_amount_applied_from for the UNAPP row.
12/05/1997 Karen Murphy Bug 546626. Call to ARPCURR.functional_amount
needs to pass the functional currency not
the invoice and receipt currencies. This
causes incorrect rounding.
09/10/2002 Debbie Jancis Modified for MRC trigger replacement. Added
call to ar_mrc_engine3 to process receivable
applications.
+===========================================================================*/
PROCEDURE update_selected_transaction(
pn_ra_id IN NUMBER,
pn_amount_applied IN NUMBER,
pc_invoice_currency_code IN VARCHAR2,
pn_invoice_exchange_rate IN NUMBER,
pc_receipt_currency_code IN VARCHAR2,
pn_receipt_exchange_rate IN NUMBER,
pc_module_name IN VARCHAR2,
pc_module_version IN VARCHAR2,
p_attribute_category 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_global_attribute_category IN VARCHAR2,
p_global_attribute1 IN VARCHAR2,
p_global_attribute2 IN VARCHAR2,
p_global_attribute3 IN VARCHAR2,
p_global_attribute4 IN VARCHAR2,
p_global_attribute5 IN VARCHAR2,
p_global_attribute6 IN VARCHAR2,
p_global_attribute7 IN VARCHAR2,
p_global_attribute8 IN VARCHAR2,
p_global_attribute9 IN VARCHAR2,
p_global_attribute10 IN VARCHAR2,
p_global_attribute11 IN VARCHAR2,
p_global_attribute12 IN VARCHAR2,
p_global_attribute13 IN VARCHAR2,
p_global_attribute14 IN VARCHAR2,
p_global_attribute15 IN VARCHAR2,
p_global_attribute16 IN VARCHAR2,
p_global_attribute17 IN VARCHAR2,
p_global_attribute18 IN VARCHAR2,
p_global_attribute19 IN VARCHAR2,
p_global_attribute20 IN VARCHAR2 ) IS
lr_ra_rec ar_receivable_applications%ROWTYPE;
arp_debug.debug( 'arp_process_application.update_selected_transaction()+');
'ARP_PROCESS_APPLICATION.UPDATE_SELECTED_TRANSACTION'
, 'PN_RA_ID'
, 'NULL');
'ARP_PROCESS_APPLICATION.UPDATE_SELECTED_TRANSACTION'
, 'pn_amount_applied'
, 'NULL');
'ARP_PROCESS_APPLICATION.UPDATE_SELECTED_TRANSACTION'
, 'pc_invoice_currency_code'
, 'NULL');
'ARP_PROCESS_APPLICATION.UPDATE_SELECTED_TRANSACTION'
, 'pn_invoice_exchange_rate'
, 'NULL');
'ARP_PROCESS_APPLICATION.UPDATE_SELECTED_TRANSACTION'
, 'pc_receipt_currency_code'
, 'NULL');
'ARP_PROCESS_APPLICATION.UPDATE_SELECTED_TRANSACTION'
, 'pn_receipt_exchange_rate'
, 'NULL');
arp_app_pkg.update_p(lr_ra_rec);
select ra.receivable_application_id
into ln_unapp_ra_id
from ar_receivable_applications ra
where ra.cash_receipt_id = ln_cash_receipt_id
and ra.status = 'UNAPP';
arp_app_pkg.update_p(lr_ra_rec);
arp_debug.debug( 'arp_process_application.update_amount_applied()-');
END update_selected_transaction;
| reversal_update_ps_recs |
| |
| DESCRIPTION |
| This procedure is called from the standard (receipt) and credit memo |
| reversal procedures. It updates the payment schedule for both the |
| source (cash receipt or on account credit) and the applied transaction.|
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| ARCUPSS.pls |
| arp_ps_util.get_closed_dates Calculate and get closed dates |
| update Payment Schedule table |
| |
| ARGUMENTS : IN: |
| p_ra_rec - Receivables application record |
| p_reversal_gl_date - Reversal GL date |
| p_reversal_date - Reversal Date |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - |
| 04/25/95 Ganesh Vaidee Created
| 1/3/1996 Harri Kaukovuo Added more code documentation
| 1/4/1996 H.Kaukovuo Added cash receipt payment schedule
| update.
| 11-Mar-97 Karen Lawrance Bug fix #464203. Modified
| modify_update_inv_ps_rec, added additional
| parameter "p_app_rec_trx_type". AR_APP is
| passed into the procedure for the specific
| (receipt) reversal case (called from
| reverse()). AR_CM is passed into the
| procedure for the credit memo reversal
| case (called from reverse_cm_app()).
| modify_update_inv_ps_rec now sets
| l_app_rec.trx_type to the parameter value
| instead of "AR_APP" which is not correct
| for the Credit Memo case.
| The value of AR_APP was causing incorrect
| processing in
| arp_ps_util.update_reverse_actions
| for the Credit Memo case.
| 15-Mar-97 Karen Lawrance Bug fix #493379. Set values for lines, tax
| freight and charges for the Credit Memo
| case. Should not be negative like the
| applied transaction.
| 21-Jul-97 Karen Lawrance Release 11.
| Renamed procedure from modify_update_inv_ps_rec
| Cleaned up code and included some more
| comments.
| Included changes for cross currency,
| using amount applied from for update of
| receipt.
| Bug fix #517496
| The discount amounts apply to the transaction
| not the source, changed code to null them
| out NOCOPY so that they are not used in calculating
| remaining amounts in the payment schedule
| package.
+===========================================================================*/
PROCEDURE reversal_update_ps_recs (
p_ra_rec IN ar_receivable_applications%ROWTYPE,
-- Trx type is either
-- AR_APP meaning that it is a cash receipt reversal
-- AR_CM meaning that it is a credit memo reversal
p_app_rec_trx_type IN VARCHAR,
p_reversal_gl_date IN DATE,
p_reversal_date IN DATE) IS
l_gl_date_closed DATE;
arp_debug.debug( 'arp_process_application.reversal_update_ps_recs()+');
* Transaction payment schedule not updated for activity application */
IF p_ra_rec.status NOT IN ('OTHER ACC', 'ACTIVITY') THEN
arp_ps_util.update_reverse_actions(l_app_rec, NULL, NULL);
are updated for the CM payment schedule row. */
if p_app_rec_trx_type IN ( 'AR_CM', 'AR_CM_REF') then
l_app_rec.line_applied := p_ra_rec.line_applied;
arp_ps_util.update_reverse_actions(l_app_rec, NULL, NULL);
arp_debug.debug( 'arp_process_application.reversal_update_ps_recs()-');
'EXCEPTION: arp_process_application.reversal_update_ps_recs' );
END reversal_update_ps_recs;
| reversal_insert_oppos_ra_recs |
| |
| DESCRIPTION |
| This procedure create opposing receivable application rows. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| ARCIAPPS.pls |
| arp_app_pkg.insert_p Table handler to insert into |
| ar_receivable_applications table. |
| |
| ARGUMENTS : IN: |
| p_ra_rec Receivables application record |
| p_reversal_gl_date Reversal GL date |
| p_reversal_date Reversal Date |
| p_module_name Calling module name |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Created by Ganesh Vaidee - 04/25/95
| 1/2/1996 Harri Kaukovuo Commented out NOCOPY sequence fetch because
| table handler will take care of that.
| Added second ar_receivable_applications
| row insert to reverse UNAPP row.
| 1/3/1996 Harri Kaukovuo Removed WHO column update because
| table handler will fill those.
| 3/13/1996 Harri Kaukovuo BUG 344689. Reverse receipt does not
| work.
| 6/21/1996 Harri Kaukovuo Changed reverse receipt application
| rule from '96' to
| 'REVERSE RECEIPT'.
| Bug 375636 fix.
| 05/06/1997 Karen Lawrance Bug fix #481761. Fixed application
| rule for Credit Memo case. Needs to
| be 75 for CM. Added parameter
| p_app_rec_trx_type so we know what
| type of application it is.
| 21-Jul-97 Karen Lawrance Release 11.
| Renamed procedure from modify_insert_ra_rec
| Cleaned up code and included some more
| comments.
| Included changes for cross currency, setting
| opposing value for amount applied from and
| using amount applied from for opposing UNAPP
| rows if populated.
| 27-Oct-97 Karen Murphy Bug #495321. In the Receipt reversal and
| Credit Memo reversal code the reversal
| of the acctd_unearned_discount_taken was
| being done twice, as a result setting it back
| to its initial value. Removed the second
| assignment.
| 10-Aug-98 Sushama Borde Bug# 700204. Added code to make the
| gl_posted_date NULL for reversed rows.
| Bug# 657464. Apply_date was being set to
| reversal_date for rows created after reversal,deleted this assignment.
| 13-Jun-00 Satheesh Nambiar Bug 1329091 - Passing a new parameter
| pay_sched_upd_yn to accounting engine
| to acknowldge PS is updated.
| 19-Dec-03 Jyoti Pandey Bug 2729626 Unapplied Amount is zero, but the
| status of receipt is 'UNAPP'.
+===========================================================================*/
PROCEDURE reversal_insert_oppos_ra_recs (
p_ra_rec IN OUT NOCOPY AR_RECEIVABLE_APPLICATIONS%ROWTYPE
, p_app_rec_trx_type IN VARCHAR
, p_reversal_gl_date DATE
, p_reversal_date DATE
, p_module_name IN VARCHAR2
, p_called_from IN VARCHAR2 DEFAULT NULL
, p_rec_app_id OUT NOCOPY NUMBER) IS /* jrautiai BR implementation */
l_ra_id NUMBER;
arp_debug.debug( 'arp_process_application.reversal_insert_oppos_ra_recs()+');
arp_app_pkg.insert_p( p_ra_rec, l_ra_id );
select 'Y' into l_llca_exist
from ar_activity_details
where cash_receipt_id = p_ra_rec.cash_receipt_id
and source_id = p_ra_rec.receivable_application_id
and source_table = 'RA'
and nvl(CURRENT_ACTIVITY_FLAG,'Y') = 'R';
arp_debug.debug('Total rows selected under activity details: ' || SQL%ROWCOUNT);
update ar_activity_details
set source_table = 'RA',
source_id = l_ra_id,
CURRENT_ACTIVITY_FLAG = 'N',
CREATED_BY = NVL(FND_GLOBAL.user_id,-1),
CREATION_DATE = SYSDATE ,
LAST_UPDATE_LOGIN = NVL( arp_standard.profile.last_update_login,
p_ra_rec.last_update_login ),
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = NVL(FND_GLOBAL.user_id,-1)
where cash_receipt_id = p_ra_rec.cash_receipt_id
and source_id = p_ra_rec.receivable_application_id
and source_table = 'RA'
and nvl(CURRENT_ACTIVITY_FLAG,'Y') = 'R';
arp_debug.debug('Total rows updated under activity details: ' || SQL%ROWCOUNT);
arp_debug.debug( 'arp_process_application.reversal_insert_oppos_ra_recs()-');
arp_app_pkg.insert_p(
p_ra_rec -- IN
, l_ra_id -- OUT NOCOPY
);
select 'Y' into l_llca_exist
from ar_activity_details
where cash_receipt_id = p_ra_rec.cash_receipt_id
and source_id = p_ra_rec.receivable_application_id
and source_table = 'RA';
select 'Y' into l_llca_exist_rev
from ar_activity_details
where cash_receipt_id = p_ra_rec.cash_receipt_id
and source_id = p_ra_rec.receivable_application_id
and source_table = 'RA'
and nvl(CURRENT_ACTIVITY_FLAG,'Y') = 'R';
arp_debug.debug('Total rows selected under activity details: ' || SQL%ROWCOUNT);
update ar_activity_details
set source_table = 'RA',
source_id = l_ra_id,
CURRENT_ACTIVITY_FLAG = 'N',
CREATED_BY = NVL(FND_GLOBAL.user_id,-1),
CREATION_DATE = SYSDATE ,
LAST_UPDATE_LOGIN = NVL( arp_standard.profile.last_update_login,
p_ra_rec.last_update_login ),
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = NVL(FND_GLOBAL.user_id,-1)
where cash_receipt_id = p_ra_rec.cash_receipt_id
and source_id = p_ra_rec.receivable_application_id
and source_table = 'RA'
and nvl(CURRENT_ACTIVITY_FLAG,'Y') = 'R';
arp_debug.debug('Total rows updated under activity details: ' || SQL%ROWCOUNT);
SELECT
rma.unapplied_ccid
, ed.code_combination_id /* earned_ccid */
, uned.code_combination_id /* unearned_ccid */
, ps.payment_schedule_id
, ps.amount_due_remaining
, ps.amount_due_original
INTO
l_rma_unapplied_ccid
, l_rma_earned_ccid
, l_rma_unearned_ccid
, l_payment_schedule_id
, l_amount_due_remaining
, l_amount_due_original
FROM
ar_receipt_method_accounts rma
, ar_payment_schedules ps
, ar_cash_receipts cr
, ar_receivables_trx ed
, ar_receivables_trx uned
WHERE
cr.cash_receipt_id = p_ra_rec.cash_receipt_id
AND cr.cash_receipt_id = ps.cash_receipt_id
AND rma.receipt_method_id = cr.receipt_method_id
AND rma.remit_bank_acct_use_id = cr.remit_bank_acct_use_id
AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
AND rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+);
arp_app_pkg.insert_p(
p_ra_rec -- IN
, l_ra_id -- OUT NOCOPY
);
select nvl(sum(ra.amount_applied),0)
into l_on_account_total
from ar_receivable_applications ra
where ra.cash_receipt_id = p_ra_rec.cash_receipt_id
and ra.status IN ('ACC','OTHER ACC');
arp_cash_receipts_pkg.update_p(l_cr_rec, p_ra_rec.cash_receipt_id);
arp_debug.debug( 'arp_process_application.reversal_insert_oppos_ra_recs()-');
'EXCEPTION: arp_process_application.reversal_insert_oppos_ra_recs');
END reversal_insert_oppos_ra_recs;
| reversal_update_old_ra_rec |
| |
| DESCRIPTION |
| This procedure updates the receivable application row that is being |
| reversed, by setting the reversal dates and setting display to 'N'. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_app_pkg.update_p - table handler to update |
| ar_receivable_applications table |
| |
| ARGUMENTS : IN: |
| p_ra_rec - Receivables application record |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
|
| 21-Jul-97 Karen Lawrance Release 11.
| Renamed procedure from modify_update_old_ra_rec
+===========================================================================*/
PROCEDURE reversal_update_old_ra_rec( p_reversal_gl_date DATE,
p_ra_rec IN OUT NOCOPY ar_receivable_applications%ROWTYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_debug.debug( 'arp_process_application.reversal_update_old_ra_rec()+' );
arp_app_pkg.update_p( p_ra_rec );
arp_debug.debug( 'arp_process_application.reversal_update_old_ra_rec()-' );
'EXCEPTION: arp_process_application.reversal_update_old_ra_rec' );
END reversal_update_old_ra_rec;
| p_adj_id - Adjustment Id of inserted ar_adjustments row|
| |
| RETURNS : FALSE or TRUE |
| |
| NOTES - This could be a public function later |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| 04-MAY-95 G Vaidees Added A check in validate_cb_reversal to |
| check for remaining balance only if adjustment|
| exists for the application |
| 10-MAY-95 G Vaidees Added Over application check |
| 12-SEP-00 skoukunt Fix 1387071, Added (( l_bal - l_adj_amount + |
| l_pmt + l_edisc + l_udisc ) <> 0) |
|
| 02-Jun-01 S.Nambiar Bug 1808020 -Modified the routine to handle |
| Activity and receipt chargeback |
+===========================================================================*/
FUNCTION check_reversable (
p_ra_id IN ar_receivable_applications.receivable_application_id%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) RETURN BOOLEAN IS
l_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
SELECT ra.cash_receipt_id,
ra.applied_payment_schedule_id,
ra.amount_applied,
nvl( ra.earned_discount_taken, 0 ),
nvl( ra.unearned_discount_taken, 0 ),
nvl( ps.amount_due_remaining, 0 ),
ra.status,
ctt.allow_overapplication_flag,
nvl(ps.amount_due_original, 0)
INTO l_ass_cr_id,
l_ps_id,
l_pmt,
l_edisc,
l_udisc,
l_bal,
l_status,
l_over_appln_flag,
l_bal_org
FROM ar_receivable_applications ra,
ar_payment_schedules ps,
ra_cust_trx_types ctt
WHERE ra.receivable_application_id = p_ra_id
AND ps.payment_schedule_id(+) = ra.applied_payment_schedule_id
AND ctt.cust_trx_type_id = ps.cust_trx_type_id;
SELECT ra.cash_receipt_id,
ra.application_ref_id,
ra.amount_applied,
nvl( ra.earned_discount_taken, 0 ),
nvl( ra.unearned_discount_taken, 0 ),
nvl( ps.amount_due_remaining, 0 ),
ra.status,
ctt.allow_overapplication_flag,
nvl(ps.amount_due_original, 0)
INTO l_ass_cr_id,
l_ct_id,
l_pmt,
l_edisc,
l_udisc,
l_bal,
l_status,
l_over_appln_flag,
l_bal_org
FROM ar_receivable_applications ra,
ar_payment_schedules ps,
ra_cust_trx_types ctt
WHERE ra.receivable_application_id = p_ra_id
AND ps.customer_trx_id(+) = ra.application_ref_id
AND ctt.cust_trx_type_id = ps.cust_trx_type_id
AND ra.application_ref_type = 'CHARGEBACK';
SELECT 1
INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 1
FROM AR_CASH_RECEIPT_HISTORY crh
WHERE crh.cash_receipt_id = l_ra_rec.application_ref_id
AND crh.status IN ('REMITTED', 'CLEARED'));
arp_debug.debug( 'l_status in app_delete IS' );
SELECT NVL( SUM( amount), 0 )
INTO l_adj_amount
FROM ar_adjustments
WHERE payment_schedule_id = l_ps_id
AND associated_cash_receipt_id = l_ass_cr_id
AND status = 'A';
SELECT NVL( SUM( amount), 0 )
INTO l_adj_amount_twb
FROM ar_adjustments
WHERE payment_schedule_id = l_ps_id
AND associated_cash_receipt_id is null
AND status = 'A';
SELECT count( distinct a.chargeback_customer_trx_id )
INTO l_cb_count
FROM ar_adjustments a,
ar_adjustments b
WHERE a.receivables_trx_id = arp_global.G_CB_RT_ID
AND a.associated_cash_receipt_id = l_ass_cr_id
AND a.payment_schedule_id = l_ps_id
AND b.receivables_trx_id(+) = arp_global.G_CB_REV_RT_ID
AND b.customer_trx_id(+) = a.chargeback_customer_trx_id
AND b.customer_trx_id is NULL;
SELECT NVL( SUM( DECODE( status,
'A', amount,
0
)
), 0
),
NVL( SUM( DECODE( status,
'A', 0,
'R', 0,
'U', 0,
amount
)
), 0
)
INTO l_adj_amt,
l_pend_amt
FROM ar_adjustments
WHERE payment_schedule_id = l_ps_id
AND associated_cash_receipt_id = l_ass_cr_id
AND chargeback_customer_trx_id iS NULL;
arp_debug.debug( 'after validate_cb_reversal in app_delete' );
arp_debug.debug( 'inside for ar_adjustments_C in app_delete' );
arp_debug.debug( 'after validate_cb_reversal in app_delete' );
/* SELECT adj.adjustment_id,
ps.payment_schedule_id
FROM ar_adjustments adj,
ar_payment_schedules ps
WHERE adj.associated_cash_receipt_id = l_ass_cr_id
AND adj.payment_schedule_id = l_ps_id
AND adj.chargeback_customer_trx_id = ps.customer_trx_id(+)
FOR UPDATE OF ps.last_updated_by,adj.last_updated_by NOWAIT; */
| p_select_flag - If this flag is TRUE, then select |
| Cash receipt Id and table, else use the |
| passed in values |
| p_module_name - Name of the module that called this |
| procedure |
| p_module_version - Version of the module that called this|
| procedure |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES - This could be a public function later |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| |
+===========================================================================*/
PROCEDURE reverse_action(
p_ra_id IN ar_receivable_applications.receivable_application_id%TYPE,
p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_reversal_gl_date IN DATE, p_reversal_date IN DATE,
p_select_flag IN BOOLEAN,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
l_adj_amount NUMBER;
SELECT a.chargeback_customer_trx_id,
a.adjustment_id,
a.status
FROM ar_adjustments a,
ar_adjustments b
WHERE a.receivables_trx_id = arp_global.G_CB_RT_ID
AND a.associated_cash_receipt_id = l_ass_cr_id
AND a.payment_schedule_id = l_ps_id
AND b.receivables_trx_id(+) = arp_global.G_CB_REV_RT_ID
AND b.customer_trx_id(+) = a.chargeback_customer_trx_id
AND b.customer_trx_id is null;
SELECT adjustment_id,
status
FROM ar_adjustments
WHERE associated_cash_receipt_id = l_ass_cr_id
AND payment_schedule_id = l_ps_id
AND status <> 'R'
AND associated_application_id = p_ra_id --Bug2144783
AND chargeback_customer_trx_id is null
UNION
SELECT adjustment_id,
status
FROM ar_adjustments
WHERE associated_cash_receipt_id = l_ass_cr_id
AND payment_schedule_id = l_ps_id
AND status <> 'R'
AND associated_application_id is null
AND chargeback_customer_trx_id is null;
arp_debug.debug( 'inside BEGIN reverse_action in app_delete' );
p_select_flag );
IF ( p_select_flag = TRUE ) THEN
BEGIN
SELECT cash_receipt_id,
applied_payment_schedule_id
INTO l_ass_cr_id,
l_ps_id
FROM ar_receivable_applications
WHERE receivable_application_id = p_ra_id;
arp_debug.debug( 'Select from ar_adjustments failed' );
arp_debug.debug( 'before SELECT NVL reverse_action in app_delete' );
SELECT NVL( SUM( DECODE( status,
'A', amount,
0
)
), 0
),
NVL( SUM( DECODE( status,
'A', acctd_amount,
0
)
), 0
),
NVL( SUM( DECODE( chargeback_customer_trx_id,
NULL, DECODE( status,
'A', amount,
0
),
0
)
), 0
),
NVL( SUM( DECODE( status,
'A', 0,
'R', 0,
'U', 0,
amount
)
), 0
),
NVL( SUM( DECODE( status,
'A', line_adjusted,
0
)
), 0
),
NVL( SUM( DECODE( status,
'A', tax_adjusted,
0 )
), 0
),
NVL( SUM( DECODE( status,
'A', freight_adjusted,
0
)
), 0
),
NVL( SUM( DECODE( status,
'A', receivables_charges_adjusted,
0
)
), 0
),
NVL( SUM( DECODE( status,
'A', DECODE( type ,
'CHARGES',amount,0),
0
)
), 0
)
INTO l_app_rec.amount_applied,
l_app_rec.acctd_amount_applied,
l_adj_amount,
l_app_rec.amount_adjusted_pending,
l_app_rec.line_applied,
l_app_rec.tax_applied,
l_app_rec.freight_applied,
l_app_rec.receivables_charges_applied,
l_app_rec.charges_type_adjusted
FROM ar_adjustments
WHERE payment_schedule_id = l_ps_id
AND associated_cash_receipt_id = l_ass_cr_id;
arp_debug.debug( 'before for reverse_action in app_delete' );
arp_debug.debug( 'inside for ar_adjustments_C in app_delete' );
arp_debug.debug( 'after reverse_chargeback in app_delete' );
'after ar_adjustment.reverse_adjustment in app_delete' );
UPDATE ra_customer_trx
SET status_trx = 'CL'
WHERE customer_trx_id = l_adj_rec.chargeback_customer_trx_id;
arp_debug.debug( 'after UPDATE in app_delete ' );
arp_debug.debug( 'before if ar_adjustments_C%ROWCOUNT in app_delete ' );
arp_debug.debug( 'before for ar_adjustments_radj_C in app_delete' );
arp_debug.debug( 'inside for ar_adjustments_radj_C in app_delete' );
'after AR_ADJUSTMENT.reverse_adjustmen in app_delete' );
'before SELECT NVL( amount_adjusted_pending in app_delete' );
SELECT NVL( amount_adjusted_pending, 0 ) -
NVL( l_app_rec.amount_adjusted_pending, 0)
INTO l_app_rec.amount_adjusted_pending
FROM ar_payment_schedules
WHERE payment_schedule_id = l_ps_id;
arp_debug.debug( 'before get_closed_dates in app_delete' );
arp_debug.debug( 'before update_reverse_actions in app_delete' );
arp_ps_util.update_reverse_actions( l_app_rec, NULL, NULL );
| p_select_flag - If this flag is TRUE, then select |
| Cash receipt Id and table, else use the |
| passed in values |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| |
+===========================================================================*/
PROCEDURE validate_reverse_action_args(
p_ra_id IN ar_receivable_applications.receivable_application_id%TYPE,
p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_reversal_gl_date IN DATE, p_reversal_date IN DATE,
p_select_flag IN BOOLEAN ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_debug.debug(
'arp_process_application.validate_reverse_action_args()+' );
p_select_flag IS NULL ) THEN
FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
IF ( p_select_flag <> TRUE ) THEN
IF ( p_cr_id IS NULL OR p_ps_id IS NULL ) THEN
FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
| Do all actions neccessary to update PS rows and insert APP and UNAPP |
| rows in RA table when a receipt is applied to a transaction. |
| The PS table rows on the transaction and receipt side are updated |
| and 2 RA rows are inserted with status 'APP' and 'UNAPP'. |
| |
| SCOPE - PUBLIC |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| arp_ps_util.update_invoice_related_columns |
| arp_ps_util.update_cm_related_columns |
| |
| ARGUMENTS : IN: |
| p_receipt_ps_id - PS Id of the receipt |
| p_invoice_ps_id - PS Id of the transaction |
| p_amount_applied - TO amount |
| p_amount_applied_from - FROM amount |
| p_trans_to_receipt_rate - Cross currency rate |
| p_receipt_currency_code - Currency of the receipt |
| p_invoice_currency_code - Currency of the transaction |
| p_earned_discount_taken - Earned Discount taken |
| p_unearned_discount_taken - UnEarned Discount taken |
| p_apply_date - Application date |
| p_gl_date - GL Date |
| p_ussgl_transaction_code - USSGL transaction code |
| p_customer_trx_line_id - Line of the transaction applied |
| p_comments - comments |
| |
| OTHER DESCRIPTIVE FLEX columns |
| p_module_name - Name of the module that called this |
| procedure |
| p_module_version - Version of the module that called this|
| procedure |
| OUT: |
| p_receivable_application_id - Identifier of RA |
| p_acctd_amount_applied_from - Rounded acctd FROM amount |
| p_acctd_amount_applied_to - Rounded acctd TO amount |
| |
| RETURNS : NONE |
| |
| NOTES - |
|
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 08/29/95
| 4/19/1996 Harri Kaukovuo Added new parameter p_customer_trx_line_id
| 5/3/1996 Harri Kaukovuo Added new parameter p_out_rec_application_id
| 5/6/1996 Harri Kaukovuo Bug 354045, modified to update the
| batch status.
| 10/17/1996 Karen Lawrance Added code to prevent the creation of more
| than one application against the same receipt
| and invoice.
| 05/06/1997 Karen Lawrance Bug fix #481761. Fixed application rule.
| 07/21/1997 Karen Lawrance Release 11.
| Added processing for cross currency
| functionality. Included new IN, OUT NOCOPY
| parameters and use of amount applied from.
| 08/21/197 Tasman Tang Added global_attribute_category,
| global_attribute[1-20] for global descriptive
| flexfield
| 07/16/1998 Karen Murphy Bug fix 634464. Modified the code that works
| out NOCOPY the status for the Receipt (APP or UNAPP)
| Now includes the total On Account amount as
| this is not included in the Pay Sched, Amt
| Due Rem total.
| 05/06/1999 Debbie Jancis Modified receipt_application to accept
| comments for insert into receivable_applications
| for Bug 741914.
| |
| 14-APR-2000 Jani Rautiainen Added parameter p_called_from. This is needed|
| in the logic to decide whether UNAPP row is |
| postable or not. In BR scenario when an |
| Activity Application of Short Term Debt is |
| unapplied and then normal application is |
| done against the BR the UNAPP row is not |
| postable. This is an user requirement for BR.|
| The parameter is defaulted to NULL so no |
| impact for the existing functionality. |
| Also added logic to prevent accounting |
| creation if the row is not postable. |
| Also added parameter p_move_deferred_tax |
| which indicates whether the accounting engine|
| should move deferred tax or not |
| 13-Jun-00 Satheesh Nambiar Bug 1329091 - Passing a new parameter
| pay_sched_upd_yn to accounting engine
| to acknowldge PS is updated.
| 27-APR-00 jbeckett Calls iClaim API if CLAIM |
| |
| 06/02/2001 S.Nambiar Bug 1808020 - Activity application should
| not fetch
| PS record when unapplying or modifying amount
| 08/03/2001 jbeckett Bug 1905659 - Added parameter
| p_amount_due_remaining to receipt_application
| 09/05/2002 jbeckett Bug 2361331 - passes primary_salesrep_id to
| create_claim
| 03-Sep-02 Debbie Jancis modified for mrc trigger replacement. |
| added processing for receivable apps |
| 06-SEP-02 jbeckett Bug 2751910 - Added p_customer_reason |
| 28-Apr-03 Rahna Kader Bug 1659928: Now the program checks for |
| over application before the applications |
| are saved |
| 07-AUG-03 Jon Beckett Bug 3087819 - Claim is not created/updated |
| if called from Trade Management |
| 10-AUG-04 Jon Beckett Bug 3773036 - new exception trade_mgt_err |
| raised if claim create/update fails to |
| ensure control is passed correctly back to |
| calling program and TM error is displayed. |
| 26-AUG-05 MRaymond 4566510 - Prorate discounts over tax via
| etax.
| 14-OCT-2005 Jon Beckett Bug 4565758 - Legal entity passed to TM
| 19-DEC-2006 M Raymond 5677984 - Removed etax calls for rec app
| and moved them inside
| update_invoice_related_columns
| 31-JUL-2009 M Raymond 8620127 - set maturity date correctly
| based on receipt_method rule
+===========================================================================*/
PROCEDURE receipt_application(
p_receipt_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_invoice_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_receivable_applications.amount_applied%TYPE,
p_amount_applied_from IN
ar_receivable_applications.amount_applied_from%TYPE,
p_trans_to_receipt_rate IN
ar_receivable_applications.trans_to_receipt_rate%TYPE,
p_invoice_currency_code IN
ar_payment_schedules.invoice_currency_code%TYPE,
p_receipt_currency_code IN ar_cash_receipts.currency_code%TYPE,
p_earned_discount_taken IN
ar_receivable_applications.earned_discount_taken%TYPE,
p_unearned_discount_taken IN
ar_receivable_applications.unearned_discount_taken%TYPE,
p_apply_date IN ar_receivable_applications.apply_date%TYPE,
p_gl_date IN ar_receivable_applications.gl_date%TYPE,
p_ussgl_transaction_code IN
ar_receivable_applications.ussgl_transaction_code%TYPE,
p_customer_trx_line_id IN
ar_receivable_applications.applied_customer_trx_line_id%TYPE,
p_application_ref_type IN
ar_receivable_applications.application_ref_type%TYPE,
p_application_ref_id IN
ar_receivable_applications.application_ref_id%TYPE,
p_application_ref_num IN
ar_receivable_applications.application_ref_num%TYPE,
p_secondary_application_ref_id IN
ar_receivable_applications.secondary_application_ref_id%TYPE,
p_attribute_category IN ar_receivable_applications.attribute_category%TYPE,
p_attribute1 IN ar_receivable_applications.attribute1%TYPE,
p_attribute2 IN ar_receivable_applications.attribute2%TYPE,
p_attribute3 IN ar_receivable_applications.attribute3%TYPE,
p_attribute4 IN ar_receivable_applications.attribute4%TYPE,
p_attribute5 IN ar_receivable_applications.attribute5%TYPE,
p_attribute6 IN ar_receivable_applications.attribute6%TYPE,
p_attribute7 IN ar_receivable_applications.attribute7%TYPE,
p_attribute8 IN ar_receivable_applications.attribute8%TYPE,
p_attribute9 IN ar_receivable_applications.attribute9%TYPE,
p_attribute10 IN ar_receivable_applications.attribute10%TYPE,
p_attribute11 IN ar_receivable_applications.attribute11%TYPE,
p_attribute12 IN ar_receivable_applications.attribute12%TYPE,
p_attribute13 IN ar_receivable_applications.attribute13%TYPE,
p_attribute14 IN ar_receivable_applications.attribute14%TYPE,
p_attribute15 IN ar_receivable_applications.attribute15%TYPE,
p_global_attribute_category IN ar_receivable_applications.global_attribute_category%TYPE,
p_global_attribute1 IN ar_receivable_applications.global_attribute1%TYPE,
p_global_attribute2 IN ar_receivable_applications.global_attribute2%TYPE,
p_global_attribute3 IN ar_receivable_applications.global_attribute3%TYPE,
p_global_attribute4 IN ar_receivable_applications.global_attribute4%TYPE,
p_global_attribute5 IN ar_receivable_applications.global_attribute5%TYPE,
p_global_attribute6 IN ar_receivable_applications.global_attribute6%TYPE,
p_global_attribute7 IN ar_receivable_applications.global_attribute7%TYPE,
p_global_attribute8 IN ar_receivable_applications.global_attribute8%TYPE,
p_global_attribute9 IN ar_receivable_applications.global_attribute9%TYPE,
p_global_attribute10 IN ar_receivable_applications.global_attribute10%TYPE,
p_global_attribute11 IN ar_receivable_applications.global_attribute11%TYPE,
p_global_attribute12 IN ar_receivable_applications.global_attribute12%TYPE,
p_global_attribute13 IN ar_receivable_applications.global_attribute13%TYPE,
p_global_attribute14 IN ar_receivable_applications.global_attribute14%TYPE,
p_global_attribute15 IN ar_receivable_applications.global_attribute15%TYPE,
p_global_attribute16 IN ar_receivable_applications.global_attribute16%TYPE,
p_global_attribute17 IN ar_receivable_applications.global_attribute17%TYPE,
p_global_attribute18 IN ar_receivable_applications.global_attribute18%TYPE,
p_global_attribute19 IN ar_receivable_applications.global_attribute19%TYPE,
p_global_attribute20 IN ar_receivable_applications.global_attribute20%TYPE,
p_comments IN ar_receivable_applications.comments%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
-- OUT NOCOPY
x_application_ref_id OUT NOCOPY
ar_receivable_applications.application_ref_id%TYPE,
x_application_ref_num OUT NOCOPY
ar_receivable_applications.application_ref_num%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_out_rec_application_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE,
p_acctd_amount_applied_from OUT NOCOPY ar_receivable_applications.acctd_amount_applied_from%TYPE,
p_acctd_amount_applied_to OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
x_claim_reason_name OUT NOCOPY VARCHAR2,
p_called_from IN VARCHAR2, /* jrautiai BR implementation */
p_move_deferred_tax IN VARCHAR2, /* jrautiai BR implementation */
p_link_to_trx_hist_id IN ar_receivable_applications.link_to_trx_hist_id%TYPE, /* jrautiai BR implementation */
p_amount_due_remaining IN
ar_payment_schedules.amount_due_remaining%TYPE,
p_payment_set_id IN ar_receivable_applications.payment_set_id%TYPE,
p_application_ref_reason IN ar_receivable_applications.application_ref_reason%TYPE,
p_customer_reference IN ar_receivable_applications.customer_reference%TYPE,
p_customer_reason IN ar_receivable_applications.customer_reason%TYPE,
from_llca_call IN VARCHAR2 DEFAULT 'N',
p_gt_id IN NUMBER DEFAULT NULL
) IS
l_rec_ra_rec ar_receivable_applications%ROWTYPE;
SELECT t.exchange_rate_type
, t.exchange_date
, t.exchange_rate
, t.trx_number
, t.cust_trx_type_id
, t.bill_to_customer_id
, t.bill_to_site_use_id
, t.ship_to_site_use_id
, t.primary_salesrep_id
, t.legal_entity_id
FROM ra_customer_trx t
WHERE t.customer_trx_id = p_customer_trx_id;
SELECT receipt_number
FROM ar_cash_receipts
WHERE cash_receipt_id = p_receipt_id;
SELECT amount_due_remaining
FROM ar_payment_schedules
WHERE payment_schedule_id = p_payment_schedule_id;
select 'Y'
into l_found
from ar_receivable_applications rap
where rap.payment_schedule_id = p_receipt_ps_id
and rap.applied_payment_schedule_id = p_invoice_ps_id
and rap.display = 'Y'
and rap.status = 'APP';
select 'Y'
into l_found
from ar_receivable_applications rap
where rap.payment_schedule_id = p_receipt_ps_id
and rap.applied_payment_schedule_id = p_invoice_ps_id
and rap.applied_customer_trx_line_id = p_customer_trx_line_id
and rap.display = 'Y'
and rap.status = 'APP';
select ps.amount_due_remaining,ps.amount_due_original,ctt.allow_overapplication_flag
into l_inv_bal_amount, l_inv_orig_amount, l_allow_over_application
from ra_cust_trx_types ctt, ar_payment_schedules ps
where ps.payment_schedule_id = p_invoice_ps_id
and ps.cust_trx_type_id = ctt.cust_trx_type_id;
/* Removing the ce_bank_acct_uses table from the select statement for bug 5571095 by gnramasa on 06/10/2006 */
--BUG 6660834
IF nvl(p_called_from,'NONE') IN ('AUTORECAPI','AUTORECAPI2') THEN
ar_autorec_api.populate_cached_data( l_receipt_info_rec );
SELECT cr.deposit_date, rm.maturity_date_rule_code, ps.due_date,
ps.amount_applied, cr.org_id
INTO l_receipt_date, l_maturity_date_rule, l_maturity_date,
l_amount_applied, l_org_id
FROM ar_cash_receipts cr,
ar_receipt_methods rm,
ar_payment_schedules ps
WHERE cr.cash_receipt_id = l_rec_ra_rec.cash_receipt_id
AND cr.receipt_method_id = rm.receipt_method_id
AND cr.cash_receipt_id = ps.cash_receipt_id;
SELECT amount_due_remaining,
due_date
INTO l_amount_due_remaining,
l_due_date
FROM ar_payment_schedules
WHERE payment_schedule_id = p_invoice_ps_id;
SELECT ps.cash_receipt_id
, ps.amount_due_remaining
, rma.unapplied_ccid
, ed.code_combination_id
, uned.code_combination_id
, crh.batch_id
, rm.maturity_date_rule_code
, cr.deposit_date
, ps.due_date
, ps.amount_applied
, cr.org_id
INTO l_rec_ra_rec.cash_receipt_id
, l_amount_due_remaining
, l_rec_ra_rec.code_combination_id
, l_inv_ra_rec.earned_discount_ccid
, l_inv_ra_rec.unearned_discount_ccid
, ln_batch_id
, l_maturity_date_rule
, l_receipt_date
, l_maturity_date
, l_amount_applied
, l_org_id
FROM ar_cash_receipts cr
, ar_cash_receipt_history crh
, ar_receipt_methods rm
, ce_bank_acct_uses_ou ba
, ar_receipt_method_accounts rma
, ar_payment_schedules ps
, ar_receivables_trx ed
, ar_receivables_trx uned
WHERE ps.payment_schedule_id = p_receipt_ps_id
AND cr.cash_receipt_id = ps.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND rm.receipt_method_id = cr.receipt_method_id
AND ba.bank_acct_use_id = cr.remit_bank_acct_use_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND rma.receipt_method_id = rm.receipt_method_id
AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
AND rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+);
SELECT customer_trx_id, due_date
INTO l_inv_ra_rec.applied_customer_trx_id,
l_due_date
FROM ar_payment_schedules
WHERE payment_schedule_id = p_invoice_ps_id;
select count(*)
into l_count
from ar_receivable_applications rap
where rap.cash_receipt_id = l_rec_ra_rec.cash_receipt_id
and rap.secondary_application_ref_id =
p_secondary_application_ref_id
and rap.application_ref_type = 'CLAIM'
and rap.display = 'Y';
SELECT ps.class
INTO l_class_var
FROM ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_invoice_ps_id;
arp_ps_util.update_receipt_related_columns(
p_receipt_ps_id,
nvl(p_amount_applied_from, p_amount_applied),
p_apply_date,
p_gl_date,
l_rec_ra_rec.acctd_amount_applied_from,
NULL_VAR, -- NULL modified to NULL_VAR for bug 460959 (Oracle 8)
l_maturity_date,
l_class_var); -- Bug 6924942
arp_app_pkg.insert_p( l_rec_ra_rec,
l_rec_ra_rec.receivable_application_id );
UPDATE ar_receivable_applications
SET include_in_accumulation = 'N'
WHERE cash_receipt_id = l_rec_ra_rec.cash_receipt_id
AND status = 'UNAPP'
AND include_in_accumulation <> 'N';
arp_ps_util.update_invoice_related_columns(
'CASH',
p_invoice_ps_id,
p_amount_applied,
p_earned_discount_taken,
p_unearned_discount_taken,
p_apply_date,
p_gl_date,
l_inv_ra_rec.acctd_amount_applied_to,
l_inv_ra_rec.acctd_earned_discount_taken,
l_inv_ra_rec.acctd_unearned_discount_taken,
l_inv_ra_rec.line_applied,
l_inv_ra_rec.tax_applied,
l_inv_ra_rec.freight_applied,
l_inv_ra_rec.receivables_charges_applied,
l_inv_ra_rec.line_ediscounted,
l_inv_ra_rec.tax_ediscounted,
l_inv_ra_rec.freight_ediscounted,
l_inv_ra_rec.charges_ediscounted,
l_inv_ra_rec.line_uediscounted,
l_inv_ra_rec.tax_uediscounted,
l_inv_ra_rec.freight_uediscounted,
l_inv_ra_rec.charges_uediscounted,
l_inv_ra_rec.rule_set_id,
NULL_VAR,
l_rec_ra_rec.cash_receipt_id,
l_ra_app_id,
l_gt_id );
SELECT payment_schedule_id
INTO l_claim_trx_ps_id
FROM ar_payment_schedules
WHERE customer_trx_id = l_inv_ra_rec.applied_customer_trx_id
AND payment_schedule_id = l_inv_ra_rec.applied_payment_schedule_id
AND NVL(active_claim_flag,'N') <> 'N' /*Bug 11841102*/
/* AND NVL(active_claim_flag,'N') not in ('N','C') Bug 10178153*/
AND ROWNUM = 1;
update_claim(
p_claim_id => l_claim_id
, p_invoice_ps_id => l_claim_trx_ps_id
, p_customer_trx_id => l_inv_ra_rec.applied_customer_trx_id
, p_amount => l_claim_amount
, p_amount_applied => p_amount_applied
, p_apply_date => p_apply_date
, p_cash_receipt_id => l_inv_ra_rec.cash_receipt_id
, p_receipt_number => l_receipt_number
, p_action_type => 'A'
, x_claim_reason_code_id => l_claim_reason_code_id
, x_claim_reason_name => l_claim_reason_name
, x_claim_number => l_inv_ra_rec.application_ref_num
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reason_id => to_number(p_application_ref_reason)--Yao Zhang add for bug 10197191
);
arp_app_pkg.insert_p( l_inv_ra_rec,
l_ra_app_id );
arp_debug.debug('Calling arp_app_pkg.insert_p and created l_inv_ra_rec.receivable_application_id :'||
l_inv_ra_rec.receivable_application_id);
select nvl(sum(ra.amount_applied),0)
into l_on_account_total
from ar_receivable_applications ra
where ra.cash_receipt_id = l_rec_ra_rec.cash_receipt_id
and ra.status IN ('ACC','OTHER ACC');
arp_cash_receipts_pkg.update_p(
l_cr_rec
, l_rec_ra_rec.cash_receipt_id);
arp_rw_batches_check_pkg.update_batch_status(ln_batch_id,p_called_from); --Bug7194951
| Do all actions neccessary to update PS rows and insert APP |
| row in RA table when a CM is applied to an invoice. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| arp_ps_util.update_invoice_related_columns |
| arp_ps_util.update_cm_related_columns |
| |
| ARGUMENTS : IN: |
| p_cm_ps_id - PS Id of the Credit Memo |
| p_invoice_ps_id - PS Id of the transaction |
| p_amount_applied - TO amount |
| p_apply_date - Application date |
| p_gl_date - GL Date |
| p_ussgl_transaction_code - USSGL transaction code |
| p_customer_trx_line_id - Line of the transaction applied |
| |
| OTHER DESCRIPTIVE FLEX columns |
| p_module_name - Name of the module that called this |
| procedure |
| p_module_version - Version of the module that called this|
| procedure |
| OUT: |
| p_receivable_application_id - Identifier of RA |
| p_acctd_amount_applied_from - Rounded acctd FROM amount |
| p_acctd_amount_applied_to - Rounded acctd TO amount |
| |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY |
| 08/29/95 Ganesh Vaidee Created |
| 02/06/1996 Harri Kaukovuo Added new parameters |
| p_customer_trx_line_id |
| p_out_rec_application_id |
| 10/17/1996 Karen Lawrance Added code to prevent the creation of more |
| than one application against the same receipt|
| and invoice. |
| 05/06/1997 Karen Lawrance Bug fix #481761. Fixed application rule. |
| 07/25/1997 Karen Lawrance Release 11. |
| Added acctd amount from and to as OUT NOCOPY |
| parameters to be consistent with receipt |
| applications. |
| Also cleaned up code and added some more |
| comments. |
| 08/21/1997 Tasman Tang Added global_attribute_category, |
| global_attribute[1-20] for global |
| descriptive flexfield |
| 13-Jun-00 Satheesh Nambiar Bug 1329091 - Passing a new parameter
| pay_sched_upd_yn to accounting engine
| to acknowldge PS is updated.
| 03-Sep-02 Debbie Jancis Added call to mrc_engine3 for processing
| mrc data for ar_receivable_applications
| 28-Apr-03 Rahna Kader Bug 1659928: Now the program checks for |
| over application before the applications |
| are saved |
| 12-Mar-04 Bhushan Dhotkar Bug 2662270: Added a column p_comments
+===========================================================================*/
PROCEDURE cm_application(
p_cm_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_invoice_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_receivable_applications.amount_applied%TYPE,
p_apply_date IN ar_receivable_applications.apply_date%TYPE,
p_gl_date IN ar_receivable_applications.gl_date%TYPE,
p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE,
p_attribute_category IN ar_receivable_applications.attribute_category%TYPE,
p_attribute1 IN ar_receivable_applications.attribute1%TYPE,
p_attribute2 IN ar_receivable_applications.attribute2%TYPE,
p_attribute3 IN ar_receivable_applications.attribute3%TYPE,
p_attribute4 IN ar_receivable_applications.attribute4%TYPE,
p_attribute5 IN ar_receivable_applications.attribute5%TYPE,
p_attribute6 IN ar_receivable_applications.attribute6%TYPE,
p_attribute7 IN ar_receivable_applications.attribute7%TYPE,
p_attribute8 IN ar_receivable_applications.attribute8%TYPE,
p_attribute9 IN ar_receivable_applications.attribute9%TYPE,
p_attribute10 IN ar_receivable_applications.attribute10%TYPE,
p_attribute11 IN ar_receivable_applications.attribute11%TYPE,
p_attribute12 IN ar_receivable_applications.attribute12%TYPE,
p_attribute13 IN ar_receivable_applications.attribute13%TYPE,
p_attribute14 IN ar_receivable_applications.attribute14%TYPE,
p_attribute15 IN ar_receivable_applications.attribute15%TYPE,
p_global_attribute_category IN ar_receivable_applications.global_attribute_category%TYPE,
p_global_attribute1 IN ar_receivable_applications.global_attribute1%TYPE,
p_global_attribute2 IN ar_receivable_applications.global_attribute2%TYPE,
p_global_attribute3 IN ar_receivable_applications.global_attribute3%TYPE,
p_global_attribute4 IN ar_receivable_applications.global_attribute4%TYPE,
p_global_attribute5 IN ar_receivable_applications.global_attribute5%TYPE,
p_global_attribute6 IN ar_receivable_applications.global_attribute6%TYPE,
p_global_attribute7 IN ar_receivable_applications.global_attribute7%TYPE,
p_global_attribute8 IN ar_receivable_applications.global_attribute8%TYPE,
p_global_attribute9 IN ar_receivable_applications.global_attribute9%TYPE,
p_global_attribute10 IN ar_receivable_applications.global_attribute10%TYPE,
p_global_attribute11 IN ar_receivable_applications.global_attribute11%TYPE,
p_global_attribute12 IN ar_receivable_applications.global_attribute12%TYPE,
p_global_attribute13 IN ar_receivable_applications.global_attribute13%TYPE,
p_global_attribute14 IN ar_receivable_applications.global_attribute14%TYPE,
p_global_attribute15 IN ar_receivable_applications.global_attribute15%TYPE,
p_global_attribute16 IN ar_receivable_applications.global_attribute16%TYPE,
p_global_attribute17 IN ar_receivable_applications.global_attribute17%TYPE,
p_global_attribute18 IN ar_receivable_applications.global_attribute18%TYPE,
p_global_attribute19 IN ar_receivable_applications.global_attribute19%TYPE,
p_global_attribute20 IN ar_receivable_applications.global_attribute20%TYPE,
p_customer_trx_line_id IN NUMBER,
p_comments IN ar_receivable_applications.comments%TYPE DEFAULT NULL, --bug2662270
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
-- OUT NOCOPY
p_out_rec_application_id OUT NOCOPY NUMBER,
p_acctd_amount_applied_from OUT NOCOPY ar_receivable_applications.acctd_amount_applied_from%TYPE,
p_acctd_amount_applied_to OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE) IS
l_inv_ra_rec ar_receivable_applications%ROWTYPE;
select 'Y'
into l_found
from ar_receivable_applications rap
where rap.payment_schedule_id = p_cm_ps_id
and rap.applied_payment_schedule_id = p_invoice_ps_id
and rap.display = 'Y'
and rap.status = 'APP';
select 'Y'
into l_found
from ar_receivable_applications rap
where rap.payment_schedule_id = p_cm_ps_id
and rap.applied_payment_schedule_id = p_invoice_ps_id
and rap.applied_customer_trx_line_id = p_customer_trx_line_id
and rap.display = 'Y'
and rap.status = 'APP';
select ps.amount_due_remaining,ps.amount_due_original,ctt.allow_overapplication_flag
into l_inv_bal_amount, l_inv_orig_amount, l_allow_over_application
from ra_cust_trx_types ctt, ar_payment_schedules ps
where ps.payment_schedule_id = p_invoice_ps_id
and ps.cust_trx_type_id = ctt.cust_trx_type_id;
arp_ps_util.update_invoice_related_columns(
'CM',
p_invoice_ps_id,
p_amount_applied,
NULL, /* Earned discount taken */
NULL, /* UnEarned discount taken */
p_apply_date,
p_gl_date,
l_inv_ra_rec.acctd_amount_applied_to,
l_inv_ra_rec.acctd_earned_discount_taken,
l_inv_ra_rec.acctd_unearned_discount_taken,
l_inv_ra_rec.line_applied,
l_inv_ra_rec.tax_applied,
l_inv_ra_rec.freight_applied,
l_inv_ra_rec.receivables_charges_applied,
l_inv_ra_rec.line_ediscounted,
l_inv_ra_rec.tax_ediscounted,
l_inv_ra_rec.freight_ediscounted,
l_inv_ra_rec.charges_ediscounted,
l_inv_ra_rec.line_uediscounted,
l_inv_ra_rec.tax_uediscounted,
l_inv_ra_rec.freight_uediscounted,
l_inv_ra_rec.charges_uediscounted,
l_inv_ra_rec.rule_set_id,
NULL_VAR ); /* NULL modified to NULL_VAR for bug 460959 - Oracle 8 */
SELECT *
INTO l_cm_ps_rec
FROM ar_payment_schedules
WHERE payment_schedule_id = p_cm_ps_id;
select NVL(ctt.post_to_gl,'N') into l_flag
from ra_cust_trx_types ctt,
ar_payment_schedules ps
where ctt.cust_trx_type_id = ps.cust_trx_type_id
and ps.payment_schedule_id = p_cm_ps_id;
arp_app_pkg.insert_p( l_inv_ra_rec,
l_inv_ra_rec.receivable_application_id );
SELECT NVL(UPGRADE_METHOD,'NONE')
INTO l_upgrade_flag
FROM RA_CUSTOMER_TRX
WHERE CUSTOMER_TRX_ID=l_inv_ra_rec.customer_trx_id;
arp_ps_util.update_cm_related_columns(
p_cm_ps_id,
p_amount_applied,
l_inv_ra_rec.line_applied,
l_inv_ra_rec.tax_applied,
l_inv_ra_rec.freight_applied,
l_inv_ra_rec.receivables_charges_applied,
p_apply_date,
p_gl_date,
l_inv_ra_rec.acctd_amount_applied_from,
l_cm_ps_rec );
arp_ps_util.update_cm_related_columns(
p_cm_ps_id,
p_amount_applied,
g_line_applied,
g_tax_applied,
g_frt_applied,
g_chrg_applied,
p_apply_date,
p_gl_date,
l_inv_ra_rec.acctd_amount_applied_from,
l_cm_ps_rec );
| Do all actions neccessary to insert rows into AR_RA table during |
| ON-ACCOUNT receipt insertion. No PS table row is updated, However |
| 2 RA rows are inserted - One as an UNAPP row and another as 'ACC' row |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| ARPCURR.functional_amount - Get the acctd amount of amount applied|
| arp_ps_pkg.fetch_p - Fetch a PS row |
| arp_app_pkg.insert_p - Insert a row into RA table |
| |
| ARGUMENTS : IN: |
| p_ps_id - PS id of the receipt |
| p_amount_applied - Input amount applied |
| p_apply_date - Application date |
| p_gl_date - Gl Date |
| p_ussgl_transaction_code - USSGL transaction code |
| OTHER DESCRIPTIVE FLEX columns |
| p_module_name - Name of the module that called this |
| procedure |
| p_module_version - Version of the module that called this|
| procedure |
| OUT: |
| p_out_rec_application_id |
| Returned receivable application id |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY |
| 08/29/1995 Ganesh Vaidee Created |
| 05/03/1996 Harri Kaukovuo Added OUT NOCOPY parameter p_out_rec_application_id |
| 05/06/1996 Harri Kaukovuo Added logics to update batch status |
| 10/31/1996 Karen Lawrance Bug fix #414626. Added code to update |
| the receipt status if the on account |
| application fully applies the receipt. |
| 08/21/1997 Tasman Tang Added global_attribute_category, |
| global_attribute[1-20] for global |
| descriptive flexfield |
| 03/05/1998 Guat Eng Tan Bug fix #627262. In the call to |
| ARPCURR.functional_amount, replace l_ps_rec. |
| invoice_currency_code with functional_curr. |
| 07/16/1998 Karen Murphy Bug fix 634464. Modified the code that works|
| out NOCOPY the status for the Receipt (APP or UNAPP)|
| Now includes the total On Account amount as |
| this is not included in the Pay Sched, Amt |
| Due Rem total. |
| 04-JAN-02 VERAO Bug 2047229 : added p_comments |
| 16-JAN-02 V Crisostomo Bug 2184812 : changes done in 2047229 added |
| parameter p_comments, but did not provide |
| default value, hence all code calling this |
| procedure and not passing comment is failing |
| modify code to DEFAULT NULL |
| 04-Sep-02 Debbie Jancis Added calls to mrc engine 3 for processing |
| inserts to ar_receivable_applications |
| 28-JUL-2003 Jon Beckett Bug 2821139 - added p_customer_reason. |
+===========================================================================*/
PROCEDURE on_account_receipts(
p_receipt_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN
ar_receivable_applications.amount_applied%TYPE,
p_apply_date IN ar_receivable_applications.apply_date%TYPE,
p_gl_date IN ar_receivable_applications.gl_date%TYPE,
p_ussgl_transaction_code IN
ar_receivable_applications.ussgl_transaction_code%TYPE,
p_attribute_category IN
ar_receivable_applications.attribute_category%TYPE,
p_attribute1 IN ar_receivable_applications.attribute1%TYPE,
p_attribute2 IN ar_receivable_applications.attribute2%TYPE,
p_attribute3 IN ar_receivable_applications.attribute3%TYPE,
p_attribute4 IN ar_receivable_applications.attribute4%TYPE,
p_attribute5 IN ar_receivable_applications.attribute5%TYPE,
p_attribute6 IN ar_receivable_applications.attribute6%TYPE,
p_attribute7 IN ar_receivable_applications.attribute7%TYPE,
p_attribute8 IN ar_receivable_applications.attribute8%TYPE,
p_attribute9 IN ar_receivable_applications.attribute9%TYPE,
p_attribute10 IN ar_receivable_applications.attribute10%TYPE,
p_attribute11 IN ar_receivable_applications.attribute11%TYPE,
p_attribute12 IN ar_receivable_applications.attribute12%TYPE,
p_attribute13 IN ar_receivable_applications.attribute13%TYPE,
p_attribute14 IN ar_receivable_applications.attribute14%TYPE,
p_attribute15 IN ar_receivable_applications.attribute15%TYPE,
p_global_attribute_category IN ar_receivable_applications.global_attribute_category%TYPE,
p_global_attribute1 IN ar_receivable_applications.global_attribute1%TYPE,
p_global_attribute2 IN ar_receivable_applications.global_attribute2%TYPE,
p_global_attribute3 IN ar_receivable_applications.global_attribute3%TYPE,
p_global_attribute4 IN ar_receivable_applications.global_attribute4%TYPE,
p_global_attribute5 IN ar_receivable_applications.global_attribute5%TYPE,
p_global_attribute6 IN ar_receivable_applications.global_attribute6%TYPE,
p_global_attribute7 IN ar_receivable_applications.global_attribute7%TYPE,
p_global_attribute8 IN ar_receivable_applications.global_attribute8%TYPE,
p_global_attribute9 IN ar_receivable_applications.global_attribute9%TYPE,
p_global_attribute10 IN ar_receivable_applications.global_attribute10%TYPE,
p_global_attribute11 IN ar_receivable_applications.global_attribute11%TYPE,
p_global_attribute12 IN ar_receivable_applications.global_attribute12%TYPE,
p_global_attribute13 IN ar_receivable_applications.global_attribute13%TYPE,
p_global_attribute14 IN ar_receivable_applications.global_attribute14%TYPE,
p_global_attribute15 IN ar_receivable_applications.global_attribute15%TYPE,
p_global_attribute16 IN ar_receivable_applications.global_attribute16%TYPE,
p_global_attribute17 IN ar_receivable_applications.global_attribute17%TYPE,
p_global_attribute18 IN ar_receivable_applications.global_attribute18%TYPE,
p_global_attribute19 IN ar_receivable_applications.global_attribute19%TYPE,
p_global_attribute20 IN ar_receivable_applications.global_attribute20%TYPE,
p_comments IN ar_receivable_applications.comments%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
p_out_rec_application_id OUT NOCOPY NUMBER
, p_application_ref_num IN ar_receivable_applications.application_ref_num%TYPE
, p_secondary_application_ref_id IN ar_receivable_applications.secondary_application_ref_id%TYPE
, p_customer_reference IN ar_receivable_applications.customer_reference%TYPE
, p_customer_reason IN ar_receivable_applications.customer_reason%TYPE
, p_secondary_app_ref_type IN
ar_receivable_applications.secondary_application_ref_type%TYPE := null
, p_secondary_app_ref_num IN
ar_receivable_applications.secondary_application_ref_num%TYPE := null
, p_on_acct_cust_id IN ar_receivable_applications.on_acct_cust_id%TYPE DEFAULT NULL
, p_on_acct_cust_site_use_id IN ar_receivable_applications.on_acct_cust_site_use_id%TYPE DEFAULT NULL
, p_on_acct_po_num IN ar_receivable_applications.on_acct_po_num%TYPE DEFAULT NULL
) IS
l_ra_rec ar_receivable_applications%ROWTYPE;
SELECT ps.cash_receipt_id,
ps.amount_due_remaining,
rma.on_account_ccid,
rma.unapplied_ccid,
crh.batch_id
INTO l_ra_rec.cash_receipt_id,
l_amount_due_remaining,
l_onacc_cc_id,
l_unapp_cc_id
, ln_batch_id
FROM ar_payment_schedules ps
, ar_cash_receipts cr
, ar_cash_receipt_history crh
, ar_receipt_methods rm
, ce_bank_acct_uses ba
, ar_receipt_method_accounts rma
WHERE ps.payment_schedule_id = p_receipt_ps_id
AND cr.cash_receipt_id = ps.cash_receipt_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND rm.receipt_method_id = cr.receipt_method_id
AND ba.bank_acct_use_id = cr.remit_bank_acct_use_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND rma.receipt_method_id = rm.receipt_method_id;
select nvl(sum(ra.amount_applied),0)
into l_on_account_total
from ar_receivable_applications ra
where ra.cash_receipt_id = l_ra_rec.cash_receipt_id
and ra.status IN ('ACC','OTHER ACC');
arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
l_ra_rec.receivable_application_id := NULL; /* filled during act. insert */
arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
arp_cash_receipts_pkg.update_p(
l_cr_rec
, l_ra_rec.cash_receipt_id);
arp_rw_batches_check_pkg.update_batch_status(ln_batch_id);
| to update receipt status properly |
| 25-FEB-2001 Manoj Gudivak Modified to handle Claim Application |
| 19-APR-2001 Jon Beckett Claim Application now handled by new procedure |
| other_account_application |
| 08-May-2001 S Nambiar Now p_application_ref_id contains the chargeback|
| customer_trx_id.And claim_id is stored in |
| chargeback_customer_trx_id field. |
| This is a temporary arrangement. |
| 14-May-01 S.Nambiar Modified activity_application() routine to replace
| the balance segment of activity application to that
| of receipt UNAPP
| 06/20/2001 S.Nambiar Bug 1823299 - Activity application should not
| leave rounding difference amount as unapplied
| for forign currency receipts.
| 08-NOV-01 S.Nambiar Bug 2103345. calculate acctd_amount_applied_to
| for cc refund activity application.
| 09-Sep-02 Debbie Jancis Modified for mrc trigger replacement. Added
| calls to ar_mrc_engine3 for processing receivable
| applications.
| 06-FEB-03 Jon Beckett Bug 2751910 - Added p_applied_rec_app_id for
| netting.
| 29-MAY-03 Jon Beckett Bug 2821138 - Added p_netted_receipt_flag and
| calculation of acctd_amount_applied_to for
| main receipt in a netting pair.
| 02-Feb-05 Debbie Jancis Enhancement 4145224: added customer_Reason
| 04-Mar-05 Jyoti Pandey Bug: 4166986 CC Chargeback logic
+===========================================================================*/
PROCEDURE activity_application(
p_receipt_ps_id IN
ar_payment_schedules.payment_schedule_id%TYPE,
p_application_ps_id IN
ar_receivable_applications.applied_payment_schedule_id%TYPE,
p_link_to_customer_trx_id IN
ar_receivable_applications.link_to_customer_trx_id%TYPE,
p_amount_applied IN
ar_receivable_applications.amount_applied%TYPE,
p_apply_date IN
ar_receivable_applications.apply_date%TYPE,
p_gl_date IN
ar_receivable_applications.gl_date%TYPE,
p_receivables_trx_id IN
ar_receivable_applications.receivables_trx_id%TYPE,
p_ussgl_transaction_code IN
ar_receivable_applications.ussgl_transaction_code%TYPE,
p_attribute_category IN
ar_receivable_applications.attribute_category%TYPE,
p_attribute1 IN
ar_receivable_applications.attribute1%TYPE,
p_attribute2 IN
ar_receivable_applications.attribute2%TYPE,
p_attribute3 IN
ar_receivable_applications.attribute3%TYPE,
p_attribute4 IN
ar_receivable_applications.attribute4%TYPE,
p_attribute5 IN
ar_receivable_applications.attribute5%TYPE,
p_attribute6 IN
ar_receivable_applications.attribute6%TYPE,
p_attribute7 IN
ar_receivable_applications.attribute7%TYPE,
p_attribute8 IN
ar_receivable_applications.attribute8%TYPE,
p_attribute9 IN
ar_receivable_applications.attribute9%TYPE,
p_attribute10 IN
ar_receivable_applications.attribute10%TYPE,
p_attribute11 IN
ar_receivable_applications.attribute11%TYPE,
p_attribute12 IN
ar_receivable_applications.attribute12%TYPE,
p_attribute13 IN
ar_receivable_applications.attribute13%TYPE,
p_attribute14 IN
ar_receivable_applications.attribute14%TYPE,
p_attribute15 IN
ar_receivable_applications.attribute15%TYPE,
p_global_attribute_category IN
ar_receivable_applications.global_attribute_category%TYPE,
p_global_attribute1 IN
ar_receivable_applications.global_attribute1%TYPE,
p_global_attribute2 IN
ar_receivable_applications.global_attribute2%TYPE,
p_global_attribute3 IN
ar_receivable_applications.global_attribute3%TYPE,
p_global_attribute4 IN
ar_receivable_applications.global_attribute4%TYPE,
p_global_attribute5 IN
ar_receivable_applications.global_attribute5%TYPE,
p_global_attribute6 IN
ar_receivable_applications.global_attribute6%TYPE,
p_global_attribute7 IN
ar_receivable_applications.global_attribute7%TYPE,
p_global_attribute8 IN
ar_receivable_applications.global_attribute8%TYPE,
p_global_attribute9 IN
ar_receivable_applications.global_attribute9%TYPE,
p_global_attribute10 IN
ar_receivable_applications.global_attribute10%TYPE,
p_global_attribute11 IN
ar_receivable_applications.global_attribute11%TYPE,
p_global_attribute12 IN
ar_receivable_applications.global_attribute12%TYPE,
p_global_attribute13 IN
ar_receivable_applications.global_attribute13%TYPE,
p_global_attribute14 IN
ar_receivable_applications.global_attribute14%TYPE,
p_global_attribute15 IN
ar_receivable_applications.global_attribute15%TYPE,
p_global_attribute16 IN
ar_receivable_applications.global_attribute16%TYPE,
p_global_attribute17 IN
ar_receivable_applications.global_attribute17%TYPE,
p_global_attribute18 IN
ar_receivable_applications.global_attribute18%TYPE,
p_global_attribute19 IN
ar_receivable_applications.global_attribute19%TYPE,
p_global_attribute20 IN
ar_receivable_applications.global_attribute20%TYPE,
p_comments IN
ar_receivable_applications.comments%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
p_application_ref_type IN OUT NOCOPY
ar_receivable_applications.application_ref_type%TYPE,
p_application_ref_id IN OUT NOCOPY
ar_receivable_applications.application_ref_id%TYPE,
p_application_ref_num IN OUT NOCOPY
ar_receivable_applications.application_ref_num%TYPE,
p_secondary_application_ref_id IN OUT NOCOPY NUMBER,
p_payment_set_id IN NUMBER,
p_called_from IN VARCHAR2, /*5444407*/
p_out_rec_application_id OUT NOCOPY NUMBER,
p_applied_rec_app_id IN NUMBER,
p_customer_reference IN ar_receivable_applications.customer_reference%TYPE,
p_netted_receipt_flag IN VARCHAR2,
p_netted_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
p_secondary_app_ref_type IN
ar_receivable_applications.secondary_application_ref_type%TYPE := null,
p_secondary_app_ref_num IN
ar_receivable_applications.secondary_application_ref_num%TYPE := null,
p_customer_reason IN
ar_receivable_applications.customer_reason%TYPE DEFAULT NULL,
--Bug 5450371
p_application_ref_reason IN ar_receivable_applications.application_ref_reason%TYPE Default NULL
) IS
/* Cursor to application information for activity application */
CURSOR activity_c IS
SELECT cr.currency_code,
ps.cash_receipt_id,
ps.amount_due_remaining,
rma.unapplied_ccid,
crh.batch_id,
rt.code_combination_id activity_ccid,
rt.type activity_type
FROM ar_payment_schedules ps,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_receipt_methods rm,
ce_bank_acct_uses ba,
ar_receipt_method_accounts rma,
ar_receivables_trx rt
WHERE ps.payment_schedule_id = p_receipt_ps_id
AND cr.cash_receipt_id = ps.cash_receipt_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND rm.receipt_method_id = cr.receipt_method_id
AND ba.bank_acct_use_id = cr.remit_bank_acct_use_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND rma.receipt_method_id = rm.receipt_method_id
AND rt.receivables_trx_id = p_receivables_trx_id;
SELECT payment_trxn_extension_id
INTO l_pymnt_trxn_ext_id_temp
FROM ar_cash_receipts
WHERE cash_receipt_id IN (
SELECT MAX(application_ref_id)
FROM ar_receivable_applications
WHERE cash_receipt_id = l_ps_rec.cash_receipt_id
AND application_type = 'CASH'
AND application_ref_type = 'MISC_RECEIPT'
AND applied_payment_schedule_id <> '-9'); -- Added for Bug 13591288
/* Update the cash receipt in the payment schedule table. */
arp_ps_util.update_receipt_related_columns(
p_receipt_ps_id,
p_amount_applied,
p_apply_date,
p_gl_date,
l_acctd_amount_applied_from,
NULL_VAR,
NULL );
/* Prepare for 'UNAPP' record insertion with -ve amount applied */
l_ra_rec.payment_schedule_id := p_receipt_ps_id;
SELECT invoice_currency_code,
exchange_rate
INTO l_invoice_currency_code,
l_exchange_rate
FROM ra_customer_trx
WHERE customer_trx_id = p_application_ref_id;
SELECT currency_code,
exchange_rate
INTO l_receipt_currency_code,
l_exchange_rate
FROM ar_cash_receipts
WHERE cash_receipt_id = l_application_ref_id;
SELECT invoice_currency_code,
exchange_rate
INTO l_receipt_currency_code,
l_exchange_rate
FROM ar_payment_schedules
WHERE payment_schedule_id = p_application_ps_id;
/* Insert UNAPP record */
arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
* Prepare for 'ACTIVITY' record insertion with +ve amount applied.
* Applied_payment_schedule_id and applied_customer_trx_id are negative
* ie for short term debt -2 and display = 'Y', Only the following
* details change for the 'ACTIVITY' record from the UNAPP record during
* application insertion.
* --------------------------------------------------------------------- */
/* -------------------------------------------------------------------+
| Balancing segment of ACTIVITY application should be replaced with |
| that of Receipt's UNAPP record |
+--------------------------------------------------------------------*/
IF NVL(FND_PROFILE.value('AR_DISABLE_REC_ACTIVITY_BALSEG_SUBSTITUTION'),
'N') = 'N' THEN
arp_util.Substitute_Ccid(
p_coa_id => arp_global.chart_of_accounts_id,
p_original_ccid => activity_rec.activity_ccid ,
p_subs_ccid => activity_rec.unapplied_ccid ,
p_actual_ccid => l_ra_rec.code_combination_id );
l_ra_rec.receivable_application_id := NULL; /* filled during act.insert */
/* Insert ACTIVITY record */
arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
* This is to distinguish between updateable NULL and NULL value (dummy)
* which means that column is not to be updated. */
arp_cash_receipts_pkg.set_to_dummy(l_cr_rec);
select nvl(sum(ra.amount_applied),0)
into l_on_account_total
from ar_receivable_applications ra
where ra.cash_receipt_id = l_ra_rec.cash_receipt_id
and ra.status IN ('ACC','OTHER ACC');
/* Update cash receipt status. */
arp_cash_receipts_pkg.update_p(l_cr_rec,
l_ra_rec.cash_receipt_id);
* Update batch status if receipt has a batch
* For Bills Receivable Short Term Debt application this does not do
* anything, since the batch id for the cash receipt history record is
* always NULL. For other type activity applications the called procedure
* needs to be changed to support the new activity application.
* --------------------------------------------------------------------- */
IF (activity_rec.batch_id IS NOT NULL AND Nvl(p_called_from,'*')<>'WRITEOFF') /*5444407*/
THEN
arp_rw_batches_check_pkg.update_batch_status(activity_rec.batch_id);
SELECT ps.customer_trx_id,
ps.class,
ps.amount_due_original
FROM ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_invoice_ps_id;
SELECT dist.code_combination_id
FROM ra_cust_trx_line_gl_dist dist
WHERE dist.customer_trx_id = l_customer_trx_id
AND dist.account_class = 'REC'
AND dist.latest_rec_flag = 'Y';
SELECT dist.code_combination_id, dist.source_type
FROM ar_distributions dist
WHERE dist.source_id = p_transaction_history_id
AND dist.source_table = 'TH'
AND dist.source_type in ('REC','REMITTANCE','FACTOR','UNPAIDREC')
AND dist.source_id_secondary IS null
AND dist.source_table_secondary IS null
AND dist.source_type_secondary IS null
and (((sign(p_sign) > 0)
and ((nvl(dist.AMOUNT_DR,0) <> 0) OR (nvl(dist.ACCTD_AMOUNT_DR,0) <> 0))
and (nvl(dist.AMOUNT_CR,0) = 0) and (nvl(dist.ACCTD_AMOUNT_CR,0) = 0))
OR ((sign(p_sign) < 0)
and ((nvl(dist.AMOUNT_CR,0) <> 0) OR (nvl(dist.ACCTD_AMOUNT_CR,0) <> 0))
and (nvl(dist.AMOUNT_DR,0) = 0) and (nvl(dist.ACCTD_AMOUNT_DR,0) = 0)))
order by dist.line_id desc;
SELECT trh.transaction_history_id, trh.postable_flag, trh.event
FROM ar_transaction_history trh
WHERE trh.customer_trx_id = p_customer_trx_id
AND trh.current_accounted_flag = 'Y';
SELECT transaction_history_id
FROM ar_transaction_history
WHERE postable_flag = 'Y'
AND event <> 'MATURITY_DATE'
CONNECT BY PRIOR prv_trx_history_id = transaction_history_id
START WITH transaction_history_id = p_transaction_history_id
ORDER BY transaction_history_id desc;
SELECT ps.class
FROM ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_applied_ps_id;
SELECT trh.status, trh.event
FROM ar_transaction_history trh
WHERE trh.customer_trx_id = p_applied_customer_trx_id
AND trh.current_record_flag = 'Y';
| Do all actions neccessary to insert rows into AR_RA table during |
| Other Application. No PS table row is updated, However |
| 2 RA rows are inserted - One as an UNAPP row and another as 'OTHER ACC'|
| application status OTHER ACC bahaves the same way as on-account ACC |
| This new procedure is introduced for creating special applications like|
| claim and prepayment. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| ARPCURR.functional_amount - Get the acctd amount of amount applied|
| arp_ps_pkg.fetch_p - Fetch a PS row |
| arp_app_pkg.insert_p - Insert a row into RA table |
| |
| ARGUMENTS : IN: |
| p_ps_id - PS id of the receipt |
| p_amount_applied - Input amount applied |
| p_apply_date - Application date |
| p_gl_date - Gl Date |
| p_receivables_trx_id -Activity id |
| p_applied_ps_id - Applied payment schedule id -4,-7 |
| p_ussgl_transaction_code - USSGL transaction code |
| OTHER DESCRIPTIVE FLEX columns |
| p_module_name - Name of the module that called this |
| procedure |
| p_module_version - Version of the module that called this|
| procedure |
| OUT: |
| p_out_rec_application_id |
| Returned receivable application id |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY |
| 17-APR-01 S Nambiar Created |
| 01-Jun-01 S.Nambiar Bug 1811261 - OTHER ACC should derive the ccid
| from activity selected |
| 7-Sep-01 S.Nambiar Added applied_ps_id,payment_set_id parameter |
| to support prepayment applications. |
| 03-Sep-02 Debbie Jancis Modified for mrc trigger replacement. |
| added processing for receivable apps |
| 07-AUG-2003 Jon Beckett Bug 3087819 - added p_called_from parameter |
| Claim is not created/updated if called from |
| Trade Management. |
| 30-JUN-2004 Jon Beckett Removed RAISE from handling of trade_mgt_err |
| to ensure TM errors are displayed correctly |
| 06-AUG-2004 Jon Beckett Bug 3643551 - index on applied_ps_id |
| ignored to ensure index on cash_receipt_id is|
| used in query on ar_receivable_applications |
| 03-MAR-2005 JASSING Added the code to check for the profile |
| option 'AR:Disable Receivable Activity |
| Balancing Segment Substitution' for Claims |
| and Prepayments. Bug Fix 4025652. |
| 14-OCT-2005 Jon Beckett Bug 4565758 - legal entity passed to TM |
+===========================================================================*/
PROCEDURE other_account_application(
p_receipt_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN
ar_receivable_applications.amount_applied%TYPE,
p_apply_date IN ar_receivable_applications.apply_date%TYPE,
p_gl_date IN ar_receivable_applications.gl_date%TYPE,
p_receivables_trx_id ar_receivable_applications.receivables_trx_id%TYPE,
p_applied_ps_id IN ar_receivable_applications.applied_payment_schedule_id%TYPE,
p_ussgl_transaction_code IN
ar_receivable_applications.ussgl_transaction_code%TYPE,
p_application_ref_type IN
ar_receivable_applications.application_ref_type%TYPE,
p_application_ref_id IN
ar_receivable_applications.application_ref_id%TYPE,
p_application_ref_num IN
ar_receivable_applications.application_ref_num%TYPE,
p_secondary_application_ref_id IN NUMBER,
p_comments IN
ar_receivable_applications.comments%TYPE,
p_attribute_category IN
ar_receivable_applications.attribute_category%TYPE,
p_attribute1 IN ar_receivable_applications.attribute1%TYPE,
p_attribute2 IN ar_receivable_applications.attribute2%TYPE,
p_attribute3 IN ar_receivable_applications.attribute3%TYPE,
p_attribute4 IN ar_receivable_applications.attribute4%TYPE,
p_attribute5 IN ar_receivable_applications.attribute5%TYPE,
p_attribute6 IN ar_receivable_applications.attribute6%TYPE,
p_attribute7 IN ar_receivable_applications.attribute7%TYPE,
p_attribute8 IN ar_receivable_applications.attribute8%TYPE,
p_attribute9 IN ar_receivable_applications.attribute9%TYPE,
p_attribute10 IN ar_receivable_applications.attribute10%TYPE,
p_attribute11 IN ar_receivable_applications.attribute11%TYPE,
p_attribute12 IN ar_receivable_applications.attribute12%TYPE,
p_attribute13 IN ar_receivable_applications.attribute13%TYPE,
p_attribute14 IN ar_receivable_applications.attribute14%TYPE,
p_attribute15 IN ar_receivable_applications.attribute15%TYPE,
p_global_attribute_category IN ar_receivable_applications.global_attribute_category%TYPE,
p_global_attribute1 IN ar_receivable_applications.global_attribute1%TYPE,
p_global_attribute2 IN ar_receivable_applications.global_attribute2%TYPE,
p_global_attribute3 IN ar_receivable_applications.global_attribute3%TYPE,
p_global_attribute4 IN ar_receivable_applications.global_attribute4%TYPE,
p_global_attribute5 IN ar_receivable_applications.global_attribute5%TYPE,
p_global_attribute6 IN ar_receivable_applications.global_attribute6%TYPE,
p_global_attribute7 IN ar_receivable_applications.global_attribute7%TYPE,
p_global_attribute8 IN ar_receivable_applications.global_attribute8%TYPE,
p_global_attribute9 IN ar_receivable_applications.global_attribute9%TYPE,
p_global_attribute10 IN ar_receivable_applications.global_attribute10%TYPE,
p_global_attribute11 IN ar_receivable_applications.global_attribute11%TYPE,
p_global_attribute12 IN ar_receivable_applications.global_attribute12%TYPE,
p_global_attribute13 IN ar_receivable_applications.global_attribute13%TYPE,
p_global_attribute14 IN ar_receivable_applications.global_attribute14%TYPE,
p_global_attribute15 IN ar_receivable_applications.global_attribute15%TYPE,
p_global_attribute16 IN ar_receivable_applications.global_attribute16%TYPE,
p_global_attribute17 IN ar_receivable_applications.global_attribute17%TYPE,
p_global_attribute18 IN ar_receivable_applications.global_attribute18%TYPE,
p_global_attribute19 IN ar_receivable_applications.global_attribute19%TYPE,
p_global_attribute20 IN ar_receivable_applications.global_attribute20%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
p_payment_set_id IN ar_receivable_applications.payment_set_id%TYPE,
x_application_ref_id OUT NOCOPY
ar_receivable_applications.application_ref_id%TYPE,
x_application_ref_num OUT NOCOPY
ar_receivable_applications.application_ref_num%TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_out_rec_application_id OUT NOCOPY NUMBER
, p_application_ref_reason IN ar_receivable_applications.application_ref_reason%TYPE
, p_customer_reference IN ar_receivable_applications.customer_reference%TYPE
, p_customer_reason IN ar_receivable_applications.customer_reason%TYPE
, x_claim_reason_name OUT NOCOPY VARCHAR2
, p_called_from IN VARCHAR2) IS
l_ra_rec ar_receivable_applications%ROWTYPE;
SELECT ps.cash_receipt_id,
ps.amount_due_remaining,
rt.code_combination_id activity_ccid,
rma.unapplied_ccid,
crh.batch_id,
cr.currency_code
, cr.exchange_rate_type
, cr.exchange_date
, cr.exchange_rate
, cr.pay_from_customer
, cr.customer_site_use_id
, cr.receipt_number
, cr.legal_entity_id
INTO l_ra_rec.cash_receipt_id
, l_amount_due_remaining
, l_activity_cc_id
, l_unapp_cc_id
, ln_batch_id
, l_currency_code
, l_exchange_rate_type
, l_exchange_rate_date
, l_exchange_rate
, l_customer_id
, l_customer_site_use_id
, l_receipt_number
, l_legal_entity_id
FROM ar_payment_schedules ps
, ar_cash_receipts cr
, ar_cash_receipt_history crh
, ar_receipt_methods rm
, ce_bank_acct_uses ba
, ar_receipt_method_accounts rma
, ar_receivables_trx rt
WHERE ps.payment_schedule_id = p_receipt_ps_id
AND cr.cash_receipt_id = ps.cash_receipt_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND rm.receipt_method_id = cr.receipt_method_id
AND ba.bank_acct_use_id = cr.remit_bank_acct_use_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND rma.receipt_method_id = rm.receipt_method_id
AND rt.receivables_trx_id = p_receivables_trx_id;
SELECT 'Y'
INTO l_found
FROM ar_receivable_applications rap
WHERE rap.cash_receipt_id = l_ra_rec.cash_receipt_id
AND rap.applied_payment_schedule_id + 0 = -4
AND rap.secondary_application_ref_id = p_secondary_application_ref_id
AND rap.display = 'Y'
AND rap.status = 'OTHER ACC';
select nvl(sum(ra.amount_applied),0)
into l_on_account_total
from ar_receivable_applications ra
where ra.cash_receipt_id = l_ra_rec.cash_receipt_id
and ra.status IN ('ACC','OTHER ACC');
arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
l_ra_rec.receivable_application_id := NULL; /* filled during act. insert */
/* Bug 4170060 - update TM amount_applied with outstanding claim
amount */
l_amount_applied := arpt_sql_func_util.get_claim_amount(l_ra_rec.secondary_application_ref_id) + p_amount_applied;
update_claim(
p_claim_id => l_ra_rec.secondary_application_ref_id
, p_invoice_ps_id => NULL
, p_customer_trx_id => NULL
, p_amount => p_amount_applied * -1
, p_amount_applied => l_amount_applied
, p_apply_date => p_apply_date
, p_cash_receipt_id => l_ra_rec.cash_receipt_id
, p_receipt_number => l_receipt_number
, p_action_type => 'A'
, x_claim_reason_code_id => l_claim_reason_code_id
, x_claim_reason_name => l_claim_reason_name
, x_claim_number => l_ra_rec.application_ref_num
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reason_id => to_number(p_application_ref_reason)--Yao Zhang add for bug 10197191
);
arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
arp_cash_receipts_pkg.update_p(
l_cr_rec
, l_ra_rec.cash_receipt_id);
arp_rw_batches_check_pkg.update_batch_status(ln_batch_id);
insert_trx_note(p_customer_trx_id
,p_receipt_number
,x_claim_number
,'CREATE');
| update_claim |
| |
| DESCRIPTION |
| Calls iClaim group API to update a deduction claim. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| OZF_Claim_GRP.Update_Deduction - Group API to update a claim from AR |
| |
| ARGUMENTS : IN: |
| OUT: |
| |
| RETURNS : NONE |
| |
| |
| MODIFICATION HISTORY |
| jbeckett 02-MAY-2001 Created |
| apandit 04-JUN-2001 added parameter p_invoice_ps_id. Bug 1812328 |
| apandit 05-JUN-2001 added parameters p_claim_number and |
| p_customer_trx_id. Bug 1812334 |
| jbeckett 07-FEB-2003 Bug 2751910 - no longer do we actually cancel |
| the claim, we just set the amount to 0 and set |
| active_claim flag to 'C' on payment schedule if|
| invoice related. |
| REnamed from cancel_claim to reflect change. |
| |
+===========================================================================*/
PROCEDURE update_claim(
p_claim_id IN OUT NOCOPY NUMBER
, p_invoice_ps_id IN NUMBER
, p_customer_trx_id IN NUMBER
, p_amount IN NUMBER
, p_amount_applied IN NUMBER
, p_apply_date IN DATE
, p_cash_receipt_id IN NUMBER
, p_receipt_number IN VARCHAR2
, p_action_type IN VARCHAR2
, x_claim_reason_code_id OUT NOCOPY NUMBER
, x_claim_reason_name OUT NOCOPY VARCHAR2
, x_claim_number OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_reason_id IN NUMBER DEFAULT NULL)--Yao Zhang add for bug 10197191
IS
l_claim_rec OZF_Claim_GRP.Deduction_Rec_Type;
select receivable_application_id ra_app_id,
amount_applied ,
application_ref_type app_ref_type
from ar_receivable_applications
WHERE applied_customer_trx_id =p_cust_trx_id
order by receivable_application_id;
arp_debug.debug( 'arp_process_application.update_claim()+' );
SELECT receipt_number INTO l_receipt_number
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT amount_due_original
INTO l_amount_due_original
FROM ar_payment_schedules
WHERE payment_schedule_id = p_invoice_ps_id;
arp_debug.debug('update_claim: p_claim_id = '||p_claim_id);
arp_debug.debug('update_claim: p_amount = '||p_amount);
arp_debug.debug('update_claim: p_apply_date = '||p_apply_date);
arp_debug.debug('update_claim: p_amount_applied = '||p_amount_applied);
arp_debug.debug('update_claim: p_action_type = '||p_action_type);
arp_debug.debug('update_claim: p_cash_receipt_id = '||p_cash_receipt_id);
arp_debug.debug('update_claim: p_customer_trx_id = '||p_customer_trx_id);
arp_debug.debug('update_claim: l_receipt_number = '||l_receipt_number);
arp_debug.debug('update_claim: l_reason_id = '||p_reason_id);
arp_debug.debug('update_claim: l_reason_id in p_deduction= '||l_claim_rec.reason_code_id);
OZF_Claim_GRP.Update_Deduction
(p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_deduction => l_claim_rec
,x_object_version_number => l_object_version_number
,x_claim_reason_code_id => x_claim_reason_code_id
,x_claim_reason_name => x_claim_reason_name
,x_claim_id => p_claim_id
,x_claim_number => x_claim_number );
SELECT oc.status_code --Add for bug 13464283
INTO l_claim_status
FROM ozf_claims oc
WHERE claim_number = x_claim_number;
insert_trx_note(p_customer_trx_id
,NULL
,x_claim_number
,'CLOSE');
insert_trx_note(p_customer_trx_id
,NULL
,x_claim_number
,'CANCEL');
update_dispute_on_trx(p_invoice_ps_id
, l_active_claim_flag
, l_amount_from_dispute);
arp_debug.debug('update_claim: l_amount_due_original = '||l_amount_due_original);
arp_debug.debug('update_claim: l_c_app_amt = '||l_c_app_amt);
ARP_Debug.debug('update_claim: l_nc_app_amt = '||l_nc_app_amt);
update_dispute_on_trx(p_invoice_ps_id
, l_claim_flag
, l_amt_in_dispute);
arp_debug.debug( 'arp_process_application.update_claim: ERROR occurred calling update_deduction: '||SQLERRM );
arp_debug.debug( 'arp_process_application.update_claim()-' );
'EXCEPTION: arp_process_application.update_claim' );
END update_claim;
| insert_trx_note |
| |
| DESCRIPTION |
| Calls arp_notes_pkg to insert a note into AR_NOTES for a given |
| transaction |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| arp_notes_pkg.insert_cover |
| |
| ARGUMENTS : IN: |
| OUT: |
| |
| RETURNS : NONE |
| |
| |
| MODIFICATION HISTORY |
| jbeckett 03-MAY-2001 Created |
| apandit 05-JUN-2001 added parameter p_flag. Bug 1812334 |
| |
+===========================================================================*/
PROCEDURE insert_trx_note(
p_customer_trx_id IN NUMBER
, p_receipt_number IN VARCHAR2
, p_claim_number IN VARCHAR2
, p_flag IN VARCHAR2)
IS
l_text VARCHAR2(2000);
l_last_update_login NUMBER;
arp_debug.debug( 'arp_process_application.insert_trx_note()+' );
l_last_update_login := arp_standard.profile.last_update_login;
arp_notes_pkg.insert_cover(
p_note_type => 'MAINTAIN',
p_text => l_text,
p_customer_call_id => NULL,
p_customer_call_topic_id => NULL,
p_call_action_id => NULL,
p_customer_trx_id => p_customer_trx_id,
p_note_id => l_note_id,
p_last_updated_by => l_user_id,
p_last_update_date => l_sysdate,
p_last_update_login => l_last_update_login,
p_created_by => l_user_id,
p_creation_date => l_sysdate);
arp_debug.debug( 'arp_process_application.insert_trx_note()-' );
arp_debug.debug( 'EXCEPTION: arp_process_application.insert_trx_note' );
END insert_trx_note;
l_last_update_login NUMBER;
SELECT payment_schedule_id,
invoice_currency_code,
due_date,
amount_in_dispute,
amount_due_original,
amount_due_remaining,
amount_adjusted,
customer_trx_id,
customer_id,
customer_site_use_id,
class,
trx_date,
dispute_date
FROM ar_payment_schedules
WHERE payment_schedule_id = p_ps_id;
l_last_update_login := arp_standard.profile.last_update_login;
AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
l_history_id,
l_trx_class,
'MODIFY_TRX');
p_LastUpdatedBy =>l_user_id,
p_LastUpdateDate => l_sysdate,
p_lastUpdateLogin => l_last_update_login);
UPDATE ar_payment_schedules ps
SET ps.amount_in_dispute = nvl(ps.amount_in_dispute,0) + p_dispute_amount,
ps.dispute_date = SYSDATE,
last_updated_by = l_user_id,
last_update_login = l_last_update_login,
active_claim_flag = p_active_claim
WHERE ps.payment_schedule_id = p_invoice_ps_id;
PROCEDURE update_dispute_on_trx(
p_invoice_ps_id IN NUMBER
,p_active_claim IN VARCHAR2
,p_amount IN NUMBER )
IS
l_last_update_login NUMBER;
SELECT payment_schedule_id,
amount_in_dispute,
amount_due_remaining,
dispute_date
FROM ar_payment_schedules
WHERE payment_schedule_id = p_ps_id;
l_last_update_login := arp_standard.profile.last_update_login;
arp_debug.debug( 'arp_process_application.update_dispute_on_trx()+' );
p_LastUpdatedBy => l_user_id,
p_LastUpdateDate => l_sysdate,
p_lastUpdateLogin =>l_last_update_login);
UPDATE ar_payment_schedules ps
SET ps.amount_in_dispute = p_amount,
ps.dispute_date = DECODE(p_amount,0,null,SYSDATE),
last_updated_by = l_user_id,
last_update_login = l_last_update_login,
active_claim_flag = decode(p_active_claim,'A','Y','C')
WHERE ps.payment_schedule_id = p_invoice_ps_id;
UPDATE ar_payment_schedules ps
SET ps.amount_in_dispute = DECODE(ps.amount_in_dispute, p_amount, NULL
, (ps.amount_in_dispute-p_amount)),
ps.dispute_date = DECODE(p_amount,0,null,SYSDATE),
last_updated_by = l_user_id,
last_update_login = l_last_update_login,
active_claim_flag = p_active_claim
WHERE ps.payment_schedule_id = p_invoice_ps_id;
arp_debug.debug( 'arp_process_application.update_dispute_on_trx()-' );
arp_debug.debug( 'EXCEPTION: arp_process_application.update_dispute_on_trx');
END update_dispute_on_trx;
' select status_code from ozf_ar_deductions_v where claim_id = :claim_id ';
| arp_app_pkg.insert_p - Insert a row into RA table |
| |
| ARGUMENTS : IN: |
| p_cm_ps_id - PS id of the receipt |
| p_application_ps_id - PS id of the special application |
| p_amount_applied - Input amount applied |
| p_apply_date - Application date |
| p_gl_date - Gl Date |
| p_ussgl_transaction_code - USSGL transaction code |
| p_receivables_trx_id -Activity id |
| p_receipt_method_id - payment method for misc receipt |
| OTHER DESCRIPTIVE FLEX columns |
| p_module_name - Name of the module that called this |
| procedure |
| p_module_version - Version of the module that called this|
| procedure |
| OUT: |
| p_out_rec_application_id |
| Returned receivable application id |
| RETURNS : NONE |
| |
| MODIFICATION HISTORY |
| jbeckett 10-JAN-05 Created for credit memo refunds. |
| |
+===========================================================================*/
PROCEDURE cm_activity_application(
p_cm_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_application_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_receivable_applications.amount_applied%TYPE,
p_apply_date IN ar_receivable_applications.apply_date%TYPE,
p_gl_date IN ar_receivable_applications.gl_date%TYPE,
p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE,
p_attribute_category IN ar_receivable_applications.attribute_category%TYPE,
p_attribute1 IN ar_receivable_applications.attribute1%TYPE,
p_attribute2 IN ar_receivable_applications.attribute2%TYPE,
p_attribute3 IN ar_receivable_applications.attribute3%TYPE,
p_attribute4 IN ar_receivable_applications.attribute4%TYPE,
p_attribute5 IN ar_receivable_applications.attribute5%TYPE,
p_attribute6 IN ar_receivable_applications.attribute6%TYPE,
p_attribute7 IN ar_receivable_applications.attribute7%TYPE,
p_attribute8 IN ar_receivable_applications.attribute8%TYPE,
p_attribute9 IN ar_receivable_applications.attribute9%TYPE,
p_attribute10 IN ar_receivable_applications.attribute10%TYPE,
p_attribute11 IN ar_receivable_applications.attribute11%TYPE,
p_attribute12 IN ar_receivable_applications.attribute12%TYPE,
p_attribute13 IN ar_receivable_applications.attribute13%TYPE,
p_attribute14 IN ar_receivable_applications.attribute14%TYPE,
p_attribute15 IN ar_receivable_applications.attribute15%TYPE,
p_global_attribute_category IN ar_receivable_applications.global_attribute_category%TYPE,
p_global_attribute1 IN ar_receivable_applications.global_attribute1%TYPE,
p_global_attribute2 IN ar_receivable_applications.global_attribute2%TYPE,
p_global_attribute3 IN ar_receivable_applications.global_attribute3%TYPE,
p_global_attribute4 IN ar_receivable_applications.global_attribute4%TYPE,
p_global_attribute5 IN ar_receivable_applications.global_attribute5%TYPE,
p_global_attribute6 IN ar_receivable_applications.global_attribute6%TYPE,
p_global_attribute7 IN ar_receivable_applications.global_attribute7%TYPE,
p_global_attribute8 IN ar_receivable_applications.global_attribute8%TYPE,
p_global_attribute9 IN ar_receivable_applications.global_attribute9%TYPE,
p_global_attribute10 IN ar_receivable_applications.global_attribute10%TYPE,
p_global_attribute11 IN ar_receivable_applications.global_attribute11%TYPE,
p_global_attribute12 IN ar_receivable_applications.global_attribute12%TYPE,
p_global_attribute13 IN ar_receivable_applications.global_attribute13%TYPE,
p_global_attribute14 IN ar_receivable_applications.global_attribute14%TYPE,
p_global_attribute15 IN ar_receivable_applications.global_attribute15%TYPE,
p_global_attribute16 IN ar_receivable_applications.global_attribute16%TYPE,
p_global_attribute17 IN ar_receivable_applications.global_attribute17%TYPE,
p_global_attribute18 IN ar_receivable_applications.global_attribute18%TYPE,
p_global_attribute19 IN ar_receivable_applications.global_attribute19%TYPE,
p_global_attribute20 IN ar_receivable_applications.global_attribute20%TYPE,
p_receivables_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE,
p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
p_comments IN ar_receivable_applications.comments%TYPE ,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
p_application_ref_id IN OUT NOCOPY ar_receivable_applications.application_ref_id%TYPE,
p_application_ref_num IN OUT NOCOPY ar_receivable_applications.application_ref_num%TYPE,
-- OUT NOCOPY
p_out_rec_application_id OUT NOCOPY NUMBER,
p_acctd_amount_applied_from OUT NOCOPY ar_receivable_applications.acctd_amount_applied_from%TYPE,
p_acctd_amount_applied_to OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_inv_ra_rec ar_receivable_applications%ROWTYPE;
arp_ps_util.update_cm_related_columns(
p_cm_ps_id,
p_amount_applied,
l_inv_ra_rec.line_applied,
l_inv_ra_rec.tax_applied,
l_inv_ra_rec.freight_applied,
l_inv_ra_rec.receivables_charges_applied,
p_apply_date,
p_gl_date,
l_inv_ra_rec.acctd_amount_applied_from,
l_cm_ps_rec,
'Y' );
select NVL(ctt.post_to_gl,'N') into l_flag
from ra_cust_trx_types ctt,
ar_payment_schedules ps
where ctt.cust_trx_type_id = ps.cust_trx_type_id
and ps.payment_schedule_id = p_cm_ps_id;
select code_combination_id INTO l_inv_ra_rec.code_combination_id
FROM ar_receivables_trx
WHERE receivables_trx_id = p_receivables_trx_id;
SELECT exchange_rate
INTO l_exchange_rate
FROM ap_invoices_v
WHERE invoice_id = p_application_ref_id;
select code_combination_id into l_rec_ccid from ra_Cust_trx_line_gl_dist
where customer_trx_id=l_cm_ps_rec.customer_trx_id
and account_class='REC'
and latest_rec_flag='Y';
arp_app_pkg.insert_p( l_inv_ra_rec,
l_receivable_application_id );
For rec in (select receivable_application_id source_id from ar_receivable_applications ra
where customer_trx_id = p_cust_Trx_id
and applied_customer_trx_id = p_app_cust_trx_id
and exists (select 'x' from ar_distributions
where source_id = ra.receivable_application_id
and source_table = 'RA'
and ref_customer_trx_line_id is not null
and ref_prev_cust_trx_line_id is null)) Loop
arp_debug.debug('Updating CM ard to stamp ref_prev_cust_trx_line_id');
update ar_distributions ard
set ref_prev_cust_trx_line_id = (select previous_customer_trx_line_id
from ra_customer_trx_lines
where customer_trx_line_id = ard.ref_customer_trx_line_id)
where source_id = rec.source_id
and source_table = 'RA'
and ref_customer_trx_line_id in (select customer_trx_line_id
from ra_customer_trx_lines ctl_cm,
ar_receivable_applications ra
where ra.receivable_application_id = rec.source_id
and ra.customer_trx_id = ctl_cm.customer_trx_id
and ctl_cm.previous_customer_trx_line_id is not null);
arp_debug.debug('CM ard rows updated : '||l_cnt);
update ar_distributions ard
set ref_prev_cust_trx_line_id = (select ref_customer_trx_line_id
from ar_distributions
where source_id = rec.source_id
and ref_prev_cust_trx_line_id = ard.ref_customer_trx_line_id
and rownum = 1)
where source_id = rec.source_id
and ref_customer_trx_line_id in (select customer_trx_line_id
from ra_customer_trx_lines ctl_inv,
ar_receivable_applications ra
where ra.receivable_application_id = rec.source_id
and ra.applied_customer_trx_id = ctl_inv.customer_trx_id);
arp_debug.debug('INV ard rows updated : '||l_cnt);
Update ra_customer_Trx
set previous_customer_Trx_id = NULL
where customer_Trx_id = p_cust_trx_id
and previous_customer_Trx_id = p_app_cust_trx_id;
update ra_customer_Trx_lines
set previous_customer_Trx_line_id = NULL,
previous_customer_Trx_id = NULL -- Fix for Bug 6726394 (customer_trx_id
where customer_Trx_id = p_cust_trx_id -- value has to be set to null in ra_customer_Trx_lines)
and previous_customer_Trx_id = p_app_cust_trx_id;
SELECT trx_number
INTO l_trx_number
FROM ra_customer_trx
WHERE customer_Trx_id = p_app_cust_Trx_id;
UPDATE ra_cust_trx_line_gl_dist
SET comments = comments || l_message_text
WHERE customer_trx_id = p_cust_trx_id
AND account_set_flag = 'N';
SELECT nvl(previous_customer_Trx_id, -999),
nvl(invoicing_rule_id, -999)
INTO l_prev_cust_trx_id,
p_invoicing_rule_id
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
SELECT trx.initial_customer_trx_id
FROM ra_customer_trx trx,
ar_adjustments adj
WHERE trx.customer_trx_id = p_cm_cust_trx_id
AND adj.customer_trx_id = p_inv_cust_trx_id
AND adj.subsequent_trx_id = p_cm_cust_trx_id; --Bug 14376534
select adj.adjustment_id
from ar_adjustments_all adj,
ra_customer_trx ra
where adj.customer_trx_id = p_inv_customer_trx_id
and adj.subsequent_trx_id = p_cm_customer_trx_id
and ra.initial_customer_trx_id = p_initial_customer_trx_id
and adj.customer_trx_id = ra.customer_trx_id;
arp_process_adjustment.insert_reverse_actions( l_adj_rec, NULL, NULL );
arp_ps_util.update_reverse_actions( l_app_rec, NULL, NULL );
Update ra_customer_Trx
set initial_customer_trx_id = NULL
where customer_Trx_id = p_cm_cust_trx_id
and initial_customer_trx_id = l_initial_customer_trx_id;