1 package body PAY_ASSG_COST_WF as
2 /* $Header: pyacoswf.pkb 120.3.12020000.3 2013/03/12 08:22:16 bklingam ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PAY_ASSG_COST_WF.';
7 g_debug boolean := hr_utility.debug_enabled;
8 g_approver ame_util.approversTable2;
9 /*Used to get the transaction id pertaining to the specified
10 item type and item key. If the transaction id does not exists
11 then it will create starting a transaction.*/
12
13 -- Start of bug 13474562
14 cursor csr_wiav (p_item_type in varchar2
15 ,p_item_key in varchar2
16 ,p_name in varchar2)
17 IS
18 select 1
19 from wf_item_attribute_values wiav
20 where wiav.item_type = p_item_type
21 and wiav.item_key = p_item_key
22 and wiav.name = p_name;
23
24
25 procedure create_item_attrib_if_notexist(itemtype in varchar2,
26 itemkey in varchar2,
27 aname in varchar2,
28 text_value in varchar2 default null,
29 number_value in number default null,
30 date_value in date default null )is
31 --
32 l_dummy number(1);
33 c_proc constant varchar2(60) := 'create_item_attrib_if_notexist';
34 begin
35 g_debug := hr_utility.debug_enabled;
36 if g_debug then
37 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
38 end if;
39
40
41 if g_debug then
42 hr_utility.set_location('opening cursor csr_wiav with itemtype:'|| itemtype ||' , itemkey: '|| itemkey ||' , aname: '|| aname, 1);
43 end if;
44 -- open the cursor to determine if the attribute exists
45 open csr_wiav(itemtype,itemkey,aname);
46 fetch csr_wiav into l_dummy;
47 if csr_wiav%notfound then
48 --
49 -- item attribute does not exist so create it
50 if g_debug then
51 hr_utility.set_location('calling wf_engine.additemattr for aname:'||aname,2);
52 end if;
53 wf_engine.additemattr
54 (itemtype => itemtype
55 ,itemkey => itemkey
56 ,aname => aname
57 ,text_value=>text_value
58 ,number_value=>number_value
59 ,date_value=>date_value);
60 end if;
61
62
63 if csr_wiav%found then
64 -- set the values as per the type
65 if(text_value is not null) then
66 if g_debug then
67 hr_utility.set_location('calling wf_engine.setitemattrtext for text_value:'|| text_value,3);
68 end if;
69 wf_engine.setitemattrtext(itemtype,itemkey,aname,text_value);
70 end if;
71
72 if(number_value is not null) then
73 if g_debug then
74 hr_utility.set_location('calling wf_engine.setitemattrnumber for text_value:'|| number_value,4);
75 end if;
76 wf_engine.setitemattrnumber(itemtype,itemkey,aname,number_value);
77 end if;
78
79 if(date_value is not null) then
80 if g_debug then
81 hr_utility.set_location('calling wf_engine.setitemattrDate for text_value:'|| date_value,5);
82 end if;
83 wf_engine.setitemattrDate(itemtype,itemkey,aname,date_value);
84 end if;
85
86 end if;
87
88 close csr_wiav;
89
90 if (g_debug ) then
91 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
92 end if;
93
94 exception
95 when others then
96 if g_debug then
97 hr_utility.set_location('Error in create_item_attrib_if_notexist SQLERRM' ||' '||to_char(SQLCODE),20);
98 end if;
99 raise;
100
101 end create_item_attrib_if_notexist;
102
103 -- End of bug 13474562
104
105 FUNCTION GET_TRANSACTION_ID(
106 itemtype in varchar2,
107 itemkey in varchar2,
108 actid in number,
109 funcmode in varchar2) RETURN NUMBER IS
110
111 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
112 l_result varchar2(100);
113 l_performer_id number(10);
114 l_proc varchar2(100) := g_package||'GET_TRANSACTION_ID';
115 BEGIN
116 hr_utility.set_location('Entering ... '||l_proc,10);
117 l_transaction_id := wf_engine.GetItemAttrNumber(
118 itemtype => itemtype
119 ,itemkey => itemkey
120 ,aname => 'TRANSACTION_ID'
121 ,ignore_notfound => false
122 );
123 /*If no transaction exists, start a transaction.*/
124 IF L_TRANSACTION_ID IS NULL THEN
125 hr_utility.set_location(l_proc || '....Step1',20);
126 l_performer_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'PERFORMER_PERSON_ID');
127
128 hr_utility.set_location(l_proc , 30);
129
130 hr_transaction_ss.start_transaction
131 (itemtype => itemtype
135 ,p_login_person_id => l_performer_id
132 ,itemkey => itemkey
133 ,actid => actid
134 ,funmode => 'RUN'
136 ,result => l_result);
137 hr_utility.set_location(l_proc, 40);
138
139 /*Gett the newly created transaction and set as workflow attribute*/
140 l_transaction_id:= hr_transaction_ss.get_transaction_id
141 (p_item_type => itemtype
142 ,p_item_key => itemkey);
143 wf_engine.SetItemAttrNumber(itemtype,itemkey,'TRANSACTION_ID',l_transaction_id);
144 hr_utility.set_location(l_proc ,50);
145
146 END IF;
147 hr_utility.set_location('Leaving....'||l_proc ,60);
148 RETURN l_transaction_id;
149
150 END GET_TRANSACTION_ID;
151 procedure check_approvers_exist
152 (itemtype in varchar2,
153 itemkey in varchar2,
154 actid in number,
155 funcmode in varchar2,
156 resultout out nocopy varchar2)
157 is
158 all_approvers ame_util.approversTable2;
159 next_approver ame_util.approversTable2;
160 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
161 l_apprvl_process_complete varchar2(1000);
162 l_proc varchar2(72) := g_package||'check_approvers_exist';
163 l_flagApproversAsNotified varchar2(10);
164 l_role_name varchar2(240);
165 l_role_display_name varchar2(240);
166
167 l_wf_admin ame_util.approverRecord2;
168 l_admin_name varchar2(240);
169 l_admin_display_name varchar2(240);
170 begin
171 hr_utility.set_location('Entering:'|| l_proc, 10);
172 --bug 16414912 transfer WAIT WF process to plsql sleep
173 dbms_lock.sleep(40);
174 --bug 16414912 transfer WAIT WF process to plsql sleep
175 /*Get the transaction id*/
176 l_transaction_id := get_transaction_id(
177 itemtype => itemtype
178 ,itemkey => itemkey
179 ,actid => actid
180 ,funcmode => funcmode
181 );
182 /*Find out any approvers are there. If there are no approvers then
183 the following call raise exception. Catch that exception and set the
184 resultout to 'F'. The WF/AME Admin gets the notification of no approver.
185 Once he setup the approver then the notification would go to the approver
186 as expected. We are not supposed to reject any Workflow in the absense of
187 approver.*/
188
189 hr_utility.set_location(l_proc, 20);
190 resultout := ame_util.booleanTrue;
191 begin
192 ame_api2.getNextApprovers4(
193 applicationIdIn => 801,
194 transactionTypeIn => 'PAY_ASSIGNMENT_COSTING',
195 transactionIdIn => l_transaction_id,
196 approvalProcessCompleteYNOut => l_apprvl_process_complete,
197 nextApproversOut => g_approver);
198 if (l_apprvl_process_complete = ame_util.booleanTrue) then
199 resultout := 'F';
200 else
201 resultout := 'T';
202 end if;
203 EXCEPTION
204 /*This is to catch the exception when there is no approver etc.
205 Once the exception raises, then the AME/workflow admin will get
206 the notification with the exception message raised.*/
207 when OTHERS then
208 wf_engine.setItemAttrText(itemtype, itemkey,'AME_EXCEPTION',SQLERRM(SQLCODE));
209 resultout:='F';
210 end;
211 if resultout <> 'F' and l_apprvl_process_complete <> ame_util.booleanTrue then
212 /*First clear all approvers, then get the approver again. This is to by-pass
213 NO_DATA_FOUND exception. This situation occurs when there is no approver
214 and resume the workflow after setting the approver.*/
215 ame_api2.clearAllApprovals(
216 APPLICATIONIDIN => 801,
217 TRANSACTIONTYPEIN => 'PAY_ASSIGNMENT_COSTING',
218 TRANSACTIONIDIN => l_transaction_id);
219 /*Now get the approver again*/
220 ame_api2.getNextApprovers4(
221 applicationIdIn => 801,
222 transactionTypeIn => 'PAY_ASSIGNMENT_COSTING',
223 transactionIdIn => l_transaction_id,
224 approvalProcessCompleteYNOut => l_apprvl_process_complete,
225 nextApproversOut => g_approver);
226 if (l_apprvl_process_complete <> ame_util.booleanTrue) then
227 wf_engine.setItemAttrText(itemtype, itemkey,'APPROVER_LOGIN_NAME',g_approver(1).name);
228 wf_directory.GetRoleName(p_orig_system => NVL(g_approver(1).orig_system,'PER')
229 ,p_orig_system_id => g_approver(1).orig_system_id
230 ,p_name => l_role_name
231 ,p_display_name => l_role_display_name);
232 wf_engine.setItemAttrText(itemtype, itemkey,'APPROVER_NAME',l_role_display_name);
233 resultout:='T';
234 end if;
235 end if;
236 EXCEPTION
237 when OTHERS then
238 resultout:='F';
239 end check_approvers_exist;
240
241
242 -- ------------------------------------------------------------------------
243 -- |-------------------------< GET_NEXT_APPROVER >-------------------------|
244 -- ------------------------------------------------------------------------
245 procedure get_next_approver
249 funcmode in varchar2,
246 (itemtype in varchar2,
247 itemkey in varchar2,
248 actid in number,
250 resultout out nocopy varchar2) is
251
252 l_current_approver ame_util.approversTable2 := g_approver;
253 l_next_approver ame_util.approversTable2;
254
255 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
256 l_apprvl_process_complete varchar2(1000);
257 l_proc varchar2(72);
258 l_flagApproversAsNotified varchar2(10);
259 l_role_name varchar2(240);
260 l_role_display_name varchar2(240);
261
262 begin
263 hr_utility.set_location('Entering ... '||l_proc, 10);
264 /*Get the transaction id*/
265 l_transaction_id := get_transaction_id(
266 itemtype => itemtype
267 ,itemkey => itemkey
268 ,actid => actid
269 ,funcmode => funcmode
270 );
271
272 /*Update the status of the previous approver to true as the control
273 comes to this place once the approver approves it.*/
274
275 hr_utility.set_location(l_proc,20);
276 ame_api2.updateApprovalStatus2(
277 applicationIdIn =>801,
278 transactionTypeIn =>'PAY_ASSIGNMENT_COSTING',
279 transactionIdIn =>l_transaction_id,
280 approvalStatusIn => ame_util.approvedStatus,
281 approverNameIn => wf_engine.getItemAttrText(itemtype, itemkey,'APPROVER_LOGIN_NAME'),
282 updateItemIn => true);
283
284 /*Now get the next approver.*/
285 hr_utility.set_location(l_proc,30);
286 ame_api2.getNextApprovers4(
287 applicationIdIn => 801,
288 transactionTypeIn => 'PAY_ASSIGNMENT_COSTING',
289 transactionIdIn => l_transaction_id,
290 approvalProcessCompleteYNOut => l_apprvl_process_complete,
291 nextApproversOut => g_approver);
292
293 /*If there are approvers, then send notifications to the approver(s) by setting
294 the attribute APPROVER_LOGIN_NAME. Workflow sends notifications to the approvers
295 If there are no approvers then we need to save the data. Once the resultout
296 is set to 'F' workflow take care of saving the data by calling underlaying
297 APIs*/
298 hr_utility.set_location(l_proc, 40);
299 if l_apprvl_process_complete = ame_util.booleanFalse then
300 /*Set the previous approver attributes.*/
301 wf_engine.setItemAttrText(itemtype, itemkey,'PREVIOUS_APPROVER_LOGIN_NAME',
302 wf_engine.getItemAttrText(itemtype, itemkey, 'APPROVER_LOGIN_NAME'));
303 wf_engine.setItemAttrText(itemtype, itemkey,'PREVIOUS_APPROVER_NAME',
304 wf_engine.getItemAttrText(itemtype, itemkey, 'APPROVER_NAME'));
305
306 /*Set the current approver attributes.*/
307 wf_engine.setItemAttrText(itemtype, itemkey,'APPROVER_LOGIN_NAME',g_approver(1).name);
308 /*Get the approver name from wf_roles.*/
309 wf_directory.GetRoleName(p_orig_system => NVL(g_approver(1).orig_system,'PER')
310 ,p_orig_system_id => g_approver(1).orig_system_id
311 ,p_name => l_role_name
312 ,p_display_name => l_role_display_name);
313 wf_engine.setItemAttrText(itemtype, itemkey,'APPROVER_NAME',l_role_display_name);
314
315 resultout := 'T';
316 else
317 resultout := 'F';
318 end if;
319 hr_utility.set_location('Leaving.....'||l_proc,50);
320 EXCEPTION
321 WHEN OTHERS THEN
322 hr_utility.set_location('An EXCEPTION occured '||SQLERRM(SQLCODE),60);
323 hr_utility.set_location('Leaving ..'||l_proc,1000);
324 RAISE;
325 end get_next_approver;
326 -- ------------------------------------------------------------------------
327 -- |--------------------------< START_WF_PROCESS>-------------------------|
328 -- ------------------------------------------------------------------------
329 procedure START_WF_PROCESS (P_PERSON_ID IN NUMBER
330 ,P_ASSIGNMENT_ID IN NUMBER
331 ,P_ITEM_KEY IN VARCHAR2
332 ,P_PERFORMER_LOGIN_NAME IN VARCHAR2
333 ,P_PERFORMER_ID in number
334 ,P_EFFECTIVE_DATE IN DATE
335 ,P_CALLED_FROM IN VARCHAR2 DEFAULT NULL
336 ) is
337
338 l_item_type varchar2(8) := 'PYASGWF';
339 l_process varchar2(30) := 'PYASGWF_PROCESS';
340 l_employee_name varchar2(240) := null;
341 l_requestor_name varchar2(240);
342 l_login_name varchar2(240);
343 l_proc varchar2(72) ;
344 --
345 l_user_key varchar2(240) := p_Item_Key;
346 --
347 l_wf_admin ame_util.approverRecord2;
348 l_admin_name varchar2(240);
349 l_admin_display_name varchar2(240);
350
351 CURSOR csr_person_name is
352 select FULL_NAME
353 from per_all_people_f
354 where person_id = p_person_id
355 and trunc(sysdate) between effective_start_date and effective_end_date;
356
357 BEGIN
358 hr_utility.set_location('Entering....'||l_proc,10);
359
360 /*Create the workflow process.*/
364 ,User_Key => l_user_key
361 wf_engine.CreateProcess (ItemType => l_Item_Type
362 ,ItemKey => p_Item_Key
363 ,process => l_process
365 ,Owner_Role => 'COREPAY'
366 );
367 /*Get the person name whose values are getting changed.*/
368 open csr_person_name;
369 fetch csr_person_name into l_employee_name;
370 close csr_person_name;
371
372 /*Get the requestor details from AME.*/
373 wf_directory.GetRoleName(p_orig_system => 'PER'
374 ,p_orig_system_id => p_performer_id
375 ,p_name => l_login_name
376 ,p_display_name => l_requestor_name);
377
378 --
379 /*Set Workflow attributes.*/
380 wf_engine.setItemAttrNumber(l_item_type, p_item_key, 'CURRENT_PERSON_ID', P_PERSON_ID);
381 wf_engine.setItemAttrNumber(l_item_type, p_item_key, 'CURRENT_ASSIGNMENT_ID',P_ASSIGNMENT_ID);
382 wf_engine.setItemAttrText(l_item_type, p_item_key, 'PROCESS_NAME','PYASGWF_PROCESS');
383 wf_engine.setItemAttrDate(l_item_type, p_item_key,'CURRENT_EFFECTIVE_DATE',SYSDATE);
384 wf_engine.setItemAttrNumber(l_item_type, p_item_key,'PERFORMER_PERSON_ID',P_PERFORMER_ID);
385 wf_engine.setItemAttrDate(l_item_type, p_item_key,'EFFECTIVE_DATE',P_EFFECTIVE_DATE);
386 wf_engine.setItemAttrText(l_item_type, p_item_key, 'EMP_NAME', l_employee_name);
387 wf_engine.setItemAttrText(l_item_type, p_item_key, 'REQUESTOR_LOGIN_NAME', P_PERFORMER_LOGIN_NAME);
388 wf_engine.setItemAttrText(l_item_type, p_item_key, 'REQUESTOR_NAME', l_requestor_name);
389 -- Start of bug 13474562
390 create_item_attrib_if_notexist(itemtype =>l_item_type,itemkey =>p_item_key,aname => 'P_CALLED_FROM',text_value =>P_CALLED_FROM );
391 -- End of bug 13474562
392
393 /*As the control comes here only for the first time. At this time only requestor would be there.
394 So for the time being we are setting the previous approver details as the requestor.*/
395 wf_engine.setItemAttrText(l_item_type, p_item_key, 'PREVIOUS_APPROVER_LOGIN_NAME', P_PERFORMER_LOGIN_NAME);
396 wf_engine.setItemAttrText(l_item_type, p_item_key, 'PREVIOUS_APPROVER_NAME', l_requestor_name);
397
398 /*Get the AME/workflow admin details and populate the attributes.*/
399 ame_api2.getAdminApprover(applicationIdIn => 801,
400 transactionTypeIn => 'PAY_ASSIGNMENT_COSTING',
401 adminApproverOut => l_wf_admin);
402 wf_engine.setItemAttrText(l_item_type, p_item_key,'WF_ADMIN_LOGIN_NAME',l_wf_admin.name);
403 wf_directory.GetRoleName(p_orig_system => NVL(l_wf_admin.orig_system,'PER')
404 ,p_orig_system_id => l_wf_admin.orig_system_id
405 ,p_name => l_admin_name
406 ,p_display_name => l_admin_display_name);
407 wf_engine.setItemAttrText(l_item_type, p_item_key,'WF_ADMIN_NAME',l_admin_display_name);
408
409 /*Start the created workflow process.*/
410 wf_engine.StartProcess (itemtype => l_Item_Type
411 ,itemkey => p_Item_Key
412 );
413 EXCEPTION
414 WHEN OTHERS THEN
415 hr_utility.set_location('An EXCEPTION occured',40);
416 hr_utility.set_location('Leaving ..'||l_proc,999);
417 RAISE;
418 END START_WF_PROCESS;
419 -- ------------------------------------------------------------------------
420 -- |--------------------------< APPROVE_PROCESS >-------------------------|
421 -- ------------------------------------------------------------------------
422 PROCEDURE APPROVE_PROCESS(
423 itemtype in varchar2
424 ,itemkey in varchar2
425 ,actid in number
426 ,funmode in varchar2
427 ,result out nocopy varchar2) is
428 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
429 l_transaction_step_id HR_UTIL_WEB.G_VARCHAR2_TAB_TYPE;
430 l_api_name HR_UTIL_WEB.G_VARCHAR2_TAB_TYPE;
431 l_row number;
432 l_proc varchar2(255) := g_package||'APPROVE_PROCESS';
433 l_api_to_call varchar2(1000);
434 l_actid WF_ITEM_ACTIVITY_STATUSES.process_activity%TYPE;
435 l_effective_date DATE;
436 begin
437 if g_debug then
438 hr_utility.set_location('Entering ..'||l_proc,10);
439 end if;
440 /*Set the previous approver attributes.*/
441 wf_engine.setItemAttrText(itemtype, itemkey,'PREVIOUS_APPROVER_LOGIN_NAME',
442 wf_engine.getItemAttrText(itemtype, itemkey, 'APPROVER_LOGIN_NAME'));
443 wf_engine.setItemAttrText(itemtype, itemkey,'PREVIOUS_APPROVER_NAME',
444 wf_engine.getItemAttrText(itemtype, itemkey, 'APPROVER_NAME'));
445
446 /* Get the transaction step id pertaining to the work flow process
447 identified by the itemkey, itemkey and actid.*/
448 hr_transaction_api.get_transaction_step_info(p_item_type => itemtype
449 ,p_item_key => itemkey
450 ,p_activity_id => 0 --l_actid
451 ,p_transaction_step_id => l_transaction_step_id
452 ,p_api_name => l_api_name
453 ,p_rows => l_row);
454 /*l_row represents number of steps pertaining to itemtype, itemkey and actid.
455 But in our case it's always one. So we assume it as one always.*/
456 /*Call the API to save the data into base tables.*/
457 for i in 0..l_row-1 loop
458 /*Construct the API call*/
459 l_api_to_call := 'Begin ';
460 l_api_to_call := l_api_to_call || l_api_name(i)||'(';
461 l_api_to_call := l_api_to_call || 'p_transaction_step_id => '||to_number(l_transaction_step_id(i));
462 l_api_to_call := l_api_to_call || ' ); end;';
463
464 /*Call the API*/
465 EXECUTE IMMEDIATE (l_api_to_call);
466
467 end loop;
468
469 /*Clearing the hr_transaction data after successful execution of API's*/
470 hr_transaction_ss.rollback_transaction(
471 itemtype => itemtype
472 ,itemkey => itemkey
473 ,actid => actid
474 ,funmode => funmode
475 ,result => result);
476 if (result = 'SUCCESS') then
477 hr_utility.set_location('Transaction deleted successfully ',20);
478 else
479 hr_utility.set_location('Error in deleting Transaction ',30);
480 end if;
481 if g_debug then
482 hr_utility.set_location('Leaving ..'||l_proc,1000);
483 end if;
484 -- commit;
485 EXCEPTION
486 WHEN OTHERS THEN
487 hr_utility.set_location('An EXCEPTION occured',40);
488 hr_utility.set_location('Leaving ..'||l_proc,1000);
489 RAISE;
490 end APPROVE_PROCESS;
491 -- ------------------------------------------------------------------------
492 -- |--------------------------< REJECT_PROCESS >--------------------------|
493 -- ------------------------------------------------------------------------
494 PROCEDURE REJECT_PROCESS(
495 itemtype in varchar2
496 ,itemkey in varchar2
497 ,actid in number
498 ,funmode in varchar2
499 ,result out nocopy varchar2) IS
500 l_proc varchar2(240) := g_package||'REJECT_PROCESS';
501 l_result varchar2(20);
502 BEGIN
503 if g_debug then
504 hr_utility.set_location('Entering ..'||l_proc,10);
505 end if;
506
507 hr_transaction_ss.rollback_transaction(
508 itemtype => itemtype
509 ,itemkey => itemkey
510 ,actid => actid
511 ,funmode => funmode
512 ,result => l_result);
513 if (l_result = 'SUCCESS') then
514 hr_utility.set_location('Transaction deleted successfully ',20);
515 else
516 hr_utility.set_location('Error in deleting Transaction ',30);
517 end if;
518 if g_debug then
519 hr_utility.set_location('Leaving ..'||l_proc,1000);
520 end if;
521 EXCEPTION
522 WHEN OTHERS THEN
523 hr_utility.set_location('An EXCEPTION occured',40);
524 hr_utility.set_location('Leaving ..'||l_proc,1000);
525 RAISE;
526 END REJECT_PROCESS;
527
528 end PAY_ASSG_COST_WF;
[Home] [Help]