1 PACKAGE BODY jtf_resource_utl AS
2 /* $Header: jtfrspub.pls 120.10 2011/03/10 10:16:57 rgokavar ship $ */
3
4 /*****************************************************************************************
5 This package body defines all the routines which are declared in the package
6 specification.
7 ******************************************************************************************/
8
9
10 /* Function to get the created_by who column. */
11
12 FUNCTION created_by
13 RETURN NUMBER
14 IS
15 l_created_by NUMBER;
16 BEGIN
17
18 SELECT fnd_global.user_id
19 INTO l_created_by
20 FROM dual;
21
22 RETURN l_created_by;
23
24 END;
25
26
27 /* Function to get the updated_by who column. */
28
29 FUNCTION updated_by
30 RETURN NUMBER
31 IS
32 l_updated_by NUMBER;
33 BEGIN
34
35 SELECT fnd_global.user_id
36 INTO l_updated_by
37 FROM dual;
38
39 RETURN l_updated_by;
40
41 END;
42
43
44 /* Function to get the login_id who column. */
45
46 FUNCTION login_id
47 RETURN NUMBER
48 IS
49 l_login_id NUMBER;
50 BEGIN
51
52 SELECT fnd_global.login_id
53 INTO l_login_id
54 FROM dual;
55
56 RETURN l_login_id;
57
58 END;
59
60
61 /* Procedure to call internal user hook. */
62
63 PROCEDURE call_internal_hook
64 (p_package_name IN VARCHAR2 ,
65 p_api_name IN VARCHAR2 ,
66 p_processing_type IN VARCHAR2,
67 x_return_status OUT NOCOPY VARCHAR2
68 ) IS
69
70 CURSOR c1
71 IS
72 SELECT hook_PACKAGE, hook_api
73 FROM jtf_hooks_data
74 WHERE PACKAGE_NAME = p_PACKAGE_name
75 AND api_name = p_api_name
76 AND execute_flag = 'Y'
77 AND processing_type = p_processing_type
78 ORDER BY execution_order;
79
80 v_cursorid integer ;
81 v_blockstr varchar2(2000);
82 v_dummy integer;
83 l_hook_package varchar2(30);
84 l_hook_api varchar2 (30);
85 BEGIN
86 x_return_status := fnd_api.g_ret_sts_success;
87
88 FOR i IN c1 LOOP
89 v_cursorid := dbms_sql.open_cursor ;
90 /* BINDVAR_SCAN_IGNORE [1] */
91 v_blockstr := ' begin '||i.hook_PACKAGE || '.' ||i.hook_api||'(:1); end; ' ;
92 dbms_sql.parse( v_cursorid , v_blockstr , dbms_sql.v7 ) ;
93 dbms_sql.bind_variable( v_cursorid , ':1' , x_return_status , 20 );
94 v_dummy := dbms_sql.execute(v_cursorid);
95 dbms_sql.variable_value( v_cursorid , ':1' , x_return_status );
96 dbms_sql.close_cursor(v_cursorid);
97
98 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
99 THEN
100 fnd_message.set_name ('JTF', 'JTF_RS_MISSING_RETURN_STATUS');
101 fnd_message.set_token('P_PROCEDURE',i.hook_PACKAGE || '.' ||i.hook_api);
102 fnd_msg_pub.add ;
103 --x_return_status :=fnd_api.g_ret_sts_error;
104 RAISE fnd_api.g_exc_error;
105 END IF;
106
107 IF X_RETURN_status IS null then
108 fnd_message.set_name ('JTF', 'JTF_RS_MISSING_RETURN_STATUS');
109 fnd_msg_pub.add ;
110 x_return_status := fnd_api.g_ret_sts_error;
111 RAISE fnd_api.g_exc_error;
112 END IF ;
113
114 END LOOP;
115
116 EXCEPTION
117 WHEN fnd_api.g_exc_unexpected_error
118 THEN
119 x_return_status := fnd_api.g_ret_sts_unexp_error;
120 WHEN OTHERS
121 THEN
122 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
123 fnd_message.set_token('P_SQLCODE',SQLCODE);
124 fnd_message.set_token('P_SQLERRM',SQLERRM);
125 fnd_message.set_token('P_API_NAME','CALL_INTERNAL_HOOK');
126 FND_MSG_PUB.add;
127 x_return_status := fnd_api.g_ret_sts_unexp_error;
128
129 END call_internal_hook;
130
131
132 /* Procedure to validate the resource group. */
133
134 PROCEDURE validate_resource_group
135 (p_group_id IN NUMBER,
136 p_group_number IN VARCHAR2,
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_group_id OUT NOCOPY NUMBER
139 ) IS
140
141 CURSOR c_group_id IS
142 SELECT group_id
143 FROM jtf_rs_groups_b
144 WHERE group_id = p_group_id;
145
146 CURSOR c_group_number IS
147 SELECT group_id
148 FROM jtf_rs_groups_b
149 WHERE group_number = p_group_number;
150
151
152 BEGIN
153
154 x_return_status := fnd_api.g_ret_sts_success;
155
156 IF p_group_id IS NULL AND p_group_number is NULL THEN
157
158 -- dbms_output.put_line('Group Id and Group Number are null');
159
160 fnd_message.set_name('JTF', 'JTF_RS_GROUP_NULL');
161 fnd_msg_pub.add;
162
163 x_return_status := fnd_api.g_ret_sts_error;
164
165 END IF;
166
167
168 IF p_group_id IS NOT NULL THEN
169
170 OPEN c_group_id;
171
172 FETCH c_group_id INTO x_group_id;
173
174 IF c_group_id%NOTFOUND THEN
175
176 -- dbms_output.put_line('Invalid Group Id');
177
178 fnd_message.set_name('JTF', 'JTF_RS_INVALID_GROUP');
179 fnd_message.set_token('P_GROUP_ID', p_group_id);
180 fnd_msg_pub.add;
181
182 x_return_status := fnd_api.g_ret_sts_error;
183
184 END IF;
185
186 CLOSE c_group_id;
187
188 ELSIF p_group_number IS NOT NULL THEN
189
190 OPEN c_group_number;
191
192 FETCH c_group_number INTO x_group_id;
193
194 IF c_group_number%NOTFOUND THEN
195
196 -- dbms_output.put_line('Invalid Group Number');
197
198 fnd_message.set_name('JTF', 'JTF_RS_INVALID_GROUP_NUMBER');
199 fnd_message.set_token('P_GROUP_NUMBER', p_group_number);
200 fnd_msg_pub.add;
201
202 x_return_status := fnd_api.g_ret_sts_error;
203
204 END IF;
205
206 CLOSE c_group_number;
207
208 END IF;
209
210 END validate_resource_group;
211
212
213
214 /* Procedure to validate the resource number. */
215
216 PROCEDURE validate_resource_number
217 (p_resource_id IN NUMBER,
218 p_resource_number IN NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_resource_id OUT NOCOPY NUMBER
221 ) IS
222
223 CURSOR c_resource_id IS
224 SELECT resource_id
225 FROM jtf_rs_resource_extns
226 WHERE resource_id = p_resource_id
227 AND ( end_date_active is null OR
228 trunc(end_date_active) >= trunc(sysdate) );
229
230 CURSOR c_resource_number IS
231 SELECT resource_id
232 FROM jtf_rs_resource_extns
233 WHERE resource_number = p_resource_number
234 AND ( end_date_active is null OR
235 trunc(end_date_active) >= trunc(sysdate) );
236
237 BEGIN
238
239 x_return_status := fnd_api.g_ret_sts_success;
240
241 IF p_resource_id IS NULL AND p_resource_number is NULL THEN
242
243 -- dbms_output.put_line('Resource Id and Resource Number are null');
244
245 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
246 fnd_msg_pub.add;
247
248 x_return_status := fnd_api.g_ret_sts_unexp_error;
249
250 END IF;
251
252
253 IF p_resource_id IS NOT NULL THEN
254
255 OPEN c_resource_id;
256
257 FETCH c_resource_id INTO x_resource_id;
258
259 IF c_resource_id%NOTFOUND THEN
260
261 -- dbms_output.put_line('Invalid or Inactive Resource');
262
263 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
264 fnd_message.set_token('P_RESOURCE_ID', p_resource_id);
265 fnd_msg_pub.add;
266
267 x_return_status := fnd_api.g_ret_sts_error;
268
269 END IF;
270
271 CLOSE c_resource_id;
272
273 ELSIF p_resource_number IS NOT NULL THEN
274
275 OPEN c_resource_number;
276
277 FETCH c_resource_number INTO x_resource_id;
278
279 IF c_resource_number%NOTFOUND THEN
280
281 -- dbms_output.put_line('Invalid or Inactive Resource');
282
283 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE_NUMBER');
284 fnd_message.set_token('P_RESOURCE_NUMBER', p_resource_number);
285 fnd_msg_pub.add;
286
287 x_return_status := fnd_api.g_ret_sts_error;
288
289 END IF;
290
291 CLOSE c_resource_number;
292
293 END IF;
294
295 END validate_resource_number;
296
297
298
299 /* Procedure to validate the input dates. */
300
301 PROCEDURE validate_input_dates
302 (p_start_date_active IN DATE,
303 p_end_date_active IN DATE,
304 x_return_status OUT NOCOPY VARCHAR2
305 ) IS
306
307 BEGIN
308
309 x_return_status := fnd_api.g_ret_sts_success;
310
311 IF p_start_date_active IS NULL THEN
312
313 -- dbms_output.put_line('Start Date Active cannot be null');
314
315 fnd_message.set_name('JTF', 'JTF_RS_START_DATE_NULL');
316 fnd_msg_pub.add;
317
318 x_return_status := fnd_api.g_ret_sts_error;
319
320 END IF;
321
322
323 /* Validate that the passed start date is less than the end date. */
324
325 IF p_end_date_active IS NOT NULL THEN
326 --Bug 9083841
327 --Changed the format of date before comparing. (Truncate format is not comparing properly)
328 --IF trunc(p_start_date_active) > trunc(p_end_date_active) THEN
329 IF to_date(to_char(p_start_date_active,'MM/DD/RRRR'),'MM/DD/RRRR') > to_date(to_char(p_end_date_active,'MM/DD/RRRR'),'MM/DD/RRRR') THEN
330
331 -- dbms_output.put_line('Start Date cannot be greater than the end date');
332
333 x_return_status := fnd_api.g_ret_sts_error;
334
335 fnd_message.set_name('JTF', 'JTF_RS_ERR_STDT_GREATER_EDDT');
336 fnd_msg_pub.add;
337
338 END IF;
339
340 END IF;
341
342
343 END validate_input_dates;
344
345
346
347 /* Procedure to validate the resource group usage. */
348
349 PROCEDURE validate_usage
350 (p_usage IN VARCHAR2,
351 x_return_status OUT NOCOPY VARCHAR2
352 ) IS
353
354 l_usage jtf_rs_group_usages.usage%TYPE;
355
356 CURSOR c_usage(p_lookup_type in varchar2,p_enabled_flag in varchar2) IS
357 SELECT lookup_code
358 FROM fnd_lookups
359 WHERE lookup_type = p_lookup_type
360 AND upper(lookup_code) = upper(p_usage)
361 AND trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate)
362 AND enabled_flag = p_enabled_flag;
363
364 BEGIN
365
366 x_return_status := fnd_api.g_ret_sts_success;
367
368 IF p_usage IS NULL THEN
369
370 -- dbms_output.put_line('Usage is null');
371
372 fnd_message.set_name('JTF', 'JTF_RS_USAGE_NULL');
373 fnd_msg_pub.add;
374
375 x_return_status := fnd_api.g_ret_sts_error;
376
377 END IF;
378
379 IF p_usage IS NOT NULL THEN
380
381 OPEN c_usage('JTF_RS_USAGE','Y');
382
383 FETCH c_usage INTO l_usage;
384
385 IF c_usage%NOTFOUND THEN
386
387 -- dbms_output.put_line('Invalid Usage');
388
389 fnd_message.set_name('JTF', 'JTF_RS_INVALID_USAGE');
390 fnd_message.set_token('P_USAGE', p_usage);
391 fnd_msg_pub.add;
392
393 x_return_status := fnd_api.g_ret_sts_error;
394
395 END IF;
396
397 CLOSE c_usage;
398
399 END IF;
400
401 END validate_usage;
402
403
404
405 /* Procedure to validate the resource category. */
406
407 PROCEDURE validate_resource_category
408 (p_category IN VARCHAR2,
409 x_return_status OUT NOCOPY VARCHAR2
410 ) IS
411
412 l_category jtf_rs_resource_extns.category%TYPE;
413
414 -- fix for performance bug # 4956628
415 -- SQL ID : 14841989 from SQL Repository
416 -- mofified the below cusror to change
417 -- the table jtf_objects_vl to jtf_objects_b from FROM clause.
418
419 CURSOR c_resource_category IS
420 SELECT jo.object_code
421 FROM jtf_objects_b jo,
422 jtf_object_usages jou
423 WHERE jo.object_code = jou.object_code
424 AND jou.object_user_code = 'RESOURCE_CATEGORIES'
425 AND upper(jo.object_code) = upper(p_category)
426 AND trunc(nvl(jo.end_date_active, sysdate)) >= trunc(sysdate);
427
428 BEGIN
429
430 x_return_status := fnd_api.g_ret_sts_success;
431
432 IF p_category IS NULL THEN
433
434 -- dbms_output.put_line('Resource Category is null');
435
436 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_CATEGORY_NULL');
437 fnd_msg_pub.add;
438
439 x_return_status := fnd_api.g_ret_sts_error;
440
441 END IF;
442
443 IF p_category IS NOT NULL THEN
444
445 OPEN c_resource_category;
446
447 FETCH c_resource_category INTO l_category;
448
449 IF c_resource_category%NOTFOUND THEN
450
451 -- dbms_output.put_line('Invalid Resource Category');
452
453 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RES_CATEGORY');
454 fnd_message.set_token('P_RESOURCE_CATEGORY', p_category);
455 fnd_msg_pub.add;
456
457 x_return_status := fnd_api.g_ret_sts_error;
458
459 END IF;
460
461 CLOSE c_resource_category;
462
463 END IF;
464
465 END validate_resource_category;
466
467
468 /* Procedure to validate the source id. */
469
470 PROCEDURE validate_source_id
471 (p_category IN VARCHAR2,
472 p_source_id IN NUMBER,
473 p_address_id IN NUMBER,
474 x_return_status OUT NOCOPY VARCHAR2
475 ) IS
476
477 CURSOR c_emp_dup_source_id IS
478 SELECT 1
479 FROM jtf_rs_resource_extns
480 WHERE source_id = p_source_id
481 AND category = 'EMPLOYEE';
482 /* Removed the below code for bug # 3455951 */
483 /*
484 CURSOR c_cwk_dup_source_id IS
485 SELECT 1
486 FROM jtf_rs_resource_extns
487 WHERE source_id = p_source_id
488 AND category = 'WORKER';
489 */
490 CURSOR c_ven_dup_source_id IS
491 SELECT 1
492 FROM jtf_rs_resource_extns
493 WHERE source_id = p_source_id
494 AND category = 'VENUE';
495
496 CURSOR c_party_dup_source_id IS
497 SELECT 1
498 FROM jtf_rs_resource_extns
499 WHERE source_id = p_source_id
500 AND category = 'PARTY';
501
502 CURSOR c_vend_dup_source_id IS
503 SELECT 1
504 FROM jtf_rs_resource_extns
505 WHERE source_id = p_source_id
506 AND category = 'SUPPLIER_CONTACT';
507
508 CURSOR c_partner_dup_source_id IS
509 SELECT 1
510 FROM jtf_rs_resource_extns
511 WHERE source_id = p_source_id
512 AND nvl(address_id,-99) = nvl(p_address_id,-99) --uncommented this to accomodate migr 09/25
513 AND category = 'PARTNER';
514
515 check_num number;
516
517 BEGIN
518
519 x_return_status := fnd_api.g_ret_sts_success;
520
521 IF p_category = 'EMPLOYEE' THEN
522 OPEN c_emp_dup_source_id;
523 FETCH c_emp_dup_source_id into check_num;
524 IF c_emp_dup_source_id%FOUND THEN
525 -- dbms_output.put_line('Source ID already exists ');
526 fnd_message.set_name('JTF', 'JTF_RS_DUP_SOURCE_ID');
527 fnd_message.set_token('P_SOURCE_ID', p_source_id);
528 fnd_msg_pub.add;
529 x_return_status := fnd_api.g_ret_sts_error;
530 END IF;
531 CLOSE c_emp_dup_source_id;
532 /* Removed the below code for bug # 3455951 */
533 /*
534 ELSIF p_category = 'WORKER' THEN
535 OPEN c_cwk_dup_source_id;
536 FETCH c_cwk_dup_source_id into check_num;
537 IF c_cwk_dup_source_id%FOUND THEN
538 fnd_message.set_name('JTF', 'JTF_RS_DUP_SOURCE_ID');
539 fnd_message.set_token('P_SOURCE_ID', p_source_id);
540 fnd_msg_pub.add;
541 x_return_status := fnd_api.g_ret_sts_error;
542 END IF;
543 CLOSE c_cwk_dup_source_id;
544 */
545 ELSIF p_category = 'PARTY' THEN
546 OPEN c_party_dup_source_id;
547 FETCH c_party_dup_source_id into check_num;
548 IF c_party_dup_source_id%FOUND THEN
549 -- dbms_output.put_line('Source ID already exists ');
550 fnd_message.set_name('JTF', 'JTF_RS_DUP_SOURCE_ID');
551 fnd_message.set_token('P_SOURCE_ID', p_source_id);
552 fnd_msg_pub.add;
553 x_return_status := fnd_api.g_ret_sts_error;
554 END IF;
555 CLOSE c_party_dup_source_id;
556
557 ELSIF p_category = 'SUPPLIER_CONTACT' THEN
558 OPEN c_vend_dup_source_id;
559 FETCH c_vend_dup_source_id into check_num;
560 IF c_vend_dup_source_id%FOUND THEN
561 -- dbms_output.put_line('Source ID already exists ');
562 fnd_message.set_name('JTF', 'JTF_RS_DUP_SOURCE_ID');
563 fnd_message.set_token('P_SOURCE_ID', p_source_id);
564 fnd_msg_pub.add;
565 x_return_status := fnd_api.g_ret_sts_error;
566 END IF;
567 CLOSE c_vend_dup_source_id;
568
569 ELSIF p_category = 'PARTNER' THEN
570 OPEN c_partner_dup_source_id;
571 FETCH c_partner_dup_source_id into check_num;
572 IF c_partner_dup_source_id%FOUND THEN
573 -- dbms_output.put_line('Source ID already exists ');
574 fnd_message.set_name('JTF', 'JTF_RS_DUP_SOURCE_ID');
575 fnd_message.set_token('P_SOURCE_ID', p_source_id);
576 fnd_msg_pub.add;
577 x_return_status := fnd_api.g_ret_sts_error;
578 END IF;
579 CLOSE c_partner_dup_source_id;
580
581 ELSIF p_category = 'VENUE' THEN
582 OPEN c_ven_dup_source_id;
583 FETCH c_ven_dup_source_id into check_num;
584 IF c_ven_dup_source_id%FOUND THEN
585 -- dbms_output.put_line('Source ID already exists ');
586 fnd_message.set_name('JTF', 'JTF_RS_DUP_SOURCE_ID');
587 fnd_message.set_token('P_SOURCE_ID', p_source_id);
588 fnd_msg_pub.add;
589 x_return_status := fnd_api.g_ret_sts_error;
590 END IF;
591 CLOSE c_ven_dup_source_id;
592
593 END IF;
594
595 END validate_source_id;
596
597
598 /* Procedure to validate the Employee Resource */
599
600 PROCEDURE validate_employee_resource
601 (p_emp_resource_id IN NUMBER,
602 p_emp_resource_number IN NUMBER,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_emp_resource_id OUT NOCOPY NUMBER
605 ) IS
606
607 CURSOR c_ppf_person_id IS
608 SELECT person_id
609 FROM per_people_f
610 WHERE person_id = p_emp_resource_id;
611
612 --CURSOR c_ppf_emp_num IS
613 --SELECT person_id
614 --FROM per_people_f
615 --WHERE employee_number = p_emp_resource_number;
616
617 BEGIN
618
619 x_return_status := fnd_api.g_ret_sts_success;
620
621 IF p_emp_resource_id IS NOT NULL THEN
622 OPEN c_ppf_person_id;
623 FETCH c_ppf_person_id INTO x_emp_resource_id;
624 IF c_ppf_person_id%NOTFOUND THEN
625 -- dbms_output.put_line('Invalid or Inactive Employee Resource');
626 fnd_message.set_name('JTF', 'JTF_RS_INVALID_EMP_RESOURCE_ID');
627 fnd_message.set_token('P_EMP_RESOURCE_ID', p_emp_resource_id);
628 fnd_msg_pub.add;
629 x_return_status := fnd_api.g_ret_sts_error;
630 END IF;
631 CLOSE c_ppf_person_id;
632
633 --ELSIF p_emp_resource_number IS NOT NULL THEN
634 -- OPEN c_ppf_emp_num;
635 -- FETCH c_ppf_emp_num INTO x_emp_resource_id;
636 -- IF c_ppf_emp_num%NOTFOUND THEN
637 -- dbms_output.put_line('Invalid or Inactive Employee Resource');
638 -- fnd_message.set_name('JTF', 'JTF_RS_INVALID_EMP_RES_NUMBER');
639 -- fnd_message.set_token('P_EMP_RESOURCE_NUMBER', p_emp_resource_number);
640 -- fnd_msg_pub.add;
641 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
642 -- END IF;
643 -- CLOSE c_ppf_emp_num;
644
645 END IF;
646 END validate_employee_resource;
647
648
649 /* Procedure to validate the Time Zone. */
650
651 PROCEDURE validate_time_zone
652 (p_time_zone_id IN NUMBER,
653 x_return_status OUT NOCOPY VARCHAR2
654 ) IS
655
656 l_time_zone_id hz_timezones.timezone_id%TYPE;
657
658 CURSOR c_time_zone_id IS
659 /* Bug 7290999 (changed reference from hz_timezones_vl to fnd_timezones_vl)
660 SELECT timezone_id
661 FROM hz_timezones
662 WHERE timezone_id = p_time_zone_id;
663 */
664 SELECT upgrade_tz_id
665 FROM fnd_timezones_vl
666 WHERE upgrade_tz_id = p_time_zone_id;
667
668 BEGIN
669
670 x_return_status := fnd_api.g_ret_sts_success;
671
672 IF p_time_zone_id IS NULL THEN
673
674 -- dbms_output.put_line('Time Zone Id is null');
675
676 fnd_message.set_name('JTF', 'JTF_RS_TIME_ZONE_NULL');
677 fnd_msg_pub.add;
678
679 x_return_status := fnd_api.g_ret_sts_error;
680
681 END IF;
682
683 IF p_time_zone_id IS NOT NULL THEN
684
685 OPEN c_time_zone_id;
686
687 FETCH c_time_zone_id INTO l_time_zone_id;
688
689 IF c_time_zone_id%NOTFOUND THEN
690
691 -- dbms_output.put_line('Invalid Time Zone');
692
693 fnd_message.set_name('JTF', 'JTF_RS_INVALID_TIME_ZONE');
694 fnd_message.set_token('P_TIME_ZONE_ID', p_time_zone_id);
695 fnd_msg_pub.add;
696
697 x_return_status := fnd_api.g_ret_sts_error;
698
699 END IF;
700
701 CLOSE c_time_zone_id;
702
703 END IF;
704
705 END validate_time_zone;
706
707
708
709 /* Procedure to validate the Language. */
710
711 PROCEDURE validate_nls_language
712 (p_nls_language IN VARCHAR2,
713 x_return_status OUT NOCOPY VARCHAR2
714 ) IS
715
716 l_nls_language fnd_languages.nls_language%TYPE;
717
718 CURSOR c_nls_language IS
719 SELECT nls_language
720 FROM fnd_languages
721 WHERE nls_language = p_nls_language;
722
723 BEGIN
724
725 x_return_status := fnd_api.g_ret_sts_success;
726
727 IF p_nls_language IS NULL THEN
728
729 -- dbms_output.put_line('Language is null');
730
731 fnd_message.set_name('JTF', 'JTF_RS_LANGUAGE_NULL');
732 fnd_msg_pub.add;
733
734 x_return_status := fnd_api.g_ret_sts_error;
735
736 END IF;
737
738 IF p_nls_language IS NOT NULL THEN
739
740 OPEN c_nls_language;
741
742 FETCH c_nls_language INTO l_nls_language;
743
744 IF c_nls_language%NOTFOUND THEN
745
746 -- dbms_output.put_line('Invalid Language');
747
748 fnd_message.set_name('JTF', 'JTF_RS_INVALID_LANGUAGE');
749 fnd_message.set_token('P_LANGUAGE', p_nls_language);
750 fnd_msg_pub.add;
751
752 x_return_status := fnd_api.g_ret_sts_error;
753
754 END IF;
755
756 CLOSE c_nls_language;
757
758 END IF;
759
760 END validate_nls_language;
761
762
763
764 /* Procedure to validate the Support Site. */
765
766 PROCEDURE validate_support_site_id
767 (p_support_site_id IN NUMBER,
768 x_return_status OUT NOCOPY VARCHAR2
769 ) IS
770
771 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
772
773 CURSOR c_support_site_id IS
774 SELECT hps.party_site_id
775 FROM hz_party_sites hps,
776 hz_party_site_uses hpsu
777 WHERE hpsu.site_use_type = 'SUPPORT_SITE'
778 AND hpsu.party_site_id = hps.party_site_id
779 AND hps.party_site_id = p_support_site_id;
780
781 BEGIN
782
783 x_return_status := fnd_api.g_ret_sts_success;
784
785 IF p_support_site_id IS NULL THEN
786
787 -- dbms_output.put_line('Support Site is null');
788
789 fnd_message.set_name('JTF', 'JTF_RS_SUPPORT_SITE_NULL');
790 fnd_msg_pub.add;
791
792 x_return_status := fnd_api.g_ret_sts_error;
793
794 END IF;
795
796 IF p_support_site_id IS NOT NULL THEN
797
798 OPEN c_support_site_id;
799
800 FETCH c_support_site_id INTO l_support_site_id;
801
802 IF c_support_site_id%NOTFOUND THEN
803
804 -- dbms_output.put_line('Invalid Support Site');
805
806 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SUPPORT_SITE');
807 fnd_message.set_token('P_SUPPORT_SITE_ID', p_support_site_id);
808 fnd_msg_pub.add;
809
810 x_return_status := fnd_api.g_ret_sts_error;
811
812 END IF;
813
814 CLOSE c_support_site_id;
815
816 END IF;
817
818 END validate_support_site_id;
819
820
821
822 /* Validate the Server Group. */
823
824 PROCEDURE validate_server_group
825 (p_server_group_id IN NUMBER,
826 p_server_group_name IN VARCHAR2,
827 x_return_status OUT NOCOPY VARCHAR2,
828 x_server_group_id OUT NOCOPY NUMBER
829 ) IS
830
831 CURSOR c_server_group_id IS
832 SELECT server_group_id
833 FROM ieo_svr_groups
834 WHERE server_group_id = p_server_group_id;
835
836 CURSOR c_server_group_name IS
837 SELECT server_group_id
838 FROM ieo_svr_groups
839 WHERE group_name = p_server_group_name;
840
841 BEGIN
842
843 x_return_status := fnd_api.g_ret_sts_success;
844
845 IF p_server_group_id IS NOT NULL THEN
846 OPEN c_server_group_id;
847
848 FETCH c_server_group_id INTO x_server_group_id;
849
850 IF c_server_group_id%NOTFOUND THEN
851
852 -- dbms_output.put_line('Invalid Server Group Id');
853
854 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SRV_GROUP_ID');
855 fnd_message.set_token('P_SERVER_GROUP_ID', p_server_group_id);
856 fnd_msg_pub.add;
857
858 x_return_status := fnd_api.g_ret_sts_error;
859
860 END IF;
861
862 CLOSE c_server_group_id;
863
864 ELSIF p_server_group_name IS NOT NULL THEN
865
866 OPEN c_server_group_name;
867
868 FETCH c_server_group_name INTO x_server_group_id;
869
870 IF c_server_group_name%NOTFOUND THEN
871
872 -- dbms_output.put_line('Invalid Interaction Center Name');
873
874 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SVR_GROUP_NAME');
875 fnd_message.set_token('P_SERVER_GROUP_NAME', p_server_group_name);
876 fnd_msg_pub.add;
877
878 x_return_status := fnd_api.g_ret_sts_error;
879
880 END IF;
881
882 CLOSE c_server_group_name;
883
884 END IF;
885
886 END validate_server_group;
887
888
889
890 /* Procedure to validate the Currency Code. */
891
892 PROCEDURE validate_currency_code
893 (p_currency_code IN VARCHAR2,
894 x_return_status OUT NOCOPY VARCHAR2
895 ) IS
896
897 l_currency_code fnd_currencies_active_monet_v.currency_code%TYPE;
898
899 CURSOR c_currency_code IS
900 SELECT currency_code
901 FROM fnd_currencies_active_monet_v
902 WHERE currency_code = p_currency_code;
903
904 BEGIN
905
906 x_return_status := fnd_api.g_ret_sts_success;
907
908 IF p_currency_code IS NULL THEN
909
910 -- dbms_output.put_line('Currency Code is null');
911
912 fnd_message.set_name('JTF', 'JTF_RS_CURRENCY_NULL');
913 fnd_msg_pub.add;
914
915 x_return_status := fnd_api.g_ret_sts_error;
916
917 END IF;
918
919 IF p_currency_code IS NOT NULL THEN
920
921 OPEN c_currency_code;
922
923 FETCH c_currency_code INTO l_currency_code;
924
925 IF c_currency_code%NOTFOUND THEN
926
927 -- dbms_output.put_line('Invalid Currency Code');
928
929 fnd_message.set_name('JTF', 'JTF_RS_INVALID_CURRENCY');
930 fnd_message.set_token('P_CURRENCY', p_currency_code);
931 fnd_msg_pub.add;
932
933 x_return_status := fnd_api.g_ret_sts_error;
934
935 END IF;
936
937 CLOSE c_currency_code;
938
939 END IF;
940
941 END validate_currency_code;
942
943
944
945 /* Procedure to validate the Hold Reason Code. */
946
947 PROCEDURE validate_hold_reason_code
948 (p_hold_reason_code IN VARCHAR2,
949 x_return_status OUT NOCOPY VARCHAR2
950 ) IS
951
952 l_hold_reason_code VARCHAR2(30);
953
954 CURSOR c_hold_reason_code IS
955 SELECT lookup_code
956 FROM fnd_lookups
957 WHERE lookup_type = 'JTF_RS_HOLD_REASON_TYPE'
958 AND lookup_code = p_hold_reason_code;
959
960 BEGIN
961
962 x_return_status := fnd_api.g_ret_sts_success;
963
964 IF p_hold_reason_code IS NULL THEN
965
966 -- dbms_output.put_line('Hold Reason Code is null');
967
968 fnd_message.set_name('JTF', 'JTF_RS_HOLD_REASON_CODE_NULL');
969 fnd_msg_pub.add;
970
971 x_return_status := fnd_api.g_ret_sts_error;
972
973 END IF;
974
975 IF p_hold_reason_code IS NOT NULL THEN
976
977 OPEN c_hold_reason_code;
978
979 FETCH c_hold_reason_code INTO l_hold_reason_code;
980
981 IF c_hold_reason_code%NOTFOUND THEN
982
983 -- dbms_output.put_line('Invalid Hold Reason Code');
984
985 fnd_message.set_name('JTF', 'JTF_RS_INVALID_HOLD_REASON_COD');
986 fnd_message.set_token('P_HOLD_REASON_CODE', p_hold_reason_code);
987 fnd_msg_pub.add;
988
989 x_return_status := fnd_api.g_ret_sts_error;
990
991 END IF;
992
993 CLOSE c_hold_reason_code;
994
995 END IF;
996
997 END validate_hold_reason_code;
998
999
1000
1001 /* Procedure to validate the Resource Team. */
1002
1003 PROCEDURE validate_resource_team
1004 (p_team_id IN NUMBER,
1005 p_team_number IN VARCHAR2,
1006 x_return_status OUT NOCOPY VARCHAR2,
1007 x_team_id OUT NOCOPY NUMBER
1008 ) IS
1009
1010 CURSOR c_team_id IS
1011 SELECT team_id
1012 FROM jtf_rs_teams_b
1013 WHERE team_id = p_team_id;
1014
1015 CURSOR c_team_number IS
1016 SELECT team_id
1017 FROM jtf_rs_teams_b
1018 WHERE team_number = p_team_number;
1019
1020
1021 BEGIN
1022
1023 x_return_status := fnd_api.g_ret_sts_success;
1024
1025 IF p_team_id IS NULL AND p_team_number is NULL THEN
1026
1027 -- dbms_output.put_line('Team Id and Team Number are null');
1028
1029 fnd_message.set_name('JTF', 'JTF_RS_TEAM_NULL');
1030 fnd_msg_pub.add;
1031
1032 x_return_status := fnd_api.g_ret_sts_error;
1033
1034 END IF;
1035
1036
1037 IF p_team_id IS NOT NULL THEN
1038
1039 OPEN c_team_id;
1040
1041 FETCH c_team_id INTO x_team_id;
1042
1043 IF c_team_id%NOTFOUND THEN
1044
1045 -- dbms_output.put_line('Invalid Team Id');
1046
1047 fnd_message.set_name('JTF', 'JTF_RS_INVALID_TEAM');
1048 fnd_message.set_token('P_TEAM_ID', p_team_id);
1049 fnd_msg_pub.add;
1050
1051 x_return_status := fnd_api.g_ret_sts_error;
1052
1053 END IF;
1054
1055 CLOSE c_team_id;
1056
1057 ELSIF p_team_number IS NOT NULL THEN
1058
1059 OPEN c_team_number;
1060
1061 FETCH c_team_number INTO x_team_id;
1062
1063 IF c_team_number%NOTFOUND THEN
1064
1065 -- dbms_output.put_line('Invalid Team Number');
1066
1067 fnd_message.set_name('JTF', 'JTF_RS_INVALID_TEAM_NUMBER');
1068 fnd_message.set_token('P_TEAM_NUMBER', p_team_number);
1069 fnd_msg_pub.add;
1070
1071 x_return_status := fnd_api.g_ret_sts_error;
1072
1073 END IF;
1074
1075 CLOSE c_team_number;
1076
1077 END IF;
1078
1079 END validate_resource_team;
1080
1081
1082
1083 /* Procedure to validate the User Id. */
1084
1085 PROCEDURE validate_user_id
1086 (p_user_id IN NUMBER,
1087 p_category IN VARCHAR2,
1088 p_source_id IN NUMBER,
1089 x_return_status OUT NOCOPY VARCHAR2
1090 ) IS
1091
1092 l_user_id fnd_user.user_id%TYPE;
1093
1094 /* Removed 'WORKER' from the below code for bug # 3455951 */
1095 CURSOR c_user_id IS
1096 SELECT user_id
1097 FROM fnd_user
1098 WHERE decode(p_category, 'EMPLOYEE', EMPLOYEE_ID, 'PARTY', CUSTOMER_ID, 'SUPPLIER_CONTACT', SUPPLIER_ID, null) = p_source_id
1099 AND user_id = p_user_id;
1100
1101 BEGIN
1102
1103 x_return_status := fnd_api.g_ret_sts_success;
1104
1105 IF p_user_id IS NULL THEN
1106
1107 -- dbms_output.put_line('User Id is null');
1108
1109 fnd_message.set_name('JTF', 'JTF_RS_USER_ID_NULL');
1110 fnd_msg_pub.add;
1111
1112 x_return_status := fnd_api.g_ret_sts_error;
1113
1114 END IF;
1115
1116 IF p_user_id IS NOT NULL THEN
1117
1118 OPEN c_user_id;
1119
1120 FETCH c_user_id INTO l_user_id;
1121
1122 IF c_user_id%NOTFOUND THEN
1123
1124 -- dbms_output.put_line('Invalid User Id');
1125
1126 fnd_message.set_name('JTF', 'JTF_RS_INVALID_USER_ID');
1127 fnd_message.set_token('P_USER_ID', p_user_id);
1128 fnd_msg_pub.add;
1129
1130 x_return_status := fnd_api.g_ret_sts_error;
1131
1132 END IF;
1133
1134 CLOSE c_user_id;
1135
1136 END IF;
1137
1138 END validate_user_id;
1139
1140
1141
1142 /* Validate the Salesrep Id */
1143
1144 PROCEDURE validate_salesrep_id
1145 (p_salesrep_id IN NUMBER,
1146 p_org_id IN NUMBER,
1147 x_return_status OUT NOCOPY VARCHAR2
1148 ) IS
1149
1150 l_salesrep_id jtf_rs_salesreps.salesrep_id%TYPE;
1151 l_org_id jtf_rs_salesreps.org_id%TYPE;
1152
1153 CURSOR c_salesrep_id IS
1154 SELECT salesrep_id
1155 FROM jtf_rs_salesreps
1156 WHERE salesrep_id = p_salesrep_id
1157 -- NVL function for ORG_ID should be removed, as Multi-Org is mandatory for Release 11i.11.(MOAC changes)
1158 -- AND nvl (org_id,-99) = nvl (p_org_id,-99);
1159 AND org_id = p_org_id;
1160
1161 BEGIN
1162 x_return_status := fnd_api.g_ret_sts_success;
1163 IF p_salesrep_id IS NULL THEN
1164 -- dbms_output.put_line('Salesrep Id is null');
1165 fnd_message.set_name('JTF', 'JTF_RS_SALESREP_ID_NULL');
1166 fnd_msg_pub.add;
1167 x_return_status := fnd_api.g_ret_sts_error;
1168 END IF;
1169 IF p_salesrep_id IS NOT NULL THEN
1170 OPEN c_salesrep_id;
1171 FETCH c_salesrep_id INTO l_salesrep_id;
1172 IF c_salesrep_id%NOTFOUND THEN
1173 -- dbms_output.put_line('Invalid Salesrep Id');
1174 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SALESREP_ID');
1175 fnd_message.set_token('P_SALESREP_ID', p_salesrep_id);
1176 fnd_msg_pub.add;
1177 x_return_status := fnd_api.g_ret_sts_error;
1178 END IF;
1179 CLOSE c_salesrep_id;
1180 END IF;
1181 END validate_salesrep_id;
1182
1183 /*
1184 Procedure : Validate Salesrep Dates
1185 Procedure created by Nishant on 17-Oct-2005 to fix bug 4354269. It will
1186 validate both start date and end date against resource start date and end date
1187
1188 Bug 8945347 - In c_get_res_dates_cr cursor out join addded on resource_id.
1189 */
1190 PROCEDURE validate_salesrep_dates
1191 (P_ID IN VARCHAR2,
1192 P_ORG_ID IN NUMBER,
1193 P_SRP_START_DATE IN DATE,
1194 p_SRP_END_DATE IN DATE,
1195 P_CR_UPD_MODE IN VARCHAR2,
1196 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1197 ) IS
1198
1199 L_SRP_START_DATE DATE;
1200 L_SRP_END_DATE DATE;
1201 L_RES_START_DATE DATE;
1202 L_RES_END_DATE DATE;
1203
1204 CURSOR c_get_res_dates_cr (l_resource_id IN NUMBER, l_org_id IN NUMBER) IS
1205 SELECT a.start_date_active, nvl(a.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
1206 FROM jtf_rs_resource_extns a
1207 , jtf_rs_salesreps b
1208 WHERE a.resource_id = b.resource_id(+)
1209 AND a.resource_id = l_resource_id
1210 AND b.org_id(+) = l_org_id
1211 ;
1212
1213 CURSOR c_get_res_dates_upd (l_salesrep_id IN NUMBER, l_org_id IN NUMBER) IS
1214 SELECT a.start_date_active, nvl(a.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
1215 FROM jtf_rs_resource_extns a
1216 , jtf_rs_salesreps b
1217 WHERE a.resource_id = b.resource_id
1218 AND b.salesrep_id = l_salesrep_id
1219 AND b.org_id = l_org_id
1220 ;
1221
1222 CURSOR c_get_srp_start_date (l_salesrep_id IN NUMBER, l_org_id IN NUMBER) IS
1223 SELECT b.start_date_active
1224 FROM jtf_rs_salesreps b
1225 WHERE b.salesrep_id = l_salesrep_id
1226 AND b.org_id = l_org_id
1227 ;
1228
1229 CURSOR c_get_srp_end_date (l_salesrep_id IN NUMBER, l_org_id IN NUMBER) IS
1230 SELECT NVL(b.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
1231 FROM jtf_rs_salesreps b
1232 WHERE b.salesrep_id = l_salesrep_id
1233 AND b.org_id = l_org_id
1234 ;
1235
1236 BEGIN
1237
1238 x_return_status := fnd_api.g_ret_sts_success;
1239
1240 -- if mode is create, p_id is resource_id
1241 IF (p_cr_upd_mode = 'C') THEN
1242 L_SRP_START_DATE := P_SRP_START_DATE;
1243
1244 IF (P_SRP_END_DATE IS NOT NULL) THEN
1245 L_SRP_END_DATE := P_SRP_END_DATE;
1246 ELSE
1247 L_SRP_END_DATE := TO_DATE('12/31/4712','MM/DD/RRRR');
1248 END IF;
1249
1250 OPEN c_get_res_dates_cr (p_id, p_org_id);
1251 FETCH c_get_res_dates_cr INTO l_res_start_date, l_res_end_date;
1252 CLOSE c_get_res_dates_cr;
1253 END IF;
1254
1255 -- if mode is create, p_id is salesrep_id
1256 IF (p_cr_upd_mode = 'U') THEN
1257 IF (P_SRP_START_DATE = FND_API.G_MISS_DATE) THEN
1258 OPEN c_get_srp_start_date(p_id, p_org_id);
1259 FETCH c_get_srp_start_date INTO L_SRP_START_DATE;
1260 CLOSE c_get_srp_start_date;
1261 ELSE
1262 L_SRP_START_DATE := P_SRP_START_DATE;
1263 END IF;
1264
1265 IF (P_SRP_END_DATE = FND_API.G_MISS_DATE) THEN
1266 OPEN c_get_srp_end_date(p_id, p_org_id);
1267 FETCH c_get_srp_end_date INTO L_SRP_END_DATE;
1268 CLOSE c_get_srp_end_date;
1269 ELSIF (P_SRP_END_DATE IS NOT NULL) THEN
1270 L_SRP_END_DATE := P_SRP_END_DATE;
1271 ELSE
1272 L_SRP_END_DATE := TO_DATE('12/31/4712','MM/DD/RRRR');
1273 END IF;
1274
1275 OPEN c_get_res_dates_upd (p_id, p_org_id);
1276 FETCH c_get_res_dates_upd INTO l_res_start_date, l_res_end_date;
1277 CLOSE c_get_res_dates_upd;
1278
1279 END IF;
1280
1281 IF (l_srp_start_date < l_res_start_date) THEN
1282 fnd_message.set_name('JTF', 'JTF_RS_SRP_STDT_GRTR_RES_STDT');
1283 fnd_msg_pub.add;
1284 x_return_status := fnd_api.g_ret_sts_error;
1285 END IF;
1286
1287 IF (l_srp_end_date > l_res_end_date) THEN
1288 fnd_message.set_name('JTF', 'JTF_RS_SRP_EDDT_GRTR_RES_EDDT');
1289 fnd_msg_pub.add;
1290 x_return_status := fnd_api.g_ret_sts_error;
1291 END IF;
1292
1293 END validate_salesrep_dates;
1294
1295 /* Validate the Territory Id */
1296
1297 PROCEDURE validate_territory_id
1298 (p_territory_id IN NUMBER,
1299 x_return_status OUT NOCOPY VARCHAR2
1300 ) IS
1301
1302 l_territory_id ra_territories.territory_id%TYPE;
1303
1304 CURSOR c_territory_id IS
1305 SELECT territory_id
1306 FROM ra_territories
1307 WHERE territory_id = p_territory_id;
1308
1309 BEGIN
1310 x_return_status := fnd_api.g_ret_sts_success;
1311 IF p_territory_id IS NULL THEN
1312 -- dbms_output.put_line('Territory Id is null');
1313 fnd_message.set_name('JTF', 'JTF_RS_TERRITORY_ID_NULL');
1314 fnd_msg_pub.add;
1315 x_return_status := fnd_api.g_ret_sts_error;
1316 END IF;
1317 IF p_territory_id IS NOT NULL THEN
1318 OPEN c_territory_id;
1319 FETCH c_territory_id INTO l_territory_id;
1320 IF c_territory_id%NOTFOUND THEN
1321 -- dbms_output.put_line('Invalid Territory Id');
1322 fnd_message.set_name('JTF', 'JTF_RS_INVALID_TERRITORY_ID');
1323 fnd_message.set_token('P_TERRITORY_ID', p_territory_id);
1324 fnd_msg_pub.add;
1325 x_return_status := fnd_api.g_ret_sts_error;
1326 END IF;
1327 CLOSE c_territory_id;
1328 END IF;
1329 END validate_territory_id;
1330
1331
1332
1333 PROCEDURE validate_salesrep_number (
1334 P_SALESREP_NUMBER IN VARCHAR2,
1335 P_ORG_ID IN NUMBER,
1336 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1337 ) IS
1338
1339 CURSOR c_salesrep_number IS
1340 SELECT 'X'
1341 FROM jtf_rs_salesreps
1342 WHERE salesrep_number = p_salesrep_number
1343 -- NVL function for ORG_ID should be removed, as Multi-Org is mandatory for Release 11i.11.(MOAC changes)
1344 -- AND nvl(org_id,-99) = nvl(p_org_id,-99);
1345 AND org_id = p_org_id;
1346
1347 -- AND org_id = to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)));
1348
1349 l_salesrep_number jtf_rs_salesreps.salesrep_number%TYPE;
1350 l_val VARCHAR2(1);
1351
1352 BEGIN
1353
1354 x_return_status := fnd_api.g_ret_sts_success;
1355
1356 /* Record is being inserted, check that salesrep number does not already exist */
1357 IF (p_salesrep_number IS NOT NULL) THEN
1358
1359 OPEN c_salesrep_number;
1360
1361 FETCH c_salesrep_number INTO l_val;
1362
1363 IF (c_salesrep_number%FOUND) THEN
1364 fnd_message.set_name('JTF', 'JTF_RS_ERR_SALESREP_NUMBER');
1365 fnd_message.set_token('P_SALESREP_NUMBER', p_salesrep_number);
1366 fnd_msg_pub.add;
1367 -- dbms_output.put_line('Salesrep already exists');
1368 x_return_status := fnd_api.g_ret_sts_error;
1369
1370 END IF;
1371
1372 CLOSE c_salesrep_number;
1373
1374 ELSE
1375
1376 fnd_message.set_name('JTF', 'JTF_RS_SALESREP_NUMBER_NULL');
1377 fnd_msg_pub.add;
1378 -- dbms_output.put_line('Salesrep number is null');
1379 x_return_status := fnd_api.g_ret_sts_error;
1380 /* Commented the below line to fix the bug # 3436895 */
1381 -- CLOSE c_salesrep_number;
1382
1383 END IF;
1384
1385 END validate_salesrep_number;
1386
1387
1388 /* Validate the Sales Credit Type Id */
1389
1390 PROCEDURE validate_sales_credit_type (
1391 P_SALES_CREDIT_TYPE_ID IN NUMBER,
1392 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1393 )
1394 IS
1395
1396 CURSOR c_credit_type IS
1397 SELECT 'X'
1398 FROM oe_sales_credit_types
1399 WHERE quota_flag = 'Y'
1400 AND enabled_flag = 'Y'
1401 AND sales_credit_type_id = p_sales_credit_type_id;
1402
1403 l_val VARCHAR2(1);
1404
1405 BEGIN
1406
1407 x_return_status := fnd_api.g_ret_sts_success;
1408
1409 /* Check credit type id is NOT NULL or not*/
1410
1411 IF (p_sales_credit_type_id is NOT NULL) THEN
1412
1413 OPEN c_credit_type;
1414 FETCH c_credit_type INTO l_val;
1415
1416 IF (c_credit_type%NOTFOUND) THEN
1417
1418 fnd_message.set_name('JTF', 'JTF_RS_ERR_SALES_CREDIT_TYPE');
1419 fnd_message.set_token('P_SALES_CREDIT_TYPE_ID', p_sales_credit_type_id);
1420 fnd_msg_pub.add;
1421 -- dbms_output.put_line('Invalid Sales Credit Type');
1422 x_return_status := fnd_api.g_ret_sts_error;
1423
1424 END IF;
1425
1426 CLOSE c_credit_type;
1427
1428 ELSE
1429
1430 fnd_message.set_name('JTF', 'JTF_RS_SALES_CREDIT_TYPE_NULL');
1431 fnd_msg_pub.add;
1432 x_return_status := fnd_api.g_ret_sts_error;
1433 -- dbms_output.put_line('Sales Credit type id is NULL');
1434
1435 END IF;
1436
1437 END validate_sales_credit_type;
1438
1439
1440 PROCEDURE check_object_existence
1441 (P_OBJECT_CODE IN JTF_OBJECTS_B.OBJECT_CODE%TYPE,
1442 P_SELECT_ID IN VARCHAR2,
1443 P_OBJECT_USER_CODE IN VARCHAR2,
1444 X_FOUND OUT NOCOPY BOOLEAN ,
1445 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1446 ) AS
1447
1448 CURSOR object_dtls_cur(l_object_code JTF_OBJECTS_B.OBJECT_CODE%TYPE,
1449 l_object_user_code JTF_OBJECT_USAGES.OBJECT_USER_CODE%TYPE)
1450 IS
1451 SELECT a.select_id
1452 ,a.from_table
1453 ,a.where_clause
1454 FROM jtf_objects_B a,
1455 jtf_object_usages b
1456 WHERE b.OBJECT_USER_CODE = l_object_user_code
1457 AND b.object_code = a.object_code
1458 and A.object_code = l_object_code;
1459
1460 object_dtls_rec object_dtls_cur%rowtype;
1461
1462 CURSOR object_code_cur(l_object_user_code JTF_OBJECT_USAGES.OBJECT_USER_CODE%TYPE)
1463 IS
1464 SELECT 'x'
1465 FROM jtf_object_usages b
1466 WHERE b.OBJECT_USER_CODE = l_object_user_code;
1467
1468 object_code_rec object_code_cur%rowtype;
1469
1470 CURSOR c_res_existence(c_resource_id IN NUMBER)
1471 IS
1472 SELECT 'X'
1473 FROM jtf_rs_resource_extns
1474 WHERE resource_id = c_resource_id;
1475
1476 l_temp varchar2(1);
1477
1478 l_object_code JTF_OBJECTS_B.OBJECT_CODE%TYPE;
1479 l_select_id number;
1480
1481 ll_object_user_code JTF_OBJECT_USAGES.OBJECT_USER_CODE%type;
1482 l_select_clause VARCHAR2(4000);
1483
1484 --FOR DYNAMIC SQL
1485 l_cursorid NUMBER;
1486 v_dummy integer;
1487
1488 --STANDARD VARIABLES
1489 l_date Date;
1490 l_user_id Number;
1491 l_login_id Number;
1492
1493 BEGIN
1494
1495 l_object_code := p_object_code;
1496 l_select_id := to_number(p_select_id);
1497 ll_object_user_code := UPPER(p_object_user_code);
1498
1499 X_FOUND := FALSE;
1500
1501 open object_code_cur(ll_object_user_code);
1502 fetch object_code_cur INTO object_code_rec;
1503 IF(object_code_cur%FOUND)
1504 THEN
1505 if object_code_cur%ISOPEN THEN
1506 close object_code_cur;
1507 end if;
1508 --open the cursor to get the related values from jtf_objects_b
1509 open object_dtls_cur(l_object_code, ll_object_user_code);
1510 fetch object_dtls_cur INTO object_dtls_rec;
1511 IF(object_dtls_cur%FOUND)
1512 THEN
1513 if object_dtls_cur%ISOPEN THEN
1514 close object_dtls_cur;
1515 end if;
1516 x_return_status := fnd_api.g_ret_sts_success;
1517 /* Added the below code to fix the bug # 3357906
1518 Changed the object existence validation for 'RS_INDIVIDUAL' to jtf_rs_resource_extns from jtf_objects,
1519 because we do not have enough time to investigate the impact of changing the
1520 jtf_objects definition for the object type 'RS_INDIVIDUAL'. */
1521 if l_object_code = 'RS_INDIVIDUAL' then
1522
1523 OPEN c_res_existence(l_select_id);
1524 FETCH c_res_existence INTO l_temp;
1525 IF c_res_existence%FOUND THEN
1526 x_found := TRUE;
1527 x_return_status := FND_API.G_RET_STS_SUCCESS;
1528 CLOSE c_res_existence;
1529 return;
1530 ELSE
1531 x_found := FALSE;
1532 x_return_status := FND_API.G_RET_STS_SUCCESS;
1533 CLOSE c_res_existence;
1534 return;
1535 END IF;
1536 else
1537 --create the sql staement based on the values fetched from jtf_objects_b
1538 IF object_dtls_rec.where_clause IS NOT NULL
1539 THEN
1540 /* BINDVAR_SCAN_IGNORE [1] */
1541 l_select_clause := 'SELECT '|| object_dtls_rec.select_id ||' '
1542 ||' FROM '||object_dtls_rec.from_table ||' '
1543 ||' WHERE ( '||object_dtls_rec.where_clause ||')'
1544 ||' AND '|| object_dtls_rec.select_id ||' = :x';
1545 ELSE
1546 /* BINDVAR_SCAN_IGNORE [1] */
1547 l_select_clause := 'SELECT '|| object_dtls_rec.select_id ||' '
1548 ||' FROM '||object_dtls_rec.from_table ||' '
1549 ||' WHERE '|| object_dtls_rec.select_id ||' = :x';
1550
1551 END IF;
1552
1553 --process the dynamic sql statement
1554 l_cursorid := DBMS_SQL.OPEN_CURSOR;
1555
1556 DBMS_SQL.PARSE(l_cursorid, l_select_clause, DBMS_SQL.V7);
1557 DBMS_SQL.BIND_VARIABLE(l_cursorid,':x',l_select_id);
1558
1559 v_dummy := DBMS_SQL.EXECUTE(l_cursorid);
1560
1561
1562 IF DBMS_SQL.FETCH_ROWS(l_cursorid) = 0
1563 THEN
1564 X_FOUND := FALSE;
1565 ELSE
1566 X_FOUND := TRUE;
1567 END IF;
1568
1569 DBMS_SQL.CLOSE_CURSOR(l_cursorid);
1570
1571 end if;
1572 ELSE
1573 --if object not found
1574 x_return_status := fnd_api.g_ret_sts_error;
1575 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_RL_RES_TYPE');
1576 FND_MSG_PUB.add;
1577
1578 END IF;
1579
1580 if object_dtls_cur%ISOPEN THEN
1581 close object_dtls_cur;
1582 end if;
1583
1584 ELSE
1585
1586 --IF object user code is not valid
1587 x_return_status := fnd_api.g_ret_sts_error;
1588 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_OBJ_USER_CODE');
1589 FND_MSG_PUB.add;
1590
1591 END IF;
1592
1593 --FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1594
1595 EXCEPTION
1596
1597 WHEN fnd_api.g_exc_unexpected_error
1598 THEN
1599 if object_code_cur%ISOPEN THEN
1600 close object_code_cur;
1601 end if;
1602 if object_dtls_cur%ISOPEN THEN
1603 close object_dtls_cur;
1604 end if;
1605 fnd_message.set_name ('JTF', 'JTF_OBJECT_ERR');
1606 FND_MSG_PUB.add;
1607 WHEN fnd_api.g_exc_error
1608 THEN
1609 null;
1610
1611 WHEN OTHERS
1612 THEN
1613 if object_code_cur%ISOPEN THEN
1614 close object_code_cur;
1615 end if;
1616 if object_dtls_cur%ISOPEN THEN
1617 close object_dtls_cur;
1618 end if;
1619 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1620 fnd_message.set_token('P_SQLCODE',SQLCODE);
1621 fnd_message.set_token('P_SQLERRM',SQLERRM);
1622 fnd_message.set_token('P_API_NAME','CHECK_OBJECT_EXISTENCE');
1623 FND_MSG_PUB.add;
1624 x_return_status := fnd_api.g_ret_sts_unexp_error;
1625
1626 END check_object_existence;
1627
1628
1629 PROCEDURE check_object_existence_migr
1630 (P_OBJECT_CODE IN JTF_OBJECTS_B.OBJECT_CODE%TYPE,
1631 P_SELECT_ID IN VARCHAR2,
1632 P_OBJECT_USER_CODE IN VARCHAR2,
1633 P_RS_ID_PUB_FLAG IN VARCHAR2,
1634 X_FOUND OUT NOCOPY BOOLEAN,
1635 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1636 ) AS
1637
1638 CURSOR object_dtls_cur(l_object_code JTF_OBJECTS_B.OBJECT_CODE%TYPE,
1639 l_object_user_code JTF_OBJECT_USAGES.OBJECT_USER_CODE%TYPE)
1640 IS
1641 SELECT a.select_id
1642 ,a.from_table
1643 ,a.where_clause
1644 FROM jtf_objects_B a,
1645 jtf_object_usages b
1646 WHERE b.OBJECT_USER_CODE = l_object_user_code
1647 AND b.object_code = a.object_code
1648 and A.object_code = l_object_code;
1649
1650 object_dtls_rec object_dtls_cur%rowtype;
1651
1652 CURSOR object_code_cur(l_object_user_code JTF_OBJECT_USAGES.OBJECT_USER_CODE%TYPE)
1653 IS
1654 SELECT 'x'
1655 FROM jtf_object_usages b
1656 WHERE b.OBJECT_USER_CODE = l_object_user_code;
1657
1658 CURSOR emp_existence(p_person_id IN NUMBER)
1659 IS
1660 SELECT 'x',full_name
1661 FROM per_all_people_f
1662 WHERE person_id = p_person_id;
1663
1664 CURSOR c_party_existence(c_party_id IN NUMBER) IS
1665 SELECT hz.party_id
1666 FROM hz_parties hz
1667 WHERE party_type not in ('ORGANIZATION', 'GROUP')
1668 AND hz.party_id = c_party_id
1669 AND not exists ( select person_id
1670 from per_all_people_f per
1671 where per.party_id = hz.party_id);
1672
1673 object_code_rec object_code_cur%rowtype;
1674
1675 l_object_code JTF_OBJECTS_B.OBJECT_CODE%TYPE;
1676 l_select_id number;
1677
1678 ll_object_user_code JTF_OBJECT_USAGES.OBJECT_USER_CODE%type;
1679 l_select_clause VARCHAR2(4000);
1680
1681 --FOR DYNAMIC SQL
1682 l_cursorid NUMBER;
1683 v_dummy integer;
1684
1685 --STANDARD VARIABLES
1686 l_date Date;
1687 l_user_id Number;
1688 l_login_id Number;
1689 l_temp Varchar2(1);
1690 l_party_id Number;
1691
1692 BEGIN
1693
1694 l_object_code := p_object_code;
1695 l_select_id := to_number(p_select_id);
1696 ll_object_user_code := UPPER(p_object_user_code);
1697 l_temp := '?';
1698
1699 X_FOUND := FALSE;
1700
1701 open object_code_cur(ll_object_user_code);
1702 fetch object_code_cur INTO object_code_rec;
1703 IF(object_code_cur%FOUND)
1704 THEN
1705 if object_code_cur%ISOPEN then
1706 close object_code_cur;
1707 end if;
1708 --open the cursor to get the related values from jtf_objects_b
1709 open object_dtls_cur(l_object_code, ll_object_user_code);
1710 fetch object_dtls_cur INTO object_dtls_rec;
1711 IF(object_dtls_cur%FOUND)
1712 THEN
1713 if object_dtls_cur%ISOPEN then
1714 close object_dtls_cur;
1715 end if;
1716 x_return_status := fnd_api.g_ret_sts_success;
1717
1718 --create the sql staement based on the values fetched from jtf_objects_b
1719
1720 IF (P_OBJECT_CODE <> 'EMPLOYEE' and P_OBJECT_CODE <> 'PARTY') THEN
1721 IF object_dtls_rec.where_clause IS NOT NULL
1722 THEN
1723 /* BINDVAR_SCAN_IGNORE [1] */
1724 l_select_clause := 'SELECT '|| object_dtls_rec.select_id ||' '
1725 ||' FROM '||object_dtls_rec.from_table ||' '
1726 ||' WHERE ( '||object_dtls_rec.where_clause ||')'
1727 ||' AND '|| object_dtls_rec.select_id ||' = :x';
1728 ELSE
1729 /* BINDVAR_SCAN_IGNORE [1] */
1730 l_select_clause := 'SELECT '|| object_dtls_rec.select_id ||' '
1731 ||' FROM '||object_dtls_rec.from_table ||' '
1732 ||' WHERE '|| object_dtls_rec.select_id ||' = :x';
1733 END IF;
1734 ELSIF P_OBJECT_CODE = 'EMPLOYEE' THEN
1735 IF P_RS_ID_PUB_FLAG = 'Y'
1736 THEN
1737 /* BINDVAR_SCAN_IGNORE [1] */
1738 l_select_clause := 'SELECT '|| object_dtls_rec.select_id ||' '
1739 ||' FROM '||object_dtls_rec.from_table ||' '
1740 ||' WHERE ( '||object_dtls_rec.where_clause ||')'
1741 ||' AND '|| object_dtls_rec.select_id ||' = :x';
1742 ELSIF P_RS_ID_PUB_FLAG = 'N'
1743 THEN
1744
1745 OPEN emp_existence(l_select_id);
1746 FETCH emp_existence INTO l_temp,G_SOURCE_NAME;
1747 CLOSE emp_existence;
1748
1749 if l_temp = 'x' then
1750 x_found := TRUE;
1751 x_return_status := FND_API.G_RET_STS_SUCCESS;
1752 return;
1753 else
1754 x_found := FALSE;
1755 x_return_status := fnd_api.g_ret_sts_unexp_error;
1756 end if;
1757 END IF;
1758 ELSIF P_OBJECT_CODE = 'PARTY' THEN
1759 OPEN c_party_existence(l_select_id);
1760 FETCH c_party_existence INTO l_party_id;
1761 IF c_party_existence%FOUND THEN
1762 x_found := TRUE;
1763 x_return_status := FND_API.G_RET_STS_SUCCESS;
1764 return;
1765 ELSE
1766 x_found := FALSE;
1767 x_return_status := FND_API.G_RET_STS_SUCCESS;
1768 return;
1769 END IF;
1770 CLOSE c_party_existence;
1771
1772 END IF;
1773
1774 --process the dynamic sql statement
1775 l_cursorid := DBMS_SQL.OPEN_CURSOR;
1776
1777 DBMS_SQL.PARSE(l_cursorid, l_select_clause, DBMS_SQL.V7);
1778
1779 DBMS_SQL.BIND_VARIABLE(l_cursorid,':x',l_select_id);
1780 v_dummy := DBMS_SQL.EXECUTE(l_cursorid);
1781
1782
1783 IF DBMS_SQL.FETCH_ROWS(l_cursorid) = 0
1784 THEN
1785 X_FOUND := FALSE;
1786 ELSE
1787 X_FOUND := TRUE;
1788 END IF;
1789
1790 DBMS_SQL.CLOSE_CURSOR(l_cursorid);
1791
1792 ELSE
1793 --if object not found
1794 x_return_status := fnd_api.g_ret_sts_unexp_error;
1795 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_RL_RES_TYPE');
1796 FND_MSG_PUB.add;
1797
1798 END IF;
1799
1800 if object_dtls_cur%ISOPEN then
1801 close object_dtls_cur;
1802 end if;
1803
1804 ELSE
1805
1806 --IF object user code is not valid
1807 x_return_status := fnd_api.g_ret_sts_unexp_error;
1808 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_OBJ_USER_CODE');
1809 FND_MSG_PUB.add;
1810
1811 END IF;
1812
1813 --FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1814
1815 EXCEPTION
1816
1817 WHEN fnd_api.g_exc_unexpected_error
1818 THEN
1819 if object_code_cur%ISOPEN then
1820 close object_code_cur;
1821 end if;
1822 if object_dtls_cur%ISOPEN then
1823 close object_dtls_cur;
1824 end if;
1825
1826 fnd_message.set_name ('JTF', 'JTF_OBJECT_ERR');
1827 FND_MSG_PUB.add;
1828 x_return_status := fnd_api.g_ret_sts_unexp_error;
1829 --FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1830
1831 WHEN fnd_api.g_exc_error
1832 THEN
1833 x_return_status := fnd_api.g_ret_sts_error;
1834 WHEN OTHERS
1835 THEN
1836 if object_code_cur%ISOPEN then
1837 close object_code_cur;
1838 end if;
1839 if object_dtls_cur%ISOPEN then
1840 close object_dtls_cur;
1841 end if;
1842 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1843 fnd_message.set_token('P_SQLCODE',SQLCODE);
1844 fnd_message.set_token('P_SQLERRM',SQLERRM);
1845 fnd_message.set_token('P_API_NAME','CHECK_OBJECT_EXISTENCE_MIGR');
1846 FND_MSG_PUB.add;
1847 x_return_status := fnd_api.g_ret_sts_unexp_error;
1848 --FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1849 END check_object_existence_migr;
1850
1851
1852
1853 /* Procedure to validate Resource Param Id */
1854
1855 PROCEDURE validate_resource_param_id (
1856 p_resource_param_id IN NUMBER,
1857 x_return_status OUT NOCOPY VARCHAR2
1858 )
1859 IS
1860
1861 l_resource_param_id JTF_RS_RESOURCE_PARAMS.resource_param_id%TYPE;
1862
1863 CURSOR c_resource_param_id IS
1864 SELECT resource_param_id
1865 FROM jtf_rs_resource_params
1866 WHERE resource_param_id = p_resource_param_id;
1867
1868 BEGIN
1869
1870 x_return_status := fnd_api.g_ret_sts_success;
1871
1872 IF p_resource_param_id IS NULL THEN
1873 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_PARAM_ID_NULL');
1874 fnd_msg_pub.add;
1875 x_return_status := fnd_api.g_ret_sts_error;
1876 END IF;
1877
1878 IF p_resource_param_id IS NOT NULL THEN
1879
1880 OPEN c_resource_param_id;
1881 FETCH c_resource_param_id INTO l_resource_param_id;
1882
1883 IF c_resource_param_id%NOTFOUND THEN
1884 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RS_PRM_ID');
1885 fnd_message.set_token('P_RESOURCE_PARAM_ID', p_resource_param_id);
1886 fnd_msg_pub.add;
1887 x_return_status := fnd_api.g_ret_sts_error;
1888 END IF;
1889 CLOSE c_resource_param_id;
1890 END IF;
1891
1892 END validate_resource_param_id;
1893
1894
1895 /* Procedure to Validate Resource Value Type */
1896
1897 PROCEDURE validate_rs_value_type (
1898 p_resource_param_id IN JTF_RS_RESOURCE_VALUES.RESOURCE_PARAM_ID%TYPE,
1899 p_value_type IN JTF_RS_RESOURCE_VALUES.VALUE_TYPE%TYPE,
1900 x_return_status OUT NOCOPY VARCHAR2
1901 )
1902 IS
1903
1904 l_value_type JTF_RS_RESOURCE_VALUES.VALUE_TYPE%TYPE;
1905 c_application_id JTF_RS_RESOURCE_PARAMS.APPLICATION_ID%TYPE;
1906
1907 CURSOR c_value_type_m (l_application_id JTF_RS_RESOURCE_PARAMS.APPLICATION_ID%TYPE)
1908 IS
1909 SELECT value_type
1910 FROM jtf_rs_cct_middlewares_v cctmv, jtf_rs_resource_params jrspm
1911 WHERE cctmv.value_type = p_value_type
1912 AND jrspm.application_id = l_application_id;
1913
1914 CURSOR c_value_type_e (l_application_id JTF_RS_RESOURCE_PARAMS.APPLICATION_ID%TYPE)
1915 IS
1916 SELECT value_type
1917 FROM jtf_rs_email_accounts_v iemac, jtf_rs_resource_params jtrpm
1918 WHERE iemac.value_type = p_value_type
1919 AND jtrpm.application_id = l_application_id;
1920
1921 CURSOR c_value_type_no_sg_check
1922 IS
1923 SELECT email_account_id
1924 FROM iem_email_accounts
1925 WHERE email_account_id = p_value_type;
1926
1927 server_group_check varchar2(100);
1928
1929 BEGIN
1930
1931 x_return_status := fnd_api.g_ret_sts_success;
1932
1933 fnd_profile.get('JTF_RS_SERVER_GROUP_CHECK',server_group_check);
1934
1935 SELECT application_id
1936 INTO c_application_id
1937 FROM jtf_rs_resource_params
1938 WHERE resource_param_id = p_resource_param_id;
1939
1940 x_return_status := fnd_api.g_ret_sts_success;
1941 IF p_value_type IS NOT NULL THEN
1942
1943 IF c_application_id = 172 THEN
1944 OPEN c_value_type_m (c_application_id);
1945 FETCH c_value_type_m INTO l_value_type;
1946
1947 IF c_value_type_m%NOTFOUND THEN
1948
1949 fnd_message.set_name('JTF', 'JTF_RS_INVALID_VALUE_TYPE');
1950 fnd_message.set_token('P_VALUE_TYPE', p_value_type);
1951 fnd_msg_pub.add;
1952
1953 x_return_status := fnd_api.g_ret_sts_error;
1954 END IF;
1955
1956 CLOSE c_value_type_m;
1957
1958 ELSIF c_application_id = 680 THEN
1959 if server_group_check = 'N' then
1960 OPEN c_value_type_no_sg_check;
1961 FETCH c_value_type_no_sg_check INTO l_value_type;
1962 IF c_value_type_no_sg_check%NOTFOUND THEN
1963 fnd_message.set_name('JTF', 'JTF_RS_INVALID_VALUE_TYPE');
1964 fnd_message.set_token('P_VALUE_TYPE', p_value_type);
1965 fnd_msg_pub.add;
1966 x_return_status := fnd_api.g_ret_sts_error;
1967 END IF;
1968 CLOSE c_value_type_no_sg_check;
1969 else
1970 OPEN c_value_type_e (c_application_id);
1971 FETCH c_value_type_e INTO l_value_type;
1972 IF c_value_type_e%NOTFOUND THEN
1973 fnd_message.set_name('JTF', 'JTF_RS_INVALID_VALUE_TYPE');
1974 fnd_message.set_token('P_VALUE_TYPE', p_value_type);
1975 fnd_msg_pub.add;
1976 x_return_status := fnd_api.g_ret_sts_error;
1977 END IF;
1978 CLOSE c_value_type_e;
1979 end if;
1980 END IF;
1981 END IF;
1982
1983 END validate_rs_value_type;
1984
1985
1986 /* Procedure to validate the resource value */
1987
1988 PROCEDURE validate_resource_value (
1989 p_resource_param_id IN JTF_RS_RESOURCE_VALUES.RESOURCE_PARAM_ID%TYPE,
1990 p_value IN JTF_RS_RESOURCE_VALUES.VALUE%TYPE,
1991 x_return_status OUT NOCOPY VARCHAR2
1992 )
1993 IS
1994
1995 l_value JTF_RS_RESOURCE_VALUES.VALUE%TYPE;
1996 l_lookup_code FND_LOOKUPS.LOOKUP_CODE%TYPE;
1997 v_type VARCHAR2(32);
1998 v_length VARCHAR2(32);
1999 p_length NUMBER;
2000 i NUMBER;
2001
2002 CURSOR c_lookup_code IS
2003 SELECT lookup_code
2004 FROM fnd_lookups fnl, jtf_rs_resource_params jrp
2005 WHERE fnl.lookup_type = jrp.domain_lookup_type
2006 AND jrp.resource_param_id = p_resource_param_id
2007 AND lookup_code = p_value;
2008
2009 BEGIN
2010
2011 l_value := p_value;
2012
2013 SELECT type , length into v_type ,v_length
2014 FROM jtf_rs_resource_params
2015 WHERE resource_param_id = p_resource_param_id;
2016
2017 p_length:=LENGTH(p_value);
2018 x_return_status := fnd_api.g_ret_sts_success;
2019 IF p_value IS NULL THEN
2020 fnd_message.set_name('JTF', 'JTF_RS_VALUE_NULL');
2021 fnd_msg_pub.add;
2022 x_return_status := fnd_api.g_ret_sts_error;
2023 END IF;
2024
2025 OPEN c_lookup_code;
2026 FETCH c_lookup_code INTO l_lookup_code;
2027 IF c_lookup_code%NOTFOUND THEN
2028 IF (v_type in ('INT','INTEGER','NUMBER')) THEN
2029 FOR i in 1..p_length LOOP
2030 IF NOT ((SUBSTR(l_value,i,1)='0') or (SUBSTR(l_value,i,1)='1') or (SUBSTR(l_value,i,1)='2') or
2031 (SUBSTR(l_value,i,1)='3') or (SUBSTR(l_value,i,1)='4') or (SUBSTR(l_value,i,1)='5') or
2032 (SUBSTR(l_value,i,1)='6') or (SUBSTR(l_value,i,1)='7') or (SUBSTR(l_value,i,1)='8') or
2033 (SUBSTR(l_value,i,1)='9')) THEN
2034 fnd_message.set_name('JTF', 'JTF_RS_VALUE_ERR_DATA_TYPE');
2035 fnd_msg_pub.add;
2036 x_return_status := fnd_api.g_ret_sts_error;
2037 END IF;
2038 END LOOP;
2039 END IF;
2040 IF (p_length > to_number(v_length)) or (p_length = 0 ) THEN
2041 fnd_message.set_name('JTF', 'JTF_RS_VALUE_INCORRECT_LENGTH');
2042 fnd_msg_pub.add;
2043 x_return_status := fnd_api.g_ret_sts_error;
2044 END IF;
2045 END IF;
2046 CLOSE c_lookup_code;
2047 END validate_resource_value;
2048
2049
2050 /* Procedure to validate the resource role */
2051
2052 PROCEDURE validate_resource_role (
2053 p_role_id IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
2054 p_role_code IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
2055 x_return_status OUT NOCOPY VARCHAR2,
2056 x_role_id OUT NOCOPY JTF_RS_ROLES_B.ROLE_ID%TYPE
2057 )
2058 IS
2059
2060 CURSOR c_role_id IS
2061 SELECT role_id
2062 FROM jtf_rs_roles_vl
2063 WHERE role_id = p_role_id;
2064
2065 CURSOR c_role_code IS
2066 SELECT role_id
2067 FROM jtf_rs_roles_vl
2068 WHERE role_code = p_role_code;
2069
2070 BEGIN
2071 x_return_status := fnd_api.g_ret_sts_success;
2072 IF p_role_id IS NULL AND p_role_code is NULL THEN
2073 fnd_message.set_name('JTF', 'JTF_RS_ROLE_NULL');
2074 fnd_msg_pub.add;
2075 x_return_status := fnd_api.g_ret_sts_error;
2076 END IF;
2077
2078 IF p_role_id IS NOT NULL THEN
2079 OPEN c_role_id;
2080 FETCH c_role_id INTO x_role_id;
2081 IF c_role_id%NOTFOUND THEN
2082 fnd_message.set_name('JTF', 'JTF_RS_INVALID_ROLE');
2083 fnd_message.set_token('P_ROLE_ID', p_role_id);
2084 fnd_msg_pub.add;
2085 x_return_status := fnd_api.g_ret_sts_error;
2086 END IF;
2087 CLOSE c_role_id;
2088
2089 ELSIF p_role_code IS NOT NULL THEN
2090 OPEN c_role_code;
2091 FETCH c_role_code INTO x_role_id;
2092 IF c_role_code%NOTFOUND THEN
2093 fnd_message.set_name('JTF', 'JTF_RS_INVALID_ROLE_CODE');
2094 fnd_message.set_token('P_ROLE_CODE', p_role_code);
2095 fnd_msg_pub.add;
2096 x_return_status := fnd_api.g_ret_sts_error;
2097 END IF;
2098 CLOSE c_role_code;
2099 END IF;
2100
2101 END validate_resource_role;
2102
2103
2104 /* Procedure to validate resource role flags */
2105
2106 PROCEDURE validate_rs_role_flags (
2107 p_rs_role_flag IN VARCHAR2,
2108 x_return_status OUT NOCOPY VARCHAR2
2109 )
2110 IS
2111 l_rs_role_flag varchar2(1);
2112
2113 BEGIN
2114 x_return_status := fnd_api.g_ret_sts_success;
2115 l_rs_role_flag := upper(p_rs_role_flag);
2116 IF ( l_rs_role_flag <> 'Y' and l_rs_role_flag <>'N') THEN
2117 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG');
2118 fnd_message.set_token('P_RS_ROLE_FLAG', p_rs_role_flag);
2119 fnd_msg_pub.add;
2120 x_return_status := fnd_api.g_ret_sts_error;
2121 END IF;
2122 END validate_rs_role_flags;
2123
2124 /* Function : get_g_miss_char */
2125
2126 FUNCTION get_g_miss_num RETURN NUMBER is
2127 begin
2128 return (fnd_api.g_miss_num);
2129 end get_g_miss_num;
2130
2131 /* Function : get_g_miss_char */
2132
2133 FUNCTION get_g_miss_char RETURN VARCHAR2 is
2134 begin
2135 return (fnd_api.g_miss_char);
2136 end get_g_miss_char;
2137
2138 /*Function : get_g_miss_date */
2139
2140 FUNCTION get_g_miss_date RETURN DATE is
2141 begin
2142 return (fnd_api.g_miss_date);
2143 end get_g_miss_date;
2144
2145
2146
2147 /*Function to check whether the ok_to_execute flag is ON or not */
2148
2149 Function Ok_To_Execute( p_Pkg_name varchar2,
2150 p_API_name varchar2,
2151 p_Process_type varchar2,
2152 p_User_hook_type varchar2
2153 ) Return Boolean
2154 IS
2155 l_execute Varchar2(1);
2156 l_conc_pgm_id Number;
2157 l_conc_pgm_name Varchar2(25);
2158
2159 Begin
2160
2161 l_conc_pgm_name := 'DEFAULT';
2162
2163 begin
2164 Select EXECUTE_FLAG
2165 into l_execute
2166 from JTF_USER_HOOKS
2167 Where
2168 pkg_name = p_pkg_name and
2169 api_name = p_api_name and
2170 processing_type = p_process_type and
2171 user_hook_type = p_user_hook_type;
2172 Exception
2173 When NO_DATA_FOUND then
2174 Return FALSE;
2175
2176 End;
2177
2178 If ( l_execute = 'Y' ) then
2179 Return TRUE;
2180 ELSE
2181 Return FALSE;
2182 End if;
2183 END Ok_To_Execute;
2184
2185
2186 -- The below function will not be called from any API's.
2187 -- Removing as a part of fixing GSCC errors in R12
2188 -- Right now it is only called from jtfrsvrb.pls
2189 /* Function to check for access to XMLGEN and valid status of JTF_USR_HKS*/
2190 /*
2191 Function check_access( x_pkg_name out NOCOPY varchar2
2192 ) Return Boolean
2193 IS
2194 l_pkg_name VARCHAR2(30);
2195
2196 CURSOR xml_cur
2197 IS
2198 SELECT obj.owner,
2199 obj.object_name
2200 FROM all_objects obj
2201 WHERE obj.object_name = 'XMLGEN'
2202 AND obj.owner IN ('APPS', 'PUBLIC');
2203
2204 xml_rec xml_cur%rowtype;
2205
2206
2207 CURSOR hk_cur
2208 IS
2209 SELECT obj.status,
2210 obj.object_type
2211 FROM all_objects obj
2212 WHERE obj.object_name = 'JTF_USR_HKS'
2213 AND obj.object_type = 'PACKAGE BODY'
2214 AND obj.owner in ('APPS', 'PUBLIC');
2215
2216 hk_rec hk_cur%rowtype;
2217
2218 BEGIN
2219
2220 open hk_cur;
2221 fetch hk_cur into hk_rec;
2222 if(hk_cur%found)
2223 then
2224 IF(hk_rec.status <> 'VALID')
2225 THEN
2226 --check xmlgen as the cause
2227 open xml_cur;
2228 fetch xml_cur into xml_rec;
2229 if(xml_cur%notfound)
2230 then
2231 x_pkg_name := 'XMLGEN';
2232 close xml_cur;
2233 return false;
2234 end if;
2235 close xml_cur;
2236 --else ret user hook error
2237 x_pkg_name := 'JTF_USR_HKS';
2238 close hk_cur;
2239 return false;
2240 END IF;
2241 end if;
2242 close hk_cur;
2243
2244 return true;
2245
2246 END check_access;
2247 */
2248 Function get_inventory_org_id RETURN NUMBER IS
2249 inventory_org_id varchar2(250);
2250 BEGIN
2251 fnd_profile.get('CS_INV_VALIDATION_ORG',inventory_org_id);
2252 RETURN(to_number(inventory_org_id));
2253 END get_inventory_org_id;
2254
2255 END jtf_resource_utl;
[Home] [Help]