[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PUBLIC PROCEDURE update_amount_in_dispute
|
| DESCRIPTION
| ----------------------------------------
| This procedure calls entity handlers to update the amount_in_dispute
| on the given transaction's payment schedule and inserts a note
| on the transaction
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| p_customer_trx_id IN Transaction whose dispute amount is changed
| p_claim_number IN Number of claim
| p_amount IN Amount of adjustment to dispute amount
| p_init_msg_list IN API message stack initialize flag
| x_return_status OUT NOCOPY
| x_msg_count OUT NOCOPY
| x_msg_data OUT NOCOPY
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 12-OCT-2001 jbeckett Created
|
*=======================================================================*/
PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
PROCEDURE update_amount_in_dispute(
p_customer_trx_id IN NUMBER,
p_claim_number IN VARCHAR2,
p_amount IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_ps_rec ar_payment_schedules%ROWTYPE;
:= 'update_amount_in_dispute';
arp_standard.debug('ARP_DEDUCTION_COVER.update_amount_in_dispute()+');
SAVEPOINT Update_amount_In_Dispute;
arp_standard.debug('update_amount_in_dispute: ' || ' p_customer_trx_id :' || p_customer_trx_id);
arp_standard.debug('update_amount_in_dispute: ' || ' p_claim_number :' || p_claim_number);
arp_standard.debug('update_amount_in_dispute: ' || ' p_amount :' || p_amount);
SELECT applied_payment_schedule_id
, secondary_application_ref_id
INTO l_payment_schedule_id
, l_claim_id
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_customer_trx_id
AND application_ref_num = p_claim_number
AND application_ref_type = 'CLAIM'
AND display = 'Y'
AND ROWNUM = 1;
| 2) Update payment schedule record |
+---------------------------------------------------------------------*/
IF (l_payment_schedule_id > 0) THEN
IF (ARPT_SQL_FUNC_UTIL.get_claim_amount(l_claim_id) = 0) THEN
l_active_claim_flag := 'N';
SELECT amount_in_dispute
INTO l_amount_in_dispute
FROM ar_payment_schedules
WHERE payment_schedule_id = l_payment_schedule_id;
arp_standard.debug('update_amount_in_dispute: ' || 'New amount in dispute = '||l_ps_rec.amount_in_dispute);
arp_standard.debug('update_amount_in_dispute: ' || 'New dispute date = '||l_ps_rec.dispute_date);
arp_ps_pkg.update_p
(p_ps_rec => l_ps_rec,
p_ps_id => l_payment_schedule_id);
arp_cmreq_wf.InsertTrxNotes
(x_customer_call_id => NULL,
x_customer_call_topic_id => NULL,
x_action_id => NULL,
x_customer_trx_id => p_customer_trx_id,
x_note_type => 'MAINTAIN',
x_text => l_note_text,
x_note_id => l_note_id);
arp_standard.debug('ARP_DEDUCTION_COVER.update_amount_in_dispute()-');
ROLLBACK TO Update_Amount_In_Dispute;
arp_standard.debug('update_amount_in_dispute: ' || 'Unexpected error '||sqlerrm||
' at arp_deduction_cover.update_amount_in_dispute()+');
ROLLBACK TO Update_Amount_In_Dispute;
arp_util.debug('update_amount_in_dispute: ' || '20001 error '||
' at arp_deduction_cover.update_amount_in_dispute()+');
arp_util.debug('update_amount_in_dispute: ' || 'Unexpected error '||sqlerrm||
' at arp_deduction_cover.update_amount_in_dispute()+');
ROLLBACK TO Update_Amount_In_Dispute;
END update_amount_in_dispute;
SELECT receivables_trx_id
INTO l_receivables_trx_id
FROM ar_receivables_trx
WHERE receivables_trx_id = p_receivables_trx_id
AND NVL(status,'A') = 'A'
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,TRUNC(SYSDATE))
AND NVL(end_date_active,trunc(SYSDATE))
AND type = 'WRITEOFF';
/* Bug 2821139 - under no circumstances should AR update claims when
they are settled from TM */
IF l_claim_applied = 'Y' THEN
AR_Receipt_API_PUB.unapply_other_account (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_receivable_application_id => l_application_id,
p_cash_receipt_id => p_cash_receipt_id,
p_cancel_claim_flag => 'N',
p_called_from => 'TRADE_MANAGEMENT');
arp_standard.debug('create_receipt_writeoff: ' || 'Before Inserting Claim Application (+)');
/* Bug 2821139 - under no circumstances should AR update claims when
they are settled from TM */
ar_receipt_api_pub.apply_other_account (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_receivable_application_id => l_receivable_application_id,
p_cash_receipt_id => p_cash_receipt_id,
p_amount_applied => l_balance,
p_receivables_trx_id => l_claim_receivables_trx_id,
p_apply_date => l_apply_date,
p_apply_gl_date => l_default_gl_date, -- bug 2615618
p_applied_payment_schedule_id => -4,
p_ussgl_transaction_code => p_ussgl_transaction_code,
p_application_ref_type => 'CLAIM',
p_application_ref_id => l_application_ref_id,
p_application_ref_num => l_new_claim_num,
p_secondary_application_ref_id => l_new_claim_id,
p_attribute_rec => l_attribute_rec,
p_global_attribute_rec => l_global_attribute_rec,
p_comments => l_comments,
p_application_ref_reason => l_application_ref_reason,
p_customer_reference => l_customer_reference,
p_called_from => 'TRADE_MANAGEMENT');
arp_standard.debug('create_receipt_writeoff: ' || 'After Inserting Claim Application (-)');
select receivable_application_id from ar_receivable_applications_all
where cash_receipt_id = p_cash_rcpt_id
and applied_customer_trx_id = p_ctx_id
and status = 'APP'
and reversal_gl_date IS NULL
order by amount_applied desc;
SELECT sum(rec.amount_applied) amt_applied,
sum(rec.earned_discount_taken) earned_discount_taken,
sum(rec.unearned_discount_taken) unearned_discount_taken
FROM ar_receivable_applications rec
, ar_payment_schedules pay
WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
AND rec.cash_receipt_id = cv_cash_receipt_id
AND pay.customer_trx_id = cv_customer_trx_id
AND rec.application_ref_type = 'CLAIM'
AND rec.display = 'Y'
AND rec.secondary_application_ref_id = cv_claim_id;
SELECT ps.payment_schedule_id
, ps.invoice_currency_code
, ps.terms_sequence_number
INTO l_applied_ps_id
, l_trx_currency_code
, l_installment
FROM ar_payment_schedules ps,
ar_receivable_applications ra
WHERE ps.payment_schedule_id = ra.applied_payment_schedule_id
AND ra.application_ref_type = 'CLAIM'
AND ra.secondary_application_ref_id = p_claim_id
AND ra.cash_receipt_id = p_cash_receipt_id
AND ra.status = 'APP'
AND ps.customer_trx_id = p_customer_trx_id
AND ROWNUM = 1;
UPDATE ar_adjustments
SET associated_cash_receipt_id = associated_cash_receipt_id * -1
WHERE associated_cash_receipt_id = p_cash_receipt_id
AND payment_schedule_id = l_applied_ps_id;
/* Bug 2821139 - under no circumstances should AR update claims when
they are settled from TM */
IF l_claim_applied = 'Y' THEN
IF p_customer_trx_id IS NOT NULL
THEN
FOR rec_app_id IN get_app_ids(p_cash_receipt_id, p_customer_trx_id) LOOP
AR_Receipt_API_PUB.Unapply(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_called_from => 'TRADE_MANAGEMENT',
p_cancel_claim_flag => 'N',
p_receivable_application_id => rec_app_id.receivable_application_id);
UPDATE ar_adjustments
SET associated_cash_receipt_id = associated_cash_receipt_id * -1
WHERE associated_cash_receipt_id = p_cash_receipt_id * -1
AND payment_schedule_id = l_applied_ps_id;
arp_standard.debug('split_claim_reapplication: ' || 'Before Inserting Claim Application (+)');
/* Bug 2821139 - under no circumstances should AR update claims when
they are settled from TM */
l_claim_id := p_claim_id;
UPDATE ar_payment_schedules
SET active_claim_flag = 'N'
WHERE payment_schedule_id = l_applied_ps_id;
arp_standard.debug('split_claim_reapplication: ' || 'After Inserting Claim Application (-)');
SELECT cr.receipt_number
, cr.receipt_date
, crh.gl_date
, cr.pay_from_customer
, cr.currency_code
, ps.payment_schedule_id
INTO x_receipt_number
, x_receipt_date
, x_cr_gl_date
, x_customer_id
, x_currency_code
, x_cr_payment_schedule_id
FROM ar_cash_receipts cr
, ar_payment_schedules ps
, ar_cash_receipt_history crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id(+)
AND crh.first_posted_record_flag(+) = 'Y'
AND cr.cash_receipt_id = ps.cash_receipt_id(+)
AND cr.cash_receipt_id = p_cash_receipt_id;
SELECT
app.receivable_application_id
, app.apply_date
, app.gl_date
, app.amount_applied
, app.trans_to_receipt_rate
, app.earned_discount_taken
, app.unearned_discount_taken
, app.application_ref_num
, app.application_ref_reason
, app.receivables_trx_id
, app.comments
, app.customer_reference
, app.attribute_category
, app.attribute1
, app.attribute2
, app.attribute3
, app.attribute4
, app.attribute5
, app.attribute6
, app.attribute7
, app.attribute8
, app.attribute9
, app.attribute10
, app.attribute11
, app.attribute12
, app.attribute13
, app.attribute14
, app.attribute15
, app.global_attribute_category
, app.global_attribute1
, app.global_attribute2
, app.global_attribute3
, app.global_attribute4
, app.global_attribute5
, app.global_attribute6
, app.global_attribute7
, app.global_attribute8
, app.global_attribute9
, app.global_attribute10
, app.global_attribute11
, app.global_attribute12
, app.global_attribute13
, app.global_attribute14
, app.global_attribute15
, app.global_attribute16
, app.global_attribute17
, app.global_attribute18
, app.global_attribute19
, app.global_attribute20
INTO
x_application_id
, x_apply_date
, x_app_gl_date
, x_amount_applied
, x_trans_to_receipt_rate
, x_discount_earned
, x_discount_unearned
, x_application_ref_num
, x_application_ref_reason
, x_receivables_trx_id
, x_comments
, x_customer_reference
, x_attribute_rec.attribute_category
, x_attribute_rec.attribute1
, x_attribute_rec.attribute2
, x_attribute_rec.attribute3
, x_attribute_rec.attribute4
, x_attribute_rec.attribute5
, x_attribute_rec.attribute6
, x_attribute_rec.attribute7
, x_attribute_rec.attribute8
, x_attribute_rec.attribute9
, x_attribute_rec.attribute10
, x_attribute_rec.attribute11
, x_attribute_rec.attribute12
, x_attribute_rec.attribute13
, x_attribute_rec.attribute14
, x_attribute_rec.attribute15
, x_global_attribute_rec.global_attribute_category
, x_global_attribute_rec.global_attribute1
, x_global_attribute_rec.global_attribute2
, x_global_attribute_rec.global_attribute3
, x_global_attribute_rec.global_attribute4
, x_global_attribute_rec.global_attribute5
, x_global_attribute_rec.global_attribute6
, x_global_attribute_rec.global_attribute7
, x_global_attribute_rec.global_attribute8
, x_global_attribute_rec.global_attribute9
, x_global_attribute_rec.global_attribute10
, x_global_attribute_rec.global_attribute11
, x_global_attribute_rec.global_attribute12
, x_global_attribute_rec.global_attribute13
, x_global_attribute_rec.global_attribute14
, x_global_attribute_rec.global_attribute15
, x_global_attribute_rec.global_attribute16
, x_global_attribute_rec.global_attribute17
, x_global_attribute_rec.global_attribute18
, x_global_attribute_rec.global_attribute19
, x_global_attribute_rec.global_attribute20
FROM ar_receivable_applications app
WHERE app.secondary_application_ref_id = p_claim_id
AND app.applied_payment_schedule_id + 0 = p_applied_ps_id
AND app.application_ref_type = 'CLAIM'
AND app.display = 'Y'
AND app.status = DECODE(p_applied_ps_id,-4,'OTHER ACC','APP')
AND app.cash_receipt_id = p_cash_receipt_id
AND ROWNUM = 1;
SELECT
app.receivable_application_id
, app.apply_date
, app.gl_date
, app.amount_applied
, app.trans_to_receipt_rate
, app.earned_discount_taken
, app.unearned_discount_taken
, app.application_ref_num
, app.application_ref_reason
, app.receivables_trx_id
, app.comments
, app.customer_reference
, app.attribute_category
, app.attribute1
, app.attribute2
, app.attribute3
, app.attribute4
, app.attribute5
, app.attribute6
, app.attribute7
, app.attribute8
, app.attribute9
, app.attribute10
, app.attribute11
, app.attribute12
, app.attribute13
, app.attribute14
, app.attribute15
, app.global_attribute_category
, app.global_attribute1
, app.global_attribute2
, app.global_attribute3
, app.global_attribute4
, app.global_attribute5
, app.global_attribute6
, app.global_attribute7
, app.global_attribute8
, app.global_attribute9
, app.global_attribute10
, app.global_attribute11
, app.global_attribute12
, app.global_attribute13
, app.global_attribute14
, app.global_attribute15
, app.global_attribute16
, app.global_attribute17
, app.global_attribute18
, app.global_attribute19
, app.global_attribute20
INTO
x_application_id
, x_apply_date
, x_app_gl_date
, x_amount_applied
, x_trans_to_receipt_rate
, x_discount_earned
, x_discount_unearned
, x_application_ref_num
, x_application_ref_reason
, x_receivables_trx_id
, x_comments
, x_customer_reference
, x_attribute_rec.attribute_category
, x_attribute_rec.attribute1
, x_attribute_rec.attribute2
, x_attribute_rec.attribute3
, x_attribute_rec.attribute4
, x_attribute_rec.attribute5
, x_attribute_rec.attribute6
, x_attribute_rec.attribute7
, x_attribute_rec.attribute8
, x_attribute_rec.attribute9
, x_attribute_rec.attribute10
, x_attribute_rec.attribute11
, x_attribute_rec.attribute12
, x_attribute_rec.attribute13
, x_attribute_rec.attribute14
, x_attribute_rec.attribute15
, x_global_attribute_rec.global_attribute_category
, x_global_attribute_rec.global_attribute1
, x_global_attribute_rec.global_attribute2
, x_global_attribute_rec.global_attribute3
, x_global_attribute_rec.global_attribute4
, x_global_attribute_rec.global_attribute5
, x_global_attribute_rec.global_attribute6
, x_global_attribute_rec.global_attribute7
, x_global_attribute_rec.global_attribute8
, x_global_attribute_rec.global_attribute9
, x_global_attribute_rec.global_attribute10
, x_global_attribute_rec.global_attribute11
, x_global_attribute_rec.global_attribute12
, x_global_attribute_rec.global_attribute13
, x_global_attribute_rec.global_attribute14
, x_global_attribute_rec.global_attribute15
, x_global_attribute_rec.global_attribute16
, x_global_attribute_rec.global_attribute17
, x_global_attribute_rec.global_attribute18
, x_global_attribute_rec.global_attribute19
, x_global_attribute_rec.global_attribute20
FROM ar_receivable_applications app
WHERE app.secondary_application_ref_id = p_claim_id
AND app.applied_payment_schedule_id + 0 = p_applied_ps_id
AND app.application_ref_type = 'CLAIM'
AND app.status = DECODE(p_applied_ps_id,-4,'OTHER ACC','APP')
AND app.cash_receipt_id = p_cash_receipt_id
AND ROWNUM = 1;
l_query := ' select claim_number '||
' from ozf_ar_deductions_v '||
' where claim_id = :claim_id '||
' and receipt_id = :receipt_id '||
' and currency_code = :currency_code ';
SELECT SUM(NVL(ra.amount_applied,0))
INTO l_unapplied_total
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.status = 'UNAPP'
AND NVL(ra.confirmed_flag,'Y') = 'Y';