[Home] [Help]
PACKAGE BODY: APPS.AME_MIGRATION_REPORT
Source
1 package body ame_migration_report as
2 /* $Header: amemigrp.pkb 120.0 2005/07/26 06:03 mbocutt noship $ */
3 -- Constants
4 dynamicPreApprover constant varchar2(50) := 'dynamic pre-approver';
5 dynamicPostApprover constant varchar2(50) := 'dynamic post-approver';
6 --
7 -- Type declaration
8 --
9 type approver_rec is record(
10 id integer
11 ,user_id integer
12 ,user_name varchar2(100)
13 ,source varchar2(100)
14 ,source_object ame_util.longStringType
15 ,description varchar2(1000)
16 );
17 type approvers_table is table of approver_rec index by binary_integer;
18 type person_rec is record(
19 person_id integer
20 ,wf_role_name varchar2(320)
21 ,full_name varchar2(100)
22 );
23 type person_table is table of person_rec index by binary_integer;
24 --
25 -- variable declaration
26 --
27 approversToBeMigrated approvers_table;
28 invalidUsers approvers_table;
29 migratedApprovers approvers_table;
30 personList person_table;
31 --
32 -- P R I V A T E R O U T I N E S
33 --
34 --
35 -- Proc: addApprover
36 --
37 procedure addApprover(id integer
38 ,user_id integer default null
39 ,user_name varchar2 default null
40 ,source varchar2
41 ,source_object varchar2
42 ,description varchar2
43 ,approvers in out nocopy approvers_table) as
44 tableIndex number;
45 --
46 --
47 --
48 cursor c_person (p_person_id number) is
49 select person_id, full_name
50 from per_all_people_f
51 where person_id = p_person_id
52 and sysdate between effective_start_date and effective_end_date;
53 begin
54 tableIndex := approvers.count + 1;
55 approvers(tableIndex).id := id;
56 approvers(tableIndex).user_id := user_id;
57 approvers(tableIndex).user_name := user_name;
58 approvers(tableIndex).source := source;
59 approvers(tableIndex).source_object := source_object;
60 approvers(tableIndex).description := description;
61 if not personList.exists(id) then
62 open c_person(id);
63 fetch c_person
64 into personList(id).person_id, personList(id).full_name;
65 if c_person%found then
66 personList(id).wf_role_name := 'AME_MIGRATION_'||personList(id).person_id;
67 else
68 personList(id).wf_role_name := 'AME_INVALID_APPROVER';
69 end if;
70 close c_person;
71 end if;
72 end addApprover;
73 --
74 -- Proc: sortApproversTable
75 --
76 procedure sortApproversTable(approvers in out nocopy approvers_table) as
77 l_appr_rec approver_rec;
78 l_person_id number;
79 l_appr_total integer;
80 begin
81 l_appr_total := approvers.count;
82 for i in 1..l_appr_total loop
83 for j in i+1..l_appr_total loop
84 if approvers(j).id < approvers(i).id then
85 l_appr_rec := approvers(i);
86 approvers(i) := approvers(j);
87 approvers(j) := l_appr_rec;
88 end if;
89 end loop;
90 end loop;
91 end sortApproversTable;
92 --
93 -- Proc wrapAndPrint (Assuming there is only 5 columns to be printed)
94 --
95 procedure wrapAndPrint(pTotalPrintColumns number
96 ,pStartPos number
97 ,pValue1 varchar2, pWidth1 number
98 ,pValue2 varchar2 default null ,pWidth2 number default null
99 ,pValue3 varchar2 default null ,pWidth3 number default null
100 ,pValue4 varchar2 default null ,pWidth4 number default null
101 ,pValue5 varchar2 default null ,pWidth5 number default null
102 ,pSpacer number default 2
103 ) as
104 --
105 Type NumberArray is varray(5) of integer;
106 Type ValueArray is varray(5) of varchar2(100);
107 --
108 widthArray NumberArray;
109 valueList ValueArray;
110 --
111 l_print_string varchar2(200);
112 l_temp_string varchar2(200);
113 l_word_pos number;
114 l_value_length number;
115 --
116 function print_not_over return boolean is
117 begin
118 for i in 1..pTotalPrintColumns loop
119 if lengthb(valueList(i)) > 0 then
120 return true;
121 end if;
122 end loop;
123 return false;
124 end;
125 begin
126 if pTotalPrintColumns < 1 or pTotalPrintColumns > 5 then
127 return;
128 end if;
129 widthArray := NumberArray(pWidth1,pWidth2,pWidth3,pWidth4,pWidth5);
130 valueList := ValueArray(pValue1,pValue2,pValue3,pValue4,pValue5);
131 while (print_not_over) loop
132 l_print_string := rpad(fnd_global.local_chr(32), pStartPos, fnd_global.local_chr(32));
133 for i in 1..pTotalPrintColumns loop
134 --
135 l_value_length := lengthb(valueList(i));
136 if l_value_length = 0 then
137 l_temp_string := fnd_global.local_chr(32);
138 elsif l_value_length <= widthArray(i) then
139 l_temp_string := valueList(i);
140 valueList(i) := '';
141 else
142 --
143 --we do not want to break the sentence abruptly, hence try to find the last word
144 --upto where we can truncate
145 --
146 --try to if we can find a word that is separated by space
147 --if not try to find a word separated by tab
148 --if not it looks like a single word and hence needs doing a substrb at column width
149 --
150 l_word_pos := instrb(substrb(valueList(i),1, widthArray(i)), fnd_global.local_chr(32),-1);
151 if l_word_pos = 0 then
152 --check for tab
153 l_word_pos := instrb(substrb(valueList(i),1,widthArray(i)), fnd_global.local_chr(9),-1);
154 if l_word_pos = 0 then
155 l_word_pos := widthArray(i);
156 end if;
157 end if;
158 l_temp_string := substrb(valueList(i), 1, l_word_pos);
159 valueList(i) := substrb(valueList(i), l_word_pos+1);
160 end if;
161 --
162 l_temp_string := rpad(l_temp_string,widthArray(i)+pSpacer,fnd_global.local_chr(32));
163 l_print_string := l_print_string || l_temp_string;
164 --
165 end loop;
166 fnd_file.put_line(fnd_file.output, l_print_string);
167 end loop;
168 end wrapAndPrint;
169 --
170 -- Generate reprot
171 --
172 procedure printReport as
173 -- Variables
174 curr_person_id integer;
175 prev_person_id integer;
176 --
177 -- Custom Handlers
178 --
179 cursor customHandlers is
180 select acty.name,
181 acty.procedure_name
182 from ame_action_types acty
183 where acty.created_by <> 1 and
184 not exists (select null
185 from ame_action_types
186 where procedure_name = acty.procedure_name and
187 created_by = 1 and
188 sysdate between start_date and
189 nvl(end_date - (ame_util.oneSecond), sysdate)) and
190 sysdate between acty.start_date and
191 nvl(acty.end_date - (ame_util.oneSecond), sysdate);
192 --
193 --Dynamic action types - to be moved to approval groups
194 --
195 cursor dynamicActionTypes is
196 select acty.name action_type
197 ,act.description action
198 ,act.parameter source_attribute
199 from ame_actions act,
200 ame_action_types acty
201 where act.action_type_id = acty.action_type_id and
202 acty.name in (dynamicPreApprover, dynamicPostApprover) and
203 sysdate between act.start_date and nvl(act.end_date - (ame_util.oneSecond), sysdate) and
204 sysdate between acty.start_date and nvl(acty.end_date - (ame_util.oneSecond), sysdate);
205 begin
206 fnd_file.put_line(fnd_file.output, rpad('Test 1', 11, ' ')||': HR People - To be migrated');
207 fnd_file.put_line(fnd_file.output, rpad('Data Changes', 11, ' ')||': This lists down all the people (used within AME) that will get migrated to WF_ROLES');
208 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
209 fnd_file.put_line(fnd_file.output, rpad('Name', 52, ' ') ||rpad('WF_ROLES.NAME', 50,' '));
210 fnd_file.put_line(fnd_file.output, lpad(' ', 5, ' ')||rpad('Source Table', 32, ' ')||rpad('Source Object', 42,' ') || rpad('Description', 50,' '));
211 fnd_file.put_line(fnd_file.output, rpad('-', 132, '-'));
212 if approversToBeMigrated.count > 0 then
213 curr_person_id := -1;
214 for i in 1..approversToBeMigrated.count loop
215 if(curr_person_id <> approversToBeMigrated(i).id) then
216 curr_person_id := approversToBeMigrated(i).id;
217 fnd_file.put_line(fnd_file.output, '--');
218 fnd_file.put_line(fnd_file.output, '--');
219 wrapAndPrint(pTotalPrintColumns => 2
220 ,pStartPos => 1
221 ,pValue1 => personList(curr_person_id).full_name
222 ,pWidth1 => 50
223 ,pValue2 => personList(curr_person_id).wf_role_name
224 ,pWidth2 => 50);
225 end if;
226 if approversToBeMigrated(i).source_object is null then
227 wrapAndPrint(pTotalPrintColumns => 1
228 ,pStartPos => 6
229 ,pValue1 => approversToBeMigrated(i).source
230 ,pWidth1 => 30);
231 else
232 wrapAndPrint(pTotalPrintColumns => 3
233 ,pStartPos => 6
234 ,pValue1 => approversToBeMigrated(i).source
235 ,pWidth1 => 30
236 ,pValue2 => approversToBeMigrated(i).source_object
237 ,pWidth2 => 40
238 ,pValue3 => approversToBeMigrated(i).description
239 ,pWidth3 => 50);
240 end if;
241 end loop;
242 end if;
243 --
244 --
245 --
246 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
247 fnd_file.put_line(fnd_file.output, rpad('Test 2', 11, ' ')||': HR People - Already migrated');
248 fnd_file.put_line(fnd_file.output, rpad('Data Changes', 11, ' ')||': This lists down all the people (used within AME) already migrated to WF_ROLES');
249 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
250 fnd_file.put_line(fnd_file.output, rpad('Name', 52, ' ') ||rpad('WF_ROLES.NAME', 50,' '));
251 fnd_file.put_line(fnd_file.output, lpad(' ', 5, ' ')||rpad('Source Table', 32, ' ')||rpad('Source Object', 42,' ') || rpad('Description', 50,' '));
252 fnd_file.put_line(fnd_file.output, rpad('-', 132, '-'));
253 if migratedApprovers.count > 0 then
254 curr_person_id := -1;
255 for i in 1..migratedApprovers.count loop
256 if(curr_person_id <> migratedApprovers(i).id) then
257 curr_person_id := migratedApprovers(i).id;
258 fnd_file.put_line(fnd_file.output, '--');
259 fnd_file.put_line(fnd_file.output, '--');
260 wrapAndPrint(pTotalPrintColumns => 2
261 ,pStartPos => 1
262 ,pValue1 => personList(curr_person_id).full_name
263 ,pWidth1 => 50
267 if migratedApprovers(i).source_object is null then
264 ,pValue2 => personList(curr_person_id).wf_role_name
265 ,pWidth2 => 50);
266 end if;
268 wrapAndPrint(pTotalPrintColumns => 1
269 ,pStartPos => 6
270 ,pValue1 => migratedApprovers(i).source
271 ,pWidth1 => 30);
272 else
273 wrapAndPrint(pTotalPrintColumns => 3
274 ,pStartPos => 6
275 ,pValue1 => migratedApprovers(i).source
276 ,pWidth1 => 30
277 ,pValue2 => migratedApprovers(i).source_object
278 ,pWidth2 => 40
279 ,pValue3 => migratedApprovers(i).description
280 ,pWidth3 => 50);
281 end if;
282 end loop;
283 end if;
284 --
285 --
286 --
287 fnd_file.put_line(fnd_file.output, '--');
288 fnd_file.put_line(fnd_file.output, '--');
289 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
290 fnd_file.put_line(fnd_file.output, rpad('Test 3', 11, ' ')||': FND User');
291 fnd_file.put_line(fnd_file.output, rpad('Data Changes', 11, ' ')||': This table lists all the FND User (linked to a person) used within AME.');
292 fnd_file.put_line(fnd_file.output, lpad(' ', 11, ' ')||': Before migration these approvers are shown as FND Users.');
293 fnd_file.put_line(fnd_file.output, lpad(' ', 11, ' ')||': After migration these approvers will be shown as HR People.');
294 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
295 fnd_file.put_line(fnd_file.output, rpad('Source', 32, ' ') ||rpad('Source Name', 32,' ') || rpad('Migrated From', 32,' ') ||rpad('Migrated To', 30,' '));
296 fnd_file.put_line(fnd_file.output, lpad('(User Name)', 75, ' ') ||lpad('(HR People)', 32,' '));
297 fnd_file.put_line(fnd_file.output, rpad('-', 132, '-'));
298 if invalidUsers.count > 0 then
299 for i in 1..invalidUsers.count loop
300 wrapAndPrint(pTotalPrintColumns => 4
301 ,pStartPos => 1
302 ,pValue1 => invalidUsers(i).source
303 ,pWidth1 => 30
304 ,pValue2 => invalidUsers(i).source_object
305 ,pWidth2 => 30
306 ,pValue3 => invalidUsers(i).user_name
307 ,pWidth3 => 30
308 ,pValue4 => personList(invalidUsers(i).id).full_name
309 ,pWidth4 => 30);
310 end loop;
311 end if;
312 --
313 --dynamic pre/post-approver types - which need to be migrated to the corresponding approval groups
314 --
315 fnd_file.put_line(fnd_file.output, '--');
316 fnd_file.put_line(fnd_file.output, '--');
317 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
318 fnd_file.put_line(fnd_file.output, rpad('Test 4', 11, ' ')||': Dynamic Approval Types');
319 fnd_file.put_line(fnd_file.output, rpad('Data Changes', 11, ' ')||': "dynamic pre-approver" and "dynamic post-approver" actions will be moved over to use Approval Groups.');
320 fnd_file.put_line(fnd_file.output, rpad(' ', 11, ' ')||' And the groups will be created with prefix "Dyn. Pre" and "Dyn. Post" respectively');
321 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
322 fnd_file.put_line(fnd_file.output, rpad('Action Type', 32, ' ') ||rpad('Action', 52,' ')|| rpad('"source attribute"', 30,' '));
323 fnd_file.put_line(fnd_file.output, rpad('-', 132, '-'));
324 for r in dynamicActionTypes loop
325 wrapAndPrint(pTotalPrintColumns => 3
326 ,pStartPos => 1
327 ,pValue1 => r.action_type
328 ,pWidth1 => 30
329 ,pValue2 => r.action
330 ,pWidth2 => 50
331 ,pValue3 => r.source_attribute
332 ,pWidth3 => 30);
333 end loop;
334 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
335 --------------------------------------------------------------------
336 -- get custom handlers which require to be re-written
337 --------------------------------------------------------------------
338 fnd_file.put_line(fnd_file.output, rpad('Test 5', 11, ' ')||': Custom Handlers');
339 fnd_file.put_line(fnd_file.output, rpad('Data Changes', 11, ' ')||': The following Custom Handlers (Action Types) will need to be re-written.');
340 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
341 fnd_file.put_line(fnd_file.output, rpad('Action Type Name', 52, ' ') ||rpad('Handler Name', 50,' '));
342 fnd_file.put_line(fnd_file.output, rpad('-', 132, '-'));
343 for r in customHandlers loop
344 wrapAndPrint(pTotalPrintColumns => 2
345 ,pStartPos => 1
346 ,pValue1 => r.name
347 ,pWidth1 => 50
348 ,pValue2 => r.procedure_name
349 ,pWidth2 => 50);
350 end loop;
351 fnd_file.put_line(fnd_file.output, rpad('=', 132, '='));
352 fnd_file.put_line(fnd_file.output, '*** End of Report ***');
353 end printReport;
354 --
355 -- P U B L I C R O U T I N E S
356 --
357 --
358 -- proc generateReport
359 --
360 procedure generateReport(errbuf out nocopy varchar2,
361 retcode out nocopy number) as
362 --
363 -- Variables
364 --
365 appIndustry VARCHAR2(30);
366 appOracleSchema VARCHAR2(30);
367 appStatus VARCHAR2(30);
368 is11510Applied varchar2(255);
369 isAppInstalled BOOLEAN;
370 lmConditionDesc varchar2(100);
371 --
372 --
373 --
374 cursor c_AME_11510_Patch (table_owner varchar2) is
375 select 'Y'
376 from all_tables
377 where table_name = 'AME_ACTION_USAGES'
378 and owner = table_owner;
379 --
380 -- Before Migration Cursors
381 --
382 --
383 -- ame_temp_old_approver_lists
384 --
385 cursor c_oldAppr_NoWFRoles is
386 select distinct oldappr.person_id person_id
387 from ame_temp_old_approver_lists oldappr
388 where oldappr.person_id is not null
389 and not exists (select null
390 from wf_roles wf
391 where wf.orig_system_id = oldappr.person_id
392 and wf.orig_system = 'PER'
393 and wf.status = 'ACTIVE'
394 and (wf.expiration_date is null or
395 sysdate < wf.expiration_date)
396 and rownum < 2);
397 --
398 -- ame_temp_deletions
399 --
400 cursor c_delAppr_NoWfRoles is
401 select distinct del.person_id person_id
402 from ame_temp_deletions del
403 where del.person_id is not null
404 and not exists (select null from wf_roles wf
405 where wf.orig_system_id = del.person_id
406 and wf.orig_system = 'PER'
407 and wf.status = 'ACTIVE'
408 and (wf.expiration_date is null or
409 sysdate < wf.expiration_date)
410 and rownum < 2);
411 --
412 -- ame_temp_insertions
413 --
414 cursor c_insAppr_NoWfRoles is
415 select distinct ins.person_id person_id
416 from ame_temp_insertions ins
417 where ins.person_id is not null
418 and not exists (select null from wf_roles wf
419 where wf.orig_system_id = ins.person_id
420 and wf.orig_system = 'PER'
421 and wf.status = 'ACTIVE'
422 and (wf.expiration_date is null or
423 sysdate < wf.expiration_date)
424 and rownum < 2);
425 --
426 -- ame_conditions - LM
427 --
428 cursor c_condPerson_NoWfRoles is
432 from ame_conditions cond
429 select distinct cond.condition_id
430 ,cond.parameter_one
431 ,cond.parameter_two person_id
433 where cond.parameter_one in ('any_approver_person_id','final_approver_person_id')
434 and not exists (select null from wf_roles wf
435 where wf.orig_system_id = cond.parameter_two
436 and wf.orig_system = 'PER'
437 and wf.status = 'ACTIVE'
438 and (wf.expiration_date is null or
439 sysdate < wf.expiration_date)
440 and rownum < 2);
441 --
442 -- ame_approval_group_items
443 --
444 cursor c_grpItems_NoWfRoles is
445 select distinct grpitems.approval_group_id
446 ,parameter person_id
447 ,grp.name
448 from ame_approval_group_items grpitems
449 ,ame_approval_groups grp
450 where grpitems.parameter_name = ame_util.approverPersonId
451 and grp.approval_group_id = grpitems.approval_group_id
452 and grp.start_date = (select max(start_date)
453 from ame_approval_groups
454 where approval_group_id = grp.approval_group_id)
455 and not exists (select null from wf_roles wf
456 where wf.orig_system_id = grpitems.parameter
457 and wf.orig_system = 'PER'
458 and wf.status = 'ACTIVE'
459 and (wf.expiration_date is null or
460 sysdate < wf.expiration_date)
461 and rownum < 2);
462 --
463 -- ame_approval_group_members
464 --
465 cursor c_grpMem_NoWfRoles is
466 select distinct grpmem.approval_group_id
467 ,grpmem.parameter person_id
468 ,grp.name
469 from ame_approval_group_members grpmem
470 ,ame_approval_groups grp
471 where grpmem.parameter_name = ame_util.approverPersonId
472 and grp.approval_group_id = grpmem.approval_group_id
473 and grp.start_date = (select max(start_date)
474 from ame_approval_groups
475 where approval_group_id = grp.approval_group_id)
476 and not exists (select null from wf_roles wf
477 where wf.orig_system_id = grpmem.parameter
478 and wf.orig_system = 'PER'
479 and wf.status = 'ACTIVE'
480 and (wf.expiration_date is null or
481 sysdate < wf.expiration_date)
482 and rownum < 2);
483 --
484 -- ame_config_vars (adminApprover)
485 --
486 cursor c_configVar_NoWfRoles is
487 select substrb(config1.variable_value, 11, instrb(config1.variable_value,',')-11) person_id
488 from ame_config_vars config1
489 where config1.variable_name = 'adminApprover'
490 and config1.variable_value like 'person_id%'
491 and not exists (select null from ame_config_vars config2
492 where config2.rowid = config1.rowid
493 --and config2.variable_value like 'person_id:,user_id%'
494 and substrb(config2.variable_value,1,12) in ('person_id:,u','person_id:0,'))
495 and not exists (select null from wf_roles wf
496 where wf.orig_system_id = substrb(config1.variable_value, 11, instrb(config1.variable_value,',')-11)
497 and wf.orig_system = 'PER'
498 and wf.status = 'ACTIVE'
499 and (wf.expiration_date is null or
500 sysdate < wf.expiration_date)
501 and rownum < 2);
502 --
503 -- ame_actions - substitution
504 --
505 cursor c_substituteAction_NoWFRoles is
506 select distinct action_id, substrb(parameter, instrb(parameter,':')+1) person_id, description
507 from ame_actions
508 where parameter like 'person_id:%'
509 and exists (select null
510 from ame_action_types
511 where name = ame_util.substitutionTypeName
512 and action_type_id = ame_actions.action_type_id
513 and rownum < 2)
514 and not exists (select null
515 from wf_roles wf
516 where wf.orig_system_id = substrb(ame_actions.parameter, instrb(ame_actions.parameter,':')+1)
517 and wf.orig_system = 'PER'
518 and wf.status = 'ACTIVE'
519 and (wf.expiration_date is null or
520 sysdate < wf.expiration_date)
521 and rownum < 2);
522 --
523 -- After Migration Cursors (Already migrated)
524 --
525 --
526 --ame_temp_old_approver_lists
527 --
528 cursor c_migrated_oldAppr is
529 select distinct oldappr.person_id person_id
530 from ame_temp_old_approver_lists oldappr
531 where oldappr.person_id is not null
532 and exists (select null
533 from wf_roles wf
534 where wf.orig_system_id = oldappr.person_id
535 and wf.orig_system = 'PER'
536 and wf.status = 'ACTIVE'
537 and (wf.expiration_date is null or
538 sysdate < wf.expiration_date)
539 and name like 'AME_MIGRATION%'
540 and rownum < 2);
541 --
542 --ame_temp_deletions
543 --
544 cursor c_migrated_delAppr is
545 select distinct del.person_id person_id
546 from ame_temp_deletions del
547 where del.person_id is not null
548 and exists (select null
549 from wf_roles wf
550 where wf.orig_system_id = del.person_id
551 and wf.orig_system = 'PER'
552 and wf.status = 'ACTIVE'
553 and (wf.expiration_date is null or
554 sysdate < wf.expiration_date)
555 and name like 'AME_MIGRATION%'
556 and rownum < 2);
557 --
558 --ame_temp_insertions
559 --
560 cursor c_migrated_insAppr is
561 select distinct ins.person_id person_id
562 from ame_temp_insertions ins
563 where ins.person_id is not null
564 and exists (select null
565 from wf_roles wf
566 where wf.orig_system_id = ins.person_id
567 and wf.orig_system = 'PER'
568 and wf.status = 'ACTIVE'
569 and (wf.expiration_date is null or
570 sysdate < wf.expiration_date)
571 and name like 'AME_MIGRATION%'
572 and rownum < 2);
573 --
574 -- ame_conditions - LM
575 --
576 cursor c_migrated_condPerson is
577 select distinct cond.condition_id
578 ,cond.parameter_one
579 ,substrb(cond.parameter_two, instrb(cond.parameter_two,'AME_MIGRATION_')+14) person_id
580 ,cond.parameter_two
581 from ame_conditions cond
582 where cond.parameter_one in ('any_approver','final_approver')
583 and cond.parameter_two like 'AME_MIGRATION%'
584 and exists (select null
585 from wf_roles wf
586 where wf.name = cond.parameter_two
587 and wf.orig_system = 'PER'
588 and wf.status = 'ACTIVE'
589 and (wf.expiration_date is null or
590 sysdate < wf.expiration_date)
591 and rownum < 2);
592 --
593 --ame_approval_group_items
594 --
595 cursor c_migrated_grpItems is
596 select distinct grpitems.approval_group_id
597 ,substrb(parameter, instrb(parameter,'AME_MIGRATION_')+14) person_id
598 ,parameter
599 ,grp.name
600 from ame_approval_group_items grpitems
601 ,ame_approval_groups grp
602 where grpitems.parameter_name = 'wf_roles_name'
603 and grp.approval_group_id = grpitems.approval_group_id
604 and grp.start_date = (select max(start_date)
605 from ame_approval_groups
606 where approval_group_id = grp.approval_group_id)
607 and parameter like 'AME_MIGRATION%'
608 and exists (select null from wf_roles wf
609 where wf.name = grpitems.parameter
610 and wf.orig_system = 'PER'
611 and wf.status = 'ACTIVE'
612 and (wf.expiration_date is null or
613 sysdate < wf.expiration_date)
614 and rownum < 2);
615 --
616 --ame_approval_group_members
617 --
618 cursor c_migrated_grpMem is
619 select distinct grpmem.approval_group_id
620 ,substrb(grpmem.parameter,instrb(grpmem.parameter,'AME_MIGRATION_')+14) person_id
621 ,grpmem.parameter
622 ,grp.name
623 from ame_approval_group_members grpmem
624 ,ame_approval_groups grp
625 where grpmem.parameter_name = 'wf_roles_name'
626 and grp.approval_group_id = grpmem.approval_group_id
627 and grp.start_date = (select max(start_date)
628 from ame_approval_groups
629 where approval_group_id = grp.approval_group_id)
630 and grpmem.parameter like 'AME_MIGRATION%'
631 and exists (select null from wf_roles wf
632 where wf.name = grpmem.parameter
633 and wf.orig_system = 'PER'
634 and wf.status = 'ACTIVE'
635 and (wf.expiration_date is null or
636 sysdate < wf.expiration_date)
637 and rownum < 2);
638 --
639 --ame_config_vars (adminApprover)
640 --
641 cursor c_migrated_configVar is
642 select substrb(config1.variable_value, instrb(config1.variable_value,'AME_MIGRATION_')+14) person_id
643 from ame_config_vars config1
644 where config1.variable_name = 'adminApprover'
645 and config1.variable_value like 'AME_MIGRATION%'
646 and exists (select null from wf_roles wf
647 where name = config1.variable_value
648 and wf.orig_system = 'PER'
649 and wf.status = 'ACTIVE'
650 and (wf.expiration_date is null or
651 sysdate < wf.expiration_date)
652 and rownum < 2);
653 --
654 --ame_actions - substitution
655 --
656 cursor c_migrated_substituteAction is
657 select distinct action_id, substrb(parameter, instrb(parameter,'AME_MIGRATION_')+14) person_id, description
658 from ame_actions
659 where parameter like 'AME_MIGRATION%'
660 and exists (select null
661 from ame_action_types
662 where name = ame_util.substitutionTypeName
663 and action_type_id = ame_actions.action_type_id
664 and rownum < 2)
665 and exists (select null
666 from wf_roles wf
667 where wf.name = ame_actions.parameter
668 and wf.orig_system = 'PER'
669 and wf.status = 'ACTIVE'
670 and (wf.expiration_date is null or
671 sysdate < wf.expiration_date)
672 and rownum < 2);
673 --
674 -- *********** Invalid Users in AME ***********
675 --
676 --
677 --ame_temp_old_approver_lists
678 --
679 cursor c_oldAppr_InvalidUser is
680 select distinct oldappr.user_id
681 ,fnd.employee_id person_id
682 ,fnd.user_name
683 from ame_temp_old_approver_lists oldappr
684 ,fnd_user fnd
685 where oldappr.user_id is not null
686 and oldappr.person_id is null
687 and fnd.employee_id is not null
688 and oldappr.user_id = fnd.user_id;
689 --
690 --ame_temp_deletions
691 --
692 cursor c_delAppr_InvalidUser is
693 select distinct del.user_id user_id
694 ,fnd.employee_id person_id
695 ,fnd.user_name
696 from ame_temp_deletions del
697 ,fnd_user fnd
698 where del.user_id is not null
699 and del.person_id is null
700 and fnd.employee_id is not null
701 and fnd.user_id = del.user_id;
702 --
703 --ame_temp_insertions
704 --
705 cursor c_insAppr_InvalidUser is
706 select distinct ins.user_id user_id
707 ,fnd.employee_id person_id
708 ,fnd.user_name
709 from ame_temp_insertions ins
710 ,fnd_user fnd
711 where ins.user_id is not null
712 and ins.person_id is null
713 and fnd.employee_id is not null
714 and fnd.user_id = ins.user_id;
715 --
716 --ame_conditions - LM
717 --
718 cursor c_condUser_InvalidUser is
719 select distinct condition_id
720 ,cond.parameter_two user_id
721 ,fnd.employee_id person_id
722 ,fnd.user_name
723 from ame_conditions cond
724 ,fnd_user fnd
725 where cond.parameter_one in ('any_approver_user_id','final_approver_user_id')
726 and fnd.employee_id is not null
727 and cond.parameter_two = to_char(fnd.user_id);
728 --
729 --ame_approval_group_items
730 --
731 cursor c_grpItems_InvalidUser is
732 select distinct grp.name
733 ,grpitems.parameter user_id
734 ,fnd.employee_id person_id
735 ,fnd.user_name
736 from ame_approval_group_items grpitems
737 ,ame_approval_groups grp
738 ,fnd_user fnd
739 where grpitems.parameter_name = 'user_id'
740 and fnd.employee_id is not null
741 and grpitems.parameter = to_char(fnd.user_id)
742 and grp.approval_group_id = grpitems.approval_group_id
743 and grp.start_date = (select max(start_date)
744 from ame_approval_groups
745 where approval_group_id = grp.approval_group_id);
746 --
747 --ame_approval_group_members
748 --
749 cursor c_grpMem_InvalidUser is
750 select distinct grp.name
751 ,grpmems.parameter user_id
752 ,fnd.employee_id person_id
753 ,fnd.user_name
754 from ame_approval_group_members grpmems
755 ,ame_approval_groups grp
756 ,fnd_user fnd
757 where grpmems.parameter_name = 'user_id'
758 and fnd.employee_id is not null
759 and grpmems.parameter = to_char(fnd.user_id)
760 and grp.approval_group_id = grpmems.approval_group_id
761 and grp.start_date = (select max(start_date)
762 from ame_approval_groups
763 where approval_group_id = grp.approval_group_id);
764 --
765 --ame_config_vars (adminApprover)
766 --
767 cursor c_configVar_InvalidUser is
768 select distinct substrb(config1.variable_value, instrb(config1.variable_value,':',-1)+1 ) user_id
769 ,fnd.employee_id person_id
770 ,fnd.user_name
771 from ame_config_vars config1
772 ,fnd_user fnd
773 where config1.variable_name = 'adminApprover'
774 and config1.variable_value like 'person_id:%'
775 and lengthb(substrb(config1.variable_value, instrb(config1.variable_value,':',-1)+1 )) > 0
776 and fnd.employee_id is not null
777 and substrb(config1.variable_value, instrb(config1.variable_value,':',-1)+1 ) = to_char(fnd.user_id);
778 --
779 -- ame_actions (substitution)
780 --
781 cursor c_substituteAction_InvalidUser is
782 select distinct action_id
783 ,substrb(parameter, instrb(parameter,':')+1) user_id
784 ,fnd.employee_id person_id
785 ,fnd.user_name
786 from ame_actions
787 ,fnd_user fnd
788 where parameter like 'user_id:%'
789 and fnd.employee_id is not null
790 and substrb(parameter, instrb(parameter,':')+1) = to_char(fnd.user_id)
791 and exists (select null
792 from ame_action_types
793 where name = ame_util.substitutionTypeName
794 and action_type_id = ame_actions.action_type_id
795 and rownum < 2);
796 begin
797 --
798 --
799 --
800 isAppInstalled := FND_INSTALLATION.GET_APP_INFO ('PER',
801 appStatus,
802 appIndustry,
803 appOracleSchema);
804 /* open c_AME_11510_Patch(appOracleSchema);
805 fetch c_AME_11510_Patch
806 into is11510Applied;
807 close c_AME_11510_Patch;*/
808 is11510Applied:= fnd_profile.value('AME_INSTALLATION_LEVEL');
809
810 --
811 -- ************* Approvers To be Migrated *************
812 --
813 --
814 --ame_config_vars - to be migrated
815 --
816 for r in c_configVar_NoWfRoles loop
817 addApprover(id => r.person_id
818 ,source => 'ame_config_vars'
819 ,source_object => 'n/a'
820 ,description => 'n/a'
821 ,approvers => approversToBeMigrated);
822 end loop;
823 --
824 --ame_conditions - to be migrated
825 --
826 for r in c_condPerson_NoWfRoles loop
827 if r.parameter_one = 'any_approver_person_id' then
828 lmConditionDesc := 'Any approver is person';
829 else --final_approver_person_id
830 lmConditionDesc := 'The final approver is person';
831 end if;
832 addApprover(id => r.person_id
833 ,source => 'ame_conditions'
834 ,source_object => 'condition_id : '||r.condition_id
835 ,description => lmConditionDesc
836 ,approvers => approversToBeMigrated);
837 end loop;
838 --
839 --ame_approval_group_items - to be migrated
840 --
841 for r in c_grpItems_NoWfRoles loop
842 addApprover(id => r.person_id
843 ,source => 'ame_approval_group_items'
844 ,source_object => 'approval_group_id : '||r.approval_group_id
845 ,description => r.name
846 ,approvers => approversToBeMigrated);
847 end loop;
848 --
849 --ame_approval_group_members - to be migrated
850 --
851 for r in c_grpMem_NoWfRoles loop
852 addApprover(id => r.person_id
853 ,source => 'ame_approval_group_members'
854 ,source_object => 'approval_group_id : '||r.approval_group_id
855 ,description => r.name
856 ,approvers => approversToBeMigrated);
857 end loop;
858 --
859 -- ame_actions - Substitution - to be migrated
860 --
861 for r in c_substituteAction_NoWfRoles loop
862 addApprover(id => r.person_id
863 ,source => 'ame_actions'
864 ,source_object => 'action_id : '||r.action_id
865 ,description => r.description
866 ,approvers => approversToBeMigrated);
867 end loop;
868 --
869 --ame_temp_deletions - to be migrated
870 --
871 for r in c_delAppr_NoWfRoles loop
872 addApprover(id => r.person_id
873 ,source => 'ame_temp_deletions'
874 ,source_object => 'n/a'
875 ,description => 'n/a'
876 ,approvers => approversToBeMigrated);
877 end loop;
878 --
879 -- ame_temp_insertions - to be migrated
880 --
881 for r in c_insAppr_NoWfRoles loop
882 addApprover(id => r.person_id
883 ,source => 'ame_temp_insertions'
884 ,source_object => 'n/a'
885 ,description => 'n/a'
886 ,approvers => approversToBeMigrated);
887 end loop;
888 --
889 -- ame_temp_old_approver_lists - to be migrated
890 --
891 for r in c_oldAppr_NoWFRoles loop
892 addApprover(id => r.person_id
893 ,source => 'ame_temp_old_approver_lists'
894 ,source_object => 'n/a'
895 ,description => 'n/a'
896 ,approvers => approversToBeMigrated);
897 end loop;
898 --
899 -- Sort the approver list
900 --
901 sortApproversTable(approversToBeMigrated);
902 --
903 -- ************ Approvers - Already Migrated ************
904 --
905 if is11510Applied is not null then
906 --
907 --ame_config_vars - already migrated
908 --
909 for r in c_migrated_configVar loop
910 addApprover(id => r.person_id
911 ,source => 'ame_config_vars'
912 ,source_object => 'n/a'
913 ,description => 'n/a'
914 ,approvers => migratedApprovers);
915 end loop;
916 --
917 --ame_conditions - already migrated
918 --
919 for r in c_migrated_condPerson loop
920 if r.parameter_one = 'any_approver' then
921 lmConditionDesc := 'Any approver is person';
922 else --final_approver
923 lmConditionDesc := 'The final approver is person';
924 end if;
925 addApprover(id => r.person_id
926 ,source => 'ame_conditions'
927 ,source_object => 'condition_id : '||r.condition_id
928 ,description => lmConditionDesc
929 ,approvers => migratedApprovers);
930 end loop;
931 --
932 --ame_approval_group_items - already migrated
933 --
937 ,source_object => 'approval_group_id : '||r.approval_group_id
934 for r in c_migrated_grpItems loop
935 addApprover(id => r.person_id
936 ,source => 'ame_approval_group_items'
938 ,description => r.name
939 ,approvers => migratedApprovers);
940 end loop;
941 --
942 --ame_approval_group_members - already migrated
943 --
944 for r in c_migrated_grpMem loop
945 addApprover(id => r.person_id
946 ,source => 'ame_approval_group_members'
947 ,source_object => 'approval_group_id : '||r.approval_group_id
948 ,description => r.name
949 ,approvers => migratedApprovers);
950 end loop;
951 --
952 -- ame_actions - Substitution - already migrated
953 --
954 for r in c_migrated_substituteAction loop
955 addApprover(id => r.person_id
956 ,source => 'ame_actions'
957 ,source_object => 'action_id : '||r.action_id
958 ,description => r.description
959 ,approvers => migratedApprovers);
960 end loop;
961 --
962 --ame_temp_deletions - already migrated
963 --
964 for r in c_migrated_delAppr loop
965 addApprover(id => r.person_id
966 ,source => 'ame_temp_deletions'
967 ,source_object => 'n/a'
968 ,description => 'n/a'
969 ,approvers => migratedApprovers);
970 end loop;
971 --
972 -- ame_temp_insertions - already migrated
973 --
974 for r in c_migrated_insAppr loop
975 addApprover(id => r.person_id
976 ,source => 'ame_temp_insertions'
977 ,source_object => 'n/a'
978 ,description => 'n/a'
979 ,approvers => migratedApprovers);
980 end loop;
981 --
982 -- ame_temp_old_approver_lists - to be migrated
983 --
984 for r in c_migrated_oldAppr loop
985 addApprover(id => r.person_id
986 ,source => 'ame_temp_old_approver_lists'
987 ,source_object => 'n/a'
988 ,description => 'n/a'
989 ,approvers => migratedApprovers);
990 end loop;
991 --
992 -- Sort the approver list
993 --
994 sortApproversTable(migratedApprovers);
995 end if;
996 --
997 -- ******** Invalid Users ********
998 --
999 --
1000 --ame_temp_old_approver_lists
1001 --
1002 for r in c_oldAppr_InvalidUser loop
1003 addApprover(id => r.person_id
1004 ,user_id => r.user_id
1005 ,user_name => r.user_name
1006 ,source => 'AME Runtime Table'
1007 ,source_object => 'ame_temp_old_approver_lists'
1008 ,description => null
1009 ,approvers => invalidUsers);
1010 end loop;
1011 --
1012 --ame_temp_deletions
1013 --
1014 for r in c_delAppr_InvalidUser loop
1015 addApprover(id => r.person_id
1016 ,user_id => r.user_id
1017 ,user_name => r.user_name
1018 ,source => 'AME Runtime Table'
1019 ,source_object => 'ame_temp_deletions'
1020 ,description => null
1021 ,approvers => invalidUsers);
1022 end loop;
1023 --
1024 --ame_temp_insertions
1025 --
1026 for r in c_insAppr_InvalidUser loop
1027 addApprover(id => r.person_id
1028 ,user_id => r.user_id
1029 ,user_name => r.user_name
1030 ,source => 'AME Runtime Table'
1031 ,source_object => 'ame_temp_insertions'
1032 ,description => null
1033 ,approvers => invalidUsers);
1034 end loop;
1035 --
1036 --ame_conditions
1037 --
1038 for r in c_condUser_InvalidUser loop
1039 addApprover(id => r.person_id
1040 ,user_id => r.user_id
1041 ,user_name => r.user_name
1042 ,source => 'ame_conditions'
1043 ,source_object => 'condition_id:'||r.condition_id
1044 ,description => null
1045 ,approvers => invalidUsers);
1046 end loop;
1047 --
1048 --ame_approval_group_items
1049 --
1050 for r in c_grpItems_InvalidUser loop
1051 addApprover(id => r.person_id
1052 ,user_id => r.user_id
1053 ,user_name => r.user_name
1054 ,source => 'ame_approval_group_items'
1055 ,source_object => 'group_name:'||r.name
1056 ,description => null
1057 ,approvers => invalidUsers);
1058 end loop;
1059 --
1060 --ame_approval_group_members
1061 --
1062 for r in c_grpMem_InvalidUser loop
1063 addApprover(id => r.person_id
1064 ,user_id => r.user_id
1065 ,user_name => r.user_name
1066 ,source => 'ame_approval_group_members'
1067 ,source_object => 'group_name:'||r.name
1068 ,description => null
1069 ,approvers => invalidUsers);
1070 end loop;
1071 --
1072 --ame_config_vars (adminApprover)
1073 --
1074 for r in c_configVar_InvalidUser loop
1075 addApprover(id => r.person_id
1076 ,user_id => r.user_id
1077 ,user_name => r.user_name
1078 ,source => 'AME Configuration Table'
1079 ,source_object => 'ame_config_vars'
1080 ,description => null
1081 ,approvers => invalidUsers);
1082 end loop;
1083 --
1084 -- ame_actions (substitution)
1085 --
1086 for r in c_substituteAction_InvalidUser loop
1087 addApprover(id => r.person_id
1088 ,user_id => r.user_id
1089 ,user_name => r.user_name
1090 ,source => 'ame_actions'
1091 ,source_object => 'action_id:'||r.action_id
1092 ,description => null
1093 ,approvers => invalidUsers);
1094 end loop;
1095 --
1096 -- Sort the invalid users
1097 --
1098 sortApproversTable(invalidUsers);
1099 --
1100 -- Print Report
1101 --
1102 printReport;
1103 --
1104 retcode := 0;
1105 errbuf := 'Report is Now Complete.';
1106 exception
1107 when others then
1108 rollback;
1109 if c_AME_11510_Patch%isOpen then
1110 close c_AME_11510_Patch;
1111 end if;
1112 end generateReport;
1113 end;