[Home] [Help]
PACKAGE BODY: APPS.JA_TW_SH_GUI_UTILS
Source
1 PACKAGE BODY ja_tw_sh_gui_utils AS
2 /* $Header: jatwsgub.pls 120.7.12010000.2 2008/12/31 13:01:51 rsaini ship $ */
3
4 PROCEDURE get_next_seq_num(
5 p_sequence_name IN VARCHAR2
6 , p_sequence_num OUT NOCOPY NUMBER
7 , p_error_code OUT NOCOPY NUMBER) IS
8
9 l_sql_stmt VARCHAR2(100);
10 l_seq_num NUMBER;
11
12 BEGIN
13 l_sql_stmt := 'SELECT '
14 || p_sequence_name
15 || '.nextval seq_number '
16 || 'FROM dual';
17
18 EXECUTE IMMEDIATE l_sql_stmt INTO l_seq_num;
19 p_sequence_num := l_seq_num;
20 p_error_code := 0;
21
22 EXCEPTION
23 WHEN OTHERS THEN
24 p_sequence_num := NULL;
25 p_error_code := SQLCODE;
26 END get_next_seq_num;
27
28 FUNCTION get_last_trx_num(
29 p_sequence_name IN VARCHAR2) RETURN NUMBER IS
30 l_apps_short_name CONSTANT VARCHAR2(2) := 'JA'; --bug7133650
31 l_status VARCHAR2(50);
32 l_industry VARCHAR2(50);
33 l_seq_owner VARCHAR2(30);
34 l_seq_name VARCHAR2(30);
35 l_last_trx_num NUMBER;
36
37 CURSOR c_last_trx_num(
38 x_seq_name IN VARCHAR2
39 , x_seq_owner IN VARCHAR2) IS
40 SELECT last_number - 1
41 FROM all_sequences
42 WHERE sequence_name = x_seq_name
43 AND sequence_owner = x_seq_owner;
44
45 BEGIN
46
47 l_seq_name := p_sequence_name;
48
49 IF NOT fnd_installation.get_app_info(
50 l_apps_short_name
51 , l_status
52 , l_industry
53 , l_seq_owner)
54 THEN
55 app_exception.raise_exception;
56 END IF;
57
58 OPEN c_last_trx_num(
59 l_seq_name
60 , l_seq_owner);
61 FETCH c_last_trx_num INTO l_last_trx_num;
62 IF c_last_trx_num%NOTFOUND THEN
63 app_exception.raise_exception;
64 END IF;
65 CLOSE c_last_trx_num;
66
67 RETURN l_last_trx_num;
68
69 END get_last_trx_num;
70
71 FUNCTION get_seq_name(
72 p_batch_source_id IN NUMBER) RETURN VARCHAR2 IS
73
74 l_org_id VARCHAR2(15);
75 l_batch_source_id VARCHAR2(15);
76 l_seq_name VARCHAR2(30);
77 l_country_code VARCHAR2(30); --bug7133650
78 BEGIN
79
80 l_batch_source_id := TO_CHAR(p_batch_source_id);
81 l_org_id := fnd_profile.value('ORG_ID');
82 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
83 -- Start Bug 7133650
84 If (l_country_code = 'TW') Then
85 l_seq_name := 'JA_GUI_NUMBER_'
86 || l_batch_source_id
87 || '_'
88 || l_org_id
89 || '_S';
90 Else
91 l_seq_name := 'RA_TRX_NUMBER_'
92 || l_batch_source_id
93 || '_'
94 || l_org_id
95 || '_S';
96 End If;
97 -- End Bug 7133650
98 RETURN l_seq_name;
99
100 END get_seq_name;
101
102
103 PROCEDURE get_trx_src_info(
104 p_batch_source_id IN NUMBER
105 , p_auto_trx_num_flag OUT NOCOPY VARCHAR2
106 , p_inv_word OUT NOCOPY VARCHAR2
107 , p_init_trx_num OUT NOCOPY VARCHAR2
108 , p_fin_trx_num OUT NOCOPY VARCHAR2
109 , p_last_trx_date OUT NOCOPY VARCHAR2
110 , p_adv_days OUT NOCOPY NUMBER
111 -- Bug 4673732 : R12 MOAC
112 , p_org_id IN NUMBER) IS
113
114 CURSOR c_trx_src_info(x_batch_source_id NUMBER,
115 x_org_id NUMBER) IS
116 SELECT
117 src.auto_trx_numbering_flag auto_trx_num_flag
118 , src.global_attribute3 inv_word
119 , src.global_attribute2 init_trx_num
120 , src.global_attribute4 fin_trx_num
121 , src.global_attribute5 last_trx_date
122 , TO_NUMBER(src.global_attribute6) adv_days
123 FROM ra_batch_sources src
124 WHERE src.batch_source_id = x_batch_source_id
125 -- Bug 4673732 : R12 MOAC change
126 AND decode(nvl(x_org_id,1), 1, 1, src.org_id) = nvl(x_org_id, 1);
127
128 l_batch_source_id NUMBER;
129 l_trx_src_info c_trx_src_info%ROWTYPE;
130
131 BEGIN
132
133 l_batch_source_id := p_batch_source_id;
134
135 OPEN c_trx_src_info(l_batch_source_id, p_org_id);
136 LOOP
137 FETCH c_trx_src_info INTO l_trx_src_info;
138 EXIT WHEN c_trx_src_info%NOTFOUND;
139 END LOOP;
140 CLOSE c_trx_src_info;
141
142 p_auto_trx_num_flag := l_trx_src_info.auto_trx_num_flag;
143 p_inv_word := l_trx_src_info.inv_word;
144 p_init_trx_num := l_trx_src_info.init_trx_num;
145 p_fin_trx_num := l_trx_src_info.fin_trx_num;
146 p_last_trx_date := l_trx_src_info.last_trx_date;
147 p_adv_days := l_trx_src_info.adv_days;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 RAISE;
152 END get_trx_src_info;
153
154 PROCEDURE get_trx_type_info(
155 p_cust_trx_type_id IN NUMBER
156 , p_gui_type OUT NOCOPY VARCHAR2
157 , p_inv_class OUT NOCOPY VARCHAR2
158 -- Bug 4673732 : R12 MOAC
159 , p_org_id IN NUMBER) IS
160
161 CURSOR c_trx_type_info(x_cust_trx_type_id NUMBER,
162 x_org_id NUMBER) IS
163 SELECT
164 ctt.type inv_class
165 -- R12 Change : , ctt.global_attribute1 gui_type
166 , NULL gui_type
167 FROM ra_cust_trx_types ctt
168 WHERE ctt.cust_trx_type_id = p_cust_trx_type_id
169 -- Bug 4673732 : R12 MOAC change
170 AND decode(nvl(x_org_id,1), 1, 1, ctt.org_id) = nvl(x_org_id, 1);
171
172 l_cust_trx_type_id NUMBER;
173 l_trx_type_info c_trx_type_info%ROWTYPE;
174
175 BEGIN
176
177 l_cust_trx_type_id := p_cust_trx_type_id;
178
179 OPEN c_trx_type_info(l_cust_trx_type_id,
180 p_org_id);
181 LOOP
182 FETCH c_trx_type_info INTO l_trx_type_info;
183 EXIT WHEN c_trx_type_info%NOTFOUND;
184 END LOOP;
185 CLOSE c_trx_type_info;
186
187 p_gui_type := l_trx_type_info.gui_type;
188 p_inv_class := l_trx_type_info.inv_class;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 RAISE;
193 END get_trx_type_info;
194
195
196 -- Bug 4673732 : R12 MOAC
197 FUNCTION get_ref_src_id(
198 p_batch_source_id IN NUMBER,
199 p_org_id IN NUMBER) RETURN NUMBER IS
200
201 l_batch_source_id NUMBER(15);
202 l_ref_source_id NUMBER(15);
203
204 CURSOR c_ref_src(x_batch_source_id NUMBER,
205 x_org_id NUMBER) IS
206 SELECT src.global_attribute1
207 FROM ra_batch_sources src
208 WHERE src.batch_source_id = x_batch_source_id
209 -- Bug 4673732 : R12 MOAC change
210 AND decode(nvl(x_org_id,1), 1, 1, src.org_id) = nvl(x_org_id, 1);
211 BEGIN
212
213 l_batch_source_id := p_batch_source_id;
214
215 OPEN c_ref_src(l_batch_source_id, p_org_id);
216 LOOP
217 FETCH c_ref_src INTO l_ref_source_id;
218 EXIT WHEN c_ref_src%NOTFOUND;
219 END LOOP;
220 CLOSE c_ref_src;
221
222 RETURN l_ref_source_id;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 RAISE;
227 END get_ref_src_id;
228
229 -- Bug 4673732 : R12 MOAC
230 FUNCTION get_inv_word(
231 p_batch_source_id IN NUMBER,
232 p_org_id IN NUMBER) RETURN VARCHAR2 IS
233
234 l_batch_source_id NUMBER;
235 l_inv_word VARCHAR2(2);
236
237 CURSOR c_inv_word(x_batch_source_id NUMBER,
238 x_org_id NUMBER) IS
239 SELECT src.global_attribute3
240 FROM ra_batch_sources src
241 WHERE src.batch_source_id = x_batch_source_id
242 -- Bug 4673732 : R12 MOAC change
243 AND decode(nvl(x_org_id,1), 1, 1, src.org_id) = nvl(x_org_id, 1);
244 BEGIN
245
246 l_batch_source_id := p_batch_source_id;
247
248 OPEN c_inv_word(l_batch_source_id, p_org_id);
249 LOOP
250 FETCH c_inv_word INTO l_inv_word;
251 EXIT WHEN c_inv_word%NOTFOUND;
252 END LOOP;
253 CLOSE c_inv_word;
254
255 RETURN l_inv_word;
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 RAISE;
260 END get_inv_word;
261
262 --
263 -- When Reference Transaction Source is not null, return
264 -- Reference Transaction Source ID(GLOBAL_ATTRIBUTE1).
265 -- Otherwise, return Transaction Source Id(BATCH_SOURCE_ID).
266 --
267
268 -- Bug 4673732 : R12 MOAC
269 FUNCTION get_gui_src_id(
270 p_batch_source_id IN NUMBER,
271 p_org_id IN NUMBER) RETURN NUMBER IS
272
273 l_batch_source_id NUMBER(15);
274 l_gui_source_id NUMBER(15);
275
276 CURSOR c_gui_src(x_batch_source_id NUMBER,
277 x_org_id NUMBER) IS
278 SELECT decode(src.global_attribute1
279 , NULL
280 , x_batch_source_id
281 , src.global_attribute1)
282 FROM ra_batch_sources src
283 WHERE src.batch_source_id = x_batch_source_id
284 -- Bug 4673732 : R12 MOAC change
285 AND decode(nvl(x_org_id,1), 1, 1, src.org_id) = nvl(x_org_id, 1);
286 BEGIN
287
288 l_batch_source_id := p_batch_source_id;
289
290 OPEN c_gui_src(l_batch_source_id, p_org_id);
291 LOOP
292 FETCH c_gui_src INTO l_gui_source_id;
293 EXIT WHEN c_gui_src%NOTFOUND;
294 END LOOP;
295 CLOSE c_gui_src;
296
297 RETURN l_gui_source_id;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 RAISE;
302 END get_gui_src_id;
303
304 -- Bug 4673732
305 FUNCTION get_trx_num_range(
306 p_batch_source_id IN NUMBER
307 , p_ini_or_fin IN VARCHAR2
308 , p_org_id IN NUMBER) RETURN VARCHAR2 IS
309
310 l_batch_source_id NUMBER(15);
311 l_ini_or_fin VARCHAR2(3);
312 l_trx_num VARCHAR2(8);
313
314 CURSOR c_trx_num_range(
315 x_batch_source_id NUMBER
316 , x_ini_or_fin VARCHAR2
317 , x_org_id NUMBER) IS
318 SELECT
319 DECODE(x_ini_or_fin
320 , 'INI'
321 , src.global_attribute2
322 , 'FIN'
323 , src.global_attribute4
324 , NULL)
325 FROM
326 ra_batch_sources src
327 WHERE
328 src.batch_source_id = x_batch_source_id
329 -- Bug 4673732 : R12 MOAC change
330 AND decode(nvl(x_org_id,1), 1, 1, src.org_id) = nvl(x_org_id, 1);
331 BEGIN
332
333 l_batch_source_id := p_batch_source_id;
334 l_ini_or_fin := p_ini_or_fin;
335
336 OPEN c_trx_num_range(
337 l_batch_source_id
338 , l_ini_or_fin
339 , p_org_id);
340 LOOP
341 FETCH c_trx_num_range INTO l_trx_num;
342 EXIT WHEN c_trx_num_range%NOTFOUND;
343 END LOOP;
344 CLOSE c_trx_num_range;
345
346 RETURN l_trx_num;
347
348 EXCEPTION
349 WHEN OTHERS THEN
350 RAISE;
351 END get_trx_num_range;
352
353 -- Bug 4673732 : R12 MOAC
354 FUNCTION get_last_trx_date(
355 p_batch_source_id IN NUMBER,
356 p_org_id NUMBER) RETURN DATE IS
357
358 l_gui_source_id NUMBER;
359 l_last_trx_date DATE;
360
361 CURSOR c_last_trx_date(x_gui_source_id NUMBER,
362 x_org_id NUMBER) IS
363 SELECT fnd_date.chardate_to_date(src.global_attribute5)
364 FROM ra_batch_sources src
365 WHERE src.batch_source_id = x_gui_source_id
366 -- Bug 4673732 : R12 MOAC change
367 AND decode(nvl(x_org_id,1), 1, 1, src.org_id) = nvl(x_org_id, 1);
368 BEGIN
369
370 l_gui_source_id := p_batch_source_id;
371
372 OPEN c_last_trx_date(l_gui_source_id,
373 p_org_id);
374 LOOP
375 FETCH c_last_trx_date INTO l_last_trx_date;
376 EXIT WHEN c_last_trx_date%NOTFOUND;
377 END LOOP;
378 CLOSE c_last_trx_date;
379
380 RETURN l_last_trx_date;
381
382 EXCEPTION
383 WHEN OTHERS THEN
384 RAISE;
385 END get_last_trx_date;
386
387 -- Bug 4673732
388 FUNCTION get_adv_days(
389 p_batch_source_id IN NUMBER,
390 p_org_id IN NUMBER) RETURN NUMBER IS
391
392 l_batch_source_id NUMBER;
393 l_adv_days NUMBER;
394
395 CURSOR c_adv_days(x_batch_source_id NUMBER,
396 x_org_id NUMBER) IS
397 SELECT src.global_attribute6
398 FROM ra_batch_sources src
402 BEGIN
399 WHERE src.batch_source_id = x_batch_source_id
400 -- Bug 4673732 : R12 MOAC change
401 AND decode(nvl(x_org_id,1), 1, 1, src.org_id) = nvl(x_org_id, 1);
403
404 l_batch_source_id := p_batch_source_id;
405
406 OPEN c_adv_days(l_batch_source_id,
407 p_org_id);
408 LOOP
409 FETCH c_adv_days INTO l_adv_days;
410 EXIT WHEN c_adv_days%NOTFOUND;
411 END LOOP;
412 CLOSE c_adv_days;
413
414 RETURN l_adv_days;
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 RAISE;
419 END get_adv_days;
420
421 --
422 -- Check if source and type relationship is defined.
423 --
424 FUNCTION val_src_type_rel(
425 p_interface_line_id IN NUMBER
426 , p_batch_source_id IN NUMBER
427 , p_cust_trx_type_id IN NUMBER
428 , p_created_from IN VARCHAR2) RETURN VARCHAR2 IS
429
430 l_interface_line_id NUMBER;
431 l_batch_source_id NUMBER;
432 l_cust_trx_type_id NUMBER;
433 l_created_from VARCHAR2(10);
434 l_dummy VARCHAR2(10);
435 l_message_text VARCHAR2(240);
436 l_invalid_value VARCHAR2(240);
437 BEGIN
438 /*
439 ** R12 Changes : 4460720
440 **
441 ** Stub out logic as Source/Type relationship feature
442 ** is obsolete in R12.
443 **
444 l_interface_line_id := p_interface_line_id;
445 l_batch_source_id := p_batch_source_id;
446 l_cust_trx_type_id := p_cust_trx_type_id;
447 l_created_from := p_created_from;
448
449 SELECT 'SUCCESS'
450 INTO l_dummy
451 FROM jg_zz_ar_src_trx_ty st
452 WHERE st.batch_source_id = l_batch_source_id
453 AND st.cust_trx_type_id = l_cust_trx_type_id
454 AND st.enable_flag = 'Y'; -- Bug 1865837
455 **
456 **
457 **
458 */
459
460 RETURN 'SUCCESS';
461
462 EXCEPTION
463 WHEN NO_DATA_FOUND THEN
464 IF l_created_from = 'RAXTRX' THEN
465 fnd_message.set_name('JA','JA_TW_AR_INVALID_TRX_TYPE');
466 l_message_text := fnd_message.get;
467 l_invalid_value := l_cust_trx_type_id;
468
469 IF NOT jg_zz_auto_invoice.put_error_message(
470 l_interface_line_id
471 , l_message_text
472 , l_invalid_value)
473 THEN
474 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_src_type_rel.');
475 arp_standard.debug('-- Cannot insert the error record into ra_interface_errors.');
476 RETURN 'FATAL';
477 ELSE
478 RETURN 'FAIL';
479 END IF;
480 ELSIF l_created_from IN ('ARXTWMAI','ARXREC') THEN --bug7133650
481 RETURN 'FAIL';
482 END IF;
483 WHEN OTHERS THEN
484 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_src_type_rel.');
485 RETURN 'FATAL';
486 END val_src_type_rel;
487
488 FUNCTION val_trx_num(
489 p_interface_line_id IN NUMBER
490 , p_batch_source_id IN NUMBER
491 , p_fin_trx_num IN VARCHAR2
492 , p_created_from IN VARCHAR2) RETURN VARCHAR2 IS
493
494 put_error_mesg EXCEPTION;
495 l_interface_line_id NUMBER;
496 l_batch_source_id NUMBER;
497 l_fin_trx_num VARCHAR2(8);
498 l_created_from VARCHAR2(10);
499 l_seq_name VARCHAR2(30);
500 l_last_trx_num VARCHAR2(8);
501
502 BEGIN
503
504 l_interface_line_id := p_interface_line_id;
505 l_batch_source_id := p_batch_source_id;
506 l_fin_trx_num := p_fin_trx_num;
507 l_created_from := p_created_from;
508
509 --
510 -- Get the sequence name.
511 --
512 l_seq_name := get_seq_name(
513 l_batch_source_id);
514 --
515 -- Get the last transaction number.
516 --
517 -- Bug 2739911
518 -- l_last_trx_num := LPAD(get_last_trx_num(l_seq_name),8,'0');
519 l_last_trx_num := get_last_trx_num(l_seq_name);
520 --
521 -- Check if the current sequence number is within the trx number range.
522 --
523 IF l_last_trx_num >= TO_NUMBER(l_fin_trx_num) THEN
524 IF l_created_from = 'RAXTRX' THEN
525 IF NOT jg_zz_auto_invoice.put_error_message(
526 'JA'
527 , 'JA_TW_AR_GUI_NUM_OUT_OF_RANGE'
528 , TO_CHAR(l_interface_line_id)
529 , l_last_trx_num)
530 THEN
531 RAISE put_error_mesg;
532 ELSE
533 RETURN 'FAIL';
534 END IF;
535 ELSIF l_created_from IN ('ARXTWMAI','ARXREC') THEN --bug7133650
536 RETURN 'FAIL';
537 END IF;
538 END IF;
539
540 RETURN 'SUCCESS';
541
542 EXCEPTION
543 WHEN put_error_mesg THEN
544 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_trx_num.');
545 arp_standard.debug('-- Cannot insert the error record into ra_interface_errors.');
549 arp_standard.debug('-- ' || SQLERRM);
546 RETURN 'FATAL';
547 WHEN OTHERS THEN
548 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_trx_num.');
550 RETURN 'FATAL';
551 END;
552
553 --
554 -- Check if transaction date is within valid ranges.
555 --
556 FUNCTION val_trx_date(
557 p_interface_line_id IN NUMBER
558 , p_batch_source_id IN NUMBER
559 , p_trx_date IN DATE
560 , p_last_trx_date IN VARCHAR2
561 , p_advance_days IN NUMBER
562 , p_created_from IN VARCHAR2
563 -- Bug 4673732 : R12 MOAC
564 , p_org_id IN NUMBER) RETURN VARCHAR2 IS
565
566 put_error_mesg EXCEPTION;
567 upd_last_trx_date EXCEPTION;
568
569 l_interface_line_id NUMBER;
570 l_batch_source_id NUMBER;
571 l_trx_date DATE;
572 l_last_trx_date DATE;
573 l_adv_days NUMBER;
574 l_created_from VARCHAR2(10);
575 l_advanced_date DATE;
576 l_dummy VARCHAR2(10);
577 l_message_text VARCHAR2(240);
578 l_invalid_value VARCHAR2(240);
579
580 BEGIN
581
582 l_interface_line_id := p_interface_line_id;
583 l_batch_source_id := p_batch_source_id;
584 l_trx_date := TRUNC(p_trx_date);
585 l_last_trx_date := TRUNC(fnd_date.canonical_to_date(p_last_trx_date));
586 l_adv_days := p_advance_days;
587 l_created_from := p_created_from;
588 l_advanced_date := sysdate + NVL(l_adv_days,0);
589
590 IF l_trx_date BETWEEN NVL(l_last_trx_date,l_trx_date - 1) AND l_advanced_date THEN
591 IF l_trx_date > NVL(l_last_trx_date,l_trx_date -1) THEN
592 BEGIN
593 IF NOT update_last_trx_date(
594 l_batch_source_id
595 , l_trx_date
596 , l_created_from
597 , p_org_id)
598 THEN
599 app_exception.raise_exception;
600 END IF;
601 EXCEPTION
602 WHEN OTHERS THEN
603 IF l_created_from = 'RAXTRX' THEN
604 IF SQLCODE = -54 THEN
605 fnd_message.set_name('JA','JA_TW_AR_LAST_ISSD_DT_LOCKED');
606 l_message_text := fnd_message.get;
607 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_trx_date.');
608 arp_standard.debug('-- '|| l_message_text);
609 ELSE
610 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_trx_date.');
611 arp_standard.debug('-- '|| SQLERRM);
612 END IF;
613 RETURN 'FATAL';
614 ELSIF l_created_from IN ('ARXTWMAI','ARXREC') THEN --bug7133650
615 RETURN TO_CHAR(SQLCODE);
616 END IF;
617 END;
618 END IF;
619 ELSE
620 IF l_created_from = 'RAXTRX' THEN
621 fnd_message.set_name('JA','JA_TW_AR_INVALID_TRX_DATE');
622 fnd_message.set_token('LAST_ISSUED_DATE',fnd_date.date_to_chardate(l_last_trx_date));
623 fnd_message.set_token('ADVANCED_DATE',fnd_date.date_to_chardate(l_advanced_date));
624 l_message_text := fnd_message.get;
625 l_invalid_value := fnd_date.date_to_chardate(l_trx_date);
626 IF NOT jg_zz_auto_invoice.put_error_message(
627 l_interface_line_id
628 , l_message_text
629 , l_invalid_value)
630 THEN
631 RAISE put_error_mesg;
632 ELSE
633 RETURN 'FAIL';
634 END IF;
635 ELSIF l_created_from IN ('ARXTWMAI','ARXREC') THEN --bug7133650
636 RETURN 'FAIL';
637 END IF;
638 END IF;
639
640 RETURN 'SUCCESS';
641
642 EXCEPTION
643 WHEN put_error_mesg THEN
644 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_trx_date.');
645 arp_standard.debug('-- Cannot insert the error record into ra_interface_errors.');
646 arp_standard.debug('-- '|| SQLERRM);
647 RETURN 'FATAL';
648 WHEN OTHERS THEN
649 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_trx_date. ');
650 arp_standard.debug('-- '|| SQLERRM);
651 RETURN 'FATAL';
652 END val_trx_date;
653
654 --
655 -- Validate Mixed Tax Codes.
656 --
657 FUNCTION val_mixed_tax_codes(
658 p_interface_line_id IN NUMBER
659 , p_customer_trx_id IN NUMBER
660 , p_created_from IN VARCHAR2) RETURN VARCHAR2 IS
661
662 put_error_mesg EXCEPTION;
663
664 l_interface_line_id NUMBER;
665 l_customer_trx_id NUMBER;
666 l_cnt NUMBER;
667 l_created_from VARCHAR2(10);
668 l_message_text VARCHAR2(240);
669
670 --
671 -- Cursor for Autoinvoice
672 --
673 CURSOR c_cnt_tax_codes_ai(x_customer_trx_id NUMBER) IS
674 SELECT
675 COUNT(DISTINCT l.tax_code)
676 FROM
677 ra_interface_lines_gt l
678 WHERE
679 l.customer_trx_id = x_customer_trx_id;
680 --
684 SELECT
681 -- Cursor for Transactions
682 --
683 CURSOR c_cnt_tax_codes_tx(x_customer_trx_id NUMBER) IS
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 l_interface_line_id := p_interface_line_id;
694 l_customer_trx_id := p_customer_trx_id;
695 l_cnt := 0;
696 l_created_from := p_created_from;
697 IF l_created_from = 'RAXTRX' THEN
698
699 OPEN c_cnt_tax_codes_ai(l_customer_trx_id);
700 LOOP
701 FETCH c_cnt_tax_codes_ai
702 INTO l_cnt;
703 EXIT WHEN c_cnt_tax_codes_ai%NOTFOUND;
704 END LOOP;
705 CLOSE c_cnt_tax_codes_ai;
706 ELSIF l_created_from IN ('ARXTWMAI','ARXREC') THEN --bug7133650
707 OPEN c_cnt_tax_codes_tx(l_customer_trx_id);
708 LOOP
709 FETCH c_cnt_tax_codes_tx
710 INTO l_cnt;
711 EXIT WHEN c_cnt_tax_codes_tx%NOTFOUND;
712 END LOOP;
713 CLOSE c_cnt_tax_codes_tx;
714 END IF;
715
716 IF l_cnt >= 2 THEN
717 IF l_created_from = 'RAXTRX' THEN
718 fnd_message.set_name('JA','JA_TW_AR_MIXED_TAX_CODE');
719 l_message_text := fnd_message.get;
720 IF NOT jg_zz_auto_invoice.put_error_message(
721 l_interface_line_id
722 , l_message_text
723 , NULL) -- Invalid Value
724 THEN
725 RAISE put_error_mesg;
726 ELSE
727 RETURN 'FAIL';
728 END IF;
729 ELSIF l_created_from IN ('ARXTWMAI','ARXREC') THEN --bug7133650
730 RETURN 'FAIL';
731 END IF;
732 ELSE
733 RETURN 'SUCCESS';
734 END IF;
735 EXCEPTION
736 WHEN put_error_mesg THEN
737 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_mixed_tax_codes.');
738 arp_standard.debug('-- Cannot insert the error record into ra_interface_errors.');
739 arp_standard.debug('-- '|| SQLERRM);
740 RETURN 'FATAL';
741 WHEN OTHERS THEN
742 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.val_mixed_tax_codes.');
743 arp_standard.debug('-- '|| SQLERRM);
744 RETURN 'FATAL';
745 END;
746
747 --
748 -- Update Last Transaction Date of Transaction Sources.
749 --
750 -- Bug 4673732 : R12 MOAC
751 FUNCTION update_last_trx_date(
752 p_batch_source_id IN NUMBER
753 , p_last_trx_date IN DATE
754 , p_created_from IN VARCHAR2
755 , p_org_id IN NUMBER) RETURN BOOLEAN IS
756
757 CURSOR c_last_issued_date(
758 x_batch_source_id NUMBER,
759 x_org_id NUMBER) IS
760 SELECT global_attribute5
761 FROM ra_batch_sources src
762 WHERE batch_source_id = x_batch_source_id
763 -- Bug 4673732 : R12 MOAC change
764 AND decode(nvl(x_org_id,1), 1, 1, src.org_id) = nvl(x_org_id, 1)
765 FOR UPDATE OF global_attribute5 NOWAIT;
766
767 l_gui_source_id NUMBER;
768 l_last_trx_date VARCHAR2(30);
769 l_dummy VARCHAR2(30);
770 l_created_from VARCHAR2(10);
771
772 BEGIN
773
774 l_gui_source_id := p_batch_source_id;
775 l_created_from := p_created_from;
776 l_last_trx_date := fnd_date.date_to_canonical(p_last_trx_date);
777
778 OPEN c_last_issued_date(l_gui_source_id,
779 p_org_id);
780 FETCH c_last_issued_date INTO l_dummy;
781
782 UPDATE ra_batch_sources
783 SET global_attribute5 = l_last_trx_date
784 WHERE CURRENT OF c_last_issued_date;
785
786 CLOSE c_last_issued_date;
787
788 RETURN TRUE;
789 --
790 -- Removed Exception Handler to pass SQLCODE to val_trx_date.
791 --
792 END update_last_trx_date;
793
794 --
795 -- Copy GUI Type of the transaction type to GDF in Transactions.
796 --
797 FUNCTION copy_gui_type(
798 p_interface_line_id IN NUMBER
799 , p_gui_type IN VARCHAR2
800 , p_created_from IN VARCHAR2) RETURN BOOLEAN IS
801
802 l_interface_line_id NUMBER;
803 l_gui_type VARCHAR2(2);
804 l_created_from VARCHAR2(10);
805 BEGIN
806 /*
807 ** R12 Changes : 4460720
808 **
809 ** Stub out logic as Source/Type relationship feature
810 ** is obsolete in R12.
811 **
812 l_interface_line_id := p_interface_line_id;
813 l_gui_type := p_gui_type;
814 l_created_from := p_created_from;
815
816 UPDATE ra_interface_lines_gt
817 SET header_gdf_attribute1 = l_gui_type
818 WHERE interface_line_id = l_interface_line_id;
819 **
820 **
821 */
822
823 RETURN TRUE;
824 EXCEPTION
825 WHEN OTHERS THEN
826 IF l_created_from = 'RAXTRX' THEN
827 arp_standard.debug('-- Found exception in ja_tw_sh_gui_utils.copy_gui_type.');
828 arp_standard.debug('-- '|| SQLERRM);
829 END IF;
830 RETURN FALSE;
831 END;
832
833 END ja_tw_sh_gui_utils;