[Home] [Help]
PACKAGE BODY: APPS.AME_TRANS_TYPE_API
Source
1 Package Body AME_TRANS_TYPE_API as
2 /* $Header: amacaapi.pkb 120.3 2006/10/05 15:43:22 pvelugul noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'AME_TRANS_TYPE_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------< CREATE_AME_TRANSACTION_TYPE >-----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ame_transaction_type
13 (p_validate in boolean default false
14 ,p_language_code in varchar2 default hr_api.userenv_lang
15 ,p_application_name in varchar2
16 ,p_fnd_application_id in number
17 ,p_transaction_type_id in varchar2
18 ,p_application_id out nocopy number
19 ,p_object_version_number out nocopy number
20 ,p_start_date out nocopy date
21 ,p_end_date out nocopy date
22 ) is
23 --
24 -- Declare cursors and local variables
25 --
26 cursor getMandatoryAttributesCur is
27 select ame_attributes.attribute_id,
28 ame_attributes.attribute_type
29 from ame_attributes,
30 ame_mandatory_attributes
31 where ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id
32 and ame_mandatory_attributes.action_type_id = -1
33 and sysdate between ame_attributes.start_date and
34 nvl(ame_attributes.end_date - (1/86400), sysdate)
35 and sysdate between ame_mandatory_attributes.start_date and
36 nvl(ame_mandatory_attributes.end_date - (1/86400), sysdate);
37 cursor getHeaderItemId is
38 select item_class_id
39 from ame_item_classes
40 where name = ame_util.headerItemClassName
41 and sysdate between start_date and
42 nvl(end_date - (1/86400), sysdate);
43 type attribTypeList is table of ame_attributes.attribute_type%type
44 index by binary_integer;
45 l_proc varchar2(72) := g_package||'create_ame_transaction_type';
46 l_swi_pkg_name varchar2(72) := 'AME_TRANS_TYPE_SWI';
47 l_application_id number;
48 l_object_version_number number;
49 l_ovn_child number;
50 l_header_item_class_id number;
51 l_start_date date;
52 l_end_date date;
53 l_start_date_child date;
54 l_end_date_child date;
55 l_attributeIds ame_util.idList;
56 l_attributeTypes attribTypeList;
57 l_queryString varchar2(5);
58 --
59 begin
60 hr_utility.set_location('Entering:'|| l_proc, 10);
61 --
62 -- Issue a savepoint
63 --
64 savepoint create_ame_transaction_type;
65 --
66 -- Remember IN OUT parameter IN values. None here.
67 --
68 -- Call Before Process User Hook
69 --
70 begin
71 ame_trans_type_bk1.create_ame_transaction_type_b
72 (p_application_name => p_application_name
73 ,p_fnd_application_id => p_fnd_application_id
74 ,p_transaction_type_id => p_transaction_type_id
75 );
76 exception
77 when hr_api.cannot_find_prog_unit then
78 hr_api.cannot_find_prog_unit_error(p_module_name => 'create_ame_transaction_type'
79 ,p_hook_type => 'BP'
80 );
81 end;
82 --
83 -- Process Logic
84 --
85 ame_aca_ins.ins(p_effective_date => sysdate
86 ,p_fnd_application_id => p_fnd_application_id
87 ,p_application_name => p_application_name
88 ,p_transaction_type_id => p_transaction_type_id
89 ,p_line_item_id_query => null
90 ,p_security_group_id => null
91 ,p_application_id => l_application_id
92 ,p_object_version_number => l_object_version_number
93 ,p_start_date => l_start_date
94 ,p_end_date => l_end_date
95 );
96 --
97 -- Create TL Data.
98 --
99 ame_cal_ins.ins_tl(p_language_code => p_language_code
100 ,p_application_id => l_application_id
101 ,p_application_name => p_application_name
102 );
103 --
104 -- If the call is NOT made through SWI package,call all auxiliary APIs.
105 --
106 if (instr(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_pkg_name||fnd_global.local_chr(10)) = 0) then
107 --
108 -- Create Header Item Class Usage
109 --
110 open getHeaderItemId;
111 fetch getHeaderItemId into l_header_item_class_id;
112 close getHeaderItemId;
113 ame_item_class_api.create_ame_item_class_usage
114 (p_validate => p_validate
115 ,p_item_id_query => 'select :transactionId from dual'
116 ,p_item_class_order_number => 1
117 ,p_item_class_par_mode => ame_util.serialItems
118 ,p_item_class_sublist_mode => ame_util.serialSublists
119 ,p_application_id => l_application_id
120 ,p_item_class_id => l_header_item_class_id
121 ,p_object_version_number => l_ovn_child
122 ,p_start_date => l_start_date_child
123 ,p_end_date => l_end_date_child
124 );
125 --
126 -- Create ATTRIBUTE USAGEs
127 --
128 open getMandatoryAttributesCur;
129 fetch getMandatoryAttributesCur bulk collect into l_attributeIds,
130 l_attributeTypes;
131 close getMandatoryAttributesCur;
132 for indx in 1..l_attributeIds.count
133 loop
134 if(LOWER(l_attributeTypes(indx)) = 'boolean') then
135 l_queryString := ame_util.booleanAttributeFalse;
136 else
137 l_queryString := null;
138 end if;
139 ame_attribute_api.create_ame_attribute_usage
140 (p_validate => p_validate
141 ,p_attribute_id => l_attributeIds(indx)
142 ,p_application_id => l_application_id
143 ,p_is_static => ame_util.booleanTrue
144 ,p_query_string => l_queryString
145 ,p_object_version_number => l_ovn_child
146 ,p_start_date => l_start_date_child
147 ,p_end_date => l_end_date_child
148 );
149 end loop;
150 --
151 end if;
152 --
153 -- Call After Process User Hook
154 --
155 begin
156 ame_trans_type_bk1.create_ame_transaction_type_a
157 (p_application_name => p_application_name
158 ,p_fnd_application_id => p_fnd_application_id
159 ,p_transaction_type_id => p_transaction_type_id
160 ,p_application_id => l_application_id
161 ,p_object_version_number => l_object_version_number
162 ,p_start_date => l_start_date
163 ,p_end_date => l_end_date
164 );
165 exception
166 when hr_api.cannot_find_prog_unit then
167 hr_api.cannot_find_prog_unit_error(p_module_name => 'create_ame_transaction_type'
168 ,p_hook_type => 'AP'
169 );
170 end;
171 --
172 -- When in validation only mode raise the Validate_Enabled exception
173 --
174 if p_validate then
175 raise hr_api.validate_enabled;
176 end if;
177 --
178 -- Set all IN OUT and OUT parameters with out values
179 --
180 p_application_id := l_application_id;
181 p_object_version_number := l_object_version_number;
182 p_start_date := l_start_date;
183 p_end_date := l_end_date;
184 --
185 hr_utility.set_location(' Leaving:'||l_proc, 70);
186 exception
187 when hr_api.validate_enabled then
188 --
189 -- As the Validate_Enabled exception has been raised
190 -- we must rollback to the savepoint
191 --
192 rollback to create_ame_transaction_type;
193 --
194 -- Reset IN OUT parameters and set OUT parameters
195 -- (Any key or derived arguments must be set to null
196 -- when validation only mode is being used.)
197 p_application_id := null;
198 p_object_version_number := null;
199 p_start_date := null;
200 p_end_date := null;
201 --
202 hr_utility.set_location(' Leaving:'||l_proc, 80);
203 when others then
204 --
205 -- A validation or unexpected error has occured
206 --
207 rollback to create_ame_transaction_type;
208 --
209 -- Reset IN OUT parameters and set all
210 -- OUT parameters, including warnings, to null
211 --
212 p_application_id := null;
213 p_object_version_number := null;
214 p_start_date := null;
215 p_end_date := null;
216 --
217 hr_utility.set_location(' Leaving:'||l_proc, 90);
218 raise;
219 end create_ame_transaction_type;
220 --
221 --
222 -- ----------------------------------------------------------------------------
223 -- |-------------------< UPDATE_AME_TRANSACTION_TYPE >------------------------|
224 -- ----------------------------------------------------------------------------
225 --
226 procedure update_ame_transaction_type
227 (p_validate in boolean default false
228 ,p_language_code in varchar2 default hr_api.userenv_lang
229 ,p_application_name in varchar2 default hr_api.g_varchar2
230 ,p_application_id in number
231 ,p_object_version_number in out nocopy number
232 ,p_start_date out nocopy date
233 ,p_end_date out nocopy date
234 ) is
235 --
236 -- Declare cursors and local variables
237 --
238 l_proc varchar2(72) := g_package||'update_ame_transaction_type';
239 l_object_version_number number;
240 l_start_date date;
241 l_end_date date;
242 --
243 begin
244 hr_utility.set_location('Entering:'|| l_proc, 10);
245 --
246 -- Issue a savepoint
247 --
248 savepoint update_ame_transaction_type;
249 --
250 -- Remember IN OUT parameter IN values
251 --
252 l_object_version_number := p_object_version_number;
253 --
254 -- Call Before Process User Hook
255 --
256 begin
257 ame_trans_type_bk2.update_ame_transaction_type_b
258 (p_application_name => p_application_name
259 ,p_application_id => p_application_id
260 ,p_object_version_number => p_object_version_number
261 );
262 exception
263 when hr_api.cannot_find_prog_unit then
264 hr_api.cannot_find_prog_unit_error(p_module_name => 'update_ame_transaction_type'
265 ,p_hook_type => 'BP'
266 );
267 end;
268 --
269 -- Process Logic
270 --
271 /*ame_aca_upd.upd(p_effective_date => sysdate
272 ,p_datetrack_mode => hr_api.g_update
273 ,p_application_id => p_application_id
274 ,p_object_version_number => p_object_version_number
275 ,p_line_item_id_query => hr_api.g_varchar2
276 ,p_security_group_id => hr_api.g_number
277 ,p_start_date => l_start_date
278 ,p_end_date => l_end_date
279 );*/
280 --
281 -- Call the _TL layer update
282 --
283 ame_cal_upd.upd_tl(p_language_code => p_language_code
284 ,p_application_id => p_application_id
285 ,p_application_name => p_application_name
286 );
287 --
288 -- Call After Process User Hook
289 --
290 begin
291 ame_trans_type_bk2.update_ame_transaction_type_a
292 (p_application_name => p_application_name
293 ,p_application_id => p_application_id
294 ,p_object_version_number => p_object_version_number
295 ,p_start_date => l_start_date
296 ,p_end_date => l_end_date
297 );
298 exception
299 when hr_api.cannot_find_prog_unit then
300 hr_api.cannot_find_prog_unit_error(p_module_name => 'update_ame_transaction_type'
301 ,p_hook_type => 'AP'
302 );
303 end;
304 --
305 -- When in validation only mode raise the Validate_Enabled exception
306 --
307 if p_validate then
308 raise hr_api.validate_enabled;
309 end if;
310 --
311 -- Set all IN OUT and OUT parameters with out values.
312 --
313 p_start_date := l_start_date;
314 p_end_date := l_end_date;
315 --
316 hr_utility.set_location(' Leaving:'||l_proc, 70);
317 exception
318 when hr_api.validate_enabled then
319 --
320 -- As the Validate_Enabled exception has been raised
321 -- we must rollback to the savepoint
322 --
323 rollback to update_ame_transaction_type;
324 --
325 -- Reset IN OUT parameters and set OUT parameters
326 -- (Any key or derived arguments must be set to null
327 -- when validation only mode is being used.)
328 --
329 p_object_version_number := l_object_version_number;
330 p_start_date := null;
331 p_end_date := null;
332 --
333 hr_utility.set_location(' Leaving:'||l_proc, 80);
334 when others then
335 --
336 -- A validation or unexpected error has occured
337 --
338 rollback to update_ame_transaction_type;
339 --
340 -- Reset IN OUT parameters and set all
341 -- OUT parameters, including warnings, to null
342 --
343 p_object_version_number := l_object_version_number;
344 p_start_date := null;
345 p_end_date := null;
346 --
347 hr_utility.set_location(' Leaving:'||l_proc, 90);
348 raise;
349 end update_ame_transaction_type;
350 --
351 --
352 -- ----------------------------------------------------------------------------
353 -- |-------------------< DELETE_AME_TRANSACTION_TYPE >------------------------|
354 -- ----------------------------------------------------------------------------
355 --
356 procedure delete_ame_transaction_type
357 (p_validate in boolean default false
358 ,p_application_id in number
359 ,p_object_version_number in out nocopy number
360 ,p_start_date out nocopy date
361 ,p_end_date out nocopy date
362 ) is
363 --
364 -- Declare cursors and local variables
365 --
366 cursor getRuleUsageCur is
367 select rule_id,
368 start_date,
369 end_date,
370 object_version_number
371 from ame_rule_usages
372 where item_id = p_application_id
373 and ((sysdate between start_date
374 and nvl(end_date - ame_util.oneSecond, sysdate)) or
375 (sysdate < start_date and
376 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
377 cursor getAttributeUsageCur is
378 select atu.attribute_id,
379 atu.object_version_number
380 from ame_attribute_usages atu
381 ,ame_attributes att
382 where application_id = p_application_id
383 and att.attribute_id = atu.attribute_id
384 and sysdate between atu.start_date and
385 nvl(atu.end_date - ame_util.oneSecond, sysdate)
386 and sysdate between att.start_date and
387 nvl(att.end_date - ame_util.oneSecond, sysdate);
388 cursor getItemClassUsageCur is
389 select item_class_id,
390 object_version_number
391 from ame_item_class_usages
392 where application_id = p_application_id
393 and sysdate between start_date and
394 nvl(end_date - ame_util.oneSecond, sysdate);
395 cursor getApprovalGroupConfigCur is
396 select approval_group_id,
397 object_version_number
398 from ame_approval_group_config
399 where application_id = p_application_id
400 and sysdate between start_date and
401 nvl(end_date - ame_util.oneSecond, sysdate);
402 cursor getActionTypesConfigCur is
403 select action_type_id,
404 object_version_number
405 from ame_action_type_config
406 where application_id = p_application_id
407 and sysdate between start_date and
408 nvl(end_date - ame_util.oneSecond, sysdate);
409 cursor getConfigVarsCur is
410 select variable_name,
411 object_version_number
412 from ame_config_vars
413 where application_id = p_application_id
414 and sysdate between start_date and
415 nvl(end_date - ame_util.oneSecond, sysdate);
416 type varsList is table of ame_config_vars.variable_name%type
417 index by binary_integer;
418 l_proc varchar2(72) := g_package||'delete_ame_transaction_type';
419 l_object_version_number number;
420 l_start_date date;
421 l_end_date date;
422 l_start_date_child date;
423 l_end_date_child date;
424 l_ruleIds ame_util.idList;
425 l_ruleStartDates ame_util.dateList;
426 l_ruleEndDates ame_util.dateList;
427 l_attributeIds ame_util.idList;
428 l_itemClassIds ame_util.idList;
429 l_approvalGroupIds ame_util.idList;
430 l_actionTypeIds ame_util.idList;
431 l_configVarsList varsList;
432 l_objectVersionNumbers ame_util.idList;
433 --
434 begin
435 hr_utility.set_location('Entering:'|| l_proc, 10);
436 --
437 -- Issue a savepoint
438 --
439 savepoint delete_ame_transaction_type;
440 --
441 -- Remember IN OUT parameter IN values
442 --
443 l_object_version_number := p_object_version_number;
444 --
445 -- Call Before Process User Hook
446 --
447 begin
448 ame_trans_type_bk3.delete_ame_transaction_type_b
449 (p_application_id => p_application_id
450 ,p_object_version_number => p_object_version_number
451 );
452 exception
453 when hr_api.cannot_find_prog_unit then
454 hr_api.cannot_find_prog_unit_error(p_module_name => 'delete_ame_transaction_type'
455 ,p_hook_type => 'BP'
456 );
457 end;
458 --
459 -- Remove RULE_USAGESs
460 --
461 open getRuleUsageCur;
462 fetch getRuleUsageCur bulk collect into l_ruleIds,
463 l_ruleStartDates,
464 l_ruleEndDates,
465 l_objectVersionNumbers;
466 close getRuleUsageCur;
467 for indx in 1..l_ruleIds.count
468 loop
469 ame_rule_api.delete_ame_rule_usage
470 (p_validate => p_validate
471 ,p_rule_id => l_ruleIds(indx)
472 ,p_application_id => p_application_id
473 ,p_object_version_number => l_objectVersionNumbers(indx)
474 ,p_start_date => l_ruleStartDates(indx)
475 ,p_end_date => l_ruleEndDates(indx)
476 );
477 end loop;
478 --
479 -- Remove ATTRIBUTES_USAGEs
480 --
481 open getAttributeUsageCur;
482 fetch getAttributeUsageCur bulk collect into l_attributeIds,
483 l_objectVersionNumbers;
484 close getAttributeUsageCur;
485 for indx in 1..l_attributeIds.count
486 loop
487 ame_attribute_api.delete_ame_attribute_usage
488 (p_validate => p_validate
489 ,p_attribute_id => l_attributeIds(indx)
490 ,p_application_id => p_application_id
491 ,p_object_version_number => l_objectVersionNumbers(indx)
492 ,p_start_date => l_start_date_child
493 ,p_end_date => l_end_date_child
494 );
495 end loop;
496 --
497 -- Remove ITEM_CLASS_USAGEs
498 --
499 open getItemClassUsageCur;
500 fetch getItemClassUsageCur bulk collect into l_itemClassIds,
501 l_objectVersionNumbers;
502 close getItemClassUsageCur;
503 for indx in 1..l_itemClassIds.count
504 loop
505 ame_item_class_api.delete_ame_item_class_usage
506 (p_validate => p_validate
507 ,p_application_id => p_application_id
508 ,p_item_class_id => l_itemClassIds(indx)
509 ,p_object_version_number => l_objectVersionNumbers(indx)
510 ,p_start_date => l_start_date_child
511 ,p_end_date => l_end_date_child
512 );
513 end loop;
514 --
515 -- Remove APPROVAL_GROUP_CONFIGs
516 --
517 open getApprovalGroupConfigCur;
518 fetch getApprovalGroupConfigCur bulk collect into l_approvalGroupIds,
519 l_objectVersionNumbers;
520 close getApprovalGroupConfigCur;
521 for indx in 1..l_approvalGroupIds.count
522 loop
523 ame_approver_group_api.delete_approver_group_config
524 (p_validate => p_validate
525 ,p_approval_group_id => l_approvalGroupIds(indx)
526 ,p_application_id => p_application_id
527 ,p_object_version_number => l_objectVersionNumbers(indx)
528 ,p_start_date => l_start_date_child
529 ,p_end_date => l_end_date_child
530 );
531 end loop;
532 --
533 -- Remove ACTION_TYPE_CONFIGs
534 --
535 open getActionTypesConfigCur;
536 fetch getActionTypesConfigCur bulk collect into l_actionTypeIds,
537 l_objectVersionNumbers;
538 close getActionTypesConfigCur;
539 for indx in 1..l_actionTypeIds.count
540 loop
541 ame_action_api.delete_ame_action_type_conf
542 (p_validate => p_validate
543 ,p_action_type_id => l_actionTypeIds(indx)
544 ,p_application_id => p_application_id
545 ,p_object_version_number => l_objectVersionNumbers(indx)
546 ,p_start_date => l_start_date_child
547 ,p_end_date => l_end_date_child
548 );
549 end loop;
550 --
551 -- Remove CONFIG_VARS
552 --
553 open getConfigVarsCur;
554 fetch getConfigVarsCur bulk collect into l_configVarsList,
555 l_objectVersionNumbers;
556 close getConfigVarsCur;
557 for indx in 1..l_configVarsList.count
558 loop
559 ame_config_var_api.delete_ame_config_variable
560 (p_validate => p_validate
561 ,p_application_id => p_application_id
562 ,p_variable_name => l_configVarsList(indx)
563 ,p_object_version_number => l_objectVersionNumbers(indx)
564 ,p_start_date => l_start_date_child
565 ,p_end_date => l_end_date_child
566 );
567 end loop;
568 --
569 -- Remove saved Test cases and associated details.
570 --
571 delete from ame_test_trans_att_values
572 where application_id = p_application_id;
573 delete from ame_test_transactions
574 where application_id = p_application_id;
575 --
576 -- Process Logic
577 --
578 ame_aca_del.del(p_effective_date => sysdate
579 ,p_datetrack_mode => hr_api.g_delete
580 ,p_application_id => p_application_id
581 ,p_object_version_number => p_object_version_number
582 ,p_start_date => l_start_date
583 ,p_end_date => l_end_date
584 );
585 --
586 -- Call After Process User Hook
587 --
588 begin
589 ame_trans_type_bk3.delete_ame_transaction_type_a
590 (p_application_id => p_application_id
591 ,p_object_version_number => p_object_version_number
592 ,p_start_date => l_start_date
593 ,p_end_date => l_end_date
594 );
595 exception
596 when hr_api.cannot_find_prog_unit then
597 hr_api.cannot_find_prog_unit_error(p_module_name => 'delete_ame_transaction_type'
598 ,p_hook_type => 'AP'
599 );
600 end;
601 --
602 -- When in validation only mode raise the Validate_Enabled exception
603 --
604 if p_validate then
605 raise hr_api.validate_enabled;
606 end if;
607 --
608 -- Set all IN OUT and OUT parameters with out values.
609 --
610 p_start_date := l_start_date;
611 p_end_date := l_end_date;
612 --
613 hr_utility.set_location(' Leaving:'||l_proc, 70);
614 exception
615 when hr_api.validate_enabled then
616 --
617 -- As the Validate_Enabled exception has been raised
618 -- we must rollback to the savepoint
619 --
620 rollback to delete_ame_transaction_type;
621 --
622 -- Reset IN OUT parameters and set OUT parameters
623 -- (Any key or derived arguments must be set to null
624 -- when validation only mode is being used.)
625 --
626 p_object_version_number := l_object_version_number;
627 p_start_date := null;
628 p_end_date := null;
629 --
630 hr_utility.set_location(' Leaving:'||l_proc, 80);
631 when others then
632 --
633 -- A validation or unexpected error has occured
634 --
635 rollback to delete_ame_transaction_type;
636 --
637 -- Reset IN OUT parameters and set all
638 -- OUT parameters, including warnings, to null
639 --
640 p_object_version_number := l_object_version_number;
641 p_start_date := null;
642 p_end_date := null;
643 --
644 hr_utility.set_location(' Leaving:'||l_proc, 90);
645 raise;
646 end delete_ame_transaction_type;
647 end AME_TRANS_TYPE_API;