[Home] [Help]
PACKAGE BODY: APPS.AME_MULTI_TENANCY_PKG
Source
1 package body ame_multi_tenancy_pkg as
2 /* $Header: amemultncy.pkb 120.0.12010000.5 2009/02/06 14:43:52 prasashe noship $ */
3 --+
4 g_enterprise varchar2(100);
5 g_enterprise_id number; -- this need to be populated before the copy process starts
6 g_seed_appl_id number;
7 g_seed_txntype varchar2(50);
8 g_fnd_application_id number;
9 g_ent_appl_id number;
10 g_package_name varchar2(100) := 'ame_multi_tenancy_pkg';
11 g_enterprise_label varchar2(100);
12 g_last_updated_by number := 1;
13 --+
14 procedure enable_globalcontext is
15 l_proc_name varchar2(100);
16 begin
17 l_proc_name := 'enable_globalcontext';
18 hr_multi_tenancy_pkg.set_context('ENT');
19 exception
20 when others then
21 Fnd_file.put_line(FND_FILE.LOG,'Error in enabling global context:'||sqlerrm);
22 logMessage(l_proc_name,'unable to set the global context');
23 end enable_globalcontext;
24 --+
25 function isSeedUser(userId in number) return varchar2 is
26 l_proc_name varchar2(100);
27 begin
28 l_proc_name := 'isSeedUser';
29 if userId in (-1,1,120,121) then
30 return ame_util.booleanTrue;
31 else
32 return ame_util.booleanFalse;
33 end if;
34 end isSeedUser;
35 --+
36 function getSeedUser return number is
37 begin
38 return 1;
39 end getSeedUser;
40 --+
41 function isEntDataModified(p_creationDateIn in date, p_lastUpdateDateIn in date) return varchar2 is
42 begin
43 if p_creationDateIn = p_lastUpdateDateIn then
44 return ame_util.booleanFalse;
45 else
46 return ame_util.booleanTrue;
47 end if;
48 end isEntDataModified;
49 --+
50 procedure logMessage(methodNameIn in varchar2, errMsgIn in varchar2) as
51 l_proc_name varchar2(100);
52 begin
53 l_proc_name := 'logMessage';
54 ame_util.runtimeException(packageNameIn => g_package_name,
55 routineNameIn => methodNameIn,
56 exceptionNumberIn => '12345',
57 exceptionStringIn => errMsgIn);
58 end logMessage;
59 --+
60 procedure enable_orgcontext is
61 l_ret boolean;
62 l_proc_name varchar2(100);
63 begin
64 l_proc_name := 'enable_orgcontext';
65 hr_multi_tenancy_pkg.set_context(g_enterprise_label);
66 exception
67 when others then
68 Fnd_file.put_line(FND_FILE.LOG,'Error in enabling org context:'||sqlerrm);
69 logMessage(l_proc_name, 'unable to set the org conext for enterprise Id:');
70 end enable_orgcontext;
71 --+
72 procedure createTxnType(cpTxnTypeIn in varchar2) is
73 l_applicationId number;
74 l_ovn number;
75 l_start_date date;
76 l_end_Date date;
77 l_appl_name varchar2(240);
78 l_seed_apl_name varchar2(240);
79 l_count number;
80 l_application_id number;
81 l_proc_name varchar2(100);
82 cursor chkTxnType(c_txn_type_id in varchar2) is
83 select application_id
84 from ame_calling_apps
85 where transaction_type_id = c_txn_type_id
86 and sysdate between start_date and nvl(end_Date,sysdate);
87 --
88 cursor chkTxnCust(c_txn_type_id in varchar2) is
89 select count(*)
90 from ame_calling_apps
91 where transaction_type_id = c_txn_type_id
92 -- and isSeedUser(last_updated_by) = ame_util.booleanFalse;
93 and isEntDataModified(creation_date,last_update_date) = ame_util.booleanTrue;
94 --
95 cursor getTxnUpdDets(l_seed_apl_name in varchar2) is
96 select ent.object_version_number
97 from ame_calling_Apps ent
98 where ent.application_id = g_ent_appl_id
99 and (ent.fnd_application_id <> g_fnd_application_id
100 or ent.application_name <> l_seed_apl_name)
101 and sysdate between ent.start_date and nvl(ent.end_Date,sysdate)
102 -- and isSeedUser(ent.last_updated_by) = ame_util.booleanTrue
103 and isEntDataModified(ent.creation_date,ent.last_update_date) = ame_util.booleanFalse;
104 begin
105 l_proc_name := 'createTxnType';
106 l_seed_apl_name := g_seed_call_apps(1).application_name;
107 open chkTxnType(cpTxnTypeIn);
108 fetch chkTxnType into l_application_id;
109 close chkTxnType;
110 if l_application_id is not null then
111 logMessage(l_proc_name,'Transaction Type is found');
112 Fnd_file.put_line(FND_FILE.LOG,'Found existing transaction type for the enterprise');
113 Fnd_file.put_line(FND_FILE.LOG,'AME application id:'||l_application_id);
114 g_ent_appl_id := l_application_id;
115 l_appl_name := l_seed_apl_name || ':' || g_enterprise;
116 for upd_rec in getTxnUpdDets(l_appl_name) loop
117 l_ovn := upd_rec.object_version_number;
118 logMessage(l_proc_name,'Updating the transaction type with name : ' || l_seed_apl_name);
119 ame_trans_type_api. update_ame_transaction_type
120 (p_application_name => l_appl_name
121 ,p_application_id => l_application_id
122 ,p_object_version_number => l_ovn
123 ,p_start_date => l_start_date
124 ,p_end_date => l_end_Date
125 );
126 update ame_calling_apps_tl
127 set created_by = getSeedUser
128 ,last_updated_by = getSeedUser
129 where application_id = g_ent_appl_id
130 and language = userenv('LANG');
131 Fnd_file.put_line(FND_FILE.LOG,'Updated the existing transaction type');
132 end loop;
133 return;
134 end if;
135 open chkTxnCust(cpTxnTypeIn);
136 fetch chkTxnCust into l_count;
137 close chkTxnCust;
138 -- the txn has been deleted, do not create
139 if l_count > 0 then
140 Fnd_file.put_line(FND_FILE.LOG,'The existing transaction type has been deleted');
141 return;
142 end if;
143 l_appl_name := l_seed_apl_name || ':' || g_enterprise; -- check for buffer overflow
144 logMessage(l_proc_name,'Creating the transaction type with name : ' || l_appl_name);
145 logMessage(l_proc_name,'Creating the transaction type with g_fnd_application_id : ' || g_fnd_application_id);
146 logMessage(l_proc_name,'Creating the transaction type with cpTxnTypeIn : ' || cpTxnTypeIn);
147 --
148 ame_trans_type_api.create_ame_transaction_type
149 (p_application_name => l_appl_name
150 ,p_fnd_application_id => g_fnd_application_id
151 ,p_transaction_type_id => cpTxnTypeIn
152 ,p_application_id => l_applicationId
153 ,p_object_version_number => l_ovn
154 ,p_start_date => l_start_date
155 ,p_end_date => l_end_Date
156 );
157 g_ent_appl_id := l_applicationId;
158 update ame_calling_apps
159 set created_by = getSeedUser
160 ,last_updated_by = getSeedUser
161 where application_id = g_ent_appl_id
162 and sysdate between start_date and nvl(end_Date,sysdate);
163 --
164 update ame_calling_apps_tl
165 set created_by = getSeedUser
166 ,last_updated_by = getSeedUser
167 where application_id = g_ent_appl_id;
168 --
169 update ame_item_class_usages
170 set created_by = getSeedUser
171 ,last_updated_by = getSeedUser
172 where application_id = g_ent_appl_id
173 and item_class_id =
174 (select item_class_id
175 from ame_item_classes
176 where name = ame_util.headerItemClassName
177 and sysdate between start_date and nvl(end_date,sysdate))
178 and sysdate between start_date and nvl(end_Date,sysdate);
179 --
180 update ame_attribute_usages
181 set created_by = getSeedUser
182 ,last_updated_by = getSeedUser
183 where application_id = g_ent_appl_id
184 and sysdate between start_date and nvl(end_Date,sysdate);
185 Fnd_file.put_line(FND_FILE.LOG,'Transaction Type has been created with AME application Id:'||g_ent_appl_id);
186 exception
187 when others then
188 Fnd_file.put_line(FND_FILE.LOG,'Error in createTxnType:'||sqlerrm);
189 logMessage(l_proc_name,sqlerrm);
190 raise;
191 end createTxnType;
192 --+
193 procedure updateConfigVars is
194 l_count number;
195 l_config_varname varchar2(50);
196 l_ovn number;
197 l_start_Date date;
198 l_end_Date date;
199 l_proc_name varchar2(100);
200 --
201 cursor checkConfigExists(c_var_name in varchar2) is
202 select count(*)
203 from ame_config_vars
204 where application_id = g_ent_appl_id
205 and variable_name = c_var_name
206 and sysdate between start_date and nvl(end_Date,sysdate);
207 --
208 cursor getEnterpriseConfigRow(c_var_name in varchar2, c_var_value varchar2) is
209 select ent.object_version_number
210 ,ent.variable_value
211 ,ent.creation_date
212 ,ent.last_update_date
213 from ame_config_vars ent
214 where ent.application_id = g_ent_appl_id
215 and ent.variable_name = c_var_name
216 and ent.variable_value <> c_var_value
217 and sysdate between ent.start_Date and nvl(ent.end_Date,sysdate)
218 and isEntDataModified(ent.creation_date,ent.last_update_date) = ame_util.booleanFalse;
219 --
220 begin
221 l_proc_name := 'updateConfigVars';
222 Fnd_file.put_line(FND_FILE.LOG,'Updating the configuration variable usage for the transaction type');
223 for i in 1..g_seed_config_usg.count loop
224 logMessage(l_proc_name,'Setting up Config Var : ' || g_seed_config_usg(i).variable_name);
225 open checkConfigExists(g_seed_config_usg(i).variable_name);
226 fetch checkConfigExists into l_count;
227 close checkConfigExists;
228 --
229 if l_count = 0 then
230 logMessage(l_proc_name,'The Config Var is not found');
231 l_ovn := null;
232 l_config_varname := g_seed_config_usg(i).variable_name;
233 Fnd_file.put_line(FND_FILE.LOG,'Creating the configuration variable usage for:'||
234 g_seed_config_usg(i).variable_name);
235 ame_config_var_api.update_ame_config_variable
236 (p_application_id => g_ent_appl_id
237 ,p_variable_name => l_config_varname
238 ,p_variable_value => g_seed_config_usg(i).variable_value
239 ,p_object_version_number => l_ovn
240 ,p_start_date => l_start_date
241 ,p_end_date => l_end_Date
242 );
243 update ame_config_vars
244 set created_by = getSeedUser
245 ,last_updated_by = getSeedUser
246 where application_id = g_ent_appl_id
247 and variable_name = g_seed_config_usg(i).variable_name
248 and sysdate between start_date and nvl(end_Date,sysdate);
249 else
250 logMessage(l_proc_name,'The Config Var is found');
251 for upd_rec in getEnterpriseConfigRow(c_var_name => g_seed_config_usg(i).variable_name
252 ,c_var_value =>g_seed_config_usg(i).variable_value) loop
253 if isSeedUser(g_seed_config_usg(i).last_updated_by) = ame_util.booleanTrue then
254 l_ovn := upd_rec.object_version_number;
255 logMessage(l_proc_name,'The Config Var is updated');
256 Fnd_file.put_line(FND_FILE.LOG,'Updating the configuration variable usage:'||
257 g_seed_config_usg(i).variable_name);
258 ame_config_var_api.update_ame_config_variable
259 (p_application_id => g_ent_appl_id
260 ,p_variable_name => g_seed_config_usg(i).variable_name
261 ,p_variable_value => g_seed_config_usg(i).variable_value
262 ,p_object_version_number => l_ovn
263 ,p_start_date => l_start_date
264 ,p_end_date => l_end_Date
265 );
266 update ame_config_vars
267 set created_by = getSeedUser
268 ,last_updated_by = getSeedUser
269 where application_id = g_ent_appl_id
270 and variable_name = g_seed_config_usg(i).variable_name
271 and sysdate between start_date and nvl(end_Date,sysdate);
272 end if;
273 end loop;
274 end if;
275 end loop;
276 exception
277 when others then
278 Fnd_file.put_line(FND_FILE.LOG,'Error updateConfigVars:'||sqlerrm);
279 logMessage(l_proc_name,l_config_varname||':'||sqlerrm);
280 raise;
281 end updateConfigVars;
282 --+
283 procedure updateItemClassUsage is
284 l_item_class_id number;
285 l_count number;
286 l_start_date date;
287 l_end_date date;
288 l_ovn number;
289 l_application_id number;
290 l_proc_name varchar2(100);
291 --
292 cursor chkExists(c_item_class_id in number) is
293 select count(*)
294 from ame_item_class_usages
295 where item_class_id = c_item_class_id
296 and application_id = g_ent_appl_id
297 and sysdate between start_date and nvl(end_date,sysdate);
298 --
299 cursor getUpdDetails(c_item_class_id in number) is
300 select ent.object_version_number
301 ,seed.item_id_query
302 ,seed.item_class_order_number
303 ,seed.item_class_par_mode
304 ,seed.item_class_sublist_mode
305 from ame_item_class_usages ent
306 ,ame_item_class_usages seed
307 where ent.item_class_id = c_item_class_id
308 and seed.item_class_id = c_item_class_id
309 and seed.application_id = g_seed_appl_id
310 and ent.application_id = g_ent_appl_id
311 and (ent.item_id_query <> seed.item_id_query or
312 ent.item_class_order_number <> seed.item_class_order_number or
313 ent.item_class_par_mode <> seed.item_class_par_mode or
314 ent.item_class_sublist_mode <> seed.item_class_sublist_mode)
315 and sysdate between ent.start_date and nvl(ent.end_Date,sysdate)
316 and sysdate between seed.start_date and nvl(seed.end_date,sysdate)
317 and isEntDataModified(ent.creation_date,ent.last_update_date) = ame_util.booleanFalse;
318 --
319 begin
320 l_proc_name := 'updateItemClassUsage';
321 Fnd_file.put_line(FND_FILE.LOG,'Updating the Item class usage for the transaction type');
322 for i in 1..g_seed_ic_usg.count loop
323 l_item_class_id := g_seed_ic_usg(i).item_class_id;
324 logMessage(l_proc_name,'Setting up the item class usage for : ' || l_item_class_id);
325 open chkExists(l_item_class_id);
326 fetch chkExists into l_count;
327 close chkExists;
328 --
329 if l_count > 0 then
330 for upd_rec in getUpdDetails(l_item_class_id) loop
331 if isSeedUser(g_seed_ic_usg(i).last_updated_by) = ame_util.booleanTrue then
332 l_ovn := upd_rec.object_version_number;
333 logMessage(l_proc_name,'Updating the item class usage for : ' || l_item_class_id);
334 Fnd_file.put_line(FND_FILE.LOG,'Updating the Item class id: '||l_item_class_id||' :usage');
335 ame_item_class_api.update_ame_item_class_usage
336 (p_application_id => g_ent_appl_id
337 ,p_item_class_id => l_item_class_id
338 ,p_item_id_query => g_seed_ic_usg(i).item_id_query
339 ,p_item_class_order_number => g_seed_ic_usg(i).item_class_order_number
340 ,p_item_class_par_mode => g_seed_ic_usg(i).item_class_par_mode
341 ,p_item_class_sublist_mode => g_seed_ic_usg(i).item_class_sublist_mode
342 ,p_object_version_number => l_ovn
343 ,p_start_date => l_start_date
344 ,p_end_date => l_end_date
345 );
346 update ame_item_class_usages
347 set created_by = getSeedUser
348 ,last_updated_by = getSeedUser
349 where application_id = g_ent_appl_id
350 and item_class_id = l_item_class_id
351 and sysdate between start_date and nvl(end_Date,sysdate);
352 end if;
353 end loop;
354 else
355 l_application_id := g_ent_appl_id;
356 logMessage(l_proc_name,'Creating the item class usage for : ' || l_item_class_id);
357 Fnd_file.put_line(FND_FILE.LOG,'Creating the Item class id: '||l_item_class_id||' :usage');
358 ame_item_class_api.create_ame_item_class_usage
359 (p_item_id_query => g_seed_ic_usg(i).item_id_query
360 ,p_item_class_order_number => g_seed_ic_usg(i).item_class_order_number
361 ,p_item_class_par_mode => g_seed_ic_usg(i).item_class_par_mode
362 ,p_item_class_sublist_mode => g_seed_ic_usg(i).item_class_sublist_mode
363 ,p_application_id => l_application_id
364 ,p_item_class_id => l_item_class_id
365 ,p_object_version_number => l_ovn
366 ,p_start_date => l_start_date
367 ,p_end_date => l_end_date
368 );
369 update ame_item_class_usages
370 set created_by = getSeedUser
371 ,last_updated_by = getSeedUser
372 where application_id = g_ent_appl_id
373 and item_class_id = l_item_class_id
374 and sysdate between start_date and nvl(end_Date,sysdate);
375 end if;
376 end loop;
377 exception
378 when others then
379 Fnd_file.put_line(FND_FILE.LOG,'Error in updateItemClassUsage:'||sqlerrm);
380 logMessage(l_proc_name,l_item_class_id||':'||sqlerrm);
381 raise;
382 end updateItemClassUsage;
383 --+
384 procedure updateMandAtrUsages is
385 l_attribute_name varchar2(50);
386 l_ovn number;
387 l_start_Date date;
388 l_end_Date date;
389 l_proc_name varchar2(100);
390 --
391 cursor getEnterpriseMandAttrUsageRow(c_attr_id number,c_is_static varchar2,c_query_string varchar2,c_value_set_id in number) is
392 select *
393 from ame_attribute_usages
394 where attribute_id = c_attr_id
395 and (is_static <> c_is_static or nvl(query_string,'PRASAD') <> nvl(c_query_string,'PRASAD') or
396 nvl(value_set_id,-1) <> NVL(c_value_set_id,-1))
397 and application_id = g_ent_appl_id
398 and sysdate between start_date and nvl(end_Date,sysdate)
399 and isEntDataModified(creation_date,last_update_date) = ame_util.booleanFalse;
400 --
401 begin
402 l_proc_name := 'updateMandAtrUsages';
403 logMessage(l_proc_name,'Updating the manadatory attribute usage for transaction type');
404 for i in 1..g_seed_mand_attr_usg.count loop
405 logMessage(l_proc_name,'Setting up the attribute : ' || g_seed_mand_attr_usg(i).attribute_id);
406 for ent_rec in getEnterpriseMandAttrUsageRow(g_seed_mand_attr_usg(i).attribute_id,
407 g_seed_mand_attr_usg(i).is_static,
408 g_seed_mand_attr_usg(i).query_string,
409 g_seed_mand_attr_usg(i).value_set_id) loop
410 l_ovn := ent_rec.object_version_number;
411 logMessage(l_proc_name,'Updating the attribute : ' || g_seed_mand_attr_usg(i).attribute_id);
412 logMessage(l_proc_name,'Updating the manadatory attribute usage for :'||g_seed_mand_attr_usg(i).attribute_id);
413 ame_attribute_api.update_ame_attribute_usage
414 (p_attribute_id => g_seed_mand_attr_usg(i).attribute_id
415 ,p_application_id => g_ent_appl_id
416 ,p_is_static => g_seed_mand_attr_usg(i).is_static
417 ,p_query_string => g_seed_mand_attr_usg(i).query_string
418 ,p_value_set_id => g_seed_mand_attr_usg(i).value_set_id
419 ,p_object_version_number => l_ovn
420 ,p_start_date => l_start_Date
421 ,p_end_date => l_end_Date
422 );
423 --
424 update ame_attribute_usages
425 set created_by = getSeedUser
426 ,last_updated_by = getSeedUser
427 where application_id = g_ent_appl_id
428 and attribute_id = g_seed_mand_attr_usg(i).attribute_id
429 and sysdate between start_date and nvl(end_Date,sysdate);
430 --
431 end loop;
432 end loop;
433 exception
434 when others then
435 Fnd_file.put_line(FND_FILE.LOG,'Error in updateMandAtrUsages:'||sqlerrm);
436 logMessage(l_proc_name,l_attribute_name||':'||sqlerrm);
437 raise;
438 end updateMandAtrUsages;
439 --+
440 procedure createAttrUsage is
441 l_proc_name varchar2(100);
442 l_attribute_name varchar2(50);
443 l_ovn number;
444 l_start_date date;
445 l_end_Date date;
446 l_count number;
447 l_attribute_id number;
448 --
449 cursor checkUsageExists(c_atr_id in number) is
450 select count(*)
451 from ame_attribute_usages
452 where application_id = g_ent_appl_id
453 and attribute_id = c_atr_id
454 and sysdate between start_date and nvl(end_Date,sysdate);
455 --
456 cursor getEnterpriseAtteUsageRow(c_atr_id in number,c_is_static varchar2,
457 c_query_string varchar2,c_value_set_id in number) is
458 select ent.object_version_number
459 from ame_attribute_usages ent
460 where ent.attribute_id = c_atr_id
461 and ent.application_id = g_ent_appl_id
462 and (ent.is_static <> c_is_static or nvl(ent.query_string,'PRASAD') <> nvl(c_query_string,'PRASAD'))
463 and sysdate between ent.start_date and nvl(ent.end_Date,sysdate)
464 and isEntDataModified(ent.creation_date,ent.last_update_date) = ame_util.booleanFalse;
465 --
466 begin
467 l_proc_name := 'createAttrUsage';
468 updateMandAtrUsages;
469 Fnd_file.put_line(FND_FILE.LOG,'Updating the attribute usage for the transaction type');
470 for i in 1..g_seed_attr_usg.count loop
471 l_attribute_id := g_seed_attr_usg(i).attribute_id;
472 logMessage(l_proc_name,'Setting up the attribute : ' || l_attribute_id);
473 l_count := 0;
474 open checkUsageExists(l_attribute_id);
475 fetch checkUsageExists into l_count;
476 close checkUsageExists;
477 if l_count > 0 then
478 for upd_rec in getEnterpriseAtteUsageRow(l_attribute_id,g_seed_attr_usg(i).is_static
479 ,g_seed_attr_usg(i).query_string
480 ,g_seed_attr_usg(i).value_set_id) loop
481 if isSeedUser(g_seed_attr_usg(i).last_updated_by) = ame_util.booleanTrue then
482 logMessage(l_proc_name,'Updating the attribute usage for : ' || l_attribute_id);
483 Fnd_file.put_line(FND_FILE.LOG,'Updating the usage for the attribute:'||l_attribute_id);
484 l_ovn := upd_rec.object_version_number;
485 ame_attribute_api.update_ame_attribute_usage
486 (p_attribute_id => l_attribute_id
487 ,p_application_id => g_ent_appl_id
488 ,p_is_static => g_seed_attr_usg(i).is_static
489 ,p_query_string => g_seed_attr_usg(i).query_string
490 ,p_value_set_id => g_seed_attr_usg(i).value_set_id
491 ,p_object_version_number => l_ovn
492 ,p_start_date => l_start_date
493 ,p_end_date => l_end_Date
494 );
495 --
496 update ame_attribute_usages
497 set created_by = getSeedUser
498 ,last_updated_by = getSeedUser
499 where application_id = g_ent_appl_id
500 and attribute_id = l_attribute_id
501 and sysdate between start_date and nvl(end_Date,sysdate);
502 --
503 end if;
504 end loop;
505 else
506 logMessage(l_proc_name,'Creating the attribute usage for : ' || l_attribute_id);
507 Fnd_file.put_line(FND_FILE.LOG,'Creating the usage for the attribute:'||l_attribute_id);
508 ame_attribute_api.create_ame_attribute_usage
509 (p_attribute_id => l_attribute_id
510 ,p_application_id => g_ent_appl_id
511 ,p_is_static => g_seed_attr_usg(i).is_static
512 ,p_query_string => g_seed_attr_usg(i).query_string
513 ,p_user_editable => g_seed_attr_usg(i).user_editable
514 ,p_value_set_id => g_seed_attr_usg(i).value_set_id
515 ,p_object_version_number => l_ovn
516 ,p_start_date => l_start_date
517 ,p_end_date => l_end_Date
518 );
519 --
520 update ame_attribute_usages
521 set created_by = getSeedUser
522 ,last_updated_by = getSeedUser
523 where application_id = g_ent_appl_id
524 and attribute_id = l_attribute_id
525 and sysdate between start_date and nvl(end_Date,sysdate);
526 end if;
527 end loop;
528 --
529 exception
530 when others then
531 Fnd_file.put_line(FND_FILE.LOG,'Error in createAttrUsage:'||sqlerrm);
532 logMessage(l_proc_name,l_attribute_id||':'||sqlerrm);
533 raise;
534 end createAttrUsage;
535 --+
536 procedure updateActionTypeConfig is
537 l_proc_name varchar2(100);
538 l_count number;
539 l_action_type_id number;
540 l_ovn number;
541 l_start_date date;
542 l_end_date date;
543
544 cursor chkActionTypeUsg(c_action_type_id in number) is
545 select count(*)
546 from ame_action_Type_config
547 where sysdate between start_date and nvl(end_Date,sysdate)
548 and application_id = g_ent_appl_id
549 and action_type_id = c_action_type_id;
550
551 cursor getActTypeUpd(c_act_type_id in number, c_voting_regime in varchar2
552 ,c_chain_ordering_mode in varchar2, c_order_number in number) is
553 select ent.object_version_number
554 from ame_action_type_config ent
555 where ent.application_id = g_ent_appl_id
556 and c_act_type_id = ent.action_type_id
557 and (nvl(ent.voting_regime,'AA') <> nvl(c_voting_regime,'AA')
558 or nvl(ent.chain_ordering_mode,'AA') <> nvl(c_chain_ordering_mode,'AA')
559 or nvl(ent.order_number,-1) <> nvl(c_order_number,-1))
560 and sysdate between ent.start_Date and nvl(ent.end_Date,sysdate)
561 and isEntDataModified(ent.creation_date,ent.last_update_date) = ame_util.booleanFalse ;
562 begin
563 l_proc_name := 'updateActionTypeConfig';
564 Fnd_file.put_line(FND_FILE.LOG,'Updating the action type configuration for the transaction type');
565 for i in 1..g_seed_act_config.count loop
566 l_action_type_id := g_seed_act_config(i).action_type_id;
567 logMessage(l_proc_name,'Setting up the action type config: ' || l_action_type_id);
568 open chkActionTypeUsg(l_action_type_id);
569 fetch chkActionTypeUsg into l_count;
570 close chkActionTypeUsg;
571 if l_count > 0 then
572 for upd_rec in getActTypeUpd(l_action_type_id
573 ,g_seed_act_config(i).voting_regime
574 ,g_seed_act_config(i).chain_ordering_mode
575 ,g_seed_act_config(i).order_number) loop
576 --call update methods
577 if isSeedUser(g_seed_act_config(i).last_updated_by) = ame_util.booleanTrue then
578 Fnd_file.put_line(FND_FILE.LOG,'Updating the action type configuration for:'||l_action_type_id);
579 logMessage(l_proc_name,'Updating up the action type config: ' || l_action_type_id);
580 l_ovn := upd_rec.object_version_number;
581 ame_action_api.update_ame_action_type_conf
582 (p_action_type_id => l_action_type_id,
583 p_application_id => g_ent_appl_id,
584 p_voting_regime => g_seed_act_config(i).voting_regime,
585 p_chain_ordering_mode => g_seed_act_config(i).chain_ordering_mode,
586 p_order_number => g_seed_act_config(i).order_number,
587 p_object_version_number => l_ovn,
588 p_start_date => l_start_date,
589 p_end_date => l_end_Date
590 );
591 update ame_action_type_config
592 set created_by = getSeedUser
593 ,last_updated_by = getSeedUser
594 where application_id = g_ent_appl_id
595 and action_type_id = l_action_type_id
596 and sysdate between start_date and nvl(end_Date,sysdate);
597 end if;
598 end loop;
599 else
600 logMessage(l_proc_name,'Creating up the action type config: ' || l_action_type_id);
601 Fnd_file.put_line(FND_FILE.LOG,'Creating the action type configuration for:'||l_action_type_id);
602 ame_action_api.create_ame_action_type_conf
603 (p_action_type_id => l_action_type_id
604 ,p_application_id => g_ent_appl_id
605 ,p_voting_regime => g_seed_act_config(i).voting_regime
606 ,p_chain_ordering_mode => g_seed_act_config(i).chain_ordering_mode
607 ,p_order_number => g_seed_act_config(i).order_number
608 ,p_object_version_number => l_ovn
609 ,p_start_date => l_start_date
610 ,p_end_date => l_end_date
611 );
612 update ame_action_type_config
613 set created_by = getSeedUser
614 ,last_updated_by = getSeedUser
615 where application_id = g_ent_appl_id
616 and action_type_id = l_action_type_id
617 and sysdate between start_date and nvl(end_Date,sysdate);
618 end if;
619 end loop;
620 exception
621 when others then
622 Fnd_file.put_line(FND_FILE.LOG,'Error in updateActionTypeConfig:'||sqlerrm);
623 logMessage(l_proc_name, sqlerrm);
624 raise;
625 end updateActionTypeConfig;
626 --+
627 procedure createAprGrpConfig(i in number,p_ent_grp_id in number) is
628 l_proc_name varchar2(100);
629 l_ovn number;
630 l_count number;
631 l_start_date date;
632 l_end_date date;
633 cursor chkGrpConfig(c_ent_grp_id in number) is
634 select count(*)
635 from ame_approval_group_config
636 where application_id = g_ent_appl_id
637 and approval_Group_id = c_ent_grp_id
638 and sysdate between start_date and nvl(end_Date,sysdate);
639 --
640 cursor getConfigUpd(c_ent_grp_id in number,c_voting_regime in varchar2,
641 c_order_number number) is
642 select ent.object_version_number
643 from ame_approval_group_config ent
644 where ent.application_id = g_ent_appl_id
645 and ent.approval_Group_id = c_ent_grp_id
646 and (c_voting_regime <> ent.voting_regime
647 or c_order_number <> ent.order_number)
648 and sysdate between ent.start_date and nvl(ent.end_Date,sysdate)
649 and isEntDataModified(ent.creation_date,ent.last_update_date) = ame_util.booleanFalse;
650 begin
651 l_proc_name := 'createAprGrpConfig';
652 logMessage(l_proc_name,'Setting up the Group Config for : ' ||g_seed_group_data(i).approval_group_id );
653 Fnd_file.put_line(FND_FILE.LOG,'Updating group configuration for:'||g_seed_group_data(i).name);
654 open chkGrpConfig(p_ent_grp_id);
655 fetch chkGrpConfig into l_count;
656 close chkGrpConfig;
657 if l_count > 0 then
658 for upd_Rec in getConfigUpd(g_seed_group_data(i).approval_group_id,g_seed_group_data(i).voting_regime,
659 g_seed_group_data(i).order_number) loop
660 if isSeedUser(g_seed_group_data(i).config_last_updated_by) = ame_util.booleanTrue then
661 l_ovn := upd_Rec.object_version_number;
662 logMessage(l_proc_name,'Updating the approval group config : ' || g_seed_group_data(i).name);
663 Fnd_file.put_line(FND_FILE.LOG,'Updating existing group configuration for:'||g_seed_group_data(i).name);
664 ame_approver_group_api.update_approver_group_config
665 (
666 p_approval_group_id => p_ent_grp_id
667 ,p_application_id => g_ent_appl_id
668 ,p_voting_regime => g_seed_group_data(i).voting_regime
669 ,p_order_number => g_seed_group_data(i).order_number
670 ,p_object_version_number => l_ovn
671 ,p_start_date => l_start_date
672 ,p_end_date => l_end_date
673 );
674 update ame_approval_group_config
675 set created_by = getSeedUser
676 ,last_updated_by = getSeedUser
677 where application_id = g_ent_appl_id
678 and approval_group_id = p_ent_grp_id
679 and sysdate between start_date and nvl(end_Date,sysdate);
680 end if;
681 end loop;
682 else
683 logMessage(l_proc_name,'Creating the approval group config : ' || g_seed_group_data(i).name);
684 Fnd_file.put_line(FND_FILE.LOG,'Creating group configuration for:'||g_seed_group_data(i).name);
685 ame_approver_group_api.create_approver_group_config
686 (
687 p_approval_group_id => p_ent_grp_id
688 ,p_application_id => g_ent_appl_id
689 ,p_voting_regime => g_seed_group_data(i).voting_regime
690 ,p_order_number => g_seed_group_data(i).order_number
691 ,p_object_version_number => l_ovn
692 ,p_start_date => l_start_date
693 ,p_end_date => l_end_date
694 );
695 update ame_approval_group_config
696 set created_by = getSeedUser
697 ,last_updated_by = getSeedUser
698 where application_id = g_ent_appl_id
699 and approval_group_id = p_ent_grp_id
700 and sysdate between start_date and nvl(end_Date,sysdate);
701 end if;
702 --+
703 exception
704 when others then
705 Fnd_file.put_line(FND_FILE.LOG,'Error in createAprGrpConfig:'||sqlerrm);
706 logMessage(l_proc_name, p_ent_grp_id||':'||sqlerrm);
707 raise;
708 end createAprGrpConfig;
709 --+
710 procedure createAprGrp(groupIndex in number) is
711 l_proc_name varchar2(100);
712 l_current_grp_id number := null;
713 l_start_date date;
714 l_end_Date date;
715 l_ovn number;
716 --+
717 cursor chkGrp(c_name_in in varchar2) is
718 select approval_Group_id
719 from ame_approval_Groups
720 where name = c_name_in
721 and sysdate between start_date and nvl(end_Date,sysdate);
722 --+
723 cursor getGrpUpdate(c_current_grp_id in number,c_query_string in varchar2) is
724 select ent.object_version_number
725 from ame_approval_groups ent
726 where ent.approval_group_id = c_current_grp_id
727 and c_query_string <> ent.query_string
728 and sysdate between ent.start_date and nvl(ent.end_Date,sysdate)
729 and isEntDataModified(ent.creation_date,ent.last_update_date) = ame_util.booleanFalse;
730 begin
731 l_proc_name := 'createAprGrp';
732 logMessage(l_proc_name,'Setting up the approval group : ' || g_seed_group_data(groupIndex).name);
733 Fnd_file.put_line(FND_FILE.LOG,'Creating the transaction type group usage:'||g_seed_group_data(groupIndex).name);
734 open chkGrp(g_seed_group_data(groupIndex).name);
735 fetch chkGrp into l_current_grp_id;
736 close chkGrp;
737 if l_current_grp_id is null then
738 logMessage(l_proc_name,'Creating the approval group : ' || g_seed_group_data(groupIndex).name);
739 Fnd_file.put_line(FND_FILE.LOG,'Creating the approval group:'||g_seed_group_data(groupIndex).name);
740 ame_approver_group_api.create_ame_approver_group
741 (p_name => g_seed_group_data(groupIndex).name
742 --,p_description => g_enterprise||':'||g_seed_group_data(groupIndex).description
743 ,p_description => g_seed_group_data(groupIndex).description
744 ,p_is_static => g_seed_group_data(groupIndex).is_static
745 ,p_query_string => g_seed_group_data(groupIndex).query_string
746 ,p_approval_group_id => l_current_grp_id
747 ,p_start_date => l_start_date
748 ,p_end_date => l_end_Date
749 ,p_object_version_number => l_ovn
750 );
751 update ame_approval_groups
752 set created_by = getSeedUser
753 ,last_updated_by = getSeedUser
754 where approval_group_id = l_current_grp_id
755 and sysdate between start_date and nvl(end_Date,sysdate);
756 --
757 update ame_approval_groups_tl
758 set created_by = getSeedUser
759 ,last_updated_by = getSeedUser
760 where approval_group_id = l_current_grp_id;
761 --
762 createAprGrpConfig(groupIndex,l_current_grp_id);
763 else
764 createAprGrpConfig(groupIndex,l_current_grp_id);
765 for upd_rec in getGrpUpdate(c_current_grp_id => l_current_grp_id
766 ,c_query_string => g_seed_group_data(groupIndex).query_string) loop
767 if isSeedUser(g_seed_group_data(groupIndex).group_last_updated_by) = ame_util.booleanTrue then
768 l_ovn := upd_rec.object_version_number;
769 logMessage(l_proc_name,'Updating the approval group : ' || g_seed_group_data(groupIndex).name);
770 Fnd_file.put_line(FND_FILE.LOG,'Updating the existing approval group:'||g_seed_group_data(groupIndex).name);
771 ame_approver_group_api.update_ame_approver_group
772 (p_approval_group_id => l_current_grp_id
773 ,p_is_static => g_seed_group_data(groupIndex).is_static
774 ,p_query_string => g_seed_group_data(groupIndex).query_string
775 ,p_object_version_number => l_ovn
776 ,p_start_date => l_start_date
777 ,p_end_date => l_end_date
778 );
779 update ame_approval_groups
780 set created_by = getSeedUser
781 ,last_updated_by = getSeedUser
782 where approval_group_id = l_current_grp_id
783 and sysdate between start_date and nvl(end_Date,sysdate);
784 --
785 update ame_approval_groups_tl
786 set created_by = getSeedUser
787 ,last_updated_by = getSeedUser
788 where approval_group_id = l_current_grp_id
789 and language = userenv('LANG');
790 --
791 end if;
792 end loop;
793 end if;
794 exception
795 when others then
796 Fnd_file.put_line(FND_FILE.LOG,'Error in createAprGrp:'||sqlerrm);
797 logMessage(l_proc_name, g_seed_group_data(groupIndex).name||':'||sqlerrm);
798 raise;
799 end createAprGrp;
800 --+
801 procedure updateGroupsConfig is
802 l_name varchar2(100);
803 l_grp_id number;
804 l_proc_name varchar2(100);
805 cursor fetchTxnGroups is
806 select agp.approval_group_id
807 ,agp.name
808 from ame_approval_group_config agc
809 ,ame_approval_Groups agp
810 where agc.application_id = g_seed_appl_id
811 and agc.approval_Group_id = agp.approval_group_id
812 and sysdate between agp.start_date and nvl(agp.end_Date,sysdate)
813 and sysdate between agc.start_date and nvl(agc.end_Date,sysdate);
814 begin
815 l_proc_name := 'updateGroupsConfig';
816 for i in 1..g_seed_group_data.count loop
817 l_name := g_seed_group_data(i).name;
818 l_grp_id := g_seed_group_data(i).approval_Group_id;
819 createAprGrp(groupIndex => i);
820 end loop;
821 exception
822 when others then
823 Fnd_file.put_line(FND_FILE.LOG,'Error in updateGroupsConfig:'||sqlerrm);
824 logMessage(l_proc_name, l_name||':'||sqlerrm);
825 raise;
826 end updateGroupsConfig;
827 --+
828 function getGrpAction(p_action_idIn in number) return number is
829 l_param varchar2(10);
830 l_grp_name varchar2(50);
831 l_action_type_id number;
832 l_proc_name varchar2(100);
833 cursor getGrpActionParam(c_action_id in number) is
834 select parameter
835 ,ac.action_type_id
836 from ame_actions ac
837 , ame_action_types act
838 where ac.action_type_id = act.action_type_id
839 and ac.action_id = c_action_id
840 and sysdate between ac.start_date and nvl(ac.end_Date,sysdate)
841 and sysdate between act.start_date and nvl(act.end_Date,sysdate)
842 and act.name in
843 (ame_util.preApprovalTypeName,
844 ame_util.groupChainApprovalTypeName,
845 ame_util.postApprovalTypeName );
846 --
847 cursor getEntAction(c_name in varchar2,c_action_type_id number) is
848 select action_id
849 from ame_actions
850 where parameter =
851 (select to_char(approval_Group_id)
852 from ame_approval_Groups
853 where name = c_name
854 and sysdate between start_Date and nvl(end_Date,sysdate))
855 and action_type_id = c_action_type_id
856 and sysdate between start_date and nvl(end_Date,sysdate);
857 begin
858 l_proc_name := 'getGrpAction';
859 open getGrpActionParam(p_action_idIn);
860 fetch getGrpActionParam into l_param,l_action_type_id;
861 close getGrpActionParam;
862 --
863 if l_param is null then
864 l_param := p_action_idIn;
865 else
866 for i in 1..g_seed_group_data.count loop
867 if g_seed_group_data(i).approval_group_id = to_number(l_param) then
868 l_grp_name := g_seed_group_data(i).name;
869 exit;
870 end if;
871 end loop;
872 open getEntAction(l_grp_name,l_action_type_id);
873 fetch getEntAction into l_param;
874 close getEntAction;
875 end if;
876 return to_number(l_param);
877 end getGrpAction;
878 --+
879 procedure crtRule(p_rule_index number
880 ,p_rule_idOut out nocopy number) is
881 l_rule_key varchar2(100);
882 l_cond_list ame_util.idList;
883 l_ation_list ame_util.idList;
884 l_conditionid number;
885 l_action_id number;
886 l_action_type_id number;
887 l_temp_action number;
888 l_proc_name varchar2(100);
889 l_found varchar2(2);
890 l_index_count number;
891 --
892 l_rule_start_date date;
893 l_rule_end_Date date;
894 l_current_rule_id number;
895 l_rule_ovn number;
896 l_ru_ovn number;
897 l_ru_start_date date;
898 l_ru_end_date date;
899 l_cnu_ovn number;
900 l_cnu_start_date date;
901 l_cnu_end_Date date;
902 l_acu_ovn number;
903 l_acu_start_date date;
904 l_acu_end_date date;
905 l_effective_date date;
906 l_seed_rule_id number;
907 rule_strt_date_chd varchar2(2) := 'N';
908
909 cursor chkCorrectAction(c_action_id in number,c_rule_type in number) is
910 select 'Y'
911 from ame_action_types act
912 ,ame_actions ac
913 ,ame_action_type_usages actu
914 where ac.action_id = c_action_id
915 and actu.rule_type = c_rule_type
916 and act.action_type_id = ac.action_Type_id
917 and actu.action_type_id = act.action_type_id
918 and sysdate between ac.start_date and nvl(ac.end_Date,sysdate)
919 and sysdate between actu.start_date and nvl(actu.end_Date,sysdate)
920 and sysdate between act.start_date and nvl(act.end_Date,sysdate);
921
922 begin
923 l_proc_name := 'crtRule';
924 l_seed_rule_id := g_seed_ame_rule(p_rule_index).rule_id;
925 --
926 l_index_count := 1;
927 l_cond_list.delete;
928 for i in 1..g_seed_cond_usage.count loop
929 if g_seed_cond_usage(i).rule_id = l_seed_rule_id then
930 l_cond_list(l_index_count) := g_seed_cond_usage(i).condition_id;
931 l_index_count := l_index_count+1;
932 if g_seed_cond_usage.exists(i+1) and g_seed_cond_usage(i+1).rule_id <> l_seed_rule_id then
933 exit;
934 end if;
935 end if;
936 end loop;
937 --
938 l_index_count := 1;
939 l_ation_list.delete;
940 for i in 1..g_seed_act_usage.count loop
941 if g_seed_act_usage(i).rule_id = l_seed_rule_id then
942 l_ation_list(l_index_count) := g_seed_act_usage(i).action_id;
943 l_index_count := l_index_count+1;
944 if g_seed_act_usage.exists(i+1) and g_seed_act_usage(i+1).rule_id <> l_seed_rule_id then
945 exit;
946 end if;
947 end if;
948 end loop;
949 --
950 if l_cond_list.count > 0 then
951 l_conditionid := l_cond_list(1);
952 end if;
953 --
954 if l_ation_list.count > 0 then
955 l_found := null;
956 for i in 1..l_ation_list.count loop
957 l_action_id := getGrpAction(l_ation_list(i));
958 open chkCorrectAction(l_action_id,g_seed_ame_rule(p_rule_index).rule_type);
959 fetch chkCorrectAction into l_found;
960 close chkCorrectAction;
961 if l_found = 'Y' then
962 l_temp_action := l_ation_list(i);
963 l_ation_list(i) := l_ation_list(1);
964 l_ation_list(1) := l_temp_action;
965 exit;
966 end if;
967 end loop;
968 end if;
969 --
970 l_rule_key := ame_rule_pkg.getNextRuleKey;
971 rule_strt_date_chd := 'N';
972 if g_seed_ame_rule(p_rule_index).start_date < sysdate then
973 l_rule_start_date := sysdate + 1;
974 rule_strt_date_chd := 'Y';
975 end if;
976 --
977 l_rule_end_Date := g_seed_ame_rule(p_rule_index).end_Date;
978 logMessage(l_proc_name,'createing the rule with condition:'||l_conditionid);
979 logMessage(l_proc_name,'createing the rule with action:'||l_action_id);
980 ame_rule_api.create_ame_rule
981 (p_rule_key => l_rule_key
982 ,p_description => g_seed_ame_rule(p_rule_index).description
983 ,p_rule_type => g_seed_ame_rule(p_rule_index).rule_type
984 ,p_item_class_id => g_seed_ame_rule(p_rule_index).item_class_id
985 ,p_condition_id => l_conditionid
986 ,p_action_id => l_action_id
987 ,p_application_id => g_ent_appl_id
988 ,p_priority => g_seed_ame_rule(p_rule_index).priority
989 ,p_approver_category => g_seed_ame_rule(p_rule_index).approver_category
990 ,p_rul_start_date => l_rule_start_date
991 ,p_rul_end_date => l_rule_end_Date
992 ,p_rule_id => l_current_rule_id
993 ,p_rul_object_version_number => l_rule_ovn
994 ,p_rlu_object_version_number => l_ru_ovn
995 ,p_rlu_start_date => l_ru_start_date
996 ,p_rlu_end_date => l_ru_end_date
997 ,p_cnu_object_version_number => l_cnu_ovn
998 ,p_cnu_start_date => l_cnu_start_date
999 ,p_cnu_end_date => l_cnu_end_Date
1000 ,p_acu_object_version_number => l_acu_ovn
1001 ,p_acu_start_date => l_acu_start_date
1002 ,p_acu_end_date => l_acu_end_date
1003 );
1004 --
1005 update ame_rules
1006 set created_by = getSeedUser
1007 ,last_updated_by = getSeedUser
1008 where rule_id = l_current_rule_id;
1009 --
1010 update ame_rules_tl
1011 set created_by = getSeedUser
1012 ,last_updated_by = getSeedUser
1013 where rule_id = l_current_rule_id;
1014 --
1015 --update the rule start date to correct value
1016 update ame_rules
1017 set start_date = sysdate
1018 where rule_id = l_current_rule_id
1019 and rule_strt_date_chd = 'Y';
1020 --
1021 update ame_rule_usages
1022 set start_date = sysdate
1023 where rule_id = l_current_rule_id
1024 and item_id = g_ent_appl_id
1025 and rule_strt_date_chd = 'Y';
1026 --
1027 update ame_action_usages
1028 set start_date = sysdate
1029 where rule_id = l_current_rule_id
1030 and rule_strt_date_chd = 'Y';
1031 --
1032 update ame_condition_usages
1033 set start_date = sysdate
1034 where rule_id = l_current_rule_id
1035 and rule_strt_date_chd = 'Y';
1036 --
1037 update ame_rule_usages
1038 set created_by = getSeedUser
1039 ,last_updated_by = getSeedUser
1040 where rule_id = l_current_rule_id
1041 and sysdate between start_date and nvl(end_Date,sysdate);
1042 --
1043 update ame_action_usages
1044 set created_by = getSeedUser
1045 ,last_updated_by = getSeedUser
1046 where rule_id = l_current_rule_id
1047 and action_id = l_action_id;
1048 --
1049 update ame_condition_usages
1050 set created_by = getSeedUser
1051 ,last_updated_by = getSeedUser
1052 where rule_id = l_current_rule_id
1053 and condition_id = l_conditionid;
1054 --
1055 Fnd_file.put_line(FND_FILE.LOG,'Rule is created with id:'||l_current_rule_id);
1056 if l_cond_list.count > 1 and l_current_rule_id is not null then
1057 for i in 2..l_cond_list.count loop
1058 ame_rule_api.create_ame_condition_to_rule
1059 (p_rule_id => l_current_rule_id
1060 ,p_condition_id => l_cond_list(i)
1061 ,p_object_version_number => l_rule_ovn
1062 ,p_start_date => l_rule_start_date
1063 ,p_end_date => l_rule_end_Date
1064 );
1065 --
1066 update ame_condition_usages
1067 set created_by = getSeedUser
1068 ,last_updated_by = getSeedUser
1069 where rule_id = l_current_rule_id
1070 and condition_id = l_cond_list(i)
1071 and sysdate between start_date and nvl(end_date,sysdate);
1072 Fnd_file.put_line(FND_FILE.LOG,'Added the condition:'||l_cond_list(i));
1073 end loop;
1074 end if;
1075 if l_ation_list.count > 1 and l_current_rule_id is not null then
1076 for i in 2..l_ation_list.count loop
1077 l_action_id := getGrpAction(l_ation_list(i));
1078 ame_rule_api.create_ame_action_to_rule
1079 (p_rule_id => l_current_rule_id
1080 ,p_action_id => l_action_id
1081 ,p_object_version_number => l_rule_ovn
1082 ,p_start_date => l_rule_start_date
1083 ,p_end_date => l_rule_end_Date
1084 );
1085 update ame_action_usages
1086 set created_by = getSeedUser
1087 ,last_updated_by = getSeedUser
1088 where rule_id = l_current_rule_id
1089 and action_id = l_action_id
1090 and sysdate between start_date and nvl(end_date,sysdate);
1091 Fnd_file.put_line(FND_FILE.LOG,'Added the action:'||l_action_id);
1092 end loop;
1093 end if;
1094 exception
1095 when others then
1096 Fnd_file.put_line(FND_FILE.LOG,'Error in crtRule:'||sqlerrm);
1097 logMessage(l_proc_name,sqlerrm);
1098 raise;
1099 end crtRule;
1100 --+
1101 procedure createRules(p_rule_index in number
1102 ,p_rule_idOut out nocopy number
1103 ,p_custfoundOut out nocopy varchar2) is
1104 l_ovn number;
1105 l_start_date date;
1106 l_end_Date date;
1107 l_count number;
1108 l_current_count number;
1109 l_end_Date_count number;
1110 l_current_rul_id number;
1111 l_proc_name varchar2(100);
1112 cursor chkRule(c_rule_name in varchar2) is
1113 select count(*)
1114 from ame_rules ar
1115 ,ame_rule_usages aru
1116 where ar.rule_id = aru.rule_id
1117 and ar.description = c_rule_name
1118 and aru.item_id = g_ent_appl_id
1119 and ((sysdate between ar.start_date
1120 and nvl(ar.end_date + 1/86400,sysdate)) or
1121 (sysdate < ar.start_date and
1122 ar.start_date < nvl(ar.end_date, ar.start_date + 1/86400)))
1123 and ((sysdate between aru.start_date
1124 and nvl(aru.end_date + 1/86400,sysdate)) or
1125 (sysdate < aru.start_date and
1126 aru.start_date < nvl(aru.end_date, aru.start_date + 1/86400)));
1127 --+
1128 cursor chkCustRule(c_rule_name in varchar2) is
1129 select count(*)
1130 from ame_rules ar
1131 ,ame_rule_usages aru
1132 where ar.rule_id = aru.rule_id
1133 and ar.description = c_rule_name
1134 and aru.item_id = g_ent_appl_id
1135 and (sysdate > aru.end_Date or sysdate > ar.end_Date);
1136 begin
1137 l_proc_name := 'createRules';
1138 p_custfoundOut := 'N';
1139 /*rule not found, it is either customized or need to create new rule*/
1140 -- check if the rule customized --there should be an end dated row in usage or rules
1141 open chkCustRule(g_seed_ame_rule(p_rule_index).description);
1142 fetch chkCustRule into l_end_Date_count;
1143 close chkCustRule;
1144 if l_end_Date_count > 0 then
1145 -- customization found found
1146 p_custfoundOut := 'Y';
1147 Fnd_file.put_line(FND_FILE.LOG,'Rule found to be customized');
1148 return;
1149 else
1150 -- create flow
1151 crtRule(p_rule_index,l_current_rul_id);
1152 end if;
1153 exception
1154 when others then
1155 Fnd_file.put_line(FND_FILE.LOG,'Error in createRules:'||sqlerrm);
1156 logMessage(l_proc_name,sqlerrm);
1157 raise;
1158 end createRules;
1159 --+
1160 procedure updateRuleUsg is
1161 l_rule_name varchar2(500);
1162 l_seed_rule_id number;
1163 l_current_rule_id number;
1164 l_custom varchar2(10);
1165 l_count number;
1166 l_proc_name varchar2(100);
1167 cursor getSeedRules is
1168 select ar.rule_id
1169 ,ar.description
1170 from ame_rules ar
1171 ,ame_rule_usages aru
1172 where ar.rule_id = aru.rule_id
1173 and aru.item_id = g_seed_appl_id
1174 and ((sysdate between ar.start_date
1175 and nvl(ar.end_date + 1/86400,sysdate)) or
1176 (sysdate < ar.start_date and
1177 ar.start_date < nvl(ar.end_date, ar.start_date + 1/86400)))
1178 and ((sysdate between aru.start_date
1179 and nvl(aru.end_date + 1/86400,sysdate)) or
1180 (sysdate < aru.start_date and
1181 aru.start_date < nvl(aru.end_date, aru.start_date + 1/86400)));
1182 --
1183 cursor chkRulUsg(c_rule_name in varchar2) is
1184 select count(*)
1185 from ame_rule_usages aru, ame_rules ar
1186 where ar.description = c_rule_name
1187 and aru.rule_id = ar.rule_id
1188 and aru.item_id = g_ent_appl_id
1189 and ((sysdate between ar.start_date
1190 and nvl(ar.end_date + 1/86400,sysdate)) or
1191 (sysdate < ar.start_date and
1192 ar.start_date < nvl(ar.end_date, ar.start_date + 1/86400)))
1193 and ((sysdate between aru.start_date
1194 and nvl(aru.end_date + 1/86400,sysdate)) or
1195 (sysdate < aru.start_date and
1196 aru.start_date < nvl(aru.end_date, aru.start_date + 1/86400)));
1197 begin
1198 l_proc_name := 'updateRuleUsg';
1199 for i in 1..g_seed_ame_rule.count loop
1200 l_rule_name := g_seed_ame_rule(i).description;
1201 l_seed_rule_id := g_seed_ame_rule(i).rule_id;
1202 logMessage(l_proc_name,'Setting up the Rule : ' ||l_rule_name);
1203 Fnd_file.put_line(FND_FILE.LOG,'Setting up the Rule:'||l_rule_name);
1204 open chkRulUsg(l_rule_name);
1205 fetch chkRulUsg into l_count;
1206 close chkRulUsg;
1207 if l_count > 0 then
1208 logMessage(l_proc_name,'Rule is found');
1209 Fnd_file.put_line(FND_FILE.LOG,'Rule is already found found');
1210 else
1211 logMessage(l_proc_name,'Rule is not found, so creating the rule');
1212 Fnd_file.put_line(FND_FILE.LOG,'Rule not found, Creating the rule');
1213 createRules(p_rule_index => i
1214 ,p_rule_idOut => l_current_rule_id
1215 ,p_custfoundOut => l_custom);
1216 end if;
1217 end loop;
1218 exception
1219 when others then
1220 Fnd_file.put_line(FND_FILE.LOG,'Error in updateRuleUsg:'||sqlerrm);
1221 logMessage(l_proc_name,l_rule_name||':'||sqlerrm);
1222 raise;
1223 end updateRuleUsg;
1224 --+
1225 Procedure fetchSeedDataFromTables is
1226 seed_row ref_cursor;
1227 l_proc_name varchar2(30);
1228 l_app_config_val varchar2(10);
1229 l_ovn number;
1230 l_ovn1 number;
1231 l_start_date date;
1232 l_end_Date date;
1233 cursor getAllowApprTypeConfig(c_appl_id in number) is
1234 select variable_value,object_version_number
1235 from ame_config_vars
1236 where variable_name = ame_util.allowAllApproverTypesConfigVar
1237 and application_id = c_appl_id
1238 and sysdate between start_date and nvl(end_date,sysdate);
1239 begin
1240 l_proc_name := 'fetchSeedDataFromTables';
1241 Fnd_file.put_line(FND_FILE.LOG,'Start reading the data for seed transaction');
1242 --+
1243 logMessage(l_proc_name,'Reading transaction type data for ' || g_seed_txntype);
1244 open seed_row for
1245 select *
1246 from ame_calling_apps
1247 where transaction_type_id = g_seed_txntype
1248 and fnd_application_id = g_fnd_application_id
1249 and sysdate between start_date and nvl(end_Date,sysdate);
1250 fetch seed_row bulk collect into g_seed_call_apps;
1251 close seed_row;
1252 g_seed_appl_id := g_seed_call_apps(1).application_id;
1253 --+
1254 /*following code specifically added for SSHR txn type to set the config
1255 allow approver type to yes in enterprise txn type even though seed txn is
1256 not defined Bug 8234223*/
1257 if g_seed_txntype = 'SSHRMS' then
1258 open getAllowApprTypeConfig(g_seed_appl_id);
1259 fetch getAllowApprTypeConfig into l_app_config_val,l_ovn;
1260 close getAllowApprTypeConfig;
1261 if l_app_config_val is null or l_app_config_val = 'no' then
1262 open getAllowApprTypeConfig(0);
1263 fetch getAllowApprTypeConfig into l_app_config_val,l_ovn1;
1264 close getAllowApprTypeConfig;
1265 if l_app_config_val = 'no' then
1266 Fnd_file.put_line(FND_FILE.LOG,'changing the SSHR transaction configuration variable to yes');
1267 ame_config_var_api.update_ame_config_variable
1268 (p_application_id => g_seed_appl_id
1269 ,p_variable_name => ame_util.allowAllApproverTypesConfigVar
1270 ,p_variable_value => 'yes'
1271 ,p_object_version_number => l_ovn
1272 ,p_start_date => l_start_date
1273 ,p_end_date => l_end_Date
1274 );
1275 update ame_config_vars
1276 set last_updated_by = getSeedUser
1277 where application_id = g_seed_appl_id
1278 and variable_name = ame_util.allowAllApproverTypesConfigVar
1279 and sysdate between start_date and nvl(end_date,sysdate);
1280 Fnd_file.put_line(FND_FILE.LOG,'Updated the SSHR default configuration variable to yes');
1281 end if;
1282 end if;
1283 end if;
1284 logMessage(l_proc_name,'Reading configuration variables data');
1285 open seed_row for
1286 select *
1287 from ame_Config_vars seed
1288 where application_id = g_seed_appl_id
1289 and sysdate between start_Date and nvl(end_Date,sysdate);
1290 fetch seed_row bulk collect into g_seed_config_usg;
1291 close seed_row;
1292 --+
1293 logMessage(l_proc_name,'Reading mandatory attribute usage data');
1294 open seed_row for
1295 select seed.*
1296 from ame_attribute_usages seed
1297 ,ame_attributes atr
1298 where seed.attribute_id = atr.attribute_id
1299 and exists
1300 (select null
1301 from ame_mandatory_attributes
1302 where attribute_id = atr.attribute_id
1303 and action_type_id = -1
1304 and sysdate between start_date and nvl(end_Date,sysdate))
1305 and seed.application_id = g_seed_appl_id
1306 and sysdate between seed.start_Date and nvl(seed.end_Date,sysdate)
1307 and sysdate between atr.start_date and nvl(atr.end_Date,sysdate)
1308 and isSeedUser(seed.last_updated_by) = ame_util.booleanTrue;
1309 fetch seed_row bulk collect into g_seed_mand_attr_usg;
1310 close seed_row;
1311 --+
1312 logMessage(l_proc_name,'Reading Transaction Type Attribute Usage data');
1313 open seed_row for
1314 select atu.*
1315 from ame_attributes atr
1316 ,ame_attribute_usages atu
1317 where atr.attribute_id = atu.attribute_id
1318 and atu.application_id = g_seed_appl_id
1319 and sysdate between atr.start_Date and nvl(atr.end_Date,sysdate)
1320 and sysdate between atu.start_date and nvl(atu.end_Date,sysdate)
1321 and not exists
1322 (select null
1323 from ame_mandatory_attributes
1324 where attribute_id = atr.attribute_id
1325 and action_type_id = -1
1326 and sysdate between start_Date and nvl(end_Date,sysdate));
1327 fetch seed_row bulk collect into g_seed_attr_usg;
1328 close seed_row;
1329 --+
1330 logMessage(l_proc_name,'Reading Action Type Configuration data');
1331 open seed_row for
1332 select *
1333 from ame_action_type_config
1334 where sysdate between start_date and nvl(end_Date,sysdate)
1335 and application_id = g_seed_appl_id;
1336 fetch seed_row bulk collect into g_seed_act_config;
1337 close seed_row;
1338 --+
1339 logMessage(l_proc_name,'Reading Approval Group data');
1340 open seed_row for
1341 select voting_regime
1342 ,order_number
1343 ,name
1344 ,groups.approval_group_id
1345 ,query_string
1346 ,is_static
1347 ,description
1348 ,groups.last_updated_by group_last_updated_by
1349 ,config.last_updated_by config_last_updated_by
1350 ,groups.creation_date group_creation_date
1351 ,config.creation_date config_creation_date
1352 from ame_approval_Groups groups
1353 ,ame_approval_group_config config
1354 where application_id = g_seed_appl_id
1355 and groups.approval_group_id = config.approval_group_id
1356 and sysdate between groups.start_date and nvl(groups.end_Date,sysdate)
1357 and sysdate between config.start_date and nvl(config.end_Date,sysdate);
1358 fetch seed_row bulk collect into g_seed_group_data;
1359 close seed_row;
1360 --+
1361 logMessage(l_proc_name,'Reading Item Class Usage data');
1362 open seed_row for
1363 select *
1364 from ame_item_class_usages
1365 where application_id = g_seed_appl_id
1366 and sysdate between start_date and nvl(end_Date,sysdate);
1367 fetch seed_row bulk collect into g_seed_ic_usg;
1368 close seed_row;
1369 --+
1370 logMessage(l_proc_name,'Reading Rule Usages data');
1371 open seed_row for
1372 select ar.rule_id
1373 ,ar.description
1374 ,ar.rule_type
1375 ,ar.start_date
1376 ,ar.end_date
1377 ,aru.start_date usage_start_date
1378 ,aru.end_date usage_end_date
1379 ,aru.approver_category
1380 ,aru.priority
1381 ,ar.item_class_id
1382 from ame_rules ar
1383 ,ame_rule_usages aru
1384 where aru.item_id = g_seed_appl_id
1385 and ar.rule_id = aru.rule_id
1386 and ((sysdate between ar.start_date
1387 and nvl(ar.end_date + 1/86400,sysdate)) or
1388 (sysdate < ar.start_date and
1389 ar.start_date < nvl(ar.end_date, ar.start_date + 1/86400)))
1390 and ((sysdate between aru.start_date
1391 and nvl(aru.end_date + 1/86400,sysdate)) or
1392 (sysdate < aru.start_date and
1393 aru.start_date < nvl(aru.end_date, aru.start_date + 1/86400)))
1394 order by aru.rule_id;
1395 fetch seed_row bulk collect into g_seed_ame_rule;
1396 close seed_row;
1397 --
1398 logMessage(l_proc_name,'Reading Rule actions data');
1399 open seed_row for
1400 select acu.*
1401 from ame_rules ar
1402 ,ame_rule_usages aru
1403 ,ame_action_usages acu
1404 where aru.item_id = g_seed_appl_id
1405 and acu.rule_id = ar.rule_id
1406 and ar.rule_id = aru.rule_id
1407 and ((sysdate between ar.start_date
1408 and nvl(ar.end_date + 1/86400,sysdate)) or
1409 (sysdate < ar.start_date and
1410 ar.start_date < nvl(ar.end_date, ar.start_date + 1/86400)))
1411 and ((sysdate between aru.start_date
1412 and nvl(aru.end_date + 1/86400,sysdate)) or
1413 (sysdate < aru.start_date and
1414 aru.start_date < nvl(aru.end_date, aru.start_date + 1/86400)))
1415 and ((sysdate between acu.start_date and nvl(acu.end_Date+1/86400,sysdate)) or
1416 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_Date,acu.start_date+1/86400)))
1417 order by acu.rule_id;
1418 fetch seed_row bulk collect into g_seed_act_usage;
1419 close seed_row;
1420 --
1421 logMessage(l_proc_name,'Reading Rule conditions data');
1422 open seed_row for
1423 select acu.*
1424 from ame_rules ar
1425 ,ame_rule_usages aru
1426 ,ame_condition_usages acu
1427 where aru.item_id = g_seed_appl_id
1428 and acu.rule_id = ar.rule_id
1429 and ar.rule_id = aru.rule_id
1430 and ((sysdate between ar.start_date
1431 and nvl(ar.end_date + 1/86400,sysdate)) or
1432 (sysdate < ar.start_date and
1433 ar.start_date < nvl(ar.end_date, ar.start_date + 1/86400)))
1434 and ((sysdate between aru.start_date
1435 and nvl(aru.end_date + 1/86400,sysdate)) or
1436 (sysdate < aru.start_date and
1437 aru.start_date < nvl(aru.end_date, aru.start_date + 1/86400)))
1438 and ((sysdate between acu.start_date and nvl(acu.end_Date+1/86400,sysdate)) or
1439 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_Date,acu.start_date+1/86400)))
1440 order by acu.rule_id;
1441 fetch seed_row bulk collect into g_seed_cond_usage;
1442 close seed_row;
1443 --
1444 Fnd_file.put_line(FND_FILE.LOG,'Completed reading the data for seed transaction');
1445 exception
1446 when others then
1447 Fnd_file.put_line(FND_FILE.LOG,'Error in fetchSeedDataFromTables:'||sqlerrm);
1448 logMessage('fetchSeedDataFromTables',sqlerrm);
1449 raise;
1450 end fetchSeedDataFromTables;
1451 --+
1452 Procedure copyEntTxnType is
1453 l_txnTypeName varchar2(50);
1454 l_applicationId number;
1455 l_proc_name varchar2(100);
1456 begin
1457 l_proc_name := 'copyEntTxnType';
1458 logMessage(l_proc_name,'Enabling the Global Context');
1459 enable_globalcontext;
1460 if g_enterprise_id is null then
1461 logMessage(l_proc_name,'Fatal Exception - Could not find Enterprise');
1462 Fnd_file.put_line(FND_FILE.LOG,'Fatal Exception - Could not find Enterprise');
1463 return;
1464 end if;
1465 l_txnTypeName := g_seed_txntype;
1466 fetchSeedDataFromTables;
1467 logMessage(l_proc_name,'Enabling the Org Context');
1468 Fnd_file.put_line(FND_FILE.LOG,'Enabling the Org Context to copy the seed date');
1469 enable_orgcontext;
1470 createTxnType(cpTxnTypeIn => l_txnTypeName);
1471 if g_ent_appl_id is not null then
1472 updateConfigVars;
1473 updateItemClassUsage;
1474 createAttrUsage;
1475 updateActionTypeConfig;
1476 updateGroupsConfig;
1477 updateRuleUsg;
1478 end if;
1479 exception
1480 when others then
1481 Fnd_file.put_line(FND_FILE.LOG,'Error in copyEntTxnType:'||sqlerrm);
1482 logMessage('copyEntTxnType',sqlerrm);
1483 raise;
1484 end;
1485 --+
1486 procedure copyTxnType(errbuf out nocopy varchar2,
1487 retcode out nocopy number,
1488 applicationIdIn in number,
1489 enterpriseIdIn in varchar2) is
1490 l_enterpriseName varchar2(100);
1491 l_txnTypeId varchar2(100);
1492 l_fnd_appl_id number;
1493 cursor getTxnType(c_applIdIn in number) is
1494 select transaction_type_id
1495 ,fnd_application_id
1496 from ame_calling_apps
1497 where application_id = c_applIdIn
1498 and sysdate between start_date and nvl(end_date,sysdate);
1499 --
1500 cursor getEnterpriseName(c_enterpriseId in number) is
1501 select enterprise_name
1502 ,'C::'||enterprise_label
1503 from per_enterprises_vl
1504 where enterprise_id = c_enterpriseId;
1505 begin
1506 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Transaction Type copy input parameter:');
1507 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'applicationIdIn:'||applicationIdIn);
1508 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'enterpriseIdIn:'||enterpriseIdIn);
1509 Fnd_file.put_line(FND_FILE.LOG,'Start Copying the transaction type');
1510 logMessage('copyTxnType','param:applicationIdIn:'||applicationIdIn);
1511 logMessage('copyTxnType','param:enterpriseIdIn:'||enterpriseIdIn);
1512 if is_multi_tenant_system = ame_util.booleanFalse then
1513 Fnd_file.put_line(FND_FILE.LOG,'Current instance is not a multitenant instance, aborting the copy');
1514 logMessage('copyTxnType','not a multitenancy instance');
1515 return;
1516 end if;
1517 open getTxnType(applicationIdIn);
1518 fetch getTxnType into l_txnTypeId,l_fnd_appl_id;
1519 close getTxnType;
1520 g_enterprise_id := enterpriseIdIn;
1521 g_seed_txntype := l_txnTypeId;
1522 g_fnd_application_id := l_fnd_appl_id;
1523 Fnd_file.put_line(FND_FILE.LOG,'Seed Transaction type selected:'||g_seed_txntype);
1524 Fnd_file.put_line(FND_FILE.LOG,'Seed transaction type application id:'||g_fnd_application_id);
1525 open getEnterpriseName(enterpriseIdIn);
1526 fetch getEnterpriseName into g_enterprise,g_enterprise_label;
1527 close getEnterpriseName;
1528 Fnd_file.put_line(FND_FILE.LOG,'Enterprise name:'||g_enterprise);
1529 Fnd_file.put_line(FND_FILE.LOG,'Enterprise label:'||g_enterprise_label);
1530 logMessage('copyTxnType','param:g_enterprise:'||g_enterprise);
1531 logMessage('copyTxnType','param:g_enterprise_label:'||g_enterprise_label);
1532 copyEntTxnType;
1533 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Transaction Type copy completed successfully');
1534 exception
1535 when others then
1536 Fnd_file.put_line(FND_FILE.LOG,'Error in enabling org context:'||sqlerrm);
1537 logMessage('copyTxnType','error:'||sqlerrm);
1538 raise;
1539 end copyTxnType;
1540 --
1541 function is_multi_tenant_system return varchar2 as
1542 l_profile_value varchar2 (255);
1543 begin
1544 l_profile_value := fnd_profile.value('HR_ENABLE_MULTI_TENANCY');
1545 if l_profile_value = 'B' then
1546 return ame_util.booleanTrue;
1547 end if;
1548 return ame_util.booleanFalse;
1549 end is_multi_tenant_system;
1550
1551 function disableConditionUpd(conditionIdIn in number) return varchar2 is
1552 l_session_label varchar2(100);
1553 l_row_label varchar2(100);
1554 label_query varchar2(1000) :=
1555 'SELECT LABEL_TO_CHAR(HR_ENTERPRISE) ENT_LABEL ' ||
1556 ' FROM ame_conditions ' ||
1557 ' WHERE condition_id = :1 ' ||
1558 ' AND (sysdate + 1/64000) between start_date and nvl(end_date,sysdate)';
1559 session_label_query varchar2(1000) :=
1560 'SELECT sa_session.row_label(''HR_ENTERPRISE_POLICY'') ' ||
1561 ' FROM dual ';
1562 begin
1563 if is_multi_tenant_system = ame_util.booleanFalse then
1564 return 'Y1';
1565 else
1566 execute immediate session_label_query
1567 into l_session_label;
1568
1569 execute immediate label_query
1570 into l_row_label
1571 using in conditionIdIn;
1572 if l_session_label <> nvl(l_row_label,'C::ENT') then
1573 return 'N1';
1574 else
1575 return 'Y1';
1576 end if;
1577 end if;
1578 end disableConditionUpd;
1579 --+
1580 function isConfigUpdatable return varchar2 is
1581 l_session_label varchar2(100);
1582 session_label_query varchar2(1000) :=
1583 'SELECT sa_session.row_label(''HR_ENTERPRISE_POLICY'') ' ||
1584 ' FROM dual ';
1585 begin
1586 if is_multi_tenant_system = ame_util.booleanFalse then
1587 return 'Y';
1588 else
1589 execute immediate session_label_query
1590 into l_session_label;
1591 if l_session_label <> 'C::ENT' then
1592 return 'N';
1593 else
1594 return 'Y';
1595 end if;
1596 end if;
1597 end isConfigUpdatable;
1598 --+
1599 end ame_multi_tenancy_pkg;