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