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