[Home] [Help]
PACKAGE BODY: APPS.AME_MIGRATION_PKG
Source
1 package BODY ame_migration_pkg as
2 /* $Header: amecpmig.pkb 120.1 2006/12/26 13:14:52 avarri noship $ */
3 procedure log_message
4 (p_message varchar2
5 ,p_errorcode integer default -20002
6 ) as
7 l_log_id integer;
8 pragma autonomous_transaction;
9 begin
10 select ame_exceptions_log_s.nextval
11 into l_log_id
12 from dual;
13
14 insert into ame_exceptions_log
15 (log_id,package_name,routine_name,transaction_id,application_id,exception_number,exception_string)
16 values
17 (l_log_id,'ame_migration_pkg','sql code block','','',p_errorcode,to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')|| p_message);
18 commit;
19 exception
20 when others then
21 rollback;
22 end log_message;
23 --+
24 PROCEDURE assign_role(p_role_name IN varchar2
25 ,p_justification IN varchar2
26 ,p_requested_start_date IN varchar2
27 ,p_requested_end_date IN varchar2
28 ,p_requested_for_user_id IN varchar2) is
29 l_registration_data UMX_REGISTRATION_PVT.UMX_REGISTRATION_DATA_TBL;
30 begin
31 --+
32 l_registration_data(1).attr_name := 'wf_role_name';
33 l_registration_data(1).attr_value := p_role_name;
34
35 l_registration_data(2).attr_name := 'justification';
36 l_registration_data(2).attr_value := p_justification;
37
38 l_registration_data(3).attr_name := 'requested_start_date';
39 l_registration_data(3).attr_value := null;
40
41 l_registration_data(4).attr_name := 'requested_end_date';
42 l_registration_data(4).attr_value := null;
43
44 l_registration_data(5).attr_name := 'requested_for_user_id';
45 l_registration_data(5).attr_value := p_requested_for_user_id;
46
47 umx_pub.assign_role(p_registration_data => l_registration_data);
48 --+
49 exception
50 WHEN OTHERS then
51 raise;
52 END assign_role;
53 --+
54 procedure grant_all_rows(p_user_name in varchar2) is
55 l_grant_guid raw(16);
56 l_success varchar2(1);
57 l_error_code number;
58 --+
59 begin
60 --+
61 fnd_grants_pkg.grant_function
62 (
63 p_api_version => 1.0,
64 p_menu_name => 'AME_TRANS_TYPE_DATA_PERM_SET',
65 p_object_name => 'AME_TRANSACTION_TYPES',
66 p_instance_type => 'GLOBAL',
67 p_instance_set_id => NULL,
68 p_instance_pk1_value => NULL,
69 p_instance_pk2_value => NULL,
70 p_instance_pk3_value => NULL,
71 p_instance_pk4_value => NULL,
72 p_instance_pk5_value => NULL,
73 p_grantee_type =>'USER',
74 p_grantee_key => p_user_name,
75 p_start_date => sysdate,
76 p_end_date => null,
77 p_program_name => NULL,
78 p_program_tag => NULL,
79 x_grant_guid => l_grant_guid,
80 x_success => l_success, /* Boolean */
81 x_errorcode => l_error_code,
82 p_parameter1 => NULL,
83 p_parameter2 => NULL,
84 p_parameter3 => NULL,
85 p_parameter4 => NULL,
86 p_parameter5 => NULL,
87 p_parameter6 => NULL,
88 p_parameter7 => NULL,
89 p_parameter8 => NULL,
90 p_parameter9 => NULL,
91 p_parameter10 => NULL,
92 p_ctx_secgrp_id => -1,
93 p_ctx_resp_id => -1,
94 p_ctx_resp_appl_id => -1,
95 p_ctx_org_id => -1,
96 p_name => null,
97 p_description => null
98 );
99 --+
100 end grant_all_rows;
101 --+
102 procedure grant_instance(p_user_name in varchar2
103 ,p_fnd_application_id in number
104 ,p_transaction_type_id IN varchar2) is
105 l_grant_guid raw(16);
106 l_success varchar2(1);
107 l_error_code number;
108 --+
109 begin
110 --+
111 fnd_grants_pkg.grant_function
112 (
113 p_api_version => 1.0,
114 p_menu_name => 'AME_TRANS_TYPE_DATA_PERM_SET',
115 p_object_name => 'AME_TRANSACTION_TYPES',
116 p_instance_type => 'INSTANCE',
117 p_instance_set_id => NULL,
118 p_instance_pk1_value => p_fnd_application_id,
119 p_instance_pk2_value => p_transaction_type_id,
120 p_instance_pk3_value => NULL,
121 p_instance_pk4_value => NULL,
122 p_instance_pk5_value => NULL,
123 p_grantee_type =>'USER',
124 p_grantee_key => p_user_name,
125 p_start_date => sysdate,
126 p_end_date => null,
127 p_program_name => NULL,
128 p_program_tag => NULL,
129 x_grant_guid => l_grant_guid,
130 x_success => l_success, /* Boolean */
131 x_errorcode => l_error_code,
132 p_parameter1 => NULL,
133 p_parameter2 => NULL,
134 p_parameter3 => NULL,
135 p_parameter4 => NULL,
136 p_parameter5 => NULL,
137 p_parameter6 => NULL,
138 p_parameter7 => NULL,
139 p_parameter8 => NULL,
140 p_parameter9 => NULL,
141 p_parameter10 => NULL,
142 p_ctx_secgrp_id => -1,
143 p_ctx_resp_id => -1,
144 p_ctx_resp_appl_id => -1,
145 p_ctx_org_id => -1,
146 p_name => null,
147 p_description => null
148 );
149 --+
150 end grant_instance;
151 --+
152 procedure migrate_amea_users
153 (errbuf out nocopy varchar2
154 ,retcode out nocopy number
155 ) as
156 --+
157 cursor get_all_ame_users is
158 select userresp.user_id
159 ,resp.responsibility_key
160 ,resp.responsibility_id
161 ,resp.application_id
162 ,users.user_name
163 ,userresp.security_group_id
164 ,appl.application_short_name
165 ,sec.security_group_key
166 from fnd_user_resp_groups userresp
167 ,fnd_responsibility_vl resp
168 ,fnd_user users
169 ,fnd_application appl
170 ,fnd_security_groups sec
171 where resp.responsibility_id = userresp.responsibility_id
172 and resp.responsibility_key in ('AMELIMUSER'
173 ,'AMEGENUSER'
174 ,'AMEAPPADM'
175 )
176 and users.user_id = userresp.user_id
177 and appl.application_id = resp.application_id
178 and sec.security_group_id = userresp.security_group_id
179 and users.start_date <= sysdate and
180 (users.end_date is null or users.end_date > sysdate)
181 and userresp.start_date <= sysdate and
182 (userresp.end_date is null or userresp.end_date > sysdate)
183 and resp.start_date <= sysdate and
184 (resp.end_date is null or resp.end_date > sysdate)
185 order by userresp.user_id, resp.responsibility_key;
186 --+
187 cursor get_sec_web_attr(p_user_id in number) is
188 select aca.fnd_application_id
189 ,aca.transaction_type_id
190 from ak_web_user_sec_attr_values sec
191 ,fnd_application app
192 ,ame_calling_apps aca
193 where sec.attribute_code = 'AME_INTERNAL_TRANS_TYPE_ID'
194 and app.application_short_name = 'ICX'
195 and sec.attribute_application_id = app.application_id
196 and sec.web_user_id = p_user_id
197 and sysdate between aca.start_date AND nvl(aca.end_date-1/86400,sysdate)
198 and aca.application_id = sec.NUMBER_VALUE;
199 --+
200 cursor get_old_responsibilities is
201 select responsibility_id
202 ,application_id
203 from fnd_responsibility_vl
204 where responsibility_key in ('AMEAPPADM', 'AMEGENUSER', 'AMELIMUSER')
205 and start_date <= sysdate and
206 (end_date is null or end_date > sysdate);
207 --+
208 l_current_user_id number;
209 l_all_users_mig boolean;
210 --+
211 begin
212 --+
213 /* Assign AME_APP_ADMIN role to users with AMEAPPADM responsibility
214 * Assign AME_BUS_ANALYST role to users with AMEGENUSER and AMELIMUSER responsibility
215 * Grant all rows of ame_calling_apps using all_rows grant on object 'AME_TRANSACTION_TYPES' to
216 * AMEGENUSER and AMEAPPADM
217 * Grant all rows represented by securing attributes on object 'AME_TRANSACTION_TYPES' to AMELIMUSER
218 */
219 --+
220 log_message ('AME User Responsibility Migration Process started at ' ||
221 to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
222 --+
223 errbuf := null;
224 retcode := 0;
225 l_current_user_id := null;
226 --+
227 for rec in get_all_ame_users loop
228 --+
229 log_message ('User: '||rec.user_name||' responsibility: '||rec.responsibility_key
230 ||' security_group_id: '||rec.security_group_key);
231 --+
232 if rec.responsibility_key = 'AMEAPPADM' then
233 --+
234 begin
235 --+
236 l_current_user_id := rec.user_id;
237 --+
238 assign_role(p_role_name => 'UMX|AME_APP_ADMIN'
239 ,p_justification => 'AME Admin User Migration: '||rec.user_id
240 ,p_requested_start_date => null
241 ,p_requested_end_date => null
242 ,p_requested_for_user_id => rec.user_id);
243 --+
244 grant_all_rows(p_user_name => rec.user_name);
245 --+
246 fnd_user_pkg.DelResp(username => rec.user_name,
247 resp_app => rec.application_short_name,
248 resp_key =>rec.responsibility_key,
249 security_group => rec.security_group_key);
250 commit;
251 --+
252 exception
253 when others then
254 log_message ('User Responsibility Migration Failed at AMEAPPADM resp for user '
255 ||rec.user_name || '. Error: '||sqlerrm);
256 retcode := 1;
257 rollback;
258 end;
259 --+
260 elsif rec.responsibility_key = 'AMEGENUSER' THEN
261 --+
262 begin
263 if(l_current_user_id is null or l_current_user_id <> rec.user_id) then
264 assign_role(p_role_name => 'UMX|AME_BUS_ANALYST'
265 ,p_justification => 'AME General User Migration: '||rec.user_id
266 ,p_requested_start_date => null
267 ,p_requested_end_date => null
268 ,p_requested_for_user_id => rec.user_id);
269
270 --+
271 grant_all_rows(p_user_name => rec.user_name);
272 --+
273 l_current_user_id := rec.user_id;
274 end if;
275 --+
276 fnd_user_pkg.DelResp (username => rec.user_name,
277 resp_app => rec.application_short_name,
278 resp_key =>rec.responsibility_key,
279 security_group => rec.security_group_key);
280 commit;
281 --+
282 exception
283 when others then
284 log_message('User Responsibility Migration Failed at AMEGENUSER resp for user '
285 ||rec.user_name||'. Error: '||sqlerrm);
286 retcode := 1;
287 rollback;
288 end;
289 --+
290 elsif rec.responsibility_key = 'AMELIMUSER' then
291 --+
292 begin
293 --+
294 if(l_current_user_id is null or l_current_user_id <> rec.user_id) then
295 --+
296 assign_role(p_role_name => 'UMX|AME_BUS_ANALYST'
297 ,p_justification => 'AME Limited User Migration: '||rec.user_id
298 ,p_requested_start_date => null
299 ,p_requested_end_date => null
300 ,p_requested_for_user_id => rec.user_id);
301
302 --+
303 for secattr in get_sec_web_attr(p_user_id => rec.user_id) loop
304 --+
305 log_message('user: '||rec.user_name||' securing attribute: '||
306 secattr.fnd_application_id||', '||secattr.transaction_type_id);
307 --+
308 grant_instance(p_user_name => rec.user_name
309 ,p_fnd_application_id => secattr.fnd_application_id
310 ,p_transaction_type_id => secattr.transaction_type_id);
311 --+
312 end loop;
313 l_current_user_id := rec.user_id;
314 end if;
315 --+
316 fnd_user_pkg.DelResp (username => rec.user_name,
317 resp_app => rec.application_short_name,
318 resp_key =>rec.responsibility_key,
319 security_group => rec.security_group_key);
320 commit;
321 exception
322 when others then
323 log_message('User Responsibility Migration Failed at AMELIMUSER resp for user '
324 ||rec.user_name||'. Error: '||sqlerrm);
325 retcode := 1;
326 rollback;
327 end;
328 --+
329 end if;
330 --+
331 end loop;
332 --+
333 l_all_users_mig := true;
334 for rec in get_all_ame_users loop
335 l_all_users_mig := false;
336 exit;
337 END loop;
338 --+
339 if(l_all_users_mig = true) then
340 --end date all old responsibilities
341 for rec in get_old_responsibilities loop
342 fnd_responsibility_pkg.DELETE_ROW (
343 X_RESPONSIBILITY_ID => rec.responsibility_id
344 ,X_APPLICATION_ID => rec.application_id
345 );
346 end loop;
347 end if;
348 --+
349 log_message('User Responsibility Migration Completed Successfully at '||
350 to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
351 --+
352 if(retcode = 1) then
353 errbuf := 'Migration of users failed for some users. Check ame_exceptions_log for more details.';
354 else
355 errbuf := 'Migration successfully completed for all users.';
356 end if;
357 --+
358 end migrate_amea_users;
359 --+
360 procedure migrate_item_class_usages
361 (errbuf out nocopy varchar2
362 ,retcode out nocopy number
363 ) as
364 begin
365 --+
366 log_message('Item Class Usages Migration Started at '||
367 to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
368 --Set all par_mode for header from 'P' to 'S'
369 update ame_item_class_usages itu
370 set itu.item_class_par_mode = 'S'
371 where itu.item_class_par_mode = 'P'
372 and sysdate between itu.start_date
373 and nvl(itu.end_date - (1/86400), sysdate)
374 and exists (select null
375 from ame_item_classes itc
376 where itc.name = 'header'
377 and itc.item_class_id = itu.item_class_id
378 and sysdate between itc.start_date
379 and nvl(itc.end_date - (1/86400), sysdate)
380 );
381 --+
382 --+Correct all item_id_queries
383 --+
384 update ame_item_class_usages itu
385 set itu.item_id_query = 'select :transactionId from dual'
386 where itu.item_id_query = 'select :transaction_id from dual'
387 and sysdate between itu.start_date
388 and nvl(itu.end_date - (1/86400), sysdate)
389 and exists (select null
390 from ame_item_classes itc
391 where itc.name = 'header'
392 and itc.item_class_id = itu.item_class_id
393 and sysdate between itc.start_date
394 and nvl(itc.end_date - (1/86400), sysdate)
395 );
396 --+
397 errbuf := 'Migration of item class usages successful';
398 retcode := 0;
399 --+
400 log_message('Item Class Usages Migration Completed successfully at '||
401 to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
402 exception
403 when others then
404 errbuf := 'Migration of item class usages failed.';
405 retcode := 1;
406 end migrate_item_class_usages;
407 --+
408 procedure migrate_all
409 (errbuf out nocopy varchar2
410 ,retcode out nocopy number
411 ) as
412 l_prog_appl varchar2(100);
413 l_prog varchar2(100);
414 l_request_id number;
415 --+
416 cursor get_program_application is
417 select application_short_name
418 from fnd_application
419 where application_id = fnd_global.prog_appl_id;
420 --+
421 cursor get_program is
422 select concurrent_program_name
423 from fnd_concurrent_programs
424 where concurrent_program_id = fnd_global.conc_program_id;
425 --+
426 begin
427 --+
428 log_message('All Migration Started at '||
429 to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
430 --+
431 open get_program_application;
432 fetch get_program_application into l_prog_appl;
433 --+
434 if(get_program_application%NOTFOUND) then
435 close get_program_application;
436 errbuf := 'Failed. Could not find concurrent program application.';
437 retcode := 1;
438 return;
439 end if;
440 close get_program_application;
441 --+
442 open get_program;
443 fetch get_program into l_prog;
444 --+
445 if(get_program%NOTFOUND) then
446 close get_program;
447 errbuf := 'Failed. Could not find concurrent program.';
448 retcode := 1;
449 return;
450 end if;
451 close get_program;
452 --+
453 errbuf := errbuf || ' application ' || l_prog_appl ||
454 ' program ' || l_prog;
455 --+
456 l_request_id := fnd_request.submit_request (
457 application => l_prog_appl,
458 program => l_prog,
459 argument1 => 'Migrate Users'
460 );
461 --+
462 errbuf := errbuf || ' Migrate users Request Id: '||l_request_id;
463 --+
464 l_request_id := fnd_request.submit_request (
465 application => l_prog_appl,
466 program => l_prog,
467 argument1 => 'Migrate Item Class Usages'
468 );
469 --+
470 errbuf := errbuf || ' Migrate item class usages Request Id: '||l_request_id;
471 --+
472 retcode := 0;
473 --+
474 log_message('All Migration completed at '||
475 to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
476 --+
477 end migrate_all;
478 --+
479 procedure migrate_to_ameb
480 (errbuf out nocopy varchar2
481 ,retcode out nocopy number
482 ,migration_type in varchar2
483 ) as
484 begin
485 if(migration_type = 'Migrate Users') then
486 migrate_amea_users(errbuf => errbuf
487 ,retcode => retcode
488 );
489 elsif (migration_type = 'Migrate Item Class Usages') then
490 migrate_item_class_usages(errbuf => errbuf
491 ,retcode => retcode
492 );
493 elsif (migration_type = 'Migrate All') then
494 migrate_all(errbuf => errbuf
495 ,retcode => retcode
496 );
497 else
498 --+
499 log_message('Invalid parameter to concurrent program '||
500 migration_type || ' ' ||
501 to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
502 errbuf := 'Invalid parameter to concurrent program.';
503 retcode := 1;
504 end if;
505 end migrate_to_ameb;
506 --+
507 end ame_migration_pkg;