[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cr.receipt_number
, cr.receipt_date
, crh.gl_date
, cr.deposit_date
, cr.pay_from_customer
, ps.payment_schedule_id
, cr.currency_code
, cr.exchange_rate
, cr.exchange_rate_type
, cr.exchange_date
, cr.customer_site_use_id
INTO l_receipt_number
, l_receipt_date
, l_cr_gl_date
, l_deposit_date
, l_customer_id
, l_cr_payment_schedule_id
, l_currency_code
, l_exchange_rate
, l_exchange_rate_type
, l_exchange_rate_date
, l_bill_to_site_use_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_chargeback_rec.cash_receipt_id;
SELECT set_of_books_id
, default_cb_due_date
, nvl(site_required_flag,'N')
INTO l_set_of_books_id
, l_default_cb_due_date
, l_site_required_flag
FROM ar_system_parameters;
SELECT
app.receivable_application_id
, app.applied_payment_schedule_id -- bug 3682568
, DECODE(SIGN(app.applied_payment_schedule_id),-1,NULL,
ps.due_date) due_date -- Bug 3590399: removed to_date
, app.apply_date
, app.gl_date
, app.amount_applied
, app.acctd_amount_applied_from
, DECODE(SIGN(app.applied_payment_schedule_id),
-1,app.applied_payment_schedule_id,
app.applied_customer_trx_id)
, TO_NUMBER(DECODE(SIGN(app.applied_payment_schedule_id),-1,NULL,
ps.terms_sequence_number)) installment
, 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
l_application_id
, l_applied_ps_id -- bug 3682568
, l_app_due_date
, l_apply_date
, l_app_gl_date
, l_amount
, l_acctd_amount
, l_customer_trx_id
, l_installment
, l_application_ref_num
, l_application_ref_reason
, l_app_receivables_trx_id
, l_app_comments
, l_customer_reference
, l_attribute_rec.attribute_category
, l_attribute_rec.attribute1
, l_attribute_rec.attribute2
, l_attribute_rec.attribute3
, l_attribute_rec.attribute4
, l_attribute_rec.attribute5
, l_attribute_rec.attribute6
, l_attribute_rec.attribute7
, l_attribute_rec.attribute8
, l_attribute_rec.attribute9
, l_attribute_rec.attribute10
, l_attribute_rec.attribute11
, l_attribute_rec.attribute12
, l_attribute_rec.attribute13
, l_attribute_rec.attribute14
, l_attribute_rec.attribute15
, l_global_attribute_rec.global_attribute_category
, l_global_attribute_rec.global_attribute1
, l_global_attribute_rec.global_attribute2
, l_global_attribute_rec.global_attribute3
, l_global_attribute_rec.global_attribute4
, l_global_attribute_rec.global_attribute5
, l_global_attribute_rec.global_attribute6
, l_global_attribute_rec.global_attribute7
, l_global_attribute_rec.global_attribute8
, l_global_attribute_rec.global_attribute9
, l_global_attribute_rec.global_attribute10
, l_global_attribute_rec.global_attribute11
, l_global_attribute_rec.global_attribute12
, l_global_attribute_rec.global_attribute13
, l_global_attribute_rec.global_attribute14
, l_global_attribute_rec.global_attribute15
, l_global_attribute_rec.global_attribute16
, l_global_attribute_rec.global_attribute17
, l_global_attribute_rec.global_attribute18
, l_global_attribute_rec.global_attribute19
, l_global_attribute_rec.global_attribute20
FROM ar_payment_schedules ps
, ar_receivable_applications app
WHERE app.applied_payment_schedule_id = ps.payment_schedule_id
AND app.secondary_application_ref_id = p_chargeback_rec.secondary_application_ref_id
AND app.application_ref_type = p_chargeback_rec.application_ref_type
AND app.applied_payment_schedule_id = ps.payment_schedule_id
AND app.display = 'Y'
AND app.status IN ('APP','OTHER ACC')
AND app.cash_receipt_id = p_chargeback_rec.cash_receipt_id
AND ROWNUM = 1;
SELECT bill_to_customer_id,
bill_to_site_use_id,
trx_date
INTO l_customer_id,
l_bill_to_site_use_id ,
l_trx_date
FROM ra_customer_trx
WHERE customer_trx_id=l_customer_trx_id;
SELECT site_uses.site_use_id
INTO l_bill_to_site_use_id
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses site_uses
WHERE acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND site_uses.site_use_code in ('BILL_TO','DRAWEE')
AND acct_site.cust_account_id = l_customer_id
AND site_uses.site_use_id=l_bill_to_site_use_id;
SELECT name,
gl_id_rec
INTO l_cb_trx_type,
l_default_code_combination_id
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_chargeback_rec.cust_trx_type_id
AND type = 'CB'
AND NVL(status,'A') = 'A'
AND post_to_gl = 'Y'
AND accounting_affect_flag = 'Y'
AND (l_receipt_date BETWEEN start_date AND nvl(end_date+1,l_receipt_date));
SELECT legal_entity_id
INTO l_legal_entity_id
FROM XLE_FIRSTPARTY_INFORMATION_V LE
WHERE LE.legal_entity_id = p_chargeback_rec.legal_entity_id;
| B) Insert new records |
+---------------------------------------------------------------------*/
-- Bug 3682568 lock the receipt or ps before calling the entity handlers
IF SIGN(l_customer_trx_id) = -1
THEN
BEGIN
arp_cash_receipts_pkg.nowaitlock_p(p_cr_id => p_chargeback_rec.cash_receipt_id);
| 1) Chargeback against a transaction so update amount in dispute |
+---------------------------------------------------------------------*/
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('create_chargeback: ' || 'Updating amount in dispute... ');
arp_deduction_cover.update_amount_in_dispute(
p_customer_trx_id => l_customer_trx_id,
p_claim_number => l_application_ref_num,
p_amount => (p_chargeback_rec.amount * -1),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
arp_standard.debug('create_chargeback: ' || 'Before Inserting Claim Application (+)');
/* Bug 2821139 - under no circumstances should AR update claims when
they are settled from TM */
arp_process_application.other_account_application (
-- *** IN
p_receipt_ps_id => l_cr_payment_schedule_id
, p_amount_applied => l_balance
, p_apply_date => l_apply_date
, p_gl_date => l_default_gl_date -- bug 2621114
, p_receivables_trx_id => l_app_receivables_trx_id
, p_applied_ps_id => -4 /* Claim Investigation */
, p_ussgl_transaction_code => p_chargeback_rec.default_ussgl_transaction_code
, p_application_ref_type => 'CLAIM'
, p_application_ref_id => NULL
, p_application_ref_num => l_new_claim_num
, p_secondary_application_ref_id => p_chargeback_rec.new_second_application_ref_id
, p_comments => l_app_comments
, p_attribute_category=> l_attribute_rec.attribute_category
, p_attribute1 => l_attribute_rec.attribute1
, p_attribute2 => l_attribute_rec.attribute2
, p_attribute3 => l_attribute_rec.attribute3
, p_attribute4 => l_attribute_rec.attribute4
, p_attribute5 => l_attribute_rec.attribute5
, p_attribute6 => l_attribute_rec.attribute6
, p_attribute7 => l_attribute_rec.attribute7
, p_attribute8 => l_attribute_rec.attribute8
, p_attribute9 => l_attribute_rec.attribute9
, p_attribute10 => l_attribute_rec.attribute10
, p_attribute11 => l_attribute_rec.attribute11
, p_attribute12 => l_attribute_rec.attribute12
, p_attribute13 => l_attribute_rec.attribute13
, p_attribute14 => l_attribute_rec.attribute14
, p_attribute15 => l_attribute_rec.attribute15
, p_global_attribute_category => l_global_attribute_rec.global_attribute_category
, p_global_attribute1 => l_global_attribute_rec.global_attribute1
, p_global_attribute2 => l_global_attribute_rec.global_attribute2
, p_global_attribute3 => l_global_attribute_rec.global_attribute3
, p_global_attribute4 => l_global_attribute_rec.global_attribute4
, p_global_attribute5 => l_global_attribute_rec.global_attribute5
, p_global_attribute6 => l_global_attribute_rec.global_attribute6
, p_global_attribute7 => l_global_attribute_rec.global_attribute7
, p_global_attribute8 => l_global_attribute_rec.global_attribute8
, p_global_attribute9 => l_global_attribute_rec.global_attribute9
, p_global_attribute10 =>l_global_attribute_rec.global_attribute10
, p_global_attribute11 =>l_global_attribute_rec.global_attribute11
, p_global_attribute12 =>l_global_attribute_rec.global_attribute12
, p_global_attribute13 =>l_global_attribute_rec.global_attribute13
, p_global_attribute14 =>l_global_attribute_rec.global_attribute14
, p_global_attribute15 =>l_global_attribute_rec.global_attribute15
, p_global_attribute16 =>l_global_attribute_rec.global_attribute16
, p_global_attribute17 =>l_global_attribute_rec.global_attribute17
, p_global_attribute18 =>l_global_attribute_rec.global_attribute18
, p_global_attribute19 =>l_global_attribute_rec.global_attribute19
, p_global_attribute20 =>l_global_attribute_rec.global_attribute20
, p_module_name => 'ARXRWAPP'
, p_module_version => '1.0'
, p_payment_set_id => l_payment_set_id
-- *** OUT NOCOPY
, x_application_ref_id => l_new_app_ref_id
, x_application_ref_num => l_new_app_ref_num
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_out_rec_application_id => l_new_application_id
, p_application_ref_reason => l_application_ref_reason
, p_customer_reference => l_customer_reference
, x_claim_reason_name => l_claim_reason_name
, p_called_from => 'TRADE_MANAGEMENT'
);
arp_standard.debug('create_chargeback: ' || 'After Inserting Claim Application (-)');