[Home] [Help]
PACKAGE BODY: APPS.AME_ACTION_API
Source
1 Package Body ame_action_api as
2 /* $Header: amatyapi.pkb 120.0 2005/09/02 03:52:24 mbocutt noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'ame_action_api.';
7 --
8 function get_action_type_order_number(p_ame_application_id in number,
9 p_action_type_id in number) return number as
10 l_proc varchar2(72) := g_package||'get_action_type_order_number';
11 l_order_number number;
12 begin
13 hr_utility.set_location('Entering:'|| l_proc, 10);
14 select order_number
15 into l_order_number
16 from ame_action_type_config
17 where
18 action_type_id = p_action_type_id and
19 application_id = p_ame_application_id and
20 sysdate between start_date and
21 nvl(end_date - ame_util.oneSecond, sysdate);
22 return(l_order_number);
23 exception
24 when others then
25 hr_utility.set_location(' Leaving:'||l_proc, 60);
26 raise;
27 return(null);
28 end get_action_type_order_number;
29 --
30 function get_allowed_rule_type(p_action_type_id in number) return number as
31 l_proc varchar2(72) := g_package||'get_allowed_rule_type';
32 l_rule_type number;
33 l_temp_count number;
34 begin
35 hr_utility.set_location('Entering:'|| l_proc, 10);
36 select count(*)
37 into l_temp_count
38 from ame_action_type_usages
39 where
40 action_type_id = p_action_type_id and
41 sysdate between start_date and
42 nvl(end_date - ame_util.oneSecond, sysdate);
43 if(l_temp_count > 1) then
44 /* authority and exception rule types are mapped to the action type */
45 /* return chain of authority */
46 return(ame_util.authorityRuleType);
47 else
48 select rule_type
49 into l_rule_type
50 from ame_action_type_usages
51 where
52 action_type_id = p_action_type_id and
53 sysdate between start_date and
54 nvl(end_date - ame_util.oneSecond, sysdate);
55 return(l_rule_type);
56 end if;
57 exception
58 when others then
59 hr_utility.set_location(' Leaving:'||l_proc, 60);
60 raise;
61 return(null);
62 end get_allowed_rule_type;
63 --
64 function get_max_order_number(p_ame_application_id in number,
65 p_action_type_id in number) return number as
66 l_proc varchar2(72) := g_package||'get_max_order_number';
67 l_order_number number;
68 l_rule_type number;
69 begin
70 hr_utility.set_location('Entering:'|| l_proc, 10);
71 l_rule_type := get_allowed_rule_type(p_action_type_id => p_action_type_id);
72 select max(order_number)
73 into l_order_number
74 from ame_action_type_config,
75 ame_action_type_usages,
76 ame_action_types
77 where
78 ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
79 ame_action_types.action_type_id = ame_action_type_config.action_type_id and
80 ame_action_type_config.application_id = p_ame_application_id and
81 ame_action_type_usages.rule_type = l_rule_type and
82 sysdate between ame_action_type_config.start_date and
83 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
84 sysdate between ame_action_types.start_date and
85 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
86 sysdate between ame_action_type_usages.start_date and
87 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate);
88 return(l_order_number);
89 exception
90 when others then
91 hr_utility.set_location(' Leaving:'||l_proc, 50);
92 raise;
93 return(null);
94 end get_max_order_number;
95 --
96 function order_number_unique(p_ame_application_id in number,
97 p_order_number in number,
98 p_action_type_id in number) return boolean as
99 l_proc varchar2(72) := g_package||'order_number_unique';
100 l_rule_type number;
101 l_temp_count number;
102 begin
103 hr_utility.set_location('Entering:'|| l_proc, 10);
104 l_rule_type := get_allowed_rule_type(p_action_type_id => p_action_type_id);
105 select count(*)
106 into l_temp_count
107 from ame_action_type_config,
108 ame_action_type_usages,
109 ame_action_types
110 where
111 ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
112 ame_action_types.action_type_id = ame_action_type_config.action_type_id and
113 ame_action_type_config.application_id = p_ame_application_id and
114 ame_action_type_config.order_number = p_order_number and
115 ame_action_type_usages.rule_type = l_rule_type and
116 sysdate between ame_action_type_config.start_date and
117 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
118 sysdate between ame_action_types.start_date and
119 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
120 sysdate between ame_action_type_usages.start_date and
121 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate);
122 if(l_temp_count > 1) then
123 return(false);
124 else
125 return(true);
126 end if;
127 exception
128 when others then
129 hr_utility.set_location(' Leaving:'||l_proc, 60);
130 raise;
131 return(false);
132 end order_number_unique;
133 --
134 procedure decrement_action_type_ord_nums(p_ame_application_id in number,
135 p_action_type_id in number,
136 p_order_number in number) as
137 cursor c_sel1(p_ame_application_id in number,
138 p_order_number in number,
139 p_rule_type in number) is
140 select ame_action_type_config.action_type_id,
141 ame_action_type_config.order_number,
142 ame_action_type_config.object_version_number,
143 ame_action_types.object_version_number
144 from ame_action_type_config,
145 ame_action_type_usages,
146 ame_action_types
147 where
148 ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
149 ame_action_types.action_type_id = ame_action_type_config.action_type_id and
150 ame_action_type_config.application_id = p_ame_application_id and
151 ame_action_type_config.order_number > p_order_number and
152 ame_action_type_usages.rule_type = p_rule_type and
153 sysdate between ame_action_type_config.start_date and
154 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
155 sysdate between ame_action_types.start_date and
156 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
157 sysdate between ame_action_type_usages.start_date and
158 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
159 order by order_number;
160 l_proc varchar2(72) := g_package||'decrement_action_type_ord_nums';
161 l_action_type_ids ame_util.idList;
162 l_order_numbers ame_util.idList;
163 l_acf_object_version_numbers ame_util.idList;
164 l_aty_object_version_numbers ame_util.idList;
165 l_rule_type number;
166 l_effective_date date;
167 l_acf_object_version_number number;
168 l_aty_object_version_number number;
169 l_acf_start_date date;
170 l_aty_start_date date;
171 l_acf_end_date date;
172 l_aty_end_date date;
173 begin
174 hr_utility.set_location('Entering:'|| l_proc, 10);
175 l_effective_date := sysdate;
176 l_rule_type := get_allowed_rule_type(p_action_type_id => p_action_type_id);
177 open c_sel1(p_ame_application_id => p_ame_application_id,
178 p_order_number => p_order_number,
179 p_rule_type => l_rule_type);
180 fetch c_sel1 bulk collect
181 into l_action_type_ids,
182 l_order_numbers,
183 l_acf_object_version_numbers,
184 l_aty_object_version_numbers;
185 close c_sel1;
186 for i in 1 .. l_action_type_ids.count loop
187 -- Update action type config details
188 ame_acf_upd.upd(p_effective_date => l_effective_date
189 ,p_datetrack_mode => 'UPDATE'
190 ,p_application_id => p_ame_application_id
191 ,p_action_type_id => l_action_type_ids(i)
192 ,p_order_number => (l_order_numbers(i) - 1)
193 ,p_object_version_number => l_acf_object_version_numbers(i)
194 ,p_start_date => l_acf_start_date
195 ,p_end_date => l_acf_end_date
196 );
197 end loop;
198 hr_utility.set_location(' Leaving:'||l_proc, 50);
199 exception
200 when others then
201 hr_utility.set_location(' Leaving:'||l_proc, 60);
202 rollback;
203 raise;
204 end decrement_action_type_ord_nums;
205 --
206 procedure increment_action_type_ord_nums(p_ame_application_id in number,
207 p_action_type_id in number,
208 p_order_number in number) as
209 cursor c_sel1(p_ame_application_id in number,
210 p_action_type_id in number,
211 p_order_number in number,
212 p_rule_type in number) is
213 select ame_action_type_config.action_type_id,
214 ame_action_type_config.order_number,
215 ame_action_type_config.object_version_number,
216 ame_action_types.object_version_number
217 from ame_action_type_config,
218 ame_action_type_usages,
219 ame_action_types
220 where
221 ame_action_type_config.action_type_id = ame_action_types.action_type_id and
222 ame_action_type_usages.action_type_id = ame_action_types.action_type_id and
223 ame_action_type_config.application_id = p_ame_application_id and
224 ame_action_type_config.action_type_id <> p_action_type_id and
225 ame_action_type_config.order_number >= p_order_number and
226 ame_action_type_usages.rule_type = p_rule_type and
227 sysdate between ame_action_type_config.start_date and
228 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
229 sysdate between ame_action_types.start_date and
230 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
231 sysdate between ame_action_type_usages.start_date and
232 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
233 order by order_number;
234 l_proc varchar2(72) := g_package||'increment_action_type_ord_nums';
235 l_effective_date date;
236 l_action_type_ids ame_util.idList;
237 l_acf_object_version_numbers ame_util.idList;
238 l_aty_object_version_numbers ame_util.idList;
239 l_order_numbers ame_util.idList;
240 l_rule_type integer;
241 l_acf_object_version_number number;
242 l_aty_object_version_number number;
243 l_acf_start_date date;
244 l_aty_start_date date;
245 l_acf_end_date date;
246 l_aty_end_date date;
247 begin
248 hr_utility.set_location('Entering:'|| l_proc, 10);
249 l_effective_date := sysdate;
250 l_rule_type := get_allowed_rule_type(p_action_type_id => p_action_type_id);
251 open c_sel1(p_ame_application_id => p_ame_application_id,
252 p_action_type_id => p_action_type_id,
253 p_order_number => p_order_number,
254 p_rule_type => l_rule_type);
255 fetch c_sel1 bulk collect
256 into l_action_type_ids,
257 l_order_numbers,
258 l_acf_object_version_numbers,
259 l_aty_object_version_numbers;
260 close c_sel1;
261 for i in 1 .. l_action_type_ids.count loop
262 -- Update action type config details
263 ame_acf_upd.upd(p_effective_date => l_effective_date
264 ,p_datetrack_mode => 'UPDATE'
265 ,p_application_id => p_ame_application_id
266 ,p_action_type_id => l_action_type_ids(i)
267 ,p_order_number => (l_order_numbers(i) + 1)
268 ,p_object_version_number => l_acf_object_version_numbers(i)
269 ,p_start_date => l_acf_start_date
270 ,p_end_date => l_acf_end_date
271 );
272 end loop;
273 hr_utility.set_location(' Leaving:'||l_proc, 50);
274 exception
275 when others then
276 hr_utility.set_location(' Leaving:'||l_proc, 60);
277 rollback;
278 raise;
279 end increment_action_type_ord_nums;
280 --
281 -- ----------------------------------------------------------------------------
282 -- |-------------------< create_ame_action_type >------------------------|
283 -- ----------------------------------------------------------------------------
284 --
285 procedure create_ame_action_type
286 (p_validate in boolean default false
287 ,p_language_code in varchar2 default hr_api.userenv_lang
288 ,p_name in varchar2
289 ,p_procedure_name in varchar2
290 ,p_dynamic_description in varchar2
291 ,p_description in varchar2
292 ,p_description_query in varchar2 default null
293 ,p_rule_type in number default null
294 ,p_approver_type_id in number default null
295 ,p_action_type_id out nocopy number
296 ,p_aty_object_version_number out nocopy number
297 ,p_aty_start_date out nocopy date
298 ,p_aty_end_date out nocopy date
299 ,p_apu_object_version_number out nocopy number
300 ,p_apu_start_date out nocopy date
301 ,p_apu_end_date out nocopy date
302 ) is
303 cursor application_cur(p_effective_date in date) is
304 select application_id
305 from ame_calling_apps
306 where p_effective_date between start_date
307 and nvl(end_date - ame_util.oneSecond, p_effective_date);
308 -- local variables
309 l_proc varchar2(72) := g_package||'create_ame_action_type';
310 l_action_type_id number;
311 l_effective_date date;
312 l_aty_object_version_number number;
313 l_aty_start_date date;
314 l_aty_end_date date;
315 l_axu_object_version_number number;
316 l_axu_start_date date;
317 l_axu_end_date date;
318 l_acf_object_version_number number;
319 l_acf_start_date date;
320 l_acf_end_date date;
321 l_apu_object_version_number number;
322 l_apu_start_date date;
323 l_apu_end_date date;
324 l_language_code fnd_languages.language_code%TYPE;
325 l_application_ids ame_util.idList;
326 --
327 begin
328 hr_utility.set_location('Entering:'|| l_proc, 10);
329 -- Validate language code
330 l_language_code := p_language_code;
331 hr_api.validate_language_code(p_language_code => l_language_code);
332 -- Set the effective date to the sysdate
333 l_effective_date := sysdate;
334 -- Issue a savepoint.
335 --
336 savepoint create_ame_action_type;
337 --
338 -- Call Before Process User Hook
339 --
340 begin
341 ame_action_bk1.create_ame_action_type_b
342 (p_language_code => p_language_code
343 ,p_name => p_name
344 ,p_procedure_name => p_procedure_name
345 ,p_description => p_description
346 ,p_dynamic_description => p_dynamic_description
347 ,p_description_query => p_description_query
348 );
349 exception
350 when hr_api.cannot_find_prog_unit then
351 hr_api.cannot_find_prog_unit_error
352 (p_module_name => 'create_ame_action_type'
353 ,p_hook_type => 'BP'
354 );
355 end;
356 --
357 -- insert the row in ame_action_types.
358 ame_aty_ins.ins(p_effective_date => l_effective_date,
359 p_name => p_name,
360 p_procedure_name => p_procedure_name,
361 p_description => p_description,
362 p_dynamic_description => p_dynamic_description,
363 p_description_query => p_description_query,
364 p_action_type_id => l_action_type_id,
365 p_object_version_number => l_aty_object_version_number,
366 p_start_date => l_aty_start_date,
367 p_end_date => l_aty_end_date
368 );
369 -- Insert data into TL Table
370 ame_ayl_ins.ins_tl(p_language_code => l_language_code,
371 p_action_type_id => l_action_type_id,
372 p_user_action_type_name => p_name,
373 p_description => p_description
374 );
375 -- Call after process user hook
376 begin
377 ame_action_bk1.create_ame_action_type_a
378 (p_language_code => p_language_code
379 ,p_name => p_name
380 ,p_procedure_name => p_procedure_name
381 ,p_description => p_description
382 ,p_dynamic_description => p_dynamic_description
383 ,p_description_query => p_description_query
384 ,p_action_type_id => l_action_type_id
385 ,p_object_version_number => l_aty_object_version_number
386 ,p_start_date => l_aty_start_date
387 ,p_end_date => l_aty_end_date
388 );
389 exception
390 when hr_api.cannot_find_prog_unit then
391 hr_api.cannot_find_prog_unit_error
392 (p_module_name => 'create_ame_action_type'
393 ,p_hook_type => 'AP'
394 );
395 end;
396 -- insert the row in ame_action_type_usages
397 -- Call DBMS_UTILITY.FORMAT_CALL_STACK to check if the call has been
398 -- made from the 'AME_ACTION_SWI' package.
399 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,'AME_ACTION_SWI') = 0) then
400 create_ame_appr_type_usage(p_validate => p_validate,
401 p_action_type_id => l_action_type_id,
402 p_approver_type_id => p_approver_type_id,
403 p_object_version_number => l_apu_object_version_number,
404 p_start_date => l_apu_start_date,
405 p_end_date => l_apu_end_date);
406 create_ame_action_type_usage(p_validate => p_validate,
407 p_action_type_id => l_action_type_id,
408 p_rule_type => p_rule_type,
409 p_object_version_number => l_axu_object_version_number,
410 p_start_date => l_axu_start_date,
411 p_end_date => l_axu_end_date);
412 /*
413 create_ame_action_type_config(p_validate => p_validate,
414 p_action_type_id => l_action_type_id,
415 p_application_id => p_application_id,
416 p_rule_type => p_rule_type,
417 p_object_version_number => l_acf_object_version_number,
418 p_start_date => l_acf_start_date,
419 p_end_date => l_acf_end_date);
420 */
421 end if;
422 --
423 p_action_type_id := l_action_type_id;
424 p_aty_object_version_number := l_aty_object_version_number;
425 p_aty_start_date := l_aty_start_date;
426 p_aty_end_date := l_aty_end_date;
427 p_apu_object_version_number := l_apu_object_version_number;
428 p_apu_start_date := l_apu_start_date;
429 p_apu_end_date := l_apu_end_date;
430 --
431 hr_utility.set_location(' Leaving:'||l_proc, 50);
432 exception
433 when hr_api.validate_enabled then
434 --
435 -- As the Validate_Enabled exception has been raised
436 -- we must rollback to the savepoint
437 --
438 ROLLBACK TO create_ame_action_type;
439 --
440 -- Only set output warning arguments
441 -- (Any key or derived arguments must be set to null
442 -- when validation only mode is being used.)
443 --
444 p_action_type_id := null;
445 p_aty_object_version_number := null;
446 p_aty_start_date := null;
447 p_aty_end_date := null;
448 p_apu_object_version_number := null;
449 p_apu_start_date := null;
450 p_apu_end_date := null;
451 --
452 hr_utility.set_location(' Leaving:'||l_proc, 60);
453 --
454 when others then
455 --
456 -- A validation or unexpected error has occurred
457 --
458 p_action_type_id := null;
459 p_aty_object_version_number := null;
460 p_aty_start_date := null;
461 p_aty_end_date := null;
462 p_apu_object_version_number := null;
463 p_apu_start_date := null;
464 p_apu_end_date := null;
465 --
466 hr_utility.set_location(' Leaving:'||l_proc, 70);
467 ROLLBACK TO create_ame_action_type;
468 raise;
469 --
470 end create_ame_action_type;
471 -- ----------------------------------------------------------------------------
472 -- |-------------------< create_ame_action_type_usage >------------------------|
473 -- ----------------------------------------------------------------------------
474 --
475 procedure create_ame_action_type_usage
476 (p_validate in boolean default false
477 ,p_action_type_id in number
478 ,p_rule_type in number
479 ,p_object_version_number out nocopy number
480 ,p_start_date out nocopy date
481 ,p_end_date out nocopy date
482 ) is
483 -- local variables
484 l_proc varchar2(72) := g_package||'create_ame_action_type_usage';
485 l_effective_date date;
486 l_object_version_number number;
487 l_start_date date;
488 l_end_date date;
489 l_approver_count number;
490 l_rule_type number;
491 --
492 begin
493 hr_utility.set_location('Entering:'|| l_proc, 10);
494 --
495 -- Set the effective date to the sysdate
496 l_effective_date := sysdate;
497 -- Issue a savepoint.
498 --
499 savepoint create_ame_action_type_usage;
500 --
501 /* if(p_rule_type = ame_util.exceptionRuleType) then
502 -- chain of authority so insert two rows,
503 -- one for list-creation and one for list-exception
504 -- isthis loop required?
505 for i in 1 .. 2 loop
506 -- Call Before Process User Hook
507 begin
508 ame_action_bk13.create_ame_action_type_usage_b
509 (p_action_type_id => p_action_type_id
510 ,p_rule_type => i
511 );
512 exception
513 when hr_api.cannot_find_prog_unit then
514 hr_api.cannot_find_prog_unit_error
515 (p_module_name => 'create_ame_action_type_usage'
516 ,p_hook_type => 'BP'
517 );
518 end;
519 ame_axu_ins.ins(p_action_type_id => p_action_type_id,
520 p_effective_date => l_effective_date ,
521 p_rule_type => i,
522 p_object_version_number => l_object_version_number,
523 p_start_date => l_start_date,
524 p_end_date => l_end_date
525 );
526 -- Call After Process User Hook
527 begin
528 ame_action_bk13.create_ame_action_type_usage_a
529 (p_action_type_id => p_action_type_id
530 ,p_rule_type => i
531 ,p_object_version_number => l_object_version_number
532 ,p_start_date => l_start_date
533 ,p_end_date => l_end_date
534 );
535 exception
536 when hr_api.cannot_find_prog_unit then
537 hr_api.cannot_find_prog_unit_error
538 (p_module_name => 'create_ame_action_type_usage'
539 ,p_hook_type => 'AP'
540 );
541 end;
542 end loop;
543 else*/
544 -- no b4 user hook
545 if(p_rule_type = ame_util.exceptionRuleType) then
546 l_rule_type := p_rule_type-1;
547 else
548 l_rule_type := p_rule_type;
549 end if;
550
551 -- Call Before Process User Hook
552 begin
553 ame_action_bk13.create_ame_action_type_usage_b
554 (p_action_type_id => p_action_type_id
555 ,p_rule_type => l_rule_type
556 );
557 exception
558 when hr_api.cannot_find_prog_unit then
559 hr_api.cannot_find_prog_unit_error
560 (p_module_name => 'create_ame_action_type_usage'
561 ,p_hook_type => 'BP'
562 );
563 end;
564
565
566
567 ame_axu_ins.ins(p_action_type_id => p_action_type_id,
568 p_effective_date => l_effective_date,
569 p_rule_type => l_rule_type,
570 p_object_version_number => l_object_version_number,
571 p_start_date => l_start_date,
572 p_end_date => l_end_date
573 );
574 -- Call After Process User Hook
575 begin
576 ame_action_bk13.create_ame_action_type_usage_a
577 (p_action_type_id => p_action_type_id
578 ,p_rule_type => l_rule_type
579 ,p_object_version_number => l_object_version_number
580 ,p_start_date => l_start_date
581 ,p_end_date => l_end_date
582 );
583 exception
584 when hr_api.cannot_find_prog_unit then
585 hr_api.cannot_find_prog_unit_error
586 (p_module_name => 'create_ame_action_type_usage'
587 ,p_hook_type => 'AP'
588 );
589 end;
590 -- end if;
591 -- When in validation only mode raise the Validate_Enabled exception
592 --
593 if p_validate then
594 raise hr_api.validate_enabled;
595 end if;
596 --
597 p_object_version_number := l_object_version_number;
598 p_start_date := l_start_date;
599 p_end_date := l_end_date;
600 --
601 hr_utility.set_location(' Leaving:'||l_proc, 50);
602 exception
603 when hr_api.validate_enabled then
604 --
605 -- As the Validate_Enabled exception has been raised
606 -- we must rollback to the savepoint
607 --
608 ROLLBACK TO create_ame_action_type_usage;
609 --
610 -- Only set output warning arguments
611 -- (Any key or derived arguments must be set to null
612 -- when validation only mode is being used.)
613 --
614 p_object_version_number := null;
615 p_start_date := null;
616 p_end_date := null;
617 --
618 hr_utility.set_location(' Leaving:'||l_proc, 60);
619 --
620 when others then
621 --
622 -- A validation or unexpected error has occurred
623 --
624 p_object_version_number := null;
625 p_start_date := null;
626 p_end_date := null;
627 --
628 hr_utility.set_location(' Leaving:'||l_proc, 70);
629 ROLLBACK TO create_ame_action_type_usage;
630 raise;
631 --
632 end create_ame_action_type_usage;
633 -- ----------------------------------------------------------------------------
634 -- |-------------------< create_ame_appr_type_usage >------------------------|
635 -- ----------------------------------------------------------------------------
636 --
637 procedure create_ame_appr_type_usage
638 (p_validate in boolean default false
639 ,p_action_type_id in number
640 ,p_approver_type_id in number
641 ,p_object_version_number out nocopy number
642 ,p_start_date out nocopy date
643 ,p_end_date out nocopy date
644 ) is
645 -- local variables
646 l_proc varchar2(72) := g_package||'create_ame_appr_type_usage';
647 l_effective_date date;
648 l_object_version_number number;
649 l_start_date date;
650 l_end_date date;
651 --
652 begin
653 hr_utility.set_location('Entering:'|| l_proc, 10);
654 --
655 -- Set the effective date to the sysdate
656 l_effective_date := sysdate;
657 -- Issue a savepoint.
658 --
659 savepoint create_ame_appr_type_usage;
660 --
661 -- Call Before Process User Hook
662 begin
663 ame_action_bk2.create_ame_appr_type_usage_b
664 (p_approver_type_id => p_approver_type_id
665 ,p_action_type_id => p_action_type_id
666 );
667 exception
668 when hr_api.cannot_find_prog_unit then
669 hr_api.cannot_find_prog_unit_error
670 (p_module_name => 'create_ame_appr_type_usage'
671 ,p_hook_type => 'BP'
672 );
673 end;
674 --
675 -- Process Logic
676 -- insert the row in ame_approver_type_usages
677 ame_apu_ins.ins(p_approver_type_id => p_approver_type_id
678 ,p_action_type_id => p_action_type_id
679 ,p_effective_date => l_effective_date
680 ,p_object_version_number => l_object_version_number
681 ,p_start_date => l_start_date
682 ,p_end_date => l_end_date
683 );
684 -- Call after process user hook
685 begin
686 ame_action_bk2.create_ame_appr_type_usage_a
687 (p_approver_type_id => p_approver_type_id
688 ,p_action_type_id => p_action_type_id
689 ,p_object_version_number => l_object_version_number
690 ,p_start_date => l_start_date
691 ,p_end_date => l_end_date
692 );
693 exception
694 when hr_api.cannot_find_prog_unit then
695 hr_api.cannot_find_prog_unit_error
696 (p_module_name => 'create_ame_appr_type_usage'
697 ,p_hook_type => 'AP'
698 );
699 end;
700 -- When in validation only mode raise the Validate_Enabled exception
701 hr_utility.set_location(l_proc, 9);
702 --
703 if p_validate then
704 raise hr_api.validate_enabled;
705 end if;
706 --
707 p_object_version_number := l_object_version_number;
708 p_start_date := l_start_date;
709 p_end_date := l_end_date;
710 --
711 hr_utility.set_location(' Leaving:'||l_proc, 50);
712 exception
713 when hr_api.validate_enabled then
714 --
715 -- As the Validate_Enabled exception has been raised
716 -- we must rollback to the savepoint
717 --
718 ROLLBACK TO create_ame_appr_type_usage;
719 --
720 -- Only set output warning arguments
721 -- (Any key or derived arguments must be set to null
722 -- when validation only mode is being used.)
723 --
724 p_object_version_number := null;
725 p_start_date := null;
726 p_end_date := null;
727 --
728 hr_utility.set_location(' Leaving:'||l_proc, 60);
729 --
730 when others then
731 --
732 -- A validation or unexpected error has occurred
733 --
734 p_object_version_number := null;
735 p_start_date := null;
736 p_end_date := null;
737 --
738 hr_utility.set_location(' Leaving:'||l_proc, 70);
739 ROLLBACK TO create_ame_appr_type_usage;
740 raise;
741 --
742 end create_ame_appr_type_usage;
743 -- ----------------------------------------------------------------------------
744 -- |-------------------< create_ame_action_type_conf >------------------------|
745 -- ----------------------------------------------------------------------------
746 --
747 procedure create_ame_action_type_conf
748 (p_validate in boolean default false
749 ,p_action_type_id in number
750 ,p_application_id in number
751 ,p_voting_regime in varchar2 default null
752 ,p_chain_ordering_mode in varchar2 default null
753 ,p_order_number in NUMBER DEFAULT null
754 ,p_object_version_number out nocopy number
755 ,p_start_date out nocopy date
756 ,p_end_date out nocopy date
757 ) is
758 -- local variables
759 l_proc varchar2(72) := g_package||'create_ame_action_type_config';
760 l_effective_date date;
761 l_object_version_number number;
762 l_start_date date;
763 l_end_date date;
764 --
765 begin
766 hr_utility.set_location('Entering:'|| l_proc, 10);
767 --
768 -- Set the effective date to the sysdate
769 l_effective_date := sysdate;
770 -- Issue a savepoint.
771 --
772 savepoint create_ame_action_type_config;
773 -- Call DBMS_UTILITY.FORMAT_CALL_STACK to check if the call has been
774 -- made from the 'AME_ACTION_SWI' package.
775 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,'AME_ACTION_SWI') <> 0) then
776 -- insert the row in ame_action_type_config with default voting regime
777 -- and default chain ordering mode.
778 /* l_voting_regime := ame_util.serializedVoting;
779 l_chain_ordering_mode := ame_util.serialChainsMode;
780 */
781 -- Call Before Process User Hook
782 begin
783 ame_action_bk12.create_ame_action_type_conf_b
784 (p_action_type_id => p_action_type_id
785 ,p_ame_application_id => p_application_id
786 ,p_voting_regime => p_voting_regime
787 ,p_chain_ordering_mode => p_chain_ordering_mode
788 ,p_order_number => p_order_number
789 );
790 exception
791 when hr_api.cannot_find_prog_unit then
792 hr_api.cannot_find_prog_unit_error
793 (p_module_name => 'create_ame_action_type_conf'
794 ,p_hook_type => 'BP'
795 );
796 end;
797 ame_acf_ins.ins(p_effective_date => l_effective_date
798 ,p_order_number => p_order_number
799 ,p_voting_regime => p_voting_regime
800 ,p_chain_ordering_mode => p_chain_ordering_mode
801 ,p_application_id => p_application_id
802 ,p_action_type_id => p_action_type_id
803 ,p_object_version_number => l_object_version_number
804 ,p_start_date => l_start_date
805 ,p_end_date => l_end_date);
806 -- Call After Process User Hook
807 begin
808 ame_action_bk12.create_ame_action_type_conf_a
809 (p_action_type_id => p_action_type_id
810 ,p_ame_application_id => p_application_id
811 ,p_voting_regime => p_voting_regime
812 ,p_chain_ordering_mode => p_chain_ordering_mode
813 ,p_order_number => p_order_number
814 ,p_object_version_number => l_object_version_number
815 ,p_start_date => l_start_date
816 ,p_end_date => l_end_date
817 );
818 exception
819 when hr_api.cannot_find_prog_unit then
820 hr_api.cannot_find_prog_unit_error
821 (p_module_name => 'create_ame_action_type_conf'
822 ,p_hook_type => 'AP'
823 );
824 end;
825 else
826 -- Call Before Process User Hook
827 begin
828 ame_action_bk12.create_ame_action_type_conf_b
829 (p_action_type_id => p_action_type_id
830 ,p_ame_application_id => p_application_id
831 ,p_voting_regime => p_voting_regime
832 ,p_chain_ordering_mode => p_chain_ordering_mode
833 ,p_order_number => p_order_number
834 );
835 exception
836 when hr_api.cannot_find_prog_unit then
837 hr_api.cannot_find_prog_unit_error
838 (p_module_name => 'create_ame_action_type_conf'
839 ,p_hook_type => 'BP'
840 );
841 end;
842 ame_acf_ins.ins(p_effective_date => l_effective_date
843 ,p_order_number => p_order_number
844 ,p_voting_regime => p_voting_regime
845 ,p_chain_ordering_mode => p_chain_ordering_mode
846 ,p_application_id => p_application_id
847 ,p_action_type_id => p_action_type_id
848 ,p_object_version_number => l_object_version_number
849 ,p_start_date => l_start_date
850 ,p_end_date => l_end_date);
851 l_object_version_number := l_object_version_number;
852 l_start_date := l_start_date;
853 l_end_date := l_end_date;
854 -- Call After Process User Hook
855 begin
856 ame_action_bk12.create_ame_action_type_conf_a
857 (p_action_type_id => p_action_type_id
858 ,p_ame_application_id => p_application_id
859 ,p_voting_regime => p_voting_regime
860 ,p_chain_ordering_mode => p_chain_ordering_mode
861 ,p_order_number => p_order_number
862 ,p_object_version_number => l_object_version_number
863 ,p_start_date => l_start_date
864 ,p_end_date => l_end_date
865 );
866 exception
867 when hr_api.cannot_find_prog_unit then
868 hr_api.cannot_find_prog_unit_error
869 (p_module_name => 'create_ame_action_type_conf'
870 ,p_hook_type => 'AP'
871 );
872 end;
873 end if;
874 -- When in validation only mode raise the Validate_Enabled exception
875 --
876 if p_validate then
877 raise hr_api.validate_enabled;
878 end if;
879 --
880 p_object_version_number := l_object_version_number;
881 p_start_date := l_start_date;
882 p_end_date := l_end_date;
883 --
884 hr_utility.set_location(' Leaving:'||l_proc, 50);
885 exception
886 when hr_api.validate_enabled then
887 --
888 -- As the Validate_Enabled exception has been raised
889 -- we must rollback to the savepoint
890 --
891 ROLLBACK TO create_ame_action_type_config;
892 --
893 -- Only set output warning arguments
894 -- (Any key or derived arguments must be set to null
895 -- when validation only mode is being used.)
896 --
897 p_object_version_number := null;
898 p_start_date := null;
899 p_end_date := null;
900 --
901 hr_utility.set_location(' Leaving:'||l_proc, 60);
902 --
903 when others then
904 --
905 -- A validation or unexpected error has occurred
906 --
907 p_object_version_number := null;
908 p_start_date := null;
909 p_end_date := null;
910 --
911 hr_utility.set_location(' Leaving:'||l_proc, 70);
912 ROLLBACK TO create_ame_action_type_config;
913 raise;
914 --
915 end create_ame_action_type_conf;
916 --
917 -- ----------------------------------------------------------------------------
918 -- |-------------------< create_ame_req_attribute >------------------------|
919 -- ----------------------------------------------------------------------------
920 --
921 procedure create_ame_req_attribute
922 (p_validate in boolean default false
923 ,p_action_type_id in number
924 ,p_attribute_id in number
925 ,p_object_version_number out nocopy number
926 ,p_start_date out nocopy date
927 ,p_end_date out nocopy date
928 ) is
929 --cursors
930 cursor C_Sel1 is
931 select axu.application_id
932 from ame_action_type_config axu
933 where axu.action_type_id = p_action_type_id
934 and sysdate between axu.start_date
935 and nvl(axu.end_date - (1/86400), sysdate)
936 and not exists(select null
937 from ame_attribute_usages atu
938 where atu.attribute_id = p_attribute_id
939 and atu.application_id = axu.application_id
940 and sysdate between atu.start_date and
941 nvl(atu.end_date - (1/86400), sysdate)
942 );
943
944 -- local variables
945 l_proc varchar2(72) := g_package||'create_ame_req_attribute';
946 l_effective_date date;
947 l_object_version_number number;
948 l_start_date date;
949 l_end_date date;
950 l_req_object_version_number number;
951 l_req_start_date date;
952 l_req_end_date date;
953 l_attribute_type ame_attributes.attribute_type%TYPE;
954 l_query_string ame_attribute_usages.query_string%TYPE;
955 --
956 begin
957 hr_utility.set_location('Entering:'|| l_proc, 10);
958 --
959 -- Set the effective date to the sysdate
960 l_effective_date := sysdate;
961 -- Issue a savepoint.
962 --
963 savepoint create_ame_req_attribute;
964 -- Call Before Process User Hook
965 begin
966 ame_action_bk3.create_ame_req_attribute_b
967 (p_attribute_id => p_attribute_id
968 ,p_action_type_id => p_action_type_id
969 );
970 exception
971 when hr_api.cannot_find_prog_unit then
972 hr_api.cannot_find_prog_unit_error
973 (p_module_name => 'create_ame_req_attribute'
974 ,p_hook_type => 'BP'
975 );
976 end;
977 --
978 -- Process Logic
979 ame_man_ins.ins(p_action_type_id => p_action_type_id
980 ,p_attribute_id => p_attribute_id
981 ,p_effective_date => l_effective_date
982 ,p_object_version_number => l_object_version_number
983 ,p_start_date => l_start_date
984 ,p_end_date => l_end_date);
985 --now create usage for this required attribute for all
986 --transaction types having config for this action type
987 select attribute_type
988 into l_attribute_type
989 from ame_attributes
990 where attribute_id = p_attribute_id
991 and sysdate between start_date and
992 nvl(end_date - (1/86400), sysdate);
993 l_query_string := null;
994 if(l_attribute_type = 'currency') then
995 l_query_string := '100,USD,Corporate';
996 elsif (l_attribute_type = 'boolean') then
997 l_query_string := 'true';
998 end if;
999 for rec in C_Sel1 loop
1000 ame_attribute_api.create_ame_attribute_usage
1001 (
1002 p_attribute_id => p_attribute_id
1003 ,p_application_id => rec.application_id
1004 ,p_is_static => ame_util.booleanTrue
1005 ,p_query_string => l_query_string
1006 ,p_object_version_number => l_req_object_version_number
1007 ,p_start_date => l_req_start_date
1008 ,p_end_date => l_req_end_date
1009 );
1010
1011 end loop;
1012 -- Call after process user hook
1013 begin
1014 ame_action_bk3.create_ame_req_attribute_a
1015 (p_attribute_id => p_attribute_id
1016 ,p_action_type_id => p_action_type_id
1017 ,p_object_version_number => l_object_version_number
1018 ,p_start_date => l_start_date
1019 ,p_end_date => l_end_date
1020 );
1021 exception
1022 when hr_api.cannot_find_prog_unit then
1023 hr_api.cannot_find_prog_unit_error
1024 (p_module_name => 'create_ame_req_attribute'
1025 ,p_hook_type => 'AP'
1026 );
1027 end;
1028 -- When in validation only mode raise the Validate_Enabled exception
1029 --
1030 if p_validate then
1031 raise hr_api.validate_enabled;
1032 end if;
1033 --
1034 p_object_version_number := l_object_version_number;
1035 p_start_date := l_start_date;
1036 p_end_date := l_end_date;
1037 --
1038 hr_utility.set_location(' Leaving:'||l_proc, 50);
1039 exception
1040 when hr_api.validate_enabled then
1041 --
1042 -- As the Validate_Enabled exception has been raised
1043 -- we must rollback to the savepoint
1044 --
1045 ROLLBACK TO create_ame_req_attribute;
1046 --
1047 -- Only set output warning arguments
1048 -- (Any key or derived arguments must be set to null
1049 -- when validation only mode is being used.)
1050 --
1051 p_object_version_number := null;
1052 p_start_date := null;
1053 p_end_date := null;
1054 --
1055 hr_utility.set_location(' Leaving:'||l_proc, 60);
1056 --
1057 when others then
1058 --
1059 -- A validation or unexpected error has occurred
1060 --
1061 p_object_version_number := null;
1062 p_start_date := null;
1063 p_end_date := null;
1064 --
1065 hr_utility.set_location(' Leaving:'||l_proc, 70);
1066 ROLLBACK TO create_ame_req_attribute;
1067 raise;
1068 --
1069 end create_ame_req_attribute;
1070 --
1071 -- ----------------------------------------------------------------------------
1072 -- |-------------------< create_ame_action >------------------------|
1073 -- ----------------------------------------------------------------------------
1074 --
1075 procedure create_ame_action
1076 (p_validate in boolean default false
1077 ,p_language_code in varchar2 default hr_api.userenv_lang
1078 ,p_action_type_id in number
1079 ,p_parameter in varchar2
1080 ,p_description in varchar2
1081 ,p_parameter_two in varchar2 default null
1082 ,p_action_id out nocopy number
1083 ,p_object_version_number out nocopy number
1084 ,p_start_date out nocopy date
1085 ,p_end_date out nocopy date
1086 ) is
1087 --cursors
1088 cursor C_Sel1 is
1089 select name
1090 from ame_action_types
1091 where action_type_id = p_action_type_id
1092 and sysdate between start_date and
1093 nvl(end_date - (1/86400), sysdate);
1094 cursor C_Sel2 is
1095 select meaning || ': ' || display_name
1096 from wf_roles,
1097 fnd_lookups
1098 where name = p_parameter
1099 and status = 'ACTIVE'
1100 and (expiration_date is null
1101 or sysdate < expiration_date)
1102 and lookup_type = 'FND_WF_ORIG_SYSTEMS'
1103 and lookup_code = orig_system;
1104 -- local variables
1105 l_proc varchar2(72) := g_package||'create_ame_action';
1106 l_action_id number;
1107 l_effective_date date;
1108 l_object_version_number number;
1109 l_start_date date;
1110 l_end_date date;
1111 l_language_code fnd_languages.language_code%TYPE;
1112 l_description ame_actions.description%TYPE;
1113 l_action_type_name ame_action_types.name%TYPE;
1114 l_approver varchar2(100);
1115 --
1116 begin
1117 hr_utility.set_location('Entering:'|| l_proc, 10);
1118 --
1119 -- Validate language code
1120 l_language_code := p_language_code;
1121 hr_api.validate_language_code(p_language_code => l_language_code);
1122 -- Set the effective date to the sysdate
1123 l_effective_date := sysdate;
1124 -- Issue a savepoint.
1125 --
1126 savepoint create_ame_action;
1127 --
1128 -- Call Before Process User Hook
1129 --
1130 begin
1131 ame_action_bk4.create_ame_action_b
1132 (p_language_code => p_language_code
1133 ,p_action_type_id => p_action_type_id
1134 ,p_parameter => p_parameter
1135 ,p_parameter_two => p_parameter_two
1136 ,p_description => p_description
1137 );
1138 exception
1139 when hr_api.cannot_find_prog_unit then
1140 hr_api.cannot_find_prog_unit_error
1141 (p_module_name => 'create_ame_action'
1142 ,p_hook_type => 'BP'
1143 );
1144 end;
1145 --
1146 -- Process Logic
1147 --generate description for substitution action type (seeded)
1148 --change made by srpurani
1149 l_description := p_description;
1150 open C_Sel1;
1151 fetch C_Sel1 into l_action_type_name;
1152 close C_Sel1;
1153 if(l_action_type_name = 'substitution') then
1154 open C_Sel2;
1155 fetch C_Sel2 into l_approver;
1156 if(C_Sel2 % NOTFOUND) then
1157 l_approver := 'Invalid approver';
1158 end if;
1159 close C_Sel2;
1160 fnd_message.set_name('PER', 'AME_400616_SUB_ACT_DESC');
1161 fnd_message.set_token('APPROVER', l_approver);
1162 l_description := fnd_message.get;
1163 end if;
1164 -- insert the row in ame_actions
1165 ame_act_ins.ins(p_effective_date => l_effective_date,
1166 p_action_type_id => p_action_type_id,
1167 p_parameter => p_parameter,
1168 p_parameter_two => p_parameter_two,
1169 p_description => l_description,
1170 p_action_id => l_action_id,
1171 p_object_version_number => l_object_version_number,
1172 p_start_date => l_start_date,
1173 p_end_date => l_end_date
1174 );
1175 -- insert data into TL tables
1176 ame_acl_ins.ins_tl(p_language_code => l_language_code
1177 ,p_action_id => l_action_id
1178 ,p_description => l_description
1179 );
1180 -- Call after process user hook
1181 begin
1182 ame_action_bk4.create_ame_action_a
1183 (p_language_code => p_language_code
1184 ,p_action_type_id => p_action_type_id
1185 ,p_parameter => p_parameter
1186 ,p_parameter_two => p_parameter_two
1187 ,p_description => p_description
1188 ,p_action_id => l_action_id
1189 ,p_object_version_number => l_object_version_number
1190 ,p_start_date => l_start_date
1191 ,p_end_date => l_end_date
1192 );
1193 exception
1194 when hr_api.cannot_find_prog_unit then
1195 hr_api.cannot_find_prog_unit_error
1196 (p_module_name => 'create_ame_action'
1197 ,p_hook_type => 'AP'
1198 );
1199 end;
1200 p_action_id := l_action_id;
1201 p_object_version_number := l_object_version_number;
1202 p_start_date := l_start_date;
1203 p_end_date := l_end_date;
1204 --
1205 hr_utility.set_location(' Leaving:'||l_proc, 50);
1206 exception
1207 when hr_api.validate_enabled then
1208 --
1209 -- As the Validate_Enabled exception has been raised
1210 -- we must rollback to the savepoint
1211 --
1212 ROLLBACK TO create_ame_action;
1213 --
1214 -- Only set output warning arguments
1215 -- (Any key or derived arguments must be set to null
1216 -- when validation only mode is being used.)
1217 --
1218 p_action_id := null;
1219 p_object_version_number := null;
1220 p_start_date := null;
1221 p_end_date := null;
1222 --
1223 hr_utility.set_location(' Leaving:'||l_proc, 60);
1224 --
1225 when others then
1226 --
1227 -- A validation or unexpected error has occurred
1228 --
1229 p_action_id := null;
1230 p_object_version_number := null;
1231 p_start_date := null;
1232 p_end_date := null;
1233 --
1234 hr_utility.set_location(' Leaving:'||l_proc, 70);
1235 ROLLBACK TO create_ame_action;
1236 raise;
1237 --
1238 end create_ame_action;
1239 --
1240 -- ----------------------------------------------------------------------------
1241 -- |----------------------------< update_ame_action_type_>-----------------|
1242 -- ----------------------------------------------------------------------------
1243 --
1244 procedure update_ame_action_type
1245 (p_validate in boolean default false,
1246 p_language_code in varchar2 default hr_api.userenv_lang,
1247 p_action_type_id in number,
1248 p_procedure_name in varchar2 default hr_api.g_varchar2,
1249 p_description in varchar2 default hr_api.g_varchar2,
1250 p_description_query in varchar2 default hr_api.g_varchar2,
1251 p_object_version_number in out nocopy number,
1252 p_start_date out nocopy date,
1253 p_end_date out nocopy date
1254 ) is
1255 --
1256 -- Local variables
1257 --
1258 l_proc varchar2(72) := g_package||'update_ame_action_type';
1259 l_effective_date date;
1260 l_object_version_number number;
1261 l_start_date date;
1262 l_end_date date;
1263 l_language_code fnd_languages.language_code%TYPE;
1264 --
1265 begin
1266 hr_utility.set_location('Entering:'|| l_proc, 10);
1267 --
1268 -- Validate language code
1269 l_language_code := p_language_code;
1270 hr_api.validate_language_code(p_language_code => l_language_code);
1271 -- Set the effective date to the sysdate
1272 l_effective_date := sysdate;
1273 l_object_version_number := p_object_version_number;
1274 -- Issue a savepoint.
1275 --
1276 savepoint update_ame_action_type;
1277 --
1278 -- Call Before Process User Hook
1279 begin
1280 ame_action_bk5.update_ame_action_type_b
1281 (p_language_code => p_language_code
1282 ,p_action_type_id => p_action_type_id
1283 ,p_procedure_name => p_procedure_name
1284 ,p_description => p_description
1285 ,p_description_query => p_description_query
1286 ,p_object_version_number => l_object_version_number
1287 );
1288 exception
1289 when hr_api.cannot_find_prog_unit then
1290 hr_api.cannot_find_prog_unit_error
1291 (p_module_name => 'update_ame_action_type'
1292 ,p_hook_type => 'BP'
1293 );
1294 end;
1295 -- Update action type.
1296 ame_aty_upd.upd(p_effective_date => l_effective_date,
1297 p_datetrack_mode => hr_api.g_update,
1298 p_action_type_id => p_action_type_id,
1299 p_object_version_number => l_object_version_number,
1300 p_procedure_name => p_procedure_name,
1301 p_description => p_description,
1302 p_description_query => p_description_query,
1303 p_start_date => l_start_date,
1304 p_end_date => l_end_date);
1305 -- Update TL Table
1306 ame_ayl_upd.upd_tl(p_language_code => p_language_code,
1307 p_action_type_id => p_action_type_id,
1308 p_description => p_description);
1309 -- Call After Process User Hook
1310 begin
1311 ame_action_bk5.update_ame_action_type_a
1312 (p_language_code => p_language_code
1313 ,p_action_type_id => p_action_type_id
1314 ,p_procedure_name => p_procedure_name
1315 ,p_description => p_description
1316 ,p_description_query => p_description_query
1317 ,p_object_version_number => l_object_version_number
1318 ,p_start_date => l_start_date
1319 ,p_end_date => l_end_date
1320 );
1321 exception
1322 when hr_api.cannot_find_prog_unit then
1323 hr_api.cannot_find_prog_unit_error
1324 (p_module_name => 'update_ame_action_type'
1325 ,p_hook_type => 'AP'
1326 );
1327 end;
1328 --
1329 -- When in validation only mode raise the Validate_Enabled exception
1330 --
1331 if p_validate then
1332 raise hr_api.validate_enabled;
1333 end if;
1334 --
1335 p_object_version_number := l_object_version_number;
1336 p_start_date := l_start_date;
1337 p_end_date := l_end_date;
1338 hr_utility.set_location(' Leaving:'||l_proc, 50);
1339 exception
1340 when hr_api.validate_enabled then
1341 --
1342 -- As the Validate_Enabled exception has been raised
1343 -- we must rollback to the savepoint
1344 --
1345 ROLLBACK TO update_ame_action_type;
1346 --
1347 -- Only set output warning arguments
1348 -- (Any key or derived arguments must be set to null
1349 -- when validation only mode is being used.)
1350 --
1351 p_object_version_number := null;
1352 p_start_date := null;
1353 p_end_date := null;
1354 --
1355 hr_utility.set_location(' Leaving:'||l_proc, 60);
1356 when others then
1357 --
1358 -- A validation or unexpected error has occurred
1359 --
1360 --
1361 p_object_version_number := null;
1362 p_start_date := null;
1363 p_end_date := null;
1364 --
1365 hr_utility.set_location(' Leaving:'||l_proc, 70);
1366 ROLLBACK TO update_ame_action_type;
1367 raise;
1368 --
1369 end update_ame_action_type;
1370 --
1371 -- ----------------------------------------------------------------------------
1372 -- |----------------------------< update_ame_action_type_conf>-----------------|
1373 -- ----------------------------------------------------------------------------
1374 --
1375 procedure update_ame_action_type_conf
1376 (p_validate in boolean default false,
1377 p_action_type_id in number,
1378 p_application_id in number,
1379 p_voting_regime in varchar2 default hr_api.g_varchar2,
1380 p_chain_ordering_mode in varchar2 default hr_api.g_varchar2,
1381 p_order_number in number default hr_api.g_number,
1382 p_object_version_number in out nocopy number,
1383 p_start_date out nocopy date,
1384 p_end_date out nocopy date
1385 ) is
1386 --
1387 -- Declare cursors and local variables
1388 --
1389 l_proc varchar2(72) := g_package||'update_ame_action_type_conf';
1390 l_effective_date date;
1391 l_object_version_number number;
1392 l_start_date date;
1393 l_end_date date;
1394 l_max_order_number number;
1395 l_new_order_number number;
1396 l_old_order_number number;
1397 l_old_order_number_unique varchar2(3);
1398 l_update_only_at_modified boolean;
1399 --
1400 begin
1401 hr_utility.set_location('Entering:'|| l_proc, 10);
1402 --
1403 -- Set the effective date to the sysdate
1404 l_effective_date := sysdate;
1405 l_object_version_number := p_object_version_number;
1406 -- Issue a savepoint.
1407 --
1408 savepoint update_ame_action_type_conf;
1409 --
1410 -- Call Before Process User Hook
1411 begin
1412 ame_action_bk6.update_ame_action_type_conf_b
1413 (p_action_type_id => p_action_type_id
1414 ,p_ame_application_id => p_application_id
1415 ,p_voting_regime => p_voting_regime
1416 ,p_order_number => p_order_number
1417 ,p_chain_ordering_mode => p_chain_ordering_mode
1418 ,p_object_version_number => l_object_version_number
1419 );
1420 exception
1421 when hr_api.cannot_find_prog_unit then
1422 hr_api.cannot_find_prog_unit_error
1423 (p_module_name => 'update_ame_action_type_conf'
1424 ,p_hook_type => 'BP'
1425 );
1426 end;
1427
1428 -- Update action type config details
1429 ame_acf_upd.upd(p_effective_date => l_effective_date
1430 ,p_datetrack_mode => hr_api.g_update
1431 ,p_application_id => p_application_id
1432 ,p_action_type_id => p_action_type_id
1433 ,p_voting_regime => p_voting_regime
1434 ,p_order_number => p_order_number
1435 ,p_chain_ordering_mode => p_chain_ordering_mode
1436 ,p_object_version_number => l_object_version_number
1437 ,p_start_date => l_start_date
1438 ,p_end_date => l_end_date
1439 );
1440
1441 -- Call After Process User Hook
1442 begin
1443 ame_action_bk6.update_ame_action_type_conf_a
1444 (p_action_type_id => p_action_type_id
1445 ,p_ame_application_id => p_application_id
1446 ,p_voting_regime => p_voting_regime
1447 ,p_order_number => p_order_number
1448 ,p_chain_ordering_mode => p_chain_ordering_mode
1449 ,p_object_version_number => l_object_version_number
1450 ,p_start_date => l_start_date
1451 ,p_end_date => l_end_date
1452 );
1453 exception
1454 when hr_api.cannot_find_prog_unit then
1455 hr_api.cannot_find_prog_unit_error
1456 (p_module_name => 'update_ame_action_type_conf'
1457 ,p_hook_type => 'AP'
1458 );
1459 end;
1460 --
1461 -- When in validation only mode raise the Validate_Enabled exception
1462 --
1463 if p_validate then
1464 raise hr_api.validate_enabled;
1465 end if;
1466 --
1467 p_object_version_number := l_object_version_number;
1468 p_start_date := l_start_date;
1469 p_end_date := l_end_date;
1470 hr_utility.set_location(' Leaving:'||l_proc, 50);
1471 exception
1472 when hr_api.validate_enabled then
1473 --
1474 -- As the Validate_Enabled exception has been raised
1475 -- we must rollback to the savepoint
1476 --
1477 ROLLBACK TO update_ame_action_type_conf;
1478 --
1479 -- Only set output warning arguments
1480 -- (Any key or derived arguments must be set to null
1481 -- when validation only mode is being used.)
1482 --
1483 p_object_version_number := null;
1484 p_start_date := null;
1485 p_end_date := null;
1486 --
1487 hr_utility.set_location(' Leaving:'||l_proc, 60);
1488 --
1489 when others then
1490 --
1491 -- A validation or unexpected error has occurred
1492 --
1493 --
1494 p_object_version_number := null;
1495 p_start_date := null;
1496 p_end_date := null;
1497 --
1498 hr_utility.set_location(' Leaving:'||l_proc, 70);
1499 ROLLBACK TO update_ame_action_type_conf;
1500 raise;
1501 --
1502 end update_ame_action_type_conf;
1503 --
1504 -- ----------------------------------------------------------------------------
1505 -- |----------------------------< update_ame_action >-----------------|
1506 -- ----------------------------------------------------------------------------
1507 --
1508 procedure update_ame_action
1509 (p_validate in boolean default false,
1510 p_language_code in varchar2 default hr_api.userenv_lang,
1511 p_action_id in number,
1512 p_action_type_id in number,
1513 p_parameter in varchar2 default hr_api.g_varchar2,
1514 p_parameter_two in varchar2 default hr_api.g_varchar2,
1515 p_description in varchar2 default hr_api.g_varchar2,
1516 p_object_version_number in out nocopy number,
1517 p_start_date out nocopy date,
1518 p_end_date out nocopy date
1519 ) is
1520 --
1521 -- Declare cursors and local variables
1522 --
1523 --cursors
1524 cursor C_Sel1 is
1525 select name
1526 from ame_action_types
1527 where action_type_id = p_action_type_id
1528 and sysdate between start_date and
1529 nvl(end_date - (1/86400), sysdate);
1530 cursor C_Sel2 is
1531 select meaning || ': ' || display_name
1532 from wf_roles,
1533 fnd_lookups
1534 where name = p_parameter
1535 and status = 'ACTIVE'
1536 and (expiration_date is null
1537 or sysdate < expiration_date)
1538 and lookup_type = 'FND_WF_ORIG_SYSTEMS'
1539 and lookup_code = orig_system;
1540 --
1541 l_proc varchar2(72) := g_package||'update_ame_action';
1542 l_effective_date date;
1543 l_object_version_number number;
1544 l_start_date date;
1545 l_end_date date;
1546 l_language_code fnd_languages.language_code%TYPE;
1547 l_description ame_actions.description%TYPE;
1548 l_action_type_name ame_action_types.name%TYPE;
1549 l_approver varchar2(100);
1550 --
1551 begin
1552 hr_utility.set_location('Entering:'|| l_proc, 10);
1553 --
1554 -- Set the effective date to the sysdate
1555 l_effective_date := sysdate;
1556 l_object_version_number := p_object_version_number;
1557 -- Validate language code
1558 l_language_code := p_language_code;
1559 hr_api.validate_language_code(p_language_code => l_language_code);
1560 -- Issue a savepoint.
1561 --
1562 savepoint update_ame_action;
1563 --
1564 -- Call Before Process User Hook
1565 begin
1566 ame_action_bk7.update_ame_action_b
1567 (p_language_code => p_language_code
1568 ,p_action_id => p_action_id
1569 ,p_parameter => p_parameter
1570 ,p_parameter_two => p_parameter_two
1571 ,p_description => p_description
1572 ,p_object_version_number => l_object_version_number
1573 );
1574 exception
1575 when hr_api.cannot_find_prog_unit then
1576 hr_api.cannot_find_prog_unit_error
1577 (p_module_name => 'update_ame_action'
1578 ,p_hook_type => 'BP'
1579 );
1580 end;
1581 -- Update action.
1582 --generate description for substitution action type (seeded)
1583 --change made by srpurani
1584 l_description := p_description;
1585 open C_Sel1;
1586 fetch C_Sel1 into l_action_type_name;
1587 close C_Sel1;
1588 if(l_action_type_name = 'substitution') then
1589 open C_Sel2;
1590 fetch C_Sel2 into l_approver;
1591 if(C_Sel2 % NOTFOUND) then
1592 l_approver := 'Invalid approver';
1593 end if;
1594 close C_Sel2;
1595 fnd_message.set_name('PER', 'AME_400616_SUB_ACT_DESC');
1596 fnd_message.set_token('APPROVER', l_approver);
1597 l_description := fnd_message.get;
1598 end if;
1599 ame_act_upd.upd(p_effective_date => l_effective_date,
1600 p_datetrack_mode => hr_api.g_update,
1601 p_action_id => p_action_id,
1602 p_action_type_id => p_action_type_id,
1603 p_object_version_number => l_object_version_number,
1604 p_parameter => p_parameter,
1605 p_parameter_two => p_parameter_two,
1606 p_description => l_description,
1607 p_start_date => l_start_date,
1608 p_end_date => l_end_date);
1609 -- update TL table
1610 ame_acl_upd.upd_tl(p_language_code => p_language_code,
1611 p_action_id => p_action_id,
1612 p_description => l_description);
1613 -- Call After Process User Hook
1614 begin
1615 ame_action_bk7.update_ame_action_a
1616 (p_language_code => p_language_code
1617 ,p_action_id => p_action_id
1618 ,p_parameter => p_parameter
1619 ,p_parameter_two => p_parameter_two
1620 ,p_description => p_description
1621 ,p_object_version_number => l_object_version_number
1622 ,p_start_date => l_start_date
1623 ,p_end_date => l_end_date
1624 );
1625 exception
1626 when hr_api.cannot_find_prog_unit then
1627 hr_api.cannot_find_prog_unit_error
1628 (p_module_name => 'update_ame_action'
1629 ,p_hook_type => 'AP'
1630 );
1631 end;
1632 --
1633 -- When in validation only mode raise the Validate_Enabled exception
1634 --
1635 if p_validate then
1636 raise hr_api.validate_enabled;
1637 end if;
1638 --
1639 p_object_version_number := l_object_version_number;
1640 p_start_date := l_start_date;
1641 p_end_date := l_end_date;
1642 hr_utility.set_location(' Leaving:'||l_proc, 50);
1643 exception
1644 when hr_api.validate_enabled then
1645 --
1646 -- As the Validate_Enabled exception has been raised
1647 -- we must rollback to the savepoint
1648 --
1649 ROLLBACK TO update_ame_action;
1650 --
1651 -- Only set output warning arguments
1652 -- (Any key or derived arguments must be set to null
1653 -- when validation only mode is being used.)
1654 --
1655 p_object_version_number := null;
1656 p_start_date := null;
1657 p_end_date := null;
1658 --
1659 hr_utility.set_location(' Leaving:'||l_proc, 60);
1660 when others then
1661 --
1662 -- A validation or unexpected error has occurred
1663 --
1664 --
1665 p_object_version_number := null;
1666 p_start_date := null;
1667 p_end_date := null;
1668 --
1669 hr_utility.set_location(' Leaving:'||l_proc, 70);
1670 ROLLBACK TO update_ame_action;
1671 raise;
1672 --
1673 end update_ame_action;
1674 --
1675 -- ----------------------------------------------------------------------------
1676 -- |--------------------< delete_ame_action_type >---------------------------------|
1677 -- ----------------------------------------------------------------------------
1678 --
1679 procedure delete_ame_action_type
1680 (p_validate in boolean default false
1681 ,p_action_type_id in number
1682 ,p_object_version_number in out nocopy number
1683 ,p_start_date out nocopy date
1684 ,p_end_date out nocopy date
1685 ) is
1686 --
1687 cursor req_attribute_cur(p_action_type_id in number,
1688 p_effective_date in date) is
1689 select attribute_id, object_version_number
1690 from ame_mandatory_attributes
1691 where
1692 action_type_id = p_action_type_id
1693 and p_effective_date
1694 between start_date and nvl(end_date - ame_util.oneSecond, p_effective_date);
1695 cursor appr_type_usage_cur(p_action_type_id in number,
1696 p_effective_date in date) is
1697 select approver_type_id, object_version_number
1698 from ame_approver_type_usages
1699 where
1700 action_type_id = p_action_type_id
1701 and p_effective_date
1702 between start_date and nvl(end_date - ame_util.oneSecond, p_effective_date);
1703
1704 cursor action_type_usage_cur(p_action_type_id in number,
1705 p_effective_date in date) is
1706 select rule_type, object_version_number
1707 from ame_action_type_usages
1708 where
1709 action_type_id = p_action_type_id
1710 and p_effective_date
1711 between start_date and nvl(end_date - ame_util.oneSecond, p_effective_date);
1712 cursor action_type_conf_cur(p_action_type_id in number,
1713 p_effective_date in date) is
1714 select count(*)
1715 from ame_action_type_config
1716 where
1717 action_type_id = p_action_type_id
1718 and p_effective_date
1719 between start_date and nvl(end_date - ame_util.oneSecond, p_effective_date);
1720 cursor actions_cur(p_action_type_id in number,
1721 p_effective_date in date) is
1722 select action_id, object_version_number
1723 from ame_actions
1724 where
1725 action_type_id = p_action_type_id
1726 and p_effective_date
1727 between start_date and nvl(end_date - ame_util.oneSecond, p_effective_date);
1728 cursor rule_usage_cur(p_action_type_id in number,
1729 p_effective_date in date) is
1730 select count(*)
1731 from ame_action_usages ruleUsages
1732 ,ame_actions actions
1733 ,ame_action_types actionTypes
1734 where ruleUsages.action_id = actions.action_id
1735 and actions.action_type_id = actionTypes.action_type_id
1736 and actionTypes.action_type_id = p_action_type_id
1737 and p_effective_date
1738 between ruleUsages.start_date and nvl(ruleUsages.end_date - ame_util.oneSecond, p_effective_date)
1739 and p_effective_date
1740 between actions.start_date and nvl(actions.end_date - ame_util.oneSecond, p_effective_date)
1741 and p_effective_date
1742 between actionTypes.start_date and nvl(actionTypes.end_date - ame_util.oneSecond, p_effective_date);
1743 --
1744 l_proc varchar2(72) := g_package||'delete_ame_action_type';
1745 l_effective_date date;
1746 l_man_attribute_ids ame_util.idList;
1747 l_axu_rule_types ame_util.idList;
1748 l_axu_object_version_number number;
1749 l_axu_object_version_numbers ame_util.idList;
1750 l_axu_start_date date;
1751 l_axu_end_date date;
1752 l_man_object_version_number number;
1753 l_man_object_version_numbers ame_util.idList;
1754 l_man_start_date date;
1755 l_man_end_date date;
1756 l_acf_object_version_number number;
1757 l_acf_object_version_numbers ame_util.idList;
1758 l_acf_start_date date;
1759 l_acf_end_date date;
1760 l_application_ids ame_util.idList;
1761 l_aty_object_version_number number;
1762 l_aty_start_date date;
1763 l_aty_end_date date;
1764 l_apu_approver_type_ids ame_util.idList;
1765 l_apu_object_version_number number;
1766 l_apu_object_version_numbers ame_util.idList;
1767 l_apu_start_date date;
1768 l_apu_end_date date;
1769 l_rule_types ame_util.idList;
1770 l_effective_date2 date;
1771 l_config_count number;
1772 l_rule_usage_count number;
1773 --
1774 begin
1775 hr_utility.set_location('Entering:'|| l_proc, 10);
1776 -- Issue a savepoint.
1777 --
1778 -- Set the effective date to the sysdate
1779 l_effective_date := sysdate;
1780 l_aty_object_version_number := p_object_version_number;
1781 savepoint delete_ame_action_type;
1782 --
1783 -- Process Logic
1784 -- Call DBMS_UTILITY.FORMAT_CALL_STACK to check if the call has been made from the 'AME_ACTION_SWI' package.
1785 --if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,'AME_ACTION_SWI') = 0) then
1786 -- Remove action type configs
1787 Open action_type_conf_cur(p_action_type_id => p_action_type_id,
1788 p_effective_date => l_effective_date);
1789 Fetch action_type_conf_cur into l_config_count;
1790 Close action_type_conf_cur;
1791 if(l_config_count <> 0) then
1792 fnd_message.set_name('PER', 'AME_400608_ACT_TYP_CONF_EXISTS');
1793 fnd_message.raise_error;
1794 end if;
1795 open rule_usage_cur(p_action_type_id => p_action_type_id,
1796 p_effective_date => l_effective_date);
1797 fetch rule_usage_cur into l_rule_usage_count;
1798 close rule_usage_cur;
1799 if(l_rule_usage_count <> 0) then
1800 fnd_message.set_name('PER', 'AME_400609_ACT_USG_EXISTS');
1801 fnd_message.raise_error;
1802 end if;
1803 -- Remove action type usages
1804 Open action_type_usage_cur(p_action_type_id => p_action_type_id,
1805 p_effective_date => l_effective_date);
1806 Fetch action_type_usage_cur bulk collect
1807 Into l_rule_types,
1808 l_axu_object_version_numbers;
1809 Close action_type_usage_cur;
1810 for i in 1 .. l_rule_types.count loop
1811 l_axu_object_version_number := l_axu_object_version_numbers(i);
1812 delete_ame_action_type_usage(p_validate => p_validate,
1813 p_action_type_id => p_action_type_id,
1814 p_rule_type => l_rule_types(i),
1815 p_object_version_number => l_axu_object_version_number,
1816 p_start_date => l_axu_start_date,
1817 p_end_date => l_axu_end_date);
1818 end loop;
1819 -- Remove required attributes
1820 Open req_attribute_cur(p_action_type_id => p_action_type_id,
1821 p_effective_date => l_effective_date);
1822 Fetch req_attribute_cur bulk collect
1823 Into l_man_attribute_ids,
1824 l_man_object_version_numbers;
1825 Close req_attribute_cur;
1826 for i in 1 .. l_man_attribute_ids.count loop
1827 l_man_object_version_number := l_man_object_version_numbers(i);
1828 delete_ame_req_attribute(p_validate => p_validate,
1829 p_action_type_id => p_action_type_id,
1830 p_attribute_id => l_man_attribute_ids(i),
1831 p_object_version_number => l_man_object_version_number,
1832 p_start_date => l_man_start_date,
1833 p_end_date => l_man_end_date);
1834 end loop;
1835 -- Remove approver type usages
1836 Open appr_type_usage_cur(p_action_type_id => p_action_type_id,
1837 p_effective_date => l_effective_date);
1838 Fetch appr_type_usage_cur bulk collect
1839 Into l_apu_approver_type_ids,
1840 l_apu_object_version_numbers;
1841 Close appr_type_usage_cur;
1842 for i in 1 .. l_apu_approver_type_ids.count loop
1843 l_apu_object_version_number := l_apu_object_version_numbers(i);
1844 delete_ame_appr_type_usage(p_validate => p_validate,
1845 p_action_type_id => p_action_type_id,
1846 p_approver_type_id => l_apu_approver_type_ids(i),
1847 p_object_version_number => l_apu_object_version_number,
1848 p_start_date => l_apu_start_date,
1849 p_end_date => l_apu_end_date);
1850 end loop;
1851 --Remove actions
1852 Open actions_cur(p_action_type_id => p_action_type_id,
1853 p_effective_date => l_effective_date);
1854 Fetch actions_cur bulk collect
1855 Into l_apu_approver_type_ids,
1856 l_apu_object_version_numbers;
1857 Close actions_cur;
1858 for i in 1 .. l_apu_approver_type_ids.count loop
1859 l_apu_object_version_number := l_apu_object_version_numbers(i);
1860 delete_ame_action(p_validate => p_validate
1861 ,p_action_id => l_apu_approver_type_ids(i)
1862 ,p_action_type_id => p_action_type_id
1863 ,p_object_version_number => l_apu_object_version_number
1864 ,p_start_date => l_apu_start_date
1865 ,p_end_date => l_apu_end_date
1866 );
1867 end loop;
1868 --end if;
1869 -- Call Before Process User Hook
1870 begin
1871 ame_action_bk8.delete_ame_action_type_b
1872 (p_action_type_id => p_action_type_id
1873 ,p_object_version_number => p_object_version_number
1874 );
1875 exception
1876 when hr_api.cannot_find_prog_unit then
1877 hr_api.cannot_find_prog_unit_error
1878 (p_module_name => 'delete_ame_action_type'
1879 ,p_hook_type => 'BP'
1880 );
1881 end;
1882 -- Remove action type
1883 l_effective_date2 := sysdate;
1884 ame_aty_del.del
1885 (p_action_type_id => p_action_type_id,
1886 p_datetrack_mode => hr_api.g_delete,
1887 p_object_version_number => l_aty_object_version_number,
1888 p_effective_date => l_effective_date2,
1889 p_start_date => l_aty_start_date,
1890 p_end_date => l_aty_end_date);
1891 -- Call After Process User Hook
1892 begin
1893 ame_action_bk8.delete_ame_action_type_a
1894 (p_action_type_id => p_action_type_id
1895 ,p_object_version_number => l_aty_object_version_number
1896 ,p_start_date => l_aty_start_date
1897 ,p_end_date => l_aty_end_date
1898 );
1899 exception
1900 when hr_api.cannot_find_prog_unit then
1901 hr_api.cannot_find_prog_unit_error
1902 (p_module_name => 'delete_ame_action_type'
1903 ,p_hook_type => 'AP'
1904 );
1905 end;
1906 if p_validate then
1907 raise hr_api.validate_enabled;
1908 end if;
1909 --
1910 p_object_version_number := l_aty_object_version_number;
1911 p_start_date := l_aty_start_date;
1912 p_end_date := l_aty_end_date;
1913 hr_utility.set_location(' Leaving:'||l_proc, 50);
1914 exception
1915 when hr_api.validate_enabled then
1916 --
1917 -- As the Validate_Enabled exception has been raised
1918 -- we must rollback to the savepoint
1919 --
1920 ROLLBACK TO delete_ame_action_type;
1921 --
1922 p_object_version_number := null;
1923 p_start_date := null;
1924 p_end_date := null;
1925 --
1926 hr_utility.set_location(' Leaving:'||l_proc, 60);
1927 when others then
1928 --
1929 -- A validation or unexpected error has occurred
1930 p_object_version_number := null;
1931 p_start_date := null;
1932 p_end_date := null;
1933 --
1934 hr_utility.set_location(' Leaving:'||l_proc, 70);
1935 ROLLBACK TO delete_ame_action_type;
1936 raise;
1937 --
1938 -- End of fix.
1939 --
1940 end delete_ame_action_type;
1941 --
1942 -- ----------------------------------------------------------------------------
1943 -- |--------------------< delete_ame_action_type_usage >------------------------|
1944 -- ----------------------------------------------------------------------------
1945 --
1946 procedure delete_ame_action_type_usage
1947 (p_validate in boolean default false
1948 ,p_action_type_id in number
1949 ,p_rule_type in number
1950 ,p_object_version_number in out nocopy number
1951 ,p_start_date out nocopy date
1952 ,p_end_date out nocopy date
1953 ) is
1954 --
1955 l_proc varchar2(72) := g_package||'delete_ame_action_type_usage';
1956 l_effective_date date;
1957 l_object_version_number number;
1958 l_start_date date;
1959 l_end_date date;
1960 --
1961 begin
1962 hr_utility.set_location('Entering:'|| l_proc, 10);
1963 --
1964 -- Set the effective date to the sysdate
1965 l_effective_date := sysdate;
1966 l_object_version_number := p_object_version_number;
1967 -- Issue a savepoint.
1968 --
1969 savepoint delete_ame_action_type_usage;
1970 -- Remove action type usage
1971 begin
1972 ame_action_bk15.delete_ame_action_type_usage_b
1973 (p_action_type_id => p_action_type_id
1974 ,p_rule_type => p_rule_type
1975 ,p_object_version_number => l_object_version_number
1976 );
1977 exception
1978 when hr_api.cannot_find_prog_unit then
1979 hr_api.cannot_find_prog_unit_error
1980 (p_module_name => 'delete_ame_action_type_usage'
1981 ,p_hook_type => 'BP'
1982 );
1983 end;
1984 ame_axu_del.del
1985 (p_action_type_id => p_action_type_id,
1986 p_rule_type => p_rule_type,
1987 p_datetrack_mode => hr_api.g_delete,
1988 p_object_version_number => l_object_version_number,
1989 p_effective_date => l_effective_date,
1990 p_start_date => l_start_date,
1991 p_end_date => l_end_date);
1992 -- Call Before Process User Hook
1993 begin
1994 ame_action_bk15.delete_ame_action_type_usage_a
1995 (p_action_type_id => p_action_type_id
1996 ,p_rule_type => p_rule_type
1997 ,p_object_version_number => l_object_version_number
1998 ,p_start_date => l_start_date
1999 ,p_end_date => l_end_date
2000 );
2001 exception
2002 when hr_api.cannot_find_prog_unit then
2003 hr_api.cannot_find_prog_unit_error
2004 (p_module_name => 'delete_ame_action_type_usage'
2005 ,p_hook_type => 'AP'
2006 );
2007 end;
2008 if p_validate then
2009 raise hr_api.validate_enabled;
2010 end if;
2011 --
2012 p_object_version_number := l_object_version_number;
2013 p_start_date := l_start_date;
2014 p_end_date := l_end_date;
2015 hr_utility.set_location(' Leaving:'||l_proc, 50);
2016 exception
2017 when hr_api.validate_enabled then
2018 --
2019 p_object_version_number := null;
2020 p_start_date := null;
2021 p_end_date := null;
2022 --
2023 hr_utility.set_location(' Leaving:'||l_proc, 60);
2024 -- As the Validate_Enabled exception has been raised
2025 -- we must rollback to the savepoint
2026 --
2027 ROLLBACK TO delete_ame_action_type_usage;
2028 --
2029 --
2030 when others then
2031 --
2032 -- A validation or unexpected error has occurred
2033 p_object_version_number := null;
2034 p_start_date := null;
2035 p_end_date := null;
2036 --
2037 hr_utility.set_location(' Leaving:'||l_proc, 70);
2038 ROLLBACK TO delete_ame_action_type_usage;
2039 raise;
2040 --
2041 end delete_ame_action_type_usage;
2042 --
2043 -- ----------------------------------------------------------------------------
2044 -- |--------------------< delete_ame_action_type_conf >----------------------|
2045 -- ----------------------------------------------------------------------------
2046 --
2047 procedure delete_ame_action_type_conf
2048 (p_validate in boolean default false
2049 ,p_action_type_id in number
2050 ,p_application_id in number
2051 ,p_object_version_number in out nocopy number
2052 ,p_start_date out nocopy date
2053 ,p_end_date out nocopy date
2054 ) is
2055 --
2056 l_proc varchar2(72) := g_package||'delete_ame_action_type_conf';
2057 l_effective_date date;
2058 l_object_version_number number;
2059 l_start_date date;
2060 l_end_date date;
2061 l_order_number number;
2062 --
2063 begin
2064 hr_utility.set_location('Entering:'|| l_proc, 10);
2065 --
2066 -- Set the effective date to the sysdate
2067 l_effective_date := sysdate;
2068 l_object_version_number := p_object_version_number;
2069 -- Issue a savepoint.
2070 --
2071 savepoint delete_ame_action_type_conf;
2072 -- Remove action type configuration
2073
2074 -- Call Before Process User Hook
2075 begin
2076 ame_action_bk14.delete_ame_action_type_conf_b
2077 (p_action_type_id => p_action_type_id
2078 ,p_ame_application_id => p_application_id
2079 ,p_object_version_number => l_object_version_number
2080 );
2081 exception
2082 when hr_api.cannot_find_prog_unit then
2083 hr_api.cannot_find_prog_unit_error
2084 (p_module_name => 'delete_ame_action_type_conf'
2085 ,p_hook_type => 'BP'
2086 );
2087 end;
2088 ame_acf_del.del
2089 (p_action_type_id => p_action_type_id,
2090 p_application_id => p_application_id,
2091 p_datetrack_mode => hr_api.g_delete,
2092 p_object_version_number => l_object_version_number,
2093 p_effective_date => l_effective_date,
2094 p_start_date => l_start_date,
2095 p_end_date => l_end_date);
2096 -- Call After Process User Hook
2097 begin
2098 ame_action_bk14.delete_ame_action_type_conf_a
2099 (p_action_type_id => p_action_type_id
2100 ,p_ame_application_id => p_application_id
2101 ,p_object_version_number => l_object_version_number
2102 ,p_start_date => l_start_date
2103 ,p_end_date => l_end_date
2104 );
2105 exception
2106 when hr_api.cannot_find_prog_unit then
2107 hr_api.cannot_find_prog_unit_error
2108 (p_module_name => 'delete_ame_action_type_conf'
2109 ,p_hook_type => 'AP'
2110 );
2111 end;
2112 if p_validate then
2113 raise hr_api.validate_enabled;
2114 end if;
2115 --
2116 p_object_version_number := l_object_version_number;
2117 p_start_date := l_start_date;
2118 p_end_date := l_end_date;
2119 hr_utility.set_location(' Leaving:'||l_proc, 50);
2120 exception
2121 when hr_api.validate_enabled then
2122 --
2123 p_object_version_number := null;
2124 p_start_date := null;
2125 p_end_date := null;
2126 --
2127 hr_utility.set_location(' Leaving:'||l_proc, 60);
2128 -- As the Validate_Enabled exception has been raised
2129 -- we must rollback to the savepoint
2130 --
2131 ROLLBACK TO delete_ame_action_type_conf;
2132 --
2133 --
2134 when others then
2135 --
2136 -- A validation or unexpected error has occurred
2137 p_object_version_number := null;
2138 p_start_date := null;
2139 p_end_date := null;
2140 --
2141 hr_utility.set_location(' Leaving:'||l_proc, 70);
2142 ROLLBACK TO delete_ame_action_type_conf;
2143 raise;
2144 --
2145 end delete_ame_action_type_conf;
2146 -- ----------------------------------------------------------------------------
2147 -- |--------------------< delete_ame_appr_type_usage >------------------------|
2148 -- ----------------------------------------------------------------------------
2149 --
2150 procedure delete_ame_appr_type_usage
2151 (p_validate in boolean default false
2152 ,p_action_type_id in number
2153 ,p_approver_type_id in number
2154 ,p_object_version_number in out nocopy number
2155 ,p_start_date out nocopy date
2156 ,p_end_date out nocopy date
2157 ) is
2158 --
2159 --
2160 l_proc varchar2(72) := g_package||'delete_ame_appr_type_usage';
2161 l_effective_date date;
2162 l_object_version_number number;
2163 l_start_date date;
2164 l_end_date date;
2165 --
2166 begin
2167 hr_utility.set_location('Entering:'|| l_proc, 10);
2168 --
2169 -- Set the effective date to the sysdate
2170 l_effective_date := sysdate;
2171 l_object_version_number := p_object_version_number;
2172 -- Issue a savepoint.
2173 --
2174 savepoint delete_ame_appr_type_usage;
2175 --
2176 -- Call Before Process User Hook
2177 begin
2178 ame_action_bk9.delete_ame_appr_type_usage_b
2179 (p_action_type_id => p_action_type_id
2180 ,p_approver_type_id => p_approver_type_id
2181 ,p_object_version_number => p_object_version_number
2182 );
2183 exception
2184 when hr_api.cannot_find_prog_unit then
2185 hr_api.cannot_find_prog_unit_error
2186 (p_module_name => 'delete_ame_appr_type_usage'
2187 ,p_hook_type => 'BP'
2188 );
2189 end;
2190 hr_utility.set_location(l_proc, 7);
2191 --
2192 ame_apu_del.del
2193 (p_action_type_id => p_action_type_id,
2194 p_approver_type_id => p_approver_type_id,
2195 p_datetrack_mode => hr_api.g_delete,
2196 p_object_version_number => l_object_version_number,
2197 p_effective_date => l_effective_date,
2198 p_start_date => l_start_date,
2199 p_end_date => l_end_date);
2200 -- Call After Process User Hook
2201 begin
2202 ame_action_bk9.delete_ame_appr_type_usage_a
2203 (p_action_type_id => p_action_type_id
2204 ,p_approver_type_id => p_approver_type_id
2205 ,p_object_version_number => l_object_version_number
2206 ,p_start_date => l_start_date
2207 ,p_end_date => l_end_date
2208 );
2209 exception
2210 when hr_api.cannot_find_prog_unit then
2211 hr_api.cannot_find_prog_unit_error
2212 (p_module_name => 'delete_ame_appr_type_usage'
2213 ,p_hook_type => 'AP'
2214 );
2215 end;
2216 if p_validate then
2217 raise hr_api.validate_enabled;
2218 end if;
2219 --
2220 p_object_version_number := l_object_version_number;
2221 p_start_date := l_start_date;
2222 p_end_date := l_end_date;
2223 hr_utility.set_location(' Leaving:'||l_proc, 50);
2224 exception
2225 when hr_api.validate_enabled then
2226 --
2227 p_object_version_number := null;
2228 p_start_date := null;
2229 p_end_date := null;
2230 --
2231 hr_utility.set_location(' Leaving:'||l_proc, 60);
2232 -- As the Validate_Enabled exception has been raised
2233 -- we must rollback to the savepoint
2234 --
2235 ROLLBACK TO delete_ame_appr_type_usage;
2236 --
2237 --
2238 when others then
2239 --
2240 -- A validation or unexpected error has occurred
2241 p_object_version_number := null;
2242 p_start_date := null;
2243 p_end_date := null;
2244 --
2245 hr_utility.set_location(' Leaving:'||l_proc, 70);
2246 ROLLBACK TO delete_ame_appr_type_usage;
2247 raise;
2248 --
2249 end delete_ame_appr_type_usage;
2250 --
2251 -- VERIFY IN HANDLER THAT SEEDED REQUIRED ATTRIBUTES CANNOT BE DELETED!!
2252 -- ----------------------------------------------------------------------------
2253 -- |--------------------< delete_ame_req_attribute >------------------------|
2254 -- ----------------------------------------------------------------------------
2255 --
2256 procedure delete_ame_req_attribute
2257 (p_validate in boolean default false
2258 ,p_action_type_id in number
2259 ,p_attribute_id in number
2260 ,p_object_version_number in out nocopy number
2261 ,p_start_date out nocopy date
2262 ,p_end_date out nocopy date
2263 ) is
2264 --
2265 l_proc varchar2(72) := g_package||'delete_ame_req_attribute';
2266 l_effective_date date;
2267 l_object_version_number number;
2268 l_start_date date;
2269 l_end_date date;
2270 --
2271 begin
2272 hr_utility.set_location('Entering:'|| l_proc, 10);
2273 --
2274 -- Set the effective date to the sysdate
2275 l_effective_date := sysdate;
2276 l_object_version_number := p_object_version_number;
2277 -- Issue a savepoint.
2278 --
2279 savepoint delete_ame_req_attribute;
2280 -- Call Before Process User Hook
2281 begin
2282 ame_action_bk10.delete_ame_req_attribute_b
2283 (p_action_type_id => p_action_type_id
2284 ,p_attribute_id => p_attribute_id
2285 ,p_object_version_number => p_object_version_number
2286 );
2287 exception
2288 when hr_api.cannot_find_prog_unit then
2289 hr_api.cannot_find_prog_unit_error
2290 (p_module_name => 'delete_ame_req_attribute'
2291 ,p_hook_type => 'BP'
2292 );
2293 end;
2294 --
2295 -- Process Logic
2296 ame_man_del.del
2297 (p_action_type_id => p_action_type_id,
2298 p_attribute_id => p_attribute_id,
2299 p_datetrack_mode => hr_api.g_delete,
2300 p_object_version_number => l_object_version_number,
2301 p_effective_date => l_effective_date,
2302 p_start_date => l_start_date,
2303 p_end_date => l_end_date);
2304 -- Call After Process User Hook
2305 begin
2306 ame_action_bk10.delete_ame_req_attribute_a
2307 (p_action_type_id => p_action_type_id
2308 ,p_attribute_id => p_attribute_id
2309 ,p_object_version_number => l_object_version_number
2310 ,p_start_date => l_start_date
2311 ,p_end_date => l_end_date
2312 );
2313 exception
2314 when hr_api.cannot_find_prog_unit then
2315 hr_api.cannot_find_prog_unit_error
2316 (p_module_name => 'delete_ame_req_attribute'
2317 ,p_hook_type => 'AP'
2318 );
2319 end;
2320 --
2321 if p_validate then
2322 raise hr_api.validate_enabled;
2323 end if;
2324 --
2325 p_object_version_number := l_object_version_number;
2326 p_start_date := l_start_date;
2327 p_end_date := l_end_date;
2328 hr_utility.set_location(' Leaving:'||l_proc, 50);
2329 exception
2330 when hr_api.validate_enabled then
2331 --
2332 p_object_version_number := null;
2333 p_start_date := null;
2334 p_end_date := null;
2335 --
2336 hr_utility.set_location(' Leaving:'||l_proc, 60);
2337 -- As the Validate_Enabled exception has been raised
2338 -- we must rollback to the savepoint
2339 --
2340 ROLLBACK TO delete_ame_req_attribute;
2341 --
2342 --
2343 when others then
2344 --
2345 -- A validation or unexpected error has occurred
2346 --
2347 p_object_version_number := null;
2348 p_start_date := null;
2349 p_end_date := null;
2350 --
2351 hr_utility.set_location(' Leaving:'||l_proc, 70);
2352 ROLLBACK TO delete_ame_req_attribute;
2353 raise;
2354 --
2355 end delete_ame_req_attribute;
2356 --
2357 -- ----------------------------------------------------------------------------
2358 -- |--------------------< delete_ame_action >------------------------|
2359 -- ----------------------------------------------------------------------------
2360 --
2361 procedure delete_ame_action
2362 (p_validate in boolean default false
2363 ,p_action_id in number
2364 ,p_action_type_id in number
2365 ,p_object_version_number in out nocopy number
2366 ,p_start_date out nocopy date
2367 ,p_end_date out nocopy date
2368 ) is
2369 --
2370 --
2371 l_proc varchar2(72) := g_package||'delete_ame_action';
2372 l_effective_date date;
2373 l_object_version_number number;
2374 l_start_date date;
2375 l_end_date date;
2376 --
2377 begin
2378 hr_utility.set_location('Entering:'|| l_proc, 10);
2379 --
2380 -- Set the effective date to the sysdate
2381 l_effective_date := sysdate;
2382 l_object_version_number := p_object_version_number;
2383 -- Issue a savepoint.
2384 --
2385 savepoint delete_ame_action;
2386 -- Call Before Process User Hook
2387 begin
2388 ame_action_bk11.delete_ame_action_b
2389 (p_action_id => p_action_id
2390 ,p_object_version_number => p_object_version_number
2391 );
2392 exception
2393 when hr_api.cannot_find_prog_unit then
2394 hr_api.cannot_find_prog_unit_error
2395 (p_module_name => 'delete_ame_action'
2396 ,p_hook_type => 'BP'
2397 );
2398 end;
2399 hr_utility.set_location(l_proc, 7);
2400 -- Process Logic
2401 ame_act_del.del
2402 (p_action_id => p_action_id,
2403 p_action_type_id => p_action_type_id,
2404 p_datetrack_mode => hr_api.g_delete,
2405 p_object_version_number => l_object_version_number,
2406 p_effective_date => l_effective_date,
2407 p_start_date => l_start_date,
2408 p_end_date => l_end_date);
2409 -- Call After Process User Hook
2410 begin
2411 ame_action_bk11.delete_ame_action_a
2412 (p_action_id => p_action_id
2413 ,p_object_version_number => l_object_version_number
2414 ,p_start_date => l_start_date
2415 ,p_end_date => l_end_date
2416 );
2417 exception
2418 when hr_api.cannot_find_prog_unit then
2419 hr_api.cannot_find_prog_unit_error
2420 (p_module_name => 'delete_ame_action'
2421 ,p_hook_type => 'AP'
2422 );
2423 end;
2424 if p_validate then
2425 raise hr_api.validate_enabled;
2426 end if;
2427 --
2428 p_object_version_number := l_object_version_number;
2429 p_start_date := l_start_date;
2430 p_end_date := l_end_date;
2431 hr_utility.set_location(' Leaving:'||l_proc, 50);
2432 exception
2433 when hr_api.validate_enabled then
2434 --
2435 p_object_version_number := null;
2436 p_start_date := null;
2437 p_end_date := null;
2438 --
2439 hr_utility.set_location(' Leaving:'||l_proc, 60);
2440 -- As the Validate_Enabled exception has been raised
2441 -- we must rollback to the savepoint
2442 --
2443 ROLLBACK TO delete_ame_action;
2444 --
2445 --
2446 when others then
2447 --
2448 -- A validation or unexpected error has occurred
2449 p_object_version_number := null;
2450 p_start_date := null;
2451 p_end_date := null;
2452 --
2453 hr_utility.set_location(' Leaving:'||l_proc, 70);
2454 ROLLBACK TO delete_ame_action;
2455 raise;
2456 --
2457 end delete_ame_action;
2458 end ame_action_api;