[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_LINE_UTIL
Source
1 PACKAGE BODY ARP_TRX_LINE_UTIL AS
2 /* $Header: ARTCTLTB.pls 120.12.12010000.3 2008/11/21 09:37:10 npanchak 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 fnd_message.set_token('MIN_DATE', TO_CHAR(l_min_gl_date, 'DD-MON-YYYY'));
173 fnd_message.set_token('MAX_DATE', TO_CHAR(l_max_gl_date, 'DD-MON-YYYY'));
174 app_exception.raise_exception;
175 END IF;
176
177 l_period_count := 0;
178
179 FOR gl_dates_rec IN gl_dates LOOP
180
181 l_period_count := l_period_count + 1;
182
183 /*-----------------------------------------------------+
184 | If p_last_date_to_credit <= fetched gl_date |
185 | THEN compute the last period to credit and return |
186 | ELSE set the prior date to the fetched date and |
187 | continue looping. |
188 +-----------------------------------------------------*/
189
190 IF ( p_last_date_to_credit <= gl_dates_rec.gl_date )
191 THEN
192
193 /*------------------------------------------------------------+
194 | If p_last_date_to_credit < fetched gl_date |
195 | THEN decrement the period count by one since we have |
196 | gone too far, and compute the last period to credit |
197 | including the fractional part |
198 | ELSE set the last period to credit to the current count |
199 +------------------------------------------------------------*/
200
201 IF ( p_last_date_to_credit < gl_dates_rec.gl_date )
202 THEN
203
204 l_period_count := l_period_count - 1;
205
206 /*----------------------------------------------+
207 | If the current date = the prior date |
208 | THEN set the period fraction to 0 |
209 | ELSE set the period fraction to |
210 | (days into period) / (period length) |
211 +----------------------------------------------*/
212
213 IF ( gl_dates_rec.gl_date = l_prior_date )
214 THEN
215 l_period_fraction := 0;
216
217 ELSE
218 l_period_fraction := ( p_last_date_to_credit - l_prior_date ) /
219 ( gl_dates_rec.gl_date - l_prior_date );
220 END IF;
221
222 IF PG_DEBUG in ('Y', 'C') THEN
223 arp_util.debug('arp_trx_line_util.derive_last_pd_to_cr()-');
224 END IF;
225 RETURN( l_period_count + l_period_fraction );
226
227 ELSE /* p_last_date_to_credit = gl_dates_rec.gl_date */
228
229 IF PG_DEBUG in ('Y', 'C') THEN
230 arp_util.debug('arp_trx_line_util.derive_last_pd_to_cr()-');
231 END IF;
232 RETURN( l_period_count );
233
234 END IF; /* IF ( p_last_date_to_credit < gl_dates_rec.gl_date ) */
235
236 ELSE /* p_last_date_to_credit > gl_dates_rec.gl_date */
237
238 l_prior_date := gl_dates_rec.gl_date;
239
240 END IF; /* IF ( p_last_date_to_credit <= gl_dates_rec.gl_date ) */
241
242 END LOOP;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 IF PG_DEBUG in ('Y', 'C') THEN
247 arp_util.debug('EXCEPTION: arp_trx_line_util.derive_last_pd_to_cr()');
248 arp_util.debug('derive_last_pd_to_cr: ' || '----- Parameters for ' ||
249 'arp_trx_line_util.derive_last_pd_to_cr() ' || '-----' );
250 arp_util.debug('derive_last_pd_to_cr: ' || 'p_customer_trx_line_id = ' || p_customer_trx_line_id );
251 arp_util.debug('derive_last_pd_to_cr: ' || 'p_last_date_to_credit = ' || p_last_date_to_credit );
252 END IF;
253 RAISE;
254
255 END derive_last_pd_to_cr;
256
257 /*===========================================================================+
258 | PROCEDURE |
259 | get_default_line_num |
260 | |
261 | DESCRIPTION |
262 | Returns the default line number. |
263 | |
264 | SCOPE - PUBLIC |
265 | |
266 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
267 | arp_util.debug |
268 | |
269 | ARGUMENTS : IN: |
270 | p_customer_trx_id |
271 | OUT: |
272 | p_line_number |
273 | |
274 | NOTES |
275 | |
276 | MODIFICATION HISTORY |
277 | 14-DEC-95 Martin Johnson Created |
278 | |
279 +===========================================================================*/
280
281 PROCEDURE get_default_line_num(p_customer_trx_id IN number,
282 p_line_number OUT NOCOPY number )
283 IS
284
285 BEGIN
286
287 IF PG_DEBUG in ('Y', 'C') THEN
288 arp_util.debug('arp_trx_line_util.get_default_line_num()+');
289 END IF;
290
291 SELECT nvl( max(line_number), 0 ) + 1
292 INTO p_line_number
293 FROM ra_customer_trx_lines
294 WHERE customer_trx_id = p_customer_trx_id
295 AND line_type in ('LINE', 'CB', 'CHARGES');
296
297 IF PG_DEBUG in ('Y', 'C') THEN
298 arp_util.debug('arp_trx_line_util.get_default_line_num()-');
299 END IF;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 IF PG_DEBUG in ('Y', 'C') THEN
304 arp_util.debug('EXCEPTION: arp_trx_line_util.get_default_line_num()');
305 arp_util.debug('get_default_line_num: ' ||
306 '---------- ' ||
307 'Parameters for arp_trx_line_util.get_default_line_num() ' ||
308 '---------- ');
309 arp_util.debug('get_default_line_num: ' || 'p_customer_trx_id = ' || p_customer_trx_id);
310 END IF;
311
312 RAISE;
313
314 END get_default_line_num;
315
316 /*===========================================================================+
317 | PROCEDURE |
318 | get_item_flex_defaults |
319 | |
320 | DESCRIPTION |
321 | Returns the defaults for the specified item flex |
322 | |
323 | SCOPE - PUBLIC |
324 | |
325 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
326 | arp_util.debug |
327 | |
328 | ARGUMENTS : IN: |
329 | p_inventory_item_id |
330 | p_organization_id |
331 | p_trx_date |
332 | p_invoicing_rule_id |
333 | OUT: |
334 | p_description |
335 | p_primary_uom_code |
336 | p_primary_uom_name |
337 | p_accounting_rule_id |
338 | p_accounting_rule_name |
339 | p_accounting_rule_duration |
340 | p_accounting_rule_type |
341 | p_rule_start_date |
342 | p_frequency |
343 | |
344 | NOTES |
345 | |
346 | MODIFICATION HISTORY |
347 | 19-DEC-95 Martin Johnson Created |
348 | 05-FEB-96 Martin Johnson Added parameter p_frequency |
349 | 18-MAR-96 Martin Johnson Validate uom against mtl_item_uoms_view|
350 | |
351 +===========================================================================*/
352
353 PROCEDURE get_item_flex_defaults(p_inventory_item_id IN Number,
354 p_organization_id IN Number,
355 p_trx_date IN Date,
356 p_invoicing_rule_id IN Number,
357 p_description OUT NOCOPY varchar2,
358 p_primary_uom_code OUT NOCOPY varchar2,
359 p_primary_uom_name OUT NOCOPY varchar2,
360 p_accounting_rule_id OUT NOCOPY Number,
361 p_accounting_rule_name OUT NOCOPY Varchar2,
362 p_accounting_rule_duration OUT NOCOPY number,
363 p_accounting_rule_type OUT NOCOPY varchar2,
364 p_rule_start_date OUT NOCOPY
365 date,
366 p_frequency OUT NOCOPY varchar2
367 )
368 IS
369
370 BEGIN
371
372 IF PG_DEBUG in ('Y', 'C') THEN
373 arp_util.debug('arp_trx_line_util.get_item_flex_defaults()+');
374 END IF;
375
376 SELECT msi.description,
377 muom.uom_code,
378 muom.unit_of_measure_tl, /*4762000*/
379 DECODE( rr.status,
380 'A', msi.accounting_rule_id,
381 null ),
382 DECODE( rr.status,
383 'A', rr.name,
384 null ),
385 DECODE( rr.status,
386 'A', DECODE(rr.type,
387 'ACC_DUR', 1,
388 'A', rr.occurrences )
389 ),
390 DECODE( rr.status,
391 'A', rr.type,
392 null ),
393 DECODE( rr.status,
394 'A', DECODE( rr.frequency,
395 'SPECIFIC', min(rs.rule_date),
396 DECODE( p_invoicing_rule_id,
397 -2, p_trx_date,
398 -3, sysdate )
399 ),
400 null
401 ),
402 DECODE( rr.status,
403 'A', rr.frequency,
404 null )
405 INTO p_description,
406 p_primary_uom_code,
407 p_primary_uom_name,
408 p_accounting_rule_id,
409 p_accounting_rule_name,
410 p_accounting_rule_duration,
411 p_accounting_rule_type,
412 p_rule_start_date,
413 p_frequency
414 FROM mtl_system_items msi,
415 mtl_item_uoms_view muom,
416 ra_rules rr,
417 ra_rule_schedules rs
418 WHERE msi.inventory_item_id = p_inventory_item_id
419 AND msi.organization_id = p_organization_id
420 AND msi.primary_uom_code = muom.uom_code (+)
421 AND muom.inventory_item_id(+) = p_inventory_item_id
422 AND muom.organization_id(+) = p_organization_id
423 AND msi.accounting_rule_id = rr.rule_id (+)
424 AND rr.rule_id = rs.rule_id (+)
425 GROUP BY msi.description,
426 muom.uom_code,
427 muom.unit_of_measure_tl, /*4762000*/
428 rr.status,
429 msi.accounting_rule_id,
430 rr.name,
431 rr.type,
432 rr.occurrences,
433 rr.frequency;
434
435 IF PG_DEBUG in ('Y', 'C') THEN
436 arp_util.debug('arp_trx_line_util.get_item_flex_defaults()-');
437 END IF;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 IF PG_DEBUG in ('Y', 'C') THEN
442 arp_util.debug('EXCEPTION: arp_trx_line_util.get_item_flex_defaults()');
443 arp_util.debug('get_item_flex_defaults: ' ||
444 '---------- ' ||
445 'Parameters for arp_trx_line_util.get_item_flex_defaults() ' ||
446 '---------- ');
447 arp_util.debug('get_item_flex_defaults: ' || 'p_inventory_item_id = ' || p_inventory_item_id);
448 arp_util.debug('get_item_flex_defaults: ' || 'p_organization_id = ' || p_organization_id );
449 arp_util.debug('get_item_flex_defaults: ' || 'p_trx_date = ' || p_trx_date );
450 arp_util.debug('get_item_flex_defaults: ' || 'p_invoicing_rule_id = ' || p_invoicing_rule_id );
451 END IF;
452
453 RAISE;
454
455 END get_item_flex_defaults;
456
457 PROCEDURE get_max_line_number(p_customer_trx_id IN number, p_line_number OUT NOCOPY NUMBER ) IS
458 l_line_number NUMBER;
459
460 BEGIN
461 IF PG_DEBUG in ('Y', 'C') THEN
462 arp_util.debug('arp_trx_util_line.get_max_line_number(+)');
463 END IF;
464
465 ---
466 --- Get Maximum Line Number
467 ---
468 SELECT
469 nvl(max(line_number) , 0)
470 INTO
471 l_line_number
472 FROM ra_customer_trx_lines
473 WHERE customer_trx_id = p_customer_trx_id
474 AND line_type IN ('LINE','CB','CHARGES');
475 p_line_number := l_line_number+1;
476 IF PG_DEBUG in ('Y', 'C') THEN
477 arp_util.debug('arp_trx_util_line.get_max_line_number(-)');
478 END IF;
479
480 EXCEPTION
481 WHEN OTHERS
482 THEN
483 IF PG_DEBUG in ('Y', 'C') THEN
484 arp_util.debug('EXCEPTION:arp_trx_util_line.get_max_line_number');
485 END IF;
486 RAISE;
487 End get_max_line_number;
488
489 /*===========================================================================+
490 | PROCEDURE |
491 | get_oe_header_id |
492 | |
493 | DESCRIPTION |
494 | Returns the oe_header_id for the given oe_line_id |
495 | |
496 | SCOPE - PUBLIC |
497 | |
498 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
499 | arp_util.debug |
500 | |
501 | ARGUMENTS : IN: |
502 | p_oe_line_id |
503 | p_interface_context |
504 | OUT: |
505 | |
506 | NOTES |
507 | |
508 | MODIFICATION HISTORY |
509 | 23-MAY-02 Ramakant Alat Created |
510 | 05-DEC-02 M Raymond Bug 2676869 Added logic to the exception
511 | handler to mask invalid number
512 | errors. These occur if the
513 | line transaction flexfield
514 | has non-numeric data in specific
515 | segments. |
516 | 05-DEC-02 Also added support for
517 | ORDER MANAGEMENT context.
518 +===========================================================================*/
519 FUNCTION get_oe_header_id(p_oe_line_id IN varchar2, p_interface_context IN VARCHAR2 ) RETURN NUMBER IS
520
521 l_oe_header_id NUMBER := null;
522
523 BEGIN
524 IF PG_DEBUG in ('Y', 'C') THEN
525 arp_util.debug('arp_trx_util_line.get_oe_header_id(+)');
526 END IF;
527
528 ---
529 --- Get OE Header Id
530 ---
531 IF p_interface_context in ('ORDER ENTRY','ORDER MANAGEMENT') THEN
532 SELECT
533 header_id
534 INTO
535 l_oe_header_id
536 FROM oe_order_lines
537 WHERE line_id = to_number(p_oe_line_id)
538 and rownum < 2;
539 END IF;
540
541 IF PG_DEBUG in ('Y', 'C') THEN
542 arp_util.debug('arp_trx_util_line.get_oe_header_id(-)');
543 END IF;
544
545 RETURN l_oe_header_id;
546
547 EXCEPTION
548 WHEN NO_DATA_FOUND
549 THEN
550 l_oe_header_id := NULL;
551 RETURN l_oe_header_id;
552 WHEN INVALID_NUMBER
553 THEN
554 l_oe_header_id := NULL;
555 RETURN l_oe_header_id;
556 WHEN OTHERS
557 THEN
558 IF PG_DEBUG in ('Y', 'C') THEN
559 arp_util.debug('EXCEPTION:arp_trx_util_line.get_oe_header_id ');
560 arp_util.debug('get_oe_header_id: ' || SQLERRM(SQLCODE));
561 END IF;
562 RAISE;
563 End get_oe_header_id;
564
565 /*===========================================================================+
566 | FUNCTION |
567 | get_tax_classification_code |
568 | |
569 | DESCRIPTION |
570 | When transactions are upgraded from 11i to R12, |
571 | tax classification code is not populated even though |
572 | vat_tax_id is NOT NULL. In that case this function returns |
573 | the Tax Classification Code as varchar2 |
574 | given the vat_tax_id number for an Invoice Line. |
575 | |
576 | SCOPE - PUBLIC |
577 | |
578 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
579 | |
580 | ARGUMENTS : IN: |
581 | p_vat_tax_id |
582 | OUT: |
583 | |
584 | NOTES |
585 | |
586 | MODIFICATION HISTORY |
587 | 13-MAR-07 Nanda Kishore Created |
588 | |
589 +===========================================================================*/
590 FUNCTION get_tax_classification_code(p_vat_tax_id IN Number) RETURN VARCHAR2
591 IS
592 l_tax_classification_code VARCHAR2(50);
593 BEGIN
594 SELECT
595 zx.tax_classification_code
596 INTO
597 l_tax_classification_code
598 FROM
599 zx_id_tcc_mapping zx
600 WHERE
601 zx.source = 'AR' and
602 zx.tax_rate_code_id = p_vat_tax_id;
603
604 Return l_tax_classification_code;
605 EXCEPTION
606 WHEN OTHERS THEN
607 Return NULL;
608 End get_tax_classification_code;
609
610 /*===========================================================================+
611 | FUNCTION |
612 | get_tax_amount |
613 | |
614 | DESCRIPTION |
615 | AR_DOCS_RECEIVABLES_V view is modifed to show tax amount. |
616 | The TAX amount is calculated in this function depending upon |
617 | the value of tax_type passed [ VAT or SALES_TAX ] |
618 | |
619 | SCOPE - PUBLIC |
620 | |
621 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
622 | |
623 | ARGUMENTS : IN: |
624 | p_customer_trx_id |
625 | p_tax_type |
626 | OUT: |
627 | |
628 | NOTES |
629 | |
630 | MODIFICATION HISTORY |
631 | 20-MAY-08 Sachin Dixit Created |
632 | |
633 +===========================================================================*/
634 FUNCTION get_tax_amount( p_customer_trx_id IN NUMBER,
635 p_tax_type IN VARCHAR2) RETURN NUMBER
636 IS
637 tax_amount NUMBER ;
638 BEGIN
639 IF p_tax_type = 'VAT'
640 THEN
641 SELECT sum(zl.tax_amt) INTO tax_amount
642 FROM ra_customer_trx_lines_all trxl, zx_lines zl
643 WHERE trxl.line_type = 'TAX'
644 AND trxl.tax_line_id = zl.tax_line_id
645 AND zl.tax_type_code like 'VAT%'
646 AND zl.application_id = 222
647 AND zl.entity_code = 'TRANSACTIONS'
648 AND trxl.customer_trx_id = p_customer_trx_id;
649 ELSE
650 SELECT sum(zl.tax_amt) INTO tax_amount
651 FROM ra_customer_trx_lines_all trxl, zx_lines zl
652 WHERE trxl.line_type = 'TAX'
653 AND trxl.tax_line_id = zl.tax_line_id
654 AND (zl.tax_type_code is null OR zl.tax_type_code not like 'VAT%')
655 AND zl.application_id = 222
656 AND zl.entity_code = 'TRANSACTIONS'
657 AND trxl.customer_trx_id = p_customer_trx_id;
658 END IF;
659
660 RETURN tax_amount;
661 EXCEPTION
662 WHEN OTHERS THEN
663 RETURN NULL;
664 END;
665
666 /*===========================================================================+
667 | FUNCTION |
668 | get_tax_amount |
669 | |
670 | DESCRIPTION |
671 | AR_DOCUMENT_LINES_V view is modifed to show tax amount. |
672 | The TAX amount is calculated in this function depending upon |
673 | the value of tax_type passed [ VAT or SALES_TAX ] |
674 | |
675 | SCOPE - PUBLIC |
676 | |
677 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
678 | |
679 | ARGUMENTS : IN: |
680 | p_customer_trx_id |
681 | p_customer_trx_line_id |
682 | p_tax_type |
683 | OUT: |
684 | |
685 | NOTES |
686 | |
687 | MODIFICATION HISTORY |
688 | 04-NOV-08 Nanda Emani Created |
689 | |
690 +===========================================================================*/
691 FUNCTION get_tax_amount( p_customer_trx_id IN NUMBER,
692 p_customer_trx_line_id IN NUMBER,
693 p_tax_type IN VARCHAR2) RETURN NUMBER
694 IS
695 tax_amount NUMBER ;
696 BEGIN
697 IF p_tax_type = 'VAT'
698 THEN
699 SELECT sum(zl.tax_amt) INTO tax_amount
700 FROM ra_customer_trx_lines trxl, zx_lines zl
701 WHERE trxl.line_type = 'TAX'
702 AND trxl.tax_line_id = zl.tax_line_id
703 AND zl.tax_type_code like 'VAT%'
704 AND zl.application_id = 222
705 AND zl.entity_code = 'TRANSACTIONS'
706 AND trxl.customer_trx_id = p_customer_trx_id
707 AND trxl.link_to_cust_trx_line_id = p_customer_trx_line_id;
708 ELSE
709 SELECT sum(zl.tax_amt) INTO tax_amount
710 FROM ra_customer_trx_lines trxl, zx_lines zl
711 WHERE trxl.line_type = 'TAX'
712 AND trxl.tax_line_id = zl.tax_line_id
713 AND (zl.tax_type_code is null OR zl.tax_type_code not like 'VAT%')
714 AND zl.application_id = 222
715 AND zl.entity_code = 'TRANSACTIONS'
716 AND trxl.customer_trx_id = p_customer_trx_id
717 AND trxl.link_to_cust_trx_line_id = p_customer_trx_line_id;
718 END IF;
719
720 RETURN tax_amount;
721 EXCEPTION
722 WHEN OTHERS THEN
723 RETURN NULL;
724 END;
725
726 END ARP_TRX_LINE_UTIL;