[Home] [Help]
5: --
6: c_not_notified constant varchar2(12) := 'NOT_NOTIFIED';
7: c_notified constant varchar2(8) := 'NOTIFIED';
8: c_finished constant varchar2(8) := 'FINISHED';
9: g_debug BOOLEAN :=hr_utility.debug_enabled;
10:
11: g_trace VARCHAR2(2000);
12:
13: TYPE approval_comp IS RECORD(
460: l_same boolean := true;
461:
462: l_proc varchar2(50) := 'HXC_APPROVAL_WF_PKG.changed';
463: BEGIN
464: g_debug:=hr_utility.debug_enabled;
465: if g_debug then
466: hr_utility.set_location(l_proc, 10);
467: end if;
468:
462: l_proc varchar2(50) := 'HXC_APPROVAL_WF_PKG.changed';
463: BEGIN
464: g_debug:=hr_utility.debug_enabled;
465: if g_debug then
466: hr_utility.set_location(l_proc, 10);
467: end if;
468:
469: IF p_time_category_id IS NULL OR p_time_category_id = 0
470: THEN
471: l_block_index := p_detail_blocks.first;
472: LOOP
473: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
474: if g_debug then
475: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
476: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
477: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
478: end if;
479:
472: LOOP
473: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
474: if g_debug then
475: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
476: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
477: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
478: end if;
479:
480: IF p_detail_blocks(l_block_index).added <> 'Y'
473: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
474: if g_debug then
475: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
476: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
477: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
478: end if;
479:
480: IF p_detail_blocks(l_block_index).added <> 'Y'
481: THEN
480: IF p_detail_blocks(l_block_index).added <> 'Y'
481: THEN
482: l_count := l_count + 1;
483: if g_debug then
484: hr_utility.set_location(l_proc, 20);
485: end if;
486:
487: IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
488: , p_detail_blocks(l_block_index).block_ovn)
487: IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
488: , p_detail_blocks(l_block_index).block_ovn)
489: THEN
490: if g_debug then
491: hr_utility.set_location(l_proc, 30);
492: end if;
493:
494: g_block_exist_for_ap := 'Y';
495:
502: END LOOP;
503:
504: ELSE
505: if g_debug then
506: hr_utility.set_location(l_proc, 40);
507: end if;
508:
509: hxc_time_category_utils_pkg.initialise_time_category(
510: p_time_category_id => p_time_category_id
522: IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
523: , p_detail_blocks(l_block_index).block_ovn)
524: THEN
525: if g_debug then
526: hr_utility.set_location(l_proc, 60);
527: end if;
528:
529: g_block_exist_for_ap := 'Y';
530:
539:
540: IF no_blocks(p_app_id, p_timecard_id) = 0
541: THEN
542: if g_debug then
543: hr_utility.trace('number not changed');
544: end if;
545: RETURN FALSE;
546: ELSE
547: if g_debug then
544: end if;
545: RETURN FALSE;
546: ELSE
547: if g_debug then
548: hr_utility.trace('number changed');
549: end if;
550: RETURN TRUE;
551: END IF;
552: END changed;
639: LOOP
640: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
641:
642: if g_debug then
643: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
644: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
645: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
646: end if;
647:
640: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
641:
642: if g_debug then
643: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
644: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
645: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
646: end if;
647:
648: IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
641:
642: if g_debug then
643: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
644: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
645: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
646: end if;
647:
648: IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
649: p_detail_blocks(l_block_index).block_id
679: LOOP
680: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
681:
682: if g_debug then
683: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
684: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
685: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
686: end if;
687:
680: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
681:
682: if g_debug then
683: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
684: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
685: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
686: end if;
687:
688: IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
681:
682: if g_debug then
683: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
684: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
685: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
686: end if;
687:
688: IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
689: p_detail_blocks(l_block_index).block_id
700: ,p_detail_blocks(l_block_index).block_ovn
701: );
702:
703: if g_debug then
704: hr_utility.trace('linked!');
705: end if;
706:
707: END IF;
708:
721:
722: BEGIN
723:
724: if g_debug then
725: hr_utility.trace('in link_ap_details_all');
726: end if;
727:
728: l_block_index := p_detail_blocks.first;
729: LOOP
729: LOOP
730: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
731:
732: if g_debug then
733: hr_utility.trace('block_id=' || p_detail_blocks(l_block_index).block_id);
734: hr_utility.trace('block_ovn=' || p_detail_blocks(l_block_index).block_ovn);
735: hr_utility.trace('added=' || p_detail_blocks(l_block_index).added);
736: end if;
737: l_block_index := p_detail_blocks.next(l_block_index);
730: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
731:
732: if g_debug then
733: hr_utility.trace('block_id=' || p_detail_blocks(l_block_index).block_id);
734: hr_utility.trace('block_ovn=' || p_detail_blocks(l_block_index).block_ovn);
735: hr_utility.trace('added=' || p_detail_blocks(l_block_index).added);
736: end if;
737: l_block_index := p_detail_blocks.next(l_block_index);
738: END LOOP;
731:
732: if g_debug then
733: hr_utility.trace('block_id=' || p_detail_blocks(l_block_index).block_id);
734: hr_utility.trace('block_ovn=' || p_detail_blocks(l_block_index).block_ovn);
735: hr_utility.trace('added=' || p_detail_blocks(l_block_index).added);
736: end if;
737: l_block_index := p_detail_blocks.next(l_block_index);
738: END LOOP;
739:
747: THEN
748: --set added flag
749: --we use this flag to find all the category 0 blocks
750: if g_debug then
751: hr_utility.trace('inserting id=' || p_detail_blocks(l_block_index).block_id
752: || '|ovn=' || p_detail_blocks(l_block_index).block_ovn);
753: end if;
754:
755: p_detail_blocks(l_block_index).added := 'Y';
769:
770: END LOOP;
771:
772: if g_debug then
773: hr_utility.trace('end link_ap_details_all');
774: end if;
775: END link_ap_details_all;
776:
777: FUNCTION get_person_id(
949: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.update_appl_period';
950:
951:
952: BEGIN
953: g_debug:=hr_utility.debug_enabled;
954: if g_debug then
955: hr_utility.set_location(l_proc, 10);
956: end if;
957:
951:
952: BEGIN
953: g_debug:=hr_utility.debug_enabled;
954: if g_debug then
955: hr_utility.set_location(l_proc, 10);
956: end if;
957:
958: l_tc_resource_id := wf_engine.GetItemAttrNumber(
959: itemtype => itemtype,
961: aname => 'RESOURCE_ID');
962:
963:
964: if g_debug then
965: hr_utility.set_location(l_proc, 30);
966: end if;
967:
968: l_period_start_date := wf_engine.GetItemAttrDate(
969: itemtype => itemtype,
970: itemkey => itemkey ,
971: aname => 'APP_START_DATE');
972:
973: if g_debug then
974: hr_utility.set_location(l_proc, 40);
975: end if;
976:
977: l_period_end_date := wf_engine.GetItemAttrDate(
978: itemtype => itemtype,
979: itemkey => itemkey ,
980: aname => 'APP_END_DATE');
981:
982: if g_debug then
983: hr_utility.set_location(l_proc, 50);
984: end if;
985:
986: l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
987: itemtype => itemtype,
988: itemkey => itemkey,
989: aname => 'APP_BB_ID');
990:
991: if g_debug then
992: hr_utility.set_location(l_proc, 60);
993: end if;
994:
995: l_appl_period_bb_ovn := wf_engine.GetItemAttrNumber(
996: itemtype => itemtype,
997: itemkey => itemkey,
998: aname => 'APP_BB_OVN');
999:
1000: if g_debug then
1001: hr_utility.set_location(l_proc, 70);
1002: end if;
1003:
1004: -- Set up the approval status - get the value for the APPROVAL_STATUS
1005: -- attribute, which is set up in the activity previous to this one.
1014: itemkey => itemkey,
1015: aname => 'APR_REJ_REASON');
1016:
1017: if g_debug then
1018: hr_utility.set_location(l_proc, 80);
1019:
1020: hr_utility.trace('l_approval_status is : ' || l_approval_status);
1021: end if;
1022:
1016:
1017: if g_debug then
1018: hr_utility.set_location(l_proc, 80);
1019:
1020: hr_utility.trace('l_approval_status is : ' || l_approval_status);
1021: end if;
1022:
1023: --get approver id
1024: --what happens to l_approver if AUTO_APPROVE??
1095: l_approver := find_mysterious_approver(itemtype,itemkey);
1096: end if;
1097:
1098: if g_debug then
1099: hr_utility.set_location(l_proc, 90);
1100: end if;
1101:
1102: --update hxc_application_period_summary table
1103: l_creation_date := get_creation_date(l_appl_period_bb_id, l_appl_period_bb_ovn);
1124: END IF;
1125:
1126:
1127: if g_debug then
1128: hr_utility.set_location(l_proc, 110);
1129: end if;
1130:
1131: /*
1132: Since this could be the last operation in the approvals process
1148: -- The line below records this function call in the error system
1149: -- in the case of an exception.
1150: --
1151: if g_debug then
1152: hr_utility.trace(sqlerrm);
1153: hr_utility.trace('lllllllllllllllllll');
1154: hr_utility.trace(hr_message.last_message_name);
1155: hr_utility.trace('----');
1156: end if;
1149: -- in the case of an exception.
1150: --
1151: if g_debug then
1152: hr_utility.trace(sqlerrm);
1153: hr_utility.trace('lllllllllllllllllll');
1154: hr_utility.trace(hr_message.last_message_name);
1155: hr_utility.trace('----');
1156: end if;
1157: IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1150: --
1151: if g_debug then
1152: hr_utility.trace(sqlerrm);
1153: hr_utility.trace('lllllllllllllllllll');
1154: hr_utility.trace(hr_message.last_message_name);
1155: hr_utility.trace('----');
1156: end if;
1157: IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1158: RETURN;
1151: if g_debug then
1152: hr_utility.trace(sqlerrm);
1153: hr_utility.trace('lllllllllllllllllll');
1154: hr_utility.trace(hr_message.last_message_name);
1155: hr_utility.trace('----');
1156: end if;
1157: IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1158: RETURN;
1159: END IF;
1158: RETURN;
1159: END IF;
1160: --
1161: if g_debug then
1162: hr_utility.set_location(l_proc, 999);
1163: hr_utility.trace('IN EXCEPTION IN update_appl_period');
1164: end if;
1165: --
1166: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.update_appl_period',
1159: END IF;
1160: --
1161: if g_debug then
1162: hr_utility.set_location(l_proc, 999);
1163: hr_utility.trace('IN EXCEPTION IN update_appl_period');
1164: end if;
1165: --
1166: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.update_appl_period',
1167: itemtype, itemkey, to_char(actid), funcmode);
1224: l_creation_date hxc_time_building_blocks.creation_date%TYPE;
1225:
1226: l_proc VARCHAR2(150) := 'create_next_period';
1227: BEGIN
1228: g_debug:=hr_utility.debug_enabled;
1229: if g_debug then
1230: hr_utility.trace('in create_next_period');
1231: end if;
1232:
1226: l_proc VARCHAR2(150) := 'create_next_period';
1227: BEGIN
1228: g_debug:=hr_utility.debug_enabled;
1229: if g_debug then
1230: hr_utility.trace('in create_next_period');
1231: end if;
1232:
1233: IF funcmode = 'RUN'
1234: THEN
1275: ,p_time_building_block_id => l_new_appl_bb_id
1276: ,p_object_version_number => l_new_appl_bb_ovn);
1277:
1278: if g_debug then
1279: hr_utility.trace('next period created=' || l_new_appl_bb_id);
1280: hr_utility.trace('next period created=' || l_new_appl_bb_ovn);
1281: end if;
1282:
1283: --populating summary tables
1276: ,p_object_version_number => l_new_appl_bb_ovn);
1277:
1278: if g_debug then
1279: hr_utility.trace('next period created=' || l_new_appl_bb_id);
1280: hr_utility.trace('next period created=' || l_new_appl_bb_ovn);
1281: end if;
1282:
1283: --populating summary tables
1284: l_creation_date := get_creation_date(l_new_appl_bb_id, l_new_appl_bb_ovn);
1291: IF c_current_period%NOTFOUND
1292: THEN
1293: CLOSE c_current_period;
1294:
1295: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP', '20');
1298: hr_utility.raise_error;
1299: END IF;
1292: THEN
1293: CLOSE c_current_period;
1294:
1295: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP', '20');
1298: hr_utility.raise_error;
1299: END IF;
1300:
1293: CLOSE c_current_period;
1294:
1295: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP', '20');
1298: hr_utility.raise_error;
1299: END IF;
1300:
1301: CLOSE c_current_period;
1294:
1295: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP', '20');
1298: hr_utility.raise_error;
1299: END IF;
1300:
1301: CLOSE c_current_period;
1302:
1409: -- The line below records this function call in the error system
1410: -- in the case of an exception.
1411: --
1412: if g_debug then
1413: hr_utility.set_location(l_proc, 999);
1414: --
1415: hr_utility.trace('IN EXCEPTION IN create_next_period');
1416: --
1417: end if;
1411: --
1412: if g_debug then
1413: hr_utility.set_location(l_proc, 999);
1414: --
1415: hr_utility.trace('IN EXCEPTION IN create_next_period');
1416: --
1417: end if;
1418: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.create_next_period',
1419: itemtype, itemkey, to_char(actid), funcmode);
1581:
1582: IF p_app_period_func IS NOT NULL
1583: THEN
1584: if g_debug then
1585: hr_utility.set_location(l_proc, 95);
1586: end if;
1587:
1588: l_call_proc := p_app_period_func ||
1589: '(p_building_block_date => ' || p_day ||
1592: ',p_period_end_date => l_period_end_date' ||
1593: ',p_override_allowed => l_override_allowed)';
1594:
1595: if g_debug then
1596: hr_utility.trace('Period Start Date (from function) is : ' ||
1597: to_char(l_period_start_date, 'DD-MM-YYYY'));
1598: hr_utility.trace('Period End Date (from function) is : ' ||
1599: to_char(l_period_end_date, 'DD-MM-YYYY'));
1600: end if;
1594:
1595: if g_debug then
1596: hr_utility.trace('Period Start Date (from function) is : ' ||
1597: to_char(l_period_start_date, 'DD-MM-YYYY'));
1598: hr_utility.trace('Period End Date (from function) is : ' ||
1599: to_char(l_period_end_date, 'DD-MM-YYYY'));
1600: end if;
1601:
1602: l_cursor := dbms_sql.open_cursor;
1604: l_ret := dbms_sql.execute(l_cursor);
1605: dbms_sql.close_cursor(l_cursor);
1606:
1607: if g_debug then
1608: hr_utility.set_location(l_proc, 110);
1609: end if;
1610: ELSE
1611:
1612: l_override_allowed := TRUE;
1619: IF l_override_allowed
1620: THEN
1621:
1622: if g_debug then
1623: hr_utility.set_location(l_proc, 120);
1624: end if;
1625:
1626: l_rec_period_id := get_approval_period_id(
1627: p_resource_id
1631: ,p_tc_stop_time
1632: );
1633:
1634: if g_debug then
1635: hr_utility.trace('Recurring Period ID is : ' || to_char(l_rec_period_id));
1636: end if;
1637:
1638: open csr_get_rec_period_info(l_rec_period_id);
1639: fetch csr_get_rec_period_info into l_rec_start_date,
1650: ,p_period_end => l_period_end_date
1651: );
1652:
1653: if g_debug then
1654: hr_utility.trace('Appl Period Start Date is : ' ||
1655: to_char(l_period_start_date, 'DD-MM-YYYY'));
1656: hr_utility.trace('Appl Period End Date is : ' ||
1657: to_char(l_period_end_date, 'DD-MM-YYYY'));
1658: end if;
1652:
1653: if g_debug then
1654: hr_utility.trace('Appl Period Start Date is : ' ||
1655: to_char(l_period_start_date, 'DD-MM-YYYY'));
1656: hr_utility.trace('Appl Period End Date is : ' ||
1657: to_char(l_period_end_date, 'DD-MM-YYYY'));
1658: end if;
1659:
1660: END IF;
1971: g_debug := true;
1972: g_trace := l_proc || '10';
1973: l_item_key := null;
1974: if g_debug then
1975: hr_utility.trace('start generating period');
1976: end if;
1977:
1978: l_time_category_id := p_approval_comp.time_category_id;
1979:
2006: l_app_comp_id_temp;
2007:
2008: EXIT WHEN c_app_period%NOTFOUND;
2009:
2010: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2011: hr_utility.trace('|--------------------------------------------------------------------|');
2012: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2013: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2007:
2008: EXIT WHEN c_app_period%NOTFOUND;
2009:
2010: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2011: hr_utility.trace('|--------------------------------------------------------------------|');
2012: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2013: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2015: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2008: EXIT WHEN c_app_period%NOTFOUND;
2009:
2010: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2011: hr_utility.trace('|--------------------------------------------------------------------|');
2012: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2013: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2015: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2016: hr_utility.trace('|--------------------------------------------------------------------|');
2009:
2010: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2011: hr_utility.trace('|--------------------------------------------------------------------|');
2012: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2013: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2015: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2016: hr_utility.trace('|--------------------------------------------------------------------|');
2017: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2010: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2011: hr_utility.trace('|--------------------------------------------------------------------|');
2012: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2013: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2015: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2016: hr_utility.trace('|--------------------------------------------------------------------|');
2017: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2018:
2011: hr_utility.trace('|--------------------------------------------------------------------|');
2012: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2013: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2015: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2016: hr_utility.trace('|--------------------------------------------------------------------|');
2017: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2018:
2019: --for hr supervisor, end date all the later supervisor's app periods
2012: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2013: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2015: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2016: hr_utility.trace('|--------------------------------------------------------------------|');
2017: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2018:
2019: --for hr supervisor, end date all the later supervisor's app periods
2020: --only keep the first supervisor's app period. we will regenereate new
2013: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2015: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2016: hr_utility.trace('|--------------------------------------------------------------------|');
2017: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2018:
2019: --for hr supervisor, end date all the later supervisor's app periods
2020: --only keep the first supervisor's app period. we will regenereate new
2021: --app periods for later supervisors
2042:
2043: END LOOP;
2044:
2045: CLOSE c_app_period;
2046: hr_utility.trace('l_time_category:'||l_time_category_id);
2047:
2048: --
2049: -- If this is a new application period, and has no associated details
2050: -- do not create it. However, if it has been previously created,
2062: -- There are no details associated with this application period
2063: IF l_app_id is null then
2064: -- This is a new application, do not generate a new, empty
2065: -- application period.
2066: hr_utility.trace('Return(0)');
2067: RETURN;
2068: END IF;
2069: IF ((l_app_id is not null)
2070: AND (l_app_ovn = 1)
2069: IF ((l_app_id is not null)
2070: AND (l_app_ovn = 1)
2071: AND (l_notification_status = c_notified OR l_notification_status = c_not_notified )) then
2072: -- Application period is not new, but the approver has not seen it
2073: hr_utility.trace('Return(1)');
2074: RETURN;
2075: END IF;
2076: END IF;
2077: END IF;
2100: hxc_app_period_summary_api.app_period_delete(l_app_id);
2101: END IF;
2102: END IF;
2103:
2104: hr_utility.trace('Return(2)');
2105: RETURN;
2106: END IF;
2107: END IF;
2108: --
2109: -- 115.93 Change: Include check that the approval component id
2110: -- is the same in the statement below, otherwise we will surely
2111: -- need to regenerate the application period. Bug 4302997.
2112: --
2113: hr_utility.trace('l_app_id:'||l_app_id);
2114: hr_utility.trace('l_approval_status:'||l_approval_status);
2115: hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2116:
2117: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2110: -- is the same in the statement below, otherwise we will surely
2111: -- need to regenerate the application period. Bug 4302997.
2112: --
2113: hr_utility.trace('l_app_id:'||l_app_id);
2114: hr_utility.trace('l_approval_status:'||l_approval_status);
2115: hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2116:
2117: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2118: hr_utility.trace('changed is true');
2111: -- need to regenerate the application period. Bug 4302997.
2112: --
2113: hr_utility.trace('l_app_id:'||l_app_id);
2114: hr_utility.trace('l_approval_status:'||l_approval_status);
2115: hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2116:
2117: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2118: hr_utility.trace('changed is true');
2119: else
2114: hr_utility.trace('l_approval_status:'||l_approval_status);
2115: hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2116:
2117: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2118: hr_utility.trace('changed is true');
2119: else
2120: hr_utility.trace('changed is false');
2121: end if;
2122:
2116:
2117: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2118: hr_utility.trace('changed is true');
2119: else
2120: hr_utility.trace('changed is false');
2121: end if;
2122:
2123: select count(*) into l_number_of_details
2124: from hxc_ap_detail_links
2123: select count(*) into l_number_of_details
2124: from hxc_ap_detail_links
2125: where application_period_id = l_app_id;
2126:
2127: hr_utility.trace('Count for '||l_app_id||' is:'||l_number_of_details);
2128:
2129:
2130: IF l_app_id IS NOT NULL
2131: AND p_approval_comp.approval_comp_id = l_app_comp_id
2146: -- we should continue and generate the notification / app period
2147:
2148: if(l_number_of_details = 0 AND l_approval_status <> 'SUBMITTED') then
2149: -- it is case 1
2150: hr_utility.trace('Return(3) - empty actioned App Period - do nothing');
2151: RETURN;
2152: else
2153: -- it is case 2
2154: hr_utility.trace('Return(3.5) - newly empty period - notify previous approver');
2150: hr_utility.trace('Return(3) - empty actioned App Period - do nothing');
2151: RETURN;
2152: else
2153: -- it is case 2
2154: hr_utility.trace('Return(3.5) - newly empty period - notify previous approver');
2155: open get_item_key(l_app_id);
2156: fetch get_item_key into l_item_key;
2157: close get_item_key;
2158:
2191: -- to other changes in the timecard, the timecard has the
2192: -- appropriate status.
2193: --
2194: hxc_timecard_summary_api.reevaluate_timecard_statuses(l_app_id);
2195: hr_utility.trace('Return(4) - populated actioned App period - no changes. Use, but do not renotify');
2196: RETURN;
2197: END IF;
2198: end if;
2199: END IF;
2262:
2263: g_trace := l_proc || '30';
2264:
2265: if g_debug then
2266: hr_utility.trace('Generate it!');
2267: end if;
2268:
2269: t_attributes := get_empty_attribute;
2270:
2291: );
2292:
2293: g_trace := l_proc || '40';
2294: if g_debug then
2295: hr_utility.trace('Generated the period');
2296: hr_utility.trace('app_id=' || l_app_id);
2297: hr_utility.trace('app_ovn=' || l_app_ovn);
2298: hr_utility.trace('Populating hxc_app_period_summary');
2299: end if;
2292:
2293: g_trace := l_proc || '40';
2294: if g_debug then
2295: hr_utility.trace('Generated the period');
2296: hr_utility.trace('app_id=' || l_app_id);
2297: hr_utility.trace('app_ovn=' || l_app_ovn);
2298: hr_utility.trace('Populating hxc_app_period_summary');
2299: end if;
2300: l_creation_date := get_creation_date(l_app_id, l_app_ovn);
2293: g_trace := l_proc || '40';
2294: if g_debug then
2295: hr_utility.trace('Generated the period');
2296: hr_utility.trace('app_id=' || l_app_id);
2297: hr_utility.trace('app_ovn=' || l_app_ovn);
2298: hr_utility.trace('Populating hxc_app_period_summary');
2299: end if;
2300: l_creation_date := get_creation_date(l_app_id, l_app_ovn);
2301:
2294: if g_debug then
2295: hr_utility.trace('Generated the period');
2296: hr_utility.trace('app_id=' || l_app_id);
2297: hr_utility.trace('app_ovn=' || l_app_ovn);
2298: hr_utility.trace('Populating hxc_app_period_summary');
2299: end if;
2300: l_creation_date := get_creation_date(l_app_id, l_app_ovn);
2301:
2302: g_trace := l_proc || '80';
2340:
2341: END IF;
2342:
2343: if g_debug then
2344: hr_utility.trace('Populating hxc_ap_detail_links');
2345: end if;
2346: --populate hxc_ap_detail_links
2347: IF l_time_category_id IS NULL
2348: OR l_time_category_id = 0
2349: THEN
2350: g_trace := l_proc || '100';
2351:
2352: if g_debug then
2353: hr_utility.trace('Populating all');
2354: end if;
2355: link_ap_details_all(
2356: p_detail_blocks => p_detail_blocks
2357: ,p_app_id => l_app_id
2361: ELSE
2362: g_trace := l_proc || '110';
2363:
2364: if g_debug then
2365: hr_utility.trace('Populating time category : ' || l_time_category_id );
2366: end if;
2367: link_ap_details(
2368: p_detail_blocks => p_detail_blocks
2369: ,p_attributes => p_detail_attributes
2392: g_trace := l_proc || '130';
2393:
2394:
2395: if g_debug then
2396: hr_utility.trace('Populating hxc_tc_ap_links');
2397: end if;
2398: --populate hxc_tc_ap_links
2399:
2400: OPEN c_tc_ap_link(p_timecard_id, l_app_id);
2423: g_trace := l_proc || '180';
2424: END IF;
2425:
2426: if g_debug then
2427: hr_utility.trace('End generating app period');
2428: end if;
2429:
2430: EXCEPTION
2431: WHEN OTHERS THEN
2471: --
2472: BEGIN
2473: --
2474: if g_debug then
2475: hr_utility.set_location(l_proc, 10);
2476: end if;
2477: --
2478: open csr_get_appr_style;
2479: fetch csr_get_appr_style into l_approval_style_id;
2479: fetch csr_get_appr_style into l_approval_style_id;
2480: IF csr_get_appr_style%NOTFOUND THEN
2481: --
2482: if g_debug then
2483: hr_utility.set_location(l_proc, 20);
2484: end if;
2485: --
2486: g_error_count := g_error_count + 1;
2487: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPR_STYLE';
2486: g_error_count := g_error_count + 1;
2487: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPR_STYLE';
2488: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2489: --
2490: hr_utility.set_message(809, 'HXC_APR_NO_APPR_STYLE');
2491: hr_utility.raise_error;
2492: --
2493: END IF;
2494: --
2487: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPR_STYLE';
2488: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2489: --
2490: hr_utility.set_message(809, 'HXC_APR_NO_APPR_STYLE');
2491: hr_utility.raise_error;
2492: --
2493: END IF;
2494: --
2495: close csr_get_appr_style;
2695: l_gen_app_period boolean;
2696:
2697: BEGIN
2698:
2699: g_debug:=hr_utility.debug_enabled;
2700: g_trace := '10';
2701: if g_debug then
2702: hr_utility.set_location(l_proc, 10);
2703: end if;
2698:
2699: g_debug:=hr_utility.debug_enabled;
2700: g_trace := '10';
2701: if g_debug then
2702: hr_utility.set_location(l_proc, 10);
2703: end if;
2704:
2705: l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
2706: (itemtype => itemtype,
2707: itemkey => itemkey,
2708: aname => 'TC_BLD_BLK_ID');
2709:
2710: if g_debug then
2711: hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
2712: end if;
2713: l_tc_ovn := wf_engine.GetItemAttrNumber
2714: (itemtype => itemtype,
2715: itemkey => itemkey,
2715: itemkey => itemkey,
2716: aname => 'TC_BLD_BLK_OVN');
2717:
2718: if g_debug then
2719: hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
2720: end if;
2721:
2722: l_tc_resubmitted := wf_engine.GetItemAttrText
2723: (itemtype => itemtype,
2724: itemkey => itemkey,
2725: aname => 'TC_RESUBMITTED');
2726:
2727: if g_debug then
2728: hr_utility.trace('Timecard Resubmitted is : ' || l_tc_resubmitted);
2729: end if;
2730:
2731: g_trace := '20';
2732:
2745: g_error_count := g_error_count + 1;
2746: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
2747: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2748:
2749: hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
2750: hr_utility.raise_error;
2751: END IF;
2752: g_trace := '40';
2753:
2746: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
2747: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2748:
2749: hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
2750: hr_utility.raise_error;
2751: END IF;
2752: g_trace := '40';
2753:
2754: close csr_get_tc_info;
2783: g_error_count := g_error_count + 1;
2784: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPL_SET_PREF';
2785: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2786:
2787: hr_utility.set_message(809, 'HXC_APR_NO_APPL_SET_PREF');
2788: hr_utility.raise_error;
2789:
2790: END IF;
2791: close csr_get_apps;
2784: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPL_SET_PREF';
2785: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2786:
2787: hr_utility.set_message(809, 'HXC_APR_NO_APPL_SET_PREF');
2788: hr_utility.raise_error;
2789:
2790: END IF;
2791: close csr_get_apps;
2792:
2812: g_trace := '90 Application=' || l_application;
2813: g_trace := '90 Time Recipient ID=' || to_char(l_time_recipient_id);
2814:
2815: if g_debug then
2816: hr_utility.trace('90 Application=' || l_application);
2817: hr_utility.trace('90 Time Recipient ID=' || to_char(l_time_recipient_id));
2818: end if;
2819:
2820: l_processed_app_start := NULL;
2813: g_trace := '90 Time Recipient ID=' || to_char(l_time_recipient_id);
2814:
2815: if g_debug then
2816: hr_utility.trace('90 Application=' || l_application);
2817: hr_utility.trace('90 Time Recipient ID=' || to_char(l_time_recipient_id));
2818: end if;
2819:
2820: l_processed_app_start := NULL;
2821: l_processed_app_stop := NULL;
2834:
2835: if(dayHasActiveAssignment(l_assignment_periods,l_day_start_time))then
2836:
2837: if g_debug then
2838: hr_utility.set_location(l_proc, 90);
2839: hr_utility.trace('day start=' || to_char(l_day_start_time, 'YYYY/MM/DD'));
2840: end if;
2841:
2842: get_application_period
2835: if(dayHasActiveAssignment(l_assignment_periods,l_day_start_time))then
2836:
2837: if g_debug then
2838: hr_utility.set_location(l_proc, 90);
2839: hr_utility.trace('day start=' || to_char(l_day_start_time, 'YYYY/MM/DD'));
2840: end if;
2841:
2842: get_application_period
2843: (p_app_period_func => l_app_period_func,
2982:
2983:
2984: IF l_approval_comp.approval_mechanism = 'ENTRY_LEVEL_APPROVAL' THEN
2985: if g_debug then
2986: hr_utility.trace(l_proc || 'Entry level approvals 150');
2987: end if;
2988:
2989: g_trace := '150 ELA';
2990:
3000:
3001: hxc_time_category_utils_pkg.push_timecard ( l_new_detail_blocks, l_new_detail_attributes, TRUE );
3002:
3003: if g_debug then
3004: hr_utility.trace(l_proc || 'Entry level approvals 160');
3005: end if;
3006:
3007: OPEN c_ela_comps(l_approval_comp.approval_comp_id,
3008: l_approval_comp.object_version_number);
3014: IF l_ela_comp.time_category_id = 0 THEN
3015: l_default_comp := l_ela_comp;
3016: ELSE
3017: if g_debug then
3018: hr_utility.trace(l_proc || 'Entry level approvals 170');
3019: end if;
3020: g_trace := '170 ELA generating period';
3021:
3022: if(l_gen_app_period) then
3035: );
3036: end if;
3037:
3038: if g_debug then
3039: hr_utility.trace(l_proc || 'Entry level approvals 180');
3040: end if;
3041: g_trace :=' 180 ELA finish generating period';
3042:
3043: END IF;
3046:
3047: CLOSE c_ela_comps;
3048:
3049: if g_debug then
3050: hr_utility.trace(l_proc || 'Entry level approvals 200');
3051: end if;
3052: g_trace := '200 any detail left??';
3053:
3054: --Now take care of the rest of the blocks
3054: --Now take care of the rest of the blocks
3055: l_count := get_rest_detail_blocks(l_detail_blocks);
3056:
3057: if g_debug then
3058: hr_utility.trace('210 rest_detail_count=' || l_count);
3059: end if;
3060:
3061: g_trace := '210 rest_detail_count=' || l_count;
3062: if g_debug then
3059: end if;
3060:
3061: g_trace := '210 rest_detail_count=' || l_count;
3062: if g_debug then
3063: hr_utility.trace('220 rest_detail_count > 0');
3064: end if;
3065:
3066: g_trace := '220 rest_detail_count > 0';
3067:
3081: );
3082: end if;
3083:
3084: if g_debug then
3085: hr_utility.trace('230 finished generating period for rest details');
3086: end if;
3087: g_trace := '230 finished generating period for rest details';
3088: ELSE
3089: if g_debug then
3086: end if;
3087: g_trace := '230 finished generating period for rest details';
3088: ELSE
3089: if g_debug then
3090: hr_utility.trace('250 NON ELA');
3091: end if;
3092: g_trace := '250 NON ELA';
3093:
3094: -- non ELA mechanism
3108: );
3109: end if;
3110:
3111: if g_debug then
3112: hr_utility.trace( '260 finished generating period for NON ELA');
3113: end if;
3114: g_trace := '260 finished generating period for NON ELA';
3115: END IF;
3116: END IF;
3132: hxc_app_comp_notifications_api.c_recipient_worker
3133: );
3134:
3135: if g_debug then
3136: hr_utility.set_location(l_proc, 200);
3137: end if;
3138: close csr_get_apps;
3139:
3140: if g_debug then
3137: end if;
3138: close csr_get_apps;
3139:
3140: if g_debug then
3141: hr_utility.trace('300 END of create_appl_period_info');
3142: end if;
3143:
3144:
3145: g_trace := '300 END of create_appl_period_info';
3152: -- The line below records this function call in the error system
3153: -- in the case of an exception.
3154: --
3155: if g_debug then
3156: hr_utility.set_location(l_proc, 999);
3157: --
3158: hr_utility.trace('IN EXCEPTION IN create_appl_period_info');
3159: --
3160: end if;
3154: --
3155: if g_debug then
3156: hr_utility.set_location(l_proc, 999);
3157: --
3158: hr_utility.trace('IN EXCEPTION IN create_appl_period_info');
3159: --
3160: end if;
3161: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.create_appl_period_info',
3162: itemtype, itemkey, to_char(actid), funcmode, g_trace);
3225:
3226: BEGIN
3227:
3228: if g_debug then
3229: hr_utility.set_location(l_proc, 10);
3230: end if;
3231: l_date := p_period_start_date;
3232:
3233: l_approved := 'Y';
3260:
3261: END LOOP;
3262:
3263: if g_debug then
3264: hr_utility.set_location(l_proc, 4);
3265: end if;
3266:
3267: RETURN 'Y';
3268:
3345: --
3346: BEGIN
3347: --
3348: if g_debug then
3349: hr_utility.set_location(l_proc, 10);
3350: end if;
3351: --
3352: BEGIN
3353: --added by jxtan to fix mid period hiring
3470: l_is_blank varchar2(1) := NULL;
3471:
3472: BEGIN
3473: l_approval_item_key := null;
3474: g_debug:=hr_utility.debug_enabled;
3475: if g_debug then
3476: hr_utility.set_location(l_proc, 10);
3477: end if;
3478: l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
3472: BEGIN
3473: l_approval_item_key := null;
3474: g_debug:=hr_utility.debug_enabled;
3475: if g_debug then
3476: hr_utility.set_location(l_proc, 10);
3477: end if;
3478: l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
3479: (itemtype => itemtype,
3480: itemkey => itemkey,
3480: itemkey => itemkey,
3481: aname => 'TC_BLD_BLK_ID');
3482:
3483: if g_debug then
3484: hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
3485: end if;
3486: l_tc_ovn := wf_engine.GetItemAttrNumber
3487: (itemtype => itemtype,
3488: itemkey => itemkey,
3488: itemkey => itemkey,
3489: aname => 'TC_BLD_BLK_OVN');
3490:
3491: if g_debug then
3492: hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
3493: end if;
3494: l_tc_resubmitted := wf_engine.GetItemAttrText
3495: (itemtype => itemtype,
3496: itemkey => itemkey,
3496: itemkey => itemkey,
3497: aname => 'TC_RESUBMITTED');
3498:
3499: if g_debug then
3500: hr_utility.trace('Timecard Resubmitted is : ' || l_tc_resubmitted);
3501: end if;
3502: l_bb_new := wf_engine.GetItemAttrText
3503: (itemtype => itemtype,
3504: itemkey => itemkey,
3504: itemkey => itemkey,
3505: aname => 'BB_NEW');
3506:
3507: if g_debug then
3508: hr_utility.trace('Building Block New is : ' || l_bb_new);
3509: hr_utility.set_location(l_proc, 20);
3510: end if;
3511:
3512: open csr_get_tc_info(l_tc_bld_blk_id,
3505: aname => 'BB_NEW');
3506:
3507: if g_debug then
3508: hr_utility.trace('Building Block New is : ' || l_bb_new);
3509: hr_utility.set_location(l_proc, 20);
3510: end if;
3511:
3512: open csr_get_tc_info(l_tc_bld_blk_id,
3513: l_tc_ovn);
3516: l_tc_stop_time,
3517: l_last_updated_by;
3518:
3519: if g_debug then
3520: hr_utility.set_location(l_proc, 30);
3521: hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
3522: hr_utility.trace('Timecard Start Time is : ' ||
3523: to_char(l_tc_start_time, 'DD-MM-YYYY'));
3524: hr_utility.trace('Timecard End Time is : ' ||
3517: l_last_updated_by;
3518:
3519: if g_debug then
3520: hr_utility.set_location(l_proc, 30);
3521: hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
3522: hr_utility.trace('Timecard Start Time is : ' ||
3523: to_char(l_tc_start_time, 'DD-MM-YYYY'));
3524: hr_utility.trace('Timecard End Time is : ' ||
3525: to_char(l_tc_stop_time, 'DD-MM-YYYY'));
3518:
3519: if g_debug then
3520: hr_utility.set_location(l_proc, 30);
3521: hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
3522: hr_utility.trace('Timecard Start Time is : ' ||
3523: to_char(l_tc_start_time, 'DD-MM-YYYY'));
3524: hr_utility.trace('Timecard End Time is : ' ||
3525: to_char(l_tc_stop_time, 'DD-MM-YYYY'));
3526: end if;
3520: hr_utility.set_location(l_proc, 30);
3521: hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
3522: hr_utility.trace('Timecard Start Time is : ' ||
3523: to_char(l_tc_start_time, 'DD-MM-YYYY'));
3524: hr_utility.trace('Timecard End Time is : ' ||
3525: to_char(l_tc_stop_time, 'DD-MM-YYYY'));
3526: end if;
3527: IF csr_get_tc_info%NOTFOUND
3528: THEN
3527: IF csr_get_tc_info%NOTFOUND
3528: THEN
3529:
3530: if g_debug then
3531: hr_utility.set_location(l_proc, 40);
3532: end if;
3533:
3534: g_error_count := g_error_count + 1;
3535: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
3534: g_error_count := g_error_count + 1;
3535: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
3536: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3537:
3538: hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
3539: hr_utility.raise_error;
3540:
3541: END IF;
3542:
3535: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
3536: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3537:
3538: hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
3539: hr_utility.raise_error;
3540:
3541: END IF;
3542:
3543: if g_debug then
3540:
3541: END IF;
3542:
3543: if g_debug then
3544: hr_utility.set_location(l_proc, 50);
3545: end if;
3546:
3547: close csr_get_tc_info;
3548:
3548:
3549: l_process_name := 'HXC_APPLY_NOTIFY';
3550:
3551: if g_debug then
3552: hr_utility.set_location(l_proc, 60);
3553: end if;
3554:
3555: l_approval_style_id := get_approval_style_id(l_tc_start_time,
3556: l_tc_stop_time,
3556: l_tc_stop_time,
3557: l_tc_resource_id);
3558: --
3559: if g_debug then
3560: hr_utility.set_location(l_proc, 70);
3561: --
3562: hr_utility.trace('l_approval_style_id is : ' || to_char(l_approval_style_id));
3563: -- For all the application periods created for this timecard,
3564: -- create a workflow process to continue with the approval - to
3558: --
3559: if g_debug then
3560: hr_utility.set_location(l_proc, 70);
3561: --
3562: hr_utility.trace('l_approval_style_id is : ' || to_char(l_approval_style_id));
3563: -- For all the application periods created for this timecard,
3564: -- create a workflow process to continue with the approval - to
3565: -- apply the approval rules and notify the approver(s).
3566:
3563: -- For all the application periods created for this timecard,
3564: -- create a workflow process to continue with the approval - to
3565: -- apply the approval rules and notify the approver(s).
3566:
3567: hr_utility.set_location(l_proc, 110);
3568: end if;
3569:
3570: open csr_get_appl_periods(l_tc_resource_id, l_tc_bld_blk_id);
3571:
3571:
3572: LOOP
3573:
3574: if g_debug then
3575: hr_utility.set_location(l_proc, 120);
3576: end if;
3577: fetch csr_get_appl_periods into l_appl_period_bb_id,
3578: l_period_start_date,
3579: l_period_end_date,
3587:
3588: exit when csr_get_appl_periods%NOTFOUND;
3589:
3590: if g_debug then
3591: hr_utility.trace('l_appl_period_bb_id is : ' ||
3592: to_char(l_appl_period_bb_id));
3593: hr_utility.trace('l_appl_period_bb_ovn is : ' ||
3594: to_char(l_appl_period_bb_ovn));
3595: hr_utility.trace('l_period_start_date is : ' ||
3589:
3590: if g_debug then
3591: hr_utility.trace('l_appl_period_bb_id is : ' ||
3592: to_char(l_appl_period_bb_id));
3593: hr_utility.trace('l_appl_period_bb_ovn is : ' ||
3594: to_char(l_appl_period_bb_ovn));
3595: hr_utility.trace('l_period_start_date is : ' ||
3596: to_char(l_period_start_date, 'DD-MM-YYYY'));
3597: hr_utility.trace('l_period_end_date is : ' ||
3591: hr_utility.trace('l_appl_period_bb_id is : ' ||
3592: to_char(l_appl_period_bb_id));
3593: hr_utility.trace('l_appl_period_bb_ovn is : ' ||
3594: to_char(l_appl_period_bb_ovn));
3595: hr_utility.trace('l_period_start_date is : ' ||
3596: to_char(l_period_start_date, 'DD-MM-YYYY'));
3597: hr_utility.trace('l_period_end_date is : ' ||
3598: to_char(l_period_end_date, 'DD-MM-YYYY'));
3599: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
3593: hr_utility.trace('l_appl_period_bb_ovn is : ' ||
3594: to_char(l_appl_period_bb_ovn));
3595: hr_utility.trace('l_period_start_date is : ' ||
3596: to_char(l_period_start_date, 'DD-MM-YYYY'));
3597: hr_utility.trace('l_period_end_date is : ' ||
3598: to_char(l_period_end_date, 'DD-MM-YYYY'));
3599: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
3600: hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
3601:
3595: hr_utility.trace('l_period_start_date is : ' ||
3596: to_char(l_period_start_date, 'DD-MM-YYYY'));
3597: hr_utility.trace('l_period_end_date is : ' ||
3598: to_char(l_period_end_date, 'DD-MM-YYYY'));
3599: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
3600: hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
3601:
3602: hr_utility.set_location(l_proc, 150);
3603: end if;
3596: to_char(l_period_start_date, 'DD-MM-YYYY'));
3597: hr_utility.trace('l_period_end_date is : ' ||
3598: to_char(l_period_end_date, 'DD-MM-YYYY'));
3599: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
3600: hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
3601:
3602: hr_utility.set_location(l_proc, 150);
3603: end if;
3604: -- Check to see if all the days in the application period have
3598: to_char(l_period_end_date, 'DD-MM-YYYY'));
3599: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
3600: hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
3601:
3602: hr_utility.set_location(l_proc, 150);
3603: end if;
3604: -- Check to see if all the days in the application period have
3605: -- submitted days.
3606: --
3612: l_process := 'N';
3613: END IF;
3614:
3615: if g_debug then
3616: hr_utility.trace('Checked days:'||l_chk_days);
3617: hr_utility.set_location(l_proc, 160);
3618: end if;
3619: IF l_chk_days = 'Y' AND l_process = 'Y' THEN
3620:
3613: END IF;
3614:
3615: if g_debug then
3616: hr_utility.trace('Checked days:'||l_chk_days);
3617: hr_utility.set_location(l_proc, 160);
3618: end if;
3619: IF l_chk_days = 'Y' AND l_process = 'Y' THEN
3620:
3621: if g_debug then
3618: end if;
3619: IF l_chk_days = 'Y' AND l_process = 'Y' THEN
3620:
3621: if g_debug then
3622: hr_utility.set_location(l_proc, 170);
3623: end if;
3624: --
3625: -- Check to see if all applications before this one in the approval
3626: -- style have approved all the days in this period.
3634: ,p_time_category_id => l_time_category_id
3635: ,p_category_sequence => l_category_sequence);
3636:
3637: if g_debug then
3638: hr_utility.trace('All previous approved:'||l_approved);
3639: end if;
3640:
3641: IF l_approved = 'Y' THEN
3642: OPEN c_period_notified(l_appl_period_bb_id);
3644:
3645: IF c_period_notified%NOTFOUND THEN
3646: CLOSE c_period_notified;
3647: if g_debug then
3648: hr_utility.trace('already processed ' || l_appl_period_bb_id);
3649: end if;
3650: ELSE
3651: CLOSE c_period_notified;
3652: if g_debug then
3649: end if;
3650: ELSE
3651: CLOSE c_period_notified;
3652: if g_debug then
3653: hr_utility.set_location(l_proc, 210);
3654: hr_utility.trace('itemtype is : ' || itemtype);
3655: hr_utility.trace('l_process_name is : ' || l_process_name);
3656: end if;
3657: --
3650: ELSE
3651: CLOSE c_period_notified;
3652: if g_debug then
3653: hr_utility.set_location(l_proc, 210);
3654: hr_utility.trace('itemtype is : ' || itemtype);
3655: hr_utility.trace('l_process_name is : ' || l_process_name);
3656: end if;
3657: --
3658: -- Setup l_item_key from a sequence.
3651: CLOSE c_period_notified;
3652: if g_debug then
3653: hr_utility.set_location(l_proc, 210);
3654: hr_utility.trace('itemtype is : ' || itemtype);
3655: hr_utility.trace('l_process_name is : ' || l_process_name);
3656: end if;
3657: --
3658: -- Setup l_item_key from a sequence.
3659: --
3678: where application_period_id = l_appl_period_bb_id
3679: and application_period_ovn = l_appl_period_bb_ovn;
3680:
3681: if g_debug then
3682: hr_utility.trace('l_item_key is : ' || l_item_key);
3683: end if;
3684:
3685: wf_engine.CreateProcess(itemtype => itemtype,
3686: itemkey => l_item_key,
3690: HXC_FIND_NOTIFY_APRS_PKG.get_login(p_person_id=>l_tc_resource_id,
3691: p_user_id => l_last_updated_by)
3692: );
3693: if g_debug then
3694: hr_utility.set_location(l_proc, 260);
3695: end if;
3696: if(item_attribute_exists(itemtype,l_item_key,'IS_DIFF_TC')) then
3697: wf_engine.SetItemAttrText(
3698: itemtype => itemtype,
3716: itemkey => l_item_key,
3717: aname => 'FORMATTED_APP_START_DATE',
3718: avalue => to_char(l_period_start_date,'YYYY/MM/DD'));
3719: if g_debug then
3720: hr_utility.set_location(l_proc, 270);
3721: hr_utility.trace('APP_START_DATE is : ' ||
3722: to_char(l_period_start_date, 'DD-MM-YYYY'));
3723: end if;
3724:
3717: aname => 'FORMATTED_APP_START_DATE',
3718: avalue => to_char(l_period_start_date,'YYYY/MM/DD'));
3719: if g_debug then
3720: hr_utility.set_location(l_proc, 270);
3721: hr_utility.trace('APP_START_DATE is : ' ||
3722: to_char(l_period_start_date, 'DD-MM-YYYY'));
3723: end if;
3724:
3725: wf_engine.SetItemAttrDate(itemtype => itemtype,
3727: aname => 'APP_END_DATE',
3728: avalue => l_period_end_date);
3729:
3730: if g_debug then
3731: hr_utility.set_location(l_proc, 280);
3732: hr_utility.trace('APP_END_DATE is : ' ||
3733: to_char(l_period_end_date, 'DD-MM-YYYY'));
3734: end if;
3735:
3728: avalue => l_period_end_date);
3729:
3730: if g_debug then
3731: hr_utility.set_location(l_proc, 280);
3732: hr_utility.trace('APP_END_DATE is : ' ||
3733: to_char(l_period_end_date, 'DD-MM-YYYY'));
3734: end if;
3735:
3736: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3738: aname => 'APP_BB_ID',
3739: avalue => l_appl_period_bb_id);
3740:
3741: if g_debug then
3742: hr_utility.set_location(l_proc, 290);
3743: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
3744: end if;
3745:
3746: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3739: avalue => l_appl_period_bb_id);
3740:
3741: if g_debug then
3742: hr_utility.set_location(l_proc, 290);
3743: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
3744: end if;
3745:
3746: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3747: itemkey => l_item_key,
3748: aname => 'APP_BB_OVN',
3749: avalue => l_appl_period_bb_ovn);
3750:
3751: if g_debug then
3752: hr_utility.set_location(l_proc, 300);
3753: hr_utility.trace('APP_BB_OVN is : ' ||
3754: to_char(l_appl_period_bb_ovn));
3755: end if;
3756:
3749: avalue => l_appl_period_bb_ovn);
3750:
3751: if g_debug then
3752: hr_utility.set_location(l_proc, 300);
3753: hr_utility.trace('APP_BB_OVN is : ' ||
3754: to_char(l_appl_period_bb_ovn));
3755: end if;
3756:
3757: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3759: aname => 'RESOURCE_ID',
3760: avalue => l_tc_resource_id);
3761:
3762: if g_debug then
3763: hr_utility.set_location(l_proc, 310);
3764: hr_utility.trace('RESOURCE_ID is : ' || to_char(l_tc_resource_id));
3765: end if;
3766:
3767: wf_engine.SetItemAttrText(itemtype => itemtype,
3760: avalue => l_tc_resource_id);
3761:
3762: if g_debug then
3763: hr_utility.set_location(l_proc, 310);
3764: hr_utility.trace('RESOURCE_ID is : ' || to_char(l_tc_resource_id));
3765: end if;
3766:
3767: wf_engine.SetItemAttrText(itemtype => itemtype,
3768: itemkey => l_item_key,
3769: aname => 'TIME_RECIPIENT_ID',
3770: avalue => l_time_recipient);
3771:
3772: if g_debug then
3773: hr_utility.set_location(l_proc, 320);
3774: hr_utility.trace('TIME_RECIPIENT_ID is : ' || l_time_recipient);
3775: end if;
3776:
3777: wf_engine.SetItemAttrText(itemtype => itemtype,
3770: avalue => l_time_recipient);
3771:
3772: if g_debug then
3773: hr_utility.set_location(l_proc, 320);
3774: hr_utility.trace('TIME_RECIPIENT_ID is : ' || l_time_recipient);
3775: end if;
3776:
3777: wf_engine.SetItemAttrText(itemtype => itemtype,
3778: itemkey => l_item_key,
3779: aname => 'TC_RESUBMITTED',
3780: avalue => l_tc_resubmitted);
3781:
3782: if g_debug then
3783: hr_utility.set_location(l_proc, 330);
3784: hr_utility.trace('TC_RESUBMITTED is : ' || l_tc_resubmitted);
3785: end if;
3786:
3787: wf_engine.SetItemAttrText(itemtype => itemtype,
3780: avalue => l_tc_resubmitted);
3781:
3782: if g_debug then
3783: hr_utility.set_location(l_proc, 330);
3784: hr_utility.trace('TC_RESUBMITTED is : ' || l_tc_resubmitted);
3785: end if;
3786:
3787: wf_engine.SetItemAttrText(itemtype => itemtype,
3788: itemkey => l_item_key,
3789: aname => 'BB_NEW',
3790: avalue => l_bb_new);
3791:
3792: if g_debug then
3793: hr_utility.set_location(l_proc, 335);
3794: hr_utility.trace('BB_NEW is : ' || l_bb_new);
3795: end if;
3796:
3797: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3790: avalue => l_bb_new);
3791:
3792: if g_debug then
3793: hr_utility.set_location(l_proc, 335);
3794: hr_utility.trace('BB_NEW is : ' || l_bb_new);
3795: end if;
3796:
3797: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3798: itemkey => l_item_key,
3799: aname => 'TC_BLD_BLK_ID',
3800: avalue => l_tc_bld_blk_id);
3801:
3802: if g_debug then
3803: hr_utility.set_location(l_proc, 340);
3804: hr_utility.trace('TC_BLD_BLK_ID is : ' || to_char(l_tc_bld_blk_id));
3805: end if;
3806:
3807: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3800: avalue => l_tc_bld_blk_id);
3801:
3802: if g_debug then
3803: hr_utility.set_location(l_proc, 340);
3804: hr_utility.trace('TC_BLD_BLK_ID is : ' || to_char(l_tc_bld_blk_id));
3805: end if;
3806:
3807: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3808: itemkey => l_item_key,
3809: aname => 'TC_BLD_BLK_OVN',
3810: avalue => l_tc_ovn);
3811:
3812: if g_debug then
3813: hr_utility.set_location(l_proc, 350);
3814: hr_utility.trace('TC_BLD_BLK_OVN is : ' || to_char(l_tc_ovn));
3815: end if;
3816:
3817: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3810: avalue => l_tc_ovn);
3811:
3812: if g_debug then
3813: hr_utility.set_location(l_proc, 350);
3814: hr_utility.trace('TC_BLD_BLK_OVN is : ' || to_char(l_tc_ovn));
3815: end if;
3816:
3817: wf_engine.SetItemAttrNumber(itemtype => itemtype,
3818: itemkey => l_item_key,
3845:
3846: -- Update attribute4 with NOTIFIED and attribute2 with the Item Key.
3847:
3848: if g_debug then
3849: hr_utility.trace('APP_BB_OVN is : ' ||
3850: to_char(l_appl_period_bb_ovn));
3851: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
3852: hr_utility.trace('Before Update');
3853: hr_utility.set_location(l_proc, 360);
3847:
3848: if g_debug then
3849: hr_utility.trace('APP_BB_OVN is : ' ||
3850: to_char(l_appl_period_bb_ovn));
3851: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
3852: hr_utility.trace('Before Update');
3853: hr_utility.set_location(l_proc, 360);
3854: end if;
3855:
3848: if g_debug then
3849: hr_utility.trace('APP_BB_OVN is : ' ||
3850: to_char(l_appl_period_bb_ovn));
3851: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
3852: hr_utility.trace('Before Update');
3853: hr_utility.set_location(l_proc, 360);
3854: end if;
3855:
3856: update hxc_app_period_summary
3849: hr_utility.trace('APP_BB_OVN is : ' ||
3850: to_char(l_appl_period_bb_ovn));
3851: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
3852: hr_utility.trace('Before Update');
3853: hr_utility.set_location(l_proc, 360);
3854: end if;
3855:
3856: update hxc_app_period_summary
3857: set notification_status = 'NOTIFIED'
3862: wf_engine.StartProcess(itemtype => itemtype,
3863: itemkey => l_item_key);
3864:
3865: if g_debug then
3866: hr_utility.set_location(l_proc, 365);
3867: end if;
3868: END IF; -- if not notified;
3869: END IF; -- approved
3870:
3870:
3871: END IF; -- l_chk_days
3872:
3873: if g_debug then
3874: hr_utility.set_location(l_proc, 380);
3875: end if;
3876:
3877: END LOOP;
3878:
3876:
3877: END LOOP;
3878:
3879: if g_debug then
3880: hr_utility.trace('OUTSIDE END LOOP');
3881: end if;
3882:
3883: close csr_get_appl_periods;
3884:
3889: -- The line below records this function call in the error system
3890: -- in the case of an exception.
3891: --
3892: if g_debug then
3893: hr_utility.set_location(l_proc, 999);
3894: --
3895: hr_utility.trace('IN EXCEPTION IN process_appl_periods');
3896: end if;
3897: --
3891: --
3892: if g_debug then
3893: hr_utility.set_location(l_proc, 999);
3894: --
3895: hr_utility.trace('IN EXCEPTION IN process_appl_periods');
3896: end if;
3897: --
3898: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.process_appl_periods',
3899: itemtype, itemkey, to_char(actid), funcmode);
4085: --
4086: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.start_approval_wf_process';
4087: --
4088: BEGIN
4089: g_debug:=hr_utility.debug_enabled;
4090: --
4091: if g_debug then
4092: hr_utility.set_location(l_proc, 10);
4093: --
4088: BEGIN
4089: g_debug:=hr_utility.debug_enabled;
4090: --
4091: if g_debug then
4092: hr_utility.set_location(l_proc, 10);
4093: --
4094: hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4095: hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4096: hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4090: --
4091: if g_debug then
4092: hr_utility.set_location(l_proc, 10);
4093: --
4094: hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4095: hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4096: hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4097: --
4098: end if;
4091: if g_debug then
4092: hr_utility.set_location(l_proc, 10);
4093: --
4094: hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4095: hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4096: hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4097: --
4098: end if;
4099: -- Nulls out the error table.
4092: hr_utility.set_location(l_proc, 10);
4093: --
4094: hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4095: hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4096: hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4097: --
4098: end if;
4099: -- Nulls out the error table.
4100: --
4117: itemkey => p_item_key,
4118: process => l_process_name);
4119: --
4120: if g_debug then
4121: hr_utility.set_location(l_proc, 20);
4122: end if;
4123: --
4124: wf_engine.SetItemAttrNumber(itemtype => p_item_type,
4125: itemkey => p_item_key,
4126: aname => 'TC_BLD_BLK_ID',
4127: avalue => p_tc_bb_id);
4128: --
4129: if g_debug then
4130: hr_utility.set_location(l_proc, 30);
4131: end if;
4132: --
4133: wf_engine.SetItemAttrNumber(itemtype => p_item_type,
4134: itemkey => p_item_key,
4135: aname => 'TC_BLD_BLK_OVN',
4136: avalue => p_tc_ovn);
4137: --
4138: if g_debug then
4139: hr_utility.set_location(l_proc, 40);
4140: end if;
4141: --
4142: wf_engine.SetItemAttrText(itemtype => p_item_type,
4143: itemkey => p_item_key,
4144: aname => 'TC_RESUBMITTED',
4145: avalue => p_tc_resubmitted);
4146: --
4147: if g_debug then
4148: hr_utility.set_location(l_proc, 50);
4149: end if;
4150: --
4151: IF p_bb_new = 'Y' THEN
4152: wf_engine.SetItemAttrText(itemtype => p_item_type,
4160: avalue => 'NO');
4161: END IF;
4162: --
4163: if g_debug then
4164: hr_utility.set_location(l_proc, 60);
4165: end if;
4166: --
4167: wf_engine.StartProcess(itemtype => p_item_type,
4168: itemkey => p_item_key);
4169: --
4170: wf_engine.threshold := 50;
4171: --
4172: if g_debug then
4173: hr_utility.set_location(l_proc, 70);
4174: end if;
4175: --
4176: p_error_table := g_error_table;
4177: --
4224: --
4225: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.upd_apr_details';
4226: --
4227: BEGIN
4228: g_debug:=hr_utility.debug_enabled;
4229: --
4230: if g_debug then
4231: hr_utility.set_location(l_proc, 10);
4232: --
4227: BEGIN
4228: g_debug:=hr_utility.debug_enabled;
4229: --
4230: if g_debug then
4231: hr_utility.set_location(l_proc, 10);
4232: --
4233: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4234: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4235: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4229: --
4230: if g_debug then
4231: hr_utility.set_location(l_proc, 10);
4232: --
4233: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4234: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4235: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4236: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4237: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4230: if g_debug then
4231: hr_utility.set_location(l_proc, 10);
4232: --
4233: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4234: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4235: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4236: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4237: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4238: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4231: hr_utility.set_location(l_proc, 10);
4232: --
4233: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4234: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4235: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4236: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4237: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4238: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4239: --
4232: --
4233: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4234: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4235: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4236: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4237: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4238: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4239: --
4240: hr_utility.set_location(l_proc, 20);
4233: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4234: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4235: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4236: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4237: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4238: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4239: --
4240: hr_utility.set_location(l_proc, 20);
4241: end if;
4234: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4235: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4236: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4237: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4238: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4239: --
4240: hr_utility.set_location(l_proc, 20);
4241: end if;
4242: -- Perf Rep Fix - SQL ID :3170802
4236: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4237: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4238: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4239: --
4240: hr_utility.set_location(l_proc, 20);
4241: end if;
4242: -- Perf Rep Fix - SQL ID :3170802
4243: -- Added attribute_category = APPROVAL to the where clause.
4244:
4255: and attribute3 = to_char(l_approver_id)
4256: and attribute_category = 'APPROVAL';
4257: --
4258: if g_debug then
4259: hr_utility.set_location(l_proc, 30);
4260: end if;
4261: --
4262: END upd_apr_details;
4263: --
4348: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.is_appr_required';
4349:
4350: --
4351: BEGIN
4352: g_debug:=hr_utility.debug_enabled;
4353:
4354: --
4355: if g_debug then
4356: hr_utility.set_location(l_proc, 10);
4352: g_debug:=hr_utility.debug_enabled;
4353:
4354: --
4355: if g_debug then
4356: hr_utility.set_location(l_proc, 10);
4357: end if;
4358: --
4359: l_app_bld_blk_id := wf_engine.GetItemAttrNumber
4360: (itemtype => itemtype,
4361: itemkey => itemkey ,
4362: aname => 'APP_BB_ID');
4363: --
4364: if g_debug then
4365: hr_utility.set_location(l_proc, 20);
4366: end if;
4367: --
4368: l_app_ovn := wf_engine.GetItemAttrNumber
4369: (itemtype => itemtype,
4375: itemkey => itemkey,
4376: aname => 'TC_BLD_BLK_ID');
4377: --
4378: if g_debug then
4379: hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
4380: end if;
4381: --
4382: l_tc_ovn := wf_engine.GetItemAttrNumber
4383: (itemtype => itemtype,
4389: itemkey => itemkey ,
4390: aname => 'TIME_RECIPIENT_ID');
4391:
4392: if g_debug then
4393: hr_utility.set_location(l_proc, 30);
4394: end if;
4395: --
4396: -- Sets up global variables for timecard records.
4397: --
4402: ,p_timecard_app_attributes => g_time_app_attributes
4403: ,p_time_recipient_id => to_number(l_time_recipient));
4404:
4405: if g_debug then
4406: hr_utility.set_location(l_proc, 40);
4407: end if;
4408: --
4409: -- Get the package.procedure from the extension_function1 column
4410: -- on hxc_time_recipients.
4439: --
4440: IF l_ext_func1 IS NOT NULL THEN
4441:
4442: if g_debug then
4443: hr_utility.set_location(l_proc, 50);
4444: end if;
4445:
4446: IF code_chk(l_ext_func1) THEN
4447:
4445:
4446: IF code_chk(l_ext_func1) THEN
4447:
4448: if g_debug then
4449: hr_utility.set_location(l_proc, 60);
4450: end if;
4451: --
4452: l_func_sql := 'BEGIN '||fnd_global.newline
4453: ||l_ext_func1 ||fnd_global.newline
4465: ELSE
4466: l_auto_approval_flag := 'N';
4467: END IF;
4468: if g_debug then
4469: hr_utility.set_location(l_proc, 70);
4470: end if;
4471: --
4472: IF l_auto_approval_flag = 'Y' THEN
4473:
4471: --
4472: IF l_auto_approval_flag = 'Y' THEN
4473:
4474: if g_debug then
4475: hr_utility.set_location(l_proc, 80);
4476: end if;
4477: --
4478: wf_engine.SetItemAttrText(itemtype => itemtype,
4479: itemkey => itemkey,
4482: --
4483: END IF;
4484:
4485: if g_debug then
4486: hr_utility.set_location(l_proc, 90);
4487: end if;
4488: --
4489: l_exception := NULL;
4490: --
4490: --
4491: IF l_message IS NOT NULL THEN
4492:
4493: if g_debug then
4494: hr_utility.set_location(l_proc, 100);
4495: end if;
4496: --
4497: l_message_table := hxc_deposit_wrapper_utilities.string_to_messages
4498: (p_message_string => l_message);
4531: --
4532: END IF;
4533:
4534: if g_debug then
4535: hr_utility.set_location(l_proc, 110);
4536: end if;
4537: --jxtan in new implementation, comment is save in hxc_time_building_blocks
4538: UPDATE hxc_time_building_blocks
4539: SET comment_text = substr(l_exception, 1, 2000)
4540: WHERE time_building_block_id = l_app_bld_blk_id
4541: AND object_version_number = l_app_ovn;
4542:
4543: if g_debug then
4544: hr_utility.set_location(l_proc, 120);
4545: end if;
4546: --
4547: wf_engine.SetItemAttrText(itemtype => itemtype,
4548: itemkey => itemkey,
4551: --
4552: END IF;
4553:
4554: if g_debug then
4555: hr_utility.set_location(l_proc, 130);
4556: end if;
4557: --
4558: IF upper(wf_engine.GetItemAttrText(itemtype => itemtype,
4559: itemkey => itemkey ,
4561: --
4562: result := 'COMPLETE:Y';
4563: --
4564: if g_debug then
4565: hr_utility.set_location(l_proc, 140);
4566: --
4567: hr_utility.trace('APPR_REQ attribute is : YES');
4568: --
4569: end if;
4563: --
4564: if g_debug then
4565: hr_utility.set_location(l_proc, 140);
4566: --
4567: hr_utility.trace('APPR_REQ attribute is : YES');
4568: --
4569: end if;
4570: return;
4571: --
4571: --
4572: ELSE
4573: --
4574: if g_debug then
4575: hr_utility.set_location(l_proc, 150);
4576: --
4577: hr_utility.trace('APPR_REQ attribute is : NO');
4578: --
4579: end if;
4573: --
4574: if g_debug then
4575: hr_utility.set_location(l_proc, 150);
4576: --
4577: hr_utility.trace('APPR_REQ attribute is : NO');
4578: --
4579: end if;
4580: wf_engine.SetItemAttrText(itemtype => itemtype,
4581: itemkey => itemkey,
4587: aname => 'APR_REJ_REASON',
4588: avalue => 'AUTO_APPROVE');
4589:
4590: if g_debug then
4591: hr_utility.set_location(l_proc, 160);
4592: --
4593: --
4594: hr_utility.trace('APPROVAL_STATUS attribute is : APPROVED');
4595: --
4590: if g_debug then
4591: hr_utility.set_location(l_proc, 160);
4592: --
4593: --
4594: hr_utility.trace('APPROVAL_STATUS attribute is : APPROVED');
4595: --
4596: end if;
4597: --OIT Enhancement.
4598: --FYI Notification to WORKER on timecard AUTO APPROVE
4607: --
4608: END IF;
4609: --
4610: if g_debug then
4611: hr_utility.set_location(l_proc, 170);
4612: end if;
4613: --
4614: exception
4615: when others then
4616: -- The line below records this function call in the error system
4617: -- in the case of an exception.
4618: --
4619: if g_debug then
4620: hr_utility.set_location(l_proc, 999);
4621: --
4622: hr_utility.trace('IN EXCEPTION IN is_appr_required');
4623: --
4624: end if;
4618: --
4619: if g_debug then
4620: hr_utility.set_location(l_proc, 999);
4621: --
4622: hr_utility.trace('IN EXCEPTION IN is_appr_required');
4623: --
4624: end if;
4625: wf_core.context('HCAPPRWF',
4626: 'hxc_approval_wf_pkg.is_appr_required',
4691: --
4692: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_appr_rules';
4693: --
4694: BEGIN
4695: g_debug:=hr_utility.debug_enabled;
4696: --
4697: if g_debug then
4698: hr_utility.set_location(l_proc, 10);
4699: end if;
4694: BEGIN
4695: g_debug:=hr_utility.debug_enabled;
4696: --
4697: if g_debug then
4698: hr_utility.set_location(l_proc, 10);
4699: end if;
4700: --
4701: l_app_bld_blk_id := wf_engine.GetItemAttrNumber
4702: (itemtype => itemtype,
4703: itemkey => itemkey ,
4704: aname => 'APP_BB_ID');
4705: --
4706: if g_debug then
4707: hr_utility.set_location(l_proc, 20);
4708: end if;
4709: --
4710: l_app_ovn := wf_engine.GetItemAttrNumber
4711: (itemtype => itemtype,
4712: itemkey => itemkey ,
4713: aname => 'APP_BB_OVN');
4714: --
4715: if g_debug then
4716: hr_utility.set_location(l_proc, 30);
4717: end if;
4718: --
4719: l_time_recipient := wf_engine.GetItemAttrText
4720: (itemtype => itemtype,
4721: itemkey => itemkey,
4722: aname => 'TIME_RECIPIENT_ID');
4723: --
4724: if g_debug then
4725: hr_utility.set_location(l_proc, 40);
4726: --
4727: hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
4728: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
4729: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4723: --
4724: if g_debug then
4725: hr_utility.set_location(l_proc, 40);
4726: --
4727: hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
4728: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
4729: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4730: --
4731:
4724: if g_debug then
4725: hr_utility.set_location(l_proc, 40);
4726: --
4727: hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
4728: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
4729: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4730: --
4731:
4732: hr_utility.set_location(l_proc, 70);
4725: hr_utility.set_location(l_proc, 40);
4726: --
4727: hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
4728: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
4729: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4730: --
4731:
4732: hr_utility.set_location(l_proc, 70);
4733: --
4728: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
4729: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4730: --
4731:
4732: hr_utility.set_location(l_proc, 70);
4733: --
4734: end if;
4735: l_tc_appr_style_id := wf_engine.GetItemAttrNumber
4736: (itemtype => itemtype,
4737: itemkey => itemkey,
4738: aname => 'APPROVAL_STYLE_ID');
4739: --
4740: if g_debug then
4741: hr_utility.trace('l_tc_appr_style_id is : ' || to_char(l_tc_appr_style_id));
4742: end if;
4743: --
4744: open csr_get_appr_rule_id(l_tc_appr_style_id,
4745: l_time_recipient);
4745: l_time_recipient);
4746: fetch csr_get_appr_rule_id into l_data_appr_rule_id;
4747: --
4748: if g_debug then
4749: hr_utility.set_location(l_proc, 80);
4750: --
4751: hr_utility.trace('l_data_appr_rule_id is : ' || to_char(l_data_appr_rule_id));
4752: --
4753: end if;
4747: --
4748: if g_debug then
4749: hr_utility.set_location(l_proc, 80);
4750: --
4751: hr_utility.trace('l_data_appr_rule_id is : ' || to_char(l_data_appr_rule_id));
4752: --
4753: end if;
4754: IF csr_get_appr_rule_id%NOTFOUND THEN
4755:
4758: result := 'COMPLETE:N';
4759: l_all_rules := 'NO_RULES';
4760: --
4761: if g_debug then
4762: hr_utility.set_location(l_proc, 90);
4763: --
4764: -- hr_utility.trace('Setting Status to Approved');
4765: --
4766: end if;
4760: --
4761: if g_debug then
4762: hr_utility.set_location(l_proc, 90);
4763: --
4764: -- hr_utility.trace('Setting Status to Approved');
4765: --
4766: end if;
4767: -- wf_engine.SetItemAttrText(itemtype => itemtype,
4768: -- itemkey => itemkey,
4775: --
4776: result := 'COMPLETE:Y';
4777: --
4778: if g_debug then
4779: hr_utility.set_location(l_proc, 100);
4780: end if;
4781: --
4782: l_cnt := 1;
4783: --
4798:
4799: CLOSE csr_get_appr_rule_id;
4800: --
4801: if g_debug then
4802: hr_utility.set_location(l_proc, 110);
4803: --
4804: hr_utility.trace('l_all_rules is : ' || l_all_rules);
4805: --
4806: end if;
4800: --
4801: if g_debug then
4802: hr_utility.set_location(l_proc, 110);
4803: --
4804: hr_utility.trace('l_all_rules is : ' || l_all_rules);
4805: --
4806: end if;
4807: END IF;
4808: --
4808: --
4809: IF l_all_rules <> 'NO_RULES' THEN
4810: --
4811: if g_debug then
4812: hr_utility.set_location(l_proc, 120);
4813: end if;
4814: --
4815: wf_engine.SetItemAttrText(itemtype => itemtype,
4816: itemkey => itemkey,
4817: aname => 'ALL_RULES',
4818: avalue => l_all_rules);
4819: --
4820: if g_debug then
4821: hr_utility.trace('ALL_RULES Attribute is : ' || l_all_rules);
4822: end if;
4823: --
4824: END IF;
4825: --
4830: -- The line below records this function call in the error system
4831: -- in the case of an exception.
4832: --
4833: if g_debug then
4834: hr_utility.set_location(l_proc, 999);
4835: --
4836: hr_utility.trace('IN EXCEPTION IN chk_appr_rules');
4837: --
4838: end if;
4832: --
4833: if g_debug then
4834: hr_utility.set_location(l_proc, 999);
4835: --
4836: hr_utility.trace('IN EXCEPTION IN chk_appr_rules');
4837: --
4838: end if;
4839: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.chk_appr_rules',
4840: itemtype, itemkey, to_char(actid), funcmode);
4863: -- l_item_key wf_items.item_key%type;
4864: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.find_approval_rule';
4865: --
4866: BEGIN
4867: g_debug:=hr_utility.debug_enabled;
4868: --
4869: if g_debug then
4870: hr_utility.set_location(l_proc, 10);
4871: end if;
4866: BEGIN
4867: g_debug:=hr_utility.debug_enabled;
4868: --
4869: if g_debug then
4870: hr_utility.set_location(l_proc, 10);
4871: end if;
4872: --
4873: l_all_rules := wf_engine.GetItemAttrText(itemtype => itemtype,
4874: itemkey => itemkey ,
4874: itemkey => itemkey ,
4875: aname => 'ALL_RULES');
4876: --
4877: if g_debug then
4878: hr_utility.set_location(l_proc, 20);
4879: --
4880: hr_utility.trace('ALL_RULES is : ' || l_all_rules);
4881: --
4882: end if;
4876: --
4877: if g_debug then
4878: hr_utility.set_location(l_proc, 20);
4879: --
4880: hr_utility.trace('ALL_RULES is : ' || l_all_rules);
4881: --
4882: end if;
4883: l_cnt := instr(l_all_rules, '|');
4884: --
4884: --
4885: IF l_cnt <> 0 THEN
4886: --
4887: if g_debug then
4888: hr_utility.set_location(l_proc, 30);
4889: end if;
4890: --
4891: l_current_rule := substr(l_all_rules, 1, l_cnt - 1);
4892: l_all_rules := replace(l_all_rules, l_current_rule || '|');
4891: l_current_rule := substr(l_all_rules, 1, l_cnt - 1);
4892: l_all_rules := replace(l_all_rules, l_current_rule || '|');
4893: --
4894: if g_debug then
4895: hr_utility.trace('l_current_rule is : ' || l_current_rule);
4896: end if;
4897: --
4898: result := 'COMPLETE:Y';
4899: --
4899: --
4900: ELSE
4901: --
4902: if g_debug then
4903: hr_utility.set_location(l_proc, 40);
4904: end if;
4905: --
4906: result := 'COMPLETE:N';
4907: --
4905: --
4906: result := 'COMPLETE:N';
4907: --
4908: if g_debug then
4909: hr_utility.trace('No More Rules - Setting status to APPROVED');
4910: end if;
4911: --
4912: wf_engine.SetItemAttrText(itemtype => itemtype,
4913: itemkey => itemkey,
4938: aname => 'CURRENT_RULE',
4939: avalue => l_current_rule);
4940: --
4941: if g_debug then
4942: hr_utility.set_location(l_proc, 60);
4943: --
4944: hr_utility.trace('CURRENT_RULE is : ' || l_current_rule);
4945: --
4946: end if;
4940: --
4941: if g_debug then
4942: hr_utility.set_location(l_proc, 60);
4943: --
4944: hr_utility.trace('CURRENT_RULE is : ' || l_current_rule);
4945: --
4946: end if;
4947: wf_engine.SetItemAttrText(itemtype => itemtype,
4948: itemkey => itemkey,
4949: aname => 'ALL_RULES',
4950: avalue => l_all_rules);
4951: --
4952: if g_debug then
4953: hr_utility.set_location(l_proc, 60);
4954: --
4955: hr_utility.trace('ALL_RULES is : ' || l_all_rules);
4956: --
4957: end if;
4951: --
4952: if g_debug then
4953: hr_utility.set_location(l_proc, 60);
4954: --
4955: hr_utility.trace('ALL_RULES is : ' || l_all_rules);
4956: --
4957: end if;
4958: return;
4959: --
4962: -- The line below records this function call in the error system
4963: -- in the case of an exception.
4964: --
4965: if g_debug then
4966: hr_utility.set_location(l_proc, 999);
4967: --
4968: hr_utility.trace('IN EXCEPTION IN find_approval_rule');
4969: --
4970: end if;
4964: --
4965: if g_debug then
4966: hr_utility.set_location(l_proc, 999);
4967: --
4968: hr_utility.trace('IN EXCEPTION IN find_approval_rule');
4969: --
4970: end if;
4971: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.find_approval_rule',
4972: itemtype, itemkey, to_char(actid), funcmode);
5151: -- l_item_key wf_items.item_key%type;
5152: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.execute_appr_rule';
5153: --
5154: BEGIN
5155: g_debug:=hr_utility.debug_enabled;
5156: --
5157: if g_debug then
5158: hr_utility.set_location(l_proc, 10);
5159: end if;
5154: BEGIN
5155: g_debug:=hr_utility.debug_enabled;
5156: --
5157: if g_debug then
5158: hr_utility.set_location(l_proc, 10);
5159: end if;
5160: --
5161:
5162:
5164: itemkey => itemkey,
5165: aname => 'CURRENT_RULE');
5166: --
5167: if g_debug then
5168: hr_utility.set_location(l_proc, 20);
5169: end if;
5170: --
5171: l_app_start_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
5172: itemkey => itemkey,
5172: itemkey => itemkey,
5173: aname => 'APP_START_DATE');
5174: --
5175: if g_debug then
5176: hr_utility.set_location(l_proc, 25);
5177: end if;
5178: --
5179: l_app_end_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
5180: itemkey => itemkey,
5180: itemkey => itemkey,
5181: aname => 'APP_END_DATE');
5182: --
5183: if g_debug then
5184: hr_utility.set_location(l_proc, 30);
5185: end if;
5186: --
5187: l_tc_resource_id := wf_engine.GetItemAttrNumber(
5188: itemtype => itemtype,
5189: itemkey => itemkey,
5190: aname => 'RESOURCE_ID');
5191: --
5192: if g_debug then
5193: hr_utility.set_location(l_proc, 40);
5194: end if;
5195: --
5196: l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
5197: (itemtype => itemtype,
5198: itemkey => itemkey,
5199: aname => 'TC_BLD_BLK_ID');
5200: --
5201: if g_debug then
5202: hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
5203: end if;
5204: --
5205: l_tc_ovn := wf_engine.GetItemAttrNumber
5206: (itemtype => itemtype,
5207: itemkey => itemkey,
5208: aname => 'TC_BLD_BLK_OVN');
5209: --
5210: if g_debug then
5211: hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
5212: end if;
5213: --
5214: -- Set up l_resubmit to indicate whether this is a resubmission or not.
5215: -- (YES means it is a resubmission; NO means it is a submission).
5218: itemkey => itemkey,
5219: aname => 'TC_RESUBMITTED');
5220: --
5221: if g_debug then
5222: hr_utility.set_location(l_proc, 50);
5223: end if;
5224: --
5225: l_bb_new := wf_engine.GetItemAttrText(itemtype => itemtype,
5226: itemkey => itemkey,
5226: itemkey => itemkey,
5227: aname => 'BB_NEW');
5228: --
5229: if g_debug then
5230: hr_utility.set_location(l_proc, 52);
5231: end if;
5232: --
5233: l_data_appr_rule_id := to_number(l_current_rule);
5234: --
5232: --
5233: l_data_appr_rule_id := to_number(l_current_rule);
5234: --
5235: if g_debug then
5236: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5237: hr_utility.trace('l_app_end_date is : ' ||
5238: to_char(l_app_end_date, 'DD-MM-YYYY'));
5239: hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5240: hr_utility.trace('l_resubmit is : ' || l_resubmit);
5233: l_data_appr_rule_id := to_number(l_current_rule);
5234: --
5235: if g_debug then
5236: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5237: hr_utility.trace('l_app_end_date is : ' ||
5238: to_char(l_app_end_date, 'DD-MM-YYYY'));
5239: hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5240: hr_utility.trace('l_resubmit is : ' || l_resubmit);
5241: end if;
5235: if g_debug then
5236: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5237: hr_utility.trace('l_app_end_date is : ' ||
5238: to_char(l_app_end_date, 'DD-MM-YYYY'));
5239: hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5240: hr_utility.trace('l_resubmit is : ' || l_resubmit);
5241: end if;
5242: --
5243: open csr_get_appr_rule_info(l_current_rule, l_app_end_date);
5236: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5237: hr_utility.trace('l_app_end_date is : ' ||
5238: to_char(l_app_end_date, 'DD-MM-YYYY'));
5239: hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5240: hr_utility.trace('l_resubmit is : ' || l_resubmit);
5241: end if;
5242: --
5243: open csr_get_appr_rule_info(l_current_rule, l_app_end_date);
5244: fetch csr_get_appr_rule_info into l_rule_rec;
5244: fetch csr_get_appr_rule_info into l_rule_rec;
5245: close csr_get_appr_rule_info;
5246: --
5247: if g_debug then
5248: hr_utility.set_location(l_proc, 60);
5249: end if;
5250: --
5251: IF (l_resubmit = 'YES' AND l_rule_rec.rule_usage <> 'SUBMISSION') OR
5252: (l_resubmit = 'NO' AND l_rule_rec.rule_usage <> 'RESUBMISSION') THEN
5251: IF (l_resubmit = 'YES' AND l_rule_rec.rule_usage <> 'SUBMISSION') OR
5252: (l_resubmit = 'NO' AND l_rule_rec.rule_usage <> 'RESUBMISSION') THEN
5253: --
5254: if g_debug then
5255: hr_utility.set_location(l_proc, 70);
5256: end if;
5257: --
5258: -- Apply rule
5259: --
5267: -- Then, check for formula, since it is possible to have both
5268: -- a mapping and a formula.
5269: --
5270: if g_debug then
5271: hr_utility.set_location(l_proc, 80);
5272: end if;
5273: --
5274: IF l_resubmit = 'YES' THEN
5275: --
5273: --
5274: IF l_resubmit = 'YES' THEN
5275: --
5276: if g_debug then
5277: hr_utility.set_location(l_proc, 90);
5278: end if;
5279: l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
5280: itemtype => itemtype,
5281: itemkey => itemkey,
5281: itemkey => itemkey,
5282: aname => 'APP_BB_ID');
5283:
5284: if g_debug then
5285: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
5286: end if;
5287: l_appl_period_bb_ovn := wf_engine.GetItemAttrNumber(
5288: itemtype => itemtype,
5289: itemkey => itemkey,
5326: --
5327: IF l_bld_blk_changed THEN
5328: --
5329: if g_debug then
5330: hr_utility.set_location(l_proc, 130);
5331: end if;
5332: --
5333: l_changed := 'YES';
5334: --
5334: --
5335: ELSE
5336: --
5337: if g_debug then
5338: hr_utility.set_location(l_proc, 140);
5339: end if;
5340: --
5341: l_mapping_changed := hxc_mapping_utilities.chk_mapping_changed(
5342: p_mapping_id => l_rule_rec.mapping_id
5348: ,p_time_building_blocks => g_time_building_blocks
5349: ,p_time_attributes => g_time_attributes);
5350: --
5351: if g_debug then
5352: hr_utility.set_location(l_proc, 120);
5353: end if;
5354: --
5355: -- If there are differences, set l_changed to YES; else set to NO.
5356: --
5356: --
5357: IF l_mapping_changed THEN
5358: --
5359: if g_debug then
5360: hr_utility.set_location(l_proc, 130);
5361: end if;
5362: --
5363: l_changed := 'YES';
5364: --
5364: --
5365: ELSE
5366: --
5367: if g_debug then
5368: hr_utility.set_location(l_proc, 140);
5369: end if;
5370: --
5371: l_changed := 'NO';
5372: --
5378: --
5379: -- Set l_changed to YES
5380: --
5381: if g_debug then
5382: hr_utility.set_location(l_proc, 150);
5383: end if;
5384: --
5385: l_changed := 'YES';
5386: --
5391: -- No Mapping ID, but there might still be a formula so set
5392: -- l_changed to YES
5393: --
5394: if g_debug then
5395: hr_utility.set_location(l_proc, 160);
5396: end if;
5397: --
5398: l_changed := 'NO'; -- GPM v115.48 WWB 2724576
5399: --
5399: --
5400: END IF;
5401: --
5402: if g_debug then
5403: hr_utility.set_location(l_proc, 170);
5404: end if;
5405: --
5406: -- Check to see is a formula needs to be applied.
5407: --
5408: IF (l_rule_rec.formula_id IS NOT NULL AND l_changed = 'NO') -- GPM v115.48 WWB 2724576
5409: THEN
5410: --
5411: if g_debug then
5412: hr_utility.set_location(l_proc, 180);
5413: --
5414: hr_utility.trace('l_formula_name is : ' || l_rule_rec.formula_name);
5415: --
5416: hr_utility.set_location(l_proc, 190);
5410: --
5411: if g_debug then
5412: hr_utility.set_location(l_proc, 180);
5413: --
5414: hr_utility.trace('l_formula_name is : ' || l_rule_rec.formula_name);
5415: --
5416: hr_utility.set_location(l_proc, 190);
5417: end if;
5418: --
5412: hr_utility.set_location(l_proc, 180);
5413: --
5414: hr_utility.trace('l_formula_name is : ' || l_rule_rec.formula_name);
5415: --
5416: hr_utility.set_location(l_proc, 190);
5417: end if;
5418: --
5419: -- call execute approval formula
5420: --
5445: l_result := 'NO';
5446: END IF;
5447: --
5448: IF upper(l_result) <> 'YES' AND upper(l_result) <> 'NO' THEN
5449: hr_utility.raise_error;
5450: END IF;
5451: --
5452: IF l_error_table.count > 0 THEN
5453: hr_utility.set_message(809, l_error_table(1).message_name);
5449: hr_utility.raise_error;
5450: END IF;
5451: --
5452: IF l_error_table.count > 0 THEN
5453: hr_utility.set_message(809, l_error_table(1).message_name);
5454: hr_utility.raise_error;
5455: END IF;
5456: --
5457: wf_engine.SetItemAttrText(itemtype => itemtype,
5450: END IF;
5451: --
5452: IF l_error_table.count > 0 THEN
5453: hr_utility.set_message(809, l_error_table(1).message_name);
5454: hr_utility.raise_error;
5455: END IF;
5456: --
5457: wf_engine.SetItemAttrText(itemtype => itemtype,
5458: itemkey => itemkey,
5459: aname => 'TO_APPROVE',
5460: avalue => upper(l_result));
5461: --
5462: if g_debug then
5463: hr_utility.trace('TO_APPROVE is : ' || l_result);
5464: end if;
5465: --
5466: ELSE
5467: --
5467: --
5468: -- Set TO_APPROVE to l_changed.
5469: --
5470: if g_debug then
5471: hr_utility.set_location(l_proc, 230);
5472: end if;
5473: --
5474: wf_engine.SetItemAttrText(itemtype => itemtype,
5475: itemkey => itemkey,
5476: aname => 'TO_APPROVE',
5477: avalue => l_changed);
5478: --
5479: if g_debug then
5480: hr_utility.trace('TO_APPROVE is : ' || l_changed);
5481: end if;
5482: --
5483: END IF;
5484: --
5486: --
5487: -- Set TO_APPROVE attribute to YES since the rule does not apply.
5488: --
5489: if g_debug then
5490: hr_utility.set_location(l_proc, 240);
5491: end if;
5492: --
5493: -- Modifying the 'TO_APPROVE' value from 'NO' to 'YES' and Commenting out the following code for bug#3497011.
5494:
5497: aname => 'TO_APPROVE',
5498: avalue => 'NO');
5499: --
5500: if g_debug then
5501: hr_utility.trace('TO_APPROVE is : NO');
5502: end if; */
5503:
5504: wf_engine.SetItemAttrText(itemtype => itemtype,
5505: itemkey => itemkey,
5506: aname => 'TO_APPROVE',
5507: avalue => 'YES');
5508: --
5509: if g_debug then
5510: hr_utility.trace('TO_APPROVE is : YES');
5511: end if;--
5512: END IF;
5513: --
5514: if g_debug then
5511: end if;--
5512: END IF;
5513: --
5514: if g_debug then
5515: hr_utility.set_location(l_proc, 250);
5516: end if;
5517: --
5518: --
5519: result := '';
5523: when others then
5524: -- The line below records this function call in the error system
5525: -- in the case of an exception.
5526: if g_debug then
5527: hr_utility.set_location(l_proc, 999);
5528: --
5529: hr_utility.trace('IN EXCEPTION IN execute_appr_rule');
5530: end if;
5531: --
5525: -- in the case of an exception.
5526: if g_debug then
5527: hr_utility.set_location(l_proc, 999);
5528: --
5529: hr_utility.trace('IN EXCEPTION IN execute_appr_rule');
5530: end if;
5531: --
5532: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.execute_appr_rule',
5533: itemtype, itemkey, to_char(actid), funcmode);
5555: -- l_item_key wf_items.item_key%type;
5556: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_approval_req';
5557: --
5558: BEGIN
5559: g_debug:=hr_utility.debug_enabled;
5560: --
5561: if g_debug then
5562: hr_utility.set_location(l_proc, 10);
5563: end if;
5558: BEGIN
5559: g_debug:=hr_utility.debug_enabled;
5560: --
5561: if g_debug then
5562: hr_utility.set_location(l_proc, 10);
5563: end if;
5564: --
5565: l_to_approve := wf_engine.GetItemAttrText(itemtype => itemtype,
5566: itemkey => itemkey ,
5566: itemkey => itemkey ,
5567: aname => 'TO_APPROVE');
5568: --
5569: if g_debug then
5570: hr_utility.set_location(l_proc, 20);
5571: end if;
5572: --
5573: if g_debug then
5574: hr_utility.trace('l_to_approve is : ' || l_to_approve);
5570: hr_utility.set_location(l_proc, 20);
5571: end if;
5572: --
5573: if g_debug then
5574: hr_utility.trace('l_to_approve is : ' || l_to_approve);
5575: end if;
5576: --
5577: IF l_to_approve = 'YES' THEN
5578: --
5576: --
5577: IF l_to_approve = 'YES' THEN
5578: --
5579: if g_debug then
5580: hr_utility.set_location(l_proc, 30);
5581: end if;
5582: --
5583: result := 'COMPLETE:Y';
5584: --
5586: --
5587: ELSE
5588: --
5589: if g_debug then
5590: hr_utility.set_location(l_proc, 40);
5591: end if;
5592: --
5593: result := 'COMPLETE:N';
5594: --
5601: -- The line below records this function call in the error system
5602: -- in the case of an exception.
5603: --
5604: if g_debug then
5605: hr_utility.set_location(l_proc, 999);
5606: --
5607: hr_utility.trace('IN EXCEPTION IN chk_approval_req');
5608: end if;
5609: --
5603: --
5604: if g_debug then
5605: hr_utility.set_location(l_proc, 999);
5606: --
5607: hr_utility.trace('IN EXCEPTION IN chk_approval_req');
5608: end if;
5609: --
5610: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.chk_approval_req',
5611: itemtype, itemkey, to_char(actid), funcmode);