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