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