[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Ae_Lines(p_ae_line_tbl IN ar_ae_alloc_rec_gt%ROWTYPE);
SELECT CODE_COMBINATION_ID
FROM ar_adjustments
WHERE adjustment_id = g_ae_doc_rec.document_id;
SELECT ar_distribution_split_s.NEXTVAL INTO g_id FROM DUAL;
SELECT legal_entity_id
INTO l_le_id
FROM ra_customer_trx
WHERE customer_trx_id = l_invoice_id;
update ar_distributions ard
set ref_prev_cust_trx_line_id = (select previous_customer_trx_line_id
from ra_customer_trx_lines
where customer_trx_line_id = ard.ref_customer_trx_line_id)
where source_id = g_ae_doc_rec.source_id
and source_table = 'RA'
and ref_customer_trx_line_id in (select customer_trx_line_id
from ra_customer_trx_lines ctl_cm,
ar_receivable_applications ra
where ra.receivable_application_id = g_ae_doc_rec.source_id
and ra.customer_trx_id = ctl_cm.customer_trx_id
and ctl_cm.previous_customer_trx_line_id is not null);
arp_standard.debug('CM ard rows updated : '||l_cnt);
update ar_distributions ard
set ref_prev_cust_trx_line_id = (select ref_customer_trx_line_id
from ar_distributions
where source_id = g_ae_doc_rec.source_id
and ref_prev_cust_trx_line_id = ard.ref_customer_trx_line_id
and rownum = 1)
where source_id = g_ae_doc_rec.source_id
and source_table = 'RA'
and ref_customer_trx_line_id in (select customer_trx_line_id
from ra_customer_trx_lines ctl_inv,
ar_receivable_applications ra
where ra.receivable_application_id = g_ae_doc_rec.source_id
and ra.applied_customer_trx_id = ctl_inv.customer_trx_id);
arp_standard.debug('INV ard rows updated : '||l_cnt);
* periodically delete the data from GT tables for every 1000 applications*
* for a receipt. * */
IF arp_det_dist_pkg.g_appln_count >= PG_DEL_FRM_GT_CNT THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('Exceeded upper limit for maximum number of rows in ra_ar_gt');
DELETE FROM ra_customer_trx_lines_gt;
arp_standard.debug('No of rows deleted from ra_customer_trx_lines_gt : '||l_cnt);
DELETE FROM ra_ar_gt;
arp_standard.debug('No of rows deleted from ra_ar_gt : '||l_cnt);
DELETE FROM ra_ar_amounts_gt;
arp_standard.debug('No of rows deleted from ra_ar_amounts_gt : '||l_cnt);
DELETE FROM ar_base_dist_amts_gt;
arp_standard.debug('No of rows deleted from ar_base_dist_amts_gt : '||l_cnt);
DELETE FROM ar_line_app_detail_gt;
arp_standard.debug('No of rows deleted from ar_line_app_detail_gt : '||l_cnt);
DELETE FROM ar_ae_alloc_rec_gt;
arp_standard.debug('No of rows deleted from ar_ae_alloc_rec_gt : '||l_cnt);
select gld.customer_trx_id
into l_dummy
from ra_cust_trx_line_gl_dist gld
where gld.account_class = 'TAX'
and gld.customer_trx_id = p_invoice_id
and gld.collected_tax_ccid IS NOT NULL
group by gld.customer_trx_id;
| Modified select to get the acctd amount due remaining from the correct |
| sob (MRC TRIGGER REPLACEMENT) |
+----------------------------------------------------------------------------*/
IF (NVL(g_ae_sys_rec.sob_type,'P') = 'P') THEN
select fc.precision ,
fc.minimum_accountable_unit ,
pay.amount_due_remaining ,
pay.acctd_amount_due_remaining ,
pay.amount_due_original
into g_ae_curr_rec.precision ,
g_ae_curr_rec.minimum_accountable_unit ,
g_amount_due_remaining ,
g_acctd_amount_due_remaining ,
g_amount_due_original
from ra_customer_trx ct ,
ar_payment_schedules pay ,
fnd_currencies fc
where ct.customer_trx_id = p_invoice_id
and pay.customer_trx_id = ct.customer_trx_id
and pay.payment_schedule_id = p_payment_schedule_id
and ct.invoice_currency_code = fc.currency_code;
SELECT /*+ INDEX(ar_ae_alloc_rec_gt AR_AE_ALLOC_REC_GT_N3) */
decode(ae_collected_tax_ccid,
'',ae_account_class,
'DEFTAX') ae_account_class ,
SUM(ae_amount) sum_ae_amount ,
SUM(ae_acctd_amount) sum_ae_acctd_amount ,
max(ae_code_combination_id) ae_code_combination_id,
max(decode(ae_override_ccid1,'',2,1)) ae_override_ccid1 ,
max(decode(ae_override_ccid2,'',2,1)) ae_override_ccid2 ,
count(ae_account_class) ae_count
FROM ar_ae_alloc_rec_gt
WHERE ae_id = g_id
GROUP BY decode(ae_collected_tax_ccid,
'',ae_account_class,
'DEFTAX');
SELECT /*+ INDEX(ar_ae_alloc_rec_gt AR_AE_ALLOC_REC_GT_N3) */
ae_account_class ae_account_class ,
SUM(ae_amount) sum_ae_amount ,
SUM(ae_acctd_amount) sum_ae_acctd_amount ,
max(ae_code_combination_id) ae_code_combination_id,
'' ae_override_ccid1 ,
'' ae_override_ccid2 ,
count(ae_account_class) ae_count
FROM ar_ae_alloc_rec_gt
WHERE ae_id = g_id
AND ae_account_class <> 'TAX'
GROUP BY ae_account_class;
SELECT /*+ INDEX(ar_ae_alloc_rec_gt AR_AE_ALLOC_REC_GT_N1) */ *
FROM ar_ae_alloc_rec_gt
WHERE ae_id = g_id
AND ae_account_class = p_type
AND ae_customer_trx_line_id = p_trx_line_id
AND ae_customer_trx_id = p_invoice_id;
SELECT decode(
max(decode(b.account_class,'REV',b.code_combination_id,0)), -- REV row gets priority
0,max(b.code_combination_id), -- If no REV row, pick max of ccid as usual
max(decode(b.account_class,'REV',b.code_combination_id,0))
),
ctl.ae_cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist b,
ar_ae_alloc_rec_gt ctl
WHERE ctl.ae_tax_link_id = b.customer_trx_line_id
AND ctl.ae_account_class = 'TAX'
GROUP BY ctl.ae_cust_trx_line_gl_dist_id;
| Insert Tax distributions |
| MRC Trigger Replacement. Modified to insert currency sensitive columns |
+----------------------------------------------------------------------------*/
insert into ar_ae_alloc_rec_gt (
ae_id ,
ae_account_class ,
ae_customer_trx_id ,
ae_customer_trx_line_id ,
ae_cust_trx_line_gl_dist_id ,
ae_link_to_cust_trx_line_id ,
ae_tax_type ,
ae_code_combination_id ,
ae_collected_tax_ccid ,
ae_line_amount ,
ae_amount ,
ae_acctd_amount ,
ae_tax_group_code_id ,
ae_tax_id ,
ae_taxable_amount ,
ae_taxable_acctd_amount ,
ae_adj_ccid ,
ae_edisc_ccid ,
ae_unedisc_ccid ,
ae_finchrg_ccid ,
ae_adj_non_rec_tax_ccid ,
ae_edisc_non_rec_tax_ccid ,
ae_unedisc_non_rec_tax_ccid ,
ae_finchrg_non_rec_tax_ccid ,
ae_override_ccid1 ,
ae_override_ccid2 ,
ae_tax_link_id , -- link_to_cust_trx_line_id
ae_tax_link_id_ed_adj , -- link_to_cust_trx_line_id
ae_tax_link_id_uned , -- link_to_cust_trx_line_id
ae_tax_link_id_act , -- left null populate later
ae_pro_amt ,
ae_pro_acctd_amt ,
ae_pro_frt_chrg_amt ,
ae_pro_frt_chrg_acctd_amt ,
ae_pro_taxable_amt ,
ae_pro_taxable_acctd_amt ,
ae_pro_split_taxable_amt ,
ae_pro_split_taxable_acctd_amt ,
ae_pro_recov_taxable_amt ,
ae_pro_recov_taxable_acctd_amt ,
ae_pro_def_tax_amt ,
ae_pro_def_tax_acctd_amt ,
ae_summarize_flag ,
ae_counted_flag ,
ae_autotax ,
ae_sum_alloc_amt ,
ae_sum_alloc_acctd_amt ,
ae_tax_line_count ,
ref_account_class ,
activity_bucket ,
ae_ref_line_id,
ae_from_pro_amt,
ae_from_pro_acctd_amt,
ref_dist_ccid,
ref_mf_dist_flag
)
SELECT
g_id ae_id,
gld.account_class ae_account_class,
ctl.customer_trx_id ae_customer_trx_id,
ctl.customer_trx_line_id ae_customer_trx_line_id,
gld.cust_trx_line_gl_dist_id ae_cust_trx_line_gl_dist_id ,
nvl(ctl.link_to_cust_trx_line_id,-9999) ae_link_to_cust_trx_line_id,
decode(ctl.location_segment_id,
'','VAT',
'LOC') ae_tax_type,
gld.code_combination_id ae_code_combination_id,
gld.collected_tax_ccid ae_collected_tax_ccid,
ctl.extended_amount ae_line_amount,
nvl(gld.amount,0) ae_amount,
NVL(gld.acctd_amount,0) ae_acctd_amount,
decode(ctl.location_segment_id,
'',
decode(nvl(ctl.autotax,'Y'),
'N', '',
decode(nvl(line.location_segment_id,line.vat_tax_id),
'','',
nvl(ctl.location_segment_id, ctl.vat_tax_id),'',
nvl(line.location_segment_id,line.vat_tax_id))),
'') ae_tax_group_code_id,
nvl(ctl.location_segment_id,ctl.vat_tax_id) ae_tax_id,
ctl.taxable_amount ae_taxable_amount,
arpcurr.functional_amount(nvl(ctl.taxable_amount,0) ,
g_ae_sys_rec.base_currency ,
g_cust_inv_rec.exchange_rate,
g_ae_sys_rec.base_precision ,
g_ae_sys_rec.base_min_acc_unit) ae_taxable_acctd_amount,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'ADJ') ae_adj_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'EDISC') ae_edisc_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'UNEDISC') ae_unedisc_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'FINCHRG') ae_finchrg_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'ADJ_NON_REC') ae_adj_non_rec_tax_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'EDISC_NON_REC') ae_edisc_non_rec_tax_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'UNEDISC_NON_REC') ae_unedisc_non_rec_tax_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'FINCHRG_NON_REC') ae_finchrg_non_rec_tax_ccid,
decode(g_ae_rule_rec.tax_code_source1,
'INVOICE', decode(g_ae_rule_rec.tax_recoverable_flag1,
'N',
decode(g_ae_doc_rec.source_table,
'RA',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'EDISC_NON_REC'),
'ADJ',
decode(g_ae_doc_rec.document_type,
'ADJUSTMENT',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'ADJ_NON_REC'),
'FINANCE_CHARGES',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'FINCHRG_NON_REC'),
''),
''),
''),
'ACTIVITY', g_ae_rule_rec.act_tax_non_rec_ccid1,
'') ae_override_ccid1,
decode(g_ae_rule_rec.tax_code_source2,
'INVOICE',decode(g_ae_rule_rec.tax_recoverable_flag2,
'N', decode(g_ae_doc_rec.source_table,
'RA',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'UNEDISC_NON_REC'),
''),
''),
'ACTIVITY', g_ae_rule_rec.act_tax_non_rec_ccid2,
'') ae_override_ccid2,
ctl.link_to_cust_trx_line_id ae_tax_link_id,
ctl.link_to_cust_trx_line_id ae_tax_link_id_ed_adj,
ctl.link_to_cust_trx_line_id ae_tax_link_id_uned,
ctl.link_to_cust_trx_line_id ae_tax_link_id_act,
det.amount ae_pro_amt,
det.acctd_amount ae_pro_acctd_amt,
0 ae_pro_frt_chrg_amt,
0 ae_pro_frt_chrg_acctd_amt,
det.taxable_amount ae_pro_taxable_amt,
det.taxable_acctd_amount ae_pro_taxable_acctd_amt,
det.taxable_amount ae_pro_split_taxable_amt ,
det.taxable_acctd_amount ae_pro_split_taxable_acctd_amt,
det.taxable_amount ae_pro_recov_taxable_amt,
det.taxable_acctd_amount ae_pro_recov_taxable_acctd_amt,
0 ae_pro_def_tax_amt,
0 ae_pro_def_tax_acctd_amt,
'N' ae_summarize_flag,
'N' ae_counted_flag,
ctl.autotax ae_autotax,
0 ae_sum_alloc_amt,
0 ae_sum_alloc_acctd_amt,
Get_Tax_Count(ctl.link_to_cust_trx_line_id) ae_tax_line_count,
det.ref_account_class ref_account_class,
det.activity_bucket activity_bucket,
det.ref_line_id ae_ref_line_id,
det.from_amount ae_from_pro_amt,
det.from_acctd_amount ae_from_pro_acctd_amt,
det.ccid ref_dist_ccid,
det.ref_mf_dist_flag
FROM ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist gld,
ra_customer_trx_lines line,
ar_line_app_detail_gt det
where ctl.customer_trx_id = p_invoice_id
and ctl.line_type = 'TAX'
and gld.customer_trx_line_id = ctl.customer_trx_line_id
and gld.account_set_flag = 'N'
and ctl.link_to_cust_trx_line_id = line.customer_trx_line_id (+)
and 'LINE' = line.line_type (+)
AND det.ref_customer_trx_id = ctl.customer_trx_id
AND det.ref_customer_trx_line_id = ctl.customer_trx_line_id
AND det.ref_cust_trx_line_gl_dist_id = gld.cust_trx_line_gl_dist_id
AND det.gt_id = g_id
AND det.ledger_id = g_ae_sys_rec.set_of_books_id;
/* and not exists (select 'x'
from ra_customer_trx_lines ctl1
where ctl1.customer_trx_id = p_invoice_id
and ctl1.autorule_complete_flag = 'N') */
/* nvl(tax.location_segment_id,tax.vat_tax_id),
decode(tax.location_segment_id,
'','VAT',
'LOC') */
arp_standard.debug('p_process_ed_adj:'||p_process_ed_adj);
UPDATE ar_ae_alloc_rec_gt
SET ae_code_combination_id = l_ccid_tab(k)
WHERE ae_cust_trx_line_gl_dist_id = l_ctlgd_tab(k);
insert into ar_ae_alloc_rec_gt (
ae_id ,
ae_account_class ,
ae_customer_trx_id ,
ae_customer_trx_line_id ,
ae_cust_trx_line_gl_dist_id ,
ae_link_to_cust_trx_line_id ,
ae_tax_type ,
ae_code_combination_id ,
ae_collected_tax_ccid ,
ae_line_amount ,
ae_amount ,
ae_acctd_amount ,
ae_tax_group_code_id ,
ae_tax_id ,
ae_taxable_amount ,
ae_taxable_acctd_amount ,
ae_adj_ccid ,
ae_edisc_ccid ,
ae_unedisc_ccid ,
ae_finchrg_ccid ,
ae_adj_non_rec_tax_ccid ,
ae_edisc_non_rec_tax_ccid ,
ae_unedisc_non_rec_tax_ccid ,
ae_finchrg_non_rec_tax_ccid ,
ae_override_ccid1 ,
ae_override_ccid2 ,
ae_tax_link_id ,
ae_tax_link_id_ed_adj ,
ae_tax_link_id_uned ,
ae_tax_link_id_act ,
ae_pro_amt ,
ae_pro_acctd_amt ,
ae_pro_frt_chrg_amt ,
ae_pro_frt_chrg_acctd_amt ,
ae_pro_taxable_amt ,
ae_pro_taxable_acctd_amt ,
ae_pro_split_taxable_amt ,
ae_pro_split_taxable_acctd_amt ,
ae_pro_recov_taxable_amt ,
ae_pro_recov_taxable_acctd_amt ,
ae_pro_def_tax_amt ,
ae_pro_def_tax_acctd_amt ,
ae_summarize_flag ,
ae_counted_flag ,
ae_autotax ,
ae_sum_alloc_amt ,
ae_sum_alloc_acctd_amt ,
ae_tax_line_count ,
ref_account_class ,
activity_bucket ,
AE_REF_LINE_ID,
ae_from_pro_amt,
ae_from_pro_acctd_amt,
ref_dist_ccid,
ref_mf_dist_flag
)
SELECT
g_id ae_id,
'TAX' ae_account_class,
det.ref_customer_trx_id ae_customer_trx_id,
det.ref_customer_trx_line_id ae_customer_trx_line_id,
det.ref_cust_trx_line_gl_dist_id ae_cust_trx_line_gl_dist_id ,
'' ae_link_to_cust_trx_line_id,
decode(ctl.location_segment_id,
'','VAT',
'LOC') ae_tax_type,
--'' ae_tax_type,
'' ae_code_combination_id,
'' ae_collected_tax_ccid,
'' ae_line_amount,
'' ae_amount,
'' ae_acctd_amount,
decode(ctl.location_segment_id,
'',
decode(nvl(ctl.autotax,'Y'),
'N', '',
decode(nvl(line.location_segment_id,line.vat_tax_id),
'','',
nvl(ctl.location_segment_id, ctl.vat_tax_id),'',
nvl(line.location_segment_id,line.vat_tax_id))),
'') ae_tax_group_code_id,
nvl(ctl.location_segment_id,ctl.vat_tax_id) ae_tax_id,
--'' ae_tax_group_code_id,
--'' ae_tax_id,
det.amount ae_taxable_amount,
arpcurr.functional_amount(nvl(det.amount,0) ,
g_ae_sys_rec.base_currency ,
g_cust_inv_rec.exchange_rate,
g_ae_sys_rec.base_precision ,
g_ae_sys_rec.base_min_acc_unit) ae_taxable_acctd_amount,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'ADJ') ae_adj_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'EDISC') ae_edisc_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'UNEDISC') ae_unedisc_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'FINCHRG') ae_finchrg_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'ADJ_NON_REC') ae_adj_non_rec_tax_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'EDISC_NON_REC') ae_edisc_non_rec_tax_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'UNEDISC_NON_REC') ae_unedisc_non_rec_tax_ccid,
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'FINCHRG_NON_REC') ae_finchrg_non_rec_tax_ccid,
decode(g_ae_rule_rec.tax_code_source1,
'INVOICE', decode(g_ae_rule_rec.tax_recoverable_flag1,
'N',
decode(g_ae_doc_rec.source_table,
'RA',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'EDISC_NON_REC'),
'ADJ',
decode(g_ae_doc_rec.document_type,
'ADJUSTMENT',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'ADJ_NON_REC'),
'FINANCE_CHARGES',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'FINCHRG_NON_REC'),
''),
''),
''),
'ACTIVITY', g_ae_rule_rec.act_tax_non_rec_ccid1,
'') ae_override_ccid1,
decode(g_ae_rule_rec.tax_code_source2,
'INVOICE',decode(g_ae_rule_rec.tax_recoverable_flag2,
'N', decode(g_ae_doc_rec.source_table,
'RA',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
null,
'UNEDISC_NON_REC'),
''),
''),
'ACTIVITY', g_ae_rule_rec.act_tax_non_rec_ccid2,
'') ae_override_ccid2,
-- '' ae_adj_ccid,
-- '' ae_edisc_ccid,
-- '' ae_unedisc_ccid,
-- '' ae_finchrg_ccid,
-- '' ae_adj_non_rec_tax_ccid,
-- '' ae_edisc_non_rec_tax_ccid,
-- '' ae_unedisc_non_rec_tax_ccid,
-- '' ae_finchrg_non_rec_tax_ccid,
-- '' ae_override_ccid1,
-- '' ae_override_ccid2,
'' ae_tax_link_id,
'' ae_tax_link_id_ed_adj,
'' ae_tax_link_id_uned,
'' ae_tax_link_id_act,
det.amount ae_pro_amt,
det.acctd_amount ae_pro_acctd_amt,
0 ae_pro_frt_chrg_amt,
0 ae_pro_frt_chrg_acctd_amt,
det.taxable_amount ae_pro_taxable_amt,
det.taxable_acctd_amount ae_pro_taxable_acctd_amt,
det.taxable_amount ae_pro_split_taxable_amt ,
det.taxable_acctd_amount ae_pro_split_taxable_acctd_amt,
det.taxable_amount ae_pro_recov_taxable_amt,
det.taxable_acctd_amount ae_pro_recov_taxable_acctd_amt,
0 ae_pro_def_tax_amt,
0 ae_pro_def_tax_acctd_amt,
'N' ae_summarize_flag,
'N' ae_counted_flag,
'' ae_autotax,
0 ae_sum_alloc_amt,
0 ae_sum_alloc_acctd_amt,
'' ae_tax_line_count,
det.ref_account_class ref_account_class,
det.activity_bucket activity_bucket,
det.ref_line_id ae_line_id,
det.from_amount ae_from_pro_amt,
det.from_acctd_amount ae_from_pro_acctd_amt,
det.ccid ref_dist_ccid,
det.ref_mf_dist_flag ref_mf_dist_flag
FROM ar_line_app_detail_gt det,
ra_customer_trx_lines ctl,
ra_customer_trx_lines line,
( SELECT customer_trx_id,
nvl(decode(g_ae_doc_rec.document_type,'ADJUSTMENT',-9999,location_segment_id),-9999) location_segment_id,
nvl(decode(g_ae_doc_rec.document_type,'ADJUSTMENT',-9999,vat_tax_id),-9999) tax_code_id,
min(customer_trx_line_id) customer_trx_line_id
FROM ra_customer_trx_lines tax
GROUP BY customer_trx_id,
decode(g_ae_doc_rec.document_type,'ADJUSTMENT',-9999,location_segment_id),
decode(g_ae_doc_rec.document_type,'ADJUSTMENT',-9999,vat_tax_id) ) tax_link
WHERE det.ref_customer_trx_id = p_invoice_id
AND gt_id = g_id
AND det.ref_customer_trx_id = tax_link.customer_trx_id
AND NVL(det.location_segment_id,-9999) = tax_link.location_segment_id
AND NVL(det.tax_code_id,-9999) = tax_link.tax_code_id
AND ctl.customer_trx_line_id = tax_link.customer_trx_line_id
AND ctl.customer_trx_id = det.ref_customer_trx_id
AND ctl.link_to_cust_trx_line_id = line.customer_trx_line_id (+)
AND 'LINE' = line.line_type (+)
AND det.ledger_id = g_ae_sys_rec.set_of_books_id
AND ref_customer_trx_line_id IN (-8);
arp_standard.debug('Get_Invoice_Distributions: ' || 'Inserting Revenue lines');
| Insert the Revenue, Unearned, Receivable, Suppense, Tax lines into the |
| temporary allocation table for computation purposes to build final |
| accounting |
+----------------------------------------------------------------------------*/
--note we have removed the outer join to lines table for REC,
-- since UNBILL and UNEARN
--always have a customer_trx_line_id
-- MRC Trigger Replacement: get currency sensitive data
insert into ar_ae_alloc_rec_gt (
ae_id ,
ae_account_class ,
ae_customer_trx_id ,
ae_customer_trx_line_id ,
ae_cust_trx_line_gl_dist_id ,
ae_link_to_cust_trx_line_id ,
ae_tax_type ,
ae_code_combination_id ,
ae_collected_tax_ccid ,
ae_line_amount ,
ae_amount ,
ae_acctd_amount ,
ae_tax_group_code_id ,
ae_tax_id ,
ae_taxable_amount ,
ae_taxable_acctd_amount ,
ae_adj_ccid ,
ae_edisc_ccid ,
ae_unedisc_ccid ,
ae_finchrg_ccid ,
ae_adj_non_rec_tax_ccid ,
ae_edisc_non_rec_tax_ccid ,
ae_unedisc_non_rec_tax_ccid ,
ae_finchrg_non_rec_tax_ccid ,
ae_override_ccid1 ,
ae_override_ccid2 ,
ae_tax_link_id ,
ae_tax_link_id_ed_adj ,
ae_tax_link_id_uned ,
ae_tax_link_id_act ,
ae_pro_amt ,
ae_pro_acctd_amt ,
ae_pro_frt_chrg_amt ,
ae_pro_frt_chrg_acctd_amt ,
ae_pro_taxable_amt ,
ae_pro_taxable_acctd_amt ,
ae_pro_split_taxable_amt ,
ae_pro_split_taxable_acctd_amt ,
ae_pro_recov_taxable_amt ,
ae_pro_recov_taxable_acctd_amt ,
ae_pro_def_tax_amt ,
ae_pro_def_tax_acctd_amt ,
ae_summarize_flag ,
ae_counted_flag ,
ae_autotax ,
ae_sum_alloc_amt ,
ae_sum_alloc_acctd_amt ,
ae_tax_line_count ,
ref_account_class ,
activity_bucket,
ae_ref_line_id,
ae_from_pro_amt,
ae_from_pro_acctd_amt,
--{ref_dist_ccid
ref_dist_ccid,
ref_mf_dist_flag
--}
)
SELECT g_id ae_id,
decode(gld.account_class,
'REV' ,'REVEARN',
'CHARGES' ,'REVEARN',
'SUSPENSE','REVEARN',
'UNBILL' ,'REVUNEARN',
'UNEARN' ,'REVUNEARN',
'FREIGHT' ,'FREIGHT') ae_account_class,
ctl.customer_trx_id ae_customer_trx_id,
ctl.customer_trx_line_id ae_customer_trx_line_id,
gld.cust_trx_line_gl_dist_id ae_cust_trx_line_gl_dist_id ,
nvl(ctl.link_to_cust_trx_line_id,
-9999) ae_link_to_cust_trx_line_id,
decode(gld.account_class,
'FREIGHT','FREIGHT',
'REV') ae_tax_type,
gld.code_combination_id ae_code_combination_id,
gld.collected_tax_ccid ae_collected_tax_ccid,
decode(gld.account_class,
'REV', nvl(ctl.revenue_amount,0),
'FREIGHT', nvl(ctl.revenue_amount,0),
'SUSPENSE',(ctl.extended_amount -
nvl(ctl.revenue_amount,0)),
ctl.extended_amount) ae_line_amount,
nvl(gld.amount,0) ae_amount,
NVL(gld.acctd_amount,0) ae_acctd_amount,
'' ae_tax_group_code_id,
'' ae_tax_id,
'' ae_taxable_amount,
'' ae_taxable_acctd_amount,
'' ae_adj_ccid,
'' ae_edisc_ccid,
'' ae_unedisc_ccid,
'' ae_finchrg_ccid,
'' ae_adj_non_rec_tax_ccid,
'' ae_edisc_non_rec_tax_ccid,
'' ae_unedisc_non_rec_tax_ccid,
'' ae_finchrg_non_rec_tax_ccid,
decode(l_override1,
'Y',
decode(g_ae_rule_rec.gl_account_source1,
'ACTIVITY_GL_ACCOUNT',
g_ae_rule_rec.code_combination_id1,
'TAX_CODE_ON_INVOICE',b5.override_ccid1,
''),
'') ae_override_ccid1,
/*
DECODE(l_override1,
'Y',
DECODE( DECODE(det.activity_bucket, 'APP_LINE', 'ACTIVITY_GL_ACCOUNT',
'APP_TAX' , 'ACTIVITY_GL_ACCOUNT',
'APP_FRT' , 'ACTIVITY_GL_ACCOUNT',
'APP_CHRG', 'ACTIVITY_GL_ACCOUNT',
g_ae_rule_rec.gl_account_source1),
'ACTIVITY_GL_ACCOUNT',
DECODE(det.activity_bucket,'APP_LINE',g_ae_rule_rec.receivable_account,
'APP_TAX' ,g_ae_rule_rec.receivable_account,
'APP_FRT' ,g_ae_rule_rec.receivable_account,
'APP_CHRG',g_ae_rule_rec.receivable_account,
g_ae_rule_rec.code_combination_id1),
'TAX_CODE_ON_INVOICE',b5.override_ccid1,
''),
'') ae_override_ccid1,
*/
decode(l_override2,
'Y',
decode(g_ae_rule_rec.gl_account_source2,
'ACTIVITY_GL_ACCOUNT',
g_ae_rule_rec.code_combination_id2,
'TAX_CODE_ON_INVOICE',b5.override_ccid2,
''),
'') ae_override_ccid2 ,
-- '' ae_tax_link_id ,
-- '' ae_tax_link_id_ed_adj ,
-- '' ae_tax_link_id_uned ,
ctl.customer_trx_line_id ae_tax_link_id,
ctl.customer_trx_line_id ae_tax_link_id_ed_adj,
ctl.customer_trx_line_id ae_tax_link_id_uned,
ctl.customer_trx_line_id ae_tax_link_id_act ,
det.amount ae_pro_amt ,
det.acctd_amount ae_pro_acctd_amt ,
0 ae_pro_frt_chrg_amt ,
0 ae_pro_frt_chrg_acctd_amt,
det.taxable_amount ae_pro_taxable_amt,
det.taxable_acctd_amount ae_pro_taxable_acctd_amt,
det.taxable_amount ae_pro_split_taxable_amt ,
det.taxable_acctd_amount ae_pro_split_taxable_acctd_amt,
det.taxable_amount ae_pro_recov_taxable_amt,
det.taxable_acctd_amount ae_pro_recov_taxable_acctd_amt,
0 ae_pro_def_tax_amt ,
0 ae_pro_def_tax_acctd_amt ,
'N' ae_summarize_flag ,
'N' ae_counted_flag ,
'' ae_autotax ,
0 ae_sum_alloc_amt ,
0 ae_sum_alloc_acctd_amt ,
0 ae_tax_line_count ,
det.ref_account_class ref_account_class,
det.activity_bucket activity_bucket,
det.ref_line_id ae_ref_line_id,
det.from_amount ae_from_pro_amt,
det.from_acctd_amount ae_from_pro_acctd_amt,
--{ref_dist_ccid
det.ccid ref_dist_ccid,
det.ref_mf_dist_flag ref_mf_dist_flag
--}
from ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist gld,
ar_line_app_detail_gt det,
(select b4.ae_link_to_cust_trx_line_id ae_link_to_cust_trx_line_id,
max(decode(g_ae_rule_rec.gl_account_source1,
'TAX_CODE_ON_INVOICE',
decode(g_ae_doc_rec.source_table,
'RA', b4.ae_edisc_ccid,
'ADJ',decode(g_ae_doc_rec.document_type,
'ADJUSTMENT', b4.ae_adj_ccid,
'FINANCE_CHARGES',b4.ae_finchrg_ccid,
''),
''),
'')) override_ccid1,
max(decode(g_ae_rule_rec.gl_account_source2,
'TAX_CODE_ON_INVOICE', decode(g_ae_doc_rec.source_table,
'RA', b4.ae_unedisc_ccid,
''),
'')) override_ccid2
from ar_ae_alloc_rec_gt b4
where b4.rowid IN
(select /*+ INDEX(b3 AR_AE_ALLOC_REC_GT_N3) */
min(b3.rowid)
from ar_ae_alloc_rec_gt b3
where b3.ae_id = g_id
and b3.ae_account_class = 'TAX'
and (((decode(g_ae_doc_rec.source_table,
'RA', decode(b3.ae_edisc_ccid,
'','N',
'Y'),
'ADJ',decode(g_ae_doc_rec.document_type,
'ADJUSTMENT', decode(b3.ae_adj_ccid,
'','N',
'Y'),
'FINANCE_CHARGES',decode(b3.ae_finchrg_ccid,
'','N',
'Y')),
'N') = 'Y')
AND (l_override1 = 'Y')
AND (g_ae_rule_rec.gl_account_source1 = 'TAX_CODE_ON_INVOICE'))
OR
((decode(g_ae_doc_rec.source_table,
'RA', decode(b3.ae_unedisc_ccid,
'','N',
'Y'),
'N') = 'Y')
AND (l_override2 = 'Y')
AND (g_ae_rule_rec.gl_account_source2 = 'TAX_CODE_ON_INVOICE')))
group by b3.ae_link_to_cust_trx_line_id)
group by b4.ae_link_to_cust_trx_line_id
-- Bug 6719986 Added union sql to get the rows for TAX line of zero percentage tax.
UNION
select ctl.link_to_cust_trx_line_id ae_link_to_cust_trx_line_id,
max(decode(g_ae_rule_rec.gl_account_source1,
'TAX_CODE_ON_INVOICE',
decode(g_ae_doc_rec.source_table,
'RA', arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'EDISC'),
'ADJ',decode(g_ae_doc_rec.document_type,
'ADJUSTMENT',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'ADJ'),
'FINANCE_CHARGES',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'FINCHRG'),
''),
''),
'')) override_ccid1,
max(decode(g_ae_rule_rec.gl_account_source2,
'TAX_CODE_ON_INVOICE', decode(g_ae_doc_rec.source_table,
'RA',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'UNEDISC'),
''),
'')) override_ccid2
from ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist gld
where gld.customer_trx_line_id = ctl.customer_trx_line_id
and gld.account_set_flag = 'N'
and ctl.customer_trx_id = p_invoice_id
and ctl.rowid in
(select min(ctl1.rowid)
from ra_customer_trx_lines ctl1,
(select /*+ INDEX(b3 AR_AE_ALLOC_REC_GT_N3) */
count(*) tax_count
from ar_ae_alloc_rec_gt b3
where b3.ae_id = g_id
and b3.ae_account_class = 'TAX') tx
where ctl1.customer_trx_id = p_invoice_id
and ctl1.line_type = 'TAX'
and tx.tax_count = 0
group by ctl1.link_to_cust_trx_line_id)
group by ctl.link_to_cust_trx_line_id
) b5
where ctl.customer_trx_id = p_invoice_id
AND ctl.line_type IN ('LINE','FREIGHT','CB','CHARGES')
--and gld.customer_trx_id = ctl.customer_trx_id
and gld.customer_trx_line_id = ctl.customer_trx_line_id
AND gld.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','CHARGES')
and gld.account_set_flag = 'N'
and decode(ctl.line_type,
'FREIGHT', ctl.link_to_cust_trx_line_id, --first available tax code netexpense account
ctl.customer_trx_line_id) = b5.ae_link_to_cust_trx_line_id (+)
AND det.ref_customer_trx_id = ctl.customer_trx_id
AND det.ref_customer_trx_line_id = ctl.customer_trx_line_id
AND det.ref_cust_trx_line_gl_dist_id = gld.cust_trx_line_gl_dist_id
AND det.ledger_id = g_ae_sys_rec.set_of_books_id
AND det.gt_id = g_id;
/* and not exists (select 'x'
from ra_customer_trx_lines ctl1
where ctl1.customer_trx_id = p_invoice_id
and ctl1.autorule_complete_flag = 'N') */
-- group by decode(gld.account_class,
-- 'REV' ,'REVEARN',
-- 'SUSPENSE','REVEARN',
-- 'UNBILL' ,'REVUNEARN',
-- 'UNEARN' ,'REVUNEARN',
-- 'FREIGHT' ,'FREIGHT'),
-- decode(gld.account_class,
-- 'FREIGHT','FREIGHT',
-- 'REV') ,
-- gld.cust_trx_line_gl_dist_id ,
-- ctl.customer_trx_id ,
-- nvl(ctl.link_to_cust_trx_line_id,-9999) ,
-- ctl.customer_trx_line_id ,
-- gld.code_combination_id ,
-- gld.collected_tax_ccid ;
insert into ar_ae_alloc_rec_gt (
ae_id ,
ae_account_class ,
ae_customer_trx_id ,
ae_customer_trx_line_id ,
ae_cust_trx_line_gl_dist_id ,
ae_link_to_cust_trx_line_id ,
ae_tax_type ,
ae_code_combination_id ,
ae_collected_tax_ccid ,
ae_line_amount ,
ae_amount ,
ae_acctd_amount ,
ae_tax_group_code_id ,
ae_tax_id ,
ae_taxable_amount ,
ae_taxable_acctd_amount ,
ae_adj_ccid ,
ae_edisc_ccid ,
ae_unedisc_ccid ,
ae_finchrg_ccid ,
ae_adj_non_rec_tax_ccid ,
ae_edisc_non_rec_tax_ccid ,
ae_unedisc_non_rec_tax_ccid ,
ae_finchrg_non_rec_tax_ccid ,
ae_override_ccid1 ,
ae_override_ccid2 ,
ae_tax_link_id ,
ae_tax_link_id_ed_adj ,
ae_tax_link_id_uned ,
ae_tax_link_id_act ,
ae_pro_amt ,
ae_pro_acctd_amt ,
ae_pro_frt_chrg_amt ,
ae_pro_frt_chrg_acctd_amt ,
ae_pro_taxable_amt ,
ae_pro_taxable_acctd_amt ,
ae_pro_split_taxable_amt ,
ae_pro_split_taxable_acctd_amt ,
ae_pro_recov_taxable_amt ,
ae_pro_recov_taxable_acctd_amt ,
ae_pro_def_tax_amt ,
ae_pro_def_tax_acctd_amt ,
ae_summarize_flag ,
ae_counted_flag ,
ae_autotax ,
ae_sum_alloc_amt ,
ae_sum_alloc_acctd_amt ,
ae_tax_line_count ,
ref_account_class ,
activity_bucket ,
ae_ref_line_id,
ae_from_pro_amt,
ae_from_pro_acctd_amt,
--{ref_dist_ccid
ref_dist_ccid,
ref_mf_dist_flag
--}
)
SELECT
g_id ae_id,
DECODE(det.ref_account_class,'REV','REVEARN',
det.ref_account_class) ae_account_class,
det.ref_customer_trx_id ae_customer_trx_id,
det.ref_customer_trx_line_id ae_customer_trx_line_id,
det.ref_cust_trx_line_gl_dist_id ae_cust_trx_line_gl_dist_id ,
'' ae_link_to_cust_trx_line_id,
'' ae_tax_type,
'' ae_code_combination_id,
'' ae_collected_tax_ccid,
'' ae_line_amount,
'' ae_amount,
'' ae_acctd_amount,
'' ae_tax_group_code_id,
'' ae_tax_id,
det.amount ae_taxable_amount,
arpcurr.functional_amount(nvl(det.amount,0) ,
g_ae_sys_rec.base_currency ,
g_cust_inv_rec.exchange_rate,
g_ae_sys_rec.base_precision ,
g_ae_sys_rec.base_min_acc_unit) ae_taxable_acctd_amount,
'' ae_adj_ccid,
'' ae_edisc_ccid,
'' ae_unedisc_ccid,
'' ae_finchrg_ccid,
'' ae_adj_non_rec_tax_ccid,
'' ae_edisc_non_rec_tax_ccid,
'' ae_unedisc_non_rec_tax_ccid,
'' ae_finchrg_non_rec_tax_ccid,
decode(l_override1,
'Y',
decode(g_ae_rule_rec.gl_account_source1,
'ACTIVITY_GL_ACCOUNT',
g_ae_rule_rec.code_combination_id1,
'TAX_CODE_ON_INVOICE',b5.override_ccid1,
''),
'') ae_override_ccid1,
decode(l_override2,
'Y',
decode(g_ae_rule_rec.gl_account_source2,
'ACTIVITY_GL_ACCOUNT',
g_ae_rule_rec.code_combination_id2,
'TAX_CODE_ON_INVOICE',b5.override_ccid2,
''),
'') ae_override_ccid2,
'' ae_tax_link_id,
'' ae_tax_link_id_ed_adj,
'' ae_tax_link_id_uned,
'' ae_tax_link_id_act,
det.amount ae_pro_amt,
det.acctd_amount ae_pro_acctd_amt,
0 ae_pro_frt_chrg_amt,
0 ae_pro_frt_chrg_acctd_amt,
det.taxable_amount ae_pro_taxable_amt,
det.taxable_acctd_amount ae_pro_taxable_acctd_amt,
det.taxable_amount ae_pro_split_taxable_amt ,
det.taxable_acctd_amount ae_pro_split_taxable_acctd_amt,
det.taxable_amount ae_pro_recov_taxable_amt,
det.taxable_acctd_amount ae_pro_recov_taxable_acctd_amt,
0 ae_pro_def_tax_amt,
0 ae_pro_def_tax_acctd_amt,
'N' ae_summarize_flag,
'N' ae_counted_flag,
'' ae_autotax,
0 ae_sum_alloc_amt,
0 ae_sum_alloc_acctd_amt,
'' ae_tax_line_count,
det.ref_account_class ref_account_class,
det.activity_bucket activity_bucket,
det.ref_line_id ae_ref_line_id,
det.from_amount ae_from_pro_amt,
det.from_acctd_amount ae_from_pro_acctd_amt,
det.ccid ref_dist_ccid,
det.ref_mf_dist_flag ref_mf_dist_flag
FROM ar_line_app_detail_gt det,
ra_customer_trx_lines ctl,
( SELECT customer_trx_id,
NVL(location_segment_id,-9999) location_segment_id,
NVL(vat_tax_id,-9999) tax_code_id,
MIN(NVL(link_to_cust_trx_line_id,customer_trx_line_id)) link_to_cust_trx_line_id
FROM ra_customer_trx_lines tax
GROUP BY customer_trx_id,
location_segment_id,
vat_tax_id ) tax_link,
( select b4.ae_link_to_cust_trx_line_id ae_link_to_cust_trx_line_id,
max(decode(g_ae_rule_rec.gl_account_source1,
'TAX_CODE_ON_INVOICE',
decode(g_ae_doc_rec.source_table,
'RA', b4.ae_edisc_ccid,
'ADJ',decode(g_ae_doc_rec.document_type,
'ADJUSTMENT', b4.ae_adj_ccid,
'FINANCE_CHARGES',b4.ae_finchrg_ccid,
''),
''),
'')) override_ccid1,
max(decode(g_ae_rule_rec.gl_account_source2,
'TAX_CODE_ON_INVOICE', decode(g_ae_doc_rec.source_table,
'RA', b4.ae_unedisc_ccid,
''),
'')) override_ccid2
from ar_ae_alloc_rec_gt b4
where b4.rowid IN
(select /*+ INDEX(b3 AR_AE_ALLOC_REC_GT_N3) */
min(b3.rowid)
from ar_ae_alloc_rec_gt b3
where b3.ae_id = g_id
and b3.ae_account_class = 'TAX'
and (((decode(g_ae_doc_rec.source_table,
'RA', decode(b3.ae_edisc_ccid,
'','N',
'Y'),
'ADJ',decode(g_ae_doc_rec.document_type,
'ADJUSTMENT', decode(b3.ae_adj_ccid,
'','N',
'Y'),
'FINANCE_CHARGES',decode(b3.ae_finchrg_ccid,
'','N',
'Y')),
'N') = 'Y')
AND (l_override1 = 'Y')
AND (g_ae_rule_rec.gl_account_source1 = 'TAX_CODE_ON_INVOICE'))
OR
((decode(g_ae_doc_rec.source_table,
'RA', decode(b3.ae_unedisc_ccid,
'','N',
'Y'),
'N') = 'Y')
AND (l_override2 = 'Y')
AND (g_ae_rule_rec.gl_account_source2 = 'TAX_CODE_ON_INVOICE')))
group by b3.ae_link_to_cust_trx_line_id)
group by b4.ae_link_to_cust_trx_line_id
-- Bug 6719986 Added union sql to get the rows for TAX line of zero percentage tax.
UNION
select ctl.link_to_cust_trx_line_id ae_link_to_cust_trx_line_id,
max(decode(g_ae_rule_rec.gl_account_source1,
'TAX_CODE_ON_INVOICE',
decode(g_ae_doc_rec.source_table,
'RA', arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'EDISC'),
'ADJ',decode(g_ae_doc_rec.document_type,
'ADJUSTMENT',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'ADJ'),
'FINANCE_CHARGES',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'FINCHRG'),
''),
''),
'')) override_ccid1,
max(decode(g_ae_rule_rec.gl_account_source2,
'TAX_CODE_ON_INVOICE', decode(g_ae_doc_rec.source_table,
'RA',
arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
gld.gl_date,
'UNEDISC'),
''),
'')) override_ccid2
from ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist gld
where gld.customer_trx_line_id = ctl.customer_trx_line_id
and gld.account_set_flag = 'N'
and ctl.customer_trx_id = p_invoice_id
and ctl.rowid in
(select min(ctl1.rowid)
from ra_customer_trx_lines ctl1,
(select /*+ INDEX(b3 AR_AE_ALLOC_REC_GT_N3) */
count(*) tax_count
from ar_ae_alloc_rec_gt b3
where b3.ae_id = g_id
and b3.ae_account_class = 'TAX') tx
where ctl1.customer_trx_id = p_invoice_id
and ctl1.line_type = 'TAX'
and tx.tax_count = 0
group by ctl1.link_to_cust_trx_line_id)
group by ctl.link_to_cust_trx_line_id
) b5
WHERE det.ref_customer_trx_id = p_invoice_id
AND det.ledger_id = g_ae_sys_rec.set_of_books_id
AND gt_id = g_id
AND ref_customer_trx_line_id IN (-6,-7,-9)
AND det.ref_customer_trx_id = tax_link.customer_trx_id
AND NVL(det.location_segment_id,-9999) = tax_link.location_segment_id
AND nvl(det.tax_code_id,-9999) = tax_link.tax_code_id
AND ctl.customer_trx_line_id = tax_link.link_to_cust_trx_line_id
AND ctl.customer_trx_id = det.ref_customer_trx_id
and decode(ctl.line_type,
'FREIGHT', ctl.link_to_cust_trx_line_id, --first available tax code netexpense account
ctl.customer_trx_line_id) = b5.ae_link_to_cust_trx_line_id (+);
SELECT amount,
acctd_amount,
code_combination_id
INTO g_ae_rule_rec.receivable_amt,
g_ae_rule_rec.receivable_acctd_amt,
g_ae_rule_rec.receivable_account
FROM ra_cust_trx_line_gl_dist
where customer_trx_id = p_invoice_id
AND account_class = 'REC'
and latest_rec_flag = 'Y';
| called after the insert of original revenue, hence all link ids set at one time. |
+----------------------------------------------------------------------------------*/
UPDATE /*+ INDEX(b1 AR_AE_ALLOC_REC_GT_N3) */
ar_ae_alloc_rec_gt b1
SET (b1.ae_tax_link_id, b1.ae_tax_link_id_ed_adj, b1.ae_tax_link_id_uned) =
(select /*+ INDEX(b8 AR_AE_ALLOC_REC_GT_N3) */
max(b8.ae_tax_link_id) ae_tax_link_id,
max(b8.ae_tax_link_id_ed_adj) ae_tax_link_id_ed_adj,
max(b8.ae_tax_link_id_uned) ae_tax_link_id_uned
from ar_ae_alloc_rec_gt b8
where b8.ae_id = g_id
and b8.ae_account_class = 'TAX'
and b8.ae_link_to_cust_trx_line_id = b1.ae_customer_trx_line_id)
WHERE b1.ae_id = g_id
AND b1.ae_account_class IN ('REVEARN','REVUNEARN') --MAINTAINTAXLINKID
AND EXISTS (select /*+ INDEX(b2 AR_AE_ALLOC_REC_GT_N3) */
'x'
from ar_ae_alloc_rec_gt b2
where b2.ae_id = g_id
and b2.ae_account_class = 'TAX'
and b2.ae_link_to_cust_trx_line_id = b1.ae_customer_trx_line_id);
UPDATE /*+ INDEX(at1 AR_AE_ALLOC_REC_GT_N3) */
ar_ae_alloc_rec_gt at1
SET (at1.ae_inv_line ,
at1.ae_sum_rev_amt ,
at1.ae_sum_rev_acctd_amt,
at1.ae_count) =
(SELECT /*+ INDEX(at2 AR_AE_ALLOC_REC_GT_N1) */
at2.ae_customer_trx_line_id,
sum(at2.ae_amount),
sum(at2.ae_acctd_amount),
count(at2.ae_customer_trx_line_id)
FROM ar_ae_alloc_rec_gt at2
WHERE at2.ae_id = g_id
AND at2.ae_customer_trx_line_id = at1.ae_customer_trx_line_id
AND at2.ae_account_class IN ('REVEARN','REVUNEARN') --MAINTAINTAXLINKID
GROUP BY at2.ae_customer_trx_line_id)
WHERE at1.ae_id = g_id
AND at1.ae_account_class IN ('REVEARN','REVUNEARN'); --MAINTAINTAXLINKID
select /*+ INDEX(ar_ae_alloc_rec_gt AR_AE_ALLOC_REC_GT_N3) */
ae_link_to_cust_trx_line_id
from ar_ae_alloc_rec_gt
where ae_id = g_id
and ae_account_class = 'TAX'
group by ae_link_to_cust_trx_line_id
order by ae_link_to_cust_trx_line_id;
update /*+ INDEX(art3 AR_AE_ALLOC_REC_GT_N4) */
ar_ae_alloc_rec_gt art3
set art3.ae_tax_link_id = decode(l_set_pay_link,
'Y', l_link_ctr,
''),
art3.ae_tax_link_id_ed_adj = decode(l_set_ed_adj_link,
'Y', l_link_ctr,
art3.ae_tax_link_id_ed_adj),
art3.ae_tax_link_id_uned = decode(l_set_uned_link,
'Y', decode(l_mirror_link_ctr,
'Y', l_link_ctr1,
l_link_ctr),
art3.ae_tax_link_id_uned),
art3.ae_counted_flag = 'Y'
where ae_id = g_id
and art3.ae_account_class = 'TAX'
and art3.ae_counted_flag = 'N'
and art3.ae_link_to_cust_trx_line_id IN
(select to_line
from(
select /*+ INDEX(art1 AR_AE_ALLOC_REC_GT_N4) INDEX(art2 AR_AE_ALLOC_REC_GT_N2) */
art1.ae_link_to_cust_trx_line_id from_line ,
art2.ae_link_to_cust_trx_line_id to_line ,
max(art1.ae_tax_line_count) tax_line_count ,
1 hit_count
from ar_ae_alloc_rec_gt art1,
ar_ae_alloc_rec_gt art2
where art1.ae_id = g_id
and art1.ae_account_class = 'TAX'
and art1.ae_account_class = art2.ae_account_class
and art1.ae_tax_id = art2.ae_tax_id
and art1.ae_tax_type = art2.ae_tax_type
and art1.ae_tax_line_count = art2.ae_tax_line_count
and art1.ae_link_to_cust_trx_line_id = inv_line_tbl(i)
--and art1.ae_link_to_cust_trx_line_id <> art2.ae_link_to_cust_trx_line_id
and art2.ae_id = art1.ae_id
and art1.ae_counted_flag = 'N'
and art2.ae_counted_flag = 'N'
group by art1.ae_link_to_cust_trx_line_id,
art2.ae_link_to_cust_trx_line_id,
art1.ae_tax_id,
art1.ae_tax_type)
group by from_line, to_line
having sum(hit_count) = max(tax_line_count));
| need to be updated with Rule used.If there is just a payment then the rule |
| information is not maintained. |
+------------------------------------------------------------------------------*/
IF (((g_ae_doc_rec.source_table = 'RA') AND (nvl(p_app_rec.earned_discount_taken,0) <> 0))
OR ((g_ae_doc_rec.source_table = 'ADJ') AND (nvl(p_adj_rec.amount,0) <> 0))
OR ((g_ae_doc_rec.source_table = 'RA') AND (nvl(p_app_rec.unearned_discount_taken,0) <> 0))) THEN
/*------------------------------------------------------------------------------+
| For payments do not call the document rule routine as this is not required |
+------------------------------------------------------------------------------*/
-- This is part of avoiding calling process_amount for ED_ADJ and UNED and PAY
IF ((g_ae_doc_rec.source_table = 'ADJ') AND (nvl(p_adj_rec.amount,0) <> 0))
OR ((g_ae_doc_rec.source_table = 'RA') AND (nvl(p_app_rec.earned_discount_taken,0) <> 0))
THEN
Doc_Tax_Acct_Rule(p_type_acct => 'ED_ADJ',
p_app_rec => p_app_rec ,
p_adj_rec => p_adj_rec );
| Updates the discount or adjustment with the accounting Rule used to
| allocate the line, tax, freight charges to required accounts. This
| helps in keeping a history of which rule was used as accounts for a
| receivable activity can change. The rule used and the amount buckets
| for the discount or adjustment can then be used to determine the nature
| of the accounting if required.
|
| SCOPE - PRIVATE
|
| PARAMETERS
| p_type_acct IN Flag to indicate accounting for Earned
| discounts Adjustments or Unearned discounts
| p_app_rec IN Receivable Application detail record
| p_adj_rec IN Adjustment detail record
*==========================================================================*/
PROCEDURE Doc_Tax_Acct_Rule(p_type_acct IN VARCHAR2 ,
p_app_rec IN ar_receivable_applications%ROWTYPE ,
p_adj_rec IN ar_adjustments%ROWTYPE ) IS
l_gl_account_source ar_receivables_trx.gl_account_source%TYPE ;
| Get Rules so that parent discount or adjustment record can be updated with the|
| Rule details. |
+-------------------------------------------------------------------------------*/
Get_Rules(p_type_acct => p_type_acct,
p_gl_account_source => l_gl_account_source,
p_tax_code_source => l_tax_code_source,
p_tax_recoverable_flag => l_tax_recoverable_flag);
arp_standard.debug('Doc_Tax_Acct_Rule: ' || 'Rule being used being updated ' || l_rule_used);
| Update the correct bucket with Rule used for a discount or an adjustment |
+-------------------------------------------------------------------------------*/
IF ((g_ae_doc_rec.source_table = 'RA') AND (p_type_acct = 'ED_ADJ')) THEN
UPDATE ar_receivable_applications
SET edisc_tax_acct_rule = l_rule_used
WHERE receivable_application_id = g_ae_doc_rec.source_id;
This is done by checking if created_from is ADJ_API or not in update stmt. */
UPDATE ar_adjustments
SET adj_tax_acct_rule = l_rule_used,
-- code_combination_id = nvl(adj_code_combination_id, code_combination_id)
code_combination_id = DECODE(created_from,
'ADJ_API', nvl(code_combination_id, adj_code_combination_id),
nvl(adj_code_combination_id, code_combination_id)
)
WHERE adjustment_id = g_ae_doc_rec.source_id;
UPDATE ar_receivable_applications
SET unedisc_tax_acct_rule = l_rule_used
WHERE receivable_application_id = g_ae_doc_rec.source_id;
select 1
from dual
where exists(select 'x'
from ar_adjustments
where type = 'CHARGES'
and nvl(tax_adjusted,0) <> 0
and status = 'A'
and customer_trx_id = p_cust_id);
| Get Rules so that parent discount or adjustment record can be updated with the|
| Rule details. |
+-------------------------------------------------------------------------------*/
Get_Rules(p_type_acct => p_type_acct,
p_gl_account_source => l_gl_account_source,
p_tax_code_source => l_tax_code_source,
p_tax_recoverable_flag => l_tax_recoverable_flag);
UPDATE /*+ INDEX(ar_ae_alloc_rec_gt AR_AE_ALLOC_REC_GT_N3)*/
ar_ae_alloc_rec_gt
SET ae_pro_amt = 0 ,
ae_pro_acctd_amt = 0 ,
ae_pro_frt_chrg_amt = 0 ,
ae_pro_frt_chrg_acctd_amt = 0 ,
ae_pro_taxable_amt = 0 ,
ae_pro_taxable_acctd_amt = 0 ,
ae_pro_split_taxable_amt = '' ,
ae_pro_split_taxable_acctd_amt = '' ,
ae_pro_recov_taxable_amt = '' ,
ae_pro_recov_taxable_acctd_amt = '' ,
ae_pro_def_tax_amt = 0 ,
ae_pro_def_tax_acctd_amt = 0 ,
ae_sum_alloc_amt = 0 ,
ae_sum_alloc_acctd_amt = 0 ,
ae_tax_link_id_act = '' ,
ae_counted_flag = 'N'
WHERE ae_id = g_id;
SELECT /*+ INDEX(ar_ae_alloc_rec_gt AR_AE_ALLOC_REC_GT_N3) */
rowid,
ae_account_class,
ae_amount,
ae_acctd_amount,
ae_pro_amt,
ae_pro_acctd_amt,
ae_taxable_amount,
ae_taxable_acctd_amount,
ae_pro_taxable_amt,
ae_pro_taxable_acctd_amt,
ae_pro_frt_chrg_amt,
ae_pro_frt_chrg_acctd_amt,
ae_collected_tax_ccid,
ae_pro_def_tax_amt,
ae_pro_def_tax_acctd_amt
FROM ar_ae_alloc_rec_gt
WHERE ae_id = g_id
AND ae_account_class IS NOT NULL
ORDER BY ae_account_class, ae_customer_trx_line_id;
| Update the amounts for revenue or tax based on rowid |
+------------------------------------------------------------------------------*/
FORALL m IN g_ae_alloc_rev_tax_tbl.l_rowid.FIRST .. g_ae_alloc_rev_tax_tbl.l_rowid.LAST
UPDATE ar_ae_alloc_rec_gt
SET ae_pro_amt = g_ae_alloc_rev_tax_tbl.ae_pro_amt(m),
ae_pro_acctd_amt = g_ae_alloc_rev_tax_tbl.ae_pro_acctd_amt(m),
ae_pro_taxable_amt = g_ae_alloc_rev_tax_tbl.ae_pro_taxable_amt(m),
ae_pro_taxable_acctd_amt = g_ae_alloc_rev_tax_tbl.ae_pro_taxable_acctd_amt(m),
ae_pro_frt_chrg_amt = g_ae_alloc_rev_tax_tbl.ae_pro_frt_chrg_amt(m),
ae_pro_frt_chrg_acctd_amt = g_ae_alloc_rev_tax_tbl.ae_pro_frt_chrg_acctd_amt(m),
ae_pro_def_tax_amt = g_ae_alloc_rev_tax_tbl.ae_pro_def_tax_amt(m),
ae_pro_def_tax_acctd_amt = g_ae_alloc_rev_tax_tbl.ae_pro_def_tax_acctd_amt(m)
WHERE rowid = g_ae_alloc_rev_tax_tbl.l_rowid(m);
select /*+ INDEX(a2 AR_AE_ALLOC_REC_GT_N3) */
a2.rowid,
a3.link_id,
a3.line_id,
a3.amt,
a3.acctd_amt
from ar_ae_alloc_rec_gt a2,
(select /*+ INDEX(a1 AR_AE_ALLOC_REC_GT_N3) */
a1.ae_id ae_id,
decode(p_type_acct,
'ED_ADJ', a1.ae_tax_link_id_ed_adj,
'UNED' , a1.ae_tax_link_id_uned,
'PAY' , a1.ae_tax_link_id) link_id,
a1.ae_customer_trx_line_id line_id,
sum(a1.ae_pro_amt) amt,
sum(a1.ae_pro_acctd_amt) acctd_amt
from ar_ae_alloc_rec_gt a1
where a1.ae_id = g_id
and a1.ae_account_class IN ('REVEARN','REVUNEARN') --MAINTAINLINKTAXID
group by
a1.ae_id,
decode(p_type_acct,
'ED_ADJ', a1.ae_tax_link_id_ed_adj,
'UNED' , a1.ae_tax_link_id_uned,
'PAY' , a1.ae_tax_link_id),
a1.ae_customer_trx_line_id) a3
where a2.ae_id = g_id
and a3.ae_id = a2.ae_id
and a2.ae_link_to_cust_trx_line_id = a3.line_id
and a2.ae_account_class = 'TAX'
and decode(p_type_acct,
'ED_ADJ', a2.ae_tax_link_id_ed_adj,
'UNED' , a2.ae_tax_link_id_uned,
'PAY' , a2.ae_tax_link_id) = a3.link_id
order by a3.link_id, (abs(a3.amt) + abs(a3.acctd_amt)) DESC, a3.line_id;
UPDATE ar_ae_alloc_rec_gt
SET ae_pro_taxable_amt = amt_tbl(m),
ae_pro_taxable_acctd_amt = acctd_amt_tbl(m),
ae_tax_link_id_act = link_id_tbl(m)
WHERE rowid = rowid_tbl(m)
AND link_id_tbl(m) IS NOT NULL;
select /*+ INDEX(a1 AR_AE_ALLOC_REC_GT_N3) */
a1.rowid row_id,
a1.ae_link_to_cust_trx_line_id inv_line_id,
a1.ae_tax_id tax_id,
a1.ae_tax_type tax_type,
a1.ae_code_combination_id ae_code_combination_id,
a1.ae_collected_tax_ccid ae_collected_tax_ccid,
a1.ae_pro_taxable_amt pro_taxable_amt,
a1.ae_pro_taxable_acctd_amt pro_taxable_acctd_amt,
0 taxable_amt_split,
0 taxable_acctd_amt_split,
'' taxable_amt_r_split,
'' taxable_acctd_amt_r_split
from ar_ae_alloc_rec_gt a1,
(select /*+ INDEX(a2 AR_AE_ALLOC_REC_GT_N3) */
a2.ae_link_to_cust_trx_line_id ae_link_to_cust_trx_line_id,
a2.ae_tax_type ae_tax_type,
a2.ae_tax_id ae_tax_id
from ar_ae_alloc_rec_gt a2
where a2.ae_id = g_id
and a2.ae_account_class = 'TAX'
group by a2.ae_link_to_cust_trx_line_id,
a2.ae_tax_type,
a2.ae_tax_id
having count(*) > 1) a3
where a1.ae_id = g_id
and a1.ae_account_class = 'TAX'
and a1.ae_link_to_cust_trx_line_id = a3.ae_link_to_cust_trx_line_id
and a1.ae_tax_id = a3.ae_tax_id
and a1.ae_tax_type = a3.ae_tax_type
order by a1.ae_link_to_cust_trx_line_id,
a1.ae_tax_type,
a1.ae_tax_id,
decode(a1.ae_collected_tax_ccid,
'',2,
1),
a1.ae_code_combination_id,
a1.ae_collected_tax_ccid;
UPDATE ar_ae_alloc_rec_gt
SET ae_pro_split_taxable_amt = taxable_amt_split_tbl(m),
ae_pro_split_taxable_acctd_amt = taxable_acctd_amt_split_tbl(m),
ae_pro_recov_taxable_amt = taxable_amt_recov_tbl(m),
ae_pro_recov_taxable_acctd_amt = taxable_acctd_amt_recov_tbl(m)
WHERE rowid = rowid_tbl(m);
SELECT /*+ INDEX(ae1 AR_AE_ALLOC_REC_GT_N3) INDEX(ae2 AR_AE_ALLOC_REC_GT_N1) */
ae2.rowid ,
ae1.ae_customer_trx_line_id ,
ae1.ae_link_to_cust_trx_line_id ,
ae1.ae_code_combination_id ,
nvl(ae1.ae_collected_tax_ccid,-9999),
ae1.ae_pro_amt ,
ae1.ae_pro_acctd_amt ,
ae2.ae_sum_rev_amt ,
ae2.ae_sum_rev_acctd_amt ,
ae2.ae_count ,
ae2.ae_amount ,
ae2.ae_acctd_amount
FROM ar_ae_alloc_rec_gt ae1,
ar_ae_alloc_rec_gt ae2
WHERE ae1.ae_id = g_id
AND ae1.ae_account_class = 'TAX'
AND ae2.ae_id = ae1.ae_id
AND ae2.ae_account_class IN ('REVEARN','REVUNEARN') --MAINTAINTAXLINKID
AND ae1.ae_link_to_cust_trx_line_id = ae2.ae_customer_trx_line_id
AND ((ae1.ae_pro_amt <> 0)
OR (ae1.ae_pro_acctd_amt <> 0))
ORDER BY ae1.ae_customer_trx_line_id, ae1.ae_link_to_cust_trx_line_id;
UPDATE ar_ae_alloc_rec_gt ae1
SET ae1.ae_pro_amt = ae1.ae_pro_amt + l_tax_amt_pro_rev,
ae1.ae_pro_acctd_amt = ae1.ae_pro_acctd_amt + l_tax_acctd_amt_pro_rev
WHERE ae1.rowid = l_rev_rowid;
UPDATE /*+ index( a1 AR_AE_ALLOC_REC_GT_N4 ) */
ar_ae_alloc_rec_gt a1
SET (a1.ae_tax_link_id_act) =
(SELECT /*+ index( a2 AR_AE_ALLOC_REC_GT_N4 ) */
MAX(a2.ae_tax_link_id)
FROM ar_ae_alloc_rec_gt a2
WHERE a2.ae_id = g_id
AND a2.ref_account_class = 'TAX'
AND a1.ae_customer_trx_line_id = a2.ae_tax_link_id)
WHERE a1.ae_id = g_id
AND a1.ref_account_class IN ('REV','UNEARN','UNBILL');
UPDATE ar_ae_alloc_rec_gt a1
SET (a1.ae_tax_link_id_act) =
(SELECT MAX(a2.ae_tax_link_id)
FROM ar_ae_alloc_rec_gt a2
WHERE a2.ae_id = g_id
AND a2.ref_account_class IN ('REV','UNEARN','UNBILL')
AND a1.ae_tax_link_id = a2.ae_customer_trx_line_id)
WHERE a1.ae_id = g_id
AND a1.ref_account_class = 'TAX';
UPDATE ar_ae_alloc_rec_gt a1
SET (a1.ae_tax_link_id_act,
a1.AE_PRO_TAXABLE_AMT,
a1.AE_PRO_TAXABLE_ACCTD_AMT,
a1.AE_PRO_SPLIT_TAXABLE_AMT,
a1.AE_PRO_SPLIT_TAXABLE_ACCTD_AMT,
a1.AE_PRO_RECOV_TAXABLE_AMT,
a1.AE_PRO_RECOV_TAXABLE_ACCTD_AMT) =
(SELECT MAX(a2.ae_tax_link_id),
MAX(a2.AE_PRO_AMT),
MAX(a2.AE_PRO_ACCTD_AMT),
MAX(a2.AE_PRO_AMT),
MAX(a2.AE_PRO_ACCTD_AMT),
MAX(a2.AE_PRO_AMT),
MAX(a2.AE_PRO_ACCTD_AMT)
FROM ar_ae_alloc_rec_gt a2
WHERE a2.ae_id = g_id
AND a2.ref_account_class = 'REV'
AND a1.ae_tax_link_id = a2.ae_customer_trx_line_id)
WHERE a1.ae_id = g_id
AND a1.ref_account_class = 'TAX';
SELECT /*+ INDEX(ae1 AR_AE_ALLOC_REC_GT_N3) */
ae1.ae_tax_link_id_act,
ae1.ae_customer_trx_line_id,
ae1.ae_cust_trx_line_gl_dist_id,
ae1.ae_ref_line_id,
ae1.ref_account_class,
ae1.activity_bucket,
ae1.ref_dist_ccid,
ae1.ref_mf_dist_flag,
DECODE(DECODE(ae1.activity_bucket,
'APP_LINE' , 'ACTIVITY_GL_ACCOUNT',
'APP_TAX' , 'ACTIVITY_GL_ACCOUNT',
'APP_FRT' , 'ACTIVITY_GL_ACCOUNT',
'APP_CHRG' , 'ACTIVITY_GL_ACCOUNT',
'ADJ_LINE' , g_ae_rule_rec.gl_account_source1,
'ADJ_TAX' , g_ae_rule_rec.gl_account_source1,
'ADJ_FRT' , g_ae_rule_rec.gl_account_source1,
'ADJ_CHRG' , g_ae_rule_rec.gl_account_source1,
'ED_LINE' , g_ae_rule_rec.gl_account_source1,
'ED_TAX' , g_ae_rule_rec.gl_account_source1,
'ED_FRT' , g_ae_rule_rec.gl_account_source1,
'ED_CHRG' , g_ae_rule_rec.gl_account_source1,
'UNED_LINE', g_ae_rule_rec.gl_account_source2,
'UNED_TAX' , g_ae_rule_rec.gl_account_source2,
'UNED_FRT' , g_ae_rule_rec.gl_account_source2,
'UNED_CHRG', g_ae_rule_rec.gl_account_source2,
g_ae_rule_rec.gl_account_source1),
'TAX_CODE_ON_INVOICE',DECODE(ae1.activity_bucket,
'ADJ_LINE' , ae1.ae_override_ccid1,
'ADJ_TAX' , ae1.ae_override_ccid1,
'ADJ_FRT' , ae1.ae_override_ccid1,
'ADJ_CHRG' , ae1.ae_override_ccid1,
'ED_LINE' , ae1.ae_override_ccid1,
'ED_TAX' , ae1.ae_override_ccid1,
'ED_FRT' , ae1.ae_override_ccid1,
'ED_CHRG' , ae1.ae_override_ccid1,
ae1.ae_override_ccid2),
'ACTIVITY_GL_ACCOUNT',DECODE(ae1.activity_bucket,
'ADJ_LINE' , ae1.ae_override_ccid1,
'ADJ_TAX' , ae1.ae_override_ccid1,
'ADJ_FRT' , ae1.ae_override_ccid1,
'ADJ_CHRG' , ae1.ae_override_ccid1,
'ED_LINE' , ae1.ae_override_ccid1,
'ED_TAX' , ae1.ae_override_ccid1,
'ED_FRT' , ae1.ae_override_ccid1,
'ED_CHRG' , ae1.ae_override_ccid1,
'APP_LINE' , g_ae_rule_rec.receivable_account,
'APP_TAX' , g_ae_rule_rec.receivable_account,
'APP_FRT' , g_ae_rule_rec.receivable_account,
'APP_CHRG' , g_ae_rule_rec.receivable_account,
ae1.ae_override_ccid2),
'REVENUE_ON_INVOICE',ae1.ae_code_combination_id,
'') actual_account,
nvl(ae1.ae_pro_amt,0) ae_pro_amt,
nvl(ae1.ae_pro_acctd_amt,0) ae_pro_acctd_amt,
nvl(ae1.ae_pro_frt_chrg_amt,0) ae_pro_frt_chrg_amt,
nvl(ae1.ae_pro_frt_chrg_acctd_amt,0) ae_pro_frt_chrg_acctd_amt,
nvl(ae1.ae_from_pro_amt,0) ae_from_pro_amt,
nvl(ae1.ae_from_pro_acctd_amt,0) ae_from_pro_acctd_amt,
nvl(ae1.ae_from_pro_chrg_amt,0) ae_from_pro_chrg_amt,
nvl(ae1.ae_from_pro_chrg_acctd_amt,0) ae_from_pro_chrg_acctd_amt
FROM ar_ae_alloc_rec_gt ae1
WHERE ae1.ae_id = g_id
AND ae1.ae_account_class IN ('REVEARN',
'FREIGHT',
'REVUNEARN',
'CHARGES');
UPDATE ar_adjustments
SET code_Combination_id = l_ae_line_rec.ae_account
WHERE adjustment_id = g_ae_doc_rec.document_id;
inserted.But no code segment in this package uses the value of the
variable,thus not incremented the variable here.*/
FORALL i IN l_ae_alloc_rec_gt_tab.first..l_ae_alloc_rec_gt_tab.last
INSERT INTO ar_ae_alloc_rec_gt VALUES l_ae_alloc_rec_gt_tab(i);
SELECT /*+ INDEX(ae1 AR_AE_ALLOC_REC_GT_N3) */
nvl(ae_pro_amt,0) ae_pro_amt,
nvl(ae_pro_acctd_amt,0) ae_pro_acctd_amt,
nvl(ae_pro_taxable_amt,0) ae_pro_taxable_amt,
nvl(ae_pro_taxable_acctd_amt,0) ae_pro_taxable_acctd_amt,
nvl(ae_from_pro_amt,0) ae_from_pro_amt,
nvl(ae_from_pro_acctd_amt,0) ae_from_pro_acctd_amt,
nvl(ae_pro_split_taxable_amt,nvl(ae_pro_taxable_amt,0)) ae_pro_split_taxable_amt,
nvl(ae_pro_split_taxable_acctd_amt,nvl(ae_pro_taxable_acctd_amt,0)) ae_pro_split_taxable_acctd_amt,
nvl(ae_pro_recov_taxable_amt,nvl(ae_pro_split_taxable_amt,nvl(ae_pro_taxable_amt,0)))
ae_pro_recov_taxable_amt,
nvl(ae_pro_recov_taxable_acctd_amt,nvl(ae_pro_split_taxable_acctd_amt,nvl(ae_pro_taxable_acctd_amt,0)))
ae_pro_recov_taxable_acctd_amt,
DECODE(ae1.ae_collected_tax_ccid,
'', DECODE( DECODE(activity_bucket, 'ADJ_LINE' , g_ae_rule_rec.tax_code_source1,
'ADJ_TAX' , g_ae_rule_rec.tax_code_source1,
'ADJ_FRT' , g_ae_rule_rec.tax_code_source1,
'ADJ_CHRG' , g_ae_rule_rec.tax_code_source1,
'APP_LINE' , 'INVOICE',
'APP_TAX' , 'INVOICE',
'APP_FRT' , 'INVOICE',
'APP_CHRG' , 'INVOICE',
'ED_LINE' , g_ae_rule_rec.tax_code_source1,
'ED_TAX' , g_ae_rule_rec.tax_code_source1,
'ED_FRT' , g_ae_rule_rec.tax_code_source1,
'ED_CHRG' , g_ae_rule_rec.tax_code_source1,
'UNED_LINE', g_ae_rule_rec.tax_code_source2,
'UNED_TAX' , g_ae_rule_rec.tax_code_source2,
'UNED_FRT' , g_ae_rule_rec.tax_code_source2,
'UNED_CHRG', g_ae_rule_rec.tax_code_source2,
g_ae_rule_rec.tax_code_source1),
'INVOICE',
DECODE(DECODE(activity_bucket,
'ADJ_LINE' , g_ae_rule_rec.tax_recoverable_flag1,
'ADJ_TAX' , g_ae_rule_rec.tax_recoverable_flag1,
'ADJ_FRT' , g_ae_rule_rec.tax_recoverable_flag1,
'ADJ_CHRG' , g_ae_rule_rec.tax_recoverable_flag1,
'APP_LINE' , 'Y',
'APP_TAX' , 'Y',
'APP_FRT' , 'Y',
'APP_CHRG' , 'Y',
'ED_LINE' , g_ae_rule_rec.tax_recoverable_flag1,
'ED_TAX' , g_ae_rule_rec.tax_recoverable_flag1,
'ED_FRT' , g_ae_rule_rec.tax_recoverable_flag1,
'ED_CHRG' , g_ae_rule_rec.tax_recoverable_flag1,
'UNED_LINE', g_ae_rule_rec.tax_recoverable_flag2,
'UNED_TAX' , g_ae_rule_rec.tax_recoverable_flag2,
'UNED_FRT' , g_ae_rule_rec.tax_recoverable_flag2,
'UNED_CHRG', g_ae_rule_rec.tax_recoverable_flag2,
g_ae_rule_rec.tax_recoverable_flag1),
'Y', ae1.ae_code_combination_id,
''),
'NONE',ae1.ae_code_combination_id,
''),
ae1.ae_code_combination_id) ae_code_combination_id,
ae1.ae_collected_tax_ccid,
DECODE(ae1.activity_bucket,
'ADJ_LINE' ,ae1.ae_override_ccid1,
'ADJ_TAX' ,ae1.ae_override_ccid1,
'ADJ_FRT' ,ae1.ae_override_ccid1,
'ADJ_CHRG' ,ae1.ae_override_ccid1,
'ED_LINE' ,ae1.ae_override_ccid1,
'ED_TAX' ,ae1.ae_override_ccid1,
'ED_FRT' ,ae1.ae_override_ccid1,
'ED_CHRG' ,ae1.ae_override_ccid1,
'UNED_LINE',ae1.ae_override_ccid2,
'UNED_TAX' ,ae1.ae_override_ccid2,
'UNED_FRT' ,ae1.ae_override_ccid2,
'UNED_CHRG',ae1.ae_override_ccid2,
'') actual_account,
ae1.ae_tax_type,
ae1.ae_tax_id,
NVL(ae1.ae_tax_group_code_id,'') ae_tax_group_code_id,
NVL(ae1.ae_tax_link_id_act,'') ae_tax_link_id_act,
ae1.ae_customer_trx_line_id,
ae1.ae_cust_trx_line_gl_dist_id,
ae1.ae_ref_line_id,
ae1.ref_account_class,
ae1.activity_bucket,
--{ref_dist_ccid
ae1.ref_dist_ccid,
ae1.ref_mf_dist_flag,
--}
ae1.ae_adj_ccid
,d.code_combination_id actual_tax_ccid
,ae1.ae_unedisc_ccid
,ae1.ae_edisc_ccid
FROM ar_ae_alloc_rec_gt ae1,
ra_cust_trx_line_gl_dist d
WHERE ae1.ae_id = g_id
AND ae1.ae_account_class = 'TAX'
AND ae1.ae_cust_trx_line_gl_dist_id = d.cust_trx_line_gl_dist_id(+);
inserted.But no code segment in this package uses the value of the
variable,thus not incremented the variable here.*/
FORALL i IN l_ae_alloc_rec_gt_tab.first..l_ae_alloc_rec_gt_tab.last
INSERT INTO ar_ae_alloc_rec_gt VALUES l_ae_alloc_rec_gt_tab(i);
SELECT /*+ INDEX(a1 AR_AE_ALLOC_REC_GT_N3) */
NVL(a1.ae_entered_dr,0) * -1 + NVL(a1.ae_entered_cr,0),
NVL(a1.ae_accounted_dr,0) * -1 + NVL(a1.ae_accounted_cr,0),
-- SUM(NVL(ae_pro_amt,0)),
-- SUM(NVL(ae_pro_acctd_amt,0)),
NVL(a1.ae_taxable_entered_dr,0) * -1 + NVL(a1.ae_taxable_entered_cr,0),
NVL(a1.ae_taxable_accounted_dr,0) * -1 + NVL(a1.ae_taxable_accounted_cr,0),
NVL(a1.ae_from_amount_dr,0) * -1 + NVL(a1.ae_from_amount_cr,0),
NVL(a1.ae_from_acctd_amount_dr,0) * -1 + NVL(a1.ae_from_acctd_amount_cr,0),
a1.ae_line_type,
a1.ae_line_type_secondary,
a1.ae_source_id,
a1.ae_source_table,
a1.ae_account,
a1.ae_source_id_secondary,
a1.ae_source_table_secondary,
a1.ae_currency_code,
a1.ae_currency_conversion_rate,
a1.ae_currency_conversion_type,
a1.ae_currency_conversion_date,
a1.ae_third_party_id,
a1.ae_third_party_sub_id,
a1.ae_tax_group_code_id,
a1.ae_tax_code_id,
a1.ae_location_segment_id,
a1.ae_tax_link_id,
decode(a1.ae_neg_ind,
-1, decode(Retain_Neg_Ind(a1.rowid),
1, a1.ae_neg_ind,
''),
a1.ae_neg_ind),
a1.ae_reversed_source_id,
DECODE(a1.ae_cust_trx_line_gl_dist_id,0 ,''
,-1,'',a1.ae_customer_trx_line_id),
DECODE(a1.ae_cust_trx_line_gl_dist_id,0 ,''
,-1,'',a1.ae_cust_trx_line_gl_dist_id),
a1.ae_ref_line_id,
a1.ref_account_class,
a1.activity_bucket,
a1.ref_dist_ccid,
a1.ref_mf_dist_flag
FROM ar_ae_alloc_rec_gt a1
WHERE a1.ae_id = g_id
AND a1.ae_summarize_flag = 'N'
AND a1.ae_account_class IS NULL
AND (NVL(a1.ae_entered_dr,0) <> 0 OR NVL(a1.ae_entered_cr,0) <> 0 OR
NVL(a1.ae_accounted_dr,0) <> 0 OR NVL(a1.ae_accounted_cr,0) <> 0 /*6321537*/
OR (a1.ae_line_type IN ('TAX','DEFERRED_TAX','EDISC_NON_REC_TAX',
'UNEDISC_NON_REC_TAX','ADJ_NON_REC_TAX',
'FINCHRG_NON_REC_TAX')
AND (NVL(a1.ae_entered_dr,0) = 0 AND NVL(a1.ae_entered_cr,0) = 0 AND
NVL(a1.ae_accounted_dr,0) = 0 AND NVL(a1.ae_accounted_cr,0) = 0)));
INSERT INTO ar_distributions (
line_id,
source_id,
source_table,
source_type,
source_type_secondary,
code_combination_id,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
source_id_secondary,
source_table_secondary,
currency_code ,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
third_party_id,
third_party_sub_id,
tax_code_id,
location_segment_id,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
tax_link_id,
reversed_source_id,
tax_group_code_id,
org_id,
ref_customer_trx_line_id,
ref_cust_trx_line_gl_dist_id,
ref_line_id,
from_amount_dr,
from_amount_cr,
from_acctd_amount_dr,
from_acctd_amount_cr,
ref_account_class,
activity_bucket,
ref_dist_ccid,
ref_mf_dist_flag
)
SELECT ar_distributions_s.nextval,
al.ae_source_id,
al.ae_source_table,
al.ae_line_type,
al.ae_line_type_secondary,
al.ae_account,
CASE WHEN (amount < 0) OR (acctd_amount < 0) THEN abs(amount)
WHEN (amount = 0) AND (acctd_amount = 0) THEN
CASE WHEN (nvl(taxable_amount,0) < 0) OR
(nvl(taxable_acctd_amount,0) < 0) THEN abs(amount)
WHEN (nvl(taxable_amount,0) = 0) AND
(nvl(taxable_acctd_amount,0) = 0) AND
(nvl(ae_neg_ind,0) < 0) THEN abs(amount)
ELSE null END
ELSE null END ae_entered_dr,
CASE WHEN (amount < 0) OR (acctd_amount < 0) THEN null
WHEN (amount = 0) AND (acctd_amount = 0) THEN
CASE WHEN (nvl(taxable_amount,0) < 0) OR
(nvl(taxable_acctd_amount,0) < 0) THEN null
WHEN (nvl(taxable_amount,0) = 0) AND
(nvl(taxable_acctd_amount,0) = 0) AND
(nvl(ae_neg_ind,0) < 0) THEN null
ELSE abs(amount) END
ELSE abs(amount) END ae_entered_cr,
CASE WHEN (amount < 0) OR (acctd_amount < 0) THEN abs(acctd_amount)
WHEN (amount = 0) AND (acctd_amount = 0) THEN
CASE WHEN (nvl(taxable_amount,0) < 0) OR
(nvl(taxable_acctd_amount,0) < 0) THEN abs(acctd_amount)
WHEN (nvl(taxable_amount,0) = 0) AND
(nvl(taxable_acctd_amount,0) = 0) AND
(nvl(ae_neg_ind,0) < 0) THEN abs(acctd_amount)
ELSE null END
ELSE null END ae_accounted_dr,
CASE WHEN (amount < 0) OR (acctd_amount < 0) THEN null
WHEN (amount = 0) AND (acctd_amount = 0) THEN
CASE WHEN (nvl(taxable_amount,0) < 0) OR
(nvl(taxable_acctd_amount,0) < 0) THEN null
WHEN (nvl(taxable_amount,0) = 0) AND
(nvl(taxable_acctd_amount,0) = 0) AND
(nvl(ae_neg_ind,0) < 0) THEN null
ELSE abs(acctd_amount) END
ELSE abs(acctd_amount) END ae_accounted_cr,
arp_standard.profile.user_id,
SYSDATE,
arp_standard.profile.user_id,
SYSDATE,
arp_standard.profile.last_update_login,
al.ae_source_id_secondary,
al.ae_source_table_secondary,
al.ae_currency_code,
al.ae_currency_conversion_rate,
al.ae_currency_conversion_type,
al.ae_currency_conversion_date,
al.ae_third_party_id,
al.ae_third_party_sub_id,
al.ae_tax_code_id,
al.ae_location_segment_id,
CASE WHEN ( nvl(taxable_amount,0) < 0) OR
( nvl(taxable_acctd_amount,0) < 0) THEN abs(taxable_amount)
WHEN ( nvl(taxable_amount,0) = 0 ) AND
( nvl(taxable_acctd_amount,0) = 0 ) AND
( ( amount < 0 ) OR ( acctd_amount < 0 )) THEN abs(taxable_amount)
ELSE null END ae_taxable_entered_dr,
CASE WHEN ( nvl(taxable_amount,0) < 0) OR
( nvl(taxable_acctd_amount,0) < 0) THEN null
WHEN ( nvl(taxable_amount,0) = 0 ) AND
( nvl(taxable_acctd_amount,0) = 0 ) AND
( ( amount < 0 ) OR ( acctd_amount < 0 ))THEN null
ELSE abs(taxable_amount) END ae_taxable_entered_cr,
CASE WHEN ( nvl(taxable_amount,0) < 0) OR
( nvl(taxable_acctd_amount,0) < 0)THEN abs(taxable_acctd_amount)
WHEN ( nvl(taxable_amount,0) = 0 ) AND
( nvl(taxable_acctd_amount,0) = 0 ) AND
( ( amount < 0 ) OR ( acctd_amount < 0 ))THEN abs(taxable_acctd_amount)
ELSE null END ae_taxable_accounted_dr,
CASE WHEN ( nvl(taxable_amount,0) < 0) OR
( nvl(taxable_acctd_amount,0) < 0)THEN null
WHEN ( nvl(taxable_amount,0) = 0 ) AND
( nvl(taxable_acctd_amount,0) = 0 ) AND
( ( amount < 0 ) OR ( acctd_amount < 0 ))THEN null
ELSE abs(taxable_acctd_amount) END ae_taxable_accounted_cr ,
al.ae_tax_link_id,
al.ae_reversed_source_id,
al.ae_tax_group_code_id,
arp_standard.sysparm.org_id, /* SSA changes anuj */
al.ae_customer_trx_line_id,
al.ae_cust_trx_line_gl_dist_id,
al.ae_ref_line_id,
CASE WHEN (amount < 0) OR (acctd_amount < 0) THEN abs(from_amount)
WHEN (amount = 0) AND (acctd_amount = 0) THEN
CASE WHEN (nvl(taxable_amount,0) < 0) OR
(nvl(taxable_acctd_amount,0) < 0)THEN abs(from_amount)
WHEN (nvl(taxable_amount,0) = 0) AND
(nvl(taxable_acctd_amount,0) = 0) AND
(nvl(ae_neg_ind,0) < 0) THEN abs(from_amount)
ELSE null END
ELSE null END ae_from_amount_dr,
CASE WHEN (amount < 0) OR (acctd_amount < 0)THEN null
WHEN (amount = 0) AND (acctd_amount = 0) THEN
CASE WHEN (nvl(taxable_amount,0) < 0) OR
(nvl(taxable_acctd_amount,0) < 0) THEN null
WHEN (nvl(taxable_amount,0) = 0) AND
(nvl(taxable_acctd_amount,0) = 0) AND
(nvl(ae_neg_ind,0) < 0)THEN null
ELSE abs(from_amount) END
ELSE abs(from_amount) END ae_from_amount_cr,
CASE WHEN (amount < 0) OR (acctd_amount < 0)THEN abs(from_acctd_amount)
WHEN (amount = 0) AND (acctd_amount = 0) THEN
CASE WHEN (nvl(taxable_amount,0) < 0) OR
(nvl(taxable_acctd_amount,0) < 0)THEN abs(from_acctd_amount)
WHEN (nvl(taxable_amount,0) = 0) AND
(nvl(taxable_acctd_amount,0) = 0) AND
(nvl(ae_neg_ind,0) < 0)THEN abs(from_acctd_amount)
ELSE null END
ELSE null END ae_from_acctd_amount_dr,
CASE WHEN (amount < 0) OR (acctd_amount < 0) THEN null
WHEN (amount = 0) AND (acctd_amount = 0) THEN
CASE WHEN (nvl(taxable_amount,0) < 0) OR
(nvl(taxable_acctd_amount,0) < 0)THEN null
WHEN (nvl(taxable_amount,0) = 0) AND
(nvl(taxable_acctd_amount,0) = 0) AND
(nvl(ae_neg_ind,0) < 0)THEN null
ELSE abs(from_acctd_amount) END
ELSE abs(from_acctd_amount) END ae_from_acctd_amount_cr,
al.ref_account_class,
al.activity_bucket,
al.ref_dist_ccid,
al.ref_mf_dist_flag
from
( --Defined it as a subquery as some of the values[Amount columns] derived in this
-- select are used for further calculations in the main select.
SELECT /*+ INDEX(a1 AR_AE_ALLOC_REC_GT_N3) */
NVL(a1.ae_entered_dr,0) * -1 + NVL(a1.ae_entered_cr,0) amount,
NVL(a1.ae_accounted_dr,0) * -1 + NVL(a1.ae_accounted_cr,0) acctd_amount,
NVL(a1.ae_taxable_entered_dr,0) * -1 + NVL(a1.ae_taxable_entered_cr,0) taxable_amount,
NVL(a1.ae_taxable_accounted_dr,0) * -1 + NVL(a1.ae_taxable_accounted_cr,0) taxable_acctd_amount,
NVL(a1.ae_from_amount_dr,0) * -1 + NVL(a1.ae_from_amount_cr,0) from_amount,
NVL(a1.ae_from_acctd_amount_dr,0) * -1 + NVL(a1.ae_from_acctd_amount_cr,0) from_acctd_amount,
a1.ae_line_type,
a1.ae_line_type_secondary,
a1.ae_source_id,
a1.ae_source_table,
a1.ae_account,
a1.ae_source_id_secondary,
a1.ae_source_table_secondary,
a1.ae_currency_code,
a1.ae_currency_conversion_rate,
a1.ae_currency_conversion_type,
a1.ae_currency_conversion_date,
a1.ae_third_party_id,
a1.ae_third_party_sub_id,
a1.ae_tax_group_code_id,
a1.ae_tax_code_id,
a1.ae_location_segment_id,
a1.ae_tax_link_id,
decode(a1.ae_neg_ind,
-1, decode(Retain_Neg_Ind(a1.rowid),
1, a1.ae_neg_ind,
''),
a1.ae_neg_ind) ae_neg_ind,
a1.ae_reversed_source_id,
DECODE(a1.ae_cust_trx_line_gl_dist_id,0 ,''
,-1,'',a1.ae_customer_trx_line_id)
ae_customer_trx_line_id,
DECODE(a1.ae_cust_trx_line_gl_dist_id,0 ,''
,-1,'',a1.ae_cust_trx_line_gl_dist_id)
ae_cust_trx_line_gl_dist_id,
a1.ae_ref_line_id,
a1.ref_account_class,
a1.activity_bucket,
a1.ref_dist_ccid,
a1.ref_mf_dist_flag
FROM ar_ae_alloc_rec_gt a1
WHERE a1.ae_id = g_id
AND a1.ae_summarize_flag = 'N'
AND a1.ae_account_class IS NULL
AND (NVL(a1.ae_entered_dr,0) <> 0 OR NVL(a1.ae_entered_cr,0) <> 0
OR NVL(a1.ae_accounted_dr,0) <> 0 OR NVL(a1.ae_accounted_cr,0) <> 0
OR (a1.ae_line_type IN ('TAX','DEFERRED_TAX','EDISC_NON_REC_TAX',
'UNEDISC_NON_REC_TAX','ADJ_NON_REC_TAX','FINCHRG_NON_REC_TAX')
AND (NVL(a1.ae_entered_dr,0) = 0 AND NVL(a1.ae_entered_cr,0) = 0 AND
NVL(a1.ae_accounted_dr,0) = 0 AND NVL(a1.ae_accounted_cr,0) = 0)))
) al;
| through the Create Debits and Credits function, rather than directly insert |
| into the ar_ae_alloc_rec_gt. |
+------------------------------------------------------------------------------*/
Create_Debits_Credits(l_ent_amt, l_ent_acctd_amt,
l_txb_amt, l_txb_acctd_amt,
l_from_ent_amt, l_from_ent_acctd_amt,
l_ae_line_rec,
NULL, 'SUMMARIZE');
SELECT /*+ INDEX(a1 AR_AE_ALLOC_REC_GT_N3) */
SUM(NVL(a1.ae_entered_dr,0) * -1 + NVL(a1.ae_entered_cr,0)),
SUM(NVL(a1.ae_accounted_dr,0) * -1 + NVL(a1.ae_accounted_cr,0)),
SUM(NVL(a1.ae_taxable_entered_dr,0) * -1 + NVL(a1.ae_taxable_entered_cr,0)),
SUM(NVL(a1.ae_taxable_accounted_dr,0) * -1 + NVL(a1.ae_taxable_accounted_cr,0)),
SUM(NVL(a1.ae_from_amount_dr,0) * -1 + NVL(a1.ae_from_amount_cr,0)),
SUM(NVL(a1.ae_from_acctd_amount_dr,0) * -1 + NVL(a1.ae_from_acctd_amount_cr,0)),
a1.ae_line_type,
a1.ae_line_type_secondary,
a1.ae_source_id,
a1.ae_source_table,
a1.ae_account,
a1.ae_source_id_secondary,
a1.ae_source_table_secondary,
a1.ae_currency_code,
a1.ae_currency_conversion_rate,
a1.ae_currency_conversion_type,
a1.ae_currency_conversion_date,
a1.ae_third_party_id,
a1.ae_third_party_sub_id,
a1.ae_tax_group_code_id,
a1.ae_tax_code_id,
a1.ae_location_segment_id,
a1.ae_tax_link_id,
decode(a1.ae_neg_ind,
-1, decode(Retain_Neg_Ind(a1.rowid),
1, a1.ae_neg_ind,
''),
a1.ae_neg_ind),
a1.ae_reversed_source_id
FROM ar_ae_alloc_rec_gt a1
WHERE a1.ae_id = g_id
AND a1.ae_summarize_flag = 'N'
AND a1.ae_account_class IS NULL
GROUP BY a1.ae_line_type,
a1.ae_line_type_secondary,
a1.ae_source_id,
a1.ae_source_table,
a1.ae_account,
a1.ae_source_id_secondary,
a1.ae_source_table_secondary,
a1.ae_currency_code,
a1.ae_currency_conversion_rate,
a1.ae_currency_conversion_type,
a1.ae_currency_conversion_date,
a1.ae_third_party_id,
a1.ae_third_party_sub_id,
a1.ae_tax_group_code_id,
a1.ae_tax_code_id,
a1.ae_location_segment_id,
a1.ae_tax_link_id,
decode(a1.ae_neg_ind,
-1, decode(Retain_Neg_Ind(a1.rowid),
1, a1.ae_neg_ind,
''),
a1.ae_neg_ind),
a1.ae_reversed_source_id
ORDER BY decode(a1.ae_line_type,
'EDISC' ,-6,
'ADJ' ,-6,
'FINCHRG' ,-6,
'UNEDISC' ,-6,
a1.ae_tax_link_id),
decode(a1.ae_line_type,
'EDISC_NON_REC_TAX' , -5,
'ADJ_NON_REC_TAX' ,-5,
'FINCHRG_NON_REC_TAX',-5,
'UNEDISC_NON_REC_TAX',-5,
'DEFERRED_TAX',decode(a1.ae_line_type_secondary,
'EDISC' , -4,
'ADJ' , -4,
'FINCHRG', -4,
'UNEDISC', -4,
-2),
'TAX' ,decode(a1.ae_line_type_secondary,
'EDISC' , -3,
'ADJ' , -3,
'FINCHRG', -3,
'UNEDISC', -3,
-1),
a1.ae_tax_link_id);
| through the Create Debits and Credits function, rather than directly insert |
| into the ar_ae_alloc_rec_gt. |
+------------------------------------------------------------------------------*/
Create_Debits_Credits(l_ent_amt, l_ent_acctd_amt,
l_txb_amt, l_txb_acctd_amt,
l_from_ent_amt, l_from_ent_acctd_amt,
l_ae_line_rec,
NULL, 'SUMMARIZE');
Insert_Ae_Lines(p_ae_line_tbl => l_ae_line_rec);
select sum(a1.tax_count)
into l_count
from (select 1 tax_count
from ra_customer_trx_lines a2
where a2.line_type = 'TAX'
and a2.link_to_cust_trx_line_id = p_invoice_line_id
group by nvl(a2.location_segment_id, a2.vat_tax_id),
decode(a2.location_segment_id,
'', 'VAT',
'LOC')
) a1;
SELECT /*+ INDEX(a1 AR_AE_ALLOC_REC_GT_N3) */
ABS(SUM(NVL(a1.ae_entered_dr,0) * -1 + NVL(a1.ae_entered_cr,0))) +
ABS(SUM(NVL(a1.ae_accounted_dr,0) * -1 + NVL(a1.ae_accounted_cr,0))) +
ABS(SUM(NVL(a1.ae_taxable_entered_dr,0) * -1 + NVL(a1.ae_taxable_entered_cr,0))) +
ABS(SUM(NVL(a1.ae_taxable_accounted_dr,0) * -1 + NVL(a1.ae_taxable_accounted_cr,0)))
INTO l_amount
FROM ar_ae_alloc_rec_gt a1,
ar_ae_alloc_rec_gt a2
WHERE a1.ae_id = g_id
AND a1.ae_summarize_flag = 'N'
AND a1.ae_account_class IS NULL
AND a2.rowid = p_rowid
AND a1.rowid <> a2.rowid
AND nvl(a1.ae_line_type, '-99') = nvl(a2.ae_line_type, '-99')
AND nvl(a1.ae_line_type_secondary, '-99') = nvl(a2.ae_line_type_secondary, '-99')
AND nvl(a1.ae_source_id,-9999) = nvl(a2.ae_source_id,-9999)
AND nvl(a1.ae_source_table,'-99') = nvl(a2.ae_source_table,'-99')
AND nvl(a1.ae_account,-9999) = nvl(a2.ae_account,-9999)
AND nvl(a1.ae_source_id_secondary,-9999) = nvl(a2.ae_source_id_secondary,-9999)
AND nvl(a1.ae_source_table_secondary,'-99') = nvl(a2.ae_source_table_secondary,'-99')
AND nvl(a1.ae_currency_code, '-99') = nvl(a2.ae_currency_code, '-99')
AND nvl(a1.ae_currency_conversion_rate, -9999) = nvl(a2.ae_currency_conversion_rate, -9999)
AND nvl(a1.ae_currency_conversion_type,'-99') = nvl(a2.ae_currency_conversion_type,'-99')
AND nvl(a1.ae_currency_conversion_date,to_date('01-01-1949','DD-MM-YYYY'))
= nvl(a2.ae_currency_conversion_date,to_date('01-01-1949','DD-MM-YYYY'))
AND nvl(a1.ae_third_party_id, -9999) = nvl(a2.ae_third_party_id, -9999)
AND nvl(a1.ae_third_party_sub_id, -9999) = nvl(a2.ae_third_party_sub_id, -9999)
AND nvl(a1.ae_tax_group_code_id, -9999) = nvl(a2.ae_tax_group_code_id, -9999)
AND nvl(a1.ae_tax_code_id, -9999) = nvl(a2.ae_tax_code_id, -9999)
AND nvl(a1.ae_location_segment_id, -9999) = nvl(a2.ae_location_segment_id, -9999)
AND nvl(a1.ae_tax_link_id, -9999) = nvl(a2.ae_tax_link_id, -9999)
AND nvl(a1.ae_reversed_source_id,-9999) = nvl(a2.ae_reversed_source_id,-9999);
insert into ar_ae_alloc_rec_gt
( ae_id,
ae_account_class ,
ae_customer_trx_id ,
ae_customer_trx_line_id ,
ae_link_to_cust_trx_line_id ,
ae_tax_type ,
ae_code_combination_id ,
ae_collected_tax_ccid ,
ae_line_amount ,
ae_amount ,
ae_acctd_amount ,
ae_taxable_amount ,
ae_taxable_acctd_amount ,
ae_adj_ccid ,
ae_edisc_ccid ,
ae_unedisc_ccid ,
ae_finchrg_ccid ,
ae_adj_non_rec_tax_ccid ,
ae_edisc_non_rec_tax_ccid ,
ae_unedisc_non_rec_tax_ccid ,
ae_finchrg_non_rec_tax_ccid ,
ae_override_ccid1 ,
ae_override_ccid2 ,
ae_tax_link_id ,
ae_tax_link_id_ed_adj ,
ae_tax_link_id_uned ,
ae_tax_link_id_act ,
ae_pro_amt ,
ae_pro_acctd_amt ,
ae_pro_chrg_amt ,
ae_pro_chrg_acctd_amt ,
ae_pro_taxable_amt ,
ae_pro_taxable_acctd_amt ,
ae_counted_flag ,
ae_autotax ,
ae_sum_alloc_amt ,
ae_sum_alloc_acctd_amt ,
ae_tax_line_count ,
ae_line_type ,
ae_line_type_secondary ,
ae_source_id ,
ae_source_table ,
ae_account ,
ae_entered_dr ,
ae_entered_cr ,
ae_accounted_dr ,
ae_accounted_cr ,
ae_source_id_secondary ,
ae_source_table_secondary ,
ae_currency_code ,
ae_currency_conversion_rate ,
ae_currency_conversion_type ,
ae_currency_conversion_date ,
ae_third_party_id ,
ae_third_party_sub_id ,
ae_tax_group_code_id ,
ae_tax_code_id ,
ae_location_segment_id ,
ae_taxable_entered_dr ,
ae_taxable_entered_cr ,
ae_taxable_accounted_dr ,
ae_taxable_accounted_cr ,
ae_reversed_source_id ,
ae_neg_ind ,
ae_summarize_flag ,
ae_cust_trx_line_gl_dist_id ,
ae_ref_line_id ,
ae_from_amount_dr ,
ae_from_amount_cr ,
ae_from_acctd_amount_dr ,
ae_from_acctd_amount_cr ,
ref_account_class,
activity_bucket,
ref_dist_ccid,
ref_mf_dist_flag
)
VALUES
( g_id,
p_ae_line_rec.ae_account_class ,
p_ae_line_rec.ae_customer_trx_id ,
p_ae_line_rec.ae_customer_trx_line_id ,
p_ae_line_rec.ae_link_to_cust_trx_line_id ,
p_ae_line_rec.ae_tax_type ,
p_ae_line_rec.ae_code_combination_id ,
p_ae_line_rec.ae_collected_tax_ccid ,
p_ae_line_rec.ae_line_amount ,
p_ae_line_rec.ae_amount ,
p_ae_line_rec.ae_acctd_amount ,
p_ae_line_rec.ae_taxable_amount ,
p_ae_line_rec.ae_taxable_acctd_amount ,
p_ae_line_rec.ae_adj_ccid ,
p_ae_line_rec.ae_edisc_ccid ,
p_ae_line_rec.ae_unedisc_ccid ,
p_ae_line_rec.ae_finchrg_ccid ,
p_ae_line_rec.ae_adj_non_rec_tax_ccid ,
p_ae_line_rec.ae_edisc_non_rec_tax_ccid ,
p_ae_line_rec.ae_unedisc_non_rec_tax_ccid ,
p_ae_line_rec.ae_finchrg_non_rec_tax_ccid ,
p_ae_line_rec.ae_override_ccid1 ,
p_ae_line_rec.ae_override_ccid2 ,
p_ae_line_rec.ae_tax_link_id ,
p_ae_line_rec.ae_tax_link_id_ed_adj ,
p_ae_line_rec.ae_tax_link_id_uned ,
p_ae_line_rec.ae_tax_link_id_act ,
p_ae_line_rec.ae_pro_amt ,
p_ae_line_rec.ae_pro_acctd_amt ,
p_ae_line_rec.ae_pro_chrg_amt ,
p_ae_line_rec.ae_pro_chrg_acctd_amt ,
p_ae_line_rec.ae_pro_taxable_amt ,
p_ae_line_rec.ae_pro_taxable_acctd_amt ,
p_ae_line_rec.ae_counted_flag ,
p_ae_line_rec.ae_autotax ,
p_ae_line_rec.ae_sum_alloc_amt ,
p_ae_line_rec.ae_sum_alloc_acctd_amt ,
p_ae_line_rec.ae_tax_line_count ,
p_ae_line_rec.ae_line_type ,
p_ae_line_rec.ae_line_type_secondary ,
p_ae_line_rec.ae_source_id ,
p_ae_line_rec.ae_source_table ,
p_ae_line_rec.ae_account ,
p_ae_line_rec.ae_entered_dr ,
p_ae_line_rec.ae_entered_cr ,
p_ae_line_rec.ae_accounted_dr ,
p_ae_line_rec.ae_accounted_cr ,
p_ae_line_rec.ae_source_id_secondary ,
p_ae_line_rec.ae_source_table_secondary ,
p_ae_line_rec.ae_currency_code ,
p_ae_line_rec.ae_currency_conversion_rate ,
p_ae_line_rec.ae_currency_conversion_type ,
p_ae_line_rec.ae_currency_conversion_date ,
p_ae_line_rec.ae_third_party_id ,
p_ae_line_rec.ae_third_party_sub_id ,
p_ae_line_rec.ae_tax_group_code_id ,
p_ae_line_rec.ae_tax_code_id ,
p_ae_line_rec.ae_location_segment_id ,
p_ae_line_rec.ae_taxable_entered_dr ,
p_ae_line_rec.ae_taxable_entered_cr ,
p_ae_line_rec.ae_taxable_accounted_dr ,
p_ae_line_rec.ae_taxable_accounted_cr ,
p_ae_line_rec.ae_reversed_source_id ,
p_ae_line_rec.ae_neg_ind ,
NVL(p_ae_line_rec.ae_summarize_flag,'N') ,
p_ae_line_rec.ae_cust_trx_line_gl_dist_id ,
p_ae_line_rec.ae_ref_line_id ,
p_ae_line_rec.ae_from_amount_dr ,
p_ae_line_rec.ae_from_amount_cr ,
p_ae_line_rec.ae_from_acctd_amount_dr ,
p_ae_line_rec.ae_from_acctd_amount_cr ,
p_ae_line_rec.ref_account_class,
p_ae_line_rec.activity_bucket,
p_ae_line_rec.ref_dist_ccid,
p_ae_line_rec.ref_mf_dist_flag
);
| PRIVATE PROCEDURE Insert_Ae_Lines
|
| DESCRIPTION
| Inserts into AR_DISTRIBUTIONS accounting lines
| ----------------------------------------------
| Calls the table handler for AR_DISTRIBUTIONS to insert accounting
| for a given document into the underlying table.
|
| PARAMETERS
| p_ae_line_tbl IN Accounting lines table
| History
| 24-NOV-2003 Herve Yu Distributions in the receipt currency
*=======================================================================*/
PROCEDURE Insert_Ae_Lines(p_ae_line_tbl IN ar_ae_alloc_rec_gt%ROWTYPE) IS
l_ae_line_rec ar_distributions%ROWTYPE;
arp_standard.debug( 'ARP_ALLOCATION_PKG.Insert_Ae_Lines()+');
arp_distributions_pkg.insert_p(l_ae_line_rec, l_dummy);
/* need to insert records into the MRC table. Calling new
mrc engine */
-- before we call the ar_mrc_engine, we need the line_id of
-- the primary row. If the Source type is EXCH_GAIN, EXCH_LOSS
-- or CURR_ROUND, use a new line_id from the sequence.
-- arp_standard.debug('source type = ' || l_ae_line_rec.source_type);
arp_standard.debug( 'ARP_ACCT_MAIN.Insert_Ae_Lines()-');
END Insert_Ae_Lines;
| Inserts into AR_DISTRIBUTIONS accounting lines
| ----------------------------------------------
| Calls the table handler for AR_DISTRIBUTIONS to insert accounting
| for a given document into the underlying table.
|
| PARAMETERS
| p_ae_line_tbl IN Accounting lines table
|
*=======================================================================*/
PROCEDURE Cache_Ae_Lines(p_ae_line_tbl IN ar_ae_alloc_rec_gt%ROWTYPE) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'ARP_ALLOCATION_PKG.Cache_Ae_Lines()+');
SELECT arp_etax_util.get_tax_account(tax.tax_rate_id,
null,
'ADJ',
'TAX_RATE'),
arp_etax_util.get_tax_account(tax.tax_rate_id,
null,
'ADJ_NON_REC',
'TAX_RATE'),
rt.tax_recoverable_flag
FROM ar_receivables_trx rt,
zx_sco_rates tax
WHERE rt.receivables_trx_id = p_receivables_trx_id
AND rt.asset_tax_code = tax.tax_rate_code
AND sysdate between nvl(tax.effective_from, sysdate) AND
nvl(tax.effective_to, sysdate);
SELECT arp_etax_util.get_tax_account(tax.tax_rate_id,
null,
'ADJ',
'TAX_RATE'),
arp_etax_util.get_tax_account(tax.tax_rate_id,
null,
'ADJ_NON_REC',
'TAX_RATE'),
rt.tax_recoverable_flag
FROM ar_receivables_trx rt,
ar_rec_trx_le_details rtd,
zx_sco_rates tax
WHERE rt.receivables_trx_id = p_receivables_trx_id
AND rtd.receivables_trx_id (+) = rt.receivables_trx_id
AND rtd.legal_entity_id (+) = p_legal_entity_id
AND nvl(rtd.asset_tax_code, rt.asset_tax_code)
= tax.tax_rate_code
AND trunc(sysdate) between nvl(tax.effective_from, trunc(sysdate)) AND
nvl(tax.effective_to, trunc(sysdate));
SELECT arp_etax_util.get_tax_account(tl.customer_trx_line_id,
trunc(sysdate),
'ADJ',
'TAX_LINE'),
arp_etax_util.get_tax_account(tl.customer_trx_line_id,
trunc(sysdate),
'FINCHRG',
'TAX_LINE')
FROM ra_customer_trx_lines tl
WHERE tl.customer_trx_id = p_customer_trx_id
AND tl.line_type = 'TAX'
AND tl.tax_line_id IS NOT NULL;
SELECT legal_entity_id
INTO l_le_id
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;