[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
500: c_additional_approver_order ame_util.orderRecord;
501: c_additional_approver_rec ame_util.approversTable;
502: -------------------------------BEGIN-------------------------------------------
503: BEGIN
504: hr_utility.set_location('Entering: '|| l_proc,5);
505: -- validate the session
506: hr_util_misc_web.validate_session(p_person_id => gn_person_id);
507:
508:
523: c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
524: itemkey => p_item_key,
525: aname => 'HR_AME_TRAN_TYPE_ATTR');
526:
527: hr_utility.trace('Going into (FOR I IN 1..p_approver_name.count LOOP): '|| l_proc);
528: -- get person id for the given full name.
529: FOR I IN 1..p_approver_name.count LOOP
530: -- get the person_id for this person_name
531: OPEN lc_approver ( p_full_name=>p_approver_name(I));
528: -- get person id for the given full name.
529: FOR I IN 1..p_approver_name.count LOOP
530: -- get the person_id for this person_name
531: OPEN lc_approver ( p_full_name=>p_approver_name(I));
532: hr_utility.trace('Going into Fetch after ( OPEN lc_approver ( p_full_name=>p_approver_name(I)) ): '|| l_proc);
533: FETCH lc_approver INTO ln_person_id;
534: IF lc_approver%NOTFOUND THEN
535: lv_exists := 'N';
536: raise gv_invalid_person;
541:
542: c_additional_approver_rec(i).person_id:=ln_person_id;
543:
544: end loop;
545: hr_utility.trace('Out of (FOR I IN 1..p_approver_name.count LOOP): '|| l_proc);
546: -- clear all the insertions into AME . Need to perform this step ONLY after we get the person id .
547: -- other wise it would clear the insertions made in the previous pass.
548: ame_api.clearInsertions(applicationIdIn =>c_application_id ,
549: transactionIdIn =>c_transaction_id,
551:
552:
553: if(c_transaction_type is not null) then
554: -- update AME list
555: hr_utility.trace('In( if(c_transaction_type is not null) ): '|| l_proc);
556: hr_utility.trace('Going into (for i in 1..c_additional_approver_rec.count): '|| l_proc);
557: for i in 1..c_additional_approver_rec.count loop
558: -- check for the default approver flag
559: if(p_approver_flag(I)='N') then
552:
553: if(c_transaction_type is not null) then
554: -- update AME list
555: hr_utility.trace('In( if(c_transaction_type is not null) ): '|| l_proc);
556: hr_utility.trace('Going into (for i in 1..c_additional_approver_rec.count): '|| l_proc);
557: for i in 1..c_additional_approver_rec.count loop
558: -- check for the default approver flag
559: if(p_approver_flag(I)='N') then
560: -- details for the record insertion into AME
574: orderIn =>c_additional_approver_order,
575: transactionTypeIn=>c_transaction_type );
576: end if;
577: end loop;
578: hr_utility.trace('Out of (for i in 1..c_additional_approver_rec.count): '|| l_proc);
579: end if; -- end updating AME list
580: hr_utility.set_location('Leaving: '|| l_proc,40);
581: EXCEPTION
582: WHEN gv_invalid_person THEN
576: end if;
577: end loop;
578: hr_utility.trace('Out of (for i in 1..c_additional_approver_rec.count): '|| l_proc);
579: end if; -- end updating AME list
580: hr_utility.set_location('Leaving: '|| l_proc,40);
581: EXCEPTION
582: WHEN gv_invalid_person THEN
583: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
584: if lc_approver%isopen then
579: end if; -- end updating AME list
580: hr_utility.set_location('Leaving: '|| l_proc,40);
581: EXCEPTION
582: WHEN gv_invalid_person THEN
583: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
584: if lc_approver%isopen then
585: close lc_approver;
586: end if;
587: raise;
585: close lc_approver;
586: end if;
587: raise;
588: WHEN OTHERS THEN
589: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
590: if lc_approver%isopen then
591: close lc_approver;
592: end if;
593: raise;
639: -------------------------------------------------------------------------------
640: -------------------------------BEGIN-------------------------------------------
641: BEGIN
642:
643: hr_utility.set_location('Entering: '|| l_proc,5);
644: -- validate the session
645: hr_util_misc_web.validate_session(p_person_id => gn_person_id);
646:
647: -- get user date format
658: aname => 'HR_AME_TRAN_TYPE_ATTR');
659:
660:
661: if (c_transaction_type is not null) then
662: hr_utility.trace('In (if (c_transaction_type is not null)): '|| l_proc);
663: update_ame_approvers_list(
664: p_item_type =>p_item_type,
665: p_item_key =>p_item_key,
666: p_act_id =>p_act_id,
667: p_approver_name =>p_approver_name,
668: p_approver_flag=>p_approver_flag);
669:
670: else
671: hr_utility.trace('In esle of (if (c_transaction_type is not null)): '|| l_proc);
672: -- fix for bug # 1570998
673: -- set all the current wf item attributes for additional approvers to deleted state.
674: -- The attributes would be updated later in the code
675: hr_utility.trace('Going into( for i in 1..p_approver_name.count): '|| l_proc);
671: hr_utility.trace('In esle of (if (c_transaction_type is not null)): '|| l_proc);
672: -- fix for bug # 1570998
673: -- set all the current wf item attributes for additional approvers to deleted state.
674: -- The attributes would be updated later in the code
675: hr_utility.trace('Going into( for i in 1..p_approver_name.count): '|| l_proc);
676: for i in 1..p_approver_name.count loop
677: lv_item_name := gv_item_name || to_char(I);
678: OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
679: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
675: hr_utility.trace('Going into( for i in 1..p_approver_name.count): '|| l_proc);
676: for i in 1..p_approver_name.count loop
677: lv_item_name := gv_item_name || to_char(I);
678: OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
679: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
680: FETCH csr_wiav into lv_dummy;
681: IF csr_wiav%notfound then
682: null;
683: ELSE
689: avalue => 'DELETED');
690: END IF; -- for csr_wiav%notfound
691: CLOSE csr_wiav;
692: end loop;
693: hr_utility.trace('Out of ( for i in 1..p_approver_name.count): '|| l_proc);
694: -- end fix for bug # 1570998
695:
696: -- update approvers data
697: hr_utility.trace('Going into( FOR I IN 1..p_approver_name.count): '|| l_proc);
693: hr_utility.trace('Out of ( for i in 1..p_approver_name.count): '|| l_proc);
694: -- end fix for bug # 1570998
695:
696: -- update approvers data
697: hr_utility.trace('Going into( FOR I IN 1..p_approver_name.count): '|| l_proc);
698: FOR I IN 1..p_approver_name.count
699: LOOP
700: IF p_approver_flag(I)='N' THEN
701: ln_addntl_approvers := ln_addntl_approvers + 1;
700: IF p_approver_flag(I)='N' THEN
701: ln_addntl_approvers := ln_addntl_approvers + 1;
702: -- get the person_id for this person_name
703: OPEN lc_approver ( p_full_name=>p_approver_name(I));
704: hr_utility.trace('Going into Fetch after (OPEN lc_approver ( p_full_name=>p_approver_name(I))): '|| l_proc);
705: FETCH lc_approver INTO ln_person_id;
706: IF lc_approver%NOTFOUND
707: THEN
708: lv_exists := 'N';
715: -- create the item attribute if it does not exist and update the value
716: lv_item_name := gv_item_name || to_char(I);
717:
718: OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
719: hr_utility.trace('Going into Fetch after (OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
720: FETCH csr_wiav into lv_dummy;
721: IF csr_wiav%notfound then
722: -- item attribute does not exist so create it
723: hr_approval_wf.create_item_attrib_if_notexist
751:
752:
753: END IF;-- p_approver_flag(I)='N'
754: END LOOP;
755: hr_utility.trace('Out of ( FOR I IN 1..p_approver_name.count): '|| l_proc);
756:
757: -- update the number of additional approvers in the system
758:
759: OPEN csr_wiav(p_item_type,p_item_key,'ADDITIONAL_APPROVERS_NUMBER');
756:
757: -- update the number of additional approvers in the system
758:
759: OPEN csr_wiav(p_item_type,p_item_key,'ADDITIONAL_APPROVERS_NUMBER');
760: hr_utility.trace('Going into Fetch after (OPEN csr_wiav(p_item_type,p_item_key,ADDITIONAL_APPROVERS_NUMBER)): '|| l_proc);
761: FETCH csr_wiav into lv_dummy;
762: IF csr_wiav%notfound THEN
763: -- create new wf_item_attribute_value to hold the additional approvers number
764: hr_approval_wf.create_item_attrib_if_notexist
783: end if ; -- end if for AME check
784:
785: -- update the data for the notifiers
786: --gv_notifier_name
787: hr_utility.trace('Going into( FOR I IN 1..p_notifier_name.count): '|| l_proc);
788: FOR I IN 1..p_notifier_name.count
789: LOOP
790: -- get the person_id for this person_name
791: OPEN lc_approver ( p_full_name=>p_notifier_name(I));
788: FOR I IN 1..p_notifier_name.count
789: LOOP
790: -- get the person_id for this person_name
791: OPEN lc_approver ( p_full_name=>p_notifier_name(I));
792: hr_utility.trace('Going into Fetch after (OPEN lc_approver ( p_full_name=>p_notifier_name(I))): '|| l_proc);
793: FETCH lc_approver INTO ln_person_id;
794: IF lc_approver%NOTFOUND
795: THEN
796: lv_exists := 'N';
803: -- create the item attribute if it does not exist and update the value
804: lv_item_name := gv_notifier_name || to_char(I);
805: ln_notifiers := ln_notifiers + 1;
806: OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
807: hr_utility.trace('Going into Fetch after (OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
808: FETCH csr_wiav into lv_dummy;
809: IF csr_wiav%notfound then
810: -- item attribute does not exist so create it
811: hr_approval_wf.create_item_attrib_if_notexist
830:
831: -- fetch if this notifier is onsubmittal
832: --loop to check if the user has checked this index
833: lv_exists := 'N';
834: hr_utility.trace('Going into( FOR J IN 1..p_notify_onsubmit_flag.count): '|| l_proc);
835: FOR J IN 1..p_notify_onsubmit_flag.count
836: LOOP
837: IF p_notify_onsubmit_flag(J)=I THEN
838: lv_exists := 'Y';
840: ELSE
841: lv_exists := 'N';
842: END IF;
843: END LOOP;
844: hr_utility.trace('Out of ( FOR J IN 1..p_notify_onsubmit_flag.count): '|| l_proc);
845: lv_notify:=lv_exists ||'|';
846:
847: lv_exists := 'N';
848: hr_utility.trace('Going into( FOR K IN 1..p_notify_onapproval_flag.count): '|| l_proc);
844: hr_utility.trace('Out of ( FOR J IN 1..p_notify_onsubmit_flag.count): '|| l_proc);
845: lv_notify:=lv_exists ||'|';
846:
847: lv_exists := 'N';
848: hr_utility.trace('Going into( FOR K IN 1..p_notify_onapproval_flag.count): '|| l_proc);
849: FOR K IN 1..p_notify_onapproval_flag.count
850: LOOP
851: IF p_notify_onapproval_flag(K)=I THEN
852: hr_utility.trace('In( IF p_notify_onapproval_flag(K)=I): '|| l_proc);
848: hr_utility.trace('Going into( FOR K IN 1..p_notify_onapproval_flag.count): '|| l_proc);
849: FOR K IN 1..p_notify_onapproval_flag.count
850: LOOP
851: IF p_notify_onapproval_flag(K)=I THEN
852: hr_utility.trace('In( IF p_notify_onapproval_flag(K)=I): '|| l_proc);
853: lv_exists := 'Y';
854: exit;
855: ELSE
856: lv_exists := 'N';
856: lv_exists := 'N';
857: END IF;
858:
859: END LOOP;
860: hr_utility.trace('Out of ( FOR K IN 1..p_notify_onapproval_flag.count): '|| l_proc);
861: lv_notify:= lv_notify||lv_exists;
862:
863: -- set the notification flag for this notifier
864: wf_engine.SetItemAttrText
867: aname => lv_item_name,
868: avalue => lv_notify);
869:
870: END LOOP;
871: hr_utility.trace('Out of ( FOR I IN 1..p_notifier_name.count): '|| l_proc);
872:
873: -- set the value for the number of notifiers
874: -- ln_notifiers
875:
873: -- set the value for the number of notifiers
874: -- ln_notifiers
875:
876: OPEN csr_wiav(p_item_type,p_item_key,'NOTIFIERS_NUMBER');
877: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,NOTIFIERS_NUMBER) ): '|| l_proc);
878: FETCH csr_wiav into lv_dummy;
879: IF csr_wiav%notfound THEN
880: -- create new wf_item_attribute_value to hold the additional approvers number
881: hr_approval_wf.create_item_attrib_if_notexist
898: CLOSE csr_wiav;
899:
900: -- set the gv_mode as re-enter
901: OPEN csr_wiav(p_item_type,p_item_key,'APPROVAL_ENTRY_MODE');
902: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,APPROVAL_ENTRY_MODE) ): '|| l_proc);
903: FETCH csr_wiav into lv_dummy;
904: IF csr_wiav%notfound THEN
905: -- create new wf_item_attribute_value to hold
906: hr_approval_wf.create_item_attrib_if_notexist
927: -- from the workflow functions for dyanamic approval and notifications process.
928:
929: -- attribute to hold the last_default approver from the heirarchy tree.
930: OPEN csr_wiav(p_item_type,p_item_key,'LAST_DEFAULT_APPROVER');
931: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,LAST_DEFAULT_APPROVER)): '|| l_proc);
932: FETCH csr_wiav into lv_dummy;
933: IF csr_wiav%notfound THEN
934: -- create new wf_item_attribute_value to hold
935: hr_approval_wf.create_item_attrib_if_notexist
1020:
1021:
1022: -- attribute to hold the current approver index .
1023: OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_APPROVER_INDEX');
1024: hr_utility.trace('Going into Fetch after (OPEN csr_wiav(p_item_type,p_item_key,CURRENT_APPROVER_INDEX) ): '|| l_proc);
1025: FETCH csr_wiav into lv_dummy;
1026: IF csr_wiav%notfound THEN
1027: -- create new wf_item_attribute_value to hold
1028: hr_approval_wf.create_item_attrib_if_notexist
1045: CLOSE csr_wiav;
1046:
1047: -- attribute to hold the current onsubmit notifier index .
1048: OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_ONSUBMIT_INDEX');
1049: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,CURRENT_ONSUBMIT_INDEX) ): '|| l_proc);
1050: FETCH csr_wiav into lv_dummy;
1051: IF csr_wiav%notfound THEN
1052: -- create new wf_item_attribute_value to hold
1053: hr_approval_wf.create_item_attrib_if_notexist
1070: CLOSE csr_wiav;
1071:
1072: -- attribute to hold the current onapproval notifier index .
1073: OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_ONAPPROVAL_INDEX');
1074: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,CURRENT_ONAPPROVAL_INDEX) ): '|| l_proc);
1075: FETCH csr_wiav into lv_dummy;
1076: IF csr_wiav%notfound THEN
1077: -- create new wf_item_attribute_value to hold
1078: hr_approval_wf.create_item_attrib_if_notexist
1093: avalue => 0);
1094: END IF;
1095: CLOSE csr_wiav;
1096:
1097: hr_utility.set_location('Leaving: '|| l_proc,130);
1098: EXCEPTION
1099: WHEN gv_invalid_person THEN
1100: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1101: if lc_approver%isopen then
1096:
1097: hr_utility.set_location('Leaving: '|| l_proc,130);
1098: EXCEPTION
1099: WHEN gv_invalid_person THEN
1100: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1101: if lc_approver%isopen then
1102: close lc_approver;
1103: end if;
1104: if csr_wiav%isopen then
1105: close csr_wiav;
1106: end if;
1107: raise;
1108: WHEN OTHERS THEN
1109: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1110: if lc_approver%isopen then
1111: close lc_approver;
1112: end if;
1113: if csr_wiav%isopen then
1150: l_proc constant varchar2(100) := g_package || ' validate_approvers';
1151:
1152: --
1153: BEGIN
1154: hr_utility.set_location('Entering: '|| l_proc,5);
1155: -- validate the session
1156: hr_util_misc_web.validate_session(p_person_id => gn_person_id);
1157:
1158: -- get user date format
1176:
1177:
1178: -- Loop through the names entered to see if we have a valid person_id
1179: -- for each name
1180: hr_utility.trace('Going into(FOR I IN 1..p_approvers_name.count): '|| l_proc);
1181: FOR I IN 1..p_approvers_name.count
1182: LOOP
1183: lv_job_title := NULL;
1184: OPEN lc_approver(p_approvers_name(I));
1181: FOR I IN 1..p_approvers_name.count
1182: LOOP
1183: lv_job_title := NULL;
1184: OPEN lc_approver(p_approvers_name(I));
1185: hr_utility.trace('Going into Fetch after (OPEN lc_approver(p_approvers_name(I)) ): '|| l_proc);
1186: FETCH lc_approver INTO ln_approver_id ;
1187: IF lc_approver%NOTFOUND
1188: THEN
1189: p_error_flag := TRUE ;
1200: CLOSE lc_approver;
1201:
1202: -- IF p_error_flag AND lv_exists = 'Y' THEN
1203: IF lv_exists = 'Y' THEN
1204: hr_utility.trace('In(IF lv_exist = Y): '|| l_proc);
1205: gv_error_table(I).full_name := p_approvers_name(I);
1206: gv_error_table(I).person_id := NULL;
1207: gv_error_table(I).job_title := NULL;
1208: gv_error_table(I).error_exists := 'Y';
1213: p_errorMsg=>hr_util_misc_web.return_msg_text
1214: (p_message_name => 'HR_DA_MESG05_WEB'
1215: ,p_application_id => 'PER'));
1216: ELSE
1217: hr_utility.trace('In else of (IF lv_exist = Y): '|| l_proc);
1218: gv_error_table(I).full_name := p_approvers_name(I);
1219: gv_error_table(I).person_id := ln_approver_id;
1220: gv_error_table(I).error_exists := 'N';
1221: gv_error_table(I).default_approver:= p_approver_flag(I);
1221: gv_error_table(I).default_approver:= p_approver_flag(I);
1222: -- get the assignment id for this person
1223: OPEN gc_assignment_id (p_person_id=>ln_approver_id,
1224: p_effective_date=>ld_effective_date);
1225: hr_utility.trace('Going into Fetch after (OPEN gc_assignment_id (p_person_id...,p_effective_date..)): '|| l_proc);
1226: FETCH gc_assignment_id INTO ln_assignment_id;
1227: IF gc_assignment_id%NOTFOUND
1228: THEN
1229: lv_job_title := NULL;
1244: */
1245: -- get job id gc_job_id
1246: OPEN gc_job_id (p_assignment_id=>ln_assignment_id,
1247: p_effective_date=>ld_effective_date);
1248: hr_utility.trace('Going into Fetch after ( OPEN gc_job_id (p_assignment_id.., p_effective_date) ): '|| l_proc);
1249: FETCH gc_job_id INTO ln_job_id;
1250: IF gc_job_id%NOTFOUND
1251: THEN
1252: ln_job_id := NULL;
1270: END LOOP;
1271:
1272:
1273: grt_approver_details_table := gv_error_table;
1274: hr_utility.set_location('Leaving: '|| l_proc,35);
1275: EXCEPTION
1276: WHEN OTHERS THEN
1277: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1278: if lc_approver%isopen then
1273: grt_approver_details_table := gv_error_table;
1274: hr_utility.set_location('Leaving: '|| l_proc,35);
1275: EXCEPTION
1276: WHEN OTHERS THEN
1277: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1278: if lc_approver%isopen then
1279: close lc_approver;
1280: end if;
1281: if csr_wiav%isopen then
1328:
1329: --
1330: BEGIN
1331:
1332: hr_utility.set_location('Entering: '|| l_proc,5);
1333: -- validate the session
1334: hr_util_misc_web.validate_session(p_person_id => gn_person_id);
1335:
1336: -- get user date format
1354:
1355:
1356: -- Loop through the names entered to see if we have a valid person_id
1357: -- for each name
1358: hr_utility.trace('Going into (FOR I IN 1..p_notifier_name.count): '|| l_proc);
1359:
1360: FOR I IN 1..p_notifier_name.count
1361: LOOP
1362: lv_job_title := NULL;
1360: FOR I IN 1..p_notifier_name.count
1361: LOOP
1362: lv_job_title := NULL;
1363: OPEN lc_approver(p_notifier_name(I));
1364: hr_utility.trace('Going into Fetch after ( OPEN lc_approver(p_notifier_name(I))): '|| l_proc);
1365: FETCH lc_approver INTO ln_notifier_id ;
1366: IF lc_approver%NOTFOUND
1367: THEN
1368: p_error_flag := TRUE ;
1380: CLOSE lc_approver;
1381:
1382: -- IF p_error_flag AND lv_exists = 'Y' THEN
1383: IF lv_exists = 'Y' THEN
1384: hr_utility.trace('In(IF lv_exists = Y): '|| l_proc);
1385: grt_notifier_error_table(I).full_name := p_notifier_name(I);
1386: grt_notifier_error_table(I).person_id := NULL;
1387: grt_notifier_error_table(I).job_title := NULL;
1388: grt_notifier_error_table(I).error_exists := 'Y';
1394: p_errorMsg=>hr_util_misc_web.return_msg_text
1395: (p_message_name => 'HR_DA_MESG06_WEB'
1396: ,p_application_id => 'PER'));
1397: ELSE
1398: hr_utility.trace('In else of (IF lv_exists = Y): '|| l_proc);
1399: grt_notifier_error_table(I).full_name := p_notifier_name(I);
1400: grt_notifier_error_table(I).person_id := ln_notifier_id;
1401: grt_notifier_error_table(I).error_exists := 'N';
1402: -- fix for bug # 1570998
1411: grt_notifier_error_table(I).job_title := lv_job_title;
1412: END IF;
1413:
1414: END LOOP;
1415: hr_utility.trace('Out of (FOR I IN 1..p_notifier_name.count): '|| l_proc);
1416: hr_utility.set_location('Leaving: '|| l_proc,30);
1417: grt_notifier_details_table := grt_notifier_error_table;
1418:
1419: EXCEPTION
1412: END IF;
1413:
1414: END LOOP;
1415: hr_utility.trace('Out of (FOR I IN 1..p_notifier_name.count): '|| l_proc);
1416: hr_utility.set_location('Leaving: '|| l_proc,30);
1417: grt_notifier_details_table := grt_notifier_error_table;
1418:
1419: EXCEPTION
1420: WHEN OTHERS THEN
1417: grt_notifier_details_table := grt_notifier_error_table;
1418:
1419: EXCEPTION
1420: WHEN OTHERS THEN
1421: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1422: if lc_approver%isopen then
1423: close lc_approver;
1424: end if;
1425: raise;
1460: --local variables
1461: l_proc constant varchar2(100) := g_package || ' get_wf_attributes';
1462: BEGIN
1463:
1464: hr_utility.set_location('Entering: '|| l_proc,5);
1465: IF hr_mee_workflow_service.check_web_page_code(
1466: p_item_type => p_item_type,
1467: p_item_key => p_item_key,
1468: p_actid => p_actid,
1495: -- need to invesitgate further and debug. Making a direct call to wf_engine
1496: -- package.
1497: -- check if the acitvity attribute for approval level exists
1498: OPEN csr_wfaav(ln_approval_level,p_actid );
1499: hr_utility.trace('Going into Fetch after ( OPEN csr_wfaav(ln_approval_level,p_actid ) ): '|| l_proc);
1500: FETCH csr_wfaav into lv_dummy;
1501: IF csr_wfaav%notfound THEN
1502: grt_wf_attributes_rec.approval_level :=NULL;
1503: ELSE
1510:
1511: END IF;
1512: CLOSE csr_wfaav;
1513:
1514: hr_utility.set_location('Leaving: '|| l_proc,15);
1515:
1516:
1517: EXCEPTION
1518: WHEN OTHERS THEN
1515:
1516:
1517: EXCEPTION
1518: WHEN OTHERS THEN
1519: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1520: if csr_wfaav%isopen then
1521: close csr_wfaav;
1522: end if;
1523: raise;
1574: c_default_approvers ame_util.approversTable;
1575:
1576:
1577: BEGIN
1578: hr_utility.set_location('Entering: '|| l_proc,5);
1579:
1580: gn_person_id := wf_engine.GetItemAttrNumber
1581: (itemtype => p_item_type
1582: ,itemkey => p_item_key
1614:
1615:
1616: -- reset the gn_approver_index each time this procedure is called
1617: gn_approver_index := 1;
1618: hr_utility.trace('Going into( for i in 1..c_default_approvers.count): '|| l_proc);
1619: for i in 1..c_default_approvers.count LOOP
1620: lv_job_title:=NULL;
1621: -- get the next approver from the list
1622: -- Check if the AME approver is authority approver
1642:
1643: gn_approver_index := gn_approver_index + 1;
1644: END IF;
1645: END LOOP;
1646: hr_utility.trace('Out of ( for i in 1..c_default_approvers.count): '|| l_proc);
1647: -- for the out parameters
1648: hr_utility.trace('Going into( FOR I IN 1..grt_person_details_rec_table.count ): '|| l_proc);
1649: FOR I IN 1..grt_person_details_rec_table.count LOOP
1650: p_approver_name(I) := grt_person_details_rec_table(I).full_name;
1644: END IF;
1645: END LOOP;
1646: hr_utility.trace('Out of ( for i in 1..c_default_approvers.count): '|| l_proc);
1647: -- for the out parameters
1648: hr_utility.trace('Going into( FOR I IN 1..grt_person_details_rec_table.count ): '|| l_proc);
1649: FOR I IN 1..grt_person_details_rec_table.count LOOP
1650: p_approver_name(I) := grt_person_details_rec_table(I).full_name;
1651: p_approver_flag(I) := grt_person_details_rec_table(I).default_approver;
1652: END LOOP;
1649: FOR I IN 1..grt_person_details_rec_table.count LOOP
1650: p_approver_name(I) := grt_person_details_rec_table(I).full_name;
1651: p_approver_flag(I) := grt_person_details_rec_table(I).default_approver;
1652: END LOOP;
1653: hr_utility.trace('Out of ( FOR I IN 1..grt_person_details_rec_table.count ): '|| l_proc);
1654: grt_approver_details_table := grt_person_details_rec_table;
1655: hr_utility.set_location('Leaving: '|| l_proc,30);
1656: EXCEPTION
1657: WHEN OTHERS THEN
1651: p_approver_flag(I) := grt_person_details_rec_table(I).default_approver;
1652: END LOOP;
1653: hr_utility.trace('Out of ( FOR I IN 1..grt_person_details_rec_table.count ): '|| l_proc);
1654: grt_approver_details_table := grt_person_details_rec_table;
1655: hr_utility.set_location('Leaving: '|| l_proc,30);
1656: EXCEPTION
1657: WHEN OTHERS THEN
1658: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1659: raise;
1654: grt_approver_details_table := grt_person_details_rec_table;
1655: hr_utility.set_location('Leaving: '|| l_proc,30);
1656: EXCEPTION
1657: WHEN OTHERS THEN
1658: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1659: raise;
1660: END get_ame_default_approvers;
1661:
1662: -- get_all_ame_approvers
1709:
1710:
1711:
1712: BEGIN
1713: hr_utility.set_location('Entering: '|| l_proc,5);
1714:
1715: IF gv_mode='RE-ENTER' THEN
1716: hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
1717:
1712: BEGIN
1713: hr_utility.set_location('Entering: '|| l_proc,5);
1714:
1715: IF gv_mode='RE-ENTER' THEN
1716: hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
1717:
1718: -- Get AME related data from WF attributes
1719: -- get the AME transaction type and app id
1720: c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1744: approversOut=>c_all_approvers);
1745: end if;
1746:
1747: ln_approver_index := c_all_approvers.count;
1748: hr_utility.trace('Going into (FOR I IN 1..ln_approver_index ): '|| l_proc);
1749: FOR I IN 1..ln_approver_index LOOP
1750: -- 11510 specific changes, bug 3841261
1751: if(nvl(c_all_approvers(i).approval_status,'NULL') not in(ame_util.suppressedStatus,ame_util.repeatedStatus)) then
1752: ln_approver_list_index:= ln_approver_list_index + 1;
1774: grt_approver_details_table(ln_approver_list_index).default_approver := 'N';
1775: END IF; -- for lv_default_approver
1776: end if;
1777: END LOOP;
1778: hr_utility.trace('Out of (FOR I IN 1..ln_approver_index ): '|| l_proc);
1779: --//grt_person_details_rec_table := grt_approver_details_table;
1780:
1781: END IF; -- for the p_mode
1782:
1779: --//grt_person_details_rec_table := grt_approver_details_table;
1780:
1781: END IF; -- for the p_mode
1782:
1783: hr_utility.trace('Going into (FOR I IN 1..ln_approver_name.count ): '|| l_proc);
1784: FOR I IN 1..p_approver_name.count LOOP
1785: lv_job_title := NULL;
1786: IF hr_errors_api.errorExists THEN
1787: grt_approver_details_table(I).full_name := gv_error_table(ln_error_count).full_name;
1792: ln_error_count := ln_error_count + 1;
1793: ELSE
1794: -- get the person id for this person
1795: OPEN lc_approver ( p_full_name=>p_approver_name(I));
1796: hr_utility.trace('Going into Fetch after ( OPEN lc_approver ( p_full_name=>p_approver_name(I)) ): '|| l_proc);
1797: FETCH lc_approver INTO ln_person_id ;
1798: IF lc_approver%NOTFOUND THEN
1799: lv_job_title := NULL;
1800: ln_assignment_id:= NULL;
1819: grt_approver_details_table(I).job_title :=lv_job_title;
1820: grt_approver_details_table(I).default_approver := p_approver_flag(I);
1821: END IF; -- for hr_errors_api.errorExists
1822: END LOOP;
1823: hr_utility.trace('Out of (FOR I IN 1..ln_approver_name.count ): '|| l_proc);
1824: hr_utility.set_location('Leaving: '|| l_proc,40);
1825:
1826: EXCEPTION
1827: WHEN OTHERS THEN
1820: grt_approver_details_table(I).default_approver := p_approver_flag(I);
1821: END IF; -- for hr_errors_api.errorExists
1822: END LOOP;
1823: hr_utility.trace('Out of (FOR I IN 1..ln_approver_name.count ): '|| l_proc);
1824: hr_utility.set_location('Leaving: '|| l_proc,40);
1825:
1826: EXCEPTION
1827: WHEN OTHERS THEN
1828: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1824: hr_utility.set_location('Leaving: '|| l_proc,40);
1825:
1826: EXCEPTION
1827: WHEN OTHERS THEN
1828: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1829: if lc_approver%isopen then
1830: close lc_approver;
1831: end if;
1832: raise;
1861: l_default_approvers_list hr_dynamic_approver_list_ss := hr_dynamic_approver_list_ss();
1862: l_default_approver hr_dynamic_approver_ss;
1863: l_proc constant varchar2(100) := g_package || ' get_default_approvers_list';
1864: BEGIN
1865: hr_utility.set_location('Entering: '|| l_proc,5);
1866: -- remove all rows from person details table
1867:
1868: grt_person_details_rec_table.DELETE;
1869: grt_approver_details_table.DELETE;
1883: grt_person_details_rec_table:= grt_approver_details_table;
1884:
1885: -- copy parameters into l_default_approvers_list
1886:
1887: hr_utility.trace('Going into (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1888: FOR I IN 1..grt_person_details_rec_table.count
1889: LOOP
1890:
1891: l_default_approver := hr_dynamic_approver_ss(
1901: -- add to list
1902: l_default_approvers_list(I) := l_default_approver;
1903:
1904: END LOOP;
1905: hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1906: -- set out parameter
1907: p_default_approvers_list := l_default_approvers_list;
1908: hr_utility.set_location('Leaving: '|| l_proc,20);
1909:
1904: END LOOP;
1905: hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1906: -- set out parameter
1907: p_default_approvers_list := l_default_approvers_list;
1908: hr_utility.set_location('Leaving: '|| l_proc,20);
1909:
1910: exception
1911: when others then
1912: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1908: hr_utility.set_location('Leaving: '|| l_proc,20);
1909:
1910: exception
1911: when others then
1912: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1913: raise;
1914:
1915: END get_default_approvers_list;
1916:
1932: l_default_approver hr_dynamic_approver_ss;
1933: l_proc constant varchar2(100) := g_package || ' get_default_approvers_list_extra';
1934: l_error_message long default null;
1935: BEGIN
1936: hr_utility.set_location('Entering: '|| l_proc,5);
1937: -- remove all rows from person details table
1938:
1939: grt_person_details_rec_table.DELETE;
1940: grt_approver_details_table.DELETE;
1954: grt_person_details_rec_table:= grt_approver_details_table;
1955:
1956: -- copy parameters into l_default_approvers_list
1957:
1958: hr_utility.trace('Going into (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1959: FOR I IN 1..grt_person_details_rec_table.count
1960: LOOP
1961:
1962: l_default_approver := hr_dynamic_approver_ss(
1972: -- add to list
1973: l_default_approvers_list(I) := l_default_approver;
1974:
1975: END LOOP;
1976: hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1977: -- set out parameter
1978: p_default_approvers_list := l_default_approvers_list;
1979: hr_utility.set_location('Leaving: '|| l_proc,20);
1980:
1975: END LOOP;
1976: hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
1977: -- set out parameter
1978: p_default_approvers_list := l_default_approvers_list;
1979: hr_utility.set_location('Leaving: '|| l_proc,20);
1980:
1981: exception
1982: when others then
1983: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1979: hr_utility.set_location('Leaving: '|| l_proc,20);
1980:
1981: exception
1982: when others then
1983: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1984: -- set error message
1985: l_error_message := hr_utility.get_message;
1986:
1987: IF (l_error_message IS NOT NULL) THEN
1981: exception
1982: when others then
1983: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1984: -- set error message
1985: l_error_message := hr_utility.get_message;
1986:
1987: IF (l_error_message IS NOT NULL) THEN
1988: p_error_message := l_error_message;
1989: END IF;
2040: c_default_approvers ame_util.approversTable;
2041:
2042:
2043: BEGIN
2044: hr_utility.set_location('Entering: '|| l_proc,5);
2045: -- validate the session
2046: -- ******************************************************************************
2047: -- commented out for v 4 by pzwalker - replaced with GetItemAttrNumber call
2048: -- hr_util_misc_web.validate_session(p_person_id => gn_person_id);
2111: (p_itemtype => p_item_type
2112: ,p_itemkey => p_item_key);
2113:
2114: -- Get all the approvers from the Custom Approval Package.
2115: hr_utility.trace('Going into(WHILE lv_result <> Y): '|| l_proc);
2116: WHILE lv_result <> 'Y'
2117: LOOP
2118: lv_job_title:=NULL;
2119: -- Check for final approver
2121: lv_result := hr_approval_custom.Check_Final_approver
2122: (p_forward_to_person_id => lv_forward_to_id
2123: ,p_person_id => lv_creator_person_id );
2124: EXCEPTION WHEN OTHERS THEN
2125: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2126: raise;
2127: END;
2128:
2129: -- Check if there is any error
2132: NULL;
2133: END IF;
2134:
2135: IF lv_result='Y' THEN
2136: hr_utility.trace('In (IF lv_result=Y): '|| l_proc);
2137: EXIT;
2138: END IF;
2139:
2140: -- get the next approver from the custom package
2145:
2146:
2147: -- Check if the person id returned is NULL
2148: IF lv_forward_to_id IS NULL THEN
2149: hr_utility.trace('In ( IF lv_forward_to_id IS NULL): '|| l_proc);
2150: lv_result:='Y';
2151: ELSE
2152: hr_utility.trace('In else of ( IF lv_forward_to_id IS NULL): '|| l_proc);
2153: -- set forward from to old forward to
2148: IF lv_forward_to_id IS NULL THEN
2149: hr_utility.trace('In ( IF lv_forward_to_id IS NULL): '|| l_proc);
2150: lv_result:='Y';
2151: ELSE
2152: hr_utility.trace('In else of ( IF lv_forward_to_id IS NULL): '|| l_proc);
2153: -- set forward from to old forward to
2154: lv_current_forward_to_id := lv_forward_to_id;
2155:
2156: -- get assignment id for the approver
2176: grt_person_details_rec_table(gn_approver_index).default_approver:= 'Y';
2177:
2178: -- bug # 1964924
2179: if(ln_approval_level>0 and ln_approval_level=grt_person_details_rec_table.count) then
2180: hr_utility.trace('In (if(ln_approval_level>0 and ln_approval_level=grt_person_details_rec_table.count)): '|| l_proc);
2181: exit;
2182: end if;
2183:
2184: -- Increment approver Index
2187: END IF;
2188:
2189:
2190: END LOOP;
2191: hr_utility.trace('Out of (WHILE lv_result <> Y): '|| l_proc);
2192: -- for the out parameters
2193: hr_utility.trace('Going into (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
2194: FOR I IN 1..grt_person_details_rec_table.count
2195: LOOP
2189:
2190: END LOOP;
2191: hr_utility.trace('Out of (WHILE lv_result <> Y): '|| l_proc);
2192: -- for the out parameters
2193: hr_utility.trace('Going into (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
2194: FOR I IN 1..grt_person_details_rec_table.count
2195: LOOP
2196: p_approver_name(I) := grt_person_details_rec_table(I).full_name;
2197: p_approver_flag(I) := grt_person_details_rec_table(I).default_approver;
2196: p_approver_name(I) := grt_person_details_rec_table(I).full_name;
2197: p_approver_flag(I) := grt_person_details_rec_table(I).default_approver;
2198:
2199: END LOOP;
2200: hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
2201: grt_approver_details_table := grt_person_details_rec_table;
2202: hr_utility.set_location('Leaving: '|| l_proc,45);
2203:
2204: EXCEPTION
2198:
2199: END LOOP;
2200: hr_utility.trace('Out of (FOR I IN 1..grt_person_details_rec_table.count): '|| l_proc);
2201: grt_approver_details_table := grt_person_details_rec_table;
2202: hr_utility.set_location('Leaving: '|| l_proc,45);
2203:
2204: EXCEPTION
2205: WHEN OTHERS THEN
2206: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2202: hr_utility.set_location('Leaving: '|| l_proc,45);
2203:
2204: EXCEPTION
2205: WHEN OTHERS THEN
2206: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2207: raise;
2208: END get_default_approvers;
2209:
2210:
2252: c_transaction_type varchar2(25);
2253:
2254: BEGIN
2255:
2256: hr_utility.set_location('Entering: '|| l_proc,5);
2257: IF gv_mode='RE-ENTER' THEN
2258: hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
2259: -- check if we need to call AME
2260: -- get the AME transaction type value from WF item attributes
2254: BEGIN
2255:
2256: hr_utility.set_location('Entering: '|| l_proc,5);
2257: IF gv_mode='RE-ENTER' THEN
2258: hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
2259: -- check if we need to call AME
2260: -- get the AME transaction type value from WF item attributes
2261: c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2262: itemkey => p_item_key,
2263: aname => 'HR_AME_TRAN_TYPE_ATTR');
2264:
2265:
2266: if (c_transaction_type is not null) then
2267: hr_utility.trace('In (if (c_transaction_type is not null)): '|| l_proc);
2268: get_all_ame_approvers(p_approver_name =>p_approver_name,
2269: p_approver_flag =>p_approver_flag,
2270: p_item_type =>p_item_type,
2271: p_item_key =>p_item_key ,
2269: p_approver_flag =>p_approver_flag,
2270: p_item_type =>p_item_type,
2271: p_item_key =>p_item_key ,
2272: p_effective_date=>p_effective_date);
2273: hr_utility.set_location('Leaving: '|| l_proc,20);
2274:
2275: return;
2276: end if;
2277:
2310:
2311:
2312:
2313: ln_approver_index := lv_approver_name.count + ln_num_of_add_apprs;
2314: hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
2315: FOR I IN 1..ln_approver_index
2316: LOOP
2317: lv_job_title := NULL;
2318: lv_item_name := gv_item_name || to_char(I);
2317: lv_job_title := NULL;
2318: lv_item_name := gv_item_name || to_char(I);
2319: -- open the cursor to determine if the item exists
2320: OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
2321: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
2322: FETCH csr_wiav into l_dummy;
2323: IF csr_wiav%notfound THEN
2324: lv_default_approver := 'Y';
2325: ELSE
2391: grt_approver_details_table(I).default_approver := 'Y';
2392: ln_def_app_index := ln_def_app_index + 1;
2393: END IF; -- for lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED'
2394: END LOOP;
2395: hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
2396: hr_utility.set_location('Leaving: '|| l_proc,40);
2397: return;
2398: END IF; -- for the p_mode
2399: hr_utility.trace('Going into(FOR I IN 1..p_approver_name.count): '|| l_proc);
2392: ln_def_app_index := ln_def_app_index + 1;
2393: END IF; -- for lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED'
2394: END LOOP;
2395: hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
2396: hr_utility.set_location('Leaving: '|| l_proc,40);
2397: return;
2398: END IF; -- for the p_mode
2399: hr_utility.trace('Going into(FOR I IN 1..p_approver_name.count): '|| l_proc);
2400:
2395: hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
2396: hr_utility.set_location('Leaving: '|| l_proc,40);
2397: return;
2398: END IF; -- for the p_mode
2399: hr_utility.trace('Going into(FOR I IN 1..p_approver_name.count): '|| l_proc);
2400:
2401: FOR I IN 1..p_approver_name.count
2402: LOOP
2403: lv_job_title := NULL;
2411: ELSE
2412:
2413: -- get the person id for this person
2414: OPEN lc_approver ( p_full_name=>p_approver_name(I));
2415: hr_utility.trace('Going into Fetch after (OPEN lc_approver ( p_full_name=>p_approver_name(I)) ): '|| l_proc);
2416: FETCH lc_approver INTO ln_person_id ;
2417: IF lc_approver%NOTFOUND
2418: THEN
2419: lv_job_title := NULL;
2443: grt_approver_details_table(I).default_approver := p_approver_flag(I);
2444:
2445: END IF; -- for hr_errors_api.errorExists
2446: END LOOP;
2447: hr_utility.trace('Out of (FOR I IN 1..p_approver_name.count): '|| l_proc);
2448: hr_utility.set_location('Leaving: '|| l_proc,60);
2449:
2450:
2451: EXCEPTION
2444:
2445: END IF; -- for hr_errors_api.errorExists
2446: END LOOP;
2447: hr_utility.trace('Out of (FOR I IN 1..p_approver_name.count): '|| l_proc);
2448: hr_utility.set_location('Leaving: '|| l_proc,60);
2449:
2450:
2451: EXCEPTION
2452: WHEN OTHERS THEN
2449:
2450:
2451: EXCEPTION
2452: WHEN OTHERS THEN
2453: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2454: if lc_approver%isopen then
2455: close lc_approver;
2456: end if;
2457: if csr_wiav%isopen then
2492: lv_index NUMBER;
2493: -- local variable
2494: l_proc constant varchar2(100) := g_package || ' build_ddl';
2495: BEGIN
2496: hr_utility.set_location('Entering: '|| l_proc,5);
2497: IF p_approver_name.count=1 THEN
2498: lv_index := p_approver_name.count;
2499: ELSE
2500: lv_index := p_approver_name.count -1;
2498: lv_index := p_approver_name.count;
2499: ELSE
2500: lv_index := p_approver_name.count -1;
2501: END IF;
2502: hr_utility.trace('Going into( FOR I IN 1..lv_index): '|| l_proc);
2503:
2504:
2505: FOR I IN 1..lv_index
2506: LOOP
2510: l_ddl_data(I).code_index := I;
2511:
2512: END LOOP;
2513:
2514: hr_utility.trace('Out of ( FOR I IN 1..lv_index): '|| l_proc);
2515: l_lov
2516: := hr_util_misc_web.g_new_line || htf.formselectopen
2517: (cname => upper(p_variable_name)
2518: ,nsize => 1
2518: ,nsize => 1
2519: ,cattributes => p_attributes) ||
2520: hr_util_misc_web.g_new_line ;
2521:
2522: hr_utility.trace('Going into(FOR i IN 1..lv_index LOOP): '|| l_proc);
2523: FOR i IN 1..lv_index LOOP
2524: IF p_variable_value IS NOT NULL THEN
2525: IF l_ddl_data(i).code = p_variable_value THEN
2526: l_checked := 'SELECTED';
2536: ,cattributes => 'VALUE="'|| l_ddl_data(i).code
2537: ||'"' ||'INDEX="'|| l_ddl_data(i).code_index
2538: ||'"' ) ;--|| hr_util_misc_web.g_new_line;
2539: END LOOP;
2540: hr_utility.trace('Out of (FOR i IN 1..lv_index LOOP): '|| l_proc);
2541: l_lov := l_lov || htf.formselectclose || hr_util_misc_web.g_new_line;
2542:
2543: hr_utility.set_location('Leaving: '|| l_proc,30);
2544:
2539: END LOOP;
2540: hr_utility.trace('Out of (FOR i IN 1..lv_index LOOP): '|| l_proc);
2541: l_lov := l_lov || htf.formselectclose || hr_util_misc_web.g_new_line;
2542:
2543: hr_utility.set_location('Leaving: '|| l_proc,30);
2544:
2545: RETURN l_lov;
2546:
2547: EXCEPTION WHEN OTHERS THEN
2544:
2545: RETURN l_lov;
2546:
2547: EXCEPTION WHEN OTHERS THEN
2548: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2549: raise;
2550:
2551:
2552: END build_ddl;
2590: DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2591: lv_approver_flag hr_util_misc_web.g_varchar2_tab_type
2592: DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2593: BEGIN
2594: hr_utility.set_location('Entering: '|| l_proc,5);
2595: -- validate the session
2596: hr_util_misc_web.validate_session(p_person_id => gn_person_id);
2597:
2598: -- set the package global variables
2599: gv_mode := 'ADD';
2600: -- new code
2601:
2602: ln_loop_index := 1;
2603: hr_utility.trace('Going into(FOR I IN 1..(p_approver_name.count + 1)): '|| l_proc);
2604: FOR I IN 1..(p_approver_name.count + 1)
2605: LOOP
2606: IF I=(p_approver_index+1) THEN
2607: lv_approver_name(I) := NULL;
2614: lv_approver_name(I) := p_approver_name(I);
2615: lv_approver_flag(I) := p_approver_flag(I);
2616: END IF;
2617: END LOOP;
2618: hr_utility.trace('Out of (FOR I IN 1..(p_approver_name.count + 1)): '|| l_proc);
2619: p_approver_name := lv_approver_name;
2620: p_approver_flag := lv_approver_flag;
2621: hr_utility.set_location('Leaving: '|| l_proc,20);
2622:
2617: END LOOP;
2618: hr_utility.trace('Out of (FOR I IN 1..(p_approver_name.count + 1)): '|| l_proc);
2619: p_approver_name := lv_approver_name;
2620: p_approver_flag := lv_approver_flag;
2621: hr_utility.set_location('Leaving: '|| l_proc,20);
2622:
2623: EXCEPTION WHEN OTHERS THEN
2624: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2625: raise;
2620: p_approver_flag := lv_approver_flag;
2621: hr_utility.set_location('Leaving: '|| l_proc,20);
2622:
2623: EXCEPTION WHEN OTHERS THEN
2624: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2625: raise;
2626:
2627: END add_approver;
2628:
2676: lv_approver_flag hr_util_misc_web.g_varchar2_tab_type
2677: DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2678:
2679: BEGIN
2680: hr_utility.set_location('Entering: '|| l_proc,5);
2681: -- validate the session
2682: hr_util_misc_web.validate_session(p_person_id => gn_person_id);
2683:
2684:
2775:
2776: -- new code
2777: gn_additional_approvers := 0;
2778:
2779: hr_utility.trace('Going into( FOR I IN 1..p_approver_name.count): '|| l_proc);
2780: FOR I IN 1..p_approver_name.count
2781: LOOP
2782: IF I=p_approver_index THEN
2783: NULL;
2793: gn_additional_approvers := gn_additional_approvers + 1;
2794: end if;
2795:
2796: END LOOP;
2797: hr_utility.trace('Out of ( FOR I IN 1..p_approver_name.count): '|| l_proc);
2798: wf_engine.SetItemAttrNumber
2799: (itemtype => p_item_type,
2800: itemkey => p_item_key,
2801: aname => 'ADDITIONAL_APPROVERS_NUMBER',
2805:
2806: p_approver_name := lv_approver_name;
2807: p_approver_flag := lv_approver_flag;
2808:
2809: hr_utility.set_location('Leaving: '|| l_proc,20);
2810:
2811: EXCEPTION WHEN OTHERS THEN
2812: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2813: if csr_wiav%isopen then
2808:
2809: hr_utility.set_location('Leaving: '|| l_proc,20);
2810:
2811: EXCEPTION WHEN OTHERS THEN
2812: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2813: if csr_wiav%isopen then
2814: close csr_wiav;
2815: end if;
2816: raise;
2844: lv_notify_onapproval_flag hr_util_misc_web.g_varchar2_tab_type;
2845: lv_exist VARCHAR2(10);
2846: l_proc constant varchar2(100) := g_package || ' delete_notifier';
2847: BEGIN
2848: hr_utility.set_location('Entering: '|| l_proc,5);hr_utility.set_location('Going into(FOR I IN 1..p_notifier_name.count): '|| l_proc,10);
2849:
2850:
2851: FOR I IN 1..p_notifier_name.count
2852: LOOP
2850:
2851: FOR I IN 1..p_notifier_name.count
2852: LOOP
2853: IF I=p_notifier_index THEN
2854: hr_utility.trace('In(IF I=p_notifier_index): '|| l_proc);
2855: NULL;
2856: ELSIF I>p_notifier_index THEN
2857: hr_utility.trace('In(ELSIF I>p_notifier_index ): '|| l_proc);
2858: lv_notifier_name(I-1) := p_notifier_name(I);
2853: IF I=p_notifier_index THEN
2854: hr_utility.trace('In(IF I=p_notifier_index): '|| l_proc);
2855: NULL;
2856: ELSIF I>p_notifier_index THEN
2857: hr_utility.trace('In(ELSIF I>p_notifier_index ): '|| l_proc);
2858: lv_notifier_name(I-1) := p_notifier_name(I);
2859: --loop to check if the user has checked this index
2860: lv_exist := 'N';
2861: FOR J IN 1..p_notify_onsubmit_flag.count
2892:
2893:
2894:
2895: ELSE
2896: hr_utility.trace('In else of (IF I=p_notifier_index): '|| l_proc);
2897: lv_notifier_name(I) := p_notifier_name(I);
2898: --loop to check if the user has checked this index
2899: lv_exist := 'N';
2900: FOR J IN 1..p_notify_onsubmit_flag.count
2932:
2933:
2934: END IF;
2935: END LOOP;
2936: hr_utility.trace('Out (FOR I IN 1..p_notifier_name.count): '|| l_proc);
2937: p_notifier_name := lv_notifier_name;
2938: p_notify_onsubmit_flag:= lv_notify_onsubmit_flag;
2939: p_notify_onapproval_flag := lv_notify_onapproval_flag;
2940:
2937: p_notifier_name := lv_notifier_name;
2938: p_notify_onsubmit_flag:= lv_notify_onsubmit_flag;
2939: p_notify_onapproval_flag := lv_notify_onapproval_flag;
2940:
2941: hr_utility.set_location('Leaving: '|| l_proc,25);
2942:
2943: EXCEPTION WHEN OTHERS THEN
2944: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2945: raise;
2940:
2941: hr_utility.set_location('Leaving: '|| l_proc,25);
2942:
2943: EXCEPTION WHEN OTHERS THEN
2944: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2945: raise;
2946:
2947: END delete_notifier;
2948:
2987: lv_notify_onapproval_flag hr_util_misc_web.g_varchar2_tab_type
2988: DEFAULT hr_util_misc_web.g_varchar2_tab_default;
2989: BEGIN
2990:
2991: hr_utility.set_location('Entering: '|| l_proc,5);
2992: lv_notifier_name := p_notifier_name;
2993: lv_notify_onsubmit_flag := p_notify_onsubmit_flag ;
2994: lv_notify_onapproval_flag := p_notify_onapproval_flag;
2995:
2999:
3000: p_notifier_name := lv_notifier_name;
3001: p_notify_onsubmit_flag := lv_notify_onsubmit_flag;
3002: p_notify_onapproval_flag := lv_notify_onapproval_flag;
3003: hr_utility.set_location('Leaving: '|| l_proc,10);
3004:
3005:
3006: EXCEPTION WHEN OTHERS THEN
3007: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3003: hr_utility.set_location('Leaving: '|| l_proc,10);
3004:
3005:
3006: EXCEPTION WHEN OTHERS THEN
3007: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3008: raise;
3009: END add_notifier;
3010:
3011: --
3046: l_curr_loc_name VARCHAR2(100);
3047: ln_error_count NUMBER DEFAULT 1;
3048: l_proc constant varchar2(100) := g_package || ' Get_all_notifiers';
3049: BEGIN
3050: hr_utility.set_location('Entering: '|| l_proc,5);
3051:
3052: IF gv_mode='RE-ENTER' THEN
3053: hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
3054: -- get the number of notifiers in the system
3049: BEGIN
3050: hr_utility.set_location('Entering: '|| l_proc,5);
3051:
3052: IF gv_mode='RE-ENTER' THEN
3053: hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
3054: -- get the number of notifiers in the system
3055: OPEN csr_wiav(p_item_type,p_item_key,'NOTIFIERS_NUMBER');
3056: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,NOTIFIERS_NUMBER) ):'|| l_proc);
3057: FETCH csr_wiav into l_dummy;
3052: IF gv_mode='RE-ENTER' THEN
3053: hr_utility.trace('In (IF gv_mode=RE-ENTER): '|| l_proc);
3054: -- get the number of notifiers in the system
3055: OPEN csr_wiav(p_item_type,p_item_key,'NOTIFIERS_NUMBER');
3056: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,NOTIFIERS_NUMBER) ):'|| l_proc);
3057: FETCH csr_wiav into l_dummy;
3058: IF csr_wiav%notfound THEN
3059: gn_notifiers := 0;
3060: ELSE
3065: END IF;
3066: CLOSE csr_wiav;
3067:
3068: IF gn_notifiers > 0 THEN
3069: hr_utility.trace('In( IF gn_notifiers > 0 ): '|| l_proc);
3070: --loop througthe counter and get all the notifiers
3071: hr_utility.trace('Going into( FOR I IN 1..gn_notifiers ): '|| l_proc);
3072: FOR I IN 1..gn_notifiers
3073: LOOP
3067:
3068: IF gn_notifiers > 0 THEN
3069: hr_utility.trace('In( IF gn_notifiers > 0 ): '|| l_proc);
3070: --loop througthe counter and get all the notifiers
3071: hr_utility.trace('Going into( FOR I IN 1..gn_notifiers ): '|| l_proc);
3072: FOR I IN 1..gn_notifiers
3073: LOOP
3074: lv_job_title := NULL;
3075: lv_item_name := gv_notifier_name||to_char(I);
3073: LOOP
3074: lv_job_title := NULL;
3075: lv_item_name := gv_notifier_name||to_char(I);
3076: OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
3077: hr_utility.trace('Going into( FETCH csr_wiav into l_dummy; ): '|| l_proc);
3078: FETCH csr_wiav into l_dummy;
3079: IF csr_wiav%notfound THEN
3080: lv_exist := 'N';
3081: ELSE
3093: END IF; -- for csr_wiav%notfound
3094: CLOSE csr_wiav;
3095:
3096: IF lv_exist = 'Y' THEN
3097: hr_utility.trace('In(IF lv_exist = Y): '|| l_proc);
3098: -- get the person and assignment details for this person_id
3099: -- get the assignment id
3100: lrt_assignment_details := hr_misc_web.get_assignment_id(p_person_id => ln_person_id);
3101: ln_assignment_id := lrt_assignment_details.assignment_id;
3121: lrt_notifier_details_table(I).on_approval := SUBSTR(lv_notify,3,3);
3122: END IF; -- for lv_exist = 'Y'
3123:
3124: END LOOP;
3125: hr_utility.trace('Out of ( FOR I IN 1..gn_notifiers ): '|| l_proc);
3126: END IF; -- for gn_notifiers > 0
3127:
3128: grt_notifier_details_table := lrt_notifier_details_table;
3129:
3126: END IF; -- for gn_notifiers > 0
3127:
3128: grt_notifier_details_table := lrt_notifier_details_table;
3129:
3130: hr_utility.set_location('Leaving: '|| l_proc,45);
3131: RETURN;
3132: END IF; -- for gv_mode
3133: hr_utility.trace('Going into( FOR I IN 1..p_notifier_name.count ): '|| l_proc);
3134:
3129:
3130: hr_utility.set_location('Leaving: '|| l_proc,45);
3131: RETURN;
3132: END IF; -- for gv_mode
3133: hr_utility.trace('Going into( FOR I IN 1..p_notifier_name.count ): '|| l_proc);
3134:
3135: FOR I IN 1..p_notifier_name.count
3136: LOOP
3137: lv_job_title := NULL;
3171:
3172:
3173: -- get the person id for this person
3174: OPEN lc_approver ( p_full_name=>p_notifier_name(I));
3175: hr_utility.trace('Going into Fetch after (OPEN lc_approver ( p_full_name=>p_notifier_name(I)) ): '|| l_proc);
3176: FETCH lc_approver INTO ln_person_id ;
3177: IF lc_approver%NOTFOUND
3178: THEN
3179: lv_job_title := NULL;
3204: lrt_notifier_details_table(I).job_title :=lv_job_title;
3205:
3206:
3207: END LOOP;
3208: hr_utility.trace('out of ( FOR I IN 1..p_notifier_name.count ): '|| l_proc);
3209:
3210: grt_notifier_details_table := lrt_notifier_details_table;
3211: hr_utility.set_location('Leaving: '|| l_proc,65);
3212:
3207: END LOOP;
3208: hr_utility.trace('out of ( FOR I IN 1..p_notifier_name.count ): '|| l_proc);
3209:
3210: grt_notifier_details_table := lrt_notifier_details_table;
3211: hr_utility.set_location('Leaving: '|| l_proc,65);
3212:
3213: EXCEPTION WHEN OTHERS THEN
3214: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3215: if lc_approver%isopen then
3210: grt_notifier_details_table := lrt_notifier_details_table;
3211: hr_utility.set_location('Leaving: '|| l_proc,65);
3212:
3213: EXCEPTION WHEN OTHERS THEN
3214: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3215: if lc_approver%isopen then
3216: close lc_approver;
3217: end if;
3218: if csr_wiav%isopen then
3258: ln_approver_id NUMBER;
3259: lv_response VARCHAR2(10);
3260: l_proc constant varchar2(100) := g_package || ' update_notifiers';
3261: BEGIN
3262: hr_utility.set_location('Entering: '|| l_proc,5);
3263:
3264: hr_utility.trace('Going into (FOR I IN 1..p_Notify_On_Submit.count ): '|| l_proc);
3265:
3266: FOR I IN 1..p_Notify_On_Submit.count
3260: l_proc constant varchar2(100) := g_package || ' update_notifiers';
3261: BEGIN
3262: hr_utility.set_location('Entering: '|| l_proc,5);
3263:
3264: hr_utility.trace('Going into (FOR I IN 1..p_Notify_On_Submit.count ): '|| l_proc);
3265:
3266: FOR I IN 1..p_Notify_On_Submit.count
3267: LOOP
3268: IF p_Notify_On_Submit(I) = 'Y' THEN
3281: -- update the wf_item_attributes with new response
3282: lv_item_name := gv_notifier_name||to_char(I);
3283:
3284: OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
3285: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
3286: FETCH csr_wiav into l_dummy;
3287: IF csr_wiav%notfound THEN
3288: NULL;
3289: ELSE
3299:
3300:
3301: END LOOP;
3302:
3303: hr_utility.trace('Out of (FOR I IN 1..p_Notify_On_Submit.count ): '|| l_proc);
3304: hr_utility.set_location('Leaving: '|| l_proc,25);
3305:
3306: EXCEPTION WHEN OTHERS THEN
3307: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3300:
3301: END LOOP;
3302:
3303: hr_utility.trace('Out of (FOR I IN 1..p_Notify_On_Submit.count ): '|| l_proc);
3304: hr_utility.set_location('Leaving: '|| l_proc,25);
3305:
3306: EXCEPTION WHEN OTHERS THEN
3307: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3308: if csr_wiav%isopen then
3303: hr_utility.trace('Out of (FOR I IN 1..p_Notify_On_Submit.count ): '|| l_proc);
3304: hr_utility.set_location('Leaving: '|| l_proc,25);
3305:
3306: EXCEPTION WHEN OTHERS THEN
3307: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3308: if csr_wiav%isopen then
3309: close csr_wiav;
3310: end if;
3311: raise;
3344: lv_approver_deleted VARCHAR2(20);
3345: ln_person_id NUMBER;
3346: l_proc constant varchar2(100) := g_package || ' clean_invalid_data';
3347: BEGIN
3348: hr_utility.set_location('Entering: '|| l_proc,5);
3349: ln_approver_index := gn_approver_index - 1;
3350: -- get the additional approvers number
3351: ln_num_of_add_apprs := wf_engine.GetItemAttrNumber
3352: (itemtype => p_item_type,
3355:
3356:
3357: ln_approver_index := ln_approver_index + ln_num_of_add_apprs;
3358: IF ln_num_of_add_apprs > 0 THEN
3359: hr_utility.trace('In( IF ln_num_of_add_apprs > 0 ): '|| l_proc);
3360: hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
3361: FOR I IN 1..ln_approver_index
3362: LOOP
3363: -- check if an additional approver exists by this index
3356:
3357: ln_approver_index := ln_approver_index + ln_num_of_add_apprs;
3358: IF ln_num_of_add_apprs > 0 THEN
3359: hr_utility.trace('In( IF ln_num_of_add_apprs > 0 ): '|| l_proc);
3360: hr_utility.trace('Going into( FOR I IN 1..ln_approver_index): '|| l_proc);
3361: FOR I IN 1..ln_approver_index
3362: LOOP
3363: -- check if an additional approver exists by this index
3364:
3366: -- open the cursor to determine if the item exists
3367: OPEN csr_wiav(p_item_type
3368: ,p_item_key
3369: ,lv_item_name);
3370: hr_utility.trace('Going intoFetch after (OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
3371: FETCH csr_wiav into l_dummy;
3372: IF csr_wiav%notfound THEN
3373: lv_default_approver := 'Y';
3374: ELSE
3401:
3402: END IF; -- for lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED'
3403:
3404: END LOOP;
3405: hr_utility.trace('Out of ( FOR I IN 1..ln_approver_index): '|| l_proc);
3406: END IF; -- for ln_num_of_add_apprs
3407:
3408:
3409: hr_utility.set_location('Leaving: '|| l_proc,30);
3405: hr_utility.trace('Out of ( FOR I IN 1..ln_approver_index): '|| l_proc);
3406: END IF; -- for ln_num_of_add_apprs
3407:
3408:
3409: hr_utility.set_location('Leaving: '|| l_proc,30);
3410:
3411: EXCEPTION WHEN OTHERS THEN
3412: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3413: if csr_wiav%isopen then
3408:
3409: hr_utility.set_location('Leaving: '|| l_proc,30);
3410:
3411: EXCEPTION WHEN OTHERS THEN
3412: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3413: if csr_wiav%isopen then
3414: close csr_wiav;
3415: end if;
3416: raise;
3468:
3469: --
3470:
3471: BEGIN
3472: hr_utility.set_location('Entering: '|| l_proc,5);
3473: --
3474:
3475: if ( funmode = 'RUN' ) then
3476: hr_utility.trace('In (if ( funmode = RUN )):'|| l_proc);
3472: hr_utility.set_location('Entering: '|| l_proc,5);
3473: --
3474:
3475: if ( funmode = 'RUN' ) then
3476: hr_utility.trace('In (if ( funmode = RUN )):'|| l_proc);
3477:
3478: -- get the current forward from person
3479: l_current_forward_from_id :=
3480: nvl(wf_engine.GetItemAttrNumber
3519:
3520: -- check if we need to call AME for default approvers.
3521:
3522: if(c_transaction_type is null) then
3523: hr_utility.trace('In (if(c_transaction_type is null) )):'|| l_proc);
3524: -- -----------------------------------------------------------------------
3525: -- expose the wf control variables to the custom package
3526: -- -----------------------------------------------------------------------
3527: set_custom_wf_globals
3542: -- fix for the bug # 1252070
3543:
3544: -- attribute to hold the last_default approver from the heirarchy tree.
3545: OPEN csr_wiav(itemtype,itemkey,'CURRENT_APPROVER_INDEX');
3546: hr_utility.trace('Going into Fetch after (OPEN csr_wiav(itemtype,itemkey,CURRENT_APPROVER_INDEX)):'|| l_proc);
3547: FETCH csr_wiav into lv_dummy;
3548: IF csr_wiav%notfound THEN
3549: -- create new wf_item_attribute_value to hold
3550: hr_approval_wf.create_item_attrib_if_notexist
3577: -- check if we have additional approver for the next index.
3578: -- Fix for the bug # 1255826
3579: IF ln_current_approver_index <= ln_addntl_approvers
3580: THEN
3581: hr_utility.trace('In ( IF ln_current_approver_index <= ln_addntl_approvers):'|| l_proc);
3582: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
3583: hr_utility.trace('Going into FETCH after(OPEN csr_wiav(itemtype,itemkey,lv_item_name)):'|| l_proc);
3584: FETCH csr_wiav into lv_dummy;
3585: IF csr_wiav%notfound THEN
3579: IF ln_current_approver_index <= ln_addntl_approvers
3580: THEN
3581: hr_utility.trace('In ( IF ln_current_approver_index <= ln_addntl_approvers):'|| l_proc);
3582: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
3583: hr_utility.trace('Going into FETCH after(OPEN csr_wiav(itemtype,itemkey,lv_item_name)):'|| l_proc);
3584: FETCH csr_wiav into lv_dummy;
3585: IF csr_wiav%notfound THEN
3586: lv_exists := 'N';
3587: ELSE
3594:
3595: END IF;
3596: CLOSE csr_wiav;
3597: ELSE
3598: hr_utility.trace('In else of ( IF ln_current_approver_index <= ln_addntl_approvers):'|| l_proc);
3599: lv_exists := 'N';
3600: END IF;
3601:
3602:
3600: END IF;
3601:
3602:
3603: IF lv_exists <>'N' AND lv_isvalid <>'DELETED' THEN
3604: hr_utility.trace('In ( IF lv_exists <>N AND lv_isvalid <>DELETED):'|| l_proc);
3605: l_forward_to_person_id :=
3606: wf_engine.GetItemAttrNumber
3607: (itemtype => itemtype,
3608: itemkey => itemkey,
3609: aname => lv_item_name
3610: );
3611:
3612: ELSE
3613: hr_utility.trace('In else of ( IF lv_exists <>N AND lv_isvalid <>DELETED):'|| l_proc);
3614: -- get the last default approver index
3615:
3616: ln_last_default_approver_id := wf_engine.GetItemAttrNumber
3617: (itemtype => itemtype,
3639: aname => 'LAST_DEFAULT_APPROVER',
3640: avalue => l_forward_to_person_id);
3641: -- set cuurent approval levels reached
3642: OPEN csr_wiav(itemtype,itemkey,'CURRENT_DEF_APPR_INDEX');
3643: hr_utility.trace('Going into FETCH after(OPEN csr_wiav(itemtype,itemkey,CURRENT_DEF_APPR_INDEX)):'|| l_proc);
3644: FETCH csr_wiav into lv_dummy;
3645: IF csr_wiav%notfound THEN
3646: -- create new wf_item_attribute_value to hold
3647: hr_approval_wf.create_item_attrib_if_notexist
3680: ,avalue => (ln_current_approver_index + 1));
3681:
3682: else
3683:
3684: hr_utility.trace('In else of (if(c_transaction_type is null) )):'|| l_proc);
3685: ame_api.getNextApprover(applicationIdIn =>c_application_id,
3686: transactionIdIn =>c_transaction_id,
3687: transactionTypeIn =>c_transaction_type,
3688: nextApproverOut =>c_next_approver_rec);
3754: --
3755: end if;
3756: --
3757: elsif ( funmode = 'CANCEL' ) then
3758: hr_utility.trace('In (if ( funmode = CANCEL )):'|| l_proc); --
3759: null;
3760: --
3761: end if;
3762: --
3759: null;
3760: --
3761: end if;
3762: --
3763: hr_utility.set_location('Leaving: '|| l_proc,45);
3764: EXCEPTION
3765: WHEN OTHERS THEN
3766: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3767: if csr_wiav%isopen then
3762: --
3763: hr_utility.set_location('Leaving: '|| l_proc,45);
3764: EXCEPTION
3765: WHEN OTHERS THEN
3766: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
3767: if csr_wiav%isopen then
3768: close csr_wiav;
3769: end if;
3770: END Get_Next_Approver;
3808:
3809: --
3810:
3811: BEGIN
3812: hr_utility.set_location('Entering: '|| l_proc,5);
3813: --
3814: if ( funmode = 'RUN' ) then
3815: hr_utility.trace('In(if ( funmode = RUN )): '|| l_proc);
3816: --
3811: BEGIN
3812: hr_utility.set_location('Entering: '|| l_proc,5);
3813: --
3814: if ( funmode = 'RUN' ) then
3815: hr_utility.trace('In(if ( funmode = RUN )): '|| l_proc);
3816: --
3817: --
3818: l_creator_person_id := wf_engine.GetItemAttrNumber
3819: (itemtype => p_item_type
3854: itemkey => p_item_key,
3855: aname => 'HR_AME_TRAN_TYPE_ATTR');
3856:
3857: if(c_transaction_type is not null) then
3858: hr_utility.trace('In(if ( if(c_transaction_type is not null))): '|| l_proc);
3859: -- fix for bug#2677648
3860: if(l_forward_to_person_id is not null) then
3861: -- call AME update approval status as approved
3862: ame_api.updateApprovalStatus2(applicationIdIn =>c_application_id,
3882: END IF;
3883:
3884:
3885: else
3886: hr_utility.trace('In else of ( if(c_transaction_type is not null))): '|| l_proc);
3887: l_forward_to_person_id := NVL(l_forward_to_person_id,l_current_forward_to_id);
3888:
3889:
3890:
3891: -- fix for the bug #1252070
3892:
3893: -- attribute to hold the last_default approver from the heirarchy tree.
3894: OPEN csr_wiav(p_item_type,p_item_key,'LAST_DEFAULT_APPROVER');
3895: hr_utility.trace('Going into FETCH after(OPEN csr_wiav(p_item_type,p_item_key,LAST_DEFAULT_APPROVER)): '|| l_proc);
3896: FETCH csr_wiav into l_dummy;
3897: IF csr_wiav%notfound THEN
3898: -- create new wf_item_attribute_value to hold
3899: hr_approval_wf.create_item_attrib_if_notexist
3937: result := 'COMPLETE:'||
3938: hr_approval_custom.Check_Final_approver
3939: (p_forward_to_person_id => ln_last_def_approver
3940: ,p_person_id => l_creator_person_id );
3941: hr_utility.set_location('Leaving: '|| l_proc,25);
3942: return;
3943:
3944: END IF;
3945:
3948: -- get the approval level as conifgured by the HR Rep or Sys Admin
3949: OPEN csr_wiav(p_item_type
3950: ,p_item_key
3951: ,'APPROVAL_LEVEL');
3952: hr_utility.trace('Going into FETCH after (OPEN csr_wiav(p_item_type,p_item_key,APPROVAL_LEVEL)):'|| l_proc);
3953: FETCH csr_wiav into l_dummy;
3954: IF csr_wiav%notfound THEN
3955: ln_approval_level := 0;
3956: ELSE
3963:
3964: IF ln_approval_level > 0 THEN
3965: -- get the current approval level reached
3966: -- first check if the attribute exists
3967: hr_utility.trace('In ( IF ln_approval_level > 0 THEN):'|| l_proc);
3968: OPEN csr_wiav(p_item_type
3969: ,p_item_key
3970: ,'CURRENT_DEF_APPR_INDEX');
3971: hr_utility.trace('Going into FETCH after( csr_wiav(p_item_type,p_item_key,CURRENT_DEF_APPR_INDEX)):'|| l_proc);
3967: hr_utility.trace('In ( IF ln_approval_level > 0 THEN):'|| l_proc);
3968: OPEN csr_wiav(p_item_type
3969: ,p_item_key
3970: ,'CURRENT_DEF_APPR_INDEX');
3971: hr_utility.trace('Going into FETCH after( csr_wiav(p_item_type,p_item_key,CURRENT_DEF_APPR_INDEX)):'|| l_proc);
3972: FETCH csr_wiav into l_dummy;
3973: IF csr_wiav%notfound THEN
3974: NULL;
3975: ELSE
3986: -- Fix for the Bug # 1255826
3987: IF (ln_approval_level> 0)
3988:
3989: THEN
3990: hr_utility.trace('In ( IF ln_approval_level > 0 THEN):'|| l_proc);
3991: IF( ln_curr_def_appr_index < ln_approval_level)
3992: THEN
3993:
3994: -- we have not reached the approval level as configured
3996: ELSE
3997: result := 'COMPLETE:'||'Y';
3998: END IF;
3999: ELSE
4000: hr_utility.trace('In Eelse of ( IF ln_approval_level > 0 THEN):'|| l_proc);
4001: result := 'COMPLETE:'||
4002: hr_approval_custom.Check_Final_approver
4003: (p_forward_to_person_id => ln_last_def_approver
4004: ,p_person_id => l_creator_person_id );
4006:
4007: end if ; -- check for AME
4008:
4009: elsif ( funmode = 'CANCEL' ) then
4010: hr_utility.trace('In(if ( funmode = CANCEL )): '|| l_proc);
4011:
4012: --
4013: null;
4014: --
4012: --
4013: null;
4014: --
4015: end if;
4016: hr_utility.set_location('Leaving: '|| l_proc,50);
4017:
4018:
4019: EXCEPTION
4020: WHEN OTHERS THEN
4017:
4018:
4019: EXCEPTION
4020: WHEN OTHERS THEN
4021: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4022: if csr_wiav%isopen then
4023: close csr_wiav;
4024: end if;
4025: END Check_Final_Approver ;
4049: lv_notify VARCHAR2(10);
4050: lv_submit VARCHAR2(10);
4051: l_proc constant varchar2(100) := g_package || ' Check_OnSubmit_Notifier';
4052: BEGIN
4053: hr_utility.set_location('Entering: '|| l_proc,5);
4054: --
4055: if ( funmode = 'RUN' ) then
4056: hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4057: --
4052: BEGIN
4053: hr_utility.set_location('Entering: '|| l_proc,5);
4054: --
4055: if ( funmode = 'RUN' ) then
4056: hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4057: --
4058: --
4059: -- get the total number of notifiers for the trnsaction
4060: OPEN csr_wiav(itemtype,itemkey,'NOTIFIERS_NUMBER');
4057: --
4058: --
4059: -- get the total number of notifiers for the trnsaction
4060: OPEN csr_wiav(itemtype,itemkey,'NOTIFIERS_NUMBER');
4061: hr_utility.trace('Going into FETCH after( OPEN csr_wiav(itemtype,itemkey,NOTIFIERS_NUMBER)):'|| l_proc);
4062: FETCH csr_wiav into lv_dummy;
4063: IF csr_wiav%notfound THEN
4064: ln_number_of_notifiers := 0;
4065: ELSE
4074:
4075: -- get the current index of the onapproval notifier
4076:
4077: OPEN csr_wiav(itemtype,itemkey,'CURRENT_ONSUBMIT_INDEX');
4078: hr_utility.trace('Going into FETCH after( OPEN csr_wiav(itemtype,itemkey,CURRENT_ONSUBMIT_INDEX)):'|| l_proc);
4079: FETCH csr_wiav into lv_dummy;
4080: IF csr_wiav%notfound THEN
4081: lv_exists := 'N';
4082: ln_current_index := 1;
4094: ln_current_index := ln_current_index +1;
4095: -- check if there are any notifiers
4096:
4097: IF ln_number_of_notifiers > 0 THEN
4098: hr_utility.trace('In(IF ln_number_of_notifiers > 0 ):'|| l_proc);
4099: IF ln_current_index <= ln_number_of_notifiers THEN
4100: hr_utility.trace('In( IF ln_current_index <= ln_number_of_notifiers ):'|| l_proc);
4101: -- loop through all the notifiers to check status
4102: hr_utility.trace('Going into( FOR I in ln_current_index..ln_number_of_notifiers ):'|| l_proc);
4096:
4097: IF ln_number_of_notifiers > 0 THEN
4098: hr_utility.trace('In(IF ln_number_of_notifiers > 0 ):'|| l_proc);
4099: IF ln_current_index <= ln_number_of_notifiers THEN
4100: hr_utility.trace('In( IF ln_current_index <= ln_number_of_notifiers ):'|| l_proc);
4101: -- loop through all the notifiers to check status
4102: hr_utility.trace('Going into( FOR I in ln_current_index..ln_number_of_notifiers ):'|| l_proc);
4103: FOR I in ln_current_index..ln_number_of_notifiers
4104: LOOP
4098: hr_utility.trace('In(IF ln_number_of_notifiers > 0 ):'|| l_proc);
4099: IF ln_current_index <= ln_number_of_notifiers THEN
4100: hr_utility.trace('In( IF ln_current_index <= ln_number_of_notifiers ):'|| l_proc);
4101: -- loop through all the notifiers to check status
4102: hr_utility.trace('Going into( FOR I in ln_current_index..ln_number_of_notifiers ):'|| l_proc);
4103: FOR I in ln_current_index..ln_number_of_notifiers
4104: LOOP
4105: lv_item_name := gv_notifier_name||to_char(I);
4106:
4104: LOOP
4105: lv_item_name := gv_notifier_name||to_char(I);
4106:
4107: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4108: hr_utility.trace('Going into FETCH after (OPEN csr_wiav(itemtype,itemkey,lv_item_name)):'|| l_proc);
4109: FETCH csr_wiav into lv_dummy;
4110: IF csr_wiav%notfound THEN
4111: lv_exists := 'N';
4112: ELSE
4128: result := 'COMPLETE:'||'Y';
4129: END IF;
4130:
4131: END LOOP;
4132: hr_utility.trace('Out of ( FOR I in ln_current_index..ln_number_of_notifiers ):'|| l_proc);
4133: ELSE
4134: hr_utility.trace('In else of ( IF ln_current_index <= ln_number_of_notifiers ):'|| l_proc);
4135: result := 'COMPLETE:'||'Y';
4136: END IF; -- for ln_current_index < ln_number_of_notifiers
4130:
4131: END LOOP;
4132: hr_utility.trace('Out of ( FOR I in ln_current_index..ln_number_of_notifiers ):'|| l_proc);
4133: ELSE
4134: hr_utility.trace('In else of ( IF ln_current_index <= ln_number_of_notifiers ):'|| l_proc);
4135: result := 'COMPLETE:'||'Y';
4136: END IF; -- for ln_current_index < ln_number_of_notifiers
4137:
4138:
4136: END IF; -- for ln_current_index < ln_number_of_notifiers
4137:
4138:
4139: ELSE
4140: hr_utility.trace('In else of (IF ln_number_of_notifiers > 0 ):'|| l_proc);
4141: result := 'COMPLETE:'||'Y';
4142: END IF; -- for ln_number_of_notifiers > 0
4143:
4144: --
4143:
4144: --
4145: elsif ( funmode = 'CANCEL' ) then
4146:
4147: hr_utility.trace('In elsif ( funmode = CANCEL ) then): '|| l_proc);
4148: --
4149: null;
4150: --
4151: end if;
4149: null;
4150: --
4151: end if;
4152:
4153: hr_utility.set_location('Leaving: '|| l_proc,50);
4154: EXCEPTION
4155: WHEN OTHERS THEN
4156: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4157: if csr_wiav%isopen then
4152:
4153: hr_utility.set_location('Leaving: '|| l_proc,50);
4154: EXCEPTION
4155: WHEN OTHERS THEN
4156: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4157: if csr_wiav%isopen then
4158: close csr_wiav;
4159: end if;
4160:
4185: lv_notify VARCHAR2(10);
4186: lv_onapproval VARCHAR2(10);
4187: l_proc constant varchar2(100) := g_package || ' Check_OnApproval_Notifier';
4188: BEGIN
4189: hr_utility.set_location('Entering: '|| l_proc,5);
4190: --
4191: if ( funmode = 'RUN' ) then
4192: --
4193: --
4190: --
4191: if ( funmode = 'RUN' ) then
4192: --
4193: --
4194: hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4195: -- get the total number of notifiers for the trnsaction
4196: OPEN csr_wiav(itemtype,itemkey,'NOTIFIERS_NUMBER');
4197: hr_utility.trace('Going into ( FETCH csr_wiav into lv_dummy;):'|| l_proc);
4198: FETCH csr_wiav into lv_dummy;
4193: --
4194: hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4195: -- get the total number of notifiers for the trnsaction
4196: OPEN csr_wiav(itemtype,itemkey,'NOTIFIERS_NUMBER');
4197: hr_utility.trace('Going into ( FETCH csr_wiav into lv_dummy;):'|| l_proc);
4198: FETCH csr_wiav into lv_dummy;
4199: IF csr_wiav%notfound THEN
4200: ln_number_of_notifiers := 0;
4201: ELSE
4210:
4211: -- get the current index of the onapproval notifier
4212:
4213: OPEN csr_wiav(itemtype,itemkey,'CURRENT_ONAPPROVAL_INDEX');
4214: hr_utility.trace('Going into ( FETCH after OPEN csr_wiav(itemtype,itemkey,CURRENT_ONAPPROVAL_INDEX):'|| l_proc);
4215: FETCH csr_wiav into lv_dummy;
4216: IF csr_wiav%notfound THEN
4217: lv_exists := 'N';
4218: ln_current_index := 1;
4231:
4232: -- check if there are any notifiers
4233:
4234: IF ln_number_of_notifiers > 0 THEN
4235: hr_utility.trace('In ( IF ln_number_of_notifiers > 0): '|| l_proc);
4236: IF ln_current_index <= ln_number_of_notifiers THEN
4237: hr_utility.trace('In (IF ln_current_index <= ln_number_of_notifiers): '|| l_proc);
4238: -- loop through all the notifiers to check status
4239: hr_utility.trace('Going into ( FOR I in ln_current_index..ln_number_of_notifiers): '|| l_proc);
4233:
4234: IF ln_number_of_notifiers > 0 THEN
4235: hr_utility.trace('In ( IF ln_number_of_notifiers > 0): '|| l_proc);
4236: IF ln_current_index <= ln_number_of_notifiers THEN
4237: hr_utility.trace('In (IF ln_current_index <= ln_number_of_notifiers): '|| l_proc);
4238: -- loop through all the notifiers to check status
4239: hr_utility.trace('Going into ( FOR I in ln_current_index..ln_number_of_notifiers): '|| l_proc);
4240: FOR I in ln_current_index..ln_number_of_notifiers
4241: LOOP
4235: hr_utility.trace('In ( IF ln_number_of_notifiers > 0): '|| l_proc);
4236: IF ln_current_index <= ln_number_of_notifiers THEN
4237: hr_utility.trace('In (IF ln_current_index <= ln_number_of_notifiers): '|| l_proc);
4238: -- loop through all the notifiers to check status
4239: hr_utility.trace('Going into ( FOR I in ln_current_index..ln_number_of_notifiers): '|| l_proc);
4240: FOR I in ln_current_index..ln_number_of_notifiers
4241: LOOP
4242: lv_item_name := gv_notifier_name||to_char(I);
4243:
4241: LOOP
4242: lv_item_name := gv_notifier_name||to_char(I);
4243:
4244: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4245: hr_utility.trace('Going Fetch after (OPEN csr_wiav(itemtype,itemkey,lv_item_name);): '|| l_proc);
4246: FETCH csr_wiav into lv_dummy;
4247: IF csr_wiav%notfound THEN
4248:
4249:
4261: lv_onapproval:= SUBSTR(lv_notify,3,3);
4262: END IF; -- for lv_exists = 'Y'
4263: IF lv_onapproval='Y' THEN
4264: result := 'COMPLETE:'||'N';
4265: hr_utility.set_location('Leaving: '|| l_proc,45);
4266: return;
4267: ELSE
4268: result := 'COMPLETE:'||'Y';
4269: END IF;
4268: result := 'COMPLETE:'||'Y';
4269: END IF;
4270:
4271: END LOOP;
4272: hr_utility.trace('Out of ( FOR I in ln_current_index..ln_number_of_notifiers): '|| l_proc);
4273: ELSE
4274: hr_utility.trace('In else of (IF ln_current_index <= ln_number_of_notifiers): '|| l_proc);
4275: result := 'COMPLETE:'||'Y';
4276: END IF; -- for ln_current_index < ln_number_of_notifiers
4270:
4271: END LOOP;
4272: hr_utility.trace('Out of ( FOR I in ln_current_index..ln_number_of_notifiers): '|| l_proc);
4273: ELSE
4274: hr_utility.trace('In else of (IF ln_current_index <= ln_number_of_notifiers): '|| l_proc);
4275: result := 'COMPLETE:'||'Y';
4276: END IF; -- for ln_current_index < ln_number_of_notifiers
4277:
4278:
4276: END IF; -- for ln_current_index < ln_number_of_notifiers
4277:
4278:
4279: ELSE
4280: hr_utility.trace('In else of ( IF ln_number_of_notifiers > 0): '|| l_proc);
4281: result := 'COMPLETE:'||'Y';
4282: END IF; -- for ln_number_of_notifiers > 0
4283:
4284: --
4283:
4284: --
4285:
4286: elsif ( funmode = 'CANCEL' ) then
4287: hr_utility.trace('In (elsif ( funmode = CANCEL )): '|| l_proc);
4288: --
4289: null;
4290: --
4291: end if;
4289: null;
4290: --
4291: end if;
4292:
4293: hr_utility.set_location('Leaving: '|| l_proc,55);
4294: --
4295:
4296: EXCEPTION
4297: WHEN OTHERS THEN
4294: --
4295:
4296: EXCEPTION
4297: WHEN OTHERS THEN
4298: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4299: if csr_wiav%isopen then
4300: close csr_wiav;
4301: end if;
4302:
4346: lv_item_name VARCHAR2(25);
4347: ln_start_index NUMBER;
4348:
4349: BEGIN
4350: hr_utility.set_location('Entering: '|| l_proc,5);
4351: if ( funmode = 'RUN' ) then
4352: hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4353: -- get the total number ofnotifiers
4354: ln_notifiers := wf_engine.GetItemAttrNumber
4348:
4349: BEGIN
4350: hr_utility.set_location('Entering: '|| l_proc,5);
4351: if ( funmode = 'RUN' ) then
4352: hr_utility.trace('In (if ( funmode = RUN )): '|| l_proc);
4353: -- get the total number ofnotifiers
4354: ln_notifiers := wf_engine.GetItemAttrNumber
4355: (itemtype => itemtype,
4356: itemkey => itemkey,
4364: ,itemkey => itemkey
4365: ,aname => 'CURRENT_ONSUBMIT_INDEX'),0);
4366:
4367: ELSE
4368: hr_utility.set_location('Leaving: '|| l_proc,15);
4369: result := 'COMPLETE:F';
4370: return;
4371: END IF;
4372:
4379: ,p_itemkey => itemkey);
4380:
4381:
4382: -- loop through and get next notifier
4383: hr_utility.trace('Going into ( FOR I in (ln_current_onsubmit_index + 1)..ln_notifiers): '|| l_proc);
4384: FOR I in (ln_current_onsubmit_index + 1)..ln_notifiers
4385: LOOP
4386: lv_item_name := gv_notifier_name||to_char(I);
4387: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4384: FOR I in (ln_current_onsubmit_index + 1)..ln_notifiers
4385: LOOP
4386: lv_item_name := gv_notifier_name||to_char(I);
4387: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4388: hr_utility.trace('In Fetch after ( OPEN csr_wiav(itemtype,itemkey,lv_item_name)): '|| l_proc);
4389: FETCH csr_wiav into lv_dummy;
4390: IF csr_wiav%notfound THEN
4391: lv_exists := 'N';
4392: ELSE
4404: END IF; -- for csr_wiav%notfound
4405: CLOSE csr_wiav;
4406:
4407: IF lv_exists = 'Y' THEN
4408: hr_utility.trace('In ( IF lv_exists = Y): '|| l_proc);
4409: lv_onsubmit := SUBSTR(lv_notify,1,1);
4410: IF lv_onsubmit= 'Y' THEN
4411: wf_engine.SetItemAttrNumber
4412: (itemtype => itemtype,
4421: result := 'COMPLETE:F';
4422:
4423: END IF;
4424: ELSE
4425: hr_utility.trace('In else of ( IF lv_exists = Y): '|| l_proc);
4426: l_forward_to_person_id := NULL;
4427: result := 'COMPLETE:F';
4428:
4429: END IF;
4428:
4429: END IF;
4430:
4431: END LOOP;
4432: hr_utility.trace('Out of ( FOR I in (ln_current_onsubmit_index + 1)..ln_notifiers): '|| l_proc);
4433: --
4434: -- set the next forward to
4435: --
4436:
4484: --
4485: end if;
4486: --
4487: elsif ( funmode = 'CANCEL' ) then
4488: hr_utility.trace('In elsif ( funmode = CANCEL )): '|| l_proc);
4489: --
4490: null;
4491: --
4492: end if;
4489: --
4490: null;
4491: --
4492: end if;
4493: hr_utility.set_location('Leaving: '|| l_proc,40);
4494:
4495: --
4496:
4497: EXCEPTION
4495: --
4496:
4497: EXCEPTION
4498: WHEN OTHERS THEN
4499: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4500: if csr_wiav%isopen then
4501: close csr_wiav;
4502: end if;
4503: END Get_OnSubmit_Notifier;
4547: lv_item_name VARCHAR2(25);
4548: ln_start_index NUMBER;
4549:
4550: BEGIN
4551: hr_utility.set_location('Entering: '|| l_proc,5);
4552:
4553: if ( funmode = 'RUN' ) then
4554: hr_utility.trace('In (if ( funmode = RUN) ): '|| l_proc);
4555:
4550: BEGIN
4551: hr_utility.set_location('Entering: '|| l_proc,5);
4552:
4553: if ( funmode = 'RUN' ) then
4554: hr_utility.trace('In (if ( funmode = RUN) ): '|| l_proc);
4555:
4556: -- get the total number ofnotifiers
4557: ln_notifiers := wf_engine.GetItemAttrNumber
4558: (itemtype => itemtype,
4569: ,aname => 'CURRENT_ONAPPROVAL_INDEX'),0);
4570:
4571: ELSE
4572: result := 'COMPLETE:F';
4573: hr_utility.set_location('Leaving: '|| l_proc,15);
4574: return;
4575: END IF;
4576:
4577:
4583: ,p_itemkey => itemkey);
4584:
4585:
4586: -- loop through and get next notifier
4587: hr_utility.trace('Going into ( FOR I in (ln_current_onapproval_index + 1)..ln_notifiers): '|| l_proc);
4588:
4589: FOR I in (ln_current_onapproval_index + 1)..ln_notifiers
4590: LOOP
4591: lv_item_name := gv_notifier_name||to_char(I);
4589: FOR I in (ln_current_onapproval_index + 1)..ln_notifiers
4590: LOOP
4591: lv_item_name := gv_notifier_name||to_char(I);
4592: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4593: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(itemtype,itemkey,lv_item_name)): '|| l_proc);
4594: FETCH csr_wiav into lv_dummy;
4595: IF csr_wiav%notfound THEN
4596: lv_exists := 'N';
4597: ELSE
4609: END IF; -- for csr_wiav%notfound
4610: CLOSE csr_wiav;
4611:
4612: IF lv_exists = 'Y' THEN
4613: hr_utility.trace('In (IF lv_exists = Y): '|| l_proc);
4614: lv_onapproval := SUBSTR(lv_notify,3,3);
4615: IF lv_onapproval= 'Y' THEN
4616: hr_utility.trace('In (IF lv_onapproval = Y): '|| l_proc);
4617: wf_engine.SetItemAttrNumber
4612: IF lv_exists = 'Y' THEN
4613: hr_utility.trace('In (IF lv_exists = Y): '|| l_proc);
4614: lv_onapproval := SUBSTR(lv_notify,3,3);
4615: IF lv_onapproval= 'Y' THEN
4616: hr_utility.trace('In (IF lv_onapproval = Y): '|| l_proc);
4617: wf_engine.SetItemAttrNumber
4618: (itemtype => itemtype,
4619: itemkey => itemkey,
4620: aname => 'CURRENT_ONAPPROVAL_INDEX',
4622: );
4623: l_forward_to_person_id := ln_person_id;
4624: EXIT;
4625: ELSE
4626: hr_utility.trace('In else of (IF lv_exists = Y): '|| l_proc);
4627: l_forward_to_person_id := NULL;
4628: result := 'COMPLETE:F';
4629:
4630: END IF;
4634:
4635: END IF;
4636:
4637: END LOOP;
4638: hr_utility.trace('Out of (FOR I in (ln_current_onapproval_index + 1)..ln_notifiers): '|| l_proc);
4639: --
4640: -- set the next forward to
4641: --
4642:
4695: --
4696: end if;
4697: --
4698: elsif ( funmode = 'CANCEL' ) then
4699: hr_utility.trace('In (elsif ( funmode = CANCEL ) ): '|| l_proc);
4700: --
4701: null;
4702: --
4703: end if;
4700: --
4701: null;
4702: --
4703: end if;
4704: hr_utility.set_location('Leaving: '|| l_proc,45);
4705:
4706: EXCEPTION
4707: WHEN OTHERS THEN
4708: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4704: hr_utility.set_location('Leaving: '|| l_proc,45);
4705:
4706: EXCEPTION
4707: WHEN OTHERS THEN
4708: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4709: if csr_wiav%isopen then
4710: close csr_wiav;
4711: end if;
4712: END Get_OnApproval_Notifier;
4737: l_proc constant varchar2(100) := g_package || ' set_first_onsubmit_person';
4738: --
4739: begin
4740: -- check the workflow funmode value
4741: hr_utility.set_location('Entering: '|| l_proc,5);
4742: if funmode = 'RUN' then
4743: hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4744: -- workflow is RUNing this procedure
4745: --
4739: begin
4740: -- check the workflow funmode value
4741: hr_utility.set_location('Entering: '|| l_proc,5);
4742: if funmode = 'RUN' then
4743: hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4744: -- workflow is RUNing this procedure
4745: --
4746: --
4747: -- get the total number of notifiers
4749: wf_engine.GetItemAttrNumber(itemtype => itemtype,
4750: itemkey => itemkey,
4751: aname => 'NOTIFIERS_NUMBER');
4752: -- loop through the notifiers to get the first on submit notifier
4753: hr_utility.trace('Going into( FOR I IN 1..ln_notifiers): '|| l_proc);
4754: FOR I IN 1..ln_notifiers
4755: LOOP
4756: lv_item_name := gv_notifier_name||to_char(I);
4757: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4754: FOR I IN 1..ln_notifiers
4755: LOOP
4756: lv_item_name := gv_notifier_name||to_char(I);
4757: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4758: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(itemtype,itemkey,lv_item_name);): '|| l_proc);
4759: FETCH csr_wiav into l_dummy;
4760: IF csr_wiav%notfound THEN
4761: lv_exist := 'N';
4762: ELSE
4773: );
4774: END IF; -- for csr_wiav%notfound
4775: CLOSE csr_wiav;
4776: IF lv_exist = 'Y' THEN
4777: hr_utility.trace('In ( IF lv_exist = Y): '|| l_proc);
4778: lv_onsubmit := SUBSTR(lv_notify,1,1);
4779: IF lv_onsubmit= 'Y' THEN
4780: hr_utility.trace('In (IF lv_onsubmit= Y): '|| l_proc);
4781: -- set the person id and start index
4776: IF lv_exist = 'Y' THEN
4777: hr_utility.trace('In ( IF lv_exist = Y): '|| l_proc);
4778: lv_onsubmit := SUBSTR(lv_notify,1,1);
4779: IF lv_onsubmit= 'Y' THEN
4780: hr_utility.trace('In (IF lv_onsubmit= Y): '|| l_proc);
4781: -- set the person id and start index
4782: wf_engine.SetItemAttrNumber
4783: (itemtype => itemtype,
4784: itemkey => itemkey,
4796: EXIT;
4797: END IF;
4798: END IF;
4799: END LOOP;
4800: hr_utility.trace('Out of ( FOR I IN 1..ln_notifiers): '|| l_proc);
4801: elsif funmode = 'CANCEL' then
4802: -- workflow is calling in cancel mode (performing a loop reset) so ignore
4803: hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
4804: null;
4799: END LOOP;
4800: hr_utility.trace('Out of ( FOR I IN 1..ln_notifiers): '|| l_proc);
4801: elsif funmode = 'CANCEL' then
4802: -- workflow is calling in cancel mode (performing a loop reset) so ignore
4803: hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
4804: null;
4805: end if;
4806: hr_utility.set_location('Leaving: '|| l_proc,40);
4807:
4802: -- workflow is calling in cancel mode (performing a loop reset) so ignore
4803: hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
4804: null;
4805: end if;
4806: hr_utility.set_location('Leaving: '|| l_proc,40);
4807:
4808:
4809: EXCEPTION
4810: WHEN OTHERS THEN
4807:
4808:
4809: EXCEPTION
4810: WHEN OTHERS THEN
4811: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4812: if csr_wiav%isopen then
4813: close csr_wiav;
4814: end if;
4815: end set_first_onsubmit_person;
4838: lv_item_name VARCHAR2(25);
4839: l_proc constant varchar2(100) := g_package || ' set_first_onapproval_person';
4840: --
4841: begin
4842: hr_utility.set_location('Entering: '|| l_proc,5);
4843: -- check the workflow funmode value
4844: if funmode = 'RUN' then
4845: hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4846: -- workflow is RUNing this procedure
4841: begin
4842: hr_utility.set_location('Entering: '|| l_proc,5);
4843: -- check the workflow funmode value
4844: if funmode = 'RUN' then
4845: hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4846: -- workflow is RUNing this procedure
4847: --
4848: --
4849: -- get the total number of notifiers
4851: wf_engine.GetItemAttrNumber(itemtype => itemtype,
4852: itemkey => itemkey,
4853: aname => 'NOTIFIERS_NUMBER');
4854: -- loop through the notifiers to get the first on submit notifier
4855: hr_utility.trace('Going into (FOR I IN 1..ln_notifiers): '|| l_proc);
4856: FOR I IN 1..ln_notifiers
4857: LOOP
4858: lv_item_name := gv_notifier_name||to_char(I);
4859: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4856: FOR I IN 1..ln_notifiers
4857: LOOP
4858: lv_item_name := gv_notifier_name||to_char(I);
4859: OPEN csr_wiav(itemtype,itemkey,lv_item_name);
4860: hr_utility.trace('Going into Fetch after ( FETCH csr_wiav into l_dummy;): '|| l_proc);
4861: FETCH csr_wiav into l_dummy;
4862: IF csr_wiav%notfound THEN
4863: lv_exist := 'N';
4864: ELSE
4876: END IF; -- for csr_wiav%notfound
4877: CLOSE csr_wiav;
4878:
4879: IF lv_exist = 'Y' THEN
4880: hr_utility.trace('In(IF lv_exist = Y): '|| l_proc);
4881: lv_onsubmit :=SUBSTR(lv_notify,3,3);
4882: IF lv_onsubmit= 'Y' THEN
4883: hr_utility.trace('In(IF lv_onsubmit = Y): '|| l_proc);
4884: -- set the person id and start index
4879: IF lv_exist = 'Y' THEN
4880: hr_utility.trace('In(IF lv_exist = Y): '|| l_proc);
4881: lv_onsubmit :=SUBSTR(lv_notify,3,3);
4882: IF lv_onsubmit= 'Y' THEN
4883: hr_utility.trace('In(IF lv_onsubmit = Y): '|| l_proc);
4884: -- set the person id and start index
4885: wf_engine.SetItemAttrNumber
4886: (itemtype => itemtype,
4887: itemkey => itemkey,
4899: EXIT;
4900: END IF;
4901: END IF;
4902: END LOOP;
4903: hr_utility.trace('Out of (FOR I IN 1..ln_notifiers): '|| l_proc);
4904: elsif funmode = 'CANCEL' then
4905: -- workflow is calling in cancel mode (performing a loop reset) so ignore
4906: hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
4907: null;
4902: END LOOP;
4903: hr_utility.trace('Out of (FOR I IN 1..ln_notifiers): '|| l_proc);
4904: elsif funmode = 'CANCEL' then
4905: -- workflow is calling in cancel mode (performing a loop reset) so ignore
4906: hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
4907: null;
4908: end if;
4909: hr_utility.set_location('Leaving: '|| l_proc,45);
4910:
4905: -- workflow is calling in cancel mode (performing a loop reset) so ignore
4906: hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
4907: null;
4908: end if;
4909: hr_utility.set_location('Leaving: '|| l_proc,45);
4910:
4911: EXCEPTION
4912: WHEN OTHERS THEN
4913: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4909: hr_utility.set_location('Leaving: '|| l_proc,45);
4910:
4911: EXCEPTION
4912: WHEN OTHERS THEN
4913: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4914: if csr_wiav%isopen then
4915: close csr_wiav;
4916: end if;
4917: END set_first_onapproval_person;
4940: -- Local Variables
4941: l_dummy VARCHAR2(100);
4942: l_proc constant varchar2(100) := g_package || ' initialize_item_attributes';
4943: BEGIN
4944: hr_utility.set_location('Entering: '|| l_proc,5);
4945: -- check the workflow funmode value
4946:
4947: if funmode = 'RUN' then
4948: hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4944: hr_utility.set_location('Entering: '|| l_proc,5);
4945: -- check the workflow funmode value
4946:
4947: if funmode = 'RUN' then
4948: hr_utility.trace('In( if funmode = RUN): '|| l_proc);
4949: -- workflow is RUNing this procedure
4950: --
4951: --
4952: -- Test that all new attributes exist and if they don't create them
4960:
4961:
4962: -- attribute to hold the approval levels for confguration.
4963: OPEN csr_wiav(itemtype,itemkey,'APPROVAL_LEVEL');
4964: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(itemtype,itemkey,APPROVAL_LEVEL)): '|| l_proc);
4965: FETCH csr_wiav into l_dummy;
4966: IF csr_wiav%notfound THEN
4967: -- create new wf_item_attribute_value to hold
4968: hr_approval_wf.create_item_attrib_if_notexist
4975:
4976:
4977: -- attribute to hold the current default approver index .
4978: OPEN csr_wiav(itemtype,itemkey,'CURRENT_DEF_APPR_INDEX');
4979: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(itemtype,itemkey,CURRENT_DEF_APPR_INDEX)): '|| l_proc);
4980: FETCH csr_wiav into l_dummy;
4981: IF csr_wiav%notfound THEN
4982: -- create new wf_item_attribute_value to hold
4983: hr_approval_wf.create_item_attrib_if_notexist
5124: --result := 'COMPLETE:SUCCESS';
5125: --
5126: elsif funmode = 'CANCEL' then
5127: -- workflow is calling in cancel mode (performing a loop reset) so ignore
5128: hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
5129: null;
5130: end if;
5131: hr_utility.set_location('Leaving: '|| l_proc,25);
5132:
5127: -- workflow is calling in cancel mode (performing a loop reset) so ignore
5128: hr_utility.trace('In( elsif funmode = CANCEL): '|| l_proc);
5129: null;
5130: end if;
5131: hr_utility.set_location('Leaving: '|| l_proc,25);
5132:
5133:
5134: EXCEPTION
5135: WHEN OTHERS THEN
5132:
5133:
5134: EXCEPTION
5135: WHEN OTHERS THEN
5136: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5137: if csr_wiav%isopen then
5138: close csr_wiav;
5139: end if;
5140: END initialize_item_attributes;
5149: AS
5150: -- local variables
5151: l_proc constant varchar2(100) := g_package || ' set_ame_attributes';
5152: begin
5153: hr_utility.set_location('Entering: '|| l_proc,5);
5154: wf_engine.SetItemAttrNumber(itemtype => itemtype ,
5155: itemkey => itemkey,
5156: aname => 'HR_AME_APP_ID_ATTR',
5157: avalue=>800);
5161: itemkey => itemkey,
5162: aname => 'HR_AME_TRAN_TYPE_ATTR',
5163: avalue=> 'SSHRMS');
5164:
5165: hr_utility.set_location('Leaving: '|| l_proc,10);
5166: end set_ame_attributes;
5167:
5168:
5169: -- ---------------------------------------------------------------------------
5196: -- local variable
5197: l_proc constant varchar2(100) := g_package || ' Notify';
5198:
5199: begin
5200: hr_utility.set_location('Entering: '|| l_proc,5);
5201:
5202:
5203: -- Do nothing in cancel or timeout mode
5204: if (funcmode <> wf_engine.eng_run) then
5202:
5203: -- Do nothing in cancel or timeout mode
5204: if (funcmode <> wf_engine.eng_run) then
5205: resultout := wf_engine.eng_null;
5206: hr_utility.set_location('Leaving: '|| l_proc,10);
5207: return;
5208: end if;
5209:
5210:
5250: --NULL if notification is FYI
5251: --NOTIFIED:notid:role if notification requires responce
5252:
5253: --resultout := null;
5254: hr_utility.set_location('Leaving: '|| l_proc,15);
5255: exception
5256: when others then
5257: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5258: Wf_Core.Context('Wf_Standard', 'Notify', itemtype,
5253: --resultout := null;
5254: hr_utility.set_location('Leaving: '|| l_proc,15);
5255: exception
5256: when others then
5257: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5258: Wf_Core.Context('Wf_Standard', 'Notify', itemtype,
5259: itemkey, to_char(actid), funcmode);
5260: raise;
5261: end Notify;
5290: l_no_approvers_list hr_dynamic_approver_list_ss := hr_dynamic_approver_list_ss();
5291: ln_transaction_id hr_api_transactions.transaction_id%type;
5292: l_proc constant varchar2(100) := g_package || ' get_ame_approvers_list';
5293: BEGIN
5294: hr_utility.set_location('Entering: '|| l_proc,5);
5295:
5296: -- remove all rows from person details table
5297:
5298: grt_approver_details_table.DELETE;
5300: -- set the gv_mode as this is needed for pl/sql compatibility
5301: gv_mode:='RE-ENTER';
5302:
5303: -- repopulate the table
5304: hr_utility.trace('calling get_all_ame_approvers ');
5305:
5306: get_all_ame_approvers(p_approver_name =>l_approver_name,
5307: p_approver_flag =>l_approver_flag,
5308: p_item_type =>p_item_type,
5312:
5313: -- copy parameters into l_default_approvers_list
5314: lv_number := grt_approver_details_table.count;
5315:
5316: hr_utility.trace('approver count retuned from get_all_ame_approvers :'||nvl(lv_number,0));
5317: hr_utility.trace('Going into ( FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
5318: FOR I IN 1..grt_approver_details_table.count
5319: LOOP
5320: hr_utility.trace('building approvers out nocopy list using hr_dynamic_approver_ss');
5313: -- copy parameters into l_default_approvers_list
5314: lv_number := grt_approver_details_table.count;
5315:
5316: hr_utility.trace('approver count retuned from get_all_ame_approvers :'||nvl(lv_number,0));
5317: hr_utility.trace('Going into ( FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
5318: FOR I IN 1..grt_approver_details_table.count
5319: LOOP
5320: hr_utility.trace('building approvers out nocopy list using hr_dynamic_approver_ss');
5321: hr_utility.trace(' Adding approver :'||grt_approver_details_table(I).person_id||' to the list');
5316: hr_utility.trace('approver count retuned from get_all_ame_approvers :'||nvl(lv_number,0));
5317: hr_utility.trace('Going into ( FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
5318: FOR I IN 1..grt_approver_details_table.count
5319: LOOP
5320: hr_utility.trace('building approvers out nocopy list using hr_dynamic_approver_ss');
5321: hr_utility.trace(' Adding approver :'||grt_approver_details_table(I).person_id||' to the list');
5322: l_default_approver := hr_dynamic_approver_ss(
5323: grt_approver_details_table(I).full_name,
5324: grt_approver_details_table(I).person_id,
5317: hr_utility.trace('Going into ( FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
5318: FOR I IN 1..grt_approver_details_table.count
5319: LOOP
5320: hr_utility.trace('building approvers out nocopy list using hr_dynamic_approver_ss');
5321: hr_utility.trace(' Adding approver :'||grt_approver_details_table(I).person_id||' to the list');
5322: l_default_approver := hr_dynamic_approver_ss(
5323: grt_approver_details_table(I).full_name,
5324: grt_approver_details_table(I).person_id,
5325: grt_approver_details_table(I).job_title,
5332: -- add to list
5333: l_default_approvers_list(I) := l_default_approver;
5334:
5335: END LOOP;
5336: hr_utility.trace('Out of ( FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
5337: -- set out parameter
5338: hr_utility.trace('setting the out nocopy parameter p_default_approvers_list');
5339: p_default_approvers_list := l_default_approvers_list;
5340:
5334:
5335: END LOOP;
5336: hr_utility.trace('Out of ( FOR I IN 1..grt_approver_details_table.count ): '|| l_proc);
5337: -- set out parameter
5338: hr_utility.trace('setting the out nocopy parameter p_default_approvers_list');
5339: p_default_approvers_list := l_default_approvers_list;
5340:
5341: if(grt_approver_details_table.count=1) then
5342: hr_utility.trace('In( if(grt_approver_details_table.count=1) ): '|| l_proc);
5338: hr_utility.trace('setting the out nocopy parameter p_default_approvers_list');
5339: p_default_approvers_list := l_default_approvers_list;
5340:
5341: if(grt_approver_details_table.count=1) then
5342: hr_utility.trace('In( if(grt_approver_details_table.count=1) ): '|| l_proc);
5343: hr_utility.trace('only approver in the list');
5344: /*
5345: -- Work around for the bug#2345264
5346: -- Remove this check once the AME fixes the issue with ALLOW_REQUESTOR_APPROVAL
5339: p_default_approvers_list := l_default_approvers_list;
5340:
5341: if(grt_approver_details_table.count=1) then
5342: hr_utility.trace('In( if(grt_approver_details_table.count=1) ): '|| l_proc);
5343: hr_utility.trace('only approver in the list');
5344: /*
5345: -- Work around for the bug#2345264
5346: -- Remove this check once the AME fixes the issue with ALLOW_REQUESTOR_APPROVAL
5347: -- attribute.
5349: -- when ALLOW_REQUESTOR_APPROVAL is true.
5350: -- Needed this for fixing bug# 2337022
5351: */
5352: -- get the creator person id
5353: hr_utility.trace('getting the creator person id from WF attr');
5354: /*ln_creator_person_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
5355: itemkey => p_item_key,
5356: aname => 'CREATOR_PERSON_ID');
5357: */
5362: ln_creator_person_id := hr_workflow_ss.getApprStartingPointPersonId(ln_transaction_id);
5363:
5364: -- check if the approver id matches the creator
5365: if( ln_creator_person_id=grt_approver_details_table(1).person_id) then
5366: hr_utility.trace('creator person id matches the approverid resetting out nocopy param to null ');
5367: p_default_approvers_list :=l_no_approvers_list;
5368: end if;
5369: end if;
5370:
5367: p_default_approvers_list :=l_no_approvers_list;
5368: end if;
5369: end if;
5370:
5371: hr_utility.set_location('Leaving: '|| l_proc,40);
5372:
5373: EXCEPTION
5374: WHEN OTHERS THEN
5375: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5371: hr_utility.set_location('Leaving: '|| l_proc,40);
5372:
5373: EXCEPTION
5374: WHEN OTHERS THEN
5375: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5376: hr_utility.trace(' exception in '||gv_package||'.get_ame_approvers_list : ' || sqlerrm);
5377: Wf_Core.Context(gv_package, 'get_ame_approvers_list', p_item_type, p_item_key);
5378: raise;
5379:
5372:
5373: EXCEPTION
5374: WHEN OTHERS THEN
5375: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5376: hr_utility.trace(' exception in '||gv_package||'.get_ame_approvers_list : ' || sqlerrm);
5377: Wf_Core.Context(gv_package, 'get_ame_approvers_list', p_item_type, p_item_key);
5378: raise;
5379:
5380: END get_ame_approvers_list;
5414: c_additional_approver_rec ame_util.approversTable;
5415:
5416:
5417: BEGIN
5418: hr_utility.set_location('Entering: '|| l_proc,5);
5419: -- get AME related WF attribute values
5420: c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
5421: itemkey => p_item_key,
5422: aname => 'HR_AME_APP_ID_ATTR');
5445: transactionTypeIn=>c_transaction_type);
5446:
5447:
5448: if(c_transaction_type is not null) then
5449: hr_utility.trace('In( if(c_transaction_type is not null)): '|| l_proc);
5450: -- update AME list
5451: hr_utility.trace('Going into ( for i in 1..p_default_approvers_list.count): '|| l_proc);
5452: for i in 1..p_default_approvers_list.count loop
5453: -- check for the default approver flag
5447:
5448: if(c_transaction_type is not null) then
5449: hr_utility.trace('In( if(c_transaction_type is not null)): '|| l_proc);
5450: -- update AME list
5451: hr_utility.trace('Going into ( for i in 1..p_default_approvers_list.count): '|| l_proc);
5452: for i in 1..p_default_approvers_list.count loop
5453: -- check for the default approver flag
5454: if(p_default_approvers_list(i).default_approver='N') then
5455: -- details for the record insertion into AME
5471: orderIn =>c_additional_approver_order,
5472: transactionTypeIn=>c_transaction_type );
5473: end if;
5474: end loop;
5475: hr_utility.trace('Out of ( for i in 1..p_default_approvers_list.count): '|| l_proc);
5476: end if; -- end updating AME list
5477: hr_utility.set_location('Leaving: '|| l_proc,25);
5478:
5479: EXCEPTION
5473: end if;
5474: end loop;
5475: hr_utility.trace('Out of ( for i in 1..p_default_approvers_list.count): '|| l_proc);
5476: end if; -- end updating AME list
5477: hr_utility.set_location('Leaving: '|| l_proc,25);
5478:
5479: EXCEPTION
5480: WHEN OTHERS THEN
5481:
5478:
5479: EXCEPTION
5480: WHEN OTHERS THEN
5481:
5482: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5483: raise;
5484:
5485: END set_ame_approvers_list;
5486:
5527: ln_error_count NUMBER DEFAULT 1;
5528: l_proc constant varchar2(100) := g_package || ' get_additional_notifiers_list';
5529:
5530: BEGIN
5531: hr_utility.set_location('Entering: '|| l_proc,5);
5532: -- remove all rows from notifiers details table
5533: grt_notifier_details_table.DELETE;
5534: -- get the number of notifiers in the system
5535: OPEN csr_wiav(p_item_type,p_item_key,'NOTIFIERS_NUMBER');
5532: -- remove all rows from notifiers details table
5533: grt_notifier_details_table.DELETE;
5534: -- get the number of notifiers in the system
5535: OPEN csr_wiav(p_item_type,p_item_key,'NOTIFIERS_NUMBER');
5536: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,NOTIFIERS_NUMBER) ): '|| l_proc);
5537: FETCH csr_wiav into l_dummy;
5538: IF csr_wiav%notfound THEN
5539: gn_notifiers := 0;
5540: ELSE
5545: END IF;
5546: CLOSE csr_wiav;
5547:
5548: IF gn_notifiers > 0 THEN
5549: hr_utility.trace('In( IF gn_notifiers > 0): '|| l_proc);
5550: --loop througthe counter and get all the notifiers
5551:
5552: hr_utility.trace('Going into (FOR I IN 1..gn_notifiers): '|| l_proc);
5553: FOR I IN 1..gn_notifiers
5548: IF gn_notifiers > 0 THEN
5549: hr_utility.trace('In( IF gn_notifiers > 0): '|| l_proc);
5550: --loop througthe counter and get all the notifiers
5551:
5552: hr_utility.trace('Going into (FOR I IN 1..gn_notifiers): '|| l_proc);
5553: FOR I IN 1..gn_notifiers
5554: LOOP
5555: lv_job_title := NULL;
5556: lv_item_name := gv_notifier_name||to_char(I);
5554: LOOP
5555: lv_job_title := NULL;
5556: lv_item_name := gv_notifier_name||to_char(I);
5557: OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
5558: hr_utility.trace('Going into Fetch after ( OPEN csr_wiav(p_item_type,p_item_key,lv_item_name)): '|| l_proc);
5559: FETCH csr_wiav into l_dummy;
5560: IF csr_wiav%notfound THEN
5561: lv_exist := 'N';
5562: ELSE
5617:
5618: END IF; -- for lv_exist = 'Y'
5619:
5620: END LOOP;
5621: hr_utility.trace('Out of (FOR I IN 1..gn_notifiers): '|| l_proc);
5622: END IF; -- for gn_notifiers > 0
5623:
5624: p_additional_notifiers_list:=l_additional_notifiers_list;
5625: hr_utility.set_location('Leaving: '|| l_proc,35);
5621: hr_utility.trace('Out of (FOR I IN 1..gn_notifiers): '|| l_proc);
5622: END IF; -- for gn_notifiers > 0
5623:
5624: p_additional_notifiers_list:=l_additional_notifiers_list;
5625: hr_utility.set_location('Leaving: '|| l_proc,35);
5626: EXCEPTION
5627: WHEN OTHERS THEN
5628: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5629: if csr_wiav%isopen then
5624: p_additional_notifiers_list:=l_additional_notifiers_list;
5625: hr_utility.set_location('Leaving: '|| l_proc,35);
5626: EXCEPTION
5627: WHEN OTHERS THEN
5628: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
5629: if csr_wiav%isopen then
5630: close csr_wiav;
5631: end if;
5632: raise;