[Home] [Help]
PACKAGE BODY: APPS.HR_TRANSACTION_API
Source
1 PACKAGE BODY HR_TRANSACTION_API as
2 /* $Header: petrnapi.pkb 120.7 2007/03/30 00:09:44 ashrivas noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_transaction_api.';
7 g_debug boolean := hr_utility.debug_enabled;
8 -- ----------------------------------------------------------------------------
9 -- |----------------------< get_transaction_step_info >-----------------------|
10 -- ----------------------------------------------------------------------------
11 procedure get_transaction_step_info
12 (p_item_type in varchar2
13 ,p_item_key in varchar2
14 ,p_activity_id in number
15 ,p_transaction_step_id out nocopy number
16 ,p_object_version_number out nocopy number) is
17 -- --------------------------------------------------------------------------
18 -- declare local variables
19 -- --------------------------------------------------------------------------
20 l_proc constant varchar2(100) := g_package || ' get_transaction_step_info';
21 --
22 cursor csr_hats is
23 select hats.transaction_step_id
24 ,hats.object_version_number
25 from hr_api_transaction_steps hats
26 where hats.item_type = p_item_type
27 and hats.item_key = p_item_key
28 and hats.activity_id = p_activity_id;
29 --
30 begin
31 g_debug := hr_utility.debug_enabled;
32
33 IF g_debug THEN
34 hr_utility.set_location('Entering:'|| l_proc, 5);
35 END IF;
36
37 -- open the cursor
38 open csr_hats;
39 hr_utility.trace('Going into Fetch after (open csr_hats ): '|| l_proc);
40 fetch csr_hats into p_transaction_step_id, p_object_version_number;
41 if csr_hats%notfound then
42 -- transaction step does not exist
43 p_transaction_step_id := null;
44 p_object_version_number := null;
45 end if;
46 close csr_hats;
47
48 IF g_debug THEN
49 hr_utility.set_location(' Leaving:'||l_proc, 15);
50 END IF;
51
52 end get_transaction_step_info;
53 -- --------------------------------------------------------------------
54 -- ------------------<< get_transaction_step_info >>-------------------
55 -- --------------------------------------------------------------------
56 procedure get_transaction_step_info
57 (p_item_type in varchar2
58 ,p_item_key in varchar2
59 ,p_activity_id in number
60 ,p_transaction_step_id out nocopy hr_util_web.g_varchar2_tab_type
61 ,p_object_version_number out nocopy hr_util_web.g_varchar2_tab_type
62 ,p_rows out nocopy number) is
63 --
64 -- -------------------------------------------------------------------
65 -- Read transaction step data, sort by the transaction step id and
66 -- object version number
67 -- -------------------------------------------------------------------
68 cursor csr_hats is
69 select hats.transaction_step_id
70 ,hats.object_version_number
71 from hr_api_transaction_steps hats
72 where hats.item_type = p_item_type
73 and hats.item_key = p_item_key
74 and hats.activity_id = p_activity_id
75 order by hats.transaction_step_id, hats.object_version_number;
76 --
77 l_index number;
78 l_data csr_hats%rowtype;
79 l_proc constant varchar2(100) := g_package || ' get_transaction_step_info';
80 --
81 begin
82 hr_utility.set_location('Entering: '|| l_proc,5);
83 g_debug := hr_utility.debug_enabled;
84 l_index := 0;
85 open csr_hats;
86 loop
87 hr_utility.trace('Going into Fetch after (open csr_hats ): '|| l_proc);
88 fetch csr_hats into l_data;
89 exit when csr_hats%notfound;
90 p_transaction_step_id(l_index) := to_char(l_data.transaction_step_id);
91 p_object_version_number(l_index) := to_char(l_data.object_version_number
92 );
93 l_index := l_index + 1;
94 end loop;
95 close csr_hats;
96 p_rows := l_index;
97 hr_utility.set_location('Leaving: '|| l_proc,15);
98 end get_transaction_step_info;
99 -- --------------------------------------------------------------------
100 -- ------------------<< get_transaction_step_info >>-------------------
101 -- --------------------------------------------------------------------
102 procedure get_transaction_step_info
103 (p_item_type in varchar2
104 ,p_item_key in varchar2
105 ,p_transaction_step_id out nocopy hr_util_web.g_varchar2_tab_type
106 ,p_object_version_number out nocopy hr_util_web.g_varchar2_tab_type
107 ,p_rows out nocopy number) is
108 --
109 -- -------------------------------------------------------------------
110 -- Read transaction step data, sort by the transaction step id and
111 -- object version number
112 -- -------------------------------------------------------------------
113 cursor csr_hats is
114 select hats.transaction_step_id
115 ,hats.object_version_number
116 from hr_api_transaction_steps hats
117 where hats.item_type = p_item_type
118 and hats.item_key = p_item_key
119 order by hats.transaction_step_id, hats.object_version_number;
120 --
121 l_index number;
122 l_data csr_hats%rowtype;
123 l_proc constant varchar2(100) := g_package || ' get_transaction_step_info';
124 --
125 BEGIN
126 hr_utility.set_location('Entering: '|| l_proc,5);
127 g_debug := hr_utility.debug_enabled;
128 l_index := 0;
129 open csr_hats;
130 loop
131 hr_utility.trace('Going into Fetch after (open csr_hats ): '|| l_proc);
132 fetch csr_hats into l_data;
133 exit when csr_hats%notfound;
134 p_transaction_step_id(l_index) := to_char(l_data.transaction_step_id);
135 p_object_version_number(l_index) := to_char(l_data.object_version_number);
136 l_index := l_index + 1;
137 end loop;
138 close csr_hats;
139 p_rows := l_index;
140 hr_utility.set_location('Leaving: '|| l_proc,15);
141 end get_transaction_step_info;
142 --
143 -- ----------------------------------------------------------------------------
144 -- |----------------------< get_transaction_step_info >-----------------------|
145 -- ----------------------------------------------------------------------------
146 procedure get_transaction_step_info
147 (p_transaction_step_id in number
148 ,p_item_type out nocopy varchar2
149 ,p_item_key out nocopy varchar2
150 ,p_activity_id out nocopy number) is
151 -- --------------------------------------------------------------------------
152 -- declare local variables
153 -- --------------------------------------------------------------------------
154 l_proc constant varchar2(100) := g_package || ' get_transaction_step_info';
155 --
156 cursor csr_hats is
157 select hats.item_type
158 ,hats.item_key
159 ,hats.activity_id
160 from hr_api_transaction_steps hats
161 where hats.transaction_step_id = p_transaction_step_id;
162 --
163 begin
164 g_debug := hr_utility.debug_enabled;
165
166 IF g_debug THEN
167 -- l_proc := g_package||'get_transaction_step_info';
168 hr_utility.set_location('Entering:'|| l_proc, 5);
169 END IF;
170
171 -- open the cursor
172 open csr_hats;
173 hr_utility.trace('Going into Fetch after (open csr_hats ): '|| l_proc);
174 fetch csr_hats into p_item_type, p_item_key, p_activity_id;
175 if csr_hats%notfound then
176 -- transaction step does not exist
177 p_item_type := null;
178 p_item_key := null;
179 p_activity_id := null;
180 end if;
181 close csr_hats;
182
183 IF g_debug THEN
184 hr_utility.set_location(' Leaving:'||l_proc, 15);
185 END IF;
186
187 end get_transaction_step_info;
188 -- ----------------------------------------------------------------------------
189 -- ------------------<< get_transaction_step_info >>-------------------
190 -- --------------------------------------------------------------------
191 procedure get_transaction_step_info
192 (p_item_type in varchar2
193 ,p_item_key in varchar2
194 ,p_activity_id in number default null
195 ,p_transaction_step_id out nocopy hr_util_web.g_varchar2_tab_type
196 ,p_api_name out nocopy hr_util_web.g_varchar2_tab_type
197 ,p_rows out nocopy number)IS
198 --
199 -- -------------------------------------------------------------------
200 -- Read transaction step data, sort by the transaction step id and
201 -- object version number
202 -- -------------------------------------------------------------------
203 cursor csr_hapi is
204 select hats.transaction_step_id
205 ,hats.api_name
206 from hr_api_transaction_steps hats
207 where hats.item_type = p_item_type
208 and hats.item_key = p_item_key
209 and hats.activity_id = p_activity_id
210 order by hats.transaction_step_id, hats.object_version_number;
211 --
212 cursor csr_hapi_nAct is
213 select hats.transaction_step_id
214 ,hats.api_name
215 from hr_api_transaction_steps hats
216 where hats.item_type = p_item_type
217 and hats.item_key = p_item_key
218 order by hats.transaction_step_id, hats.object_version_number;
219 --
220
221 l_index number;
222 l_data csr_hapi%rowtype;
223 l_cursor varchar2(20);
224 l_proc constant varchar2(100) := g_package || ' get_transaction_step_info ';
225 --
226 begin
227
228 hr_utility.set_location('Entering: '|| l_proc,5);
229 g_debug := hr_utility.debug_enabled;
230 l_index := 0;
231
232 if(p_activity_id is null) then
233 open csr_hapi_nAct;
234 loop
235 hr_utility.trace('Going into Fetch after (open csr_hapi_nAct ): '|| l_proc);
236 fetch csr_hapi_nAct into l_data;
237 exit when csr_hapi_nAct%notfound;
238 p_transaction_step_id(l_index) := to_char(l_data.transaction_step_id);
239 p_api_name(l_index) := l_data.api_name;
240 l_index := l_index + 1;
241 end loop;
242 close csr_hapi_nAct;
243 else
244 open csr_hapi;
245 loop
246 hr_utility.trace('Going into Fetch after (open csr_hapi ): '|| l_proc);
247 fetch csr_hapi into l_data;
248 exit when csr_hapi%notfound;
249 p_transaction_step_id(l_index) := to_char(l_data.transaction_step_id);
250 p_api_name(l_index) := l_data.api_name;
251 l_index := l_index + 1;
252 end loop;
253 close csr_hapi;
254 end if;
255
256 p_rows := l_index;
257
258 hr_utility.set_location('Leaving: '|| l_proc,20);
259 end get_transaction_step_info;
260 -- ----------------------------------------------------------------------------
261 -- |-----------------------< transaction_step_exist >-------------------------|
262 -- ----------------------------------------------------------------------------
263 function transaction_step_exist
264 (p_item_type in varchar2
265 ,p_item_key in varchar2
266 ,p_activity_id in number) return boolean is
267 -- --------------------------------------------------------------------------
268 -- declare local variables
269 -- --------------------------------------------------------------------------
270 l_proc constant varchar2(100) := g_package || ' transaction_step_exist';
271 l_object_version_number hr_api_transaction_steps.object_version_number%type;
272 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
273 l_return boolean := true;
274 --
275 begin
276 g_debug := hr_utility.debug_enabled;
277
278 IF g_debug THEN
279 --l_proc := g_package||'transaction_step_exist';
280 hr_utility.set_location('Entering:'|| l_proc, 5);
281 END IF;
282
283 -- get the transaction step info (if a step exists)
284 hr_transaction_api.get_transaction_step_info
285 (p_item_type => p_item_type
286 ,p_item_key => p_item_key
287 ,p_activity_id => p_activity_id
288 ,p_transaction_step_id => l_transaction_step_id
289 ,p_object_version_number => l_object_version_number);
290 --
291 if l_transaction_step_id is null then
292 -- transaction step does not exist
293 l_return := false;
294 end if;
295 hr_utility.set_location('Leaving: '|| l_proc,10);
296 return(l_return);
297 end transaction_step_exist;
298 -- ----------------------------------------------------------------------------
299 -- |------------------------< get_transaction_id >----------------------------|
300 -- ----------------------------------------------------------------------------
301 function get_transaction_id
302 (p_transaction_step_id in number) return number is
303 -- --------------------------------------------------------------------------
304 -- declare local variables
305 -- --------------------------------------------------------------------------
306 l_proc constant varchar2(100) := g_package || ' get_transaction_id';
307 l_transaction_id hr_api_transactions.transaction_id%type;
308 -- cursor to select the transaction_id of the step
309 cursor csr_hats is
310 select hats.transaction_id
311 from hr_api_transaction_steps hats
312 where hats.transaction_step_id = p_transaction_step_id;
313
314 begin
315
316 IF g_debug THEN
317 -- l_proc := g_package||'get_transaction_id';
318 hr_utility.set_location('Entering:'|| l_proc, 5);
319 END IF;
320
321 open csr_hats;
322 hr_utility.trace('Going into Fetch after (open csr_hats ): '|| l_proc);
323 fetch csr_hats into l_transaction_id;
324 if csr_hats%notfound then
325 -- the transaction step doesn't exist
326 close csr_hats;
327 hr_utility.set_message(801, 'HR_51751_WEB_TRA_STEP_EXISTS');
328 hr_utility.raise_error;
329 end if;
330 close csr_hats;
331 hr_utility.set_location(' Leaving:'||l_proc, 15);
332 return(l_transaction_id);
333
334 IF g_debug THEN
335 hr_utility.set_location(' Leaving:'||l_proc, 20);
336 END IF;
337
338 end get_transaction_id;
339 -- ----------------------------------------------------------------------------
340 -- |-------------------< check_transaction_privilege >------------------------|
341 -- ----------------------------------------------------------------------------
342 procedure check_transaction_privilege
343 (p_transaction_id in number
344 ,p_person_id in number) is
345 -- --------------------------------------------------------------------------
346 -- declare local variables
347 -- --------------------------------------------------------------------------
348 l_proc constant varchar2(100) := g_package || 'check_transaction_privilege';
349 l_creator_person_id hr_api_transactions.creator_person_id%type;
350 l_transaction_privilege hr_api_transactions.transaction_privilege%type;
351 -- cursor to select the privilege
352 cursor csr_hat is
353 select hat.creator_person_id
354 ,hat.transaction_privilege
355 from hr_api_transactions hat
356 where hat.transaction_id = p_transaction_id;
357
358 begin
359
360 IF g_debug THEN
361 -- l_proc := g_package||'check_transaction_privilege';
362 hr_utility.set_location('Entering:'|| l_proc, 5);
363 END IF;
364
365 open csr_hat;
366 hr_utility.trace('Going into Fetch after (open csr_hats ): '|| l_proc);
367 fetch csr_hat into l_creator_person_id, l_transaction_privilege;
368 if csr_hat%notfound then
369 close csr_hat;
370 -- transaction not found
371 hr_utility.set_message(801, 'HR_51752_WEB_TRANSAC_EXISTS');
372 hr_utility.raise_error;
373 end if;
374 close csr_hat;
375 --
376 if (l_transaction_privilege = 'PRIVATEns' and --ns
377 l_creator_person_id <> p_person_id) then
378 -- the transaction is PRIVATE and the person who is attempting to
379 -- create the step is not the creator of the transaction
380 hr_utility.set_message(801, 'HR_51753_WEB_FAIL_CREATE_STEP');
381 hr_utility.raise_error;
382 end if;
383
384 IF g_debug THEN
385 hr_utility.set_location(' Leaving:'||l_proc, 15);
386 END IF;
387
388 end check_transaction_privilege;
389 -- ----------------------------------------------------------------------------
390 -- |---------------------------< create_transaction >-------------------------|
391 -- ----------------------------------------------------------------------------
392 procedure create_transaction
393 (p_validate in boolean default false
394 ,p_creator_person_id in number
395 ,p_transaction_privilege in varchar2
396 ,p_transaction_id out nocopy number) is
397 -- --------------------------------------------------------------------------
398 -- declare local variables
399 -- --------------------------------------------------------------------------
400 l_proc constant varchar2(100) := g_package || ' create_transaction';
401 --
402 begin
403 g_debug := hr_utility.debug_enabled;
404
405 IF g_debug THEN
406 -- l_proc := g_package||'create_transaction';
407 hr_utility.set_location('Entering:'|| l_proc, 5);
408 END IF;
409
410 --
411 -- issue a savepoint if operating in validation only mode.
412 --
413 if p_validate then
414 savepoint create_transaction;
415 end if;
416 -- call the row handler to insert the transaction
417 hr_trn_ins.ins
418 (p_validate => false
419 ,p_transaction_id => p_transaction_id
420 ,p_creator_person_id => p_creator_person_id
421 ,p_transaction_privilege => p_transaction_privilege);
422 --
423 -- when in validation only mode raise the Validate_Enabled exception
424 --
425 if p_validate then
426 raise hr_api.validate_enabled;
427 end if;
428 --
429
430 IF g_debug THEN
431 hr_utility.set_location(' Leaving:'||l_proc, 10);
432 END IF;
433
434 exception
435 when hr_api.validate_enabled then
436
437 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
438 --
439 -- As the Validate_Enabled exception has been raised
440 -- we must rollback to the savepoint
441 --
442 rollback to create_transaction;
443 -- set primary key to null
444 p_transaction_id := null;
445 end create_transaction;
446 --
447 -- ----------------------------------------------------------------------------
448 -- |---------------------------< create_transaction >-------------------------|
449 -- ----------------------------------------------------------------------------
450 procedure create_transaction
451 (p_validate in boolean default false
452 ,p_creator_person_id in number
453 ,p_transaction_privilege in varchar2
454 ,p_product_code in varchar2 default null
455 ,p_url in varchar2 default null
456 ,p_status in varchar2 default null
457 ,p_section_display_name in varchar2 default null
458 ,p_function_id in number
459 ,p_transaction_ref_table in varchar2 default null
460 ,p_transaction_ref_id in number default null
461 ,p_transaction_type in varchar2 default null
462 ,p_assignment_id in number default null
463 ,p_api_addtnl_info in varchar2 default null
464 ,p_selected_person_id in number default null
465 ,p_item_type in varchar2 default null
466 ,p_item_key in varchar2 default null
467 ,p_transaction_effective_date in date default null
468 ,p_process_name in varchar2 default null
469 ,p_plan_id in number default null
470 ,p_rptg_grp_id in number default null
471 ,p_effective_date_option in varchar2 default null
472 ,p_transaction_id out nocopy number) is
473 --
474 -- p_plan_id, p_rptg_grp_id, p_effective_date_option added by sanej
475 -- --------------------------------------------------------------------------
476 -- declare local variables
477 -- --------------------------------------------------------------------------
478 l_proc constant varchar2(100) := g_package || ' create_transaction';
479 url_too_long exception;
480 --
481 begin
482 g_debug := hr_utility.debug_enabled;
483
484 IF g_debug THEN
485 -- l_proc := g_package||'create_transaction';
486 hr_utility.set_location('Entering:'|| l_proc, 5);
487 END IF;
488
489
490 IF p_url is not null and length(p_url) > 4000 THEN
491 raise url_too_long;
492 END IF;
493
494 --
495 -- issue a savepoint if operating in validation only mode.
496 --
497 if p_validate then
498 savepoint create_transaction;
499 end if;
500 -- call the row handler to insert the transaction
501
502
503 hr_trn_ins.ins(
504 p_transaction_id => p_transaction_id,
505 p_creator_person_id => p_creator_person_id,
506 p_transaction_privilege => p_transaction_privilege,
507 p_product_code => p_product_code,
508 p_url => p_url,
509 p_status => p_status,
510 p_section_display_name => p_section_display_name,
511 p_function_id => p_function_id,
512 p_transaction_ref_table => p_transaction_ref_table,
513 p_transaction_ref_id => p_transaction_ref_id,
514 p_transaction_type => p_transaction_type,
515 p_assignment_id => p_assignment_id,
516 p_api_addtnl_info => p_api_addtnl_info,
517 p_selected_person_id => p_selected_person_id,
518 p_item_type => p_item_type,
519 p_item_key => p_item_key,
520 p_transaction_effective_date => p_transaction_effective_date,
521 p_process_name => p_process_name,
522 p_plan_id => p_plan_id,
523 p_rptg_grp_id => p_rptg_grp_id,
524 p_effective_date_option => p_effective_date_option,
525 p_validate => false
526 );
527 --
528 -- p_plan_id, p_rptg_grp_id, p_effective_date_option added by sanej
529
530 --
531 -- when in validation only mode raise the Validate_Enabled exception
532 --
533 if p_validate then
534 raise hr_api.validate_enabled;
535 end if;
536 --
537
538 IF g_debug THEN
539 hr_utility.set_location(' Leaving:'||l_proc, 10);
540 END IF;
541
542 exception
543
544
545 when url_too_long then
546 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
547 p_transaction_id := null;
548 hr_utility.trace(' exception in ' || 'Url too long, it supports only 4000 characters' );
549 when hr_api.validate_enabled then
550
551 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
552 --
553 -- As the Validate_Enabled exception has been raised
554 -- we must rollback to the savepoint
555 --
556 rollback to create_transaction;
557 -- set primary key to null
558 p_transaction_id := null;
559 hr_utility.trace(' exception in ' || sqlerrm);
560 when others then
561
562 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
563 p_transaction_id := null;
564 hr_utility.trace(' exception in ' || sqlerrm);
565 end create_transaction;
566
567 --
568
569 --ns start
570 -- New procedure to accept transaction state and/or date_option as a parameter
571 procedure update_transaction
572 (p_validate in boolean default false
573 ,p_transaction_id in number
574 ,p_status in varchar2 default hr_api.g_varchar2
575 ,p_transaction_state in varchar2 default hr_api.g_varchar2
576 ,p_transaction_effective_date in date default hr_api.g_date
577 ,p_effective_date_option in varchar2 default hr_api.g_varchar2
578 ,p_item_key in varchar2 default hr_api.g_varchar2
579 ) is
580 ------
581 l_proc constant varchar2(100) := g_package || ' update_transaction';
582 begin
583 hr_utility.set_location('Entering: '|| l_proc,5);
584 hr_trn_upd.upd
585 (
586 p_transaction_id => p_transaction_id,
587 p_status => p_status,
588 p_transaction_state => p_transaction_state,
589 p_transaction_effective_date => p_transaction_effective_date,
590 p_effective_date_option => p_effective_date_option,
591 p_item_key => p_item_key
592 );
593 hr_utility.set_location('Leaving: '|| l_proc,10);
594 end update_transaction;
595 --ns end
596
597
598 ------------------------------------------------------------------------
599 ----------------------- Get_Last_Process_Order--------------------------
600 ------------------------------------------------------------------------
601 function Get_Last_Process_Order
602 (p_process_order_str in varchar2)
603 return varchar2 is
604 --
605 l_last_process_order varchar2(100);
606 l_proc constant varchar2(100) := g_package || ' Get_Last_Process_Order';
607 --
608 begin
609 hr_utility.set_location('Entering: '|| l_proc,5);
610 g_debug := hr_utility.debug_enabled;
611 if p_process_order_str is not null then
612 l_last_process_order := rtrim(substr(p_process_order_str, (instr(p_process_order_str, '%', -1,2)) +1),'%' );
613 end if;
614 hr_utility.set_location('Leaving: '|| l_proc,10);
615 return (l_last_process_order);
616
617 exception
618 when others then
619 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
620 return(null);
621 --
622 end Get_Last_Process_Order ;
623 --
624 /*----------------------------------------------------------------------
625 Set_Process_Order_String constructs the string to store activity id with
626 the corresponding processing order concatenated.It's called in Block proc.
627 ----------------------------------------------------------------------*/
628 ------------------------------------------------------------------------
629 ----------------------- Set_Process_Order_String------------------------
630 ------------------------------------------------------------------------
631 procedure Set_Process_Order_String
632 (p_item_type in wf_items.item_type%type
633 ,p_item_key in wf_items.item_key%type
634 ,p_actid in wf_activity_attr_values.process_activity_id%type
635 )
636 is
637 --
638 l_value varchar2(4000);
639 l_last_process_order number;
640 l_process_order_str varchar2(4000);
641 l_counter number := 2;
642 l_dummy number(1);
643 l_proc constant varchar2(100) := g_package || ' Set_Process_Order_String';
644 -- cursor determines if an attribute exists
645 cursor csr_wiav is
646 select 1
647 from wf_item_attribute_values wiav
648 where wiav.item_type = p_item_type
649 and wiav.item_key = p_item_key
650 and wiav.name = 'PROCESS_ORDER_STRING' ;
651
652 --
653 begin
654 hr_utility.set_location('Entering: '|| l_proc,5);
655 g_debug := hr_utility.debug_enabled;
656 -- fix for bug#2112623
657 -- open the cursor to determine if the attribute exists
658 open csr_wiav;
659 hr_utility.trace('Going into Fetch after (open csr_wiav ): '|| l_proc);
660 fetch csr_wiav into l_dummy;
661 if csr_wiav%notfound then
662 l_dummy := -1;
663 end if;
664 close csr_wiav;
665
666 if(l_dummy=-1) then
667 hr_utility.set_location('Leaving: '|| l_proc,10);
668 return ;
669 end if;
670 -- end of bug fix
671
672 --
673 l_process_order_str := wf_engine.GetItemAttrText
674 (itemtype => p_item_type,
675 itemkey => p_item_key,
676 aname =>'PROCESS_ORDER_STRING');
677
678 --
679 if l_process_order_str is null then
680 hr_utility.trace('In (if l_process_order_str is null then): '|| l_proc);
681 l_process_order_str := '%'||to_char(p_actid)||'%1%';
682 wf_engine.SetItemAttrText (itemtype => p_item_type,
683 itemkey => p_item_key,
684 aname => 'PROCESS_ORDER_STRING',
685 avalue => l_process_order_str );
686 else
687 hr_utility.trace('In else of (if l_process_order_str is null then): '|| l_proc);
688 -- Following commented code is replaced by if(instr(l_process_order_str,p_actid)=0) code
689 /*
690 while instr(l_process_order_str,'%', l_counter) <> 0 loop
691 l_value := substr(l_process_order_str,
692 instr(l_process_order_str,'%', 1,l_counter-1) +1,
693 (instr(l_process_order_str,'%',1, l_counter) -
694 instr(l_process_order_str,'%',1, l_counter-1)) -1);
695 if to_char(p_actid) <> l_value then
696 l_counter := l_counter + 2;
697 else
698 exit;
699 end if;
700 end loop;
701 */
702 if(instr(l_process_order_str,p_actid) = 0)then
703 --
704 l_last_process_order := to_number(Get_Last_Process_Order(l_process_order_str))+1;
705 l_value := l_process_order_str ||to_char(p_actid)||'%'||to_char(l_last_process_order)||'%';
706 wf_engine.SetItemAttrText (itemtype => p_item_type,
707 itemkey => p_item_key,
708 aname => 'PROCESS_ORDER_STRING',
709 avalue => l_value );
710 --
711 end if;
712 end if;
713 --
714 exception
715 when others then
716 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
717 null;
718 --
719 end Set_Process_Order_String ;
720 --
721 /*----------------------------------------------------------------------
722 Get_Process_Order get the process order from the string stored in wf
723 attribute PROCESS_ORDER_STRING for the passed in activity
724 id.This string is constructed by Set_Process_Order_String.
725 -----------------------------------------------------------------------*/
726 ------------------------------------------------------------------------
727 ----------------------- Get_Process_Order-------------------------------
728 ------------------------------------------------------------------------
729 function Get_Process_Order
730 (p_item_type in wf_items.item_type%type
731 ,p_item_key in wf_items.item_key%type
732 ,p_actid in wf_activity_attr_values.process_activity_id%type)
733 return varchar2 is
734 --
735 l_value varchar2(100);
736 l_process_order varchar2(100);
737 l_process_order_str varchar2(4000);
738 l_counter number := 2;
739 l_dummy number(1);
740 l_proc constant varchar2(100) := g_package || ' Get_Process_Order';
741 -- cursor determines if an attribute exists
742 cursor csr_wiav is
743 select 1
744 from wf_item_attribute_values wiav
745 where wiav.item_type = p_item_type
746 and wiav.item_key = p_item_key
747 and wiav.name = 'PROCESS_ORDER_STRING' ;
748 --
749 begin
750 hr_utility.set_location('Entering: '|| l_proc,5);
751 g_debug := hr_utility.debug_enabled;
752 -- fix for bug#2112623
753 -- open the cursor to determine if the a
754 open csr_wiav;
755 hr_utility.trace('Going into Fetch after (open csr_wiav ): '|| l_proc);
756 fetch csr_wiav into l_dummy;
757 if csr_wiav%notfound then
758 l_dummy := -1;
759 end if;
760 close csr_wiav;
761
762 if(l_dummy=-1) then
763 hr_utility.set_location('Leaving: '|| l_proc,20);
764 return (null);
765 end if;
766 -- end of bug fix
767
768 l_process_order_str := wf_engine.GetItemAttrText
769 (itemtype => p_item_type,
770 itemkey => p_item_key,
771 aname =>'PROCESS_ORDER_STRING');
772 --hr_utility.trace('Get Process Order--Process Order Str ' || l_process_order_str);
773 if l_process_order_str is null then
774 hr_utility.set_location('Leaving: '|| l_proc,25);
775 return(null);
776 else
777 while instr(l_process_order_str,'%', l_counter) <> 0 loop
778 l_value := substr(l_process_order_str,
779 instr(l_process_order_str,'%',1, l_counter-1) +1,
780 (instr(l_process_order_str,'%',1, l_counter) -
781 instr(l_process_order_str,'%',1, l_counter-1)) -1);
782 --hr_utility.trace('Get Process Order--l_value ' || l_value);
783 if to_char(p_actid) <> l_value then
784 l_counter := l_counter + 2;
785 else
786 l_process_order := substr(l_process_order_str,
787 instr(l_process_order_str,'%',1, l_counter) +1,
788 (instr(l_process_order_str,'%',1, l_counter+1) -
789 instr(l_process_order_str,'%',1, l_counter)) -1);
790 --hr_utility.trace('Get Process Order--Process Order ' || l_process_order);
791 hr_utility.set_location('Leaving: '|| l_proc,30);
792 return(l_process_order);
793 end if;
794 end loop;
795 hr_utility.set_location('Leaving: '|| l_proc,35);
796 return(null);
797 end if;
798 --
799 exception
800 when others then
801
802 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
803 hr_utility.set_message(800, 'HR_NO_PROCESS_ORDER');
804 hr_utility.set_message_token('ACTID', p_actid);
805 hr_utility.raise_error;
806 --
807 end Get_Process_Order ;
808 --
809 -- ----------------------------------------------------------------------------
810 -- |-----------------------< create_trans_step >------------------------|
811 -- ----------------------------------------------------------------------------
812 procedure create_trans_step
813 (p_validate in boolean default false
814 ,p_creator_person_id in number
815 ,p_transaction_id in number
816 ,p_api_name in varchar2
817 ,p_api_display_name in varchar2 default null
818 ,p_item_type in varchar2 default null
819 ,p_item_key in varchar2 default null
820 ,p_activity_id in number default null
821 ,p_processing_order in number default null
822 ,p_transaction_step_id out nocopy number
823 ,p_object_version_number out nocopy number) is
824 -- --------------------------------------------------------------------------
825 -- declare local variables
826 -- --------------------------------------------------------------------------
827 l_proc constant varchar2(100) := g_package || ' create_trans_step';
828 l_processing_order number default null;
829 --
830 begin
831 g_debug := hr_utility.debug_enabled;
832
833 IF g_debug THEN
834 --l_proc := g_package||'create_trans_step';
835 hr_utility.set_location('Entering:'|| l_proc, 5);
836 END IF;
837
838 -- verify that person has transaction privilege
839 check_transaction_privilege
840 (p_transaction_id => p_transaction_id
841 ,p_person_id => p_creator_person_id);
842 --
843 -- issue a savepoint if operating in validation only mode.
844 --
845 if p_validate then
846 savepoint create_trans_step;
847 end if;
848 if p_processing_order is null then
849 l_processing_order := to_number(get_process_order
850 (p_item_type => p_item_type
851 ,p_item_key => p_item_key
852 ,p_actid => p_activity_id));
853 if l_processing_order is null then
854 l_processing_order := 0;
855 end if;
856 end if;
857 -- call the row handler to insert the transaction
858 hr_trs_ins.ins
859 (p_validate => false
860 ,p_transaction_id => p_transaction_id
861 ,p_api_name => p_api_name
862 ,p_api_display_name => p_api_display_name
863 ,p_processing_order => nvl(p_processing_order,l_processing_order)
864 ,p_item_type => p_item_type
865 ,p_item_key => p_item_key
866 ,p_activity_id => p_activity_id
867 ,p_creator_person_id => p_creator_person_id
868 ,p_transaction_step_id => p_transaction_step_id
869 ,p_object_version_number => p_object_version_number);
870 --
871 -- when in validation only mode raise the Validate_Enabled exception
872 --
873 if p_validate then
874 raise hr_api.validate_enabled;
875 end if;
876 --
877
878 IF g_debug THEN
879 hr_utility.set_location(' Leaving:'||l_proc, 10);
880 END IF;
881
882 exception
883 when hr_api.validate_enabled then
884
885 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
886 --
887 -- As the Validate_Enabled exception has been raised
888 -- we must rollback to the savepoint
889 --
890 rollback to create_trans_step;
891 -- set primary key to null
892 p_transaction_step_id := null;
893 p_object_version_number := null;
894 end create_trans_step;
895 -- ----------------------------------------------------------------------------
896 -- |-----------------------< create_transaction_step >------------------------|
897 -- ----------------------------------------------------------------------------
898 procedure create_transaction_step
899 (p_validate in boolean default false
900 ,p_creator_person_id in number
901 ,p_transaction_id in number
902 ,p_api_name in varchar2
903 ,p_api_display_name in varchar2 default null
904 ,p_item_type in varchar2 default null
905 ,p_item_key in varchar2 default null
906 ,p_activity_id in number default null
907 ,p_transaction_step_id out nocopy number
908 ,p_object_version_number out nocopy number) is
909 -- --------------------------------------------------------------------------
910 -- declare local variables
911 -- --------------------------------------------------------------------------
912 l_proc constant varchar2(100) := g_package || ' create_transaction_step';
913 --
914 begin
915 g_debug := hr_utility.debug_enabled;
916
917 IF g_debug THEN
918 -- l_proc := g_package||'create_transaction_step';
919 hr_utility.set_location('Entering:'|| l_proc, 5);
920 END IF;
921
922 create_trans_step
923 (p_validate => false
924 ,p_creator_person_id => p_creator_person_id
925 ,p_transaction_id => p_transaction_id
926 ,p_api_name => p_api_name
927 ,p_api_display_name => p_api_display_name
928 ,p_item_type => p_item_type
929 ,p_item_key => p_item_key
930 ,p_activity_id => p_activity_id
931 ,p_processing_order => null
932 ,p_transaction_step_id => p_transaction_step_id
933 ,p_object_version_number => p_object_version_number);
934 --
935
936 IF g_debug THEN
937 hr_utility.set_location(' Leaving:'||l_proc, 10);
938 END IF;
939
940 end create_transaction_step;
941 -- ----------------------------------------------------------------------------
942 -- |-----------------------< update_transaction_step >------------------------|
943 -- ----------------------------------------------------------------------------
944 procedure update_transaction_step
945 (p_validate in boolean default false
946 ,p_transaction_step_id in number
947 ,p_update_person_id in number
948 ,p_object_version_number in out nocopy number) is
949 -- --------------------------------------------------------------------------
950 -- declare local variables
951 -- --------------------------------------------------------------------------
952 l_proc constant varchar2(100) := g_package || ' update_transaction_step';
953 --
954 cursor csr_hatv is
955 select hatv.transaction_value_id
956 from hr_api_transaction_values hatv
957 where hatv.transaction_step_id = p_transaction_step_id;
958 --
959 begin
960 g_debug := hr_utility.debug_enabled;
961
962 IF g_debug THEN
963 -- l_proc := g_package||'update_transaction_step';
964 hr_utility.set_location('Entering: '|| l_proc, 5);
965 END IF;
966
967 -- verify that person has transaction privilege
968 check_transaction_privilege
969 (p_transaction_id => get_transaction_id
970 (p_transaction_step_id => p_transaction_step_id)
971 ,p_person_id => p_update_person_id);
972 --
973 -- issue a savepoint if operating in validation only mode.
974 --
975 if p_validate then
976 savepoint create_transaction_step;
977 end if;
978 -- call the row handler to update the transaction setp
979 hr_trs_upd.upd
980 (p_validate => false
981 ,p_transaction_step_id => p_transaction_step_id
982 ,p_update_person_id => p_update_person_id
983 ,p_object_version_number => p_object_version_number);
984 --
985 -- when in validation only mode raise the Validate_Enabled exception
986 --
987 if p_validate then
988 raise hr_api.validate_enabled;
989 end if;
990 --
991
992 IF g_debug THEN
993 hr_utility.set_location(' Leaving:'||l_proc, 10);
994 END IF;
995
996 exception
997 when hr_api.validate_enabled then
998 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
999 --
1000 -- As the Validate_Enabled exception has been raised
1001 -- we must rollback to the savepoint
1002 --
1003 rollback to create_transaction_step;
1004 -- set primary key to null
1005 p_object_version_number := null;
1006 end update_transaction_step;
1007 -- ----------------------------------------------------------------------------
1008 -- |-----------------------< delete_transaction_step >------------------------|
1009 -- ----------------------------------------------------------------------------
1010 procedure delete_transaction_step
1011 (p_validate in boolean default false
1012 ,p_transaction_step_id in number
1013 ,p_person_id in number
1014 ,p_object_version_number in number) is
1015 --
1016 l_proc constant varchar2(100) := g_package || ' delete_transaction_step';
1017 begin
1018 hr_utility.set_location('Entering: '|| l_proc,5);
1019 g_debug := hr_utility.debug_enabled;
1020 -- verify that person has transaction privilege
1021 check_transaction_privilege
1022 (p_transaction_id => get_transaction_id
1023 (p_transaction_step_id => p_transaction_step_id)
1024 ,p_person_id => p_person_id);
1025 --
1026 -- issue a savepoint if operating in validation only mode.
1027 --
1028 if p_validate then
1029 savepoint delete_transaction_step;
1030 end if;
1031 -- lock the current transaction step
1032 hr_trs_shd.lck
1033 (p_transaction_step_id => p_transaction_step_id
1034 ,p_object_version_number => p_object_version_number);
1035 --
1036 -- delete each transaction value
1037 -- Do this using direct SQL rather than in a loop using the row handler
1038 -- to improve performance.
1039 --
1040 delete from hr_api_transaction_values
1041 where transaction_step_id = p_transaction_step_id;
1042 -- delete the transaction step
1043 hr_trs_del.del
1044 (p_transaction_step_id => p_transaction_step_id
1045 ,p_object_version_number => p_object_version_number);
1046 if p_validate then
1047 raise hr_api.validate_enabled;
1048 end if;
1049 hr_utility.set_location('Leaving: '|| l_proc,10);
1050 exception
1051 when hr_api.validate_enabled then
1052 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1053 --
1054 -- As the Validate_Enabled exception has been raised
1055 -- we must rollback to the savepoint
1056 --
1057 rollback to delete_transaction_step;
1058 end delete_transaction_step;
1059 -- ----------------------------------------------------------------------------
1060 -- |------------------------------< set_value >-------------------------------|
1061 -- ----------------------------------------------------------------------------
1062 procedure set_value
1063 (p_validate in boolean default false
1064 ,p_transaction_step_id in number
1065 ,p_person_id in number
1066 ,p_datatype in varchar2 default null
1067 ,p_name in varchar2
1068 ,p_varchar2_value in varchar2 default null
1069 ,p_number_value in number default null
1070 ,p_date_value in date default null
1071 ,p_original_varchar2_value in varchar2 default null --ns
1072 ,p_original_number_value in number default null --ns
1073 ,p_original_date_value in date default null --ns
1074 ,p_transaction_value_id out nocopy number) is
1075 -- --------------------------------------------------------------------------
1076 -- declare local variables
1077 -- --------------------------------------------------------------------------
1078 l_proc constant varchar2(100) := g_package || ' set_value';
1079 l_insert boolean := false;
1080 l_transaction_value_id hr_api_transaction_values.transaction_value_id%type;
1081 l_name hr_api_transaction_values.name%type;
1082 --
1083 l_current_value varchar2(2000); --ns
1084 l_original_value varchar2(2000);
1085 --
1086 cursor csr_hatv is
1087 select hatv.transaction_value_id,
1088 varchar2_value || fnd_date.date_to_canonical(date_value) || number_value current_value, --ns
1089 original_varchar2_value || fnd_date.date_to_canonical(original_date_value) || original_number_value original_value
1090 from hr_api_transaction_values hatv
1091 where hatv.transaction_step_id = p_transaction_step_id
1092 and hatv.name = l_name;
1093 --
1094 begin
1095
1096 IF g_debug THEN
1097 -- l_proc := g_package||'set_value';
1098 hr_utility.set_location('Entering:'|| l_proc, 5);
1099 END IF;
1100
1101 -- upper the parameter name
1102 l_name := upper(p_name);
1103 -- verify that person has transaction privilege
1104 check_transaction_privilege
1105 (p_transaction_id => get_transaction_id
1106 (p_transaction_step_id => p_transaction_step_id)
1107 ,p_person_id => p_person_id);
1108 -- determine if we are doing an insert or update
1109 open csr_hatv;
1110 hr_utility.trace('Going into Fetch after (open csr_hatv ): '|| l_proc);
1111 fetch csr_hatv into l_transaction_value_id,l_current_value,l_original_value;
1112 if csr_hatv%notfound then
1113 -- a row does exist so we must be trying to create a value
1114 l_insert := true;
1115 end if;
1116 close csr_hatv;
1117 --
1118 -- issue a savepoint if operating in validation only mode.
1119 --
1120 if p_validate then
1121 savepoint set_value;
1122 end if;
1123 --
1124 if l_insert then
1125 -- call the row handler to insert the transaction value
1126 hr_trv_ins.ins
1127 (p_validate => false
1128 ,p_transaction_value_id => p_transaction_value_id
1129 ,p_transaction_step_id => p_transaction_step_id
1130 ,p_datatype => p_datatype
1131 ,p_name => l_name
1132 ,p_varchar2_value => p_varchar2_value
1133 ,p_number_value => p_number_value
1134 ,p_date_value => p_date_value
1135 ,p_original_varchar2_value => p_original_varchar2_value -- remove from RH API interface --ns
1136 ,p_original_number_value => p_original_number_value -- remove from RH API interface --ns
1137 ,p_original_date_value => p_original_date_value); -- remove from RH API interface --ns
1138 else
1139 -- call the row handler to update the transaction value
1140 if ((NVL(p_varchar2_value||fnd_date.date_to_canonical(p_date_value)||p_number_value,'X') <> NVL(l_current_value,'X'))
1141 OR (NVL(p_original_varchar2_value||fnd_date.date_to_canonical(p_original_date_value)||p_original_number_value,'X') <> NVL(l_original_value,'X')))
1142 then
1143 g_update_flag := 'Y';
1144 hr_trv_upd.upd
1145 (p_validate => false
1146 ,p_transaction_value_id => l_transaction_value_id
1147 ,p_transaction_step_id => hr_api.g_number -- remove from RH API interface
1148 ,p_datatype => hr_api.g_varchar2 -- remove from RH API interface
1149 ,p_name => hr_api.g_varchar2 -- remove from RH API interface
1150 ,p_varchar2_value => p_varchar2_value
1151 ,p_number_value => p_number_value
1152 ,p_date_value => p_date_value
1153 ,p_original_varchar2_value => p_original_varchar2_value -- remove from RH API interface --ns
1154 ,p_original_number_value => p_original_number_value -- remove from RH API interface --ns
1155 ,p_original_date_value => p_original_date_value); -- remove from RH API interface --ns
1156 end if;
1157 end if;
1158 --
1159 -- when in validation only mode raise the Validate_Enabled exception
1160 --
1161 if p_validate then
1162 raise hr_api.validate_enabled;
1163 end if;
1164 --
1165
1166 IF g_debug THEN
1167 hr_utility.set_location(' Leaving:'||l_proc, 10);
1168 END IF;
1169
1170 exception
1171 when hr_api.validate_enabled then
1172
1173 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1174 --
1175 -- As the Validate_Enabled exception has been raised
1176 -- we must rollback to the savepoint
1177 --
1178 rollback to set_transaction_value;
1179 -- set primary key to null
1180 p_transaction_value_id := null;
1181 end set_value;
1182 -- ----------------------------------------------------------------------------
1183 -- |---------------------------< set_varchar2_value >-------------------------|
1184 -- ----------------------------------------------------------------------------
1185 procedure set_varchar2_value
1186 (p_validate in boolean default false
1187 ,p_transaction_step_id in number
1188 ,p_person_id in number
1189 ,p_name in varchar2
1190 ,p_value in varchar2 default null
1191 ,p_original_value in varchar2 default null ) is --ns
1192 -- --------------------------------------------------------------------------
1193 -- declare local variables
1194 -- --------------------------------------------------------------------------
1195 l_proc constant varchar2(100) := g_package || ' set_varchar2_value';
1196 l_transaction_value_id hr_api_transaction_values.transaction_value_id%type;
1197 --
1198 begin
1199 g_debug := hr_utility.debug_enabled;
1200
1201 IF g_debug THEN
1202 -- l_proc := g_package||'set_varchar2_value';
1203 hr_utility.set_location('Entering:'|| l_proc, 5);
1204 END IF;
1205
1206 --
1207 set_value
1208 (p_validate => p_validate
1209 ,p_transaction_step_id => p_transaction_step_id
1210 ,p_person_id => p_person_id
1211 ,p_datatype => 'VARCHAR2'
1212 ,p_name => p_name
1213 ,p_varchar2_value => p_value
1214 ,p_original_varchar2_value => p_original_value --ns
1215 ,p_transaction_value_id => l_transaction_value_id);
1216 --
1217
1218 IF g_debug THEN
1219 hr_utility.set_location(' Leaving:'||l_proc, 10);
1220 END IF;
1221
1222 end set_varchar2_value;
1223 -- ----------------------------------------------------------------------------
1224 -- |---------------------------< set_number_value >---------------------------|
1225 -- ----------------------------------------------------------------------------
1226 procedure set_number_value
1227 (p_validate in boolean default false
1228 ,p_transaction_step_id in number
1229 ,p_person_id in number
1230 ,p_name in varchar2
1231 ,p_value in number default null
1232 ,p_original_value in number default null ) is --ns
1233 -- --------------------------------------------------------------------------
1234 -- declare local variables
1235 -- --------------------------------------------------------------------------
1236 l_proc constant varchar2(100) := g_package || ' set_number_value';
1237 l_transaction_value_id hr_api_transaction_values.transaction_value_id%type;
1238 --
1239 begin
1240 g_debug := hr_utility.debug_enabled;
1241
1242 IF g_debug THEN
1243 -- l_proc := g_package||'set_number_value';
1244 hr_utility.set_location('Entering:'|| l_proc, 5);
1245 END IF;
1246
1247 --
1248 set_value
1249 (p_validate => p_validate
1250 ,p_transaction_step_id => p_transaction_step_id
1251 ,p_person_id => p_person_id
1252 ,p_datatype => 'NUMBER'
1253 ,p_name => p_name
1254 ,p_number_value => p_value
1255 ,p_original_number_value => p_original_value --ns
1256 ,p_transaction_value_id => l_transaction_value_id);
1257 --
1258
1259 IF g_debug THEN
1260 hr_utility.set_location(' Leaving:'||l_proc, 10);
1261 END IF;
1262
1263 end set_number_value;
1264 -- ----------------------------------------------------------------------------
1265 -- |-----------------------------< set_date_value >---------------------------|
1266 -- ----------------------------------------------------------------------------
1267 procedure set_date_value
1268 (p_validate in boolean default false
1269 ,p_transaction_step_id in number
1270 ,p_person_id in number
1271 ,p_name in varchar2
1272 ,p_value in date default null
1273 ,p_original_value in date default null ) is --ns
1274 -- --------------------------------------------------------------------------
1275 -- declare local variables
1276 -- --------------------------------------------------------------------------
1277 l_proc constant varchar2(100) := g_package || ' set_date_value';
1278 l_transaction_value_id hr_api_transaction_values.transaction_value_id%type;
1279 --
1280 begin
1281 g_debug := hr_utility.debug_enabled;
1282
1283 IF g_debug THEN
1284 -- l_proc := g_package||'set_date_value';
1285 hr_utility.set_location('Entering:'|| l_proc, 5);
1286 END IF;
1287
1288 --
1289 set_value
1290 (p_validate => p_validate
1291 ,p_transaction_step_id => p_transaction_step_id
1292 ,p_person_id => p_person_id
1293 ,p_datatype => 'DATE'
1294 ,p_name => p_name
1295 ,p_date_value => trunc(p_value)
1296 ,p_original_date_value => p_original_value --ns
1297 ,p_transaction_value_id => l_transaction_value_id);
1298 --
1299
1300 IF g_debug THEN
1301 hr_utility.set_location(' Leaving:'||l_proc, 10);
1302 END IF;
1303
1304 end set_date_value;
1305 -- ----------------------------------------------------------------------------
1306 -- |--------------------------< set_boolean_value >---------------------------|
1307 -- ----------------------------------------------------------------------------
1308 procedure set_boolean_value
1309 (p_validate in boolean default false
1310 ,p_transaction_step_id in number
1311 ,p_person_id in number
1312 ,p_name in varchar2
1313 ,p_value in boolean default null
1314 ,p_original_value in boolean default null ) is --ns
1315 -- --------------------------------------------------------------------------
1316 -- declare local variables
1317 -- --------------------------------------------------------------------------
1318 l_proc constant varchar2(100) := g_package || ' set_boolean_value';
1319 l_transaction_value_id hr_api_transaction_values.transaction_value_id%type;
1320 l_value varchar2(30);
1321 l_original_value varchar2(30); --ns
1322 --
1323 begin
1324 g_debug := hr_utility.debug_enabled;
1325
1326 IF g_debug THEN
1327 -- l_proc := g_package||'set_boolean_value';
1328 hr_utility.set_location('Entering:'|| l_proc, 5);
1329 END IF;
1330
1331 -- derive the value
1332 if p_value then
1333 l_value := 'TRUE';
1334 elsif not p_value then
1335 l_value := 'FALSE';
1336 else
1337 l_value := null;
1338 end if;
1339 --
1340 --ns start
1341 if p_original_value then
1342 l_original_value := 'TRUE';
1343 elsif not p_original_value then
1344 l_original_value := 'FALSE';
1345 else
1346 l_original_value := null;
1347 end if;
1348 --ns end
1349
1350 set_value
1351 (p_validate => p_validate
1352 ,p_transaction_step_id => p_transaction_step_id
1353 ,p_person_id => p_person_id
1354 ,p_datatype => 'BOOLEAN'
1355 ,p_name => p_name
1356 ,p_varchar2_value => l_value
1357 ,p_original_varchar2_value => l_original_value --ns
1358 ,p_transaction_value_id => l_transaction_value_id);
1359 --
1360
1361 IF g_debug THEN
1362 hr_utility.set_location(' Leaving:'||l_proc, 10);
1363 END IF;
1364
1365 end set_boolean_value;
1366 -- ----------------------------------------------------------------------------
1367 -- |------------------------------< get_value >-------------------------------|
1368 -- ----------------------------------------------------------------------------
1369 procedure get_value
1370 (p_transaction_step_id in number
1371 ,p_name in varchar2
1372 ,p_datatype out nocopy varchar2
1373 ,p_varchar2_value out nocopy varchar2
1374 ,p_number_value out nocopy number
1375 ,p_date_value out nocopy date) is
1376 -- --------------------------------------------------------------------------
1377 -- declare local variables
1378 -- --------------------------------------------------------------------------
1379 l_proc constant varchar2(100) := g_package || ' get_value';
1380 l_insert boolean := false;
1381 l_name hr_api_transaction_values.name%type;
1382 --
1383 cursor csr_hatv is
1384 select hatv.datatype
1385 ,hatv.varchar2_value
1386 ,hatv.number_value
1387 ,hatv.date_value
1388 from hr_api_transaction_values hatv
1389 where hatv.transaction_step_id = p_transaction_step_id
1390 and hatv.name = l_name;
1391 --
1392 begin
1393 g_debug := hr_utility.debug_enabled;
1394
1395 IF g_debug THEN
1396 -- l_proc := g_package||'get_value';
1397 hr_utility.set_location('Entering:'|| l_proc, 5);
1398 END IF;
1399
1400 -- upper the parameter name
1401 l_name := upper(p_name);
1402 -- select the transaction value details
1403 open csr_hatv;
1404 hr_utility.trace('Going into Fetch after (open csr_hatv ): '|| l_proc);
1405 fetch csr_hatv
1406 into p_datatype
1407 ,p_varchar2_value
1408 ,p_number_value
1409 ,p_date_value;
1410 close csr_hatv;
1411 --
1412
1413 IF g_debug THEN
1414 hr_utility.set_location(' Leaving:'||l_proc, 15);
1415 END IF;
1416
1417 end get_value;
1418 -- ----------------------------------------------------------------------------
1419 -- |---------------------------< get_varchar2_value >-------------------------|
1420 -- ----------------------------------------------------------------------------
1421 function get_varchar2_value
1422 (p_transaction_step_id in number
1423 ,p_name in varchar2) return varchar2 is
1424 -- --------------------------------------------------------------------------
1425 -- declare local variables
1426 -- --------------------------------------------------------------------------
1427 l_proc constant varchar2(100) := g_package || ' get_varchar2_value';
1428 l_datatype hr_api_transaction_values.datatype%type;
1429 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1430 l_number hr_api_transaction_values.number_value%type;
1431 l_date hr_api_transaction_values.date_value%type;
1432 --
1433 begin
1434 g_debug := hr_utility.debug_enabled;
1435
1436 IF g_debug THEN
1437 -- l_proc := g_package||'get_varchar2_value';
1438 hr_utility.set_location('Entering:'|| l_proc, 5);
1439 END IF;
1440
1441 --
1442 get_value
1443 (p_transaction_step_id => p_transaction_step_id
1444 ,p_name => p_name
1445 ,p_datatype => l_datatype
1446 ,p_varchar2_value => l_varchar2
1447 ,p_number_value => l_number
1448 ,p_date_value => l_date);
1449 --
1450
1451 IF g_debug THEN
1452 hr_utility.set_location(' Leaving:'||l_proc, 10);
1453 END IF;
1454
1455 return(l_varchar2);
1456 end get_varchar2_value;
1457 -- ----------------------------------------------------------------------------
1458 -- |-----------------------------< get_number_value >-------------------------|
1459 -- ----------------------------------------------------------------------------
1460 function get_number_value
1461 (p_transaction_step_id in number
1462 ,p_name in varchar2) return number is
1463 -- --------------------------------------------------------------------------
1464 -- declare local variables
1465 -- --------------------------------------------------------------------------
1466 l_proc constant varchar2(100) := g_package || ' get_number_value';
1467 l_datatype hr_api_transaction_values.datatype%type;
1468 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1469 l_number hr_api_transaction_values.number_value%type;
1470 l_date hr_api_transaction_values.date_value%type;
1471 --
1472 begin
1473 g_debug := hr_utility.debug_enabled;
1474
1475 IF g_debug THEN
1476 -- l_proc := g_package||'get_number_value';
1477 hr_utility.set_location('Entering:'|| l_proc, 5);
1478 END IF;
1479
1480 --
1481 get_value
1482 (p_transaction_step_id => p_transaction_step_id
1483 ,p_name => p_name
1484 ,p_datatype => l_datatype
1485 ,p_varchar2_value => l_varchar2
1486 ,p_number_value => l_number
1487 ,p_date_value => l_date);
1488 --
1489
1490 IF g_debug THEN
1491 hr_utility.set_location(' Leaving:'||l_proc, 10);
1492 END IF;
1493
1494 hr_utility.set_location(' Leaving:'||l_proc, 15);
1495 return(l_number);
1496
1497 end get_number_value;
1498 -- ----------------------------------------------------------------------------
1499 -- |-------------------------------< get_date_value >-------------------------|
1500 -- ----------------------------------------------------------------------------
1501 function get_date_value
1502 (p_transaction_step_id in number
1503 ,p_name in varchar2) return date is
1504 -- --------------------------------------------------------------------------
1505 -- declare local variables
1506 -- --------------------------------------------------------------------------
1507 l_proc constant varchar2(100) := g_package || ' get_date_value';
1508 l_datatype hr_api_transaction_values.datatype%type;
1509 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1510 l_number hr_api_transaction_values.number_value%type;
1511 l_date hr_api_transaction_values.date_value%type;
1512 --
1513 begin
1514 g_debug := hr_utility.debug_enabled;
1515
1516 IF g_debug THEN
1517 -- l_proc := g_package||'get_date_value';
1518 hr_utility.set_location('Entering:'|| l_proc, 5);
1519 END IF;
1520
1521 --
1522 get_value
1523 (p_transaction_step_id => p_transaction_step_id
1524 ,p_name => p_name
1525 ,p_datatype => l_datatype
1526 ,p_varchar2_value => l_varchar2
1527 ,p_number_value => l_number
1528 ,p_date_value => l_date);
1529 --
1530
1531 IF g_debug THEN
1532 hr_utility.set_location(' Leaving:'||l_proc, 10);
1533 END IF;
1534
1535 return(l_date);
1536 end get_date_value;
1537 --
1538 -- 11/12/1997 Change Begins
1539 -- ----------------------------------------------------------------------------
1540 -- |--------------------------< get_date2char_value >-------------------------|
1541 -- ----------------------------------------------------------------------------
1542 function get_date2char_value
1543 (p_transaction_step_id in number
1544 ,p_name in varchar2
1545 ,p_date_format in varchar2) return varchar2 is
1546 -- --------------------------------------------------------------------------
1547 -- declare local variables
1548 -- --------------------------------------------------------------------------
1549 l_proc constant varchar2(100) := g_package || ' get_date2char_value';
1550 l_datatype hr_api_transaction_values.datatype%type;
1551 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1552 l_number hr_api_transaction_values.number_value%type;
1553 l_date hr_api_transaction_values.date_value%type;
1554 l_char_date varchar2(200);
1555 --
1556 begin
1557 g_debug := hr_utility.debug_enabled;
1558
1559 IF g_debug THEN
1560 -- l_proc := g_package||'get_date2char_value';
1561 hr_utility.set_location('Entering:'|| l_proc, 5);
1562 END IF;
1563
1564 --
1565 get_value
1566 (p_transaction_step_id => p_transaction_step_id
1567 ,p_name => p_name
1568 ,p_datatype => l_datatype
1569 ,p_varchar2_value => l_varchar2
1570 ,p_number_value => l_number
1571 ,p_date_value => l_date);
1572 --
1573
1574 IF g_debug THEN
1575 hr_utility.set_location(' Leaving:'||l_proc, 10);
1576 END IF;
1577
1578 l_char_date := to_char(l_date, p_date_format);
1579 hr_utility.set_location(' Leaving:'||l_proc, 15);
1580 return(l_char_date);
1581 --
1582 end get_date2char_value;
1583 --
1584 -- 11/12/1997 Change Ends
1585 --
1586 -- ----------------------------------------------------------------------------
1587 -- |----------------------------< get_boolean_value >-------------------------|
1588 -- ----------------------------------------------------------------------------
1589 function get_boolean_value
1590 (p_transaction_step_id in number
1591 ,p_name in varchar2) return boolean is
1592 -- --------------------------------------------------------------------------
1593 -- declare local variables
1594 -- --------------------------------------------------------------------------
1595 l_proc constant varchar2(100) := g_package || ' get_boolean_value';
1596 l_datatype hr_api_transaction_values.datatype%type;
1597 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1598 l_number hr_api_transaction_values.number_value%type;
1599 l_date hr_api_transaction_values.date_value%type;
1600 l_boolean boolean;
1601 --
1602 begin
1603 g_debug := hr_utility.debug_enabled;
1604
1605 IF g_debug THEN
1606 -- l_proc := g_package||'get_boolean_value';
1607 hr_utility.set_location('Entering:'|| l_proc, 5);
1608 END IF;
1609
1610 --
1611 get_value
1612 (p_transaction_step_id => p_transaction_step_id
1613 ,p_name => p_name
1614 ,p_datatype => l_datatype
1615 ,p_varchar2_value => l_varchar2
1616 ,p_number_value => l_number
1617 ,p_date_value => l_date);
1618 --
1619 if l_varchar2 = 'TRUE' then
1620 l_boolean := true;
1621 elsif l_varchar2 = 'FALSE' then
1622 l_boolean := false;
1623 else
1624 l_boolean := null;
1625 end if;
1626
1627 IF g_debug THEN
1628 hr_utility.set_location(' Leaving:'||l_proc, 10);
1629 END IF;
1630 hr_utility.set_location(' Leaving:'||l_proc, 15);
1631 return(l_boolean);
1632 end get_boolean_value;
1633 -- ----------------------------------------------------------------------------
1634 -- |---------------------< get_original_value >-------------------------------|
1635 -- ----------------------------------------------------------------------------
1636 procedure get_original_value
1637 (p_transaction_step_id in number
1638 ,p_name in varchar2
1639 ,p_datatype out nocopy varchar2
1640 ,p_original_varchar2_value out nocopy varchar2
1641 ,p_original_number_value out nocopy number
1642 ,p_original_date_value out nocopy date) is
1643 -- --------------------------------------------------------------------------
1644 -- declare local variables
1645 -- --------------------------------------------------------------------------
1646 l_proc constant varchar2(100) := g_package || ' get_original_value';
1647 l_insert boolean := false;
1648 l_name hr_api_transaction_values.name%type;
1649 --
1650 cursor csr_hatv is
1651 select hatv.datatype
1652 ,hatv.original_varchar2_value
1653 ,hatv.original_number_value
1654 ,hatv.original_date_value
1655 from hr_api_transaction_values hatv
1656 where hatv.transaction_step_id = p_transaction_step_id
1657 and hatv.name = l_name;
1658 --
1659 begin
1660 g_debug := hr_utility.debug_enabled;
1661
1662 IF g_debug THEN
1663 -- l_proc := g_package||'get_original_value';
1664 hr_utility.set_location('Entering:'|| l_proc, 5);
1665 END IF;
1666
1667 -- upper the parameter name
1668 l_name := upper(p_name);
1669 -- select the transaction value details
1670 open csr_hatv;
1671 hr_utility.trace('Going into Fetch after (open csr_hatv ): '|| l_proc);
1672 fetch csr_hatv
1673 into p_datatype
1674 ,p_original_varchar2_value
1675 ,p_original_number_value
1676 ,p_original_date_value;
1677 if csr_hatv%notfound then
1678 -- parameter does not exist
1679 close csr_hatv;
1680 hr_utility.set_message(801, 'HR_51751_WEB_TRA_STEP_EXISTS');
1681 hr_utility.raise_error;
1682 end if;
1683 close csr_hatv;
1684 --
1685
1686 IF g_debug THEN
1687 hr_utility.set_location(' Leaving:'||l_proc, 15);
1688 END IF;
1689
1690 end get_original_value;
1691 -- ----------------------------------------------------------------------------
1692 -- |------------------< get_original_varchar2_value >-------------------------|
1693 -- ----------------------------------------------------------------------------
1694 function get_original_varchar2_value
1695 (p_transaction_step_id in number
1696 ,p_name in varchar2) return varchar2 is
1697 -- --------------------------------------------------------------------------
1698 -- declare local variables
1699 -- --------------------------------------------------------------------------
1700 l_proc constant varchar2(100) := g_package || ' get_original_varchar2_value';
1701 l_datatype hr_api_transaction_values.datatype%type;
1702 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1703 l_number hr_api_transaction_values.number_value%type;
1704 l_date hr_api_transaction_values.date_value%type;
1705 --
1706 begin
1707 g_debug := hr_utility.debug_enabled;
1708
1709 IF g_debug THEN
1710 -- l_proc := g_package||'get_original_varchar2_value';
1711 hr_utility.set_location('Entering:'|| l_proc, 5);
1712 END IF;
1713
1714 --
1715 get_original_value
1716 (p_transaction_step_id => p_transaction_step_id
1717 ,p_name => p_name
1718 ,p_datatype => l_datatype
1719 ,p_original_varchar2_value => l_varchar2
1720 ,p_original_number_value => l_number
1721 ,p_original_date_value => l_date);
1722 --
1723
1724 IF g_debug THEN
1725 hr_utility.set_location(' Leaving:'||l_proc, 10);
1726 END IF;
1727 hr_utility.set_location(' Leaving:'||l_proc, 15);
1728 return(l_varchar2);
1729 end get_original_varchar2_value;
1730 -- ----------------------------------------------------------------------------
1731 -- |--------------------< get_original_number_value >-------------------------|
1732 -- ----------------------------------------------------------------------------
1733 function get_original_number_value
1734 (p_transaction_step_id in number
1735 ,p_name in varchar2) return number is
1736 -- --------------------------------------------------------------------------
1737 -- declare local variables
1738 -- --------------------------------------------------------------------------
1739 l_proc constant varchar2(100) := g_package || ' get_original_number_value';
1740 l_datatype hr_api_transaction_values.datatype%type;
1741 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1742 l_number hr_api_transaction_values.number_value%type;
1743 l_date hr_api_transaction_values.date_value%type;
1744 --
1745 begin
1746 g_debug := hr_utility.debug_enabled;
1747
1748 IF g_debug THEN
1749 -- l_proc := g_package||'get_original_number_value';
1750 hr_utility.set_location('Entering:'|| l_proc, 5);
1751 END IF;
1752
1753 --
1754 get_original_value
1755 (p_transaction_step_id => p_transaction_step_id
1756 ,p_name => p_name
1757 ,p_datatype => l_datatype
1758 ,p_original_varchar2_value => l_varchar2
1759 ,p_original_number_value => l_number
1760 ,p_original_date_value => l_date);
1761 --
1762
1763 IF g_debug THEN
1764 hr_utility.set_location(' Leaving:'||l_proc, 10);
1765 END IF;
1766 hr_utility.set_location(' Leaving:'||l_proc, 15);
1767 return(l_number);
1768 end get_original_number_value;
1769 -- ----------------------------------------------------------------------------
1770 -- |----------------------< get_original_date_value >-------------------------|
1771 -- ----------------------------------------------------------------------------
1772 function get_original_date_value
1773 (p_transaction_step_id in number
1774 ,p_name in varchar2) return date is
1775 -- --------------------------------------------------------------------------
1776 -- declare local variables
1777 -- --------------------------------------------------------------------------
1778 l_proc constant varchar2(100) := g_package || ' get_original_date_value';
1779 l_datatype hr_api_transaction_values.datatype%type;
1780 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1781 l_number hr_api_transaction_values.number_value%type;
1782 l_date hr_api_transaction_values.date_value%type;
1783 --
1784 begin
1785 g_debug := hr_utility.debug_enabled;
1786
1787 IF g_debug THEN
1788 -- l_proc := g_package||'get_original_date_value';
1789 hr_utility.set_location('Entering:'|| l_proc, 5);
1790 END IF;
1791
1792 --
1793 get_original_value
1794 (p_transaction_step_id => p_transaction_step_id
1795 ,p_name => p_name
1796 ,p_datatype => l_datatype
1797 ,p_original_varchar2_value => l_varchar2
1798 ,p_original_number_value => l_number
1799 ,p_original_date_value => l_date);
1800 --
1801
1802 IF g_debug THEN
1803 hr_utility.set_location(' Leaving:'||l_proc, 10);
1804 END IF;
1805 hr_utility.set_location(' Leaving:'||l_proc, 15);
1806 return(l_date);
1807 end get_original_date_value;
1808 -- ----------------------------------------------------------------------------
1809 -- |----------------< get_original_boolean_value >----------------------------|
1810 -- ----------------------------------------------------------------------------
1811 function get_original_boolean_value
1812 (p_transaction_step_id in number
1813 ,p_name in varchar2) return boolean is
1814 -- --------------------------------------------------------------------------
1815 -- declare local variables
1816 -- --------------------------------------------------------------------------
1817 l_proc constant varchar2(100) := g_package || ' get_original_boolean_value';
1818 l_datatype hr_api_transaction_values.datatype%type;
1819 l_varchar2 hr_api_transaction_values.varchar2_value%type;
1820 l_number hr_api_transaction_values.number_value%type;
1821 l_date hr_api_transaction_values.date_value%type;
1822 l_boolean boolean;
1823 --
1824 begin
1825 g_debug := hr_utility.debug_enabled;
1826
1827 IF g_debug THEN
1828 -- l_proc := g_package||'get_original_boolean_value';
1829 hr_utility.set_location('Entering:'|| l_proc, 5);
1830 END IF;
1831
1832 --
1833 get_original_value
1834 (p_transaction_step_id => p_transaction_step_id
1835 ,p_name => p_name
1836 ,p_datatype => l_datatype
1837 ,p_original_varchar2_value => l_varchar2
1838 ,p_original_number_value => l_number
1839 ,p_original_date_value => l_date);
1840 --
1841 if l_varchar2 = 'TRUE' then
1842 l_boolean := true;
1843 elsif l_varchar2 = 'FALSE' then
1844 l_boolean := false;
1845 else
1846 l_boolean := null;
1847 end if;
1848
1849 IF g_debug THEN
1850 hr_utility.set_location(' Leaving:'||l_proc, 10);
1851 END IF;
1852 hr_utility.set_location(' Leaving:'||l_proc, 15);
1853 return(l_boolean);
1854 end get_original_boolean_value;
1855 --
1856 -- ----------------------------------------------------------------------------
1857 -- |----------------------------< finalize_transaction >-----------------------|
1858 -- ----------------------------------------------------------------------------
1859 --
1860 Procedure finalize_transaction (
1861 P_TRANSACTION_ID IN NUMBER
1862 ,P_EVENT IN VARCHAR2
1863 ,P_RETURN_STATUS OUT NOCOPY VARCHAR2
1864 )
1865 is
1866 l_apiName Varchar2(100);
1867 l_sqlbuf Varchar2(1000);
1868 Begin
1869
1870 l_apiName := hr_xml_util.get_node_value(
1871 p_transaction_id => P_TRANSACTION_ID
1872 ,p_desired_node_value => 'TxnFinalizeApi'
1873 ,p_xpath => 'Transaction/TransCtx');
1874
1875 If l_apiName is not null Then
1876 l_sqlbuf:= 'BEGIN ' || l_apiName
1877 || ' (P_TRANSACTION_ID => :1 '
1878 || ' ,P_EVENT => :2 '
1879 || ' ,P_RETURN_STATUS => :3 ); END; ';
1880 EXECUTE IMMEDIATE l_sqlbuf using in P_TRANSACTION_ID, in P_EVENT, out P_RETURN_STATUS;
1881 End If;
1882 End finalize_transaction;
1883 --
1884 -- ----------------------------------------------------------------------------
1885 -- |----------------------------< rollback_transaction >----------------------|
1886 -- ----------------------------------------------------------------------------
1887 procedure rollback_transaction
1888 (p_transaction_id in number
1889 ,p_validate in boolean default false) is
1890 -- cursor to select all transaction values for a transaction
1891 cursor csr_trv(c_transaction_step_id number) is
1892 select trv.transaction_value_id
1893 from hr_api_transaction_values trv
1894 where trv.transaction_step_id = c_transaction_step_id;
1895 -- cursor to select all transaction steps for a transaction
1896 cursor csr_trs is
1897 select trs.transaction_step_id
1898 ,trs.object_version_number
1899 from hr_api_transaction_steps trs
1900 where trs.transaction_id = p_transaction_id;
1901 --
1902 cursor csr_hist is
1903 select action
1904 from pqh_ss_approval_history
1905 where transaction_history_id=p_transaction_id
1906 order by last_update_date desc;
1907
1908 --
1909 l_proc constant varchar2(100) := g_package || ' rollback_transaction';
1910 l_return_status varchar2(10);
1911 lv_status pqh_ss_approval_history.action%type;
1912
1913 begin
1914 hr_utility.set_location('Entering: '|| l_proc,5);
1915 g_debug := hr_utility.debug_enabled;
1916 --
1917 -- issue a savepoint if operating in validation only mode.
1918 --
1919 if p_validate then
1920 savepoint rollback_transaction;
1921 end if;
1922
1923 -- block for the module call back on transaction deletion
1924 BEGIN
1925 lv_status := 'NONE';
1926
1927 For I in csr_hist Loop
1928 lv_status := I.action;
1929 Exit;
1930 End Loop;
1931
1932 finalize_transaction (
1933 P_TRANSACTION_ID => p_transaction_id
1934 ,P_EVENT => lv_status -- Add code here to pass the Event.
1935 ,P_RETURN_STATUS => l_return_status
1936 );
1937 exception when others then
1938 null;
1939 END;
1940
1941 -- lock the transaction
1942 hr_trn_shd.lck
1943 (p_transaction_id => p_transaction_id);
1944 -- delete all transaction steps and values
1945 hr_utility.trace('Going into (for csr1 in csr_trs loop): '|| l_proc);
1946 for csr1 in csr_trs loop
1947 -- lock the transaction step
1948 hr_trs_shd.lck
1949 (p_transaction_step_id => csr1.transaction_step_id
1950 ,p_object_version_number => csr1.object_version_number);
1951 -- select and delete each transaction value
1952 for csr2 in csr_trv(csr1.transaction_step_id) loop
1953 -- delete all transaction values
1954 hr_trv_del.del
1955 (p_transaction_value_id => csr2.transaction_value_id);
1956 end loop;
1957 -- delete transaction step
1958 hr_trs_del.del
1959 (p_transaction_step_id => csr1.transaction_step_id
1960 ,p_object_version_number => csr1.object_version_number);
1961 end loop;
1962 hr_utility.trace('Out of (for csr1 in csr_trs loop): '|| l_proc);
1963 -- delete transaction
1964 hr_trn_del.del
1965 (p_transaction_id => p_transaction_id);
1966 --
1967 --delete per_pay_transactions
1968 delete from per_pay_transactions
1969 where transaction_id = p_transaction_id;
1970 -- delete from ben_icd_transaction
1971 delete from ben_icd_transaction where transaction_id = p_transaction_id and status <> 'SP';
1972 -- when in validation only mode raise the Validate_Enabled exception
1973 --
1974 if p_validate then
1975 raise hr_api.validate_enabled;
1976 end if;
1977 --
1978 exception
1979 when hr_api.validate_enabled then
1980 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1981 --
1982 -- As the Validate_Enabled exception has been raised
1983 -- we must rollback to the savepoint
1984 --
1985 rollback to rollback_transaction;
1986 end rollback_transaction;
1987 --
1988 end hr_transaction_api;