1 Package Body hr_transaction_ss as
2 /* $Header: hrtrnwrs.pkb 120.10.12010000.2 2008/12/19 10:48:55 ckondapi ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_transaction_ss.';
7 g_update_object_version varchar2(30) := 'update_object_version';
8
9 g_debug boolean := hr_utility.debug_enabled;
10
11 --- -------------------------------------------------------------------------
12 --- -------------setRespondedUserCtx-----------------------------------
13 --- -------------------------------------------------------------------------
14 -- This method calls the fnd_global.apps_initialize if the context user
15 -- responding to approval notification does not match fnd_global.user_name
16 -- --------------------------------------------------------------------------
17
18 procedure setRespondedUserCtx(p_item_type in varchar2,
19 p_item_key in varchar2) is
20
21 c_proc constant varchar2(60) := 'setRespondedUserCtx';
22 contextUser wf_users.name%type;
23 contextProxyUser wf_users.name%type;
24
25 userId fnd_user.user_id%type;
26 cursor username is
27 select user_id from fnd_user where user_name=contextUser;
28 begin
29 if g_debug then
30 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
31 hr_utility.set_location('p_item_type:'||p_item_type,2);
32 hr_utility.set_location('p_item_key:'||p_item_key,3);
33 hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,4);
34 hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,5);
35 end if;
36
37
38 -- check if we have the context set by the Approver Notification
39 -- call back function; if exists
40 -- HR_CONTEXT_USER_ATTR
41 contextUser := wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_CONTEXT_USER_ATTR',true);
42 contextProxyUser := wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_CONTEXT_PROXY_ATTR',true);
43
44 if g_debug then
45 hr_utility.set_location('contextUser value :'|| contextUser,6);
46 hr_utility.set_location('contextProxyUser value :'|| contextProxyUser,6);
47
48 end if;
49 /* possible values
50 Case 1: Null => Call back function does not exists or context attribute
51 never set
52 Case 2: valid WF role
53 Case 3: email:<incoming email address>
54
55 Note: In case of e-mail response the response it could yield into the
56 following options
57 1. email address of the responder found on more than one role in
58 the directory service; then context_user = 'email:'<incoming email address>
59 2. email address of the responder found on one and only one role
60 in the directory service; then context_user = <role name>
61 3. mail address of the responder not found on any role in the
62 directory service,then context_user = 'email:'<incoming email address>
63
64
65 Functionally we will call apps intialization only if we have valid FND user
66 and context user does not match the fnd_global.user_name
67 */
68
69 if(contextUser is not null and substr (contextUser, 1, 6)<>'email:') then
70 -- we have valid wf role
71 -- check if the role is same as fnd_global.user_name
72 if(contextUser<>fnd_global.user_name) then
73 if(nvl(contextProxyUser,fnd_global.user_name)<>fnd_global.user_name) then
74
75 -- check if the role is a valid FND user
76 if g_debug then
77 hr_utility.set_location('contextUser and contextProxyUser does not match fnd_global.user_name',7);
78 end if;
79 open username;
80 fetch username into userId;
81 if username%found then
82 if g_debug then
83 hr_utility.set_location('found a valid fnd user corresponding to context user',8);
84 hr_utility.set_location('calling fnd_global.apps_initialize for userId:'||userId,9);
85 end if;
86 -- call the apps intialization
87 fnd_global.apps_initialize(userId,null,null,null,null);
88 if g_debug then
89 hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,10);
90 hr_utility.set_location('fnd_global.user_name after reset:'||fnd_global.user_name,11);
91 hr_utility.set_location('fnd_global.user_id after reset:'||fnd_global.user_id,12);
92 end if;
93 end if;
94 close username;
95 end if;
96 end if;
97 end if;
98
99 if (g_debug ) then
100 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 20);
101 end if;
102
103 exception
104 when others then
105 if ( username%isopen ) then
106 close username;
107 end if;
108 if g_debug then
109 hr_utility.set_location('Error in setRespondedUserCtx SQLERRM' ||' '||to_char(SQLCODE),30);
110 end if;
111 raise;
112 end setRespondedUserCtx;
113
114
115 --- -------------------------------------------------------------------------
116 --- -------------reset_ame_approval_status-----------------------------------
117 --- -------------------------------------------------------------------------
118 -- This method resets the approval status of all the approvers, when the
119 -- transaction is returned by the aprover for correction.
120 -- --------------------------------------------------------------------------
121 procedure reset_ame_approval_status
122 (p_item_type in varchar2,
123 p_item_key in varchar2)
124 AS
125 -- Variables required for AME API
126 c_application_id integer;
127 c_transaction_id varchar2(25);
128 c_transaction_type varchar2(25);
129 c_next_approver_rec ame_util.approverRecord;
130 c_additional_approver_order ame_util.orderRecord;
131 c_additional_approver_rec ame_util.approversTable;
132 c_all_approvers ame_util.approversTable;
133 c_proc varchar2(30) default 'reset_ame_approval_status';
134
135 --ns begin
136 c_creator_user wf_users.name%Type;
137 c_return_user wf_users.name%Type;
138 c_return_person number;
139 c_match_found varchar2(1) := 'N';
140 c_rfc_initiator varchar2(1);
141 l_proc constant varchar2(100) := g_package || ' reset_ame_approval_status';
142 --ns end
143
144 BEGIN
145 hr_utility.set_location('Entering: '|| l_proc,5);
146 g_debug := hr_utility.debug_enabled;
147
148 IF g_debug THEN
149 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
150 END IF;
151
152
153 -- get AME related WF attribute values
154 if hr_workflow_utility.item_attribute_exists
155 (p_item_type => p_item_type
156 ,p_item_key => p_item_key
157 ,p_name => 'HR_AME_APP_ID_ATTR') then
158 -- get the attribute value
159 hr_utility.trace('In : (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
160 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
161 itemkey => p_item_key,
162 aname => 'HR_AME_APP_ID_ATTR');
163
164 else
165 hr_utility.trace('In : else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
166 c_application_id := null;
167 end if;
168
169 c_application_id := nvl(c_application_id,800);
170
171 if hr_workflow_utility.item_attribute_exists
172 (p_item_type => p_item_type
173 ,p_item_key => p_item_key
174 ,p_name => 'TRANSACTION_ID') then
175 -- get the attribute value
176 hr_utility.trace('In : (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
177 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
178 itemkey => p_item_key,
179 aname => 'TRANSACTION_ID');
180 else
181 hr_utility.trace('In : else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
182 c_transaction_id := null;
183 end if;
184
185
186 if hr_workflow_utility.item_attribute_exists
187 (p_item_type => p_item_type
188 ,p_item_key => p_item_key
189 ,p_name => 'HR_AME_TRAN_TYPE_ATTR') then
190 -- get the attribute value
191 hr_utility.trace('In : (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
192 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
193 itemkey => p_item_key,
194 aname => 'HR_AME_TRAN_TYPE_ATTR');
195 else
196 hr_utility.trace('In : else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
197 c_transaction_type := null;
198
199 end if;
200
201
202
203
204 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
205 itemkey => p_item_key,
206 aname => 'HR_AME_TRAN_TYPE_ATTR');
207
208 if(c_transaction_type is not null) then
209 hr_utility.trace('In : (if(c_transaction_type is not null) ) '|| l_proc);
210 IF g_debug THEN
211 hr_utility.trace('calling ame_api.getAllApprovers ');
212 END IF;
213
214 ame_api.getAllApprovers(applicationIdIn =>c_application_id,
215 transactionIdIn=>c_transaction_id,
216 transactionTypeIn =>c_transaction_type,
217 approversOut=>c_all_approvers);
218
219 --ns begin
220 c_creator_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
221 itemkey => p_item_key,
222 aname => 'CREATOR_PERSON_USERNAME');
223 Begin
224 c_return_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
225 itemkey => p_item_key,
226 aname => 'RETURN_TO_USERNAME');
227 Exception
228 WHEN others then
229 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
230 null; -- Bug 3050544
231 End;
232 IF ( c_return_user IS NULL OR c_creator_user = c_return_user ) THEN
233 c_rfc_initiator := 'Y';
234 ELSE
235 c_rfc_initiator := 'N';
236 select employee_id into c_return_person from fnd_user where user_name = c_return_user ;
237 END IF;
238 --ns end
239
240 for i in 1..c_all_approvers.count loop
241
242 IF g_debug THEN
243 hr_utility.trace('calling ame_api..updateApprovalStatus2 ');
244 END IF;
245
246 -- call AME update approval status as null
247 --ns comment start
248 -- If Return to initiator OR
249 -- to other than initiator and matching record found the update approvel status to null.
250 -- Assuming approver list is sorted. All approvers appearing after selected one will be removed--ns comment end
251 IF (c_rfc_initiator = 'Y' OR (c_rfc_initiator = 'N' AND c_match_found = 'Y' ) ) THEN --ns
252 ame_api.updateApprovalStatus2(applicationIdIn => c_application_id,
253 transactionIdIn => c_transaction_id,
254 approvalStatusIn => null,
255 approverPersonIdIn => c_all_approvers(i).person_id,
256 approverUserIdIn => null,
257 transactionTypeIn => c_transaction_type,
258 forwardeeIn => null);
259
260 END IF; --ns
261
262 IF ( c_rfc_initiator = 'N' AND ( c_return_person = c_all_approvers(i).person_id )) THEN --ns
263 c_match_found := 'Y'; --ns
264 END IF; --ns
265
266 end loop;
267
268
269
270 end if;
271
272
273 IF g_debug THEN
274 hr_utility.set_location('Leaving:'||g_package||'.'|| c_proc, 35);
275 END IF;
276
277 hr_utility.set_location('Leaving: '|| l_proc,40);
278 EXCEPTION
279 when others then
280 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
281 hr_utility.trace(' exception in '||c_proc||' : ' || sqlerrm);
282 Wf_Core.Context(g_package, c_proc, p_item_type, p_item_key);
283 raise;
284
285 END reset_ame_approval_status;
286
287 -----------------------------------------------------------------------------
288 ----------------reset_approval_status-----------------------------------
289 ---------------------------------------------------------------------------
290 -- This method resets the approval status of all the approvers, when the
291 -- transaction is returned by the aprover for correction.
292 -----------------------------------------------------------------------------
293 procedure reset_approval_status
294 (p_item_type in varchar2,
295 p_item_key in varchar2)
296 AS
297 c_proc varchar2(30) default 'reset_approval_status';
298 l_approvalProcessVersion varchar2(10);
299 l_proc constant varchar2(100) := g_package || ' reset_approval_status';
300 BEGIN
301 hr_utility.set_location('Entering: '|| l_proc,5);
302 g_debug := hr_utility.debug_enabled;
303
304 IF g_debug THEN
305 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
306 END IF;
307
308 -- need to reset all the Workflow item attributes used in the approval process
309
310 -- FORWARD_FROM_DISPLAY_NAME
311 if hr_workflow_utility.item_attribute_exists
312 (p_item_type => p_item_type
313 ,p_item_key => p_item_key
314 ,p_name => 'FORWARD_FROM_DISPLAY_NAME') then
315 -- set the attribute value to null
316 wf_engine.SetItemAttrText(itemtype => p_item_type ,
317 itemkey => p_item_key,
318 aname => 'FORWARD_FROM_DISPLAY_NAME',
319 avalue=>null);
320 end if;
321 -- FORWARD_FROM_USERNAME
322 if hr_workflow_utility.item_attribute_exists
323 (p_item_type => p_item_type
324 ,p_item_key => p_item_key
325 ,p_name => 'FORWARD_FROM_USERNAME') then
326 -- set the attribute value to null
327 wf_engine.SetItemAttrText(itemtype => p_item_type ,
328 itemkey => p_item_key,
329 aname => 'FORWARD_FROM_USERNAME',
330 avalue=>null);
331 end if;
332 -- FORWARD_FROM_PERSON_ID
333 if hr_workflow_utility.item_attribute_exists
334 (p_item_type => p_item_type
335 ,p_item_key => p_item_key
336 ,p_name => 'FORWARD_FROM_PERSON_ID') then
337 -- set the attribute value to null
338 wf_engine.SetItemAttrNumber(itemtype => p_item_type ,
339 itemkey => p_item_key,
340 aname => 'FORWARD_FROM_PERSON_ID',
341 avalue=>null);
342 end if;
343 -- FORWARD_TO_DISPLAY_NAME
344 if hr_workflow_utility.item_attribute_exists
345 (p_item_type => p_item_type
346 ,p_item_key => p_item_key
347 ,p_name => 'FORWARD_TO_DISPLAY_NAME') then
348 -- set the attribute value to null
349 wf_engine.SetItemAttrText(itemtype => p_item_type ,
350 itemkey => p_item_key,
351 aname => 'FORWARD_TO_DISPLAY_NAME',
352 avalue=>null);
353 end if;
354 -- FORWARD_TO_USERNAME
355 if hr_workflow_utility.item_attribute_exists
356 (p_item_type => p_item_type
357 ,p_item_key => p_item_key
358 ,p_name => 'FORWARD_TO_USERNAME') then
359 -- set the attribute value to null
360 wf_engine.SetItemAttrText(itemtype => p_item_type ,
361 itemkey => p_item_key,
362 aname => 'FORWARD_TO_USERNAME',
363 avalue=>null);
364 end if;
365 -- FORWARD_TO_PERSON_ID
366 if hr_workflow_utility.item_attribute_exists
367 (p_item_type => p_item_type
368 ,p_item_key => p_item_key
369 ,p_name => 'FORWARD_TO_PERSON_ID') then
370 -- set the attribute value to null
371 wf_engine.SetItemAttrNumber(itemtype => p_item_type ,
372 itemkey => p_item_key,
373 aname => 'FORWARD_TO_PERSON_ID',
374 avalue=>null);
375 end if;
376
377 /* Bug 2940951: No need to reset current approver index and last default approver in case
378 * new approval process is used and the non-AME approval is used
379 * as the two attributes are set when pqh_ss_workflow.return_for_correction is invoked.
380 * CAUTION: IF this procedure is invoked from somewhere else (apart from RFC) then this needs
381 * to be checked for that condition too.
382 */
383 Begin
384 l_approvalProcessVersion := wf_engine.GetItemAttrText(
385 itemtype => p_item_Type,
386 itemkey => p_item_Key,
387 aname => 'HR_APPROVAL_PRC_VERSION');
388 Exception
389 when others then
390 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
391 null;
392
393 End;
394
395 IF ( NVL(l_approvalProcessversion,'X') <> 'V5' OR
396 wf_engine.GetItemAttrText(
397 itemtype => p_item_Type, itemkey => p_item_Key,
398 aname => 'HR_AME_TRAN_TYPE_ATTR') IS NOT NULL) THEN
399 -- CURRENT_APPROVER_INDEX
400 hr_utility.trace('In ( IF ( NVL(l_approvalProcessversion,X) <> V5 OR
401 wf_engine.GetItemAttrText(..,..,..,)IS NOT NULL '|| l_proc);
402 if hr_workflow_utility.item_attribute_exists
403 (p_item_type => p_item_type
404 ,p_item_key => p_item_key
405 ,p_name => 'CURRENT_APPROVER_INDEX') then
406 -- set the attribute value to null
407 wf_engine.SetItemAttrNumber(itemtype => p_item_type ,
408 itemkey => p_item_key,
409 aname => 'CURRENT_APPROVER_INDEX',
410 avalue=>null);
411 end if;
412
413 -- 'LAST_DEFAULT_APPROVER'
414 if hr_workflow_utility.item_attribute_exists
415 (p_item_type => p_item_type
416 ,p_item_key => p_item_key
417 ,p_name => 'LAST_DEFAULT_APPROVER') then
418 -- set the attribute value to null
419 wf_engine.SetItemAttrNumber(itemtype => p_item_type ,
420 itemkey => p_item_key,
421 aname => 'LAST_DEFAULT_APPROVER',
422 avalue=>null);
423 end if;
424 END IF;
425
426 -- CURRENT_DEF_APPR_INDEX
427 if hr_workflow_utility.item_attribute_exists
428 (p_item_type => p_item_type
429 ,p_item_key => p_item_key
430 ,p_name => 'CURRENT_DEF_APPR_INDEX') then
431 -- set the attribute value to null
432 wf_engine.SetItemAttrNumber(itemtype => p_item_type ,
433 itemkey => p_item_key,
434 aname => 'CURRENT_DEF_APPR_INDEX',
435 avalue=>null);
436 end if;
437
438
439
440 IF g_debug THEN
441 hr_utility.set_location('Leaving:'||g_package||'.'|| c_proc, 20);
442 END IF;
443
444 hr_utility.set_location('Leaving: '|| l_proc,25);
445 EXCEPTION
446 when others then
447 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
448 hr_utility.trace(' exception in '||c_proc||' : ' || sqlerrm);
449 Wf_Core.Context(g_package, c_proc, p_item_type, p_item_key);
450 raise;
451
452 END reset_approval_status;
453
454 -- ----------------------------------------------------------------------------
455 -- get workflow attribute p_effective_date.
456 -- ----------------------------------------------------------------------------
457
458 FUNCTION get_wf_effective_date
459 (p_transaction_step_id in number)
460 RETURN varchar2 IS
461
462 cursor csr_item_type_key is
463 select item_type,item_key
464 from hr_api_transaction_steps
465 where transaction_step_id = p_transaction_step_id;
466
467 l_item_type hr_api_transaction_steps.item_type%type;
468 l_item_key hr_api_transaction_steps.item_key%type;
469 l_effective_date varchar2(100);
470 l_proc constant varchar2(100) := g_package || ' get_wf_effective_date';
471 BEGIN
472 hr_utility.set_location('Entering: '|| l_proc,5);
473 open csr_item_type_key;
474 fetch csr_item_type_key into l_item_type,l_item_key;
475 close csr_item_type_key;
476 if l_item_type is not null and l_item_key is not null then
477 l_effective_date := wf_engine.getitemattrtext
478 (itemtype => l_item_type
479 ,itemkey => l_item_key
480 ,aname => 'P_EFFECTIVE_DATE');
481 else
482 l_effective_date := null;
483 end if;
484 hr_utility.set_location('Leaving: '|| l_proc,10);
485 return l_effective_date;
486
487 EXCEPTION
488 when others then
489
490 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
491 return null;
492
493 END get_wf_effective_date;
494
495 -------------------------------------------------------------------------------
496 -- set workflow item attribute 'TRAN_SUBMIT'
497 -- 'S' -- Save for Later
498 -- 'C' -- Returned for Correction
499 -- 'A' -- Submitted for Approval
500 -- 'N' -- this is the default value. It is for the system crash or system time
501 -- out.
502 -- ----------------------------------------------------------------------------
503 procedure set_save_for_later_status
504 (p_item_type in varchar2
505 ,p_item_key in varchar2
506 ,p_status in varchar2
507 ,p_transaction_id in number default null) is
508 l_proc constant varchar2(100) := g_package || ' set_save_for_later_status';
509 begin
510 hr_utility.set_location('Entering: '|| l_proc,5);
511 If (p_item_type is not null and
512 p_item_key is not null )
513 then
514 wf_engine.SetItemAttrText(itemtype => p_item_type,
515 itemkey => p_item_key,
516 aname => 'TRAN_SUBMIT',
517 avalue => p_status);
518 End If;
519
520 If p_transaction_id is not null then
521
522 hr_transaction_api.update_transaction(p_transaction_id => p_transaction_id,
523 p_status => p_status );
524 end If;
525
526 hr_utility.set_location('Leaving: '|| l_proc,10);
527 end set_save_for_later_status;
528
529 procedure set_initial_save_for_later
530 (itemtype in varchar2,
531 itemkey in varchar2,
532 actid in number,
533 funmode in varchar2,
534 result out nocopy varchar2 ) is
535 l_proc constant varchar2(100) := g_package || ' set_initial_save_for_later';
536 begin
537 hr_utility.set_location('Entering: '|| l_proc,5);
538 if ( funmode = 'RUN' ) then
539 set_save_for_later_status
540 (p_item_type => itemtype,
541 p_item_key => itemkey,
542 p_status => 'W');
543 result := 'COMPLETE:SUCCESS';
544 elsif ( funmode = 'CANCEL' ) then
545 --
546 null;
547 --
548 --
549 end if;
550 hr_utility.set_location('Leaving: '|| l_proc,10);
551
552 end set_initial_save_for_later;
553
554 procedure set_delete_save_for_later
555 (itemtype in varchar2,
556 itemkey in varchar2,
557 actid in number,
558 funmode in varchar2,
559 result out nocopy varchar2 ) is
560 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
561 l_proc constant varchar2(100) := g_package || ' set_delete_save_for_later';
562 begin
563 hr_utility.set_location('Entering: '|| l_proc,5);
564 if ( funmode = 'RUN' ) then
565 ln_transaction_id := get_transaction_id(itemtype, itemkey);
566 set_save_for_later_status
567 (p_item_type => itemtype,
568 p_item_key => itemkey,
569 p_status => 'D',
570 p_transaction_id => ln_transaction_id);
571 result := 'COMPLETE:SUCCESS';
572 elsif ( funmode = 'CANCEL' ) then
573 --
574 null;
575 --
576 --
577 end if;
578 hr_utility.set_location('Leaving: '|| l_proc,10);
579 end set_delete_save_for_later;
580
581
582 procedure set_save_for_later
583 (itemtype in varchar2,
584 itemkey in varchar2,
585 actid in number,
586 funmode in varchar2,
587 result out nocopy varchar2 ) is
588 l_proc constant varchar2(100) := g_package || ' set_save_for_later';
589 begin
590 hr_utility.set_location('Entering: '|| l_proc,5);
591 if ( funmode = 'RUN' ) then
592 set_save_for_later_status
593 (p_item_type => itemtype,
594 p_item_key => itemkey,
595 p_status => 'S');
596 result := 'COMPLETE:SUCCESS';
597 elsif ( funmode = 'CANCEL' ) then
598 --
599 null;
600 --
601 --
602 end if;
603 hr_utility.set_location('Leaving: '|| l_proc,10);
604 end set_save_for_later;
605
606
607 procedure set_return_for_correction
608 (itemtype in varchar2,
609 itemkey in varchar2,
610 actid in number,
611 funmode in varchar2,
612 result out nocopy varchar2 ) is
613 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
614 l_result varchar2(2000); --ns
615 l_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
616 l_proc constant varchar2(100) := g_package || ' set_return_for_correction';
617 begin
618 hr_utility.set_location('Entering: '|| l_proc,5);
619 if ( funmode = 'RUN' ) then
620 hr_utility.trace('In (: if ( funmode = RUN )'|| l_proc);
621 --ns commented
622 --ns this procedure is replace by the one below to set the return for correction status appropriately.
623 /*
624 ln_transaction_id := get_transaction_id(itemtype, itemkey);
625 set_save_for_later_status
626 (p_item_type => itemtype,
627 p_item_key => itemkey,
628 p_status => 'C',
629 p_transaction_id => ln_transaction_id);
630 */
631 --ns call this procedure
632 pqh_ss_workflow.set_transaction_status (
633 p_itemType => itemType
634 ,p_itemKey => itemKey
635 ,p_action => 'RFC'
636 ,p_result => l_result );
637
638
639 -- need to call the AME to reset the approval status of the approvers.
640 reset_ame_approval_status (p_item_type =>itemtype,
641 p_item_key =>itemkey);
642 -- need to reset the workflow item attributes values used in Approval process
643 reset_approval_status(p_item_type =>itemtype,
644 p_item_key =>itemkey);
645
646 -- as per the appraisals new build sshr5.1 check if we need call the appraisals routine
647 -- in RFC
648 begin
649 -- check if this is a appraisal transaction
650 -- the hr_api_transactions.transaction_ref_table should be used for reference
651 -- get the transaction id
652 ln_transaction_id := get_transaction_id(itemtype, itemkey);
653
654 -- get the transaction_ref_table
655 select transaction_ref_table
656 into l_transaction_ref_table
657 from hr_api_transactions
658 where transaction_id=ln_transaction_id;
659
660 if(upper(l_transaction_ref_table)='PER_APPRAISALS') then
661 hr_appraisal_workflow_ss.set_appraisal_rfc_status(p_itemtype=>itemType
662 ,p_itemkey=>itemKey
663 ,p_actid=>actid
664 ,p_funcmode=>funmode
665 ,p_result=> l_result);
666 end if;
667
668 exception
669 when others then
670 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
671 raise;
672 end;
673 result := 'COMPLETE:SUCCESS';
674 elsif ( funmode = 'CANCEL' ) then
675 hr_utility.trace('In (: if ( funmode = CANCEL )'|| l_proc);
676 --
677 null;
678 --
679 --
680 end if;
681 hr_utility.set_location('Leaving: '|| l_proc,20);
682
683 end set_return_for_correction;
684
685
686 procedure set_submit_for_approval
687 (itemtype in varchar2,
688 itemkey in varchar2,
689 actid in number,
690 funmode in varchar2,
691 result out nocopy varchar2 ) is
692 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
693 l_proc constant varchar2(100) := g_package || ' set_submit_for_approval';
694 begin
695 hr_utility.set_location('Entering: '|| l_proc,5);
696
697 if ( funmode = 'RUN' ) then
698 ln_transaction_id := get_transaction_id(itemtype, itemkey);
699 set_save_for_later_status
700 (p_item_type => itemtype,
701 p_item_key => itemkey,
702 p_status => 'Y',
703 p_transaction_id => ln_transaction_id);
704 result := 'COMPLETE:SUCCESS';
705 elsif ( funmode = 'CANCEL' ) then
706 --
707 null;
708 --
709 --
710 end if;
711 hr_utility.set_location('Leaving: '|| l_proc,10);
712 end set_submit_for_approval;
713
714
715 -- ----------------------------------------------------------------------------
716 -- |---------------------------< get_transaction_id >-------------------------|
717 -- ----------------------------------------------------------------------------
718 function get_transaction_id
719 (p_item_type in varchar2
720 ,p_item_key in varchar2) return number is
721 l_proc constant varchar2(100) := g_package || ' get_transaction_id';
722 --
723 l_transaction_id number;
724 begin
725 hr_utility.set_location('Entering: '|| l_proc,5);
726 hr_utility.set_location('Leaving: '|| l_proc,10);
727 return(wf_engine.getitemattrnumber
728 (itemtype => p_item_type
729 ,itemkey => p_item_key
730 ,aname => 'TRANSACTION_ID'));
731 exception
732 when others then
733 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
734 -- the TRANSACTION_ID doesn't exist as an item so return null
735 select transaction_id into l_transaction_id from hr_api_transactions where item_type = p_item_type and item_key = p_item_key;
736 hr_utility.set_location('l_transaction_id: '|| l_transaction_id,10);
737 return(l_transaction_id);
738
739 end get_transaction_id;
740
741 PROCEDURE populate_null_values
742 (p_item_type IN VARCHAR2
743 ,p_item_key IN VARCHAR2
744 ,p_function_id in number
745 ,p_selected_person_id in number
746 ,p_process_name in varchar2
747 ,p_status in varchar2
748 ,p_section_display_name in varchar2
749 ,p_assignment_id in number
750 ,p_transaction_effective_date in date
751 ,p_transaction_type in varchar2
752 ,l_function_id in out nocopy hr_api_transactions.function_id%TYPE
753 ,ln_selected_person_id in out nocopy hr_api_transactions.selected_person_id%TYPE
754 ,lv_process_name in out nocopy hr_api_transactions.process_name%TYPE
755 ,lv_status in out nocopy hr_api_transactions.status%TYPE
756 ,lv_section_display_name in out nocopy hr_api_transactions.section_display_name%TYPE
757 ,ln_assignment_id in out nocopy hr_api_transactions.assignment_id%TYPE
758 ,ld_trans_effec_date in out nocopy hr_api_transactions.transaction_effective_date%TYPE
759 ,lv_transaction_type in out nocopy hr_api_transactions.transaction_type%TYPE
760 )
761 AS
762 cursor get_function_info ( p_item_type HR_API_TRANSACTION_STEPS.item_type%TYPE
763 ,p_item_key HR_API_TRANSACTION_STEPS.item_key%TYPE ) is
764 select fff.function_id, fff.function_name from
765 fnd_form_functions_vl fff
766 where fff.function_name = ( select iav.text_value
767 from wf_item_attribute_values iav
768 where iav.item_type = p_item_type
769 and iav.item_key = p_item_key
770 and iav.name = 'P_CALLED_FROM') ;
771
772 l_function_name fnd_form_functions_vl.function_name%TYPE default null;
773 l_proc constant varchar2(100) := g_package || ' populate_null_values';
774 BEGIN
775 hr_utility.set_location('Entering: '|| l_proc,5);
776 If p_function_id is null then
777 hr_utility.trace('In(If p_function_id is null)'|| l_proc);
778 If p_item_type is not null and p_item_key is not null then
779 hr_utility.trace('In(p_item_type is not null and p_item_key is not null)'|| l_proc);
780 OPEN get_function_info(p_item_type => p_item_type,
781 p_item_key => p_item_key);
782
783 FETCH get_function_info into l_function_id, l_function_name;
784 -- fix for bug 7658326
785 /*IF(get_function_info%notfound) then
786 CLOSE get_function_info;
787 END if;*/
788 close get_function_info;
789 end if;
790 else
791 hr_utility.trace('In else of (If p_function_id is null)'|| l_proc);
792 l_function_id := p_function_id;
793 end if;
794
795 If p_selected_person_id is null then
796 If p_item_type is not null and p_item_key is not null then
797 ln_selected_person_id := wf_engine.GetItemAttrNumber(p_item_type,
798 p_item_key,
799 'CURRENT_PERSON_ID');
800 end if;
801 else
802 ln_selected_person_id := p_selected_person_id;
803 end if;
804
805 If p_process_name is null then
806 If p_item_type is not null and p_item_key is not null then
807 lv_process_name := wf_engine.GetItemAttrText(p_item_type
808 ,p_item_key
809 ,'PROCESS_NAME');
810 end if;
811 else
812 lv_process_name := p_process_name;
813 end if;
814
815 If p_status is null then
816 If p_item_type is not null and p_item_key is not null then
817 lv_status := wf_engine.GetItemAttrText(p_item_type
818 ,p_item_key
819 ,'TRAN_SUBMIT');
820 end if;
821 else
822 lv_status := p_status;
823 end if;
824
825 If p_section_display_name is null then
826 If p_item_type is not null and p_item_key is not null then
827 lv_section_display_name := wf_engine.GetItemAttrText( p_item_type
828 ,p_item_key
829 ,'HR_SECTION_DISPLAY_NAME');
830 end if;
831 else
832 lv_section_display_name := p_section_display_name;
833 end if;
834
835 If p_assignment_id is null then
836 If p_item_type is not null and p_item_key is not null then
837 ln_assignment_id := wf_engine.GetItemAttrText(p_item_type
838 ,p_item_key
839 ,'CURRENT_ASSIGNMENT_ID');
840 end if;
841 else
842 ln_assignment_id := p_assignment_id;
843 end if;
844
845 If p_transaction_effective_date is null then
846 If p_item_type is not null and p_item_key is not null then
847 ld_trans_effec_date := wf_engine.GetItemAttrText(p_item_type
848 ,p_item_key
849 ,'CURRENT_EFFECTIVE_DATE');
850 end if;
851 else
852 ld_trans_effec_date := p_transaction_effective_date;
853 end if;
854
855
856 If p_item_type is not null and p_item_key is not null then
857 lv_transaction_type := nvl(p_transaction_type,'WF');
858 else
859 lv_transaction_type := nvl(p_transaction_type,'NWF');
860 end if;
861
862 hr_utility.set_location('Leaving: '|| l_proc,20);
863 END populate_null_values;
864
865 -- ----------------------------------------------------------------------------
866 -- |----------------------------< start_transaction >-------------------------|
867 -- ----------------------------------------------------------------------------
868 procedure start_transaction
869 (itemtype in varchar2
870 ,itemkey in varchar2
871 ,actid in number
872 ,funmode in varchar2
873 ,p_login_person_id in number
874 ,p_product_code in varchar2 default null
875 ,p_url in varchar2 default null
876 ,p_status in varchar2 default null
877 ,p_section_display_name in varchar2 default null
878 ,p_function_id in number default null
879 ,p_transaction_ref_table in varchar2 default 'HR_API_TRANSACTIONS'
880 ,p_transaction_ref_id in number default null
881 ,p_transaction_type in varchar2 default null
882 ,p_assignment_id in number default null
883 ,p_api_addtnl_info in varchar2 default null
884 ,p_selected_person_id in number default null
885 ,p_transaction_effective_date in date default null
886 ,p_process_name in varchar2 default null
887 ,p_plan_id in number default null
888 ,p_rptg_grp_id in number default null
889 ,p_effective_date_option in varchar2 default null
890 ,result out nocopy varchar2) is
891 -- --------------------------------------------------------------------------
892 -- declare local variables
893 -- --------------------------------------------------------------------------
894 l_transaction_privilege hr_api_transactions.transaction_privilege%type;
895 l_transaction_id hr_api_transactions.transaction_id%type;
896
897 l_function_id hr_api_transactions.function_id%TYPE;
898 ln_selected_person_id hr_api_transactions.selected_person_id%TYPE;
899 lv_process_name hr_api_transactions.process_name%TYPE;
900 lv_status hr_api_transactions.status%TYPE;
901 lv_section_display_name hr_api_transactions.section_display_name%TYPE;
902 ln_assignment_id hr_api_transactions.assignment_id%TYPE;
903 ld_trans_effec_date hr_api_transactions.transaction_effective_date%TYPE;
904 lv_transaction_type hr_api_transactions.transaction_type%TYPE;
905 l_proc constant varchar2(100) := g_package || ' start_transaction';
906 --
907 begin
908 hr_utility.set_location('Entering: '|| l_proc,5);
909 g_debug := hr_utility.debug_enabled;
910
911 IF g_debug THEN
912 hr_utility.trace('In (IF g_debug )'|| l_proc);
913 END IF;
914
915 populate_null_values
916 (itemtype
917 ,itemkey
918 ,p_function_id
919 ,p_selected_person_id
920 ,p_process_name
921 ,p_status
922 ,p_section_display_name
923 ,p_assignment_id
924 ,p_transaction_effective_date
925 ,p_transaction_type
926 ,l_function_id
927 ,ln_selected_person_id
928 ,lv_process_name
929 ,lv_status
930 ,lv_section_display_name
931 ,ln_assignment_id
932 ,ld_trans_effec_date
933 ,lv_transaction_type
934 );
935
936
937
938 if funmode = 'RUN' then
939 hr_utility.trace('In (if funmode = RUN) '|| l_proc);
940 savepoint start_transaction;
941 -- check to see if the TRANSACTION_ID attribute has been created
942 if hr_workflow_utility.item_attribute_exists
943 (p_item_type => itemtype
944 ,p_item_key => itemkey
945 ,p_name => 'TRANSACTION_ID') then
946 hr_utility.trace('In (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
947 -- the TRANSACTION_ID exists so ensure that it is null
948 if get_transaction_id
949 (p_item_type => itemtype
950 ,p_item_key => itemkey) is not null then
951 -- a current transaction is in progress we cannot overwrite it
952 -- hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
953 -- hr_utility.raise_error;
954 result := 'SUCCESS';
955 hr_utility.set_location('Leaving: '|| l_proc,25);
956 return;
957 end if;
958 else
959 hr_utility.trace('In else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
960 -- the TRANSACTION_ID does not exist so create it
961 wf_engine.additemattr
962 (itemtype => itemtype
963 ,itemkey => itemkey
964 ,aname => 'TRANSACTION_ID');
965 end if;
966 -- check to see if the TRANSACTION_PRIVILEGE attribute has been created
967 if not hr_workflow_utility.item_attribute_exists
968 (p_item_type => itemtype
969 ,p_item_key => itemkey
970 ,p_name => 'TRANSACTION_PRIVILEGE') then
971 -- the TRANSACTION_PRIVILEGE does not exist so create it
972 wf_engine.additemattr
973 (itemtype => itemtype
974 ,itemkey => itemkey
975 ,aname => 'TRANSACTION_PRIVILEGE');
976 end if;
977 -- get the TRANSACTION_PRIVILEGE
978 l_transaction_privilege :=
979 wf_engine.getitemattrtext
980 (itemtype => itemtype
981 ,itemkey => itemkey
982 ,aname => 'TRANSACTION_PRIVILEGE');
983 -- check to see if the TRANSACTION_PRIVILEGE is null
984 if l_transaction_privilege is null then
985 -- default the TRANSACTION_PRIVILEGE to PRIVATE
986 l_transaction_privilege := 'PRIVATE';
987 wf_engine.setitemattrtext
988 (itemtype => itemtype
989 ,itemkey => itemkey
990 ,aname => 'TRANSACTION_PRIVILEGE'
991 ,avalue => l_transaction_privilege);
992 end if;
993 -- call the BP API to create the transaction
994 /*
995 hr_transaction_api.create_transaction
996 (p_validate => false
997 ,p_creator_person_id => p_login_person_id
998 ,p_transaction_privilege => l_transaction_privilege
999 ,p_transaction_id => l_transaction_id);
1000 */
1001
1002
1003 hr_transaction_api.create_transaction(
1004 p_validate => false
1005 ,p_creator_person_id => p_login_person_id
1006 ,p_transaction_privilege => l_transaction_privilege
1007 ,p_transaction_id => l_transaction_id
1008 ,p_product_code => p_product_code
1009 ,p_url=> p_url
1010 ,p_status=>lv_status
1011 ,p_section_display_name=>lv_section_display_name
1012 ,p_function_id=>l_function_id
1013 ,p_transaction_ref_table=>p_transaction_ref_table
1014 ,p_transaction_ref_id=>p_transaction_ref_id
1015 ,p_transaction_type=>lv_transaction_type
1016 ,p_assignment_id=>ln_assignment_id
1017 ,p_selected_person_id=>ln_selected_person_id
1018 ,p_item_type=>itemtype
1019 ,p_item_key=>itemkey
1020 ,p_transaction_effective_date=>ld_trans_effec_date
1021 ,p_process_name=>lv_process_name
1022 ,p_plan_id=>p_plan_id
1023 ,p_rptg_grp_id=>p_rptg_grp_id
1024 ,p_effective_date_option=>p_effective_date_option
1025 ,p_api_addtnl_info=>p_api_addtnl_info);
1026
1027 -- set the TRANSACTION_ID
1028 wf_engine.setitemattrnumber
1029 (itemtype => itemtype
1030 ,itemkey => itemkey
1031 ,aname => 'TRANSACTION_ID'
1032 ,avalue => l_transaction_id);
1033 -- transaction has been successfully created so commit and return success
1034 -- commit;
1035 result := 'SUCCESS';
1036 elsif funmode = 'CANCEL' then
1037 hr_utility.trace('In ( elsif funmode = CANCEL) '|| l_proc);
1038 null;
1039 end if;
1040
1041 IF g_debug THEN
1042 hr_utility.set_location(' Leaving:'||l_proc, 30);
1043 END IF;
1044 hr_utility.set_location('Leaving: '|| l_proc,35);
1045 exception
1046 when hr_util_web.g_error_handled then
1047 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1048 -- error from validating the login
1049 rollback to start_transaction;
1050 when others then
1051 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1052 -- rollback any work
1053 rollback to start_transaction;
1054 raise;
1055 --
1056 end start_transaction;
1057
1058 -- ----------------------------------------------------------------------------
1059 -- |----------------------------< rollback_transaction >----------------------|
1060 -- ----------------------------------------------------------------------------
1061 procedure rollback_transaction
1062 (itemtype in varchar2
1063 ,itemkey in varchar2
1064 ,actid in number
1065 ,funmode in varchar2
1066 ,result out nocopy varchar2) is
1067 -- --------------------------------------------------------------------------
1068 -- declare local variables
1069 -- --------------------------------------------------------------------------
1070 lv_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
1071 l_proc constant varchar2(100) := g_package || ' rollback_transaction';
1072 begin
1073 hr_utility.set_location('Entering: '|| l_proc,5);
1074 if funmode = 'RUN' then
1075 hr_utility.trace('In ( if funmode = RUN ) '|| l_proc);
1076 -- Get the TRANSACTION_ID exists so ensure that it is null
1077 if get_transaction_id
1078 (p_item_type => itemtype
1079 ,p_item_key => itemkey) is not null then
1080 hr_utility.trace('In ( if get_transaction_id(..,.., ) '|| l_proc);
1081 savepoint rollback_transaction;
1082 -- check if this is appraisal transaction
1083 -- we need to call the custom call to update the appraisal status
1084 begin
1085 select hr_api_transactions.transaction_ref_table
1086 into lv_transaction_ref_table
1087 from hr_api_transactions
1088 where hr_api_transactions.transaction_id=(get_transaction_id(itemtype,itemkey));
1089 /* BUG FIX 3112230
1090 if(lv_transaction_ref_table='PER_APPRAISALS') then
1091 -- call the custom call to update the status
1092 hr_appraisal_workflow_ss.set_appraisal_reject_status(itemtype,itemkey, actid ,
1093 funmode,result );
1094 end if;
1095 */
1096 exception
1097 when others then
1098 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1099 hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'||
1100 'rollback_transaction'||' : ' || sqlerrm);
1101 -- just log the message no need to raise it
1102 end;
1103
1104 hr_transaction_api.rollback_transaction
1105 (p_transaction_id => get_transaction_id
1106 (p_item_type => itemtype
1107 ,p_item_key => itemkey));
1108 end if;
1109 --commit;
1110 result := 'SUCCESS';
1111 elsif funmode = 'CANCEL' then
1112 hr_utility.trace('In ( if funmode = CANCEL ) '|| l_proc);
1113 null;
1114 end if;
1115 hr_utility.set_location('Leaving: '|| l_proc,20);
1116 exception
1117 when others then
1118 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1119 -- rollback any work
1120 rollback to rollback_transaction;
1121 -- raise a system error
1122 raise;
1123 end rollback_transaction;
1124
1125 -- ----------------------------------------------------------------------------
1126 -- |-------------------------< process_web_api_call >-------------------------|
1127 -- ----------------------------------------------------------------------------
1128 -- {Start Of Comments}
1129 --
1130 -- Description:
1131 -- This procedure will for the specified transaction step and web form API
1132 -- name execute the API using dynamic PL/SQL.
1133 --
1134 -- Pre-Requisities:
1135 -- The transaction step and API code must exist.
1136 --
1137 -- In Parameters:
1138 -- p_transaction_step_id -> The transaction step identifier.
1139 -- p_api_name -> The API name to be called (e.g.
1140 -- hr_emp_marital_web.process_api).
1141 -- p_validate -> If set to TRUE all the work
1142 -- performed by the API will be
1143 -- rolled back. If set to FALSE,
1144 -- the work is not rolled back.
1145 --
1146 -- Post Success:
1147 -- The API would be dynamically built, parsed and executed.
1148 --
1149 -- Post Failure:
1150 -- The exception is raised.
1151 --
1152 -- Developer Implementation Notes:
1153 -- None
1154 --
1155 -- Access Status:
1156 -- Internal Development Use Only.
1157 --
1158 -- {End Of Comments}
1159 -- ----------------------------------------------------------------------------
1160 procedure process_web_api_call
1161 (p_transaction_step_id in number
1162 ,p_api_name in varchar2
1163 ,p_extra_parameter_name in varchar2 default null
1164 ,p_extra_parameter_value in varchar2 default null
1165 ,p_validate in boolean default false) is
1166 --
1167 l_sqlbuf varchar2(1000);
1168 l_cursor integer;
1169 l_row_processed integer;
1170 l_extra_parameter_text varchar2(2000);
1171 l_process_api boolean := false;
1172 l_error varchar2(2000);
1173 l_proc constant varchar2(100) := g_package || ' process_web_api_call';
1174 --
1175 cursor csel is
1176 select nvl(varchar2_value, hr_api.g_varchar2) varchar2_value
1177 ,nvl(number_value, hr_api.g_number) number_value
1178 ,nvl(date_value, hr_api.g_date) date_value
1179 ,nvl(original_varchar2_value, hr_api.g_varchar2) original_varchar2_value
1180 ,nvl(original_number_value, hr_api.g_number) original_number_value
1181 ,nvl(original_date_value, hr_api.g_date) original_date_value
1182 from hr_api_transaction_values hatv
1183 where hatv.transaction_step_id = p_transaction_step_id;
1184 --
1185 begin
1186 /* this step has been commented out nocopy and should not be used until */
1187 /* the transaction step is save on display_workspace. */
1188 /* post release 11 */
1189 /*
1190 -- before we do any processing lets determine if the step needs to be
1191 -- processed
1192 for hr_trs_csr in csel loop
1193 if not((hr_trs_csr.varchar2_value = hr_trs_csr.original_varchar2_value) and
1194 (hr_trs_csr.number_value = hr_trs_csr.original_number_value) and
1195 (hr_trs_csr.date_value = hr_trs_csr.original_date_value)) then
1196 l_process_api := true;
1197 exit;
1198 end if;
1199 end loop;
1200 */
1201
1202 hr_utility.set_location('Entering: '|| l_proc,5);
1203 l_process_api := true;
1204 --
1205 if l_process_api then
1206 hr_utility.trace('In ( if l_process_api): '|| l_proc);
1207 --
1208 -- issue a savepoint if operating in validation only mode.
1209 --
1210 if p_validate then
1211 savepoint process_web_api_call;
1212 end if;
1213 -- define the anonymous pl/sql block that is going to be executed
1214 --
1215 -- begin
1216 -- hr_emp_marital_web.process_api
1217 -- (p_transaction_step_id => :transaction_step_id);
1218 -- end;
1219 --
1220 if p_extra_parameter_name is not null then
1221 l_extra_parameter_text := ','||p_extra_parameter_name||' => :'||
1222 p_extra_parameter_name;
1223 end if;
1224 --
1225 l_sqlbuf :=
1226 'begin '||p_api_name||
1227 '(p_transaction_step_id => '||':transaction_step_id'||
1228 l_extra_parameter_text||'); end;';
1229 -- open the dynamic cursor
1230 l_cursor := dbms_sql.open_cursor;
1231 -- parse the dynamic cursor
1232 dbms_sql.parse(l_cursor, l_sqlbuf, dbms_sql.v7);
1233 -- bind the transaction step identifier
1234 dbms_sql.bind_variable
1235 (l_cursor, ':transaction_step_id', p_transaction_step_id);
1236 if l_extra_parameter_text is not null then
1237 dbms_sql.bind_variable
1238 (l_cursor, ':'||p_extra_parameter_name, p_extra_parameter_value);
1239 end if;
1240 -- execute the dynamic statement
1241 l_row_processed := dbms_sql.execute(l_cursor);
1242 -- close the cursor
1243 dbms_sql.close_cursor(l_cursor);
1244 --
1245 -- when in validation only mode raise the Validate_Enabled exception
1246 --
1247 if p_validate then
1248 raise hr_api.validate_enabled;
1249 end if;
1250 end if;
1251 hr_utility.set_location('Leaving: '|| l_proc,15);
1252 exception
1253 when hr_api.validate_enabled then
1254 --
1255 -- As the Validate_Enabled exception has been raised
1256 -- we must rollback to the savepoint
1257 --
1258 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1259 rollback to process_web_api_call;
1260 when others then
1261 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1262
1263 --close the cursor
1264 dbms_sql.close_cursor(l_cursor);
1265 raise;
1266 end process_web_api_call;
1267
1268 -- ----------------------------------------------------------------------------
1269 -- |---------------------------< process_transaction >------------------------|
1270 -- ----------------------------------------------------------------------------
1271 -- {Start Of Comments}
1272 --
1273 -- Description:
1274 -- This procedure will for a transaction, identify all the steps which need
1275 -- to be processed. Each step is then processed by calling the
1276 -- procedure process_web_api_call.
1277 --
1278 -- Pre-Requisities:
1279 -- None.
1280 --
1281 -- In Parameters:
1282 -- p_item_type -> The internal name for the item type.
1283 -- p_item_key -> A string that represents a primary key generated by
1284 -- the application for the item type. The string
1285 -- uniquely identifies the item within an item type.
1286 -- p_ignore_warings -> If set to 'Y' then all warnings encountered during
1287 -- processing are ignored (i.e. no error is raised).
1288 -- If set to 'N' then any warnings encountered are
1289 -- not ignored and raised.
1290 --
1291 -- Post Success:
1292 -- The transaction will be converted in API calls.
1293 --
1294 -- Post Failure:
1295 -- The exception is raised.
1296 --
1297 -- Developer Implementation Notes:
1298 -- None
1299 --
1300 -- Access Status:
1301 -- Internal Development Use Only.
1302 --
1303 -- {End Of Comments}
1304 -- ----------------------------------------------------------------------------
1305 procedure process_transaction
1306 (p_item_type in varchar2
1307 ,p_item_key in varchar2
1308 ,p_ignore_warnings in varchar2 default 'Y'
1309 ,p_validate in boolean default false
1310 ,p_update_object_version in varchar2 default 'N'
1311 ,p_effective_date in varchar2 default null) is
1312 -- --------------------------------------------------------------------------
1313 -- declare local variables
1314 -- --------------------------------------------------------------------------
1315 l_transaction_id hr_api_transactions.transaction_id%type;
1316 l_application_error boolean := false;
1317 l_object_version_error boolean := false;
1318 l_obj_fatal_error boolean := false;
1319 l_warning_error boolean := false;
1320 l_ignore_warnings boolean;
1321 l_obj_api_name varchar2(200);
1322 l_api_error_name varchar2(200);
1323 l_proc constant varchar2(100) := g_package || ' process_transaction';
1324 --
1325 cursor csr_trs is
1326 select trs.transaction_step_id
1327 ,trs.api_name
1328 ,trs.item_type
1329 ,trs.item_key
1330 ,trs.activity_id
1331 ,trs.creator_person_id
1332 from hr_api_transaction_steps trs
1333 where trs.transaction_id = l_transaction_id
1334 and trs.api_name <> 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API' --#4110654
1335 and object_type is null
1336 order by trs.processing_order,trs.transaction_step_id ; --#2313279
1337 --
1338 cursor cur_fn is
1339 select fff.parameters
1340 from fnd_form_functions fff, hr_api_transactions hat
1341 where fff.function_id = hat.function_id
1342 and hat.transaction_id = l_transaction_id;
1343 --
1344 l_parameter fnd_form_functions.parameters%TYPE;
1345 l_effectiveDate boolean := FALSE;
1346 begin
1347 hr_utility.set_location('Entering: '|| l_proc,5);
1348 -- set the ignore warnings flag
1349 if upper(p_ignore_warnings) = 'Y' then
1350 l_ignore_warnings := true;
1351 else
1352 l_ignore_warnings := false;
1353 end if;
1354 -- get the transaction id
1355 l_transaction_id := get_transaction_id
1356 (p_item_type => p_item_type
1357 ,p_item_key => p_item_key);
1358 if l_transaction_id is null then
1359 fnd_message.set_name('PER','l_transaction_id');
1360 hr_utility.raise_error;
1361 end if;
1362 -- set the Profiles before starting to process any step.
1363 hr_utility.set_location('Call Set_Transaction_Context: '|| l_proc, 10);
1364 -- Call Set_Transaction_Context
1365 hr_transaction_swi.set_transaction_context(l_transaction_id);
1366
1367 -- If p_effective_date is not NULL then set it on the g_txn_ctx.EFFECTIVE_DATE
1368 if ( p_effective_date is not null ) then
1369 BEGIN
1370 hr_transaction_swi.g_txn_ctx.EFFECTIVE_DATE := trunc(fnd_date.canonical_to_date(p_effective_date));
1371 EXCEPTION When Others then
1372 hr_transaction_swi.g_txn_ctx.EFFECTIVE_DATE := trunc(fnd_date.chardate_to_date(p_effective_date));
1373 END;
1374 end if;
1375
1376 hr_utility.set_location('Call Set_Person_Context: '|| l_proc, 15);
1377 -- Call Set_Person_Context
1378
1379 hr_transaction_swi.set_person_context(
1380 p_selected_person_id => hr_transaction_swi.g_txn_ctx.SELECTED_PERSON_ID,
1381 p_selected_assignment_id => hr_transaction_swi.g_txn_ctx.ASSIGNMENT_ID,
1382 p_effective_date => hr_transaction_swi.g_txn_ctx.EFFECTIVE_DATE
1383 );
1384
1385 -- select each transaction steps to process
1386 hr_utility.set_location('select each transaction steps to process: '|| l_proc, 20);
1387 open cur_fn;
1388 fetch cur_fn INTO l_parameter;
1389 close cur_fn;
1390 --
1391 if ( INSTR(l_parameter,'pEffectiveDate') > 0 ) then
1392 l_effectiveDate := true;
1393 else
1394 l_effectiveDate := false;
1395 end if;
1396
1397 hr_process_person_ss.g_person_id := null;
1398 hr_process_person_ss.g_assignment_id := null;
1399 hr_process_person_ss.g_session_id := null;
1400 hr_new_user_reg_ss.g_ignore_emp_generation := 'NO';
1401 hr_utility.trace('In (for I in csr_trs loop) '|| l_proc);
1402 for I in csr_trs loop
1403 begin
1404 -- call the API for the transaction step
1405 if p_update_object_version = 'Y' then
1406 -- update object version for each step
1407 l_obj_api_name := substr(I.api_name,1, instr(I.api_name,'.'));
1408 l_obj_api_name := l_obj_api_name || g_update_object_version;
1409 process_web_api_call
1410 (p_transaction_step_id => I.transaction_step_id
1411 ,p_api_name => l_obj_api_name
1412 ,p_extra_parameter_name => 'p_login_person_id'
1413 ,p_extra_parameter_value => I.creator_person_id
1414 ,p_validate => false);
1415
1416 elsif p_effective_date is not null and l_effectiveDate then
1417
1418 /* elsif p_effective_date is not null then
1419 --ns 11/06/2003: Bug 3223682: Validate non-Assignment data alone.
1420 IF NOT( p_ignore_warnings = 'NON_ASGN' AND I.api_name IN (
1421 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
1422 'HR_SUPERVISOR_SS.PROCESS_API',
1423 'HR_TERMINATION_SS.PROCESS_API',
1424 'HR_PAY_RATE_SS.PROCESS_API' ,
1425 'HR_CAED_SS.PROCESS_API' ) ) THEN
1426 -- Fix 3263968. Included HR_CAED_SS.PROCESS_API
1427 */
1428 --validate api with the new p_effective_date
1429 process_web_api_call
1430 (p_transaction_step_id => I.transaction_step_id
1431 ,p_api_name => I.api_name
1432 ,p_extra_parameter_name => 'p_effective_date'
1433 ,p_extra_parameter_value => p_effective_date
1434 ,p_validate => p_validate);
1435 /*
1436 END IF;
1437 */
1438 else
1439 --validate api
1440 process_web_api_call
1441 (p_transaction_step_id => I.transaction_step_id
1442 ,p_api_name => I.api_name
1443 ,p_validate => p_validate);
1444 end if;
1445 -- do we ignore any warnings which may have been set?
1446 if not l_ignore_warnings then
1447 -- check to see if any warnings have been set
1448 if (not l_warning_error) and
1449 hr_emp_error_utility.exists_warning_text
1450 (p_item_type => I.item_type
1451 ,p_item_key => I.item_key
1452 ,p_actid => I.activity_id) then
1453 -- set the warning flag to true
1454 l_warning_error := true;
1455 end if;
1456 end if;
1457 exception
1458 when hr_utility.hr_error then
1459 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1460 -- an application error has been raised. set the error flag
1461 -- to indicate an application error
1462 -- the error message should of been set already
1463 hr_message.provide_error;
1464 l_api_error_name := hr_message.last_message_name;
1465 if l_api_error_name = 'HR_7155_OBJECT_INVALID' then
1466 l_obj_fatal_error := true;
1467 exit;
1468 --if csr.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API' then
1469 -- fnd_message.set_name('PER','HR_FATAL_OBJECT_ERROR');
1470 -- l_obj_fatal_error := true;
1471 -- exit;
1472 --end if;
1473 else
1474 l_application_error := true;
1475
1476 -------------------------------------------------------------------
1477 -- 05/09/2002 Bug 2356339 Fix Begins
1478 -- We need to exit the loop here when there is an application error.
1479 -- This will happen when apporval is required and the final approver
1480 -- approved the change. When the Workflow responsibility to approve
1481 -- the transaction has no Security Profile attached, and the
1482 -- Business Group profile option is null and Cross Business Group
1483 -- equals to 'N', you will get an Application Error after the final
1484 -- approver approves the transaction. This problem usually happens
1485 -- in New Hire or Applicant Hire whereby the new employee created
1486 -- is not in the per_person_list table. In hr_process_person_ss.
1487 -- process_api, it call hr_employee_api.create_employee which
1488 -- eventually will call dt_api.return_min_start_date. This
1489 -- dt_api.return_min_start_date accesses the per_people_f secured
1490 -- view, you will get HR_7182_DT_NO_MIN_MAX_ROWS error with the
1491 -- following error text:
1492 -- No DateTrack row found in table per_people_f.
1493 -- When that happens, the l_application_error is set to true. However,
1494 -- if there is no Exit statement, this code will continue to call
1495 -- the next transaction step. Each of the subsequent step will fail
1496 -- with an error until the last step is called and the error from
1497 -- the last step will overwrite the initial real error message.
1498 -- Without the exit statement, it will be very difficult to pinpoint
1499 -- the location where the real problem occurred.
1500 ---------------------------------------------------------------------
1501
1502 EXIT; -- Bug 2356339 Fix
1503
1504 -- 05/09/2002 Bug 2356339 Fix Ends
1505
1506 end if;
1507 when others then
1508 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1509 -- a system error has occurred so raise it to stop
1510 -- processing of the transaction steps
1511 raise;
1512 end;
1513 end loop;
1514 -- check to see if any application errors where raised
1515 if l_obj_fatal_error then
1516 fnd_message.set_name('PER','HR_FATAL_OBJECT_ERROR');
1517 raise hr_utility.hr_error;
1518 elsif l_object_version_error then
1519 fnd_message.set_name('PER','HR_7155_OBJECT_INVALID');
1520 raise hr_utility.hr_error;
1521 elsif l_application_error or l_warning_error then
1522 raise hr_utility.hr_error;
1523 end if;
1524 hr_utility.set_location('Leaving: '|| l_proc,15);
1525
1526 exception
1527 when others then
1528 hr_utility.set_location('EXCEPTION: '|| l_proc,565);
1529 -- an application error, warning or system error was raised so
1530 -- keep raising it so the calling process must handle it
1531 raise;
1532 end process_transaction;
1533
1534
1535 -- ----------------------------------------------------------------------------
1536 -- |----------------------------< validate_transaction >----------------------|
1537 -- ----------------------------------------------------------------------------
1538 procedure validate_transaction
1539 (p_item_type in varchar2
1540 ,p_item_key in varchar2
1541 ,p_effective_date in varchar2 default null
1542 ,p_update_object_version in varchar2 default 'N'
1543 ,p_result out nocopy varchar2) is
1544 l_proc constant varchar2(100) := g_package || ' validate_transaction';
1545 begin
1546 hr_utility.set_location('Entering: '|| l_proc,5);
1547 validate_transaction (
1548 p_item_type => p_item_type
1549 ,p_item_key => p_item_key
1550 ,p_effective_date => p_effective_date
1551 ,p_update_object_version => p_update_object_version
1552 ,p_ignore_warnings => 'N'
1553 ,p_result => p_result );
1554
1555 hr_utility.set_location('Leaving: '|| l_proc,10);
1556 end;
1557
1558
1559 -- ns 11/06/2003: Bug 3223682: Overloaded validate_transaction with additional
1560 -- parameter p_ignore_warnings. The value for this would be NON_ASGN
1561 -- when invoked while editing a save/rfc/pending/wip action, since assignment
1562 -- related validations are already performed elsewhere.
1563 -- Dependency: Package specification modified
1564 -- ----------------------------------------------------------------------------
1565 -- |-----------------------< overloaded validate_transaction >-----------------|
1566 -- ----------------------------------------------------------------------------
1567 procedure validate_transaction
1568 (p_item_type in varchar2
1569 ,p_item_key in varchar2
1570 ,p_effective_date in varchar2 default null
1571 ,p_update_object_version in varchar2 default 'N'
1572 ,p_ignore_warnings in varchar2 default 'N'
1573 ,p_result out nocopy varchar2) is
1574 l_proc constant varchar2(100) := g_package || ' validate_transaction';
1575 begin
1576 hr_utility.set_location('Entering: '|| l_proc,5);
1577 p_result := 'N';
1578 savepoint VALIDATE_TRANSACTION;
1579 begin
1580 -- process the transaction reporting warnings if they exist
1581 process_transaction
1582 (p_item_type => p_item_type
1583 ,p_item_key => p_item_key
1584 ,p_update_object_version => p_update_object_version
1585 ,p_effective_date => p_effective_date
1586 ,p_ignore_warnings => p_ignore_warnings
1587 ,p_validate => false);
1588 rollback to VALIDATE_TRANSACTION;
1589 p_result := null;
1590 exception
1591 when hr_utility.hr_error then
1592 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1593 hr_message.provide_error;
1594 --result := fnd_message.get;
1595 p_result := hr_message.last_message_name;
1596 if p_result <> 'HR_7155_OBJECT_INVALID' and
1597 p_result <> 'HR_FATAL_OBJECT_ERROR' then
1598 p_result := hr_message.get_message_text;
1599 end if;
1600 rollback to VALIDATE_TRANSACTION;
1601 when others then
1602 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1603 p_result := sqlerrm;
1604 rollback to VALIDATE_TRANSACTION;
1605 end;
1606 hr_utility.set_location('Leaving: '|| l_proc,10);
1607 end validate_transaction;
1608 -- ----------------------------------------------------------------------------
1609 -- |----------------------------< commit_transaction >------------------------|
1610 -- ----------------------------------------------------------------------------
1611 procedure commit_transaction
1612 (itemtype in varchar2
1613 ,itemkey in varchar2
1614 ,actid in number
1615 ,funmode in varchar2
1616 ,result out nocopy varchar2) is
1617 --
1618 l_error_text varchar2(2000);
1619 l_sqlerrm varchar2(2000);
1620 l_proc constant varchar2(100) := g_package || ' commit_transaction';
1621 --
1622 l_commit_error exception;
1623 l_return_status varchar2(10);
1624 l_txn_id number;
1625
1626 begin
1627 hr_utility.set_location('Entering: '|| l_proc,5);
1628
1629 if funmode = 'RUN' then
1630 hr_utility.trace('In(if funmode = RUN) '|| l_proc);
1631
1632
1633 -- new call to reset the apps context, if does not match
1634 -- the approving user context
1635 if g_debug then
1636 hr_utility.set_location('calling setRespondedUserCtx',10);
1637 hr_utility.set_location('itemtype:'||itemtype,11);
1638 hr_utility.set_location('itemkey:'||itemkey,12);
1639 end if;
1640 begin
1641 setRespondedUserCtx(itemtype,itemkey);
1642 exception
1643 when others then
1644 -- do nothing ??
1645 if g_debug then
1646 hr_utility.set_location('Error calling setRespondedUserCtx SQLERRM' ||' '||to_char(SQLCODE),20);
1647 end if;
1648 null;
1649 end;
1650
1651 -- fix for bug 4454439
1652 begin
1653 -- re-intialize the performer roles
1654 hr_approval_ss.reinitperformerroles(p_notification_id=>null
1655 ,p_transaction_id=>null
1656 ,p_item_type=>itemtype
1657 ,p_item_key=>itemKey);
1658 exception
1659 when others then
1660 null;
1661 end;
1662
1663
1664 savepoint commit_transaction;
1665 begin
1666 -- process the transaction reporting warnings if they exist
1667 process_transaction
1668 (p_item_type => itemtype
1669 ,p_item_key => itemkey
1670 ,p_ignore_warnings => 'N');
1671
1672 -- call SWI commit Transaction
1673 l_txn_id := get_transaction_id(itemtype,itemkey);
1674
1675 if l_txn_id is not null then
1676 l_return_status :=hr_transaction_swi.commit_transaction
1677 ( p_transaction_id => l_txn_id );
1678 if l_return_status = 'E' then
1679 raise l_commit_error;
1680 end if;
1681 end if;
1682
1683 exception
1684 when l_commit_error then
1685 hr_utility.set_location('EXCEPTION: SWI COMMIT ERROR '|| l_proc,555);
1686 raise;
1687
1688 when others then
1689 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1690 l_sqlerrm := sqlerrm;
1691 raise;
1692 end;
1693 -- transition workflow with SUCCESS
1694 result := 'COMPLETE:SUCCESS';
1695 elsif funmode = 'CANCEL' then
1696 hr_utility.trace('In(if funmode = CANCEL) '|| l_proc);
1697 result := 'COMPLETE:';
1698 end if;
1699 hr_utility.set_location('Leaving: '|| l_proc,15);
1700 exception
1701 when hr_utility.hr_error then
1702 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1703 -- rollback any work
1704 rollback to commit_transaction;
1705 --
1706 l_error_text := hr_utility.get_message;
1707 if l_error_text is null then
1708 l_error_text := fnd_message.get;
1709 end if;
1710 -- 1903606
1711 wf_engine.setitemattrtext
1712 (itemtype => itemtype
1713 ,itemkey => itemkey
1714 ,aname => 'TRAN_SUBMIT'
1715 ,avalue => 'E');
1716
1717 -- set the ERROR_MESSAGE_TEXT
1718 wf_engine.setitemattrtext
1719 (itemtype => itemtype
1720 ,itemkey => itemkey
1721 ,aname => 'ERROR_MESSAGE_TEXT'
1722 ,avalue => nvl(l_error_text, l_sqlerrm));
1723
1724 -- update the transaction table status
1725 hr_transaction_api.update_transaction(
1726 p_transaction_id => get_transaction_id
1727 (p_item_type => itemtype
1728 ,p_item_key => itemkey),
1729 p_status => 'E');
1730
1731 -- an application error or warning has been set
1732 result := 'COMPLETE:APPLICATION_ERROR';
1733 when others then
1734 hr_utility.set_location('EXCEPTION: '|| l_proc,565);
1735 -- rollback any work
1736 rollback to commit_transaction;
1737 -- 1903606
1738 wf_engine.setitemattrtext
1739 (itemtype => itemtype
1740 ,itemkey => itemkey
1741 ,aname => 'TRAN_SUBMIT'
1742 ,avalue => 'E');
1743 -- set the ERROR_MESSAGE_TEXT
1744 wf_engine.setitemattrtext
1745 (itemtype => itemtype
1746 ,itemkey => itemkey
1747 ,aname => 'ERROR_MESSAGE_TEXT'
1748 ,avalue => l_sqlerrm);
1749 -- update the transaction table status
1750 hr_transaction_api.update_transaction(
1751 p_transaction_id => get_transaction_id
1752 (p_item_type => itemtype
1753 ,p_item_key => itemkey),
1754 p_status => 'E');
1755
1756 -- system error
1757 result := 'COMPLETE:SYSTEM_ERROR';
1758 end commit_transaction;
1759
1760 -- ----------------------------------------------------------------------------
1761 -- |---------------------< commit_approval_transaction >----------------------|
1762 -- ----------------------------------------------------------------------------
1763 procedure commit_approval_transaction
1764 (itemtype in varchar2
1765 ,itemkey in varchar2
1766 ,actid in number
1767 ,funmode in varchar2
1768 ,result out nocopy varchar2) is
1769 l_proc constant varchar2(100) := g_package || ' commit_approval_transaction';
1770 begin
1771 hr_utility.set_location('Entering: '|| l_proc,5);
1772 if funmode = 'RUN' then
1773 savepoint commit_approval_transaction;
1774 -- process the transaction reporting warnings if they exist
1775 process_transaction
1776 (p_item_type => itemtype
1777 ,p_item_key => itemkey
1778 ,p_ignore_warnings => 'Y');
1779 -- transition workflow with SUCCESS
1780 result := 'SUCCESS';
1781 elsif funmode = 'CANCEL' then
1782 null;
1783 end if;
1784 hr_utility.set_location('Leaving: '|| l_proc,10);
1785 exception
1786 when others then
1787 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1788 -- rollback any work
1789 rollback to commit_approval_transaction;
1790 -- system error
1791 raise;
1792 end commit_approval_transaction;
1793 -- ----------------------------------------------------------------------------
1794 -- |-----------------------------< retry_transaction >------------------------|
1795 -- ----------------------------------------------------------------------------
1796 procedure retry_transaction
1797 (p_item_type in varchar2
1798 ,p_item_key in varchar2
1799 ,p_actid in number) is
1800 l_proc constant varchar2(100) := g_package || ' retry_transaction';
1801
1802 begin
1803 hr_utility.set_location('Entering: '|| l_proc,5);
1804 savepoint retry_transaction;
1805 -- process the transaction ignoring warnings if they exist
1806 process_transaction
1807 (p_item_type => p_item_type
1808 ,p_item_key => p_item_key
1809 ,p_ignore_warnings => 'Y');
1810 -- Complete the activity with SUCCESS
1811 hr_workflow_utility.workflow_transition
1812 (p_item_type => p_item_type
1813 ,p_item_key => p_item_key
1814 ,p_actid => p_actid
1815 ,p_result => 'SUCCESS');
1816 --wf_engine.completeactivity
1817 -- (itemtype => p_item_type
1818 -- ,itemkey => p_item_key
1819 -- ,activity => '#'||p_actid
1820 -- ,result => 'SUCCESS');
1821 -- commit the transaction
1822 -- commit;
1823 hr_utility.set_location('Leaving: '|| l_proc,10);
1824 exception
1825 when hr_utility.hr_error then
1826 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1827 -- rollback any work
1828 rollback to retry_transaction;
1829 -- an application error has been set
1830 -- redisplay the errors
1831 raise;
1832 when others then
1833 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1834 -- rollback any work
1835 rollback to retry_transaction;
1836 -- system error
1837 raise;
1838 end retry_transaction;
1839
1840 --
1841 /*------------------------------------------------------------------------------
1842 |
1843 | Name : save_transaction_step
1844 |
1845 | Purpose :
1846 |
1847 | Saves the records into Transaction Tables.
1848 |
1849 +-----------------------------------------------------------------------------*/
1850 PROCEDURE save_transaction_step
1851 (p_item_type IN VARCHAR2
1852 ,p_item_key IN VARCHAR2
1853 ,p_actid IN NUMBER
1854 ,p_login_person_id IN NUMBER
1855 ,p_transaction_step_id IN OUT NOCOPY NUMBER
1856 ,p_api_name IN VARCHAR2 default null
1857 ,p_api_display_name IN VARCHAR2 DEFAULT NULL
1858 ,p_transaction_data IN TRANSACTION_TABLE
1859 ,p_product_code in varchar2 default null
1860 ,p_url in varchar2 default null
1861 ,p_status in varchar2 default null
1862 ,p_section_display_name in varchar2 default null
1863 ,p_function_id in number default null
1864 ,p_transaction_ref_table in varchar2 default null
1865 ,p_transaction_ref_id in number default null
1866 ,p_transaction_type in varchar2 default null
1867 ,p_assignment_id in number default null
1868 ,p_api_addtnl_info in varchar2 default null
1869 ,p_selected_person_id in number default null
1870 ,p_transaction_effective_date in date default null
1871 ,p_process_name in varchar2 default null
1872 ,p_plan_id in number default null
1873 ,p_rptg_grp_id in number default null
1874 ,p_effective_date_option in varchar2 default null
1875 ) AS
1876
1877
1878 cursor get_trans_id ( p_trans_step_id HR_API_TRANSACTION_STEPS.transaction_step_id%TYPE) is
1879 select transaction_id from hr_api_transaction_steps
1880 where transaction_step_id = p_trans_step_id;
1881
1882 l_count INTEGER := 0;
1883 l_result VARCHAR2(100);
1884 l_trs_ovn hr_api_transaction_steps.object_version_number%TYPE;
1885 l_original_date date; --ns
1886 l_original_number number; --ns
1887 l_transaction_state varchar2(10) := hr_api.g_varchar2; --ns
1888 l_date date;
1889 l_value_error BOOLEAN := false;
1890 l_proc constant varchar2(100) := g_package || ' save_transaction_step';
1891
1892 l_function_id hr_api_transactions.function_id%TYPE;
1893 ln_selected_person_id hr_api_transactions.selected_person_id%TYPE;
1894 lv_process_name hr_api_transactions.process_name%TYPE;
1895 lv_status hr_api_transactions.status%TYPE;
1896 lv_section_display_name hr_api_transactions.section_display_name%TYPE;
1897 ln_assignment_id hr_api_transactions.assignment_id%TYPE;
1898 ld_trans_effec_date hr_api_transactions.transaction_effective_date%TYPE;
1899 lv_transaction_type hr_api_transactions.transaction_type%TYPE;
1900 ln_transaction_id hr_api_transaction_steps.transaction_id%TYPE;
1901
1902 BEGIN
1903
1904 hr_utility.set_location('Entering: '|| l_proc,5);
1905 -- populate the values in case of null before passing it to API.
1906 -- CompWorkBench
1907
1908 populate_null_values
1909 (p_item_type
1910 ,p_item_key
1911 ,p_function_id
1912 ,p_selected_person_id
1913 ,p_process_name
1914 ,p_status
1915 ,p_section_display_name
1916 ,p_assignment_id
1917 ,p_transaction_effective_date
1918 ,p_transaction_type
1919 ,l_function_id
1920 ,ln_selected_person_id
1921 ,lv_process_name
1922 ,lv_status
1923 ,lv_section_display_name
1924 ,ln_assignment_id
1925 ,ld_trans_effec_date
1926 ,lv_transaction_type
1927 );
1928
1929 -- Check to see if Transaction Step exists
1930 IF p_transaction_step_id IS NULL THEN
1931 -- Create Transaction
1932 -- Following procedure will set item attribute 'TRANSACTION_ID'.
1933 -- Later on you can access the value of TRANSACTION_ID by
1934 -- calling HR_TRANSACTION_SS.GET_TRANSACTION_ID
1935
1936 start_transaction
1937 (itemtype => p_item_type
1938 ,itemkey => p_item_key
1939 ,actid => p_actid
1940 ,funmode => 'RUN'
1941 ,p_login_person_id => p_login_person_id
1942 ,result => l_result
1943 ,p_product_code => p_product_code
1944 ,p_url => p_url
1945 ,p_status => lv_status
1946 ,p_section_display_name => lv_section_display_name
1947 ,p_function_id => l_function_id
1948 ,p_transaction_ref_table => 'HR_API_TRANSACTIONS'
1949 ,p_transaction_ref_id => p_transaction_ref_id
1950 ,p_transaction_type => lv_transaction_type
1951 ,p_assignment_id => ln_assignment_id
1952 ,p_api_addtnl_info => p_api_addtnl_info
1953 ,p_selected_person_id => ln_selected_person_id
1954 ,p_transaction_effective_date => ld_trans_effec_date
1955 ,p_process_name => lv_process_name
1956 ,p_plan_id=> p_plan_id
1957 ,p_rptg_grp_id=> p_rptg_grp_id
1958 ,p_effective_date_option=> p_effective_date_option
1959 );
1960 -- Create a Transaction Step for this Transaction.
1961 hr_transaction_api.create_transaction_step
1962 (p_validate => false
1963 ,p_creator_person_id => p_login_person_id
1964 ,p_transaction_id => get_transaction_id
1965 (p_item_type => p_item_type
1966 ,p_item_key => p_item_key)
1967 ,p_api_name => p_api_name
1968 ,p_api_display_name => p_api_display_name
1969 ,p_item_type => p_item_type
1970 ,p_item_key => p_item_key
1971 ,p_activity_id => p_actid
1972 ,p_transaction_step_id => p_transaction_step_id
1973 ,p_object_version_number => l_trs_ovn);
1974 END IF;
1975
1976 hr_transaction_api.g_update_flag := 'N';
1977 l_count := p_transaction_data.COUNT;
1978 hr_utility.trace('In(for I in csr_trs loop)'||l_proc);
1979 FOR i IN 1..l_count LOOP
1980 IF p_transaction_data(i).param_data_type = 'DATE' THEN
1981 --ensure that the effective date is in the correct format
1982 BEGIN
1983 l_date := trunc(to_date(p_transaction_data(i).param_value,
1984 g_date_format));
1985 l_original_date := trunc(to_date(p_transaction_data(i).param_original_value,
1986 g_date_format)); --ns
1987 EXCEPTION
1988 WHEN OTHERS THEN
1989 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1990 -- the date check failed therefore we must report the error
1991 -- and reset to the original value
1992 l_value_error := true;
1993 fnd_message.set_name('PER', 'HR_51778_WEB_KIOSK_INV_EDATE');
1994 l_date := trunc(sysdate);
1995 END;
1996 hr_transaction_api.set_date_value
1997 (p_transaction_step_id => p_transaction_step_id
1998 ,p_person_id => p_login_person_id
1999 ,p_name => p_transaction_data(i).param_name
2000 ,p_value => l_date
2001 ,p_original_value => l_original_date);
2002
2003 IF l_value_error THEN
2004 RAISE hr_utility.hr_error;
2005 END IF;
2006 ELSIF p_transaction_data(i).param_data_type = 'NUMBER' THEN
2007 BEGIN
2008 --ns start
2009 IF p_transaction_data(i).param_original_value is NULL THEN
2010 l_original_number := null;
2011 ELSE
2012 l_original_number := to_number(p_transaction_data(i).param_original_value );
2013 END IF;
2014 --ns end
2015 -----------------------------------------------------------------------
2016 -- 11/07/00 Need to test that if the param_value is null or not. If
2017 -- it is null, don't use the to_number function to try to convert a null
2018 -- to a number value. Otherwise, you'll get ORA-06502 PL/SQL numeric
2019 -- or value error.
2020 -----------------------------------------------------------------------
2021 IF p_transaction_data(i).param_value is NULL
2022 THEN
2023 hr_transaction_api.set_number_value
2024 (p_transaction_step_id => p_transaction_step_id
2025 ,p_person_id => p_login_person_id
2026 ,p_name => p_transaction_data(i).param_name
2027 ,p_original_value => l_original_number); --ns
2028 ELSE
2029 hr_transaction_api.set_number_value
2030 (p_transaction_step_id => p_transaction_step_id
2031 ,p_person_id => p_login_person_id
2032 ,p_name => p_transaction_data(i).param_name
2033 ,p_value => to_number(p_transaction_data(i).param_value)
2034 ,p_original_value => l_original_number); --ns
2035 END IF;
2036 exception
2037 when others then
2038 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
2039 --SQLERRM
2040 RAISE hr_utility.hr_error;
2041 END;
2042 ELSIF p_transaction_data(i).param_data_type = 'VARCHAR2' THEN
2043 BEGIN
2044 hr_transaction_api.set_varchar2_value
2045 (p_transaction_step_id => p_transaction_step_id
2046 ,p_person_id => p_login_person_id
2047 ,p_name => p_transaction_data(i).param_name
2048 ,p_value => p_transaction_data(i).param_value
2049 ,p_original_value => p_transaction_data(i).param_original_value); --ns
2050 exception
2051 when others then
2052 hr_utility.set_location('EXCEPTION: '|| l_proc,565);
2053 --SQLERRM
2054 RAISE hr_utility.hr_error;
2055 END;
2056 END IF;
2057 END LOOP;
2058
2059 if (hr_transaction_api.g_update_flag = 'Y') then
2060 l_transaction_state := 'W';
2061 hr_transaction_api.g_update_flag := 'N';
2062 end if;
2063
2064 -- --------------------------------------------------------------------
2065 -- for each of the transaction values which need to be either created or
2066 -- updated set the transaction value
2067 -- --------------------------------------------------------------------
2068 -- Find out how many variables we have to set
2069
2070 IF p_transaction_step_id IS NOT NULL THEN
2071 hr_utility.trace('In ( IF p_transaction_step_id IS NOT NULL ) '|| l_proc);
2072
2073 OPEN get_trans_id(p_transaction_step_id);
2074 FETCH get_trans_id into ln_transaction_id;
2075 CLOSE get_trans_id;
2076
2077 IF ln_transaction_id IS NOT NULL THEN
2078 hr_utility.trace('In ( I IF ln_transaction_id IS NOT NULL ) '|| l_proc);
2079 Begin
2080 if ( wf_engine.GetItemAttrText(p_item_type ,p_item_key ,'HR_APPROVAL_PRC_VERSION') = 'V5' ) then
2081 lv_status := hr_api.g_varchar2; -- so that the original value is picked from txn table;
2082 end if;
2083 Exception
2084 when Others then -- wf attribute not found
2085 hr_utility.set_location('EXCEPTION: '|| l_proc,570);
2086 null;
2087 End;
2088
2089 hr_transaction_api.update_transaction
2090 (p_transaction_id => ln_transaction_id
2091 ,p_status => lv_status
2092 ,p_transaction_state => l_transaction_state
2093 ,p_transaction_effective_date => ld_trans_effec_date
2094 );
2095 END IF;
2096 END IF;
2097
2098 hr_utility.set_location('Leaving: '|| l_proc,25);
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 hr_utility.set_location('EXCEPTION: '|| l_proc,575);
2102 --SQLERRM
2103 raise;
2104 END save_transaction_step;
2105
2106 /*
2107 ||=======================================================================
2108 || FUNCTION : get_activity_trans_step_id
2109 || DESCRIPTION : This will return the transaction step id for a given
2110 || activity name and from possible 'active' transaction
2111 || steps.
2112 ||=======================================================================
2113 */
2114 FUNCTION get_activity_trans_step_id
2115 (p_activity_name IN
2116 wf_item_activity_statuses_v.activity_name%TYPE
2117 ,p_trans_step_id_tbl IN hr_util_web.g_varchar2_tab_type)
2118 RETURN hr_api_transaction_steps.transaction_step_id%TYPE IS
2119 ln_transaction_step_id
2120 hr_api_transaction_steps.transaction_step_id%TYPE;
2121 li_step_count INTEGER;
2122 l_proc constant varchar2(100) := g_package || ' get_activity_trans_step_id';
2123 BEGIN
2124
2125 hr_utility.set_location('Entering: '|| l_proc,5);
2126 li_step_count := p_trans_step_id_tbl.COUNT;
2127 hr_utility.trace('Going to ( FOR i IN 0..li_step_count LOOP): '|| l_proc);
2128 FOR i IN 0..li_step_count LOOP
2129 IF p_activity_name =
2130 hr_transaction_api.get_varchar2_value
2131 (p_transaction_step_id => p_trans_step_id_tbl(i)
2132 ,p_name => 'p_activity_name')
2133 THEN
2134 RETURN p_trans_step_id_tbl(i);
2135 END IF;
2136 END LOOP;
2137 hr_utility.set_location('Leaving: '|| l_proc,15);
2138
2139
2140 RETURN NULL;
2141
2142
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2146 --SQLERRM
2147 raise;
2148 END get_activity_trans_step_id;
2149
2150 /* FUNCTION check_txn_step_exists
2151 -- function to check whether data exists in txn steps
2152 */
2153 FUNCTION check_txn_step_exists (
2154 p_item_type IN wf_items.item_type%TYPE,
2155 p_item_key IN wf_items.item_key%TYPE,
2156 p_actid IN NUMBER )
2157 RETURN BOOLEAN
2158 IS
2159
2160 ln_transaction_id NUMBER ;
2161 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
2162 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
2163 ln_trans_step_rows number ;
2164 l_proc constant varchar2(100) := g_package || ' check_txn_step_exists';
2165 BEGIN
2166 hr_utility.set_location('Entering: '|| l_proc,5);
2167 ln_transaction_id := get_transaction_id
2168 (p_Item_Type => p_item_type
2169 , p_Item_Key => p_item_key);
2170
2171 IF ln_transaction_id IS NOT NULL
2172 THEN
2173 hr_utility.trace(' In(IF ln_transaction_id IS NOT NULL)'|| l_proc);
2174 hr_transaction_api.get_transaction_step_info
2175 (p_Item_Type => p_item_type,
2176 p_Item_Key => p_item_key,
2177 p_activity_id =>p_actid,
2178 p_transaction_step_id => ltt_trans_step_ids,
2179 p_object_version_number => ltt_trans_obj_vers_num,
2180 p_rows => ln_trans_step_rows);
2181
2182 -- if no transaction steps are found , return
2183 IF ln_trans_step_rows >= 1
2184 THEN
2185 hr_utility.trace(' In( IF ln_trans_step_rows >= 1)'|| l_proc);
2186 hr_utility.set_location('Leaving: '|| l_proc,20);
2187 return TRUE ;
2188 ELSE
2189 hr_utility.set_location('Leaving: '|| l_proc,20);
2190 return FALSE ;
2191 END IF ;
2192
2193 END IF ;
2194 hr_utility.set_location('Leaving: '|| l_proc,20);
2195 return FALSE ;
2196 END ;
2197
2198
2199
2200 -- ---------------------------------------------------------
2201 -- Procedure to delete a transaction step by p_activity_name
2202 -- --------------------------------------------------------------
2203 PROCEDURE delete_trn_step_by_act_name(
2204 p_item_type IN varchar2,
2205 p_item_key IN varchar2 ,
2206 p_actid IN varchar2 ,
2207 p_activity_name IN varchar2,
2208 p_login_person_id IN varchar2 )
2209 IS
2210 ln_ovn NUMBER ;
2211 ln_transaction_step_id NUMBER;
2212 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
2213 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
2214 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
2215 ln_trans_step_rows NUMBER ;
2216 ln_value_id NUMBER ;
2217 l_proc constant varchar2(100) := g_package || ' delete_trn_step_by_act_name';
2218 BEGIN
2219 hr_utility.set_location('Entering: '|| l_proc,5);
2220 IF p_activity_name is not null THEN
2221 hr_utility.trace('In(IF p_activity_name is not null) '|| l_proc);
2222
2223 ln_transaction_id := get_transaction_id
2224 (p_Item_Type => p_item_type,
2225 p_Item_Key => p_item_key);
2226 IF ln_transaction_id IS NOT NULL
2227 THEN
2228 hr_utility.trace('In( IF ln_transaction_id IS NOT NULL) '|| l_proc);
2229 hr_transaction_api.get_transaction_step_info
2230 (p_Item_Type => p_item_type,
2231 p_Item_Key => p_item_key,
2232 p_activity_id =>p_actid,
2233 p_transaction_step_id => ltt_trans_step_ids,
2234 p_object_version_number => ltt_trans_obj_vers_num,
2235 p_rows => ln_trans_step_rows);
2236
2237
2238 -- if no transaction steps are found , return
2239 IF ln_trans_step_rows > 0
2240 THEN
2241 hr_utility.trace('In( IF ln_trans_step_rows > 0) '|| l_proc);
2242 ln_transaction_step_id :=
2243 hr_transaction_ss.get_activity_trans_step_id
2244 (p_activity_name =>p_activity_name,
2245 p_trans_step_id_tbl => ltt_trans_step_ids);
2246 delete_transaction_step(
2247 p_transaction_step_id => ln_transaction_step_id,
2248 p_login_person_id => p_login_person_id);
2249 END IF ;
2250 END IF;
2251 END IF;
2252 hr_utility.set_location('Leaving: '|| l_proc,25);
2253
2254 EXCEPTION
2255 WHEN OTHERS THEN
2256 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2257 raise;
2258 END delete_trn_step_by_act_name;
2259
2260 -- -------------------------------------------------------------------------
2261 -- delete_transaction_steps is used by FWK java class. The parameters are
2262 -- defined as varchar2. Please do not change the parameter data type.
2263 -- -------------------------------------------------------------------------
2264
2265 PROCEDURE delete_transaction_steps(
2266 p_item_type IN varchar2,
2267 p_item_key IN varchar2,
2268 p_actid IN varchar2 default null,
2269 p_login_person_id IN varchar2) IS
2270
2271 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
2272 l_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
2273 l_trans_step_rows NUMBER;
2274 l_proc constant varchar2(100) := g_package || ' delete_transaction_steps';
2275
2276 BEGIN
2277 hr_utility.set_location('Entering: '|| l_proc,5);
2278 if p_actid IS NULL then
2279 hr_transaction_api.get_transaction_step_info (
2280 p_Item_Type => p_item_type,
2281 p_Item_Key => p_item_key,
2282 p_transaction_step_id => l_trans_step_ids,
2283 p_object_version_number => l_trans_obj_vers_num,
2284 p_rows => l_trans_step_rows
2285 );
2286 else
2287 hr_transaction_api.get_transaction_step_info (
2288 p_Item_Type => p_item_type,
2289 p_Item_Key => p_item_key,
2290 p_activity_id => to_number(p_actid),
2291 p_transaction_step_id => l_trans_step_ids,
2292 p_object_version_number => l_trans_obj_vers_num,
2293 p_rows => l_trans_step_rows
2294 );
2295 end if;
2296 hr_utility.trace('Going to ( FOR i IN 0..(l_trans_step_rows - 1) LOOP) '|| l_proc);
2297 FOR i IN 0..(l_trans_step_rows - 1) LOOP
2298 delete_transaction_step
2299 (p_transaction_step_id => l_trans_step_ids(i)
2300 ,p_object_version_number => l_trans_obj_vers_num(i)
2301 ,p_login_person_id => p_login_person_id);
2302 END LOOP;
2303
2304 --hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
2305 --hr_utility.raise_error;
2306 hr_utility.set_location('Leaving: '|| l_proc,15);
2307
2308 EXCEPTION
2309 WHEN OTHERS THEN
2310 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2311 raise;
2312
2313 END delete_transaction_steps;
2314
2315 -- -------------------------------------------------------------------------
2316 -- delete_transaction_step is used by FWK java class. The parameters are
2317 -- defined as varchar2. Please do not change the parameter data type.
2318 -- -------------------------------------------------------------------------
2319
2320 PROCEDURE delete_transaction_step(
2321 p_transaction_step_id IN varchar2,
2322 p_object_version_number IN varchar2 default null,
2323 p_login_person_id IN varchar2) IS
2324
2325 l_object_version_number number;
2326 l_proc constant varchar2(100) := g_package || ' delete_transaction_step';
2327
2328 ----- bug 5102128
2329 L_DEL_PHONE_TYPE varchar2(100):=null;
2330 L_DEL_PHONE_NUMBER varchar2(100):=null;
2331 L_DEL_PHONE_ID number;
2332 ----- bug 5102128
2333
2334
2335 BEGIN
2336 hr_utility.set_location('Entering: '|| l_proc,5);
2337 if p_transaction_step_id is not null then
2338 hr_utility.trace('In (if p_transaction_step_id is not null ) '|| l_proc);
2339 if p_object_version_number is null then
2340 hr_utility.trace('In (if p_object_version_number is null ) '|| l_proc);
2341 l_object_version_number :=
2342 get_transaction_step_ovn(to_number(p_transaction_step_id));
2343 else
2344 hr_utility.trace('In else of (if p_object_version_number is null ) '|| l_proc);
2345 l_object_version_number := to_number(p_object_version_number);
2346 end if;
2347
2348 ----- bug 5102128
2349 L_DEL_PHONE_NUMBER := hr_transaction_api.get_varchar2_value
2350 (p_transaction_step_id => to_number(p_transaction_step_id)
2351 ,p_name => 'P_PHONE_NUMBER');
2352 L_DEL_PHONE_TYPE := hr_transaction_api.get_varchar2_value
2353 (p_transaction_step_id => to_number(p_transaction_step_id)
2354 ,p_name => 'P_PHONE_TYPE');
2355 L_DEL_PHONE_ID := hr_transaction_api.get_number_value
2356 (p_transaction_step_id => to_number(p_transaction_step_id)
2357 ,p_name => 'P_PHONE_ID');
2358
2359 if L_DEL_PHONE_ID is not null and L_DEL_PHONE_TYPE = 'DELETE' and L_DEL_PHONE_NUMBER = 'DELETE_NUMBER' then
2360 null;
2361 else
2362 ----- bug 5102128
2363
2364 hr_transaction_api.delete_transaction_step
2365 (p_validate => FALSE
2366 ,p_transaction_step_id => to_number(p_transaction_step_id)
2367 ,p_object_version_number => l_object_version_number
2368 ,p_person_id => to_number(p_login_person_id));
2369 ----- bug 5102128
2370 end if;
2371 ----- bug 5102128
2372 end if;
2373 hr_utility.set_location('Leaving: '|| l_proc,20);
2374
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2378 raise;
2379
2380 END delete_transaction_step;
2381
2382 function get_transaction_step_ovn(
2383 p_transaction_step_id in number)
2384 return number is
2385
2386 cursor csr_hats is
2387 select hats.object_version_number
2388 from hr_api_transaction_steps hats
2389 where hats.transaction_step_id = p_transaction_step_id;
2390
2391 l_ovb number;
2392 l_proc constant varchar2(100) := g_package || ' get_transaction_step_ovn';
2393 begin
2394 hr_utility.set_location('Entering: '|| l_proc,5);
2395 open csr_hats;
2396 fetch csr_hats into l_ovb;
2397 if csr_hats%notfound then
2398 l_ovb := null;
2399 end if;
2400 close csr_hats;
2401 hr_utility.set_location('Leaving: '|| l_proc,10);
2402 return l_ovb;
2403
2404 EXCEPTION
2405 WHEN OTHERS THEN
2406 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2407 raise;
2408
2409 end get_transaction_step_ovn;
2410
2411 procedure set_transaction_value
2412 (p_transaction_step_id in varchar2
2413 ,p_login_person_id in varchar2
2414 ,p_datatype in varchar2
2415 ,p_name in varchar2
2416 ,p_value in varchar2) is
2417
2418 l_varchar2_value varchar2(2000);
2419 l_number_value number;
2420 l_date date;
2421 l_proc constant varchar2(100) := g_package || ' set_transaction_value';
2422 begin
2423 hr_utility.set_location('Entering: '|| l_proc,5);
2424 if (p_datatype = 'VARCHAR2') then
2425 hr_utility.trace('In (if (p_datatype = VARCHAR2)) '|| l_proc);
2426 hr_transaction_api.set_varchar2_value
2427 (p_transaction_step_id => p_transaction_step_id
2428 ,p_person_id => p_login_person_id
2429 ,p_name => p_name
2430 ,p_value => p_value);
2431 elsif p_datatype = 'NUMBER' then
2432 hr_utility.trace('In ( elsif p_datatype = NUMBER) '|| l_proc);
2433 hr_transaction_api.set_number_value
2434 (p_transaction_step_id => p_transaction_step_id
2435 ,p_person_id => p_login_person_id
2436 ,p_name => p_name
2437 ,p_value => to_number(p_value));
2438 elsif p_datatype = 'DATE' then
2439 hr_utility.trace('In ( elsif p_datatype = DATE) '|| l_proc);
2440 hr_transaction_api.set_date_value
2441 (p_transaction_step_id => p_transaction_step_id
2442 ,p_person_id => p_login_person_id
2443 ,p_name => p_name
2444 ,p_value => to_date(p_value, g_date_format));
2445 else
2446 hr_utility.trace('In else of (if (p_datatype = VARCHAR2)) '|| l_proc);
2447 --raise datetype error;
2448 null;
2449 end if;
2450 hr_utility.set_location('Leaving: '|| l_proc,15);
2451 EXCEPTION
2452 WHEN OTHERS THEN
2453
2454 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2455 raise;
2456 end set_transaction_value;
2457
2458 procedure create_transaction_step
2459 (p_item_type in varchar2
2460 ,p_item_key in varchar2
2461 ,p_actid in varchar2
2462 ,p_login_person_id in varchar2
2463 ,p_api_name in varchar2
2464 ,p_transaction_step_id out nocopy varchar2
2465 ,p_object_version_number out nocopy varchar2) is
2466
2467 l_transaction_id number;
2468 l_result varchar2(100);
2469 l_trns_object_version_number number;
2470 l_proc constant varchar2(100) := g_package || ' create_transaction_step';
2471
2472 begin
2473 hr_utility.set_location('Entering: '|| l_proc,5);
2474 l_transaction_id := get_transaction_id
2475 (p_item_type => p_item_type
2476 ,p_item_key => p_item_key);
2477 if l_transaction_id is null then
2478 start_transaction
2479 (itemtype => p_item_type
2480 ,itemkey => p_item_key
2481 ,actid => to_number(p_actid)
2482 ,funmode => 'RUN'
2483 ,p_login_person_id => to_number(p_login_person_id)
2484 ,result => l_result);
2485 --
2486 l_transaction_id:= get_transaction_id
2487 (p_item_type => p_item_type
2488 ,p_item_key => p_item_key);
2489 end if;
2490
2491 hr_transaction_api.create_transaction_step
2492 (p_validate => false
2493 ,p_creator_person_id => to_number(p_login_person_id)
2494 ,p_transaction_id => l_transaction_id
2495 ,p_api_name => p_api_name
2496 ,p_item_type => p_item_type
2497 ,p_item_key => p_item_key
2498 ,p_activity_id => to_number(p_actid)
2499 ,p_transaction_step_id => p_transaction_step_id
2500 ,p_object_version_number => l_trns_object_version_number);
2501 hr_utility.set_location('Leaving: '|| l_proc,10);
2502 end create_transaction_step;
2503
2504 --
2505 -- --------------------<get_review_regions>----------------------------- --
2506 -- Procedure to get the review region item names, step ids and activity ids of the
2507 -- update regions involved in a current transaction.
2508 -- This procedure return one string which will in turn gets parsed by the
2509 -- jdbc code that is calling this.
2510 -- ---------------------------------------------------------------------- --
2511 --
2512 procedure get_review_regions
2513 (p_item_key IN VARCHAR2
2514 ,p_item_Type IN VARCHAR2
2515 ,p_review_regions OUT NOCOPY VARCHAR2
2516 ,p_status OUT NOCOPY VARCHAR2) IS
2517
2518 l_tmp_proc_call varchar2(32000);
2519 l_tmp_proc_call1 varchar2(32000);
2520 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
2521 l_delimiter varchar2(3) := '|!|';
2522 l_start number;
2523 l_start1 number;
2524 l_pos number;
2525 l_pos1 number;
2526 l_count number := 0;
2527 l_count1 number;
2528 l_index number;
2529 l_loop_count number;
2530 l_last_rec boolean default TRUE;
2531 l_review_proc_list VARCHAR2(32000);
2532
2533 l_tx_step_count number := 0;
2534 l_proc constant varchar2(100) := g_package || ' get_review_regions';
2535 -- Local cursor definations
2536 -- csr_wf_active_item Returns the item key of any process which
2537 -- is currently active with the name of p_process and belonging to
2538 -- the given person id
2539 cursor csr_hatv (
2540 p_item_key in hr_api_transaction_steps.item_key%type
2541 ,p_item_Type in hr_api_transaction_steps.item_Type%type
2542 ,p_name in hr_api_transaction_values.name%type
2543 ) is
2544 select val.varchar2_value, val.transaction_step_id
2545 from hr_api_transaction_values val, hr_api_transaction_steps step
2546 where step.item_type = p_item_type
2547 and step.item_key = p_item_key
2548 and step.transaction_step_id = val.transaction_step_id
2549 and val.name = p_name
2550 and val.varchar2_value Is Not Null
2551 order by step.processing_order, step.transaction_step_id asc;
2552
2553 cursor csr_act (
2554 p_transaction_step_id
2555 in hr_api_transaction_steps.transaction_step_id%type
2556 ,p_name in hr_api_transaction_values.name%type
2557 ) is
2558 select varchar2_value
2559 from hr_api_transaction_values
2560 where transaction_step_id = p_transaction_step_id
2561 and name = p_name
2562 and varchar2_value Is Not Null;
2563
2564 begin
2565 hr_utility.set_location('Entering: '|| l_proc,5);
2566 -- to hold error message if raised;
2567 p_status := 'SUCCESS';
2568
2569 select count(transaction_step_id)
2570 into l_tx_step_count
2571 from hr_api_transaction_steps
2572 where item_key = p_item_key
2573 and item_type = p_item_type;
2574
2575 if l_tx_step_count <> 0 then -- user has changed data in one of the previous pages
2576 hr_utility.trace('In ( if l_tx_step_count <> 0 ) '|| l_proc);
2577
2578 -- Initialize Table index to 0
2579 l_index := 0;
2580 hr_utility.trace('Going to ( for I in csr_hatv ( p_item_key => p_item_key ) '|| l_proc);
2581 for I in csr_hatv ( p_item_key => p_item_key
2582 ,p_item_type => p_item_type
2583 ,p_name => 'P_REVIEW_PROC_CALL'
2584 ) loop
2585 l_tmp_proc_call := I.varchar2_value;
2586 l_transaction_step_id := I.transaction_step_id;
2587
2588 open csr_act (p_transaction_step_id => l_transaction_step_id
2589 ,p_name => 'P_REVIEW_ACTID'
2590 );
2591 fetch csr_act into l_tmp_proc_call1;
2592 IF csr_act%NOTFOUND THEN
2593 l_tmp_proc_call1 := NULL;
2594 END IF;
2595 close csr_act;
2596 -- Parse the string based on |!|
2597 l_start := 1;
2598 l_start1 := 1;
2599 l_pos := instr(l_tmp_proc_call, l_delimiter,l_start, 1);
2600 l_pos1 := instr(l_tmp_proc_call1, l_delimiter,l_start1, 1);
2601 -- Go in into For loop only if there is delimiter
2602 if l_pos <> 0 then
2603 l_count := length(l_tmp_proc_call);
2604 l_count1 := length(l_tmp_proc_call1);
2605 if l_count <> 0 then
2606 FOR i IN 1..l_count LOOP
2607 -- Find the delimter and its position
2608 l_pos := instr(l_tmp_proc_call, l_delimiter,l_start, 1);
2609 l_pos1:= instr(l_tmp_proc_call1, l_delimiter,l_start1, 1);
2610 if l_pos <> 0 then
2611 -- Now We need to Parse for reviewRegionItemName|!|reviewRegionItemName...
2612 if length(l_review_proc_list) is null then
2613 l_review_proc_list := Rtrim(Ltrim(substr( l_tmp_proc_call, l_start, l_pos - l_start)))||'~'||to_char(l_transaction_step_id)||'~'||Rtrim(Ltrim(substr( l_tmp_proc_call1, l_start1, l_pos1 - l_start1)));
2614 else
2615 l_review_proc_list := l_review_proc_list||'?'||Rtrim(Ltrim(substr( l_tmp_proc_call, l_start, l_pos - l_start)))||'~'||to_char(l_transaction_step_id)||'~'||Rtrim(Ltrim(substr( l_tmp_proc_call1, l_start1, l_pos1 - l_start1)));
2616 end if;
2617 l_index := l_index + 1;
2618 -- increment the start location
2619 l_start := l_pos + 3;
2620 l_start1 := l_pos1 + 3;
2621 else
2622 --- exit loop as there are no more delimter matches
2623 -- Now We need to Parse for Last reviewRegionItemName
2624 if length(l_review_proc_list) is null then
2625 l_review_proc_list := Rtrim(Ltrim(substr( l_tmp_proc_call, l_start, l_count)))||'~'||to_char(l_transaction_step_id)||'~'||Rtrim(Ltrim(substr( l_tmp_proc_call1, l_start1, l_count1)));
2626 else
2627 l_review_proc_list := l_review_proc_list||'?'||Rtrim(Ltrim(substr( l_tmp_proc_call, l_start, l_count)))||'~'||to_char(l_transaction_step_id)||'~'||Rtrim(Ltrim(substr( l_tmp_proc_call1, l_start1, l_count1)));
2628 end if;
2629 l_index := l_index + 1;
2630 -- increment the start location
2631 l_start := l_pos + 3;
2632 l_start1 := l_pos1 + 3;
2633 exit;
2634 end if;
2635 END LOOP;
2636 end if;
2637 else
2638 if length(l_review_proc_list) is null then
2639 l_review_proc_list := l_tmp_proc_call||'~'||to_char(l_transaction_step_id)||'~'||l_tmp_proc_call1;
2640 else
2641 l_review_proc_list := l_review_proc_list||'?'||l_tmp_proc_call||'~'||to_char(l_transaction_step_id)||'~'||l_tmp_proc_call1;
2642 end if;
2643 l_index := l_index + 1;
2644 end if;
2645 end loop;
2646
2647 if l_review_proc_list is not null then
2648 p_review_regions := l_review_proc_list;
2649 else
2650 p_review_regions := 'NO_REVIEW_PROC_CALL'; -- Update page has not stored the P_REVIEW_PROC_CALL or P_REVIEW_ACTID.
2651 end if;
2652
2653 else
2654 p_review_regions := 'NO_CHANGES';
2655 end if;
2656
2657 hr_utility.set_location('Leaving: '|| l_proc,20);
2658
2659 --
2660 EXCEPTION WHEN OTHERS THEN
2661 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2662 p_status := g_package||'get_review_regions Error :'||substr(SQLERRM,1,1000);
2663 -- handle this exception and raise it to jdbc call
2664 end; -- get_review_regions
2665
2666 procedure get_transaction_data (
2667 p_transaction_step_id IN VARCHAR2
2668 ,p_bulk_fetch_limit IN NUMBER DEFAULT 200
2669 ,p_transaction_data OUT nocopy transaction_data) is
2670
2671 cursor transaction_data_row is
2672 select NAME,
2673 VARCHAR2_VALUE,
2674 NUMBER_VALUE,
2675 DATE_VALUE
2676 from hr_api_transaction_values
2677 where transaction_step_id = p_transaction_step_id
2678 order by transaction_value_id;
2679
2680 i integer := 0;
2681 l_proc constant varchar2(100) := g_package || ' get_transaction_data';
2682
2683 begin
2684 hr_utility.set_location('Entering: '|| l_proc,5);
2685 p_transaction_data := null;
2686 open transaction_data_row;
2687 if g_oracle_db_version >= 9 then
2688 hr_utility.trace('In(if g_oracle_db_version >= 9 ): '|| l_proc);
2689 loop
2690 fetch transaction_data_row bulk collect
2691 into p_transaction_data.name,
2692 p_transaction_data.VARCHAR2_VALUE,
2693 p_transaction_data.NUMBER_VALUE,
2694 p_transaction_data.DATE_VALUE
2695 limit p_bulk_fetch_limit;
2696 exit when transaction_data_row%notfound;
2697 end loop;
2698 else
2699 hr_utility.trace('In else of (if g_oracle_db_version >= 9 ): '|| l_proc);
2700 loop
2701 i := i + 1;
2702 fetch transaction_data_row
2703 into p_transaction_data.name(i),
2704 p_transaction_data.VARCHAR2_VALUE(i),
2705 p_transaction_data.NUMBER_VALUE(i),
2706 p_transaction_data.DATE_VALUE(i);
2707 exit when transaction_data_row%notfound;
2708 end loop;
2709 end if;
2710 close transaction_data_row;
2711 hr_utility.set_location('Leaving: '|| l_proc,15);
2712 end get_transaction_data;
2713
2714 end hr_transaction_ss;
[Home] [Help]