1 package body WF_LOCAL_SYNCH as
2 /* $Header: WFLOCALB.pls 120.30.12010000.2 2008/08/26 20:35:26 alepe ship $ */
3 ------------------------------------------------------------------------------
4 -- Global Private Variables
5
6 g_wf_schema VARCHAR2(320);
7 g_parallel NUMBER;
8 g_logging VARCHAR2(10);
9 g_BaseLanguage VARCHAR2(30) := 'AMERICAN';
10 g_BaseTerritory VARCHAR2(30) := 'AMERICA';
11 g_temptablespace VARCHAR2(30);
12 g_modulePkg varchar2(100) := 'wf.plsql.WF_LOCAL_SYNCH';
13 g_trustedRoles WF_DIRECTORY.roleTable;
14 g_trustTimeStamp DATE;
15
16 ------------------------------------------------------------------------------
17 -- Role variables
18 --
19 g_name VARCHAR2(320);
20 g_displayName VARCHAR2(360);
21 g_origSystem VARCHAR2(240);
22 g_origSystemID NUMBER;
23 g_parentOrigSys VARCHAR2(240);
24 g_parentOrigSysID NUMBER;
25 g_ownerTag VARCHAR2(50);
26 g_oldOrigSystemID NUMBER;
27 g_language VARCHAR2(30);
28 g_territory VARCHAR2(30);
29 g_description VARCHAR2(1000);
30 g_notificationPref VARCHAR2(8);
31 g_emailAddress VARCHAR2(320);
32 g_fax VARCHAR2(240);
33 g_status VARCHAR2(8);
34 g_employeeID NUMBER;
35 g_expDate DATE;
36 g_delete BOOLEAN;
37 g_updateOnly BOOLEAN;
38 g_raiseErrors BOOLEAN;
39 g_overWrite BOOLEAN;
40 g_overWrite_UserRoles BOOLEAN; -- <6817561> this exposes a switch to update
41 -- standard user/role table columns
42 g_oldName VARCHAR2(320);
43 g_ppID NUMBER;
44 g_lastUpdateDate DATE;
45 g_lastUpdatedBy NUMBER;
46 g_creationDate DATE;
47 g_createdBy NUMBER;
48 g_lastUpdateLogin NUMBER;
49 g_attributes WF_PARAMETER_LIST_T;
50 ------------------------------------------------------------------------------
51 /*
52 ** seedAttributes - <private>
53 **
54 ** This routine opens a parameter list and seeds role variables.
55 */
56 procedure seedAttributes (p_attributes in wf_parameter_list_t,
57 p_origSystem in VARCHAR2,
58 p_origSystemID in NUMBER,
59 p_expDate in DATE) is
60
61 l_sql VARCHAR2(2000);
62 l_modulePkg varchar2(240) := g_modulePkg||'.seedAttributes';
63
64 begin
65 -- Log only
66 -- BINDVAR_SCAN_IGNORE[5]
67 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
68 'Begin seedAttributes(p_attributes '||
69 '(wf_parameter_list_t), '||p_origSystem||', '||
70 to_char(p_origSystemID)||', '||
71 to_char(p_expDate, WF_CORE.canonical_date_mask)||')');
72
73 g_name := NULL;
74 g_displayName := NULL;
75 g_origSystem := NULL;
76 g_origSystemID := NULL;
77 g_parentOrigSys := NULL;
78 g_parentOrigSysID := NULL;
79 g_ownerTag := NULL;
80 g_language := NULL;
81 g_territory := NULL;
82 g_description := NULL;
83 g_notificationPref := NULL;
84 g_emailAddress := NULL;
85 g_fax := NULL;
86 g_employeeID := NULL;
87 g_status := NULL;
88 g_expDate := NULL;
89 g_delete := FALSE;
90 g_updateOnly := FALSE;
91 g_raiseErrors := FALSE;
92 g_overWrite := FALSE;
93 g_overWrite_UserRoles := FALSE; -- <6817561>
94 g_oldName := NULL;
95 g_ppID := NULL;
96 g_lastUpdateDate := sysdate;
97 g_lastUpdatedBy := WFA_SEC.user_id;
98 g_creationDate := sysdate;
99 g_createdBy := WFA_SEC.user_id;
100 g_lastUpdateLogin := WFA_SEC.login_id;
101 g_Attributes := wf_parameter_list_t();
102
103
104 FOR i in p_attributes.FIRST..p_attributes.LAST loop
105 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
106 'Processing parameter: '||p_attributes(i).getName());
107 begin
108 if (upper(p_attributes(i).getName()) = 'USER_NAME') then
109 g_name := p_attributes(i).getValue();
110
111 elsif (upper(p_attributes(i).getName()) = 'DISPLAYNAME') then
112 g_displayName := p_attributes(i).getValue();
113
114 elsif (upper(p_attributes(i).getName()) = 'ORCLWFORIGSYSTEM') then
115 g_origSystem := UPPER(p_attributes(i).getValue());
116
117 elsif (upper(p_attributes(i).getName()) = 'ORCLWFORIGSYSTEMID') then
118 g_origSystemID := to_number(p_attributes(i).getValue());
119
120 elsif (upper(p_attributes(i).getName()) = 'ORCLWFPARENTORIGSYS') then
121 g_parentOrigSys := p_attributes(i).getValue();
122
123 elsif (upper(p_attributes(i).getName()) = 'ORCLWFPARENTORIGSYSID') then
124 g_parentOrigSysID := to_number(p_attributes(i).getValue());
125
126 elsif (upper(p_attributes(i).getName()) = 'OWNER_TAG') then
127 g_ownerTag := p_attributes(i).getValue();
128
129 elsif (upper(p_attributes(i).getName()) = 'PREFERREDLANGUAGE') then
130 g_language := p_attributes(i).getValue();
131
132 elsif (upper(p_attributes(i).getName()) = 'ORCLNLSTERRITORY') then
133 g_territory := p_attributes(i).getValue();
134
135 elsif (upper(p_attributes(i).getName()) = 'DESCRIPTION') then
136 g_description := p_attributes(i).getValue();
137
138 elsif (upper(p_attributes(i).getName()) =
139 'ORCLWORKFLOWNOTIFICATIONPREF') then
140 g_notificationPref := upper(p_attributes(i).getValue());
141
142 elsif (upper(p_attributes(i).getName()) = 'MAIL') then
143 g_emailAddress := p_attributes(i).getValue();
144
145 elsif (upper(p_attributes(i).getName()) = 'FACSIMILETELEPHONENUMBER') then
146 g_fax := p_attributes(i).getValue();
147
148 elsif (upper(p_attributes(i).getName()) = 'ORCLISENABLED') then
149 g_status := p_attributes(i).getValue();
150
151 elsif (upper(p_attributes(i).getName()) = 'EXPIRATIONDATE') then
152 g_expDate := to_date(p_attributes(i).getValue(), WF_ENGINE.Date_Format);
153
154 elsif (upper(p_attributes(i).getName()) = 'PER_PERSON_ID') then
155 g_employeeID := p_attributes(i).getValue();
156
157 elsif (upper(p_attributes(i).getName()) = 'DELETE') then
158 if (upper(p_attributes(i).getValue()) = 'TRUE') then
159 g_delete := TRUE;
160 end if;
161
162 elsif (upper(p_attributes(i).getName()) = 'UPDATEONLY') then
163 if (upper(p_attributes(i).getValue()) = 'TRUE') then
164 g_updateOnly := TRUE;
165 end if;
166
167
168 elsif (upper(p_attributes(i).getName()) = 'RAISEERRORS') then
169 if (upper(p_attributes(i).getValue()) = 'TRUE') then
170 g_raiseErrors := TRUE;
171
172 end if;
173
174 elsif (upper(p_attributes(i).getName()) = 'WFSYNCH_OVERWRITE') then
175 if (upper(p_attributes(i).getValue()) = 'TRUE') then
176 g_overWrite := TRUE;
177 end if;
178
179 elsif (upper(p_attributes(i).getName()) = 'WFSYNCH_OVERWRITE_USERROLES') then
180 if (upper(p_attributes(i).getValue()) = 'TRUE') then
181 g_overWrite_UserRoles := true;
182 end if;
183
184 elsif (upper(p_attributes(i).getName()) = 'OLD_USER_NAME') then
185 g_oldName := p_attributes(i).getValue();
186
187 elsif (upper(p_attributes(i).getName()) = 'PERSON_PARTY_ID') then
188 g_ppID := to_number(p_attributes(i).getValue());
189
190 elsif (upper(p_attributes(i).getName()) = 'LAST_UPDATED_BY') then
191 g_lastUpdatedBy := to_number(p_attributes(i).getValue());
192
193 elsif (upper(p_attributes(i).getName()) = 'LAST_UPDATE_DATE') then
194 g_lastUpdateDate := to_date(p_attributes(i).getValue(),
195 WF_CORE.canonical_date_mask);
196
197 elsif (upper(p_attributes(i).getName()) = 'LAST_UPDATE_LOGIN') then
198 g_lastUpdateLogin := to_number(p_attributes(i).getValue());
199
200 elsif (upper(p_attributes(i).getName()) = 'CREATED_BY') then
201 g_createdBy := to_number(p_attributes(i).getValue());
202
203
204 elsif (upper(p_attributes(i).getName()) = 'CREATION_DATE') then
205 g_creationDate := to_date(p_attributes(i).getValue(),
206 WF_CORE.canonical_date_mask);
207
208 else
209 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
210 'Parameter: '||p_attributes(i).getName()||
211 'is ignored by seedAttributes.');
212
213 WF_EVENT.addParameterToList(upper(p_attributes(i).getName()),
214 p_attributes(i).getValue(),g_attributes);
215 end if;
216
217
218
219
220 exception
221 when OTHERS then
222 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED, l_modulePkg,
223 'Exception: '||sqlerrm||
224 ' continuing to retrieve attributes.');
225 null; --We need to attempt to get all attributes.
226
227 end;
228
229 end loop;
230
231 if (p_expDate is not NULL) then
232 --Explicit expiration date parameter will override attribute.
233 g_expDate := p_expDate;
234
235 end if;
236
237 --If the expiration date is now or earlier, then we will set inactive.
238 --The expirationdate attribute will override the delete attribute.
239
240 if (g_expDate is NOT NULL) then
241 if (g_expDate <= sysdate) then
242 g_status := 'INACTIVE';
243 end if;
244 g_delete := FALSE;
245 end if;
246
247
248 if ((g_delete) and (g_expDate is NULL)) then
249 --If delete=true then we will set inactive immediately. However if
250 --there is an expiration date, that will override the delete.
251 g_expDate := sysdate;
252 g_updateOnly := TRUE;
253 g_status := 'INACTIVE';
254
255 end if;
256
257 --p_origSystem and p_origSystemID will override attribute settings.
258 if (p_origSystem is NOT NULL) then
259 g_origSystem := p_origSystem;
260
261 end if;
262
263 if (p_origSystemID is NOT NULL) then
264 g_origSystemID := p_origSystemID;
265
266 end if;
267
268 if (g_ppID is NOT NULL) then
269 g_parentOrigSys := 'HZ_PARTY';
270 g_parentOrigSysID := g_ppID;
271
272 elsif ((g_parentOrigSys is NULL) or
273 (g_parentOrigSysID is NULL)) then
274 if g_employeeID is NOT NULL then -- PER users
275 g_parentOrigSys := 'PER';
276 g_parentOrigSysID := g_employeeID;
277 else
278 g_parentOrigSys := g_origSystem;
279 g_parentOrigSysID := g_origSystemID;
280 end if;
281
282 end if;
283
284 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
285 'End seedAttributes(p_attributes '||
286 '(wf_parameter_list_t), '||p_origSystem||', '||
287 to_char(p_origSystemID)||', '||
288 to_char(p_expDate, WF_CORE.canonical_date_mask)||')');
289
290 exception
291 when OTHERS then
292 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED, l_modulePkg,
293 'Exception: '||sqlerrm);
294 WF_CORE.Context('WF_LOCAL_SYNCH', 'seedAttributes', p_origSystem,
295 to_char(p_origSystemID), to_char(p_expDate,
296 WF_CORE.canonical_date_mask));
297 raise;
298
299
300 end;
301
302 ------------------------------------------------------------------------------
303 /*
304 ** wf_schema - <private>
305 **
306 */
307 function wf_schema return varchar2 is
308 begin
309 if (g_wf_schema is NULL) then
310 g_wf_schema := WF_CORE.Translate('WF_SCHEMA');
311 end if;
312
313 return g_wf_schema;
314
315 end;
316
317
318 ------------------------------------------------------------------------------
319 /*
320 ** update_entmgr - <private>
321 **
322 ** This routine encapsulates the bit that keeps entmgr in the loop
323 */
324 PROCEDURE update_entmgr(p_entity_type in varchar2,
325 p_entity_key_value in varchar2,
326 p_attributes in wf_parameter_list_t,
327 p_source in varchar2) is
328 i number;
329 l_modulePkg varchar2(240) := g_modulePkg||'.update_entmgr';
330
331 begin
332 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
333
334 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
335 'Begin update_entmgr('|| p_entity_type||', '||
336 p_entity_key_value||', '||
337 'p_attributes (wf_parameter_list_t)'||', '||
338 p_source||')');
339
340
341 end if;
342
343 if (p_attributes is not null) then
344 i := p_attributes.FIRST;
345 while (i <= p_attributes.LAST) loop
346 wf_entity_mgr.put_attribute_value(p_entity_type,
347 p_entity_key_value,
348 p_attributes(i).getName(),
349 p_attributes(i).getValue());
350 i := p_attributes.NEXT(i);
351 end loop;
352 wf_entity_mgr.process_changes(p_entity_type,
353 p_entity_key_value,
354 p_source);
355 end if;
356
357 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
358
359 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
360 'End update_entmgr('||p_entity_type||', '||
361 p_entity_key_value||', '||
362 'p_attributes(wf_parameter_list_t)'||', '||
363 p_source||')');
364
365 end if;
366
367
368
369 end;
370
371 ------------------------------------------------------------------------------
372 /*
373 ** Create_Stage_Indexes - <private>
374 **
375 ** This routine examines the base table provided and creates matching indexes
376 ** on the stage table.
377 */
378 PROCEDURE Create_Stage_Indexes (p_sourceTable in VARCHAR2,
379 p_targetTable in VARCHAR2) is
380
381 type OwnerList is table of varchar2(30);
382 type IndexList is table of varchar2(30);
383 type TableList is table of varchar2(30);
384 type IndTypList is table of varchar2(8);
385
386 l_owners OwnerList;
387 l_indexes IndexList;
388 l_tables TableList;
389 l_indtypes IndTypList;
390 l_modulePkg varchar2(240) := g_modulePkg||'.Create_Stage_Indexes';
391
392
393 CURSOR stageIndexes (tableOwner varchar2, tableName varchar2,
394 indexOwner varchar2) IS
395 SELECT di.OWNER,
396 di.index_name,
397 di.table_name,
398 decode(di.uniqueness, 'UNIQUE', ' UNIQUE ', ' ')
399 FROM dba_indexes di
400 WHERE di.table_owner = tableOwner
401 AND di.owner = indexOwner
402 AND di.table_name = tableName;
403
404 CURSOR stageIndParts (indexOwner varchar2, indexName varchar2) IS
405 SELECT dip.tablespace_name,
406 dip.ini_trans,
407 dip.max_trans,
408 dip.initial_extent,
409 dip.next_extent,
410 dip.min_extent,
411 dip.max_extent,
412 dip.pct_increase,
413 dip.pct_free,
414 dip.freelists,
415 dip.freelist_groups
416 FROM dba_ind_partitions dip
417 WHERE dip.index_owner = indexOwner
418 AND dip.index_name = indexName
419 AND dip.partition_name = 'WF_LOCAL_ROLES';
420
421 CURSOR indexColumns (indexOwner varchar2, indexName varchar2,
422 tableName varchar2) IS
423 SELECT column_name, column_position
424 FROM dba_ind_columns
425 WHERE index_owner = indexOwner
426 AND index_name = indexName
427 AND table_name = tableName
428 ORDER BY column_position;
429
430 ObjectExists EXCEPTION;
431 pragma exception_init(ObjectExists, -955);
432
433 l_columnList VARCHAR2(2000);
434 l_columnExpr VARCHAR2(2000);
435 l_sql VARCHAR2(4000);
436 l_newindex varchar2(80);
437 l_storage VARCHAR2(4000);
438
439 begin
440
441 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
442 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
443 l_modulePkg,
444 'Begin Create_Stage_Indexes('||p_sourceTable||', '||
445 p_targetTable||')');
446 end if;
447
448 --Bulding indexes on stage table as determined by the base table.
449 open stageIndexes(wf_schema, p_sourceTable, wf_schema);
450 fetch stageIndexes bulk collect into
451 l_owners, l_indexes, l_tables, l_indtypes;
452 close stageIndexes;
453
454 if (l_owners.COUNT > 0) then
455 for i in l_owners.FIRST..l_owners.LAST loop
456 for a in stageIndParts(l_owners(i), l_indexes(i)) loop
457 if (l_tables(i) = 'WF_USER_ROLE_ASSIGNMENTS') then
458 l_newindex := l_owners(i)||'.'||REPLACE(l_indexes(i),
459 'USER_ROLE_ASSIGNMENTS',
460 'UR_ASSIGNMENTS_STAGE');
461 else
462 l_newindex := l_owners(i)||'.'||REPLACE(l_indexes(i), 'ROLES',
463 'ROLES_STAGE');
464 end if;
465
466 l_sql := 'CREATE'||l_indtypes(i)||'INDEX '||l_newindex||' ON '||
467 wf_schema||'.'||p_targetTable||' (';
468
469 l_ColumnList := NULL; --Clear the column list.
470 for b in indexColumns (l_owners(i), l_indexes(i), l_tables(i)) loop
471 if (b.column_name like 'SYS_%') then --Functional index
472 select COLUMN_EXPRESSION
473 into l_ColumnExpr
474 from dba_ind_expressions
475 where INDEX_NAME = l_indexes(i)
476 and INDEX_OWNER = l_owners(i)
477 and COLUMN_POSITION = b.column_position;
478
479 l_ColumnList := l_ColumnList||REPLACE(l_ColumnExpr, '"', '')||', ';
480 else
481 l_ColumnList := l_ColumnList||b.column_name||', ';
482 end if;
483 end loop; --Column Loop
484 --Need to trim the last comma and close the column list.
485 l_columnList := rtrim(l_columnList, ', ');
486
487 l_sql := l_sql||l_columnList||')';
488
489 --Bug 2931877
490 --Add the tablespace detail if provided to the storage clause
491 if (g_temptablespace is not NULL) then
492 l_sql := l_sql||' TABLESPACE '||g_temptablespace||' ';
493 end if;
494
495 if (a.initial_extent is not null) then
496 l_storage := 'INITIAL '||to_char(a.initial_extent);
497 else
498 l_storage := '';
499 end if;
500
501 if (a.next_extent is not null) then
502 l_storage := l_storage||' NEXT '||to_char(a.next_extent);
503 end if;
504
505 if (a.min_extent is not null) then
506 l_storage := l_storage||' MINEXTENTS '||to_char(a.min_extent);
507 end if;
508
509 if (a.max_extent is not null) then
510 l_storage := l_storage||' MAXEXTENTS '||to_char(a.max_extent);
511 end if;
512
513 if (a.pct_increase is not null) then
514 l_storage := l_storage||' PCTINCREASE '||to_char(a.pct_increase);
515 end if;
516
517 if ((a.freelist_groups is not null) AND (a.freelists is not null)) then
518 l_storage :=l_storage||' FREELIST GROUPS '||to_char(a.freelist_groups);
519 l_storage :=l_storage||' FREELISTS '||to_char(a.freelists);
520 end if;
521
522 if (l_storage is not null) then
523 l_sql := l_sql||' STORAGE ('||l_storage||')';
524 end if;
525
526 if (a.pct_free is not null) then
527 l_sql := l_sql||' PCTFREE '||to_char(a.pct_free);
528 end if;
529
530 if (a.ini_trans is not null) then
531 l_sql := l_sql||' INITRANS '||to_char(a.ini_trans);
532 end if;
533
534 if (a.max_trans is not null) then
535 l_sql := l_sql||' MAXTRANS '||to_char(a.max_trans);
536 end if;
537
538 l_sql := l_sql||' '||g_logging ||' PARALLEL '||to_char(g_parallel)||
539 ' COMPUTE STATISTICS';
540
541 begin
542 execute IMMEDIATE l_sql;
543
544 exception
545 when ObjectExists then
546 null;
547 end;
548
549 if ((g_logging = 'NOLOGGING') or (g_parallel > 1)) then
550 execute IMMEDIATE 'alter index '||l_newindex||' LOGGING NOPARALLEL';
551
552 end if;
553 end loop; -- IndParts (index partitions) Loop
554 end loop; --Index (l_owners index) Loop
555 end if; -- (l_owners.COUNT > 0);
556
557 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
558 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
559 l_modulePkg,
560 'End Create_Stage_Indexes('||p_sourceTable||', '||
561 p_targetTable||')');
562 end if;
563
564 exception
565 when OTHERS then
566
567 if (wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
568 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED, l_modulePkg,
569 'Exception: '||sqlerrm);
570
571 end if;
572
573
574 WF_CORE.Context('WF_LOCAL_SYNCH', 'Create_Stage_Indexes',
575 p_sourceTable, p_targetTable);
576 raise;
577
578 end;
579
580 ------------------------------------------------------------------------------
581 /*
582 ** BuildQuery - <private>
583 **
584 ** This routine dynamically builds a column and select list based on a
585 ** comparison of the stage table and the seeding view.
586 */
587 function BuildQuery (p_orig_system in VARCHAR2,
588 p_stage_table in VARCHAR2,
589 p_seed_view in VARCHAR2,
590 p_columnList in out NOCOPY VARCHAR2,
591 p_selectList in out NOCOPY VARCHAR2) return BOOLEAN is
592
593 l_seedCursor NUMBER;
594 l_seedViewDesc DBMS_SQL.DESC_TAB;
595 l_stageCursor NUMBER;
596 l_stageTableDesc DBMS_SQL.DESC_TAB;
597 l_rowCount NUMBER;
598 l_colCount PLS_INTEGER;
599 l_colExists BOOLEAN;
600 l_colName VARCHAR2(30);
601 l_partitionID NUMBER;
602 l_partitionName VARCHAR2(30);
603 l_sql VARCHAR2(2000);
604 l_modulePkg VARCHAR2(240) := g_modulePkg||'.BuildQuery';
605
606 stageIND PLS_INTEGER;
607 seedIND PLS_INTEGER;
608
609 noTable EXCEPTION;
610 pragma exception_init(noTable, -942);
611
612 begin
613 -- Log only
614 -- BINDVAR_SCAN_IGNORE[3]
615 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
616 'Begin BuildQuery('||p_orig_system||', '||p_stage_table||
617 ', '||p_seed_view||')');
618 --Prepend space to the lists.
619 p_columnList := ' ';
620 p_selectList := ' ';
621
622 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
623 'Obtaining the description of the stage table');
624 --First we will get a description of the staging table in its current form
625 --Open the Stage Cursor
626 l_stageCursor := DBMS_SQL.open_cursor;
627
628 --Select one row to get the description.
629 -- p_stage_table came from table wf_directory_partitions
630 -- also l_sql is not to be run, but to get the columns
631 -- BINDVAR_SCAN_IGNORE
632 l_sql := 'select * from '||p_stage_table||' where rownum < 2';
633 DBMS_SQL.parse(c=>l_stageCursor, statement=>l_sql,
634 language_flag=>DBMS_SQL.native);
635
636 --Obtain the column list
637 DBMS_SQL.describe_columns(l_stageCursor, l_colCount, l_stageTableDesc);
638
639 --Close the Stage Cursor
640 DBMS_SQL.close_cursor(l_stageCursor);
641
642 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
643 'Obtaining the description of the seeding view');
644 --Now we will get a description of the seeding view in its current form
645 --Open the Seed Cursor
646 l_seedCursor := DBMS_SQL.open_cursor;
647
648 --Select one row to get the description.
649 -- p_seed_view came from table wf_directory_partitions
650 -- also l_sql is not to be run, but to get the columns
651 -- BINDVAR_SCAN_IGNORE
652 l_sql := 'select * from '||p_seed_view||' where rownum < 2';
653 DBMS_SQL.parse(c=>l_seedCursor, statement=>l_sql,
654 language_flag=>DBMS_SQL.native);
655
656 --Obtain the column list
657 DBMS_SQL.describe_columns(l_seedCursor, l_colCount, l_seedViewDesc);
658
659 --Close the Stage Cursor
660 DBMS_SQL.close_cursor(l_seedCursor);
661
662 --Retrieve the partition id.
663 select PARTITION_ID, ORIG_SYSTEM
664 into l_partitionID, l_partitionName
665 from WF_DIRECTORY_PARTITIONS
666 where ORIG_SYSTEM = upper(p_orig_system);
667
668 --We now have two description tables that we can compare and build our
669 --column and select lists. We will also apply the special rules in this api
670 --that were in the calling apis.
671 --First, we can build the column list from the stage table description.
672 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
673 'Populating p_columnList');
674 for l_colCount in l_stageTableDesc.FIRST..l_stageTableDesc.LAST loop
675 p_columnList := p_columnList||l_stageTableDesc(l_colCount).COL_NAME||', ';
676 end loop;
677
678 --Now we will trim the last comma to end the column list.
679 p_columnList := rtrim(p_columnList, ', ');
680 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
681 'p_columnList is: ' ||p_columnList);
682
683 --Populating the select list is more involved. First we need to see if the
684 --seeding view populates the column, then we need to apply any business
685 --rules such as controlling the partition_id.
686 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
687 'Populating the select list...');
688 <<StageLoop>>
689 for stageIND in l_stageTableDesc.FIRST..l_stageTableDesc.LAST loop
690 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
691 'Determing if the seeding view provides column: '||
692
693 l_stageTableDesc(stageIND).COL_NAME);
694 l_colExists := FALSE;
695 <<SeedLoop>>
696 for seedIND in l_seedViewDesc.FIRST..l_seedViewDesc.LAST loop
697 if (l_seedViewDesc(seedIND).col_name =
698 l_stageTableDesc(stageIND).col_name) then
699 --Our current stage table column is provided by the view so it can be
700 --used in our select and insert.
701 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
702 'Column found, applying business rules.');
703 l_colExists := TRUE;
704 l_colName := l_seedViewDesc(seedIND).COL_NAME;
705 exit SeedLoop;
706 end if;
707 end loop SeedLoop;
708
709 --------------------------------------------------------------------------
710 --- Business Rules Processing | ---
711 ---------------------------------+ ---
712 --- For each column we need to consider the business rules we have ---
713 --- in place. These rules range from controlling the partition_id ---
714 --- to handling the situation where the column is not provided by ---
715 --- the seeding view. We do duplicate the nullable column rule ---
716 --- because we split the rules by tables to aid performance. ---
717 --------------------------------------------------------------------------
718 if NOT (l_colExists) then
719 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
720 'Seeding View does not provide column ['||
721 l_stageTableDesc(stageIND).COL_NAME||
722 '] applying business rules for missing column.');
723 end if;
724
725 if ((l_stageTableDesc(stageIND).COL_NAME = 'LANGUAGE') and
726 (p_stage_table <> 'WF_LOCAL_ROLES_TL_STAGE')) then
727 if (p_orig_system IN ('PER_ROLE', 'ENG_LIST', 'FND_RESP', 'GBX') OR
728 NOT l_colExists) then
729 p_selectList := (p_selectList||''''||g_BaseLanguage||''''||', ');
730 else
731 p_selectList := (p_selectList||
732 'nvl(LANGUAGE, '''||g_BaseLanguage||'''), ');
733 end if;
734
735 elsif (l_stageTableDesc(stageIND).COL_NAME = 'PARENT_ORIG_SYSTEM') then
736 if (NOT l_colExists) then
737 if (p_stage_table = 'WF_UR_ASSIGNMENTS_STAGE') then
738 p_selectList := (p_selectList||'ROLE_ORIG_SYSTEM, ');
739 else
740 p_selectList := (p_selectList||'ORIG_SYSTEM, ');
741 end if;
742 else
743 if (p_stage_table = 'WF_UR_ASSIGNMENTS_STAGE') then
744 p_selectList := (p_selectList||
745 'nvl(PARENT_ORIG_SYSTEM, ROLE_ORIG_SYSTEM), ');
746 else
747 p_selectList := (p_selectList||
748 'nvl(PARENT_ORIG_SYSTEM, ORIG_SYSTEM), ');
749 end if;
750 end if;
751
752 elsif (l_stageTableDesc(stageIND).COL_NAME =
753 'PARENT_ORIG_SYSTEM_ID') then
754 if (NOT l_colExists) then
755 if (p_stage_table = 'WF_UR_ASSIGNMENTS_STAGE') then
756 p_selectList := (p_selectList||'ROLE_ORIG_SYSTEM_ID, ');
757 else
758 p_selectList := (p_selectList||'ORIG_SYSTEM_ID, ');
759 end if;
760 else
761 if (p_stage_table = 'WF_UR_ASSIGNMENTS_STAGE') then
762 p_selectList := (p_selectList||
763 'nvl(PARENT_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM_ID), ');
764 else
765 p_selectList := (p_selectList||
766 'nvl(PARENT_ORIG_SYSTEM_ID, ORIG_SYSTEM_ID), ');
767 end if;
768 end if;
769
770 elsif (l_stageTableDesc(stageIND).COL_NAME = 'TERRITORY') then
771 if (p_orig_system IN ('PER_ROLE', 'ENG_LIST', 'FND_RESP', 'GBX') OR
772 NOT l_colExists) then
773 p_selectList := (p_selectList||''''||g_BaseTerritory||''''||', ');
774 else
775 p_selectList := (p_selectList||
776 'nvl(TERRITORY, '''||g_BaseTerritory||'''), ');
777 end if;
778
779 elsif (l_stageTableDesc(stageIND).COL_NAME = 'USER_FLAG') then
780 if ((p_orig_system in ('FND_USR', 'HZ_PARTY') AND l_colExists)) then
781 p_selectList := (p_selectList||'USER_FLAG, ');
782 else
783 p_selectList := (p_selectList||'''N'', ');
784 end if;
785
786 elsif (l_stageTableDesc(stageIND).COL_NAME = 'PARTITION_ID') then
787 p_selectList := (p_selectList||''''||to_char(l_partitionID)||''', ');
788
789 elsif ((l_stageTableDesc(stageIND).COL_NAME = 'CREATED_BY') and
790 (NOT l_colExists)) then
791 p_selectList := (p_selectList||to_char(FND_GLOBAL.user_id)||', ');
792
793 elsif ((l_stageTableDesc(stageIND).COL_NAME = 'LAST_UPDATED_BY') and
794 (NOT l_colExists)) then
795 p_selectList := (p_selectList||to_char(FND_GLOBAL.user_id)||', ');
796
797 elsif ((l_stageTableDesc(stageIND).COL_NAME = 'LAST_UPDATE_LOGIN') and
798 (NOT l_colExists)) then
799 p_selectList := (p_selectList||to_char(FND_GLOBAL.login_id)||', ');
800
801 elsif ((l_stageTableDesc(stageIND).COL_NAME = 'LAST_UPDATE_DATE') and
802 (NOT l_colExists)) then
803 p_selectList := (p_selectList||'sysdate, ');
804
805 elsif ((l_stageTableDesc(stageIND).COL_NAME = 'CREATION_DATE') and
806 (NOT l_colExists)) then
807 p_selectList := (p_selectList||'sysdate, ');
808
809 elsif ((l_stageTableDesc(stageIND).COL_NAME = 'RELATIONSHIP_ID') and
810 (NOT l_colExists)) then
811 p_selectList := (p_selectList||'-1, ');
812
813 elsif ((l_stageTableDesc(stageIND).COL_NAME = 'ASSIGNING_ROLE') and
814 (NOT l_colExists)) then
815 p_selectList := (p_selectList||'ROLE_NAME, ');
816
817 elsif ((l_stageTableDesc(stageIND).COL_NAME = 'END_DATE') and
818 (NOT l_colExists)) then
819 p_selectList := (p_selectList||'EXPIRATION_DATE, ');
820
821 -------------------------------------------
822 -- Final Business Rules that take effect --
823 -- only if none of the rules above were --
824 -- triggered. --
825 -------------------------------------------
826 elsif (NOT l_colExists) then
827 if (l_stageTableDesc(stageIND).COL_NULL_OK) then
828 p_selectList := (p_selectList||'NULL, ');
829 else
830 WF_CORE.Token('COLNAME', l_stageTableDesc(stageIND).COL_NAME);
831 WF_CORE.Token('VIEWNAME', p_seed_view);
832 WF_CORE.Token('STAGETABLE', p_stage_table);
833 WF_CORE.Raise('WFDS_SEED_COLUMN');
834 end if;
835
836 else
837 p_selectList := (p_selectList||l_colName||', ');
838 end if;
839
840
841
842
843 end loop StageLoop;
844 --Now we will trim the last comma to end the select list.
845 p_selectList := rtrim(p_selectList, ', ');
846 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
847 'p_select list is: '||p_selectList);
848 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
849 'End BuildQuery('||p_orig_system||', '||p_stage_table||
850 ', '||p_seed_view||') [Returning True]');
851 return TRUE;
852 exception
853 when NoTable then
854 if (substr(p_seed_view, 1, 3) = 'WF_') then
855 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
856 'End BuildQuery('||p_orig_system||', '||p_stage_table||
857 ', '||p_seed_view||') [Returning False]');
858 return FALSE;
859
860 else
861 WF_CORE.Token('VIEWNAME', p_seed_view);
862 WF_CORE.Token('PARTITION_NAME', p_orig_system);
863 WF_CORE.Raise('WFDS_BSYNC_VIEW');
864 end if;
865 end;
866
867 /*
868 ** propagate_user - <described in WFLOCALS.pls>
869 */
870 PROCEDURE propagate_user(p_orig_system in varchar2,
871 p_orig_system_id in number,
872 p_attributes in wf_parameter_list_t,
873 p_start_date in date,
874 p_expiration_date in date) is
875
876 cursor linked_per_users is
877 select user_name from fnd_user
878 where employee_id = p_orig_system_id;
879
880 cursor linked_tca_users is
881 select user_name from fnd_user
882 where customer_id = p_orig_system_id;
883
884 cursor fnd_users is
885 select user_name from fnd_user
886 where user_id = p_orig_system_id;
887
888 l_partitionID NUMBER;
889 l_partitionName varchar2(30);
890 l_oldOrigSystemID NUMBER;
891 l_status VARCHAR2(8);
892
893 l_params WF_PARAMETER_LIST_T;
894 l_overWrite varchar2(2) :='N';
895 l_overWrite_UserRoles varchar2(2) :='N';
896 l_oldLastUpdDate date;
897 l_oldLastUpdLogin number;
898 l_oldLastUpdBy number;
899 l_auxLastUpdDate date;
900 l_auxLastUpdLogin number;
901 l_auxLastUpdBy number;
902 l_modulePkg varchar2(240) := g_modulePkg||'.propagate_user';
903
904 BEGIN
905
906 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
907 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
908 l_modulePkg,
909 'Begin propagate_user('||p_orig_system||', '||
910 p_orig_system_id||','||
911 'p_attributes(wf_parameter_list_t)'||','||
912 to_char(p_start_date,WF_CORE.canonical_date_mask)||','||
913 to_char(p_expiration_date,WF_CORE.canonical_date_mask)||')');
914 end if;
915
916 seedAttributes(p_attributes,
917 p_orig_system,
918 p_orig_system_id,
919 p_expiration_date);
920
921 if (g_overWrite) then -- <6817561>
922 l_overWrite :='Y';
923 end if;
924 if (g_overWrite_UserRoles) then -- <6817561>
925 l_overWrite_UserRoles := 'Y';
926 end if;
927 --
928 -- tell entmgr if linked to an FND user
929 --
930 if (p_orig_system = 'FND_USR') then
931 for myuser in fnd_users loop
932 wf_local_synch.update_entmgr('USER',
933 myuser.user_name,
934 p_attributes,
935 p_orig_system);
936 end loop;
937 elsif (p_orig_system = 'PER') then
938 for myuser in linked_per_users loop
939 wf_local_synch.update_entmgr('USER',
940 myuser.user_name,
941 p_attributes,
942 p_orig_system);
943 end loop;
944 elsif (p_orig_system = 'HZ_PARTY') then
945 for myuser in linked_tca_users loop
946 wf_local_synch.update_entmgr('USER',
947 myuser.user_name,
948 p_attributes,
949 p_orig_system);
950 end loop;
951 end if;
952
953 if ( g_oldName is NOT NULL) then
954 WF_DIRECTORY.assignPartition(p_orig_system, l_partitionID,
955 l_partitionName);
956 --We inline update the role and direct user/role then raise the event so
957 --the rest of the work can be deferred.
958 if (l_partitionID = 1) then
959
960 UPDATE WF_LOCAL_ROLES
961 SET NAME = g_name
962 -- <6817561>
963 , LAST_UPDATE_DATE = decode(l_overWrite, 'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
964 LAST_UPDATED_BY = decode(l_overWrite, 'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
965 LAST_UPDATE_LOGIN = decode(l_overWrite, 'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
966 -- , CREATED_BY = decode(l_overWrite, 'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
967 -- , CREATION_DATE = decode(l_overWrite, 'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
968 -- </6817561>
969 WHERE NAME = g_oldName
970 AND PARTITION_ID = l_partitionID;
971 else
972 UPDATE WF_LOCAL_ROLES
973 SET NAME = g_name
974 -- <6817561>
975 , LAST_UPDATE_DATE = decode(l_overWrite, 'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
976 LAST_UPDATED_BY = decode(l_overWrite, 'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
977 LAST_UPDATE_LOGIN = decode(l_overWrite, 'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
978 -- , CREATED_BY = decode(l_overWrite, 'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
979 -- , CREATION_DATE = decode(l_overWrite, 'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
980 -- </6817561>
981 WHERE NAME = g_oldName
982 AND PARTITION_ID = l_partitionID
983 AND ORIG_SYSTEM = p_orig_system
984 AND ORIG_SYSTEM_ID = p_orig_system_id;
985 end if;
986 --Update the user reference to itself if there is one.
987 UPDATE WF_LOCAL_USER_ROLES
988 SET USER_NAME = g_name,
989 ROLE_NAME = g_name
990 -- <6817561>
991 , LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
992 LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
993 LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
994 -- , CREATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
995 -- , CREATION_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
996 -- </6817561>
997 WHERE USER_NAME = g_oldName
998 AND ROLE_NAME = g_oldName
999 AND PARTITION_ID = l_partitionID;
1000
1001
1002 --Update the user/roles
1003 UPDATE WF_LOCAL_USER_ROLES
1004 SET USER_NAME = g_Name
1005 -- <6817561>
1006 , LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
1007 LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
1008 LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
1009 -- , CREATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
1010 -- , CREATION_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
1011 -- </6817561>
1012 WHERE USER_NAME = g_oldName;
1013
1014 --Update the user/role assignments
1015
1016 UPDATE WF_USER_ROLE_ASSIGNMENTS
1017 SET USER_NAME=g_name,
1018 ROLE_NAME=g_name,
1019 ASSIGNING_ROLE=g_name
1020 -- <6817561>
1021 , LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
1022 LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
1023 LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
1024 -- , CREATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
1025 -- , CREATION_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
1026 -- </6817561>
1027 WHERE ASSIGNING_ROLE=g_oldName
1028 AND USER_NAME=g_oldName
1029 AND RELATIONSHIP_ID=-1
1030 AND PARTITION_ID=l_partitionId;
1031
1032 UPDATE WF_USER_ROLE_ASSIGNMENTS
1033 SET USER_NAME = g_Name
1034 -- <6817561>
1035 , LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
1036 LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
1037 LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
1038 -- , CREATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
1039 -- , CREATION_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
1040 -- </6817561>
1041 WHERE USER_NAME = g_oldName;
1042
1043
1044
1045 WF_EVENT.AddParameterToList('OLD_USER_NAME', g_oldName, l_params);
1046 WF_EVENT.AddParameterToList('USER_NAME', g_name, l_params);
1047 WF_EVENT.AddParameterToList('ORIG_SYSTEM',p_orig_system,l_params);
1048 WF_EVENT.AddParameterToList('ORIG_SYSTEM_ID',to_char(p_orig_system_id),l_params);
1049 if(g_attributes.count>0) then
1050 for i in g_attributes.first..g_attributes.last loop
1051 WF_EVENT.AddParameterToList(upper(g_attributes(i).getName()),
1052 g_attributes(i).getValue(),l_params);
1053 end loop;
1054 end if;
1055 WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.user.nameChanged',
1056 p_event_key=>'NameChanged:'||p_orig_system||':'||
1057 to_char(p_orig_system_id)||'|'||
1058 to_char(SYSDATE, wf_core.canonical_date_mask) ,
1059 p_parameters=>l_params);
1060 end if;
1061
1062 -- If the calling orig_system is 'FND_USR' and g_employeeID is not null, we
1063 -- need to check for one of two situations.
1064 --
1065 -- First, a new FND_USER might be created with the association to an employee
1066 -- in the same transaction.
1067 --
1068 -- Second, an FND_USER might be updated to be associated with the employee.
1069 --
1070 -- If there is an employee id, fnd_usr is required to populate the
1071 -- employeeID attribute for every call to WF_LOCAL_SYNCH.
1072 --
1073 -- If there is no employee associated, then employeeID is null.
1074 -- The same will hold true for a dis-association, so we will
1075 -- always need to check for that.
1076 --Case 1: There is an employee ID.
1077 if ((p_orig_system = 'FND_USR') and
1078 (g_employeeID is not NULL)) then
1079 --FND_USR is propagating an employee association.
1080 --Update the Orig_system and Orig_system id for a PER record.
1081 g_origSystem := 'PER';
1082 g_origSystemID := g_employeeID;
1083
1084 --We first need to make sure that the employee id was not merely changed
1085 --Such as user SYSADMIN associated to employee 1, is now associated to
1086 --employee 2. The way we can detect this is to check for a PER role that
1087 --has the same username, but a different origSystemID.
1088 --Attempt to delete any other employee that might be still associated
1089 --to this user. Then we will proceed to update/create the user. Finally
1090 --we will reassign any user/roles from the previous 'PER' role to the
1091 --new 'PER' role.
1092 DELETE from WF_LOCAL_ROLES PARTITION (FND_USR) WR
1093 WHERE WR.ORIG_SYSTEM = 'PER'
1094 AND WR.NAME = g_name
1095 AND WR.ORIG_SYSTEM_ID <> g_employeeID
1096 Returning WR.ORIG_SYSTEM_ID , last_update_date, last_update_login, last_updated_by
1097 into l_oldOrigSystemID, l_oldLastUpdDate, l_oldLastUpdLogin, l_oldLastUpdBy -- <6817561>
1098 ;
1099
1100 begin
1101 Select WR.EMAIL_ADDRESS, WR.DISPLAY_NAME, WR.DESCRIPTION,
1102 WR.STATUS
1103 into g_emailAddress, g_displayName, g_description, g_status
1104 From WF_LOCAL_ROLES PARTITION (PER_ROLE) WR
1105 Where WR.ORIG_SYSTEM = 'PER_ROLE'
1106 And WR.ORIG_SYSTEM_ID = g_employeeID;
1107 exception
1108 when NO_DATA_FOUND then
1109 --The PER_ROLE does not yet exist so we will just use the data provided
1110 --by FND until the HR data is propagated.
1111 null;
1112 end;
1113
1114 begin
1115 WF_DIRECTORY.SetUserAttr( user_name=>g_name,
1116 orig_system=>g_origSystem,
1117 orig_system_id=>g_origSystemID,
1118 display_name=>g_displayName,
1119 description=>g_description,
1120 notification_preference=>g_notificationPref,
1121 language=>g_language,
1122 territory=>g_territory,
1123 email_address=>g_emailAddress,
1124 fax=>g_fax,
1125 expiration_date=>g_expDate,
1126 status=>g_status,
1127 start_date=>p_start_date,
1128 overWrite=>g_overWrite,
1129 parent_orig_system=>g_parentOrigSys,
1130 parent_orig_system_id=>g_parentOrigSysID,
1131 owner_tag=>g_ownerTag,
1132 last_updated_by=>g_lastUpdatedBy,
1133 last_update_login=>g_lastUpdateLogin,
1134 last_update_date=>g_lastUpdateDate,
1135 eventParams=>g_attributes);
1136
1137 exception
1138 when OTHERS then
1139 if (WF_CORE.error_name = 'WF_INVALID_USER') then
1140 WF_CORE.Clear;
1141 if NOT (g_delete) then --No reason to create a deleted user.
1142 l_status := nvl(g_status,'ACTIVE');
1143
1144 -- <6817561>
1145 if (not g_overWrite) then
1146 -- a potential problem here is that after processing (user create),
1147 -- Last_update_date < creation_date for the created user. This is
1148 -- just a consequence of the g_overWrite flag value
1149 l_auxLastUpdDate := l_oldLastUpdDate;
1150 l_auxLastUpdLogin := l_oldLastUpdLogin;
1151 l_auxLastUpdBy:= l_oldLastUpdBy;
1152 else
1153 l_auxLastUpdDate := g_lastUpdateDate;
1154 l_auxLastUpdLogin := g_lastUpdateLogin;
1155 l_auxLastUpdBy:= g_lastUpdatedBy;
1156 end if; -- </6817561>
1157
1158 WF_DIRECTORY.CreateUser( name=>g_name,
1159 display_name=>g_displayName,
1160 orig_system=>g_origSystem,
1161 orig_system_id=>g_origSystemID,
1162 language=>g_language,
1163 territory=>g_territory,
1164 description=>g_description,
1165 notification_preference=>
1166 g_notificationPref,
1167 email_address=>g_emailAddress,
1168 fax=>g_fax,
1169 status=>g_status,
1170 expiration_date=>g_expDate,
1171 start_date=>p_start_date,
1172 parent_orig_system=>g_parentOrigSys,
1173 parent_orig_system_id=>g_parentOrigSysID,
1174 owner_tag=>g_ownerTag,
1175 created_by=>g_createdBy,
1176 last_updated_by=>l_auxLastUpdBy,
1177 last_update_login=>l_auxLastUpdLogin,
1178 creation_date=>g_creationDate,
1179 last_update_date=>l_auxLastUpdDate);
1180 end if;
1181 else
1182 raise;
1183 end if;
1184 end;
1185
1186 -- We will attempt to delete an FND_USR row, if there is one to
1187 -- delete then we will need to re-associate any user_roles as well.
1188
1189 if (g_employeeID is NOT NULL) then
1190 Delete from WF_LOCAL_ROLES PARTITION (FND_USR) WR
1191 Where WR.ORIG_SYSTEM = p_orig_system
1192 And WR.ORIG_SYSTEM_ID = p_orig_system_id
1193 returning last_update_date, last_update_login, last_updated_by
1194 into l_oldLastUpdDate, l_oldLastUpdLogin, l_oldLastUpdBy -- <6817561>
1195 ;
1196
1197 end if;
1198
1199 if (sql%rowcount > 0) then
1200 -- If we were able to delete an fnd_user from wf_local_roles then
1201 -- We can change any wf_local_user_roles over to PER.
1202
1203 -- We will now reassign all active user/role relationships.
1204 WF_DIRECTORY.ReassignUserRoles(g_name, p_orig_system,
1205 p_orig_system_id, g_origSystem,
1206 g_origSystemID, g_lastUpdateDate,
1207 g_lastUpdatedBy, g_lastUpdateLogin
1208 , g_overWrite_UserRoles -- <6817561>
1209 );
1210
1211 -- <6817561> case when we need to keep the std WHO columns old values
1212 if (not g_overWrite) then
1213
1214 update wf_local_roles
1215 set last_update_date = l_oldLastUpdDate,
1216 last_updated_by = l_oldLastUpdBy,
1217 last_update_login = l_oldLastUpdLogin
1218 where name = g_name
1219 and orig_system = g_origSystem
1220 and orig_system_id = g_origSystemID;
1221
1222 end if; -- </6817561>
1223 elsif (g_overWrite_UserRoles) then -- <6817561> needed to update std WHO columns
1224
1225 update wf_local_user_roles
1226 set last_update_date = nvl(g_lastUpdateDate,last_update_date),
1227 last_updated_by = nvl(g_lastUpdatedBy, last_updated_by),
1228 last_update_login = nvl(g_lastUpdateLogin, last_update_login)
1229 where user_name = g_name
1230 and user_orig_system=g_origSystem
1231 and user_orig_system_id= g_origSystemID;
1232
1233 update wf_user_role_assignments
1234 set last_update_date = nvl(g_lastUpdateDate,last_update_date),
1235 last_updated_by = nvl(g_lastUpdatedBy, last_updated_by),
1236 last_update_login = nvl(g_lastUpdateLogin, last_update_login)
1237 where user_name = g_name
1238 and user_orig_system=g_origSystem
1239 and user_orig_system_id= g_origSystemID; -- </6817561>
1240
1241 end if;
1242
1243 --We now need to reassign any userRoles that may be associated to
1244 --an old PER role (This occurs in the case of an FND_USR being
1245 --switched from one employee to another).
1246 if (l_oldOrigSystemID is NOT NULL) then
1247 -- First, we must expire the existing User/Role relationship
1248 -- from the user to itself.
1249 /* WF_DIRECTORY.ReassignUserRoles was updated to handle the
1250 self-references
1251 begin
1252 WF_DIRECTORY.SetUserRoleAttr(user_name=>g_name,
1253 role_name=>g_name,
1254 start_date=>to_date(NULL),
1255 end_date=>sysdate,
1256 user_orig_system=>'PER',
1257 user_orig_system_id=>l_oldOrigSystemID,
1258 role_orig_system=>'PER',
1259 role_orig_system_id=>l_oldOrigSystemID,
1260 OverWrite=>FALSE,
1261 last_updated_by=>g_lastUpdatedBy,
1262 last_update_login=>g_lastUpdateLogin,
1263 last_update_date=>g_lastUpdateDate);
1264 exception
1265 when OTHERS then
1266 if (WF_CORE.error_name = 'WF_INVAL_USER_ROLE') then
1267 null; --Nothing to expire
1268 else
1269 raise;
1270 end if;
1271 end;
1272 */
1273 -- Now we can reassign any active user/role relationships.
1274 WF_DIRECTORY.ReassignUserRoles(g_name, g_origSystem,
1275 l_oldOrigSystemID, g_origSystem,
1276 g_origSystemID, g_lastUpdateDate,
1277 g_lastUpdatedBy, g_lastUpdateLogin
1278 , g_overWrite_UserRoles -- <6817561>
1279 );
1280 end if;
1281
1282 elsif ((p_orig_system = 'FND_USR') and (g_employeeID is NULL)) then
1283 -- FND_USR is either propagating a user who is not associated with
1284 -- an employee or is dis-associating one. We will check to see if a
1285 --dis-association has just occured, the PER record will still exist.
1286 Delete from WF_LOCAL_ROLES PARTITION (FND_USR) WR
1287 Where WR.ORIG_SYSTEM = 'PER'
1288 And WR.NAME = g_name
1289 Returning WR.ORIG_SYSTEM_ID, last_update_date, last_update_login, last_updated_by
1290 into g_employeeID, l_oldLastUpdDate, l_oldLastUpdLogin, l_oldLastUpdBy; -- <6817561>
1291
1292 if (sql%rowcount > 0) then
1293 l_status := nvl(g_status,'ACTIVE');
1294
1295 -- <6817561>
1296 if (not g_overWrite) then
1297 -- again, maybe last_update_date < creation_date after processing
1298 l_auxLastUpdDate := l_oldLastUpdDate;
1299 l_auxLastUpdLogin := l_oldLastUpdLogin;
1300 l_auxLastUpdBy:= l_oldLastUpdBy;
1301 else
1302 l_auxLastUpdDate := g_lastUpdateDate;
1303 l_auxLastUpdLogin := g_lastUpdateLogin;
1304 l_auxLastUpdBy:= g_lastUpdatedBy;
1305 end if; -- </6817561>
1306
1307 WF_DIRECTORY.CreateUser( name=>g_name,
1308 display_name=>g_displayName,
1309 orig_system=>g_origSystem,
1310 orig_system_id=>g_origSystemID,
1311 language=>g_language,
1312 territory=>g_territory,
1313 description=>g_description,
1314 notification_preference=>g_notificationPref,
1315 email_address=>g_emailAddress,
1316 fax=>g_fax,
1317 status=>l_status,
1318 expiration_date=>g_expDate,
1319 start_date=>p_start_date,
1320 parent_orig_system=>g_parentOrigSys,
1321 parent_orig_system_id=>g_parentOrigSysID,
1322 owner_tag=>g_ownerTag,
1323 created_by=>g_createdBy,
1324 last_updated_by=>l_auxLastUpdBy,
1325 last_update_login=>l_auxLastUpdLogin,
1326 creation_date=>g_creationDate,
1327 last_update_date=>l_auxLastUpdDate);
1328
1329 -- Expire the old user/role relationship with itself.
1330 /* WF_DIRECTORY.ReassignUserRoles was updated to handle the
1331 self-references
1332 begin
1333 WF_DIRECTORY.SetUserRoleAttr(user_name=>g_name,
1334 role_name=>g_name,
1335 end_date=>sysdate,
1336 user_orig_system=>'PER',
1337 user_orig_system_id=>g_employeeID,
1338 role_orig_system=>'PER',
1339 role_orig_system_id=>g_employeeID,
1340 OverWrite=>FALSE,
1341 last_updated_by=>g_lastUpdatedBy,
1342 last_update_login=>g_lastUpdateLogin,
1343 last_update_date=>g_lastUpdateDate);
1344 exception
1345 when OTHERS then
1346 if (WF_CORE.error_name = 'WF_INVAL_USER_ROLE') then
1347 null; --Nothing to expire
1348 else
1349 raise;
1350 end if;
1351 end;
1352 */
1353 -- Now we need to update all of the user_roles back to the fnd_user.
1354 WF_DIRECTORY.ReassignUserRoles(g_name, 'PER', g_employeeID,
1355 p_orig_system, p_orig_system_id,
1356 g_lastUpdateDate, g_lastUpdatedBy,
1357 g_lastUpdateLogin
1358 , g_overWrite_UserRoles -- <6817561>
1359 );
1360
1361 else
1362 --FND_USER is propagating a user that is not associated with an employee.
1363 g_origSystem := p_orig_system;
1364 g_origSystemID := p_orig_system_id;
1365
1366 begin
1367 WF_DIRECTORY.SetUserAttr( user_name=>g_name,
1368 orig_system=>g_origSystem,
1369 orig_system_id=>g_origSystemID,
1370 display_name=>g_displayName,
1371 description=>g_description,
1372 notification_preference=>g_notificationPref,
1373 language=>g_language,
1374 territory=>g_territory,
1375 email_address=>g_emailAddress,
1376 fax=>g_fax,
1377 expiration_date=>g_expDate,
1378 status=>g_status,
1379 overWrite=>g_overWrite,
1380 start_date=>p_start_date,
1381 parent_orig_system=>g_parentOrigSys,
1382 parent_orig_system_id=>g_parentOrigSysID,
1383 owner_tag=>g_ownerTag,
1384 last_updated_by=>g_lastUpdatedBy,
1385 last_update_login=>g_lastUpdateLogin,
1386 last_update_date=>g_lastUpdateDate,
1387 eventParams=>g_attributes);
1388
1389 exception
1390 when OTHERS then
1391 if (WF_CORE.error_name = 'WF_INVALID_USER') then
1392 WF_CORE.Clear;
1393 if NOT (g_delete) then
1394 l_status := nvl(g_status,'ACTIVE');
1395
1396 WF_DIRECTORY.CreateUser( name=>g_name,
1397 display_name=>g_displayName,
1398 orig_system=>g_origSystem,
1399 orig_system_id=>g_origSystemID,
1400 language=>g_language,
1401 territory=>g_territory,
1402 description=>g_description,
1403 notification_preference=>
1404 g_notificationPref,
1405 email_address=>g_emailAddress,
1406 fax=>g_fax,
1407 status=>l_status,
1408 expiration_date=>g_expDate,
1409 start_date=>p_start_date,
1410 parent_orig_system=>g_parentOrigSys,
1411 parent_orig_system_id=>g_parentOrigSysID,
1412 owner_tag=>g_ownerTag,
1413 created_by=>g_createdBy,
1414 last_updated_by=>g_lastUpdatedBy,
1415 last_update_login=>g_lastUpdateLogin,
1416 creation_date=>g_creationDate,
1417 last_update_date=>g_lastUpdateDate );
1418
1419 end if;
1420 else
1421 raise;
1422
1423 end if;
1424 end;
1425
1426 -- <6817561>
1427 if (g_overWrite_UserRoles) then
1428 update wf_local_user_roles
1429 set last_update_date = nvl(g_lastUpdateDate,last_update_date),
1430 last_updated_by = nvl(g_lastUpdatedBy, last_updated_by),
1431 last_update_login = nvl(g_lastUpdateLogin, last_update_login)
1432 where user_name = g_name
1433 and user_orig_system=g_origSystem
1434 and user_orig_system_id= g_origSystemID;
1435
1436 update wf_user_role_assignments
1437 set last_update_date = nvl(g_lastUpdateDate,last_update_date),
1438 last_updated_by = nvl(g_lastUpdatedBy, last_updated_by),
1439 last_update_login = nvl(g_lastUpdateLogin, last_update_login)
1440 where user_name = g_name
1441 and user_orig_system=g_origSystem
1442 and user_orig_system_id= g_origSystemID;
1443 end if;
1444 -- </6817561>
1445 end if;
1446 elsif (p_orig_system = 'HZ_PARTY') then --<rwunderl:2729190> HZ_PARTY
1447 g_origSystem := p_orig_system; --persons are now users.
1448 g_origSystemID := p_orig_system_id;
1449
1450 begin
1451 WF_DIRECTORY.SetUserAttr( user_name=>g_name,
1452 orig_system=>g_origSystem,
1453 orig_system_id=>g_origSystemID,
1454 display_name=>g_displayName,
1455 description=>g_description,
1456 notification_preference=>g_notificationPref,
1457 language=>g_language,
1458 territory=>g_territory,
1459 email_address=>g_emailAddress,
1460 fax=>g_fax,
1461 expiration_date=>g_expDate,
1462 status=>g_status,
1463 overWrite=>g_overWrite,
1464 start_date=>p_start_date,
1465 parent_orig_system=>g_parentOrigSys,
1466 parent_orig_system_id=>g_parentOrigSysID,
1467 owner_tag=>g_ownerTag,
1468 last_updated_by=>g_lastUpdatedBy,
1469 last_update_login=>g_lastUpdateLogin,
1470 last_update_date=>g_lastUpdateDate,
1471 eventParams=>g_attributes );
1472
1473 exception
1474 when OTHERS then
1475 if (WF_CORE.error_name = 'WF_INVALID_USER') then
1476 WF_CORE.Clear;
1477 if NOT (g_delete) then
1478 l_status := nvl(g_status,'ACTIVE');
1479
1480 WF_DIRECTORY.CreateUser( name=>g_name,
1481 display_name=>g_displayName,
1482 orig_system=>g_origSystem,
1483 orig_system_id=>g_origSystemID,
1484 language=>g_language,
1485 territory=>g_territory,
1486 description=>g_description,
1487 notification_preference=>
1488 g_notificationPref,
1489 email_address=>g_emailAddress,
1490 fax=>g_fax,
1491 status=>l_status,
1492 expiration_date=>g_expDate,
1493 start_date=>p_start_date,
1494 parent_orig_system=>g_parentOrigSys,
1495 parent_orig_system_id=>g_parentOrigSysID,
1496 owner_tag=>g_ownerTag,
1497 created_by=>g_createdBy,
1498 last_updated_by=>g_lastUpdatedBy,
1499 last_update_login=>g_lastUpdateLogin,
1500 creation_date=>g_creationDate,
1501 last_update_date=>g_lastUpdateDate );
1502
1503 end if;
1504 else
1505 raise;
1506 end if;
1507 end;
1508
1509 else --Only FND_USR, and HZ_PARTY can propagate users.
1510 g_origSystem := p_orig_system;
1511 g_origSystemID := p_orig_system_id;
1512
1513 if NOT (g_delete) then
1514 --Bug 3064439
1515 propagate_role(p_orig_system, p_orig_system_id, p_attributes,
1516 p_start_date, p_expiration_date);
1517
1518 end if;
1519 end if;
1520
1521 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1522 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1523 l_modulePkg,
1524 'End propagate_user('||p_orig_system||', '||
1525 p_orig_system_id||','||
1526 'p_attributes(wf_parameter_list_t)'||','||
1527 to_char(p_start_date,WF_CORE.canonical_date_mask)||','||
1528 to_char(p_expiration_date,WF_CORE.canonical_date_mask)||')');
1529
1530 end if;
1531
1532
1533 exception
1534 when OTHERS then
1535 if (g_raiseErrors) then
1536
1537 if (wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
1538 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED, l_modulePkg,
1539 'Exception: '||sqlerrm);
1540
1541 end if;
1542
1543 WF_CORE.Context('WF_LOCAL_SYNCH', 'Propagate_User',
1544 p_orig_system, p_orig_system_id);
1545 raise;
1546
1547 else
1548 null;
1549
1550 end if;
1551
1552 end;
1553
1554 ------------------------------------------------------------------------------
1555 /*
1556 ** propagate_role - <described in WFLOCALS.pls>
1557 */
1558 PROCEDURE propagate_role(p_orig_system in varchar2,
1559 p_orig_system_id in number,
1560 p_attributes in wf_parameter_list_t,
1561 p_start_date in date,
1562 p_expiration_date in date) is
1563 l_status VARCHAR2(8);
1564 l_partitionName VARCHAR2(30);
1565 l_partitionID NUMBER;
1566 l_overWrite_UserRoles varchar2(2) :='N';
1567 l_modulePkg varchar2(240) := g_modulePkg||'.propagate_role';
1568
1569
1570 CURSOR perRoles (c_orig_system in VARCHAR2, c_orig_system_id in NUMBER) is
1571 SELECT WR.NAME, WR.NOTIFICATION_PREFERENCE, WR.LANGUAGE, WR.TERRITORY,
1572 WR.FAX, WR.START_DATE, WR.EXPIRATION_DATE
1573 FROM WF_LOCAL_ROLES PARTITION (FND_USR) WR
1574 WHERE ORIG_SYSTEM = c_orig_system
1575 AND ORIG_SYSTEM_ID = c_orig_system_id;
1576
1577 begin
1578
1579 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1580 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1581 l_modulePkg,
1582 'Begin propagate_role('||p_orig_system||', '||
1583 p_orig_system_id||','||
1584 'p_attributes(wf_parameter_list_t)'||','||
1585 to_char(p_start_date,WF_CORE.canonical_date_mask)||','||
1586 to_char(p_expiration_date,WF_CORE.canonical_date_mask)||')');
1587
1588 end if;
1589
1590 seedAttributes(p_attributes,
1591 p_orig_system,
1592 p_orig_system_id,
1593 p_expiration_date);
1594
1595 --First check to see if a name change was communicated.
1596 if ( g_oldName is NOT NULL) then
1597 WF_DIRECTORY.assignPartition(p_orig_system, l_partitionID,
1598 l_partitionName);
1599
1600 if (g_overWrite_UserRoles) then -- <6817561>
1601 l_overWrite_UserRoles := 'Y';
1602 end if;
1603
1604 --We will use the partition id where we can to improve performance.
1605 UPDATE WF_LOCAL_ROLES
1606 SET NAME = g_name
1607 WHERE NAME = g_oldName
1608 AND PARTITION_ID = l_partitionID
1609 AND ORIG_SYSTEM = p_orig_system
1610 AND ORIG_SYSTEM_ID = p_orig_system_id;
1611
1612 UPDATE WF_LOCAL_USER_ROLES
1613 SET ROLE_NAME = g_name
1614 -- <6817561>
1615 , LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
1616 LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
1617 LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
1618 -- </6817561>
1619 WHERE ROLE_NAME = g_oldName
1620 AND ROLE_ORIG_SYSTEM = p_orig_system
1621 AND ROLE_ORIG_SYSTEM_ID = p_orig_system_id
1622 AND PARTITION_ID = l_partitionID;
1623
1624 UPDATE WF_USER_ROLE_ASSIGNMENTS
1625 SET ASSIGNING_ROLE = g_name
1626 -- <6817561>
1627 , LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
1628 LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
1629 LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
1630 -- </6817561>
1631 WHERE ASSIGNING_ROLE = g_oldName
1632 AND PARTITION_ID = l_partitionID;
1633
1634 --These tables are not partitioned, but do have fk references and may later
1635 --be partitioned on one of these partition ids so we are specifying the
1636 --partition id here.
1637 UPDATE WF_ROLE_HIERARCHIES
1638 SET SUB_NAME = g_name
1639 WHERE SUB_NAME = g_oldName
1640 AND PARTITION_ID = l_partitionID;
1641
1642 UPDATE WF_ROLE_HIERARCHIES
1643 SET SUPER_NAME = g_name
1644 WHERE SUPER_NAME = g_oldName
1645 AND SUPERIOR_PARTITION_ID = l_partitionID;
1646
1647 --These statements cannot take advantage of the partitions.
1648 UPDATE WF_USER_ROLE_ASSIGNMENTS
1649 SET ROLE_NAME = g_name
1650 WHERE ROLE_NAME = g_oldName;
1651
1652 WF_MAINTENANCE.PropagateChangedName(OLDNAME=>g_oldName, NEWNAME=>g_name);
1653 elsif (g_overWrite_UserRoles) then -- <6817561>
1654 WF_DIRECTORY.assignPartition(p_orig_system, l_partitionID,
1655 l_partitionName);
1656
1657 --We will use the partition id where we can to improve performance.
1658 UPDATE WF_LOCAL_USER_ROLES
1659 SET
1660 LAST_UPDATE_DATE = nvl(g_lastUpdateDate, LAST_UPDATE_DATE),
1661 LAST_UPDATED_BY = nvl(g_lastUpdatedBy, LAST_UPDATED_BY),
1662 LAST_UPDATE_LOGIN = nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN)
1663 WHERE ROLE_NAME = g_name
1664 AND ROLE_ORIG_SYSTEM = p_orig_system
1665 AND ROLE_ORIG_SYSTEM_ID = p_orig_system_id
1666 AND PARTITION_ID = l_partitionID;
1667
1668 UPDATE WF_USER_ROLE_ASSIGNMENTS
1669 SET
1670 LAST_UPDATE_DATE = nvl(g_lastUpdateDate, LAST_UPDATE_DATE),
1671 LAST_UPDATED_BY = nvl(g_lastUpdatedBy, LAST_UPDATED_BY),
1672 LAST_UPDATE_LOGIN = nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN)
1673 WHERE ROLE_NAME = g_name
1674 AND ROLE_ORIG_SYSTEM = p_orig_system
1675 AND ROLE_ORIG_SYSTEM_ID = p_orig_system_id
1676 AND PARTITION_ID = l_partitionID;
1677 -- </6817561>
1678
1679 end if;
1680
1681 --Due to the association between employees and users, we have special
1682 --handling for calls coming from 'PER'. If 'PER' is inserting a new
1683 --record (creating an employee), it cannot be associated with a user
1684 --since the FND User form is responsible for that. So we will create
1685 --a 'PER_ROLE' to designate an employee that is not associated with a user.
1686 --
1687 --However, if the employee is already associated with a user, then this would
1688 --be an update call. We need to preserve the NOTIFICATION_PREFERENCE,
1689 --LANGUAGE, and TERRITORY since they were defined by FND_USR. All other
1690 --information can be changed by 'PER'
1691 if (p_orig_system = 'PER') then
1692 --If more than one FND_USR was assigned to the same employee
1693 --(user ignored warnings) then we may have multiple rows, so we use a
1694 --cursor to handle a one or many situation. If there are no PER rows,
1695 --this portion of the code will naturally not execute.
1696 for perRole in perRoles(p_orig_system, p_orig_system_id) loop
1697
1698 -- A 'PER' record exists (this employee is associated to at least one
1699 -- user). We preserved the NOTIFICATION_PREFERENCE, LANGUAGE and
1700 -- TERRITORY. Now we will set the origSystem and the origSystemID.
1701 -- We also can setg_updateOnly, because we know the record(s) exist(s).
1702
1703 WF_DIRECTORY.SetUserAttr( user_name=>perRole.NAME,
1704 orig_system=>g_origSystem,
1705 orig_system_id=>g_origSystemID,
1706 display_name=>g_displayName,
1707 description=>g_description,
1708 notification_preference=>
1709 perRole.NOTIFICATION_PREFERENCE,
1710 language=>perRole.LANGUAGE,
1711 territory=>perRole.TERRITORY,
1712 email_address=>g_emailAddress,
1713 fax=>perRole.FAX,
1714 expiration_date=>perRole.EXPIRATION_DATE,
1715 status=>g_status,
1716 overWrite=>g_overWrite,
1717 start_date=>perRole.START_DATE,
1718 parent_orig_system=>g_parentOrigSys,
1719 parent_orig_system_id=>g_parentOrigSysID,
1720 owner_tag=>g_ownerTag,
1721 last_updated_by=>g_lastUpdatedBy,
1722 last_update_login=>g_lastUpdateLogin,
1723 last_update_date=>g_lastUpdateDate );
1724 --Since we founde one or more PER records we can set the g_updateOnly flag
1725 --to true
1726 g_updateOnly := TRUE;
1727 end loop;
1728
1729 -- No matter what the result of the above attempt to update a PER record we
1730 -- still need to update or create the 'PER_ROLE' record.
1731
1732 g_origSystem := 'PER_ROLE';
1733 g_origSystemID := p_orig_system_id;
1734 g_name := g_origSystem||':'||g_origSystemID;
1735
1736 end if;
1737
1738 if (g_updateOnly) then
1739 begin
1740 WF_DIRECTORY.SetRoleAttr( role_name=>g_name,
1741 orig_system=>g_origSystem,
1742 orig_system_id=>g_origSystemID,
1743 display_name=>g_displayName,
1744 description=>g_description,
1745 notification_preference=>g_notificationPref,
1746 language=>g_language,
1747 territory=>g_territory,
1748 email_address=>g_emailAddress,
1749 fax=>g_fax,
1750 expiration_date=>g_expDate,
1751 status=>g_status,
1752 overWrite=>g_overWrite,
1753 start_date=>p_start_date,
1754 parent_orig_system=>g_parentOrigSys,
1755 parent_orig_system_id=>g_parentOrigSysID,
1756 owner_tag=>g_ownerTag,
1757 last_updated_by=>g_lastUpdatedBy,
1758 last_update_login=>g_lastUpdateLogin,
1759 last_update_date=>g_lastUpdateDate );
1760
1761 exception
1762 when OTHERS then
1763 if (WF_CORE.error_name = 'WF_INVALID_ROLE') then
1764 WF_CORE.Clear;
1765 g_updateOnly := FALSE;
1766
1767 else
1768 raise;
1769
1770 end if;
1771
1772 end;
1773 end if;
1774
1775 if ((NOT g_delete) AND (NOT g_updateOnly)) then
1776 begin
1777 l_status := nvl(g_status,'ACTIVE');
1778 WF_DIRECTORY.CreateRole( role_name=>g_name,
1779 role_display_name=>g_displayName,
1780 orig_system=>g_origSystem,
1781 orig_system_id=>g_origSystemID,
1782 language=>g_language,
1783 territory=>g_territory,
1784 role_description=>g_description,
1785 notification_preference=>g_notificationPref,
1786 email_address=>g_emailAddress,
1787 fax=>g_fax,
1788 status=>l_status,
1789 expiration_date=>g_expDate,
1790 start_date=>p_start_date,
1791 parent_orig_system=>g_parentOrigSys,
1792 parent_orig_system_id=>g_parentOrigSysID,
1793 owner_tag=>g_ownerTag,
1794 created_by=>g_createdBy,
1795 last_updated_by=>g_lastUpdatedBy,
1796 last_update_login=>g_lastUpdateLogin,
1797 creation_date=>g_creationDate,
1798 last_update_date=>g_lastUpdateDate );
1799
1800 --Add this role to the cache of newly created roles.
1801 if (g_trustedRoles.COUNT = 0) then
1802 -- Call CreateSession from WF_ROLE_HIERARCHY.AddRelationship
1803 -- instead of here
1804 -- g_trustTimeStamp := WF_ROLE_HIERARCHY.CreateSession;
1805 g_trustedRoles(0) := g_name;
1806 else
1807 g_trustedRoles(g_trustedRoles.LAST + 1) := g_name;
1808 end if;
1809
1810 exception
1811 when OTHERS then
1812 if (WF_CORE.error_name = 'WF_DUP_ROLE') then
1813 WF_CORE.Clear;
1814 --Bug 3064439
1815 --We let the wf_directory take care of nulls
1816 --We just do update no new value other than the one passed.
1817 WF_DIRECTORY.SetRoleAttr( role_name=>g_name,
1818 orig_system=>g_origSystem,
1819 orig_system_id=>g_origSystemID,
1820 display_name=>g_displayName,
1821 description=>g_description,
1822 notification_preference=>g_notificationPref,
1823 language=>g_language,
1824 territory=>g_territory,
1825 email_address=>g_emailAddress,
1826 fax=>g_fax,
1827 expiration_date=>g_expDate,
1828 status=>g_status,
1829 overWrite=>g_overWrite,
1830 start_date=>p_start_date,
1831 parent_orig_system=>g_parentOrigSys,
1832 parent_orig_system_id=>g_parentOrigSysID,
1833 owner_tag=>g_ownerTag,
1834 last_updated_by=>g_lastUpdatedBy,
1835 last_update_login=>g_lastUpdateLogin,
1836 last_update_date=>g_lastUpdateDate,
1837 eventParams=>g_attributes );
1838
1839 else
1840 raise;
1841
1842 end if;
1843 end;
1844 end if;
1845 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1846 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE,
1847 l_modulePkg,
1848 'End propagate_role('||p_orig_system||', '||
1849 p_orig_system_id||','||
1850 'p_attributes(wf_parameter_list_t)'||','||
1851 to_char(p_start_date,WF_CORE.canonical_date_mask)||','||
1852 to_char(p_expiration_date,WF_CORE.canonical_date_mask)||')');
1853
1854 end if;
1855
1856 exception
1857 when OTHERS then
1858 if (g_raiseErrors) then
1859 if (wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
1860 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_UNEXPECTED,l_modulePkg,
1861 'Exception: '||sqlerrm);
1862
1863 end if;
1864
1865 WF_CORE.Context('WF_LOCAL_SYNCH', 'Propagate_Role',
1866 p_orig_system, p_orig_system_id);
1867 raise;
1868
1869 else
1870 null;
1871
1872 end if;
1873
1874 end;
1875
1876
1877 ------------------------------------------------------------------------------
1878 /*
1879 ** propagate_user_role - (DEPRECATED) use propagateUserRole()
1880 --Bug 2756776
1881 --Added the p_overwrite IN parameter to allow the user to reset the
1882 --nullable columns . In other propagate APIs we pass this parameter
1883 --in the attribute list.
1884
1885 --Bug 2716191
1886 --Added the p_raiseErrors IN parameter to allow the exception
1887 --to be thrown to the caller. In other propagate APIs we pass this
1888 --parameter in the attribute list.
1889 **
1890 */
1891
1892 PROCEDURE propagate_user_role(p_user_orig_system in varchar2,
1893 p_user_orig_system_id in number,
1894 p_role_orig_system in varchar2,
1895 p_role_orig_system_id in number,
1896 p_start_date in date,
1897 p_expiration_date in date,
1898 p_overwrite in boolean,
1899 p_raiseErrors in boolean ) is
1900 l_roleName varchar2(320);
1901 l_userName varchar2(320);
1902 l_origSystem varchar2(30);
1903 l_origSystemID number;
1904
1905 begin
1906
1907 SELECT name INTO l_roleName
1908 FROM wf_roles
1909 WHERE orig_system = p_role_orig_system
1910 AND orig_system_id = p_role_orig_system_id;
1911
1912 begin
1913 SELECT name INTO l_userName
1914 FROM wf_roles
1915 WHERE orig_system = p_user_orig_system
1916 AND orig_system_id = p_user_orig_system_id;
1917
1918 l_origSystem := p_user_orig_system;
1919 l_origSystemID := p_user_orig_system_id;
1920 exception
1921 when NO_DATA_FOUND then
1922 if (p_user_orig_system = 'FND_USR') then --Check for possible PER
1923 SELECT user_name, employee_id, 'PER'
1924 INTO l_userName, l_origSystemID, l_origSystem
1925 FROM FND_USER
1926 WHERE USER_ID = p_user_orig_system_id;
1927 end if;
1928 end;
1929
1930 begin
1931 WF_LOCAL_SYNCH.propagateUserRole(p_user_name=>l_userName,
1932 p_role_name=>l_roleName,
1933 p_user_orig_system=>l_origSystem,
1934 p_user_orig_system_id=>l_origSystemID,
1935 p_role_orig_system=>Propagate_user_role.p_role_orig_system,
1936 p_role_orig_system_id=>Propagate_user_role.p_role_orig_system_id,
1937 p_start_date=>Propagate_user_role.p_start_date,
1938 p_expiration_date=>Propagate_user_role.p_expiration_date,
1939 p_overwrite=>Propagate_user_role.p_overwrite,
1940 p_raiseErrors=>Propagate_user_role.p_raiseErrors);
1941 end;
1942
1943 exception
1944 when OTHERS then
1945 if (p_raiseErrors) then
1946 WF_CORE.Context('WF_LOCAL_SYNCH', 'Propagate_User_Role',
1947 p_user_orig_system, p_user_orig_system_id,
1948 p_role_orig_system, p_role_orig_system_id);
1949 raise;
1950
1951 else
1952 null;
1953
1954 end if;
1955
1956 end;
1957
1958 ------------------------------------------------------------------------------
1959 /*
1960 ** propagateUserRole - Synchronizes the WF_LOCAL_USER_ROLES table.
1961 */
1962 PROCEDURE propagateUserRole(p_user_name in varchar2,
1963 p_role_name in varchar2,
1964 p_user_orig_system in varchar2,
1965 p_user_orig_system_id in number,
1966 p_role_orig_system in varchar2,
1967 p_role_orig_system_id in number,
1968 p_start_date in date,
1969 p_expiration_date in date,
1970 p_overwrite in boolean,
1971 p_raiseErrors in boolean,
1972 p_parent_orig_system in varchar2,
1973 p_parent_orig_system_id in varchar2,
1974 p_ownerTag in varchar2,
1975 p_createdBy in number,
1976 p_lastUpdatedBy in number,
1977 p_lastUpdateLogin in number,
1978 p_creationDate in date,
1979 p_lastUpdateDate in date,
1980 p_assignmentReason in varchar2,
1981 p_UpdateWho in boolean,
1982 p_attributes in WF_PARAMETER_LIST_T)
1983 is
1984 l_uorigSys VARCHAR2(30);
1985 l_uorigSysID NUMBER;
1986 l_rorigSys VARCHAR2(30);
1987 l_rorigSysID NUMBER;
1988
1989
1990 begin
1991 --Need to check if the orig_system info is null.
1992 if ((p_user_orig_system is NULL) or (p_user_orig_system_id is NULL) or
1993 (p_role_orig_system is NULL) or (p_role_orig_system_id is NULL)) then
1994 WF_DIRECTORY.GetRoleOrigSysInfo(p_user_name, l_uorigSys, l_uorigSysID);
1995 WF_DIRECTORY.GetRoleOrigSysInfo(p_role_name, l_rorigSys, l_rorigSysID);
1996
1997 else
1998 l_uorigSys := UPPER(p_user_orig_system);
1999 l_uorigSysID := UPPER(p_user_orig_system_id);
2000 l_rorigSys := UPPER(p_role_orig_system);
2001 l_rorigSysID := UPPER(p_role_orig_system_id);
2002
2003 end if;
2004
2005
2006 WF_DIRECTORY.SetUserRoleAttr(user_name=>p_user_name,
2007 role_name=>p_role_name,
2008 start_date=>p_start_date,
2009 end_date=>p_expiration_date,
2010 user_orig_system=>l_uorigSys,
2011 user_orig_system_id=>l_uorigSysID,
2012 role_orig_system=>l_rorigSys,
2013 role_orig_system_id=>l_rorigSysID,
2014 overWrite=>p_overwrite,
2015 parent_orig_system=>p_parent_orig_system,
2016 parent_orig_system_id=>p_parent_orig_system_ID,
2017 owner_tag=>p_ownerTag,
2018 created_by=>p_createdBy,
2019 creation_date=>p_creationDate,
2020 last_updated_by=>p_lastUpdatedBy,
2021 last_update_login=>p_lastUpdateLogin,
2022 last_update_date=>p_lastUpdateDate,
2023 assignment_reason=>p_assignmentReason,
2024 updateWho=>p_UpdateWho,
2025 eventParams=>p_attributes);
2026
2027
2028 exception
2029 when OTHERS then
2030 if (WF_CORE.error_name = 'WF_INVAL_USER_ROLE') then
2031 WF_CORE.Clear;
2032 WF_DIRECTORY.CreateUserRole(user_name=>p_user_name,
2033 role_name=>p_role_name,
2034 start_date=>p_start_date,
2035 end_date=>p_expiration_date,
2036 user_orig_system=>l_uorigSys,
2037 user_orig_system_id=>l_uorigSysID,
2038 role_orig_system=>l_rorigSys,
2039 role_orig_system_id=>l_rorigSysID,
2040 parent_orig_system=>p_parent_orig_system,
2041 parent_orig_system_id=>p_parent_orig_system_ID,
2042 owner_tag=>p_ownerTag,
2043 created_by=>p_createdBy,
2044 last_updated_by=>p_lastUpdatedBy,
2045 last_update_login=>p_lastUpdateLogin,
2046 creation_date=>p_creationDate,
2047 last_update_date=>p_lastUpdateDate,
2048 assignment_reason=>p_assignmentReason,
2049 eventParams=>p_attributes );
2050
2051 else
2052 raise;
2053
2054 end if;
2055 end;
2056
2057 ------------------------------------------------------------------------------
2058 /*
2059 ** syncUsers - <private>
2060 */
2061 PROCEDURE syncUsers(p_orig_system in varchar2)
2062 is
2063 begin
2064 null; --This is obsoleted for syncRoles
2065 end;
2066
2067 ------------------------------------------------------------------------------
2068 /*
2069 ** syncRolesTL - <private>
2070 ** Pass the partition_id and roletlview so as to avoid
2071 ** additional queries we have already done in SyncRolesTL
2072 */
2073 PROCEDURE syncRolesTL(p_orig_system in varchar2,
2074 p_partitionID in number,
2075 p_partitionName in varchar2,
2076 p_roletlview in varchar2)
2077 is
2078
2079 CURSOR dbaIndexes(tabName varchar2,
2080 tabOwner varchar2) is
2081 SELECT INDEX_NAME
2082 FROM DBA_INDEXES
2083 WHERE TABLE_NAME = tabName
2084 AND TABLE_OWNER = tabOwner;
2085
2086
2087 l_sql VARCHAR2(2000);
2088 l_selectList VARCHAR2(1000);
2089 l_columnList VARCHAR2(1000);
2090 l_storageClause VARCHAR2(2000);
2091 l_importSuccess BOOLEAN;
2092 l_modulePkg VARCHAR2(240) := g_modulePkg||'.syncRolesTL';
2093
2094 begin
2095 -- Log only
2096 -- BINDVAR_SCAN_IGNORE[3]
2097 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
2098 'Begin syncRolesTL('||p_orig_system||', '|| p_partitionid||
2099 ', '||p_partitionName||', '||p_roleTLView||')');
2100
2101 --Truncate the temp table.
2102 WF_DDL.TruncateTable(TableName=>'WF_LOCAL_ROLES_TL_STAGE',
2103 Owner=>wf_schema);
2104
2105 --Drop indexes from the temp table.
2106 for c in dbaIndexes('WF_LOCAL_ROLES_TL_STAGE', wf_schema) loop
2107 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2108 'Dropping index '||c.INDEX_NAME);
2109 WF_DDL.DropIndex(IndexName=>c.INDEX_NAME,
2110 Owner=>wf_schema,
2111 IgnoreNotFound=>TRUE);
2112
2113 end loop;
2114
2115 --Enable parallel DML
2116 execute IMMEDIATE 'alter session enable parallel dml';
2117
2118 --Alter the session to set the sort_area_size and hash_area_size.
2119 execute IMMEDIATE 'alter session set sort_area_size=104857600';
2120 execute IMMEDIATE 'alter session set hash_area_size=204857600';
2121
2122 begin
2123 --Select the data from WF_<origSystem>_ROLES_TL and insert into
2124 --WF_LOCAL_ROLES_TL_STAGE.
2125
2126 if (BuildQuery (p_orig_system=>syncRolesTL.p_orig_system,
2127 p_stage_table=>'WF_LOCAL_ROLES_TL_STAGE',
2128 p_seed_view=>syncRolesTL.p_roletlview,
2129 p_columnList=>l_columnList,
2130 p_selectList=>l_selectList)) then
2131 -- l_selectList is controlled by us
2132 -- g_parallel must not be varchar
2133 -- p_roletlview came from wf_directory_partitions
2134 -- BINDVAR_SCAN_IGNORE[4]
2135 l_sql := ( 'insert /*+ append parallel(T, '||to_char(g_parallel)||
2136 ') */ into '||wf_schema||'.WF_LOCAL_ROLES_TL_STAGE T ('||
2137 l_columnList||') select /*+ parallel(R, '||
2138 to_char(g_parallel)||') */ '||l_selectList||
2139 ' from '||p_roletlview );
2140
2141 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2142 'Loading stage table with: '||l_sql);
2143
2144 -- g_parallel is non-varchar
2145 -- BINDVAR_SCAN_IGNORE[2]
2146 execute IMMEDIATE 'alter session force parallel query parallel '||
2147 to_char(g_parallel) ;
2148 execute IMMEDIATE l_sql;
2149 execute IMMEDIATE 'alter session disable parallel query ' ;
2150 commit;
2151 l_importSuccess := TRUE;
2152 else
2153 l_importSuccess := FALSE;
2154 end if;
2155
2156 exception
2157 when OTHERS then
2158 raise;
2159
2160 end;
2161
2162 if (l_importSuccess) then
2163 --Gather Table Statistics
2164 FND_STATS.Gather_Table_Stats(OWNNAME=>wf_schema,
2165 TABNAME=>'WF_LOCAL_ROLES_TL_STAGE',
2166 PERCENT=>10);
2167
2168 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2169 'Creating indexes on stage table.');
2170
2171 Create_Stage_Indexes (p_sourceTable=>'WF_LOCAL_ROLES_TL',
2172 p_targetTable=>'WF_LOCAL_ROLES_TL_STAGE');
2173
2174 --Get in line to lock the table for partition exchange.
2175 --BINDVAR_SCAN_IGNORE[1]
2176 execute IMMEDIATE 'lock table '||wf_schema||'.WF_LOCAL_ROLES_TL '||
2177 'in exclusive mode';
2178
2179 --Partition exchange the temp table into the wf_local_roles table.
2180 -- wf_schema came from wf_resources
2181 -- p_partitionName cames from wf_directory_partions
2182 -- BINDVAR_SCAN_IGNORE[8]
2183 l_sql := 'ALTER TABLE ' ||wf_schema||'.WF_LOCAL_ROLES_TL ' ||
2184 'EXCHANGE PARTITION ' || p_partitionName ||
2185 ' WITH TABLE ' ||wf_schema||
2186 '.WF_LOCAL_ROLES_TL_STAGE INCLUDING '||
2187 'INDEXES WITHOUT VALIDATION';
2188
2189 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2190 'Exchanging partition with: '||l_sql);
2191 execute IMMEDIATE l_sql;
2192 commit;
2193 end if;
2194 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
2195 'End syncRolesTL('||p_orig_system||', '|| p_partitionid||
2196 ', '||p_partitionName||', '||p_roleTLView||')');
2197 end;
2198 ------------------------------------------------------------------------------
2199
2200 /*
2201 ** syncRoles - <private>
2202 */
2203 PROCEDURE syncRoles(p_orig_system in varchar2)
2204 is
2205
2206 CURSOR dbaIndexes(tabName varchar2,
2207 tabOwner varchar2) is
2208 SELECT INDEX_NAME
2209 FROM DBA_INDEXES
2210 WHERE TABLE_NAME = tabName
2211 AND TABLE_OWNER = tabOwner;
2212
2213 l_partitionID NUMBER;
2214 l_partitionName VARCHAR2(30);
2215 l_roleView VARCHAR2(30);
2216 l_role_tl_view varchar2(30);
2217
2218 l_sql VARCHAR2(2000);
2219 l_storageClause VARCHAR2(2000);
2220 l_importSuccess BOOLEAN;
2221
2222 l_columnList VARCHAR2(1000);
2223 l_selectList VARCHAR2(1000);
2224 l_modulePkg VARCHAR2(240) := g_modulePkg||'.syncRoles';
2225
2226 begin
2227 -- Log only
2228 -- BINDVAR_SCAN_IGNORE[2]
2229 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
2230 'Begin syncRoles('||p_orig_system||')');
2231 --<rwunderl:3109120>
2232 --If the orig_system is FND_USR we will synch PER_ROLE first.
2233 if (p_orig_system = 'FND_USR') then
2234 syncRoles('PER_ROLE');
2235 end if;
2236
2237 --Truncate the temp table.
2238 WF_DDL.TruncateTable(TableName=>'WF_LOCAL_ROLES_STAGE',
2239 Owner=>wf_schema);
2240
2241 --Drop indexes from the temp table.
2242 for c in dbaIndexes('WF_LOCAL_ROLES_STAGE', wf_schema) loop
2243 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2244 'Dropping index '||c.INDEX_NAME);
2245 WF_DDL.DropIndex(IndexName=>c.INDEX_NAME,
2246 Owner=>wf_schema,
2247 IgnoreNotFound=>TRUE);
2248
2249 end loop;
2250
2251 --Check the partition this orig_system belongs to.
2252 WF_DIRECTORY.AssignPartition(p_orig_system,
2253 l_partitionID, l_partitionName);
2254
2255 --If we received a partition_id of 0, this p_orig_system cannot be
2256 --BulkSynched
2257 if (l_partitionID = 0) then
2258 WF_CORE.Token('ORIGSYS', p_orig_system);
2259 WF_CORE.Raise('WF_NOPART_ORIGSYS');
2260
2261 end if;
2262
2263 --Retrieve the role and the user/role view names to be used.
2264 SELECT trim(role_view) , trim(role_tl_view)
2265 INTO l_roleView ,l_role_tl_view
2266 FROM wf_directory_partitions
2267 WHERE partition_id = l_partitionID;
2268 --Enable parallel DML
2269 execute IMMEDIATE 'alter session enable parallel dml';
2270
2271 --Alter the session to set the sort_area_size and hash_area_size.
2272 execute IMMEDIATE 'alter session set sort_area_size=104857600';
2273 execute IMMEDIATE 'alter session set hash_area_size=204857600';
2274
2275 begin
2276 --Select the data from WF_<origSystem>_ROLES and insert into
2277 --WF_LOCAL_ROLES_STAGE.
2278 if (BuildQuery (p_orig_system=>l_partitionName,
2279 p_stage_table=>'WF_LOCAL_ROLES_STAGE',
2280 p_seed_view=>nvl(l_roleView,
2281 'WF_'||l_partitionName||'_ROLES'),
2282 p_columnList=>l_columnList,
2283 p_selectList=>l_selectList)) then
2284 -- g_parallel must not be varchar2
2285 -- wf_schema came from wf_resources
2286 -- l_selectList is controlled by us
2287 -- l_partitionName came from wf_directory_partitions
2288 -- BINDVAR_SCAN_IGNORE[5]
2289 l_sql := ( 'insert /*+ append parallel(T, '||to_char(g_parallel)||
2290 ') */ into '||wf_schema||'.WF_LOCAL_ROLES_STAGE T '||
2291 '('||l_columnList||') select /*+ parallel(R, '||
2292 to_char(g_parallel)||') */ '||l_selectList ||' from '||
2293 nvl(l_roleView, 'WF_'||l_partitionName||'_ROLES R ' ));
2294
2295 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2296 'Loading stage table with: '||l_sql);
2297 execute IMMEDIATE 'alter session force parallel query parallel '||
2298 to_char(g_parallel) ;
2299 execute IMMEDIATE l_sql;
2300 execute IMMEDIATE 'alter session disable parallel query ' ;
2301 commit;
2302 l_importSuccess := TRUE;
2303 else
2304 l_importSuccess := FALSE;
2305 end if;
2306
2307 exception
2308 when OTHERS then
2309 raise;
2310
2311 end;
2312
2313 if (l_importSuccess) then
2314 --Gather Table Statistics
2315 FND_STATS.Gather_Table_Stats(OWNNAME=>wf_schema,
2316 TABNAME=>'WF_LOCAL_ROLES_STAGE',
2317 PERCENT=>10);
2318
2319 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2320 'Creating indexes on stage table.');
2321
2322 Create_Stage_Indexes (p_sourceTable=>'WF_LOCAL_ROLES',
2323 p_targetTable=>'WF_LOCAL_ROLES_STAGE');
2324
2325 --Get in line to lock the table for partition exchange.
2326 --BINDVAR_SCAN_IGNORE[1]
2327 execute IMMEDIATE 'lock table '||wf_schema||'.WF_LOCAL_ROLES '||
2328 'in exclusive mode';
2329 --Partition exchange the temp table into the wf_local_roles table.
2330 -- wf_schema came from wf_resources
2331 -- l_partitionName came from wf_directory_partitions
2332 -- BINDVAR_SCAN_IGNORE[4]
2333 l_sql := 'ALTER TABLE ' ||wf_schema||'.WF_LOCAL_ROLES ' ||
2334 'EXCHANGE PARTITION ' || l_partitionName ||
2335 ' WITH TABLE ' ||wf_schema|| '.WF_LOCAL_ROLES_STAGE INCLUDING '||
2336 'INDEXES WITHOUT VALIDATION';
2337 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2338 'Exchanging partition with: '||l_sql);
2339 execute IMMEDIATE l_sql;
2340 commit;
2341 end if;
2342
2343 --If role_tl_view is null it means the MLS is not
2344 --enabled for this orig_system lets set a global reference as FALSE
2345 --so that inserts/updates will not operate on _TL tables.
2346 if (l_role_tl_view is not NULL) then
2347 --Call SyncRolesTL to synchronise _TL table
2348 SyncRolesTL(p_orig_system, l_partitionID,l_partitionName,l_role_tl_view);
2349 end if;
2350
2351 commit;
2352 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
2353 'End syncRoles('||p_orig_system||')');
2354 end;
2355
2356
2357 ------------------------------------------------------------------------------
2358 /*
2359 ** syncUserRoles - <private>
2360 */
2361 PROCEDURE syncUserRoles(p_orig_system in varchar2)
2362 is
2363 CURSOR dbaIndexes(tabName varchar2,
2364 tabOwner varchar2) is
2365 SELECT INDEX_NAME
2366 FROM DBA_INDEXES
2367 WHERE TABLE_NAME = tabName
2368 AND TABLE_OWNER = tabOwner;
2369
2370
2371 l_partitionID NUMBER;
2372 l_partitionName VARCHAR2(30);
2373 l_userRoleView VARCHAR2(30);
2374 l_sql VARCHAR2(2000);
2375 l_storageClause VARCHAR2(2000);
2376 l_importSuccess BOOLEAN;
2377 l_columnList VARCHAR2(1000);
2378 l_selectList VARCHAR2(1000);
2379 l_modulePkg VARCHAR2(240) := g_modulePkg||'.syncUserRoles';
2380
2381 begin
2382 -- Log only
2383 -- BINDVAR_SCAN_IGNORE[2]
2384 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
2385 'Begin syncUserRoles('||p_orig_system||')');
2386
2387 l_storageClause := ('STORAGE (INITIAL 4K NEXT 512K MINEXTENTS 1 MAXEXTENTS '||
2388 'UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4) '||
2389 ' PCTFREE 10 INITRANS 11 MAXTRANS 255 '||g_logging||' PARALLEL '||
2390 to_char(g_parallel)||' COMPUTE STATISTICS' );
2391
2392 --Bug 2931877
2393 --Add the tablespace clause if its defined.
2394 if (g_temptablespace is not NULL) then
2395 l_storageClause := ' TABLESPACE '||g_temptablespace||' '||
2396 l_storageClause||' ';
2397 end if;
2398
2399 --Truncate the temp table.
2400 WF_DDL.TruncateTable(TableName=>'WF_LOCAL_USER_ROLES_STAGE',
2401 Owner=>wf_schema);
2402
2403 --Drop indexes from the temp table.
2404 for c in dbaIndexes('WF_LOCAL_USER_ROLES_STAGE', wf_schema) loop
2405 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2406 'Dropping index '||c.INDEX_NAME);
2407 WF_DDL.DropIndex(IndexName=>c.INDEX_NAME,
2408 Owner=>wf_schema,
2409 IgnoreNotFound=>TRUE);
2410
2411 end loop;
2412
2413 --Truncate the temp table.
2414 WF_DDL.TruncateTable(TableName=>'WF_UR_ASSIGNMENTS_STAGE',
2415 Owner=>wf_schema);
2416
2417 --Drop indexes from the temp table.
2418 for c in dbaIndexes('WF_UR_ASSIGNMENTS_STAGE', wf_schema) loop
2419 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2420 'Dropping index '||c.INDEX_NAME);
2421 WF_DDL.DropIndex(IndexName=>c.INDEX_NAME,
2422 Owner=>wf_schema,
2423 IgnoreNotFound=>TRUE);
2424
2425 end loop;
2426 --Check the partition this orig_system belongs to.
2427 WF_DIRECTORY.AssignPartition(p_orig_system,
2428 l_partitionID, l_partitionName);
2429
2430 --Retrieve the role and the user/role view names to be used.
2431 SELECT trim(user_role_view)
2432 INTO l_userRoleView
2433 FROM wf_directory_partitions
2434 WHERE partition_id = l_partitionID;
2435
2436 --Enable parallel DML
2437 execute IMMEDIATE 'alter session enable parallel dml';
2438
2439 --Alter the session to set the sort_area_size and hash_area_size.
2440 execute IMMEDIATE 'alter session set sort_area_size=104857600';
2441 execute IMMEDIATE 'alter session set hash_area_size=204857600';
2442
2443 begin
2444 if (BuildQuery (p_orig_system=>l_partitionName,
2445 p_stage_table=>'WF_UR_ASSIGNMENTS_STAGE',
2446 p_seed_view=>nvl(l_userRoleView,
2447 'WF_'||l_partitionName||'_UR'),
2448 p_columnList=>l_columnList,
2449 p_selectList=>l_selectList)) then
2450
2451 -- g_parallel must be number
2452 -- wf_schema came from wf_resources
2453 -- l_columnList came from DBMS_SQL.describe_columns()
2454 -- l_selectList controlled by us
2455 -- l_userRoleView came from wf_directory_partitions
2456 -- l_partitionName came from wf_directory_partitions
2457 -- BINDVAR_SCAN_IGNORE[5]
2458 l_sql := ( 'insert /*+ append parallel(T, '||to_char(g_parallel)||
2459 ') */ into '||wf_schema||'.WF_UR_ASSIGNMENTS_STAGE T '||
2460 '( '||l_columnList||' ) select /*+ parallel(R, '||
2461 to_char(g_parallel)||') */ ' ||l_selectList||' from '||
2462 nvl(l_userRoleView, 'WF_'||l_partitionName||'_UR R ' ));
2463
2464 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2465 'Loading WF_LOCAL_USER_ROLES_STAGE with: '||l_sql);
2466
2467 -- BINDVAR_SCAN_IGNORE[2]
2468 execute IMMEDIATE 'alter session force parallel query parallel '||
2469 to_char(g_parallel) ;
2470 execute IMMEDIATE l_sql;
2471 execute IMMEDIATE 'alter session disable parallel query ' ;
2472 commit;
2473
2474 --Now we will load all the direct assignments into the
2475 --WF_UR_ASSIGNMENTS_STAGE table.
2476 -- g_parallel must be number
2477 -- wf_schema came from wf_resources
2478 -- BINDVAR_SCAN_IGNORE[6]
2479 l_sql := 'insert /*+ append parallel(T, '||to_char(g_parallel)||
2480 ') */ into '||wf_schema||'.WF_LOCAL_USER_ROLES_STAGE T '||
2481 '( USER_NAME, ROLE_NAME, USER_ORIG_SYSTEM,USER_ORIG_SYSTEM_ID, '||
2482 'ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID,PARENT_ORIG_SYSTEM, '||
2483 'PARENT_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, CREATED_BY, '||
2484 'CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ';
2485 -- g_parallel must be number
2486 -- BINDVAR_SCAN_IGNORE[5]
2487 l_sql := l_sql||'USER_START_DATE, ROLE_START_DATE, '||
2488 'USER_END_DATE, ROLE_END_DATE, ASSIGNMENT_TYPE, '||
2489 'PARTITION_ID, ASSIGNMENT_REASON) '||
2490 'select /*+ parallel(R, '||
2491 ''''||to_char(g_parallel)||''') */ USER_NAME, ROLE_NAME, ';
2492 -- wf_schema came from wf_resources
2493 -- BINDVAR_SCAN_IGNORE[8]
2494 l_sql := l_sql|| 'USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, '||
2495 'ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID,PARENT_ORIG_SYSTEM, '||
2496 'PARENT_ORIG_SYSTEM_ID,START_DATE,END_DATE, CREATED_BY, CREATION_DATE, '||
2497 'LAST_UPDATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, USER_START_DATE, '||
2498 'ROLE_START_DATE, USER_END_DATE, '||
2499 'ROLE_END_DATE, ''D'', '||
2500 ''''||to_char(l_partitionID)||''', ASSIGNMENT_REASON from '||
2501 wf_schema||'.WF_UR_ASSIGNMENTS_STAGE R';
2502
2503 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2504 'Loading WF_UR_ASSIGNMENTS_STAGE with: '||l_sql);
2505
2506 execute IMMEDIATE 'alter session force parallel query parallel '||
2507 to_char(g_parallel) ;
2508 execute IMMEDIATE l_sql;
2509 execute IMMEDIATE 'alter session disable parallel query ' ;
2510 commit;
2511 l_importSuccess := TRUE;
2512 else
2513 l_importSuccess := FALSE;
2514 end if;
2515
2516 exception
2517 when OTHERS then
2518 raise;
2519
2520 end;
2521
2522 --Under 3542997 we will add the functionality to continue populating the
2523 --WF_LOCAL_USER_ROLES_STAGE and WF_UR_ASSIGNMENTS_STAGE tables based
2524 --on the hierarchy. After 3542997 is complete, orig_systems should be able
2525 --to both participate in hierarchies and continue to bulk synchronize.
2526 --At this time, an orig_system that participates in a hierarchy cannot bulk
2527 --synchronize.
2528
2529 if (l_importSuccess) then
2530 --Gather Table Statistics
2531 FND_STATS.Gather_Table_Stats(OWNNAME=>wf_schema,
2532 TABNAME=>'WF_LOCAL_USER_ROLES_STAGE',
2533 PERCENT=>10);
2534
2535 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2536 'Creating indexes on stage table.');
2537
2538 Create_Stage_Indexes (p_sourceTable=>'WF_LOCAL_USER_ROLES',
2539 p_targetTable=>'WF_LOCAL_USER_ROLES_STAGE');
2540
2541 FND_STATS.Gather_Table_Stats(OWNNAME=>wf_schema,
2542 TABNAME=>'WF_UR_ASSIGNMENTS_STAGE',
2543 PERCENT=>10);
2544
2545 Create_Stage_Indexes (p_sourceTable=>'WF_USER_ROLE_ASSIGNMENTS',
2546 p_targetTable=>'WF_UR_ASSIGNMENTS_STAGE');
2547
2548 --Get in line to lock the table for partition exchange.
2549 --BINDVAR_SCAN_IGNORE[1]
2550 execute IMMEDIATE 'lock table '||wf_schema||'.WF_LOCAL_USER_ROLES '||
2551 'in exclusive mode';
2552 --Partition exchange the temp table into the wf_local_user_roles table.
2553 -- wf_schema came from wf_resources
2554 -- l_partitionName came from wf_directory_partitions
2555 -- BINDVAR_SCAN_IGNORE[4]
2556 l_sql := 'ALTER TABLE ' ||wf_schema||'.WF_LOCAL_USER_ROLES ' ||
2557 'EXCHANGE PARTITION ' || l_partitionName ||
2558 ' WITH TABLE ' ||wf_schema||
2559 '.WF_LOCAL_USER_ROLES_STAGE INCLUDING INDEXES WITHOUT VALIDATION';
2560 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2561 'Exchanging partition with: '||l_sql);
2562 execute IMMEDIATE l_sql;
2563 commit;
2564
2565 --Get in line to lock the table for partition exchange.
2566 --BINDVAR_SCAN_IGNORE[1]
2567 execute IMMEDIATE 'lock table '||wf_schema||'.WF_USER_ROLE_ASSIGNMENTS '||
2568 'in exclusive mode';
2569 --Partition exchange the temp table into the wf_user_role_assignments table.
2570 l_sql := 'ALTER TABLE ' ||wf_schema||'.WF_USER_ROLE_ASSIGNMENTS ' ||
2571 'EXCHANGE PARTITION ' || l_partitionName ||
2572 ' WITH TABLE ' ||wf_schema||'.WF_UR_ASSIGNMENTS_STAGE '||
2573 'INCLUDING INDEXES WITHOUT VALIDATION';
2574 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_STATEMENT, l_modulePkg,
2575 'Exchanging partition with: '||l_sql);
2576 execute IMMEDIATE l_sql;
2577
2578 end if;
2579 commit;
2580
2581 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
2582 'End syncUserRoles('||p_orig_system||')');
2583 end;
2584 ------------------------------------------------------------------------------
2585 /*
2586 ** BulkSynchronization - <described in WFLOCALS.pls>
2587 */
2588 PROCEDURE BulkSynchronization(p_orig_system in varchar2,
2589 p_parallel_processes in number,
2590 p_logging in varchar2,
2591 p_raiseErrors in boolean,
2592 p_temptablespace in varchar2)
2593 is
2594 DuplicateKeys EXCEPTION;
2595 pragma exception_init(DuplicateKeys, -1452);
2596
2597 cursor valid_orig_systems is
2598 select distinct lookup_code name
2599 from fnd_lookup_values
2600 where lookup_type = 'FND_WF_ORIG_SYSTEMS'
2601 and language = 'US';
2602
2603 --Bug 3585554
2604 l_partitionID NUMBER;
2605 l_partitionName VARCHAR2(30);
2606
2607 begin
2608 if ((p_parallel_processes is NULL) or (p_parallel_processes < 1) or
2609 (mod(p_parallel_processes, 1) <> 0)) then
2610 -- Retrieve g_parallel.
2611 select min(to_number(value))
2612 into g_parallel
2613 from v$parameter
2614 where name in ('parallel_max_servers','cpu_count');
2615 else
2616 g_parallel := p_parallel_processes;
2617 end if;
2618
2619 if (p_logging in ('NOLOGGING','LOGGING')) then
2620 g_logging := p_logging;
2621 else
2622 g_logging := 'LOGGING';
2623 end if;
2624
2625 -- Sanity check on p_temptablespace
2626 if (p_temptablespace is not null and instr(p_temptablespace,';') = 0) then
2627 --Bug 2931877
2628 g_temptablespace := p_temptablespace;
2629 end if;
2630
2631 select NLS_LANGUAGE, NLS_TERRITORY
2632 into g_BaseLanguage, g_BaseTerritory
2633 from FND_LANGUAGES
2634 where INSTALLED_FLAG = 'B';
2635
2636 if (p_orig_system = 'ALL') then
2637 for origsys in valid_orig_systems loop
2638 --<rwunderl:3659321>: 'PER' is bulk-synched through 'FND_USR'
2639 if (origsys.name not in ('ALL', 'PER')) then
2640 begin
2641 --<rwunderl:2823630>
2642 --If the orig system is hierarchy enabled, it cannot bulk synchronize
2643 --Move this condition to after checking the assigned partition
2644 --Maximum case they will be same
2645 --Bug 3585554
2646 --Check if this orig system is attached to another
2647 --one eg : PER to fnd_usr
2648 --Checking it will avoid erroring off down the line
2649 --when you try to sync and the base orig system is
2650 --Hierarchy enabled
2651 WF_DIRECTORY.AssignPartition(origsys.name,
2652 l_partitionID, l_partitionName);
2653 if NOT (WF_ROLE_HIERARCHY.HierarchyEnabled(l_partitionName)) then
2654 syncRoles(origsys.name);
2655 syncUserRoles(origsys.name);
2656 end if;
2657
2658
2659 exception
2660 when DuplicateKeys then
2661 if (p_raiseErrors) then
2662 WF_CORE.Token('P_ORIG_SYSTEM', origsys.name);
2663 WF_CORE.Raise('WFDS_BULK_DUP_KEYS');
2664 else
2665 null;
2666
2667 end if;
2668
2669 when OTHERS then
2670 if (WF_CORE.error_name = 'WF_NOPART_ORIGSYS') then
2671 WF_CORE.Clear;
2672 null; --If we do not have a partition for this orig_system we
2673 --ignore it and continue to the next.
2674 else
2675 if (p_raiseErrors) then
2676 raise;
2677
2678 end if;
2679 end if;
2680 end;
2681
2682 end if;
2683 end loop;
2684 else
2685 begin
2686 --<rwunderl:2823630>
2687 --If the orig system is hierarchy enabled, it cannot bulk synchronize
2688
2689 --ie resolve the assigned partition and then check.
2690 --if NOT (WF_ROLE_HIERARCHY.HierarchyEnabled(p_orig_system)) then
2691
2692 --Bug 3585554
2693 --Check if this orig system is attached to another
2694 --one eg : PER to fnd_usr
2695 --Checking it will avoid erroring off down the line
2696 --when you try to sync and the base orig system is
2697 --Hierarchy enabled
2698 WF_DIRECTORY.AssignPartition(p_orig_system,
2699 l_partitionID, l_partitionName);
2700 if NOT (WF_ROLE_HIERARCHY.HierarchyEnabled(l_partitionName)) then
2701 syncRoles(p_orig_system);
2702 syncUserRoles(p_orig_system);
2703 else
2704 WF_CORE.Token('ORIG_SYSTEM', p_orig_system);
2705 WF_CORE.Raise('WFDS_ORIGSYS_HIERARCHY_ENABLED');
2706 end if;
2707 exception
2708 when DuplicateKeys then
2709 if (p_raiseErrors) then
2710 WF_CORE.Token('P_ORIG_SYSTEM', p_orig_system);
2711 WF_CORE.Raise('WFDS_BULK_DUP_KEYS');
2712 else
2713 null;
2714
2715 end if;
2716
2717 when OTHERS then
2718 if (p_raiseErrors) then
2719 raise;
2720
2721 else
2722 null;
2723
2724 end if;
2725 end;
2726 end if;
2727
2728 g_parallel := 1;
2729 g_logging := 'LOGGING';
2730
2731 end;
2732 ------------------------------------------------------------------------------
2733 /*
2734 ** BulkSynchronization_conc - <described in WFLOCALS.pls>
2735 ** Bug 2931877
2736 ** Added option to chose the tablespace
2737 */
2738 PROCEDURE BulkSynchronization_conc(errbuf out NOCOPY varchar2,
2739 retcode out NOCOPY varchar2,
2740 p_orig_system in varchar2,
2741 p_parallel_processes in varchar2,
2742 p_logging in varchar2,
2743 p_temptablespace in varchar2,
2744 p_raiseerrors in varchar2)
2745 is
2746 l_temptablespace varchar2(30);
2747 l_raiseerrors boolean;
2748 begin
2749 if ((p_temptablespace is null) OR (p_temptablespace = 'NULL')) then
2750 l_temptablespace := null;
2751 else
2752 --We do not require any validation of tablespace as
2753 --the LOV restricts the same and validates it beforehand
2754 l_temptablespace := p_temptablespace;
2755 end if;
2756
2757 if(nvl(p_raiseerrors, 'Y') = 'N') then
2758 l_raiseerrors := FALSE;
2759 else
2760 l_raiseerrors := TRUE;
2761 end if;
2762
2763
2764 wf_local_synch.BulkSynchronization(p_orig_system,
2765 to_number(p_parallel_processes),
2766 p_logging,
2767 l_raiseerrors,
2768 l_temptablespace);
2769
2770 retcode := '0'; -- (successful completion)
2771 errbuf := '';
2772
2773 --<rwunderl:3145844> We need to commit and disable parallel DML so CP won't
2774 -- choke.
2775 commit;
2776 execute IMMEDIATE 'alter session disable parallel dml';
2777
2778 exception
2779 when others then
2780 execute IMMEDIATE 'alter session disable parallel dml';
2781 if (wf_core.error_name = 'WFDS_ORIGSYS_HIERARCHY_ENABLED') then
2782 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
2783 retcode := '1'; -- (warning)
2784 else
2785 retcode := '2'; -- (error)
2786 errbuf := sqlerrm;
2787 end if;
2788 WF_CORE.Clear;
2789 end;
2790
2791
2792 ------------------------------------------------------------------------------
2793 /*
2794 ** CheckCache - <private>
2795 **
2796 ** Checks to see if a role is in the cache of recently created roles.
2797 ** IN
2798 ** p_role_name VARCHAR2
2799 ** RETURNS
2800 ** BOOLEAN
2801 */
2802 FUNCTION CheckCache (p_role_name in VARCHAR2) return boolean
2803 is
2804 roleIND PLS_INTEGER;
2805 begin
2806 if NOT (WF_ROLE_HIERARCHY.validateSession(g_trustTimeStamp)) then
2807 g_trustedRoles.DELETE;
2808 return FALSE;
2809 end if;
2810
2811 roleIND := g_trustedRoles.FIRST;
2812 while (roleIND is NOT NULL) loop
2813 if (g_trustedRoles(roleIND) = p_role_name) then
2814 return TRUE;
2815 end if;
2816 roleIND := g_trustedRoles.NEXT(roleIND);
2817 end loop;
2818
2819 --If we did not yet return true, then the name is not in cache, so we
2820 --return false.
2821 return FALSE;
2822 end;
2823
2824 ------------------------------------------------------------------------------
2825 /*
2826 ** DeleteCache - <private>
2827 **
2828 ** Removes a role from the cache of newly created roles.
2829 ** IN
2830 ** p_role_name VARCHAR2
2831 */
2832 PROCEDURE DeleteCache (p_role_name in VARCHAR2)
2833 is
2834 roleIND PLS_INTEGER;
2835 begin
2836 roleIND := g_trustedRoles.FIRST;
2837 while (roleIND is NOT NULL) loop
2838 if (g_trustedRoles(roleIND) = p_role_name) then
2839 g_trustedRoles.DELETE(roleIND);
2840 end if;
2841 roleIND := g_trustedRoles.NEXT(roleIND);
2842 end loop;
2843 end;
2844
2845 ------------------------------------------------------------------------------
2846 /*
2847 ** ValidateUserRoles - Validates and corrects denormalized user and role
2848 ** information in user/role relationships.
2849 */
2850 PROCEDURE ValidateUserRoles(p_BatchSize in NUMBER,
2851 p_check_dangling in BOOLEAN,
2852 p_check_missing_ura in BOOLEAN,
2853 p_UpdateWho in BOOLEAN,
2854 p_parallel_processes in number) is
2855 begin
2856 WF_MAINTENANCE.ValidateUserRoles(p_BatchSize,
2857 p_check_dangling,
2858 p_check_missing_ura,
2859 p_UpdateWho,
2860 p_parallel_processes);
2861 end;
2862
2863 ------------------------------------------------------------------------------
2864 /*
2865 ** ValidateUserRoles_conc - CM cover routine for ValidateUserRoles()
2866 */
2867 PROCEDURE ValidateUserRoles_conc(errbuf out NOCOPY varchar2,
2868 retcode out NOCOPY varchar2,
2869 p_BatchSize in varchar2,
2870 p_check_dangling in varchar2,
2871 p_check_missing_ura in varchar2,
2872 p_UpdateWho in varchar2,
2873 p_parallel_processes in number) is
2874
2875 l_checkDangling BOOLEAN;
2876 l_checkMissingURA BOOLEAN;
2877 l_UpdateWho BOOLEAN;
2878 begin
2879
2880 if(nvl(p_check_missing_ura, 'N') = 'Y') then
2881 l_checkMissingURA := TRUE;
2882 else
2883 l_checkMissingURA := FALSE;
2884 end if;
2885
2886 if(nvl(p_check_dangling, 'N') = 'Y') then
2887 l_checkDangling := TRUE;
2888 else
2889 l_checkDangling := FALSE;
2890 end if;
2891
2892 if(nvl(p_UpdateWho,'N')='Y') then
2893 l_UpdateWho := TRUE;
2894 else
2895 l_UpdateWho := FALSE;
2896 end if;
2897
2898 ValidateUserRoles(to_number(p_BatchSize),l_checkDangling,l_checkMissingURA,l_UpdateWho
2899 , p_parallel_processes);
2900 retcode := '0'; -- (successful completion)
2901 errbuf := '';
2902 exception
2903 when OTHERS then
2904 retcode := '2'; -- (error)
2905 errbuf := sqlerrm;
2906 end;
2907 end WF_LOCAL_SYNCH;
[Home] [Help]