[Home] [Help]
PACKAGE BODY: APPS.JTF_NOTES_SECURITY_PVT
Source
1 PACKAGE BODY JTF_NOTES_SECURITY_PVT AS
2 /* $Header: jtfvnsb.pls 115.7 2003/09/26 22:52:51 hbouten ship $ */
3
4 PROCEDURE check_notes_access
5 ( p_api_version IN NUMBER
6 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
7 , p_note_id IN NUMBER
8 , x_select_predicate IN OUT NOCOPY VARCHAR2
9 , x_note_type_predicate IN OUT NOCOPY VARCHAR2
10 , x_select_access IN OUT NOCOPY NUMBER
11 , x_create_access IN OUT NOCOPY NUMBER
12 , x_update_note_access IN OUT NOCOPY NUMBER
13 , x_update_note_details_access IN OUT NOCOPY NUMBER
14 , x_update_secondary_access IN OUT NOCOPY NUMBER
15 , x_delete_access IN OUT NOCOPY NUMBER
16 , x_return_status OUT NOCOPY VARCHAR2
17 , x_msg_count OUT NOCOPY NUMBER
18 , x_msg_data OUT NOCOPY VARCHAR2
19 ) IS
20
21 l_api_name CONSTANT VARCHAR2(30) := 'check_notes_access';
22 l_api_version CONSTANT NUMBER := 1.0;
23 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
24
25 l_privilege_tbl FND_DATA_SECURITY.FND_PRIVILEGE_NAME_TABLE_TYPE;
26
27 BEGIN
28
29 --
30 -- Standard call to check for call compatibility
31 --
32 IF NOT FND_API.Compatible_API_Call( l_api_version
33 , p_api_version
34 , l_api_name
35 , G_PKG_NAME
36 )
37 THEN
38 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
39 END IF;
40
41 --
42 -- Initialize message list if p_init_msg_list is set to TRUE
43 --
44 IF FND_API.To_Boolean(p_init_msg_list)
45 THEN
46 FND_MSG_PUB.Initialize;
47 END IF;
48
49 --
50 -- Initialize API return status to success
51 --
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 --
55 -- Get Security Predicate for Select
56 --
57 IF (x_select_predicate IS NULL)
58 THEN
59 get_security_predicate
60 ( p_api_version => 1.0
61 , p_init_msg_list => FND_API.G_FALSE
62 , p_object_name => G_OBJECT_NOTE
63 , p_function => G_FUNCTION_SELECT
64 , p_statement_type => 'OTHER'
65 , p_table_alias => NULL
66 , x_predicate => x_select_predicate
67 , x_return_status => x_return_status
68 , x_msg_count => x_msg_count
69 , x_msg_data => x_msg_data
70 );
71 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
72 THEN
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 END IF;
75 END IF;
76
77 --
78 -- Get Security Predicate for Note Type DropDown
79 --
80 IF (x_note_type_predicate IS NULL)
81 THEN
82
83 get_security_predicate
84 ( p_api_version => 1.0
85 , p_init_msg_list => FND_API.G_FALSE
86 , p_object_name => G_OBJECT_NOTE_TYPE
87 , p_function => G_FUNCTION_TYPE_SELECT
88 , p_statement_type => 'OTHER'
89 , p_table_alias => NULL
90 , x_predicate => x_note_type_predicate
91 , x_return_status => x_return_status
92 , x_msg_count => x_msg_count
93 , x_msg_data => x_msg_data
94 );
95
96 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
97 THEN
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100
101 END IF;
102
103 IF ((p_note_id IS NOT NULL) AND
104 ((x_select_access NOT IN (0,1)) OR
105 (x_update_note_access NOT IN (0,1)) OR
106 (x_update_note_details_access NOT IN (0,1)) OR
107 (x_update_secondary_access NOT IN (0,1)) OR
108 (x_delete_access NOT IN (0,1))))
109 THEN
110
111 x_select_access := 0;
112 x_update_note_access := 0;
113 x_update_note_details_access := 0;
114 x_update_secondary_access := 0;
115 x_delete_access := 0;
116
117 get_functions
118 ( p_api_version => 1.0
119 , p_init_msg_list => FND_API.G_FALSE
120 , p_object_name => G_OBJECT_NOTE
121 , p_instance_pk1_value => TO_CHAR(p_note_id)
122 , p_instance_pk2_value => NULL
123 , p_instance_pk3_value => NULL
124 , p_instance_pk4_value => NULL
125 , p_instance_pk5_value => NULL
126 , p_user_name => NULL
127 , x_return_status => x_return_status
128 , x_privilege_tbl => l_privilege_tbl
129 , x_msg_count => x_msg_count
130 , x_msg_data => x_msg_data
131 );
132
133 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
134 THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138 IF (l_privilege_tbl.LAST IS NOT NULL)
139 THEN
140 FOR i IN l_privilege_tbl.FIRST..l_privilege_tbl.LAST
141 LOOP <<FUNCTIONS>>
142 IF (l_privilege_tbl(i) = G_FUNCTION_SELECT)
143 THEN
144 x_select_access := 1;
145 ELSIF (l_privilege_tbl(i) = G_FUNCTION_UPDATE_NOTE)
146 THEN
147 x_update_note_access := 1;
148 ELSIF (l_privilege_tbl(i) = G_FUNCTION_UPDATE_NOTE_DTLS)
149 THEN
150 x_update_note_details_access := 1;
151 ELSIF (l_privilege_tbl(i) = G_FUNCTION_UPDATE_SEC)
152 THEN
153 x_update_secondary_access := 1;
154 ELSIF (l_privilege_tbl(i) = G_FUNCTION_DELETE)
155 THEN
156 x_delete_access := 1;
157 END IF;
158 END LOOP;
159 END IF;
160
161 END IF;
162
163 --
164 -- Get Security Access for Create
165 --
166 IF (x_create_access NOT IN (0,1))
167 THEN
168
169 check_function
170 ( p_api_version => 1.0
171 , p_init_msg_list => FND_API.G_FALSE
172 , p_function => G_FUNCTION_CREATE
173 , p_object_name => G_OBJECT_NOTE
174 , p_instance_pk1_value => NULL
175 , p_instance_pk2_value => NULL
176 , p_instance_pk3_value => NULL
177 , p_instance_pk4_value => NULL
178 , p_instance_pk5_value => NULL
179 , p_user_name => NULL
180 , x_return_status => x_return_status
181 , x_grant => x_create_access
182 , x_msg_count => x_msg_count
183 , x_msg_data => x_msg_data
184 );
185
186 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
187 THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190 END IF;
191
192 --
193 -- Standard call to get message count and if count is 1, get message info
194 --
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
197 , p_data => x_msg_data
198 );
199
200
201 EXCEPTION
202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
203 THEN
204 -- In case of exception return no access for all functions
205 -- (required for NOCOPY)
206 x_select_predicate := 0;
207 x_note_type_predicate := 0;
208 x_select_access := 0;
209 x_create_access := 0;
210 x_update_note_access := 0;
211 x_update_note_details_access := 0;
212 x_update_secondary_access := 0;
213 x_delete_access := 0;
214
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
216 fnd_msg_pub.count_and_get( p_encoded => 'F'
217 , p_count => x_msg_count
218 , p_data => x_msg_data
219 );
220 WHEN OTHERS
221 THEN
222 -- In case of exception return no access for all functions
223 -- (required for NOCOPY)
224 x_select_predicate := 0;
225 x_note_type_predicate := 0;
226 x_select_access := 0;
227 x_create_access := 0;
228 x_update_note_access := 0;
229 x_update_note_details_access := 0;
230 x_update_secondary_access := 0;
231 x_delete_access := 0;
232 --
233 -- Set status
234 --
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 --
237 -- Push message onto CRM stack
238 --
239 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
240 , l_api_name
241 , SQLERRM
242 );
243 --
244 -- Count the messages on the CRM stack
245 --
246 x_msg_count := FND_MSG_PUB.COUNT_MSG;
247
248 END check_notes_access;
249
250
251 PROCEDURE get_security_predicate
252 ( p_api_version IN NUMBER
253 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
254 , p_object_name IN VARCHAR2 DEFAULT NULL
255 , p_function IN VARCHAR2 DEFAULT NULL
256 , p_grant_instance_type IN VARCHAR2 DEFAULT 'UNIVERSAL'
257 , p_user_name IN VARCHAR2 DEFAULT NULL
258 , p_statement_type IN VARCHAR2 DEFAULT 'OTHER'
259 , p_table_alias IN VARCHAR2 DEFAULT NULL
260 , x_predicate OUT NOCOPY VARCHAR2
261 , x_return_status OUT NOCOPY VARCHAR2
262 , x_msg_count OUT NOCOPY NUMBER
263 , x_msg_data OUT NOCOPY VARCHAR2
264 )
265 IS
266
267 l_api_name CONSTANT VARCHAR2(30) := 'get_security_predicate';
268 l_api_version CONSTANT NUMBER := 1.0;
269 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
270
271 l_msg_data VARCHAR2(2000);
272
273 BEGIN
274 --
275 -- Standard call to check for call compatibility
276 --
277 IF NOT FND_API.Compatible_API_Call( l_api_version
278 , p_api_version
279 , l_api_name
280 , G_PKG_NAME
281 )
282 THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285
286 --
287 -- Initialize message list if p_init_msg_list is set to TRUE
288 --
289 IF FND_API.To_Boolean(p_init_msg_list)
290 THEN
291 FND_MSG_PUB.Initialize;
292 END IF;
293
294 --
295 -- Initialize API return status to success
296 --
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 --
300 -- Call the FND procedure
301 --
302 FND_DATA_SECURITY.get_security_predicate
303 ( p_api_version => 1.0
304 , p_function => p_function
305 , p_object_name => p_object_name
306 , p_grant_instance_type => p_grant_instance_type
307 , p_user_name => p_user_name
308 , p_statement_type => p_statement_type
309 , p_table_alias => p_table_alias
310 , x_predicate => x_predicate
311 , x_return_status => x_return_status
312 );
313
314 IF (x_return_status NOT IN ('T','F'))
315 THEN
316 --
317 -- An error occured
318 --
319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320 ELSE
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322
323 END IF;
324
325 --
326 -- Standard call to get message count and if count is 1, get message info
327 --
328 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
329 , p_data => x_msg_data
330 );
331
332
333 EXCEPTION
334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
335 THEN
336 --
337 -- Set status
338 --
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
340
341 --
342 -- Get error message from FND stack
343 --
344 l_msg_data := FND_MESSAGE.GET;
345
346 --
347 -- Push message onto CRM stack
348 --
349 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
350 , l_api_name
351 , l_msg_data
352 );
353 --
354 -- Count the messages on the CRM stack
355 --
356 x_msg_count := FND_MSG_PUB.COUNT_MSG;
357
358 --
359 -- Reset to NULL because of NOCOPY
360 --
361 x_predicate := NULL;
362
363 WHEN OTHERS
364 THEN
365 --
366 -- Set status
367 --
368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
369
370 --
371 -- Push message onto CRM stack
372 --
373 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
374 , l_api_name
375 , SQLERRM
376 );
377 --
378 -- Count the messages on the CRM stack
379 --
380 x_msg_count := FND_MSG_PUB.COUNT_MSG;
381
382 --
383 -- Reset to NULL because of NOCOPY
384 --
385 x_predicate := NULL;
386
387 END get_security_predicate;
388
389
390 PROCEDURE get_functions
391 ( p_api_version IN NUMBER
392 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
393 , p_object_name IN VARCHAR2
394 , p_instance_pk1_value IN VARCHAR2 DEFAULT NULL
395 , p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
396 , p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
397 , p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
398 , p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
399 , p_user_name IN VARCHAR2 DEFAULT NULL
400 , x_return_status OUT NOCOPY VARCHAR2
401 , x_privilege_tbl OUT NOCOPY FND_DATA_SECURITY.FND_PRIVILEGE_NAME_TABLE_TYPE
402 , x_msg_count OUT NOCOPY NUMBER
403 , x_msg_data OUT NOCOPY VARCHAR2
404 )IS
405
406 l_api_name CONSTANT VARCHAR2(30) := 'get_functions';
407 l_api_version CONSTANT NUMBER := 1.0;
408 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
409
410 l_msg_data VARCHAR2(2000);
411 l_privilege_tbl FND_DATA_SECURITY.FND_PRIVILEGE_NAME_TABLE_TYPE;
412
413 BEGIN
414 --
415 -- Standard call to check for call compatibility
416 --
417 IF NOT FND_API.Compatible_API_Call( l_api_version
418 , p_api_version
419 , l_api_name
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 , G_PKG_NAME
421 )
422 THEN
424 END IF;
425
426 --
427 -- Initialize message list if p_init_msg_list is set to TRUE
428 --
429 IF FND_API.To_Boolean(p_init_msg_list)
430 THEN
431 FND_MSG_PUB.Initialize;
432 END IF;
433
434 --
435 -- Initialize API return status to success
436 --
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 --
440 -- Call the FND procedure
441 --
442 FND_DATA_SECURITY.get_functions
443 ( p_api_version => 1.0
444 , p_object_name => p_object_name
445 , p_instance_pk1_value => p_instance_pk1_value
446 , p_instance_pk2_value => p_instance_pk2_value
447 , p_instance_pk3_value => p_instance_pk3_value
448 , p_instance_pk4_value => p_instance_pk4_value
449 , p_instance_pk5_value => p_instance_pk5_value
450 , p_user_name => p_user_name
451 , x_return_status => x_return_status
452 , x_privilege_tbl => x_privilege_tbl
453 );
454
455 IF (x_return_status NOT IN('T','F'))
456 THEN
457 --
458 -- An error occured
459 --
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 ELSE
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463
464 END IF;
465
466 --
467 -- Standard call to get message count and if count is 1, get message info
468 --
469 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
470 , p_data => x_msg_data
471 );
472
473 EXCEPTION
474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
475 THEN
476 --
477 -- Set status
478 --
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
480
481 --
482 -- Get error message from FND stack
483 --
484 l_msg_data := FND_MESSAGE.GET;
485
486 --
487 -- Push message onto CRM stack
488 --
489 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
490 , l_api_name
491 , l_msg_data
492 );
493 --
494 -- Count the messages on the CRM stack
495 --
496 x_msg_count := FND_MSG_PUB.COUNT_MSG;
497
498 --
499 -- reset to NULL because of NOCOPY
500 --
501 x_privilege_tbl := l_privilege_tbl;
502
503 WHEN OTHERS
504 THEN
505 --
506 -- Set status
507 --
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509
510 --
511 -- Push message onto CRM stack
512 --
513 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
514 , l_api_name
515 , SQLERRM
516 );
517 --
518 -- Count the messages on the CRM stack
519 --
520 x_msg_count := FND_MSG_PUB.COUNT_MSG;
521
522 --
523 -- reset to NULL because of NOCOPY
524 --
525 x_privilege_tbl := l_privilege_tbl;
526
527
528 END get_functions;
529
530 PROCEDURE check_function
531 ( p_api_version IN NUMBER
532 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
533 , p_function IN VARCHAR2
534 , p_object_name IN VARCHAR2
535 , p_instance_pk1_value IN VARCHAR2 DEFAULT NULL
536 , p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
537 , p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
538 , p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
539 , p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
540 , p_user_name IN VARCHAR2 DEFAULT NULL
541 , x_return_status OUT NOCOPY VARCHAR2
542 , x_grant OUT NOCOPY NUMBER -- 1 yes, 0 no
543 , x_msg_count OUT NOCOPY NUMBER
544 , x_msg_data OUT NOCOPY VARCHAR2
545 )
546 IS
547 l_api_name CONSTANT VARCHAR2(30) := 'check_function';
548 l_api_version CONSTANT NUMBER := 1.0;
549 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
550 l_fnd_grant BOOLEAN;
551
552 l_msg_data VARCHAR2(2000);
553
554 BEGIN
555 --
556 -- Standard call to check for call compatibility
557 --
558 IF NOT FND_API.Compatible_API_Call( l_api_version
559 , p_api_version
560 , l_api_name
561 , G_PKG_NAME
562 )
563 THEN
564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
565 END IF;
566
567 --
568 -- Initialize message list if p_init_msg_list is set to TRUE
569 --
570 IF FND_API.To_Boolean(p_init_msg_list)
571 THEN
572 FND_MSG_PUB.Initialize;
573 END IF;
574
575 --
576 -- Initialize API return status to success
577 --
578 x_return_status := FND_API.G_RET_STS_SUCCESS;
579
583 --
580
581 --
582 -- Call the FND procedure
584
585 l_fnd_grant := FND_FUNCTION.test_instance
586 ( function_name => p_function
587 , object_name => p_object_name
588 , instance_pk1_value => p_instance_pk1_value
589 , instance_pk2_value => p_instance_pk2_value
590 , instance_pk3_value => p_instance_pk3_value
591 , instance_pk4_value => p_instance_pk4_value
592 , instance_pk5_value => p_instance_pk5_value
593 , user_name => p_user_name
594 );
595
596
597 IF (l_fnd_grant)
598 THEN
599 x_grant := 1;
600 ELSE
601 x_grant := 0;
602 END IF;
603
604 --
605 -- Standard call to get message count and if count is 1, get message info
606 --
607 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
608 , p_data => x_msg_data
609 );
610
611 EXCEPTION
612 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
613 THEN
614 --
615 -- Something is wrong: no access
616 --
617 x_grant := 0;
618
619 --
620 -- Set status
621 --
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623 --
624 -- Get error message from FND stack
625 --
626 l_msg_data := FND_MESSAGE.GET;
627 --
628 -- Push message onto CRM stack
629 --
630 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
631 , l_api_name
632 , l_msg_data
633 );
634 --
635 -- Count the messages on the CRM stack
636 --
637 x_msg_count := FND_MSG_PUB.COUNT_MSG;
638
639 WHEN OTHERS
640 THEN
641 --
642 -- Something is wrong: no access
643 --
644 x_grant := 0;
645
646 --
647 -- Set status
648 --
649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
650 --
651 -- Push message onto CRM stack
652 --
653 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
654 , l_api_name
655 , SQLERRM
656 );
657 --
658 -- Count the messages on the CRM stack
659 --
660 x_msg_count := FND_MSG_PUB.COUNT_MSG;
661
662 END check_function;
663
664
665 PROCEDURE check_note_type
666 ( p_api_version IN NUMBER
667 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
668 , p_note_type IN VARCHAR2
669 , x_return_status OUT NOCOPY VARCHAR2
670 , x_grant OUT NOCOPY NUMBER -- 1 yes, 0 no
671 , x_msg_count OUT NOCOPY NUMBER
672 , x_msg_data OUT NOCOPY VARCHAR2
673 )
674 IS
675
676 TYPE note_types_cur_type IS REF CURSOR;
677
678 l_api_name CONSTANT VARCHAR2(30) := 'check_note_type';
679 l_api_version CONSTANT NUMBER := 1.0;
680 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
681
682 cv_note_types note_types_cur_type; -- place holder for dynamic cursor
683
684
685 l_query VARCHAR2(4000) := 'SELECT COUNT(1) '
686 ||'FROM FND_LOOKUPS FNS '
687 ||'WHERE FNS.LOOKUP_TYPE = ''JTF_NOTE_TYPE'' '
688 ||'AND FNS.LOOKUP_CODE = :A ';
689
690 l_predicate VARCHAR2(32767);
691
692 BEGIN
693 --
694 -- Standard call to check for call compatibility
695 --
696 IF NOT FND_API.Compatible_API_Call( l_api_version
697 , p_api_version
698 , l_api_name
699 , G_PKG_NAME
700 )
701 THEN
702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
703 END IF;
704
705 --
706 -- Initialize message list if p_init_msg_list is set to TRUE
707 --
708 IF FND_API.To_Boolean(p_init_msg_list)
709 THEN
710 FND_MSG_PUB.Initialize;
711 END IF;
712
713 --
714 -- Initialize API return status to success
715 --
716 x_return_status := FND_API.G_RET_STS_SUCCESS;
717
718 --
719 -- Get the security predicate
720 --
721 get_security_predicate
722 ( p_api_version => 1.0
723 , p_init_msg_list => FND_API.G_FALSE
724 , p_object_name => G_OBJECT_NOTE_TYPE
725 , p_function => G_FUNCTION_TYPE_SELECT
726 , p_statement_type => 'OTHER'
727 , p_table_alias => 'FNS'
728 , x_predicate => l_predicate
729 , x_return_status => x_return_status
730 , x_msg_count => x_msg_count
731 , x_msg_data => x_msg_data
732 );
733
734
735 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
736 THEN
740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 --
738 -- An error occured
739 --
741 END IF;
742
743 --
744 -- No error so we can do the checks
745 --
746 IF (l_predicate = '1=2')
747 THEN
748 --
749 -- this means no grant was given, avoiding the dynamic SQL
750 --
751 x_grant := 0;
752
753 ELSE
754 --
755 -- We got a genuine where clause so we can do the check
756 --
757 l_query := l_query ||' AND '||l_predicate;
758
759 OPEN cv_note_types FOR l_query USING p_note_type;
760
761 FETCH cv_note_types INTO x_grant;
762
763 CLOSE cv_note_types;
764
765 END IF;
766
767 -- Standard call to get message count and if count is 1, get message info
768 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
769 , p_data => x_msg_data
770 );
771
772 EXCEPTION
773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
774 THEN
775 --
776 -- Something is wrong: no access
777 --
778 x_grant := 0;
779
780 IF (cv_note_types%ISOPEN)
781 THEN
782 CLOSE cv_note_types;
783 END IF;
784
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786
787 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
788 , p_data => x_msg_data
789 );
790 WHEN OTHERS
791 THEN
792 --
793 -- Something is wrong: no access
794 --
795 x_grant := 0;
796
797 IF (cv_note_types%ISOPEN)
798 THEN
799 CLOSE cv_note_types;
800 END IF;
801
802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
803
804 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
805 , l_api_name
806 , SQLERRM
807 );
808
809 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
810 , p_data => x_msg_data
811 );
812
813 END check_note_type;
814
815 FUNCTION check_update_sec_access
816 -- --------------------------------------------------------------------------
817 -- Start of notes
818 -- API Name : check_update_sec_access
819 -- Type : Private
820 -- Usage : Check notes access using AOL security
821 -- Version : Initial version 1.0
822 --
823 --
824 -- End of notes
825 -- --------------------------------------------------------------------------
826 ( p_note_id IN NUMBER
827 ) RETURN INTEGER
828 IS
829
830 retAccess INTEGER := 0;
831 l_return_status VARCHAR2(1);
832 l_msg_count NUMBER;
833 l_msg_data VARCHAR2(2000);
834
835 BEGIN
836
837 IF (p_note_id IS NOT NULL)
838 THEN
839 check_function
840 ( p_api_version => 1.0
841 , p_init_msg_list => FND_API.G_FALSE
842 , p_function => G_FUNCTION_UPDATE_SEC
843 , p_object_name => G_OBJECT_NOTE
844 , p_instance_pk1_value => p_note_id
845 , p_instance_pk2_value => NULL
846 , p_instance_pk3_value => NULL
847 , p_instance_pk4_value => NULL
848 , p_instance_pk5_value => NULL
849 , p_user_name => NULL
850 , x_return_status => l_return_status
851 , x_grant => retAccess
852 , x_msg_count => l_msg_count
853 , x_msg_data => l_msg_data
854 );
855
856 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
857 THEN
858 retAccess := 0;
859 END IF;
860 END IF;
861
862 RETURN retAccess;
863
864 END check_update_sec_access;
865
866 FUNCTION check_update_prim_access
867 -- --------------------------------------------------------------------------
868 -- Start of notes
869 -- API Name : check_update_prim_access
870 -- Type : Private
871 -- Usage : Check notes access using AOL security
872 -- Version : Initial version 1.0
873 --
874 --
875 -- End of notes
876 -- --------------------------------------------------------------------------
877 ( p_note_id IN NUMBER
878 ) RETURN INTEGER
879 IS
880
881 retAccess INTEGER := 0;
882 l_return_status VARCHAR2(1);
883 l_msg_count NUMBER;
884 l_msg_data VARCHAR2(2000);
885
886 BEGIN
887
888 IF (p_note_id IS NOT NULL)
889 THEN
890 check_function
891 ( p_api_version => 1.0
892 , p_init_msg_list => FND_API.G_FALSE
893 , p_function => G_FUNCTION_UPDATE_NOTE_DTLS
894 , p_object_name => G_OBJECT_NOTE
895 , p_instance_pk1_value => p_note_id
896 , p_instance_pk2_value => NULL
897 , p_instance_pk3_value => NULL
898 , p_instance_pk4_value => NULL
899 , p_instance_pk5_value => NULL
900 , p_user_name => NULL
901 , x_return_status => l_return_status
902 , x_grant => retAccess
903 , x_msg_count => l_msg_count
904 , x_msg_data => l_msg_data
905 );
906
907 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
908 THEN
909 retAccess := 0;
910 END IF;
911
912 IF (retAccess <> 0)
913 THEN
914 check_function
915 ( p_api_version => 1.0
916 , p_init_msg_list => FND_API.G_FALSE
917 , p_function => G_FUNCTION_UPDATE_NOTE
918 , p_object_name => G_OBJECT_NOTE
919 , p_instance_pk1_value => p_note_id
920 , p_instance_pk2_value => NULL
921 , p_instance_pk3_value => NULL
922 , p_instance_pk4_value => NULL
923 , p_instance_pk5_value => NULL
924 , p_user_name => NULL
925 , x_return_status => l_return_status
926 , x_grant => retAccess
927 , x_msg_count => l_msg_count
928 , x_msg_data => l_msg_data
929 );
930
931 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
932 THEN
933 retAccess := 0;
934 END IF;
935 END IF;
936 END IF;
937
938 RETURN retAccess;
939
940 END check_update_prim_access;
941
942 FUNCTION check_delete_access
943 -- --------------------------------------------------------------------------
944 -- Start of notes
945 -- API Name : check_update_sec_access
946 -- Type : Private
947 -- Usage : Check notes access using AOL security
948 -- Version : Initial version 1.0
949 --
950 --
951 -- End of notes
952 -- --------------------------------------------------------------------------
953 ( p_note_id IN NUMBER
954 ) RETURN INTEGER
955 IS
956
957 retAccess INTEGER := 0;
958 l_return_status VARCHAR2(1);
959 l_msg_count NUMBER;
960 l_msg_data VARCHAR2(2000);
961
962 BEGIN
963
964 IF (p_note_id IS NOT NULL)
965 THEN
966 check_function
967 ( p_api_version => 1.0
968 , p_init_msg_list => FND_API.G_FALSE
969 , p_function => G_FUNCTION_DELETE
970 , p_object_name => G_OBJECT_NOTE
971 , p_instance_pk1_value => p_note_id
972 , p_instance_pk2_value => NULL
973 , p_instance_pk3_value => NULL
974 , p_instance_pk4_value => NULL
975 , p_instance_pk5_value => NULL
976 , p_user_name => NULL
977 , x_return_status => l_return_status
978 , x_grant => retAccess
979 , x_msg_count => l_msg_count
980 , x_msg_data => l_msg_data
981 );
982
983 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
984 THEN
985 retAccess := 0;
986 END IF;
987 END IF;
988
989 RETURN retAccess;
990
991 END check_delete_access;
992
993 END JTF_NOTES_SECURITY_PVT;