[Home] [Help]
PACKAGE BODY: APPS.AME_UPDATE_USERNAME_PKG
Source
1 package BODY ame_update_username_pkg as
2 /* $Header: ameupdun.pkb 120.4 2006/12/26 13:23:42 avarri noship $ */
3
4 procedure log_message
5 (p_message varchar2
6 ,p_errorcode integer default -20002
7 ) as
8 l_log_id integer;
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_update_username_pkg','update_username','','',p_errorcode,to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')|| p_message);
18
19 end log_message;
20
21 procedure update_username
22 (itemtype in varchar2
23 ,itemkey in varchar2
24 ,actid in number
25 ,funcmode in varchar2
26 ,resultout in out nocopy varchar2
27 ) as
28
29 l_event_key varchar2(650);
30 l_old_name varchar2(320);
31 l_new_name varchar2(320);
32 l_delim_pos integer;
33 l_success boolean;
34 wf_yes varchar2(1);
35 wf_no varchar2(1);
36 admin_approver varchar2(320);
37 l_event wf_event_t;
38 begin
39 wf_yes := 'Y';
40 wf_no := 'N';
41 l_success := true;
42
43 if funcmode = 'RUN' then
44
45 log_message ('AME User Name Migration Process started at ' || to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
46
47 l_event_key := wf_engine.GetItemAttrText
48 (itemtype => itemtype
49 ,itemkey => itemkey
50 ,aname => 'AMEUPDUN_EVENT_KEY'
51 );
52 log_message ('Event Key Recieved is ' || l_event_key);
53
54 l_event := wf_engine.GetItemAttrEvent
55 (itemtype => itemtype
56 ,itemkey => itemkey
57 ,name => 'AMEUPDUN_EVENT_MSG'
58 );
59 log_message ('Event msg Recieved ');
60
61 l_new_name := l_event.getValueForParameter('USER_NAME');
62 l_old_name := l_event.getValueForParameter('OLD_USER_NAME');
63
64 log_message ('Old user name is ' || l_old_name);
65 log_message ('New user name is ' || l_new_name);
66 log_message ('Migrating variable_value field in ame_config_vars');
67 begin
68 update ame_config_vars acv
69 set acv.variable_value = l_new_name
70 where acv.variable_value = l_old_name
71 and acv.variable_name = 'adminApprover';
72 if sql%found then
73 log_message('Migrated ' || sql%rowcount || ' rows successfully');
74 else
75 log_message('No Rows Migrated');
76 end if;
77 exception
78 when others then
79 rollback;
80 log_message('Migration Failed');
81 log_message(sqlerrm,sqlcode);
82 l_success := false;
83 end;
84
85 log_message('Migrating parameter field in ame_approval_group_items');
86 begin
87 update ame_approval_group_items aagi
88 set aagi.parameter = l_new_name
89 where aagi.parameter = l_old_name
90 and aagi.parameter_name = 'wf_roles_name';
91 if sql%found then
92 log_message('Migrated ' || sql%rowcount || ' rows successfully');
93 else
94 log_message('No Rows Migrated');
95 end if;
96 exception
97 when others then
98 rollback;
99 log_message('Migration Failed');
100 log_message(sqlerrm,sqlcode);
101 l_success := false;
102 end;
103
104 log_message('Migrating parameter field in ame_approval_group_members');
105 begin
106 update ame_approval_group_members aagm
107 set aagm.parameter = l_new_name
108 where aagm.parameter = l_old_name
109 and aagm.parameter_name = 'wf_roles_name';
110 if sql%found then
111 log_message('Migrated ' || sql%rowcount || ' rows successfully');
112 else
113 log_message('No Rows Migrated');
114 end if;
115 exception
116 when others then
117 rollback;
118 log_message('Migration Failed');
119 log_message(sqlerrm,sqlcode);
120 l_success := false;
121 end;
122
123 log_message('Migrating parameter_two field in ame_conditions');
124 begin
125 update ame_conditions ac
126 set ac.parameter_two = l_new_name
127 where ac.parameter_two = l_old_name
128 and ac.attribute_id = 0;
129 if sql%found then
130 log_message('Migrated ' || sql%rowcount || ' rows successfully');
131 else
132 log_message('No Rows Migrated');
133 end if;
134 exception
135 when others then
136 rollback;
137 log_message('Migration Failed');
138 log_message(sqlerrm,sqlcode);
139 l_success := false;
140 end;
141
142 log_message('Migrating parameter,description fields in ame_actions');
143 begin
144 update ame_actions aa
145 set aa.parameter = l_new_name,
146 aa.description = replace(aa.description,l_old_name,l_new_name)
147 where aa.parameter = l_old_name
148 and exists (select aat.action_type_id
149 from ame_action_types aat
150 where aat.action_type_id = aa.action_type_id
151 and aat.name = 'substitution');
152 if sql%found then
153 log_message('Migrated ' || sql%rowcount || ' rows successfully');
154 else
155 log_message('No Rows Migrated');
156 end if;
157 exception
158 when others then
159 rollback;
160 log_message('Migration Failed');
161 log_message(sqlerrm,sqlcode);
162 l_success := false;
163 end;
164
165 log_message('Migrating name field in ame_temp_old_approver_lists');
166 begin
167 update ame_temp_old_approver_lists atoal
168 set atoal.name = l_new_name
169 where atoal.name = l_old_name;
170 if sql%found then
171 log_message('Migrated ' || sql%rowcount || ' rows successfully');
172 else
173 log_message('No Rows Migrated');
174 end if;
175 exception
176 when others then
177 rollback;
178 log_message('Migration Failed');
179 log_message(sqlerrm,sqlcode);
180 l_success := false;
181 end;
182
183 log_message('Migrating name field in ame_temp_insertions');
184 begin
185 update ame_temp_insertions ati
186 set ati.name = l_new_name
187 where ati.name = l_old_name;
188 if sql%found then
189 log_message('Migrated ' || sql%rowcount || ' rows successfully');
190 else
191 log_message('No Rows Migrated');
192 end if;
193 exception
194 when others then
195 rollback;
196 log_message('Migration Failed');
197 log_message(sqlerrm,sqlcode);
198 l_success := false;
199 end;
200
201 log_message('Migrating name field in ame_temp_deletions');
202 begin
203 update ame_temp_deletions atd
204 set atd.name = l_new_name
205 where atd.name = l_old_name;
206 if sql%found then
207 log_message('Migrated ' || sql%rowcount || ' rows successfully');
208 else
209 log_message('No Rows Migrated');
210 end if;
211 exception
212 when others then
213 rollback;
214 log_message('Migration Failed');
215 log_message(sqlerrm,sqlcode);
216 l_success := false;
217 end;
218
219 log_message('Migrating parameter field in ame_temp_insertions');
220 begin
221 update ame_temp_insertions ati
222 set ati.parameter = l_new_name ||
223 substrb(ati.parameter
224 ,instrb(ati.parameter
225 ,fnd_global.local_chr(11)
226 ,1
227 ,1)
228 ,(lengthb(ati.parameter) - instrb(ati.parameter
229 ,fnd_global.local_chr(11)
230 ,1
231 ,1) + 1))
232 where ati.order_type in ('before approver','after approver')
233 and instrb(ati.parameter,l_old_name,1,1) = 1;
234 if sql%found then
235 log_message('Migrated ' || sql%rowcount || ' rows successfully');
236 else
237 log_message('No Rows Migrated');
238 end if;
239 exception
240 when others then
241 rollback;
242 log_message('Migration Failed');
243 log_message(sqlerrm,sqlcode);
244 l_success := false;
245 end;
246
247 log_message('Migrating description field in ame_temp_insertions');
248 begin
249 update ame_temp_insertions ati
250 set ati.description = decode(order_type
251 ,'after_approver'
252 ,'Always put the new approver right after the following approver: ' || l_new_name
253 ,'Always put the new approver right before the following approver: ' || l_new_name)
254 where ati.order_type in ('before approver','after approver')
255 and exists (select aca.application_id
256 from ame_calling_apps aca
257 where aca.application_id = ati.application_id)
258 and instrb(ati.parameter,l_new_name,1,1) = 1
259 and exists (select wr.name
260 from wf_roles wr
261 where wr.name = l_new_name
262 and wr.orig_system = 'FND_USR');
263 if sql%found then
264 log_message('Migrated ' || sql%rowcount || ' rows successfully');
265 else
266 log_message('No Rows Migrated');
267 end if;
268 exception
269 when others then
270 rollback;
271 log_message('Migration Failed');
272 log_message(sqlerrm,sqlcode);
273 l_success := false;
274 end;
275
276 log_message('Migrating name field in ame_trans_approval_history');
277 begin
278 update ame_trans_approval_history atah
279 set atah.name = l_new_name
280 where atah.name = l_old_name;
281 if sql%found then
282 log_message('Migrated ' || sql%rowcount || ' rows successfully');
283 else
284 log_message('No Rows Migrated');
285 end if;
286 exception
287 when others then
288 rollback;
289 log_message('Migration Failed');
290 log_message(sqlerrm,sqlcode);
291 l_success := false;
292 end;
293 log_message('AME User Name Migration Process completed at ' || to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'));
294 end if;
295
296 begin
297 select variable_value
298 into admin_approver
299 from ame_config_vars
300 where sysdate between start_date and nvl(end_date - (1/86400),sysdate)
301 and variable_name = 'adminApprover'
302 and (application_id is null or application_id = 0)
303 and rownum < 2;
304 wf_engine.SetItemAttrText
305 (itemtype => itemtype
306 ,itemkey => itemkey
307 ,aname => 'AMEUPDUN_ADMIN_APPROVER'
308 ,avalue => admin_approver);
309 exception
310 when others then
311 admin_approver := 'SYSADMIN';
312 wf_engine.SetItemAttrText
313 (itemtype => itemtype
314 ,itemkey => itemkey
315 ,aname => 'AMEUPDUN_ADMIN_APPROVER'
316 ,avalue => admin_approver);
317 end;
318
319 if l_success then
320 resultout := wf_engine.eng_completed || ':' || wf_yes;
321 else
322 resultout := wf_engine.eng_completed || ':' || wf_no;
323 end if;
324
325 return;
326
327 exception
328 when others then
329 rollback;
330 begin
331 select variable_value
332 into admin_approver
333 from ame_config_vars
334 where sysdate between start_date and nvl(end_date - (1/86400),sysdate)
335 and variable_name = 'adminApprover'
336 and (application_id is null or application_id = 0)
337 and rownum < 2;
338 wf_engine.SetItemAttrText
339 (itemtype => itemtype
340 ,itemkey => itemkey
341 ,aname => 'AMEUPDUN_ADMIN_APPROVER'
342 ,avalue => admin_approver);
343 exception
344 when others then
345 admin_approver := 'SYSADMIN';
346 wf_engine.SetItemAttrText
347 (itemtype => itemtype
348 ,itemkey => itemkey
349 ,aname => 'AMEUPDUN_ADMIN_APPROVER'
350 ,avalue => admin_approver);
351 end;
352 log_message('Migration Failed Completely');
353 log_message(sqlerrm,sqlcode);
354 resultout := wf_engine.eng_completed || ':' || wf_no;
355 return;
356 end update_username;
357
358 end ame_update_username_pkg;