[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sum(decode(ctlgd.account_class,
'REC', nvl(amount,0) * -1,
nvl(amount,0))) net_amount,
sum(decode(ctlgd.account_class,
'REC', nvl(acctd_amount,0) * -1,
nvl(acctd_amount,0))) net_acctd_amount,
gl_date
from ra_cust_trx_line_gl_dist ctlgd
where ctlgd.customer_trx_id = p_cust_trx_id
and ctlgd.account_set_flag = 'N'
and ctlgd.posting_control_id = -3
and not exists (select 'x'
from ra_customer_trx_lines ctl
where ctl.customer_trx_id = p_customer_trx_id
and ctl.autorule_complete_flag||'' = 'N'
group by ctl.customer_trx_id)
group by customer_trx_id, gl_date;
SELECT NVL(gen_line_level_bal_flag,'N'),
NVL(rt.complete_flag,'N')
INTO l_gen_line_level_bal_flag,
l_complete_flag_db
FROM ra_batch_sources ra, ra_customer_trx rt
WHERE ra.batch_source_id = rt.batch_source_id
AND rt.customer_trx_id = p_customer_trx_id;
select sum(nvl(amount_dr,0)) sum_amount_dr,
sum(nvl(amount_cr,0)) sum_amount_cr,
sum(nvl(acctd_amount_dr,0)) sum_acctd_amount_dr,
sum(nvl(acctd_amount_cr,0)) sum_acctd_amount_cr,
max(src.cc_flag) cc_flag, src.gl_date
from (select cash_receipt_id cr_id,
misc_cash_distribution_id src_id,
'MCD' src_tab,
'N' cc_flag,
gl_date
from ar_misc_cash_distributions
where cash_receipt_id = p_cr_id
and posting_control_id = -3
UNION ALL
select /*+ index(CRH AR_CASH_RECEIPT_HISTORY_N1) */
cash_receipt_id cr_id,
cash_receipt_history_id src_id,
'CRH' src_tab,
'N' cc_flag,
gl_date
from ar_cash_receipt_history
where cash_receipt_id = p_cr_id
and posting_control_id = -3
and nvl(postable_flag,'Y') = 'Y'
UNION ALL
select /*+ index(RA AR_RECEIVABLE_APPLICATIONS_N1) */
cash_receipt_id cr_id,
receivable_application_id src_id,
'RA' src_tab,
decode(amount_applied_from,NULL,
'N',
'Y') cc_flag,
gl_date
from ar_receivable_applications
where cash_receipt_id = p_cr_id
and nvl(confirmed_flag,'Y') = 'Y'
and nvl(postable,'Y') = 'Y'
and posting_control_id = -3) src,
ar_distributions ard
where ard.source_id = src.src_id
and ard.source_table = src.src_tab
group by src.gl_date;
select sum(nvl(amount_dr,0)) sum_amount_dr,
sum(nvl(amount_cr,0)) sum_amount_cr,
sum(nvl(acctd_amount_dr,0)) sum_acctd_amount_dr,
sum(nvl(acctd_amount_cr,0)) sum_acctd_amount_cr,
max(src.cc_flag) cc_flag, src.gl_date, src.cr_id
from ( select mcd.cash_receipt_id cr_id,
mcd.misc_cash_distribution_id src_id,
'MCD' src_tab,
'N' cc_flag,
mcd.gl_date
from ar_cash_receipts cr, ar_misc_cash_distributions mcd
where cr.cash_receipt_id between p_cr_id_low and p_cr_id_high
and cr.cash_receipt_id = mcd.cash_receipt_id
and mcd.posting_control_id = -3
UNION ALL
select crh.cash_receipt_id cr_id,
crh.cash_receipt_history_id src_id,
'CRH' src_tab,
'N' cc_flag,
crh.gl_date
from ar_cash_receipts cr, ar_cash_receipt_history crh
where cr.cash_receipt_id between p_cr_id_low and p_cr_id_high
and cr.cash_receipt_id = crh.cash_receipt_id
and crh.posting_control_id = -3
and nvl(crh.postable_flag,'Y') = 'Y'
UNION ALL
select ra.cash_receipt_id cr_id,
ra.receivable_application_id src_id,
'RA' src_tab,
decode(ra.amount_applied_from,NULL,
'N',
'Y') cc_flag,
ra.gl_date
from ar_cash_receipts cr, ar_receivable_applications ra
where cr.cash_receipt_id between p_cr_id_low and p_cr_id_high
and cr.cash_receipt_id = ra.cash_receipt_id
and nvl(ra.confirmed_flag,'Y') = 'Y'
and nvl(ra.postable,'Y') = 'Y'
and ra.posting_control_id = -3) src,
ar_distributions ard
where ard.source_id = src.src_id
and ard.source_table = src.src_tab
group by src.gl_date, src.cr_id
having ((sum(nvl(amount_dr,0)) <> sum(nvl(amount_cr,0)) AND max(src.cc_flag) = 'N')
OR (sum(nvl(acctd_amount_dr,0)) <> sum(nvl(acctd_amount_cr,0))))
order by src.cr_id;
select sum(nvl(amount_dr,0)), sum(nvl(amount_cr,0)),
sum(nvl(acctd_amount_dr,0)), sum(nvl(acctd_amount_cr,0))
into l_amt_dr, l_amt_cr, l_acctd_amt_dr, l_acctd_amt_cr
from (select adjustment_id src_id,
'ADJ' src_tab
from ar_adjustments
where adjustment_id = p_adj_id
and nvl(postable,'Y') = 'Y'
and posting_control_id = -3) src,
ar_distributions ard
where ard.source_id = src.src_id
and ard.source_table = src.src_tab;
select nvl(amount,0), nvl(line_adjusted,0)+nvl(tax_adjusted,0)+
nvl(freight_adjusted,0)+nvl(receivables_charges_adjusted,0),
status
into l_amount, l_calc_amount,l_status
from ar_adjustments
where adjustment_id = p_adj_id;
select sum(nvl(amount_dr,0)), sum(nvl(amount_cr,0)),
sum(nvl(acctd_amount_dr,0)), sum(nvl(acctd_amount_cr,0))
into l_amt_dr, l_amt_cr, l_acctd_amt_dr, l_acctd_amt_cr
from (select receivable_application_id src_id,
'RA' src_tab
from ar_receivable_applications
where receivable_application_id = p_receivable_application_id
and nvl(postable,'Y') = 'Y'
and posting_control_id = -3) src,
ar_distributions ard
where ard.source_id = src.src_id
and ard.source_table = src.src_tab;
select sum(nvl(amount_dr,0)), sum(nvl(amount_cr,0)),
sum(nvl(acctd_amount_dr,0)), sum(nvl(acctd_amount_cr,0)),
max(src.cc_flag) cc_flag
into l_amt_dr, l_amt_cr, l_acctd_amt_dr, l_acctd_amt_cr, l_cc_flag
from (select receivable_application_id src_id,
'RA' src_tab,
decode(amount_applied_from,NULL,
'N',
'Y') cc_flag
from ar_receivable_applications
where ( receivable_application_id = p_receivable_application_id1
or receivable_application_id = p_receivable_application_id2)
and nvl(postable,'Y') = 'Y'
and posting_control_id = -3) src,
ar_distributions ard
where ard.source_id = src.src_id
and ard.source_table = src.src_tab;
SELECT MAX(apply_date)
INTO l_max_apply_date
FROM (
SELECT MAX(apply_date) apply_date
FROM ar_receivable_applications ra
WHERE status = 'APP'
AND ra.payment_schedule_id = p_ps_rec.payment_schedule_id
UNION ALL
SELECT MAX(apply_date) apply_date
FROM ar_receivable_applications ra
WHERE status = 'APP'
AND ra.applied_payment_schedule_id = p_ps_rec.payment_schedule_id
UNION ALL
SELECT MAX(apply_date) apply_date
FROM ar_adjustments adj
WHERE status = 'A'
AND adj.payment_schedule_id = p_ps_rec.payment_schedule_id
);
SELECT MAX(apply_date)
INTO l_max_apply_date
FROM ar_receivable_applications ra
WHERE payment_schedule_id = p_ps_rec.payment_schedule_id;
SELECT MAX(gl_date)
INTO l_max_gl_date
FROM (
SELECT MAX(gl_date) gl_date
FROM ar_receivable_applications ra
WHERE status = 'APP'
AND ra.payment_schedule_id = p_ps_rec.payment_schedule_id
UNION ALL
SELECT MAX(gl_date) gl_date
FROM ar_receivable_applications ra
WHERE status = 'APP'
AND ra.applied_payment_schedule_id = p_ps_rec.payment_schedule_id
UNION ALL
SELECT MAX(gl_date) gl_date
FROM ar_adjustments adj
WHERE status = 'A'
AND adj.payment_schedule_id = p_ps_rec.payment_schedule_id
);
SELECT MAX(gl_date)
INTO l_max_gl_date
FROM ar_receivable_applications ra
WHERE payment_schedule_id = p_ps_rec.payment_schedule_id;
l_ps_rec.last_update_date := SYSDATE;
arp_ps_pkg.update_p(l_ps_rec);
SELECT sob.currency_code
INTO p_FunctionalCurrency
FROM ar_system_parameters sp,
gl_sets_of_books sob
WHERE sob.set_of_books_id = sp.set_of_books_id;
select 'X'
from ar_payment_schedules ps,
ra_cust_trx_line_gl_dist gld,
ra_customer_trx ct,
(select customer_trx_id,
sum(amount_due_remaining + nvl(chrg_amount_remaining, 0) + nvl(frt_adj_remaining, 0)) amount_due_remaining,
sum(acctd_amount_due_remaining + nvl(chrg_acctd_amount_remaining, 0) + nvl(frt_adj_acctd_remaining, 0)) acctd_amount_due_remaining
from ra_customer_trx_lines
where customer_trx_id=decode(p_customer_trx_id,0,customer_trx_id,p_customer_trx_id)
GROUP BY customer_trx_id) a
where ct.upgrade_method='R12'
and ct.customer_trx_id=decode(p_customer_trx_id,0,ct.customer_trx_id,p_customer_trx_id)
and ps.customer_trx_id=ct.customer_trx_id
and ct.customer_trx_id = a.customer_trx_id
and ct.customer_trx_id = gld.customer_trx_id(+) --modified
and gld.account_class(+) = 'ROUND'
and((a.amount_due_remaining is not null
and nvl(ps.amount_due_remaining,0) <> nvl(a.amount_due_remaining, 0))
or
(a.acctd_amount_due_remaining is not null
and nvl(ps.acctd_amount_due_remaining-nvl(gld.acctd_amount,0),0)<>nvl(a.acctd_amount_due_remaining, 0) and ps.acctd_amount_due_remaining <> a.acctd_amount_due_remaining)) ;