[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_TAX_UTIL
Source
1 PACKAGE BODY ARP_TRX_TAX_UTIL AS
2 /* $Header: ARTCTTXB.pls 115.4 2003/10/10 14:27:45 mraymond ship $ */
3
4 /*===========================================================================+
5 | PROCEDURE |
6 | get_default_line_num |
7 | |
8 | DESCRIPTION |
9 | Returns the default line number. |
10 | |
11 | SCOPE - PUBLIC |
12 | |
13 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
14 | arp_util.debug |
15 | |
16 | ARGUMENTS : IN: |
17 | p_customer_trx_id |
18 | OUT: |
19 | p_line_number |
20 | |
21 | NOTES |
22 | |
23 | MODIFICATION HISTORY |
24 | 03-JAN-96 Sunil Mody Created |
25 | |
26 +===========================================================================*/
27
28 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
29
30 PROCEDURE get_default_line_num(p_customer_trx_id IN
31 ra_customer_trx_lines.customer_trx_id%type,
32 p_customer_trx_line_id IN
33 ra_customer_trx_lines.customer_trx_line_id%type,
34 p_line_number OUT NOCOPY
35 ra_customer_trx_lines.line_number%type )
36 IS
37
38 BEGIN
39
40 IF PG_DEBUG in ('Y', 'C') THEN
41 arp_util.debug('arp_trx_tax_util.get_default_line_num()+');
42 END IF;
43
44 SELECT nvl( max(line_number), 0 ) + 1
45 INTO p_line_number
46 FROM ra_customer_trx_lines
47 WHERE customer_trx_id = p_customer_trx_id
48 AND link_to_cust_trx_line_id = p_customer_trx_line_id
49 AND line_type = 'TAX';
50
51 IF PG_DEBUG in ('Y', 'C') THEN
52 arp_util.debug('arp_trx_tax_util.get_default_line_num()-');
53 END IF;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 IF PG_DEBUG in ('Y', 'C') THEN
58 arp_util.debug('EXCEPTION: arp_trx_tax_util.get_default_line_num()');
59 arp_util.debug('get_default_line_num: ' ||
60 '---------- ' ||
61 'Parameters for arp_trx_tax_util.get_default_line_num() ' ||
62 '---------- ');
63 arp_util.debug('get_default_line_num: ' || 'p_customer_trx_id = ' || p_customer_trx_id);
64 END IF;
65
66 RAISE;
67
68 END get_default_line_num;
69
70 /*===========================================================================+
71 | PROCEDURE |
72 | get_item_flex_defaults |
73 | |
74 | DESCRIPTION |
75 | Returns the defaults for the specified item flex |
76 | |
77 | SCOPE - PUBLIC |
78 | |
79 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
80 | arp_util.debug |
81 | |
82 | ARGUMENTS : IN: |
83 | p_inventory_item_id |
84 | p_organization_id |
85 | p_trx_date |
86 | p_invoicing_rule_id |
87 | OUT: |
88 | p_description |
89 | p_primary_uom_code |
90 | p_primary_uom_name |
91 | p_accounting_rule_id |
92 | p_accounting_rule_name |
93 | p_accounting_rule_duration |
94 | p_accounting_rule_type |
95 | p_rule_start_date |
96 | |
97 | NOTES |
98 | |
99 | MODIFICATION HISTORY |
100 | 03-JAN-96 Sunil Mody Created |
101 | |
102 +===========================================================================*/
103
104 PROCEDURE get_item_flex_defaults(p_inventory_item_id IN
105 mtl_system_items.inventory_item_id%type,
106 p_organization_id IN
107 mtl_system_items.organization_id%type,
108 p_trx_date IN
109 ra_customer_trx.trx_date%type,
110 p_invoicing_rule_id IN
111 ra_customer_trx.invoicing_rule_id%type,
112 p_description OUT NOCOPY
113 mtl_system_items.description%type,
114 p_primary_uom_code OUT NOCOPY
115 mtl_system_items.primary_uom_code%type,
116 p_primary_uom_name OUT NOCOPY
117 mtl_units_of_measure.unit_of_measure%type,
118 p_accounting_rule_id OUT NOCOPY
119 mtl_system_items.accounting_rule_id%type,
120 p_accounting_rule_name OUT NOCOPY
121 ra_rules.name%type,
122 p_accounting_rule_duration OUT NOCOPY
123 ra_rules.occurrences%type,
124 p_accounting_rule_type OUT NOCOPY
125 ra_rules.type%type,
126 p_rule_start_date OUT NOCOPY
127 date )
128 IS
129
130 BEGIN
131
132 IF PG_DEBUG in ('Y', 'C') THEN
133 arp_util.debug('arp_trx_tax_util.get_item_flex_defaults()+');
134 END IF;
135
136 SELECT msi.description,
137 DECODE( SIGN( p_trx_date - TRUNC( NVL(muom.disable_date,
138 p_trx_date) ) ),
139 -1, muom.uom_code,
140 0, muom.uom_code,
141 1, null
142 ),
143 DECODE( SIGN( p_trx_date - TRUNC( NVL(muom.disable_date,
144 p_trx_date) ) ),
145 -1, muom.unit_of_measure,
146 0, muom.unit_of_measure,
147 1, null
148 ),
149 DECODE( rr.status,
150 'A', msi.accounting_rule_id,
151 null ),
152 DECODE( rr.status,
153 'A', rr.name,
154 null ),
155 DECODE( rr.status,
156 'A', DECODE(rr.type,
157 'ACC_DUR', 1,
158 'A', rr.occurrences )
159 ),
160 DECODE( rr.status,
161 'A', rr.type,
162 null ),
163 DECODE( rr.status,
164 'A', DECODE( rr.frequency,
165 'SPECIFIC', min(rs.rule_date),
166 DECODE( p_invoicing_rule_id,
167 -2, p_trx_date,
168 -3, sysdate )
169 ),
170 null
171 )
172 INTO p_description,
173 p_primary_uom_code,
174 p_primary_uom_name,
175 p_accounting_rule_id,
176 p_accounting_rule_name,
177 p_accounting_rule_duration,
178 p_accounting_rule_type,
179 p_rule_start_date
180 FROM mtl_system_items msi,
181 mtl_units_of_measure muom,
182 ra_rules rr,
183 ra_rule_schedules rs
184 WHERE msi.inventory_item_id = p_inventory_item_id
185 AND msi.organization_id = p_organization_id
186 AND msi.primary_uom_code = muom.uom_code (+)
187 AND msi.accounting_rule_id = rr.rule_id (+)
188 AND rr.rule_id = rs.rule_id (+)
189 GROUP BY msi.description,
190 muom.disable_date,
191 muom.uom_code,
192 muom.unit_of_measure,
193 rr.status,
194 msi.accounting_rule_id,
195 rr.name,
196 rr.type,
197 rr.occurrences,
198 rr.frequency;
199
200 IF PG_DEBUG in ('Y', 'C') THEN
201 arp_util.debug('arp_trx_tax_util.get_item_flex_defaults()-');
202 END IF;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 IF PG_DEBUG in ('Y', 'C') THEN
207 arp_util.debug('EXCEPTION: arp_trx_tax_util.get_item_flex_defaults()');
208 arp_util.debug('get_item_flex_defaults: ' ||
209 '---------- ' ||
210 'Parameters for arp_trx_tax_util.get_item_flex_defaults() ' ||
211 '---------- ');
212 arp_util.debug('get_item_flex_defaults: ' || 'p_inventory_item_id = ' || p_inventory_item_id);
213 arp_util.debug('get_item_flex_defaults: ' || 'p_organization_id = ' || p_organization_id );
214 arp_util.debug('get_item_flex_defaults: ' || 'p_trx_date = ' || p_trx_date );
215 arp_util.debug('get_item_flex_defaults: ' || 'p_invoicing_rule_id = ' || p_invoicing_rule_id );
216 END IF;
217
218 RAISE;
219
220 END get_item_flex_defaults;
221
222 /*===========================================================================+
223 | PROCEDURE |
224 | select_summary |
225 | |
226 | DESCRIPTION |
227 | Returns the sum of the extended amount. |
228 | |
229 | SCOPE - PUBLIC |
230 | |
231 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
232 | arp_util.debug |
233 | |
234 | ARGUMENTS : IN: |
235 | p_customer_trx_id |
236 | OUT: |
237 | p_total |
238 | p_total_rtot_db |
239 | |
240 | NOTES |
241 | |
242 | MODIFICATION HISTORY |
243 | 03-JAN-96 Sunil Mody Created |
244 | |
245 +===========================================================================*/
246
247 PROCEDURE select_summary(p_customer_trx_id IN
248 ra_customer_trx_lines.customer_trx_id%type,
249 p_customer_trx_line_id IN number,
250 p_mode IN varchar2,
251 p_total IN OUT NOCOPY
252 ra_customer_trx_lines.extended_amount%type,
253 p_total_rtot_db IN OUT NOCOPY
254 ra_customer_trx_lines.extended_amount%type)
255 IS
256
257 BEGIN
258
259 IF PG_DEBUG in ('Y', 'C') THEN
260 arp_util.debug('arp_trx_tax_util.select_summary()+');
261 END IF;
262
263 SELECT nvl(sum(extended_amount),0), nvl(sum(extended_amount),0)
264 INTO p_total, p_total_rtot_db
265 FROM ra_customer_trx_lines
266 WHERE customer_trx_id = p_customer_trx_id
267 AND NVL( link_to_cust_trx_line_id, -10 ) =
268 DECODE(p_mode,
269 'LINE', p_customer_trx_line_id,
270 'ALL', link_to_cust_trx_line_id,
271 -10 )
272 AND line_type = 'TAX';
273
274 IF PG_DEBUG in ('Y', 'C') THEN
275 arp_util.debug('arp_trx_tax_util.select_summary()-');
276 END IF;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 IF PG_DEBUG in ('Y', 'C') THEN
281 arp_util.debug('EXCEPTION: arp_trx_tax_util.select_summary()');
282 arp_util.debug('select_summary: ' || 'p_customer_trx_id = ' || p_customer_trx_id);
283 END IF;
284 RAISE;
285 END select_summary;
286 /*===========================================================================+
287 | PROCEDURE |
288 | check_last_line_on_delete |
289 | |
290 | DESCRIPTION |
291 | Returns whether this is the only tax line for the customer_trx_line_id |
292 | |
293 | SCOPE - PUBLIC |
294 | |
295 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
296 | arp_util.debug |
297 | |
298 | ARGUMENTS : IN: |
299 | p_customer_trx_line_id |
300 | OUT: |
301 | p_only_tax_line |
305 | MODIFICATION HISTORY |
302 | |
303 | NOTES |
304 | |
306 | 28-MAR-96 Vikas Mahajan Created |
307 | |
308 +===========================================================================*/
309
310 PROCEDURE check_last_line_on_delete(p_customer_trx_line_id IN
311 ra_customer_trx_lines.customer_trx_line_id%type
312 ,
313 p_only_tax_line_flag OUT NOCOPY BOOLEAN)
314 IS
315 l_only_tax_line_flag varchar2(2);
316 BEGIN
317
318 arp_util.debug('arp_trx_tax_util.check_last_line_on_delete()+');
319 SELECT decode(max(dummy),
320 '', 'N',
321 'Y')
322 INTO l_only_tax_line_flag
323 FROM dual
324 WHERE EXISTS
325 (SELECT 'deleted last tax line'
326 FROM ra_customer_trx_lines
327 WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
328 AND line_type = 'TAX'
329 having count(*) = 1
330 ) ;
331
332 IF (l_only_tax_line_flag = 'Y')
333 THEN p_only_tax_line_flag := TRUE;
334 ELSE p_only_tax_line_flag := FALSE;
335 END IF;
336
337 arp_util.debug('arp_trx_tax_util.check_last_line_on_delete()-');
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 arp_util.debug('EXCEPTION: arp_trx_tax_util.check_last_line_on_delete()');
342 arp_util.debug('p_customer_trx_line_id = ' || p_customer_trx_line_id);
343 RAISE;
344
345 END;
346 /*===========================================================================+
347 | PROCEDURE |
348 | check_unique_line |
349 | |
350 | DESCRIPTION |
351 | Returns whether the new tax line number is unique for the invoice line |
352 | SCOPE - PUBLIC |
353 | |
354 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
355 | arp_util.debug |
356 | |
357 | ARGUMENTS : IN: |
358 | p_customer_trx_line_id |
359 | p_customer_trx_tax_line_num |
360 | OUT: |
361 | p_unique_line_flag |
362 | TRUE - if line is unique |
363 | FALSE - if line is non unique |
364 | |
365 | NOTES |
366 | |
367 | MODIFICATION HISTORY |
368 | 28-MAR-96 Vikas Mahajan Created |
369 | |
370 +===========================================================================*/
371
372 PROCEDURE check_unique_line(p_customer_trx_line_id IN
373 ra_customer_trx_lines.customer_trx_line_id%type,
374 p_customer_trx_line_num IN
375 ra_customer_trx_lines.line_number%type,
376 p_unique_line_flag OUT NOCOPY Boolean )
377 IS
378 l_unique_line_flag varchar2(2);
379 BEGIN
380
381 arp_util.debug('arp_trx_tax_util.check_unique_line()+');
382 SELECT decode(max(dummy),
383 '', 'N',
384 'Y')
385 INTO l_unique_line_flag
386 FROM dual
387 WHERE EXISTS
388 (SELECT 'unique tax line'
389 FROM ra_customer_trx_lines
390 WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
391 AND line_type = 'TAX'
392 AND line_number = p_customer_trx_line_num
393 ) ;
394
395 IF (l_unique_line_flag='Y')
396 THEN
397 p_unique_line_flag := FALSE;
398 ELSE
399 p_unique_line_flag := TRUE;
400 END IF;
401
402 arp_util.debug('arp_trx_tax_util.check_unique_line()-');
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 arp_util.debug('EXCEPTION: arp_trx_tax_util.check_unique_line()');
407 arp_util.debug(
408 '---------- ' ||
409 'Parameters for arp_trx_tax_util.check_unique_line() ' ||
410 '---------- ');
411 arp_util.debug('p_customer_trx_line_id = ' || p_customer_trx_line_id);
412 arp_util.debug('p_customer_trx_line_number = ' || p_customer_trx_line_num);
413
414 RAISE;
415 END;
419 | |
416 /*===========================================================================+
417 | FUNCTION |
418 | balance_due |
420 | DESCRIPTION |
421 | Returns balance due for this tax line |
422 | |
423 | SCOPE - PUBLIC |
424 | |
425 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
426 | arp_util.debug |
427 | |
428 | ARGUMENTS : IN: |
429 | p_customer_trx_line_id |
430 | OUT: |
431 | |
432 | NOTES |
433 | |
434 | MODIFICATION HISTORY |
435 | 28-MAR-96 Vikas Mahajan Created |
436 | |
437 +===========================================================================*/
438
439
440 FUNCTION tax_balance(p_prev_cust_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
441 p_cust_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type )
442
443 RETURN NUMBER IS
444 l_balance_due number;
445 Begin
446 arp_util.debug('arp_trx_tax_util.tax_balance()+');
447
448 /* Calculate Balance Due By Checking Any other Credit Memos
449 Which Are Complete */
450
451 SELECT nvl(sum(extended_amount),0)
452 INTO l_balance_due
453 FROM ra_customer_trx_lines ctl,
454 ra_customer_trx ct
455 WHERE previous_customer_trx_line_id = p_prev_cust_trx_line_id
456 AND customer_trx_line_id <> p_cust_trx_line_id
457 AND ctl.customer_trx_id = ct.customer_trx_id
458 AND ct.complete_flag = 'Y';
459
460 return(l_balance_due);
461
462 arp_util.debug('arp_trx_tax_util.tax_balance()-');
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 arp_util.debug('EXCEPTION: arp_trx_tax_util.tax_balance');
467 RAISE;
468 End;
469
470 /*===========================================================================+
471 | PROCEDURE |
472 | check_tax_code |
473 | |
474 | DESCRIPTION |
475 | Returns whether the tax code is adhoc or not. |
476 | SCOPE - PUBLIC |
477 | |
478 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
479 | arp_util.debug |
480 | |
481 | ARGUMENTS : IN: |
482 | p_tax_code |
483 | OUT: |
484 | p_adhoc_tax_flag |
485 | TRUE - if tax_code is adhoc |
486 | FALSE - if tax_code is non adhoc |
487 | |
488 | NOTES |
489 | |
490 | MODIFICATION HISTORY |
491 | 28-MAR-96 Vikas Mahajan Created |
492 | |
493 +===========================================================================*/
494
495 PROCEDURE check_tax_code(p_tax_code IN
496 ar_vat_tax.tax_code%type,
497 p_adhoc_tax_flag OUT NOCOPY Boolean )
498 IS
499 l_adhoc_tax_flag varchar2(2);
500 BEGIN
501
502 arp_util.debug('arp_trx_tax_util.check_tax_code()+');
503 SELECT validate_flag
504 INTO l_adhoc_tax_flag
505 FROM ar_vat_tax
506 WHERE tax_code = p_tax_code;
507
508 IF (l_adhoc_tax_flag='Y')
509 THEN
510 p_adhoc_tax_flag := TRUE;
511 ELSE
512 p_adhoc_tax_flag := FALSE;
513 END IF;
514
515 arp_util.debug('arp_trx_tax_util.check_tax_code()-');
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 arp_util.debug('EXCEPTION: arp_trx_tax_util.check_tax_code()');
520 arp_util.debug(
521 '---------- ' ||
525
522 'Parameters for arp_trx_tax_util.check_tax_code() ' ||
523 '---------- ');
524 arp_util.debug('p_tax_code = ' || p_tax_code);
526 RAISE;
527 END;
528 END ARP_TRX_TAX_UTIL;