[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
into l_def_flag
from dual
where exists (select 'x'
from ra_cust_trx_line_gl_dist gld
where gld.account_class = 'TAX'
and gld.customer_trx_id = p_customer_trx_id
and gld.collected_tax_ccid IS NOT NULL
);
SELECT ctl.customer_trx_id br_cust_trx_id ,
ctl.customer_trx_line_id br_customer_trx_line_id ,
ctl.br_ref_customer_trx_id br_ref_customer_trx_id ,
ctl.br_ref_payment_schedule_id br_ref_payment_schedule_id ,
ct.drawee_site_use_id drawee_site_use_id ,
ct.invoice_currency_code invoice_currency_code ,
ct.exchange_rate exchange_rate ,
ct.exchange_rate_type exchange_rate_type ,
ct.exchange_date exchange_date ,
ct.trx_date trx_date ,
ct.bill_to_customer_id bill_to_customer_id ,
ct.bill_to_site_use_id bill_to_site_use_id ,
adj.adjustment_id br_adj_id ,
nvl(adj.amount,0) br_adj_amt ,
nvl(adj.acctd_amount,0) br_adj_acctd_amt ,
nvl(adj.line_adjusted,0) br_adj_line_amt ,
nvl(adj.tax_adjusted,0) br_adj_tax_amt ,
nvl(adj.freight_adjusted,0) br_adj_frt_amt ,
nvl(adj.receivables_charges_adjusted,0) br_adj_chrg_amt
FROM ra_customer_trx_lines ctl,
ar_adjustments adj,
ra_customer_trx ct
WHERE ctl.customer_trx_id = p_customer_trx_id
AND ctl.br_adjustment_id = adj.adjustment_id
AND ct.customer_trx_id = ctl.br_ref_customer_trx_id
AND adj.status = 'A'
order by ctl.customer_trx_line_id;
select1_stmt VARCHAR2(4000) := '
select source_type source_type,
source_id_secondary source_id_secondary,
source_table_secondary source_table_secondary,
source_type_secondary source_type_secondary,
max(currency_code) currency_code,
max(currency_conversion_rate) currency_conversion_rate,
max(currency_conversion_type) currency_conversion_type,
max(currency_conversion_date) currency_conversion_date,
max(third_party_id) third_party_id,
max(third_party_sub_id) third_party_sub_id,
max(reversed_source_id) reversed_source_id,
sum(amount) amount,
sum(acctd_amount) acctd_amount,
sum(taxable_entered) taxable_entered,
sum(taxable_accounted) taxable_accounted,
location_segment_id location_segment_id,
tax_group_code_id tax_group_code_id,
tax_code_id tax_code_id,
code_combination_id code_combination_id
from ( ';
select2_stmt VARCHAR2(4000) := '
select
ard.source_type source_type,
decode(:p_calling_point,
''BLTR'', :p_customer_trx_line_id,
ard.source_id_secondary) source_id_secondary,
decode(:p_calling_point,
''BLTR'', ''CTL'',
ard.source_table_secondary) source_table_secondary,
ard.source_type_secondary source_type_secondary,
max(ard.currency_code) currency_code,
max(ard.currency_conversion_rate) currency_conversion_rate,
max(ard.currency_conversion_type) currency_conversion_type,
max(ard.currency_conversion_date) currency_conversion_date,
max(ard.third_party_id) third_party_id,
max(ard.third_party_sub_id) third_party_sub_id,
max(reversed_source_id) reversed_source_id,
sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) amount,
sum(nvl(ard.acctd_amount_dr,0) * -1 +
nvl(ard.acctd_amount_cr,0)) acctd_amount,
sum(nvl(ard.taxable_entered_dr,0) * -1 +
nvl(ard.taxable_entered_cr,0)) taxable_entered,
sum(nvl(ard.taxable_accounted_dr,0) * -1 +
nvl(ard.taxable_accounted_cr,0)) taxable_accounted,
ard.location_segment_id location_segment_id,
ard.tax_group_code_id tax_group_code_id,
ard.tax_code_id tax_code_id,
ard.code_combination_id code_combination_id';
select3_stmt VARCHAR2(4000) := '
select
ard.source_type source_type,
decode(:p_calling_point,
''BLTR'', :p_customer_trx_line_id,
:p_customer_trx_id) source_id_secondary,
decode(:p_calling_point,
''BLTR'',''CTL'',
''CT'') source_table_secondary,
ard.source_type_secondary source_type_secondary,
max(ard.currency_code) currency_code,
max(ard.currency_conversion_rate) currency_conversion_rate,
max(ard.currency_conversion_type) currency_conversion_type,
max(ard.currency_conversion_date) currency_conversion_date,
max(ard.third_party_id) third_party_id,
max(ard.third_party_sub_id) third_party_sub_id,
max(reversed_source_id) reversed_source_id,
sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) amount,
sum(nvl(ard.acctd_amount_dr,0) * -1 +
nvl(ard.acctd_amount_cr,0)) acctd_amount,
sum(nvl(ard.taxable_entered_dr,0) * -1 +
nvl(ard.taxable_entered_cr,0)) taxable_entered,
sum(nvl(ard.taxable_accounted_dr,0) * -1 +
nvl(ard.taxable_accounted_cr,0)) taxable_accounted,
ard.location_segment_id location_segment_id,
ard.tax_group_code_id tax_group_code_id,
ard.tax_code_id tax_code_id,
ard.code_combination_id code_combination_id';
select4_stmt VARCHAR2(4000) := '
select
ard.source_type source_type,
ard.source_id_secondary source_id_secondary,
ard.source_table_secondary source_table_secondary,
ard.source_type_secondary source_type_secondary,
max(ard.currency_code) currency_code,
max(ard.currency_conversion_rate) currency_conversion_rate,
max(ard.currency_conversion_type) currency_conversion_type,
max(ard.currency_conversion_date) currency_conversion_date,
max(ard.third_party_id) third_party_id,
max(ard.third_party_sub_id) third_party_sub_id,
max(reversed_source_id) reversed_source_id,
sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) amount,
sum(nvl(ard.acctd_amount_dr,0) * -1 +
nvl(ard.acctd_amount_cr,0)) acctd_amount,
sum(nvl(ard.taxable_entered_dr,0) * -1 +
nvl(ard.taxable_entered_cr,0)) taxable_entered,
sum(nvl(ard.taxable_accounted_dr,0) * -1 +
nvl(ard.taxable_accounted_cr,0)) taxable_accounted,
ard.location_segment_id location_segment_id,
ard.tax_group_code_id tax_group_code_id,
ard.tax_code_id tax_code_id,
ard.code_combination_id code_combination_id ';
arp_standard.debug('get_recon_acct: ' || 'selecting cursor based on reporting type');
sql_stmt := select1_stmt ||
select2_stmt; /* common between primary and reporting */
select2_stmt || CRLF || from2_stmt || CRLF ||
where2_stmt || CRLF ||
group_stmt || CRLF ||
union_stmt ||
select3_stmt || CRLF || from3_stmt || CRLF ||
where3_stmt || CRLF ||
group3_stmt || CRLF ||
union_stmt ||
select4_stmt || CRLF || from4_stmt || CRLF ||
where4_stmt || CRLF ||
group4_stmt || CRLF ||
group2;
arp_standard.debug ('get_recon_acct: ' || 'select_stmt = ' || sql_stmt);
select 'N'
into p_all_br_closed
from dual
where exists ( select /*+ ordered leading(rc.rct) use_nl(rc.rct ps)*/ 'x'
from ( select customer_trx_id
from ra_customer_trx_lines rct
start with br_ref_customer_trx_id = p_customer_trx_id
connect by prior customer_trx_id = br_ref_customer_trx_id
) rc, ar_payment_schedules ps
where ps.customer_trx_id = rc.customer_trx_id
and ps.status = 'OP'
and ps.customer_trx_id <> g_orig_cust_trx_id
);
SELECT pay.payment_schedule_id payment_schedule_id,
sum( nvl(app.amount_applied,0) +
nvl(app.earned_discount_taken,0) +
nvl(app.unearned_discount_taken,0)) amount,
sum(nvl(app.acctd_amount_applied_to,0) +
nvl(app.acctd_earned_discount_taken,0) +
nvl(app.acctd_unearned_discount_taken,0)) acctd_amount,
sum(nvl(app.line_applied,0) +
nvl(app.line_ediscounted,0) +
nvl(app.line_uediscounted,0)) line_amount,
sum(nvl(app.tax_applied,0) +
nvl(app.tax_ediscounted,0) +
nvl(app.tax_uediscounted,0)) tax_amount,
sum(nvl(app.freight_applied,0) +
nvl(app.freight_ediscounted,0) +
nvl(app.freight_uediscounted,0)) freight_amount,
sum(nvl(app.receivables_charges_applied,0) +
nvl(app.charges_ediscounted,0) +
nvl(app.charges_uediscounted,0)) receivables_charges_amount
FROM ar_receivable_applications app,
ar_payment_schedules pay
WHERE app.applied_customer_trx_id = p_customer_trx_id
AND app.status = 'APP'
AND nvl(app.confirmed_flag, 'Y') = 'Y'
AND app.applied_payment_schedule_id = pay.payment_schedule_id
AND app.application_type = 'CASH' --only payments result in movement of
GROUP by pay.payment_schedule_id
UNION ALL --get adjustment bucket details
SELECT pay.payment_schedule_id payment_schedule_id,
sum(nvl(adj.amount,0) * -1) amount,
sum(nvl(adj.acctd_amount,0) * -1) acctd_amount,
sum(nvl(adj.line_adjusted,0) * -1) line_amount,
sum(nvl(adj.tax_adjusted,0) * -1) tax_amount,
sum(nvl(adj.freight_adjusted,0) * -1) freight_amount,
sum(nvl(adj.receivables_charges_adjusted,0) * -1) receivables_charges_amount
FROM ar_adjustments adj,
ar_payment_schedules pay
WHERE adj.customer_trx_id = p_customer_trx_id
AND adj.payment_schedule_id = pay.payment_schedule_id
AND adj.status = 'A'
GROUP by pay.payment_schedule_id;
SELECT sum(nvl(app.amount_applied,0)) ,
sum(nvl(app.acctd_amount_applied_to,0)),
sum(nvl(app.line_applied,0)),
sum(nvl(app.tax_applied,0)),
sum(nvl(app.freight_applied,0)),
sum(nvl(app.receivables_charges_applied,0))
INTO l_cm_amt,
l_cm_acctd_amt,
l_cm_line_amt,
l_cm_tax_amt,
l_cm_frt_amt,
l_cm_chrg_amt
FROM ar_receivable_applications app
WHERE app.applied_customer_trx_id = p_customer_trx_id
AND app.application_type = 'CM'
AND nvl(app.confirmed_flag, 'Y') = 'Y'
AND app.status = 'APP';
SELECT sum(nvl(app.amount_applied,0)) ,
sum(nvl(app.acctd_amount_applied_to,0)),
sum(nvl(app.line_applied,0)),
sum(nvl(app.tax_applied,0)),
sum(nvl(app.freight_applied,0)),
sum(nvl(app.receivables_charges_applied,0))
INTO l_cm_amt,
l_cm_acctd_amt,
l_cm_line_amt,
l_cm_tax_amt,
l_cm_frt_amt,
l_cm_chrg_amt
FROM ar_receivable_applications app
WHERE app.customer_trx_id = p_customer_trx_id
AND app.application_type = 'CM'
AND nvl(app.confirmed_flag, 'Y') = 'Y'
AND app.status = 'APP';
SELECT ctl.customer_trx_id br_cust_trx_id ,
ctl.customer_trx_line_id br_customer_trx_line_id ,
ctl.br_ref_customer_trx_id br_ref_customer_trx_id ,
ctl.br_ref_payment_schedule_id br_ref_payment_schedule_id ,
ct.drawee_site_use_id drawee_site_use_id ,
ct.invoice_currency_code invoice_currency_code ,
ct.exchange_rate exchange_rate,
ct.exchange_rate_type exchange_rate_type,
ct.exchange_date exchange_date,
ct.trx_date trx_date ,
ct.bill_to_customer_id bill_to_customer_id ,
ct.bill_to_site_use_id bill_to_site_use_id ,
adj.adjustment_id br_adj_id ,
nvl(adj.amount,0) br_adj_amt ,
nvl(adj.acctd_amount,0) br_adj_acctd_amt ,
nvl(adj.line_adjusted,0) br_adj_line_amt ,
nvl(adj.tax_adjusted,0) br_adj_tax_amt ,
nvl(adj.freight_adjusted,0) br_adj_frt_amt ,
nvl(adj.receivables_charges_adjusted,0) br_adj_chrg_amt
FROM ra_customer_trx_lines ctl,
ar_adjustments adj,
ra_customer_trx ct
WHERE ctl.customer_trx_id = p_customer_trx_id
AND ctl.br_adjustment_id = adj.adjustment_id
AND ct.customer_trx_id = ctl.br_ref_customer_trx_id
AND adj.status = 'A'
order by ctl.customer_trx_line_id;
| schedule been updated or not, if not
| then this routine will add the activity
| on the Bill or transaction to the
| installments
| to make this decision
| p_activity_amt previous activity amount
| p_activity_acctd_amt previous activity accounted amount
| p_closed_pymt_yn A Y value indicates that the Bill or
| transaction is a candidate for
| reconciliation
*==========================================================================*/
PROCEDURE Detect_Closure(p_customer_trx_id IN NUMBER ,
p_pay_sched_upd_yn IN VARCHAR2 ,
p_pay_sched_upd_cm_yn IN VARCHAR2 ,
p_activity_amt IN NUMBER ,
p_activity_acctd_amt IN NUMBER ,
p_ae_sys_rec IN ae_sys_rec_type,
p_closed_pymt_yn OUT NOCOPY VARCHAR2 ,
p_pay_class OUT NOCOPY VARCHAR2 ) IS
l_amount_due_remaining NUMBER := 0;
select sum(pay.amount_due_remaining) ,
sum(pay.acctd_amount_due_remaining) ,
max(pay.class)
into l_amount_due_remaining,
l_acctd_amount_due_remaining,
p_pay_class
from ar_payment_schedules pay
where pay.customer_trx_id = p_customer_trx_id;
arp_standard.debug('Detect_Closure: ' || 'Selected pay l_amount_due_remaining ' || l_amount_due_remaining);
arp_standard.debug('Detect_Closure: ' || 'Selected pay l_acctd_amount_due_remaining ' || l_acctd_amount_due_remaining);
arp_standard.debug('Detect_Closure: ' || 'Payment schedule not updated hence calculating remaining amounts ');
SELECT ctl.location_segment_id location_segment_id ,
decode(ctl.autotax,
'N','',
decode(ctl.location_segment_id,
'', decode(ctl.vat_tax_id,
'','',
ctl1.vat_tax_id, '',
ctl1.vat_tax_id),
'')) tax_group_code_id,
ctl.vat_tax_id tax_code_id,
gld.code_combination_id account,
sum(nvl(gld.amount,0)) amount,
sum(nvl(gld.acctd_amount,0)) acctd_amount,
max(nvl(ctl.taxable_amount,0)) taxable_amount,
max(decode(gld.account_class,
'TAX',
arpcurr.functional_amount(
nvl(ctl.taxable_amount,0),
p_ae_sys_rec.base_currency ,
p_cust_inv_rec.exchange_rate ,
p_ae_sys_rec.base_precision ,
p_ae_sys_rec.base_min_acc_unit),
'')) taxable_acctd_amount
FROM ra_customer_trx ct ,
ra_cust_trx_line_gl_dist gld,
ra_customer_trx_lines ctl,
ra_customer_trx_lines ctl1
where ct.customer_trx_id = p_customer_trx_id
and p_calling_point IN ('TRAN', 'BLTR')
and ct.customer_trx_id = gld.customer_trx_id
and gld.customer_trx_id = ctl.customer_trx_id
and gld.customer_trx_line_id = ctl.customer_trx_line_id
and gld.account_class = 'TAX'
and gld.collected_tax_ccid IS NOT NULL --deferred tax lines only
and gld.account_set_flag = 'N'
and ctl.link_to_cust_trx_line_id = ctl1.customer_trx_line_id --outer join not required here
and not exists (select 'x'
from ra_customer_trx_lines ctl2
where ctl2.customer_trx_id = p_customer_trx_id
and p_calling_point IN ('TRAN', 'BLTR')
and ctl2.autorule_complete_flag = 'N')
group by ctl.customer_trx_line_id ,
ctl.location_segment_id ,
decode(ctl.autotax,'N','',
decode(ctl.location_segment_id,
'', decode(ctl.vat_tax_id,
'','',
ctl1.vat_tax_id, '',
ctl1.vat_tax_id),
'')),
ctl.vat_tax_id ,
gld.code_combination_id
order by 1,2,3;
select ard.location_segment_id location_segment_id ,
ard.tax_group_code_id tax_group_code_id ,
ard.tax_code_id tax_code_id ,
ard.code_combination_id account ,
sum(nvl(ard.amount_dr,0) * -1 +
nvl(ard.amount_cr,0)) amount ,
sum(nvl(ard.acctd_amount_dr,0) * -1 +
nvl(ard.acctd_amount_cr,0)) acctd_amount ,
sum(nvl(ard.taxable_entered_dr,0) * -1 +
nvl(ard.taxable_entered_cr,0)) taxable_amount ,
sum(nvl(ard.taxable_accounted_dr,0) * -1 +
nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
from ar_distributions ard,
ar_receivable_applications app
where p_ae_sys_rec.sob_type = 'P'
and app.applied_customer_trx_id = p_customer_trx_id
and p_calling_point IN ('TRAN', 'BLTR')
and app.status = 'APP'
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ard.source_id = app.receivable_application_id
and ard.source_table = 'RA'
and ard.source_type = 'DEFERRED_TAX'
and decode(ard.source_type_secondary,
'RECONCILE', ard.source_id_secondary,
p_customer_trx_id) = p_customer_trx_id
group by ard.location_segment_id ,
ard.tax_group_code_id ,
ard.tax_code_id ,
ard.code_combination_id
/*-------------------------------------------------------------------------+
| Gets the accounting for adjustments on transactions from the accounting |
| table for reconciliation purposes. |
+-------------------------------------------------------------------------*/
UNION ALL--get accounting for adjustments on transaction
select ard.location_segment_id location_segment_id ,
ard.tax_group_code_id tax_group_code_id ,
ard.tax_code_id tax_code_id ,
ard.code_combination_id account ,
sum(nvl(ard.amount_dr,0) * -1 +
nvl(ard.amount_cr,0)) amount ,
sum(nvl(ard.acctd_amount_dr,0) * -1 +
nvl(ard.acctd_amount_cr,0)) acctd_amount ,
sum(nvl(ard.taxable_entered_dr,0) * -1 +
nvl(ard.taxable_entered_cr,0)) taxable_amount ,
sum(nvl(ard.taxable_accounted_dr,0) * -1 +
nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
from ar_distributions ard,
ar_adjustments adj
where p_ae_sys_rec.sob_type = 'P'
and adj.customer_trx_id = p_customer_trx_id
and p_calling_point IN ('TRAN', 'BLTR')
and adj.status = 'A'
and ard.source_id = adj.adjustment_id
and ard.source_table = 'ADJ'
and ard.source_type = 'DEFERRED_TAX'
and decode(ard.source_type_secondary,
'RECONCILE', ard.source_id_secondary,
p_customer_trx_id) = p_customer_trx_id
group by ard.location_segment_id ,
ard.tax_group_code_id ,
ard.tax_code_id ,
ard.code_combination_id
/*--------------------------------------------------------------------------+
| Gets the accounting for activity on a Bill to which the transactions has |
| been assigned. i.e. deferred tax accounting for transaction assignments |
| to the Bill. This is used to reconcile the transaction. p_customer_trx_id|
| is null when processing assignments on a Bill. So the statement below is |
| used for transactions only. |
+--------------------------------------------------------------------------*/
UNION ALL--get accounting on Bills for Transactions
select ard.location_segment_id location_segment_id ,
ard.tax_group_code_id tax_group_code_id ,
ard.tax_code_id tax_code_id ,
ard.code_combination_id account ,
sum(nvl(ard.amount_dr,0) * -1 +
nvl(ard.amount_cr,0)) amount ,
sum(nvl(ard.acctd_amount_dr,0) * -1 +
nvl(ard.acctd_amount_cr,0)) acctd_amount ,
sum(nvl(ard.taxable_entered_dr,0) * -1 +
nvl(ard.taxable_entered_cr,0)) taxable_amount ,
sum(nvl(ard.taxable_accounted_dr,0) * -1 +
nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
from ra_customer_trx_lines ctl,
ar_distributions ard
where p_ae_sys_rec.sob_type = 'P'
and ctl.br_ref_customer_trx_id = p_customer_trx_id
and p_calling_point IN ('TRAN', 'BLTR')
and ard.source_id_secondary = ctl.customer_trx_line_id
and ard.source_table_secondary = 'CTL'
and ard.source_type_secondary IN ('ASSIGNMENT', 'ASSIGNMENT_RECONCILE',
'RECONCILE')
and ard.source_type = 'DEFERRED_TAX'
group by ard.location_segment_id ,
ard.tax_group_code_id ,
ard.tax_code_id ,
ard.code_combination_id
/*--------------------------------------------------------------------------+
| Get the deferred tax accounting moved for the assignment on the Bill due |
| to activity on the Bill from the accounting table. The assignment line id|
| is used by the statement below. |
+--------------------------------------------------------------------------*/
UNION ALL--reconcile bill only
select ard.location_segment_id location_segment_id ,
ard.tax_group_code_id tax_group_code_id ,
ard.tax_code_id tax_code_id ,
ard.code_combination_id account ,
sum(nvl(ard.amount_dr,0) * -1 +
nvl(ard.amount_cr,0)) amount ,
sum(nvl(ard.acctd_amount_dr,0) * -1 +
nvl(ard.acctd_amount_cr,0)) acctd_amount ,
sum(nvl(ard.taxable_entered_dr,0) * -1 +
nvl(ard.taxable_entered_cr,0)) taxable_amount ,
sum(nvl(ard.taxable_accounted_dr,0) * -1 +
nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
from ar_distributions ard
where p_ae_sys_rec.sob_type = 'P'
and ard.source_id_secondary = p_br_cust_trx_line_id
and p_calling_point = 'BILL'
and ard.source_table_secondary = 'CTL'
and ard.source_type_secondary IN ('ASSIGNMENT', 'ASSIGNMENT_RECONCILE')
and ard.source_type = 'DEFERRED_TAX'
group by ard.location_segment_id ,
ard.tax_group_code_id ,
ard.tax_code_id ,
ard.code_combination_id
order by 1,2,3;
SELECT max(gld.collected_tax_ccid) ae_collected_tax_ccid
INTO l_collected_ccid
FROM ra_cust_trx_line_gl_dist gld,
ra_customer_trx_lines ctl
--ra_customer_trx_lines ctl1
WHERE ctl.customer_trx_id = p_customer_trx_id
AND gld.customer_trx_id = ctl.customer_trx_id
AND gld.customer_trx_line_id = ctl.customer_trx_line_id
AND gld.account_class = 'TAX'
AND gld.account_set_flag = 'N'
AND gld.collected_tax_ccid IS NOT NULL --deferred tax only
AND gld.code_combination_id = p_code_combination_id
-- AND (((p_location_segment_id IS NOT NULL)
-- AND (ctl.location_segment_id = nvl(p_location_segment_id,-999)))
--AND (p_tax_code_id IS NOT NULL)
AND ctl.vat_tax_id = nvl(p_tax_code_id,-999)
--AND ctl.link_to_cust_trx_line_id = ctl1.customer_trx_line_id
--AND ctl1.vat_tax_id = nvl(p_tax_group_code_id,ctl1.vat_tax_id)
AND not exists (select 'x'
from ra_customer_trx_lines ctl1
where ctl1.customer_trx_id = p_customer_trx_id
and ctl1.autorule_complete_flag = 'N');
SELECT max(gld.collected_tax_ccid) ae_collected_tax_ccid
INTO l_collected_ccid
FROM ra_cust_trx_line_gl_dist gld,
ra_customer_trx_lines ctl
--ra_customer_trx_lines ctl1
WHERE ctl.customer_trx_id = p_customer_trx_id
AND gld.customer_trx_id = ctl.customer_trx_id
AND gld.customer_trx_line_id = ctl.customer_trx_line_id
AND gld.account_class = 'TAX'
AND gld.account_set_flag = 'N'
AND gld.collected_tax_ccid IS NOT NULL --deferred tax only
-- AND gld.code_combination_id = p_code_combination_id
-- AND (((p_location_segment_id IS NOT NULL)
-- AND (ctl.location_segment_id = nvl(p_location_segment_id,-999)))
--AND (p_tax_code_id IS NOT NULL)
AND ctl.vat_tax_id = nvl(p_tax_code_id,-999)
--AND ctl.link_to_cust_trx_line_id = ctl1.customer_trx_line_id
--AND ctl1.vat_tax_id = nvl(p_tax_group_code_id,ctl1.vat_tax_id)
AND not exists (select 'x'
from ra_customer_trx_lines ctl1
where ctl1.customer_trx_id = p_customer_trx_id
and ctl1.autorule_complete_flag = 'N');
SELECT MAX(code_combination_id)
INTO l_collected_ccid
FROM
(SELECT ard.code_combination_id
FROM ar_distributions ard
WHERE ard.source_table = 'RA'
AND source_id IN (SELECT receivable_application_id
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_customer_trx_id)
AND ard.tax_code_id = nvl(p_tax_code_id,-999)
AND source_type = 'TAX'
AND source_type_secondary = 'PAYMENT'
UNION
SELECT ard.code_combination_id
FROM ar_distributions ard
WHERE ard.source_table = 'ADJ'
AND source_id IN (SELECT adjustment_id
FROM ar_adjustments
WHERE customer_trx_id = p_customer_trx_id
AND status = 'A')
AND ard.tax_code_id = nvl(p_tax_code_id,-999)
AND source_type = 'TAX'
);