[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')
INTO l_gen_line_level_bal_flag
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 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 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(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;