[Home] [Help]
PACKAGE BODY: APPS.HR_DYNAMIC_APPROVAL_WEB
Source
1 PACKAGE BODY hr_dynamic_approval_web AS
2 /* $Header: hrdynapr.pkb 120.2.12000000.2 2007/08/08 13:29:12 ckondapi ship $ */
3
4 -- |---------------------------------------------------------------------------|
5 -- |-- < TIPS Begin > ---------------------------------------------------------|
6 -- |---------------------------------------------------------------------------|
7 --
8 -- TIP INSTRUCTION1 HR_DYNAMIC_APPROVERS_WEB
9 -- TIP INSTRUCTION2 HR_DYNAMIC_APPROVERS_WEB
10 -- TIP P_APPROVER_NAME HR_DYNAMIC_APPROVERS_WEB
11 -- TIP P_NOTIFIER_NAME HR_DYNAMIC_APPROVERS_WEB
12 --
13 -- |---------------------------------------------------------------------------|
14 -- |-- < TIPS End >------------------------------------------------------------|
15 -- |---------------------------------------------------------------------------|
16
17 gv_package CONSTANT VARCHAR2(100)
18 DEFAULT 'hr_dynamic_approval_web';
19 gv_dynappr_js_file CONSTANT VARCHAR2(100) DEFAULT 'hrdynapr.js';
20 gv_form_name CONSTANT VARCHAR2(100) DEFAULT 'approvalsForm';
21 gv_tip_test_mode BOOLEAN DEFAULT FALSE;
22 gv_user_date_format VARCHAR2(2000);
23 gn_person_id per_people_f.person_id%TYPE;
24 gn_assignment_id per_all_assignments_f.assignment_id%TYPE;
25 gv_error_table hr_dynamic_approval_web.t_person_table;
26 grt_wf_attributes_rec hr_dynamic_approval_web.grt_wf_attributes;
27
28 gv_current_row NUMBER DEFAULT 1;
29 gv_mode VARCHAR2(25) DEFAULT 'RUN';
30 gv_job_type hr_suit_match_utility_web.g_job_type%TYPE;
31
32 -- variables for ak data
33 gv_region_code CONSTANT VARCHAR2(100)
34 DEFAULT 'HR_DYNAMIC_APPROVALS_WEB';
35 gv_nav_region_code CONSTANT VARCHAR2(100)
36 DEFAULT 'HR_BUTTON_LABELS_WEB';
37 gn_region_application_id CONSTANT integer := 601;
38 gv_browser_title hr_util_misc_web.g_title%TYPE;
39 gtt_prompts hr_util_misc_web.g_prompts%TYPE;
40 gv_language_code VARCHAR2(200) DEFAULT NULL;
41 gv_approvals_mode wf_activity_attr_values.text_value%TYPE;
42 gv_update_mode wf_activity_attr_values.text_value%TYPE
43 DEFAULT 'OFF';
44 gv_effective_date wf_item_attribute_values.text_value%TYPE;
45 gv_APPROVALS_ACTIVITY_NAME CONSTANT
46 wf_item_activity_statuses_v.activity_name%TYPE
47 DEFAULT 'HR_DYNAMIC_APPROVALS';
48 gv_process_name wf_process_activities.process_name%TYPE
49 DEFAULT 'HR_HRSSA_DYNA_APPROVAL_PRC' ;
50
51 grt_person_details hr_dynamic_approval_web.person_details;
52 grt_person_details_rec_table hr_dynamic_approval_web.t_person_table;
53 grt_approver_details_table hr_dynamic_approval_web.t_person_table;
54 gn_approver_index NUMBER DEFAULT 1;
55 gv_additional_approvers VARCHAR2(10) DEFAULT 'NO';
56 gn_additional_approvers NUMBER DEFAULT 0;
57 gv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
58 gv_notifier_name VARCHAR2(100) DEFAULT 'NOTIFIER_';
59 gn_notifiers NUMBER DEFAULT 0;
60 grt_notifier_details_table hr_dynamic_approval_web.notifier_rec_table;
61 grt_notifier_error_table hr_dynamic_approval_web.notifier_rec_table;
62 gv_cross_business_group VARCHAR2(10) ;
63 g_package CONSTANT VARCHAR2(100):='hr_dynamic_approval_web';
64
65
66 -- exceptions
67 gv_invalid_person EXCEPTION;
68 gv_no_default_approver EXCEPTION;
69 -- Cursors for the Name, Job Title
70
71 -- CURSOR FOR FULL_NAME
72 CURSOR gc_full_name (
73 p_person IN per_people_f.person_id%TYPE
74 )
75 IS
76 SELECT peo.full_name
77 FROM per_people_f peo
78 WHERE peo.person_id = p_person;
79
80 -- curosr for Person_id
81 CURSOR lc_approver ( p_full_name VARCHAR2)
82 IS
83 SELECT person_id
84 FROM per_all_people_f
85 WHERE full_name = p_full_name ;
86
87 -- assignment id for the approver
88 CURSOR gc_assignment_id (p_person_id IN per_people_f.person_id%TYPE,
89 p_effective_date IN DATE
90 ) IS
91 SELECT paf.assignment_id
92 FROM
93 per_all_assignments_f paf,
94 per_all_people_f peo
95 WHERE
96 peo.person_id = paf.person_id
97 AND peo.person_id = p_person_id
98 AND p_effective_date BETWEEN paf.effective_start_date
99 AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
100 ORDER BY paf.effective_start_date DESC;
101
102
103
104 -- CURSOR FOR JOB TITLE
105 CURSOR gc_job_details (
106 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
107 p_effective_date IN DATE
108 ) IS
109 SELECT pj.name
110 FROM per_jobs_vl pj,
111 per_assignments_f paf
112 WHERE paf.assignment_id = p_assignment_id
113 AND paf.job_id = pj.job_id
114 AND paf.primary_flag = 'Y'
115 AND paf.business_group_id+0 = pj.business_group_id+0
116 AND p_effective_date BETWEEN paf.effective_start_date
117 AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
118 ORDER BY paf.effective_start_date DESC;
119
120 -- cursor for JOB ID
121 CURSOR gc_job_id (
122 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
123 p_effective_date IN DATE
124 ) IS
125 SELECT pj.job_id
126 FROM per_jobs pj,
127 per_assignments_f paf
128 WHERE paf.assignment_id = p_assignment_id
129 AND paf.job_id = pj.job_id
130 AND paf.primary_flag = 'Y'
131 AND paf.business_group_id+0 = pj.business_group_id+0
132 AND p_effective_date BETWEEN paf.effective_start_date
133 AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
134 ORDER BY paf.effective_start_date DESC;
135
136
137 -- cursor determines if an attribute exists
138 cursor csr_wiav (p_item_type in varchar2
139 ,p_item_key in varchar2
140 ,p_name in varchar2)
141 IS
142 select 1
143 from wf_item_attribute_values wiav
144 where wiav.item_type = p_item_type
145 and wiav.item_key = p_item_key
146 and wiav.name = p_name;
147
148 -- cursor determines if an acitivity attribute exists
149 cursor csr_wfaav (p_name in varchar2
150 , p_id in varchar2
151 )
152 IS
153 select 1
154 from WF_ACTIVITY_ATTR_VALUES wfaav
155 where wfaav.name = p_name
156 and wfaav.PROCESS_ACTIVITY_ID = p_id;
157
158
159
160
161
162
163 -- ---------------------------------------------------------------------------
164 -- private procedure declarations
165 -- ---------------------------------------------------------------------------
166 --
167 -- ---------------------------------------------------------------------------
168 -- |------------------------------< set_custom_wf_globals >-------------------|
169 -- ---------------------------------------------------------------------------
170 --
171 -- This procedure sets the customized global variables with the standard wf
172 -- values
173 --
174 PROCEDURE SET_CUSTOM_WF_GLOBALS
175 (p_itemtype in varchar2
176 ,p_itemkey in varchar2)
177 IS
178 -- Local Variables
179 l_proc constant varchar2(100) := g_package || ' SET_CUSTOM_WF_GLOBALS';
180 BEGIN
181 hr_utility.set_location('Entering: '|| l_proc,5);
182 hr_approval_custom.g_itemtype := p_itemtype;
183 hr_approval_custom.g_itemkey := p_itemkey;
184 hr_utility.set_location('Leaving: '|| l_proc,10);
185 END SET_CUSTOM_WF_GLOBALS;
186 --
187
188
189 -- ---------------------------------------------------------------------------
190 -- private procedure declarations
191 -- ---------------------------------------------------------------------------
192 --
193
194
195 -- ---------------------------------------------------------------------------
196 -- private Function declarations
197 -- ---------------------------------------------------------------------------
198 --
199 -- ----------------------------------------------------------------------------
200 -- |------------------------------< get_job_details >-------------------|
201 -- ----------------------------------------------------------------------------
202 --
203 -- This function will return the job title for the person id passed
204 --
205
206 FUNCTION get_job_details (p_person_id IN NUMBER,
207 p_assignment_id IN NUMBER DEFAULT NULL,
208 p_effective_date IN DATE DEFAULT SYSDATE
209 )
210 RETURN VARCHAR2
211 IS
212 -- Local Variables
213 lv_job_title VARCHAR2(1000);
214 ln_job_id NUMBER;
215 l_curr_org_name VARCHAR2(100);
216 l_curr_loc_name VARCHAR2(100);
217 ln_assignment_id NUMBER;
218 lrt_assignment_details hr_misc_web.grt_assignment_details;
219 l_proc constant varchar2(100) := g_package || ' get_job_details';
220
221 BEGIN
222 hr_utility.set_location('Entering: '|| l_proc,5);
223 IF p_person_id IS NOT NULL THEN
224 hr_utility.trace('In ( IF p_person_id IS NOT NULL): '|| l_proc);
225 -- check assignment id
226 IF p_assignment_id IS NULL THEN
227 -- get assignment id for the person_id
228 lrt_assignment_details := hr_misc_web.get_assignment_id (
229 p_person_id =>p_person_id);
230 ln_assignment_id := lrt_assignment_details.assignment_id;
231 ELSE
232 ln_assignment_id := p_assignment_id;
233 END IF;
234 -- get the job details from the assignment record
235 lrt_assignment_details := hr_misc_web.get_assignment_details(
236 p_assignment_id => ln_assignment_id,
237 p_effective_date =>p_effective_date
238 );
239 ln_job_id := lrt_assignment_details.job_id;
240
241 -- get the job title from flexfields
242 hr_suit_match_utility_web.get_job_info
243 (p_search_type => gv_job_type,
244 p_id => ln_job_id,
245 p_name => lv_job_title,
246 p_org_name => l_curr_org_name,
247 p_location_code => l_curr_loc_name);
248
249 END IF;
250
251 lv_job_title := NVL(lv_job_title,lrt_assignment_details.job_name);
252 hr_utility.set_location('Leaving: '|| l_proc,15);
253 return lv_job_title;
254
255 EXCEPTION WHEN OTHERS THEN
256 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
257 raise;
258
259 END get_job_details;
260
261 -- ---------------------------------------------------------------------------
262 -- private Function declarations
263 -- ---------------------------------------------------------------------------
264 --
265 -- ----------------------------------------------------------------------------
266 -- |------------------------------< build_where_clause >-------------------|
267 -- ----------------------------------------------------------------------------
268 --
269 -- This function will build the where clause for approvers and notifiers based
270 -- on context passed.
271 --
272
273 FUNCTION build_where_clause(
274 p_where_for IN VARCHAR2,
275 p_Approvers_list hr_util_misc_web.g_varchar2_tab_type
276 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
277 p_Notifiers_list hr_util_misc_web.g_varchar2_tab_type
278 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
279 p_effective_date IN DATE DEFAULT SYSDATE,
280 p_business_group_id IN NUMBER DEFAULT 0
281
282 )
283 RETURN LONG
284 IS
285 lv_where LONG ;
286 lv_person_id_list hr_util_misc_web.g_varchar2_tab_type
287 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
288 lv_error_flag boolean;
289 ln_count NUMBER DEFAULT 0;
290 ld_effective_date DATE DEFAULT SYSDATE;
291 lv_search VARCHAR2(1000);
292 l_proc constant varchar2(100) := g_package || ' build_where_clause';
293 BEGIN
294
295 -- get the global supervisor profile value
296 -- if profile value is 'Y' , we do not check for business
297 -- group, otherwise we will check for business group id in
298 -- all the cursors etc.
299 hr_utility.set_location('Entering: '|| l_proc,5);
300 gv_cross_business_group :=
301 fnd_profile.value ( 'HR_CROSS_BUSINESS_GROUP');
302
303
304 IF p_where_for='APPROVERS' THEN
305 hr_utility.trace('In (IF p_where_for=APPROVERS): '|| l_proc);
306 lv_where := 'PERSON_ID NOT IN(';
307 ln_count := 0;
308 hr_utility.trace('Going into ( FOR I IN 1..grt_approver_details_table.count): '|| l_proc);
309 FOR I IN 1..grt_approver_details_table.count
310 LOOP
311 IF grt_approver_details_table(I).person_id IS NOT NULL THEN
312 ln_count := ln_count + 1;
313 IF ln_count < 2 THEN
314 lv_where :=lv_where ||to_char(grt_approver_details_table(I).person_id);
315 ELSE
316 lv_where :=lv_where ||','||to_char(grt_approver_details_table(I).person_id);
317 END IF;
318
319 END IF;
320 END LOOP;
321 hr_utility.trace('Out of ( FOR I IN 1..grt_approver_details_table.count): '|| l_proc);
322
323 IF gv_cross_business_group = 'Y'
324 THEN
325 hr_utility.trace('In (IF gv_cross_business_group = Y): '|| l_proc);
326 lv_where := lv_where ||') '||' AND business_group_id = business_group_id '||
327 ' AND to_date(''' || ld_effective_date || ''',''' ||
328 gv_user_date_format || ''') between effective_start_date and '||
329 ' nvl(effective_end_date, (to_date(''' || ld_effective_date ||
330 ''',''' || gv_user_date_format || ''') +1 ) )';
331 ELSE
332 hr_utility.trace('In else of (IF gv_cross_business_group = Y): '|| l_proc);
333 lv_where := lv_where ||') '||' AND business_group_id = '||p_business_group_id||
334 ' AND to_date(''' || ld_effective_date || ''',''' ||
335 gv_user_date_format || ''') between effective_start_date and '||
336 ' nvl(effective_end_date, (to_date(''' || ld_effective_date ||
337 ''',''' || gv_user_date_format || ''') +1 ) )';
338 END IF;
339
340 -- Added check for effective_start_date and effective_end_date of assingments table
341 lv_where := lv_where ||' AND to_date(''' || ld_effective_date || ''',''' ||
342 gv_user_date_format || ''') between asn_effective_start_date and '||
343 ' nvl(asn_effective_end_date, (to_date(''' || ld_effective_date ||
344 ''',''' || gv_user_date_format || ''') +1 ) )';
345
346
347
348 END IF;
349
350 IF p_where_for='NOTIFIERS' THEN
351 hr_utility.trace('In (IF p_where_for=NOTIFIERS): '|| l_proc);
352 -- lv_search
353 -- build string for the person_id's in approvers list
354 -- we do not want notifiers and approvers to be same;
355 -- avoid duplicate notifications
356
357 IF grt_approver_details_table.count < 1 THEN
358 hr_utility.trace('In (IF grt_approver_details_table.count < 1 ): '|| l_proc);
359 -- no approvers currently
360 lv_search := '';
361 ELSE
362 hr_utility.trace('In else(IF grt_approver_details_table.count < 1 ): '|| l_proc);
363 ln_count := 0;
364 hr_utility.trace('Going into ( FOR I IN 1..grt_approver_details_table.count): '|| l_proc);
365 FOR I IN 1..grt_approver_details_table.count
366 LOOP
367 IF grt_approver_details_table(I).person_id IS NOT NULL THEN
368 ln_count := ln_count + 1;
369 lv_search := lv_search ||'|'
370 ||to_char(grt_approver_details_table(I).person_id);
371
372 END IF;
373 END LOOP;
374 hr_utility.trace('Going into ( FOR I IN 1..grt_approver_details_table.count): '|| l_proc);
375 END IF;
376
377
378
379 -- build the where clause
380 IF grt_approver_details_table.count< 1 THEN
381 hr_utility.trace('In (IF grt_approver_details_table.count < 1 ): '|| l_proc);
382 lv_where := ' to_date(''' || ld_effective_date || ''',''' ||
383 gv_user_date_format || ''') between effective_start_date and '||
384 ' nvl(effective_end_date, (to_date(''' || ld_effective_date ||
385 ''',''' || gv_user_date_format || ''') +1 ) )';
386 -- Added check for effective_start_date and effective_end_date of assingments table
387 lv_where := lv_where ||' AND to_date(''' || ld_effective_date || ''',''' ||
388 gv_user_date_format || ''') between asn_effective_start_date and '||
389 ' nvl(asn_effective_end_date, (to_date(''' || ld_effective_date ||
390 ''',''' || gv_user_date_format || ''') +1 ) )';
391
392 ELSE
393 hr_utility.trace('In else(IF grt_approver_details_table.count < 1 ): '|| l_proc);
394 lv_where := 'PERSON_ID NOT IN(';
395 ln_count := 0;
396 hr_utility.trace('Going into (FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
397 -- first exclude the person_id's from approvers list
398 FOR I IN 1..grt_approver_details_table.count
399 LOOP
400
401 IF grt_approver_details_table(I).person_id IS NOT NULL THEN
402 ln_count := ln_count + 1;
403 IF ln_count < 2 THEN
404 lv_where :=lv_where ||to_char(grt_approver_details_table(I).person_id);
405 ELSE
406 lv_where :=lv_where ||','||to_char(grt_approver_details_table(I).person_id);
407 END IF;
408
409 END IF;
410 END LOOP;
411 hr_utility.trace('Out of (FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
412 -- exclude person_id's from notifiers list
413 IF grt_notifier_details_table.count>0 THEN
414 hr_utility.trace('In (IF grt_notifier_details_table.count>0 ): '|| l_proc);
415 hr_utility.trace('Going into (FOR I IN 1..grt_notifier_details_table.count): '|| l_proc);
416 FOR I IN 1..grt_notifier_details_table.count
417 LOOP
418 IF INSTR(lv_search,
419 to_char(grt_notifier_details_table(I).person_id)) = 0
420 THEN
421 lv_where :=lv_where ||','||
422 to_char(grt_notifier_details_table(I).person_id);
423 END IF;
424 END LOOP;
425 hr_utility.trace('Out of (FOR I IN 1..grt_notifier_details_table.count): '|| l_proc);
426 END IF;
427
428
429 IF gv_cross_business_group = 'Y'
430 THEN
431 hr_utility.trace('In (IF IF gv_cross_business_group = Y ): '|| l_proc);
432 lv_where := lv_where ||') '||' AND business_group_id = business_group_id '||
433 ' AND to_date(''' || ld_effective_date || ''',''' ||
434 gv_user_date_format || ''') between effective_start_date and '||
435 ' nvl(effective_end_date, (to_date(''' || ld_effective_date ||
436 ''',''' || gv_user_date_format || ''') +1 ) )';
437 ELSE
438 hr_utility.trace('In else (IF IF gv_cross_business_group = Y ): '|| l_proc);
439 lv_where := lv_where ||') '||' AND business_group_id = '||p_business_group_id||
440 ' AND to_date(''' || ld_effective_date || ''',''' ||
441 gv_user_date_format || ''') between effective_start_date and '||
442 ' nvl(effective_end_date, (to_date(''' || ld_effective_date ||
443 ''',''' || gv_user_date_format || ''') +1 ) )';
444 END IF;
445
446 -- Added check for effective_start_date and effective_end_date of assingments table
447 lv_where := lv_where ||' AND to_date(''' || ld_effective_date || ''',''' ||
448 gv_user_date_format || ''') between asn_effective_start_date and '||
449 ' nvl(asn_effective_end_date, (to_date(''' || ld_effective_date ||
450 ''',''' || gv_user_date_format || ''') +1 ) )';
451
452
453 END IF; -- for grt_approver_details_table.count < 1
454
455 END IF; -- for p_where_for='NOTIFIERS'
456
457 hr_utility.set_location('Leaving: '|| l_proc,85);
458
459 return lv_where;
460 EXCEPTION WHEN OTHERS THEN
461 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
462 raise;
463 END build_where_clause;
464 -- ---------------------------------------------------------------------------
465 -- private Procedure declarations
466 -- ---------------------------------------------------------------------------
467 --
468 -- ----------------------------------------------------------------------------
469 -- |------------------------------< update_ame_approvers_list>-------------------|
470 -- ----------------------------------------------------------------------------
471 --
472 -- This procedure will write the additional approvers added to the AME tables.
473 -- ----------------------------------------------------------------------------
474
475 PROCEDURE update_ame_approvers_list(
476 p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
477 p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
478 p_act_id IN NUMBER ,
479 p_approver_name hr_util_misc_web.g_varchar2_tab_type
480 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
481 p_approver_flag hr_util_misc_web.g_varchar2_tab_type
482 DEFAULT hr_util_misc_web.g_varchar2_tab_default)
483
484 IS
485 -- Local variables
486 ln_person_id per_people_f.person_id%TYPE;
487 lv_exists VARCHAR2(10);
488 lv_dummy VARCHAR2(100);
489 lv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
490 ln_addntl_approvers NUMBER DEFAULT 0;
491 lv_notify VARCHAR2(10);
492 ln_notifiers NUMBER DEFAULT 0;
493 ln_approval_level NUMBER DEFAULT NULL;
494 l_proc constant varchar2(100) := g_package || ' update_ame_approvers_list';
495 -- Variables required for AME API
496 c_application_id integer;
497 c_transaction_id varchar2(25);
498 c_transaction_type varchar2(25);
499 c_next_approver_rec ame_util.approverRecord;
500 c_additional_approver_order ame_util.orderRecord;
501 c_additional_approver_rec ame_util.approversTable;
502 -------------------------------BEGIN-------------------------------------------
503 BEGIN
504 hr_utility.set_location('Entering: '|| l_proc,5);
505 -- validate the session
506 hr_util_misc_web.validate_session(p_person_id => gn_person_id);
507
508
509 -- get AME related WF attribute values
510 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
511 itemkey => p_item_key,
512 aname => 'HR_AME_APP_ID_ATTR');
513
514 c_application_id := nvl(c_application_id,800);
515
516
517 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
518 itemkey => p_item_key,
519 aname => 'TRANSACTION_ID');
520
521
522
523 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
524 itemkey => p_item_key,
525 aname => 'HR_AME_TRAN_TYPE_ATTR');
526
527 hr_utility.trace('Going into (FOR I IN 1..p_approver_name.count LOOP): '|| l_proc);
528 -- get person id for the given full name.
529 FOR I IN 1..p_approver_name.count LOOP
530 -- get the person_id for this person_name
531 OPEN lc_approver ( p_full_name=>p_approver_name(I));
532 hr_utility.trace('Going into Fetch after ( OPEN lc_approver ( p_full_name=>p_approver_name(I)) ): '|| l_proc);
533 FETCH lc_approver INTO ln_person_id;
534 IF lc_approver%NOTFOUND THEN
535 lv_exists := 'N';
536 raise gv_invalid_person;
537 ELSE
538 lv_exists:= 'Y';
539 END IF ;
540 CLOSE lc_approver;
541
542 c_additional_approver_rec(i).person_id:=ln_person_id;
543
544 end loop;
545 hr_utility.trace('Out of (FOR I IN 1..p_approver_name.count LOOP): '|| l_proc);
546 -- clear all the insertions into AME . Need to perform this step ONLY after we get the person id .
547 -- other wise it would clear the insertions made in the previous pass.
548 ame_api.clearInsertions(applicationIdIn =>c_application_id ,
549 transactionIdIn =>c_transaction_id,
550 transactionTypeIn=>c_transaction_type);
551
552
553 if(c_transaction_type is not null) then
554 -- update AME list
555 hr_utility.trace('In( if(c_transaction_type is not null) ): '|| l_proc);
556 hr_utility.trace('Going into (for i in 1..c_additional_approver_rec.count): '|| l_proc);
557 for i in 1..c_additional_approver_rec.count loop
558 -- check for the default approver flag
559 if(p_approver_flag(I)='N') then
560 -- details for the record insertion into AME
561 c_next_approver_rec.person_id:=c_additional_approver_rec(i).person_id;
562 c_next_approver_rec.api_insertion:= ame_util.apiInsertion;
563 c_next_approver_rec.authority:=ame_util.authorityApprover;
564
565 -- details for the insertion order for the AME record.
566 c_additional_approver_order.order_type:=ame_util.absoluteOrder;
567 c_additional_approver_order.parameter:=I;
568 c_additional_approver_order.description:=p_approver_name(I);
569
570 ame_api.insertApprover(applicationIdIn =>c_application_id,
571 transactionIdIn =>c_transaction_id,
572 approverIn =>c_next_approver_rec,
573 positionIn =>I,
574 orderIn =>c_additional_approver_order,
575 transactionTypeIn=>c_transaction_type );
576 end if;
577 end loop;
578 hr_utility.trace('Out of (for i in 1..c_additional_approver_rec.count): '|| l_proc);
579 end if; -- end updating AME list
580 hr_utility.set_location('Leaving: '|| l_proc,40);
581 EXCEPTION
582 WHEN gv_invalid_person THEN
583 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
584 if lc_approver%isopen then
585 close lc_approver;
586 end if;
587 raise;
588 WHEN OTHERS THEN
589 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
590 if lc_approver%isopen then
591 close lc_approver;
592 end if;
593 raise;
594 END update_ame_approvers_list;
595
596
597 -- ---------------------------------------------------------------------------
598 -- private Procedure declarations
599 -- ---------------------------------------------------------------------------
600 --
601 -- ----------------------------------------------------------------------------
602 -- |------------------------------< COMMIT_DATA>-------------------|
603 -- ----------------------------------------------------------------------------
604 --
605 -- This procedure will write the approvers and notifiers data to the
606 -- wf_item_attribute_values table and creates other item_attributes if not exist.
607 --
608 PROCEDURE COMMIT_DATA(
609 p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
610 p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
611 p_act_id IN NUMBER ,
612 p_approver_name hr_util_misc_web.g_varchar2_tab_type
613 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
614 p_approver_flag hr_util_misc_web.g_varchar2_tab_type
615 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
616 p_notifier_name hr_util_misc_web.g_varchar2_tab_type
617 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
618 p_notify_onsubmit_flag hr_util_misc_web.g_varchar2_tab_type
619 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
620 p_notify_onapproval_flag hr_util_misc_web.g_varchar2_tab_type
621 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
622 p_mode IN VARCHAR2
623 )
624 IS
625 -- Local variables
626 ln_person_id per_people_f.person_id%TYPE;
627 lv_exists VARCHAR2(10);
628 lv_dummy VARCHAR2(100);
629 lv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
630 ln_addntl_approvers NUMBER DEFAULT 0;
631 lv_notify VARCHAR2(10);
632 ln_notifiers NUMBER DEFAULT 0;
633 ln_approval_level NUMBER DEFAULT NULL;
634 l_proc constant varchar2(100) := g_package || ' COMMIT_DATA';
635
636 -- for AME
637 c_transaction_type varchar2(25);
638
639 -------------------------------------------------------------------------------
640 -------------------------------BEGIN-------------------------------------------
641 BEGIN
642
643 hr_utility.set_location('Entering: '|| l_proc,5);
644 -- validate the session
645 hr_util_misc_web.validate_session(p_person_id => gn_person_id);
646
647 -- get user date format
648 gv_user_date_format := hr_util_misc_web.get_user_date_format;
649
650
651 -- get session language code
652 gv_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
653
654 -- check if we need to call AME
655 -- get the AME transaction type value from WF item attributes
656 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
657 itemkey => p_item_key,
658 aname => 'HR_AME_TRAN_TYPE_ATTR');
659
660
661 if (c_transaction_type is not null) then
662 hr_utility.trace('In (if (c_transaction_type is not null)): '|| l_proc);
663 update_ame_approvers_list(
664 p_item_type =>p_item_type,
665 p_item_key =>p_item_key,
666 p_act_id =>p_act_id,
667 p_approver_name =>p_approver_name,
668 p_approver_flag=>p_approver_flag);
669
670 else
671 hr_utility.trace('In esle of (if (c_transaction_type is not null)): '|| l_proc);
672 -- fix for bug # 1570998
673 -- set all the current wf item attributes for additional approvers to deleted state.
674 -- The attributes would be updated later in the code
675 hr_utility.trace('Going into( for i in 1..p_approver_name.count): '|| l_proc);
676 for i in 1..p_approver_name.count loop
677 lv_item_name := gv_item_name || to_char(I);
678 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
679 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
680 FETCH csr_wiav into lv_dummy;
681 IF csr_wiav%notfound then
682 null;
683 ELSE
684 -- set the item attribute text value to DELETED
685 wf_engine.SetItemAttrText
686 (itemtype => p_item_type,
687 itemkey => p_item_key,
688 aname => lv_item_name,
689 avalue => 'DELETED');
690 END IF; -- for csr_wiav%notfound
691 CLOSE csr_wiav;
692 end loop;
693 hr_utility.trace('Out of ( for i in 1..p_approver_name.count): '|| l_proc);
694 -- end fix for bug # 1570998
695
696 -- update approvers data
697 hr_utility.trace('Going into( FOR I IN 1..p_approver_name.count): '|| l_proc);
698 FOR I IN 1..p_approver_name.count
699 LOOP
700 IF p_approver_flag(I)='N' THEN
701 ln_addntl_approvers := ln_addntl_approvers + 1;
702 -- get the person_id for this person_name
703 OPEN lc_approver ( p_full_name=>p_approver_name(I));
704 hr_utility.trace('Going into Fetch after (OPEN lc_approver ( p_full_name=>p_approver_name(I))): '|| l_proc);
705 FETCH lc_approver INTO ln_person_id;
706 IF lc_approver%NOTFOUND
707 THEN
708 lv_exists := 'N';
709 raise gv_invalid_person;
710 ELSE
711 lv_exists:= 'Y';
712 END IF ;
713 CLOSE lc_approver;
714
715 -- create the item attribute if it does not exist and update the value
716 lv_item_name := gv_item_name || to_char(I);
717
718 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
719 hr_utility.trace('Going into Fetch after (OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
720 FETCH csr_wiav into lv_dummy;
721 IF csr_wiav%notfound then
722 -- item attribute does not exist so create it
723 hr_approval_wf.create_item_attrib_if_notexist
724 (p_item_type => p_item_type,
725 p_item_key => p_item_key,
726 p_name => lv_item_name);
727 wf_engine.SetItemAttrNumber
728 (itemtype => p_item_type,
729 itemkey => p_item_key,
730 aname => lv_item_name,
731 avalue => ln_person_id);
732 wf_engine.SetItemAttrText
733 (itemtype => p_item_type,
734 itemkey => p_item_key,
735 aname => lv_item_name,
736 avalue => 'VALID');
737 ELSE
738 wf_engine.SetItemAttrNumber
739 (itemtype => p_item_type,
740 itemkey => p_item_key,
741 aname => lv_item_name,
742 avalue => ln_person_id);
743 wf_engine.SetItemAttrText
744 (itemtype => p_item_type,
745 itemkey => p_item_key,
746 aname => lv_item_name,
747 avalue => 'VALID');
748
749 END IF; -- for csr_wiav%notfound
750 CLOSE csr_wiav;
751
752
753 END IF;-- p_approver_flag(I)='N'
754 END LOOP;
755 hr_utility.trace('Out of ( FOR I IN 1..p_approver_name.count): '|| l_proc);
756
757 -- update the number of additional approvers in the system
758
759 OPEN csr_wiav(p_item_type,p_item_key,'ADDITIONAL_APPROVERS_NUMBER');
760 hr_utility.trace('Going into Fetch after (OPEN csr_wiav(p_item_type,p_item_key,ADDITIONAL_APPROVERS_NUMBER)): '|| l_proc);
761 FETCH csr_wiav into lv_dummy;
762 IF csr_wiav%notfound THEN
763 -- create new wf_item_attribute_value to hold the additional approvers number
764 hr_approval_wf.create_item_attrib_if_notexist
765 (p_item_type => p_item_type
766 ,p_item_key => p_item_key
767 ,p_name => 'ADDITIONAL_APPROVERS_NUMBER');
768
769 wf_engine.SetItemAttrNumber
770 (itemtype => p_item_type,
771 itemkey => p_item_key,
772 aname => 'ADDITIONAL_APPROVERS_NUMBER',
773 avalue => ln_addntl_approvers );
774 ELSE
775 wf_engine.SetItemAttrNumber
776 (itemtype => p_item_type,
777 itemkey => p_item_key,
778 aname => 'ADDITIONAL_APPROVERS_NUMBER',
779 avalue => ln_addntl_approvers );
780 END IF;
781 CLOSE csr_wiav;
782
783 end if ; -- end if for AME check
784
785 -- update the data for the notifiers
786 --gv_notifier_name
787 hr_utility.trace('Going into( FOR I IN 1..p_notifier_name.count): '|| l_proc);
788 FOR I IN 1..p_notifier_name.count
789 LOOP
790 -- get the person_id for this person_name
791 OPEN lc_approver ( p_full_name=>p_notifier_name(I));
792 hr_utility.trace('Going into Fetch after (OPEN lc_approver ( p_full_name=>p_notifier_name(I))): '|| l_proc);
793 FETCH lc_approver INTO ln_person_id;
794 IF lc_approver%NOTFOUND
795 THEN
796 lv_exists := 'N';
797 raise gv_invalid_person;
798 ELSE
799 lv_exists:= 'Y';
800 END IF ;
801 CLOSE lc_approver;
802
803 -- create the item attribute if it does not exist and update the value
804 lv_item_name := gv_notifier_name || to_char(I);
805 ln_notifiers := ln_notifiers + 1;
806 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
807 hr_utility.trace('Going into Fetch after (OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
808 FETCH csr_wiav into lv_dummy;
809 IF csr_wiav%notfound then
810 -- item attribute does not exist so create it
811 hr_approval_wf.create_item_attrib_if_notexist
812 (p_item_type => p_item_type,
813 p_item_key => p_item_key,
814 p_name => lv_item_name);
815 wf_engine.SetItemAttrNumber
816 (itemtype => p_item_type,
817 itemkey => p_item_key,
818 aname => lv_item_name,
819 avalue => ln_person_id);
820
821 ELSE
822 wf_engine.SetItemAttrNumber
823 (itemtype => p_item_type,
824 itemkey => p_item_key,
825 aname => lv_item_name,
826 avalue => ln_person_id);
827
828 END IF; -- for csr_wiav%notfound
829 CLOSE csr_wiav;
830
831 -- fetch if this notifier is onsubmittal
832 --loop to check if the user has checked this index
833 lv_exists := 'N';
834 hr_utility.trace('Going into( FOR J IN 1..p_notify_onsubmit_flag.count): '|| l_proc);
835 FOR J IN 1..p_notify_onsubmit_flag.count
836 LOOP
837 IF p_notify_onsubmit_flag(J)=I THEN
838 lv_exists := 'Y';
839 exit;
840 ELSE
841 lv_exists := 'N';
842 END IF;
843 END LOOP;
844 hr_utility.trace('Out of ( FOR J IN 1..p_notify_onsubmit_flag.count): '|| l_proc);
845 lv_notify:=lv_exists ||'|';
846
847 lv_exists := 'N';
848 hr_utility.trace('Going into( FOR K IN 1..p_notify_onapproval_flag.count): '|| l_proc);
849 FOR K IN 1..p_notify_onapproval_flag.count
850 LOOP
851 IF p_notify_onapproval_flag(K)=I THEN
852 hr_utility.trace('In( IF p_notify_onapproval_flag(K)=I): '|| l_proc);
853 lv_exists := 'Y';
854 exit;
855 ELSE
856 lv_exists := 'N';
857 END IF;
858
859 END LOOP;
860 hr_utility.trace('Out of ( FOR K IN 1..p_notify_onapproval_flag.count): '|| l_proc);
861 lv_notify:= lv_notify||lv_exists;
862
863 -- set the notification flag for this notifier
864 wf_engine.SetItemAttrText
865 (itemtype => p_item_type,
866 itemkey => p_item_key,
867 aname => lv_item_name,
868 avalue => lv_notify);
869
870 END LOOP;
871 hr_utility.trace('Out of ( FOR I IN 1..p_notifier_name.count): '|| l_proc);
872
873 -- set the value for the number of notifiers
874 -- ln_notifiers
875
876 OPEN csr_wiav(p_item_type,p_item_key,'NOTIFIERS_NUMBER');
877 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,NOTIFIERS_NUMBER) ): '|| l_proc);
878 FETCH csr_wiav into lv_dummy;
879 IF csr_wiav%notfound THEN
880 -- create new wf_item_attribute_value to hold the additional approvers number
881 hr_approval_wf.create_item_attrib_if_notexist
882 (p_item_type => p_item_type
883 ,p_item_key => p_item_key
884 ,p_name => 'NOTIFIERS_NUMBER');
885
886 wf_engine.SetItemAttrNumber
887 (itemtype => p_item_type,
888 itemkey => p_item_key,
889 aname => 'NOTIFIERS_NUMBER',
890 avalue => ln_notifiers );
891 ELSE
892 wf_engine.SetItemAttrNumber
893 (itemtype => p_item_type,
894 itemkey => p_item_key,
895 aname => 'NOTIFIERS_NUMBER',
896 avalue => ln_notifiers );
897 END IF;
898 CLOSE csr_wiav;
899
900 -- set the gv_mode as re-enter
901 OPEN csr_wiav(p_item_type,p_item_key,'APPROVAL_ENTRY_MODE');
902 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,APPROVAL_ENTRY_MODE) ): '|| l_proc);
903 FETCH csr_wiav into lv_dummy;
904 IF csr_wiav%notfound THEN
905 -- create new wf_item_attribute_value to hold
906 hr_approval_wf.create_item_attrib_if_notexist
907 (p_item_type => p_item_type
908 ,p_item_key => p_item_key
909 ,p_name => 'APPROVAL_ENTRY_MODE');
910
911 wf_engine.SetItemAttrText
912 (itemtype => p_item_type,
913 itemkey => p_item_key,
914 aname => 'APPROVAL_ENTRY_MODE',
915 avalue => 'RE-ENTER');
916 ELSE
917 wf_engine.SetItemAttrText
918 (itemtype => p_item_type,
919 itemkey => p_item_key,
920 aname => 'APPROVAL_ENTRY_MODE',
921 avalue => 'RE-ENTER');
922 END IF;
923 CLOSE csr_wiav;
924
925 -- create new attributes for the transaction work flow process.
926 -- These will be accessed
927 -- from the workflow functions for dyanamic approval and notifications process.
928
929 -- attribute to hold the last_default approver from the heirarchy tree.
930 OPEN csr_wiav(p_item_type,p_item_key,'LAST_DEFAULT_APPROVER');
931 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,LAST_DEFAULT_APPROVER)): '|| l_proc);
932 FETCH csr_wiav into lv_dummy;
933 IF csr_wiav%notfound THEN
934 -- create new wf_item_attribute_value to hold
935 hr_approval_wf.create_item_attrib_if_notexist
936 (p_item_type => p_item_type
937 ,p_item_key => p_item_key
938 ,p_name => 'LAST_DEFAULT_APPROVER');
939
940 wf_engine.SetItemAttrNumber
941 (itemtype => p_item_type,
942 itemkey => p_item_key,
943 aname => 'LAST_DEFAULT_APPROVER',
944 avalue => NULL);
945 ELSE
946 wf_engine.SetItemAttrNumber
947 (itemtype => p_item_type,
948 itemkey => p_item_key,
949 aname => 'LAST_DEFAULT_APPROVER',
950 avalue => NULL);
951 END IF;
952 CLOSE csr_wiav;
953
954 -- Fix for the Bug #1255826
955 /*
956 -- check if the acitvity attribute for approval level exists
957 OPEN csr_wfaav('HR_DYNA_APPR_LEVEL_ATR',p_act_id);
958 FETCH csr_wfaav into lv_dummy;
959 IF csr_wfaav%notfound THEN
960 ln_approval_level :=NULL;
961 ELSE
962 ln_approval_level :=
963 wf_engine.GetActivityAttrNumber(
964 itemtype => p_item_type,
965 itemkey => p_item_key,
966 actid => p_act_id,
967 aname => 'HR_DYNA_APPR_LEVEL_ATR');
968
969 END IF;
970 CLOSE csr_wfaav;
971
972 -- attribute to hold the approval levels for confguration.
973 OPEN csr_wiav(p_item_type,p_item_key,'APPROVAL_LEVEL');
974 FETCH csr_wiav into lv_dummy;
975 IF csr_wiav%notfound THEN
976 -- create new wf_item_attribute_value to hold
977 hr_approval_wf.create_item_attrib_if_notexist
978 (p_item_type => p_item_type
979 ,p_item_key => p_item_key
980 ,p_name => 'APPROVAL_LEVEL');
981
982 END IF;
983 CLOSE csr_wiav;
984 -- set the process level approval level
985 IF ln_approval_level IS NOT NULL THEN
986 wf_engine.SetItemAttrNumber
987 (itemtype => p_item_type,
988 itemkey => p_item_key,
989 aname => 'APPROVAL_LEVEL',
990 avalue => ln_approval_level);
991 END IF;
992
993
994
995 -- attribute to hold the current default approver index .
996 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_DEF_APPR_INDEX');
997 FETCH csr_wiav into lv_dummy;
998 IF csr_wiav%notfound THEN
999 -- create new wf_item_attribute_value to hold
1000 hr_approval_wf.create_item_attrib_if_notexist
1001 (p_item_type => p_item_type
1002 ,p_item_key => p_item_key
1003 ,p_name => 'CURRENT_DEF_APPR_INDEX');
1004
1005 wf_engine.SetItemAttrNumber
1006 (itemtype => p_item_type,
1007 itemkey => p_item_key,
1008 aname => 'CURRENT_DEF_APPR_INDEX',
1009 avalue => 0);
1010 ELSE
1011 wf_engine.SetItemAttrNumber
1012 (itemtype => p_item_type,
1013 itemkey => p_item_key,
1014 aname => 'CURRENT_DEF_APPR_INDEX',
1015 avalue => 0);
1016 END IF;
1017 CLOSE csr_wiav;
1018
1019 */
1020
1021
1022 -- attribute to hold the current approver index .
1023 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_APPROVER_INDEX');
1024 hr_utility.trace('Going into Fetch after (OPEN csr_wiav(p_item_type,p_item_key,CURRENT_APPROVER_INDEX) ): '|| l_proc);
1025 FETCH csr_wiav into lv_dummy;
1026 IF csr_wiav%notfound THEN
1027 -- create new wf_item_attribute_value to hold
1028 hr_approval_wf.create_item_attrib_if_notexist
1029 (p_item_type => p_item_type
1030 ,p_item_key => p_item_key
1031 ,p_name => 'CURRENT_APPROVER_INDEX');
1032
1033 wf_engine.SetItemAttrNumber
1034 (itemtype => p_item_type,
1035 itemkey => p_item_key,
1036 aname => 'CURRENT_APPROVER_INDEX',
1037 avalue => 0);
1038 ELSE
1039 wf_engine.SetItemAttrNumber
1040 (itemtype => p_item_type,
1041 itemkey => p_item_key,
1042 aname => 'CURRENT_APPROVER_INDEX',
1043 avalue => 0);
1044 END IF;
1045 CLOSE csr_wiav;
1046
1047 -- attribute to hold the current onsubmit notifier index .
1048 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_ONSUBMIT_INDEX');
1049 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,CURRENT_ONSUBMIT_INDEX) ): '|| l_proc);
1050 FETCH csr_wiav into lv_dummy;
1051 IF csr_wiav%notfound THEN
1052 -- create new wf_item_attribute_value to hold
1053 hr_approval_wf.create_item_attrib_if_notexist
1054 (p_item_type => p_item_type
1055 ,p_item_key => p_item_key
1056 ,p_name => 'CURRENT_ONSUBMIT_INDEX');
1057
1058 wf_engine.SetItemAttrNumber
1059 (itemtype => p_item_type,
1060 itemkey => p_item_key,
1061 aname => 'CURRENT_ONSUBMIT_INDEX',
1062 avalue => 0);
1063 ELSE
1064 wf_engine.SetItemAttrNumber
1065 (itemtype => p_item_type,
1066 itemkey => p_item_key,
1067 aname => 'CURRENT_ONSUBMIT_INDEX',
1068 avalue => 0);
1069 END IF;
1070 CLOSE csr_wiav;
1071
1072 -- attribute to hold the current onapproval notifier index .
1073 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_ONAPPROVAL_INDEX');
1074 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,CURRENT_ONAPPROVAL_INDEX) ): '|| l_proc);
1075 FETCH csr_wiav into lv_dummy;
1076 IF csr_wiav%notfound THEN
1077 -- create new wf_item_attribute_value to hold
1078 hr_approval_wf.create_item_attrib_if_notexist
1079 (p_item_type => p_item_type
1080 ,p_item_key => p_item_key
1081 ,p_name => 'CURRENT_ONAPPROVAL_INDEX');
1082
1083 wf_engine.SetItemAttrNumber
1084 (itemtype => p_item_type,
1085 itemkey => p_item_key,
1086 aname => 'CURRENT_ONAPPROVAL_INDEX',
1087 avalue => 0);
1088 ELSE
1089 wf_engine.SetItemAttrNumber
1090 (itemtype => p_item_type,
1091 itemkey => p_item_key,
1092 aname => 'CURRENT_ONAPPROVAL_INDEX',
1093 avalue => 0);
1094 END IF;
1095 CLOSE csr_wiav;
1096
1097 hr_utility.set_location('Leaving: '|| l_proc,130);
1098 EXCEPTION
1099 WHEN gv_invalid_person THEN
1100 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1101 if lc_approver%isopen then
1102 close lc_approver;
1103 end if;
1104 if csr_wiav%isopen then
1105 close csr_wiav;
1106 end if;
1107 raise;
1108 WHEN OTHERS THEN
1109 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1110 if lc_approver%isopen then
1111 close lc_approver;
1112 end if;
1113 if csr_wiav%isopen then
1114 close csr_wiav;
1115 end if;
1116 raise;
1117 END COMMIT_DATA;
1118
1119
1120
1121
1122
1123 -- ---------------------------------------------------------------------------
1124 -- public Procedure declarations
1125 -- ---------------------------------------------------------------------------
1126 --
1127 -- ----------------------------------------------------------------------------
1128 -- |------------------------------< validate_approvers>-------------------|
1129 -- ----------------------------------------------------------------------------
1130 --
1131 -- This procedure validates the full_name against the person_id's for the given
1132 -- effective date of the transaction.
1133 --
1134 PROCEDURE validate_approvers(
1135 p_item_type in varchar2,
1136 p_item_key in varchar2,
1137 p_approvers_name IN hr_util_misc_web.g_varchar2_tab_type,
1138 p_approver_flag IN hr_util_misc_web.g_varchar2_tab_type,
1139 p_error_flag OUT NOCOPY BOOLEAN )
1140 IS
1141 -- Local variables
1142 ln_approver_id per_people_f.person_id%TYPE DEFAULT NULL;
1143 lv_job_title VARCHAR2(1000) DEFAULT NULL;
1144 ln_assignment_id NUMBER;
1145 ld_effective_date DATE;
1146 lv_exists VARCHAR2(10) DEFAULT 'N';
1147 ln_job_id NUMBER;
1148 l_curr_org_name VARCHAR2(100);
1149 l_curr_loc_name VARCHAR2(100);
1150 l_proc constant varchar2(100) := g_package || ' validate_approvers';
1151
1152 --
1153 BEGIN
1154 hr_utility.set_location('Entering: '|| l_proc,5);
1155 -- validate the session
1156 hr_util_misc_web.validate_session(p_person_id => gn_person_id);
1157
1158 -- get user date format
1159 gv_user_date_format := hr_util_misc_web.get_user_date_format;
1160
1161
1162 -- get session language code
1163 gv_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1164
1165 -- get effective date from Workflow Item Attribute
1166 gv_effective_date := wf_engine.getItemAttrText(
1167 itemtype => p_item_type,
1168 itemkey => p_item_key,
1169 aname => 'CURRENT_EFFECTIVE_DATE');
1170
1171 ld_effective_date := to_date(gv_effective_date, gv_user_date_format);
1172
1173
1174 -- first validate if selected person is valid
1175 p_error_flag := FALSE ;
1176
1177
1178 -- Loop through the names entered to see if we have a valid person_id
1179 -- for each name
1180 hr_utility.trace('Going into(FOR I IN 1..p_approvers_name.count): '|| l_proc);
1181 FOR I IN 1..p_approvers_name.count
1182 LOOP
1183 lv_job_title := NULL;
1184 OPEN lc_approver(p_approvers_name(I));
1185 hr_utility.trace('Going into Fetch after (OPEN lc_approver(p_approvers_name(I)) ): '|| l_proc);
1186 FETCH lc_approver INTO ln_approver_id ;
1187 IF lc_approver%NOTFOUND
1188 THEN
1189 p_error_flag := TRUE ;
1190 lv_exists := 'Y';
1191
1192 ELSE
1193 gv_error_table(I).full_name := p_approvers_name(I);
1194 gv_error_table(I).person_id := ln_approver_id;
1195 gv_error_table(I).job_title := '';
1196 gv_error_table(I).error_exists := 'N';
1197 gv_error_table(I).default_approver:= p_approver_flag(I);
1198 lv_exists := 'N';
1199 END IF ;
1200 CLOSE lc_approver;
1201
1202 -- IF p_error_flag AND lv_exists = 'Y' THEN
1203 IF lv_exists = 'Y' THEN
1204 hr_utility.trace('In(IF lv_exist = Y): '|| l_proc);
1205 gv_error_table(I).full_name := p_approvers_name(I);
1206 gv_error_table(I).person_id := NULL;
1207 gv_error_table(I).job_title := NULL;
1208 gv_error_table(I).error_exists := 'Y';
1209 gv_error_table(I).default_approver:= p_approver_flag(I);
1210 -- add a row level error here
1211 hr_errors_api.addErrorToTable (
1212 p_rownumber => i,
1213 p_errorMsg=>hr_util_misc_web.return_msg_text
1214 (p_message_name => 'HR_DA_MESG05_WEB'
1215 ,p_application_id => 'PER'));
1216 ELSE
1217 hr_utility.trace('In else of (IF lv_exist = Y): '|| l_proc);
1218 gv_error_table(I).full_name := p_approvers_name(I);
1219 gv_error_table(I).person_id := ln_approver_id;
1220 gv_error_table(I).error_exists := 'N';
1221 gv_error_table(I).default_approver:= p_approver_flag(I);
1222 -- get the assignment id for this person
1223 OPEN gc_assignment_id (p_person_id=>ln_approver_id,
1224 p_effective_date=>ld_effective_date);
1225 hr_utility.trace('Going into Fetch after (OPEN gc_assignment_id (p_person_id...,p_effective_date..)): '|| l_proc);
1226 FETCH gc_assignment_id INTO ln_assignment_id;
1227 IF gc_assignment_id%NOTFOUND
1228 THEN
1229 lv_job_title := NULL;
1230 ln_assignment_id:= NULL;
1231 END IF;
1232 CLOSE gc_assignment_id;
1233
1234 -- get the job title for this assignment_id
1235 /*OPEN gc_job_details (p_assignment_id=>ln_assignment_id,
1236 p_effective_date=>ld_effective_date);
1237 FETCH gc_job_details INTO lv_job_title;
1238 IF gc_job_details%NOTFOUND
1239 THEN
1240 lv_job_title := NULL;
1241 ln_assignment_id:= NULL;
1242 END IF;
1243 CLOSE gc_job_details;
1244 */
1245 -- get job id gc_job_id
1246 OPEN gc_job_id (p_assignment_id=>ln_assignment_id,
1247 p_effective_date=>ld_effective_date);
1248 hr_utility.trace('Going into Fetch after ( OPEN gc_job_id (p_assignment_id.., p_effective_date) ): '|| l_proc);
1249 FETCH gc_job_id INTO ln_job_id;
1250 IF gc_job_id%NOTFOUND
1251 THEN
1252 ln_job_id := NULL;
1253 ln_assignment_id:= NULL;
1254 END IF;
1255 CLOSE gc_job_id;
1256
1257
1258 hr_suit_match_utility_web.get_job_info
1259 (p_search_type => gv_job_type,
1260 p_id => ln_job_id,
1261 p_name => lv_job_title,
1262 p_org_name => l_curr_org_name,
1263 p_location_code => l_curr_loc_name);
1264
1265
1266 -- update the row record with proper job title
1267 gv_error_table(I).job_title := lv_job_title;
1268 END IF;
1269
1270 END LOOP;
1271
1272
1273 grt_approver_details_table := gv_error_table;
1274 hr_utility.set_location('Leaving: '|| l_proc,35);
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1278 if lc_approver%isopen then
1279 close lc_approver;
1280 end if;
1281 if csr_wiav%isopen then
1282 close csr_wiav;
1283 end if;
1284 if gc_assignment_id%isopen then
1285 close gc_assignment_id;
1286 end if;
1287 if gc_job_details%isopen then
1288 close gc_job_details;
1289 end if;
1290 if gc_job_id%isopen then
1291 close gc_job_id;
1292 end if;
1293 raise;
1294 END validate_approvers;
1295
1296
1297 -- ---------------------------------------------------------------------------
1298 -- public Procedure declarations
1299 -- ---------------------------------------------------------------------------
1300 --
1301 -- ----------------------------------------------------------------------------
1302 -- |------------------------------< validate_notifiers>-------------------|
1303 -- ----------------------------------------------------------------------------
1304 --
1305 -- This procedure validates the full_name against the person_id's for the given
1306 -- effective date of the transaction.
1307 --
1308 PROCEDURE validate_notifiers(
1309 p_item_type in varchar2,
1310 p_item_key in varchar2,
1311 p_notifier_name IN hr_util_misc_web.g_varchar2_tab_type,
1312 p_notify_onsubmit_flag hr_util_misc_web.g_varchar2_tab_type
1313 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
1314 p_notify_onapproval_flag hr_util_misc_web.g_varchar2_tab_type
1315 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
1316 p_error_flag OUT NOCOPY BOOLEAN )
1317 IS
1318 -- Local variables
1319 ln_notifier_id per_people_f.person_id%TYPE DEFAULT NULL;
1320 lv_job_title VARCHAR2(1000) DEFAULT NULL;
1321 ln_assignment_id NUMBER;
1322 ld_effective_date DATE;
1323 lv_exists VARCHAR2(10) DEFAULT 'N';
1324 ln_job_id NUMBER;
1325 l_curr_org_name VARCHAR2(100);
1326 l_curr_loc_name VARCHAR2(100);
1327 l_proc constant varchar2(100) := g_package || ' validate_notifiers';
1328
1329 --
1330 BEGIN
1331
1332 hr_utility.set_location('Entering: '|| l_proc,5);
1333 -- validate the session
1334 hr_util_misc_web.validate_session(p_person_id => gn_person_id);
1335
1336 -- get user date format
1337 gv_user_date_format := hr_util_misc_web.get_user_date_format;
1338
1339
1340 -- get session language code
1341 gv_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1342
1343 -- get effective date from Workflow Item Attribute
1344 gv_effective_date := wf_engine.getItemAttrText(
1345 itemtype => p_item_type,
1346 itemkey => p_item_key,
1347 aname => 'CURRENT_EFFECTIVE_DATE');
1348
1349 ld_effective_date := to_date(gv_effective_date, gv_user_date_format);
1350
1351
1352 -- first validate if selected person is valid
1353 p_error_flag := FALSE ;
1354
1355
1356 -- Loop through the names entered to see if we have a valid person_id
1357 -- for each name
1358 hr_utility.trace('Going into (FOR I IN 1..p_notifier_name.count): '|| l_proc);
1359
1360 FOR I IN 1..p_notifier_name.count
1361 LOOP
1362 lv_job_title := NULL;
1363 OPEN lc_approver(p_notifier_name(I));
1364 hr_utility.trace('Going into Fetch after ( OPEN lc_approver(p_notifier_name(I))): '|| l_proc);
1365 FETCH lc_approver INTO ln_notifier_id ;
1366 IF lc_approver%NOTFOUND
1367 THEN
1368 p_error_flag := TRUE ;
1369 lv_exists := 'Y';
1370
1371 ELSE
1372 grt_notifier_error_table(I).full_name := p_notifier_name(I);
1373 grt_notifier_error_table(I).person_id := ln_notifier_id;
1374 grt_notifier_error_table(I).job_title := '';
1375 grt_notifier_error_table(I).error_exists := 'N';
1376 -- fix for bug # 1570998
1377 --grt_notifier_error_table(I).on_submit:= p_notify_onsubmit_flag (I);
1378 lv_exists := 'N';
1379 END IF ;
1380 CLOSE lc_approver;
1381
1382 -- IF p_error_flag AND lv_exists = 'Y' THEN
1383 IF lv_exists = 'Y' THEN
1384 hr_utility.trace('In(IF lv_exists = Y): '|| l_proc);
1385 grt_notifier_error_table(I).full_name := p_notifier_name(I);
1386 grt_notifier_error_table(I).person_id := NULL;
1387 grt_notifier_error_table(I).job_title := NULL;
1388 grt_notifier_error_table(I).error_exists := 'Y';
1389 -- fix for bug # 1570998
1390 --grt_notifier_error_table(I).on_submit:= p_notify_onsubmit_flag (I);
1391 -- add a row level error here
1392 hr_errors_api.addErrorToTable (
1393 p_rownumber => (100+i),
1394 p_errorMsg=>hr_util_misc_web.return_msg_text
1395 (p_message_name => 'HR_DA_MESG06_WEB'
1396 ,p_application_id => 'PER'));
1397 ELSE
1398 hr_utility.trace('In else of (IF lv_exists = Y): '|| l_proc);
1399 grt_notifier_error_table(I).full_name := p_notifier_name(I);
1400 grt_notifier_error_table(I).person_id := ln_notifier_id;
1401 grt_notifier_error_table(I).error_exists := 'N';
1402 -- fix for bug # 1570998
1403 --grt_notifier_error_table(I).on_submit:= p_notify_onsubmit_flag (I);
1404 lv_job_title := hr_dynamic_approval_web.get_job_details
1405 (p_person_id =>ln_notifier_id,
1406 p_assignment_id=>ln_assignment_id,
1407 p_effective_date=>ld_effective_date
1408 );
1409
1410 -- update the row record with proper job title
1411 grt_notifier_error_table(I).job_title := lv_job_title;
1412 END IF;
1413
1414 END LOOP;
1415 hr_utility.trace('Out of (FOR I IN 1..p_notifier_name.count): '|| l_proc);
1416 hr_utility.set_location('Leaving: '|| l_proc,30);
1417 grt_notifier_details_table := grt_notifier_error_table;
1418
1419 EXCEPTION
1420 WHEN OTHERS THEN
1421 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1422 if lc_approver%isopen then
1423 close lc_approver;
1424 end if;
1425 raise;
1426 END validate_notifiers;
1427
1428
1429
1430
1431
1432
1433 /*
1434 ||==========================================================================
1435 || PROCEDURE: get_wf_attributes
1436 ||--------------------------------------------------------------------------
1437 ||
1438 || Description:
1439 ||
1440 ||
1441 || Access Status:
1442 || Public.
1443 ||
1444 ||==========================================================================
1445 */
1446
1447
1448
1449
1450
1451 PROCEDURE get_wf_attributes (
1452 p_item_type in wf_items.item_type%TYPE
1453 ,p_item_key in wf_items.item_key%TYPE
1454 ,p_actid in number
1455 )
1456 AS
1457 lv_dynamic_approval_mode VARCHAR2(100) DEFAULT 'HR_DYNA_APPROVAL_ATR';
1458 ln_approval_level VARCHAR2(100) DEFAULT 'HR_DYNA_APPR_LEVEL_ATR';
1459 lv_dummy VARCHAR2(10);
1460 --local variables
1461 l_proc constant varchar2(100) := g_package || ' get_wf_attributes';
1462 BEGIN
1463
1464 hr_utility.set_location('Entering: '|| l_proc,5);
1465 IF hr_mee_workflow_service.check_web_page_code(
1466 p_item_type => p_item_type,
1467 p_item_key => p_item_key,
1468 p_actid => p_actid,
1469 p_web_page_section_code => lv_dynamic_approval_mode)
1470 THEN
1471 grt_wf_attributes_rec.dynamic_approval_mode :=
1472 hr_mee_workflow_service.get_web_page_code(
1473 p_item_type => p_item_type,
1474 p_item_key => p_item_key,
1475 p_actID => p_actid,
1476 p_web_page_section_code => lv_dynamic_approval_mode
1477 );
1478 END IF;
1479
1480 IF hr_mee_workflow_service.check_web_page_code(
1481 p_item_type => p_item_type,
1482 p_item_key => p_item_key,
1483 p_actid => p_actid,
1484 p_web_page_section_code => ln_approval_level)
1485 THEN
1486 grt_wf_attributes_rec.approval_level :=
1487 hr_mee_workflow_service.get_web_page_code(
1488 p_item_type => p_item_type,
1489 p_item_key => p_item_key,
1490 p_actID => p_actid,
1491 p_web_page_section_code => ln_approval_level
1492 );
1493 END IF;
1494 -- The hr_mee_workflow_service.get_web_page_code is not returning values
1495 -- need to invesitgate further and debug. Making a direct call to wf_engine
1496 -- package.
1497 -- check if the acitvity attribute for approval level exists
1498 OPEN csr_wfaav(ln_approval_level,p_actid );
1499 hr_utility.trace('Going into Fetch after ( OPEN csr_wfaav(ln_approval_level,p_actid ) ): '|| l_proc);
1500 FETCH csr_wfaav into lv_dummy;
1501 IF csr_wfaav%notfound THEN
1502 grt_wf_attributes_rec.approval_level :=NULL;
1503 ELSE
1504 grt_wf_attributes_rec.approval_level :=
1505 wf_engine.GetActivityAttrNumber(
1506 itemtype => p_item_type,
1507 itemkey => p_item_key,
1508 actid => p_actid,
1509 aname => ln_approval_level);
1510
1511 END IF;
1512 CLOSE csr_wfaav;
1513
1514 hr_utility.set_location('Leaving: '|| l_proc,15);
1515
1516
1517 EXCEPTION
1518 WHEN OTHERS THEN
1519 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1520 if csr_wfaav%isopen then
1521 close csr_wfaav;
1522 end if;
1523 raise;
1524
1525 END get_wf_attributes;
1526 -- Methods calling AME
1527 /*-----------------------------------------------------------------------
1528
1529 || PROCEDURE : get_ame_default_approvers
1530 ||
1531 ||
1532 ||-----------------------------------------------------------------------*/
1533
1534 PROCEDURE get_ame_default_approvers(
1535 p_approver_name OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
1536 p_approver_flag OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
1537 p_item_type IN wf_items.item_type%TYPE,
1538 p_item_key IN wf_items.item_key%TYPE)
1539
1540 AS
1541
1542 --local variables
1543 lrt_person_details_rec_table hr_dynamic_approval_web.t_person_table;
1544 lv_creator_person_id per_people_f.person_id%TYPE ;
1545 lv_forward_from_id per_people_f.person_id%TYPE DEFAULT NULL;
1546 lv_forward_to_id per_people_f.person_id%TYPE DEFAULT NULL;
1547 lv_current_forward_to_id per_people_f.person_id%TYPE ;
1548 lv_current_forward_from_id per_people_f.person_id%TYPE ;
1549 lv_result VARCHAR2(20) DEFAULT 'N';
1550 ln_approver_index NUMBER DEFAULT 1;
1551 lv_full_name per_people_f.full_name%TYPE;
1552 lv_job_title per_jobs.name%TYPE;
1553 ln_assignment_id per_assignments_f.ASSIGNMENT_ID%TYPE;
1554 ld_effective_date per_assignments_f.EFFECTIVE_START_DATE%TYPE;
1555 lrt_assignment_details hr_misc_web.grt_assignment_details;
1556 l_proc constant varchar2(100) := g_package || ' get_ame_default_approvers';
1557 --my new variables
1558 lv_approver_name hr_util_misc_web.g_varchar2_tab_type DEFAULT
1559 hr_util_misc_web.g_varchar2_tab_default;
1560 lv_approver_flag hr_util_misc_web.g_varchar2_tab_type DEFAULT
1561 hr_util_misc_web.g_varchar2_tab_default;
1562 ln_job_id NUMBER;
1563 l_curr_org_name VARCHAR2(100);
1564 l_curr_loc_name VARCHAR2(100);
1565
1566 --bug #1964924
1567 ln_approval_level number;
1568
1569 -- Variables for AME API
1570 c_application_id integer;
1571 c_transaction_id varchar2(25);
1572 c_transaction_type varchar2(25);
1573 c_next_approver_rec ame_util.approverRecord;
1574 c_default_approvers ame_util.approversTable;
1575
1576
1577 BEGIN
1578 hr_utility.set_location('Entering: '|| l_proc,5);
1579
1580 gn_person_id := wf_engine.GetItemAttrNumber
1581 (itemtype => p_item_type
1582 ,itemkey => p_item_key
1583 ,aname => 'CREATOR_PERSON_ID');
1584
1585 -- Get AME related data from WF attributes
1586 -- get the AME transaction type and app id
1587 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1588 itemkey => p_item_key,
1589 aname => 'HR_AME_APP_ID_ATTR');
1590
1591
1592 c_application_id := nvl(c_application_id,800);
1593
1594 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1595 itemkey => p_item_key,
1596 aname => 'TRANSACTION_ID');
1597
1598
1599
1600 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1601 itemkey => p_item_key,
1602 aname => 'HR_AME_TRAN_TYPE_ATTR');
1603
1604
1605 -- check if we need to call AME for default approvers.
1606
1607 if(c_transaction_type is not null) then
1608 ame_api.getAllApprovers(applicationIdIn =>c_application_id,
1609 transactionIdIn=>c_transaction_id,
1610 transactionTypeIn =>c_transaction_type,
1611 approversOut=>c_default_approvers);
1612
1613 end if;
1614
1615
1616 -- reset the gn_approver_index each time this procedure is called
1617 gn_approver_index := 1;
1618 hr_utility.trace('Going into( for i in 1..c_default_approvers.count): '|| l_proc);
1619 for i in 1..c_default_approvers.count LOOP
1620 lv_job_title:=NULL;
1621 -- get the next approver from the list
1622 -- Check if the AME approver is authority approver
1623 if(c_default_approvers(i).api_insertion <> ame_util.apiInsertion) then
1624 lv_forward_to_id := c_default_approvers(i).person_id;
1625 -- get assignment id for the approver
1626 lrt_assignment_details := hr_misc_web.get_assignment_id (p_person_id =>lv_forward_to_id);
1627 ln_assignment_id := lrt_assignment_details.assignment_id;
1628 -- get name and job title for this person id
1629 lrt_assignment_details := hr_misc_web.get_assignment_details(p_assignment_id => ln_assignment_id,
1630 p_effective_date =>ld_effective_date);
1631
1632 lv_full_name := lrt_assignment_details.person_full_name;
1633
1634 lv_job_title := hr_dynamic_approval_web.get_job_details(p_person_id =>lv_forward_to_id,
1635 p_assignment_id=>ln_assignment_id,
1636 p_effective_date=>ld_effective_date);
1637 -- Insert the data into the person_details_rec_table
1638 grt_person_details_rec_table(gn_approver_index).person_id := lv_forward_to_id ;
1639 grt_person_details_rec_table(gn_approver_index).full_name := lv_full_name;
1640 grt_person_details_rec_table(gn_approver_index).job_title := lv_job_title;
1641 grt_person_details_rec_table(gn_approver_index).default_approver:= 'Y';
1642
1643 gn_approver_index := gn_approver_index + 1;
1644 END IF;
1645 END LOOP;
1646 hr_utility.trace('Out of ( for i in 1..c_default_approvers.count): '|| l_proc);
1647 -- for the out parameters
1648 hr_utility.trace('Going into( FOR I IN 1..grt_person_details_rec_table.count ): '|| l_proc);
1649 FOR I IN 1..grt_person_details_rec_table.count LOOP
1650 p_approver_name(I) := grt_person_details_rec_table(I).full_name;
1651 p_approver_flag(I) := grt_person_details_rec_table(I).default_approver;
1652 END LOOP;
1653 hr_utility.trace('Out of ( FOR I IN 1..grt_person_details_rec_table.count ): '|| l_proc);
1654 grt_approver_details_table := grt_person_details_rec_table;
1655 hr_utility.set_location('Leaving: '|| l_proc,30);
1656 EXCEPTION
1657 WHEN OTHERS THEN
1658 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1659 raise;
1660 END get_ame_default_approvers;
1661
1662 -- get_all_ame_approvers
1663
1664 /*-----------------------------------------------------------------------
1665
1666 || PROCEDURE : get_all_ame_approvers
1667 ||
1668 ||
1669 ||-----------------------------------------------------------------------*/
1670
1671 PROCEDURE get_all_ame_approvers(p_approver_name hr_util_misc_web.g_varchar2_tab_type
1672 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
1673 p_approver_flag hr_util_misc_web.g_varchar2_tab_type
1674 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
1675 p_item_type IN wf_items.item_type%TYPE,
1676 p_item_key IN wf_items.item_key%TYPE,
1677 p_effective_date IN DATE DEFAULT SYSDATE)
1678
1679 AS
1680
1681 --local variables
1682 ln_approver_index NUMBER DEFAULT 1;
1683 ln_approver_list_index NUMBER DEFAULT 0;
1684 ln_num_of_add_apprs NUMBER DEFAULT 0;
1685 lv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
1686 l_dummy VARCHAR2(100);
1687 ln_def_app_index NUMBER DEFAULT 1;
1688 ln_person_id per_people_f.person_id%TYPE;
1689 ln_assignment_id per_assignments_f.assignment_id%TYPE;
1690 lrt_assignment_details hr_misc_web.grt_assignment_details;
1691 lv_approver_deleted VARCHAR2(100) DEFAULT NULL;
1692 lv_default_approver VARCHAR2(100) DEFAULT 'NO';
1693 ln_error_count NUMBER DEFAULT 1;
1694 lv_job_title VARCHAR2(1000);
1695 lv_approver_name hr_util_misc_web.g_varchar2_tab_type
1696 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
1697 lv_approver_flag hr_util_misc_web.g_varchar2_tab_type
1698 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
1699 ln_job_id NUMBER;
1700 l_curr_org_name VARCHAR2(100);
1701 l_curr_loc_name VARCHAR2(100);
1702 l_proc constant varchar2(100) := g_package || ' et_all_ame_approvers';
1703 -- Variables for AME API
1704 c_application_id integer;
1705 c_transaction_id varchar2(25);
1706 c_transaction_type varchar2(25);
1707 c_next_approver_rec ame_util.approverRecord;
1708 c_all_approvers ame_util.approversTable;
1709
1710
1711
1712 BEGIN
1713 hr_utility.set_location('Entering: '|| l_proc,5);
1714
1715 IF gv_mode='RE-ENTER' THEN
1716 hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
1717
1718 -- Get AME related data from WF attributes
1719 -- get the AME transaction type and app id
1720 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1721 itemkey => p_item_key,
1722 aname => 'HR_AME_APP_ID_ATTR');
1723
1724
1725 c_application_id := nvl(c_application_id,800);
1726
1727 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1728 itemkey => p_item_key,
1729 aname => 'TRANSACTION_ID');
1730
1731
1732
1733 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1734 itemkey => p_item_key,
1735 aname => 'HR_AME_TRAN_TYPE_ATTR');
1736
1737
1738 -- check if we need to call AME for default approvers.
1739
1740 if(c_transaction_type is not null) then
1741 ame_api.getAllApprovers(applicationIdIn =>c_application_id,
1742 transactionIdIn=>c_transaction_id,
1743 transactionTypeIn =>c_transaction_type,
1744 approversOut=>c_all_approvers);
1745 end if;
1746
1747 ln_approver_index := c_all_approvers.count;
1748 hr_utility.trace('Going into (FOR I IN 1..ln_approver_index ): '|| l_proc);
1749 FOR I IN 1..ln_approver_index LOOP
1750 -- 11510 specific changes, bug 3841261
1751 if(nvl(c_all_approvers(i).approval_status,'NULL') not in(ame_util.suppressedStatus,ame_util.repeatedStatus)) then
1752 ln_approver_list_index:= ln_approver_list_index + 1;
1753 lv_job_title := NULL;
1754 ln_person_id := c_all_approvers(i).person_id;
1755
1756 -- get the assignment id
1757 lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
1758 ln_assignment_id := lrt_assignment_details.assignment_id;
1759 -- get name and job title for this person id
1760 lrt_assignment_details := hr_misc_web.get_assignment_details(p_assignment_id => ln_assignment_id,
1761 p_effective_date =>p_effective_date);
1762
1763 grt_approver_details_table(ln_approver_list_index).full_name := lrt_assignment_details.person_full_name;
1764 grt_approver_details_table(ln_approver_list_index).person_id :=ln_person_id;
1765 grt_approver_details_table(ln_approver_list_index).job_title :=hr_dynamic_approval_web.get_job_details
1766 (p_person_id =>ln_person_id,
1767 p_assignment_id=>ln_assignment_id,
1768 p_effective_date=>p_effective_date);
1769
1770
1771 if(c_all_approvers(i).api_insertion <> ame_util.apiInsertion) then
1772 grt_approver_details_table(ln_approver_list_index).default_approver := 'Y';
1773 ELSE
1774 grt_approver_details_table(ln_approver_list_index).default_approver := 'N';
1775 END IF; -- for lv_default_approver
1776 end if;
1777 END LOOP;
1778 hr_utility.trace('Out of (FOR I IN 1..ln_approver_index ): '|| l_proc);
1779 --//grt_person_details_rec_table := grt_approver_details_table;
1780
1781 END IF; -- for the p_mode
1782
1783 hr_utility.trace('Going into (FOR I IN 1..ln_approver_name.count ): '|| l_proc);
1784 FOR I IN 1..p_approver_name.count LOOP
1785 lv_job_title := NULL;
1786 IF hr_errors_api.errorExists THEN
1787 grt_approver_details_table(I).full_name := gv_error_table(ln_error_count).full_name;
1788 grt_approver_details_table(I).person_id := gv_error_table(ln_error_count).person_id;
1789 grt_approver_details_table(I).job_title := gv_error_table(ln_error_count).job_title;
1790 grt_approver_details_table(I).error_exists:= gv_error_table(ln_error_count).error_exists;
1791 grt_approver_details_table(I).default_approver := gv_error_table(ln_error_count).default_approver;
1792 ln_error_count := ln_error_count + 1;
1793 ELSE
1794 -- get the person id for this person
1795 OPEN lc_approver ( p_full_name=>p_approver_name(I));
1796 hr_utility.trace('Going into Fetch after ( OPEN lc_approver ( p_full_name=>p_approver_name(I)) ): '|| l_proc);
1797 FETCH lc_approver INTO ln_person_id ;
1798 IF lc_approver%NOTFOUND THEN
1799 lv_job_title := NULL;
1800 ln_assignment_id:= NULL;
1801 END IF;
1802 CLOSE lc_approver;
1803
1804 lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
1805 ln_assignment_id := lrt_assignment_details.assignment_id;
1806 -- get name and job title for this person id
1807 lrt_assignment_details := hr_misc_web.get_assignment_details(
1808 p_assignment_id => ln_assignment_id,
1809 p_effective_date =>p_effective_date);
1810
1811 lv_job_title := hr_dynamic_approval_web.get_job_details
1812 (p_person_id =>ln_person_id,
1813 p_assignment_id=>ln_assignment_id,
1814 p_effective_date=>p_effective_date
1815 );
1816
1817 grt_approver_details_table(I).person_id := ln_person_id;
1818 grt_approver_details_table(I).full_name := p_approver_name(I);
1819 grt_approver_details_table(I).job_title :=lv_job_title;
1820 grt_approver_details_table(I).default_approver := p_approver_flag(I);
1821 END IF; -- for hr_errors_api.errorExists
1822 END LOOP;
1823 hr_utility.trace('Out of (FOR I IN 1..ln_approver_name.count ): '|| l_proc);
1824 hr_utility.set_location('Leaving: '|| l_proc,40);
1825
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1829 if lc_approver%isopen then
1830 close lc_approver;
1831 end if;
1832 raise;
1833 END get_all_ame_approvers;
1834
1835
1836
1837
1838
1839
1840 /*-----------------------------------------------------------------------
1841
1842 || PROCEDURE : get_default_approvers_list
1843 ||
1844 || This is a wrapper procedure to get_default_approvers to return
1845 || the list of default approvers to a java oracle.sql.ARRAY object
1846 ||
1847 ||
1848 ||
1849 ||-----------------------------------------------------------------------*/
1850
1851 PROCEDURE get_default_approvers_list(
1852 p_item_type IN wf_items.item_type%TYPE,
1853 p_item_key IN wf_items.item_key%TYPE,
1854 p_default_approvers_list OUT NOCOPY hr_dynamic_approver_list_ss)
1855
1856 AS
1857
1858 --local variables
1859 l_approver_name hr_util_misc_web.g_varchar2_tab_type;
1860 l_approver_flag hr_util_misc_web.g_varchar2_tab_type;
1861 l_default_approvers_list hr_dynamic_approver_list_ss := hr_dynamic_approver_list_ss();
1862 l_default_approver hr_dynamic_approver_ss;
1863 l_proc constant varchar2(100) := g_package || ' get_default_approvers_list';
1864 BEGIN
1865 hr_utility.set_location('Entering: '|| l_proc,5);
1866 -- remove all rows from person details table
1867
1868 grt_person_details_rec_table.DELETE;
1869 grt_approver_details_table.DELETE;
1870
1871 -- set the gv_mode as this is needed for pl/sql compatibility
1872 gv_mode:='RE-ENTER';
1873
1874 -- repopulate the table
1875
1876 hr_dynamic_approval_web.get_all_approvers(
1877 p_approver_name =>l_approver_name,
1878 p_approver_flag=>l_approver_flag,
1879 p_item_type =>p_item_type,
1880 p_item_key =>p_item_key
1881 );
1882
1883 grt_person_details_rec_table:= grt_approver_details_table;
1884
1885 -- copy parameters into l_default_approvers_list
1886
1887 hr_utility.trace('Going into (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1888 FOR I IN 1..grt_person_details_rec_table.count
1889 LOOP
1890
1891 l_default_approver := hr_dynamic_approver_ss(
1892 grt_person_details_rec_table(I).full_name,
1893 grt_person_details_rec_table(I).person_id,
1894 grt_person_details_rec_table(I).job_title,
1895 grt_person_details_rec_table(I).default_approver,
1896 grt_person_details_rec_table(I).error_exists);
1897
1898 -- add new row to list
1899 l_default_approvers_list.EXTEND;
1900
1901 -- add to list
1902 l_default_approvers_list(I) := l_default_approver;
1903
1904 END LOOP;
1905 hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1906 -- set out parameter
1907 p_default_approvers_list := l_default_approvers_list;
1908 hr_utility.set_location('Leaving: '|| l_proc,20);
1909
1910 exception
1911 when others then
1912 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1913 raise;
1914
1915 END get_default_approvers_list;
1916
1917
1918
1919
1920 PROCEDURE get_default_approvers_list(
1921 p_item_type IN wf_items.item_type%TYPE,
1922 p_item_key IN wf_items.item_key%TYPE,
1923 p_default_approvers_list OUT NOCOPY hr_dynamic_approver_list_ss,
1924 p_error_message OUT NOCOPY varchar)
1925
1926 AS
1927
1928 --local variables
1929 l_approver_name hr_util_misc_web.g_varchar2_tab_type;
1930 l_approver_flag hr_util_misc_web.g_varchar2_tab_type;
1931 l_default_approvers_list hr_dynamic_approver_list_ss := hr_dynamic_approver_list_ss();
1932 l_default_approver hr_dynamic_approver_ss;
1933 l_proc constant varchar2(100) := g_package || ' get_default_approvers_list_extra';
1934 l_error_message long default null;
1935 BEGIN
1936 hr_utility.set_location('Entering: '|| l_proc,5);
1937 -- remove all rows from person details table
1938
1939 grt_person_details_rec_table.DELETE;
1940 grt_approver_details_table.DELETE;
1941
1942 -- set the gv_mode as this is needed for pl/sql compatibility
1943 gv_mode:='RE-ENTER';
1944
1945 -- repopulate the table
1946
1947 hr_dynamic_approval_web.get_all_approvers(
1948 p_approver_name =>l_approver_name,
1949 p_approver_flag=>l_approver_flag,
1950 p_item_type =>p_item_type,
1951 p_item_key =>p_item_key
1952 );
1953
1954 grt_person_details_rec_table:= grt_approver_details_table;
1955
1956 -- copy parameters into l_default_approvers_list
1957
1958 hr_utility.trace('Going into (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1959 FOR I IN 1..grt_person_details_rec_table.count
1960 LOOP
1961
1962 l_default_approver := hr_dynamic_approver_ss(
1963 grt_person_details_rec_table(I).full_name,
1964 grt_person_details_rec_table(I).person_id,
1965 grt_person_details_rec_table(I).job_title,
1966 grt_person_details_rec_table(I).default_approver,
1967 grt_person_details_rec_table(I).error_exists);
1968
1969 -- add new row to list
1970 l_default_approvers_list.EXTEND;
1971
1972 -- add to list
1973 l_default_approvers_list(I) := l_default_approver;
1974
1975 END LOOP;
1976 hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1977 -- set out parameter
1978 p_default_approvers_list := l_default_approvers_list;
1979 hr_utility.set_location('Leaving: '|| l_proc,20);
1980
1981 exception
1982 when others then
1983 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1984 -- set error message
1985 l_error_message := hr_utility.get_message;
1986
1987 IF (l_error_message IS NOT NULL) THEN
1988 p_error_message := l_error_message;
1989 END IF;
1990 END get_default_approvers_list;
1991
1992
1993 /*-----------------------------------------------------------------------
1994
1995 || PROCEDURE : get_default_approvers
1996 ||
1997 ||
1998 ||-----------------------------------------------------------------------*/
1999
2000 PROCEDURE get_default_approvers(
2001 p_approver_name OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2002 p_approver_flag OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2003 p_item_type IN wf_items.item_type%TYPE,
2004 p_item_key IN wf_items.item_key%TYPE)
2005
2006 AS
2007
2008 --local variables
2009 lrt_person_details_rec_table hr_dynamic_approval_web.t_person_table;
2010 lv_creator_person_id per_people_f.person_id%TYPE ;
2011 lv_forward_from_id per_people_f.person_id%TYPE DEFAULT NULL;
2012 lv_forward_to_id per_people_f.person_id%TYPE DEFAULT NULL;
2013 lv_current_forward_to_id per_people_f.person_id%TYPE ;
2014 lv_current_forward_from_id per_people_f.person_id%TYPE ;
2015 lv_result VARCHAR2(20) DEFAULT 'N';
2016 ln_approver_index NUMBER DEFAULT 1;
2017 lv_full_name per_people_f.full_name%TYPE;
2018 lv_job_title per_jobs.name%TYPE;
2019 ln_assignment_id per_assignments_f.ASSIGNMENT_ID%TYPE;
2020 ld_effective_date per_assignments_f.EFFECTIVE_START_DATE%TYPE;
2021 lrt_assignment_details hr_misc_web.grt_assignment_details;
2022 l_proc constant varchar2(100) := g_package || ' get_default_approvers';
2023 --my new variables
2024 lv_approver_name hr_util_misc_web.g_varchar2_tab_type DEFAULT
2025 hr_util_misc_web.g_varchar2_tab_default;
2026 lv_approver_flag hr_util_misc_web.g_varchar2_tab_type DEFAULT
2027 hr_util_misc_web.g_varchar2_tab_default;
2028 ln_job_id NUMBER;
2029 l_curr_org_name VARCHAR2(100);
2030 l_curr_loc_name VARCHAR2(100);
2031
2032 --bug #1964924
2033 ln_approval_level number;
2034
2035 -- Variables for AME API
2036 c_application_id integer;
2037 c_transaction_id varchar2(25);
2038 c_transaction_type varchar2(25);
2039 c_next_approver_rec ame_util.approverRecord;
2040 c_default_approvers ame_util.approversTable;
2041
2042
2043 BEGIN
2044 hr_utility.set_location('Entering: '|| l_proc,5);
2045 -- validate the session
2046 -- ******************************************************************************
2047 -- commented out for v 4 by pzwalker - replaced with GetItemAttrNumber call
2048 -- hr_util_misc_web.validate_session(p_person_id => gn_person_id);
2049 gn_person_id := wf_engine.GetItemAttrNumber
2050 (itemtype => p_item_type
2051 ,itemkey => p_item_key
2052 ,aname => 'CREATOR_PERSON_ID');
2053
2054
2055 -- Get AME related data from WF attributes
2056 -- get the AME transaction type and app id
2057 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2058 itemkey => p_item_key,
2059 aname => 'HR_AME_APP_ID_ATTR');
2060
2061
2062 c_application_id := nvl(c_application_id,800);
2063
2064 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2065 itemkey => p_item_key,
2066 aname => 'TRANSACTION_ID');
2067
2068
2069
2070 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2071 itemkey => p_item_key,
2072 aname => 'HR_AME_TRAN_TYPE_ATTR');
2073
2074
2075 -- check if we need to call AME for default approvers.
2076
2077 if(c_transaction_type is not null) then
2078
2079 ame_api.getAllApprovers(applicationIdIn =>c_application_id,
2080 transactionIdIn=>c_transaction_id,
2081 transactionTypeIn =>c_transaction_type,
2082 approversOut=>c_default_approvers);
2083
2084 end if;
2085
2086 -- bug # 1964924
2087 ln_approval_level:= wf_engine.GetItemAttrNumber
2088 (itemtype => p_item_type
2089 ,itemkey => p_item_key
2090 ,aname => 'APPROVAL_LEVEL');
2091
2092 -- reset the gn_approver_index each time this procedure is called
2093 gn_approver_index := 1;
2094 -- get assignment id and effective date
2095
2096 hr_mee_workflow_service.get_assignment_details(
2097 p_item_type => p_item_type
2098 ,p_item_key => p_item_key
2099 ,p_assignment_id => ln_assignment_id
2100 ,p_effective_date => ld_effective_date);
2101
2102 -- intialise startup details
2103 lv_creator_person_id := gn_person_id;
2104 lv_current_forward_from_id := lv_creator_person_id;
2105 lv_current_forward_to_id := lv_creator_person_id;
2106
2107 -- -----------------------------------------------------------------------
2108 -- expose the wf control variables to the custom package
2109 -- -----------------------------------------------------------------------
2110 set_custom_wf_globals
2111 (p_itemtype => p_item_type
2112 ,p_itemkey => p_item_key);
2113
2114 -- Get all the approvers from the Custom Approval Package.
2115 hr_utility.trace('Going into(WHILE lv_result <> Y): '|| l_proc);
2116 WHILE lv_result <> 'Y'
2117 LOOP
2118 lv_job_title:=NULL;
2119 -- Check for final approver
2120 BEGIN
2121 lv_result := hr_approval_custom.Check_Final_approver
2122 (p_forward_to_person_id => lv_forward_to_id
2123 ,p_person_id => lv_creator_person_id );
2124 EXCEPTION WHEN OTHERS THEN
2125 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2126 raise;
2127 END;
2128
2129 -- Check if there is any error
2130 IF lv_result='E' THEN
2131 -- Add Error to the Error Table
2132 NULL;
2133 END IF;
2134
2135 IF lv_result='Y' THEN
2136 hr_utility.trace('In (IF lv_result=Y): '|| l_proc);
2137 EXIT;
2138 END IF;
2139
2140 -- get the next approver from the custom package
2141
2142 lv_forward_to_id := hr_approval_custom.Get_Next_Approver
2143 (p_person_id => lv_current_forward_to_id);
2144
2145
2146
2147 -- Check if the person id returned is NULL
2148 IF lv_forward_to_id IS NULL THEN
2149 hr_utility.trace('In ( IF lv_forward_to_id IS NULL): '|| l_proc);
2150 lv_result:='Y';
2151 ELSE
2152 hr_utility.trace('In else of ( IF lv_forward_to_id IS NULL): '|| l_proc);
2153 -- set forward from to old forward to
2154 lv_current_forward_to_id := lv_forward_to_id;
2155
2156 -- get assignment id for the approver
2157 lrt_assignment_details := hr_misc_web.get_assignment_id (
2158 p_person_id =>lv_forward_to_id);
2159 ln_assignment_id := lrt_assignment_details.assignment_id;
2160 -- get name and job title for this person id
2161 lrt_assignment_details := hr_misc_web.get_assignment_details(
2162 p_assignment_id => ln_assignment_id,
2163 p_effective_date =>ld_effective_date
2164 );
2165
2166 lv_full_name := lrt_assignment_details.person_full_name;
2167 lv_job_title := hr_dynamic_approval_web.get_job_details
2168 (p_person_id =>lv_forward_to_id,
2169 p_assignment_id=>ln_assignment_id,
2170 p_effective_date=>ld_effective_date
2171 );
2172 -- Insert the data into the person_details_rec_table
2173 grt_person_details_rec_table(gn_approver_index).person_id := lv_forward_to_id ;
2174 grt_person_details_rec_table(gn_approver_index).full_name := lv_full_name;
2175 grt_person_details_rec_table(gn_approver_index).job_title := lv_job_title;
2176 grt_person_details_rec_table(gn_approver_index).default_approver:= 'Y';
2177
2178 -- bug # 1964924
2179 if(ln_approval_level>0 and ln_approval_level=grt_person_details_rec_table.count) then
2180 hr_utility.trace('In (if(ln_approval_level>0 and ln_approval_level=grt_person_details_rec_table.count)): '|| l_proc);
2181 exit;
2182 end if;
2183
2184 -- Increment approver Index
2185 -- ln_approver_index := ln_approver_index + 1;
2186 gn_approver_index := gn_approver_index + 1;
2187 END IF;
2188
2189
2190 END LOOP;
2191 hr_utility.trace('Out of (WHILE lv_result <> Y): '|| l_proc);
2192 -- for the out parameters
2193 hr_utility.trace('Going into (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
2194 FOR I IN 1..grt_person_details_rec_table.count
2195 LOOP
2196 p_approver_name(I) := grt_person_details_rec_table(I).full_name;
2197 p_approver_flag(I) := grt_person_details_rec_table(I).default_approver;
2198
2199 END LOOP;
2200 hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
2201 grt_approver_details_table := grt_person_details_rec_table;
2202 hr_utility.set_location('Leaving: '|| l_proc,45);
2203
2204 EXCEPTION
2205 WHEN OTHERS THEN
2206 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2207 raise;
2208 END get_default_approvers;
2209
2210
2211 -- get_all_approvers
2212
2213 /*-----------------------------------------------------------------------
2214
2215 || PROCEDURE : get_all_approvers
2216 ||
2217 ||
2218 ||-----------------------------------------------------------------------*/
2219
2220 PROCEDURE get_all_approvers(p_approver_name hr_util_misc_web.g_varchar2_tab_type
2221 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
2222 p_approver_flag hr_util_misc_web.g_varchar2_tab_type
2223 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
2224 p_item_type IN wf_items.item_type%TYPE,
2225 p_item_key IN wf_items.item_key%TYPE,
2226 p_effective_date IN DATE DEFAULT SYSDATE)
2227
2228 AS
2229
2230 --local variables
2231 ln_approver_index NUMBER DEFAULT 1;
2232 ln_num_of_add_apprs NUMBER DEFAULT 0;
2233 lv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
2234 l_dummy VARCHAR2(100);
2235 ln_def_app_index NUMBER DEFAULT 1;
2236 ln_person_id per_people_f.person_id%TYPE;
2237 ln_assignment_id per_assignments_f.assignment_id%TYPE;
2238 lrt_assignment_details hr_misc_web.grt_assignment_details;
2239 lv_approver_deleted VARCHAR2(100) DEFAULT NULL;
2240 lv_default_approver VARCHAR2(100) DEFAULT 'NO';
2241 ln_error_count NUMBER DEFAULT 1;
2242 lv_job_title VARCHAR2(1000);
2243 lv_approver_name hr_util_misc_web.g_varchar2_tab_type
2244 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2245 lv_approver_flag hr_util_misc_web.g_varchar2_tab_type
2246 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2247 ln_job_id NUMBER;
2248 l_curr_org_name VARCHAR2(100);
2249 l_curr_loc_name VARCHAR2(100);
2250 l_proc constant varchar2(100) := g_package || ' get_all_approvers';
2251 -- for AME
2252 c_transaction_type varchar2(25);
2253
2254 BEGIN
2255
2256 hr_utility.set_location('Entering: '|| l_proc,5);
2257 IF gv_mode='RE-ENTER' THEN
2258 hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
2259 -- check if we need to call AME
2260 -- get the AME transaction type value from WF item attributes
2261 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2262 itemkey => p_item_key,
2263 aname => 'HR_AME_TRAN_TYPE_ATTR');
2264
2265
2266 if (c_transaction_type is not null) then
2267 hr_utility.trace('In (if (c_transaction_type is not null)): '|| l_proc);
2268 get_all_ame_approvers(p_approver_name =>p_approver_name,
2269 p_approver_flag =>p_approver_flag,
2270 p_item_type =>p_item_type,
2271 p_item_key =>p_item_key ,
2272 p_effective_date=>p_effective_date);
2273 hr_utility.set_location('Leaving: '|| l_proc,20);
2274
2275 return;
2276 end if;
2277
2278
2279 -- get the default approvers in the system
2280 hr_dynamic_approval_web.get_default_approvers(
2281 p_approver_name =>lv_approver_name,
2282 p_approver_flag=>lv_approver_flag,
2283 p_item_type =>p_item_type,
2284 p_item_key =>p_item_key
2285 );
2286
2287 -- get the number of additional approvers in the system
2288 -- check if the item attribute exists
2289 if hr_workflow_utility.item_attribute_exists
2290 (p_item_type => p_item_type
2291 ,p_item_key => p_item_key
2292 ,p_name => 'ADDITIONAL_APPROVERS_NUMBER') then
2293 -- get the attribute value
2294 ln_num_of_add_apprs := wf_engine.GetItemAttrNumber
2295 (itemtype => p_item_type,
2296 itemkey => p_item_key,
2297 aname => 'ADDITIONAL_APPROVERS_NUMBER');
2298
2299 else
2300 wf_engine.additemattr
2301 (itemtype => p_item_type
2302 ,itemkey => p_item_key
2303 ,aname => 'ADDITIONAL_APPROVERS_NUMBER');
2304
2305 wf_engine.SetItemAttrNumber (itemtype => p_item_type,
2306 itemkey => p_item_key,
2307 aname => 'ADDITIONAL_APPROVERS_NUMBER',
2308 avalue => 0);
2309 end if;
2310
2311
2312
2313 ln_approver_index := lv_approver_name.count + ln_num_of_add_apprs;
2314 hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
2315 FOR I IN 1..ln_approver_index
2316 LOOP
2317 lv_job_title := NULL;
2318 lv_item_name := gv_item_name || to_char(I);
2319 -- open the cursor to determine if the item exists
2320 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
2321 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
2322 FETCH csr_wiav into l_dummy;
2323 IF csr_wiav%notfound THEN
2324 lv_default_approver := 'Y';
2325 ELSE
2326 lv_default_approver := 'N';
2327 -- check if this additional index has been removed by the user
2328 lv_approver_deleted := wf_engine.GetItemAttrText
2329 (itemtype => p_item_type,
2330 itemkey => p_item_key,
2331 aname => lv_item_name);
2332 lv_approver_deleted := NVL(lv_approver_deleted,' ');
2333
2334 END IF; -- for csr_wiav%notfound
2335 CLOSE csr_wiav;
2336
2337 -- insert proper record.
2338 IF lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED' THEN
2339
2340 -- check if an error exists for this additional approver index
2341 IF hr_errors_api.errorExists THEN
2342 grt_approver_details_table(I).full_name := gv_error_table(ln_error_count).full_name;
2343 grt_approver_details_table(I).person_id := gv_error_table(ln_error_count).person_id;
2344 grt_approver_details_table(I).job_title := gv_error_table(ln_error_count).job_title;
2345 grt_approver_details_table(I).error_exists:= gv_error_table(ln_error_count).error_exists;
2346 ln_error_count := ln_error_count + 1;
2347 ELSE
2348 ln_person_id := wf_engine.GetItemAttrNumber
2349 (itemtype => p_item_type,
2350 itemkey => p_item_key,
2351 aname => lv_item_name);
2352
2353 -- get the assignment id
2354 lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
2355 ln_assignment_id := lrt_assignment_details.assignment_id;
2356 -- get name and job title for this person id
2357 lrt_assignment_details := hr_misc_web.get_assignment_details(
2358 p_assignment_id => ln_assignment_id,
2359 p_effective_date =>p_effective_date
2360 );
2361 grt_approver_details_table(I).full_name :=
2362 lrt_assignment_details.person_full_name;
2363 grt_approver_details_table(I).person_id :=ln_person_id;
2364 grt_approver_details_table(I).job_title :=
2365 hr_dynamic_approval_web.get_job_details
2366 (p_person_id =>ln_person_id,
2367 p_assignment_id=>ln_assignment_id,
2368 p_effective_date=>p_effective_date
2369 );
2370
2371
2372 grt_approver_details_table(I).default_approver := lv_default_approver;
2373 END IF; -- for the hr_errors_api.errorExists
2374 ELSE
2375 grt_approver_details_table(I).full_name :=
2376 grt_person_details_rec_table(ln_def_app_index).full_name ;
2377 grt_approver_details_table(I).person_id :=
2378 grt_person_details_rec_table(ln_def_app_index).person_id;
2379 ln_person_id := grt_person_details_rec_table(ln_def_app_index).person_id;
2380 lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
2381 ln_assignment_id := lrt_assignment_details.assignment_id;
2382
2383 grt_approver_details_table(I).job_title :=
2384 hr_dynamic_approval_web.get_job_details
2385 (p_person_id =>ln_person_id,
2386 p_assignment_id=>ln_assignment_id,
2387 p_effective_date=>p_effective_date
2388 );
2389
2390
2391 grt_approver_details_table(I).default_approver := 'Y';
2392 ln_def_app_index := ln_def_app_index + 1;
2393 END IF; -- for lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED'
2394 END LOOP;
2395 hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
2396 hr_utility.set_location('Leaving: '|| l_proc,40);
2397 return;
2398 END IF; -- for the p_mode
2399 hr_utility.trace('Going into(FOR I IN 1..p_approver_name.count): '|| l_proc);
2400
2401 FOR I IN 1..p_approver_name.count
2402 LOOP
2403 lv_job_title := NULL;
2404 IF hr_errors_api.errorExists THEN
2405 grt_approver_details_table(I).full_name := gv_error_table(ln_error_count).full_name;
2406 grt_approver_details_table(I).person_id := gv_error_table(ln_error_count).person_id;
2407 grt_approver_details_table(I).job_title := gv_error_table(ln_error_count).job_title;
2408 grt_approver_details_table(I).error_exists:= gv_error_table(ln_error_count).error_exists;
2409 grt_approver_details_table(I).default_approver := gv_error_table(ln_error_count).default_approver;
2410 ln_error_count := ln_error_count + 1;
2411 ELSE
2412
2413 -- get the person id for this person
2414 OPEN lc_approver ( p_full_name=>p_approver_name(I));
2415 hr_utility.trace('Going into Fetch after (OPEN lc_approver ( p_full_name=>p_approver_name(I)) ): '|| l_proc);
2416 FETCH lc_approver INTO ln_person_id ;
2417 IF lc_approver%NOTFOUND
2418 THEN
2419 lv_job_title := NULL;
2420 ln_assignment_id:= NULL;
2421 END IF;
2422 CLOSE lc_approver;
2423
2424 lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
2425 ln_assignment_id := lrt_assignment_details.assignment_id;
2426 -- get name and job title for this person id
2427 lrt_assignment_details := hr_misc_web.get_assignment_details(
2428 p_assignment_id => ln_assignment_id,
2429 p_effective_date =>p_effective_date
2430 );
2431
2432 lv_job_title := hr_dynamic_approval_web.get_job_details
2433 (p_person_id =>ln_person_id,
2434 p_assignment_id=>ln_assignment_id,
2435 p_effective_date=>p_effective_date
2436 );
2437
2438 grt_approver_details_table(I).person_id := ln_person_id;
2439 grt_approver_details_table(I).full_name := p_approver_name(I);
2440 grt_approver_details_table(I).job_title :=
2441 -- lrt_assignment_details.job_name;
2442 lv_job_title;
2443 grt_approver_details_table(I).default_approver := p_approver_flag(I);
2444
2445 END IF; -- for hr_errors_api.errorExists
2446 END LOOP;
2447 hr_utility.trace('Out of (FOR I IN 1..p_approver_name.count): '|| l_proc);
2448 hr_utility.set_location('Leaving: '|| l_proc,60);
2449
2450
2451 EXCEPTION
2452 WHEN OTHERS THEN
2453 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2454 if lc_approver%isopen then
2455 close lc_approver;
2456 end if;
2457 if csr_wiav%isopen then
2458 close csr_wiav;
2459 end if;
2460 raise;
2461 END get_all_approvers;
2462
2463
2464
2465 /* ---------------------------------------
2466 || FUNCTION: build_ddl
2467 ||
2468 ||
2469 ||---------------------------------------*/
2470
2471 FUNCTION build_ddl(p_approver_name hr_util_misc_web.g_varchar2_tab_type
2472 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
2473 p_approver_flag hr_util_misc_web.g_varchar2_tab_type
2474 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
2475 p_item_type IN wf_items.item_type%TYPE,
2476 p_item_key IN wf_items.item_key%TYPE ,
2477 p_variable_name in varchar2,
2478 p_variable_value in varchar2 DEFAULT NULL
2479 ,p_attributes IN VARCHAR2 DEFAULT NULL) RETURN LONG IS
2480
2481 l_ddl_data hr_dynamic_approval_web.ddl_data;
2482 l_lov LONG;
2483 l_count INTEGER;
2484 l_checked VARCHAR2(25);
2485 lv_variable_name VARCHAR2(200) DEFAULT 'p_person_id';
2486 ln_number_of_approvers NUMBER ;
2487 l_dummy VARCHAR2(100);
2488 lv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
2489 ln_def_app_index NUMBER DEFAULT 1;
2490 ln_person_id per_people_f.person_id%TYPE;
2491 ln_full_name per_people_f.full_name%TYPE;
2492 lv_index NUMBER;
2493 -- local variable
2494 l_proc constant varchar2(100) := g_package || ' build_ddl';
2495 BEGIN
2496 hr_utility.set_location('Entering: '|| l_proc,5);
2497 IF p_approver_name.count=1 THEN
2498 lv_index := p_approver_name.count;
2499 ELSE
2500 lv_index := p_approver_name.count -1;
2501 END IF;
2502 hr_utility.trace('Going into( FOR I IN 1..lv_index): '|| l_proc);
2503
2504
2505 FOR I IN 1..lv_index
2506 LOOP
2507 l_ddl_data(I).label := p_approver_name(I) ;
2508 -- l_ddl_data(I).code := grt_approver_details_table(I).person_id;
2509 l_ddl_data(I).code := I;
2510 l_ddl_data(I).code_index := I;
2511
2512 END LOOP;
2513
2514 hr_utility.trace('Out of ( FOR I IN 1..lv_index): '|| l_proc);
2515 l_lov
2516 := hr_util_misc_web.g_new_line || htf.formselectopen
2517 (cname => upper(p_variable_name)
2518 ,nsize => 1
2519 ,cattributes => p_attributes) ||
2520 hr_util_misc_web.g_new_line ;
2521
2522 hr_utility.trace('Going into(FOR i IN 1..lv_index LOOP): '|| l_proc);
2523 FOR i IN 1..lv_index LOOP
2524 IF p_variable_value IS NOT NULL THEN
2525 IF l_ddl_data(i).code = p_variable_value THEN
2526 l_checked := 'SELECTED';
2527 ELSE
2528 l_checked := null;
2529 END IF;
2530 ELSE
2531 l_checked := null;
2532 END IF;
2533 l_lov := l_lov || htf.formselectoption
2534 (cvalue => l_ddl_data(i).label
2535 ,cselected => l_checked
2536 ,cattributes => 'VALUE="'|| l_ddl_data(i).code
2537 ||'"' ||'INDEX="'|| l_ddl_data(i).code_index
2538 ||'"' ) ;--|| hr_util_misc_web.g_new_line;
2539 END LOOP;
2540 hr_utility.trace('Out of (FOR i IN 1..lv_index LOOP): '|| l_proc);
2541 l_lov := l_lov || htf.formselectclose || hr_util_misc_web.g_new_line;
2542
2543 hr_utility.set_location('Leaving: '|| l_proc,30);
2544
2545 RETURN l_lov;
2546
2547 EXCEPTION WHEN OTHERS THEN
2548 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2549 raise;
2550
2551
2552 END build_ddl;
2553
2554
2555 -- ---------------------------------------------------------------------------
2556 -- Public procedure declarations
2557 -- ---------------------------------------------------------------------------
2558 --
2559 -- ----------------------------------------------------------------------------
2560 -- |------------------------------< add_approver >-------------------|
2561 -- ----------------------------------------------------------------------------
2562 --
2563 -- This procedure will add a new approver to the list passed at index
2564 -- and passes the new list back.
2565 --
2566
2567
2568 PROCEDURE add_approver(p_approver_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2569 p_approver_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2570 p_item_type IN wf_items.item_type%TYPE,
2571 p_item_key IN wf_items.item_key%TYPE,
2572 p_approver_index IN NUMBER DEFAULT 0)
2573 AS
2574 -- Local Variables
2575 ln_number_of_approvers NUMBER ;
2576 ln_additional_approvers NUMBER DEFAULT 0;
2577 lv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
2578 l_dummy NUMBER(1);
2579 lv_item_name_from VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
2580 lv_item_name_to VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
2581 ln_from_index_id per_people_f.person_id%TYPE;
2582 ln_to_index_id per_people_f.person_id%TYPE;
2583 ln_start_index NUMBER ;
2584 ln_end_index NUMBER ;
2585 ln_loop_index NUMBER;
2586 lv_exist VARCHAR2(10) DEFAULT 'NO';
2587 l_proc constant varchar2(100) := g_package || ' add_approver';
2588 --new variables
2589 lv_approver_name hr_util_misc_web.g_varchar2_tab_type
2590 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2591 lv_approver_flag hr_util_misc_web.g_varchar2_tab_type
2592 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2593 BEGIN
2594 hr_utility.set_location('Entering: '|| l_proc,5);
2595 -- validate the session
2596 hr_util_misc_web.validate_session(p_person_id => gn_person_id);
2597
2598 -- set the package global variables
2599 gv_mode := 'ADD';
2600 -- new code
2601
2602 ln_loop_index := 1;
2603 hr_utility.trace('Going into(FOR I IN 1..(p_approver_name.count + 1)): '|| l_proc);
2604 FOR I IN 1..(p_approver_name.count + 1)
2605 LOOP
2606 IF I=(p_approver_index+1) THEN
2607 lv_approver_name(I) := NULL;
2608 lv_approver_flag(I) := 'N';
2609
2610 ELSIF I>p_approver_index THEN
2611 lv_approver_name(I) := p_approver_name(I-1);
2612 lv_approver_flag(I) := p_approver_flag(I-1);
2613 ELSE
2614 lv_approver_name(I) := p_approver_name(I);
2615 lv_approver_flag(I) := p_approver_flag(I);
2616 END IF;
2617 END LOOP;
2618 hr_utility.trace('Out of (FOR I IN 1..(p_approver_name.count + 1)): '|| l_proc);
2619 p_approver_name := lv_approver_name;
2620 p_approver_flag := lv_approver_flag;
2621 hr_utility.set_location('Leaving: '|| l_proc,20);
2622
2623 EXCEPTION WHEN OTHERS THEN
2624 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2625 raise;
2626
2627 END add_approver;
2628
2629
2630
2631
2632 -- ---------------------------------------------------------------------------
2633 -- Public procedure declarations
2634 -- ---------------------------------------------------------------------------
2635 --
2636 -- ----------------------------------------------------------------------------
2637 -- |------------------------------< delete_approver >-------------------|
2638 -- ----------------------------------------------------------------------------
2639 --
2640 -- This procedure will delete a approver from the list passed at index
2641 -- and passes the new list back.
2642 --
2643
2644
2645 PROCEDURE delete_approver(p_approver_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2646 p_approver_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2647 p_item_type IN wf_items.item_type%TYPE,
2648 p_item_key IN wf_items.item_key%TYPE,
2649 p_approver_index IN NUMBER DEFAULT 1)
2650 AS
2651 -- Local Variables
2652 ln_number_of_approvers NUMBER ;
2653 ln_additional_approvers NUMBER DEFAULT 0;
2654 lrt_approver_details_table hr_dynamic_approval_web.approver_rec_table;
2655 lv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
2656 l_dummy NUMBER(1);
2657 lv_item_name_from VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
2658 lv_item_name_to VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
2659 ln_from_index_id per_people_f.person_id%TYPE;
2660 ln_to_index_id per_people_f.person_id%TYPE;
2661 ln_start_index NUMBER ;
2662 ln_end_index NUMBER ;
2663 ln_loop_index NUMBER;
2664 lv_exist VARCHAR2(10) DEFAULT 'NO';
2665 ln_current_index NUMBER;
2666 ln_current_person_id per_people_f.person_id%TYPE;
2667 ln_change_to_index NUMBER;
2668 ln_change_to_person_id per_people_f.person_id%TYPE;
2669 ln_curr_add_appr_index NUMBER DEFAULT 0;
2670 lv_last_addnl_approver VARCHAR2(10) DEFAULT 'NO';
2671 lv_valid_approver VARCHAR2(100) DEFAULT 'VALID';
2672 l_proc constant varchar2(100) := g_package || ' delete_approver';
2673 -- new variables
2674 lv_approver_name hr_util_misc_web.g_varchar2_tab_type
2675 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2676 lv_approver_flag hr_util_misc_web.g_varchar2_tab_type
2677 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2678
2679 BEGIN
2680 hr_utility.set_location('Entering: '|| l_proc,5);
2681 -- validate the session
2682 hr_util_misc_web.validate_session(p_person_id => gn_person_id);
2683
2684
2685 -- get all the additional approvers
2686 -- get number of additional approvers
2687 gn_additional_approvers :=
2688 wf_engine.GetItemAttrNumber(itemtype => p_item_type,
2689 itemkey => p_item_key,
2690 aname => 'ADDITIONAL_APPROVERS_NUMBER');
2691
2692
2693
2694 ln_number_of_approvers := (gn_approver_index -1) + gn_additional_approvers ;
2695
2696 ln_curr_add_appr_index := 0;
2697
2698 -- fix for bug #1570998
2699 /*
2700 FOR I IN REVERSE p_approver_index..ln_number_of_approvers
2701 LOOP
2702 lv_item_name := gv_item_name ||to_char(I);
2703 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
2704 FETCH csr_wiav into l_dummy;
2705 IF csr_wiav%notfound THEN
2706 lv_exist := 'N';
2707 ELSE
2708 lv_exist := 'Y';
2709 ln_curr_add_appr_index := ln_curr_add_appr_index + 1;
2710 END IF;
2711 CLOSE csr_wiav;
2712
2713
2714
2715 -- Find the last approver , get his ID and update the flag to DELETED
2716
2717 IF ln_curr_add_appr_index=1 AND lv_exist = 'Y' THEN
2718 -- get the status of this approver index
2719 lv_valid_approver := wf_engine.GetItemAttrText
2720 (itemtype => p_item_type,
2721 itemkey => p_item_key,
2722 aname => lv_item_name);
2723
2724 IF lv_valid_approver<>'DELETED' THEN
2725 -- get the person_id for this index
2726 ln_from_index_id := wf_engine.GetItemAttrNumber
2727 (itemtype => p_item_type,
2728 itemkey => p_item_key,
2729 aname => lv_item_name);
2730
2731 -- set the flag to DELETED and person_id to NULL
2732 wf_engine.SetItemAttrNumber
2733 (itemtype => p_item_type,
2734 itemkey => p_item_key,
2735 aname => lv_item_name,
2736 avalue => NULL);
2737 wf_engine.SetItemAttrText
2738 (itemtype => p_item_type,
2739 itemkey => p_item_key,
2740 aname => lv_item_name,
2741 avalue => 'DELETED');
2742
2743 -- set the to and from as equal
2744 ln_to_index_id := ln_from_index_id;
2745 ELSE
2746 ln_curr_add_appr_index := 0;
2747 END IF;
2748 ELSIF ln_curr_add_appr_index>1 AND lv_exist = 'Y' THEN
2749 -- get the person_id for this index
2750 ln_from_index_id := wf_engine.GetItemAttrNumber
2751 (itemtype => p_item_type,
2752 itemkey => p_item_key,
2753 aname => lv_item_name);
2754 -- reset it with the new value
2755 wf_engine.SetItemAttrNumber
2756 (itemtype => p_item_type,
2757 itemkey => p_item_key,
2758 aname => lv_item_name,
2759 avalue => ln_to_index_id);
2760 -- reset the 'to' id
2761 ln_to_index_id := ln_from_index_id;
2762 END IF;
2763
2764 END LOOP;
2765
2766 -- reset the index for the number of additional approvers
2767 gn_additional_approvers := (gn_additional_approvers -1);
2768 wf_engine.SetItemAttrNumber
2769 (itemtype => p_item_type,
2770 itemkey => p_item_key,
2771 aname => 'ADDITIONAL_APPROVERS_NUMBER',
2772 avalue => gn_additional_approvers);
2773
2774 */
2775
2776 -- new code
2777 gn_additional_approvers := 0;
2778
2779 hr_utility.trace('Going into( FOR I IN 1..p_approver_name.count): '|| l_proc);
2780 FOR I IN 1..p_approver_name.count
2781 LOOP
2782 IF I=p_approver_index THEN
2783 NULL;
2784 ELSIF I>p_approver_index THEN
2785 lv_approver_name(I-1) := p_approver_name(I);
2786 lv_approver_flag(I-1) := p_approver_flag(I);
2787 ELSE
2788 lv_approver_name(I) := p_approver_name(I);
2789 lv_approver_flag(I) := p_approver_flag(I);
2790 END IF;
2791
2792 if p_approver_flag(I)='Y' then
2793 gn_additional_approvers := gn_additional_approvers + 1;
2794 end if;
2795
2796 END LOOP;
2797 hr_utility.trace('Out of ( FOR I IN 1..p_approver_name.count): '|| l_proc);
2798 wf_engine.SetItemAttrNumber
2799 (itemtype => p_item_type,
2800 itemkey => p_item_key,
2801 aname => 'ADDITIONAL_APPROVERS_NUMBER',
2802 avalue => gn_additional_approvers);
2803
2804
2805
2806 p_approver_name := lv_approver_name;
2807 p_approver_flag := lv_approver_flag;
2808
2809 hr_utility.set_location('Leaving: '|| l_proc,20);
2810
2811 EXCEPTION WHEN OTHERS THEN
2812 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2813 if csr_wiav%isopen then
2814 close csr_wiav;
2815 end if;
2816 raise;
2817
2818 END delete_approver;
2819
2820
2821 -- ---------------------------------------------------------------------------
2822 -- Public procedure declarations
2823 -- ---------------------------------------------------------------------------
2824 --
2825 -- ----------------------------------------------------------------------------
2826 -- |------------------------------< delete_notifier >-------------------|
2827 -- ----------------------------------------------------------------------------
2828 --
2829 -- This procedure will delete a approver from the list passed at index
2830 -- and passes the new list back.
2831 --
2832
2833
2834 PROCEDURE delete_notifier(p_notifier_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2835 p_notify_onsubmit_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2836 p_notify_onapproval_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2837 p_item_type IN wf_items.item_type%TYPE,
2838 p_item_key IN wf_items.item_key%TYPE,
2839 p_notifier_index IN NUMBER DEFAULT 1)
2840 AS
2841 -- Local Variables
2842 lv_notifier_name hr_util_misc_web.g_varchar2_tab_type;
2843 lv_notify_onsubmit_flag hr_util_misc_web.g_varchar2_tab_type;
2844 lv_notify_onapproval_flag hr_util_misc_web.g_varchar2_tab_type;
2845 lv_exist VARCHAR2(10);
2846 l_proc constant varchar2(100) := g_package || ' delete_notifier';
2847 BEGIN
2848 hr_utility.set_location('Entering: '|| l_proc,5);hr_utility.set_location('Going into(FOR I IN 1..p_notifier_name.count): '|| l_proc,10);
2849
2850
2851 FOR I IN 1..p_notifier_name.count
2852 LOOP
2853 IF I=p_notifier_index THEN
2854 hr_utility.trace('In(IF I=p_notifier_index): '|| l_proc);
2855 NULL;
2856 ELSIF I>p_notifier_index THEN
2857 hr_utility.trace('In(ELSIF I>p_notifier_index ): '|| l_proc);
2858 lv_notifier_name(I-1) := p_notifier_name(I);
2859 --loop to check if the user has checked this index
2860 lv_exist := 'N';
2861 FOR J IN 1..p_notify_onsubmit_flag.count
2862 LOOP
2863 IF p_notify_onsubmit_flag(J)=I THEN
2864 lv_exist := 'Y';
2865 exit;
2866 ELSE
2867 lv_exist := 'N';
2868 END IF;
2869 END LOOP;
2870 IF lv_exist='Y' THEN
2871 lv_notify_onsubmit_flag(I-1) := (I-1) ;
2872 ELSE
2873 lv_notify_onsubmit_flag(I-1) := NULL ;
2874 END IF;
2875
2876 lv_exist := 'N';
2877 FOR K IN 1..p_notify_onapproval_flag.count
2878 LOOP
2879 IF p_notify_onapproval_flag(K)=I THEN
2880 lv_exist := 'Y';
2881 exit;
2882 ELSE
2883 lv_exist := 'N';
2884 END IF;
2885
2886 END LOOP;
2887 IF lv_exist='Y' THEN
2888 lv_notify_onapproval_flag(I-1) := (I-1) ;
2889 ELSE
2890 lv_notify_onapproval_flag(I-1) := NULL ;
2891 END IF;
2892
2893
2894
2895 ELSE
2896 hr_utility.trace('In else of (IF I=p_notifier_index): '|| l_proc);
2897 lv_notifier_name(I) := p_notifier_name(I);
2898 --loop to check if the user has checked this index
2899 lv_exist := 'N';
2900 FOR J IN 1..p_notify_onsubmit_flag.count
2901 LOOP
2902 IF p_notify_onsubmit_flag(J)=I THEN
2903 lv_exist := 'Y';
2904 exit;
2905 ELSE
2906 lv_exist := 'N';
2907 END IF;
2908 END LOOP;
2909 IF lv_exist='Y' THEN
2910 lv_notify_onsubmit_flag(I) := I ;
2911 ELSE
2912 lv_notify_onsubmit_flag(I) := NULL ;
2913 END IF;
2914
2915 lv_exist := 'N';
2916 FOR K IN 1..p_notify_onapproval_flag.count
2917 LOOP
2918 IF p_notify_onapproval_flag(K)=I THEN
2919 lv_exist := 'Y';
2920 exit;
2921 ELSE
2922 lv_exist := 'N';
2923 END IF;
2924
2925 END LOOP;
2926 IF lv_exist='Y' THEN
2927 lv_notify_onapproval_flag(I) := I ;
2928 ELSE
2929 lv_notify_onapproval_flag(I) := NULL ;
2930 END IF;
2931
2932
2933
2934 END IF;
2935 END LOOP;
2936 hr_utility.trace('Out (FOR I IN 1..p_notifier_name.count): '|| l_proc);
2937 p_notifier_name := lv_notifier_name;
2938 p_notify_onsubmit_flag:= lv_notify_onsubmit_flag;
2939 p_notify_onapproval_flag := lv_notify_onapproval_flag;
2940
2941 hr_utility.set_location('Leaving: '|| l_proc,25);
2942
2943 EXCEPTION WHEN OTHERS THEN
2944 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2945 raise;
2946
2947 END delete_notifier;
2948
2949
2950 -- ---------------------------------------------------------------------------
2951 -- Public procedure declarations
2952 -- ---------------------------------------------------------------------------
2953 --
2954
2955 -- ---------------------------------------------------------------------------
2956 -- public Procedure declarations
2957 -- ---------------------------------------------------------------------------
2958 --
2959 -- ----------------------------------------------------------------------------
2960 -- |------------------------------< add_notifier>-------------------|
2961 -- ----------------------------------------------------------------------------
2962 --
2963 -- This procedure adds a new notifier to the list with onsubmit and onapproval as
2964 -- default flags.
2965 --
2966
2967
2968 PROCEDURE add_notifier(p_notifier_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2969 p_notify_onsubmit_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2970 p_notify_onapproval_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
2971 p_item_type IN wf_items.item_type%TYPE
2972 ,p_item_key IN wf_items.item_key%TYPE
2973 ,P_PERSON_NAME IN per_all_people_f.full_name%TYPE
2974 ,p_person_id IN per_all_people_f.person_id%TYPE
2975 )
2976 AS
2977 -- Local Variables
2978 lv_exist VARCHAR2(10) DEFAULT 'N';
2979 l_dummy VARCHAR2(100) ;
2980 lv_item_name VARCHAR2(100);
2981 l_proc constant varchar2(100) := g_package || ' add_notifier';
2982 -- new variables
2983 lv_notifier_name hr_util_misc_web.g_varchar2_tab_type
2984 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2985 lv_notify_onsubmit_flag hr_util_misc_web.g_varchar2_tab_type
2986 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2987 lv_notify_onapproval_flag hr_util_misc_web.g_varchar2_tab_type
2988 DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2989 BEGIN
2990
2991 hr_utility.set_location('Entering: '|| l_proc,5);
2992 lv_notifier_name := p_notifier_name;
2993 lv_notify_onsubmit_flag := p_notify_onsubmit_flag ;
2994 lv_notify_onapproval_flag := p_notify_onapproval_flag;
2995
2996 lv_notifier_name(p_notifier_name.count +1) := P_PERSON_NAME;
2997 lv_notify_onsubmit_flag(p_notify_onsubmit_flag.count +1) := to_char((p_notifier_name.count +1));
2998 lv_notify_onapproval_flag(p_notify_onapproval_flag.count+1) := to_char((p_notifier_name.count +1));
2999
3000 p_notifier_name := lv_notifier_name;
3001 p_notify_onsubmit_flag := lv_notify_onsubmit_flag;
3002 p_notify_onapproval_flag := lv_notify_onapproval_flag;
3003 hr_utility.set_location('Leaving: '|| l_proc,10);
3004
3005
3006 EXCEPTION WHEN OTHERS THEN
3007 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3008 raise;
3009 END add_notifier;
3010
3011 --
3012 -- ------------------------------------------------------------------------
3013 -- |------------------------< Get_all_notifiers >-------------------------|
3014 -- ------------------------------------------------------------------------
3015 --
3016 -- Description
3017 --
3018 -- Get all the notifiers for the process
3019 --
3020 --
3021 PROCEDURE Get_all_notifiers(
3022 p_notifier_name IN hr_util_misc_web.g_varchar2_tab_type,
3023 p_notify_onsubmit_flag IN hr_util_misc_web.g_varchar2_tab_type,
3024 p_notify_onapproval_flag IN hr_util_misc_web.g_varchar2_tab_type,
3025 p_item_type IN wf_items.item_type%TYPE,
3026 p_item_key IN wf_items.item_key%TYPE,
3027 p_effective_date IN DATE
3028 )
3029 AS
3030 -- Local Variables
3031 lv_exist VARCHAR2(10) DEFAULT 'N';
3032 l_dummy VARCHAR2(100) ;
3033 lv_item_name VARCHAR2(100);
3034 lrt_notifier_details_table hr_dynamic_approval_web.notifier_rec_table;
3035 ln_person_id per_people_f.person_id%TYPE;
3036 lv_full_name per_people_f.person_id%TYPE;
3037 lv_job_title VARCHAR2(1000);
3038 lv_on_submit VARCHAR2(10);
3039 lv_on_approval VARCHAR2(10);
3040 lv_notify VARCHAR2(10);
3041 ln_assignment_id NUMBER ;
3042 lrt_assignment_details hr_misc_web.grt_assignment_details;
3043 ln_loop_index NUMBER;
3044 ln_job_id NUMBER;
3045 l_curr_org_name VARCHAR2(100);
3046 l_curr_loc_name VARCHAR2(100);
3047 ln_error_count NUMBER DEFAULT 1;
3048 l_proc constant varchar2(100) := g_package || ' Get_all_notifiers';
3049 BEGIN
3050 hr_utility.set_location('Entering: '|| l_proc,5);
3051
3052 IF gv_mode='RE-ENTER' THEN
3053 hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
3054 -- get the number of notifiers in the system
3055 OPEN csr_wiav(p_item_type,p_item_key,'NOTIFIERS_NUMBER');
3056 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,NOTIFIERS_NUMBER) ):'|| l_proc);
3057 FETCH csr_wiav into l_dummy;
3058 IF csr_wiav%notfound THEN
3059 gn_notifiers := 0;
3060 ELSE
3061 gn_notifiers :=
3062 wf_engine.GetItemAttrNumber(itemtype => p_item_type,
3063 itemkey => p_item_key,
3064 aname => 'NOTIFIERS_NUMBER');
3065 END IF;
3066 CLOSE csr_wiav;
3067
3068 IF gn_notifiers > 0 THEN
3069 hr_utility.trace('In( IF gn_notifiers > 0 ): '|| l_proc);
3070 --loop througthe counter and get all the notifiers
3071 hr_utility.trace('Going into( FOR I IN 1..gn_notifiers ): '|| l_proc);
3072 FOR I IN 1..gn_notifiers
3073 LOOP
3074 lv_job_title := NULL;
3075 lv_item_name := gv_notifier_name||to_char(I);
3076 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
3077 hr_utility.trace('Going into( FETCH csr_wiav into l_dummy; ): '|| l_proc);
3078 FETCH csr_wiav into l_dummy;
3079 IF csr_wiav%notfound THEN
3080 lv_exist := 'N';
3081 ELSE
3082 lv_exist := 'Y';
3083 ln_person_id:= wf_engine.GetItemAttrNumber
3084 (itemtype => p_item_type,
3085 itemkey => p_item_key,
3086 aname => lv_item_name
3087 );
3088 lv_notify := wf_engine.GetItemAttrText
3089 (itemtype => p_item_type,
3090 itemkey => p_item_key,
3091 aname => lv_item_name
3092 );
3093 END IF; -- for csr_wiav%notfound
3094 CLOSE csr_wiav;
3095
3096 IF lv_exist = 'Y' THEN
3097 hr_utility.trace('In(IF lv_exist = Y): '|| l_proc);
3098 -- get the person and assignment details for this person_id
3099 -- get the assignment id
3100 lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
3101 ln_assignment_id := lrt_assignment_details.assignment_id;
3102 -- get name and job title for this person id
3103 lrt_assignment_details := hr_misc_web.get_assignment_details(
3104 p_assignment_id => ln_assignment_id,
3105 p_effective_date =>p_effective_date
3106 );
3107
3108 -- populate the notifiers rec table
3109 lrt_notifier_details_table(I).person_id := ln_person_id;
3110 lrt_notifier_details_table(I).full_name := lrt_assignment_details.person_full_name;
3111
3112 lv_job_title := hr_dynamic_approval_web.get_job_details
3113 (p_person_id =>ln_person_id,
3114 p_assignment_id=>ln_assignment_id,
3115 p_effective_date=>p_effective_date
3116 );
3117 lrt_notifier_details_table(I).job_title := lv_job_title;
3118
3119 -- parse the lv_notify for these values
3120 lrt_notifier_details_table(I).on_submit := SUBSTR(lv_notify,1,1);
3121 lrt_notifier_details_table(I).on_approval := SUBSTR(lv_notify,3,3);
3122 END IF; -- for lv_exist = 'Y'
3123
3124 END LOOP;
3125 hr_utility.trace('Out of ( FOR I IN 1..gn_notifiers ): '|| l_proc);
3126 END IF; -- for gn_notifiers > 0
3127
3128 grt_notifier_details_table := lrt_notifier_details_table;
3129
3130 hr_utility.set_location('Leaving: '|| l_proc,45);
3131 RETURN;
3132 END IF; -- for gv_mode
3133 hr_utility.trace('Going into( FOR I IN 1..p_notifier_name.count ): '|| l_proc);
3134
3135 FOR I IN 1..p_notifier_name.count
3136 LOOP
3137 lv_job_title := NULL;
3138 IF hr_errors_api.errorExists THEN
3139 lrt_notifier_details_table(I).full_name := grt_notifier_error_table(ln_error_count).full_name;
3140 lrt_notifier_details_table(I).error_exists:= grt_notifier_error_table(ln_error_count).error_exists;
3141 ln_error_count := ln_error_count + 1;
3142 ELSE
3143 lrt_notifier_details_table(I).full_name := p_notifier_name(I);
3144 END IF;
3145
3146 --loop to check if the user has checked this index
3147 lv_exist := 'N';
3148 FOR J IN 1..p_notify_onsubmit_flag.count
3149 LOOP
3150 IF p_notify_onsubmit_flag(J)=I THEN
3151 lv_exist := 'Y';
3152 exit;
3153 ELSE
3154 lv_exist := 'N';
3155 END IF;
3156 END LOOP;
3157 lrt_notifier_details_table(I).on_submit:=lv_exist ;
3158
3159 lv_exist := 'N';
3160 FOR K IN 1..p_notify_onapproval_flag.count
3161 LOOP
3162 IF p_notify_onapproval_flag(K)=I THEN
3163 lv_exist := 'Y';
3164 exit;
3165 ELSE
3166 lv_exist := 'N';
3167 END IF;
3168
3169 END LOOP;
3170 lrt_notifier_details_table(I).on_approval:= lv_exist ;
3171
3172
3173 -- get the person id for this person
3174 OPEN lc_approver ( p_full_name=>p_notifier_name(I));
3175 hr_utility.trace('Going into Fetch after (OPEN lc_approver ( p_full_name=>p_notifier_name(I)) ): '|| l_proc);
3176 FETCH lc_approver INTO ln_person_id ;
3177 IF lc_approver%NOTFOUND
3178 THEN
3179 lv_job_title := NULL;
3180 ln_assignment_id:= NULL;
3181 END IF;
3182 CLOSE lc_approver;
3183
3184 IF hr_errors_api.errorExists THEN
3185 lrt_notifier_details_table(I).person_id := NULL;
3186 ELSE
3187 lrt_notifier_details_table(I).person_id := ln_person_id;
3188 END IF;
3189 lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
3190 ln_assignment_id := lrt_assignment_details.assignment_id;
3191
3192 -- get name and job title for this person id
3193 lrt_assignment_details := hr_misc_web.get_assignment_details(
3194 p_assignment_id => ln_assignment_id,
3195 p_effective_date =>p_effective_date
3196 );
3197
3198 lv_job_title := hr_dynamic_approval_web.get_job_details
3199 (p_person_id =>ln_person_id,
3200 p_assignment_id=>ln_assignment_id,
3201 p_effective_date=>p_effective_date
3202 );
3203
3204 lrt_notifier_details_table(I).job_title :=lv_job_title;
3205
3206
3207 END LOOP;
3208 hr_utility.trace('out of ( FOR I IN 1..p_notifier_name.count ): '|| l_proc);
3209
3210 grt_notifier_details_table := lrt_notifier_details_table;
3211 hr_utility.set_location('Leaving: '|| l_proc,65);
3212
3213 EXCEPTION WHEN OTHERS THEN
3214 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3215 if lc_approver%isopen then
3216 close lc_approver;
3217 end if;
3218 if csr_wiav%isopen then
3219 close csr_wiav;
3220 end if;
3221 raise;
3222
3223 END Get_all_notifiers;
3224
3225 -- ---------------------------------------------------------------------------
3226 -- public Procedure declarations
3227 -- ---------------------------------------------------------------------------
3228 --
3229 -- ----------------------------------------------------------------------------
3230 -- |------------------------------< update_notifiers>-------------------|
3231 -- ----------------------------------------------------------------------------
3232 --
3233 -- This procedure updates the notifiers list with proper flags as selected.
3234 --
3235 --
3236
3237
3238 PROCEDURE update_notifiers(
3239 p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
3240 p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
3241 p_act_id IN NUMBER ,
3242 p_notifiers_num IN NUMBER DEFAULT 0,
3243 p_Notify_On_Submit hr_util_misc_web.g_varchar2_tab_type DEFAULT
3244 hr_util_misc_web.g_varchar2_tab_default,
3245 p_Notify_On_Approval hr_util_misc_web.g_varchar2_tab_type DEFAULT
3246 hr_util_misc_web.g_varchar2_tab_default
3247
3248 )
3249 AS
3250 -- Local Variables
3251 lv_error_flag BOOLEAN DEFAULT FALSE;
3252 ln_approver_index NUMBER DEFAULT 0;
3253 lv_item_name VARCHAR2(1000);
3254 lv_default_approver VARCHAR2(10);
3255 ln_count NUMBER DEFAULT 1;
3256 l_dummy VARCHAR2(1000);
3257 lv_approver_deleted VARCHAR2(100);
3258 ln_approver_id NUMBER;
3259 lv_response VARCHAR2(10);
3260 l_proc constant varchar2(100) := g_package || ' update_notifiers';
3261 BEGIN
3262 hr_utility.set_location('Entering: '|| l_proc,5);
3263
3264 hr_utility.trace('Going into (FOR I IN 1..p_Notify_On_Submit.count ): '|| l_proc);
3265
3266 FOR I IN 1..p_Notify_On_Submit.count
3267 LOOP
3268 IF p_Notify_On_Submit(I) = 'Y' THEN
3269 lv_response := 'Y|';
3270 ELSE
3271 lv_response := 'N|';
3272 END IF;
3273
3274 IF p_Notify_On_Approval(I) = 'Y' THEN
3275 lv_response := lv_response ||'Y';
3276 ELSE
3277 lv_response := lv_response ||'N';
3278 END IF;
3279
3280
3281 -- update the wf_item_attributes with new response
3282 lv_item_name := gv_notifier_name||to_char(I);
3283
3284 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
3285 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
3286 FETCH csr_wiav into l_dummy;
3287 IF csr_wiav%notfound THEN
3288 NULL;
3289 ELSE
3290 wf_engine.SetItemAttrText
3291 (itemtype => p_item_type,
3292 itemkey => p_item_key,
3293 aname => lv_item_name,
3294 avalue => lv_response
3295 );
3296 END IF; -- for csr_wiav%notfound
3297 CLOSE csr_wiav;
3298
3299
3300
3301 END LOOP;
3302
3303 hr_utility.trace('Out of (FOR I IN 1..p_Notify_On_Submit.count ): '|| l_proc);
3304 hr_utility.set_location('Leaving: '|| l_proc,25);
3305
3306 EXCEPTION WHEN OTHERS THEN
3307 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3308 if csr_wiav%isopen then
3309 close csr_wiav;
3310 end if;
3311 raise;
3312
3313 END update_notifiers;
3314
3315 --
3316 -- ------------------------------------------------------------------------
3317 -- |------------------------< clean_invalid_data >-------------------------|
3318 -- ------------------------------------------------------------------------
3319 --
3320 -- Description
3321 --
3322 -- Flag the invalid data as deleted for the additional approvers
3323 --
3324 --
3325
3326 PROCEDURE clean_invalid_data( p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
3327 p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
3328 p_act_id IN NUMBER ,
3329 p_approvers_name IN hr_util_misc_web.g_varchar2_tab_type
3330 )
3331 AS
3332 -- Local Varaibles
3333 ln_approver_id per_people_f.person_id%TYPE ;
3334 lv_job_title VARCHAR2(1000) DEFAULT NULL;
3335 ln_assignment_id NUMBER;
3336 ld_effective_date DATE;
3337 lv_exists VARCHAR2(10) DEFAULT 'N';
3338 p_error_flag BOOLEAN;
3339 lv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
3340 ln_approver_index NUMBER;
3341 ln_num_of_add_apprs NUMBER;
3342 l_dummy VARCHAR2(100);
3343 lv_default_approver VARCHAR2(10);
3344 lv_approver_deleted VARCHAR2(20);
3345 ln_person_id NUMBER;
3346 l_proc constant varchar2(100) := g_package || ' clean_invalid_data';
3347 BEGIN
3348 hr_utility.set_location('Entering: '|| l_proc,5);
3349 ln_approver_index := gn_approver_index - 1;
3350 -- get the additional approvers number
3351 ln_num_of_add_apprs := wf_engine.GetItemAttrNumber
3352 (itemtype => p_item_type,
3353 itemkey => p_item_key,
3354 aname => 'ADDITIONAL_APPROVERS_NUMBER');
3355
3356
3357 ln_approver_index := ln_approver_index + ln_num_of_add_apprs;
3358 IF ln_num_of_add_apprs > 0 THEN
3359 hr_utility.trace('In( IF ln_num_of_add_apprs > 0 ): '|| l_proc);
3360 hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
3361 FOR I IN 1..ln_approver_index
3362 LOOP
3363 -- check if an additional approver exists by this index
3364
3365 lv_item_name := gv_item_name ||to_char(I);
3366 -- open the cursor to determine if the item exists
3367 OPEN csr_wiav(p_item_type
3368 ,p_item_key
3369 ,lv_item_name);
3370 hr_utility.trace('Going intoFetch after (OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
3371 FETCH csr_wiav into l_dummy;
3372 IF csr_wiav%notfound THEN
3373 lv_default_approver := 'Y';
3374 ELSE
3375 lv_default_approver := 'N';
3376 -- check if this additional index has been removed by the user
3377
3378 lv_approver_deleted := wf_engine.GetItemAttrText
3379 (itemtype => p_item_type,
3380 itemkey => p_item_key,
3381 aname => lv_item_name);
3382 lv_approver_deleted := NVL(lv_approver_deleted,' ');
3383
3384 END IF; -- for csr_wiav%notfound
3385 CLOSE csr_wiav;
3386
3387 -- delete proper record.
3388 IF lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED' THEN
3389 -- get the approver ID for this index
3390 ln_person_id := wf_engine.GetItemAttrNumber
3391 (itemtype => p_item_type,
3392 itemkey => p_item_key,
3393 aname => lv_item_name);
3394 IF ln_person_id IS NULL THEN
3395 /* hr_dynamic_approval_web.delete_approver(p_item_type=>p_item_type,
3396 p_item_key=>p_item_key,
3397 p_approver_index=>I);
3398 */
3399 NULL;
3400 END IF; -- forln_person_id IS NULL
3401
3402 END IF; -- for lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED'
3403
3404 END LOOP;
3405 hr_utility.trace('Out of ( FOR I IN 1..ln_approver_index): '|| l_proc);
3406 END IF; -- for ln_num_of_add_apprs
3407
3408
3409 hr_utility.set_location('Leaving: '|| l_proc,30);
3410
3411 EXCEPTION WHEN OTHERS THEN
3412 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3413 if csr_wiav%isopen then
3414 close csr_wiav;
3415 end if;
3416 raise;
3417
3418 END clean_invalid_data;
3419
3420
3421
3422 --
3423 -- ------------------------------------------------------------------------
3424 -- |------------------------< Get_next_approver >-------------------------|
3425 -- ------------------------------------------------------------------------
3426 --
3427 -- Description
3428 --
3429 -- Get the next approver in the chain
3430 --
3431 --
3432 procedure Get_Next_Approver ( itemtype in varchar2,
3433 itemkey in varchar2,
3434 actid in number,
3435 funmode in varchar2,
3436 result out nocopy varchar2 )
3437 AS
3438
3439 -- -------------------------------------------------------------------------
3440 -- local variables
3441 -- -------------------------------------------------------------------------
3442 l_creator_person_id per_people_f.person_id%type;
3443 l_forward_from_person_id per_people_f.person_id%type;
3444 l_forward_from_username wf_users.name%type;
3445 l_forward_from_disp_name wf_users.display_name%type;
3446 l_forward_to_person_id per_people_f.person_id%type;
3447 l_forward_to_username wf_users.name%type;
3448 l_forward_to_disp_name wf_users.display_name%type;
3449 l_proc varchar2(61) := gv_package||' get_next_approver';
3450 l_current_forward_to_id per_people_f.person_id%type;
3451 l_current_forward_from_id per_people_f.person_id%type;
3452 lv_last_approver_def VARCHAR2(10) DEFAULT 'Y';
3453 ln_current_approver_index NUMBER ;
3454 ln_curr_def_appr_index NUMBER;
3455 ln_last_default_approver_id per_people_f.person_id%type;
3456 ln_addntl_approver_id per_people_f.person_id%type;
3457 lv_item_name hr_dynamic_approval_web.gv_item_name%type;
3458 ln_addntl_approvers NUMBER;
3459 lv_exists VARCHAR2(10);
3460 lv_dummy VARCHAR2(20);
3461 lv_isvalid VARCHAR2(10);
3462
3463 -- Variables for AME API
3464 c_application_id integer;
3465 c_transaction_id varchar2(25);
3466 c_transaction_type varchar2(25);
3467 c_next_approver_rec ame_util.approverRecord;
3468
3469 --
3470
3471 BEGIN
3472 hr_utility.set_location('Entering: '|| l_proc,5);
3473 --
3474
3475 if ( funmode = 'RUN' ) then
3476 hr_utility.trace('In (if ( funmode = RUN )):'|| l_proc);
3477
3478 -- get the current forward from person
3479 l_current_forward_from_id :=
3480 nvl(wf_engine.GetItemAttrNumber
3481 (itemtype => itemtype
3482 ,itemkey => itemkey
3483 ,aname => 'FORWARD_FROM_PERSON_ID'),
3484 wf_engine.GetItemAttrNumber
3485 (itemtype => itemtype
3486 ,itemkey => itemkey
3487 ,aname => 'CREATOR_PERSON_ID'));
3488 -- get the current forward to person
3489 l_current_forward_to_id :=
3490 nvl(wf_engine.GetItemAttrNumber
3491 (itemtype => itemtype
3492 ,itemkey => itemkey
3493 ,aname => 'FORWARD_TO_PERSON_ID'),
3494 wf_engine.GetItemAttrNumber
3495 (itemtype => itemtype
3496 ,itemkey => itemkey
3497 ,aname => 'CREATOR_PERSON_ID'));
3498
3499
3500 -- get the AME transaction type and app id
3501 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => itemtype ,
3502 itemkey => itemkey,
3503 aname => 'HR_AME_APP_ID_ATTR');
3504
3505
3506 c_application_id := nvl(c_application_id,800);
3507
3508 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => itemtype ,
3509 itemkey => itemkey,
3510 aname => 'TRANSACTION_ID');
3511
3512
3513
3514 c_transaction_type := wf_engine.GetItemAttrText(itemtype => itemtype ,
3515 itemkey => itemkey,
3516 aname => 'HR_AME_TRAN_TYPE_ATTR');
3517
3518
3519
3520 -- check if we need to call AME for default approvers.
3521
3522 if(c_transaction_type is null) then
3523 hr_utility.trace('In (if(c_transaction_type is null) )):'|| l_proc);
3524 -- -----------------------------------------------------------------------
3525 -- expose the wf control variables to the custom package
3526 -- -----------------------------------------------------------------------
3527 set_custom_wf_globals
3528 (p_itemtype => itemtype
3529 ,p_itemkey => itemkey);
3530 --
3531 -- set the next forward to
3532 --
3533
3534
3535 -- get the total number of additional approvers for this transaction
3536 ln_addntl_approvers := NVL(wf_engine.GetItemAttrNumber
3537 (itemtype => itemtype
3538 ,itemkey => itemkey
3539 ,aname => 'ADDITIONAL_APPROVERS_NUMBER'),
3540 0);
3541
3542 -- fix for the bug # 1252070
3543
3544 -- attribute to hold the last_default approver from the heirarchy tree.
3545 OPEN csr_wiav(itemtype,itemkey,'CURRENT_APPROVER_INDEX');
3546 hr_utility.trace('Going into Fetch after (OPEN csr_wiav(itemtype,itemkey,CURRENT_APPROVER_INDEX)):'|| l_proc);
3547 FETCH csr_wiav into lv_dummy;
3548 IF csr_wiav%notfound THEN
3549 -- create new wf_item_attribute_value to hold
3550 hr_approval_wf.create_item_attrib_if_notexist
3551 (p_item_type => itemtype
3552 ,p_item_key => itemkey
3553 ,p_name => 'CURRENT_APPROVER_INDEX');
3554
3555 wf_engine.SetItemAttrNumber
3556 (itemtype => itemtype,
3557 itemkey => itemkey,
3558 aname => 'CURRENT_APPROVER_INDEX',
3559 avalue => NULL);
3560
3561 END IF;
3562 CLOSE csr_wiav;
3563
3564
3565
3566
3567
3568 -- get the current_approver_index
3569 ln_current_approver_index := NVL(wf_engine.GetItemAttrNumber
3570 (itemtype => itemtype
3571 ,itemkey => itemkey
3572 ,aname => 'CURRENT_APPROVER_INDEX'),
3573 0);
3574 -- set the item name
3575 lv_item_name := gv_item_name || to_char(ln_current_approver_index + 1);
3576
3577 -- check if we have additional approver for the next index.
3578 -- Fix for the bug # 1255826
3579 IF ln_current_approver_index <= ln_addntl_approvers
3580 THEN
3581 hr_utility.trace('In ( IF ln_current_approver_index <= ln_addntl_approvers):'|| l_proc);
3582 OPEN csr_wiav(itemtype,itemkey,lv_item_name);
3583 hr_utility.trace('Going into FETCH after(OPEN csr_wiav(itemtype,itemkey,lv_item_name)):'|| l_proc);
3584 FETCH csr_wiav into lv_dummy;
3585 IF csr_wiav%notfound THEN
3586 lv_exists := 'N';
3587 ELSE
3588 lv_exists := 'Y';
3589 lv_isvalid := wf_engine.GetItemAttrText
3590 (itemtype => itemtype,
3591 itemkey => itemkey,
3592 aname => lv_item_name);
3593 lv_isvalid := NVL(lv_isvalid,' ');
3594
3595 END IF;
3596 CLOSE csr_wiav;
3597 ELSE
3598 hr_utility.trace('In else of ( IF ln_current_approver_index <= ln_addntl_approvers):'|| l_proc);
3599 lv_exists := 'N';
3600 END IF;
3601
3602
3603 IF lv_exists <>'N' AND lv_isvalid <>'DELETED' THEN
3604 hr_utility.trace('In ( IF lv_exists <>N AND lv_isvalid <>DELETED):'|| l_proc);
3605 l_forward_to_person_id :=
3606 wf_engine.GetItemAttrNumber
3607 (itemtype => itemtype,
3608 itemkey => itemkey,
3609 aname => lv_item_name
3610 );
3611
3612 ELSE
3613 hr_utility.trace('In else of ( IF lv_exists <>N AND lv_isvalid <>DELETED):'|| l_proc);
3614 -- get the last default approver index
3615
3616 ln_last_default_approver_id := wf_engine.GetItemAttrNumber
3617 (itemtype => itemtype,
3618 itemkey => itemkey,
3619 aname => 'LAST_DEFAULT_APPROVER');
3620
3621
3622
3623 -- get the next approver from the heirarchy tree.
3624 -- fix for bug #2087458
3625 -- the l_current_forward_to_id resetting was removed for default approver.
3626 -- now the from column will show the last approver approved.
3627 l_forward_to_person_id :=
3628 hr_approval_custom.Get_Next_Approver
3629 (p_person_id => NVL(ln_last_default_approver_id,
3630 wf_engine.GetItemAttrNumber
3631 (itemtype => itemtype
3632 ,itemkey => itemkey
3633 ,aname => 'CREATOR_PERSON_ID')));
3634 -- set the last default approver id
3635 -- 'LAST_DEFAULT_APPROVER'
3636 wf_engine.SetItemAttrNumber
3637 (itemtype => itemtype,
3638 itemkey => itemkey,
3639 aname => 'LAST_DEFAULT_APPROVER',
3640 avalue => l_forward_to_person_id);
3641 -- set cuurent approval levels reached
3642 OPEN csr_wiav(itemtype,itemkey,'CURRENT_DEF_APPR_INDEX');
3643 hr_utility.trace('Going into FETCH after(OPEN csr_wiav(itemtype,itemkey,CURRENT_DEF_APPR_INDEX)):'|| l_proc);
3644 FETCH csr_wiav into lv_dummy;
3645 IF csr_wiav%notfound THEN
3646 -- create new wf_item_attribute_value to hold
3647 hr_approval_wf.create_item_attrib_if_notexist
3648 (p_item_type => itemtype
3649 ,p_item_key => itemkey
3650 ,p_name => 'CURRENT_DEF_APPR_INDEX');
3651
3652 wf_engine.SetItemAttrNumber
3653 (itemtype => itemtype,
3654 itemkey => itemkey,
3655 aname => 'CURRENT_DEF_APPR_INDEX',
3656 avalue => 0);
3657 ELSE
3658 ln_curr_def_appr_index :=
3659 wf_engine.GetItemAttrNumber
3660 (itemtype => itemtype,
3661 itemkey => itemkey,
3662 aname => 'CURRENT_DEF_APPR_INDEX'
3663 );
3664 -- increment it and update the item attribute value
3665 ln_curr_def_appr_index := ln_curr_def_appr_index + 1;
3666 wf_engine.SetItemAttrNumber
3667 (itemtype => itemtype,
3668 itemkey => itemkey,
3669 aname => 'CURRENT_DEF_APPR_INDEX',
3670 avalue => ln_curr_def_appr_index);
3671 END IF;
3672 CLOSE csr_wiav;
3673
3674 END IF;
3675
3676 -- set the current_approver_index
3677 wf_engine.SetItemAttrNumber (itemtype => itemtype
3678 ,itemkey => itemkey
3679 ,aname => 'CURRENT_APPROVER_INDEX'
3680 ,avalue => (ln_current_approver_index + 1));
3681
3682 else
3683
3684 hr_utility.trace('In else of (if(c_transaction_type is null) )):'|| l_proc);
3685 ame_api.getNextApprover(applicationIdIn =>c_application_id,
3686 transactionIdIn =>c_transaction_id,
3687 transactionTypeIn =>c_transaction_type,
3688 nextApproverOut =>c_next_approver_rec);
3689
3690 l_forward_to_person_id :=c_next_approver_rec.person_id;
3691 end if; -- check for AME usage
3692
3693 if ( l_forward_to_person_id is null ) then
3694 --
3695 result := 'COMPLETE:F';
3696 --
3697 else
3698 --
3699 wf_directory.GetUserName
3700 (p_orig_system => 'PER'
3701 ,p_orig_system_id => l_forward_to_person_id
3702 ,p_name => l_forward_to_username
3703 ,p_display_name => l_forward_to_disp_name);
3704
3705 --
3706 wf_engine.SetItemAttrNumber
3707 (itemtype => itemtype
3708 ,itemkey => itemkey
3709 ,aname => 'FORWARD_TO_PERSON_ID'
3710 ,avalue => l_forward_to_person_id);
3711 --
3712 wf_engine.SetItemAttrText
3713 (itemtype => itemtype
3714 ,itemkey => itemkey
3715 ,aname => 'FORWARD_TO_USERNAME'
3716 ,avalue => l_forward_to_username);
3717 --
3718 Wf_engine.SetItemAttrText
3719 (itemtype => itemtype
3720 ,itemkey => itemkey
3721 ,aname => 'FORWARD_TO_DISPLAY_NAME'
3722 ,avalue => l_forward_to_disp_name);
3723 --
3724 -- set forward from to old forward to
3725 --
3726 wf_engine.SetItemAttrNumber
3727 (itemtype => itemtype
3728 ,itemkey => itemkey
3729 ,aname => 'FORWARD_FROM_PERSON_ID'
3730 ,avalue => l_current_forward_to_id);
3731 --
3732 -- Get the username and display name for forward from person
3733 -- and save to item attributes
3734 --
3735 wf_directory.GetUserName
3736 (p_orig_system => 'PER'
3737 ,p_orig_system_id => l_current_forward_to_id
3738 ,p_name => l_forward_from_username
3739 ,p_display_name => l_forward_from_disp_name);
3740 --
3741 wf_engine.SetItemAttrText
3742 (itemtype => itemtype
3743 ,itemkey => itemkey
3744 ,aname => 'FORWARD_FROM_USERNAME'
3745 ,avalue => l_forward_from_username);
3746 --
3747 wf_engine.SetItemAttrText
3748 (itemtype => itemtype
3749 ,itemkey => itemkey
3750 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
3751 ,avalue => l_forward_from_disp_name);
3752 --
3753 result := 'COMPLETE:T';
3754 --
3755 end if;
3756 --
3757 elsif ( funmode = 'CANCEL' ) then
3758 hr_utility.trace('In (if ( funmode = CANCEL )):'|| l_proc); --
3759 null;
3760 --
3761 end if;
3762 --
3763 hr_utility.set_location('Leaving: '|| l_proc,45);
3764 EXCEPTION
3765 WHEN OTHERS THEN
3766 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3767 if csr_wiav%isopen then
3768 close csr_wiav;
3769 end if;
3770 END Get_Next_Approver;
3771
3772
3773 -- ------------------------------------------------------------------------
3774 -- |----------------------< Check_Final_Approver >-------------------------|
3775 -- ------------------------------------------------------------------------
3776 --
3777 -- Description
3778 --
3779 -- Determine if this person is the final manager in the approval chain
3780 --
3781 --
3782 procedure Check_Final_Approver( p_item_type in varchar2,
3783 p_item_key in varchar2,
3784 p_act_id in number,
3785 funmode in varchar2,
3786 result out nocopy varchar2 )
3787 AS
3788 -- Local Variables
3789 l_proc varchar2(61) := gv_package||'check_final_approver';
3790 l_creator_person_id per_people_f.person_id%type;
3791 l_forward_to_person_id per_people_f.person_id%type;
3792 l_current_forward_to_id per_people_f.person_id%type;
3793 ln_addntl_approvers NUMBER DEFAULT 0;
3794 ln_approval_level NUMBER DEFAULT 0;
3795 ln_curr_def_appr_index NUMBER DEFAULT 1;
3796 ln_last_def_approver NUMBER;
3797 l_dummy VARCHAR2(100);
3798 lv_exists VARCHAR2(10);
3799 lv_isvalid VARCHAR2(10);
3800 lv_response VARCHAR2(10);
3801
3802
3803 -- Variables required for AME API
3804 c_application_id integer;
3805 c_transaction_id varchar2(25);
3806 c_transaction_type varchar2(25);
3807 c_next_approver_rec ame_util.approverRecord;
3808
3809 --
3810
3811 BEGIN
3812 hr_utility.set_location('Entering: '|| l_proc,5);
3813 --
3814 if ( funmode = 'RUN' ) then
3815 hr_utility.trace('In(if ( funmode = RUN )): '|| l_proc);
3816 --
3817 --
3818 l_creator_person_id := wf_engine.GetItemAttrNumber
3819 (itemtype => p_item_type
3820 ,itemkey => p_item_key
3821 ,aname => 'CREATOR_PERSON_ID');
3822 --
3823 l_forward_to_person_id := wf_engine.GetItemAttrNumber
3824 (itemtype => p_item_type
3825 ,itemkey => p_item_key
3826 ,aname =>'FORWARD_TO_PERSON_ID');
3827
3828 -- get the current forward to person
3829 l_current_forward_to_id :=
3830 nvl(wf_engine.GetItemAttrNumber
3831 (itemtype => p_item_type
3832 ,itemkey => p_item_key
3833 ,aname => 'FORWARD_TO_PERSON_ID'),
3834 wf_engine.GetItemAttrNumber
3835 (itemtype => p_item_type
3836 ,itemkey => p_item_key
3837 ,aname => 'CREATOR_PERSON_ID'));
3838
3839
3840
3841 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
3842 itemkey => p_item_key,
3843 aname => 'HR_AME_APP_ID_ATTR');
3844
3845
3846 c_application_id := nvl(c_application_id,800);
3847 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
3848 itemkey => p_item_key,
3849 aname => 'TRANSACTION_ID');
3850
3851
3852
3853 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3854 itemkey => p_item_key,
3855 aname => 'HR_AME_TRAN_TYPE_ATTR');
3856
3857 if(c_transaction_type is not null) then
3858 hr_utility.trace('In(if ( if(c_transaction_type is not null))): '|| l_proc);
3859 -- fix for bug#2677648
3860 if(l_forward_to_person_id is not null) then
3861 -- call AME update approval status as approved
3862 ame_api.updateApprovalStatus2(applicationIdIn =>c_application_id,
3863 transactionIdIn =>c_transaction_id,
3864 approvalStatusIn =>ame_util.approvedStatus,
3865 approverPersonIdIn =>l_forward_to_person_id,
3866 approverUserIdIn =>null,
3867 transactionTypeIn =>c_transaction_type,
3868 forwardeeIn =>null);
3869 end if; -- call for AME update status
3870
3871 -- call AME to get next approver
3872 ame_api.getNextApprover(applicationIdIn =>c_application_id,
3873 transactionIdIn =>c_transaction_id,
3874 transactionTypeIn =>c_transaction_type,
3875 nextApproverOut =>c_next_approver_rec);
3876
3877 -- check if the person_id of the next approver is null
3878 IF(c_next_approver_rec.person_id is null) THEN
3879 result := 'COMPLETE:'||'Y';
3880 ELSE
3881 result := 'COMPLETE:'||'N';
3882 END IF;
3883
3884
3885 else
3886 hr_utility.trace('In else of ( if(c_transaction_type is not null))): '|| l_proc);
3887 l_forward_to_person_id := NVL(l_forward_to_person_id,l_current_forward_to_id);
3888
3889
3890
3891 -- fix for the bug #1252070
3892
3893 -- attribute to hold the last_default approver from the heirarchy tree.
3894 OPEN csr_wiav(p_item_type,p_item_key,'LAST_DEFAULT_APPROVER');
3895 hr_utility.trace('Going into FETCH after(OPEN csr_wiav(p_item_type,p_item_key,LAST_DEFAULT_APPROVER)): '|| l_proc);
3896 FETCH csr_wiav into l_dummy;
3897 IF csr_wiav%notfound THEN
3898 -- create new wf_item_attribute_value to hold
3899 hr_approval_wf.create_item_attrib_if_notexist
3900 (p_item_type => p_item_type
3901 ,p_item_key => p_item_key
3902 ,p_name => 'LAST_DEFAULT_APPROVER');
3903
3904 wf_engine.SetItemAttrNumber
3905 (itemtype => p_item_type,
3906 itemkey => p_item_key,
3907 aname => 'LAST_DEFAULT_APPROVER',
3908 avalue => NULL);
3909
3910 END IF;
3911 CLOSE csr_wiav;
3912
3913
3914 -- 'LAST_DEFAULT_APPROVER'
3915 ln_last_def_approver:= wf_engine.GetItemAttrNumber
3916 (itemtype => p_item_type,
3917 itemkey => p_item_key,
3918 aname => 'LAST_DEFAULT_APPROVER'
3919 );
3920
3921 ln_last_def_approver:= NVL(ln_last_def_approver,l_forward_to_person_id);
3922
3923 -- -----------------------------------------------------------------------
3924 -- expose the wf control variables to the custom package
3925 -- -----------------------------------------------------------------------
3926 set_custom_wf_globals
3927 (p_itemtype => p_item_type
3928 ,p_itemkey => p_item_key);
3929
3930
3931 -- check if we have default approvers
3932 lv_response := hr_approval_custom.Check_Final_approver
3933 (p_forward_to_person_id => ln_last_def_approver
3934 ,p_person_id => l_creator_person_id );
3935
3936 IF lv_response <>'N' THEN
3937 result := 'COMPLETE:'||
3938 hr_approval_custom.Check_Final_approver
3939 (p_forward_to_person_id => ln_last_def_approver
3940 ,p_person_id => l_creator_person_id );
3941 hr_utility.set_location('Leaving: '|| l_proc,25);
3942 return;
3943
3944 END IF;
3945
3946 -- check if we have reached the max limit on the approvers level
3947 -- the level is based on the heirarchy tree.
3948 -- get the approval level as conifgured by the HR Rep or Sys Admin
3949 OPEN csr_wiav(p_item_type
3950 ,p_item_key
3951 ,'APPROVAL_LEVEL');
3952 hr_utility.trace('Going into FETCH after (OPEN csr_wiav(p_item_type,p_item_key,APPROVAL_LEVEL)):'|| l_proc);
3953 FETCH csr_wiav into l_dummy;
3954 IF csr_wiav%notfound THEN
3955 ln_approval_level := 0;
3956 ELSE
3957 ln_approval_level := wf_engine.GetItemAttrNumber
3958 (itemtype => p_item_type,
3959 itemkey => p_item_key,
3960 aname => 'APPROVAL_LEVEL');
3961 END IF; -- for csr_wiav%notfound
3962 CLOSE csr_wiav;
3963
3964 IF ln_approval_level > 0 THEN
3965 -- get the current approval level reached
3966 -- first check if the attribute exists
3967 hr_utility.trace('In ( IF ln_approval_level > 0 THEN):'|| l_proc);
3968 OPEN csr_wiav(p_item_type
3969 ,p_item_key
3970 ,'CURRENT_DEF_APPR_INDEX');
3971 hr_utility.trace('Going into FETCH after( csr_wiav(p_item_type,p_item_key,CURRENT_DEF_APPR_INDEX)):'|| l_proc);
3972 FETCH csr_wiav into l_dummy;
3973 IF csr_wiav%notfound THEN
3974 NULL;
3975 ELSE
3976 ln_curr_def_appr_index := wf_engine.GetItemAttrNumber
3977 (itemtype => p_item_type,
3978 itemkey => p_item_key,
3979 aname => 'CURRENT_DEF_APPR_INDEX');
3980 END IF;-- for csr_wiav%notfound
3981 CLOSE csr_wiav;
3982
3983 END IF; -- for ln_num_of_add_apprs > 0
3984
3985
3986 -- Fix for the Bug # 1255826
3987 IF (ln_approval_level> 0)
3988
3989 THEN
3990 hr_utility.trace('In ( IF ln_approval_level > 0 THEN):'|| l_proc);
3991 IF( ln_curr_def_appr_index < ln_approval_level)
3992 THEN
3993
3994 -- we have not reached the approval level as configured
3995 result := 'COMPLETE:'||'N';
3996 ELSE
3997 result := 'COMPLETE:'||'Y';
3998 END IF;
3999 ELSE
4000 hr_utility.trace('In Eelse of ( IF ln_approval_level > 0 THEN):'|| l_proc);
4001 result := 'COMPLETE:'||
4002 hr_approval_custom.Check_Final_approver
4003 (p_forward_to_person_id => ln_last_def_approver
4004 ,p_person_id => l_creator_person_id );
4005 END IF;
4006
4007 end if ; -- check for AME
4008
4009 elsif ( funmode = 'CANCEL' ) then
4010 hr_utility.trace('In(if ( funmode = CANCEL )): '|| l_proc);
4011
4012 --
4013 null;
4014 --
4015 end if;
4016 hr_utility.set_location('Leaving: '|| l_proc,50);
4017
4018
4019 EXCEPTION
4020 WHEN OTHERS THEN
4021 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4022 if csr_wiav%isopen then
4023 close csr_wiav;
4024 end if;
4025 END Check_Final_Approver ;
4026
4027 -- ---------------------------------------------------------------------------------
4028 -- |----------------------< Check_Final_OnSubmit_Notifier >-------------------------|
4029 -- ---------------------------------------------------------------------------------
4030 --
4031 -- Description
4032 --
4033 -- Determine if this person is the final manager in the approval chain
4034 --
4035 --
4036 procedure Check_OnSubmit_Notifier( itemtype in varchar2,
4037 itemkey in varchar2,
4038 actid in number,
4039 funmode in varchar2,
4040 result out nocopy varchar2 )
4041 AS
4042 -- Local Variables
4043 ln_current_index NUMBER;
4044 ln_number_of_notifiers NUMBER;
4045 lv_dummy VARCHAR2(10);
4046 lv_exists VARCHAR2(10);
4047 lv_status VARCHAR2(10);
4048 lv_item_name hr_dynamic_approval_web.gv_notifier_name%type;
4049 lv_notify VARCHAR2(10);
4050 lv_submit VARCHAR2(10);
4051 l_proc constant varchar2(100) := g_package || ' Check_OnSubmit_Notifier';
4052 BEGIN
4053 hr_utility.set_location('Entering: '|| l_proc,5);
4054 --
4055 if ( funmode = 'RUN' ) then
4056 hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4057 --
4058 --
4059 -- get the total number of notifiers for the trnsaction
4060 OPEN csr_wiav(itemtype,itemkey,'NOTIFIERS_NUMBER');
4061 hr_utility.trace('Going into FETCH after( OPEN csr_wiav(itemtype,itemkey,NOTIFIERS_NUMBER)):'|| l_proc);
4062 FETCH csr_wiav into lv_dummy;
4063 IF csr_wiav%notfound THEN
4064 ln_number_of_notifiers := 0;
4065 ELSE
4066 ln_number_of_notifiers:=
4067 wf_engine.GetItemAttrNumber
4068 (itemtype => itemtype,
4069 itemkey => itemkey,
4070 aname => 'NOTIFIERS_NUMBER'
4071 );
4072 END IF;
4073 CLOSE csr_wiav;
4074
4075 -- get the current index of the onapproval notifier
4076
4077 OPEN csr_wiav(itemtype,itemkey,'CURRENT_ONSUBMIT_INDEX');
4078 hr_utility.trace('Going into FETCH after( OPEN csr_wiav(itemtype,itemkey,CURRENT_ONSUBMIT_INDEX)):'|| l_proc);
4079 FETCH csr_wiav into lv_dummy;
4080 IF csr_wiav%notfound THEN
4081 lv_exists := 'N';
4082 ln_current_index := 1;
4083
4084 ELSE
4085 ln_current_index:=
4086 NVL( wf_engine.GetItemAttrNumber
4087 (itemtype => itemtype,
4088 itemkey => itemkey,
4089 aname => 'CURRENT_ONSUBMIT_INDEX')
4090 ,0);
4091 END IF;
4092 CLOSE csr_wiav;
4093
4094 ln_current_index := ln_current_index +1;
4095 -- check if there are any notifiers
4096
4097 IF ln_number_of_notifiers > 0 THEN
4098 hr_utility.trace('In(IF ln_number_of_notifiers > 0 ):'|| l_proc);
4099 IF ln_current_index <= ln_number_of_notifiers THEN
4100 hr_utility.trace('In( IF ln_current_index <= ln_number_of_notifiers ):'|| l_proc);
4101 -- loop through all the notifiers to check status
4102 hr_utility.trace('Going into( FOR I in ln_current_index..ln_number_of_notifiers ):'|| l_proc);
4103 FOR I in ln_current_index..ln_number_of_notifiers
4104 LOOP
4105 lv_item_name := gv_notifier_name||to_char(I);
4106
4107 OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4108 hr_utility.trace('Going into FETCH after (OPEN csr_wiav(itemtype,itemkey,lv_item_name)):'|| l_proc);
4109 FETCH csr_wiav into lv_dummy;
4110 IF csr_wiav%notfound THEN
4111 lv_exists := 'N';
4112 ELSE
4113 lv_exists := 'Y';
4114 lv_notify := wf_engine.GetItemAttrText
4115 (itemtype => itemtype,
4116 itemkey => itemkey,
4117 aname => lv_item_name
4118 );
4119 END IF; -- for csr_wiav%notfound
4120 CLOSE csr_wiav;
4121 IF lv_exists = 'Y' THEN
4122 lv_submit:= SUBSTR(lv_notify,1,1);
4123 END IF; -- for lv_exists = 'Y'
4124 IF lv_submit='Y' THEN
4125 result := 'COMPLETE:'||'N';
4126 return;
4127 ELSE
4128 result := 'COMPLETE:'||'Y';
4129 END IF;
4130
4131 END LOOP;
4132 hr_utility.trace('Out of ( FOR I in ln_current_index..ln_number_of_notifiers ):'|| l_proc);
4133 ELSE
4134 hr_utility.trace('In else of ( IF ln_current_index <= ln_number_of_notifiers ):'|| l_proc);
4135 result := 'COMPLETE:'||'Y';
4136 END IF; -- for ln_current_index < ln_number_of_notifiers
4137
4138
4139 ELSE
4140 hr_utility.trace('In else of (IF ln_number_of_notifiers > 0 ):'|| l_proc);
4141 result := 'COMPLETE:'||'Y';
4142 END IF; -- for ln_number_of_notifiers > 0
4143
4144 --
4145 elsif ( funmode = 'CANCEL' ) then
4146
4147 hr_utility.trace('In elsif ( funmode = CANCEL ) then): '|| l_proc);
4148 --
4149 null;
4150 --
4151 end if;
4152
4153 hr_utility.set_location('Leaving: '|| l_proc,50);
4154 EXCEPTION
4155 WHEN OTHERS THEN
4156 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4157 if csr_wiav%isopen then
4158 close csr_wiav;
4159 end if;
4160
4161 END Check_OnSubmit_Notifier ;
4162
4163 -- ---------------------------------------------------------------------------------
4164 -- |----------------------< Check_OnApproval_Notifier >-------------------------|
4165 -- ---------------------------------------------------------------------------------
4166 --
4167 -- Description
4168 --
4169 -- Determine if this person is the final manager in the approval chain
4170 --
4171 --
4172 procedure Check_OnApproval_Notifier( itemtype in varchar2,
4173 itemkey in varchar2,
4174 actid in number,
4175 funmode in varchar2,
4176 result out nocopy varchar2 )
4177 AS
4178 -- Local Variables
4179 ln_current_index NUMBER;
4180 ln_number_of_notifiers NUMBER;
4181 lv_dummy VARCHAR2(10);
4182 lv_exists VARCHAR2(10);
4183 lv_status VARCHAR2(10);
4184 lv_item_name hr_dynamic_approval_web.gv_notifier_name%type;
4185 lv_notify VARCHAR2(10);
4186 lv_onapproval VARCHAR2(10);
4187 l_proc constant varchar2(100) := g_package || ' Check_OnApproval_Notifier';
4188 BEGIN
4189 hr_utility.set_location('Entering: '|| l_proc,5);
4190 --
4191 if ( funmode = 'RUN' ) then
4192 --
4193 --
4194 hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4195 -- get the total number of notifiers for the trnsaction
4196 OPEN csr_wiav(itemtype,itemkey,'NOTIFIERS_NUMBER');
4197 hr_utility.trace('Going into ( FETCH csr_wiav into lv_dummy;):'|| l_proc);
4198 FETCH csr_wiav into lv_dummy;
4199 IF csr_wiav%notfound THEN
4200 ln_number_of_notifiers := 0;
4201 ELSE
4202 ln_number_of_notifiers:=
4203 wf_engine.GetItemAttrNumber
4204 (itemtype => itemtype,
4205 itemkey => itemkey,
4206 aname => 'NOTIFIERS_NUMBER'
4207 );
4208 END IF;
4209 CLOSE csr_wiav;
4210
4211 -- get the current index of the onapproval notifier
4212
4213 OPEN csr_wiav(itemtype,itemkey,'CURRENT_ONAPPROVAL_INDEX');
4214 hr_utility.trace('Going into ( FETCH after OPEN csr_wiav(itemtype,itemkey,CURRENT_ONAPPROVAL_INDEX):'|| l_proc);
4215 FETCH csr_wiav into lv_dummy;
4216 IF csr_wiav%notfound THEN
4217 lv_exists := 'N';
4218 ln_current_index := 1;
4219
4220 ELSE
4221 ln_current_index:=
4222 NVL(wf_engine.GetItemAttrNumber
4223 (itemtype => itemtype,
4224 itemkey => itemkey,
4225 aname => 'CURRENT_ONAPPROVAL_INDEX'
4226 ),0);
4227 END IF;
4228 CLOSE csr_wiav;
4229
4230 ln_current_index := ln_current_index +1;
4231
4232 -- check if there are any notifiers
4233
4234 IF ln_number_of_notifiers > 0 THEN
4235 hr_utility.trace('In ( IF ln_number_of_notifiers > 0): '|| l_proc);
4236 IF ln_current_index <= ln_number_of_notifiers THEN
4237 hr_utility.trace('In (IF ln_current_index <= ln_number_of_notifiers): '|| l_proc);
4238 -- loop through all the notifiers to check status
4239 hr_utility.trace('Going into ( FOR I in ln_current_index..ln_number_of_notifiers): '|| l_proc);
4240 FOR I in ln_current_index..ln_number_of_notifiers
4241 LOOP
4242 lv_item_name := gv_notifier_name||to_char(I);
4243
4244 OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4245 hr_utility.trace('Going Fetch after (OPEN csr_wiav(itemtype,itemkey,lv_item_name);): '|| l_proc);
4246 FETCH csr_wiav into lv_dummy;
4247 IF csr_wiav%notfound THEN
4248
4249
4250 lv_exists := 'N';
4251 ELSE
4252 lv_exists := 'Y';
4253 lv_notify := wf_engine.GetItemAttrText
4254 (itemtype => itemtype,
4255 itemkey => itemkey,
4256 aname => lv_item_name
4257 );
4258 END IF; -- for csr_wiav%notfound
4259 CLOSE csr_wiav;
4260 IF lv_exists = 'Y' THEN
4261 lv_onapproval:= SUBSTR(lv_notify,3,3);
4262 END IF; -- for lv_exists = 'Y'
4263 IF lv_onapproval='Y' THEN
4264 result := 'COMPLETE:'||'N';
4265 hr_utility.set_location('Leaving: '|| l_proc,45);
4266 return;
4267 ELSE
4268 result := 'COMPLETE:'||'Y';
4269 END IF;
4270
4271 END LOOP;
4272 hr_utility.trace('Out of ( FOR I in ln_current_index..ln_number_of_notifiers): '|| l_proc);
4273 ELSE
4274 hr_utility.trace('In else of (IF ln_current_index <= ln_number_of_notifiers): '|| l_proc);
4275 result := 'COMPLETE:'||'Y';
4276 END IF; -- for ln_current_index < ln_number_of_notifiers
4277
4278
4279 ELSE
4280 hr_utility.trace('In else of ( IF ln_number_of_notifiers > 0): '|| l_proc);
4281 result := 'COMPLETE:'||'Y';
4282 END IF; -- for ln_number_of_notifiers > 0
4283
4284 --
4285
4286 elsif ( funmode = 'CANCEL' ) then
4287 hr_utility.trace('In (elsif ( funmode = CANCEL )): '|| l_proc);
4288 --
4289 null;
4290 --
4291 end if;
4292
4293 hr_utility.set_location('Leaving: '|| l_proc,55);
4294 --
4295
4296 EXCEPTION
4297 WHEN OTHERS THEN
4298 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4299 if csr_wiav%isopen then
4300 close csr_wiav;
4301 end if;
4302
4303 END Check_OnApproval_Notifier ;
4304
4305 -- ------------------------------------------------------------------------
4306 -- |------------------------< Get_OnSubmit_notifier >-------------------------|
4307 -- ------------------------------------------------------------------------
4308 --
4309 -- Description
4310 --
4311 -- Get the next notifier in the chain
4312 --
4313 --
4314 procedure Get_OnSubmit_Notifier ( itemtype in varchar2,
4315 itemkey in varchar2,
4316 actid in number,
4317 funmode in varchar2,
4318 result out nocopy varchar2 )
4319 AS
4320
4321 -- Local Variables
4322 l_creator_person_id per_people_f.person_id%type;
4323 l_forward_from_person_id per_people_f.person_id%type;
4324 l_forward_from_username wf_users.name%type;
4325 l_forward_from_disp_name wf_users.display_name%type;
4326 l_forward_to_person_id per_people_f.person_id%type;
4327 l_forward_to_username wf_users.name%type;
4328 l_forward_to_disp_name wf_users.display_name%type;
4329 l_proc varchar2(61) := gv_package||'get_next_approver';
4330 l_current_forward_to_id per_people_f.person_id%type;
4331 l_current_forward_from_id per_people_f.person_id%type;
4332 lv_last_approver_def VARCHAR2(10) DEFAULT 'Y';
4333 ln_current_approver_index NUMBER ;
4334 ln_curr_def_appr_index NUMBER;
4335 ln_last_default_approver_id per_people_f.person_id%type;
4336 ln_addntl_approver_id per_people_f.person_id%type;
4337 ln_addntl_approvers NUMBER;
4338 lv_exists VARCHAR2(10);
4339 lv_dummy VARCHAR2(20);
4340 lv_isvalid VARCHAR2(10);
4341 ln_notifiers NUMBER DEFAULT 0;
4342 ln_current_onsubmit_index NUMBER ;
4343 ln_person_id per_people_f.person_id%type DEFAULT NULL;
4344 lv_notify VARCHAR2(10);
4345 lv_onsubmit VARCHAR2(10);
4346 lv_item_name VARCHAR2(25);
4347 ln_start_index NUMBER;
4348
4349 BEGIN
4350 hr_utility.set_location('Entering: '|| l_proc,5);
4351 if ( funmode = 'RUN' ) then
4352 hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4353 -- get the total number ofnotifiers
4354 ln_notifiers := wf_engine.GetItemAttrNumber
4355 (itemtype => itemtype,
4356 itemkey => itemkey,
4357 aname => 'NOTIFIERS_NUMBER'
4358 );
4359 IF ln_notifiers > 0 THEN
4360 -- get the current index
4361 ln_current_onsubmit_index :=
4362 NVL(wf_engine.GetItemAttrNumber
4363 (itemtype => itemtype
4364 ,itemkey => itemkey
4365 ,aname => 'CURRENT_ONSUBMIT_INDEX'),0);
4366
4367 ELSE
4368 hr_utility.set_location('Leaving: '|| l_proc,15);
4369 result := 'COMPLETE:F';
4370 return;
4371 END IF;
4372
4373
4374 -- -----------------------------------------------------------------------
4375 -- expose the wf control variables to the custom package
4376 -- -----------------------------------------------------------------------
4377 set_custom_wf_globals
4378 (p_itemtype => itemtype
4379 ,p_itemkey => itemkey);
4380
4381
4382 -- loop through and get next notifier
4383 hr_utility.trace('Going into ( FOR I in (ln_current_onsubmit_index + 1)..ln_notifiers): '|| l_proc);
4384 FOR I in (ln_current_onsubmit_index + 1)..ln_notifiers
4385 LOOP
4386 lv_item_name := gv_notifier_name||to_char(I);
4387 OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4388 hr_utility.trace('In Fetch after ( OPEN csr_wiav(itemtype,itemkey,lv_item_name)): '|| l_proc);
4389 FETCH csr_wiav into lv_dummy;
4390 IF csr_wiav%notfound THEN
4391 lv_exists := 'N';
4392 ELSE
4393 lv_exists := 'Y';
4394 ln_person_id:= wf_engine.GetItemAttrNumber
4395 (itemtype => itemtype,
4396 itemkey => itemkey,
4397 aname => lv_item_name
4398 );
4399 lv_notify := wf_engine.GetItemAttrText
4400 (itemtype => itemtype,
4401 itemkey => itemkey,
4402 aname => lv_item_name
4403 );
4404 END IF; -- for csr_wiav%notfound
4405 CLOSE csr_wiav;
4406
4407 IF lv_exists = 'Y' THEN
4408 hr_utility.trace('In ( IF lv_exists = Y): '|| l_proc);
4409 lv_onsubmit := SUBSTR(lv_notify,1,1);
4410 IF lv_onsubmit= 'Y' THEN
4411 wf_engine.SetItemAttrNumber
4412 (itemtype => itemtype,
4413 itemkey => itemkey,
4414 aname => 'CURRENT_ONSUBMIT_INDEX',
4415 avalue => I
4416 );
4417 l_forward_to_person_id := ln_person_id;
4418 EXIT;
4419 ELSE
4420 l_forward_to_person_id := NULL;
4421 result := 'COMPLETE:F';
4422
4423 END IF;
4424 ELSE
4425 hr_utility.trace('In else of ( IF lv_exists = Y): '|| l_proc);
4426 l_forward_to_person_id := NULL;
4427 result := 'COMPLETE:F';
4428
4429 END IF;
4430
4431 END LOOP;
4432 hr_utility.trace('Out of ( FOR I in (ln_current_onsubmit_index + 1)..ln_notifiers): '|| l_proc);
4433 --
4434 -- set the next forward to
4435 --
4436
4437 IF (l_forward_to_person_id is null) THEN
4438 result := 'COMPLETE:F';
4439 --
4440 else
4441 --
4442 wf_directory.GetUserName
4443 (p_orig_system => 'PER'
4444 ,p_orig_system_id => l_forward_to_person_id
4445 ,p_name => l_forward_to_username
4446 ,p_display_name => l_forward_to_disp_name);
4447
4448 --
4449 hr_approval_wf.create_item_attrib_if_notexist
4450 (p_item_type => itemtype
4451 ,p_item_key => itemkey
4452 ,p_name => 'ONSUB_FWD_TO_PERSON_ID');
4453
4454 wf_engine.SetItemAttrNumber
4455 (itemtype => itemtype
4456 ,itemkey => itemkey
4457 ,aname => 'ONSUB_FWD_TO_PERSON_ID'
4458 ,avalue => l_forward_to_person_id);
4459 --
4460 hr_approval_wf.create_item_attrib_if_notexist
4461 (p_item_type => itemtype
4462 ,p_item_key => itemkey
4463 ,p_name => 'ONSUB_FWD_TO_USERNAME');
4464
4465 wf_engine.SetItemAttrText
4466 (itemtype => itemtype
4467 ,itemkey => itemkey
4468 ,aname => 'ONSUB_FWD_TO_USERNAME'
4469 ,avalue => l_forward_to_username);
4470 --
4471
4472 hr_approval_wf.create_item_attrib_if_notexist
4473 (p_item_type => itemtype
4474 ,p_item_key => itemkey
4475 ,p_name => 'ONSUB_FWD_TO_DISPLAY_NAME');
4476 wf_engine.SetItemAttrText
4477 (itemtype => itemtype
4478 ,itemkey => itemkey
4479 ,aname => 'ONSUB_FWD_TO_DISPLAY_NAME'
4480 ,avalue => l_forward_to_disp_name);
4481
4482 --
4483 result := 'COMPLETE:T';
4484 --
4485 end if;
4486 --
4487 elsif ( funmode = 'CANCEL' ) then
4488 hr_utility.trace('In elsif ( funmode = CANCEL )): '|| l_proc);
4489 --
4490 null;
4491 --
4492 end if;
4493 hr_utility.set_location('Leaving: '|| l_proc,40);
4494
4495 --
4496
4497 EXCEPTION
4498 WHEN OTHERS THEN
4499 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4500 if csr_wiav%isopen then
4501 close csr_wiav;
4502 end if;
4503 END Get_OnSubmit_Notifier;
4504
4505 --
4506 -- ------------------------------------------------------------------------
4507 -- |------------------------< Get_OnApproval_notifier >-------------------------|
4508 -- ------------------------------------------------------------------------
4509 --
4510 -- Description
4511 --
4512 -- Get the next notifier in the chain
4513 --
4514 --
4515 procedure Get_OnApproval_Notifier ( itemtype in varchar2,
4516 itemkey in varchar2,
4517 actid in number,
4518 funmode in varchar2,
4519 result out nocopy varchar2 )
4520 AS
4521
4522 -- Local Variables
4523 l_creator_person_id per_people_f.person_id%type;
4524 l_forward_from_person_id per_people_f.person_id%type;
4525 l_forward_from_username wf_users.name%type;
4526 l_forward_from_disp_name wf_users.display_name%type;
4527 l_forward_to_person_id per_people_f.person_id%type DEFAULT NULL;
4528 l_forward_to_username wf_users.name%type;
4529 l_forward_to_disp_name wf_users.display_name%type;
4530 l_proc varchar2(61) := g_package||'get_next_approver';
4531 l_current_forward_to_id per_people_f.person_id%type;
4532 l_current_forward_from_id per_people_f.person_id%type;
4533 lv_last_approver_def VARCHAR2(10) DEFAULT 'Y';
4534 ln_current_approver_index NUMBER ;
4535 ln_curr_def_appr_index NUMBER;
4536 ln_last_default_approver_id per_people_f.person_id%type;
4537 ln_addntl_approver_id per_people_f.person_id%type;
4538 ln_addntl_approvers NUMBER;
4539 lv_exists VARCHAR2(10);
4540 lv_dummy VARCHAR2(20);
4541 lv_isvalid VARCHAR2(10);
4542 ln_notifiers NUMBER DEFAULT 0;
4543 ln_current_onapproval_index NUMBER ;
4544 ln_person_id per_people_f.person_id%type DEFAULT NULL;
4545 lv_notify VARCHAR2(10);
4546 lv_onapproval VARCHAR2(10);
4547 lv_item_name VARCHAR2(25);
4548 ln_start_index NUMBER;
4549
4550 BEGIN
4551 hr_utility.set_location('Entering: '|| l_proc,5);
4552
4553 if ( funmode = 'RUN' ) then
4554 hr_utility.trace('In (if ( funmode = RUN) ): '|| l_proc);
4555
4556 -- get the total number ofnotifiers
4557 ln_notifiers := wf_engine.GetItemAttrNumber
4558 (itemtype => itemtype,
4559 itemkey => itemkey,
4560 aname => 'NOTIFIERS_NUMBER'
4561 );
4562 IF ln_notifiers > 0 THEN
4563
4564 -- get the current index
4565 ln_current_onapproval_index :=
4566 NVL(wf_engine.GetItemAttrNumber
4567 (itemtype => itemtype
4568 ,itemkey => itemkey
4569 ,aname => 'CURRENT_ONAPPROVAL_INDEX'),0);
4570
4571 ELSE
4572 result := 'COMPLETE:F';
4573 hr_utility.set_location('Leaving: '|| l_proc,15);
4574 return;
4575 END IF;
4576
4577
4578 -- -----------------------------------------------------------------------
4579 -- expose the wf control variables to the custom package
4580 -- -----------------------------------------------------------------------
4581 set_custom_wf_globals
4582 (p_itemtype => itemtype
4583 ,p_itemkey => itemkey);
4584
4585
4586 -- loop through and get next notifier
4587 hr_utility.trace('Going into ( FOR I in (ln_current_onapproval_index + 1)..ln_notifiers): '|| l_proc);
4588
4589 FOR I in (ln_current_onapproval_index + 1)..ln_notifiers
4590 LOOP
4591 lv_item_name := gv_notifier_name||to_char(I);
4592 OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4593 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(itemtype,itemkey,lv_item_name)): '|| l_proc);
4594 FETCH csr_wiav into lv_dummy;
4595 IF csr_wiav%notfound THEN
4596 lv_exists := 'N';
4597 ELSE
4598 lv_exists := 'Y';
4599 ln_person_id:= wf_engine.GetItemAttrNumber
4600 (itemtype => itemtype,
4601 itemkey => itemkey,
4602 aname => lv_item_name
4603 );
4604 lv_notify := wf_engine.GetItemAttrText
4605 (itemtype => itemtype,
4606 itemkey => itemkey,
4607 aname => lv_item_name
4608 );
4609 END IF; -- for csr_wiav%notfound
4610 CLOSE csr_wiav;
4611
4612 IF lv_exists = 'Y' THEN
4613 hr_utility.trace('In (IF lv_exists = Y): '|| l_proc);
4614 lv_onapproval := SUBSTR(lv_notify,3,3);
4615 IF lv_onapproval= 'Y' THEN
4616 hr_utility.trace('In (IF lv_onapproval = Y): '|| l_proc);
4617 wf_engine.SetItemAttrNumber
4618 (itemtype => itemtype,
4619 itemkey => itemkey,
4620 aname => 'CURRENT_ONAPPROVAL_INDEX',
4621 avalue => I
4622 );
4623 l_forward_to_person_id := ln_person_id;
4624 EXIT;
4625 ELSE
4626 hr_utility.trace('In else of (IF lv_exists = Y): '|| l_proc);
4627 l_forward_to_person_id := NULL;
4628 result := 'COMPLETE:F';
4629
4630 END IF;
4631 ELSE
4632 l_forward_to_person_id := NULL;
4633 result := 'COMPLETE:F';
4634
4635 END IF;
4636
4637 END LOOP;
4638 hr_utility.trace('Out of (FOR I in (ln_current_onapproval_index + 1)..ln_notifiers): '|| l_proc);
4639 --
4640 -- set the next forward to
4641 --
4642
4643 IF (l_forward_to_person_id is null) THEN
4644 result := 'COMPLETE:F';
4645 --
4646 else
4647 --
4648 wf_directory.GetUserName
4649 (p_orig_system => 'PER'
4650 ,p_orig_system_id => l_forward_to_person_id
4651 ,p_name => l_forward_to_username
4652 ,p_display_name => l_forward_to_disp_name);
4653
4654 --
4655 hr_approval_wf.create_item_attrib_if_notexist
4656 (p_item_type => itemtype
4657 ,p_item_key => itemkey
4658 ,p_name => 'ONAPPR_FWD_TO_PERSON_ID');
4659 wf_engine.SetItemAttrNumber
4660 (itemtype => itemtype
4661 ,itemkey => itemkey
4662 ,aname => 'ONAPPR_FWD_TO_PERSON_ID'
4663 ,avalue => l_forward_to_person_id);
4664 --
4665 hr_approval_wf.create_item_attrib_if_notexist
4666 (p_item_type => itemtype
4667 ,p_item_key => itemkey
4668 ,p_name => 'ONAPPR_FWD_TO_USERNAME');
4669 wf_engine.SetItemAttrText
4670 (itemtype => itemtype
4671 ,itemkey => itemkey
4672 ,aname => 'ONAPPR_FWD_TO_USERNAME'
4673 ,avalue => l_forward_to_username);
4674 --
4675 hr_approval_wf.create_item_attrib_if_notexist
4676 (p_item_type => itemtype
4677 ,p_item_key => itemkey
4678 ,p_name => 'ONAPPR_FWD_TO_DISPLAY_NAME');
4679 wf_engine.SetItemAttrText
4680 (itemtype => itemtype
4681 ,itemkey => itemkey
4682 ,aname => 'ONAPPR_FWD_TO_DISPLAY_NAME'
4683 ,avalue => l_forward_to_disp_name);
4684 --
4685 -- set forward from to old forward to
4686 --
4687 wf_engine.SetItemAttrNumber
4688 (itemtype => itemtype
4689 ,itemkey => itemkey
4690 ,aname => 'ONAPPR_FWD_FROM_PERSON_ID'
4691 ,avalue => l_current_forward_to_id);
4692
4693 --
4694 result := 'COMPLETE:T';
4695 --
4696 end if;
4697 --
4698 elsif ( funmode = 'CANCEL' ) then
4699 hr_utility.trace('In (elsif ( funmode = CANCEL ) ): '|| l_proc);
4700 --
4701 null;
4702 --
4703 end if;
4704 hr_utility.set_location('Leaving: '|| l_proc,45);
4705
4706 EXCEPTION
4707 WHEN OTHERS THEN
4708 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4709 if csr_wiav%isopen then
4710 close csr_wiav;
4711 end if;
4712 END Get_OnApproval_Notifier;
4713
4714
4715
4716 -- ---------------------------------------------------------------------------
4717 -- |-------------------------< set_first_onsubmit_person>----------------|
4718 -- ---------------------------------------------------------------------------
4719 procedure set_first_onsubmit_person
4720 (itemtype in varchar2
4721 ,itemkey in varchar2
4722 ,actid in number
4723 ,funmode in varchar2
4724 ,result out nocopy varchar2) is
4725 -- -------------------------------------------------------------------------
4726 -- local variables
4727 -- -------------------------------------------------------------------------
4728
4729
4730 ln_notifiers NUMBER;
4731 l_dummy VARCHAR2(10);
4732 lv_exist VARCHAR2(10);
4733 ln_person_id NUMBER;
4734 lv_notify VARCHAR2(10);
4735 lv_onsubmit VARCHAR2(10);
4736 lv_item_name VARCHAR2(25);
4737 l_proc constant varchar2(100) := g_package || ' set_first_onsubmit_person';
4738 --
4739 begin
4740 -- check the workflow funmode value
4741 hr_utility.set_location('Entering: '|| l_proc,5);
4742 if funmode = 'RUN' then
4743 hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4744 -- workflow is RUNing this procedure
4745 --
4746 --
4747 -- get the total number of notifiers
4748 ln_notifiers :=
4749 wf_engine.GetItemAttrNumber(itemtype => itemtype,
4750 itemkey => itemkey,
4751 aname => 'NOTIFIERS_NUMBER');
4752 -- loop through the notifiers to get the first on submit notifier
4753 hr_utility.trace('Going into( FOR I IN 1..ln_notifiers): '|| l_proc);
4754 FOR I IN 1..ln_notifiers
4755 LOOP
4756 lv_item_name := gv_notifier_name||to_char(I);
4757 OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4758 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(itemtype,itemkey,lv_item_name);): '|| l_proc);
4759 FETCH csr_wiav into l_dummy;
4760 IF csr_wiav%notfound THEN
4761 lv_exist := 'N';
4762 ELSE
4763 lv_exist := 'Y';
4764 ln_person_id:= wf_engine.GetItemAttrNumber
4765 (itemtype => itemtype,
4766 itemkey => itemkey,
4767 aname => lv_item_name
4768 );
4769 lv_notify := wf_engine.GetItemAttrText
4770 (itemtype => itemtype,
4771 itemkey => itemkey,
4772 aname => lv_item_name
4773 );
4774 END IF; -- for csr_wiav%notfound
4775 CLOSE csr_wiav;
4776 IF lv_exist = 'Y' THEN
4777 hr_utility.trace('In ( IF lv_exist = Y): '|| l_proc);
4778 lv_onsubmit := SUBSTR(lv_notify,1,1);
4779 IF lv_onsubmit= 'Y' THEN
4780 hr_utility.trace('In (IF lv_onsubmit= Y): '|| l_proc);
4781 -- set the person id and start index
4782 wf_engine.SetItemAttrNumber
4783 (itemtype => itemtype,
4784 itemkey => itemkey,
4785 aname => 'ONSUBMIT_START_INDEX',
4786 avalue => I
4787 );
4788
4789 wf_engine.SetItemAttrNumber
4790 (itemtype => itemtype,
4791 itemkey => itemkey,
4792 aname => 'ONSUBMIT_START_PERSON_ID',
4793 avalue => ln_person_id
4794 );
4795 result := 'COMPLETE:SUCCESS';
4796 EXIT;
4797 END IF;
4798 END IF;
4799 END LOOP;
4800 hr_utility.trace('Out of ( FOR I IN 1..ln_notifiers): '|| l_proc);
4801 elsif funmode = 'CANCEL' then
4802 -- workflow is calling in cancel mode (performing a loop reset) so ignore
4803 hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
4804 null;
4805 end if;
4806 hr_utility.set_location('Leaving: '|| l_proc,40);
4807
4808
4809 EXCEPTION
4810 WHEN OTHERS THEN
4811 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4812 if csr_wiav%isopen then
4813 close csr_wiav;
4814 end if;
4815 end set_first_onsubmit_person;
4816
4817
4818 -- ---------------------------------------------------------------------------
4819 -- |-------------------------< set_first_onapproval_person>----------------|
4820 -- ---------------------------------------------------------------------------
4821 procedure set_first_onapproval_person
4822 (itemtype in varchar2
4823 ,itemkey in varchar2
4824 ,actid in number
4825 ,funmode in varchar2
4826 ,result out nocopy varchar2) is
4827 -- -------------------------------------------------------------------------
4828 -- local variables
4829 -- -------------------------------------------------------------------------
4830
4831
4832 ln_notifiers NUMBER;
4833 l_dummy VARCHAR2(10);
4834 lv_exist VARCHAR2(10);
4835 ln_person_id NUMBER;
4836 lv_notify VARCHAR2(10);
4837 lv_onsubmit VARCHAR2(10);
4838 lv_item_name VARCHAR2(25);
4839 l_proc constant varchar2(100) := g_package || ' set_first_onapproval_person';
4840 --
4841 begin
4842 hr_utility.set_location('Entering: '|| l_proc,5);
4843 -- check the workflow funmode value
4844 if funmode = 'RUN' then
4845 hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4846 -- workflow is RUNing this procedure
4847 --
4848 --
4849 -- get the total number of notifiers
4850 ln_notifiers :=
4851 wf_engine.GetItemAttrNumber(itemtype => itemtype,
4852 itemkey => itemkey,
4853 aname => 'NOTIFIERS_NUMBER');
4854 -- loop through the notifiers to get the first on submit notifier
4855 hr_utility.trace('Going into (FOR I IN 1..ln_notifiers): '|| l_proc);
4856 FOR I IN 1..ln_notifiers
4857 LOOP
4858 lv_item_name := gv_notifier_name||to_char(I);
4859 OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4860 hr_utility.trace('Going into Fetch after ( FETCH csr_wiav into l_dummy;): '|| l_proc);
4861 FETCH csr_wiav into l_dummy;
4862 IF csr_wiav%notfound THEN
4863 lv_exist := 'N';
4864 ELSE
4865 lv_exist := 'Y';
4866 ln_person_id:= wf_engine.GetItemAttrNumber
4867 (itemtype => itemtype,
4868 itemkey => itemkey,
4869 aname => lv_item_name
4870 );
4871 lv_notify := wf_engine.GetItemAttrText
4872 (itemtype => itemtype,
4873 itemkey => itemkey,
4874 aname => lv_item_name
4875 );
4876 END IF; -- for csr_wiav%notfound
4877 CLOSE csr_wiav;
4878
4879 IF lv_exist = 'Y' THEN
4880 hr_utility.trace('In(IF lv_exist = Y): '|| l_proc);
4881 lv_onsubmit :=SUBSTR(lv_notify,3,3);
4882 IF lv_onsubmit= 'Y' THEN
4883 hr_utility.trace('In(IF lv_onsubmit = Y): '|| l_proc);
4884 -- set the person id and start index
4885 wf_engine.SetItemAttrNumber
4886 (itemtype => itemtype,
4887 itemkey => itemkey,
4888 aname => 'ONAPPROVAL_START_INDEX',
4889 avalue => I
4890 );
4891
4892 wf_engine.SetItemAttrNumber
4893 (itemtype => itemtype,
4894 itemkey => itemkey,
4895 aname => 'ONAPPROVAL_START_PERSON_ID',
4896 avalue => ln_person_id
4897 );
4898 result := 'COMPLETE:SUCCESS';
4899 EXIT;
4900 END IF;
4901 END IF;
4902 END LOOP;
4903 hr_utility.trace('Out of (FOR I IN 1..ln_notifiers): '|| l_proc);
4904 elsif funmode = 'CANCEL' then
4905 -- workflow is calling in cancel mode (performing a loop reset) so ignore
4906 hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
4907 null;
4908 end if;
4909 hr_utility.set_location('Leaving: '|| l_proc,45);
4910
4911 EXCEPTION
4912 WHEN OTHERS THEN
4913 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4914 if csr_wiav%isopen then
4915 close csr_wiav;
4916 end if;
4917 END set_first_onapproval_person;
4918
4919
4920 -- ---------------------------------------------------------------------------
4921 -- public Procedure declarations
4922 -- ---------------------------------------------------------------------------
4923 --
4924 -- ----------------------------------------------------------------------------
4925 -- |------------------------------< initialize_item_attributes>-------------------|
4926 -- ----------------------------------------------------------------------------
4927 --
4928 -- This procedure initializes item attributes for the onsubmit and onapproval process.
4929 -- The procedure sets the start index for onsubmit notifier and onapproval notifier.
4930 --
4931
4932
4933 procedure initialize_item_attributes
4934 (itemtype in varchar2
4935 ,itemkey in varchar2
4936 ,actid in number
4937 ,funmode in varchar2
4938 ,result out nocopy varchar2)
4939 AS
4940 -- Local Variables
4941 l_dummy VARCHAR2(100);
4942 l_proc constant varchar2(100) := g_package || ' initialize_item_attributes';
4943 BEGIN
4944 hr_utility.set_location('Entering: '|| l_proc,5);
4945 -- check the workflow funmode value
4946
4947 if funmode = 'RUN' then
4948 hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4949 -- workflow is RUNing this procedure
4950 --
4951 --
4952 -- Test that all new attributes exist and if they don't create them
4953
4954 -- create new wf_item_attribute_value to hold the additional approvers number
4955 hr_approval_wf.create_item_attrib_if_notexist
4956 (p_item_type => itemtype
4957 ,p_item_key => itemkey
4958 ,p_name => 'ADDITIONAL_APPROVERS_NUMBER');
4959
4960
4961
4962 -- attribute to hold the approval levels for confguration.
4963 OPEN csr_wiav(itemtype,itemkey,'APPROVAL_LEVEL');
4964 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(itemtype,itemkey,APPROVAL_LEVEL)): '|| l_proc);
4965 FETCH csr_wiav into l_dummy;
4966 IF csr_wiav%notfound THEN
4967 -- create new wf_item_attribute_value to hold
4968 hr_approval_wf.create_item_attrib_if_notexist
4969 (p_item_type => itemtype
4970 ,p_item_key => itemkey
4971 ,p_name => 'APPROVAL_LEVEL');
4972
4973 END IF;
4974 CLOSE csr_wiav;
4975
4976
4977 -- attribute to hold the current default approver index .
4978 OPEN csr_wiav(itemtype,itemkey,'CURRENT_DEF_APPR_INDEX');
4979 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(itemtype,itemkey,CURRENT_DEF_APPR_INDEX)): '|| l_proc);
4980 FETCH csr_wiav into l_dummy;
4981 IF csr_wiav%notfound THEN
4982 -- create new wf_item_attribute_value to hold
4983 hr_approval_wf.create_item_attrib_if_notexist
4984 (p_item_type => itemtype
4985 ,p_item_key => itemkey
4986 ,p_name => 'CURRENT_DEF_APPR_INDEX');
4987
4988 wf_engine.SetItemAttrNumber
4989 (itemtype => itemtype,
4990 itemkey => itemkey,
4991 aname => 'CURRENT_DEF_APPR_INDEX',
4992 avalue => 0);
4993 ELSE
4994 wf_engine.SetItemAttrNumber
4995 (itemtype => itemtype,
4996 itemkey => itemkey,
4997 aname => 'CURRENT_DEF_APPR_INDEX',
4998 avalue => 0);
4999 END IF;
5000 CLOSE csr_wiav;
5001
5002
5003
5004
5005 --
5006 -- These attributes are for the new notification process ,
5007 -- onsubmit and onapproval
5008
5009 -- attributes for the onsubmit notification process
5010 -- CURRENT_ONSUBMIT_INDEX
5011 hr_approval_wf.create_item_attrib_if_notexist
5012 (p_item_type => itemtype
5013 ,p_item_key => itemkey
5014 ,p_name => 'CURRENT_ONSUBMIT_INDEX');
5015
5016 -- ONSUBMIT_START_INDEX
5017 hr_approval_wf.create_item_attrib_if_notexist
5018 (p_item_type => itemtype
5019 ,p_item_key => itemkey
5020 ,p_name => 'ONSUBMIT_START_INDEX');
5021
5022 -- ONSUBMIT_START_PERSON_ID
5023 hr_approval_wf.create_item_attrib_if_notexist
5024 (p_item_type => itemtype
5025 ,p_item_key => itemkey
5026 ,p_name => 'ONSUBMIT_START_PERSON_ID');
5027
5028 -- ONSUBMIT_FORWARD_FROM_USERNAME
5029 hr_approval_wf.create_item_attrib_if_notexist
5030 (p_item_type => itemtype
5031 ,p_item_key => itemkey
5032 ,p_name => 'ONSUB_FWD_FROM_USERNAME');
5033 --
5034 -- ONSUBMIT_FORWARD_FROM_PERSON_ID
5035 hr_approval_wf.create_item_attrib_if_notexist
5036 (p_item_type => itemtype
5037 ,p_item_key => itemkey
5038 ,p_name => 'ONSUB_FWD_FROM_PERSON_ID');
5039 --
5040 -- ONSUBMIT_FORWARD_FROM_DISPLAY_NAME
5041 hr_approval_wf.create_item_attrib_if_notexist
5042 (p_item_type => itemtype
5043 ,p_item_key => itemkey
5044 ,p_name => 'ONSUB_FWD_FROM_DISPLAY_NAME');
5045 --
5046 -- ONSUBMIT_FORWARD_TO_USERNAME
5047 hr_approval_wf.create_item_attrib_if_notexist
5048 (p_item_type => itemtype
5049 ,p_item_key => itemkey
5050 ,p_name => 'ONSUB_FWD_TO_USERNAME');
5051 --
5052 -- FORWARD_TO_PERSON_ID
5053 hr_approval_wf.create_item_attrib_if_notexist
5054 (p_item_type => itemtype
5055 ,p_item_key => itemkey
5056 ,p_name => 'ONSUB_FWD_TO_PERSON_ID');
5057 --
5058 -- FORWARD_TO_DISPLAY_NAME
5059 hr_approval_wf.create_item_attrib_if_notexist
5060 (p_item_type => itemtype
5061 ,p_item_key => itemkey
5062 ,p_name => 'ONSUB_FWD_TO_DISPLAY_NAME');
5063 --
5064 -- for onapproval notification process
5065
5066 -- CURRENT_ONAPPROVAL_INDEX
5067 hr_approval_wf.create_item_attrib_if_notexist
5068 (p_item_type => itemtype
5069 ,p_item_key => itemkey
5070 ,p_name => 'CURRENT_ONAPPROVAL_INDEX');
5071
5072 -- ONAPPROVAL_START_INDEX
5073 hr_approval_wf.create_item_attrib_if_notexist
5074 (p_item_type => itemtype
5075 ,p_item_key => itemkey
5076 ,p_name => 'ONAPPROVAL_START_INDEX');
5077 -- ONAPPROVAL_START_PERSON_ID
5078 hr_approval_wf.create_item_attrib_if_notexist
5079 (p_item_type => itemtype
5080 ,p_item_key => itemkey
5081 ,p_name => 'ONAPPROVAL_START_PERSON_ID');
5082
5083 -- ONAPPROVAL_FORWARD_FROM_USERNAME
5084 hr_approval_wf.create_item_attrib_if_notexist
5085 (p_item_type => itemtype
5086 ,p_item_key => itemkey
5087 ,p_name => 'ONAPPR_FWD_FROM_USERNAME');
5088 --
5089 -- ONAPPROVAL_FORWARD_FROM_PERSON_ID
5090 hr_approval_wf.create_item_attrib_if_notexist
5091 (p_item_type => itemtype
5092 ,p_item_key => itemkey
5093 ,p_name => 'ONAPPR_FWD_FROM_PERSON_ID');
5094 --
5095 -- ONAPPROVAL_FORWARD_FROM_DISPLAY_NAME
5096 hr_approval_wf.create_item_attrib_if_notexist
5097 (p_item_type => itemtype
5098 ,p_item_key => itemkey
5099 ,p_name => 'ONAPPR_FWD_FROM_DISPLAY_NAME');
5100 --
5101 -- ONAPPROVAL_FORWARD_TO_USERNAME
5102 hr_approval_wf.create_item_attrib_if_notexist
5103 (p_item_type => itemtype
5104 ,p_item_key => itemkey
5105 ,p_name => 'ONAPPR_FWD_TO_USERNAME');
5106 --
5107 -- ONAPPROVAL_FORWARD_TO_PERSON_ID
5108 hr_approval_wf.create_item_attrib_if_notexist
5109 (p_item_type => itemtype
5110 ,p_item_key => itemkey
5111 ,p_name => 'ONAPPR_FWD_TO_PERSON_ID');
5112 --
5113 -- ONAPPROVAL_FORWARD_TO_DISPLAY_NAME
5114 hr_approval_wf.create_item_attrib_if_notexist
5115 (p_item_type => itemtype
5116 ,p_item_key => itemkey
5117 ,p_name => 'ONAPPR_FWD_TO_DISPLAY_NAME');
5118 --
5119
5120 --
5121 -- -----------------------------------------------------------------------
5122 -- set workflow activity to the SUCCESS state to end workflow
5123 -- -----------------------------------------------------------------------
5124 --result := 'COMPLETE:SUCCESS';
5125 --
5126 elsif funmode = 'CANCEL' then
5127 -- workflow is calling in cancel mode (performing a loop reset) so ignore
5128 hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
5129 null;
5130 end if;
5131 hr_utility.set_location('Leaving: '|| l_proc,25);
5132
5133
5134 EXCEPTION
5135 WHEN OTHERS THEN
5136 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5137 if csr_wiav%isopen then
5138 close csr_wiav;
5139 end if;
5140 END initialize_item_attributes;
5141
5142
5143
5144
5145
5146 procedure set_ame_attributes(itemtype in varchar2
5147 ,itemkey in varchar2
5148 ,actid in number)
5149 AS
5150 -- local variables
5151 l_proc constant varchar2(100) := g_package || ' set_ame_attributes';
5152 begin
5153 hr_utility.set_location('Entering: '|| l_proc,5);
5154 wf_engine.SetItemAttrNumber(itemtype => itemtype ,
5155 itemkey => itemkey,
5156 aname => 'HR_AME_APP_ID_ATTR',
5157 avalue=>800);
5158
5159
5160 wf_engine.SetItemAttrText(itemtype => itemtype ,
5161 itemkey => itemkey,
5162 aname => 'HR_AME_TRAN_TYPE_ATTR',
5163 avalue=> 'SSHRMS');
5164
5165 hr_utility.set_location('Leaving: '|| l_proc,10);
5166 end set_ame_attributes;
5167
5168
5169 -- ---------------------------------------------------------------------------
5170 -- public Procedure declarations
5171 -- ---------------------------------------------------------------------------
5172 --
5173 -- ----------------------------------------------------------------------------
5174 -- |------------------------------< Notify>-------------------|
5175 -- ----------------------------------------------------------------------------
5176 --
5177 -- This procedure is a public wrapper to engine notification call
5178 -- This reads the activity attributes and sends notification to the ROLE defined
5179 -- in the activity attribute PERFORMER with the message conigured in the activity
5180 -- attribute MESSAGE. And also can send to group if configured through the activity
5181 -- attribute EXPANDROLES.
5182 --
5183 procedure Notify(itemtype in varchar2,
5184 itemkey in varchar2,
5185 actid in number,
5186 funcmode in varchar2,
5187 resultout in out nocopy varchar2)
5188 is
5189 msg varchar2(30);
5190 msgtype varchar2(8);
5191 prole wf_users.name%type;
5192 expand_role varchar2(1);
5193
5194 colon pls_integer;
5195 avalue varchar2(240);
5196 -- local variable
5197 l_proc constant varchar2(100) := g_package || ' Notify';
5198
5199 begin
5200 hr_utility.set_location('Entering: '|| l_proc,5);
5201
5202
5203 -- Do nothing in cancel or timeout mode
5204 if (funcmode <> wf_engine.eng_run) then
5205 resultout := wf_engine.eng_null;
5206 hr_utility.set_location('Leaving: '|| l_proc,10);
5207 return;
5208 end if;
5209
5210
5211 --PERFORMER
5212 prole := wf_engine.GetActivityAttrText(
5213 itemtype => itemtype,
5214 itemkey => itemkey,
5215 actid => actid,
5216 aname => 'PERFORMER');
5217
5218
5219 if prole is null then
5220 Wf_Core.Token('TYPE', itemtype);
5221 Wf_Core.Token('ACTID', to_char(actid));
5222 Wf_Core.Raise('WFENG_NOTIFICATION_PERFORMER');
5223 end if;
5224
5225 -- message name and expand roles will be null. Get these from attributes
5226 avalue := upper(Wf_Engine.GetActivityAttrText(itemtype, itemkey,
5227 actid, 'MESSAGE'));
5228
5229 -- let notification_send catch a missing message name.
5230 expand_role := nvl(Wf_Engine.GetActivityAttrText(itemtype, itemkey,
5231 actid, 'EXPANDROLES'),'N');
5232
5233 -- parse out the message type if given
5234 colon := instr(avalue, ':');
5235 if colon = 0 then
5236 msgtype := itemtype;
5237 msg := avalue;
5238 else
5239 msgtype := substr(avalue, 1, colon - 1);
5240 msg := substr(avalue, colon + 1);
5241 end if;
5242
5243 -- Actually send the notification
5244 Wf_Engine_Util.Notification_Send(itemtype, itemkey, actid,
5245 msg, msgtype, prole, expand_role,
5246 resultout);
5247
5248
5249 --resultout is determined by Notification_Send as either
5250 --NULL if notification is FYI
5251 --NOTIFIED:notid:role if notification requires responce
5252
5253 --resultout := null;
5254 hr_utility.set_location('Leaving: '|| l_proc,15);
5255 exception
5256 when others then
5257 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5258 Wf_Core.Context('Wf_Standard', 'Notify', itemtype,
5259 itemkey, to_char(actid), funcmode);
5260 raise;
5261 end Notify;
5262
5263
5264
5265 /*-----------------------------------------------------------------------
5266
5267 || PROCEDURE : get_ame_approvers_list
5268 ||
5269 || This is a wrapper procedure to get_all_ame_approvers to return
5270 || the list of default approvers to a java oracle.sql.ARRAY object
5271 ||
5272 ||
5273 ||
5274 ||-----------------------------------------------------------------------*/
5275
5276 PROCEDURE get_ame_approvers_list(
5277 p_item_type IN wf_items.item_type%TYPE,
5278 p_item_key IN wf_items.item_key%TYPE,
5279 p_default_approvers_list OUT NOCOPY hr_dynamic_approver_list_ss)
5280
5281 AS
5282
5283 --local variables
5284 l_approver_name hr_util_misc_web.g_varchar2_tab_type;
5285 l_approver_flag hr_util_misc_web.g_varchar2_tab_type;
5286 l_default_approvers_list hr_dynamic_approver_list_ss := hr_dynamic_approver_list_ss();
5287 l_default_approver hr_dynamic_approver_ss;
5288 lv_number varchar2(10);
5289 ln_creator_person_id number;
5290 l_no_approvers_list hr_dynamic_approver_list_ss := hr_dynamic_approver_list_ss();
5291 ln_transaction_id hr_api_transactions.transaction_id%type;
5292 l_proc constant varchar2(100) := g_package || ' get_ame_approvers_list';
5293 BEGIN
5294 hr_utility.set_location('Entering: '|| l_proc,5);
5295
5296 -- remove all rows from person details table
5297
5298 grt_approver_details_table.DELETE;
5299
5300 -- set the gv_mode as this is needed for pl/sql compatibility
5301 gv_mode:='RE-ENTER';
5302
5303 -- repopulate the table
5304 hr_utility.trace('calling get_all_ame_approvers ');
5305
5306 get_all_ame_approvers(p_approver_name =>l_approver_name,
5307 p_approver_flag =>l_approver_flag,
5308 p_item_type =>p_item_type,
5309 p_item_key =>p_item_key);
5310
5311
5312
5313 -- copy parameters into l_default_approvers_list
5314 lv_number := grt_approver_details_table.count;
5315
5316 hr_utility.trace('approver count retuned from get_all_ame_approvers :'||nvl(lv_number,0));
5317 hr_utility.trace('Going into ( FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
5318 FOR I IN 1..grt_approver_details_table.count
5319 LOOP
5320 hr_utility.trace('building approvers out nocopy list using hr_dynamic_approver_ss');
5321 hr_utility.trace(' Adding approver :'||grt_approver_details_table(I).person_id||' to the list');
5322 l_default_approver := hr_dynamic_approver_ss(
5323 grt_approver_details_table(I).full_name,
5324 grt_approver_details_table(I).person_id,
5325 grt_approver_details_table(I).job_title,
5326 grt_approver_details_table(I).default_approver,
5327 grt_approver_details_table(I).error_exists);
5328
5329 -- add new row to list
5330 l_default_approvers_list.EXTEND;
5331
5332 -- add to list
5333 l_default_approvers_list(I) := l_default_approver;
5334
5335 END LOOP;
5336 hr_utility.trace('Out of ( FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
5337 -- set out parameter
5338 hr_utility.trace('setting the out nocopy parameter p_default_approvers_list');
5339 p_default_approvers_list := l_default_approvers_list;
5340
5341 if(grt_approver_details_table.count=1) then
5342 hr_utility.trace('In( if(grt_approver_details_table.count=1) ): '|| l_proc);
5343 hr_utility.trace('only approver in the list');
5344 /*
5345 -- Work around for the bug#2345264
5346 -- Remove this check once the AME fixes the issue with ALLOW_REQUESTOR_APPROVAL
5347 -- attribute.
5348 -- ame_api.getAllApprovers returns the creator too as the approver
5349 -- when ALLOW_REQUESTOR_APPROVAL is true.
5350 -- Needed this for fixing bug# 2337022
5351 */
5352 -- get the creator person id
5353 hr_utility.trace('getting the creator person id from WF attr');
5354 /*ln_creator_person_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
5355 itemkey => p_item_key,
5356 aname => 'CREATOR_PERSON_ID');
5357 */
5358 ln_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
5359 itemkey => p_item_key,
5360 aname => 'TRANSACTION_ID');
5361
5362 ln_creator_person_id := hr_workflow_ss.getApprStartingPointPersonId(ln_transaction_id);
5363
5364 -- check if the approver id matches the creator
5365 if( ln_creator_person_id=grt_approver_details_table(1).person_id) then
5366 hr_utility.trace('creator person id matches the approverid resetting out nocopy param to null ');
5367 p_default_approvers_list :=l_no_approvers_list;
5368 end if;
5369 end if;
5370
5371 hr_utility.set_location('Leaving: '|| l_proc,40);
5372
5373 EXCEPTION
5374 WHEN OTHERS THEN
5375 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5376 hr_utility.trace(' exception in '||gv_package||'.get_ame_approvers_list : ' || sqlerrm);
5377 Wf_Core.Context(gv_package, 'get_ame_approvers_list', p_item_type, p_item_key);
5378 raise;
5379
5380 END get_ame_approvers_list;
5381
5382 /*-----------------------------------------------------------------------
5383
5384 || PROCEDURE : set_ame_approvers_list
5385 ||
5386 || This is a wrapper procedure to get_all_ame_approvers to return
5387 || the list of default approvers to a java oracle.sql.ARRAY object
5388 ||
5389 ||
5390 ||
5391 ||-----------------------------------------------------------------------*/
5392
5393 PROCEDURE set_ame_approvers_list(
5394 p_item_type IN wf_items.item_type%TYPE,
5395 p_item_key IN wf_items.item_key%TYPE,
5396 p_default_approvers_list IN hr_dynamic_approver_list_ss)
5397
5398 AS
5399
5400 --local variables
5401 l_approver_name hr_util_misc_web.g_varchar2_tab_type;
5402 l_approver_flag hr_util_misc_web.g_varchar2_tab_type;
5403 l_default_approvers_list hr_dynamic_approver_list_ss := hr_dynamic_approver_list_ss();
5404 l_default_approver hr_dynamic_approver_ss;
5405 l_approvers_list hr_dynamic_approver_list_ss;
5406 lv_number varchar2(10);
5407 l_proc constant varchar2(100) := g_package || ' set_ame_approvers_list';
5408 -- Variables required for AME API
5409 c_application_id integer;
5410 c_transaction_id varchar2(25);
5411 c_transaction_type varchar2(25);
5412 c_next_approver_rec ame_util.approverRecord;
5413 c_additional_approver_order ame_util.orderRecord;
5414 c_additional_approver_rec ame_util.approversTable;
5415
5416
5417 BEGIN
5418 hr_utility.set_location('Entering: '|| l_proc,5);
5419 -- get AME related WF attribute values
5420 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
5421 itemkey => p_item_key,
5422 aname => 'HR_AME_APP_ID_ATTR');
5423
5424 c_application_id := nvl(c_application_id,800);
5425
5426
5427 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
5428 itemkey => p_item_key,
5429 aname => 'TRANSACTION_ID');
5430
5431
5432
5433 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
5434 itemkey => p_item_key,
5435 aname => 'HR_AME_TRAN_TYPE_ATTR');
5436
5437
5438
5439 -- call AME to update additional approvers,
5440
5441 -- clear all the insertions into AME . Need to perform this step ONLY after we get the person id .
5442 -- other wise it would clear the insertions made in the previous pass.
5443 ame_api.clearInsertions(applicationIdIn =>c_application_id ,
5444 transactionIdIn =>c_transaction_id,
5445 transactionTypeIn=>c_transaction_type);
5446
5447
5448 if(c_transaction_type is not null) then
5449 hr_utility.trace('In( if(c_transaction_type is not null)): '|| l_proc);
5450 -- update AME list
5451 hr_utility.trace('Going into ( for i in 1..p_default_approvers_list.count): '|| l_proc);
5452 for i in 1..p_default_approvers_list.count loop
5453 -- check for the default approver flag
5454 if(p_default_approvers_list(i).default_approver='N') then
5455 -- details for the record insertion into AME
5456 c_next_approver_rec.person_id:=p_default_approvers_list(i).person_id;
5457 c_next_approver_rec.api_insertion:= ame_util.apiInsertion;
5458 c_next_approver_rec.authority:=ame_util.authorityApprover;
5459
5460 -- details for the insertion order for the AME record.
5461 c_additional_approver_order.order_type:=ame_util.absoluteOrder;
5462 c_additional_approver_order.parameter:=I;
5463 -- commented the code for UTF8 fixes
5464 --c_additional_approver_order.description:=p_default_approvers_list(i).full_name ||':'||p_default_approvers_list(i).job_title;
5465 c_additional_approver_order.description:= null;
5466
5467 ame_api.insertApprover(applicationIdIn =>c_application_id,
5468 transactionIdIn =>c_transaction_id,
5469 approverIn =>c_next_approver_rec,
5470 positionIn =>I,
5471 orderIn =>c_additional_approver_order,
5472 transactionTypeIn=>c_transaction_type );
5473 end if;
5474 end loop;
5475 hr_utility.trace('Out of ( for i in 1..p_default_approvers_list.count): '|| l_proc);
5476 end if; -- end updating AME list
5477 hr_utility.set_location('Leaving: '|| l_proc,25);
5478
5479 EXCEPTION
5480 WHEN OTHERS THEN
5481
5482 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5483 raise;
5484
5485 END set_ame_approvers_list;
5486
5487 /*-----------------------------------------------------------------------
5488
5489 || PROCEDURE : get_additional_notifiers_list
5490 ||
5491 || This is a wrapper procedure to get_default_approvers to return
5492 || the list of default approvers to a java oracle.sql.ARRAY object
5493 ||
5494 ||
5495 ||
5496 ||-----------------------------------------------------------------------*/
5497
5498 PROCEDURE get_additional_notifiers_list(
5499 p_item_type IN wf_items.item_type%TYPE,
5500 p_item_key IN wf_items.item_key%TYPE,
5501 p_additional_notifiers_list OUT NOCOPY hr_dynamic_approver_list_ss)
5502
5503 AS
5504
5505 --local variables
5506 l_approver_name hr_util_misc_web.g_varchar2_tab_type;
5507 l_approver_flag hr_util_misc_web.g_varchar2_tab_type;
5508 l_additional_notifiers_list hr_dynamic_approver_list_ss := hr_dynamic_approver_list_ss();
5509 l_notifier_rec hr_dynamic_approver_ss;
5510 lv_number varchar2(10);
5511 lv_exist VARCHAR2(10) DEFAULT 'N';
5512 l_dummy VARCHAR2(100) ;
5513 lv_item_name VARCHAR2(100);
5514 lrt_notifier_details_table hr_dynamic_approval_web.notifier_rec_table;
5515 ln_person_id per_people_f.person_id%TYPE;
5516 lv_full_name per_people_f.person_id%TYPE;
5517 lv_job_title VARCHAR2(1000);
5518 lv_on_submit VARCHAR2(10);
5519 lv_on_approval VARCHAR2(10);
5520 lv_notify VARCHAR2(10);
5521 ln_assignment_id NUMBER ;
5522 lrt_assignment_details hr_misc_web.grt_assignment_details;
5523 ln_loop_index NUMBER;
5524 ln_job_id NUMBER;
5525 l_curr_org_name VARCHAR2(100);
5526 l_curr_loc_name VARCHAR2(100);
5527 ln_error_count NUMBER DEFAULT 1;
5528 l_proc constant varchar2(100) := g_package || ' get_additional_notifiers_list';
5529
5530 BEGIN
5531 hr_utility.set_location('Entering: '|| l_proc,5);
5532 -- remove all rows from notifiers details table
5533 grt_notifier_details_table.DELETE;
5534 -- get the number of notifiers in the system
5535 OPEN csr_wiav(p_item_type,p_item_key,'NOTIFIERS_NUMBER');
5536 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,NOTIFIERS_NUMBER) ): '|| l_proc);
5537 FETCH csr_wiav into l_dummy;
5538 IF csr_wiav%notfound THEN
5539 gn_notifiers := 0;
5540 ELSE
5541 gn_notifiers :=
5542 wf_engine.GetItemAttrNumber(itemtype => p_item_type,
5543 itemkey => p_item_key,
5544 aname => 'NOTIFIERS_NUMBER');
5545 END IF;
5546 CLOSE csr_wiav;
5547
5548 IF gn_notifiers > 0 THEN
5549 hr_utility.trace('In( IF gn_notifiers > 0): '|| l_proc);
5550 --loop througthe counter and get all the notifiers
5551
5552 hr_utility.trace('Going into (FOR I IN 1..gn_notifiers): '|| l_proc);
5553 FOR I IN 1..gn_notifiers
5554 LOOP
5555 lv_job_title := NULL;
5556 lv_item_name := gv_notifier_name||to_char(I);
5557 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
5558 hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
5559 FETCH csr_wiav into l_dummy;
5560 IF csr_wiav%notfound THEN
5561 lv_exist := 'N';
5562 ELSE
5563 lv_exist := 'Y';
5564 ln_person_id:= wf_engine.GetItemAttrNumber
5565 (itemtype => p_item_type,
5566 itemkey => p_item_key,
5567 aname => lv_item_name
5568 );
5569 lv_notify := wf_engine.GetItemAttrText
5570 (itemtype => p_item_type,
5571 itemkey => p_item_key,
5572 aname => lv_item_name
5573 );
5574 END IF; -- for csr_wiav%notfound
5575 CLOSE csr_wiav;
5576
5577 IF lv_exist = 'Y' THEN
5578 -- get the person and assignment details for this person_id
5579 -- get the assignment id
5580 lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
5581 ln_assignment_id := lrt_assignment_details.assignment_id;
5582 -- get name and job title for this person id
5583 lrt_assignment_details := hr_misc_web.get_assignment_details(
5584 p_assignment_id => ln_assignment_id,
5585 p_effective_date =>trunc(sysdate)
5586 );
5587
5588 -- populate the notifiers rec table
5589 lrt_notifier_details_table(I).person_id := ln_person_id;
5590 lrt_notifier_details_table(I).full_name := lrt_assignment_details.person_full_name;
5591
5592 lv_job_title := hr_dynamic_approval_web.get_job_details
5593 (p_person_id =>ln_person_id,
5594 p_assignment_id=>ln_assignment_id,
5595 p_effective_date=>trunc(sysdate)
5596 );
5597 lrt_notifier_details_table(I).job_title := lv_job_title;
5598
5599 -- parse the lv_notify for these values
5600 lrt_notifier_details_table(I).on_submit := SUBSTR(lv_notify,1,1);
5601 lrt_notifier_details_table(I).on_approval := SUBSTR(lv_notify,3,3);
5602
5603 l_notifier_rec := hr_dynamic_approver_ss(
5604 lrt_notifier_details_table(I).full_name,
5605 lrt_notifier_details_table(I).person_id,
5606 lrt_notifier_details_table(I).job_title ,
5607 lv_notify,
5608 'FALSE');
5609
5610 -- add new row to list
5611 l_additional_notifiers_list.EXTEND;
5612
5613 -- add to list
5614 l_additional_notifiers_list(I) := l_notifier_rec;
5615
5616
5617
5618 END IF; -- for lv_exist = 'Y'
5619
5620 END LOOP;
5621 hr_utility.trace('Out of (FOR I IN 1..gn_notifiers): '|| l_proc);
5622 END IF; -- for gn_notifiers > 0
5623
5624 p_additional_notifiers_list:=l_additional_notifiers_list;
5625 hr_utility.set_location('Leaving: '|| l_proc,35);
5626 EXCEPTION
5627 WHEN OTHERS THEN
5628 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5629 if csr_wiav%isopen then
5630 close csr_wiav;
5631 end if;
5632 raise;
5633
5634 END get_additional_notifiers_list;
5635
5636
5637
5638
5639
5640 end hr_dynamic_approval_web;