[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_LINE_UTIL
Source
1 PACKAGE BODY ARP_TRX_LINE_UTIL AS
2 /* $Header: ARTCTLTB.pls 120.16 2011/07/19 00:34:16 dgaurab ship $ */
3
4 /*===========================================================================+
5 | FUNCTION |
6 | derive_last_date_to_cr |
7 | |
8 | DESCRIPTION |
9 | This function returns the Last Period to Credit as a date |
10 | given the last period number and customer_trx_line_id and for an |
11 | invoice line with rules. |
12 | |
13 | SCOPE - PUBLIC |
14 | |
15 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
16 | |
17 | ARGUMENTS : IN: |
18 | p_customer_trx_line_id |
19 | p_last_period_to_cr |
20 | p_period_set_name |
21 | OUT: |
22 | |
23 | NOTES |
24 | |
25 | MODIFICATION HISTORY |
26 | 13-MAR-96 Martin Johnson Created |
27 | |
28 +===========================================================================*/
29
30 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
31
32 FUNCTION derive_last_date_to_cr(
33 p_customer_trx_line_id IN number,
34 p_last_period_to_cr IN number,
35 p_period_set_name IN varchar2 DEFAULT NULL) RETURN date IS
36
37 l_count number;
38 l_last_gl_date date;
39 l_period_set_name varchar2(15);
40
41 BEGIN
42
43 /*---------------------------------------------+
44 | If period set name was not passed, get it |
45 +---------------------------------------------*/
46
47 IF ( p_period_set_name IS NULL )
48 THEN
49 SELECT sb.period_set_name
50 INTO l_period_set_name
51 FROM ar_system_parameters sp,
52 gl_sets_of_books sb
53 WHERE sp.set_of_books_id = sb.set_of_books_id;
54 ELSE
55 l_period_set_name := p_period_set_name;
56 END IF;
57
58 SELECT MAX(gl_date),
59 count(*)
60 INTO l_last_gl_date,
61 l_count
62 FROM ar_revenue_assignments
63 WHERE customer_trx_line_id = p_customer_trx_line_id
64 AND account_class = 'REV'
65 AND period_set_name = l_period_set_name
66 AND rownum <= DECODE(p_last_period_to_cr -
67 round(p_last_period_to_cr, 0),
68 0, p_last_period_to_cr,
69 p_last_period_to_cr + 1 );
70
71 IF ( p_last_period_to_cr - trunc(p_last_period_to_cr, 0) <> 0 )
72 THEN
73 /*-------------------------------------------------------------------+
74 | Last Date = |
75 | (days in period * fractional part of last period to cr) + |
76 | last full period |
77 +-------------------------------------------------------------------*/
78
79 SELECT ( (l_last_gl_date - max(gl_date) ) *
80 (p_last_period_to_cr - trunc(p_last_period_to_cr, 0) )
81 ) + max(gl_date)
82 INTO l_last_gl_date
83 FROM ar_revenue_assignments
84 WHERE customer_trx_line_id = p_customer_trx_line_id
85 AND account_class = 'REV'
86 AND period_set_name = l_period_set_name
87 AND rownum <= l_count - 1;
88 END IF;
89
90 RETURN( l_last_gl_date );
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 RAISE;
95
96 END derive_last_date_to_cr;
97
98 /*===========================================================================+
99 | FUNCTION |
100 | derive_last_pd_to_cr |
101 | |
102 | DESCRIPTION |
103 | This function returns the Last Period to Credit as a real number |
104 | given the last credit date and customer_trx_line_id for an |
105 | invoice line with rules. |
106 | |
107 | SCOPE - PUBLIC |
108 | |
109 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
110 | arp_util.debug |
111 | |
112 | ARGUMENTS : IN: |
113 | p_customer_trx_line_id |
114 | p_last_date_to_credit |
115 | OUT: |
116 | |
117 | NOTES |
118 | |
119 | MODIFICATION HISTORY |
120 | 12-MAR-96 Martin Johnson Created |
121 | 21-MAR-96 Martin Johnson Added tokens to message |
122 | AR_TW_LAST_GL_DATE |
123 | |
124 +===========================================================================*/
125
126 FUNCTION derive_last_pd_to_cr( p_customer_trx_line_id IN number,
127 p_last_date_to_credit IN date ) RETURN number
128 IS
129
130 l_min_gl_date date;
131 l_max_gl_date date;
132
133 l_period_count number;
134 l_period_fraction number;
135 l_prior_date date;
136
137 CURSOR gl_dates IS
138 SELECT gl_date
139 FROM ra_customer_trx_lines l,
140 ar_revenue_assignments ra
141 WHERE l.customer_trx_line_id = p_customer_trx_line_id
142 AND l.customer_trx_line_id = ra.customer_trx_line_id
143 AND ra.account_class = 'REV'
144 ORDER BY gl_date;
145
146 BEGIN
147
148 IF PG_DEBUG in ('Y', 'C') THEN
149 arp_util.debug('arp_trx_line_util.derive_last_pd_to_cr()+');
150 END IF;
151
152 /*-------------------------------------------------+
153 | Validate that the Last GL Date is between the |
154 | first and last invoice GL Date |
155 +-------------------------------------------------*/
156
157 SELECT MIN(gl_date),
158 MAX(gl_date)
159 INTO l_min_gl_date,
160 l_max_gl_date
161 FROM ra_customer_trx_lines l,
162 ar_revenue_assignments ra
163 WHERE l.customer_trx_line_id = p_customer_trx_line_id
164 AND l.customer_trx_line_id = ra.customer_trx_line_id
165 AND ra.account_class = 'REV';
166
167 IF ( p_last_date_to_credit BETWEEN l_min_gl_date
168 AND l_max_gl_date )
169 THEN null;
170 ELSE
171 fnd_message.set_name('AR', 'AR_TW_LAST_GL_DATE');
172 --Int'l Calendar Project
173 fnd_message.set_token('MIN_DATE', fnd_date.date_to_chardate(l_min_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
174 fnd_message.set_token('MAX_DATE', fnd_date.date_to_chardate(l_max_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
175 app_exception.raise_exception;
176 END IF;
177
178 l_period_count := 0;
179
180 FOR gl_dates_rec IN gl_dates LOOP
181
182 l_period_count := l_period_count + 1;
183
184 /*-----------------------------------------------------+
185 | If p_last_date_to_credit <= fetched gl_date |
186 | THEN compute the last period to credit and return |
187 | ELSE set the prior date to the fetched date and |
188 | continue looping. |
189 +-----------------------------------------------------*/
190
191 IF ( p_last_date_to_credit <= gl_dates_rec.gl_date )
192 THEN
193
194 /*------------------------------------------------------------+
195 | If p_last_date_to_credit < fetched gl_date |
196 | THEN decrement the period count by one since we have |
197 | gone too far, and compute the last period to credit |
198 | including the fractional part |
199 | ELSE set the last period to credit to the current count |
200 +------------------------------------------------------------*/
201
202 IF ( p_last_date_to_credit < gl_dates_rec.gl_date )
203 THEN
204
205 l_period_count := l_period_count - 1;
206
207 /*----------------------------------------------+
208 | If the current date = the prior date |
209 | THEN set the period fraction to 0 |
210 | ELSE set the period fraction to |
211 | (days into period) / (period length) |
212 +----------------------------------------------*/
213
214 IF ( gl_dates_rec.gl_date = l_prior_date )
215 THEN
216 l_period_fraction := 0;
217
218 ELSE
219 l_period_fraction := ( p_last_date_to_credit - l_prior_date ) /
220 ( gl_dates_rec.gl_date - l_prior_date );
221 END IF;
222
223 IF PG_DEBUG in ('Y', 'C') THEN
224 arp_util.debug('arp_trx_line_util.derive_last_pd_to_cr()-');
225 END IF;
226 RETURN( l_period_count + l_period_fraction );
227
228 ELSE /* p_last_date_to_credit = gl_dates_rec.gl_date */
229
230 IF PG_DEBUG in ('Y', 'C') THEN
231 arp_util.debug('arp_trx_line_util.derive_last_pd_to_cr()-');
232 END IF;
233 RETURN( l_period_count );
234
235 END IF; /* IF ( p_last_date_to_credit < gl_dates_rec.gl_date ) */
236
237 ELSE /* p_last_date_to_credit > gl_dates_rec.gl_date */
238
239 l_prior_date := gl_dates_rec.gl_date;
240
241 END IF; /* IF ( p_last_date_to_credit <= gl_dates_rec.gl_date ) */
242
243 END LOOP;
244
245 EXCEPTION
246 WHEN OTHERS THEN
247 IF PG_DEBUG in ('Y', 'C') THEN
248 arp_util.debug('EXCEPTION: arp_trx_line_util.derive_last_pd_to_cr()');
249 arp_util.debug('derive_last_pd_to_cr: ' || '----- Parameters for ' ||
250 'arp_trx_line_util.derive_last_pd_to_cr() ' || '-----' );
251 arp_util.debug('derive_last_pd_to_cr: ' || 'p_customer_trx_line_id = ' || p_customer_trx_line_id );
252 arp_util.debug('derive_last_pd_to_cr: ' || 'p_last_date_to_credit = ' || p_last_date_to_credit );
253 END IF;
254 RAISE;
255
256 END derive_last_pd_to_cr;
257
258 /*===========================================================================+
259 | PROCEDURE |
260 | get_default_line_num |
261 | |
262 | DESCRIPTION |
263 | Returns the default line number. |
264 | |
265 | SCOPE - PUBLIC |
266 | |
267 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
268 | arp_util.debug |
269 | |
270 | ARGUMENTS : IN: |
271 | p_customer_trx_id |
272 | OUT: |
273 | p_line_number |
274 | |
275 | NOTES |
276 | |
277 | MODIFICATION HISTORY |
278 | 14-DEC-95 Martin Johnson Created |
279 | |
280 +===========================================================================*/
281
282 PROCEDURE get_default_line_num(p_customer_trx_id IN number,
283 p_line_number OUT NOCOPY number )
284 IS
285
286 BEGIN
287
288 IF PG_DEBUG in ('Y', 'C') THEN
289 arp_util.debug('arp_trx_line_util.get_default_line_num()+');
290 END IF;
291
292 SELECT nvl( max(line_number), 0 ) + 1
293 INTO p_line_number
294 FROM ra_customer_trx_lines
295 WHERE customer_trx_id = p_customer_trx_id
296 AND line_type in ('LINE', 'CB', 'CHARGES');
297
298 IF PG_DEBUG in ('Y', 'C') THEN
299 arp_util.debug('arp_trx_line_util.get_default_line_num()-');
300 END IF;
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 IF PG_DEBUG in ('Y', 'C') THEN
305 arp_util.debug('EXCEPTION: arp_trx_line_util.get_default_line_num()');
306 arp_util.debug('get_default_line_num: ' ||
307 '---------- ' ||
308 'Parameters for arp_trx_line_util.get_default_line_num() ' ||
309 '---------- ');
310 arp_util.debug('get_default_line_num: ' || 'p_customer_trx_id = ' || p_customer_trx_id);
311 END IF;
312
313 RAISE;
314
315 END get_default_line_num;
316
317 /*===========================================================================+
318 | PROCEDURE |
319 | get_item_flex_defaults |
320 | |
321 | DESCRIPTION |
322 | Returns the defaults for the specified item flex |
323 | |
324 | SCOPE - PUBLIC |
325 | |
326 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
327 | arp_util.debug |
328 | |
329 | ARGUMENTS : IN: |
330 | p_inventory_item_id |
331 | p_organization_id |
332 | p_trx_date |
333 | p_invoicing_rule_id |
334 | OUT: |
335 | p_description |
336 | p_primary_uom_code |
337 | p_primary_uom_name |
338 | p_accounting_rule_id |
339 | p_accounting_rule_name |
340 | p_accounting_rule_duration |
341 | p_accounting_rule_type |
342 | p_rule_start_date |
343 | p_frequency |
344 | |
345 | NOTES |
346 | |
347 | MODIFICATION HISTORY |
348 | 19-DEC-95 Martin Johnson Created |
349 | 05-FEB-96 Martin Johnson Added parameter p_frequency |
350 | 18-MAR-96 Martin Johnson Validate uom against mtl_item_uoms_view|
351 | |
352 +===========================================================================*/
353
354 PROCEDURE get_item_flex_defaults(p_inventory_item_id IN Number,
355 p_organization_id IN Number,
356 p_trx_date IN Date,
357 p_invoicing_rule_id IN Number,
358 p_description OUT NOCOPY varchar2,
359 p_primary_uom_code OUT NOCOPY varchar2,
360 p_primary_uom_name OUT NOCOPY varchar2,
361 p_accounting_rule_id OUT NOCOPY Number,
362 p_accounting_rule_name OUT NOCOPY Varchar2,
363 p_accounting_rule_duration OUT NOCOPY number,
364 p_accounting_rule_type OUT NOCOPY varchar2,
365 p_rule_start_date OUT NOCOPY
366 date,
367 p_frequency OUT NOCOPY varchar2
368 )
369 IS
370
371 BEGIN
372
373 IF PG_DEBUG in ('Y', 'C') THEN
374 arp_util.debug('arp_trx_line_util.get_item_flex_defaults()+');
375 END IF;
376
377 SELECT msi.description,
378 muom.uom_code,
379 muom.unit_of_measure_tl, /*4762000*/
380 DECODE( rr.status,
381 'A', msi.accounting_rule_id,
382 null ),
383 DECODE( rr.status,
384 'A', rr.name,
385 null ),
386 DECODE( rr.status,
387 'A', DECODE(rr.type,
388 'ACC_DUR', 1,
389 'A', rr.occurrences )
390 ),
391 DECODE( rr.status,
392 'A', rr.type,
393 null ),
394 DECODE( rr.status,
395 'A', DECODE( rr.frequency,
396 'SPECIFIC', min(rs.rule_date),
397 DECODE( p_invoicing_rule_id,
398 -2, p_trx_date,
399 -3, sysdate )
400 ),
401 null
402 ),
403 DECODE( rr.status,
404 'A', rr.frequency,
405 null )
406 INTO p_description,
407 p_primary_uom_code,
408 p_primary_uom_name,
409 p_accounting_rule_id,
410 p_accounting_rule_name,
411 p_accounting_rule_duration,
412 p_accounting_rule_type,
413 p_rule_start_date,
414 p_frequency
415 FROM mtl_system_items msi,
416 mtl_item_uoms_view muom,
417 ra_rules rr,
418 ra_rule_schedules rs
419 WHERE msi.inventory_item_id = p_inventory_item_id
420 AND msi.organization_id = p_organization_id
421 AND msi.primary_uom_code = muom.uom_code (+)
422 AND muom.inventory_item_id(+) = p_inventory_item_id
423 AND muom.organization_id(+) = p_organization_id
424 AND msi.accounting_rule_id = rr.rule_id (+)
425 AND rr.rule_id = rs.rule_id (+)
426 GROUP BY msi.description,
427 muom.uom_code,
428 muom.unit_of_measure_tl, /*4762000*/
429 rr.status,
430 msi.accounting_rule_id,
431 rr.name,
432 rr.type,
433 rr.occurrences,
434 rr.frequency;
435
436 IF PG_DEBUG in ('Y', 'C') THEN
437 arp_util.debug('arp_trx_line_util.get_item_flex_defaults()-');
438 END IF;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 IF PG_DEBUG in ('Y', 'C') THEN
443 arp_util.debug('EXCEPTION: arp_trx_line_util.get_item_flex_defaults()');
444 arp_util.debug('get_item_flex_defaults: ' ||
445 '---------- ' ||
446 'Parameters for arp_trx_line_util.get_item_flex_defaults() ' ||
447 '---------- ');
448 arp_util.debug('get_item_flex_defaults: ' || 'p_inventory_item_id = ' || p_inventory_item_id);
449 arp_util.debug('get_item_flex_defaults: ' || 'p_organization_id = ' || p_organization_id );
450 arp_util.debug('get_item_flex_defaults: ' || 'p_trx_date = ' || p_trx_date );
451 arp_util.debug('get_item_flex_defaults: ' || 'p_invoicing_rule_id = ' || p_invoicing_rule_id );
452 END IF;
453
454 RAISE;
455
456 END get_item_flex_defaults;
457
458 PROCEDURE get_max_line_number(p_customer_trx_id IN number, p_line_number OUT NOCOPY NUMBER ) IS
459 l_line_number NUMBER;
460
461 BEGIN
462 IF PG_DEBUG in ('Y', 'C') THEN
463 arp_util.debug('arp_trx_util_line.get_max_line_number(+)');
464 END IF;
465
466 ---
467 --- Get Maximum Line Number
468 ---
469 SELECT
470 nvl(max(line_number) , 0)
471 INTO
472 l_line_number
473 FROM ra_customer_trx_lines
474 WHERE customer_trx_id = p_customer_trx_id
475 AND line_type IN ('LINE','CB','CHARGES');
476 p_line_number := l_line_number+1;
477 IF PG_DEBUG in ('Y', 'C') THEN
478 arp_util.debug('arp_trx_util_line.get_max_line_number(-)');
479 END IF;
480
481 EXCEPTION
482 WHEN OTHERS
483 THEN
484 IF PG_DEBUG in ('Y', 'C') THEN
485 arp_util.debug('EXCEPTION:arp_trx_util_line.get_max_line_number');
486 END IF;
487 RAISE;
488 End get_max_line_number;
489
490 /*===========================================================================+
491 | PROCEDURE |
492 | get_oe_header_id |
493 | |
494 | DESCRIPTION |
495 | Returns the oe_header_id for the given oe_line_id |
496 | |
497 | SCOPE - PUBLIC |
498 | |
499 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
500 | arp_util.debug |
501 | |
502 | ARGUMENTS : IN: |
503 | p_oe_line_id |
504 | p_interface_context |
505 | OUT: |
506 | |
507 | NOTES |
508 | |
509 | MODIFICATION HISTORY |
510 | 23-MAY-02 Ramakant Alat Created |
511 | 05-DEC-02 M Raymond Bug 2676869 Added logic to the exception
512 | handler to mask invalid number
513 | errors. These occur if the
514 | line transaction flexfield
515 | has non-numeric data in specific
516 | segments. |
517 | 05-DEC-02 Also added support for
518 | ORDER MANAGEMENT context.
519 +===========================================================================*/
520 FUNCTION get_oe_header_id(p_oe_line_id IN varchar2, p_interface_context IN VARCHAR2 ) RETURN NUMBER IS
521
522 l_oe_header_id NUMBER := null;
523
524 BEGIN
525 IF PG_DEBUG in ('Y', 'C') THEN
526 arp_util.debug('arp_trx_util_line.get_oe_header_id(+)');
527 END IF;
528
529 ---
530 --- Get OE Header Id
531 ---
532 IF p_interface_context in ('ORDER ENTRY','ORDER MANAGEMENT') THEN
533 SELECT
534 header_id
535 INTO
536 l_oe_header_id
537 FROM oe_order_lines
538 WHERE line_id = to_number(p_oe_line_id)
539 and rownum < 2;
540 END IF;
541
542 IF PG_DEBUG in ('Y', 'C') THEN
543 arp_util.debug('arp_trx_util_line.get_oe_header_id(-)');
544 END IF;
545
546 RETURN l_oe_header_id;
547
548 EXCEPTION
549 WHEN NO_DATA_FOUND
550 THEN
551 l_oe_header_id := NULL;
552 RETURN l_oe_header_id;
553 WHEN INVALID_NUMBER
554 THEN
555 l_oe_header_id := NULL;
556 RETURN l_oe_header_id;
557 WHEN OTHERS
558 THEN
559 IF PG_DEBUG in ('Y', 'C') THEN
560 arp_util.debug('EXCEPTION:arp_trx_util_line.get_oe_header_id ');
561 arp_util.debug('get_oe_header_id: ' || SQLERRM(SQLCODE));
562 END IF;
563 RAISE;
564 End get_oe_header_id;
565
566 /*===========================================================================+
567 | FUNCTION |
568 | get_tax_classification_code |
569 | |
570 | DESCRIPTION |
571 | When transactions are upgraded from 11i to R12, |
572 | tax classification code is not populated even though |
573 | vat_tax_id is NOT NULL. In that case this function returns |
574 | the Tax Classification Code as varchar2 |
575 | given the vat_tax_id number for an Invoice Line. |
576 | |
577 | SCOPE - PUBLIC |
578 | |
579 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
580 | |
581 | ARGUMENTS : IN: |
582 | p_vat_tax_id |
583 | OUT: |
584 | |
585 | NOTES |
586 | |
587 | MODIFICATION HISTORY |
588 | 13-MAR-07 Nanda Kishore Created |
589 | |
590 +===========================================================================*/
591 FUNCTION get_tax_classification_code(p_vat_tax_id IN Number) RETURN VARCHAR2
592 IS
593 l_tax_classification_code VARCHAR2(50);
594 BEGIN
595 SELECT
596 zx.tax_classification_code
597 INTO
598 l_tax_classification_code
599 FROM
600 zx_id_tcc_mapping zx
601 WHERE
602 zx.source = 'AR' and
603 zx.tax_rate_code_id = p_vat_tax_id;
604
605 Return l_tax_classification_code;
606 EXCEPTION
607 WHEN OTHERS THEN
608 Return NULL;
609 End get_tax_classification_code;
610
611 /*===========================================================================+
612 | FUNCTION |
613 | get_tax_amount |
614 | |
615 | DESCRIPTION |
616 | AR_DOCS_RECEIVABLES_V view is modifed to show tax amount. |
617 | The TAX amount is calculated in this function depending upon |
618 | the value of tax_type passed [ VAT or SALES_TAX ] |
619 | |
620 | SCOPE - PUBLIC |
621 | |
622 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
623 | |
624 | ARGUMENTS : IN: |
625 | p_customer_trx_id |
626 | p_tax_type |
627 | OUT: |
628 | |
629 | NOTES |
630 | |
631 | MODIFICATION HISTORY |
632 | 20-MAY-08 Sachin Dixit Created |
633 | |
634 +===========================================================================*/
635 FUNCTION get_tax_amount( p_customer_trx_id IN NUMBER,
636 p_tax_type IN VARCHAR2) RETURN NUMBER
637 IS
638 tax_amount NUMBER ;
639 BEGIN
640 IF p_tax_type = 'VAT'
641 THEN
642 -- Bug 9955306
643 SELECT SUM(zl.tax_amt)
644 INTO tax_amount
645 FROM zx_lines zl
646 WHERE zl.tax_type_code LIKE 'VAT%'
647 AND zl.application_id = 222
648 AND zl.entity_code = 'TRANSACTIONS'
649 AND zl.trx_id = p_customer_trx_id;
650
651 ELSE
652 -- Bug 9955306
653 SELECT SUM(zl.tax_amt)
654 INTO tax_amount
655 FROM zx_lines zl
656 WHERE (zl.tax_type_code is null OR zl.tax_type_code not like 'VAT%')
657 AND zl.application_id = 222
658 AND zl.entity_code = 'TRANSACTIONS'
659 AND zl.trx_id = p_customer_trx_id;
660
661 END IF;
662
663 RETURN tax_amount;
664 EXCEPTION
665 WHEN OTHERS THEN
666 RETURN NULL;
667 END;
668
669 /*===========================================================================+
670 | FUNCTION |
671 | get_tax_amount |
672 | |
673 | DESCRIPTION |
674 | AR_DOCUMENT_LINES_V view is modifed to show tax amount. |
675 | The TAX amount is calculated in this function depending upon |
676 | the value of tax_type passed [ VAT or SALES_TAX ] |
677 | |
678 | SCOPE - PUBLIC |
679 | |
680 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
681 | |
682 | ARGUMENTS : IN: |
683 | p_customer_trx_id |
684 | p_customer_trx_line_id |
685 | p_tax_type |
686 | OUT: |
687 | |
688 | NOTES |
689 | |
690 | MODIFICATION HISTORY |
691 | 04-NOV-08 Nanda Emani Created |
692 | |
693 +===========================================================================*/
694 FUNCTION get_tax_amount( p_customer_trx_id IN NUMBER,
695 p_customer_trx_line_id IN NUMBER,
696 p_tax_type IN VARCHAR2) RETURN NUMBER
697 IS
698 tax_amount NUMBER ;
699 BEGIN
700 IF p_tax_type = 'VAT'
701 THEN
702 SELECT sum(zl.tax_amt) INTO tax_amount
703 FROM ra_customer_trx_lines trxl, zx_lines zl
704 WHERE trxl.line_type = 'TAX'
705 AND trxl.tax_line_id = zl.tax_line_id
706 AND zl.tax_type_code like 'VAT%'
707 AND zl.application_id = 222
708 AND zl.entity_code = 'TRANSACTIONS'
709 AND trxl.customer_trx_id = p_customer_trx_id
710 AND trxl.link_to_cust_trx_line_id = p_customer_trx_line_id;
711 ELSE
712 SELECT sum(zl.tax_amt) INTO tax_amount
713 FROM ra_customer_trx_lines trxl, zx_lines zl
714 WHERE trxl.line_type = 'TAX'
715 AND trxl.tax_line_id = zl.tax_line_id
716 AND (zl.tax_type_code is null OR zl.tax_type_code not like 'VAT%')
717 AND zl.application_id = 222
718 AND zl.entity_code = 'TRANSACTIONS'
719 AND trxl.customer_trx_id = p_customer_trx_id
720 AND trxl.link_to_cust_trx_line_id = p_customer_trx_line_id;
721 END IF;
722
723 RETURN tax_amount;
724 EXCEPTION
725 WHEN OTHERS THEN
726 RETURN NULL;
727 END;
728
729 END ARP_TRX_LINE_UTIL;