1 PACKAGE BODY WF_ROLE_HIERARCHY as
2 /*$Header: WFRHIERB.pls 120.27.12010000.4 2008/11/07 16:25:48 alsosa ship $*/
3
4 ----
5 -- Private Globals
6 --
7 --
8 g_trustTimeStamp DATE;
9
10 ----
11 -- Private APIs
12 --
13 --
14
15 -- RaiseEvent (PRIVATE)
16 -- Wrapper to raise events to BES.
17 -- IN
18 -- p_eventName (VARCHAR2)
19 -- p_relationshipID (NUMBER)
20 -- p_superName (VARCHAR2)
21 -- p_subName (VARCHAR2)
22 -- p_defer (BOOLEAN)
23
24 procedure RaiseEvent( p_eventName VARCHAR2,
25 p_relationshipID NUMBER,
26 p_superName VARCHAR2,
27 p_subName VARCHAR2,
28 p_defer BOOLEAN) is
29
30 l_params WF_PARAMETER_LIST_T;
31
32 begin
33 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
34 -- Log only
35 -- BINDVAR_SCAN_IGNORE[5]
36 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
37 g_modulePkg||'.RaiseEvent',
38 'Begin RaiseEvent('||p_eventName||', '||
39 p_relationshipID||', '||p_superName||', '||
40 p_subName||')');
41 end if;
42
43 WF_EVENT.AddParameterToList('RELATIONSHIP_ID', p_relationshipID, l_params);
44 WF_EVENT.AddParameterToList('SUPER_NAME', p_superName, l_params);
45 WF_EVENT.AddParameterToList('SUB_NAME', p_subName, l_params);
46 WF_EVENT.AddParameterToList('USER_ID', WFA_SEC.USER_ID, l_params);
47 WF_EVENT.AddParameterToList('LOGIN_ID', WFA_SEC.LOGIN_ID, l_params);
48 WF_EVENT.AddParameterToList('SECURITY_GROUP_ID',
49 WFA_SEC.SECURITY_GROUP_ID, l_params);
50
51 if (p_defer) then
52 WF_EVENT.AddParameterToList('DEFER_PROPAGATION', 'TRUE', l_params);
53 else
54 WF_EVENT.AddParameterToList('DEFER_PROPAGATION', 'FALSE', l_params);
55 end if;
56
57 WF_EVENT.Raise(P_EVENT_NAME=>p_eventName,
58 P_EVENT_KEY=>p_relationshipID, P_PARAMETERS=>l_params);
59
60 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
61 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
62 g_modulePkg||'.RaiseEvent',
63 'End RaiseEvent('||p_eventName||', '||
64 p_relationshipID||', '||p_superName||', '||
65 p_subName||')');
66 end if;
67 exception
68 when OTHERS then
69 if (wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
70 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
71 g_modulePkg||'.RaiseEvent',
72 'Exception: '||sqlerrm);
73 end if;
74 WF_CORE.Context('WF_ROLE_HIERARCHY', 'RaiseEvent', p_eventName,
75 p_relationshipID, p_superName, p_subName);
76 raise;
77 end RaiseEvent;
78
79
80 --
81 -- HierarchyEnabled (PRIVATE)
82 --
83 -- IN
84 -- p_origSystem (VARCHAR2)
85 --
86 -- RETURNS
87 -- BOOLEAN
88 --
89 -- NOTES
90 -- Checks to see if p_origSystem does NOT participate in bulk synch
91 -- therefore is able to be hierarchy enabled.
92 --
93 function HierarchyEnabled (p_origSystem in VARCHAR2) return boolean
94 is
95 l_viewName VARCHAR2(30);
96 l_partitionID number;
97 l_partitionName varchar2(30);
98 l_hierarchyEnabled BOOLEAN;
99
100 begin
101 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
102 -- Log only
103 -- BINDVAR_SCAN_IGNORE[3]
104 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
105 g_modulePkg||'.HierarchyEnabled',
106 'Begin HierarchyEnabled('||p_origSystem||')');
107 end if;
108
109 WF_DIRECTORY.AssignPartition(p_origSystem, l_partitionID,
110 l_partitionName);
111
112 --First Check:If the partition is registered and the view names are
113 --set to 'NOBS' or if the partition is not registered, we return true.
114 begin
115 SELECT ROLE_VIEW
116 INTO l_viewName
117 FROM WF_DIRECTORY_PARTITIONS
118 WHERE ORIG_SYSTEM = UPPER(p_origSystem)
119 AND PARTITION_ID <> 0 --<rwunderl:3588271>
120 AND (ROLE_VIEW is NULL
121 or ROLE_VIEW <> 'NOBS');
122
123 l_hierarchyEnabled := FALSE;
124
125 exception
126 when NO_DATA_FOUND then
127 l_hierarchyEnabled := TRUE;
128 if wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level then
129 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
130 g_modulePkg||'.HierarchyEnabled',
131 p_origSystem||' is hierarchy enabled.');
132 end if;
133 end;
134
135 --Second Check: If a hierarchical relationship was created then
136 --we are hierarchy enabled.
137 if NOT (l_hierarchyEnabled) then
138 begin
139 select 'NOBS'
140 into l_viewName
141 from dual
142 where EXISTS (select NULL
143 from WF_ROLE_HIERARCHIES
144 where PARTITION_ID = l_partitionID
145 or SUPERIOR_PARTITION_ID = l_partitionID);
146
147 l_hierarchyEnabled := TRUE;
148 exception
149 when NO_DATA_FOUND then
150 l_hierarchyEnabled := FALSE;
151 end;
152 end if;
153 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
154 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
155 g_modulePkg||'.HierarchyEnabled',
156 'End HierarchyEnabled('||p_origSystem||')');
157 end if;
158 return l_HierarchyEnabled;
159
160 exception
161 when OTHERS then
162 if(wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
163 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
164 g_modulePkg||'.HierarchyEnabled',
165 'Exception: '||sqlerrm);
166 end if;
167 WF_CORE.Context('WF_ROLE_HIERARCHY', 'HierarchyEnabled', p_origSystem);
168 raise;
169
170 end HierarchyEnabled;
171
172 --
173 -- Calculate_Effective_Dates(PRIVATE)
174 --
175 -- IN
176 -- p_startDate DATE,
177 -- p_endDate DATE,
178 -- p_userStartDate DATE,
179 -- p_userEndDate DATE,
180 -- p_roleStartDate DATE,
181 -- p_roleEndDate DATE,
182 -- p_assignRoleStart DATE,
183 -- p_assignRoleEnd DATE,
184 --
185 -- IN OUT
186 -- p_effStartDate DATE
187 -- p_effEndDate DATE
188 --
189 -- NOTES
190 -- Calculates the effective start and end dates in WF_USER_ROLE_ASSIGNMENTS
191 -- from the user/role and asigning_Role start and end dates respectively
192
193 procedure Calculate_Effective_Dates(
194 p_startDate in DATE,
195 p_endDate in DATE,
196 p_userStartDate in DATE,
197 p_userEndDate in DATE,
198 p_roleStartDate in DATE,
199 p_roleEndDate in DATE,
200 p_assignRoleStart in DATE,
201 p_assignRoleEnd in DATE,
202 p_effStartDate in out NOCOPY DATE,
203 p_effEndDate in out NOCOPY DATE
204 )
205 is
206
207 begin
208
209 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
210 -- Log only
211 -- BINDVAR_SCAN_IGNORE[9]
212 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
213 g_modulePkg||'.Calculate_Effective_Dates',
214 'Begin Calculate_Effective_Dates('||
215 to_char(p_startDate,WF_CORE.canonical_date_mask)|| ', ' ||
216 to_char(p_endDate,WF_CORE.canonical_date_mask)|| ', ' ||
217 to_char(p_userStartDate,WF_CORE.canonical_date_mask)||', '||
218 to_char(p_userEndDate,WF_CORE.canonical_date_mask)||', ' ||
219 to_char(p_roleStartDate,WF_CORE.canonical_date_mask)||', '||
220 to_char(p_roleEndDate,WF_CORE.canonical_date_mask)||', '||
221 to_char(p_assignRoleStart,WF_CORE.canonical_date_mask)||', '||
222 to_char(p_assignRoleEnd,WF_CORE.canonical_date_mask)||', '||
223 to_char( p_effStartDate,WF_CORE.canonical_date_mask)||', '||
224 to_char(p_effEndDate,WF_CORE.canonical_date_mask)||')');
225 end if;
226 --Intialize effective start date to beginning of time
227 p_effStartDate := to_date(1,'J');
228 --The effective start should be the greatest of all start dates.
229 p_effStartDate := greatest(nvl(p_startDate, p_effStartDate),
230 nvl(p_userStartDate, p_effStartDate),
231 nvl(p_roleStartDate, p_effStartDate),
232 nvl(p_assignRoleStart, p_effStartDate));
233
234 --Intialize effective start date to end of time
235 p_effEndDate := to_date('9999/01/01','YYYY/MM/DD');
236 --The effective end should be the least of all end dates.
237 p_effEndDate := least(nvl(p_endDate, p_effEndDate),
238 nvl(p_userEndDate, p_effEndDate),
239 nvl(p_roleEndDate, p_effEndDate),
240 nvl(p_assignRoleEnd, p_effEndDate));
241
242
243 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
244 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
245 g_modulePkg||'.Calculate_Effective_Dates',
246 'End Calculate_Effective_Dates');
247 end if;
248 exception
249 when OTHERS then
250 if (wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
251 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
252 g_modulePkg||'.Calculate_Effective_Dates',
253 'Exception: '||sqlerrm);
254 end if;
255
256 WF_CORE.Context('WF_ROLE_HIERARCHY', 'Calculate_Effective_Dates',
257 to_char(p_startDate,WF_CORE.canonical_date_mask),
258 to_char(p_endDate,WF_CORE.canonical_date_mask),
259 to_char(p_userStartDate,WF_CORE.canonical_date_mask),
260 to_char(p_userEndDate,WF_CORE.canonical_date_mask),
261 to_char(p_roleStartDate,WF_CORE.canonical_date_mask),
262 to_char(p_roleEndDate,WF_CORE.canonical_date_mask),
263 to_char(p_assignRoleStart,WF_CORE.canonical_date_mask),
264 to_char(p_assignRoleEnd,WF_CORE.canonical_date_mask),
265 to_char(p_effStartDate,WF_CORE.canonical_date_mask),
266 to_char(p_effEndDate,WF_CORE.canonical_date_mask));
267
268 raise;
269 end Calculate_Effective_Dates;
270
271 --
272 -- AssignmentType (PRIVATE)
273 --
274 -- IN
275 -- p_UserName (VARCHAR2)
276 -- p_RoleName (VARCHAR2)
277 --
278 -- RETURNS
279 -- VARCHAR2
280 --
281 -- NOTES
282 -- Checks to see if this is a direct, indirect or both. Any exception
283 -- or failure to determine the assignment type returns 'X'.
284 --
285 function AssignmentType(p_UserName VARCHAR2,
286 p_RoleName VARCHAR2) return varchar2 is
287
288 TYPE numTAB is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
289 l_relIDTAB numTAB;
290 l_assignmentType VARCHAR2(1) := 'X';
291 arIND NUMBER;
292
293 begin
294 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
295 -- Log only
296 -- BINDVAR_SCAN_IGNORE[4]
297 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
298 g_modulePkg||'.AssignmentType',
299 'Begin AssignmentType('||p_UserName||', '||
300 p_RoleName||')');
301 end if;
302 --Determine assignment type
303 SELECT RELATIONSHIP_ID
304 BULK COLLECT INTO l_relIDTAB
305 FROM WF_USER_ROLE_ASSIGNMENTS
306 WHERE ROLE_NAME = p_RoleName
307 AND USER_NAME = p_UserName
308 AND trunc(sysdate) BETWEEN
309 trunc(EFFECTIVE_START_DATE)
310 AND trunc(EFFECTIVE_END_DATE);
311
312
313
314 <<assignmentTypes>>
315 for arIND in l_relIDTAB.FIRST..l_relIDTAB.LAST loop
316 if (l_relIDTAB(arIND) = -1) then
317 --This is a direct assignment, we will check to see if an
318 --active inherited assignment was already registered.
319 if (l_assignmentType = 'I') then
320 l_assignmentType := 'B';
321
322 --We can stop the comparison because we have already
323 --determined that this assignment is both direct and
324 --inherited.
325 exit assignmentTypes;
326
327 else
328 --We are registering the direct assignment
329 l_assignmentType := 'D';
330
331 end if;
332 else
333 --This is not a direct assignment (it is inherited)
334 --we will see if an active direct assignment was already registered.
335 if (l_assignmentType = 'D') then
336 l_assignmentType := 'B';
337
338 --We can stop the comparison because we have already
339 --determined that this assignment is both direct and inherited.
340 exit assignmentTypes;
341
342 else
343 --We are registering the inherited assignment.
344 l_assignmentType := 'I';
345
346 end if;
347 end if;
348 end loop assignmentTypes;
349
350 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
351 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
352 g_modulePkg||'.AssignmentType',
353 'End AssignmentType('||p_UserName||', '||
354 p_RoleName||') returning ['||l_assignmentType||']');
355 end if;
356 return l_assignmentType;
357
358 exception
359 when OTHERS then
360 return 'X';
361
362 end AssignmentType;
363
364
365 -- Cascade_RF (PRIVATE)
366 -- Rule function to cascade changes according to the active hierarchy
367 -- when a user/role relationship is assigned or revoked.
368 -- IN
369 -- p_sub_guid (RAW)
370 -- p_event ([WF_EVENT_T])
371 -- RETURNS
372 -- VARCHAR2
373
374 function Cascade_RF ( p_sub_guid in RAW,
375 p_event in out NOCOPY WF_EVENT_T )
376 return VARCHAR2 is
377
378 TYPE dateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
379 TYPE idTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
380
381
382 l_subordinates WF_ROLE_HIERARCHY.relTAB;
383 l_superiors WF_ROLE_HIERARCHY.relTAB;
384 l_rowIDTAB idTab;
385 l_roleStartTAB dateTab;
386 l_roleEndTAB dateTab;
387 l_effStartTAB dateTab;
388 l_effEndTAB dateTab;
389 l_RoleName VARCHAR2(320);
390 l_UserName VARCHAR2(320);
391 l_StartDate DATE;
392 l_EndDate DATE;
393 l_UserStartDate DATE;
394 l_UserEndDate DATE;
395 l_RoleStartDate DATE;
396 l_RoleEndDate DATE;
397 l_SupStartDate DATE;
398 l_SupEndDate DATE;
399 l_EffStartDate DATE;
400 l_EffEndDate DATE;
401 l_CreatedBy NUMBER := WFA_SEC.USER_ID;
402 l_CreationDate DATE;
403 l_LastUpdatedBy NUMBER := WFA_SEC.USER_ID;
404 l_LastUpdateDate DATE;
405 l_LastUpdateLogin NUMBER := WFA_SEC.LOGIN_ID;
406 l_RoleOrigSystem VARCHAR2(30);
407 l_RoleOrigSystemID NUMBER;
408 l_UserOrigSystem VARCHAR2(30);
409 l_UserOrigSystemID NUMBER;
410 l_OwnerTag VARCHAR2(50);
411 l_assignmentType VARCHAR2(1);
412 l_assignmentReason VARCHAR2(4000);
413 l_partitionID NUMBER;
414 l_partitionName VARCHAR2(30);
415 l_count NUMBER;
416 l_rowid ROWID;
417 OverWrite BOOLEAN;
418 UpdateWho BOOLEAN := TRUE;
419
420 begin
421 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
422 -- Log only
423 -- BINDVAR_SCAN_IGNORE[4]
424 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
425 g_modulePkg||'.Cascade_RF',
426 'Begin Cascade_RF('||rawtohex(p_sub_guid)||', '||
427 p_event.getEventName||')');
428 end if;
429 --Retrieve the parameters from the event and cast to appropiate data types.
430 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
431 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
432 g_modulePkg||'.Cascade_RF',
433 'Retrieving parameters from the event.');
434 end if;
435 l_RoleName := p_event.getValueForParameter('ROLE_NAME');
436 l_StartDate := to_date(p_event.getValueForParameter('START_DATE'),
437 WF_CORE.canonical_date_mask);
438 l_EndDate := to_date(p_event.getValueForParameter('END_DATE'),
439 WF_CORE.canonical_date_mask);
440 l_UserName := p_event.getValueForParameter('USER_NAME');
441 l_UserOrigSystem := p_event.getValueForParameter('USER_ORIG_SYSTEM');
442 l_UserOrigSystemID := to_number(p_event.getValueForParameter(
443 'USER_ORIG_SYSTEM_ID'), WF_CORE.canonical_number_mask);
444 l_RoleOrigSystem := p_event.getValueForParameter('ROLE_ORIG_SYSTEM');
445 l_RoleOrigSystemID := to_number(p_event.getValueForParameter(
446 'ROLE_ORIG_SYSTEM_ID'), WF_CORE.canonical_number_mask);
447 l_OwnerTag := p_event.getValueForParameter('OWNER_TAG');
448 l_CreatedBy := to_number(p_event.getValueForParameter('CREATED_BY'),
449 WF_CORE.canonical_number_mask);
450 l_CreationDate := to_date(p_event.getValueForParameter('CREATION_DATE'),
451 WF_CORE.canonical_date_mask);
452 l_LastUpdatedBy := to_number(p_event.getValueForParameter(
453 'LAST_UPDATED_BY'), WF_CORE.canonical_number_mask);
454 l_LastUpdateDate := to_date(p_event.getValueForParameter(
455 'LAST_UPDATE_DATE'),
456 WF_CORE.canonical_date_mask);
457 l_LastUpdateLogin := to_number(p_event.getValueForParameter(
458 'LAST_UPDATE_LOGIN'), WF_CORE.canonical_number_mask);
459 l_assignmentReason := p_event.getValueForParameter('ASSIGNMENT_REASON');
460 l_rowid := chartorowid(p_event.getValueForParameter('ROWID'));
461 if (p_event.getValueForParameter('WFSYNCH_OVERWRITE') ='TRUE') then
462 OverWrite := TRUE;
463 else
464 OverWrite:= FALSE;
465 end if;
466
467 if (p_event.getValueForParameter('UPDATE_WHO') ='TRUE') then
468 UpdateWho:= TRUE;
469 else
470 UpdateWho:= FALSE;
471 end if;
472
473 --If this is not a direct assignment, we don't need to cascade
474 --the user/role creation, but we do need to validate the assignment type..
475 if (p_event.getValueForParameter('ASSIGNMENT_TYPE') <> 'D') then
476 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
477 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
478 g_modulePkg||'.Cascade_RF',
479 'This is not a direct assignment, so nothing to '||
480 'cascade. But we need to check of existing direct '||
481 'assignments that would cause the denormalized '||
482 'assignment_type to be set to B from D');
483 end if;
484 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
485 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
486 g_modulePkg||'.Cascade_RF',
487 'End Cascade_RF('||rawtohex(p_sub_guid)||', '||
488 p_event.getEventName||')');
489 end if;
490 l_assignmentType := AssignmentType(p_UserName=>l_UserName,
491 p_RoleName=>l_RoleName);
492
493 --Validate the assignment type status
494 UPDATE WF_LOCAL_USER_ROLES
495 SET ASSIGNMENT_TYPE = l_assignmentType
496 WHERE ROWID = l_rowid;
497
498 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
499 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
500 g_modulePkg||'.Cascade_RF',
501 'End Cascade_RF('||rawtohex(p_sub_guid)||', '||
502 p_event.getEventName||') returning [SUCCESS]');
503 end if;
504 return 'SUCCESS';
505
506 else
507
508 -- Since this is a direct assignment we need to retrieve the
509 -- user and role start/end dates
510
511 WF_DIRECTORY.AssignPartition(p_orig_system=>l_UserOrigSystem,
512 p_partitionID=>l_partitionID,
513 p_partitionName=>l_partitionName);
514
515 if (l_partitionID = 1) then
516 SELECT START_DATE, EXPIRATION_DATE
517 INTO l_UserStartDate, l_UserEndDate
518 FROM WF_LOCAL_ROLES
519 WHERE NAME = l_UserName
520 AND PARTITION_ID = l_partitionID
521 and rownum<2;
522 else
523 SELECT START_DATE, EXPIRATION_DATE
524 INTO l_UserStartDate, l_UserEndDate
525 FROM WF_LOCAL_ROLES
526 WHERE NAME = l_UserName
527 AND ORIG_SYSTEM= l_UserOrigSystem
528 AND ORIG_SYSTEM_ID = l_UserOrigSystemID
529 AND PARTITION_ID = l_partitionID
530 AND rownum<2;
531 end if;
532
533 WF_DIRECTORY.AssignPartition(p_orig_system=>l_RoleOrigSystem,
534 p_partitionID=>l_partitionID,
535 p_partitionName=>l_partitionName);
536 if (l_partitionID= 1) then
537 SELECT START_DATE, EXPIRATION_DATE
538 INTO l_RoleStartDate, l_RoleEndDate
539 FROM WF_LOCAL_ROLES
540 WHERE NAME = l_RoleName
541 AND PARTITION_ID = l_partitionID
542 AND rownum<2;
543 else
544 SELECT START_DATE, EXPIRATION_DATE
545 INTO l_RoleStartDate, l_RoleEndDate
546 FROM WF_LOCAL_ROLES
547 WHERE NAME = l_RoleName
548 AND ORIG_SYSTEM= l_RoleOrigSystem
549 AND ORIG_SYSTEM_ID = l_RoleOrigSystemID
550 AND PARTITION_ID = l_partitionID
551 AND rownum<2;
552 end if;
553 -- If we are updating the user/role such as setting the end_date, then
554 -- This part of the code will handle all of the assignments.
555
556 -- we need to recalculate the effective dates as well since these
557 -- might now be changed
558
559
560 SELECT ROWID, ROLE_START_DATE, ROLE_END_DATE,EFFECTIVE_START_DATE,
561 EFFECTIVE_END_DATE
562 BULK COLLECT INTO l_RowIDTAB, l_roleStartTAB,l_roleEndTAB,
563 l_effStartTAB,l_effEndTAB
564 FROM WF_USER_ROLE_ASSIGNMENTS
565 WHERE USER_NAME = l_UserName
566 AND ASSIGNING_ROLE = l_RoleName;
567
568 if (l_rowIDTAB.COUNT > 0) then
569 -- Update Assignment Reason for direct assignment
570 UPDATE WF_USER_ROLE_ASSIGNMENTS
571 SET ASSIGNMENT_REASON = l_assignmentReason
572 WHERE USER_NAME = l_UserName
573 AND ASSIGNING_ROLE = l_RoleName
574 AND RELATIONSHIP_ID = -1;
575
576 --We don't want to loop if there are no records that meet
577 --our criteria, we could stop right now.
578
579 for tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST loop
580 --Now we want to calculate the effective start and end dates
581 --for this assignment.
582 Calculate_Effective_Dates(l_StartDate, l_EndDate,
583 l_UserStartDate,l_UserEndDate,
584 l_roleStartTAB(tabIndex),l_roleEndTAB(tabIndex),
585 l_RoleStartDate,l_RoleEndDate,
586 l_effStartTAB(tabIndex),l_effEndTAB(tabIndex));
587 end loop;
588 if OverWrite and UpdateWho then
589 --allow update of creation_date and created_by
590 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
591 update WF_USER_ROLE_ASSIGNMENTS
592 set START_DATE = l_StartDate,
593 END_DATE = l_EndDate,
594 LAST_UPDATED_BY = nvl(l_LastUpdatedBy,WFA_SEC.USER_ID),
595 LAST_UPDATE_DATE = nvl(l_LastUpdateDate,SYSDATE),
596 LAST_UPDATE_LOGIN = nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
597 CREATION_DATE = nvl(l_CreationDate,CREATION_DATE),
598 CREATED_BY = nvl(l_CreatedBy, CREATED_BY),
599 EFFECTIVE_START_DATE = l_effStartTAB(tabIndex),
600 EFFECTIVE_END_DATE = l_effEndTAB(tabIndex)
601 where rowid = l_rowIDTAB(tabIndex);
602 elsif UpdateWho then
603 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
604 update WF_USER_ROLE_ASSIGNMENTS
605 set START_DATE = l_StartDate,
606 END_DATE = l_EndDate,
607 LAST_UPDATED_BY = nvl(l_LastUpdatedBy,WFA_SEC.USER_ID),
608 LAST_UPDATE_DATE = nvl(l_LastUpdateDate,SYSDATE),
609 LAST_UPDATE_LOGIN = nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
610 EFFECTIVE_START_DATE = l_effStartTAB(tabIndex),
611 EFFECTIVE_END_DATE = l_effEndTAB(tabIndex)
612 where rowid = l_rowIDTAB(tabIndex);
613 else -- Donot update WHO columns
614 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
615 update WF_USER_ROLE_ASSIGNMENTS
616 set START_DATE = l_StartDate,
617 END_DATE = l_EndDate,
618 EFFECTIVE_START_DATE = l_effStartTAB(tabIndex),
619 EFFECTIVE_END_DATE = l_effEndTAB(tabIndex)
620 where rowid = l_rowIDTAB(tabIndex);
621
622 end if;
623
624 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
625 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
626 g_modulePkg||'.Cascade_RF',
627 'Assignments for assigning role: '||
628 l_RoleName||' exist for user '||l_UserName||
629 '. Updated existing assignments.');
630 end if;
631 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
632 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
633 g_modulePkg||'.Cascade_RF',
634 'End Cascade_RF('||rawtohex(p_sub_guid)||', '||
635 p_event.getEventName||')');
636 end if;
637
638
639 return 'SUCCESS';
640 end if;
641 end if;
642
643 -- We made it here so that means there are no assignments.
644 -- First, we must create the assignment record for the direct assignment.
645
646 --<rwunderl:3737114>
647 WF_DIRECTORY.AssignPartition(p_orig_system=>l_RoleOrigSystem,
648 p_partitionID=>l_partitionID,
649 p_partitionName=>l_partitionName);
650 --calculate the effective dates for the direct assignment
651
652 Calculate_Effective_Dates(l_StartDate,
653 l_EndDate,
654 l_UserStartDate,
655 l_UserEndDate,
656 l_RoleStartDate,
657 l_RoleEndDate,
658 l_RoleStartDate,
659 l_RoleEndDate,
660 l_EffStartDate,
661 l_EffEndDate);
662
663 --</rwunderl:3737114>
664
665 INSERT INTO WF_USER_ROLE_ASSIGNMENTS
666 ( USER_NAME,
667 ROLE_NAME,
668 RELATIONSHIP_ID,
669 ASSIGNING_ROLE,
670 START_DATE,
671 END_DATE,
672 ROLE_START_DATE,
673 ROLE_END_DATE,
674 USER_START_DATE,
675 USER_END_DATE,
676 ASSIGNING_ROLE_START_DATE,
677 ASSIGNING_ROLE_END_DATE,
678 CREATED_BY,
679 CREATION_DATE,
680 LAST_UPDATED_BY,
681 LAST_UPDATE_DATE,
682 LAST_UPDATE_LOGIN,
683 PARTITION_ID,
684 EFFECTIVE_START_DATE,
685 EFFECTIVE_END_DATE,
686 USER_ORIG_SYSTEM,
687 USER_ORIG_SYSTEM_ID,
688 ROLE_ORIG_SYSTEM,
689 ROLE_ORIG_SYSTEM_ID,
690 ASSIGNMENT_REASON)
691 values
692 ( l_UserName,
693 l_RoleName,
694 -1,
695 l_RoleName,
696 l_StartDate,
697 l_EndDate,
698 l_RoleStartDate,
699 l_RoleEndDate,
700 l_UserStartDate,
701 l_UserEndDate,
702 l_RoleStartDate,
703 l_RoleEndDate,
704 nvl(l_CreatedBy,WFA_SEC.User_ID),
705 nvl(l_CreationDate,SYSDATE),
706 nvl(l_LastUpdatedBy, WFA_SEC.User_ID),
707 nvl(l_LastUpdateDate,SYSDATE),
708 nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
709 l_partitionID,
710 l_effStartDate,
711 l_effEndDate,
712 l_UserOrigSystem,
713 l_UserOrigSystemId,
714 l_RoleOrigSystem,
715 l_RoleOrigSystemId,
716 l_assignmentReason);
717
718
719
720 GetRelationships(p_name=>l_RoleName,
721 p_superiors=>l_superiors,
722 p_subordinates=>l_subordinates,
723 p_direction=>'SUPERIORS');
724
725 if (l_superiors.COUNT <= 0) then
726 --There is nothing to do.
727 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
728 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
729 g_modulePkg||'.Cascade_RF',
730 'There are no superiors, updates are limited to this '||
731 'user/role relationship.');
732 end if;
733 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
734 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
735 g_modulePkg||'.Cascade_RF',
736 'End Cascade_RF('||rawtohex(p_sub_guid)||', '||
737 p_event.getEventName||')');
738 end if;
739
740
741 return 'SUCCESS';
742
743 end if;
744 --If we made it here, there is hierarchy processing to do. First we need
745 --to attempt to create a user/role for l_UserName to l_Superiors(i)
746 --then we need to create a user/role assignment for l_RoleName as the
747 --assigning role.
748 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
749 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
750 g_modulePkg||'.Cascade_RF',
751 'Beginning user/role inheritance for '||l_UserName||
752 ' to the superior roles of '||l_RoleName);
753 end if;
754 for l_count in l_superiors.FIRST..l_superiors.LAST loop
755 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
756 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
757 g_modulePkg||'.Cascade_RF',
758 'Beginning user/role inheritance for '||l_UserName||
759 ' to '||l_superiors(l_count).SUPER_NAME);
760 end if;
761 --WF_DIRECTORY.GetRoleOrigSysInfo(l_superiors(l_count).SUPER_NAME,
762 -- l_roleOrigSystem, l_roleOrigSystemID);
763
764 --Get the Role Start and End Dates for the superior roles
765 SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID,
766 START_DATE, EXPIRATION_DATE
767 INTO l_roleOrigSystem,l_roleOrigSystemID,
768 l_SupStartDate, l_SupEndDate
769 FROM WF_LOCAL_ROLES
770 WHERE NAME=l_superiors(l_count).SUPER_NAME
771 AND rownum<2;
772
773 --Calculate the effective_dates for each of these assignments
774
775 Calculate_Effective_Dates( l_StartDate,
776 l_EndDate,
777 l_UserStartDate,
778 l_UserEndDate,
779 l_SupStartDate,
780 l_SupEndDate,
781 l_RoleStartDate,
782 l_RoleEndDate,
783 l_EffStartDate,
784 l_EffEndDate);
785
786 --Creating the assignment record for each user/role assignment.
787 begin
788 INSERT INTO WF_USER_ROLE_ASSIGNMENTS
789 ( USER_NAME,
790 ROLE_NAME,
791 RELATIONSHIP_ID,
792 ASSIGNING_ROLE,
793 START_DATE,
794 END_DATE,
795 USER_START_DATE,
796 USER_END_DATE,
797 ROLE_START_DATE,
798 ROLE_END_DATE,
799 ASSIGNING_ROLE_START_DATE,
800 ASSIGNING_ROLE_END_DATE,
801 CREATED_BY,
802 CREATION_DATE,
803 LAST_UPDATED_BY,
804 LAST_UPDATE_DATE,
805 LAST_UPDATE_LOGIN,
806 PARTITION_ID,
807 EFFECTIVE_START_DATE,
808 EFFECTIVE_END_DATE ,
809 USER_ORIG_SYSTEM,
810 USER_ORIG_SYSTEM_ID,
811 ROLE_ORIG_SYSTEM,
812 ROLE_ORIG_SYSTEM_ID
813 )
814 values
815 ( l_UserName,
816 l_superiors(l_count).SUPER_NAME,
817 l_superiors(l_count).RELATIONSHIP_ID,
818 l_RoleName,
819 l_StartDate,
820 l_EndDate,
821 l_UserStartDate,
822 l_UserEndDate,
823 l_SupStartDate,
824 l_SupEndDate,
825 l_RoleStartDate,
826 l_RoleEndDate,
827 nvl(l_CreatedBy,WFA_SEC.User_ID),
828 nvl(l_CreationDate,SYSDATE),
829 nvl(l_LastUpdatedBy, WFA_SEC.User_ID),
830 nvl(l_LastUpdateDate,SYSDATE),
831 nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
832 l_partitionID,
833 l_EffStartDate,
834 l_EffEndDate,
835 l_UserOrigSystem,
836 l_UserOrigSystemID,
837 l_RoleOrigSystem,
838 l_RoleOrigSystemID
839 );
840
841 exception
842 when DUP_VAL_ON_INDEX then
843 if (OverWrite and UpdateWho) then
844 --allow update of creation_date and created_by
845 UPDATE WF_USER_ROLE_ASSIGNMENTS
846 SET END_DATE = l_EndDate,
847 START_DATE = l_StartDate,
848 USER_START_DATE = l_UserStartDate,
849 USER_END_DATE = l_UserEndDate,
850 ROLE_START_DATE = l_SupStartDate,
851 ROLE_END_DATE = l_SupEndDate,
852 ASSIGNING_ROLE_START_DATE = l_RoleStartDate,
853 ASSIGNING_ROLE_END_DATE = l_RoleEndDate,
854 EFFECTIVE_START_DATE = l_EffStartDate,
855 EFFECTIVE_END_DATE = l_EffEndDate,
856 LAST_UPDATED_BY = nvl(l_LastUpdatedBy, WFA_SEC.User_ID),
857 LAST_UPDATE_DATE = nvl(l_LastUpdateDate,SYSDATE),
858 LAST_UPDATE_LOGIN = nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
859 CREATED_BY = nvl(l_CreatedBy,CREATED_BY),
860 CREATION_DATE = nvl(l_CreationDate, CREATION_DATE)
861 WHERE RELATIONSHIP_ID = l_superiors(l_count).RELATIONSHIP_ID
862 AND USER_NAME = l_UserName
863 AND ROLE_NAME = l_superiors(l_count).SUPER_NAME
864 AND ASSIGNING_ROLE = l_RoleName;
865 elsif UpdateWho then
866 UPDATE WF_USER_ROLE_ASSIGNMENTS
867 SET END_DATE = l_EndDate,
868 START_DATE = l_StartDate,
869 USER_START_DATE = l_UserStartDate,
870 USER_END_DATE = l_UserEndDate,
871 ROLE_START_DATE = l_SupStartDate,
872 ROLE_END_DATE = l_SupEndDate,
873 ASSIGNING_ROLE_START_DATE = l_RoleStartDate,
874 ASSIGNING_ROLE_END_DATE = l_RoleEndDate,
875 EFFECTIVE_START_DATE = l_EffStartDate,
876 EFFECTIVE_END_DATE = l_EffEndDate,
877 LAST_UPDATED_BY = nvl(l_LastUpdatedBy, WFA_SEC.User_ID),
878 LAST_UPDATE_DATE = nvl(l_LastUpdateDate,SYSDATE),
879 LAST_UPDATE_LOGIN = nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID)
880 WHERE RELATIONSHIP_ID = l_superiors(l_count).RELATIONSHIP_ID
881 AND USER_NAME = l_UserName
882 AND ROLE_NAME = l_superiors(l_count).SUPER_NAME
883 AND ASSIGNING_ROLE = l_RoleName;
884 else
885 UPDATE WF_USER_ROLE_ASSIGNMENTS
886 SET END_DATE = l_EndDate,
887 START_DATE = l_StartDate,
888 USER_START_DATE = l_UserStartDate,
889 USER_END_DATE = l_UserEndDate,
890 ROLE_START_DATE = l_SupStartDate,
891 ROLE_END_DATE = l_SupEndDate,
892 ASSIGNING_ROLE_START_DATE = l_RoleStartDate,
893 ASSIGNING_ROLE_END_DATE = l_RoleEndDate,
894 EFFECTIVE_START_DATE = l_EffStartDate,
895 EFFECTIVE_END_DATE = l_EffEndDate
896 WHERE RELATIONSHIP_ID = l_superiors(l_count).RELATIONSHIP_ID
897 AND USER_NAME = l_UserName
898 AND ROLE_NAME = l_superiors(l_count).SUPER_NAME
899 AND ASSIGNING_ROLE = l_RoleName;
900 end if;
901 when OTHERS then
902 raise;
903
904 end;
905
906 begin
907 --We will create/update the actual user/role record's timestamp only
908 --The effectivity dates will be set by the assignments.
909 WF_DIRECTORY.CreateUserRole(user_name=>l_UserName,
910 role_name=>l_superiors(l_count).SUPER_NAME,
911 start_date=>l_startDate,
912 end_date=>l_endDate,
913 user_orig_system=>l_userOrigSystem,
914 user_orig_system_id=>l_userOrigSystemID,
915 role_orig_system=>l_roleOrigSystem,
916 role_orig_system_id=>l_roleOrigSystemID,
917 validateUserRole=>TRUE,
918 created_by=>l_CreatedBy,
919 creation_date=>l_CreationDate,
920 last_updated_by=>l_LastUpdatedBy,
921 last_update_date=>l_LastUpdateDate,
922 last_update_login=>l_LastUpdateLogin,
923 assignment_type=>'I');
924
925 exception
926 when OTHERS then
927 if (WF_CORE.error_name = 'WF_DUP_USER_ROLE') then
928 --Updating the existing user/role with an assignment_type of 'X'.
929 --The recursive call to cascade_RF() will validate the
930 --assignment_type and set it to the proper value.
931 WF_CORE.Clear;
932 WF_DIRECTORY.SetUserRoleAttr(user_name=>l_UserName,
933 role_name=>l_superiors(l_count).SUPER_NAME,
934 start_date=>l_startDate,
935 end_date=>l_endDate,
936 user_orig_system=>l_userOrigSystem,
937 user_orig_system_id=>l_userOrigSystemID,
938 role_orig_system=>l_roleOrigSystem,
939 role_orig_system_id=>l_roleOrigSystemID,
940 last_updated_by=>l_LastUpdatedBy,
941 last_update_date=>l_LastUpdateDate,
942 last_update_login=>l_LastUpdateLogin,
943 assignment_type=>'X',
944 updateWho=>UpdateWho);
945
946 else
947 WF_CORE.Context('WF_ROLE_HIERARCHY','Cascade_RF',
948 p_event.getEventName( ), p_sub_guid);
949
950 WF_EVENT.setErrorInfo(p_event, 'ERROR');
951
952 return 'ERROR';
953
954 end if;
955 end;
956
957 end loop;
958 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
959 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
960 g_modulePkg||'.Cascade_RF',
961 'End Cascade_RF('||rawtohex(p_sub_guid)||', '||
962 p_event.getEventName||')');
963 end if;
964 return 'SUCCESS';
965 end Cascade_RF;
966
967
968 --
969 -- Propagate_RF (PRIVATE)
970 -- Rule function to handle events when a relationship is created or
971 -- expired
972 -- IN
973 -- p_sub_guid (RAW)
974 -- p_event ([WF_EVENT_T])
975 -- RETURNS
976 -- VARCHAR2
977
978 function Propagate_RF ( p_sub_guid in RAW,
979 p_event in out NOCOPY WF_EVENT_T )
980 return VARCHAR2 is
981
982 l_rel VARCHAR2(10);
983 l_cp_ID NUMBER;
984
985 begin
986 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
987 -- Log only
988 -- BINDVAR_SCAN_IGNORE[4]
989 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
990 g_modulePkg||'.Propagate_RF',
991 'Begin Propagate_RF('||rawtohex(p_sub_guid)||', '||
992 p_event.getEventName||')');
993 end if;
994
995 begin
996 --First check to see if we are to defer propagation.
997 if (nvl(p_event.GetValueForParameter('DEFER_PROPAGATION'),
998 'TRUE') = 'TRUE') then
999 return 'SUCCESS'; --Nothing to do since we are deferring the
1000 --propagation.
1001 end if;
1002 l_rel := nvl(p_event.GetValueForParameter('RELATIONSHIP_ID'), '-1');
1003
1004 if (l_rel = -1) then
1005 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
1006 g_modulePkg||'.Propagate_RF',
1007 'Relationship_ID is NULL!');
1008 return 'ERROR';
1009
1010 else
1011 l_cp_id := FND_REQUEST.Submit_Request(APPLICATION=>'FND',
1012 PROGRAM=>'FNDWFDSRHP',
1013 ARGUMENT1=>l_rel);
1014
1015 if (l_cp_id = -1) then
1016 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
1017 g_modulePkg||'.Propagate_RF',
1018 'Call to FND_SUBMIT failed!');
1019 return 'ERROR';
1020
1021 end if;
1022 end if;
1023 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1024 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
1025 g_modulePkg||'.Propagate_RF',
1026 'Concurrent request '||l_cp_id||
1027 ' Submitted for '||l_rel);
1028 end if;
1029 end;
1030 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1031 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1032 g_modulePkg||'.Propagate_RF',
1033 'End Propagate_RF('||rawtohex(p_sub_guid)||', '||
1034 p_event.getEventName||')');
1035 end if;
1036 return 'SUCCESS';
1037
1038 end Propagate_RF;
1039
1040 --
1041 -- Propagate (PRIVATE)
1042 -- Updates all existing assignments when a change occurs in a hierarchy.
1043 -- IN
1044 -- p_relationship_id (NUMBER)
1045 --
1046
1047
1048
1049 procedure Propagate (p_relationship_id in NUMBER,
1050 p_propagateDate in DATE) is
1051
1052 --Type declarations
1053 TYPE numberTAB is TABLE of NUMBER index by binary_integer;
1054 TYPE dateTAB is TABLE of DATE index by binary_integer;
1055 TYPE ownerTAB is TABLE of VARCHAR2(50) index by binary_integer;
1056 TYPE flagTAB is TABLE of VARCHAR2(1) index by binary_integer;
1057
1058 --Local Variables.
1059 l_relIDTAB numberTAB;
1060 l_userTAB WF_DIRECTORY.UserTable;
1061 l_roleTAB WF_DIRECTORY.RoleTable;
1062 l_assignTAB WF_DIRECTORY.RoleTable;
1063 l_ownerTAB WF_DIRECTORY.RoleTable;
1064 l_uorigSysTAB WF_DIRECTORY.OrigSysTable;
1065 l_uorigSysIDTAB numberTAB;
1066 l_rorigSysTAB WF_DIRECTORY.RoleTable;
1067 l_rorigSysIDTAB numberTAB;
1068 l_rpartIDTAB numberTAB;
1069 l_apartIDTAB numberTAB;
1070 l_startDateTAB dateTAB;
1071 l_endDateTAB dateTAB;
1072 l_uStartDateTAB dateTAB;
1073 l_uEndDateTAB dateTAB;
1074 l_rStartDateTAB dateTAB;
1075 l_rEndDateTAB dateTAB;
1076 l_aStartDateTAB dateTAB;
1077 l_aEndDateTAB dateTAB;
1078 l_enabledFlagTAB flagTAB;
1079 l_user VARCHAR2(320);
1080 l_role VARCHAR2(320);
1081 l_assignmentType VARCHAR2(1);
1082
1083 l_effStartDate DATE;
1084 l_effEndDate DATE;
1085 l_superName VARCHAR2(320);
1086 l_subName VARCHAR2(320);
1087 l_enabledFlag VARCHAR2(1);
1088
1089 --Index Variables
1090 subIND PLS_INTEGER;
1091 hitIND PLS_INTEGER;
1092 userIND PLS_INTEGER;
1093 roleIND PLS_INTEGER;
1094
1095 --Cursor to select expired relationships. This statement selects
1096 --the complete hierarchy then with the minus operator substracts
1097 --relationships from the active hierarchy leaving the relationship(s)
1098 --that should be expired.
1099 cursor expiredRelationships(p_subName in VARCHAR2) is
1100 select WRH1.RELATIONSHIP_ID REL_ID
1101 from WF_ROLE_HIERARCHIES WRH1
1102 connect by prior SUPER_NAME = SUB_NAME
1103 start with SUB_NAME = p_subName
1104 minus
1105 select WRH2.RELATIONSHIP_ID REL_ID
1106 from WF_ROLE_HIERARCHIES WRH2
1107 where ENABLED_FLAG = 'Y'
1108 connect by prior SUPER_NAME = SUB_NAME
1109 and prior ENABLED_FLAG = 'Y'
1110 start with SUB_NAME = p_subName;
1111
1112 cursor relationships (p_subName in VARCHAR2) is
1113 select SUPER_NAME, RELATIONSHIP_ID
1114 from WF_ROLE_HIERARCHIES
1115 where ENABLED_FLAG = 'Y'
1116 connect by prior SUPER_NAME = SUB_NAME
1117 and prior ENABLED_FLAG = 'Y'
1118 start with SUB_NAME = p_subName;
1119
1120 begin
1121 --Retrieve the relationship
1122 select SUPER_NAME, SUB_NAME, ENABLED_FLAG
1123 into l_superName, l_subName, l_enabledFlag
1124 from WF_ROLE_HIERARCHIES
1125 where RELATIONSHIP_ID = p_relationship_id;
1126
1127 --Update the propagate timestamp
1128 update WF_ROLE_HIERARCHIES
1129 set PROPAGATE_DATE = p_propagateDate
1130 where RELATIONSHIP_ID = p_relationship_id
1131 and SUPER_NAME = l_superName
1132 and SUB_NAME = l_subName;
1133
1134 if (l_enabledFlag = 'N') then
1135 --We are propagating an expired relationship
1136 --To address the issue of a relationship being shared by parallel
1137 --Branches, we retrieve the subordinate relationships, then traverse
1138 --down the hierarchy. For each subordinate relationship, we compare the
1139 --active and inactive superiors to remove truly expired hierarchy
1140 --relationships.
1141 --Retrieve the subordinates
1142 select SUB_NAME
1143 bulk collect into l_assignTAB
1144 from WF_ROLE_HIERARCHIES
1145 connect by prior SUB_NAME = SUPER_NAME
1146 start with SUPER_NAME = l_superName;
1147
1148 --Outer loop to traverse down the subordinates in the hierarchy.
1149 if (l_assignTAB.COUNT > 0) then
1150 <<Subordinates>>
1151 for subIND in l_assignTAB.FIRST..l_assignTAB.LAST loop
1152 --Reset the hitList counter.
1153 hitIND := 0;
1154 l_relIDTAB.DELETE; --Truncate the PL/SQL Table.
1155 --Inner loop to select expired relationships providing.
1156 <<ExpiredSuperiors>>
1157 for a in expiredRelationships(l_assignTAB(subIND)) loop
1158 --Load the potential relationship ids into a local table.
1159 l_relIDTAB(hitIND) := a.REL_ID;
1160 hitIND := hitIND + 1; --Advance the counter
1161 end loop ExpiredSuperiors;
1162 --Now we will perform a bulk update to expire the user/role
1163 --assignments that are based on these relationship_ids and were
1164 --assigned from this subordinate (this protects a relationship that
1165 --may be serving more than one subordinate assignment as well as any
1166 --parallel branches).
1167 if (l_relIDTAB.COUNT > 0) then
1168 <<Assignments>>
1169 forall hitIND in l_relIDTAB.FIRST..l_relIDTAB.LAST
1170 delete from WF_USER_ROLE_ASSIGNMENTS
1171 where RELATIONSHIP_ID = l_relIDTAB(hitIND)
1172 and ASSIGNING_ROLE = l_assignTAB(subIND)
1173 returning USER_NAME, ROLE_NAME
1174 bulk collect into l_userTAB, l_roleTAB;
1175
1176 if (l_userTAB.COUNT > 0) then
1177 for userIND in l_userTAB.FIRST..l_userTAB.LAST loop
1178 l_assignmentType := AssignmentType(l_userTAB(userIND),
1179 l_roleTAB(userIND));
1180
1181 select min(effective_start_Date),max(effective_end_date)
1182 into l_effStartDate, l_effEndDate
1183 from wf_user_role_assignments
1184 where user_name= l_userTAB(userIND)
1185 and role_name = l_roleTAB(userIND);
1186
1187
1188 if (l_effStartDate is null) then
1189 -- implies there are no more active inherited assignments
1190 -- to this user/role. So we can expire it. Since this was
1191 -- only an inherited assignment therefore we can safely
1192 -- remove it from WF_LOCAL_USER_ROLES knowing that only
1193 -- direct assignments are shipped.
1194
1195 delete from
1196 WF_LOCAL_USER_ROLES
1197 where USER_NAME = l_userTAB(userIND)
1198 and ROLE_NAME = l_roleTAB(userIND);
1199
1200 else
1201 -- implies the assignment is still active through
1202 -- some other branch.So we just update the effective
1203 -- dates and assignment type.
1204
1205 update WF_LOCAL_USER_ROLES
1206 set ASSIGNMENT_TYPE = l_assignmentType,
1207 EFFECTIVE_START_DATE= l_effStartDate,
1208 EFFECTIVE_END_DATE = l_effEndDate,
1209 LAST_UPDATED_BY = WFA_SEC.user_id,
1210 LAST_UPDATE_DATE = sysdate,
1211 LAST_UPDATE_LOGIN = WFA_SEC.login_id
1212 where USER_NAME = l_userTAB(userIND)
1213 and ROLE_NAME = l_roleTAB(userIND);
1214 end if;
1215 end loop;
1216 end if;
1217 -- end loop Assignments;
1218 commit; --Commiting this batch of updates.
1219 end if;
1220 end loop Subordinates;
1221 end if;
1222 else --(l_enabledFlag = 'Y')
1223 --Retrieve the superiors of this relationship.
1224 open relationships (l_subName);
1225 fetch relationships bulk collect into l_roleTAB, l_relIDTAB;
1226 close relationships;
1227
1228 --Retrieve the role information for each superior.
1229 <<Superiors_Info>>
1230 for roleIND in l_roleTAB.FIRST..l_roleTAB.LAST loop
1231 select ORIG_SYSTEM, ORIG_SYSTEM_ID, PARTITION_ID,
1232 START_DATE, EXPIRATION_DATE
1233 into l_rorigSysTAB(roleIND), l_rorigSysIDTAB(roleIND),
1234 l_rpartIDTAB(roleIND), l_rStartDateTAB(roleIND),
1235 l_rEndDateTAB(roleIND)
1236 from WF_LOCAL_ROLES
1237 where NAME = l_roleTAB(roleIND);
1238 end loop Superiors_Info;
1239
1240 --Retrieve any direct or inherited assignments to the subordinate
1241 --of this relationship.
1242 select USER_NAME, ASSIGNING_ROLE, START_DATE, END_DATE,
1243 USER_START_DATE, USER_END_DATE,
1244 ASSIGNING_ROLE_START_DATE, ASSIGNING_ROLE_END_DATE,
1245 PARTITION_ID, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID
1246 bulk collect into l_userTAB, l_assignTAB, l_startDateTAB, l_endDateTAB,
1247 l_uStartDateTAB, l_uEndDateTAB, l_aStartDateTAB,
1248 l_aEndDateTAB, l_apartIDTAB, l_uorigSysTAB, l_uorigSysIDTAB
1249 from WF_USER_ROLE_ASSIGNMENTS
1250 where ROLE_NAME = l_subName;
1251
1252 --We will propagate the newly inherited assignments before the
1253 --associated user/role relationships because of the functionality of the
1254 --WF_USER_ROLES view. By propagating the assignments first, when we
1255 --create the actual records in WF_LOCAL_USER_ROLES, the WF_USER_ROLES
1256 --view will immediately pickup not only the user/role, but according
1257 --to the effective date-range. If we did this the other way around,
1258 --there may possibly be a way that a user/role relationship that is
1259 --not supposed to be visable, would appear in the view.
1260
1261
1262 --Outer loop to select the superior role.
1263 <<Superiors>>
1264 for roleIND in l_roleTAB.FIRST..l_roleTAB.LAST loop
1265 --Inner loop to select select and propagate any existing assignments
1266 --up the hierarchy.
1267 <<User_Role_Assignments>>
1268 if (l_userTAB.COUNT > 0) then
1269 for userIND in l_userTAB.FIRST..l_userTAB.LAST loop
1270 begin
1271
1272 --calculate the effective start and dates
1273 calculate_effective_dates ( l_startDateTAB(userIND),
1274 l_endDateTAB(userIND),
1275 l_uStartDateTAB(userIND),
1276 l_uEndDateTAB(userIND),
1277 l_rStartDateTAB(roleIND),
1278 l_rEndDateTAB(roleIND),
1279 l_aStartDateTAB(userIND),
1280 l_aEndDateTAB(userIND),
1281 l_effStartDate,
1282 l_effEndDate);
1283
1284 insert into WF_USER_ROLE_ASSIGNMENTS (USER_NAME,
1285 ROLE_NAME,
1286 RELATIONSHIP_ID,
1287 ASSIGNING_ROLE,
1288 START_DATE,
1289 END_DATE,
1290 USER_START_DATE,
1291 USER_END_DATE,
1292 ROLE_START_DATE,
1293 ROLE_END_DATE,
1294 ASSIGNING_ROLE_START_DATE,
1295 ASSIGNING_ROLE_END_DATE,
1296 EFFECTIVE_START_DATE,
1297 EFFECTIVE_END_DATE,
1298 USER_ORIG_SYSTEM,
1299 USER_ORIG_SYSTEM_ID,
1300 ROLE_ORIG_SYSTEM,
1301 ROLE_ORIG_SYSTEM_ID,
1302 CREATED_BY,
1303 CREATION_DATE,
1304 LAST_UPDATED_BY,
1305 LAST_UPDATE_DATE,
1306 LAST_UPDATE_LOGIN,
1307 PARTITION_ID) values
1308 (
1309 l_userTAB(userIND),
1310 l_roleTAB(roleIND),
1311 l_relIDTAB(roleIND),
1312 l_assignTAB(userIND),
1313 trunc(l_startDateTAB(userIND)),
1314 trunc(l_endDateTAB(userIND)),
1315 trunc(l_uStartDateTAB(userIND)),
1316 trunc(l_uEndDateTAB(userIND)),
1317 trunc(l_rStartDateTAB(roleIND)),
1318 trunc(l_rEndDateTAB(roleIND)),
1319 trunc(l_aStartDateTAB(userIND)),
1320 trunc(l_aEndDateTAB(userIND)),
1321 l_effStartDate,
1322 l_effEndDate,
1323 l_uorigSysTAB(userIND),
1324 l_uorigSysIDTAB(userIND),
1325 l_rorigSysTAB(roleIND),
1326 l_rorigSysIDTAB(roleIND),
1327 WFA_SEC.user_id,
1328 sysdate,
1329 WFA_SEC.user_id,
1330 sysdate,
1331 WFA_SEC.login_id,
1332 l_apartIDTAB(userIND));
1333 exception
1334 when DUP_VAL_ON_INDEX then
1335 --This can happen if there is a parallel branch.
1336 --We will just update the timestamp.
1337 update WF_USER_ROLE_ASSIGNMENTS
1338 set START_DATE = trunc(l_startDateTAB(userIND)),
1339 END_DATE = trunc(l_endDateTAB(userIND)),
1340 USER_START_DATE = trunc(l_uStartDateTAB(userIND)),
1341 USER_END_DATE = trunc(l_uEndDateTAB(userIND)),
1342 ROLE_START_DATE = trunc(l_rStartDateTAB(roleIND)),
1343 ROLE_END_DATE = trunc(l_rEndDateTAB(roleIND)),
1344 ASSIGNING_ROLE_START_DATE = trunc(l_aStartDateTAB(userIND)),
1345 ASSIGNING_ROLE_END_DATE = trunc(l_aEndDateTAB(userIND)),
1346 EFFECTIVE_START_DATE = l_effStartDate,
1347 EFFECTIVE_END_DATE = l_effEndDate,
1348 LAST_UPDATED_BY = WFA_SEC.user_id,
1349 LAST_UPDATE_DATE = sysdate,
1350 LAST_UPDATE_LOGIN = WFA_SEC.login_id
1351 where USER_NAME = l_userTAB(userIND)
1352 and ROLE_NAME = l_roleTAB(roleIND)
1353 and RELATIONSHIP_ID = l_relIDTAB(roleIND)
1354 and ASSIGNING_ROLE = l_assignTAB(userIND);
1355 end;
1356 end loop User_Role_Assignments;
1357 end if;
1358 commit; --We will commit all of the user assignments to this superior.
1359 end loop Superiors;
1360
1361 --Retrieve a list of the effected users.
1362 select USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID,
1363 OWNER_TAG, USER_START_DATE, USER_END_DATE, START_DATE,
1364 EXPIRATION_DATE
1365 bulk collect into l_userTAB, l_uorigSysTAB, l_uorigSysIDTAB,
1366 l_ownerTAB, l_uStartDateTAB, l_uEndDateTAB,
1367 l_startDateTAB, l_endDateTAB
1368 from WF_LOCAL_USER_ROLES
1369 where ROLE_NAME = l_subName;
1370
1371 --We will now create the user_role records in WF_LOCAL_USER_ROLES.
1372 --Outer loop to select an effected user (IE: A user that is assigned
1373 --either directly or indirectly to the subordinate role of this
1374 --relationship.
1375 if (l_userTAB.COUNT > 0) then
1376 <<Users>>
1377 for userIND in l_userTAB.FIRST..l_userTAB.LAST loop
1378 --Inner Loop, to select each role traversing up the hierarchy that
1379 --the effected user will inherit.
1380 <<UserRoles>>
1381 for roleIND in l_roleTAB.FIRST..l_roleTAB.LAST loop
1382 begin
1383 --determine the assignment type and effective dates.
1384
1385 l_assignmentType := AssignmentType(l_userTAB(userIND),
1386 l_roleTAB(roleIND));
1387
1388 select min(effective_start_date),
1389 max(effective_end_date)
1390 into l_effStartDate, l_effEndDate
1391 from wf_user_role_Assignments
1392 where user_name=l_userTAB(userIND)
1393 and role_name = l_roleTAB(roleIND);
1394
1395 insert into WF_LOCAL_USER_ROLES (USER_NAME,
1396 ROLE_NAME,
1397 USER_ORIG_SYSTEM,
1398 USER_ORIG_SYSTEM_ID,
1399 ROLE_ORIG_SYSTEM,
1400 ROLE_ORIG_SYSTEM_ID,
1401 START_DATE,
1402 EXPIRATION_DATE,
1403 USER_START_DATE,
1404 USER_END_DATE,
1405 ROLE_START_DATE,
1406 ROLE_END_DATE,
1407 EFFECTIVE_START_DATE,
1408 EFFECTIVE_END_DATE,
1409 SECURITY_GROUP_ID,
1410 PARTITION_ID,
1411 OWNER_TAG,
1412 CREATED_BY,
1413 CREATION_DATE,
1414 LAST_UPDATED_BY,
1415 LAST_UPDATE_DATE,
1416 LAST_UPDATE_LOGIN,
1417 ASSIGNMENT_TYPE) values
1418 (l_userTAB(userIND),
1419 l_roleTAB(roleIND),
1420 l_uorigSysTAB(userIND),
1421 l_uorigSysIDTAB(userIND),
1422 l_rorigSysTAB(roleIND),
1423 l_rorigSysIDTAB(roleIND),
1424 l_startDateTAB(userIND),
1425 l_endDateTAB(userIND),
1426 trunc(l_uStartDateTAB(userIND)),
1427 trunc(l_uEndDateTAB(userIND)),
1428 trunc(l_rStartDateTAB(roleIND)),
1429 trunc(l_rEndDateTAB(roleIND)),
1430 l_effStartDate,
1431 l_effEndDate,
1432 NULL,
1433 l_rpartIDTAB(roleIND),
1434 l_ownerTAB(userIND),
1435 WFA_SEC.user_id,
1436 sysdate,
1437 WFA_SEC.user_id,
1438 sysdate,
1439 WFA_SEC.login_id,
1440 l_AssignmentType);
1441 exception
1442 when DUP_VAL_ON_INDEX then
1443 --The record already exists, so we will just update the
1444 --timestamp
1445 if (l_assignmentType = 'I') then
1446 SELECT min(start_date)
1447 INTO l_startDateTAB(userIND)
1448 FROM WF_USER_ROLE_ASSIGNMENTS_V
1449 WHERE USER_NAME = l_userTAB(userIND)
1450 AND ROLE_NAME = l_roleTAB(roleIND);
1451
1452 SELECT max(end_date)
1453 INTO l_endDateTAB(userIND)
1454 FROM WF_USER_ROLE_ASSIGNMENTS_V
1455 WHERE USER_NAME = l_userTAB(userIND)
1456 AND ROLE_NAME = l_roleTAB(roleIND);
1457
1458 end if;
1459
1460 update WF_LOCAL_USER_ROLES
1461 set START_DATE = trunc(l_startDateTAB(userIND)),
1462 EXPIRATION_DATE = trunc(l_endDateTAB(userIND)),
1463 USER_START_DATE = trunc(l_uStartDateTAB(userIND)),
1464 USER_END_DATE = trunc(l_uEndDateTAB(userIND)),
1465 ROLE_START_DATE = trunc(l_rStartDateTAB(roleIND)),
1466 ROLE_END_DATE = trunc(l_rEndDateTAB(roleIND)),
1467 EFFECTIVE_START_DATE = l_effStartDate,
1468 EFFECTIVE_END_DATE = l_effEndDate,
1469 LAST_UPDATED_BY = WFA_SEC.user_id,
1470 LAST_UPDATE_DATE = sysdate,
1471 LAST_UPDATE_LOGIN = WFA_SEC.login_id,
1472 ASSIGNMENT_TYPE = l_AssignmentType
1473 where USER_NAME = l_userTAB(userIND)
1474 and ROLE_NAME = l_roleTAB(roleIND)
1475 and USER_ORIG_SYSTEM = l_uorigSysTAB(userIND)
1476 and USER_ORIG_SYSTEM_ID = l_uorigSysIDTAB(userIND)
1477 and ROLE_ORIG_SYSTEM = l_rorigSysTAB(roleIND)
1478 and ROLE_ORIG_SYSTEM_ID = l_rorigSysIDTAB(roleIND);
1479
1480
1481 end;
1482 end loop UserRoles;
1483 commit; --Commiting the inherited user/roles for this user.
1484 end loop Users;
1485 end if;
1486 end if; --(if ENABLED_FLAG = 'N')
1487 end Propagate;
1488
1489 --
1490 -- Propagate_CP (PRIVATE)
1491 -- Concurrent program wrapper to call Propagate().
1492 -- IN
1493 -- p_relationship_id (VARCHAR2)
1494 -- retcode [VARCHAR2]
1495 -- errbuf [VARCHAR2]
1496
1497 procedure Propagate_CP (retcode out NOCOPY VARCHAR2,
1498 errbuf out NOCOPY VARCHAR2,
1499 p_relationship_id in VARCHAR2) is
1500
1501 TYPE numTAB is table of NUMBER;
1502 relIDTAB numTAB;
1503 relIND number;
1504 l_propagateDate date;
1505
1506 begin
1507 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1508 -- Log only
1509 -- BINDVAR_SCAN_IGNORE[3]
1510 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1511 g_modulePkg||'.Propagate_CP',
1512 'Begin Propagate_CP('||p_relationship_id||')');
1513 end if;
1514 errbuf := '';
1515 l_propagateDate := sysdate;
1516 if (p_relationship_id = 'ALL') then
1517 SELECT relationship_id
1518 BULK COLLECT INTO relIDTAB
1519 FROM WF_ROLE_HIERARCHIES
1520 WHERE (PROPAGATE_DATE is NULL or
1521 ((PROPAGATE_DATE is NOT NULL) and
1522 (PROPAGATE_DATE < LAST_UPDATE_DATE)));
1523
1524 if (relIDTAB.COUNT > 0) then
1525 for relIND in relIDTAB.FIRST..relIDTAB.LAST loop
1526 propagate(p_relationship_id=>relIDTAB(relIND),
1527 p_propagateDate=>l_propagateDate);
1528 end loop;
1529 end if;
1530 else
1531 propagate(p_relationship_id=>to_number(Propagate_CP.p_relationship_id));
1532 end if;
1533
1534 retcode := '0';
1535 commit;
1536 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1537 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1538 g_modulePkg||'.Propagate_CP',
1539 'End Propagate_CP('||p_relationship_id||')');
1540 end if;
1541 exception
1542 when OTHERS then
1543 if(wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
1544 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
1545 g_modulePkg||'.Propagate_CP', 'Exception: '||
1546 sqlerrm);
1547 end if;
1548
1549 retcode := '2';
1550 errbuf := sqlerrm;
1551 WF_CORE.Clear;
1552 end Propagate_CP;
1553
1554
1555 -- Denormalize_UR_Assignments(PRIVATE)
1556 -- Procedure to update the user and role dates and
1557 -- effective dates of user/role assignments.
1558 -- IN OUT
1559 -- p_event WF_EVENT_T
1560
1561
1562
1563 procedure Denormalize_UR_Assignments( p_event in out NOCOPY WF_EVENT_T)
1564 is
1565
1566
1567 l_UserName VARCHAR2(320);
1568 l_RoleName VARCHAR2(320);
1569 l_StartDate DATE;
1570 l_EndDate DATE;
1571 l_LastUpdatedBy NUMBER ;
1572 l_LastUpdateDate DATE;
1573 l_LastUpdateLogin NUMBER ;
1574 l_OrigSystem VARCHAR2(30);
1575 l_OrigSystemID NUMBER;
1576
1577 TYPE dateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1578 TYPE idTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1579 TYPE numTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1580 l_roleTAB WF_DIRECTORY.roleTable;
1581 l_userTAB WF_DIRECTORY.userTable;
1582 l_assigningRoleTAB WF_DIRECTORY.roleTable;
1583 l_asgStartTAB dateTab;
1584 l_asgEndTAB dateTab;
1585 l_rowIDTAB idTab;
1586 l_userStartTAB dateTab;
1587 l_roleStartTAB dateTab;
1588 l_userEndTAB dateTab;
1589 l_roleEndTAB dateTab;
1590 l_effStartTAB dateTab;
1591 l_effEndTAB dateTab;
1592 l_startTAB dateTab;
1593 l_endTAB dateTab;
1594 -- who column pl/sql source tables
1595 l_creatDtTAB dateTab;
1596 l_creatByTAB numTab;
1597 l_lastUpdDtTAB dateTab;
1598 l_lastUpdByTAB numTab;
1599 l_lastUpdLogTAB numTab;
1600
1601 cursor c_UserRoleAssignments (c_userName in varchar2,
1602 c_roleName in varchar2)
1603 is
1604 select ROWID, ROLE_NAME, USER_NAME,ASSIGNING_ROLE, START_DATE, END_DATE,
1605 ROLE_START_DATE, ROLE_END_DATE,USER_START_DATE, USER_END_DATE,
1606 ASSIGNING_ROLE_START_DATE, ASSIGNING_ROLE_END_DATE, EFFECTIVE_START_DATE,
1607 EFFECTIVE_END_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1608 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
1609 from WF_USER_ROLE_ASSIGNMENTS
1610 where (ROLE_NAME = c_roleName or ASSIGNING_ROLE = c_roleName)
1611 and USER_NAME=c_userName;
1612
1613 cursor c_UserRoleAssignments_u (c_userName in varchar2)
1614 is
1615 select ROWID, ROLE_NAME, USER_NAME,ASSIGNING_ROLE, START_DATE, END_DATE,
1616 ROLE_START_DATE, ROLE_END_DATE,USER_START_DATE, USER_END_DATE,
1617 ASSIGNING_ROLE_START_DATE, ASSIGNING_ROLE_END_DATE, EFFECTIVE_START_DATE,
1618 EFFECTIVE_END_DATE ,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1619 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
1620 from WF_USER_ROLE_ASSIGNMENTS
1621 where USER_NAME=c_userName;
1622
1623 cursor c_UserRoleAssignments_r (c_roleName in varchar2)
1624 is
1625 select ROWID, ROLE_NAME, USER_NAME,ASSIGNING_ROLE, START_DATE, END_DATE,
1626 ROLE_START_DATE, ROLE_END_DATE,USER_START_DATE, USER_END_DATE,
1627 ASSIGNING_ROLE_START_DATE, ASSIGNING_ROLE_END_DATE, EFFECTIVE_START_DATE,
1628 EFFECTIVE_END_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1629 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
1630 from WF_USER_ROLE_ASSIGNMENTS
1631 where ROLE_NAME = c_roleName
1632 or ASSIGNING_ROLE = c_roleName;
1633
1634 begin
1635 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1636 -- Log only
1637 -- BINDVAR_SCAN_IGNORE[3]
1638 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1639 g_modulePkg||'.Denormalize_UR_Assignments',
1640 'Begin Denormalize_UR_Assignments('||p_event.getEventName||')');
1641 end if;
1642
1643 l_RoleName := p_event.getValueForParameter('ROLE_NAME');
1644 l_UserName := p_event.getValueForParameter('USER_NAME');
1645 l_LastUpdatedBy := to_number(p_event.getValueForParameter(
1646 'LAST_UPDATED_BY'), WF_CORE.canonical_number_mask);
1647 l_LastUpdateDate := nvl(to_date(p_event.getValueForParameter(
1648 'LAST_UPDATE_DATE'),
1649 WF_CORE.canonical_date_mask),SYSDATE);
1650 l_LastUpdateLogin := to_number(p_event.getValueForParameter(
1651 'LAST_UPDATE_LOGIN'), WF_CORE.canonical_number_mask);
1652
1653 l_OrigSystem := p_event.getValueForParameter('ORIG_SYSTEM');
1654 l_OrigSystemID := to_number(p_event.getValueForParameter(
1655 'ORIG_SYSTEM_ID'), WF_CORE.canonical_number_mask);
1656 l_StartDate := trunc(to_date(p_event.getValueForParameter('START_DATE'),
1657 WF_CORE.canonical_date_mask));
1658 l_EndDate:= trunc(to_date(p_event.getValueForParameter('EXPIRATION_DATE'),
1659 WF_CORE.canonical_date_mask));
1660
1661 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
1662 open c_userRoleAssignments (l_userName , l_roleName);
1663 elsif (l_userName is NOT NULL) then
1664 open c_userRoleAssignments_u (l_userName);
1665 elsif (l_roleName is NOT NULL) then
1666 open c_userRoleAssignments_r(l_roleName);
1667 else
1668 return;
1669 end if;
1670 loop
1671
1672 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
1673 fetch c_UserRoleAssignments
1674 bulk collect into l_rowIDTAB, l_roleTAB, l_userTAB,l_assigningRoleTAB,
1675 l_startTAB,l_endTAB, l_roleStartTAB,l_roleEndTAB, l_userStartTAB,
1676 l_userEndTAB, l_asgStartTAB,l_asgEndTAB, l_effStartTAB, l_effEndTAB,
1677 l_creatbyTAB, l_creatdtTAB, l_lastupdByTAB, l_lastupdDtTAB, l_lastupdlogTAB
1678 limit g_maxRows;
1679 elsif (l_userName is NOT NULL) then
1680 fetch c_UserRoleAssignments_u
1681 bulk collect into l_rowIDTAB, l_roleTAB, l_userTAB,l_assigningRoleTAB,
1682 l_startTAB,l_endTAB, l_roleStartTAB,l_roleEndTAB, l_userStartTAB,
1683 l_userEndTAB, l_asgStartTAB,l_asgEndTAB, l_effStartTAB, l_effEndTAB,
1684 l_creatbyTAB, l_creatdtTAB, l_lastupdByTAB, l_lastupdDtTAB, l_lastupdlogTAB
1685 limit g_maxRows;
1686 elsif (l_roleName is NOT NULL) then
1687 fetch c_UserRoleAssignments_r
1688 bulk collect into l_rowIDTAB, l_roleTAB, l_userTAB,l_assigningRoleTAB,
1689 l_startTAB,l_endTAB, l_roleStartTAB,l_roleEndTAB, l_userStartTAB,
1690 l_userEndTAB, l_asgStartTAB,l_asgEndTAB, l_effStartTAB, l_effEndTAB,
1691 l_creatbyTAB, l_creatdtTAB, l_lastupdByTAB, l_lastupdDtTAB, l_lastupdlogTAB
1692 limit g_maxRows;
1693 end if;
1694
1695 --We now have pl/sql tables in memory that we can update with the new
1696 --values. So we loop through them and begin the processing.
1697
1698
1699 if (l_rowIDTAB.COUNT > 0) then
1700 ---We don't want to loop if there are no records that meet our
1701 --criteria, we could stop right now.
1702
1703 for tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST loop
1704 if (l_roleName is not null and l_userName is null
1705 and l_roleTAB(tabIndex) = l_roleName) then
1706 l_roleStartTAB(tabIndex) := l_startDate;
1707 l_roleEndTAB(tabIndex) := l_endDate;
1708 end if;
1709
1710 if (l_roleName is not null and l_userName is null
1711 and l_assigningRoleTAB(tabIndex) = l_roleName) then
1712 --This was an assigning role
1713 l_asgStartTAB(tabIndex) := l_startDate;
1714 l_asgEndTAB(tabIndex) := l_endDate;
1715 end if;
1716
1717
1718 if (l_userName is not null and l_roleName is null
1719 and l_userTAB(tabIndex) = l_userName) then
1720 l_userStartTAB(tabIndex) := l_startDate;
1721 l_userEndTAB(tabIndex) := l_endDate;
1722 --Check the self reference
1723 if (l_userTAB(tabIndex) = l_roleTab(tabIndex)) then
1724 l_roleStartTAB(tabIndex) := l_startDate;
1725 l_asgStartTAB(tabIndex) := l_startDate;
1726 l_roleEndTAB(tabIndex) := l_endDate;
1727 l_asgEndTAB(tabIndex) := l_endDate;
1728 l_startTAB(tabIndex) := l_startDate;
1729 l_endTAB(tabIndex) := l_endDate;
1730 -- also update WHO columns in case of self-reference
1731 l_lastUpdLogTAB(tabIndex):=nvl(l_lastUpdateLogin,WFA_SEC.Login_ID);
1732 l_lastUpdByTAB(tabIndex):=nvl(l_lastUpdatedBy, WFA_SEC.User_ID);
1733 l_lastUpdDtTAB(tabIndex):=nvl(l_lastUpdateDate,SYSDATE);
1734 end if;
1735 end if;
1736
1737 if (l_userName is not null and l_roleName is null
1738 and l_roleTAB(tabIndex) = l_userName) then
1739 --Case when user=role
1740 l_roleStartTAB(tabIndex) := l_startDate;
1741 l_roleEndTAB(tabIndex) := l_endDate;
1742 end if;
1743
1744 --Now we want to calculate the effective start and end dates
1745 --for this assignment.
1746 Calculate_Effective_Dates(l_startTAB(tabIndex),
1747 l_endTAB(tabIndex),
1748 l_userStartTAB(tabIndex),
1749 l_userEndTAB(tabIndex),
1750 l_roleStartTAB(tabIndex),
1751 l_roleEndTAB(tabIndex),
1752 l_asgStartTAB(tabIndex),
1753 l_asgEndTAB(tabIndex),
1754 l_effStartTAB(tabIndex),
1755 l_effEndTAB(tabIndex));
1756
1757 end loop;
1758
1759 --After this point we have a complete series of pl/sql tables with
1760 --all of the start/end dates and calculated effective start/end dates
1761 --We can then issue the bulk update.
1762 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
1763
1764 update WF_USER_ROLE_ASSIGNMENTS
1765 set ROLE_START_DATE = l_roleStartTAB(tabIndex),
1766 ROLE_END_DATE = l_roleEndTAB(tabIndex),
1767 USER_START_DATE = l_userStartTAB(tabIndex),
1768 USER_END_DATE = l_userEndTAB(tabIndex),
1769 EFFECTIVE_START_DATE = l_effStartTAB(tabIndex),
1770 EFFECTIVE_END_DATE = l_effEndTAB(tabIndex),
1771 START_DATE=l_startTAB(tabIndex),
1772 END_DATE=l_endTAB(tabIndex),
1773 ASSIGNING_ROLE_START_DATE = l_asgStartTAB(tabIndex),
1774 ASSIGNING_ROLE_END_DATE = l_asgEndTAB(tabIndex),
1775 LAST_UPDATED_BY = l_lastUpdByTAB(tabIndex),
1776 LAST_UPDATE_DATE = l_lastUpdDtTAB(tabIndex),
1777 LAST_UPDATE_LOGIN = l_lastUpdLogTAB(tabIndex)
1778 where rowid = l_rowIDTAB(tabIndex);
1779
1780 end if; --if (l_rowIDTAB.COUNT > 0)
1781
1782 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
1783 exit when c_userRoleAssignments%notfound;
1784 elsif (l_userName is NOT NULL) then
1785 exit when c_userRoleAssignments_u%notfound;
1786 elsif (l_roleName is NOT NULL) then
1787 exit when c_userRoleAssignments_r%notfound;
1788 end if;
1789
1790 end loop;
1791
1792
1793 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
1794 close c_UserRoleAssignments;
1795 elsif (l_userName is NOT NULL) then
1796 close c_UserRoleAssignments_u;
1797 elsif (l_roleName is NOT NULL) then
1798 close c_UserRoleAssignments_r;
1799 end if;
1800
1801 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1802 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1803 g_modulePkg||'.Denormalize_UR_Assignments',
1804 'End Denormalize_UR_Assignments');
1805 end if;
1806
1807 exception
1808 when OTHERS then
1809 if c_UserRoleAssignments%ISOPEN then
1810 close c_UserRoleAssignments;
1811 elsif c_UserRoleAssignments_u%ISOPEN then
1812 close c_UserRoleAssignments_u;
1813 elsif c_UserRoleAssignments_r%ISOPEN then
1814 close c_UserRoleAssignments_r;
1815 end if;
1816
1817 if(wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
1818 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
1819 g_modulePkg||'.Denormalize_UR_Assignments',
1820 'Exception: '||sqlerrm);
1821 end if;
1822 WF_CORE.Context('WF_ROLE_HIERARCHY', 'Denormalize_UR_Assignments',
1823 p_event.getEventName);
1824 raise;
1825
1826
1827 end Denormalize_UR_Assignments;
1828
1829
1830
1831 -- Aggregate_User_Roles_RF(PRIVATE)
1832 -- Rule Function to update WF_LOCAL_USER_ROLES as
1833 -- summary table of WF_USER_ROLE_ASSIGNMENTS
1834 -- IN
1835 -- p_sub_guid (RAW)
1836 -- p_event ([WF_EVENT_T])
1837 -- returns
1838 -- VARCHAR2
1839 function Aggregate_User_Roles_RF ( p_sub_guid in RAW,
1840 p_event in out NOCOPY WF_EVENT_T)
1841 return VARCHAR2 is
1842
1843 l_UserName VARCHAR2(320);
1844 l_RoleName VARCHAR2(320);
1845 l_RoleStartDate DATE;
1846 l_RoleEndDate DATE;
1847 l_userStartDate DATE;
1848 l_effStartDate DATE;
1849 l_effEndDate DATE;
1850 l_userEndDate DATE;
1851 l_LastUpdatedBy NUMBER;
1852 l_LastUpdateDate DATE;
1853 l_LastUpdateLogin NUMBER;
1854 l_CreatedBy NUMBER;
1855 l_CreationDate DATE;
1856 sumTabIndex NUMBER;
1857 overWrite BOOLEAN;
1858 l_AssignmentType VARCHAR2(1);
1859 l_rowid rowid;
1860
1861 TYPE charTab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
1862 TYPE dateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1863 TYPE numTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1864 TYPE idTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1865
1866 l_roleSrcTAB WF_DIRECTORY.roleTable;
1867 l_userSrcTAB WF_DIRECTORY.userTable;
1868 l_roleDestTAB WF_DIRECTORY.roleTable;
1869 l_userDestTAB WF_DIRECTORY.userTable;
1870
1871 l_rowIDTAB idTab;
1872 l_userStartSrcTAB dateTab;
1873 l_roleStartSrcTAB dateTab;
1874 l_userEndSrcTAB dateTab;
1875 l_roleEndSrcTAB dateTab;
1876 l_effStartSrcTAB dateTab;
1877 l_effEndSrcTAB dateTab;
1878 l_AssignTAB charTab;
1879 l_userStartDestTAB dateTab;
1880 l_roleStartDestTAB dateTab;
1881 l_userEndDestTAB dateTab;
1882 l_roleEndDestTAB dateTab;
1883 l_effStartDestTAB dateTab;
1884 l_effEndDestTAB dateTab;
1885 l_startSrcTAB dateTab;
1886 l_endSrcTAB dateTab;
1887 l_startDestTAB dateTab;
1888 l_endDestTAB dateTab;
1889 l_relIDTAB numTab;
1890 -- who column pl/sql source tables
1891 l_creatDtSrcTAB dateTab;
1892 l_creatBySrcTAB numTab;
1893 l_lastUpdDtSrcTAB dateTab;
1894 l_lastUpdBySrcTAB numTab;
1895 l_lastUpdLogSrcTAB numTab;
1896
1897 -- who column pl/sql summary tables
1898 l_creatDtDestTAB dateTab;
1899 l_creatByDestTAB numTab;
1900 l_lastUpdDtDestTAB dateTab;
1901 l_lastUpdByDestTAB numTab;
1902 l_lastUpdLogDestTAB numTab;
1903
1904 --retrieve all WF_USER_ROLE_ASSIGNMENTS records which need to
1905 --get summarised
1906 --A User/Role was updated.
1907
1908 --<6028394:rwunderl> Sub-select necessary to catch effected user/roles
1909 cursor c_userRoleAssignments (c_userName in varchar2,
1910 c_roleName in varchar2) is
1911 select /*+ use_concat */ ROWID, ROLE_NAME, USER_NAME, ROLE_START_DATE,
1912 ROLE_END_DATE, USER_START_DATE, USER_END_DATE, EFFECTIVE_START_DATE,
1913 EFFECTIVE_END_DATE, START_DATE, END_DATE, RELATIONSHIP_ID,
1914 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1915 CREATION_DATE, CREATED_BY
1916 from WF_USER_ROLE_ASSIGNMENTS
1917 where USER_NAME=c_userName
1918 and ROLE_NAME in (select ROLE_NAME
1919 from WF_USER_ROLE_ASSIGNMENTS
1920 where ASSIGNING_ROLE = c_roleName
1921 and USER_NAME = c_userName)
1922 order by ROLE_NAME, USER_NAME;
1923
1924
1925 --A User was updated.
1926 cursor c_userRoleAssignments_u (c_userName in varchar2) is
1927
1928 select ROWID, ROLE_NAME, USER_NAME, ROLE_START_DATE,
1929 ROLE_END_DATE, USER_START_DATE, USER_END_DATE, EFFECTIVE_START_DATE,
1930 EFFECTIVE_END_DATE, START_DATE, END_DATE, RELATIONSHIP_ID,
1931 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1932 CREATION_DATE, CREATED_BY
1933 from WF_USER_ROLE_ASSIGNMENTS
1934 where USER_NAME=c_userName
1935 order by ROLE_NAME, USER_NAME;
1936
1937 --A Role was updated.
1938 cursor c_userRoleAssignments_r (c_roleName in varchar2) is
1939
1940 -- <bug 6665149> replaced query to also include those rows whose user, role
1941 -- pair match the directly effected user role through the
1942 -- given assigning role (the changed role).
1943 -- select ROWID, ROLE_NAME, USER_NAME, ROLE_START_DATE,
1944 -- ROLE_END_DATE, USER_START_DATE, USER_END_DATE, EFFECTIVE_START_DATE,
1945 -- EFFECTIVE_END_DATE, START_DATE,END_DATE, RELATIONSHIP_ID,
1946 -- LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1947 -- CREATION_DATE, CREATED_BY
1948 -- from WF_USER_ROLE_ASSIGNMENTS
1949 -- where ROLE_NAME=c_roleName or ASSIGNING_ROLE=c_roleName
1950 -- order by ROLE_NAME, USER_NAME;
1951 select ura.ROWID, ura.ROLE_NAME, ura.USER_NAME, ura.ROLE_START_DATE,
1952 ura.ROLE_END_DATE, ura.USER_START_DATE, ura.USER_END_DATE, ura.EFFECTIVE_START_DATE,
1953 ura.EFFECTIVE_END_DATE, ura.START_DATE, ura.END_DATE, ura.RELATIONSHIP_ID,
1954 ura.LAST_UPDATE_DATE, ura.LAST_UPDATED_BY, ura.LAST_UPDATE_LOGIN,
1955 ura.CREATION_DATE, ura.CREATED_BY
1956 from WF_USER_ROLE_ASSIGNMENTS ura,
1957 WF_USER_ROLE_ASSIGNMENTS ura2
1958 where ura2.ASSIGNING_ROLE= c_roleName
1959 and ura2.ROLE_NAME= ura.ROLE_NAME
1960 and ura2.USER_NAME = ura.USER_NAME
1961 order by ura.ROLE_NAME, ura.USER_NAME;
1962 -- </bug 6665149>
1963
1964 begin
1965 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1966 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1967 g_modulePkg||'.Aggregate_User_Roles_RF',
1968 'Begin Aggregate_User_Roles_RF('||rawtohex(p_sub_guid)||', '||
1969 p_event.getEventName||')');
1970 end if;
1971
1972
1973
1974 l_rowid := chartorowid(p_event.getValueForParameter('ROWID'));
1975
1976 l_RoleName := p_event.getValueForParameter('ROLE_NAME');
1977 l_UserName := p_event.getValueForParameter('USER_NAME');
1978 /* l_LastUpdatedBy := to_number(p_event.getValueForParameter(
1979 'LAST_UPDATED_BY'));
1980 l_LastUpdateDate := nvl(to_date(p_event.getValueForParameter(
1981 'LAST_UPDATE_DATE'),
1982 WF_CORE.canonical_date_mask),SYSDATE);
1983 l_LastUpdateLogin := to_number(p_event.getValueForParameter(
1984 'LAST_UPDATE_LOGIN'));
1985 l_CreatedBy := to_number(p_event.getValueForParameter('CREATED_BY'));
1986 l_CreationDate := to_date(p_event.getValueForParameter('CREATION_DATE'),
1987 WF_CORE.canonical_date_mask);*/
1988
1989 if (p_event.getValueForParameter('WFSYNCH_OVERWRITE') ='TRUE') then
1990 OverWrite := TRUE;
1991 else
1992 OverWrite:= FALSE;
1993 end if;
1994
1995
1996 --First check to see if we even have to run.
1997 if ((p_event.getValueForParameter('OLD_START_DATE') = '*UNDEFINED*') and
1998 (p_event.getValueForParameter('OLD_END_DATE') = '*UNDEFINED*')) then
1999
2000 --we might need to recalculate assignment type if a user/role propagation
2001 --has trigerred this rule function
2002 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
2003 l_assignmentType := AssignmentType(p_UserName=>l_userName,
2004 p_RoleName=>l_roleName);
2005
2006 --Validate the assignment type status
2007 UPDATE WF_LOCAL_USER_ROLES
2008 SET ASSIGNMENT_TYPE = l_assignmentType
2009 WHERE ROWID = l_rowid;
2010 end if;
2011
2012 return 'SUCCESS';
2013 end if;
2014
2015 ---retrieve all WF_USER_ROLE_ASSIGNMENTS records which need to
2016 --get summarized
2017
2018 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
2019 open c_userRoleAssignments (l_userName , l_roleName);
2020 elsif (l_userName is NOT NULL) then
2021 open c_userRoleAssignments_u (l_userName);
2022 elsif (l_roleName is NOT NULL) then
2023 open c_userRoleAssignments_r(l_roleName);
2024 else
2025 return 'SUCCESS';
2026 end if;
2027
2028 sumTabIndex := 0;
2029
2030 loop
2031 -- fetch a new batch of records
2032 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
2033 fetch c_userRoleAssignments bulk collect into
2034 l_rowIDTAB, l_roleSrcTAB, l_userSrcTAB, l_roleStartSrcTAB,
2035 l_roleEndSrcTAB,l_userStartSrcTAB, l_userEndSrcTAB, l_effStartSrcTAB,
2036 l_effEndSrcTAB, l_StartSrcTAB, l_EndSrcTAB, l_relIDTAB,
2037 l_lastUpdDtSrcTAB, l_lastUpdBySrcTAB,l_lastUpdLogSrcTAB,
2038 l_creatDtSrcTAB, l_creatBySrcTAB limit g_maxRows;
2039 elsif (l_userName is NOT NULL) then
2040 fetch c_userRoleAssignments_u bulk collect into
2041 l_rowIDTAB, l_roleSrcTAB, l_userSrcTAB, l_roleStartSrcTAB,
2042 l_roleEndSrcTAB,l_userStartSrcTAB, l_userEndSrcTAB, l_effStartSrcTAB,
2043 l_effEndSrcTAB, l_StartSrcTAB, l_EndSrcTAB, l_relIDTAB,
2044 l_lastUpdDtSrcTAB, l_lastUpdBySrcTAB,l_lastUpdLogSrcTAB,
2045 l_creatDtSrcTAB, l_creatBySrcTAB limit g_maxRows;
2046 elsif (l_roleName is NOT NULL) then
2047 fetch c_userRoleAssignments_r bulk collect into
2048 l_rowIDTAB, l_roleSrcTAB, l_userSrcTAB, l_roleStartSrcTAB,
2049 l_roleEndSrcTAB,l_userStartSrcTAB, l_userEndSrcTAB, l_effStartSrcTAB,
2050 l_effEndSrcTAB, l_StartSrcTAB, l_EndSrcTAB, l_relIDTAB,
2051 l_lastUpdDtSrcTAB, l_lastUpdBySrcTAB,l_lastUpdLogSrcTAB,
2052 l_creatDtSrcTAB, l_creatBySrcTAB limit g_maxRows;
2053 end if;
2054
2055
2056 if (l_rowIDTAB.COUNT > 0) then
2057 ---We don't want to loop if there are no records that meet our criteria
2058 --we could stop right now.
2059
2060 for tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST loop
2061
2062 --we need to insert into summary table if this is the first
2063 --record to be inserted or, we have a new user/role combination
2064 --in the assignment table, which hasnt yet been inserted into the
2065 --summary table
2066 if ((tabIndex=l_rowIDTAB.FIRST and l_roleDestTab.COUNT < 1) or
2067 (l_roleDestTab.COUNT >=1 and
2068 ((l_roleSrcTAB(tabIndex) <> l_roleDestTAB(sumTabIndex))
2069 or (l_userSrcTAB(tabIndex) <> l_userDestTAB(sumTabIndex))))) then
2070
2071 -- before inserting, check whether the summarytable has
2072 -- grown too large
2073
2074 if sumTabIndex >= g_maxRows then
2075
2076 --limit reached for summary table, so perform
2077 --the bulk update and clear off the table.
2078 --We need to perform the bulk update here in addition to
2079 --bulk update after exit from the loop, so that clearing
2080 --the summary table will not lose user/role effective date
2081 --information when duplicate user/role
2082 --combinations are spread across multiple groups
2083 if (OverWrite) then
2084 --allow update of created_by and creation_date
2085 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
2086
2087 UPDATE WF_LOCAL_USER_ROLES wur
2088 SET
2089 ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
2090 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
2091 USER_START_DATE = l_userStartDestTAB(destTabIndex),
2092 USER_END_DATE = l_userEndDestTAB(destTabIndex),
2093 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
2094 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
2095 START_DATE = l_startDestTAB(destTabIndex),
2096 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
2097 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
2098 LAST_UPDATED_BY = l_lastUpdByDestTAB(destTabIndex),
2099 LAST_UPDATE_LOGIN = l_lastUpdLogDestTAB(destTabIndex),
2100 CREATION_DATE = nvl(l_creatDtSrcTAB(destTabIndex),CREATION_DATE),
2101 CREATED_BY= nvl(l_creatBySrcTAB(destTabIndex), CREATED_BY),
2102 LAST_UPDATE_DATE = l_lastUpdDtDestTAB(destTabIndex)
2103 WHERE ROLE_NAME = l_roleDestTAB(destTabIndex)
2104 AND USER_NAME = l_userDestTAB(destTabIndex);
2105 else
2106 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
2107 UPDATE WF_LOCAL_USER_ROLES wur
2108 SET
2109 ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
2110 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
2111 USER_START_DATE = l_userStartDestTAB(destTabIndex),
2112 USER_END_DATE = l_userEndDestTAB(destTabIndex),
2113 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
2114 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
2115 START_DATE = l_startDestTAB(destTabIndex),
2116 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
2117 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
2118 LAST_UPDATED_BY = l_lastUpdByDestTAB(destTabIndex),
2119 LAST_UPDATE_LOGIN = l_lastUpdLogDestTAB(destTabIndex),
2120 LAST_UPDATE_DATE = l_lastUpdDtDestTAB(destTabIndex)
2121 WHERE ROLE_NAME = l_roleDestTAB(destTabIndex)
2122 AND USER_NAME = l_userDestTAB(destTabIndex);
2123 end if;
2124 l_roleStartDestTAB.DELETE;
2125 l_roleEndDestTAB.DELETE;
2126 l_userStartDestTAB.DELETE;
2127 l_userEndDestTAB.DELETE;
2128 l_effStartDestTAB.DELETE;
2129 l_effEndDestTAB.DELETE;
2130 l_assignTAB.DELETE;
2131 l_roleDestTAB.DELETE;
2132 l_userDestTAB.DELETE;
2133 l_startDestTAB.DELETE;
2134 l_endDestTAB.DELETE;
2135 l_lastUpdDtDestTAB.DELETE;
2136 l_lastUpdByDestTAB.DELETE;
2137 l_lastUpdLogDestTAB.DELETE;
2138 l_creatDtDestTAB.DELETE;
2139 l_creatByDestTAB.DELETE;
2140
2141 sumTabIndex := 0;
2142
2143 end if;
2144
2145
2146 --now perform the insert
2147 sumTabIndex := sumTabIndex + 1;
2148 l_RoleDestTAB(sumTabIndex) :=l_roleSrcTAB(tabIndex);
2149 l_UserDestTAB(sumTabIndex) :=l_userSRcTAB(tabIndex);
2150 l_roleStartDestTAB(sumTabIndex):=l_roleStartSrcTAB(tabIndex);
2151 l_roleEndDestTAB(sumTabIndex) :=l_roleEndSrcTAB(tabIndex);
2152 l_userStartDestTAB(sumTabIndex):=l_userStartSrcTAB(tabIndex);
2153 l_userEndDestTAB(sumTabIndex) :=l_userEndSrcTAB(tabIndex);
2154 l_effStartDestTAB(sumTabIndex) :=l_effStartSrcTAB(tabIndex);
2155 l_effEndDestTAB(sumTabIndex) :=l_effEndSrcTAB(tabIndex);
2156 l_lastUpdDtDestTAB(sumTabIndex):=l_lastUpdDtSrcTAB(tabIndex);
2157 l_lastUpdByDestTAB(sumTabIndex):=l_lastUpdBySrcTAB(tabIndex);
2158 l_lastUpdLogDestTAB(sumTabIndex):=l_lastUpdLogSrcTAB(tabIndex);
2159 l_creatDtDestTAB(sumTabIndex) :=l_creatDtSrcTAB(tabIndex);
2160 l_creatByDestTAB(sumTabIndex) := l_creatBySrcTAB(tabIndex);
2161
2162 if l_relIDTAB(tabIndex) = -1 then
2163 l_AssignTAB(sumTabIndex):='D';
2164 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
2165 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
2166 else
2167 l_AssignTAB(sumTabIndex) :='I';
2168 l_startDestTAB(sumTabIndex) :=null;
2169 l_endDestTAB(sumTabIndex) :=null;
2170 end if;
2171
2172
2173 else
2174 -- check whether we have a duplicate user/role combination
2175 if ((l_roleSrcTAB(tabIndex) = l_roleDestTAB(sumTabIndex)) and
2176 (l_userSrcTAB(tabIndex) = l_userDestTAB(sumTabIndex))) then
2177
2178 --update effective_dates
2179
2180 if l_effStartSrcTAB(tabIndex) <
2181 l_effStartDestTAB(sumTabIndex) then
2182 l_effStartDestTAB(sumTabIndex):= l_effStartSrcTAB(tabIndex);
2183 end if;
2184
2185 if l_effEndSrcTAB(tabIndex) > l_effEndDestTAB(sumTabIndex) then
2186 l_effEndDestTAB(sumTabIndex) := l_effEndSrcTAB(tabIndex);
2187 end if;
2188
2189 -- update the last update date and last update login if it is later
2190
2191 if l_lastUpdDtSrcTAB(tabIndex) > l_lastUpdDtDestTAB(sumTabIndex) then
2192 l_lastUpdDtDestTAB(sumTabIndex):=l_lastUpdDtSrcTAB(tabIndex);
2193 l_lastUpdByDestTAB(sumTabIndex):=l_lastUpdBySrcTAB(tabIndex);
2194 l_lastUpdLogDestTAB(sumTabIndex):=l_lastUpdLogSrcTAB(tabIndex);
2195 end if;
2196 --if this is a direct assignment, the start and end dates need to
2197 --be set.
2198 if l_relIDTAB(tabIndex) = -1 then
2199
2200 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
2201 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
2202 end if;
2203
2204 --if the assignment type in summary table is Direct and
2205 --we encountered an inherited assignment in the Assignment table
2206 --or if the assignment type in summary table is inherited and we
2207 --encountered a direct assignment in the Assignment table
2208 --update the assignment_Type to Both
2209
2210 if (((l_AssignTAB(sumTabIndex) = 'D') and
2211 (l_relIDTAB(tabIndex) <> -1)) or
2212 ((l_AssignTAB(sumTabIndex) = 'I') and
2213 (l_relIDTAB(tabIndex) = -1))) then
2214
2215 l_AssignTAB(sumTabIndex) := 'B';
2216
2217 end if;
2218 end if;
2219 end if;
2220
2221 end loop;
2222 end if;
2223
2224 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
2225 exit when c_userRoleAssignments%notfound;
2226 elsif (l_userName is NOT NULL) then
2227 exit when c_userRoleAssignments_u%notfound;
2228 elsif (l_roleName is NOT NULL) then
2229 exit when c_userRoleAssignments_r%notfound;
2230 end if;
2231
2232 end loop;
2233
2234 if (l_userName is NOT NULL and l_roleName is NOT NULL) then
2235 close c_UserRoleAssignments;
2236 elsif (l_userName is NOT NULL) then
2237 close c_UserRoleAssignments_u;
2238 elsif (l_roleName is NOT NULL) then
2239 close c_UserRoleAssignments_r;
2240 end if;
2241
2242 --when we reach here, we need to bulk update the leftover records ,
2243 --if any, in the summary table.
2244 if sumTabIndex> 0 then
2245 if (OverWrite) then
2246 --allow update of created_by and creation_date
2247 forall tabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
2248
2249 UPDATE WF_LOCAL_USER_ROLES wur
2250 SET
2251 ROLE_START_DATE = l_roleStartDestTAB(tabIndex),
2252 ROLE_END_DATE = l_roleEndDestTAB(tabIndex),
2253 USER_START_DATE = l_userStartDestTAB(tabIndex),
2254 USER_END_DATE = l_userEndDestTAB(tabIndex),
2255 EFFECTIVE_START_DATE = l_effStartDestTAB(tabIndex),
2256 EFFECTIVE_END_DATE = l_effEndDestTAB(tabIndex),
2257 START_DATE = l_startDestTAB(tabIndex),
2258 EXPIRATION_DATE = l_endDestTAB(tabIndex),
2259 ASSIGNMENT_TYPE = l_assignTAB(tabIndex),
2260 LAST_UPDATED_BY = l_lastUpdByDestTAB(tabIndex),
2261 LAST_UPDATE_LOGIN = l_lastUpdLogDestTAB(tabIndex),
2262 LAST_UPDATE_DATE = l_lastUpdDtDestTAB(tabIndex),
2263 CREATION_DATE = nvl(l_creatDtSrcTAB(tabIndex),CREATION_DATE),
2264 CREATED_BY= nvl(l_creatBySrcTAB(tabIndex), CREATED_BY)
2265 WHERE ROLE_NAME = l_roleDestTAB(tabIndex)
2266 AND USER_NAME = l_userDestTAB(tabIndex);
2267 else
2268 forall tabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
2269
2270 UPDATE WF_LOCAL_USER_ROLES wur
2271 SET
2272 ROLE_START_DATE = l_roleStartDestTAB(tabIndex),
2273 ROLE_END_DATE = l_roleEndDestTAB(tabIndex),
2274 USER_START_DATE = l_userStartDestTAB(tabIndex),
2275 USER_END_DATE = l_userEndDestTAB(tabIndex),
2276 EFFECTIVE_START_DATE = l_effStartDestTAB(tabIndex),
2277 EFFECTIVE_END_DATE = l_effEndDestTAB(tabIndex),
2278 START_DATE = l_startDestTAB(tabIndex),
2279 EXPIRATION_DATE = l_endDestTAB(tabIndex),
2280 ASSIGNMENT_TYPE = l_assignTAB(tabIndex),
2281 LAST_UPDATED_BY = l_lastUpdByDestTAB(tabIndex),
2282 LAST_UPDATE_LOGIN = l_lastUpdLogDestTAB(tabIndex),
2283 LAST_UPDATE_DATE = l_lastUpdDtDestTAB(tabIndex)
2284 WHERE ROLE_NAME = l_roleDestTAB(tabIndex)
2285 AND USER_NAME = l_userDestTAB(tabIndex);
2286 end if;
2287 end if;
2288
2289 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2290 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
2291 g_modulePkg||'.Aggregate_User_Roles_RF',
2292 'End Aggregate_User_Roles_RF('||rawtohex(p_sub_guid)||', '||
2293 p_event.getEventName||')');
2294 end if;
2295 return 'SUCCESS';
2296
2297 EXCEPTION WHEN OTHERS THEN
2298 if c_UserRoleAssignments%ISOPEN then
2299 close c_UserRoleAssignments;
2300 elsif c_UserRoleAssignments_u%ISOPEN then
2301 close c_UserRoleAssignments_u;
2302 elsif c_UserRoleAssignments_r%ISOPEN then
2303 close c_UserRoleAssignments_r;
2304 end if;
2305
2306 WF_CORE.Context('WF_ROLE_HIERARCHY', 'Aggregate_User_Roles_RF',
2307 p_event.getEventName( ), p_sub_guid);
2308
2309 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2310 return 'ERROR';
2311
2312
2313 end Aggregate_User_Roles_RF;
2314
2315
2316
2317
2318
2319 -- Public APIs
2320 --
2321 --
2322
2323 --
2324 -- GetRelationships (PUBLIC)
2325 -- Retrieves the hierarchies for a given role.
2326 -- IN
2327 -- p_name (VARCHAR2)
2328 -- p_superiors (WF_ROLE_HIERARCHY.relTAB)
2329 -- p_subordinates (WF_ROLE_HIERARCHY.relTAB)
2330
2331 procedure GetRelationships (p_name in VARCHAR2,
2332 p_superiors out NOCOPY WF_ROLE_HIERARCHY.relTAB,
2333 p_subordinates out NOCOPY WF_ROLE_HIERARCHY.relTAB,
2334 p_direction in VARCHAR2 )
2335 is
2336 --<8i Support>
2337 TYPE super_name_Tab is TABLE of VARCHAR2(320);
2338 TYPE sub_name_Tab is TABLE of VARCHAR2(320);
2339 TYPE rel_ID_Tab is TABLE of NUMBER;
2340 TYPE enabled_Tab is TABLE OF VARCHAR2(1);
2341
2342 superTab super_name_Tab;
2343 subTab sub_name_Tab;
2344 relIDTAB rel_ID_Tab;
2345 enabledTab enabled_Tab;
2346 --</8i Support>
2347
2348 begin
2349 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2350 -- Log only
2351 -- BINDVAR_SCAN_IGNORE[4]
2352 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
2353 g_modulePkg||'.GetRelationships',
2354 'Begin GetRelationships('||
2355 p_name||')');
2356 end if;
2357 begin
2358
2359 /*
2360 -- <9i Support>
2361 if (p_direction in ('SUPERIORS', 'BOTH')) then
2362 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2363 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2364 g_modulePkg||'.GetRelationships',
2365 'Retrieving the superior relationships.');
2366 end if;
2367 SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
2368 ENABLED_FLAG
2369 BULK COLLECT INTO p_superiors
2370 FROM WF_ROLE_HIERARCHIES
2371 WHERE ENABLED_FLAG = 'Y'
2372 CONNECT BY PRIOR SUPER_NAME = SUB_NAME
2373 AND ENABLED_FLAG = 'Y'
2374 START WITH SUB_NAME = upper(p_name);
2375
2376 end if;
2377
2378 if (p_direction in ('SUBORDINATES', 'BOTH')) then
2379 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2380 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2381 g_modulePkg||'.GetRelationships',
2382 'Retrieving the subordinate relationships.');
2383 end if;
2384 SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
2385 ENABLED_FLAG
2386 BULK COLLECT INTO p_subordinates
2387 FROM WF_ROLE_HIERARCHIES
2388 WHERE ENABLED_FLAG = 'Y'
2389 CONNECT BY PRIOR SUB_NAME = SUPER_NAME
2390 AND ENABLED_FLAG = 'Y'
2391 START WITH SUPER_NAME = upper(p_name);
2392 end if;
2393 */
2394
2395 --<8i Support>
2396 if (p_direction in ('SUPERIORS', 'BOTH')) then
2397 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2398 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2399 g_modulePkg||'.GetRelationships',
2400 'Retrieving the superior relationships.');
2401 end if;
2402 SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
2403 ENABLED_FLAG
2404 BULK COLLECT INTO relIDTab, subTab, superTab, enabledTab
2405 FROM WF_ROLE_HIERARCHIES
2406 WHERE ENABLED_FLAG = 'Y'
2407 CONNECT BY PRIOR SUPER_NAME = SUB_NAME
2408 AND PRIOR ENABLED_FLAG = 'Y'
2409 START WITH SUB_NAME = upper(p_name);
2410
2411 if (relIDTab.COUNT > 0) then
2412 for a in relIDTab.FIRST..relIDTab.LAST loop
2413 p_superiors(a).RELATIONSHIP_ID := relIDTab(a);
2414 p_superiors(a).SUB_NAME := subTab(a);
2415 p_superiors(a).SUPER_NAME := superTab(a);
2416 p_superiors(a).ENABLED_FLAG := enabledTab(a);
2417 end loop;
2418 end if;
2419 end if;
2420
2421 if (p_direction in ('SUBORDINATES', 'BOTH')) then
2422 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2423 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2424 g_modulePkg||'.GetRelationships',
2425 'Retrieving the subordinate relationships.');
2426 end if;
2427 SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
2428 ENABLED_FLAG
2429 BULK COLLECT INTO relIDTab, subTab, superTab, enabledTab
2430 FROM WF_ROLE_HIERARCHIES
2431 WHERE ENABLED_FLAG = 'Y'
2432 CONNECT BY PRIOR SUB_NAME = SUPER_NAME
2433 AND PRIOR ENABLED_FLAG = 'Y'
2434 START WITH SUPER_NAME = upper(p_name);
2435
2436 if (relIDTab.COUNT > 0) then
2437 for a in relIDTab.FIRST..relIDTab.LAST loop
2438 p_subordinates(a).RELATIONSHIP_ID := relIDTab(a);
2439 p_subordinates(a).SUB_NAME := subTab(a);
2440 p_subordinates(a).SUPER_NAME := superTab(a);
2441 p_subordinates(a).ENABLED_FLAG := enabledTab(a);
2442 end loop;
2443 end if;
2444 end if;
2445
2446 --</8i Support>
2447
2448 end;
2449 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2450 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
2451 g_modulePkg||'.GetRelationships',
2452 'End GetRelationships('||
2453 p_name||')');
2454 end if;
2455 exception
2456 when OTHERS then
2457 if(wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
2458 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
2459 g_modulePkg||'.GetRelationships', 'Exception: '||
2460 sqlerrm);
2461 end if;
2462 WF_CORE.Context('WF_ROLE_HIERARCHY', 'GetRelationships', p_name);
2463 raise;
2464
2465 end GetRelationships;
2466
2467 --
2468 -- GetAllRelationships (PUBLIC)
2469 -- Retrieves both enabled and disabled hierarchies for a given role..
2470 -- IN
2471 -- p_name (VARCHAR2)
2472 -- p_superiors (WF_ROLE_HIERARCHY.relTAB)
2473 -- p_subordinates (WF_ROLE_HIERARCHY.relTAB)
2474
2475 procedure GetAllRelationships (p_name in VARCHAR2,
2476 p_superiors out NOCOPY WF_ROLE_HIERARCHY.relTAB,
2477 p_subordinates out NOCOPY WF_ROLE_HIERARCHY.relTAB,
2478 p_direction in VARCHAR2)
2479 is
2480 --<8i Support>
2481 TYPE super_name_Tab is TABLE of VARCHAR2(320);
2482 TYPE sub_name_Tab is TABLE of VARCHAR2(320);
2483 TYPE rel_ID_Tab is TABLE of NUMBER;
2484 TYPE enabled_Tab is TABLE OF VARCHAR2(1);
2485
2486 superTab super_name_Tab;
2487 subTab sub_name_Tab;
2488 relIDTAB rel_ID_Tab;
2489 enabledTab enabled_Tab;
2490 --</8i Support>
2491
2492 begin
2493 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2494 -- Log only
2495 -- BINDVAR_SCAN_IGNORE[4]
2496 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
2497 g_modulePkg||'.GetAllRelationships',
2498 'Begin GetAllRelationships('||
2499 p_name||')');
2500 end if;
2501 --<8i Support>
2502 if (p_direction in ('SUPERIORS', 'BOTH')) then
2503 SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
2504 ENABLED_FLAG
2505 BULK COLLECT INTO relIDTab, subTab, superTab, enabledTab
2506 FROM WF_ROLE_HIERARCHIES
2507 CONNECT BY PRIOR SUPER_NAME = SUB_NAME
2508 START WITH SUB_NAME = upper(p_name);
2509
2510 if (relIDTab.COUNT > 0) then
2511 for a in relIDTab.FIRST..relIDTab.LAST loop
2512 p_superiors(a).RELATIONSHIP_ID := relIDTab(a);
2513 p_superiors(a).SUB_NAME := subTab(a);
2514 p_superiors(a).SUPER_NAME := superTab(a);
2515 p_superiors(a).ENABLED_FLAG := enabledTab(a);
2516 end loop;
2517 end if;
2518 end if;
2519
2520 if (p_direction in ('SUBORDINATES', 'BOTH')) then
2521 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2522 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2523 g_modulePkg||'.GetAllRelationships',
2524 'Retrieving the subordinate relationships.');
2525 end if;
2526 SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
2527 ENABLED_FLAG
2528 BULK COLLECT INTO relIDTab, subTab, superTab, enabledTab
2529 FROM WF_ROLE_HIERARCHIES
2530 CONNECT BY PRIOR SUB_NAME = SUPER_NAME
2531 START WITH SUPER_NAME = upper(p_name);
2532
2533 if (relIDTab.COUNT > 0) then
2534 for a in relIDTab.FIRST..relIDTab.LAST loop
2535 p_subordinates(a).RELATIONSHIP_ID := relIDTab(a);
2536 p_subordinates(a).SUB_NAME := subTab(a);
2537 p_subordinates(a).SUPER_NAME := superTab(a);
2538 p_subordinates(a).ENABLED_FLAG := enabledTab(a);
2539 end loop;
2540 end if;
2541 end if;
2542 --</8i Support>
2543 /*--<9i Support>
2544 begin
2545 if (p_direction in ('SUPERIORS', 'BOTH')) then
2546 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2547 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2548 g_modulePkg||'.GetAllRelationships',
2549 'Retrieving the superior relationships.');
2550 end if;
2551 SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
2552 ENABLED_FLAG
2553 BULK COLLECT INTO p_superiors
2554 FROM WF_ROLE_HIERARCHIES
2555 CONNECT BY PRIOR SUPER_NAME = SUB_NAME
2556 START WITH SUB_NAME = upper(p_name);
2557 end if;
2558
2559 if (p_direction in ('SUBORDINATES', 'BOTH')) then
2560 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2561 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2562 g_modulePkg||'.GetAllRelationships',
2563 'Retrieving the subordinate relationships.');
2564 end if;
2565 SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
2566 ENABLED_FLAG
2567 BULK COLLECT INTO p_subordinates
2568 FROM WF_ROLE_HIERARCHIES
2569 CONNECT BY PRIOR SUB_NAME = SUPER_NAME
2570 START WITH SUPER_NAME = upper(p_name);
2571 end if;
2572 end;
2573 */
2574 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2575 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
2576 g_modulePkg||'.GetAllRelationships',
2577 'End GetAllRelationships('||
2578 p_name||')');
2579 end if;
2580 exception
2581 when OTHERS then
2582 if(wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
2583 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
2584 g_modulePkg||'.GetAllRelationships', 'Exception: '||
2585 sqlerrm);
2586 end if;
2587 WF_CORE.Context('WF_ROLE_HIERARCHY', 'GetAllRelationships', p_name);
2588 raise;
2589
2590 end GetAllRelationships;
2591
2592 -- AddRelationship (PUBLIC)
2593 -- Creates a super/sub role hierarchy relationship in WF_ROLE_HIERARCHIES.
2594 -- IN
2595 -- p_sub_name (VARCHAR2)
2596 -- p_super_name (VARCHAR2)
2597 -- p_deferMode (BOOLEAN)
2598 -- p_enabled (VARCHAR2)
2599 --
2600 -- RETURNS
2601 -- NUMBER
2602
2603 function AddRelationship (p_sub_name in VARCHAR2,
2604 p_super_name in VARCHAR2,
2605 p_deferMode in BOOLEAN,
2606 p_enabled in varchar2) return number is
2607 l_RelationshipID NUMBER;
2608 l_sub_origSys VARCHAR2(30);
2609 l_super_origSys VARCHAR2(30);
2610 l_roleView VARCHAR2(30);
2611
2612 l_superiors WF_ROLE_HIERARCHY.relTAB;
2613 l_subordinates WF_ROLE_HIERARCHY.relTAB;
2614
2615 HierarchyLoop EXCEPTION;
2616 pragma exception_init(HierarchyLoop, -01436);
2617
2618 --<rwunderl:3634880>
2619 trig_SavePoint EXCEPTION;
2620 pragma exception_init(trig_SavePoint, -04092);
2621 called_from_trigger BOOLEAN := FALSE;
2622 l_updateTime DATE;
2623 l_partitionID NUMBER;
2624 l_superPartitionID number;
2625
2626 --set the enabled_flag default to 'Y'
2627 l_enabled varchar2(1) := 'Y';
2628
2629 begin
2630 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2631 -- Log only
2632 -- BINDVAR_SCAN_IGNORE[4]
2633 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
2634 g_modulePkg||'.AddRelationship',
2635 'Begin AddRelationship('||
2636 p_sub_name||', '||p_super_name||')');
2637 end if;
2638 --need to serialize here to prevent potential circular loops
2639
2640 g_trustTimeStamp := WF_ROLE_HIERARCHY.CreateSession;
2641 -- Validating Roles
2642 begin
2643 begin
2644 SELECT ORIG_SYSTEM, PARTITION_ID
2645 INTO l_sub_origSys, l_partitionID
2646 FROM WF_LOCAL_ROLES
2647 WHERE NAME = p_sub_name;
2648 exception
2649 when NO_DATA_FOUND then
2650 WF_CORE.Token('NAME', p_sub_name);
2651 WF_CORE.Raise('WF_NO_ROLE');
2652 end;
2653
2654 begin
2655 SELECT ORIG_SYSTEM, PARTITION_ID
2656 INTO l_super_origSys, l_superPartitionID
2657 FROM WF_LOCAL_ROLES
2658 WHERE NAME = p_super_name;
2659 exception
2660 when NO_DATA_FOUND then
2661 WF_CORE.Token('NAME', p_super_name);
2662 WF_CORE.Raise('WF_NO_ROLE');
2663 end;
2664
2665 -- Obtain the relationship_id from WF_ROLE_HIERARCHIES_S.
2666 select WF_ROLE_HIERARCHIES_S.NEXTVAL
2667 into l_RelationshipID
2668 from dual;
2669
2670 -- Perform the insert
2671 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2672 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2673 g_modulePkg||'.AddRelationship.Insert',
2674 'Inserting record');
2675 end if;
2676 begin
2677 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2678 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2679 g_modulePkg||'.AddRelationship',
2680 'Setting savepoint loopCheck');
2681 end if;
2682 savepoint loopCheck;
2683 exception
2684 when trig_SavePoint then
2685 if(wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
2686 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
2687 g_modulePkg||'.AddRelationship',
2688 'Call stack initiated from trigger, unable to set '||
2689 'savepoint. Any exception will result in complete '||
2690 'rollback.');
2691 end if;
2692 called_from_trigger := TRUE;
2693 end;
2694
2695 --Once ready to insert record verify that the enabled_flag
2696 --is essentially binay ie 'Y' or 'N'
2697 --Hence anything other than 'Y' set it to 'N'
2698 if (p_enabled <> 'Y') then
2699 l_enabled := 'N';
2700 end if;
2701
2702 insert into WF_ROLE_HIERARCHIES
2703 (RELATIONSHIP_ID,
2704 SUB_NAME,
2705 SUPER_NAME,
2706 CREATED_BY,
2707 CREATION_DATE,
2708 LAST_UPDATED_BY,
2709 LAST_UPDATE_DATE,
2710 LAST_UPDATE_LOGIN,
2711 ENABLED_FLAG,
2712 SECURITY_GROUP_ID,
2713 PARTITION_ID,
2714 SUPERIOR_PARTITION_ID)
2715 values
2716 ( l_RelationshipID,
2717 p_sub_name,
2718 p_super_name,
2719 WFA_SEC.USER_ID,
2720 sysdate,
2721 WFA_SEC.USER_ID,
2722 sysdate,
2723 WFA_SEC.LOGIN_ID,
2724 l_enabled,
2725 WFA_SEC.SECURITY_GROUP_ID,
2726 l_partitionID,
2727 l_superPartitionID);
2728 exception
2729 when DUP_VAL_ON_INDEX then
2730 -- The row already exists, if it is expired, we can update with
2731 -- with the new information, if it is active, we will raise an error.
2732 if(wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
2733 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
2734 g_modulePkg||
2735 '.AddRelationship.Insert.DUP_VAL_ON_INDEX',
2736 'Updating expired relationship');
2737 end if;
2738 update WF_ROLE_HIERARCHIES set
2739 LAST_UPDATED_BY = WFA_SEC.USER_ID,
2740 LAST_UPDATE_DATE = sysdate,
2741 LAST_UPDATE_LOGIN= WFA_SEC.USER_ID,
2742 ENABLED_FLAG = 'Y',
2743 SECURITY_GROUP_ID = WFA_SEC.SECURITY_GROUP_ID
2744 where SUB_NAME = p_sub_name
2745 and SUPER_NAME = p_super_name
2746 and ENABLED_FLAG = 'N'
2747 returning RELATIONSHIP_ID into l_relationshipID;
2748
2749 if (sql%ROWCOUNT = 0) then
2750 --We already encountered a dup_val_on_index with sub/super name
2751 --so by default for the above update to not work the enabled_flag
2752 --would have to be 'Y' which is a true duplicate.
2753 if(wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
2754 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
2755 g_modulePkg||
2756 '.AddRelationship.Insert.DUP_VAL_ON_INDEX.NoUpdate',
2757 'Active relationship exists, raising WFDS_DUP_HIERARCHY');
2758 end if;
2759 WF_CORE.Token('P_SUB_NAME', p_sub_name);
2760 WF_CORE.Token('P_SUPER_NAME', p_super_name);
2761 WF_CORE.Raise('WFDS_DUP_HIERARCHY');
2762 end if;
2763 end;
2764
2765 --If either the superior or the subordinate is a newly created role we
2766 --know that it is impossible to have created a loop in the hierarchy so
2767 --we only validate the hierarchy if neither is in the WF_LOCAL_SYNCH.Cache
2768 if (NOT (WF_LOCAL_SYNCH.CheckCache(p_sub_name)) and
2769 NOT (WF_LOCAL_SYNCH.CheckCache(p_super_name))) then
2770 --Access the hierarchy to make sure a loop was not created.
2771
2772 GetRelationships(p_name=>p_sub_name,
2773 p_superiors=>l_superiors,
2774 p_subordinates=>l_subordinates);
2775 else
2776 --We must be sure to delete the super and sub name from the
2777 --WF_LOCAL_SYNCH cache because they are no longer "trusted" to not
2778 --potentially cause a loop in the hierarchy.
2779 WF_LOCAL_SYNCH.DeleteCache(p_sub_name);
2780 WF_LOCAL_SYNCH.DeleteCache(p_super_name);
2781 end if;
2782
2783 --Raise the oracle.apps.wf.ds.roleHierarchy.relationshipCreated event
2784 RaiseEvent(
2785 p_eventName=>'oracle.apps.fnd.wf.ds.roleHierarchy.relationshipCreated',
2786 p_relationshipID=>l_RelationshipID,
2787 p_superName=>p_super_name,
2788 p_subName=>p_sub_name,
2789 p_defer=>p_deferMode);
2790 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2791 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, g_modulePkg||
2792 '.AddRelationship',
2793 'End AddRelationship('|| p_sub_name||', '||
2794 p_super_name||')');
2795 end if;
2796 return l_RelationshipID;
2797
2798 exception
2799 when HierarchyLoop then
2800 if(wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
2801 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
2802 g_modulePkg||'.AddRelationship',
2803 'Circular reference detected in hierarchy.');
2804 end if;
2805 if (called_from_trigger) then
2806 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2807 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2808 g_modulePkg||'.AddRelationship',
2809 'Initiation from trigger prevents rollback to '||
2810 'savepoint executing complete rollback.');
2811 end if;
2812 rollback;
2813 else
2814 rollback to loopCheck;
2815 end if;
2816 if(wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
2817 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
2818 g_modulePkg||'.AddRelationship', 'Exception: '||
2819 sqlerrm);
2820 end if;
2821 WF_CORE.Context('WF_ROLE_HIERARCHY', 'AddRelationship', p_sub_name,
2822 p_super_name);
2823 WF_CORE.Raise('WFDS_HIERARCHY_LOOP');
2824
2825 when OTHERS then
2826 if(wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
2827 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
2828 g_modulePkg||'.AddRelationship', 'Exception: '||
2829 sqlerrm);
2830 end if;
2831 WF_CORE.Context('WF_ROLE_HIERARCHY', 'AddRelationship', p_sub_name,
2832 p_super_name);
2833 raise;
2834 end AddRelationship;
2835
2836 --
2837 -- ExpireRelationship(PUBLIC)
2838 -- Expires a super/sub role hierarchy relationship
2839 -- IN
2840 -- p_sub_name (VARCHAR2)
2841 -- p_super_name (VARCHAR2)
2842 -- p_deferMode (BOOLEAN)
2843 --
2844 -- RETURNS
2845 -- NUMBER
2846
2847 function ExpireRelationship (p_sub_name in VARCHAR2,
2848 p_super_name in VARCHAR2,
2849 p_defer_mode in BOOLEAN) return number is
2850
2851 l_relationshipID NUMBER;
2852
2853 begin
2854 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2855 -- Log only
2856 -- BINDVAR_SCAN_IGNORE[4]
2857 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
2858 g_modulePkg||'.ExpireRelationship',
2859 'Begin ExpireRelationship('||
2860 p_sub_name||', '||p_super_name||')');
2861 end if;
2862 begin
2863 if(wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2864 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT,
2865 g_modulePkg||'.ExpireRelationship.Update',
2866 'Updating record');
2867 end if;
2868 update WF_ROLE_HIERARCHIES
2869 set LAST_UPDATED_BY = WFA_SEC.USER_ID,
2870 LAST_UPDATE_DATE = sysdate,
2871 LAST_UPDATE_LOGIN= WFA_SEC.USER_ID,
2872 ENABLED_FLAG = 'N'
2873 where SUB_NAME = p_sub_name
2874 and SUPER_NAME = p_super_name
2875 and ENABLED_FLAG = 'Y'
2876 returning RELATIONSHIP_ID into l_relationshipID;
2877
2878 if (sql%ROWCOUNT = 0) then
2879 -- There is no active role hierarchy relationship to expire
2880 if(wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
2881 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION, g_modulePkg||
2882 '.ExpireRelationship.Update.NoActiveRelationship',
2883 'No Active relationship exists, '||
2884 'raising WFDS_NO_HIERARCHY');
2885 end if;
2886 WF_CORE.Token('P_SUB_NAME', p_sub_name);
2887 WF_CORE.Token('P_SUPER_NAME', p_super_name);
2888 WF_CORE.Raise('WFDS_NO_HIERARCHY');
2889 end if;
2890
2891 end;
2892
2893 --Raise the oracle.apps.wf.ds.roleHierarchy.relationshipUpdated event
2894 RaiseEvent(
2895 p_eventName=>'oracle.apps.fnd.wf.ds.roleHierarchy.relationshipUpdated',
2896 p_relationshipID=>l_RelationshipID,
2897 p_superName=>p_super_name,
2898 p_subName=>p_sub_name,
2899 p_defer=>p_defer_mode);
2900
2901 if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2902 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, g_modulePkg||
2903 '.ExpireRelationship',
2904 'End ExpireRelationship('|| p_sub_name||', '||
2905 p_super_name||')');
2906 end if;
2907
2908 return l_RelationshipID;
2909
2910 exception
2911 when OTHERS then
2912 if(wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
2913 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,
2914 g_modulePkg||'.ExpireRelationship', 'Exception: '||
2915 sqlerrm);
2916 end if;
2917 WF_CORE.Context('WF_ROLE_HIERARCHY', 'ExpireRelationship', p_sub_name,
2918 p_super_name);
2919 raise;
2920 end ExpireRelationship;
2921
2922 -- Denormalize_UserRole_RF (PRIVATE)
2923 -- Rule function to update the user and role dates of user/role
2924 -- relationships as well as assignments.
2925 -- IN
2926 -- p_sub_guid (RAW)
2927 -- p_event ([WF_EVENT_T])
2928 -- RETURNS
2929 -- VARCHAR2
2930
2931 function Denormalize_User_Role_RF ( p_sub_guid in RAW,
2932 p_event in out NOCOPY WF_EVENT_T )
2933 return VARCHAR2 is
2934
2935
2936
2937 begin
2938 --First check to see if we even have to run.
2939 if ((p_event.getValueForParameter('OLD_START_DATE') = '*UNDEFINED*') and
2940 (p_event.getValueForParameter('OLD_END_DATE') = '*UNDEFINED*')) then
2941 return 'SUCCESS';
2942 end if;
2943 Denormalize_UR_Assignments(p_event);
2944
2945 return 'SUCCESS';
2946
2947 exception
2948 when OTHERS then
2949
2950 WF_CORE.Context('WF_ROLE_HIERARCHY', 'Denormalize_User_Role_RF',
2951 p_event.getEventName( ), p_sub_guid);
2952
2953 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2954 return 'ERROR';
2955 end Denormalize_User_Role_RF;
2956
2957
2958
2959 ----
2960 ----
2961 -- validateSession()
2962 -- IN
2963 -- p_timeStamp DATE
2964 -- RETURNS
2965 -- BOOLEAN
2966 function validateSession (p_timeStamp in DATE) return boolean
2967 is
2968 l_updateTime DATE;
2969
2970 begin
2971 select to_date(text, WF_CORE.canonical_date_mask)
2972 into l_UpdateTime
2973 from WF_RESOURCES
2974 where TYPE='WFTKN'
2975 and NAME = 'WFDS_HIERARCHY_UPD'
2976 and LANGUAGE = 'US';
2977
2978 if ((p_timeStamp is NOT NULL) and (l_updateTime is NOT NULL) and
2979 (p_timeStamp = l_updateTime) and g_trustTimeStamp = l_updateTime) then
2980 return TRUE;
2981 else
2982 return FALSE;
2983 end if;
2984
2985 exception
2986 when NO_DATA_FOUND then
2987 return FALSE;
2988
2989 when others then
2990 raise;
2991
2992 end validateSession;
2993
2994
2995 ----
2996 ----
2997 -- createSession()
2998 -- RETURNS
2999 -- DATE
3000 function createSession return DATE
3001 is
3002 PRAGMA AUTONOMOUS_TRANSACTION;
3003
3004 begin
3005 g_trustTimeStamp := sysdate;
3006 update WF_RESOURCES
3007 set text = to_char(g_trustTimeStamp, WF_CORE.canonical_date_mask)
3008 where name = 'WFDS_HIERARCHY_UPD';
3009
3010 if (sql%rowcount = 0) then
3011 begin
3012 insert into WF_RESOURCES (TYPE,
3013 NAME,
3014 LANGUAGE,
3015 SOURCE_LANG,
3016 ID,
3017 TEXT,
3018 PROTECT_LEVEL,
3019 CUSTOM_LEVEL) values
3020 ('WFTKN',
3021 'WFDS_HIERARCHY_UPD',
3022 'US',
3023 'US',
3024 0,
3025 to_char(g_trustTimeStamp,
3026 WF_CORE.canonical_date_mask),
3027 0,
3028 0);
3029 exception
3030 when DUP_VAL_ON_INDEX then
3031 null;
3032
3033 when others then
3034 raise;
3035
3036 end;
3037 end if;
3038 commit;
3039 return g_trustTimeStamp;
3040
3041 end createSession;
3042
3043 ----
3044 ----
3045 -- removeRelationship()
3046 -- IN
3047 -- relationship_id NUMBER
3048 -- RETURNS
3049 -- BOOLEAN
3050 procedure removeRelationship (p_relationshipID in NUMBER,
3051 p_forceRemove in BOOLEAN
3052 )
3053 is
3054 l_enabled varchar2(1);
3055 l_relationshipID number;
3056 l_subName varchar2(320);
3057 l_superName varchar2(320);
3058 begin
3059
3060 --check the relationship status
3061
3062 select enabled_flag,sub_name,super_name
3063 into l_enabled, l_subName , l_superName
3064 from wf_role_hierarchies
3065 where relationship_id=p_relationshipID;
3066
3067 if (l_enabled='Y') then
3068 if (p_forceRemove is null or not p_forceRemove) then
3069 -- raise error
3070 wf_core.token('P_RELATIONSHIPID',p_relationshipID);
3071 wf_core.raise('WFDS_ACTIVE_RELN');
3072 else
3073 --call API to expire this remationship forcefully
3074 l_relationshipID:=ExpireRelationship(l_subName,l_superName);
3075
3076 --propagate the change to user role assignments
3077 propagate(l_relationshipID, SYSDATE);
3078 end if;
3079 end if;
3080
3081 --now remove the realtionship from the table
3082
3083 Delete from WF_ROLE_HIERARCHIES
3084 where relationship_id=p_relationshipID;
3085
3086
3087
3088 exception
3089 when others then
3090 raise;
3091
3092 end removeRelationship;
3093
3094 end WF_ROLE_HIERARCHY;
[Home] [Help]