1 PACKAGE BODY BIS_DELEGATION_PUB AS
2 /* $Header: BISPDLGB.pls 120.1 2006/08/08 07:48:29 nbarik noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.1=120.1):~PROD:~PATH:~FILE
5
6 /*
7 REM +=======================================================================+
8 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
9 REM | All rights reserved. |
10 REM +=======================================================================+
11 REM | FILENAME |
12 REM | BISPDLGB.pls |
13 REM | |
14 REM | DESCRIPTION |
15 REM | This is the Delegation API Pkg. for PMV. |
16 REM | |
17 REM | HISTORY |
18 REM | jrhyde 06/25/05 Created Enh 4325431 |
19 REM | |
20 REM +=======================================================================+
21 */
22 --
23 -- Constants for the FND_GRANTS when used for PMV delegation
24 g_c_program_name CONSTANT FND_GRANTS.program_name%TYPE := 'BIS_PMV_GRANTS';
25 -- Exceptions
26 g_e_INVALID_DATES EXCEPTION; -- either or both dates not
27 g_e_INVALID_DELEGATION_TYPE EXCEPTION; -- delegate type not defined
28 g_e_INVALID_FND_OBJECT EXCEPTION; -- object not defined
29 g_e_INVALID_INSTANCE EXCEPTION; -- instance is not valid for delegate
30 g_e_INVALID_GRANTEE EXCEPTION; -- grantee not valid
31 g_e_INVALID_MENU_NAME EXCEPTION; -- menu not valid
32 --
33 -- Global table of records to store details of each delegate type
34 -- Note: Need to Populate and stay resident in session memory
35 --
36 -- Define global record type
37 TYPE g_rec_delegate_type_t IS RECORD
38 (parameter1 fnd_grants.parameter1%TYPE -- delegate type
39 ,grantee_type fnd_grants.grantee_type%TYPE
40 ,instance_type fnd_grants.instance_type%TYPE
41 ,object_id fnd_objects.object_id%TYPE --runtime TBD
42 ,object_name fnd_objects.obj_name%TYPE -- grantee object
43 ,object_database_object_name fnd_objects.database_object_name%TYPE
44 ,object_pk1_column_name fnd_objects.pk1_column_name%TYPE --runtime TBD
45 ,object_pk1_column_type fnd_objects.pk1_column_type%TYPE --runtime TBD
46 --unlikely to need but put in for completeness
47 ,object_pk2_column_name fnd_objects.pk2_column_name%TYPE --runtime TBD
48 ,object_pk2_column_type fnd_objects.pk2_column_type%TYPE --runtime TBD
49 -- may want to derive and store the SQL to from:
50 -- object name
51 -- object_pkX_column_name
52 -- object_pkX_column_type
53 -- to validate the grantor
54 -- will not implement for first version where only design for HRI_PER_USRDR
55 -- will use hardcoded SQL
56 --,object_sql_stmt --
57 );
58 -- Define global table / collection of record
59 TYPE g_tbl_delegate_type_t
60 IS TABLE OF g_rec_delegate_type_t
61 INDEX BY fnd_grants.parameter1%TYPE; -- Index by delegate type indentifier
62 -- Define global table of records
63 g_tbl_delegate_type g_tbl_delegate_type_t;
64 --
65 --------------------------------------------------------------------------------
66 --------------------------------------------------------------------------------
67 --***************************PRIVATE FUNCTIIONS*******************************--
68 --------------------------------------------------------------------------------
69 --------------------------------------------------------------------------------
70 --
71 --------------------------------------------------------------------------------
72 -- output
73 --------------------------------------------------------------------------------
74 --
75 -- Simple central point to handle user output messages
76 PROCEDURE output(text IN VARCHAR2)
77 IS
78 BEGIN
79 NULL;
80 -- DBMS_OUTPUT.put_line(text);
81 END;
82 --------------------------------------------------------------------------------
83 -- dbg
84 --------------------------------------------------------------------------------
85 --
86 -- Simple central point to handle debug messages
87 PROCEDURE dbg(text IN VARCHAR2)
88 IS
89 BEGIN
90 NULL;
91 -- output(' DEBUG : '||text);
92 END;
93 --
94 --------------------------------------------------------------------------------
95 -- setup_globals
96 --------------------------------------------------------------------------------
97 PROCEDURE setup_globals
98 IS
99 --
100 l_delegate_type fnd_grants.parameter1%TYPE;
101 --
102 l_object_id fnd_objects.object_id%TYPE;
103 l_object_database_object_name fnd_objects.database_object_name%TYPE;
104 l_object_pk1_column_name fnd_objects.pk1_column_name%TYPE;
105 l_object_pk1_column_type fnd_objects.pk1_column_type%TYPE;
106 l_object_pk2_column_name fnd_objects.pk2_column_name%TYPE;
107 l_object_pk2_column_type fnd_objects.pk2_column_type%TYPE;
108 --
109 CURSOR csr_fnd_objects
110 (cp_obj_name fnd_objects.obj_name%TYPE
111 )
112 IS
113 SELECT o.object_id
114 , o.database_object_name
115 , o.pk1_column_name
116 , o.pk1_column_type
117 , o.pk2_column_name
118 , o.pk2_column_type
119 FROM fnd_objects o
120 WHERE o.obj_name = cp_obj_name
121 ;
122 --
123 BEGIN
124 -- Setup Metatdata
125 dbg(' Setup Globals---------------');
126 -- Currently on HRI_PER_USRDR_H supported
127 g_tbl_delegate_type('HRI_PER_USRDR_H').parameter1 := 'HRI_PER_USRDR_H';
128 g_tbl_delegate_type('HRI_PER_USRDR_H').grantee_type := 'USER';
129 g_tbl_delegate_type('HRI_PER_USRDR_H').object_name := 'HRI_PER';
130 g_tbl_delegate_type('HRI_PER_USRDR_H').instance_type := 'INSTANCE';
131 -- Designed to be extend to multiple types
132 l_delegate_type := 'HRI_PER_USRDR_H';
133 -- query object information
134 BEGIN
135 -- Ensure cursor is closed prior to opening
136 IF csr_fnd_objects%ISOPEN THEN
137 CLOSE csr_fnd_objects;
138 END IF;
139 --
140 OPEN csr_fnd_objects(g_tbl_delegate_type('HRI_PER_USRDR_H').object_name);
141 FETCH csr_fnd_objects INTO
142 l_object_id
143 ,l_object_database_object_name
144 ,l_object_pk1_column_name
145 ,l_object_pk1_column_type
146 ,l_object_pk2_column_name
147 ,l_object_pk2_column_type
148 ;
149 IF (csr_fnd_objects%FOUND) THEN
150 g_tbl_delegate_type(l_delegate_type).object_id
151 :=l_object_id;
152 g_tbl_delegate_type(l_delegate_type).object_pk1_column_name
153 :=l_object_pk1_column_name;
154 g_tbl_delegate_type(l_delegate_type).object_pk1_column_type
155 :=l_object_pk1_column_type;
156 /* Not currently required
157 g_tbl_delegate_type(l_delegate_type).object_pk2_column_name
158 :=l_object_pk2_column_name;
159 g_tbl_delegate_type(l_delegate_type).object_pk2_column_type
160 :=l_object_pk2_column_type;
161 */
162 --
163 dbg(' object_id :'
164 ||g_tbl_delegate_type(l_delegate_type).object_id);
165 dbg(' object_pk1_column_name :'
166 ||g_tbl_delegate_type(l_delegate_type).object_pk1_column_name);
167 dbg(' object_pk1_column_type :'
168 ||g_tbl_delegate_type(l_delegate_type).object_pk1_column_type);
169 ELSE
170 RAISE NO_DATA_FOUND;
171 END IF;
172 CLOSE csr_fnd_objects;
173 --
174 EXCEPTION
175 WHEN OTHERS THEN
176 IF csr_fnd_objects%ISOPEN THEN
177 CLOSE csr_fnd_objects;
178 END IF;
179 RAISE g_e_INVALID_FND_OBJECT;
180 END;
181 --
182 END setup_globals;
183 --
184 --------------------------------------------------------------------------------
185 -- DELEGATE_TYPE_IS_VALID
186 --------------------------------------------------------------------------------
187 FUNCTION delegate_type_is_valid
188 ( p_delegate_type IN VARCHAR2
189 )
190 RETURN BOOLEAN
191 IS
192 l_result BOOLEAN;
193 BEGIN
194 IF p_delegate_type = 'HRI_PER_USRDR_H' THEN
195 l_result := TRUE;
196 ELSE
197 l_result := FALSE;
198 END IF;
199 RETURN l_result;
200 EXCEPTION
201 WHEN others THEN
202 RETURN FALSE;
203 END delegate_type_is_valid;
204 --
205 --------------------------------------------------------------------------------
206 -- GRANTEE_IS_VALID
207 --------------------------------------------------------------------------------
208 FUNCTION grantee_is_valid
209 ( p_delegate_type IN VARCHAR2
210 ,p_grantee_key IN VARCHAR2
211 ,p_start_date IN DATE DEFAULT SYSDATE
212 ,p_end_date IN DATE DEFAULT NULL
213 )
214 RETURN BOOLEAN
215 IS
216 -- varialbe to take teh grantee key if it needs to be converted to a NUMBER
217 l_grantee_key_num NUMBER;
218 -- buffer to get the output of the validation cursor
219 l_crs_op_buf_char VARCHAR2(200);
220 l_result BOOLEAN;
221 CURSOR crs_grantee_test_person
222 ( cp_person_id NUMBER
223 ,cp_date DATE
224 )
225 IS
226 SELECT VALUE
227 FROM hri_cl_per_n_v
228 WHERE ID = cp_person_id
229 AND cp_date BETWEEN effective_start_date AND effective_end_date
230 ;
231 BEGIN
232 dbg(' Grantee_is_valid------------');
233 -- Default result to FALSE.
234 l_result := FALSE;
235 -- this section intended to be replaced by more generic validation
236 -- mechansims based on information stored in the g_tbl_delegate_type
237 IF p_delegate_type = 'HRI_PER_USRDR_H' THEN
238 -- convert varchar2 to number => Person_id
239 -- note if it doesn't convert exception will be thrown suggesting that
240 -- a bad parameter has been passed
241 l_grantee_key_num := TO_NUMBER(p_grantee_key);
242 BEGIN
243 -- Ensure cursor is closed prior to opening
244 IF crs_grantee_test_person%ISOPEN THEN
245 CLOSE crs_grantee_test_person;
246 END IF;
247 --
248 OPEN crs_grantee_test_person(l_grantee_key_num,p_start_date);
249 FETCH crs_grantee_test_person INTO l_crs_op_buf_char;
250 IF crs_grantee_test_person%FOUND THEN
251 dbg(' Person validated :'||l_crs_op_buf_char);
252 l_result := TRUE;
253 ELSE
254 dbg(' Person not validated');
255 l_result := FALSE;
256 END IF;
257 CLOSE crs_grantee_test_person;
258 EXCEPTION
259 WHEN OTHERS THEN
260 dbg(' Cursor exception');
261 IF crs_grantee_test_person%ISOPEN THEN
262 CLOSE crs_grantee_test_person;
263 END IF;
264 END;
265 --
266 ELSE
267 --
268 dbg(' Not a supported delegate type');
269 l_result := FALSE;
270 END IF;
271 RETURN l_result;
272 --
273 EXCEPTION
274 WHEN others THEN
275 RETURN FALSE;
276 END grantee_is_valid;
277 --
278 --------------------------------------------------------------------------------
279 -- INSTANCE_IS_VALID
280 --------------------------------------------------------------------------------
281 -- Tests to determine if instance is valid based
282 FUNCTION instance_is_valid
283 ( p_delegate_type IN VARCHAR2
284 ,p_instance_pk1_value IN VARCHAR2
285 ,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
286 ,p_start_date IN DATE DEFAULT sysdate
287 ,p_end_date IN DATE DEFAULT NULL
288 )
289 RETURN BOOLEAN
290 IS
291 -- variable to take the instance keys if it needs to be converted to a NUMBER
292 l_instance_pk1_num NUMBER;
293 l_instance_pk2_num NUMBER;
294 -- buffer to get the output of the validation cursor
295 l_crs_op_buf_char VARCHAR2(200);
296 -- local variable holding the ultimate result of the function
297 l_result BOOLEAN;
298 --
299 CURSOR crs_instance_test_person
300 ( cp_person_id NUMBER
301 ,cp_date DATE
302 )
303 IS
304 SELECT VALUE
305 FROM hri_cl_per_n_v
309 BEGIN
306 WHERE ID = cp_person_id
307 AND cp_date BETWEEN effective_start_date AND effective_end_date
308 ;
310 dbg(' Instance_is_valid------------');
311 -- Default result to FALSE.
312 l_result := FALSE;
313 -- this section intended to be replaced by more generic validation
314 -- mechansims based on information stored in the g_tbl_delegate_type
315 IF p_delegate_type = 'HRI_PER_USRDR_H' THEN
316 -- convert varchar2 to number => Person_id
317 -- note if it doesn't convert exception will be thrown suggesting that
318 -- a bad parameter has been passed
319 -- Hence test has failed and FALSE returned
320 l_instance_pk1_num := TO_NUMBER(p_instance_pk1_value);
321 BEGIN
322 -- Ensure cursor is closed prior to opening
323 IF crs_instance_test_person%ISOPEN THEN
324 CLOSE crs_instance_test_person;
325 END IF;
326 --
327 OPEN crs_instance_test_person(l_instance_pk1_num,p_start_date);
328 FETCH crs_instance_test_person INTO l_crs_op_buf_char;
329 IF crs_instance_test_person%FOUND THEN
330 dbg(' Person instance validated :'||l_crs_op_buf_char);
331 l_result := TRUE;
332 ELSE
333 dbg(' Person instance not validated');
334 l_result := FALSE;
335 END IF;
336 CLOSE crs_instance_test_person;
337 EXCEPTION
338 WHEN OTHERS THEN
339 dbg(' Cursor exception');
340 IF crs_instance_test_person%ISOPEN THEN
341 CLOSE crs_instance_test_person;
342 END IF;
343 END;
344 --
345 ELSE
346 --
347 dbg(' Not a supported delegate type');
348 l_result := FALSE;
349 END IF;
350 RETURN l_result;
351 EXCEPTION
352 WHEN others THEN
353 RETURN FALSE;
354 END instance_is_valid;
355 --
356 --------------------------------------------------------------------------------
357 -- GET_MENU_ID
358 --------------------------------------------------------------------------------
359 --
360 FUNCTION get_menu_id
361 (p_menu_name IN VARCHAR2
362 )
363 RETURN NUMBER
364 IS
365 --
366 CURSOR csr_get_menu_id(cp_menu_name VARCHAR2)
367 IS
368 SELECT m.menu_id
369 FROM fnd_menus m
370 WHERE m.menu_name =cp_menu_name
371 ;
372 --
373 l_menu_id NUMBER;
374 --
375 BEGIN
376 --
377 dbg(' Get_menu_id-----------------');
378 -- Ensure cursor is closed prior to opening
379 IF csr_get_menu_id%ISOPEN THEN
380 CLOSE csr_get_menu_id;
381 END IF;
382 --
383 OPEN csr_get_menu_id(p_menu_name);
384 FETCH csr_get_menu_id INTO l_menu_id;
385 IF (csr_get_menu_id%NOTFOUND) THEN
386 CLOSE csr_get_menu_id;
387 dbg(' menu not found');
388 RAISE NO_DATA_FOUND;
389 ELSE
390 dbg(' menu found');
391 END IF;
392 CLOSE csr_get_menu_id;
393 --
394 RETURN l_menu_id;
395 EXCEPTION
396 WHEN OTHERS THEN
397 IF csr_get_menu_id%ISOPEN THEN
398 CLOSE csr_get_menu_id;
399 END IF;
400 RAISE g_e_INVALID_MENU_NAME;
401 END;
402 --------------------------------------------------------------------------------
403 -- GRANT_EXISTS
404 --------------------------------------------------------------------------------
405 -- Tests to determine if grant exists
406 FUNCTION grant_exists
407 ( p_delegate_type IN VARCHAR2
408 ,p_grantee_key IN VARCHAR2
409 ,p_instance_pk1_value IN VARCHAR2
410 ,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
411 ,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
412 ,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
413 ,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
414 ,p_start_date IN DATE DEFAULT sysdate
415 ,p_end_date IN DATE DEFAULT NULL
416 ,p_menu_id IN NUMBER
417 ,x_grant_guid OUT NOCOPY RAW
418 )
419 RETURN BOOLEAN
420 IS
421 l_result BOOLEAN;
422 --
423 CURSOR csr_grant_exits
424 (cp_grantee_type IN VARCHAR2
425 ,cp_grantee_key IN VARCHAR2
426 ,cp_menu_id IN NUMBER
427 ,cp_start_date IN DATE DEFAULT SYSDATE
428 ,cp_end_date IN DATE DEFAULT NULL
429 ,cp_object_id IN NUMBER
430 ,cp_instance_pk1_value IN VARCHAR2
431 ,cp_instance_pk2_value IN VARCHAR2 DEFAULT NULL
432 ,cp_parameter1 IN VARCHAR2
433 )
434 IS
435 SELECT g.grant_guid
436 FROM fnd_grants g
437 WHERE g.grantee_type = cp_grantee_type
438 AND g.grantee_key = cp_grantee_key
439 AND g.menu_id = cp_menu_id
440 AND (cp_end_date IS NULL
441 OR g.start_date <= cp_end_date)
445 AND g.instance_pk1_value = cp_instance_pk1_value
442 AND (g.end_date IS NULL
443 OR cp_start_date <= g.end_date)
444 AND g.object_id = cp_object_id
446 AND ( cp_instance_pk2_value IS NULL
447 OR cp_instance_pk2_value = g.instance_pk2_value)
448 AND g.parameter1 = cp_parameter1
449 AND g.program_name = g_c_program_name
450 ;
451 l_grant_guid fnd_grants.grant_guid%TYPE;
452 --
453 BEGIN
454 --
455 dbg(' Grant_Exists----------------');
456 dbg(' grantee_type :'
457 ||g_tbl_delegate_type(p_delegate_type).grantee_type);
458 dbg(' grantee_key :'||p_grantee_key);
459 dbg(' menu_id :'||to_char(p_menu_id));
460 dbg(' start_date :'||to_char(p_start_date));
461 dbg(' end_date :'||TO_CHAR(p_end_date));
462 dbg(' object_id :'
463 ||g_tbl_delegate_type(p_delegate_type).object_id);
464 dbg(' instance_pk1_value :'||p_instance_pk1_value);
465 dbg(' instance_pk2_value :'||p_instance_pk2_value);
466 dbg(' parameter1 :'
467 ||g_tbl_delegate_type(p_delegate_type).parameter1);
468 dbg(' program_name :'||g_c_program_name);
469 -- Check that the cursors is not already open
470 IF csr_grant_exits%ISOPEN THEN
471 CLOSE csr_grant_exits;
472 END IF;
473 --
474 OPEN csr_grant_exits
475 (cp_grantee_type
476 =>g_tbl_delegate_type(p_delegate_type).grantee_type
477 ,cp_grantee_key => p_grantee_key
478 ,cp_menu_id => p_menu_id
479 ,cp_start_date => p_start_date
480 ,cp_end_date => p_end_date
481 ,cp_object_id
482 =>g_tbl_delegate_type(p_delegate_type).object_id
483 ,cp_instance_pk1_value => p_instance_pk1_value
484 ,cp_instance_pk2_value => p_instance_pk2_value
485 ,cp_parameter1
486 =>g_tbl_delegate_type(p_delegate_type).parameter1
487 );
488 FETCH csr_grant_exits INTO l_grant_guid;
489 dbg(' grat_guid :'||TO_CHAR(l_grant_guid));
490 IF (csr_grant_exits%NOTFOUND) THEN
491 l_result := FALSE;
492 ELSE
493 x_grant_guid := l_grant_guid;
494 l_result := TRUE;
495 END IF;
496 --
497 CLOSE csr_grant_exits;
498 --
499 RETURN l_result;
500 --
501 EXCEPTION
502 WHEN others THEN
503 IF csr_grant_exits%ISOPEN THEN
504 CLOSE csr_grant_exits;
505 END IF;
506 RETURN FALSE;
507 END grant_exists;
508 --
509 --------------------------------------------------------------------------------
510 -- UPDATE_DELEGATE_GRANTS
511 --------------------------------------------------------------------------------
512 -- Updates a number of grants for a delegate type and instance across:
513 -- grantees
514 -- menus
515 -- Decision to not make it across instance was for security and performance
516 -- No retrospective transactions, all transactions must be for either present
517 -- or future delegations.
518 -- 2 modes of operation - p_update_mode:
519 -- REVOKE - trims the grants down to the end_date if the records are larger
520 -- EXTEND - extends the grants up to the end_date if records are smaller
521 --------------------------------------------------------------------------------
522 PROCEDURE update_delegation_grants
523 ( p_delegate_type IN VARCHAR2
524 ,p_grantee_key IN VARCHAR2 DEFAULT NULL
525 ,p_instance_pk1_value IN VARCHAR2
526 ,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
527 ,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
528 ,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
529 ,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
530 ,p_start_date IN DATE DEFAULT SYSDATE
531 ,p_end_date IN DATE DEFAULT SYSDATE
532 ,p_menu_id IN NUMBER DEFAULT NULL
533 ,x_success OUT NOCOPY VARCHAR /* Boolean */
534 ,x_errorcode OUT NOCOPY VARCHAR2
535 ,p_update_mode IN VARCHAR2 DEFAULT 'EXTEND'
536 )
537 IS
538 --
539 l_result BOOLEAN;
540 -- buffer for FND API success code
541 l_success VARCHAR(1);
542 --
543 l_cntr NUMBER := 0;
544 --
545 -- Cursor parameters
546 l_cp_start_date DATE;
547 l_cp_end_date DATE;
548 -- Local to a record in the cursor loop
549 l_start_date DATE;
550 l_end_date DATE;
551 -- Cursor to do a search of all existing delegations
552 -- matching criteria
553 -- Note: Order by required to order by date
554 --- to allow adjustments of concurrent periods
555 CURSOR csr_grant
556 (cp_grantee_type IN VARCHAR2
557 ,cp_grantee_key IN VARCHAR2 DEFAULT NULL
558 ,cp_menu_id IN NUMBER DEFAULT NULL
559 ,cp_start_date IN DATE DEFAULT NULL
560 ,cp_end_date IN DATE DEFAULT NULL
561 ,cp_object_id IN NUMBER
562 ,cp_instance_pk1_value IN VARCHAR2
563 ,cp_instance_pk2_value IN VARCHAR2 DEFAULT NULL
564 ,cp_parameter1 IN VARCHAR2
565 )
566 IS
567 SELECT g.grant_guid grant_guid
568 , g.START_DATE start_date
569 , g.end_date end_date
570 , g.program_name
574 , g.object_id
571 , g.grantee_type
572 , g.grantee_key
573 , g.menu_id
575 , g.instance_pk1_value
576 , g.instance_pk2_value
577 , g.parameter1
578 FROM fnd_grants g
579 WHERE g.grantee_type = cp_grantee_type
580 AND (cp_grantee_key IS NULL
581 OR g.grantee_key = cp_grantee_key)
582 AND g.menu_id = cp_menu_id
583 AND (cp_end_date IS NULL
584 OR g.start_date <= cp_end_date)
585 AND (g.end_date IS NULL
586 OR cp_start_date <= g.end_date)
587 AND g.object_id = cp_object_id
588 AND g.instance_pk1_value = cp_instance_pk1_value
589 AND ( cp_instance_pk2_value IS NULL
590 OR cp_instance_pk2_value = g.instance_pk2_value)
591 AND g.parameter1 = cp_parameter1
592 AND g.program_name = g_c_program_name
593 ORDER BY g.parameter1
594 ,g.program_name
595 ,g.menu_id
596 ,g.grantee_key
597 ,g.instance_pk1_value
598 ,g.start_date
599 ;
600 --
601 -- Previous record details
602 c_rec_prev csr_grant%ROWTYPE;
603 --
604 BEGIN
605 --
606 x_success := FND_API.G_FALSE;
607 x_errorcode:= NULL; --meaning nothing done
608 dbg(' Update_Delegate_Grants------');
609 dbg(' p_mode_type :'||p_update_mode);
610 dbg(' p_start_date :'||to_char(p_start_date));
611 dbg(' p_end_date :'||TO_CHAR(p_end_date));
612 -- Prepare cursor date parameters
613 ---Start_date must be sysdate or greater
614 --- Otherwise default to sysdate
615 IF p_start_date >= TRUNC(SYSDATE) THEN
616 l_cp_start_date := p_start_date;
617 ELSE
618 l_cp_start_date := TRUNC(SYSDATE);
619 END IF;
620 ---End_date must be NULL = meaning EOT
621 --- OR sysdate or greater
622 --- Otherwise default to sysdate
623 IF ( p_end_date IS NULL
624 OR p_end_date >= SYSDATE) THEN
625 l_cp_end_date := p_end_date;
626 ELSE
627 l_cp_end_date := TRUNC(SYSDATE);
628 END IF;
629 -- End_date >= Start_date
630 -- Leave to the FND_GRATS API to handle
631 dbg(' Cursor Parameters ->');
632 dbg(' grantee_type :'
633 ||g_tbl_delegate_type(p_delegate_type).grantee_type);
634 dbg(' grantee_key :'||p_grantee_key);
635 dbg(' menu_id :'||to_char(p_menu_id));
636 dbg(' cp_start_date :'||to_char(l_cp_start_date));
637 dbg(' cp_end_date :'||TO_CHAR(l_cp_end_date));
638 dbg(' object_id :'
639 ||g_tbl_delegate_type(p_delegate_type).object_id);
640 dbg(' instance_pk1_value :'||p_instance_pk1_value);
641 dbg(' instance_pk2_value :'||p_instance_pk2_value);
642 dbg(' parameter1 :'
643 ||g_tbl_delegate_type(p_delegate_type).parameter1);
644 dbg(' program_name :'||g_c_program_name);
645 --
646 -- Loop through all the results returned by the cursor and update
647 -- all records that are valid within the start_date and end_date window
648 dbg(' Loop through records matching search criteria');
649 -- Check that cursor is not already open
650 IF csr_grant%ISOPEN THEN
651 CLOSE csr_grant;
652 END IF;
653 --
654 FOR c_rec IN csr_grant
655 (cp_grantee_type
656 =>g_tbl_delegate_type(p_delegate_type).grantee_type
657 ,cp_grantee_key => p_grantee_key
658 ,cp_menu_id => p_menu_id
659 ,cp_start_date => l_cp_start_date
660 ,cp_end_date => l_cp_end_date
661 ,cp_object_id
662 =>g_tbl_delegate_type(p_delegate_type).object_id
663 ,cp_instance_pk1_value => p_instance_pk1_value
664 ,cp_instance_pk2_value => p_instance_pk2_value
665 ,cp_parameter1
666 =>g_tbl_delegate_type(p_delegate_type).parameter1
667 )
668 LOOP
669 --
670 dbg(' loop_number :'||to_char(l_cntr+1));
671 dbg(' record grant_guid :'||to_char(c_rec.grant_guid));
672 dbg(' record start_date :'||to_char(c_rec.start_date));
673 dbg(' record end_date :'||TO_CHAR(c_rec.end_date));
674 -- Update logic
675 -- EXTEND mode
676 -- If the existing record is a subset of the new period then extend
677 -- REVOKE mode
678 -- If the specified record is a subset of the new period then revoke
679 --
680 -- Extend tests:
681 -- 1. Check to determine if this is not the second delegation record
682 -- where all criteria match except for dates. In this case need to
683 -- merge records based on previously updated records
684 -- 2. Extend End Date -> New End_date > Existing record End_date
685 -- needs to account for NULL = End of Time
686 -- 3. Bring forward start -> New Start_date < Existing record Start_date
687 -- any tests for valid start date needs to have occured prior to this
688 -- 4. Check for identical records
689 -- do nothing but record as success to prevent new record
690 -- 5. Check for records already extending beyond parameters of this update
691 -- again do nothing but record as success to prevent new record
692 IF (p_update_mode = 'EXTEND') THEN
693 IF ( --1.
694 c_rec.program_name = c_rec_prev.program_name
695 AND c_rec.parameter1 = c_rec_prev.parameter1
696 AND c_rec.object_id = c_rec_prev.object_id
697 AND c_rec.grantee_type = c_rec_prev.grantee_type
698 AND c_rec.grantee_key = c_rec_prev.grantee_key
699 AND c_rec.menu_id = c_rec_prev.menu_id
700 AND c_rec.instance_pk1_value = c_rec_prev.instance_pk1_value
701 AND NVL(c_rec.instance_pk2_value,'X')
702 = NVL(c_rec_prev.instance_pk2_value,'X')
703 )
704 THEN
705 -- At this point a record has already been extended
706 -- and this record is duplicating a period of the grant
707 -- hence need to determine 2 different cases
708 -- A. current record is entirely overlapped by prev new record
709 --- in this case need to delete duplicate record
710 -- B. current record partially overlapped by prev. record
711 --- in this case need to update the record
712 --
713 dbg(' Overlapping delegation grant found');
714 dbg(' program_name :'||c_rec.program_name);
715 dbg(' parameter1 :'||c_rec.parameter1);
716 dbg(' object_id :'||c_rec.object_id);
717 dbg(' grantee_type :'||c_rec.grantee_type);
718 dbg(' grantee_key :'||c_rec.grantee_key);
719 dbg(' instance_pk1_value :'||c_rec.instance_pk1_value);
720 dbg(' instance_pk2_value :'||c_rec.instance_pk2_value);
721 dbg(' menu_id :'||to_char(c_rec.menu_id));
722 dbg(' grant_guid :'||to_char(c_rec.grant_guid));
723 -- A.
724 IF( --A.
725 l_cp_end_date IS NULL
726 --Note if c_rec.end_date is NULL=>EOT then this will be false
727 OR c_rec.end_date <= l_cp_end_date
728 )
729 THEN -- delete
730 dbg(' deleting completely overlapped grant....');
731 fnd_grants_pkg.revoke_grant
732 (p_api_version =>1.0 --IN NUMBER,
733 ,p_grant_guid =>c_rec.grant_guid --IN raw,
734 ,x_success =>x_success --OUT NOCOPY VARCHAR2,
735 ,x_errorcode =>x_errorcode --OUT NOCOPY NUMBER
736 );
737 dbg(' ....deleting grant complete');
738 dbg(' API success result :'||x_success);
739 -- Add formated output comments
740 IF x_success = FND_API.G_TRUE THEN
741 dbg('Record Deleted');
742 dbg(' -grant_guid :'||to_char(c_rec.grant_guid));
743 END IF;
744 ELSE --B.
745 -- c_rec.end_date > l_cp_end_date
746 dbg(' updatting partially overlapped grant....');
747 l_start_date := l_cp_end_date + 1;
748 l_end_date := c_rec.end_date;
749 dbg(' new start_date :'||to_char(l_start_date));
750 fnd_grants_pkg.update_grant
751 (p_api_version => 1.0
752 ,p_grant_guid => c_rec.grant_guid
753 ,p_start_date => l_start_date
754 ,p_end_date => l_end_date
755 ,p_name => NULL
756 ,p_description => 'BIS_DELEGATION API -> EXTEND'
757 ,x_success => l_success);
758 dbg(' ...updatting grant completed');
759 dbg(' API success result :'||x_success);
760 -- Add formated output comments
761 IF x_success = FND_API.G_TRUE THEN
762 dbg('Record Updated');
766 --
763 dbg(' -grant_guid :'||to_char(c_rec.grant_guid));
764 END IF;
765 END IF;
767 ELSIF ( --2.
768 l_cp_end_date > c_rec.end_date
769 OR (l_cp_end_date IS NULL AND c_rec.end_date IS NOT NULL)
770 --3.
771 OR l_cp_start_date < c_rec.START_DATE
772 )
773 THEN
774 dbg(' updating grant....');
775 --Note only moving start date back to earliest of sysdate
776 l_start_date := LEAST(l_cp_start_date,c_rec.START_DATE);
777 l_end_date := GREATEST(l_cp_end_date,c_rec.end_date);
778 dbg(' new start date :'||TO_CHAR(l_start_date));
779 dbg(' new end date :'||TO_CHAR(l_end_date));
780 fnd_grants_pkg.update_grant
781 (p_api_version => 1.0
782 ,p_grant_guid => c_rec.grant_guid
783 ,p_start_date => l_start_date
784 ,p_end_date => l_end_date
785 ,p_name => NULL
786 ,p_description => 'BIS_DELEGATION API -> EXTEND'
787 ,x_success => l_success);
788 dbg(' ....updating grant complete');
789 dbg(' API success result :'||l_success);
790 -- Add formated output comments
791 IF x_success = FND_API.G_TRUE THEN
792 dbg('Record Updated');
793 dbg(' -grant_guid :'||to_char(c_rec.grant_guid));
794 END IF;
795 ELSIF ( --4.
796 ( l_cp_end_date = c_rec.end_date
797 OR (l_cp_end_date IS NULL AND c_rec.end_date IS NULL) )
798 AND l_cp_start_date = c_rec.START_DATE
799 )
800 THEN
801 dbg(' Existing record found with same details as request');
802 dbg(' -> No need for any record changes');
803 x_success := FND_API.G_TRUE;
804 ELSIF ( --5.
805 ( l_cp_end_date <= c_rec.end_date
806 OR (l_cp_end_date IS NULL AND c_rec.end_date IS NOT NULL) )
807 AND l_cp_start_date >= c_rec.START_DATE
808 )
809 THEN
810 dbg(' Existing record a super set of request');
811 dbg(' -> No need for any record changes');
812 x_success := FND_API.G_TRUE;
813 END IF;
814 --
815 -- Revoke tests:
816 -- 1. Existing record date range must be outside modified record dates
817 -- 2. Check if record has already started -> Change end date
818 -- Otherwise delete the dlegation
819 ELSIF (p_update_mode = 'REVOKE'
820 -- 1.
821 AND( ( c_rec.end_date IS NULL
822 OR l_cp_end_date < c_rec.end_date
823 )
824 OR l_cp_start_date > c_rec.START_DATE
825 )
826 )
827 THEN
828 -- 2.
829 IF c_rec.START_DATE <= TRUNC(SYSDATE) THEN
830 -- If the grant has started then update the end date
831 -- Note only changing the end date
832 dbg(' revoking grant....');
833 -- Calculate dates for new record
834 --- Start Date is unchanged
835 l_start_date := c_rec.START_DATE;
836 --- End Date modified based on
837 ---- 1. start_date=NULL then use SYSDATE-1 (BIS implementation)
838 ---- 2. start of revoke window.
839 IF p_start_date IS NULL THEN
840 l_end_date := TRUNC(SYSDATE - 1);
841 ELSE
842 l_end_date := LEAST(l_cp_start_date
843 ,NVL(c_rec.end_date,l_cp_start_date)
844 );
845 END IF;
846 --
847 dbg(' new end date :'||TO_CHAR(l_end_date));
848 fnd_grants_pkg.update_grant
849 (p_api_version => 1.0
850 ,p_grant_guid => c_rec.grant_guid
851 ,p_start_date => l_start_date
852 ,p_end_date => l_end_date
853 ,p_name => NULL
854 ,p_description => 'BIS_DELEGATION API -> REVOKE'
855 ,x_success => l_success);
856 dbg(' ....revoking grant complete');
857 dbg(' success result:'||l_success);
858 -- Add formated output comments
859 IF x_success = FND_API.G_TRUE THEN
860 dbg('Record Updated');
861 dbg(' -grant_guid :'||to_char(c_rec.grant_guid));
862 END IF;
863 ELSE
864 -- Else record has not come into usage as it's future
865 -- so delete
866 dbg(' deleting grant....');
867 fnd_grants_pkg.revoke_grant
868 (p_api_version =>1.0 --IN NUMBER,
869 ,p_grant_guid =>c_rec.grant_guid --IN raw,
870 ,x_success =>x_success --OUT NOCOPY VARCHAR2, /* Boolean */
871 ,x_errorcode =>x_errorcode --OUT NOCOPY NUMBER
872 );
873 dbg(' ....deleting grant complete');
874 dbg(' success result:'||x_success);
875 -- Add formated output comments
876 IF x_success = FND_API.G_TRUE THEN
877 dbg('Record Deleted');
878 dbg(' -grant_guid :'||to_char(c_rec.grant_guid));
879 END IF;
880 END IF;
881 ELSE
882 -- Row not updated
883 -- this is valid and does not require an exception at this point
884 -- calling method to determine how to handle
885 dbg(' Record found but does not required update or delete');
886 x_success := FND_API.G_TRUE;
887 END IF;
888 -- increment loop counter
889 l_cntr:=l_cntr+1;
893 THEN
890 -- Update success output flag if a row updated/revoked successfully
891 IF ( x_success = FND_API.G_TRUE
892 OR l_success = FND_API.G_TRUE)
894 x_success := FND_API.G_TRUE;
895 END IF;
896 dbg(' x_success :'||x_success);
897 --
898 -- Take a copy of the current record
899 c_rec_prev:=c_rec;
900 END LOOP;
901 dbg(' Update success result :'||x_success);
902 --
903 -- Deliberately have not put exception here to ensure that un-forseen
904 -- exceptions are propagated to calling method and out to calling code
905 --
906 END update_delegation_grants;
907 --------------------------------------------------------------------------------
908 --------------------------------------------------------------------------------
909 --***************************PUBLIC FUNCTIIONS********************************--
910 --------------------------------------------------------------------------------
911 --------------------------------------------------------------------------------
912 --
913 --------------------------------------------------------------------------------
914 -- GRANT_DELEGATE_FUNCTION
915 --------------------------------------------------------------------------------
916 PROCEDURE grant_delegation
917 ( p_delegate_type IN VARCHAR2
918 ,p_grantee_key IN VARCHAR2
919 ,p_instance_pk1_value IN VARCHAR2
920 ,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
921 ,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
922 ,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
923 ,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
924 ,p_start_date IN DATE DEFAULT NULL
925 ,p_end_date IN DATE DEFAULT NULL
926 ,p_menu_name IN VARCHAR2
927 ,x_grant_guid OUT NOCOPY RAW /*fnd_grants pk*/
928 ,x_success OUT NOCOPY VARCHAR /* Boolean */
929 ,x_errorcode OUT NOCOPY VARCHAR2
930 )
931 IS
932 -- Local variables
933 -- fnd_grants PK used if need to do update instead of a new record
934 l_grant_guid fnd_grants.grant_guid%TYPE;
935 -- local parameters for fnd_grants API
936 l_start_date DATE;
937 l_end_date DATE;
938 l_menu_id NUMBER; --menu_id for menu name
939 --
940 BEGIN
941 --
942 dbg('Grant_delegation--------------');
943 dbg(' p_delegate_type :'||p_delegate_type);
944 dbg(' p_grantee_key :'||p_grantee_key);
945 dbg(' p_instance_pk1_value :'||p_instance_pk1_value);
946 dbg(' p_instance_pk2_value :'||p_instance_pk2_value);
947 dbg(' p_instance_pk3_value :'||p_instance_pk3_value);
948 dbg(' p_instance_pk4_value :'||p_instance_pk4_value);
949 dbg(' p_instance_pk5_value :'||p_instance_pk5_value);
950 dbg(' p_start_date :'||to_char(p_start_date));
951 dbg(' p_end_date :'||to_char(p_end_date));
952 dbg(' p_menu_name :'||p_menu_name);
953 -- Initialize globals
954 setup_globals;
955 -- get menu_id for name passed in
956 l_menu_id := get_menu_id(p_menu_name);
957 dbg(' menu_id :'||TO_CHAR(l_menu_id));
958 -- Validate parameters
959 --Note do not need to validate role or menu as this is done by fnd API
960 -- Step 1. validate date
961 --- Ensure Start_date < End_date
962 --- Ensure dates are not prior to sysdate (rewritting history)
963 --- Truncating all dates
964 --- Have to validate first as these dates used for other validation steps
965 dbg(' Check Grant Dates-----------');
966 IF TRUNC(p_start_date) > TRUNC(p_end_date) THEN
967 --
968 dbg('RAISE:g_e_INVALID_DATES');
969 RAISE g_e_INVALID_DATES;
970 ELSIF ( p_start_date IS NULL
971 OR TRUNC(p_start_date) < TRUNC(SYSDATE))
972 THEN
973 l_start_date := TRUNC(SYSDATE);
974 ELSE
975 l_start_date := TRUNC(p_start_date);
976 END IF;
977 --
978 IF ( p_end_date IS NULL
979 OR TRUNC(p_end_date) >= TRUNC(SYSDATE))
980 THEN
981 l_end_date := TRUNC(p_end_date);
982 ELSE
983 l_end_date := TRUNC(SYSDATE);
984 END IF;
985 dbg(' l_start_date :'||to_char(l_start_date));
986 dbg(' l_end_date :'||to_char(l_end_date));
987 --
988 -- Step 2. check that delegate type is supported
989 --
990 IF NOT delegate_type_is_valid(p_delegate_type) THEN
991 dbg('RAISE:g_e_INVALID_DELEGATION_TYPE');
992 RAISE g_e_INVALID_DELEGATION_TYPE;
993 END IF;
994 -- Step 2. check that grantee is a valid value for delegate type
995 IF NOT grantee_is_valid
996 (p_delegate_type
997 ,p_grantee_key
998 ,l_start_date
999 ,l_end_date)
1000 THEN
1001 dbg('RAISE:g_e_INVALID_GRANTEE');
1002 RAISE g_e_INVALID_GRANTEE;
1003 END IF;
1004 --
1005 -- Step 3. check that instance parameters are valid
1006 --
1007 IF NOT instance_is_valid
1008 (p_delegate_type
1009 ,p_instance_pk1_value
1010 ,p_instance_pk2_value
1011 ,l_start_date
1012 ,l_end_date)
1013 THEN
1014 dbg('RAISE:g_e_INVALID_INSTANCE');
1015 RAISE g_e_INVALID_INSTANCE;
1016 END IF;
1017 --
1018 -- Step 4. Implement delegation
1019 --- First Check if an over lapping grant record exists by trying an update
1020 update_delegation_grants
1024 --,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
1021 ( p_delegate_type =>p_delegate_type--IN VARCHAR2
1022 ,p_grantee_key =>p_grantee_key--IN VARCHAR2 DEFAULT NULL
1023 ,p_instance_pk1_value =>p_instance_pk1_value--IN VARCHAR2
1025 --,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
1026 --,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
1027 --,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
1028 ,p_start_date =>l_start_date--IN DATE DEFAULT SYSDATE
1029 ,p_end_date =>l_end_date--IN DATE DEFAULT SYSDATE
1030 ,p_menu_id =>l_menu_id--IN NUMBER DEFAULT NULL
1031 ,x_success =>x_success--OUT NOCOPY VARCHAR /* Boolean */
1032 ,x_errorcode =>x_errorcode--OUT NOCOPY NUMBER
1033 ,p_update_mode =>'EXTEND'--IN VARCHAR2 DEFAULT 'EXTEND'
1034 );
1035 ---- Second if the update failed then do an insert
1036 IF x_success = FND_API.G_FALSE THEN
1037 -- Note: grant function api does a number of parameter checks:
1038 -- menu
1039 -- object
1040 dbg(' no matching records found');
1041 dbg(' granting function.....');
1042 fnd_grants_pkg.grant_function
1043 (p_api_version =>1.0 --IN NUMBER,
1044 ,p_menu_name =>p_menu_name--IN VARCHAR2,
1045 ,p_object_name --IN VARCHAR2,
1046 =>g_tbl_delegate_type(p_delegate_type).object_name
1047 ,p_instance_type --IN VARCHAR2,
1048 =>g_tbl_delegate_type(p_delegate_type).instance_type
1049 --,p_instance_set_id IN NUMBER DEFAULT NULL,
1050 ,p_instance_pk1_value =>p_instance_pk1_value--IN VARCHAR2 DEFAULT NULL,
1051 --,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
1052 --,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
1053 --,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
1054 --,p_instance_pk5_value --IN VARCHAR2 DEFAULT NULL,
1055 ,p_grantee_type --IN VARCHAR2 DEFAULT 'USER',
1056 =>g_tbl_delegate_type(p_delegate_type).grantee_type
1057 ,p_grantee_key =>p_grantee_key--IN VARCHAR2,
1058 ,p_start_date =>l_start_date--IN DATE,
1059 ,p_end_date =>l_end_date--IN DATE,
1060 ,p_program_name =>g_c_program_name--IN VARCHAR2 DEFAULT NULL,
1061 --,p_program_tag IN VARCHAR2 DEFAULT NULL,
1062 ,x_grant_guid =>x_grant_guid--OUT NOCOPY RAW,
1063 ,x_success =>x_success--OUT NOCOPY VARCHAR, /* Boolean */
1064 ,x_errorcode =>x_errorcode--OUT NOCOPY NUMBER,
1065 ,p_parameter1 --IN VARCHAR2 DEFAULT NULL,
1066 =>g_tbl_delegate_type(p_delegate_type).parameter1
1067 --,p_parameter2 IN VARCHAR2 DEFAULT NULL,
1068 --,p_parameter3 IN VARCHAR2 DEFAULT NULL,
1069 --,p_parameter4 IN VARCHAR2 DEFAULT NULL,
1070 --,p_parameter5 IN VARCHAR2 DEFAULT NULL,
1071 --,p_parameter6 IN VARCHAR2 DEFAULT NULL,
1072 --,p_parameter7 IN VARCHAR2 DEFAULT NULL,
1073 --,p_parameter8 IN VARCHAR2 DEFAULT NULL,
1074 --,p_parameter9 IN VARCHAR2 DEFAULT NULL,
1075 --,p_parameter10 IN VARCHAR2 DEFAULT NULL,
1076 --,p_ctx_secgrp_id IN NUMBER default -1,
1077 --,p_ctx_resp_id IN NUMBER default -1,
1078 --,p_ctx_resp_appl_id IN NUMBER default -1,
1079 --,p_ctx_org_id IN NUMBER default -1,
1080 --,p_name in VARCHAR2 default null,
1081 ,p_description =>'BIS_DELEGATION API -> NEW'
1082 --in VARCHAR2 default null
1083 );
1084 dbg(' ....grant function complete');
1085 dbg(' success result:'||x_success);
1086 -- Add formated output comments
1087 IF x_success = FND_API.G_TRUE THEN
1088 dbg('Record Created');
1089 dbg(' -grant_guid :'||to_char(x_grant_guid));
1090 END IF;
1091 END IF;
1092 --
1093 EXCEPTION
1094 WHEN g_e_INVALID_DATES THEN
1095 x_success :='F';
1096 x_errorcode := 'INVALID_DATES';
1097 WHEN g_e_INVALID_DELEGATION_TYPE THEN
1098 x_success :='F';
1099 x_errorcode := 'INVALID_DELEGATION_TYPE';
1100 WHEN g_e_INVALID_FND_OBJECT THEN
1101 x_success :='F';
1102 x_errorcode := 'INVALID_FND_OBJECT';
1103 WHEN g_e_INVALID_INSTANCE THEN
1104 x_success :='F';
1105 x_errorcode := 'INVALID_INSTANCE';
1106 WHEN g_e_INVALID_GRANTEE THEN
1107 x_success :='F';
1108 x_errorcode := 'INVALID_GRANTEE';
1109 WHEN g_e_INVALID_MENU_NAME THEN
1110 x_success :='F';
1111 x_errorcode := 'INVALID_MENU_NAME';
1112 END grant_delegation;
1113 --
1114 --------------------------------------------------------------------------------
1115 -- REVOKE_DELEGATE_FUNCTION
1116 --------------------------------------------------------------------------------
1117 PROCEDURE revoke_delegation
1118 ( p_delegate_type IN VARCHAR2
1119 ,p_grantee_key IN VARCHAR2 DEFAULT NULL
1120 ,p_instance_pk1_value IN VARCHAR2
1121 ,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
1122 ,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
1123 ,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
1124 ,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
1125 ,p_start_date IN DATE DEFAULT NULL
1126 ,p_end_date IN DATE DEFAULT NULL
1127 ,p_menu_name IN VARCHAR2 DEFAULT NULL
1128 ,x_success OUT NOCOPY VARCHAR /* Boolean */
1129 ,x_errorcode OUT NOCOPY VARCHAR2
1130 )
1131 IS
1132 --
1133 l_start_date DATE;
1134 l_end_date DATE;
1135 l_menu_id NUMBER; --menu_id for menu name
1136 BEGIN
1137 --
1138 dbg('Revoke_delegaton----------------');
1139 dbg(' p_delegate_type :'||p_delegate_type);
1140 dbg(' p_grantee_key :'||p_grantee_key);
1141 dbg(' p_instance_pk1_value :'||p_instance_pk1_value);
1142 dbg(' p_instance_pk2_value :'||p_instance_pk2_value);
1143 dbg(' p_instance_pk3_value :'||p_instance_pk3_value);
1144 dbg(' p_instance_pk4_value :'||p_instance_pk4_value);
1145 dbg(' p_instance_pk5_value :'||p_instance_pk5_value);
1146 dbg(' p_start_date :'||to_char(p_start_date));
1147 dbg(' p_end_date :'||to_char(p_end_date));
1148 dbg(' p_menu_name :'||p_menu_name);
1149 -- Initialize globals
1150 setup_globals;
1151 -- get menu_id for name passed in
1152 l_menu_id := get_menu_id(p_menu_name);
1153 dbg(' menu_id :'||TO_CHAR(l_menu_id));
1154 -- Validate parameters
1155 --Note do not need to validate role or menu as this is done by fnd API
1156 -- Step 1. check that delegate type is supported
1157 IF NOT delegate_type_is_valid(p_delegate_type)
1158 THEN
1159 dbg('RAISE:g_e_INVALID_DELEGATE_TYPE');
1160 RAISE g_e_INVALID_DELEGATION_TYPE;
1161 END IF;
1162 -- Step 2. validate dates
1163 --- Rules:
1164 --- a. Truncate dates
1165 --- b. Start Date must be < End Date otherwise raise error
1166 --- c. Start Date and hence End Date must be >= TRUNC(sysdate)
1167 --- otherwise raise error
1168 --- NOTE
1169 --- - Start Date can be NULL to mean revoke "as of now" evaluated
1170 --- as SYSDATE in validations
1171 --- - End Date can be NULL to mean revoke everything from start date to
1172 --- "end of time", evaluated in validations as "End of Time"
1173 --
1174 -- a.
1175 dbg(' Check Revoke Dates----------');
1176 l_start_date := TRUNC(p_start_date);
1177 l_end_date := TRUNC(p_end_date);
1178 -- b. & c.
1179 IF ( -- b.
1180 ( l_end_date IS NULL
1181 OR NVL(l_start_date,TRUNC(SYSDATE)) < l_end_date )
1182 -- c.
1183 AND ( l_start_date IS NULL
1184 OR l_start_date >= TRUNC(SYSDATE) )
1185 )
1186 THEN
1187 dbg(' Dates are valid');
1188 dbg(' l_start_date :'||TO_CHAR(l_start_date));
1189 dbg(' l_end_date :'||TO_CHAR(l_end_date));
1190 ELSE
1191 --
1192 dbg('RAISE:g_e_INVALID_DATES');
1193 RAISE g_e_INVALID_DATES;
1194 END IF;
1195 --
1196 -- Step 3. check that either:
1197 -- 1. instance ids are NULL - defaulted
1198 -- 2. instance parameters are valid
1199 IF ( --1.
1200 p_instance_pk1_value IS NULL
1201 --2.
1202 OR(instance_is_valid
1203 (p_delegate_type
1204 ,p_instance_pk1_value
1205 ,p_instance_pk2_value
1206 ,TRUNC(SYSDATE)
1207 ,p_end_date)
1208 )
1209 )
1210 THEN
1211 dbg(' Instance keys are valid');
1212 ELSE
1213 dbg('RAISE:g_e_INVALID_INSTANCE');
1214 RAISE g_e_INVALID_INSTANCE;
1215 END IF;
1216 -- Step 5. update records in revoke mode
1217 update_delegation_grants
1218 ( p_delegate_type =>p_delegate_type--IN VARCHAR2
1219 ,p_grantee_key =>p_grantee_key--IN VARCHAR2 DEFAULT NULL
1220 ,p_instance_pk1_value =>p_instance_pk1_value--IN VARCHAR2
1221 --,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
1222 --,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
1223 --,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
1224 --,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
1225 ,p_start_date =>l_start_date--IN DATE DEFAULT SYSDATE
1226 ,p_end_date =>l_end_date--IN DATE DEFAULT SYSDATE
1227 ,p_menu_id =>l_menu_id--IN NUMBER DEFAULT NULL
1228 ,x_success =>x_success--OUT NOCOPY VARCHAR /* Boolean */
1229 ,x_errorcode =>x_errorcode--OUT NOCOPY NUMBER
1230 ,p_update_mode =>'REVOKE'--IN VARCHAR2 DEFAULT 'EXTEND'
1231 );
1232 EXCEPTION
1233 WHEN g_e_INVALID_DATES THEN
1234 x_success :='F';
1235 x_errorcode := 'INVALID_DATES';
1236 WHEN g_e_INVALID_DELEGATION_TYPE THEN
1237 x_success := 'F';
1238 x_errorcode := 'INVALID_DELEGATION_TYPE';
1239 WHEN g_e_INVALID_FND_OBJECT THEN
1240 x_success := 'F';
1241 x_errorcode := 'INVALID_FND_OBJECT';
1242 WHEN g_e_INVALID_INSTANCE THEN
1243 x_success := 'F';
1244 x_errorcode := 'INVALID_INSTANCE';
1245 WHEN g_e_INVALID_GRANTEE THEN
1246 x_success := 'F';
1247 x_errorcode := 'INVALID_GRANTEE';
1248 WHEN g_e_INVALID_MENU_NAME THEN
1249 x_success := 'F';
1250 x_errorcode := 'INVALID_MENU_NAME';
1251 END revoke_delegation;
1252 --
1253 END BIS_DELEGATION_PUB;
[Home] [Help]