[Home] [Help]
PACKAGE BODY: APPS.JA_ZZ_VALIDATE_INVOICE_GDF
Source
1 PACKAGE BODY ja_zz_validate_invoice_gdf AS
2 /* $Header: jazzrivb.pls 120.4 2006/01/06 20:19:19 ykonishi ship $ */
3
4 -- pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5
6 -----------------------------------------------------------------------------
7 -- Taiwan Validation --
8 -----------------------------------------------------------------------------
9
10 PROCEDURE get_next_seq_num(
11 p_sequence_name IN VARCHAR2
12 , p_sequence_num OUT NOCOPY NUMBER
13 , p_error_code OUT NOCOPY NUMBER) IS
14
15 l_sql_stmt VARCHAR2(100);
16 l_seq_num NUMBER;
17
18 BEGIN
19 l_sql_stmt := 'SELECT '
20 || p_sequence_name
21 || '.nextval seq_number '
22 || 'FROM dual';
23
24 EXECUTE IMMEDIATE l_sql_stmt INTO l_seq_num;
25 p_sequence_num := l_seq_num;
26 p_error_code := 0;
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 p_sequence_num := NULL;
31 p_error_code := SQLCODE;
32 END get_next_seq_num;
33
34 FUNCTION get_last_trx_num(
35 p_sequence_name IN VARCHAR2) RETURN NUMBER IS
36
37 l_apps_short_name CONSTANT VARCHAR2(2) := 'AR';
38 l_status VARCHAR2(50);
39 l_industry VARCHAR2(50);
40 l_seq_owner VARCHAR2(30);
41 l_seq_name VARCHAR2(30);
42 l_last_trx_num NUMBER;
43
44 CURSOR c_last_trx_num(
45 x_seq_name IN VARCHAR2
46 , x_seq_owner IN VARCHAR2) IS
47 SELECT last_number - 1
48 FROM all_sequences
49 WHERE sequence_name = x_seq_name
50 AND sequence_owner = x_seq_owner;
51
52 BEGIN
53
54 l_seq_name := p_sequence_name;
55
56 IF NOT fnd_installation.get_app_info(
57 l_apps_short_name
58 , l_status
59 , l_industry
60 , l_seq_owner)
61 THEN
62 app_exception.raise_exception;
63 END IF;
64
65 OPEN c_last_trx_num(
66 l_seq_name
67 , l_seq_owner);
68 FETCH c_last_trx_num INTO l_last_trx_num;
69 IF c_last_trx_num%NOTFOUND THEN
70 app_exception.raise_exception;
71 END IF;
72 CLOSE c_last_trx_num;
73
74 RETURN l_last_trx_num;
75
76 END get_last_trx_num;
77
78 FUNCTION get_seq_name(
79 p_batch_source_id IN NUMBER) RETURN VARCHAR2 IS
80
81 l_org_id VARCHAR2(15);
82 l_batch_source_id VARCHAR2(15);
83 l_seq_name VARCHAR2(30);
84 BEGIN
85
86 l_batch_source_id := TO_CHAR(p_batch_source_id);
87
88 l_org_id := fnd_profile.value('ORG_ID');
89
90 l_seq_name := 'RA_TRX_NUMBER_'
91 || l_batch_source_id
92 || '_'
93 || l_org_id
94 || '_S';
95
96 RETURN l_seq_name;
97
98 END get_seq_name;
99
100
101 PROCEDURE get_trx_src_info(
102 p_batch_source_id IN NUMBER
103 , p_auto_trx_num_flag OUT NOCOPY VARCHAR2
104 , p_inv_word OUT NOCOPY VARCHAR2
105 , p_init_trx_num OUT NOCOPY VARCHAR2
106 , p_fin_trx_num OUT NOCOPY VARCHAR2
107 , p_last_trx_date OUT NOCOPY VARCHAR2
108 , p_adv_days OUT NOCOPY NUMBER) IS
109
110 CURSOR c_trx_src_info(x_batch_source_id NUMBER) IS
111 SELECT
112 src.auto_trx_numbering_flag auto_trx_num_flag
113 , src.global_attribute3 inv_word
114 , src.global_attribute2 init_trx_num
115 , src.global_attribute4 fin_trx_num
116 , src.global_attribute5 last_trx_date
117 , TO_NUMBER(src.global_attribute6) adv_days
118 FROM ra_batch_sources src
119 WHERE src.batch_source_id = x_batch_source_id;
120
121 l_batch_source_id NUMBER;
122 l_trx_src_info c_trx_src_info%ROWTYPE;
123
124 BEGIN
125
126 l_batch_source_id := p_batch_source_id;
127
128 OPEN c_trx_src_info(l_batch_source_id);
129 LOOP
130 FETCH c_trx_src_info INTO l_trx_src_info;
131 EXIT WHEN c_trx_src_info%NOTFOUND;
132 END LOOP;
133 CLOSE c_trx_src_info;
134
135 p_auto_trx_num_flag := l_trx_src_info.auto_trx_num_flag;
136 p_inv_word := l_trx_src_info.inv_word;
137 p_init_trx_num := l_trx_src_info.init_trx_num;
138 p_fin_trx_num := l_trx_src_info.fin_trx_num;
139 p_last_trx_date := l_trx_src_info.last_trx_date;
140 p_adv_days := l_trx_src_info.adv_days;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 RAISE;
145 END get_trx_src_info;
146
147 PROCEDURE get_trx_type_info(
148 p_cust_trx_type_id IN NUMBER
149 , p_gui_type OUT NOCOPY VARCHAR2
150 , p_inv_class OUT NOCOPY VARCHAR2) IS
151
152 CURSOR c_trx_type_info(x_cust_trx_type_id NUMBER) IS
153 SELECT
154 ctt.type inv_class
155 -- R12 Changes : , ctt.global_attribute1 gui_type
156 , NULL gui_type
157 FROM ra_cust_trx_types ctt
158 WHERE ctt.cust_trx_type_id = p_cust_trx_type_id;
159
160 l_cust_trx_type_id NUMBER;
161 l_trx_type_info c_trx_type_info%ROWTYPE;
162
163 BEGIN
164
165 l_cust_trx_type_id := p_cust_trx_type_id;
166
167 OPEN c_trx_type_info(l_cust_trx_type_id);
168 LOOP
169 FETCH c_trx_type_info INTO l_trx_type_info;
170 EXIT WHEN c_trx_type_info%NOTFOUND;
171 END LOOP;
172 CLOSE c_trx_type_info;
173
174 p_gui_type := l_trx_type_info.gui_type;
175 p_inv_class := l_trx_type_info.inv_class;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 RAISE;
180 END get_trx_type_info;
181
182
183 FUNCTION get_ref_src_id(
184 p_batch_source_id IN NUMBER) RETURN NUMBER IS
185
186 l_batch_source_id NUMBER(15);
187 l_ref_source_id NUMBER(15);
188
189 CURSOR c_ref_src(x_batch_source_id NUMBER) IS
190 SELECT src.global_attribute1
191 FROM ra_batch_sources src
192 WHERE src.batch_source_id = x_batch_source_id;
193 BEGIN
194
195 l_batch_source_id := p_batch_source_id;
196
197 OPEN c_ref_src(l_batch_source_id);
198 LOOP
199 FETCH c_ref_src INTO l_ref_source_id;
200 EXIT WHEN c_ref_src%NOTFOUND;
201 END LOOP;
202 CLOSE c_ref_src;
203
204 RETURN l_ref_source_id;
205
206 EXCEPTION
207 WHEN OTHERS THEN
208 RAISE;
209 END get_ref_src_id;
210
211 FUNCTION get_inv_word(
212 p_batch_source_id IN NUMBER) RETURN VARCHAR2 IS
213
214 l_batch_source_id NUMBER;
215 l_inv_word VARCHAR2(2);
216
217 CURSOR c_inv_word(x_batch_source_id NUMBER) IS
218 SELECT src.global_attribute3
219 FROM ra_batch_sources src
220 WHERE src.batch_source_id = x_batch_source_id;
221 BEGIN
222
223 l_batch_source_id := p_batch_source_id;
224
225 OPEN c_inv_word(l_batch_source_id);
226 LOOP
227 FETCH c_inv_word INTO l_inv_word;
228 EXIT WHEN c_inv_word%NOTFOUND;
229 END LOOP;
230 CLOSE c_inv_word;
231
232 RETURN l_inv_word;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 RAISE;
237 END get_inv_word;
238
239 --
240 -- When Reference Transaction Source is not null, return
241 -- Reference Transaction Source ID(GLOBAL_ATTRIBUTE1).
242 -- Otherwise, return Transaction Source Id(BATCH_SOURCE_ID).
243 --
244 FUNCTION get_gui_src_id(
245 p_batch_source_id IN NUMBER) RETURN NUMBER IS
246
247 l_batch_source_id NUMBER(15);
248 l_gui_source_id NUMBER(15);
249
250 CURSOR c_gui_src(x_batch_source_id NUMBER) IS
251 SELECT decode(src.global_attribute1
252 , NULL
253 , x_batch_source_id
254 , src.global_attribute1)
255 FROM ra_batch_sources src
256 WHERE src.batch_source_id = x_batch_source_id;
257
258 BEGIN
259
260 l_batch_source_id := p_batch_source_id;
261
262 OPEN c_gui_src(l_batch_source_id);
263 LOOP
264 FETCH c_gui_src INTO l_gui_source_id;
265 EXIT WHEN c_gui_src%NOTFOUND;
266 END LOOP;
267 CLOSE c_gui_src;
268
269 RETURN l_gui_source_id;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 RAISE;
274 END get_gui_src_id;
275
276 FUNCTION get_trx_num_range(
277 p_batch_source_id IN NUMBER
278 , p_ini_or_fin IN VARCHAR2 ) RETURN VARCHAR2 IS
279
280 l_batch_source_id NUMBER(15);
281 l_ini_or_fin VARCHAR2(3);
282 l_trx_num VARCHAR2(8);
283
284 CURSOR c_trx_num_range(
285 x_batch_source_id NUMBER
286 , x_ini_or_fin VARCHAR2) IS
287 SELECT
288 DECODE(x_ini_or_fin
289 , 'INI'
290 , src.global_attribute2
291 , 'FIN'
292 , src.global_attribute4
293 , NULL)
294 FROM
295 ra_batch_sources src
296 WHERE
297 src.batch_source_id = x_batch_source_id;
298 BEGIN
299
300 l_batch_source_id := p_batch_source_id;
301 l_ini_or_fin := p_ini_or_fin;
302
303 OPEN c_trx_num_range(
304 l_batch_source_id
305 , l_ini_or_fin);
306 LOOP
307 FETCH c_trx_num_range INTO l_trx_num;
308 EXIT WHEN c_trx_num_range%NOTFOUND;
309 END LOOP;
310 CLOSE c_trx_num_range;
311
312 RETURN l_trx_num;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 RAISE;
317 END get_trx_num_range;
318
319 FUNCTION get_last_trx_date(
320 p_batch_source_id IN NUMBER) RETURN DATE IS
321
322 l_gui_source_id NUMBER;
323 l_last_trx_date DATE;
324
325 CURSOR c_last_trx_date(x_gui_source_id NUMBER) IS
326 SELECT fnd_date.chardate_to_date(src.global_attribute5)
327 FROM ra_batch_sources src
328 WHERE src.batch_source_id = x_gui_source_id;
329
330 BEGIN
331
332 l_gui_source_id := p_batch_source_id;
333
334 OPEN c_last_trx_date(l_gui_source_id);
335 LOOP
336 FETCH c_last_trx_date INTO l_last_trx_date;
337 EXIT WHEN c_last_trx_date%NOTFOUND;
338 END LOOP;
339 CLOSE c_last_trx_date;
340
341 RETURN l_last_trx_date;
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 RAISE;
346 END get_last_trx_date;
347
348 FUNCTION get_adv_days(
349 p_batch_source_id IN NUMBER) RETURN NUMBER IS
350
351 l_batch_source_id NUMBER;
352 l_adv_days NUMBER;
353
354 CURSOR c_adv_days(x_batch_source_id NUMBER) IS
355 SELECT src.global_attribute6
356 FROM ra_batch_sources src
357 WHERE src.batch_source_id = x_batch_source_id;
358
359 BEGIN
360
361 l_batch_source_id := p_batch_source_id;
362
363 OPEN c_adv_days(l_batch_source_id);
364 LOOP
365 FETCH c_adv_days INTO l_adv_days;
366 EXIT WHEN c_adv_days%NOTFOUND;
367 END LOOP;
368 CLOSE c_adv_days;
369
370 RETURN l_adv_days;
371
372 EXCEPTION
373 WHEN OTHERS THEN
374 RAISE;
375 END get_adv_days;
376
377 --
378 -- Check if source and type relationship is defined.
379 --
380 FUNCTION val_src_type_rel(
381 p_trx_header_id IN NUMBER
382 , p_trx_line_id IN NUMBER
383 , p_batch_source_id IN NUMBER
384 , p_cust_trx_type_id IN NUMBER
385 , p_created_from IN VARCHAR2) RETURN VARCHAR2 IS
386
387 l_trx_header_id NUMBER;
388 l_trx_line_id NUMBER;
389 l_batch_source_id NUMBER;
390 l_cust_trx_type_id NUMBER;
391 l_created_from VARCHAR2(10);
392 l_dummy VARCHAR2(10);
393 l_message_text VARCHAR2(240);
394 l_invalid_value VARCHAR2(240);
395 pg_debug VARCHAR2(1);
396 BEGIN
397
398 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
399
400 /*
401 ** R12 Changes : 4460720
402 **
403 ** Stub out logic as Source/Type relationship feature
404 ** is obsolete in R12.
405 **
406 l_trx_header_id := p_trx_header_id;
407 l_trx_line_id := p_trx_line_id;
408 l_batch_source_id := p_batch_source_id;
409 l_cust_trx_type_id := p_cust_trx_type_id;
410 l_created_from := p_created_from;
411
412 SELECT 'SUCCESS'
413 INTO l_dummy
414 FROM jg_zz_ar_src_trx_ty st
415 WHERE st.batch_source_id = l_batch_source_id
416 AND st.cust_trx_type_id = l_cust_trx_type_id
417 AND st.enable_flag = 'Y'; -- Bug 1865837
418 **
419 **
420 **
421 */
422
423 RETURN 'SUCCESS';
424
425 EXCEPTION
426 WHEN NO_DATA_FOUND THEN
427 IF l_created_from = 'RAXTRX' THEN
428 l_invalid_value := l_cust_trx_type_id;
429
430 IF NOT jg_zz_invoice_create.put_error_message( 'JA'
431 , 'JA_TW_AR_INVALID_TRX_TYPE'
432 , p_trx_header_id
433 , p_trx_line_id
434 , l_invalid_value)
435 THEN
436 IF pg_debug = 'Y' THEN
437 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_src_type_rel.');
438 ar_invoice_utils.debug('-- Cannot insert the error record into ar_trx_errors.');
439 END IF;
440 RETURN 'FATAL';
441 ELSE
442 RETURN 'FAIL';
443 END IF;
444 ELSIF l_created_from = 'ARXTWMAI' THEN
445 RETURN 'FAIL';
446 END IF;
447 WHEN OTHERS THEN
448 IF pg_debug = 'Y' THEN
449 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_src_type_rel.');
450 END IF;
451 RETURN 'FATAL';
452 END val_src_type_rel;
453
454 FUNCTION val_trx_num(
455 p_trx_header_id IN NUMBER
456 , p_trx_line_id IN NUMBER
457 , p_batch_source_id IN NUMBER
458 , p_fin_trx_num IN VARCHAR2
459 , p_created_from IN VARCHAR2) RETURN VARCHAR2 IS
460
461 put_error_mesg EXCEPTION;
462 l_trx_header_id NUMBER;
463 l_trx_line_id NUMBER;
464 l_batch_source_id NUMBER;
465 l_fin_trx_num VARCHAR2(8);
466 l_created_from VARCHAR2(10);
467 l_seq_name VARCHAR2(30);
468 l_last_trx_num VARCHAR2(8);
469 pg_debug VARCHAR2(1);
470
471 BEGIN
472
473 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
474
478 l_fin_trx_num := p_fin_trx_num;
475 l_trx_header_id := p_trx_header_id;
476 l_trx_line_id := p_trx_line_id;
477 l_batch_source_id := p_batch_source_id;
479 l_created_from := p_created_from;
480
481 --
482 -- Get the sequence name.
483 --
484 l_seq_name := get_seq_name(
485 l_batch_source_id);
486 --
487 -- Get the last transaction number.
488 --
489 -- Bug 2739911
490 -- l_last_trx_num := LPAD(get_last_trx_num(l_seq_name),8,'0');
491 l_last_trx_num := get_last_trx_num(l_seq_name);
492 --
493 -- Check if the current sequence number is within the trx number range.
494 --
495 IF l_last_trx_num >= TO_NUMBER(l_fin_trx_num) THEN
496 IF l_created_from = 'RAXTRX' THEN
497 IF NOT jg_zz_invoice_create.put_error_message(
498 'JA'
499 , 'JA_TW_AR_GUI_NUM_OUT_OF_RANGE'
500 , l_trx_header_id
501 , l_trx_line_id
502 , l_last_trx_num)
503 THEN
504 RAISE put_error_mesg;
505 ELSE
506 RETURN 'FAIL';
507 END IF;
508 ELSIF l_created_from = 'ARXTWMAI' THEN
509 RETURN 'FAIL';
510 END IF;
511 END IF;
512
513 RETURN 'SUCCESS';
514
515 EXCEPTION
516 WHEN put_error_mesg THEN
517 IF pg_debug = 'Y' THEN
518 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_trx_num.');
519 ar_invoice_utils.debug('-- Cannot insert the error record into ar_trx_errors.');
520 END IF;
521 RETURN 'FATAL';
522 WHEN OTHERS THEN
523 IF pg_debug = 'Y' THEN
524 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_trx_num.');
525 ar_invoice_utils.debug('-- ' || SQLERRM);
526 END IF;
527 RETURN 'FATAL';
528 END;
529
530 --
531 -- Check if transaction date is within valid ranges.
532 --
533 FUNCTION val_trx_date(
534 p_trx_header_id IN NUMBER
535 , p_trx_line_id IN NUMBER
536 , p_batch_source_id IN NUMBER
537 , p_trx_date IN DATE
538 , p_last_trx_date IN VARCHAR2
539 , p_advance_days IN NUMBER
540 , p_created_from IN VARCHAR2) RETURN VARCHAR2 IS
541
542 put_error_mesg EXCEPTION;
543 upd_last_trx_date EXCEPTION;
544
545 l_trx_header_id NUMBER;
546 l_trx_line_id NUMBER;
547 l_batch_source_id NUMBER;
548 l_trx_date DATE;
549 l_last_trx_date DATE;
550 l_adv_days NUMBER;
551 l_created_from VARCHAR2(10);
552 l_advanced_date DATE;
553 l_dummy VARCHAR2(10);
554 l_message_text VARCHAR2(240);
555 l_invalid_value VARCHAR2(240);
556 pg_debug VARCHAR2(1);
557
558 BEGIN
559
560 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
561
562 l_trx_header_id := p_trx_header_id;
563 l_trx_line_id := p_trx_line_id;
564 l_batch_source_id := p_batch_source_id;
565 l_trx_date := TRUNC(p_trx_date);
566 l_last_trx_date := TRUNC(fnd_date.canonical_to_date(p_last_trx_date));
567 l_adv_days := p_advance_days;
568 l_created_from := p_created_from;
569
570 l_advanced_date := sysdate + NVL(l_adv_days,0);
571
572 IF l_trx_date BETWEEN NVL(l_last_trx_date,l_trx_date - 1) AND l_advanced_date THEN
573 IF l_trx_date > NVL(l_last_trx_date,l_trx_date -1) THEN
574 BEGIN
575 IF NOT update_last_trx_date(
576 l_batch_source_id
577 , l_trx_date
578 , l_created_from)
579 THEN
580 app_exception.raise_exception;
581 END IF;
582 EXCEPTION
583 WHEN OTHERS THEN
584 IF l_created_from = 'RAXTRX' THEN
585 IF SQLCODE = -54 THEN
586 fnd_message.set_name('JA','JA_TW_AR_LAST_ISSD_DT_LOCKED');
587 l_message_text := fnd_message.get;
588 IF pg_debug = 'Y' THEN
589 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_trx_date.');
590 ar_invoice_utils.debug('-- '|| l_message_text);
591 END IF;
592 ELSE
593 IF pg_debug = 'Y' THEN
594 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_trx_date.');
595 ar_invoice_utils.debug('-- '|| SQLERRM);
596 END IF;
597 END IF;
598 RETURN 'FATAL';
599 ELSIF l_created_from = 'ARXTWMAI' THEN
600 RETURN TO_CHAR(SQLCODE);
601 END IF;
602 END;
603 END IF;
604 ELSE
605 IF l_created_from = 'RAXTRX' THEN
606 fnd_message.set_token('LAST_ISSUED_DATE',fnd_date.date_to_chardate(l_last_trx_date));
607 fnd_message.set_token('ADVANCED_DATE',fnd_date.date_to_chardate(l_advanced_date));
608 l_message_text := fnd_message.get;
609 l_invalid_value := fnd_date.date_to_chardate(l_trx_date);
613 , l_trx_header_id
610 IF NOT jg_zz_invoice_create.put_error_message(
611 'JA'
612 , 'JA_TW_AR_INVALID_TRX_DATE'
614 , l_trx_line_id
615 , l_invalid_value)
616 THEN
617 RAISE put_error_mesg;
618 ELSE
619 RETURN 'FAIL';
620 END IF;
621 ELSIF l_created_from = 'ARXTWMAI' THEN
622 RETURN 'FAIL';
623 END IF;
624 END IF;
625
626 RETURN 'SUCCESS';
627
628 EXCEPTION
629 WHEN put_error_mesg THEN
630 IF pg_debug = 'Y' THEN
631 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_trx_date.');
632 ar_invoice_utils.debug('-- Cannot insert the error record into ar_trx_errors.');
633 ar_invoice_utils.debug('-- '|| SQLERRM);
634 END IF;
635 RETURN 'FATAL';
636 WHEN OTHERS THEN
637 IF pg_debug = 'Y' THEN
638 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_trx_date. ');
639 ar_invoice_utils.debug('-- '|| SQLERRM);
640 END IF;
641 RETURN 'FATAL';
642 END val_trx_date;
643
644 --
645 -- Validate Mixed Tax Codes.
646 --
647 FUNCTION val_mixed_tax_codes(
648 p_trx_header_id IN NUMBER
649 , p_trx_line_id IN NUMBER
650 , p_customer_trx_id IN NUMBER
651 , p_created_from IN VARCHAR2) RETURN VARCHAR2 IS
652
653 put_error_mesg EXCEPTION;
654
655 l_trx_header_id NUMBER;
656 l_trx_line_id NUMBER;
657 l_customer_trx_id NUMBER;
658 l_cnt NUMBER;
659 l_created_from VARCHAR2(10);
660 l_message_text VARCHAR2(240);
661
662 pg_debug VARCHAR2(1);
663
664 --
665 -- Cursor for Invoice
666 --
667 CURSOR c_cnt_tax_codes_ai(x_customer_trx_id NUMBER) IS
668 SELECT
669 COUNT(DISTINCT tax.tax_rate_code)
670 FROM
671 ar_trx_lines_gt l,
672 --
673 -- Bug 4756219
674 -- Changing reference to ar vat tax to zx_mco_rates
675 zx_mco_rates tax
676
677 WHERE
678 l.customer_trx_id = x_customer_trx_id
679 AND l.vat_tax_id = tax.tax_rate_id;
680 --
681 -- Cursor for Transactions
682 --
683 CURSOR c_cnt_tax_codes_tx(x_customer_trx_id NUMBER) IS
684 SELECT
685 COUNT(DISTINCT tl.vat_tax_id)
686 FROM
687 ra_customer_trx_lines tl
688 WHERE
689 tl.customer_trx_id = x_customer_trx_id
690 AND tl.line_type = 'TAX'; -- Bug 2753541
691 BEGIN
692
693 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
694
695 l_trx_header_id := p_trx_header_id;
696 l_trx_line_id := p_trx_line_id;
697 l_customer_trx_id := p_customer_trx_id;
698 l_cnt := 0;
699 l_created_from := p_created_from;
700
701 IF l_created_from = 'RAXTRX' THEN
702 OPEN c_cnt_tax_codes_ai(l_customer_trx_id);
703 LOOP
704 FETCH c_cnt_tax_codes_ai
705 INTO l_cnt;
706 EXIT WHEN c_cnt_tax_codes_ai%NOTFOUND;
707 END LOOP;
708 CLOSE c_cnt_tax_codes_ai;
709 ELSIF l_created_from = 'ARXTWMAI' THEN
710 OPEN c_cnt_tax_codes_tx(l_customer_trx_id);
711 LOOP
712 FETCH c_cnt_tax_codes_tx
713 INTO l_cnt;
714 EXIT WHEN c_cnt_tax_codes_tx%NOTFOUND;
715 END LOOP;
716 CLOSE c_cnt_tax_codes_tx;
717 END IF;
718
719 IF l_cnt >= 2 THEN
720 IF l_created_from = 'RAXTRX' THEN
721 IF NOT jg_zz_invoice_create.put_error_message(
722 'JA'
723 , 'JA_TW_AR_MIXED_TAX_CODE'
724 , l_trx_header_id
725 , l_trx_line_id
726 , NULL) -- Invalid Value
727 THEN
728 RAISE put_error_mesg;
729 ELSE
730 RETURN 'FAIL';
731 END IF;
732 ELSIF l_created_from = 'ARXTWMAI' THEN
733 RETURN 'FAIL';
734 END IF;
735 ELSE
736 RETURN 'SUCCESS';
737 END IF;
738 EXCEPTION
739 WHEN put_error_mesg THEN
740 IF pg_debug = 'Y' THEN
741 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_mixed_tax_codes.');
742 ar_invoice_utils.debug('-- Cannot insert the error record into ar_trx_errors.');
743 ar_invoice_utils.debug('-- '|| SQLERRM);
744 END IF;
745 RETURN 'FATAL';
746 WHEN OTHERS THEN
747 IF pg_debug = 'Y' THEN
748 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.val_mixed_tax_codes.');
749 ar_invoice_utils.debug('-- '|| SQLERRM);
750 END IF;
751 RETURN 'FATAL';
752 END;
753
754 --
755 -- Update Last Transaction Date of Transaction Sources.
756 --
757 FUNCTION update_last_trx_date(
761
758 p_batch_source_id IN NUMBER
759 , p_last_trx_date IN DATE
760 , p_created_from IN VARCHAR2) RETURN BOOLEAN IS
762 CURSOR c_last_issued_date(
763 x_batch_source_id NUMBER) IS
764 SELECT global_attribute5
765 FROM ra_batch_sources
766 WHERE batch_source_id = x_batch_source_id
767 FOR UPDATE OF global_attribute5 NOWAIT;
768
769 l_gui_source_id NUMBER;
770 l_last_trx_date VARCHAR2(30);
771 l_dummy VARCHAR2(30);
772 l_created_from VARCHAR2(10);
773
774 BEGIN
775
776 l_gui_source_id := p_batch_source_id;
777 l_created_from := p_created_from;
778
779 l_last_trx_date := fnd_date.date_to_canonical(p_last_trx_date);
780
781 OPEN c_last_issued_date(l_gui_source_id);
782 FETCH c_last_issued_date INTO l_dummy;
783
784 UPDATE ra_batch_sources
785 SET global_attribute5 = l_last_trx_date
786 WHERE CURRENT OF c_last_issued_date;
787
788 CLOSE c_last_issued_date;
789
790 RETURN TRUE;
791 --
792 -- Removed Exception Handler to pass SQLCODE to val_trx_date.
793 --
794 END update_last_trx_date;
795
796 --
797 -- Copy GUI Type of the transaction type to GDF in Transactions.
798 --
799 FUNCTION copy_gui_type(
800 p_trx_line_id IN NUMBER
801 , p_gui_type IN VARCHAR2
802 , p_created_from IN VARCHAR2) RETURN BOOLEAN IS
803
804 l_trx_line_id NUMBER;
805 l_gui_type VARCHAR2(2);
806 l_created_from VARCHAR2(10);
807 pg_debug VARCHAR2(1);
808
809 BEGIN
810
811 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
812 /*
813 ** R12 Changes : 4460720
814 **
815 ** Stub out logic as Source/Type relationship feature
816 ** is obsolete in R12.
817 **
818 l_trx_line_id := p_trx_line_id;
819 l_gui_type := p_gui_type;
820 l_created_from := p_created_from;
821
822 UPDATE ar_trx_header_gt
823 SET global_attribute1 = l_gui_type
824 WHERE trx_header_id =
825 (select trx_header_id
826 from ar_trx_lines_gt
827 where trx_line_id = l_trx_line_id);
828 **
829 **
830 */
831
832 RETURN TRUE;
833 EXCEPTION
834 WHEN OTHERS THEN
835 IF l_created_from = 'RAXTRX' THEN
836 IF pg_debug = 'Y' THEN
837 ar_invoice_utils.debug('-- Found exception in ja_tw_invoice_create.copy_gui_type.');
838 ar_invoice_utils.debug('-- '|| SQLERRM);
839 END IF;
840 END IF;
841 RETURN FALSE;
842 END;
843
844 -----------------------------------------------------------------------------
845 -- Thailand Validation --
846 -----------------------------------------------------------------------------
847
848 FUNCTION insert_interface_errors(
849 p_customer_trx_id IN NUMBER,
850 p_msg_name IN VARCHAR2,
851 p_invalid_value IN VARCHAR2,
852 p_validation_name IN VARCHAR2
853 )
854 RETURN BOOLEAN IS
855 CURSOR interface_line IS
856 SELECT header.trx_header_id,
857 lines.trx_line_id
858 FROM ar_trx_header_gt header,
859 ar_trx_lines_gt lines
860 WHERE lines.customer_trx_id = p_customer_trx_id
861 AND lines.trx_header_id = header.trx_header_id;
862
863 cannot_insert_error EXCEPTION;
864
865 pg_debug VARCHAR2(1);
866
867 BEGIN
868
869 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
870
871 FOR l IN interface_line
872 LOOP
873 IF NOT jg_zz_invoice_create.put_error_message(
874 'JA',
875 p_msg_name,
876 l.trx_header_id,
877 l.trx_line_id,
878 p_invalid_value) THEN
879
880 raise cannot_insert_error;
881
882 END IF;
883 END LOOP;
884
885 return(TRUE);
886
887 EXCEPTION
888 WHEN cannot_insert_error THEN
889 IF pg_debug = 'Y' THEN
890 ar_invoice_utils.debug('-- Found exception in ja_th_invoice_create.' ||
891 p_validation_name);
892 ar_invoice_utils.debug('-- Cannot insert the error record into ' ||
893 'ar_trx_errors.');
894 END IF;
895 return(FALSE);
896
897 WHEN others THEN
898 IF pg_debug = 'Y' THEN
899 ar_invoice_utils.debug('-- Found exception in ' ||
900 'ja_th_invoice_create.insert_interface_errors.');
901 ar_invoice_utils.debug('-- ' || SQLERRM);
902 END IF;
903
904 return(FALSE);
905
906 END insert_interface_errors;
907
908
909 FUNCTION validate_trx_date(
910 p_customer_trx_id IN NUMBER,
911 p_trx_date IN DATE,
915 )
912 p_last_issued_date IN DATE,
913 p_advance_days IN NUMBER,
914 p_created_from IN VARCHAR2
916 RETURN NUMBER IS
917
918 pg_debug VARCHAR2(1);
919
920 BEGIN
921
922 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
923
924 IF p_last_issued_date is NULL THEN
925 return(1);
926 ELSIF p_trx_date BETWEEN p_last_issued_date AND
927 (sysdate + nvl(p_advance_days,0)) THEN
928 return(1);
929 ELSE
930 IF p_created_from = 'RAXTRX' THEN
931 fnd_message.set_token(
932 'LAST_ISSUED_DATE',
933 fnd_date.date_to_chardate(p_last_issued_date));
934 fnd_message.set_token(
935 'ADVANCED_DATE',
936 fnd_date.date_to_chardate(sysdate+nvl(p_advance_days,0)));
937
938 IF insert_interface_errors(
939 p_customer_trx_id,
940 'JA_TH_AR_INVALID_TRX_DATE',
941 fnd_date.date_to_chardate(p_trx_date),
942 'validate_trx_date') THEN
943 return(0);
944 ELSE
945 return(-1);
946 END IF;
947 ELSIF p_created_from = 'ARXTWMAI' THEN
948 return(0);
949 END IF;
950 END IF;
951
952 EXCEPTION
953
954 WHEN OTHERS THEN
955 IF p_created_from = 'RAXTRX' THEN
956 IF pg_debug = 'Y' THEN
957 ar_invoice_utils.debug('-- Found exception in ' ||
958 'ja_th_invoice_create.validate_trx_date.');
959 ar_invoice_utils.debug('-- ' || SQLERRM);
960 END IF;
961 return(-1);
962 ELSIF p_created_from = 'ARXTWMAI' THEN
963 return(0);
964 END IF;
965
966 END validate_trx_date;
967
968
969 FUNCTION validate_tax_code(
970 p_customer_trx_id IN NUMBER,
971 p_created_from IN VARCHAR2
972 )
973 RETURN NUMBER IS
974
975 n NUMBER;
976 pg_debug VARCHAR2(1);
977
978 BEGIN
979
980 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
981
982 IF p_created_from = 'ARXTWMAI' THEN
983 SELECT count(l.customer_trx_line_id)
984 INTO n
985 FROM ra_customer_trx_lines l,
986 -- Bug 4756219
987 -- Changing reference to ar vat tax to zx_mco_rates
988 zx_mco_rates v,
989 zx_accounts a
990 WHERE l.customer_trx_id = p_customer_trx_id
991 AND v.tax_rate_id = l.vat_tax_id
992 AND v.tax_rate_id = a.tax_account_entity_id
993 AND a.tax_account_entity_code = 'RATES'
994 AND a.interim_tax_ccid is NULL;
995 ELSIF p_created_from = 'RAXTRX' THEN
996 SELECT count(l.customer_trx_line_id)
997 INTO n
998 FROM ar_trx_lines_gt l,
999 -- Bug 4756219
1000 -- Changing reference to ar vat tax to zx_mco_rates
1001 zx_mco_rates v,
1002 zx_accounts a
1003 WHERE l.customer_trx_id = p_customer_trx_id
1004 AND v.tax_rate_id = l.vat_tax_id
1005 AND (v.tax_class = 'O' OR v.tax_class IS NULL)
1006 AND v.active_flag='Y'
1007 AND v.effective_from <= sysdate
1008 AND (v.effective_to >= sysdate OR v.effective_to is NULL)
1009 AND v.tax_rate_id = a.tax_account_entity_id
1010 AND a.tax_account_entity_code = 'RATES'
1011 AND a.interim_tax_ccid is NULL;
1012 END IF;
1013
1014 IF n > 0 THEN
1015 return(1);
1016 ELSE
1017 IF p_created_from = 'RAXTRX' THEN
1018
1019 IF insert_interface_errors(
1020 p_customer_trx_id,
1021 'JA_TH_AR_INVALID_TAX_CODE',
1022 NULL,
1023 'validate_tax_code') THEN
1024 return(0);
1025 ELSE
1026 return(-1);
1027 END IF;
1028 ELSIF p_created_from = 'ARXTWMAI' THEN
1029 return(0);
1030 END IF;
1031 END IF;
1032
1033 EXCEPTION
1034
1035 WHEN OTHERS THEN
1036 IF p_created_from = 'RAXTRX' THEN
1037 IF pg_debug = 'Y' THEN
1038 ar_invoice_utils.debug('-- Found exception in ' ||
1039 'ja_th_invoice_create.validate_tax_code.');
1040 ar_invoice_utils.debug('-- ' || SQLERRM);
1041 END IF;
1042 return(-1);
1043 ELSIF p_created_from = 'ARXTWMAI' THEN
1044 return(0);
1045 END IF;
1046
1047 END validate_tax_code;
1048
1049
1050 FUNCTION update_last_issued_date(
1051 p_customer_trx_id IN NUMBER,
1052 p_cust_trx_type_id IN NUMBER,
1053 p_trx_date IN DATE,
1054 p_created_from IN VARCHAR2
1055 )
1056 RETURN NUMBER IS
1057
1058 CURSOR last_issued_date IS
1059 SELECT global_attribute2
1060 FROM ra_cust_trx_types
1061 WHERE cust_trx_type_id = p_cust_trx_type_id
1062 FOR UPDATE OF global_attribute2 NOWAIT;
1063
1064 cannot_lock EXCEPTION;
1065 PRAGMA EXCEPTION_INIT(cannot_lock, -54);
1066
1067 dummy VARCHAR2(150);
1068 trx_type_name VARCHAR2(20);
1069
1070 pg_debug VARCHAR2(1);
1071
1072 BEGIN
1073
1074 BEGIN
1075
1076 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
1077
1078 OPEN last_issued_date;
1079
1080 FETCH last_issued_date INTO dummy;
1081
1082 UPDATE ra_cust_trx_types
1083 SET global_attribute2 = fnd_date.date_to_canonical(p_trx_date)
1084 WHERE CURRENT OF last_issued_date;
1085
1086 CLOSE last_issued_date;
1087
1088 EXCEPTION
1089 WHEN cannot_lock THEN
1090
1091 IF p_created_from = 'RAXTRX' THEN
1092
1093 BEGIN
1094 SELECT name
1095 INTO trx_type_name
1096 FROM ra_cust_trx_types
1097 WHERE cust_trx_type_id = p_cust_trx_type_id;
1098 EXCEPTION
1099 WHEN others THEN
1100 raise;
1101 END;
1102
1103 IF insert_interface_errors(
1104 p_customer_trx_id,
1105 'JA_TH_AR_LAST_ISSD_DT_LOCKED',
1106 fnd_date.date_to_chardate(p_trx_date),
1107 'update_last_issued_date') THEN
1108 return(0);
1109 ELSE
1110 return(-1);
1111 END IF;
1112 ELSIF p_created_from = 'ARXTWMAI' THEN
1113 return(0);
1114 END IF;
1115
1116 raise;
1117
1118 END;
1119
1120 return(1);
1121
1122 EXCEPTION
1123
1124 WHEN others THEN
1125 IF p_created_from = 'RAXTRX' THEN
1126 IF pg_debug = 'Y' THEN
1127 ar_invoice_utils.debug('-- Found exception in ' ||
1128 'ja_th_invoice_create.update_last_issued_date.');
1129 ar_invoice_utils.debug('-- ' || SQLERRM);
1130 END IF;
1131 return(-1);
1132 ELSIF p_created_from = 'ARXTWMAI' THEN
1133 return(0);
1134 END IF;
1135
1136 END update_last_issued_date;
1137
1138 END ja_zz_validate_invoice_gdf;