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