[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| update_header |
| |
| DESCRIPTION |
| Recalculates tax for the given transaction, returning true if |
| the new tax amount is the same as the old tax amount. |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-Nov-95 Nigel Smith Created |
| |
+===========================================================================*/
PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
function update_header( p_customer_trx_id IN number, p_msg out NOCOPY varchar2 ) return BOOLEAN IS
l_old_trx_rec ra_customer_trx%rowtype;
select sum(extended_amount), count(l.customer_trx_line_id), max(l.customer_trx_line_id)
into l_old_tax_amount, l_old_tax_lines, l_max_cust_trx_line_id
from ra_customer_trx_lines l
where l.customer_trx_id = l_some_trx_id
and line_type = 'TAX'
and l.autotax = 'Y'
and l.customer_trx_id = p_customer_trx_id
and l.customer_trx_line_id in ( select customer_trx_line_id from ra_cust_trx_line_gl_dist
where customer_trx_line_id = l.customer_trx_line_id );
arp_process_header.update_header(
'TEST',
1,
l_some_trx_rec,
l_some_trx_id,
l_some_trx_amount,
'INV',
l_some_gl_date,
l_some_ictli,
l_some_commitment_rec,
'Y',
l_some_in_use_before,
TRUE,
FALSE,
l_some_dispute_amt,
l_some_dispute_date,
l_status );
| Verify that all columns were updated properly |
+------------------------------------------------*/
select sum(extended_amount), count(l.customer_trx_line_id), min(l.customer_trx_line_id)
into l_new_tax_amount, l_new_tax_lines, l_min_cust_trx_line_id
from ra_customer_trx_lines l
where l.customer_trx_id = l_some_trx_id
and l.line_type = 'TAX'
and l.autotax = 'Y'
and l.customer_trx_id = p_customer_trx_id
and l.customer_trx_line_id in ( select customer_trx_line_id from ra_cust_trx_line_gl_dist
where customer_trx_line_id = l.customer_trx_line_id );
END update_header;
| update_all_headers |
| |
| DESCRIPTION |
| Calls update_header for every transaction that has |
| One and only invoice automatically generated tax line per invoice line |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| arp_ct_pkg.fetch_p |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-Nov-95 Nigel Smith Created |
| 10-Mar-01 Debbie Jancis modified for tca uptake. Removed all |
| references of ar/ra customer tables |
| and replaced with hz counterparts. |
+===========================================================================*/
procedure update_all_headers( p_tax_line_count in number default NULL ) IS
cursor c_trx is
select t.customer_trx_id,
t.trx_number,
t.trx_date,
count( tax.customer_trx_line_id) count_tax
from ra_customer_trx t,
ra_cust_trx_types y,
ra_customer_trx p,
ra_customer_trx_lines l,
ra_customer_trx_lines tax,
ar_vat_tax vat,
hz_cust_acct_sites sa,
hz_cust_site_uses sus
where t.customer_trx_id = l.customer_trx_id
and tax.link_to_cust_trx_line_id = l.customer_trx_line_id
and tax.vat_tax_id = vat.vat_tax_id(+)
and t.previous_customer_trx_id = p.customer_trx_id(+)
and t.cust_trx_type_id = y.cust_trx_type_id
and nvl( t.ship_to_site_use_id, t.bill_to_site_use_id) = sus.site_use_id
and sa.cust_acct_site_id = sus.cust_acct_site_id
and t.customer_trx_id not in ( select customer_trx_id from ra_customer_trx_lines where line_type = 'TAX'
and nvl(autotax,'N') = 'N' and customer_trx_id = t.customer_trx_id )
group by t.customer_trx_id, t.trx_number, t.trx_date
order by t.trx_number;
arp_util.debug('update_all_headers: ' || 'arp_test_tax - ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI') );
l_pass := update_header( hdr.customer_trx_id, msg );
arp_util.debug('update_all_headers: ' || 'arp_test_tax - ' || rpad(hdr.trx_number||'-'||hdr.count_tax,30, '.') || ' ' || 'Pass ' || to_char(sysdate, 'HH24:MI:SS') );
arp_util.debug('update_all_headers: ' || 'arp_test_tax - ' || rpad(hdr.trx_number||'-'||hdr.count_tax,30, '.') || ' ' || 'Fail ' ||
to_char(sysdate, 'HH24:MI:SS') || ' ' || substr(msg,1,100) );
END update_all_headers;
SELECT l.customer_trx_line_id customer_trx_line_id,
l.extended_amount extended_amount,
sum(d.amount) amount,
sum(d.acctd_amount) acctd_amount,
sum(decode( t.invoice_currency_code, 'USD', 1, t.exchange_rate )*l.extended_amount )
trx_acctd_amount,
sum(d.percent) percent,
t.invoice_currency_code invoice_currency_code
FROM ra_customer_trx_lines l,
ra_cust_trx_line_gl_dist d,
ra_customer_trx t
WHERE l.customer_trx_line_id = d.customer_trx_line_id
AND l.customer_trx_id = p_customer_trx_id
AND l.customer_trx_id = t.customer_trx_id
AND d.account_class = 'TAX'
AND d.acctd_amount IS NOT NULL
AND d.amount IS NOT NULL
GROUP BY l.customer_trx_id, t.invoice_currency_code, t.exchange_rate, l.customer_trx_line_id, l.extended_amount
HAVING sum(d.amount) <> l.extended_amount
OR round(sum(d.acctd_amount)) <>
round(decode( t.invoice_currency_code, 'USD', 1, t.exchange_rate )*l.extended_amount)
UNION
/* Search for any tax accounting, where the tax accounting is without
a parent tax line within this transaction.
*/
SELECT d.customer_trx_line_id customer_trx_line_id,
to_number(null) extended_amount,
sum(d.amount) amount,
sum(d.acctd_amount) acctd_amount,
to_number(NULL) trx_acctd_amount,
sum(d.percent) percent,
to_char(NULL) invoice_currency_code
FROM ra_cust_trx_line_gl_dist d
WHERE d.customer_trx_id = p_customer_trx_id
AND d.account_set_flag = 'N'
AND not exists ( select 'x' from ra_customer_trx_lines l
where l.customer_trx_id = d.customer_trx_id )
GROUP BY d.customer_trx_line_id, d.customer_trx_id;