[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(
509: open c_block(p_app_id, p_block_id);
510: fetch c_block into l_block_ovn;
511:
512: if g_debug then
513: hr_utility.trace('OTL:Entered same_block');
514: hr_utility.trace('OTL:p_app_id = '||p_app_id);
515: hr_utility.trace('OTL:p_block_id = '||p_block_id);
516: hr_utility.trace('OTL:p_block_ovn = '||p_block_ovn);
517: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
510: fetch c_block into l_block_ovn;
511:
512: if g_debug then
513: hr_utility.trace('OTL:Entered same_block');
514: hr_utility.trace('OTL:p_app_id = '||p_app_id);
515: hr_utility.trace('OTL:p_block_id = '||p_block_id);
516: hr_utility.trace('OTL:p_block_ovn = '||p_block_ovn);
517: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
518: end if;
511:
512: if g_debug then
513: hr_utility.trace('OTL:Entered same_block');
514: hr_utility.trace('OTL:p_app_id = '||p_app_id);
515: hr_utility.trace('OTL:p_block_id = '||p_block_id);
516: hr_utility.trace('OTL:p_block_ovn = '||p_block_ovn);
517: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
518: end if;
519:
512: if g_debug then
513: hr_utility.trace('OTL:Entered same_block');
514: hr_utility.trace('OTL:p_app_id = '||p_app_id);
515: hr_utility.trace('OTL:p_block_id = '||p_block_id);
516: hr_utility.trace('OTL:p_block_ovn = '||p_block_ovn);
517: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
518: end if;
519:
520: if (c_block%notfound) then
513: hr_utility.trace('OTL:Entered same_block');
514: hr_utility.trace('OTL:p_app_id = '||p_app_id);
515: hr_utility.trace('OTL:p_block_id = '||p_block_id);
516: hr_utility.trace('OTL:p_block_ovn = '||p_block_ovn);
517: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
518: end if;
519:
520: if (c_block%notfound) then
521: close c_block;
533: fetch c_test_translation_key into l_block_ovn, l_trans_disp_key1, l_trans_disp_key2;
534: if(c_test_translation_key%found) then
535: close c_test_translation_key;
536: if g_debug then
537: hr_utility.trace('OTL: translation display key%FOUND');
538: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
539: hr_utility.trace('OTL:l_trans_disp_key1 = '||l_trans_disp_key1);
540: hr_utility.trace('OTL:l_trans_disp_key2 = '||l_trans_disp_key2);
541: end if;
534: if(c_test_translation_key%found) then
535: close c_test_translation_key;
536: if g_debug then
537: hr_utility.trace('OTL: translation display key%FOUND');
538: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
539: hr_utility.trace('OTL:l_trans_disp_key1 = '||l_trans_disp_key1);
540: hr_utility.trace('OTL:l_trans_disp_key2 = '||l_trans_disp_key2);
541: end if;
542: -- Added this IF block for bug 9747521
535: close c_test_translation_key;
536: if g_debug then
537: hr_utility.trace('OTL: translation display key%FOUND');
538: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
539: hr_utility.trace('OTL:l_trans_disp_key1 = '||l_trans_disp_key1);
540: hr_utility.trace('OTL:l_trans_disp_key2 = '||l_trans_disp_key2);
541: end if;
542: -- Added this IF block for bug 9747521
543: IF ((l_trans_disp_key1 IS NULL and l_trans_disp_key2 IS NOT NULL)
536: if g_debug then
537: hr_utility.trace('OTL: translation display key%FOUND');
538: hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
539: hr_utility.trace('OTL:l_trans_disp_key1 = '||l_trans_disp_key1);
540: hr_utility.trace('OTL:l_trans_disp_key2 = '||l_trans_disp_key2);
541: end if;
542: -- Added this IF block for bug 9747521
543: IF ((l_trans_disp_key1 IS NULL and l_trans_disp_key2 IS NOT NULL)
544: OR
549: return true;
550: else
551: close c_test_translation_key;
552: if g_debug then
553: hr_utility.trace('OTL: translation display key%NOTFOUND');
554: end if;
555:
556: return false;
557: end if;
612: l_same boolean := true;
613:
614: l_proc varchar2(50) := 'HXC_APPROVAL_WF_PKG.changed';
615: BEGIN
616: g_debug:=hr_utility.debug_enabled;
617: if g_debug then
618: hr_utility.set_location(l_proc, 10);
619: end if;
620:
614: l_proc varchar2(50) := 'HXC_APPROVAL_WF_PKG.changed';
615: BEGIN
616: g_debug:=hr_utility.debug_enabled;
617: if g_debug then
618: hr_utility.set_location(l_proc, 10);
619: end if;
620:
621: IF p_time_category_id IS NULL OR p_time_category_id = 0
622: THEN
623: l_block_index := p_detail_blocks.first;
624: LOOP
625: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
626: if g_debug then
627: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
628: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
629: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
630: end if;
631:
624: LOOP
625: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
626: if g_debug then
627: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
628: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
629: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
630: end if;
631:
632: IF p_detail_blocks(l_block_index).added <> 'Y'
625: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
626: if g_debug then
627: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
628: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
629: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
630: end if;
631:
632: IF p_detail_blocks(l_block_index).added <> 'Y'
633: THEN
632: IF p_detail_blocks(l_block_index).added <> 'Y'
633: THEN
634: l_count := l_count + 1;
635: if g_debug then
636: hr_utility.set_location(l_proc, 20);
637: end if;
638:
639: IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
640: , p_detail_blocks(l_block_index).block_ovn)
639: IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
640: , p_detail_blocks(l_block_index).block_ovn)
641: THEN
642: if g_debug then
643: hr_utility.set_location(l_proc, 30);
644: end if;
645:
646: g_block_exist_for_ap := 'Y';
647:
654: END LOOP;
655:
656: ELSE
657: if g_debug then
658: hr_utility.set_location(l_proc, 40);
659: end if;
660:
661: hxc_time_category_utils_pkg.initialise_time_category(
662: p_time_category_id => p_time_category_id
674: IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
675: , p_detail_blocks(l_block_index).block_ovn)
676: THEN
677: if g_debug then
678: hr_utility.set_location(l_proc, 60);
679: end if;
680:
681: g_block_exist_for_ap := 'Y';
682:
691:
692: IF no_blocks(p_app_id, p_timecard_id) = 0
693: THEN
694: if g_debug then
695: hr_utility.trace('number not changed');
696: end if;
697: RETURN FALSE;
698: ELSE
699: if g_debug then
696: end if;
697: RETURN FALSE;
698: ELSE
699: if g_debug then
700: hr_utility.trace('number changed');
701: end if;
702: RETURN TRUE;
703: END IF;
704: END changed;
791: LOOP
792: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
793:
794: if g_debug then
795: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
796: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
797: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
798: end if;
799:
792: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
793:
794: if g_debug then
795: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
796: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
797: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
798: end if;
799:
800: IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
793:
794: if g_debug then
795: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
796: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
797: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
798: end if;
799:
800: IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
801: p_detail_blocks(l_block_index).block_id
831: LOOP
832: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
833:
834: if g_debug then
835: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
836: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
837: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
838: end if;
839:
832: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
833:
834: if g_debug then
835: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
836: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
837: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
838: end if;
839:
840: IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
833:
834: if g_debug then
835: hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
836: hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
837: hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
838: end if;
839:
840: IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
841: p_detail_blocks(l_block_index).block_id
852: ,p_detail_blocks(l_block_index).block_ovn
853: );
854:
855: if g_debug then
856: hr_utility.trace('linked!');
857: end if;
858:
859: END IF;
860:
873:
874: BEGIN
875:
876: if g_debug then
877: hr_utility.trace('in link_ap_details_all');
878: end if;
879:
880: l_block_index := p_detail_blocks.first;
881: LOOP
881: LOOP
882: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
883:
884: if g_debug then
885: hr_utility.trace('block_id=' || p_detail_blocks(l_block_index).block_id);
886: hr_utility.trace('block_ovn=' || p_detail_blocks(l_block_index).block_ovn);
887: hr_utility.trace('added=' || p_detail_blocks(l_block_index).added);
888: end if;
889: l_block_index := p_detail_blocks.next(l_block_index);
882: EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
883:
884: if g_debug then
885: hr_utility.trace('block_id=' || p_detail_blocks(l_block_index).block_id);
886: hr_utility.trace('block_ovn=' || p_detail_blocks(l_block_index).block_ovn);
887: hr_utility.trace('added=' || p_detail_blocks(l_block_index).added);
888: end if;
889: l_block_index := p_detail_blocks.next(l_block_index);
890: END LOOP;
883:
884: if g_debug then
885: hr_utility.trace('block_id=' || p_detail_blocks(l_block_index).block_id);
886: hr_utility.trace('block_ovn=' || p_detail_blocks(l_block_index).block_ovn);
887: hr_utility.trace('added=' || p_detail_blocks(l_block_index).added);
888: end if;
889: l_block_index := p_detail_blocks.next(l_block_index);
890: END LOOP;
891:
899: THEN
900: --set added flag
901: --we use this flag to find all the category 0 blocks
902: if g_debug then
903: hr_utility.trace('inserting id=' || p_detail_blocks(l_block_index).block_id
904: || '|ovn=' || p_detail_blocks(l_block_index).block_ovn);
905: end if;
906:
907: p_detail_blocks(l_block_index).added := 'Y';
921:
922: END LOOP;
923:
924: if g_debug then
925: hr_utility.trace('end link_ap_details_all');
926: end if;
927: END link_ap_details_all;
928:
929: FUNCTION get_person_id(
1116: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.update_appl_period';
1117:
1118:
1119: BEGIN
1120: g_debug:=hr_utility.debug_enabled;
1121: if g_debug then
1122: hr_utility.set_location(l_proc, 10);
1123: end if;
1124:
1118:
1119: BEGIN
1120: g_debug:=hr_utility.debug_enabled;
1121: if g_debug then
1122: hr_utility.set_location(l_proc, 10);
1123: end if;
1124:
1125: l_tc_resource_id := wf_engine.GetItemAttrNumber(
1126: itemtype => itemtype,
1128: aname => 'RESOURCE_ID');
1129:
1130:
1131: if g_debug then
1132: hr_utility.set_location(l_proc, 30);
1133: end if;
1134:
1135: l_period_start_date := wf_engine.GetItemAttrDate(
1136: itemtype => itemtype,
1137: itemkey => itemkey ,
1138: aname => 'APP_START_DATE');
1139:
1140: if g_debug then
1141: hr_utility.set_location(l_proc, 40);
1142: end if;
1143:
1144: l_period_end_date := wf_engine.GetItemAttrDate(
1145: itemtype => itemtype,
1146: itemkey => itemkey ,
1147: aname => 'APP_END_DATE');
1148:
1149: if g_debug then
1150: hr_utility.set_location(l_proc, 50);
1151: end if;
1152:
1153: l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
1154: itemtype => itemtype,
1155: itemkey => itemkey,
1156: aname => 'APP_BB_ID');
1157:
1158: if g_debug then
1159: hr_utility.set_location(l_proc, 60);
1160: end if;
1161:
1162: l_appl_period_bb_ovn := wf_engine.GetItemAttrNumber(
1163: itemtype => itemtype,
1164: itemkey => itemkey,
1165: aname => 'APP_BB_OVN');
1166:
1167: if g_debug then
1168: hr_utility.set_location(l_proc, 70);
1169: end if;
1170:
1171: -- Set up the approval status - get the value for the APPROVAL_STATUS
1172: -- attribute, which is set up in the activity previous to this one.
1181: itemkey => itemkey,
1182: aname => 'APR_REJ_REASON');
1183:
1184: if g_debug then
1185: hr_utility.set_location(l_proc, 80);
1186:
1187: hr_utility.trace('l_approval_status is : ' || l_approval_status);
1188: end if;
1189:
1183:
1184: if g_debug then
1185: hr_utility.set_location(l_proc, 80);
1186:
1187: hr_utility.trace('l_approval_status is : ' || l_approval_status);
1188: end if;
1189:
1190: --get approver id
1191: --what happens to l_approver if AUTO_APPROVE??
1224: i.e. fnd_global.employee_id is
1225:
1226: */
1227:
1228: hr_utility.trace('OTL:pass 100 - '||itemkey);
1229:
1230: -- Added for bug 9076079
1231: IF g_old_item_key.exists(itemkey) then
1232: hr_utility.trace('OTL: Exists in g table');
1228: hr_utility.trace('OTL:pass 100 - '||itemkey);
1229:
1230: -- Added for bug 9076079
1231: IF g_old_item_key.exists(itemkey) then
1232: hr_utility.trace('OTL: Exists in g table');
1233: l_is_blank := g_old_item_key(itemkey).is_diff_tc;
1234: ELSE
1235: hr_utility.trace('OTL: Not exists in g table');
1236: l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
1231: IF g_old_item_key.exists(itemkey) then
1232: hr_utility.trace('OTL: Exists in g table');
1233: l_is_blank := g_old_item_key(itemkey).is_diff_tc;
1234: ELSE
1235: hr_utility.trace('OTL: Not exists in g table');
1236: l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
1237: itemkey => itemkey ,
1238: aname => 'IS_DIFF_TC',
1239: ignore_notfound => true);
1238: aname => 'IS_DIFF_TC',
1239: ignore_notfound => true);
1240: END IF;
1241:
1242: hr_utility.trace('OTL:pass 110');
1243:
1244: if l_is_blank = 'Y' then
1245: l_approver_comment := l_approver_comment ||'BLANK_NOTIFICATION';
1246: end if;
1274: l_approver := find_mysterious_approver(itemtype,itemkey);
1275: end if;
1276:
1277: if g_debug then
1278: hr_utility.set_location(l_proc, 90);
1279: end if;
1280:
1281: --update hxc_application_period_summary table
1282: l_creation_date := get_creation_date(l_appl_period_bb_id, l_appl_period_bb_ovn);
1303: END IF;
1304:
1305:
1306: if g_debug then
1307: hr_utility.set_location(l_proc, 110);
1308: end if;
1309:
1310: /*
1311: Since this could be the last operation in the approvals process
1327: -- The line below records this function call in the error system
1328: -- in the case of an exception.
1329: --
1330: if g_debug then
1331: hr_utility.trace(sqlerrm);
1332: hr_utility.trace('lllllllllllllllllll');
1333: hr_utility.trace(hr_message.last_message_name);
1334: hr_utility.trace('----');
1335: end if;
1328: -- in the case of an exception.
1329: --
1330: if g_debug then
1331: hr_utility.trace(sqlerrm);
1332: hr_utility.trace('lllllllllllllllllll');
1333: hr_utility.trace(hr_message.last_message_name);
1334: hr_utility.trace('----');
1335: end if;
1336: IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1329: --
1330: if g_debug then
1331: hr_utility.trace(sqlerrm);
1332: hr_utility.trace('lllllllllllllllllll');
1333: hr_utility.trace(hr_message.last_message_name);
1334: hr_utility.trace('----');
1335: end if;
1336: IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1337: RETURN;
1330: if g_debug then
1331: hr_utility.trace(sqlerrm);
1332: hr_utility.trace('lllllllllllllllllll');
1333: hr_utility.trace(hr_message.last_message_name);
1334: hr_utility.trace('----');
1335: end if;
1336: IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1337: RETURN;
1338: END IF;
1337: RETURN;
1338: END IF;
1339: --
1340: if g_debug then
1341: hr_utility.set_location(l_proc, 999);
1342: hr_utility.trace('IN EXCEPTION IN update_appl_period');
1343: end if;
1344: --
1345: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.update_appl_period',
1338: END IF;
1339: --
1340: if g_debug then
1341: hr_utility.set_location(l_proc, 999);
1342: hr_utility.trace('IN EXCEPTION IN update_appl_period');
1343: end if;
1344: --
1345: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.update_appl_period',
1346: itemtype, itemkey, to_char(actid), funcmode);
1403: l_creation_date hxc_time_building_blocks.creation_date%TYPE;
1404:
1405: l_proc VARCHAR2(150) := 'create_next_period';
1406: BEGIN
1407: g_debug:=hr_utility.debug_enabled;
1408: if g_debug then
1409: hr_utility.trace('in create_next_period');
1410: end if;
1411:
1405: l_proc VARCHAR2(150) := 'create_next_period';
1406: BEGIN
1407: g_debug:=hr_utility.debug_enabled;
1408: if g_debug then
1409: hr_utility.trace('in create_next_period');
1410: end if;
1411:
1412: IF funcmode = 'RUN'
1413: THEN
1454: ,p_time_building_block_id => l_new_appl_bb_id
1455: ,p_object_version_number => l_new_appl_bb_ovn);
1456:
1457: if g_debug then
1458: hr_utility.trace('next period created=' || l_new_appl_bb_id);
1459: hr_utility.trace('next period created=' || l_new_appl_bb_ovn);
1460: end if;
1461:
1462: --populating summary tables
1455: ,p_object_version_number => l_new_appl_bb_ovn);
1456:
1457: if g_debug then
1458: hr_utility.trace('next period created=' || l_new_appl_bb_id);
1459: hr_utility.trace('next period created=' || l_new_appl_bb_ovn);
1460: end if;
1461:
1462: --populating summary tables
1463: l_creation_date := get_creation_date(l_new_appl_bb_id, l_new_appl_bb_ovn);
1470: IF c_current_period%NOTFOUND
1471: THEN
1472: CLOSE c_current_period;
1473:
1474: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1475: hr_utility.set_message_token('PROCEDURE', l_proc);
1476: hr_utility.set_message_token('STEP', '20');
1477: hr_utility.raise_error;
1478: END IF;
1471: THEN
1472: CLOSE c_current_period;
1473:
1474: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1475: hr_utility.set_message_token('PROCEDURE', l_proc);
1476: hr_utility.set_message_token('STEP', '20');
1477: hr_utility.raise_error;
1478: END IF;
1479:
1472: CLOSE c_current_period;
1473:
1474: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1475: hr_utility.set_message_token('PROCEDURE', l_proc);
1476: hr_utility.set_message_token('STEP', '20');
1477: hr_utility.raise_error;
1478: END IF;
1479:
1480: CLOSE c_current_period;
1473:
1474: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1475: hr_utility.set_message_token('PROCEDURE', l_proc);
1476: hr_utility.set_message_token('STEP', '20');
1477: hr_utility.raise_error;
1478: END IF;
1479:
1480: CLOSE c_current_period;
1481:
1588: -- The line below records this function call in the error system
1589: -- in the case of an exception.
1590: --
1591: if g_debug then
1592: hr_utility.set_location(l_proc, 999);
1593: --
1594: hr_utility.trace('IN EXCEPTION IN create_next_period');
1595: --
1596: end if;
1590: --
1591: if g_debug then
1592: hr_utility.set_location(l_proc, 999);
1593: --
1594: hr_utility.trace('IN EXCEPTION IN create_next_period');
1595: --
1596: end if;
1597: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.create_next_period',
1598: itemtype, itemkey, to_char(actid), funcmode);
1760:
1761: IF p_app_period_func IS NOT NULL
1762: THEN
1763: if g_debug then
1764: hr_utility.set_location(l_proc, 95);
1765: end if;
1766:
1767: l_call_proc := p_app_period_func ||
1768: '(p_building_block_date => ' || p_day ||
1771: ',p_period_end_date => l_period_end_date' ||
1772: ',p_override_allowed => l_override_allowed)';
1773:
1774: if g_debug then
1775: hr_utility.trace('Period Start Date (from function) is : ' ||
1776: to_char(l_period_start_date, 'DD-MM-YYYY'));
1777: hr_utility.trace('Period End Date (from function) is : ' ||
1778: to_char(l_period_end_date, 'DD-MM-YYYY'));
1779: end if;
1773:
1774: if g_debug then
1775: hr_utility.trace('Period Start Date (from function) is : ' ||
1776: to_char(l_period_start_date, 'DD-MM-YYYY'));
1777: hr_utility.trace('Period End Date (from function) is : ' ||
1778: to_char(l_period_end_date, 'DD-MM-YYYY'));
1779: end if;
1780:
1781: l_cursor := dbms_sql.open_cursor;
1783: l_ret := dbms_sql.execute(l_cursor);
1784: dbms_sql.close_cursor(l_cursor);
1785:
1786: if g_debug then
1787: hr_utility.set_location(l_proc, 110);
1788: end if;
1789: ELSE
1790:
1791: l_override_allowed := TRUE;
1798: IF l_override_allowed
1799: THEN
1800:
1801: if g_debug then
1802: hr_utility.set_location(l_proc, 120);
1803: end if;
1804:
1805: l_rec_period_id := get_approval_period_id(
1806: p_resource_id
1810: ,p_tc_stop_time
1811: );
1812:
1813: if g_debug then
1814: hr_utility.trace('Recurring Period ID is : ' || to_char(l_rec_period_id));
1815: end if;
1816:
1817: open csr_get_rec_period_info(l_rec_period_id);
1818: fetch csr_get_rec_period_info into l_rec_start_date,
1829: ,p_period_end => l_period_end_date
1830: );
1831:
1832: if g_debug then
1833: hr_utility.trace('Appl Period Start Date is : ' ||
1834: to_char(l_period_start_date, 'DD-MM-YYYY'));
1835: hr_utility.trace('Appl Period End Date is : ' ||
1836: to_char(l_period_end_date, 'DD-MM-YYYY'));
1837: end if;
1831:
1832: if g_debug then
1833: hr_utility.trace('Appl Period Start Date is : ' ||
1834: to_char(l_period_start_date, 'DD-MM-YYYY'));
1835: hr_utility.trace('Appl Period End Date is : ' ||
1836: to_char(l_period_end_date, 'DD-MM-YYYY'));
1837: end if;
1838:
1839: END IF;
2158: g_debug := true;
2159: g_trace := l_proc || '10';
2160: l_item_key := null;
2161: if g_debug then
2162: hr_utility.trace('start generating period');
2163: end if;
2164:
2165: l_time_category_id := p_approval_comp.time_category_id;
2166:
2192: l_app_comp_id_temp;
2193:
2194: EXIT WHEN c_app_period%NOTFOUND;
2195:
2196: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2197: hr_utility.trace('|--------------------------------------------------------------------|');
2198: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2199: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2193:
2194: EXIT WHEN c_app_period%NOTFOUND;
2195:
2196: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2197: hr_utility.trace('|--------------------------------------------------------------------|');
2198: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2199: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2201: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2194: EXIT WHEN c_app_period%NOTFOUND;
2195:
2196: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2197: hr_utility.trace('|--------------------------------------------------------------------|');
2198: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2199: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2201: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2202: hr_utility.trace('|--------------------------------------------------------------------|');
2195:
2196: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2197: hr_utility.trace('|--------------------------------------------------------------------|');
2198: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2199: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2201: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2202: hr_utility.trace('|--------------------------------------------------------------------|');
2203: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2196: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2197: hr_utility.trace('|--------------------------------------------------------------------|');
2198: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2199: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2201: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2202: hr_utility.trace('|--------------------------------------------------------------------|');
2203: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2204:
2197: hr_utility.trace('|--------------------------------------------------------------------|');
2198: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2199: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2201: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2202: hr_utility.trace('|--------------------------------------------------------------------|');
2203: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2204:
2205: --for hr supervisor, end date all the later supervisor's app periods
2198: hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2199: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2201: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2202: hr_utility.trace('|--------------------------------------------------------------------|');
2203: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2204:
2205: --for hr supervisor, end date all the later supervisor's app periods
2206: --only keep the first supervisor's app period. we will regenereate new
2199: hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200: hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2201: hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2202: hr_utility.trace('|--------------------------------------------------------------------|');
2203: hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2204:
2205: --for hr supervisor, end date all the later supervisor's app periods
2206: --only keep the first supervisor's app period. we will regenereate new
2207: --app periods for later supervisors
2229:
2230: END LOOP;
2231:
2232: CLOSE c_app_period;
2233: hr_utility.trace('l_time_category:'||l_time_category_id);
2234:
2235: --
2236: -- If this is a new application period, and has no associated details
2237: -- do not create it. However, if it has been previously created,
2249: -- There are no details associated with this application period
2250: IF l_app_id is null then
2251: -- This is a new application, do not generate a new, empty
2252: -- application period.
2253: hr_utility.trace('Return(0)');
2254: RETURN;
2255: END IF;
2256: IF ((l_app_id is not null)
2257: AND (l_app_ovn = 1)
2256: IF ((l_app_id is not null)
2257: AND (l_app_ovn = 1)
2258: AND (l_notification_status = c_notified OR l_notification_status = c_not_notified )) then
2259: -- Application period is not new, but the approver has not seen it
2260: hr_utility.trace('Return(1)');
2261: RETURN;
2262: END IF;
2263: END IF;
2264: END IF;
2287: hxc_app_period_summary_api.app_period_delete(l_app_id);
2288: END IF;
2289: END IF;
2290:
2291: hr_utility.trace('Return(2)');
2292: RETURN;
2293: END IF;
2294: END IF;
2295: --
2296: -- 115.93 Change: Include check that the approval component id
2297: -- is the same in the statement below, otherwise we will surely
2298: -- need to regenerate the application period. Bug 4302997.
2299: --
2300: hr_utility.trace('l_app_id:'||l_app_id);
2301: hr_utility.trace('l_approval_status:'||l_approval_status);
2302: hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2303:
2304: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2297: -- is the same in the statement below, otherwise we will surely
2298: -- need to regenerate the application period. Bug 4302997.
2299: --
2300: hr_utility.trace('l_app_id:'||l_app_id);
2301: hr_utility.trace('l_approval_status:'||l_approval_status);
2302: hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2303:
2304: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2305: hr_utility.trace('changed is true');
2298: -- need to regenerate the application period. Bug 4302997.
2299: --
2300: hr_utility.trace('l_app_id:'||l_app_id);
2301: hr_utility.trace('l_approval_status:'||l_approval_status);
2302: hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2303:
2304: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2305: hr_utility.trace('changed is true');
2306: else
2301: hr_utility.trace('l_approval_status:'||l_approval_status);
2302: hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2303:
2304: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2305: hr_utility.trace('changed is true');
2306: else
2307: hr_utility.trace('changed is false');
2308: end if;
2309:
2303:
2304: if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2305: hr_utility.trace('changed is true');
2306: else
2307: hr_utility.trace('changed is false');
2308: end if;
2309:
2310: select count(*) into l_number_of_details
2311: from hxc_ap_detail_links
2310: select count(*) into l_number_of_details
2311: from hxc_ap_detail_links
2312: where application_period_id = l_app_id;
2313:
2314: hr_utility.trace('Count for '||l_app_id||' is:'||l_number_of_details);
2315:
2316: hr_utility.trace('check if the timecard ever had any details attached to it');
2317: hr_utility.trace('p_resource_id ::'||p_resource_id);
2318: hr_utility.trace('trunc(p_start_time) ::'||trunc(p_start_time));
2312: where application_period_id = l_app_id;
2313:
2314: hr_utility.trace('Count for '||l_app_id||' is:'||l_number_of_details);
2315:
2316: hr_utility.trace('check if the timecard ever had any details attached to it');
2317: hr_utility.trace('p_resource_id ::'||p_resource_id);
2318: hr_utility.trace('trunc(p_start_time) ::'||trunc(p_start_time));
2319: hr_utility.trace('trunc(p_stop_time) ::'||trunc(p_stop_time));
2320:
2313:
2314: hr_utility.trace('Count for '||l_app_id||' is:'||l_number_of_details);
2315:
2316: hr_utility.trace('check if the timecard ever had any details attached to it');
2317: hr_utility.trace('p_resource_id ::'||p_resource_id);
2318: hr_utility.trace('trunc(p_start_time) ::'||trunc(p_start_time));
2319: hr_utility.trace('trunc(p_stop_time) ::'||trunc(p_stop_time));
2320:
2321: -- Bug 8685110
2314: hr_utility.trace('Count for '||l_app_id||' is:'||l_number_of_details);
2315:
2316: hr_utility.trace('check if the timecard ever had any details attached to it');
2317: hr_utility.trace('p_resource_id ::'||p_resource_id);
2318: hr_utility.trace('trunc(p_start_time) ::'||trunc(p_start_time));
2319: hr_utility.trace('trunc(p_stop_time) ::'||trunc(p_stop_time));
2320:
2321: -- Bug 8685110
2322: -- this is to check if the timecard ever had any details attached to it
2315:
2316: hr_utility.trace('check if the timecard ever had any details attached to it');
2317: hr_utility.trace('p_resource_id ::'||p_resource_id);
2318: hr_utility.trace('trunc(p_start_time) ::'||trunc(p_start_time));
2319: hr_utility.trace('trunc(p_stop_time) ::'||trunc(p_stop_time));
2320:
2321: -- Bug 8685110
2322: -- this is to check if the timecard ever had any details attached to it
2323: select count(*) into l_tc_details
2325: where resource_id = p_resource_id
2326: and trunc(start_time) >= trunc(p_start_time)
2327: and trunc(stop_time) <= trunc(p_stop_time);
2328:
2329: hr_utility.trace('l_tc_details = '||l_tc_details);
2330:
2331: -- Bug 13095894
2332: -- Check total hours recorded for the timecard
2333:
2349: where timecard_id = p_timecard_id;
2350:
2351:
2352:
2353: hr_utility.trace('l_total_hours = '||l_total_hours);
2354:
2355:
2356: IF l_app_id IS NOT NULL
2357: AND p_approval_comp.approval_comp_id = l_app_comp_id
2373:
2374: if(l_number_of_details = 0 AND l_approval_status <> 'SUBMITTED') then
2375: -- it is case 1
2376:
2377: hr_utility.trace('into case1');
2378: -- if an empty timecard is approved/rejected and then resubmitted again, it has to go
2379: -- through the approval process, so skip this call
2380:
2381: IF (l_tc_details <> 0) -- Bug 8685110
2380:
2381: IF (l_tc_details <> 0) -- Bug 8685110
2382: AND (l_total_hours <> 0) -- Bug 13095894
2383: THEN
2384: hr_utility.trace('Return(3) - empty actioned App Period - do nothing');
2385: RETURN;
2386: end if;
2387: else
2388: -- it is case 2
2393:
2394: IF (l_tc_details <> 0) -- Bug 8685110
2395: AND (l_total_hours <> 0) -- Bug 13095894
2396: THEN
2397: hr_utility.trace('Return(3.5) - newly empty period - notify previous approver');
2398: open get_item_key(l_app_id);
2399: fetch get_item_key into l_item_key;
2400: close get_item_key;
2401:
2398: open get_item_key(l_app_id);
2399: fetch get_item_key into l_item_key;
2400: close get_item_key;
2401:
2402: hr_utility.trace('OTL:-10- before - '||l_item_key);
2403:
2404: If l_item_key is not null then
2405:
2406: -- Added for bug 9076079
2411: and item_type = 'HXCEMP'
2412: and rownum < 2;
2413:
2414:
2415: hr_utility.trace('OTL:-10- before - l_item_key_exists = '||l_item_key_exists);
2416:
2417: IF l_item_key_exists <> 0 THEN
2418: hr_utility.trace('pass 11');
2419: if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2414:
2415: hr_utility.trace('OTL:-10- before - l_item_key_exists = '||l_item_key_exists);
2416:
2417: IF l_item_key_exists <> 0 THEN
2418: hr_utility.trace('pass 11');
2419: if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2420:
2421: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 01');
2422:
2417: IF l_item_key_exists <> 0 THEN
2418: hr_utility.trace('pass 11');
2419: if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2420:
2421: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 01');
2422:
2423: wf_engine.SetItemAttrText(
2424: itemtype => 'HXCEMP',
2425: itemkey => l_item_key,
2426: aname => 'IS_DIFF_TC',
2427: avalue => 'Y');
2428: else
2429:
2430: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 02');
2431:
2432: wf_engine.additemattr
2433: (itemtype => 'HXCEMP',
2434: itemkey => l_item_key,
2436: text_value => 'Y');
2437: end if;
2438: ELSE
2439: -- Added for bug 9076079
2440: hr_utility.trace('OTL:pass 12');
2441: g_old_item_key(l_item_key).is_diff_tc := 'Y';
2442:
2443: END IF;
2444:
2441: g_old_item_key(l_item_key).is_diff_tc := 'Y';
2442:
2443: END IF;
2444:
2445: hr_utility.trace('OTL:-10- after');
2446:
2447: end if;
2448:
2449: l_blank := 'Y';
2464: -- to other changes in the timecard, the timecard has the
2465: -- appropriate status.
2466: --
2467: hxc_timecard_summary_api.reevaluate_timecard_statuses(l_app_id);
2468: hr_utility.trace('Return(4) - populated actioned App period - no changes. Use, but do not renotify');
2469: RETURN;
2470: END IF;
2471: end if;
2472: END IF;
2471: end if;
2472: END IF;
2473: g_block_exist_for_ap := 'N';
2474:
2475: hr_utility.trace('l_number_of_details = '||l_number_of_details);
2476: hr_utility.trace('g_block_exist_for_ap = '||g_block_exist_for_ap);
2477: hr_utility.trace('no_blocks(l_app_id, p_timecard_id) = '||no_blocks(l_app_id, p_timecard_id));
2478: hr_utility.trace('l_approval_status = '||l_approval_status);
2479:
2472: END IF;
2473: g_block_exist_for_ap := 'N';
2474:
2475: hr_utility.trace('l_number_of_details = '||l_number_of_details);
2476: hr_utility.trace('g_block_exist_for_ap = '||g_block_exist_for_ap);
2477: hr_utility.trace('no_blocks(l_app_id, p_timecard_id) = '||no_blocks(l_app_id, p_timecard_id));
2478: hr_utility.trace('l_approval_status = '||l_approval_status);
2479:
2480: -- Reverted the changes in bug 8322444 and introduced new changes via 8620917
2473: g_block_exist_for_ap := 'N';
2474:
2475: hr_utility.trace('l_number_of_details = '||l_number_of_details);
2476: hr_utility.trace('g_block_exist_for_ap = '||g_block_exist_for_ap);
2477: hr_utility.trace('no_blocks(l_app_id, p_timecard_id) = '||no_blocks(l_app_id, p_timecard_id));
2478: hr_utility.trace('l_approval_status = '||l_approval_status);
2479:
2480: -- Reverted the changes in bug 8322444 and introduced new changes via 8620917
2481: /* After a approval notification is approved/rejected, if the user
2474:
2475: hr_utility.trace('l_number_of_details = '||l_number_of_details);
2476: hr_utility.trace('g_block_exist_for_ap = '||g_block_exist_for_ap);
2477: hr_utility.trace('no_blocks(l_app_id, p_timecard_id) = '||no_blocks(l_app_id, p_timecard_id));
2478: hr_utility.trace('l_approval_status = '||l_approval_status);
2479:
2480: -- Reverted the changes in bug 8322444 and introduced new changes via 8620917
2481: /* After a approval notification is approved/rejected, if the user
2482: deletes a rejected/approved timecard row, then a blank notification has to be sent to the approver.
2507: AND latest.object_version_number = detail.object_version_number
2508: AND detail.date_to = hr_general.end_of_time;
2509:
2510:
2511: hr_utility.trace('l_active_details = '||l_active_details);
2512:
2513:
2514: ---
2515: IF changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id) AND
2519: and (l_tc_details <> 0) -- Bug 8685110 , no need to send blank notification for empty timecard
2520: and (l_total_hours <> 0) -- Bug 13095894
2521: THEN
2522:
2523: hr_utility.trace('to set l_blank');
2524:
2525: open get_item_key(l_app_id);
2526: fetch get_item_key into l_item_key;
2527: close get_item_key;
2534: where item_key = to_char(l_item_key)
2535: and item_type = 'HXCEMP'
2536: and rownum < 2;
2537:
2538: hr_utility.trace('OTL:-20- before - l_item_key_exists = '||l_item_key_exists);
2539: hr_utility.trace('OTL:-20- before - '||l_item_key);
2540:
2541: IF l_item_key_exists <> 0 THEN
2542: hr_utility.trace('OTL: Item key exists in workflow tables');
2535: and item_type = 'HXCEMP'
2536: and rownum < 2;
2537:
2538: hr_utility.trace('OTL:-20- before - l_item_key_exists = '||l_item_key_exists);
2539: hr_utility.trace('OTL:-20- before - '||l_item_key);
2540:
2541: IF l_item_key_exists <> 0 THEN
2542: hr_utility.trace('OTL: Item key exists in workflow tables');
2543: if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2538: hr_utility.trace('OTL:-20- before - l_item_key_exists = '||l_item_key_exists);
2539: hr_utility.trace('OTL:-20- before - '||l_item_key);
2540:
2541: IF l_item_key_exists <> 0 THEN
2542: hr_utility.trace('OTL: Item key exists in workflow tables');
2543: if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2544:
2545: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 03');
2546: wf_engine.SetItemAttrText(
2541: IF l_item_key_exists <> 0 THEN
2542: hr_utility.trace('OTL: Item key exists in workflow tables');
2543: if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2544:
2545: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 03');
2546: wf_engine.SetItemAttrText(
2547: itemtype => 'HXCEMP',
2548: itemkey => l_item_key,
2549: aname => 'IS_DIFF_TC',
2548: itemkey => l_item_key,
2549: aname => 'IS_DIFF_TC',
2550: avalue => 'Y');
2551: else
2552: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 04');
2553: wf_engine.additemattr
2554: (itemtype => 'HXCEMP',
2555: itemkey => l_item_key,
2556: aname => 'IS_DIFF_TC',
2556: aname => 'IS_DIFF_TC',
2557: text_value => 'Y');
2558: end if;
2559: ELSE
2560: HR_UTILITY.TRACE('OTL: Adding to g table');
2561: g_old_item_key(l_item_key).is_diff_tc := 'Y';
2562: END IF;
2563:
2564:
2562: END IF;
2563:
2564:
2565:
2566: hr_utility.trace('OTL:-20- after');
2567: end if;
2568:
2569: l_blank := 'Y';
2570: END IF;
2604:
2605: g_trace := l_proc || '30';
2606:
2607: if g_debug then
2608: hr_utility.trace('Generate it!');
2609: end if;
2610:
2611: t_attributes := get_empty_attribute;
2612:
2633: );
2634:
2635: g_trace := l_proc || '40';
2636: if g_debug then
2637: hr_utility.trace('Generated the period');
2638: hr_utility.trace('app_id=' || l_app_id);
2639: hr_utility.trace('app_ovn=' || l_app_ovn);
2640: hr_utility.trace('Populating hxc_app_period_summary');
2641: end if;
2634:
2635: g_trace := l_proc || '40';
2636: if g_debug then
2637: hr_utility.trace('Generated the period');
2638: hr_utility.trace('app_id=' || l_app_id);
2639: hr_utility.trace('app_ovn=' || l_app_ovn);
2640: hr_utility.trace('Populating hxc_app_period_summary');
2641: end if;
2642: l_creation_date := get_creation_date(l_app_id, l_app_ovn);
2635: g_trace := l_proc || '40';
2636: if g_debug then
2637: hr_utility.trace('Generated the period');
2638: hr_utility.trace('app_id=' || l_app_id);
2639: hr_utility.trace('app_ovn=' || l_app_ovn);
2640: hr_utility.trace('Populating hxc_app_period_summary');
2641: end if;
2642: l_creation_date := get_creation_date(l_app_id, l_app_ovn);
2643:
2636: if g_debug then
2637: hr_utility.trace('Generated the period');
2638: hr_utility.trace('app_id=' || l_app_id);
2639: hr_utility.trace('app_ovn=' || l_app_ovn);
2640: hr_utility.trace('Populating hxc_app_period_summary');
2641: end if;
2642: l_creation_date := get_creation_date(l_app_id, l_app_ovn);
2643:
2644: g_trace := l_proc || '80';
2682:
2683: END IF;
2684:
2685: if g_debug then
2686: hr_utility.trace('Populating hxc_ap_detail_links');
2687: end if;
2688: --populate hxc_ap_detail_links
2689: IF l_time_category_id IS NULL
2690: OR l_time_category_id = 0
2691: THEN
2692: g_trace := l_proc || '100';
2693:
2694: if g_debug then
2695: hr_utility.trace('Populating all');
2696: end if;
2697: link_ap_details_all(
2698: p_detail_blocks => p_detail_blocks
2699: ,p_app_id => l_app_id
2703: ELSE
2704: g_trace := l_proc || '110';
2705:
2706: if g_debug then
2707: hr_utility.trace('Populating time category : ' || l_time_category_id );
2708: end if;
2709: link_ap_details(
2710: p_detail_blocks => p_detail_blocks
2711: ,p_attributes => p_detail_attributes
2739: g_trace := l_proc || '130';
2740:
2741:
2742: if g_debug then
2743: hr_utility.trace('Populating hxc_tc_ap_links');
2744: end if;
2745: --populate hxc_tc_ap_links
2746:
2747: OPEN c_tc_ap_link(p_timecard_id, l_app_id);
2770: g_trace := l_proc || '180';
2771: END IF;
2772:
2773: if g_debug then
2774: hr_utility.trace('End generating app period');
2775: end if;
2776:
2777: EXCEPTION
2778: WHEN OTHERS THEN
2775: end if;
2776:
2777: EXCEPTION
2778: WHEN OTHERS THEN
2779: hr_utility.trace('exception in generate_app_period - '||sqlerrm);
2780: RAISE;
2781:
2782: END generate_app_period;
2783:
2819: --
2820: BEGIN
2821: --
2822: if g_debug then
2823: hr_utility.set_location(l_proc, 10);
2824: end if;
2825: --
2826: open csr_get_appr_style;
2827: fetch csr_get_appr_style into l_approval_style_id;
2827: fetch csr_get_appr_style into l_approval_style_id;
2828: IF csr_get_appr_style%NOTFOUND THEN
2829: --
2830: if g_debug then
2831: hr_utility.set_location(l_proc, 20);
2832: end if;
2833: --
2834: g_error_count := g_error_count + 1;
2835: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPR_STYLE';
2834: g_error_count := g_error_count + 1;
2835: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPR_STYLE';
2836: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2837: --
2838: hr_utility.set_message(809, 'HXC_APR_NO_APPR_STYLE');
2839: hr_utility.raise_error;
2840: --
2841: END IF;
2842: --
2835: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPR_STYLE';
2836: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2837: --
2838: hr_utility.set_message(809, 'HXC_APR_NO_APPR_STYLE');
2839: hr_utility.raise_error;
2840: --
2841: END IF;
2842: --
2843: close csr_get_appr_style;
3150: l_gen_app_period boolean;
3151:
3152: BEGIN
3153:
3154: g_debug:=hr_utility.debug_enabled;
3155: g_trace := '10';
3156: if g_debug then
3157: hr_utility.set_location(l_proc, 10);
3158: end if;
3153:
3154: g_debug:=hr_utility.debug_enabled;
3155: g_trace := '10';
3156: if g_debug then
3157: hr_utility.set_location(l_proc, 10);
3158: end if;
3159:
3160: l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
3161: (itemtype => itemtype,
3162: itemkey => itemkey,
3163: aname => 'TC_BLD_BLK_ID');
3164:
3165: if g_debug then
3166: hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
3167: end if;
3168: l_tc_ovn := wf_engine.GetItemAttrNumber
3169: (itemtype => itemtype,
3170: itemkey => itemkey,
3170: itemkey => itemkey,
3171: aname => 'TC_BLD_BLK_OVN');
3172:
3173: if g_debug then
3174: hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
3175: end if;
3176:
3177: l_tc_resubmitted := wf_engine.GetItemAttrText
3178: (itemtype => itemtype,
3179: itemkey => itemkey,
3180: aname => 'TC_RESUBMITTED');
3181:
3182: if g_debug then
3183: hr_utility.trace('Timecard Resubmitted is : ' || l_tc_resubmitted);
3184: end if;
3185:
3186: g_trace := '20';
3187:
3200: g_error_count := g_error_count + 1;
3201: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
3202: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3203:
3204: hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
3205: hr_utility.raise_error;
3206: END IF;
3207: g_trace := '40';
3208:
3201: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
3202: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3203:
3204: hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
3205: hr_utility.raise_error;
3206: END IF;
3207: g_trace := '40';
3208:
3209: close csr_get_tc_info;
3221: LOOP
3222: IF l_pref_table(l_index).preference_code = 'TS_PER_ELP_RULES'
3223: THEN
3224: l_elp_rule := l_pref_table(l_index).attribute1;
3225: hr_utility.trace('l_elp_rule = '||l_elp_rule);
3226: EXIT;
3227: END IF;
3228: l_index := l_pref_table.NEXT(l_index);
3229: EXIT WHEN NOT l_pref_table.EXISTS(l_index);
3263: g_error_count := g_error_count + 1;
3264: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPL_SET_PREF';
3265: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3266:
3267: hr_utility.set_message(809, 'HXC_APR_NO_APPL_SET_PREF');
3268: hr_utility.raise_error;
3269:
3270: END IF;
3271: close csr_get_apps;
3264: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPL_SET_PREF';
3265: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3266:
3267: hr_utility.set_message(809, 'HXC_APR_NO_APPL_SET_PREF');
3268: hr_utility.raise_error;
3269:
3270: END IF;
3271: close csr_get_apps;
3272:
3292: g_trace := '90 Application=' || l_application;
3293: g_trace := '90 Time Recipient ID=' || to_char(l_time_recipient_id);
3294:
3295: if g_debug then
3296: hr_utility.trace('90 Application=' || l_application);
3297: hr_utility.trace('90 Time Recipient ID=' || to_char(l_time_recipient_id));
3298: end if;
3299:
3300: l_processed_app_start := NULL;
3293: g_trace := '90 Time Recipient ID=' || to_char(l_time_recipient_id);
3294:
3295: if g_debug then
3296: hr_utility.trace('90 Application=' || l_application);
3297: hr_utility.trace('90 Time Recipient ID=' || to_char(l_time_recipient_id));
3298: end if;
3299:
3300: l_processed_app_start := NULL;
3301: l_processed_app_stop := NULL;
3314:
3315: if(dayHasActiveAssignment(l_assignment_periods,l_day_start_time))then
3316:
3317: if g_debug then
3318: hr_utility.set_location(l_proc, 90);
3319: hr_utility.trace('day start=' || to_char(l_day_start_time, 'YYYY/MM/DD'));
3320: end if;
3321:
3322: get_application_period
3315: if(dayHasActiveAssignment(l_assignment_periods,l_day_start_time))then
3316:
3317: if g_debug then
3318: hr_utility.set_location(l_proc, 90);
3319: hr_utility.trace('day start=' || to_char(l_day_start_time, 'YYYY/MM/DD'));
3320: end if;
3321:
3322: get_application_period
3323: (p_app_period_func => l_app_period_func,
3363: --prepare data
3364:
3365:
3366: IF g_debug THEN
3367: hr_utility.trace('l_approval_style ::'||l_approval_style);
3368: hr_utility.trace('l_elp_rule ::'||l_elp_rule);
3369: hr_utility.trace('Profile value of OTL: Verify Entry Level Application Set for Approval is :: '||
3370: NVL(FND_PROFILE.value('HXC_VERIFY_APPLN_SET_FOR_APPROVAL'),'N'));
3371: END IF;
3364:
3365:
3366: IF g_debug THEN
3367: hr_utility.trace('l_approval_style ::'||l_approval_style);
3368: hr_utility.trace('l_elp_rule ::'||l_elp_rule);
3369: hr_utility.trace('Profile value of OTL: Verify Entry Level Application Set for Approval is :: '||
3370: NVL(FND_PROFILE.value('HXC_VERIFY_APPLN_SET_FOR_APPROVAL'),'N'));
3371: END IF;
3372:
3365:
3366: IF g_debug THEN
3367: hr_utility.trace('l_approval_style ::'||l_approval_style);
3368: hr_utility.trace('l_elp_rule ::'||l_elp_rule);
3369: hr_utility.trace('Profile value of OTL: Verify Entry Level Application Set for Approval is :: '||
3370: NVL(FND_PROFILE.value('HXC_VERIFY_APPLN_SET_FOR_APPROVAL'),'N'));
3371: END IF;
3372:
3373: -- Bug 10164135
3381:
3382: THEN
3383: IF g_debug
3384: THEN
3385: hr_utility.trace('ELP Rule Turned ON '||l_elp_rule);
3386: hr_utility.trace('l_time_reciipeint '||l_time_recipient_id);
3387: END IF;
3388: get_detail_blocks(p_timecard_id => l_tc_bld_blk_id,
3389: p_timecard_ovn => l_tc_ovn,
3382: THEN
3383: IF g_debug
3384: THEN
3385: hr_utility.trace('ELP Rule Turned ON '||l_elp_rule);
3386: hr_utility.trace('l_time_reciipeint '||l_time_recipient_id);
3387: END IF;
3388: get_detail_blocks(p_timecard_id => l_tc_bld_blk_id,
3389: p_timecard_ovn => l_tc_ovn,
3390: p_start_time => l_period_start_date,
3396:
3397: -- Bug 13036988
3398: -- Below condition will be called only when ELP rule is set.
3399: IF g_debug THEN
3400: hr_utility.trace('l_new_detail_blocks.COUNT ::'||l_new_detail_blocks.COUNT);
3401: hr_utility.trace('l_detail_blocks.COUNT ::'||l_detail_blocks.COUNT );
3402: END IF;
3403:
3404:
3397: -- Bug 13036988
3398: -- Below condition will be called only when ELP rule is set.
3399: IF g_debug THEN
3400: hr_utility.trace('l_new_detail_blocks.COUNT ::'||l_new_detail_blocks.COUNT);
3401: hr_utility.trace('l_detail_blocks.COUNT ::'||l_detail_blocks.COUNT );
3402: END IF;
3403:
3404:
3405: IF l_new_detail_blocks.COUNT = 0
3409: END IF;
3410:
3411: IF g_debug
3412: THEN
3413: hr_utility.trace(' Printing detail blocks ');
3414: l_index := l_detail_blocks.FIRST;
3415: LOOP
3416: hr_utility.trace('l_detail_blocks(l_index).id '||l_detail_blocks(l_index).block_id);
3417: hr_utility.trace('l_detail_blocks(l_index).ovn '||l_detail_blocks(l_index).block_ovn);
3412: THEN
3413: hr_utility.trace(' Printing detail blocks ');
3414: l_index := l_detail_blocks.FIRST;
3415: LOOP
3416: hr_utility.trace('l_detail_blocks(l_index).id '||l_detail_blocks(l_index).block_id);
3417: hr_utility.trace('l_detail_blocks(l_index).ovn '||l_detail_blocks(l_index).block_ovn);
3418: l_index := l_detail_blocks.NEXT(l_index);
3419: EXIT WHEN NOT l_detail_blocks.EXISTS(l_index);
3420: END LOOP;
3413: hr_utility.trace(' Printing detail blocks ');
3414: l_index := l_detail_blocks.FIRST;
3415: LOOP
3416: hr_utility.trace('l_detail_blocks(l_index).id '||l_detail_blocks(l_index).block_id);
3417: hr_utility.trace('l_detail_blocks(l_index).ovn '||l_detail_blocks(l_index).block_ovn);
3418: l_index := l_detail_blocks.NEXT(l_index);
3419: EXIT WHEN NOT l_detail_blocks.EXISTS(l_index);
3420: END LOOP;
3421: END IF;
3524:
3525:
3526: IF l_approval_comp.approval_mechanism = 'ENTRY_LEVEL_APPROVAL' THEN
3527: if g_debug then
3528: hr_utility.trace(l_proc || 'Entry level approvals 150');
3529: end if;
3530:
3531: g_trace := '150 ELA';
3532:
3542:
3543: hxc_time_category_utils_pkg.push_timecard ( l_new_detail_blocks, l_new_detail_attributes, TRUE );
3544:
3545: if g_debug then
3546: hr_utility.trace(l_proc || 'Entry level approvals 160');
3547: end if;
3548:
3549: -- Bug 13817976
3550: -- Added the first half of the following IF construct.
3585: EXIT WHEN c_ela_comps2%NOTFOUND;
3586:
3587: IF g_debug
3588: THEN
3589: hr_utility.trace('ASH:Y-TC '||l_ela_comp.time_category_id);
3590: END IF;
3591:
3592: IF l_ela_comp.time_category_id = 0 THEN
3593: l_default_comp := l_ela_comp;
3592: IF l_ela_comp.time_category_id = 0 THEN
3593: l_default_comp := l_ela_comp;
3594: ELSE
3595: if g_debug then
3596: hr_utility.trace(l_proc || 'Entry level approvals 170-1');
3597: end if;
3598: g_trace := '170 ELA generating period-1';
3599:
3600: if(l_gen_app_period) then
3613: );
3614: end if;
3615:
3616: if g_debug then
3617: hr_utility.trace(l_proc || 'Entry level approvals 180-1');
3618: end if;
3619: g_trace :=' 180 ELA finish generating period-1';
3620:
3621: END IF;
3634: EXIT WHEN c_ela_comps%NOTFOUND;
3635:
3636: IF g_debug
3637: THEN
3638: hr_utility.trace('ASH:N-TC '||l_ela_comp.time_category_id);
3639: END IF;
3640:
3641: IF l_ela_comp.time_category_id = 0 THEN
3642: l_default_comp := l_ela_comp;
3642: l_default_comp := l_ela_comp;
3643: ELSE
3644: if g_debug
3645: then
3646: hr_utility.trace(l_proc || 'Entry level approvals 170');
3647: end if;
3648: g_trace := '170 ELA generating period';
3649:
3650: if(l_gen_app_period)
3665: end if;
3666:
3667: if g_debug
3668: then
3669: hr_utility.trace(l_proc || 'Entry level approvals 180');
3670: end if;
3671: g_trace :=' 180 ELA finish generating period';
3672:
3673: END IF;
3677: CLOSE c_ela_comps;
3678: END IF;
3679:
3680: if g_debug then
3681: hr_utility.trace(l_proc || 'Entry level approvals 200');
3682: end if;
3683: g_trace := '200 any detail left??';
3684:
3685: --Now take care of the rest of the blocks
3685: --Now take care of the rest of the blocks
3686: l_count := get_rest_detail_blocks(l_detail_blocks);
3687:
3688: if g_debug then
3689: hr_utility.trace('210 rest_detail_count=' || l_count);
3690: end if;
3691:
3692: g_trace := '210 rest_detail_count=' || l_count;
3693: if g_debug then
3690: end if;
3691:
3692: g_trace := '210 rest_detail_count=' || l_count;
3693: if g_debug then
3694: hr_utility.trace('220 rest_detail_count > 0');
3695: end if;
3696:
3697: g_trace := '220 rest_detail_count > 0';
3698:
3712: );
3713: end if;
3714:
3715: if g_debug then
3716: hr_utility.trace('230 finished generating period for rest details');
3717: end if;
3718: g_trace := '230 finished generating period for rest details';
3719: ELSE
3720: if g_debug then
3717: end if;
3718: g_trace := '230 finished generating period for rest details';
3719: ELSE
3720: if g_debug then
3721: hr_utility.trace('250 NON ELA');
3722: end if;
3723: g_trace := '250 NON ELA';
3724:
3725: -- non ELA mechanism
3739: );
3740: end if;
3741:
3742: if g_debug then
3743: hr_utility.trace( '260 finished generating period for NON ELA');
3744: end if;
3745: g_trace := '260 finished generating period for NON ELA';
3746: END IF;
3747: END IF;
3763: hxc_app_comp_notifications_api.c_recipient_worker
3764: );
3765:
3766: if g_debug then
3767: hr_utility.set_location(l_proc, 200);
3768: end if;
3769: close csr_get_apps;
3770:
3771: if g_debug then
3768: end if;
3769: close csr_get_apps;
3770:
3771: if g_debug then
3772: hr_utility.trace('300 END of create_appl_period_info');
3773: end if;
3774:
3775:
3776: g_trace := '300 END of create_appl_period_info';
3783: -- The line below records this function call in the error system
3784: -- in the case of an exception.
3785: --
3786: if g_debug then
3787: hr_utility.set_location(l_proc, 999);
3788: --
3789: hr_utility.trace('IN EXCEPTION IN create_appl_period_info');
3790: --
3791: end if;
3785: --
3786: if g_debug then
3787: hr_utility.set_location(l_proc, 999);
3788: --
3789: hr_utility.trace('IN EXCEPTION IN create_appl_period_info');
3790: --
3791: end if;
3792: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.create_appl_period_info',
3793: itemtype, itemkey, to_char(actid), funcmode, g_trace);
3856:
3857: BEGIN
3858:
3859: if g_debug then
3860: hr_utility.set_location(l_proc, 10);
3861: end if;
3862: l_date := p_period_start_date;
3863:
3864: l_approved := 'Y';
3891:
3892: END LOOP;
3893:
3894: if g_debug then
3895: hr_utility.set_location(l_proc, 4);
3896: end if;
3897:
3898: RETURN 'Y';
3899:
3976: --
3977: BEGIN
3978: --
3979: if g_debug then
3980: hr_utility.set_location(l_proc, 10);
3981: end if;
3982: --
3983: BEGIN
3984: --added by jxtan to fix mid period hiring
4102: l_process varchar2(1) := 'Y';
4103:
4104: BEGIN
4105: l_approval_item_key := null;
4106: g_debug:=hr_utility.debug_enabled;
4107: if g_debug then
4108: hr_utility.set_location(l_proc, 10);
4109: end if;
4110: l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
4104: BEGIN
4105: l_approval_item_key := null;
4106: g_debug:=hr_utility.debug_enabled;
4107: if g_debug then
4108: hr_utility.set_location(l_proc, 10);
4109: end if;
4110: l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
4111: (itemtype => itemtype,
4112: itemkey => itemkey,
4112: itemkey => itemkey,
4113: aname => 'TC_BLD_BLK_ID');
4114:
4115: if g_debug then
4116: hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
4117: end if;
4118: l_tc_ovn := wf_engine.GetItemAttrNumber
4119: (itemtype => itemtype,
4120: itemkey => itemkey,
4120: itemkey => itemkey,
4121: aname => 'TC_BLD_BLK_OVN');
4122:
4123: if g_debug then
4124: hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
4125: end if;
4126: l_tc_resubmitted := wf_engine.GetItemAttrText
4127: (itemtype => itemtype,
4128: itemkey => itemkey,
4128: itemkey => itemkey,
4129: aname => 'TC_RESUBMITTED');
4130:
4131: if g_debug then
4132: hr_utility.trace('Timecard Resubmitted is : ' || l_tc_resubmitted);
4133: end if;
4134: l_bb_new := wf_engine.GetItemAttrText
4135: (itemtype => itemtype,
4136: itemkey => itemkey,
4136: itemkey => itemkey,
4137: aname => 'BB_NEW');
4138:
4139: if g_debug then
4140: hr_utility.trace('Building Block New is : ' || l_bb_new);
4141: hr_utility.set_location(l_proc, 20);
4142: end if;
4143:
4144: open csr_get_tc_info(l_tc_bld_blk_id,
4137: aname => 'BB_NEW');
4138:
4139: if g_debug then
4140: hr_utility.trace('Building Block New is : ' || l_bb_new);
4141: hr_utility.set_location(l_proc, 20);
4142: end if;
4143:
4144: open csr_get_tc_info(l_tc_bld_blk_id,
4145: l_tc_ovn);
4148: l_tc_stop_time,
4149: l_last_updated_by;
4150:
4151: if g_debug then
4152: hr_utility.set_location(l_proc, 30);
4153: hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
4154: hr_utility.trace('Timecard Start Time is : ' ||
4155: to_char(l_tc_start_time, 'DD-MM-YYYY'));
4156: hr_utility.trace('Timecard End Time is : ' ||
4149: l_last_updated_by;
4150:
4151: if g_debug then
4152: hr_utility.set_location(l_proc, 30);
4153: hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
4154: hr_utility.trace('Timecard Start Time is : ' ||
4155: to_char(l_tc_start_time, 'DD-MM-YYYY'));
4156: hr_utility.trace('Timecard End Time is : ' ||
4157: to_char(l_tc_stop_time, 'DD-MM-YYYY'));
4150:
4151: if g_debug then
4152: hr_utility.set_location(l_proc, 30);
4153: hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
4154: hr_utility.trace('Timecard Start Time is : ' ||
4155: to_char(l_tc_start_time, 'DD-MM-YYYY'));
4156: hr_utility.trace('Timecard End Time is : ' ||
4157: to_char(l_tc_stop_time, 'DD-MM-YYYY'));
4158: end if;
4152: hr_utility.set_location(l_proc, 30);
4153: hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
4154: hr_utility.trace('Timecard Start Time is : ' ||
4155: to_char(l_tc_start_time, 'DD-MM-YYYY'));
4156: hr_utility.trace('Timecard End Time is : ' ||
4157: to_char(l_tc_stop_time, 'DD-MM-YYYY'));
4158: end if;
4159: IF csr_get_tc_info%NOTFOUND
4160: THEN
4159: IF csr_get_tc_info%NOTFOUND
4160: THEN
4161:
4162: if g_debug then
4163: hr_utility.set_location(l_proc, 40);
4164: end if;
4165:
4166: g_error_count := g_error_count + 1;
4167: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
4166: g_error_count := g_error_count + 1;
4167: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
4168: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
4169:
4170: hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
4171: hr_utility.raise_error;
4172:
4173: END IF;
4174:
4167: g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
4168: g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
4169:
4170: hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
4171: hr_utility.raise_error;
4172:
4173: END IF;
4174:
4175: if g_debug then
4172:
4173: END IF;
4174:
4175: if g_debug then
4176: hr_utility.set_location(l_proc, 50);
4177: end if;
4178:
4179: close csr_get_tc_info;
4180:
4180:
4181: l_process_name := 'HXC_APPLY_NOTIFY';
4182:
4183: if g_debug then
4184: hr_utility.set_location(l_proc, 60);
4185: end if;
4186:
4187: l_approval_style_id := get_approval_style_id(l_tc_start_time,
4188: l_tc_stop_time,
4188: l_tc_stop_time,
4189: l_tc_resource_id);
4190: --
4191: if g_debug then
4192: hr_utility.set_location(l_proc, 70);
4193: --
4194: hr_utility.trace('l_approval_style_id is : ' || to_char(l_approval_style_id));
4195: -- For all the application periods created for this timecard,
4196: -- create a workflow process to continue with the approval - to
4190: --
4191: if g_debug then
4192: hr_utility.set_location(l_proc, 70);
4193: --
4194: hr_utility.trace('l_approval_style_id is : ' || to_char(l_approval_style_id));
4195: -- For all the application periods created for this timecard,
4196: -- create a workflow process to continue with the approval - to
4197: -- apply the approval rules and notify the approver(s).
4198:
4195: -- For all the application periods created for this timecard,
4196: -- create a workflow process to continue with the approval - to
4197: -- apply the approval rules and notify the approver(s).
4198:
4199: hr_utility.set_location(l_proc, 110);
4200: end if;
4201:
4202: open csr_get_appl_periods(l_tc_resource_id, l_tc_bld_blk_id);
4203:
4203:
4204: LOOP
4205:
4206: if g_debug then
4207: hr_utility.set_location(l_proc, 120);
4208: end if;
4209: fetch csr_get_appl_periods into l_appl_period_bb_id,
4210: l_period_start_date,
4211: l_period_end_date,
4219:
4220: exit when csr_get_appl_periods%NOTFOUND;
4221:
4222: if g_debug then
4223: hr_utility.trace('l_appl_period_bb_id is : ' ||
4224: to_char(l_appl_period_bb_id));
4225: hr_utility.trace('l_appl_period_bb_ovn is : ' ||
4226: to_char(l_appl_period_bb_ovn));
4227: hr_utility.trace('l_period_start_date is : ' ||
4221:
4222: if g_debug then
4223: hr_utility.trace('l_appl_period_bb_id is : ' ||
4224: to_char(l_appl_period_bb_id));
4225: hr_utility.trace('l_appl_period_bb_ovn is : ' ||
4226: to_char(l_appl_period_bb_ovn));
4227: hr_utility.trace('l_period_start_date is : ' ||
4228: to_char(l_period_start_date, 'DD-MM-YYYY'));
4229: hr_utility.trace('l_period_end_date is : ' ||
4223: hr_utility.trace('l_appl_period_bb_id is : ' ||
4224: to_char(l_appl_period_bb_id));
4225: hr_utility.trace('l_appl_period_bb_ovn is : ' ||
4226: to_char(l_appl_period_bb_ovn));
4227: hr_utility.trace('l_period_start_date is : ' ||
4228: to_char(l_period_start_date, 'DD-MM-YYYY'));
4229: hr_utility.trace('l_period_end_date is : ' ||
4230: to_char(l_period_end_date, 'DD-MM-YYYY'));
4231: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4225: hr_utility.trace('l_appl_period_bb_ovn is : ' ||
4226: to_char(l_appl_period_bb_ovn));
4227: hr_utility.trace('l_period_start_date is : ' ||
4228: to_char(l_period_start_date, 'DD-MM-YYYY'));
4229: hr_utility.trace('l_period_end_date is : ' ||
4230: to_char(l_period_end_date, 'DD-MM-YYYY'));
4231: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4232: hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
4233:
4227: hr_utility.trace('l_period_start_date is : ' ||
4228: to_char(l_period_start_date, 'DD-MM-YYYY'));
4229: hr_utility.trace('l_period_end_date is : ' ||
4230: to_char(l_period_end_date, 'DD-MM-YYYY'));
4231: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4232: hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
4233:
4234: hr_utility.set_location(l_proc, 150);
4235: end if;
4228: to_char(l_period_start_date, 'DD-MM-YYYY'));
4229: hr_utility.trace('l_period_end_date is : ' ||
4230: to_char(l_period_end_date, 'DD-MM-YYYY'));
4231: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4232: hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
4233:
4234: hr_utility.set_location(l_proc, 150);
4235: end if;
4236: -- Check to see if all the days in the application period have
4230: to_char(l_period_end_date, 'DD-MM-YYYY'));
4231: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4232: hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
4233:
4234: hr_utility.set_location(l_proc, 150);
4235: end if;
4236: -- Check to see if all the days in the application period have
4237: -- submitted days.
4238: --
4247: l_process := 'N';
4248: END IF;
4249:
4250: if g_debug then
4251: hr_utility.trace('Checked days:'||l_chk_days);
4252: hr_utility.set_location(l_proc, 160);
4253: end if;
4254: IF l_chk_days = 'Y' AND l_process = 'Y' THEN
4255:
4248: END IF;
4249:
4250: if g_debug then
4251: hr_utility.trace('Checked days:'||l_chk_days);
4252: hr_utility.set_location(l_proc, 160);
4253: end if;
4254: IF l_chk_days = 'Y' AND l_process = 'Y' THEN
4255:
4256: if g_debug then
4253: end if;
4254: IF l_chk_days = 'Y' AND l_process = 'Y' THEN
4255:
4256: if g_debug then
4257: hr_utility.set_location(l_proc, 170);
4258: end if;
4259: --
4260: -- Check to see if all applications before this one in the approval
4261: -- style have approved all the days in this period.
4269: ,p_time_category_id => l_time_category_id
4270: ,p_category_sequence => l_category_sequence);
4271:
4272: if g_debug then
4273: hr_utility.trace('All previous approved:'||l_approved);
4274: end if;
4275:
4276: IF l_approved = 'Y' THEN
4277:
4280:
4281: IF c_period_notified%NOTFOUND THEN
4282: CLOSE c_period_notified;
4283: if g_debug then
4284: hr_utility.trace('already processed ' || l_appl_period_bb_id);
4285: end if;
4286: ELSE
4287: CLOSE c_period_notified;
4288: if g_debug then
4285: end if;
4286: ELSE
4287: CLOSE c_period_notified;
4288: if g_debug then
4289: hr_utility.set_location(l_proc, 210);
4290: hr_utility.trace('itemtype is : ' || itemtype);
4291: hr_utility.trace('l_process_name is : ' || l_process_name);
4292: end if;
4293: --
4286: ELSE
4287: CLOSE c_period_notified;
4288: if g_debug then
4289: hr_utility.set_location(l_proc, 210);
4290: hr_utility.trace('itemtype is : ' || itemtype);
4291: hr_utility.trace('l_process_name is : ' || l_process_name);
4292: end if;
4293: --
4294: -- Setup l_item_key from a sequence.
4287: CLOSE c_period_notified;
4288: if g_debug then
4289: hr_utility.set_location(l_proc, 210);
4290: hr_utility.trace('itemtype is : ' || itemtype);
4291: hr_utility.trace('l_process_name is : ' || l_process_name);
4292: end if;
4293: --
4294: -- Setup l_item_key from a sequence.
4295: --
4293: --
4294: -- Setup l_item_key from a sequence.
4295: --
4296: if l_approval_item_key is not null then
4297: hr_utility.trace('OTL:pass 200 - '||l_approval_item_key);
4298:
4299: IF g_old_item_key.exists(l_approval_item_key) then
4300: hr_utility.trace('OTL: 200: exists in g table');
4301: l_is_blank := g_old_item_key(l_approval_item_key).is_diff_tc;
4296: if l_approval_item_key is not null then
4297: hr_utility.trace('OTL:pass 200 - '||l_approval_item_key);
4298:
4299: IF g_old_item_key.exists(l_approval_item_key) then
4300: hr_utility.trace('OTL: 200: exists in g table');
4301: l_is_blank := g_old_item_key(l_approval_item_key).is_diff_tc;
4302: ELSE
4303:
4304: l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
4305: itemkey => l_approval_item_key ,
4306: aname => 'IS_DIFF_TC',
4307: ignore_notfound => true);
4308: END IF;
4309: hr_utility.trace('OTL:pass 210');
4310: else
4311: l_is_blank := null;
4312: end if;
4313:
4323: where application_period_id = l_appl_period_bb_id
4324: and application_period_ovn = l_appl_period_bb_ovn;
4325:
4326: if g_debug then
4327: hr_utility.trace('l_item_key is : ' || l_item_key);
4328: end if;
4329:
4330: wf_engine.CreateProcess(itemtype => itemtype,
4331: itemkey => l_item_key,
4335: HXC_FIND_NOTIFY_APRS_PKG.get_login(p_person_id=>l_tc_resource_id,
4336: p_user_id => l_last_updated_by)
4337: );
4338: if g_debug then
4339: hr_utility.set_location(l_proc, 260);
4340: end if;
4341: if(item_attribute_exists(itemtype,l_item_key,'IS_DIFF_TC')) then
4342:
4343: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to '||l_is_blank||' - 05');
4339: hr_utility.set_location(l_proc, 260);
4340: end if;
4341: if(item_attribute_exists(itemtype,l_item_key,'IS_DIFF_TC')) then
4342:
4343: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to '||l_is_blank||' - 05');
4344:
4345: wf_engine.SetItemAttrText(
4346: itemtype => itemtype,
4347: itemkey => l_item_key,
4348: aname => 'IS_DIFF_TC',
4349: avalue => l_is_blank);
4350: else
4351:
4352: hr_utility.trace('SetItemAttrText of IS_DIFF_TC to '||l_is_blank||' - 06');
4353:
4354: wf_engine.additemattr
4355: (itemtype => itemtype,
4356: itemkey => l_item_key,
4367: itemkey => l_item_key,
4368: aname => 'FORMATTED_APP_START_DATE',
4369: avalue => to_char(l_period_start_date,'YYYY/MM/DD'));
4370: if g_debug then
4371: hr_utility.set_location(l_proc, 270);
4372: hr_utility.trace('APP_START_DATE is : ' ||
4373: to_char(l_period_start_date, 'DD-MM-YYYY'));
4374: end if;
4375:
4368: aname => 'FORMATTED_APP_START_DATE',
4369: avalue => to_char(l_period_start_date,'YYYY/MM/DD'));
4370: if g_debug then
4371: hr_utility.set_location(l_proc, 270);
4372: hr_utility.trace('APP_START_DATE is : ' ||
4373: to_char(l_period_start_date, 'DD-MM-YYYY'));
4374: end if;
4375:
4376: wf_engine.SetItemAttrDate(itemtype => itemtype,
4378: aname => 'APP_END_DATE',
4379: avalue => l_period_end_date);
4380:
4381: if g_debug then
4382: hr_utility.set_location(l_proc, 280);
4383: hr_utility.trace('APP_END_DATE is : ' ||
4384: to_char(l_period_end_date, 'DD-MM-YYYY'));
4385: end if;
4386:
4379: avalue => l_period_end_date);
4380:
4381: if g_debug then
4382: hr_utility.set_location(l_proc, 280);
4383: hr_utility.trace('APP_END_DATE is : ' ||
4384: to_char(l_period_end_date, 'DD-MM-YYYY'));
4385: end if;
4386:
4387: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4389: aname => 'APP_BB_ID',
4390: avalue => l_appl_period_bb_id);
4391:
4392: if g_debug then
4393: hr_utility.set_location(l_proc, 290);
4394: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
4395: end if;
4396:
4397: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4390: avalue => l_appl_period_bb_id);
4391:
4392: if g_debug then
4393: hr_utility.set_location(l_proc, 290);
4394: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
4395: end if;
4396:
4397: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4398: itemkey => l_item_key,
4399: aname => 'APP_BB_OVN',
4400: avalue => l_appl_period_bb_ovn);
4401:
4402: if g_debug then
4403: hr_utility.set_location(l_proc, 300);
4404: hr_utility.trace('APP_BB_OVN is : ' ||
4405: to_char(l_appl_period_bb_ovn));
4406: end if;
4407:
4400: avalue => l_appl_period_bb_ovn);
4401:
4402: if g_debug then
4403: hr_utility.set_location(l_proc, 300);
4404: hr_utility.trace('APP_BB_OVN is : ' ||
4405: to_char(l_appl_period_bb_ovn));
4406: end if;
4407:
4408: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4410: aname => 'RESOURCE_ID',
4411: avalue => l_tc_resource_id);
4412:
4413: if g_debug then
4414: hr_utility.set_location(l_proc, 310);
4415: hr_utility.trace('RESOURCE_ID is : ' || to_char(l_tc_resource_id));
4416: end if;
4417:
4418: wf_engine.SetItemAttrText(itemtype => itemtype,
4411: avalue => l_tc_resource_id);
4412:
4413: if g_debug then
4414: hr_utility.set_location(l_proc, 310);
4415: hr_utility.trace('RESOURCE_ID is : ' || to_char(l_tc_resource_id));
4416: end if;
4417:
4418: wf_engine.SetItemAttrText(itemtype => itemtype,
4419: itemkey => l_item_key,
4420: aname => 'TIME_RECIPIENT_ID',
4421: avalue => l_time_recipient);
4422:
4423: if g_debug then
4424: hr_utility.set_location(l_proc, 320);
4425: hr_utility.trace('TIME_RECIPIENT_ID is : ' || l_time_recipient);
4426: end if;
4427:
4428: wf_engine.SetItemAttrText(itemtype => itemtype,
4421: avalue => l_time_recipient);
4422:
4423: if g_debug then
4424: hr_utility.set_location(l_proc, 320);
4425: hr_utility.trace('TIME_RECIPIENT_ID is : ' || l_time_recipient);
4426: end if;
4427:
4428: wf_engine.SetItemAttrText(itemtype => itemtype,
4429: itemkey => l_item_key,
4430: aname => 'TC_RESUBMITTED',
4431: avalue => l_tc_resubmitted);
4432:
4433: if g_debug then
4434: hr_utility.set_location(l_proc, 330);
4435: hr_utility.trace('TC_RESUBMITTED is : ' || l_tc_resubmitted);
4436: end if;
4437:
4438: wf_engine.SetItemAttrText(itemtype => itemtype,
4431: avalue => l_tc_resubmitted);
4432:
4433: if g_debug then
4434: hr_utility.set_location(l_proc, 330);
4435: hr_utility.trace('TC_RESUBMITTED is : ' || l_tc_resubmitted);
4436: end if;
4437:
4438: wf_engine.SetItemAttrText(itemtype => itemtype,
4439: itemkey => l_item_key,
4440: aname => 'BB_NEW',
4441: avalue => l_bb_new);
4442:
4443: if g_debug then
4444: hr_utility.set_location(l_proc, 335);
4445: hr_utility.trace('BB_NEW is : ' || l_bb_new);
4446: end if;
4447:
4448: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4441: avalue => l_bb_new);
4442:
4443: if g_debug then
4444: hr_utility.set_location(l_proc, 335);
4445: hr_utility.trace('BB_NEW is : ' || l_bb_new);
4446: end if;
4447:
4448: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4449: itemkey => l_item_key,
4450: aname => 'TC_BLD_BLK_ID',
4451: avalue => l_tc_bld_blk_id);
4452:
4453: if g_debug then
4454: hr_utility.set_location(l_proc, 340);
4455: hr_utility.trace('TC_BLD_BLK_ID is : ' || to_char(l_tc_bld_blk_id));
4456: end if;
4457:
4458: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4451: avalue => l_tc_bld_blk_id);
4452:
4453: if g_debug then
4454: hr_utility.set_location(l_proc, 340);
4455: hr_utility.trace('TC_BLD_BLK_ID is : ' || to_char(l_tc_bld_blk_id));
4456: end if;
4457:
4458: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4459: itemkey => l_item_key,
4460: aname => 'TC_BLD_BLK_OVN',
4461: avalue => l_tc_ovn);
4462:
4463: if g_debug then
4464: hr_utility.set_location(l_proc, 350);
4465: hr_utility.trace('TC_BLD_BLK_OVN is : ' || to_char(l_tc_ovn));
4466: end if;
4467:
4468: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4461: avalue => l_tc_ovn);
4462:
4463: if g_debug then
4464: hr_utility.set_location(l_proc, 350);
4465: hr_utility.trace('TC_BLD_BLK_OVN is : ' || to_char(l_tc_ovn));
4466: end if;
4467:
4468: wf_engine.SetItemAttrNumber(itemtype => itemtype,
4469: itemkey => l_item_key,
4497:
4498: -- Update attribute4 with NOTIFIED and attribute2 with the Item Key.
4499:
4500: if g_debug then
4501: hr_utility.trace('APP_BB_OVN is : ' ||
4502: to_char(l_appl_period_bb_ovn));
4503: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
4504: hr_utility.trace('Before Update');
4505: hr_utility.set_location(l_proc, 360);
4499:
4500: if g_debug then
4501: hr_utility.trace('APP_BB_OVN is : ' ||
4502: to_char(l_appl_period_bb_ovn));
4503: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
4504: hr_utility.trace('Before Update');
4505: hr_utility.set_location(l_proc, 360);
4506: end if;
4507:
4500: if g_debug then
4501: hr_utility.trace('APP_BB_OVN is : ' ||
4502: to_char(l_appl_period_bb_ovn));
4503: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
4504: hr_utility.trace('Before Update');
4505: hr_utility.set_location(l_proc, 360);
4506: end if;
4507:
4508: update hxc_app_period_summary
4501: hr_utility.trace('APP_BB_OVN is : ' ||
4502: to_char(l_appl_period_bb_ovn));
4503: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
4504: hr_utility.trace('Before Update');
4505: hr_utility.set_location(l_proc, 360);
4506: end if;
4507:
4508: update hxc_app_period_summary
4509: set notification_status = 'NOTIFIED'
4514: wf_engine.StartProcess(itemtype => itemtype,
4515: itemkey => l_item_key);
4516:
4517: if g_debug then
4518: hr_utility.set_location(l_proc, 365);
4519: end if;
4520: END IF; -- if not notified;
4521: END IF; -- approved
4522:
4522:
4523: END IF; -- l_chk_days
4524:
4525: if g_debug then
4526: hr_utility.set_location(l_proc, 380);
4527: end if;
4528:
4529: END LOOP;
4530:
4528:
4529: END LOOP;
4530:
4531: if g_debug then
4532: hr_utility.trace('OUTSIDE END LOOP');
4533: end if;
4534:
4535: close csr_get_appl_periods;
4536:
4541: -- The line below records this function call in the error system
4542: -- in the case of an exception.
4543: --
4544: if g_debug then
4545: hr_utility.set_location(l_proc, 999);
4546: --
4547: hr_utility.trace('IN EXCEPTION IN process_appl_periods');
4548: end if;
4549: --
4543: --
4544: if g_debug then
4545: hr_utility.set_location(l_proc, 999);
4546: --
4547: hr_utility.trace('IN EXCEPTION IN process_appl_periods');
4548: end if;
4549: --
4550: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.process_appl_periods',
4551: itemtype, itemkey, to_char(actid), funcmode);
4737: --
4738: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.start_approval_wf_process';
4739: --
4740: BEGIN
4741: g_debug:=hr_utility.debug_enabled;
4742: --
4743: if g_debug then
4744: hr_utility.set_location(l_proc, 10);
4745: --
4740: BEGIN
4741: g_debug:=hr_utility.debug_enabled;
4742: --
4743: if g_debug then
4744: hr_utility.set_location(l_proc, 10);
4745: --
4746: hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4747: hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4748: hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4742: --
4743: if g_debug then
4744: hr_utility.set_location(l_proc, 10);
4745: --
4746: hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4747: hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4748: hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4749: --
4750: end if;
4743: if g_debug then
4744: hr_utility.set_location(l_proc, 10);
4745: --
4746: hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4747: hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4748: hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4749: --
4750: end if;
4751: -- Nulls out the error table.
4744: hr_utility.set_location(l_proc, 10);
4745: --
4746: hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4747: hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4748: hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4749: --
4750: end if;
4751: -- Nulls out the error table.
4752: --
4769: itemkey => p_item_key,
4770: process => l_process_name);
4771: --
4772: if g_debug then
4773: hr_utility.set_location(l_proc, 20);
4774: end if;
4775: --
4776: wf_engine.SetItemAttrNumber(itemtype => p_item_type,
4777: itemkey => p_item_key,
4778: aname => 'TC_BLD_BLK_ID',
4779: avalue => p_tc_bb_id);
4780: --
4781: if g_debug then
4782: hr_utility.set_location(l_proc, 30);
4783: end if;
4784: --
4785: wf_engine.SetItemAttrNumber(itemtype => p_item_type,
4786: itemkey => p_item_key,
4787: aname => 'TC_BLD_BLK_OVN',
4788: avalue => p_tc_ovn);
4789: --
4790: if g_debug then
4791: hr_utility.set_location(l_proc, 40);
4792: end if;
4793: --
4794: wf_engine.SetItemAttrText(itemtype => p_item_type,
4795: itemkey => p_item_key,
4796: aname => 'TC_RESUBMITTED',
4797: avalue => p_tc_resubmitted);
4798: --
4799: if g_debug then
4800: hr_utility.set_location(l_proc, 50);
4801: end if;
4802: --
4803: IF p_bb_new = 'Y' THEN
4804: wf_engine.SetItemAttrText(itemtype => p_item_type,
4812: avalue => 'NO');
4813: END IF;
4814: --
4815: if g_debug then
4816: hr_utility.set_location(l_proc, 60);
4817: end if;
4818: --
4819: wf_engine.StartProcess(itemtype => p_item_type,
4820: itemkey => p_item_key);
4821: --
4822: wf_engine.threshold := 50;
4823: --
4824: if g_debug then
4825: hr_utility.set_location(l_proc, 70);
4826: end if;
4827: --
4828: p_error_table := g_error_table;
4829: --
4876: --
4877: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.upd_apr_details';
4878: --
4879: BEGIN
4880: g_debug:=hr_utility.debug_enabled;
4881: --
4882: if g_debug then
4883: hr_utility.set_location(l_proc, 10);
4884: --
4879: BEGIN
4880: g_debug:=hr_utility.debug_enabled;
4881: --
4882: if g_debug then
4883: hr_utility.set_location(l_proc, 10);
4884: --
4885: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4886: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4887: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4881: --
4882: if g_debug then
4883: hr_utility.set_location(l_proc, 10);
4884: --
4885: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4886: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4887: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4888: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4889: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4882: if g_debug then
4883: hr_utility.set_location(l_proc, 10);
4884: --
4885: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4886: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4887: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4888: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4889: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4890: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4883: hr_utility.set_location(l_proc, 10);
4884: --
4885: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4886: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4887: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4888: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4889: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4890: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4891: --
4884: --
4885: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4886: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4887: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4888: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4889: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4890: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4891: --
4892: hr_utility.set_location(l_proc, 20);
4885: hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4886: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4887: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4888: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4889: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4890: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4891: --
4892: hr_utility.set_location(l_proc, 20);
4893: end if;
4886: hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4887: hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4888: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4889: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4890: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4891: --
4892: hr_utility.set_location(l_proc, 20);
4893: end if;
4894: -- Perf Rep Fix - SQL ID :3170802
4888: hr_utility.trace('l_approved_time is : ' || l_approved_time);
4889: hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4890: hr_utility.trace('l_approved_status is : ' || l_approved_status);
4891: --
4892: hr_utility.set_location(l_proc, 20);
4893: end if;
4894: -- Perf Rep Fix - SQL ID :3170802
4895: -- Added attribute_category = APPROVAL to the where clause.
4896:
4907: and attribute3 = to_char(l_approver_id)
4908: and attribute_category = 'APPROVAL';
4909: --
4910: if g_debug then
4911: hr_utility.set_location(l_proc, 30);
4912: end if;
4913: --
4914: END upd_apr_details;
4915: --
5035: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.is_appr_required';
5036:
5037: --
5038: BEGIN
5039: g_debug:=hr_utility.debug_enabled;
5040:
5041: --
5042: if g_debug then
5043: hr_utility.set_location(l_proc, 10);
5039: g_debug:=hr_utility.debug_enabled;
5040:
5041: --
5042: if g_debug then
5043: hr_utility.set_location(l_proc, 10);
5044: end if;
5045: --
5046: l_app_bld_blk_id := wf_engine.GetItemAttrNumber
5047: (itemtype => itemtype,
5048: itemkey => itemkey ,
5049: aname => 'APP_BB_ID');
5050: --
5051: if g_debug then
5052: hr_utility.set_location(l_proc, 20);
5053: end if;
5054: --
5055: l_app_ovn := wf_engine.GetItemAttrNumber
5056: (itemtype => itemtype,
5062: itemkey => itemkey,
5063: aname => 'TC_BLD_BLK_ID');
5064: --
5065: if g_debug then
5066: hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
5067: end if;
5068: --
5069: l_tc_ovn := wf_engine.GetItemAttrNumber
5070: (itemtype => itemtype,
5076: itemkey => itemkey ,
5077: aname => 'TIME_RECIPIENT_ID');
5078:
5079: if g_debug then
5080: hr_utility.set_location(l_proc, 30);
5081: end if;
5082: --
5083: -- Sets up global variables for timecard records.
5084: --
5089: ,p_timecard_app_attributes => g_time_app_attributes
5090: ,p_time_recipient_id => to_number(l_time_recipient));
5091:
5092: if g_debug then
5093: hr_utility.set_location(l_proc, 40);
5094: end if;
5095: --
5096: -- Get the package.procedure from the extension_function1 column
5097: -- on hxc_time_recipients.
5126: --
5127: IF l_ext_func1 IS NOT NULL THEN
5128:
5129: if g_debug then
5130: hr_utility.set_location(l_proc, 50);
5131: end if;
5132:
5133: IF code_chk(l_ext_func1) THEN
5134:
5132:
5133: IF code_chk(l_ext_func1) THEN
5134:
5135: if g_debug then
5136: hr_utility.set_location(l_proc, 60);
5137: end if;
5138: --
5139: l_func_sql := 'BEGIN '||fnd_global.newline
5140: ||l_ext_func1 ||fnd_global.newline
5152: ELSE
5153: l_auto_approval_flag := 'N';
5154: END IF;
5155: if g_debug then
5156: hr_utility.set_location(l_proc, 70);
5157: end if;
5158: --
5159: IF l_auto_approval_flag = 'Y' THEN
5160:
5158: --
5159: IF l_auto_approval_flag = 'Y' THEN
5160:
5161: if g_debug then
5162: hr_utility.set_location(l_proc, 80);
5163: end if;
5164: --
5165: wf_engine.SetItemAttrText(itemtype => itemtype,
5166: itemkey => itemkey,
5169: --
5170: END IF;
5171:
5172: if g_debug then
5173: hr_utility.set_location(l_proc, 90);
5174: end if;
5175: --
5176: l_exception := NULL;
5177: --
5177: --
5178: IF l_message IS NOT NULL THEN
5179:
5180: if g_debug then
5181: hr_utility.set_location(l_proc, 100);
5182: end if;
5183: --
5184: l_message_table := hxc_deposit_wrapper_utilities.string_to_messages
5185: (p_message_string => l_message);
5218: --
5219: END IF;
5220:
5221: if g_debug then
5222: hr_utility.set_location(l_proc, 110);
5223: end if;
5224: --jxtan in new implementation, comment is save in hxc_time_building_blocks
5225: UPDATE hxc_time_building_blocks
5226: SET comment_text = substr(l_exception, 1, 2000)
5227: WHERE time_building_block_id = l_app_bld_blk_id
5228: AND object_version_number = l_app_ovn;
5229:
5230: if g_debug then
5231: hr_utility.set_location(l_proc, 120);
5232: end if;
5233: --
5234: wf_engine.SetItemAttrText(itemtype => itemtype,
5235: itemkey => itemkey,
5238: --
5239: END IF;
5240:
5241: if g_debug then
5242: hr_utility.set_location(l_proc, 130);
5243: end if;
5244: --
5245: IF upper(wf_engine.GetItemAttrText(itemtype => itemtype,
5246: itemkey => itemkey ,
5248: --
5249: result := 'COMPLETE:Y';
5250: --
5251: if g_debug then
5252: hr_utility.set_location(l_proc, 140);
5253: --
5254: hr_utility.trace('APPR_REQ attribute is : YES');
5255: --
5256: end if;
5250: --
5251: if g_debug then
5252: hr_utility.set_location(l_proc, 140);
5253: --
5254: hr_utility.trace('APPR_REQ attribute is : YES');
5255: --
5256: end if;
5257: return;
5258: --
5258: --
5259: ELSE
5260: --
5261: if g_debug then
5262: hr_utility.set_location(l_proc, 150);
5263: --
5264: hr_utility.trace('APPR_REQ attribute is : NO');
5265: --
5266: end if;
5260: --
5261: if g_debug then
5262: hr_utility.set_location(l_proc, 150);
5263: --
5264: hr_utility.trace('APPR_REQ attribute is : NO');
5265: --
5266: end if;
5267: wf_engine.SetItemAttrText(itemtype => itemtype,
5268: itemkey => itemkey,
5274: aname => 'APR_REJ_REASON',
5275: avalue => 'AUTO_APPROVE');
5276:
5277: if g_debug then
5278: hr_utility.set_location(l_proc, 160);
5279: --
5280: --
5281: hr_utility.trace('APPROVAL_STATUS attribute is : APPROVED');
5282: --
5277: if g_debug then
5278: hr_utility.set_location(l_proc, 160);
5279: --
5280: --
5281: hr_utility.trace('APPROVAL_STATUS attribute is : APPROVED');
5282: --
5283: end if;
5284: --OIT Enhancement.
5285: --FYI Notification to WORKER on timecard AUTO APPROVE
5294: --
5295: END IF;
5296: --
5297: if g_debug then
5298: hr_utility.set_location(l_proc, 170);
5299: end if;
5300: --
5301: exception
5302: when others then
5303: -- The line below records this function call in the error system
5304: -- in the case of an exception.
5305: --
5306: if g_debug then
5307: hr_utility.set_location(l_proc, 999);
5308: --
5309: hr_utility.trace('IN EXCEPTION IN is_appr_required');
5310: --
5311: end if;
5305: --
5306: if g_debug then
5307: hr_utility.set_location(l_proc, 999);
5308: --
5309: hr_utility.trace('IN EXCEPTION IN is_appr_required');
5310: --
5311: end if;
5312: wf_core.context('HCAPPRWF',
5313: 'hxc_approval_wf_pkg.is_appr_required',
5378: --
5379: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_appr_rules';
5380: --
5381: BEGIN
5382: g_debug:=hr_utility.debug_enabled;
5383: --
5384: if g_debug then
5385: hr_utility.set_location(l_proc, 10);
5386: end if;
5381: BEGIN
5382: g_debug:=hr_utility.debug_enabled;
5383: --
5384: if g_debug then
5385: hr_utility.set_location(l_proc, 10);
5386: end if;
5387: --
5388: l_app_bld_blk_id := wf_engine.GetItemAttrNumber
5389: (itemtype => itemtype,
5390: itemkey => itemkey ,
5391: aname => 'APP_BB_ID');
5392: --
5393: if g_debug then
5394: hr_utility.set_location(l_proc, 20);
5395: end if;
5396: --
5397: l_app_ovn := wf_engine.GetItemAttrNumber
5398: (itemtype => itemtype,
5399: itemkey => itemkey ,
5400: aname => 'APP_BB_OVN');
5401: --
5402: if g_debug then
5403: hr_utility.set_location(l_proc, 30);
5404: end if;
5405: --
5406: l_time_recipient := wf_engine.GetItemAttrText
5407: (itemtype => itemtype,
5408: itemkey => itemkey,
5409: aname => 'TIME_RECIPIENT_ID');
5410: --
5411: if g_debug then
5412: hr_utility.set_location(l_proc, 40);
5413: --
5414: hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
5415: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
5416: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
5410: --
5411: if g_debug then
5412: hr_utility.set_location(l_proc, 40);
5413: --
5414: hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
5415: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
5416: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
5417: --
5418:
5411: if g_debug then
5412: hr_utility.set_location(l_proc, 40);
5413: --
5414: hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
5415: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
5416: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
5417: --
5418:
5419: hr_utility.set_location(l_proc, 70);
5412: hr_utility.set_location(l_proc, 40);
5413: --
5414: hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
5415: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
5416: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
5417: --
5418:
5419: hr_utility.set_location(l_proc, 70);
5420: --
5415: hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
5416: hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
5417: --
5418:
5419: hr_utility.set_location(l_proc, 70);
5420: --
5421: end if;
5422: l_tc_appr_style_id := wf_engine.GetItemAttrNumber
5423: (itemtype => itemtype,
5424: itemkey => itemkey,
5425: aname => 'APPROVAL_STYLE_ID');
5426: --
5427: if g_debug then
5428: hr_utility.trace('l_tc_appr_style_id is : ' || to_char(l_tc_appr_style_id));
5429: end if;
5430: --
5431: open csr_get_appr_rule_id(l_tc_appr_style_id,
5432: l_time_recipient);
5432: l_time_recipient);
5433: fetch csr_get_appr_rule_id into l_data_appr_rule_id;
5434: --
5435: if g_debug then
5436: hr_utility.set_location(l_proc, 80);
5437: --
5438: hr_utility.trace('l_data_appr_rule_id is : ' || to_char(l_data_appr_rule_id));
5439: --
5440: end if;
5434: --
5435: if g_debug then
5436: hr_utility.set_location(l_proc, 80);
5437: --
5438: hr_utility.trace('l_data_appr_rule_id is : ' || to_char(l_data_appr_rule_id));
5439: --
5440: end if;
5441: IF csr_get_appr_rule_id%NOTFOUND THEN
5442:
5445: result := 'COMPLETE:N';
5446: l_all_rules := 'NO_RULES';
5447: --
5448: if g_debug then
5449: hr_utility.set_location(l_proc, 90);
5450: --
5451: -- hr_utility.trace('Setting Status to Approved');
5452: --
5453: end if;
5447: --
5448: if g_debug then
5449: hr_utility.set_location(l_proc, 90);
5450: --
5451: -- hr_utility.trace('Setting Status to Approved');
5452: --
5453: end if;
5454: -- wf_engine.SetItemAttrText(itemtype => itemtype,
5455: -- itemkey => itemkey,
5462: --
5463: result := 'COMPLETE:Y';
5464: --
5465: if g_debug then
5466: hr_utility.set_location(l_proc, 100);
5467: end if;
5468: --
5469: l_cnt := 1;
5470: --
5485:
5486: CLOSE csr_get_appr_rule_id;
5487: --
5488: if g_debug then
5489: hr_utility.set_location(l_proc, 110);
5490: --
5491: hr_utility.trace('l_all_rules is : ' || l_all_rules);
5492: --
5493: end if;
5487: --
5488: if g_debug then
5489: hr_utility.set_location(l_proc, 110);
5490: --
5491: hr_utility.trace('l_all_rules is : ' || l_all_rules);
5492: --
5493: end if;
5494: END IF;
5495: --
5495: --
5496: IF l_all_rules <> 'NO_RULES' THEN
5497: --
5498: if g_debug then
5499: hr_utility.set_location(l_proc, 120);
5500: end if;
5501: --
5502: wf_engine.SetItemAttrText(itemtype => itemtype,
5503: itemkey => itemkey,
5504: aname => 'ALL_RULES',
5505: avalue => l_all_rules);
5506: --
5507: if g_debug then
5508: hr_utility.trace('ALL_RULES Attribute is : ' || l_all_rules);
5509: end if;
5510: --
5511: END IF;
5512: --
5517: -- The line below records this function call in the error system
5518: -- in the case of an exception.
5519: --
5520: if g_debug then
5521: hr_utility.set_location(l_proc, 999);
5522: --
5523: hr_utility.trace('IN EXCEPTION IN chk_appr_rules');
5524: --
5525: end if;
5519: --
5520: if g_debug then
5521: hr_utility.set_location(l_proc, 999);
5522: --
5523: hr_utility.trace('IN EXCEPTION IN chk_appr_rules');
5524: --
5525: end if;
5526: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.chk_appr_rules',
5527: itemtype, itemkey, to_char(actid), funcmode);
5550: -- l_item_key wf_items.item_key%type;
5551: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.find_approval_rule';
5552: --
5553: BEGIN
5554: g_debug:=hr_utility.debug_enabled;
5555: --
5556: if g_debug then
5557: hr_utility.set_location(l_proc, 10);
5558: end if;
5553: BEGIN
5554: g_debug:=hr_utility.debug_enabled;
5555: --
5556: if g_debug then
5557: hr_utility.set_location(l_proc, 10);
5558: end if;
5559: --
5560: l_all_rules := wf_engine.GetItemAttrText(itemtype => itemtype,
5561: itemkey => itemkey ,
5561: itemkey => itemkey ,
5562: aname => 'ALL_RULES');
5563: --
5564: if g_debug then
5565: hr_utility.set_location(l_proc, 20);
5566: --
5567: hr_utility.trace('ALL_RULES is : ' || l_all_rules);
5568: --
5569: end if;
5563: --
5564: if g_debug then
5565: hr_utility.set_location(l_proc, 20);
5566: --
5567: hr_utility.trace('ALL_RULES is : ' || l_all_rules);
5568: --
5569: end if;
5570: l_cnt := instr(l_all_rules, '|');
5571: --
5571: --
5572: IF l_cnt <> 0 THEN
5573: --
5574: if g_debug then
5575: hr_utility.set_location(l_proc, 30);
5576: end if;
5577: --
5578: l_current_rule := substr(l_all_rules, 1, l_cnt - 1);
5579: l_all_rules := replace(l_all_rules, l_current_rule || '|');
5578: l_current_rule := substr(l_all_rules, 1, l_cnt - 1);
5579: l_all_rules := replace(l_all_rules, l_current_rule || '|');
5580: --
5581: if g_debug then
5582: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5583: end if;
5584: --
5585: result := 'COMPLETE:Y';
5586: --
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: --
5592: --
5593: result := 'COMPLETE:N';
5594: --
5595: if g_debug then
5596: hr_utility.trace('No More Rules - Setting status to APPROVED');
5597: end if;
5598: --
5599: wf_engine.SetItemAttrText(itemtype => itemtype,
5600: itemkey => itemkey,
5625: aname => 'CURRENT_RULE',
5626: avalue => l_current_rule);
5627: --
5628: if g_debug then
5629: hr_utility.set_location(l_proc, 60);
5630: --
5631: hr_utility.trace('CURRENT_RULE is : ' || l_current_rule);
5632: --
5633: end if;
5627: --
5628: if g_debug then
5629: hr_utility.set_location(l_proc, 60);
5630: --
5631: hr_utility.trace('CURRENT_RULE is : ' || l_current_rule);
5632: --
5633: end if;
5634: wf_engine.SetItemAttrText(itemtype => itemtype,
5635: itemkey => itemkey,
5636: aname => 'ALL_RULES',
5637: avalue => l_all_rules);
5638: --
5639: if g_debug then
5640: hr_utility.set_location(l_proc, 60);
5641: --
5642: hr_utility.trace('ALL_RULES is : ' || l_all_rules);
5643: --
5644: end if;
5638: --
5639: if g_debug then
5640: hr_utility.set_location(l_proc, 60);
5641: --
5642: hr_utility.trace('ALL_RULES is : ' || l_all_rules);
5643: --
5644: end if;
5645: return;
5646: --
5649: -- The line below records this function call in the error system
5650: -- in the case of an exception.
5651: --
5652: if g_debug then
5653: hr_utility.set_location(l_proc, 999);
5654: --
5655: hr_utility.trace('IN EXCEPTION IN find_approval_rule');
5656: --
5657: end if;
5651: --
5652: if g_debug then
5653: hr_utility.set_location(l_proc, 999);
5654: --
5655: hr_utility.trace('IN EXCEPTION IN find_approval_rule');
5656: --
5657: end if;
5658: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.find_approval_rule',
5659: itemtype, itemkey, to_char(actid), funcmode);
5838: -- l_item_key wf_items.item_key%type;
5839: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.execute_appr_rule';
5840: --
5841: BEGIN
5842: g_debug:=hr_utility.debug_enabled;
5843: --
5844: if g_debug then
5845: hr_utility.set_location(l_proc, 10);
5846: end if;
5841: BEGIN
5842: g_debug:=hr_utility.debug_enabled;
5843: --
5844: if g_debug then
5845: hr_utility.set_location(l_proc, 10);
5846: end if;
5847: --
5848:
5849:
5851: itemkey => itemkey,
5852: aname => 'CURRENT_RULE');
5853: --
5854: if g_debug then
5855: hr_utility.set_location(l_proc, 20);
5856: end if;
5857: --
5858: l_app_start_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
5859: itemkey => itemkey,
5859: itemkey => itemkey,
5860: aname => 'APP_START_DATE');
5861: --
5862: if g_debug then
5863: hr_utility.set_location(l_proc, 25);
5864: end if;
5865: --
5866: l_app_end_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
5867: itemkey => itemkey,
5867: itemkey => itemkey,
5868: aname => 'APP_END_DATE');
5869: --
5870: if g_debug then
5871: hr_utility.set_location(l_proc, 30);
5872: end if;
5873: --
5874: l_tc_resource_id := wf_engine.GetItemAttrNumber(
5875: itemtype => itemtype,
5876: itemkey => itemkey,
5877: aname => 'RESOURCE_ID');
5878: --
5879: if g_debug then
5880: hr_utility.set_location(l_proc, 40);
5881: end if;
5882: --
5883: l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
5884: (itemtype => itemtype,
5885: itemkey => itemkey,
5886: aname => 'TC_BLD_BLK_ID');
5887: --
5888: if g_debug then
5889: hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
5890: end if;
5891: --
5892: l_tc_ovn := wf_engine.GetItemAttrNumber
5893: (itemtype => itemtype,
5894: itemkey => itemkey,
5895: aname => 'TC_BLD_BLK_OVN');
5896: --
5897: if g_debug then
5898: hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
5899: end if;
5900: --
5901: -- Set up l_resubmit to indicate whether this is a resubmission or not.
5902: -- (YES means it is a resubmission; NO means it is a submission).
5905: itemkey => itemkey,
5906: aname => 'TC_RESUBMITTED');
5907: --
5908: if g_debug then
5909: hr_utility.set_location(l_proc, 50);
5910: end if;
5911: --
5912: l_bb_new := wf_engine.GetItemAttrText(itemtype => itemtype,
5913: itemkey => itemkey,
5913: itemkey => itemkey,
5914: aname => 'BB_NEW');
5915: --
5916: if g_debug then
5917: hr_utility.set_location(l_proc, 52);
5918: end if;
5919: --
5920: l_data_appr_rule_id := to_number(l_current_rule);
5921: --
5919: --
5920: l_data_appr_rule_id := to_number(l_current_rule);
5921: --
5922: if g_debug then
5923: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5924: hr_utility.trace('l_app_end_date is : ' ||
5925: to_char(l_app_end_date, 'DD-MM-YYYY'));
5926: hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5927: hr_utility.trace('l_resubmit is : ' || l_resubmit);
5920: l_data_appr_rule_id := to_number(l_current_rule);
5921: --
5922: if g_debug then
5923: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5924: hr_utility.trace('l_app_end_date is : ' ||
5925: to_char(l_app_end_date, 'DD-MM-YYYY'));
5926: hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5927: hr_utility.trace('l_resubmit is : ' || l_resubmit);
5928: end if;
5922: if g_debug then
5923: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5924: hr_utility.trace('l_app_end_date is : ' ||
5925: to_char(l_app_end_date, 'DD-MM-YYYY'));
5926: hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5927: hr_utility.trace('l_resubmit is : ' || l_resubmit);
5928: end if;
5929: --
5930: open csr_get_appr_rule_info(l_current_rule, l_app_end_date);
5923: hr_utility.trace('l_current_rule is : ' || l_current_rule);
5924: hr_utility.trace('l_app_end_date is : ' ||
5925: to_char(l_app_end_date, 'DD-MM-YYYY'));
5926: hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5927: hr_utility.trace('l_resubmit is : ' || l_resubmit);
5928: end if;
5929: --
5930: open csr_get_appr_rule_info(l_current_rule, l_app_end_date);
5931: fetch csr_get_appr_rule_info into l_rule_rec;
5931: fetch csr_get_appr_rule_info into l_rule_rec;
5932: close csr_get_appr_rule_info;
5933: --
5934: if g_debug then
5935: hr_utility.set_location(l_proc, 60);
5936: end if;
5937: --
5938: IF (l_resubmit = 'YES' AND l_rule_rec.rule_usage <> 'SUBMISSION') OR
5939: (l_resubmit = 'NO' AND l_rule_rec.rule_usage <> 'RESUBMISSION') THEN
5938: IF (l_resubmit = 'YES' AND l_rule_rec.rule_usage <> 'SUBMISSION') OR
5939: (l_resubmit = 'NO' AND l_rule_rec.rule_usage <> 'RESUBMISSION') THEN
5940: --
5941: if g_debug then
5942: hr_utility.set_location(l_proc, 70);
5943: end if;
5944: --
5945: -- Apply rule
5946: --
5954: -- Then, check for formula, since it is possible to have both
5955: -- a mapping and a formula.
5956: --
5957: if g_debug then
5958: hr_utility.set_location(l_proc, 80);
5959: end if;
5960: --
5961: IF l_resubmit = 'YES' THEN
5962: --
5960: --
5961: IF l_resubmit = 'YES' THEN
5962: --
5963: if g_debug then
5964: hr_utility.set_location(l_proc, 90);
5965: end if;
5966: l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
5967: itemtype => itemtype,
5968: itemkey => itemkey,
5968: itemkey => itemkey,
5969: aname => 'APP_BB_ID');
5970:
5971: if g_debug then
5972: hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
5973: end if;
5974: l_appl_period_bb_ovn := wf_engine.GetItemAttrNumber(
5975: itemtype => itemtype,
5976: itemkey => itemkey,
6013: --
6014: IF l_bld_blk_changed THEN
6015: --
6016: if g_debug then
6017: hr_utility.set_location(l_proc, 130);
6018: end if;
6019: --
6020: l_changed := 'YES';
6021: --
6021: --
6022: ELSE
6023: --
6024: if g_debug then
6025: hr_utility.set_location(l_proc, 140);
6026: end if;
6027: --
6028: l_mapping_changed := hxc_mapping_utilities.chk_mapping_changed(
6029: p_mapping_id => l_rule_rec.mapping_id
6035: ,p_time_building_blocks => g_time_building_blocks
6036: ,p_time_attributes => g_time_attributes);
6037: --
6038: if g_debug then
6039: hr_utility.set_location(l_proc, 120);
6040: end if;
6041: --
6042: -- If there are differences, set l_changed to YES; else set to NO.
6043: --
6043: --
6044: IF l_mapping_changed THEN
6045: --
6046: if g_debug then
6047: hr_utility.set_location(l_proc, 130);
6048: end if;
6049: --
6050: l_changed := 'YES';
6051: --
6051: --
6052: ELSE
6053: --
6054: if g_debug then
6055: hr_utility.set_location(l_proc, 140);
6056: end if;
6057: --
6058: l_changed := 'NO';
6059: --
6065: --
6066: -- Set l_changed to YES
6067: --
6068: if g_debug then
6069: hr_utility.set_location(l_proc, 150);
6070: end if;
6071: --
6072: l_changed := 'YES';
6073: --
6078: -- No Mapping ID, but there might still be a formula so set
6079: -- l_changed to YES
6080: --
6081: if g_debug then
6082: hr_utility.set_location(l_proc, 160);
6083: end if;
6084: --
6085: l_changed := 'NO'; -- GPM v115.48 WWB 2724576
6086: --
6086: --
6087: END IF;
6088: --
6089: if g_debug then
6090: hr_utility.set_location(l_proc, 170);
6091: end if;
6092: --
6093: -- Check to see is a formula needs to be applied.
6094: --
6095: IF (l_rule_rec.formula_id IS NOT NULL AND l_changed = 'NO') -- GPM v115.48 WWB 2724576
6096: THEN
6097: --
6098: if g_debug then
6099: hr_utility.set_location(l_proc, 180);
6100: --
6101: hr_utility.trace('l_formula_name is : ' || l_rule_rec.formula_name);
6102: --
6103: hr_utility.set_location(l_proc, 190);
6097: --
6098: if g_debug then
6099: hr_utility.set_location(l_proc, 180);
6100: --
6101: hr_utility.trace('l_formula_name is : ' || l_rule_rec.formula_name);
6102: --
6103: hr_utility.set_location(l_proc, 190);
6104: end if;
6105: --
6099: hr_utility.set_location(l_proc, 180);
6100: --
6101: hr_utility.trace('l_formula_name is : ' || l_rule_rec.formula_name);
6102: --
6103: hr_utility.set_location(l_proc, 190);
6104: end if;
6105: --
6106: -- call execute approval formula
6107: --
6132: l_result := 'NO';
6133: END IF;
6134: --
6135: IF upper(l_result) <> 'YES' AND upper(l_result) <> 'NO' THEN
6136: hr_utility.raise_error;
6137: END IF;
6138: --
6139: IF l_error_table.count > 0 THEN
6140: hr_utility.set_message(809, l_error_table(1).message_name);
6136: hr_utility.raise_error;
6137: END IF;
6138: --
6139: IF l_error_table.count > 0 THEN
6140: hr_utility.set_message(809, l_error_table(1).message_name);
6141: hr_utility.raise_error;
6142: END IF;
6143: --
6144: wf_engine.SetItemAttrText(itemtype => itemtype,
6137: END IF;
6138: --
6139: IF l_error_table.count > 0 THEN
6140: hr_utility.set_message(809, l_error_table(1).message_name);
6141: hr_utility.raise_error;
6142: END IF;
6143: --
6144: wf_engine.SetItemAttrText(itemtype => itemtype,
6145: itemkey => itemkey,
6146: aname => 'TO_APPROVE',
6147: avalue => upper(l_result));
6148: --
6149: if g_debug then
6150: hr_utility.trace('TO_APPROVE is : ' || l_result);
6151: end if;
6152: --
6153: ELSE
6154: --
6154: --
6155: -- Set TO_APPROVE to l_changed.
6156: --
6157: if g_debug then
6158: hr_utility.set_location(l_proc, 230);
6159: end if;
6160: --
6161: wf_engine.SetItemAttrText(itemtype => itemtype,
6162: itemkey => itemkey,
6163: aname => 'TO_APPROVE',
6164: avalue => l_changed);
6165: --
6166: if g_debug then
6167: hr_utility.trace('TO_APPROVE is : ' || l_changed);
6168: end if;
6169: --
6170: END IF;
6171: --
6173: --
6174: -- Set TO_APPROVE attribute to YES since the rule does not apply.
6175: --
6176: if g_debug then
6177: hr_utility.set_location(l_proc, 240);
6178: end if;
6179: --
6180: -- Modifying the 'TO_APPROVE' value from 'NO' to 'YES' and Commenting out the following code for bug#3497011.
6181:
6184: aname => 'TO_APPROVE',
6185: avalue => 'NO');
6186: --
6187: if g_debug then
6188: hr_utility.trace('TO_APPROVE is : NO');
6189: end if; */
6190:
6191: wf_engine.SetItemAttrText(itemtype => itemtype,
6192: itemkey => itemkey,
6193: aname => 'TO_APPROVE',
6194: avalue => 'YES');
6195: --
6196: if g_debug then
6197: hr_utility.trace('TO_APPROVE is : YES');
6198: end if;--
6199: END IF;
6200: --
6201: if g_debug then
6198: end if;--
6199: END IF;
6200: --
6201: if g_debug then
6202: hr_utility.set_location(l_proc, 250);
6203: end if;
6204: --
6205: --
6206: result := '';
6210: when others then
6211: -- The line below records this function call in the error system
6212: -- in the case of an exception.
6213: if g_debug then
6214: hr_utility.set_location(l_proc, 999);
6215: --
6216: hr_utility.trace('IN EXCEPTION IN execute_appr_rule');
6217: end if;
6218: --
6212: -- in the case of an exception.
6213: if g_debug then
6214: hr_utility.set_location(l_proc, 999);
6215: --
6216: hr_utility.trace('IN EXCEPTION IN execute_appr_rule');
6217: end if;
6218: --
6219: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.execute_appr_rule',
6220: itemtype, itemkey, to_char(actid), funcmode);
6242: -- l_item_key wf_items.item_key%type;
6243: l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_approval_req';
6244: --
6245: BEGIN
6246: g_debug:=hr_utility.debug_enabled;
6247: --
6248: if g_debug then
6249: hr_utility.set_location(l_proc, 10);
6250: end if;
6245: BEGIN
6246: g_debug:=hr_utility.debug_enabled;
6247: --
6248: if g_debug then
6249: hr_utility.set_location(l_proc, 10);
6250: end if;
6251: --
6252: l_to_approve := wf_engine.GetItemAttrText(itemtype => itemtype,
6253: itemkey => itemkey ,
6253: itemkey => itemkey ,
6254: aname => 'TO_APPROVE');
6255: --
6256: if g_debug then
6257: hr_utility.set_location(l_proc, 20);
6258: end if;
6259: --
6260: if g_debug then
6261: hr_utility.trace('l_to_approve is : ' || l_to_approve);
6257: hr_utility.set_location(l_proc, 20);
6258: end if;
6259: --
6260: if g_debug then
6261: hr_utility.trace('l_to_approve is : ' || l_to_approve);
6262: end if;
6263: --
6264: IF l_to_approve = 'YES' THEN
6265: --
6263: --
6264: IF l_to_approve = 'YES' THEN
6265: --
6266: if g_debug then
6267: hr_utility.set_location(l_proc, 30);
6268: end if;
6269: --
6270: result := 'COMPLETE:Y';
6271: --
6273: --
6274: ELSE
6275: --
6276: if g_debug then
6277: hr_utility.set_location(l_proc, 40);
6278: end if;
6279: --
6280: result := 'COMPLETE:N';
6281: --
6288: -- The line below records this function call in the error system
6289: -- in the case of an exception.
6290: --
6291: if g_debug then
6292: hr_utility.set_location(l_proc, 999);
6293: --
6294: hr_utility.trace('IN EXCEPTION IN chk_approval_req');
6295: end if;
6296: --
6290: --
6291: if g_debug then
6292: hr_utility.set_location(l_proc, 999);
6293: --
6294: hr_utility.trace('IN EXCEPTION IN chk_approval_req');
6295: end if;
6296: --
6297: wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.chk_approval_req',
6298: itemtype, itemkey, to_char(actid), funcmode);
6356: l_app_bb_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
6357: itemkey => itemkey ,
6358: aname => 'APP_BB_ID');
6359:
6360: hr_utility.trace('is_different_time_category l_is_blank ::'||l_is_blank);
6361:
6362: hr_utility.trace('is_different_time_category l_app_bb_id ::'||l_app_bb_id);
6363:
6364: if l_is_blank = 'Y' then
6358: aname => 'APP_BB_ID');
6359:
6360: hr_utility.trace('is_different_time_category l_is_blank ::'||l_is_blank);
6361:
6362: hr_utility.trace('is_different_time_category l_app_bb_id ::'||l_app_bb_id);
6363:
6364: if l_is_blank = 'Y' then
6365:
6366: l_total_hours:= HXC_FIND_NOTIFY_APRS_PKG.category_timecard_hrs(l_app_bb_id,'');
6370: itemkey => itemkey,
6371: aname => 'TOTAL_TC_HOURS',
6372: avalue => l_total_hours);
6373:
6374: hr_utility.trace('is_different_time_category COMPLETE:Y ');
6375: result := 'COMPLETE:Y';
6376: else
6377: hr_utility.trace('is_different_time_category COMPLETE:N ');
6378: result := 'COMPLETE:N';
6373:
6374: hr_utility.trace('is_different_time_category COMPLETE:Y ');
6375: result := 'COMPLETE:Y';
6376: else
6377: hr_utility.trace('is_different_time_category COMPLETE:N ');
6378: result := 'COMPLETE:N';
6379:
6380: end if;
6381: