[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO x_approved_amount
FROM ar_approval_user_limits araul,
gl_sets_of_books glsb,
ar_system_parameters arsp
WHERE araul.user_id = x_user_id
AND araul.document_type = 'ADJ'
AND glsb.set_of_books_id = arsp.set_of_books_id
AND araul.currency_code = glsb.currency_code
AND araul.amount_to >= x_amount
AND araul.amount_from <= x_amount;
SELECT receivable_application_id
INTO l_rec_appln_id
FROM ar_receivable_applications_all
WHERE cash_receipt_id = x_cash_receipt_id
AND applied_payment_schedule_id = x_payment_schedule_id;
SELECT trx_date
INTO x_trans_date
FROM ra_customer_trx
WHERE customer_trx_id = x_customer_trx_id;
SELECT loc.city,
loc.state,
arps.org_id
INTO x_city,
x_state,
x_org_id
FROM hz_locations loc,
hz_cust_acct_sites ad,
hz_party_sites pty,
ra_customer_trx ract,
hz_cust_site_uses rasu,
ar_payment_schedules arps,
jl_br_ar_collection_docs dc
WHERE dc.payment_schedule_id = x_payment_schedule_id
AND arps.payment_schedule_id = dc.payment_schedule_id
AND ract.customer_trx_id = arps.customer_trx_id
AND rasu.site_use_id = ract.bill_to_site_use_id
AND ad.cust_acct_site_id = rasu.cust_acct_site_id
AND ad.party_site_id = pty.party_site_id
AND loc.location_id = pty.location_id;
SELECT distinct nvl(arrma.floating,0)
INTO x_floating
FROM ar_receipt_methods arrm,
ar_system_parameters arsp,
ar_payment_schedules arps,
jl_br_ar_collection_docs dc,
jl_br_ar_rec_met_accts_ext arrma
WHERE dc.payment_schedule_id = x_payment_schedule_id
AND arps.payment_schedule_id = dc.payment_schedule_id
AND arrma.bank_account_id = dc.bank_account_id
AND arrma.receipt_method_id = dc.receipt_method_id
AND arrm.receipt_method_id = arrma.receipt_method_id;
SELECT x_receipt_date + x_floating
INTO x_rec_date
FROM dual;
select to_char(x_rec_date,'DD-MM-YYYY')
into x_rec_date_char
from dual;
select to_date(x_rec_workdate_char,'DD-MM-YYYY')
into x_rec_workdate
from dual;
SELECT x_user_id,
sysdate,
x_user_id,
x_user_id,
sysdate,
x_amount,
sysdate,
-- bug 12742020 x_rcpt_out,
x_rec_workdate,
arsp.set_of_books_id,
x_acc_id,
'CHARGES',
decode(x_approved_amount,'Y','A','M'),
decode(x_approved_amount,'Y','A','W'),
x_cash_receipt_id,
x_customer_trx_id,
x_payment_schedule_id,
x_rectrx_id,
'ARXCAERA',
decode(x_approved_amount,'Y','Y','N'),
decode(x_approved_amount,'Y',x_user_id,NULL),
-3,
x_amount,
l_rec_appln_id, --bug 12323016
org_id
INTO adj_rec.LAST_UPDATED_BY,
adj_rec.LAST_UPDATE_DATE,
adj_rec.LAST_UPDATE_LOGIN,
adj_rec.CREATED_BY,
adj_rec.CREATION_DATE,
adj_rec.AMOUNT,
adj_rec.APPLY_DATE,
adj_rec.GL_DATE,
adj_rec.SET_OF_BOOKS_ID,
adj_rec.CODE_COMBINATION_ID,
adj_rec.TYPE,
adj_rec.ADJUSTMENT_TYPE,
adj_rec.STATUS,
adj_rec.ASSOCIATED_CASH_RECEIPT_ID,
adj_rec.CUSTOMER_TRX_ID,
adj_rec.PAYMENT_SCHEDULE_ID,
adj_rec.RECEIVABLES_TRX_ID,
adj_rec.CREATED_FROM,
adj_rec.POSTABLE,
adj_rec.APPROVED_BY,
adj_rec.POSTING_CONTROL_ID,
adj_rec.ACCTD_AMOUNT,
adj_rec.ASSOCIATED_APPLICATION_ID, --bug 12323016
adj_rec.ORG_ID
FROM ar_system_parameters arsp;
SELECT writeoff_perc_tolerance,
writeoff_amount_tolerance,
interest_writeoff_rectrx_id,
interest_writeoff_ccid,
interest_revenue_rectrx_id,
interest_revenue_ccid,
calculated_interest_ccid,
calculated_interest_rectrx_id
INTO x_writeoff_tolerance,
x_writeoff_amount,
x_writeoff_rectrx_id,
x_writeoff_ccid,
x_int_revenue_rectrx_id,
x_int_revenue_ccid,
x_calc_interest_ccid,
x_calc_interest_rectrx_id
FROM jl_br_ar_rec_met_accts_ext
WHERE receipt_method_id = x_rcpt_method_id
AND bank_acct_use_id = x_bank_acct_id;
SELECT amount_due_original, amount_due_remaining
INTO x_amount_due_original, x_amount_due_remaining
FROM ar_payment_schedules
WHERE payment_schedule_id = x_payment_schedule_id;
SELECT org_id
INTO x_org_id
FROM ar_payment_schedules
WHERE payment_schedule_id = p_applied_payment_schedule_id;
SELECT amount_due_remaining,
global_attribute7
INTO x_amount_due_remaining,
p_global_attribute11
FROM ar_payment_schedules
WHERE payment_schedule_id = p_applied_payment_schedule_id;
/* UPDATE ar_payment_schedules
SET global_attribute1 = p_global_attribute1,
global_attribute2 = p_global_attribute2,
global_attribute3 = p_global_attribute3,
global_attribute4 = p_global_attribute4,
global_attribute5 = p_global_attribute5,
global_attribute6 = p_global_attribute6,
global_attribute7 = nvl(p_global_attribute7,p_global_attribute11),
global_attribute15 = p_global_attribute8
WHERE payment_schedule_id = p_applied_payment_schedule_id;
/* Replace Update by AR's table handlers. Bug # 2249731 */
arp_ps_pkg.fetch_p(p_applied_payment_schedule_id, l_ps_rec);
arp_ps_pkg.update_p(l_ps_rec, p_applied_payment_schedule_id);
SELECT apply_date
INTO x_apply_date
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cash_receipt_id
AND applied_payment_schedule_id = p_applied_payment_schedule_id;
/* UPDATE ar_payment_schedules
SET global_attribute1 = x_main_amnt_rec,
global_attribute2 = x_base_int_calc,
global_attribute3 = x_calculated_interest,
global_attribute4 = x_received_interest,
global_attribute5 = x_int_diff_action,
global_attribute6 = x_int_writeoff_reason,
global_attribute7 = x_payment_date,
global_attribute15 = x_writeoff_date
WHERE payment_schedule_id = p_applied_payment_schedule_id;
/* Replace Update by AR's table handlers. Bug # 2249731 */
arp_ps_pkg.fetch_p(p_applied_payment_schedule_id, l_ps_rec);
arp_ps_pkg.update_p(l_ps_rec, p_applied_payment_schedule_id);
Select applied_payment_schedule_id pay_sched_id,
global_attribute3 calculated_interest,
apply_date
from ar_receivable_applications
where status = 'APP'
and cash_receipt_id = p_cash_receipt_id;
/* UPDATE ar_payment_schedules
SET global_attribute1 = x_main_amnt_rec,
global_attribute2 = x_base_int_calc,
global_attribute3 = x_calculated_interest,
global_attribute4 = x_received_interest,
global_attribute5 = x_int_diff_action,
global_attribute6 = x_int_writeoff_reason,
global_attribute7 = x_payment_date,
global_attribute15 = x_writeoff_date
WHERE payment_schedule_id = ps_rec.pay_sched_id;
/* Replace Update by AR's table handlers. Bug # 2249731 */
arp_ps_pkg.fetch_p(ps_rec.pay_sched_id, l_ps_rec);
arp_ps_pkg.update_p(l_ps_rec, ps_rec.pay_sched_id);
select ara.receivable_application_id,
acr.cash_receipt_id,
ara.applied_payment_schedule_id,
acr.receipt_method_id,
acr.receipt_date,
acr.remit_bank_acct_use_id,
aicl.global_attribute1,
aicl.global_attribute2,
aicl.global_attribute3,
aicl.global_attribute4,
aicl.global_attribute5,
aicl.global_attribute6,
aicl.global_attribute7,
aicl.global_attribute8,
aicl.global_attribute10
from ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
ar_interim_cash_rcpt_lines_all aicl
--ar_interim_cash_receipts_all aic
where ara.request_id = p_request_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND ara.cash_receipt_id = aicl.cash_receipt_id
AND ara.applied_payment_schedule_id = aicl.payment_schedule_id
AND ara.global_attribute_category is NULL;
select ara.cash_receipt_id,
ara.applied_payment_schedule_id
from ar_receivable_applications_all ara
where request_id = p_request_id;
update ar_receivable_applications_all
set global_attribute1 = rint.global_attribute3 ,
global_attribute2 = rint.global_attribute4 ,
global_attribute3 = rint.global_attribute1 ,
global_attribute4 = rint.global_attribute2 ,
global_attribute7 = rint.global_attribute5 ,
global_attribute9 = rint.global_attribute6 ,
global_attribute10 = rint.global_attribute7,
global_attribute11 = rint.global_attribute8,
global_attribute12 = rint.global_attribute10,
global_attribute_category = 'JL.BR.ARXRWMAI.Additional Info'
where receivable_application_id = rint.receivable_application_id;
fnd_file.put_line(fnd_file.log,'After update statement()+');
SELECT ract.cust_trx_type_id,
ract.batch_source_id,
arps.due_date,
nvl(arps.amount_due_remaining,0),
ract.customer_trx_id,
arps.amount_applied
INTO
l_trx_type_id,
l_batch_source_id,
l_due_date,
l_amount_due_remaining,
l_cust_trx_id,
l_payment_amount
FROM ra_customer_trx ract,
ar_payment_schedules arps
WHERE arps.payment_schedule_id = rint.applied_payment_schedule_id
AND ract.customer_trx_id = arps.customer_trx_id;
PROCEDURE delete_interest_adjustment (
p_cash_receipt_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
delete from ar_adjustments_all
where associated_cash_receipt_id = p_cash_receipt_id;
END delete_interest_adjustment;