[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_ps_select VARCHAR2(5000);
l_ra_select VARCHAR2(5000);
l_cm_ra_select VARCHAR2(5000);
l_adj_select VARCHAR2(5000);
l_cancel_br_select VARCHAR2(5000);
l_trx_main_select VARCHAR2(32000);
l_br_select VARCHAR2(5000);
l_br_app_select VARCHAR2(5000);
l_br_adj_select VARCHAR2(5000);
l_br_main_select VARCHAR2(32000);
l_unapp_select VARCHAR2(5000);
l_main_select VARCHAR2(32000);
l_ps_select := 'SELECT ps.customer_trx_id ,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
NULL,:p_as_of_date_from)
* ps.amount_due_remaining) start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
NULL,:p_as_of_date_to)
* ps.amount_due_remaining) end_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
NULL,:p_as_of_date_from)
* ps.acctd_amount_due_remaining) acctd_start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
NULL,:p_as_of_date_to)
* ps.acctd_amount_due_remaining) acctd_end_bal
FROM '||l_ps_table||' ps
WHERE ps.payment_schedule_id+0 > 0
AND ps.gl_date_closed >= :p_as_of_date_from
AND ps.class IN ( ''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
AND ps.gl_date <= :p_as_of_date_to
'|| l_ps_org_where ||'
GROUP BY ps.customer_trx_id ' ;
l_ra_select := 'SELECT
ps.customer_trx_id ,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_from)
* ( ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_to)
* ( ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) end_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_from)
* (ra.acctd_amount_applied_to +
NVL(ra.acctd_earned_discount_taken,0)
+ NVL(ra.acctd_unearned_discount_taken,0))) acctd_start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_to)
* (ra.acctd_amount_applied_to +
NVL(ra.acctd_earned_discount_taken,0)
+ NVL(ra.acctd_unearned_discount_taken,0))) acctd_end_bal
FROM '|| l_ps_table ||' ps,
'|| l_ra_table ||' ra
WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ps.payment_schedule_id+0 > 0
AND ps.gl_date_closed >= :p_as_of_date_from
AND ps.class IN ( ''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
AND ra.gl_date > :p_as_of_date_from
AND ra.status = ''APP''
AND ps.gl_date <= :p_as_of_date_to
AND NVL(ra.confirmed_flag,''Y'') = ''Y''
'|| l_ps_org_where||'
'|| l_ra_org_where||'
GROUP BY ps.customer_trx_id ';
l_cm_ra_select := 'SELECT
ps.customer_trx_id ,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_from)
* -1
* ( ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_to)
* -1
* ( ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) end_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_from)
* -1
* ra.acctd_amount_applied_from ) acctd_start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_to)
* -1
* ra.acctd_amount_applied_from ) acctd_end_bal
FROM '|| l_ps_table ||' ps,
'|| l_ra_table ||' ra
WHERE ra.payment_schedule_id = ps.payment_schedule_id
AND ps.payment_schedule_id+0 > 0
AND ps.gl_date_closed >= :p_as_of_date_from
AND ps.class = ''CM''
AND ra.gl_date > :p_as_of_date_from
AND ra.status IN (''APP'',''ACTIVITY'') --bug 5290086
AND ra.application_type = ''CM''
AND ps.gl_date <= :p_as_of_date_to
AND NVL(ra.confirmed_flag,''Y'') = ''Y''
'|| l_ps_org_where||'
'|| l_ra_org_where||'
GROUP BY ps.customer_trx_id ';
l_adj_select := 'SELECT ps.customer_trx_id,
-sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
adj.gl_date,:p_as_of_date_from)
* adj.amount) start_bal,
-sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
adj.gl_date,:p_as_of_date_to)
* adj.amount) end_bal ,
-sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
adj.gl_date,:p_as_of_date_from)
* adj.acctd_amount) acctd_start_bal,
-sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
adj.gl_date,:p_as_of_date_to)
* adj.acctd_amount) acctd_end_bal
FROM '||l_adj_table||' adj ,'
||l_ps_table ||' ps
WHERE ps.payment_schedule_id + 0 > 0
AND ps.gl_date_closed >= :p_as_of_date_from
AND ps.class IN ( ''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
AND ps.gl_date <= :p_as_of_date_to
AND adj.payment_schedule_id = ps.payment_schedule_id
AND adj.gl_date > :p_as_of_date_from
AND adj.status = ''A''
'|| l_adj_org_where||'
'|| l_ps_org_where|| '
GROUP BY ps.customer_trx_id ';
l_cancel_br_select := 'SELECT
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ath.gl_date,:p_as_of_date_from)
* decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
(ard.amount_cr * -1))) start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ath.gl_date,:p_as_of_date_to)
* decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
(ard.amount_cr * -1))) end_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ath.gl_date,:p_as_of_date_from)
* decode(nvl(ard.acctd_amount_cr,0), 0, nvl(ard.acctd_amount_dr,0),
(ard.acctd_amount_cr * -1))) acctd_start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ath.gl_date,:p_as_of_date_to)
* decode(nvl(ard.acctd_amount_cr,0), 0, nvl(ard.acctd_amount_dr,0),
(ard.acctd_amount_cr * -1))) acctd_end_bal
FROM '||l_ps_table||' ps,
'||l_ard_table || ' ard,
'||'ar_transaction_history_all ath,
'||l_line_table|| ' lines,
gl_code_combinations gc
WHERE ps.payment_schedule_id+0 > 0
AND ps.gl_date_closed >= :p_as_of_date_from
AND ps.class IN ( ''BR'',''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
AND ath.gl_date > :p_as_of_date_from
AND ath.event = ''CANCELLED''
AND ps.gl_date <= :p_as_of_date_to
AND ps.customer_trx_id = ath.customer_trx_id
AND ard.source_table = ''TH''
AND ard.source_id = ath.transaction_history_id
AND ps.customer_trx_id = lines.customer_trx_id
AND ard.source_id_secondary = lines.customer_trx_line_id
AND ard.code_combination_id = gc.code_combination_id
' || l_ps_org_where ||'
' || l_ard_org_where||'
' || l_ath_org_where||'
' || l_line_org_where ||'
' || company_segment_where;
l_cancel_br_select := 'SELECT
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ath.gl_date,:p_as_of_date_from)
* decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
(ard.amount_cr * -1))) start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ath.gl_date,:p_as_of_date_to)
* decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
(ard.amount_cr * -1))) end_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ath.gl_date,:p_as_of_date_from)
* decode(nvl(ard.acctd_amount_cr,0), 0, nvl(ard.acctd_amount_dr,0),
(ard.acctd_amount_cr * -1))) acctd_start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ath.gl_date,:p_as_of_date_to)
* decode(nvl(ard.acctd_amount_cr,0), 0, nvl(ard.acctd_amount_dr,0),
(ard.acctd_amount_cr * -1))) acctd_end_bal
FROM '||l_ps_table||' ps,
'||l_ard_table || ' ard,
'||'ar_transaction_history_all ath,
'||l_line_table|| ' lines
WHERE ps.payment_schedule_id+0 > 0
AND ps.gl_date_closed >= :p_as_of_date_from
AND ps.class IN ( ''BR'',''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
AND ath.gl_date > :p_as_of_date_from
AND ath.event = ''CANCELLED''
AND ps.gl_date <= :p_as_of_date_to
AND ps.customer_trx_id = ath.customer_trx_id
AND ard.source_table = ''TH''
AND ard.source_id = ath.transaction_history_id
AND ps.customer_trx_id = lines.customer_trx_id
AND ard.source_id_secondary = lines.customer_trx_line_id
' || l_ps_org_where ||'
' || l_ard_org_where||'
' || l_ath_org_where||'
' || l_line_org_where;
l_br_select := ' SELECT ps.customer_trx_id ,
sum(ar_calc_aging.begin_or_end_bal(gl_date,gl_date_closed,
NULL,:p_as_of_date_from)
* ps.amount_due_remaining) start_bal,
sum(ar_calc_aging.begin_or_end_bal(gl_date,gl_date_closed,
NULL,:p_as_of_date_to)
* ps.amount_due_remaining) end_bal,
sum(ar_calc_aging.begin_or_end_bal(gl_date,gl_date_closed,
NULL,:p_as_of_date_from)
* ps.acctd_amount_due_remaining) acctd_start_bal,
sum(ar_calc_aging.begin_or_end_bal(gl_date,gl_date_closed,
NULL,:p_as_of_date_to)
* ps.acctd_amount_due_remaining) acctd_end_bal
FROM '||l_ps_table||' ps
WHERE ps.payment_schedule_id+0 > 0
AND ps.class = ''BR''
AND ps.gl_date <= :p_as_of_date_to
AND ps.gl_date_closed >= :p_as_of_date_from
'|| l_ps_org_where ||'
GROUP BY ps.customer_trx_id ';
l_br_app_select := ' SELECT
ps.customer_trx_id ,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_from)
*(ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_to)
*(ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) end_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_from)
*(ra.acctd_amount_applied_to + NVL(ra.acctd_earned_discount_taken,0)
+ NVL(ra.acctd_unearned_discount_taken,0))) acctd_start_bal,
sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
ra.gl_date,:p_as_of_date_to)
*(ra.acctd_amount_applied_to + NVL(ra.acctd_earned_discount_taken,0)
+ NVL(ra.acctd_unearned_discount_taken,0))) acctd_end_bal
FROM '|| l_ps_table||' ps,
'|| l_ra_table||' ra
WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ps.payment_schedule_id+0 > 0
AND ps.class =''BR''
AND ra.gl_date > :p_as_of_date_from
AND ra.status = ''APP''
AND ps.gl_date <= :p_as_of_date_to
AND ps.gl_date_closed >= :p_as_of_date_from
AND NVL(ra.confirmed_flag,''Y'') = ''Y''
'|| l_ps_org_where ||'
'|| l_ra_org_where ||'
GROUP by ps.customer_trx_id ';
l_br_adj_select:= ' SELECT ps.customer_trx_id,
-sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
adj.gl_date,:p_as_of_date_from)
* adj.amount) start_bal,
-sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
adj.gl_date,:p_as_of_date_to)
* adj.amount) end_bal,
-sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
adj.gl_date,:p_as_of_date_from)
* adj.acctd_amount) acctd_start_bal,
-sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
adj.gl_date,:p_as_of_date_to)
* adj.acctd_amount) acctd_end_bal
FROM '|| l_adj_table ||' adj,
'|| l_ps_table ||' ps
WHERE ps.payment_schedule_id + 0 > 0
AND ps.class = ''BR''
AND adj.payment_schedule_id = ps.payment_schedule_id
AND adj.gl_date > :p_as_of_date_from
AND ps.gl_date <= :p_as_of_date_to
AND ps.gl_date_closed >= :p_as_of_date_from
AND adj.status = ''A''
'|| l_adj_org_where||'
'|| l_ps_org_where ||'
GROUP BY ps.customer_trx_id ';
l_unapp_select := 'SELECT
NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
NULL,:p_as_of_date_from)
* ra.amount_applied) ,0 ) start_bal,
NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
NULL,:p_as_of_date_to)
* ra.amount_applied) ,0) end_bal,
NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
NULL,:p_as_of_date_from)
* ra.acctd_amount_applied_from) ,0 ) acctd_start_bal,
NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
NULL,:p_as_of_date_to)
* ra.acctd_amount_applied_from) ,0) acctd_end_bal
FROM '|| l_ps_table ||' ps,
'|| l_ra_table ||' ra,
gl_code_combinations gc
WHERE ra.gl_date <= :p_as_of_date_to
AND ps.cash_receipt_id = ra.cash_receipt_id
AND ra.status in ( ''ACC'', ''UNAPP'', ''UNID'', ''OTHER ACC'' )
AND nvl(ra.confirmed_flag, ''Y'') = ''Y''
AND ps.class = ''PMT''
AND ps.gl_date_closed >= :p_as_of_date_from
AND nvl( ps.receipt_confirmed_flag, ''Y'' ) = ''Y''
AND gc.code_combination_id = ra.code_combination_id
' || l_ps_org_where ||'
' || l_ra_org_where || '
' || company_segment_where;
l_unapp_select := 'SELECT
NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
NULL,:p_as_of_date_from)
* ra.amount_applied) ,0 ) start_bal,
NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
NULL,:p_as_of_date_to)
* ra.amount_applied) ,0) end_bal,
NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
NULL,:p_as_of_date_from)
* ra.acctd_amount_applied_from) ,0 ) acctd_start_bal,
NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
NULL,:p_as_of_date_to)
* ra.acctd_amount_applied_from) ,0) acctd_end_bal
FROM '|| l_ps_table ||' ps,
'|| l_ra_table ||' ra
WHERE ra.gl_date <= :p_as_of_date_to
AND ps.cash_receipt_id = ra.cash_receipt_id
AND ra.status in ( ''ACC'', ''UNAPP'', ''UNID'', ''OTHER ACC'' )
AND nvl(ra.confirmed_flag, ''Y'') = ''Y''
AND ps.class = ''PMT''
AND ps.gl_date_closed >= :p_as_of_date_from
AND nvl( ps.receipt_confirmed_flag, ''Y'' ) = ''Y''
' || l_ps_org_where ||'
' || l_ra_org_where ;
l_trx_main_select := '
SELECT sum(start_bal) start_bal,
sum(end_bal) end_bal,
sum(acctd_start_bal)acctd_start_bal ,
sum(acctd_end_bal) acctd_end_bal
FROM (
'||l_ps_select ||'
UNION ALL
'||l_ra_select ||'
UNION ALL
'||l_cm_ra_select ||'
UNION ALL
'||l_adj_select ||'
) ps ';
l_trx_main_select := l_trx_main_select || ', '|| l_gl_dist_table ||' gl_dist,
gl_code_combinations gc
where gl_dist.customer_trx_id = ps.customer_trx_id
and gl_dist.account_class =''REC''
and gl_dist.latest_rec_flag =''Y''
and gl_dist.code_combination_id = gc.code_combination_id
' || l_gl_dist_org_where ||'
' || company_segment_where ;
l_br_main_select := '
SELECT sum(start_bal) start_bal,
sum(end_bal) end_bal,
sum(acctd_start_bal)acctd_start_bal ,
sum(acctd_end_bal) acctd_end_bal
FROM (
'||l_br_select ||'
UNION ALL
'||l_br_app_select ||'
UNION ALL
'||l_br_adj_select ||'
) ps ';
l_br_main_select := l_br_main_select || ' , ar_transaction_history_all ath,
'|| l_ard_table ||' ard,
gl_code_combinations gc
WHERE ps.customer_trx_id = ath.customer_trx_id
AND ath.status = ''PENDING_REMITTANCE''
AND ath.event in (''COMPLETED'',''ACCEPTED'')
AND ard.source_id = ath.transaction_history_id
AND ard.source_table = ''TH''
AND ard.source_type = ''REC''
AND ard.source_id_secondary IS NULL
AND ard.source_table_secondary IS NULL
AND ard.source_type_secondary IS NULL
AND gc.code_combination_id = ard.code_combination_id
'|| l_ath_org_where ||'
'|| l_ard_org_where ||'
'|| company_segment_where ;
l_main_select := 'SELECT sum(start_bal) start_bal,
sum(end_bal) end_bal,
sum(acctd_start_bal) acctd_start_bal ,
sum(acctd_end_bal) acctd_end_bal
FROM ('|| l_trx_main_select ||' UNION ALL '||
l_br_main_select ||'
UNION ALL
'|| l_unapp_select ||' UNION ALL
'|| l_cancel_br_select|| ') ';
l_main_select := 'SELECT sum(start_bal) start_bal,
sum(end_bal) end_bal,
sum(acctd_start_bal) acctd_start_bal ,
sum(acctd_end_bal) acctd_end_bal
FROM ('|| l_trx_main_select ||' UNION ALL
'|| l_unapp_select
|| ') ';
dbms_sql.parse(v_cursor,l_main_select,DBMS_SQL.NATIVE);
l_main_select VARCHAR2(10000);
l_endorsement_select VARCHAR2(5000);
l_main_select := '
SELECT sum(decode(rec.type,''FINCHRG'', adj.amount,0)) fin_amount,
sum(decode(rec.type,''FINCHRG'', adj.acctd_amount,0)) fin_acctd_amount,
sum(decode(rec.type,''ADJUST'',
decode(adj.adjustment_type,''C'',0,
decode(adj.receivables_trx_id,-15,0, adj.amount)))) Adj_amount,
sum(decode(rec.type,''ADJUST'',
decode(adj.adjustment_type,''C'',0,
decode(adj.receivables_trx_id,-15,0, adj.acctd_amount)))) Adj_acctd_amount,
sum(decode(rec.type,''ADJUST'',
decode(adj.adjustment_type,''C'',
decode(type.type,''GUAR'',adj.amount,0)))) Guar_amount,
sum(decode(rec.type,''ADJUST'',
decode(adj.adjustment_type,''C'',
decode(type.type,''GUAR'',adj.acctd_amount,0)))) Guar_acctd_amount,
sum(decode(rec.type,''ADJUST'',
decode(adj.adjustment_type,''C'',
decode(type.type,''GUAR'',0,adj.amount)))) Dep_amount,
sum(decode(rec.type,''ADJUST'',
decode(adj.adjustment_type,''C'',
decode(type.type,''GUAR'',0,adj.acctd_amount)))) Dep_acctd_amount
FROM '||l_adj_table||' adj,
ar_receivables_trx_all rec,
'||l_trx_table||' trx,
ra_cust_trx_types_all type ';
l_main_select := l_main_select || ',
'||l_gl_dist_table||' gl_dist,
gl_code_combinations gc ';
l_main_select := l_main_select ||'
WHERE nvl(adj.status, ''A'') = ''A''
AND adj.receivables_trx_id <> -15
AND adj.receivables_trx_id = rec.receivables_trx_id
AND nvl(rec.org_id,-99) = nvl(adj.org_id,-99)
AND adj.gl_date between :gl_date_low and :gl_date_high
AND trx.customer_trx_id = adj.customer_trx_id
AND trx.complete_flag = ''Y''
AND trx.cust_trx_type_id = type.cust_trx_type_id
AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
'|| l_adj_org_where ||'
'|| l_trx_org_where ;
l_main_select := l_main_select ||'
AND adj.customer_trx_id = gl_dist.customer_trx_id
AND gl_dist.account_class = ''REC''
AND gl_dist.latest_rec_flag = ''Y''
AND gc.code_combination_id = gl_dist.code_combination_id
'|| l_gl_dist_org_where ||'
'|| company_segment_where;
l_endorsement_select := 'SELECT
sum(adj.amount) Endsmnt_amount,
sum(adj.acctd_amount) Endrsmnt_acctd_amount
FROM '||l_adj_table||' adj,
ar_receivables_trx_all rec';
l_endorsement_select := l_endorsement_select || ' ,
ar_transaction_history_all ath ';
l_endorsement_select := l_endorsement_select ||'
WHERE nvl(adj.status, ''A'') = ''A''
AND adj.receivables_trx_id <> -15
AND adj.receivables_trx_id = rec.receivables_trx_id
AND nvl(adj.org_id,-99) = nvl(rec.org_id,-99)
AND rec.type = ''ENDORSEMENT''
AND adj.gl_date between :gl_date_low and :gl_date_high
'|| l_adj_org_where ;
l_endorsement_select := l_endorsement_select || '
AND adj.customer_trx_id = ath.customer_trx_id
AND ath.status = ''PENDING_REMITTANCE''
AND ath.event in (''COMPLETED'',''ACCEPTED'')
'|| l_ath_org_where ||'
AND exists (SELECT line_id
FROM '|| l_ard_table ||' ard,
gl_code_combinations gc
WHERE ard.source_id = ath.transaction_history_id
AND ard.source_table = ''TH''
AND ard.source_type = ''REC''
AND ard.source_id_secondary IS NULL
AND ard.source_table_secondary IS NULL
AND ard.source_type_secondary IS NULL
AND gc.code_combination_id = ard.code_combination_id
'|| l_ard_org_where ||'
'||company_segment_where||')';
dbms_sql.parse(v_cursor,l_main_select,DBMS_SQL.NATIVE);
dbms_sql.parse(v_cursor,l_endorsement_select,DBMS_SQL.NATIVE);
l_post_select VARCHAR2(2000);
l_non_post_select VARCHAR2(2000);
l_post_select := '
SELECT
NVL(SUM(NVL(gl_dist.amount,0)),0) Invoice_Currency,
NVL(SUM(NVL(gl_dist.acctd_amount,0)),0) Functional_Currency
FROM ra_cust_trx_types_all type,
'||l_trx_table||' trx,
'||l_gl_dist_table||' gl_dist,
gl_code_combinations gc
WHERE gl_dist.gl_date BETWEEN :gl_date_low AND :gl_date_high
AND gl_dist.gl_date IS NOT NULL
AND gl_dist.account_class = ''REC''
AND gl_dist.latest_rec_flag = ''Y''
AND gl_dist.customer_trx_id = trx.customer_trx_id
AND type.cust_trx_type_id = trx.cust_trx_type_id
AND trx.complete_flag = ''Y''
AND type.type in (''INV'',''DEP'',''GUAR'', ''CM'',''DM'', ''CB'' )
AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
AND gc.code_combination_id = gl_dist.code_combination_id
'||l_gl_dist_org_where ||'
'||l_trx_org_where ||'
'||company_segment_where;
l_non_post_select := '
SELECT
NVL(SUM(NVL(gl_dist.amount,0)),0) Invoice_Currency,
NVL(SUM(NVL(gl_dist.acctd_amount,0)),0) Functional_Currency
FROM ra_cust_trx_types_all type,
'||l_trx_table||' trx,
'||l_gl_dist_table||' gl_dist,
gl_code_combinations gc
WHERE trx.trx_date BETWEEN :gl_date_low AND :gl_date_high
AND gl_dist.gl_date IS NULL
AND gl_dist.account_class = ''REC''
AND gl_dist.latest_rec_flag = ''Y''
AND gl_dist.customer_trx_id = trx.customer_trx_id
AND type.cust_trx_type_id = trx.cust_trx_type_id
AND trx.complete_flag = ''Y''
AND type.type in (''INV'',''DEP'',''GUAR'', ''CM'',''DM'', ''CB'' )
AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
AND gc.code_combination_id = gl_dist.code_combination_id
'||l_gl_dist_org_where ||'
'||l_trx_org_where ||'
'||company_segment_where;
l_post_select := '
SELECT
NVL(SUM(NVL(gl_dist.amount,0)),0) Invoice_Currency,
NVL(SUM(NVL(gl_dist.acctd_amount,0)),0) Functional_Currency
FROM ra_cust_trx_types_all type,
'||l_trx_table||' trx,
'||l_gl_dist_table||' gl_dist
WHERE gl_dist.gl_date BETWEEN :gl_date_low AND :gl_date_high
AND gl_dist.gl_date IS NOT NULL
AND gl_dist.account_class = ''REC''
AND gl_dist.latest_rec_flag = ''Y''
AND gl_dist.customer_trx_id = trx.customer_trx_id
AND type.cust_trx_type_id = trx.cust_trx_type_id
AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
AND trx.complete_flag = ''Y''
AND type.type in (''INV'',''DEP'',''GUAR'', ''CM'',''DM'', ''CB'' )
'||l_gl_dist_org_where ||'
'||l_trx_org_where;
l_non_post_select := '
SELECT
NVL(SUM(NVL(gl_dist.amount,0)),0) Invoice_Currency,
NVL(SUM(NVL(gl_dist.acctd_amount,0)),0) Functional_Currency
FROM ra_cust_trx_types_all type,
'||l_trx_table||' trx,
'||l_gl_dist_table||' gl_dist
WHERE trx.trx_date BETWEEN :gl_date_low AND :gl_date_high
AND gl_dist.gl_date IS NULL
AND gl_dist.account_class = ''REC''
AND gl_dist.latest_rec_flag = ''Y''
AND gl_dist.customer_trx_id = trx.customer_trx_id
AND type.cust_trx_type_id = trx.cust_trx_type_id
AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
AND trx.complete_flag = ''Y''
AND type.type in (''INV'',''DEP'',''GUAR'', ''CM'',''DM'', ''CB'' )
'||l_gl_dist_org_where ||'
'||l_trx_org_where;
dbms_sql.parse(v_cursor,l_post_select ,DBMS_SQL.NATIVE);
dbms_sql.parse(v_cursor,l_non_post_select ,DBMS_SQL.NATIVE);
* MRC enhancements to select data from reporting book
* please refer to bug for more details.
* we need to execute different selects depending on the book
* for which report is run
*/
-- For Zero Amount Transactions , sometimes the acctd_amount is
-- derived as 0.01 or 0.02.
IF NVL(ar_calc_aging.ca_sob_type,'P') = 'P'
THEN
SELECT NVL(SUM(NVL(acctd_amount,0)),0)
INTO l_rounding_diff
FROM ra_cust_trx_line_gl_dist
WHERE amount = 0
AND gl_date BETWEEN l_gl_date_low AND l_gl_date_high ;
SELECT NVL(SUM(NVL(acctd_amount,0)),0)
INTO l_rounding_diff
FROM ra_trx_line_gl_dist_mrc_v
WHERE amount = 0
AND gl_date BETWEEN l_gl_date_low AND l_gl_date_high ;
l_main_select VARCHAR2(20000);
l_main_select := 'SELECT NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''ACC'', ra.amount_applied,0)
,0)),0) Onacc_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''ACC'', ra.acctd_amount_applied_from,0)
,0)),0) Onacc_acctd_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''OTHER ACC'', DECODE(ra.applied_payment_schedule_id,
-4, ra.amount_applied,0),0)
,0)),0) claim_amount,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''OTHER ACC'', DECODE(ra.applied_payment_schedule_id,
-4, ra.acctd_amount_applied_from,0),0)
,0)),0) claim_acctd_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''OTHER ACC'', DECODE(ra.applied_payment_schedule_id,
-7, ra.amount_applied,0),0)
,0)),0) prepay_amount,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''OTHER ACC'', DECODE(ra.applied_payment_schedule_id,
-7, ra.acctd_amount_applied_from,0),0)
,0)),0) prepay_acctd_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''UNAPP'', ra.amount_applied,
''UNID'', ra.amount_applied,0)
,0)),0) unapp_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''UNAPP'', ra.acctd_amount_applied_from,
''UNID'', ra.acctd_amount_applied_from,0)
,0)),0) unapp_acctd_amt,
NVL(SUM(DECODE(ra.application_type,
''CM'', DECODE(ra.amount_applied,0,0,
ra.acctd_amount_applied_from)
, 0)
),0) -
NVL(SUM(DECODE(ra.application_type,
''CM'', DECODE(ra.amount_applied,0,0,
NVL(ra.acctd_amount_applied_to,0))
, 0)
),0) cm_gain_loss,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''APP'',
ra.amount_applied,0),0)),0) app_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''APP'',
NVL(ra.earned_discount_taken,0),0),0)),0) edisc_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''APP'',
NVL(ra.unearned_discount_taken,0),0),0)),0) unedisc_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''APP'',
NVL(ra.acctd_amount_applied_to,0),0),0)),0) acctd_app_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''APP'',
NVL(ra.acctd_earned_discount_taken,0),0),0)),0) acctd_edisc_amt,
NVL(SUM(DECODE(ra.application_type,
''CASH'',
DECODE(ra.status,
''APP'',
NVL(ra.acctd_unearned_discount_taken,0),0),0)),0) acctd_unedisc_amt,
NVL(SUM(DECODE(ra.application_type, /*bug5290086*/
''CM'',
DECODE(ra.status,
''ACTIVITY'', DECODE(ra.applied_payment_schedule_id,
-8, ra.amount_applied,0),0)
,0)),0) onacc_cm_ref_amount,
NVL(SUM(DECODE(ra.application_type,
''CM'',
DECODE(ra.status,
''ACTIVITY'', DECODE(ra.applied_payment_schedule_id,
-8, ra.acctd_amount_applied_to,0),0)
,0)),0) onacc_cm_ref_acctd_amount
FROM '|| l_ra_table || ' ra ';
l_main_select := l_main_select || ',
gl_code_combinations gc ';
l_main_select := l_main_select || '
WHERE NVL(ra.confirmed_flag,''Y'') = ''Y''
AND ra.gl_date BETWEEN :gl_date_low AND :gl_date_high
'|| l_ra_org_where;
l_main_select := l_main_select || '
AND gc.code_combination_id = ra.code_combination_id
'|| company_segment_where;
dbms_sql.parse(v_cursor,l_main_select,DBMS_SQL.NATIVE);
l_post_select VARCHAR2(10000);
l_non_post_select VARCHAR2(10000);
l_post_select := '
SELECT
NVL(SUM(NVL(gl_dist.amount,0)),0) ,
NVL(SUM(NVL(gl_dist.acctd_amount,0)),0)
FROM
ra_cust_trx_types_all type,
'||l_trx_table||' trx,
'||l_gl_dist_table||' gl_dist ';
l_non_post_select := '
SELECT
NVL(SUM(NVL(gl_dist.amount,0)),0) ,
NVL(SUM(NVL(gl_dist.acctd_amount,0)),0)
FROM
ra_cust_trx_types_all type,
'||l_trx_table||' trx,
'||l_gl_dist_table||' gl_dist ';
l_post_select := l_post_select ||',
gl_code_combinations gc ';
l_non_post_select := l_non_post_select ||',
gl_code_combinations gc ';
l_post_select := l_post_select || '
WHERE trx.complete_flag = ''Y''
AND NOT EXISTS ( SELECT ''x''
FROM '||l_ps_table||' ps
WHERE ps.customer_trx_id = trx.customer_trx_id
'|| l_ps_org_where||')
AND gl_dist.gl_date BETWEEN :gl_date_low AND :gl_date_high
AND type.post_to_gl = ''Y''
AND gl_dist.account_class = ''REC''
AND gl_dist.latest_rec_flag = ''Y''
AND gl_dist.customer_trx_id = trx.customer_trx_id
AND trx.cust_trx_type_id = type.cust_trx_type_id
AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
AND type.type IN (''INV'', ''DEP'', ''GUAR'', ''CM'',''DM'')
'|| l_trx_org_where||'
'|| l_gl_dist_org_where ;
l_non_post_select := l_non_post_select||'
WHERE trx.complete_flag = ''Y''
AND NOT EXISTS ( SELECT ''x''
FROM '||l_ps_table||' ps
WHERE ps.customer_trx_id = trx.customer_trx_id
'|| l_ps_org_where||')
AND trx.trx_date BETWEEN :gl_date_low AND :gl_date_high
AND type.post_to_gl = ''N''
AND gl_dist.account_class = ''REC''
AND gl_dist.latest_rec_flag = ''Y''
AND gl_dist.customer_trx_id = trx.customer_trx_id
AND trx.cust_trx_type_id = type.cust_trx_type_id
AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
AND type.type IN (''INV'', ''DEP'', ''GUAR'', ''CM'',''DM'')
'|| l_trx_org_where ||'
'|| l_gl_dist_org_where;
l_post_select := l_post_select||'
AND gc.code_combination_id = gl_dist.code_combination_id
'||company_segment_where ;
l_non_post_select := l_non_post_select ||'
AND gc.code_combination_id = gl_dist.code_combination_id
'||company_segment_where ;
dbms_sql.parse(v_cursor,l_post_select,DBMS_SQL.NATIVE);
dbms_sql.parse(v_cursor,l_non_post_select,DBMS_SQL.NATIVE);
l_adj_journal_select VARCHAR2(2000);
l_app_journal_select VARCHAR2(3000);
l_unapp_journal_select VARCHAR2(2000);
l_cm_journal_select VARCHAR2(2000);
SELECT set_of_books_id
INTO l_ledger_id
FROM ar_system_parameters_all
WHERE org_id = p_reporting_entity_id;
l_sales_journal_salect := ' SELECT (sum(nvl(ae.entered_dr,0))- sum(nvl(ae.entered_cr,0))),
(sum(nvl(ae.accounted_dr,0))- sum(nvl(ae.accounted_cr,0)))
FROM '||l_trx_table||' trx,
xla_transaction_entities_upg en,
xla_ae_headers hdr,
xla_ae_lines ae ';
AND EXISTS ( SELECT ''x''
FROM xla_distribution_links lk
WHERE lk.event_id = hdr.event_id
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.application_id = 222
AND lk.source_distribution_type = ''RA_CUST_TRX_LINE_GL_DIST_ALL'')
'|| l_trx_org_where;
l_adj_journal_select := 'SELECT (sum(nvl(ae.entered_dr,0))- sum(nvl(ae.entered_cr,0))),
(sum(nvl(ae.accounted_dr,0))- sum(nvl(ae.accounted_cr,0)))
FROM '||l_adj_table||' adj,
xla_transaction_entities_upg en,
xla_ae_headers hdr,
xla_ae_lines ae ';
l_adj_journal_select := l_adj_journal_select||',
gl_code_combinations gc';
l_adj_journal_select := l_adj_journal_select||'
WHERE en.application_id = 222
AND en.ledger_id = '|| l_ledger_id ||'
AND hdr.entity_id = en.entity_id
AND adj.adjustment_id = en.source_id_int_1
AND hdr.application_id = 222
AND hdr.ledger_id = en.ledger_id
AND hdr.ae_header_id = ae.ae_header_id
AND hdr.accounting_date between :gl_date_low and :gl_date_high
AND ae.application_id = 222
AND ae.accounting_class_code IN (''RECEIVABLE'')
AND ae.ledger_id = en.ledger_id
AND EXISTS ( SELECT ''x''
FROM xla_distribution_links lk
WHERE lk.event_id = hdr.event_id
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND lk.event_class_code = ''ADJUSTMENT'')
'|| l_adj_org_where;
l_adj_journal_select := l_adj_journal_select||'
AND gc.code_combination_id = ae.code_combination_id
'||company_segment_where;
l_app_journal_select := 'SELECT (sum(nvl(ae.entered_cr,0))- sum(nvl(ae.entered_dr,0))),
(sum(nvl(ae.accounted_cr,0))- sum(nvl(ae.accounted_dr,0)))
FROM '||l_cr_table ||' cr,
xla_transaction_entities_upg en,
xla_ae_headers hdr,
xla_ae_lines ae ';
l_app_journal_select := l_app_journal_select ||',
gl_code_combinations gc';
l_app_journal_select := l_app_journal_select||'
WHERE en.application_id = 222
AND en.ledger_id = '|| l_ledger_id ||'
AND hdr.entity_id = en.entity_id
AND cr.cash_receipt_id = en.source_id_int_1
AND hdr.application_id = 222
AND hdr.ledger_id = en.ledger_id
AND hdr.ae_header_id = ae.ae_header_id
AND hdr.accounting_date between :gl_date_low and :gl_date_high
AND ae.application_id = 222
AND ae.accounting_class_code IN (''RECEIVABLE'', ''EDISC'', ''UNEDISC'', ''UNPAID_BR'', ''REM_BR'', ''FAC_BR'')
AND ae.ledger_id = en.ledger_id
AND EXISTS ( SELECT ''x''
FROM xla_distribution_links lk
WHERE lk.event_id = hdr.event_id
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND lk.event_class_code = ''RECEIPT'')
'|| l_cr_org_where;
l_app_journal_select := l_app_journal_select||'
AND gc.code_combination_id = ae.code_combination_id
'||company_segment_where;
l_unapp_journal_select := 'SELECT (sum(nvl(entered_cr,0))- sum(nvl(entered_dr,0))),
(sum(nvl(accounted_cr,0))- sum(nvl(accounted_dr,0)))
FROM '||l_cr_table||' cr,
xla_transaction_entities_upg en,
xla_ae_headers hdr,
xla_ae_lines ae ';
l_unapp_journal_select := l_unapp_journal_select ||',
gl_code_combinations gc ';
l_unapp_journal_select := l_unapp_journal_select ||'
WHERE en.application_id = 222
AND en.ledger_id = '|| l_ledger_id ||'
AND hdr.entity_id = en.entity_id
AND cr.cash_receipt_id = en.source_id_int_1
AND hdr.application_id = 222
AND hdr.ledger_id = en.ledger_id
AND hdr.ae_header_id = ae.ae_header_id
AND hdr.accounting_date between :gl_date_low and :gl_date_high
AND ae.application_id = 222
AND ae.accounting_class_code IN (''CLAIM'',''PREPAY'',''UNAPP'',''UNID'',''ACC'')
AND ae.ledger_id = en.ledger_id
'|| l_cr_org_where;
l_unapp_journal_select := l_unapp_journal_select ||'
AND gc.code_combination_id = ae.code_combination_id
'||company_segment_where;
l_cm_journal_select := 'SELECT (sum(nvl(ae.accounted_cr,0))- sum(nvl(ae.accounted_dr,0)))
FROM '||l_trx_table||' trx,
xla_transaction_entities_upg en,
xla_ae_headers hdr,
xla_ae_lines ae ';
l_cm_journal_select := l_cm_journal_select ||',
gl_code_combinations gc';
l_cm_journal_select := l_cm_journal_select ||'
WHERE en.application_id = 222
AND en.ledger_id = '|| l_ledger_id ||'
AND hdr.entity_id = en.entity_id
AND trx.customer_trx_id = en.source_id_int_1
AND hdr.application_id = 222
AND hdr.ledger_id = en.ledger_id
AND hdr.ae_header_id = ae.ae_header_id
AND hdr.accounting_date between :gl_date_low and :gl_date_high
AND ae.application_id = 222
AND ae.ledger_id = en.ledger_id
AND ae.accounting_class_code IN (''EXCHANGE_GAIN_LOSS'')
AND EXISTS ( SELECT ''x''
FROM xla_distribution_links lk
WHERE lk.event_id = hdr.event_id
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND lk.event_class_code = ''CREDIT_MEMO'')
'|| l_trx_org_where;
l_cm_journal_select := l_cm_journal_select ||'
AND gc.code_combination_id = ae.code_combination_id
'||company_segment_where;
dbms_sql.parse(v_cursor,l_adj_journal_select,DBMS_SQL.NATIVE);
dbms_sql.parse(v_cursor,l_app_journal_select,DBMS_SQL.NATIVE);
dbms_sql.parse(v_cursor,l_unapp_journal_select,DBMS_SQL.NATIVE);
dbms_sql.parse(v_cursor,l_cm_journal_select,DBMS_SQL.NATIVE);
l_select_stmt VARCHAR2(10000);
l_select_stmt := 'SELECT sob.name sob_name,
sob.currency_code functional_currency,
sob.chart_of_accounts_id ,
cur.precision,
to_char(sysdate,''DD-MON-YYYY hh24:mi'') p_sysdate
FROM gl_sets_of_books sob,
fnd_currencies cur
WHERE sob.set_of_books_id = :p_set_of_books_id
AND sob.currency_code = cur.currency_code';
EXECUTE IMMEDIATE l_select_stmt
INTO p_sob_name,
p_functional_currency,
p_coa_id,
p_precision,
p_sysdate
USING p_set_of_books_id;
select meaning
into p_organization
from ar_lookups
where lookup_code = 'ALL'
and lookup_type = 'ALL';
'select ''Y''
from dual
where exists( select ''br_enabled''
from '||l_sysparam_table||' param
where bills_receivable_enabled_flag = ''Y''
'||l_sysparam_where||')'
into br_enabled_flag;
execute immediate 'select substr(hou.name,1,60) organization,
nvl(param.bills_receivable_enabled_flag,''N'')
from hr_organization_units hou,
'||l_sysparam_table||' param
where hou.organization_id = :org_id
and hou.organization_id = param.org_id'
into p_organization,br_enabled_flag
using p_reporting_entity_id;