[Home] [Help]
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;
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:
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
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 (
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);
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:
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:
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
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;
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);
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 ||
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 ||
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
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);
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
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;
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:
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 ) )';
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
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:
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);
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,
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
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'
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 ||
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 ||
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);
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
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:
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));
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;
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,
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
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
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
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
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;
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;
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
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);
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);
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
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);
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;
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';
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
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');
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
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));
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';
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
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';
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);
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);
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';
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
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:
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
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
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
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
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
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
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
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
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
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));
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 ;
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';
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);
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;
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;
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
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
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
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;
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 ;
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';
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
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
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
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;
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,
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
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
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;
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
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
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;
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;
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
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;
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
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:
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 ,
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;
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:
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;
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;
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
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);
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;
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;
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(
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:
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);
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:
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;
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(
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:
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);
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
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;
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);
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
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
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
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
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
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
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
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;
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
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);
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:
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
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 ,
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:
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);
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
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);
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:
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;
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;
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
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
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
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;
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
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
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';
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:
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
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;
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;
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:
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;
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:
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:
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;
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',
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
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;
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
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);
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
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
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:
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;
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:
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:
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);
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: --
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
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;
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
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
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);
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
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;
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:
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:
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;
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;
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:
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
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
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
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
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
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);
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
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;
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,
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
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:
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
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);
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
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;
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);
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
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
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
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
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
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:
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,
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
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,
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: --
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
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;
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: --
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
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 ,
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
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:
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
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);
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
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
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 );
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: --
4128: --
4129: null;
4130: --
4131: end if;
4132: hr_utility.set_location('Leaving: '|| l_proc,50);
4133:
4134:
4135: EXCEPTION
4136: WHEN OTHERS THEN
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 ;
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: --
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');
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
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;
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);
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
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:
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
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
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:
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: --
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;
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
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:
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: --
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;
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
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;
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);
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
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:
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:
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;
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
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:
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: --
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;
4405: null;
4406: --
4407: end if;
4408:
4409: hr_utility.set_location('Leaving: '|| l_proc,55);
4410: --
4411:
4412: EXCEPTION
4413: WHEN OTHERS THEN
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:
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
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,
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:
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);
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
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,
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;
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:
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;
4605: --
4606: null;
4607: --
4608: end if;
4609: hr_utility.set_location('Leaving: '|| l_proc,40);
4610:
4611: --
4612:
4613: EXCEPTION
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;
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:
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,
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:
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);
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
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
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',
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;
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:
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;
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);
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;
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: --
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
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);
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
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
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,
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;
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:
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
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;
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
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
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);
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
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
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,
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;
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:
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);
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;
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);
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
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
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
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:
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
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;
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);
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: -- ---------------------------------------------------------------------------
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
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:
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,
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;
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;
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,
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');
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');
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,
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,
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:
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);
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
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.
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: */
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:
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);
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:
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;
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');
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
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
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
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:
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: /*-----------------------------------------------------------------------
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');
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
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
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);
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
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);
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
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;