[Home] [Help]
PACKAGE BODY: APPS.ARP_TEST_TAX
Source
1 package body arp_test_tax as
2 /* $Header: ARTSTTXB.pls 115.6 2003/10/10 14:29:28 mraymond ship $ */
3
4 /*===========================================================================+
5 | FUNCTION |
6 | update_header |
7 | |
8 | DESCRIPTION |
9 | Recalculates tax for the given transaction, returning true if |
10 | the new tax amount is the same as the old tax amount. |
11 | |
12 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
13 | |
14 | NOTES |
15 | |
16 | MODIFICATION HISTORY |
17 | 28-Nov-95 Nigel Smith Created |
18 | |
19 +===========================================================================*/
20
21 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
22
23 function update_header( p_customer_trx_id IN number, p_msg out NOCOPY varchar2 ) return BOOLEAN IS
24
25 l_old_trx_rec ra_customer_trx%rowtype;
26 l_some_trx_rec ra_customer_trx%rowtype;
27 l_some_trx_id ra_customer_trx.customer_trx_id%type;
28 l_some_commitment_rec arp_process_commitment.commitment_rec_type;
29 l_some_result BOOLEAN;
30
31 l_some_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
32 l_some_trx_amount ra_cust_trx_line_gl_dist.amount%type;
33 l_some_ictli ra_customer_trx_lines.initial_customer_trx_line_id%type;
34 l_some_in_use_before varchar2(1);
35 l_some_in_use_after varchar2(1);
36
37 l_old_tax_amount number;
38 l_new_tax_amount number;
39 l_old_tax_lines number;
40 l_new_tax_lines number;
41 l_min_cust_trx_line_id number;
42 l_max_cust_trx_line_id number;
43 l_some_dispute_amt number := NULL;
44 l_some_dispute_date date := NULL;
45 l_status varchar2(100);
46 l_msg varchar2(2000);
47
48 begin
49
50 arp_ct_pkg.set_to_dummy(l_some_trx_rec);
51 l_some_trx_id := p_customer_trx_id;
52
53 select sum(extended_amount), count(l.customer_trx_line_id), max(l.customer_trx_line_id)
54 into l_old_tax_amount, l_old_tax_lines, l_max_cust_trx_line_id
55 from ra_customer_trx_lines l
56 where l.customer_trx_id = l_some_trx_id
57 and line_type = 'TAX'
58 and l.autotax = 'Y'
59 and l.customer_trx_id = p_customer_trx_id
60 and l.customer_trx_line_id in ( select customer_trx_line_id from ra_cust_trx_line_gl_dist
61 where customer_trx_line_id = l.customer_trx_line_id );
62
63
64
65 arp_ct_pkg.fetch_p(l_old_trx_rec, l_some_trx_id);
66
67 arp_test_tax.test_description := l_old_trx_rec.trx_number;
68
69 arp_process_header.update_header(
70 'TEST',
71 1,
72 l_some_trx_rec,
73 l_some_trx_id,
74 l_some_trx_amount,
75 'INV',
76 l_some_gl_date,
77 l_some_ictli,
78 l_some_commitment_rec,
79 'Y',
80 l_some_in_use_before,
81 TRUE,
82 FALSE,
83 l_some_dispute_amt,
84 l_some_dispute_date,
85 l_status );
86
87 /*------------------------------------------------+
88 | Verify if tax 'soft error' was raised. |
89 +------------------------------------------------*/
90 IF ( nvl(l_status,'IGNORE') = 'AR_TAX_EXCEPTION' ) THEN
91 fnd_message.retrieve( l_msg );
92 p_msg := NVL( l_msg, sqlerrm );
93 return(FALSE);
94 END IF;
95
96 /*------------------------------------------------+
97 | Verify that all columns were updated properly |
98 +------------------------------------------------*/
99
100 select sum(extended_amount), count(l.customer_trx_line_id), min(l.customer_trx_line_id)
101 into l_new_tax_amount, l_new_tax_lines, l_min_cust_trx_line_id
102 from ra_customer_trx_lines l
103 where l.customer_trx_id = l_some_trx_id
104 and l.line_type = 'TAX'
105 and l.autotax = 'Y'
106 and l.customer_trx_id = p_customer_trx_id
107 and l.customer_trx_line_id in ( select customer_trx_line_id from ra_cust_trx_line_gl_dist
108 where customer_trx_line_id = l.customer_trx_line_id );
109
110 IF NVL(l_new_tax_amount,-1) = NVL(l_old_tax_amount,-1) AND
111 NVL(l_new_tax_lines,-1) = NVL(l_old_tax_lines,-1) AND
112 NVL(l_min_cust_trx_line_id,-1) > NVL(l_max_cust_trx_line_id,-2)
113 THEN
114 /*--------------------------------------------------------------------------+
115 | Check Autoaccounting for for all tax lines associated with this invoice |
116 +--------------------------------------------------------------------------*/
117 l_some_result := check_dist( l_some_trx_id );
118 l_some_result := TRUE; /* Not Checked for JAN CD Release */
119 ELSE
120 p_msg := 'Amounts(' || l_old_tax_amount ||','||l_new_tax_amount||') ' ||
121 'Tax Lines( ' || l_old_tax_lines || ','||l_new_tax_lines||') ' ||
122 'Line IDs( ' || l_max_cust_trx_line_id || ', ' || l_min_cust_trx_line_id || ' )' ;
123 l_some_result := FALSE;
124 END IF;
125 return( l_some_result );
126
127
128 exception
129 when others
130 then
131 fnd_message.retrieve( l_msg );
132 p_msg := NVL( l_msg, sqlerrm );
133 return(false);
134
135
136 END update_header;
137
138
139 /*===========================================================================+
140 | FUNCTION |
141 | update_all_headers |
142 | |
143 | DESCRIPTION |
144 | Calls update_header for every transaction that has |
145 | One and only invoice automatically generated tax line per invoice line |
146 | |
147 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
148 | arp_util.debug |
149 | arp_ct_pkg.fetch_p |
150 | |
151 | NOTES |
152 | |
153 | MODIFICATION HISTORY |
154 | 28-Nov-95 Nigel Smith Created |
155 | 10-Mar-01 Debbie Jancis modified for tca uptake. Removed all |
156 | references of ar/ra customer tables |
157 | and replaced with hz counterparts. |
158 +===========================================================================*/
159
160
161 procedure update_all_headers( p_tax_line_count in number default NULL ) IS
162
163 cursor c_trx is
164 select t.customer_trx_id,
165 t.trx_number,
166 t.trx_date,
167 count( tax.customer_trx_line_id) count_tax
168 from ra_customer_trx t,
169 ra_cust_trx_types y,
170 ra_customer_trx p,
171 ra_customer_trx_lines l,
172 ra_customer_trx_lines tax,
173 ar_vat_tax vat,
174 hz_cust_acct_sites sa,
175 hz_cust_site_uses sus
176 where t.customer_trx_id = l.customer_trx_id
177 and tax.link_to_cust_trx_line_id = l.customer_trx_line_id
178 and tax.vat_tax_id = vat.vat_tax_id(+)
179 and t.previous_customer_trx_id = p.customer_trx_id(+)
180 and t.cust_trx_type_id = y.cust_trx_type_id
181 and nvl( t.ship_to_site_use_id, t.bill_to_site_use_id) = sus.site_use_id
182 and sa.cust_acct_site_id = sus.cust_acct_site_id
183 and t.customer_trx_id not in ( select customer_trx_id from ra_customer_trx_lines where line_type = 'TAX'
184 and nvl(autotax,'N') = 'N' and customer_trx_id = t.customer_trx_id )
185 group by t.customer_trx_id, t.trx_number, t.trx_date
186 order by t.trx_number;
187
188 l_trx_id NUMBER;
189 l_pass BOOLEAN;
190 timecost varchar2(30);
191 starttime date;
192 msg varchar2(2000);
193 row number := 0;
194
195 BEGIN
196 IF PG_DEBUG in ('Y', 'C') THEN
197 arp_util.debug('update_all_headers: ' || 'arp_test_tax - ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI') );
198 END IF;
199 FOR hdr in c_trx
200 LOOP
201 row := row +1;
202
203 EXIT WHEN row > p_tax_line_count;
204
205 starttime := sysdate;
206 l_pass := update_header( hdr.customer_trx_id, msg );
207 timecost := null;
208
209 IF l_pass
210 THEN
211 IF PG_DEBUG in ('Y', 'C') THEN
212 arp_util.debug('update_all_headers: ' || 'arp_test_tax - ' || rpad(hdr.trx_number||'-'||hdr.count_tax,30, '.') || ' ' || 'Pass ' || to_char(sysdate, 'HH24:MI:SS') );
213 END IF;
214 ELSE
215 IF PG_DEBUG in ('Y', 'C') THEN
216 arp_util.debug('update_all_headers: ' || 'arp_test_tax - ' || rpad(hdr.trx_number||'-'||hdr.count_tax,30, '.') || ' ' || 'Fail ' ||
217 to_char(sysdate, 'HH24:MI:SS') || ' ' || substr(msg,1,100) );
218 END IF;
219 END IF;
220
221 END LOOP;
222 END update_all_headers;
223
224
225
226 /*===========================================================================+
227 | FUNCTION |
228 | check_dist |
229 | |
230 | DESCRIPTION |
231 | Check accouting for tax, will return FALSE if any of the accounting |
232 | is incorrect for a given transaction. |
233 | |
234 | NOTES |
235 | |
236 | MODIFICATION HISTORY |
237 | 28-Nov-95 Nigel Smith Created |
238 | |
239 +===========================================================================*/
240
241 FUNCTION check_dist( p_customer_trx_id IN NUMBER ) RETURN BOOLEAN IS
242
243 l_pass BOOLEAN := TRUE;
244
245 cursor c_tax( p_customer_trx_id IN NUMBER ) IS
246
247 /* Search for any tax lines where the tax accounting is not
248 equal to the invoice amounts */
249
250 SELECT l.customer_trx_line_id customer_trx_line_id,
251 l.extended_amount extended_amount,
252 sum(d.amount) amount,
253 sum(d.acctd_amount) acctd_amount,
254 sum(decode( t.invoice_currency_code, 'USD', 1, t.exchange_rate )*l.extended_amount )
255 trx_acctd_amount,
256 sum(d.percent) percent,
257 t.invoice_currency_code invoice_currency_code
258 FROM ra_customer_trx_lines l,
259 ra_cust_trx_line_gl_dist d,
260 ra_customer_trx t
261 WHERE l.customer_trx_line_id = d.customer_trx_line_id
262 AND l.customer_trx_id = p_customer_trx_id
263 AND l.customer_trx_id = t.customer_trx_id
264 AND d.account_class = 'TAX'
265 AND d.acctd_amount IS NOT NULL
266 AND d.amount IS NOT NULL
267 GROUP BY l.customer_trx_id, t.invoice_currency_code, t.exchange_rate, l.customer_trx_line_id, l.extended_amount
268 HAVING sum(d.amount) <> l.extended_amount
269 OR round(sum(d.acctd_amount)) <>
270 round(decode( t.invoice_currency_code, 'USD', 1, t.exchange_rate )*l.extended_amount)
271
272 UNION
273
274 /* Search for any tax accounting, where the tax accounting is without
275 a parent tax line within this transaction.
276 */
277
278 SELECT d.customer_trx_line_id customer_trx_line_id,
279 to_number(null) extended_amount,
280 sum(d.amount) amount,
281 sum(d.acctd_amount) acctd_amount,
282 to_number(NULL) trx_acctd_amount,
283 sum(d.percent) percent,
284 to_char(NULL) invoice_currency_code
285 FROM ra_cust_trx_line_gl_dist d
286 WHERE d.customer_trx_id = p_customer_trx_id
287 AND d.account_set_flag = 'N'
288 AND not exists ( select 'x' from ra_customer_trx_lines l
289 where l.customer_trx_id = d.customer_trx_id )
290 GROUP BY d.customer_trx_line_id, d.customer_trx_id;
291
292
293 BEGIN
294
295 IF PG_DEBUG in ('Y', 'C') THEN
296 arp_util.debug( 'arp_test_tax.check_dist( ' || p_customer_trx_id || ' )+');
297 END IF;
298
299 FOR t in c_tax(p_customer_trx_id)
300 LOOP
301 l_pass := FALSE;
302 IF PG_DEBUG in ('Y', 'C') THEN
303 arp_util.debug('check_dist: ' || t.invoice_currency_code || ' ' ||
304 t.customer_trx_line_id || ' ' ||
305 t.extended_amount || ' ' ||
306 t.amount || ' ' ||
307 t.acctd_amount || ' ' ||
308 t.trx_acctd_amount || ' ' ||
309 t.percent || '%' );
310 END IF;
311 EXIT WHEN NOT l_pass;
312 END LOOP;
313
314 IF PG_DEBUG in ('Y', 'C') THEN
315 arp_util.debug( 'arp_test_tax.check_dist()-');
316 END IF;
317
318 return(l_pass);
319
320 END check_dist;
321
322 END arp_test_tax;