[Home] [Help]
5: g_debug BOOLEAN := hr_utility.debug_enabled;
6:
7: /*
8: Procedure which will dump data into temporary table
9: hxc_tcd_timecards and hxc_tcd_status_count.
10: */
11: PROCEDURE go(p_recurring_period IN varchar2 default Null,
12: p_supervisor_id IN Number,
13: p_reptng_emp IN varchar2,
332: hr_utility.set_location('Entering:'|| l_proc, 10);
333: END IF;
334:
335: DELETE
336: FROM HXC_TCD_TIMECARDS htt
337: WHERE EXISTS
338: (
339: SELECT 1
340: FROM HXC_TCD_TIMECARDS htt1
336: FROM HXC_TCD_TIMECARDS htt
337: WHERE EXISTS
338: (
339: SELECT 1
340: FROM HXC_TCD_TIMECARDS htt1
341: WHERE htt1.resource_id = htt.resource_id
342: AND trunc (htt1.period_start_date) = trunc (htt.period_start_date)
343: AND htt1.approval_status <> htt.approval_status
344: AND htt.approval_status = 'NOTENTERED'
351: * The following script deletes duplicate records for mid period
352: * assignment changes.
353: */
354: DELETE
355: FROM hxc_tcd_timecards t1
356: WHERE EXISTS
357: (
358: SELECT 1
359: FROM hxc_tcd_timecards t2
355: FROM hxc_tcd_timecards t1
356: WHERE EXISTS
357: (
358: SELECT 1
359: FROM hxc_tcd_timecards t2
360: WHERE t1.user_id = p_user_id
361: AND t1.resource_id = t2.resource_id
362: AND t2.user_id = t1.user_id
363: AND t1.period_start_date = t2.period_start_date
414:
415: DELETE FROM HXC_TEMP_TCD
416: WHERE user_id = p_user_id;
417:
418: DELETE FROM HXC_TCD_TIMECARDS
419: WHERE user_id = p_user_id;
420:
421: DELETE FROM HXC_TCD_STATUS_COUNT
422: WHERE user_id = p_user_id;
437:
438: CURSOR c_get_inactive_sessions
439: IS
440: SELECT distinct hxc.user_id
441: FROM fnd_logins fnd, hxc_tcd_timecards hxc
442: WHERE
443: hxc.user_id = fnd.login_id
444: AND
445: (fnd.start_time < sysdate - 1 OR fnd.end_time IS NOT NULL);
458: FETCH c_get_inactive_sessions BULK COLLECT INTO l_login_ids
459: LIMIT 500;
460: EXIT WHEN l_login_ids.COUNT = 0;
461: FORALL i IN l_login_ids.FIRST..l_login_ids.LAST
462: DELETE FROM HXC_TCD_TIMECARDS
463: WHERE user_id = l_login_ids(i);
464:
465: FORALL i IN l_login_ids.FIRST..l_login_ids.LAST
466: DELETE FROM HXC_TCD_STATUS_COUNT
1525:
1526: END insert_notification_id;
1527:
1528: /*
1529: Procedure to insert data in temporary table hxc_tcd_timecards
1530: for timecards which exist.
1531: */
1532:
1533: PROCEDURE insert_timecard_exist(p_user_id IN number)
1540: l_proc := g_package||'insert_timecard_exist';
1541: hr_utility.set_location('Entering:'|| l_proc, 10);
1542: END IF;
1543:
1544: INSERT INTO HXC_TCD_TIMECARDS
1545: (
1546: resource_id,
1547: rec_period_id,
1548: period_start_date,
1598:
1599: END insert_timecard_exist;
1600:
1601: /*
1602: Procedure to insert data in temporary table hxc_tcd_timecards
1603: for timecards which does not exist.
1604: */
1605:
1606: PROCEDURE insert_timecard_not_exist(p_user_id IN number)
1833: elsif(l_period_start_date(j) < l_timecard(i).assg_start_date) then
1834: l_period_start_date(j) := l_timecard(i).assg_start_date;
1835: end if;
1836: end if;
1837: INSERT INTO HXC_TCD_TIMECARDS
1838: (
1839: resource_id,
1840: rec_period_id,
1841: period_start_date,
1956: elsif(l_period_start_date(j) < l_timecard(i).assg_start_date) then
1957: l_period_start_date(j) := l_timecard(i).assg_start_date;
1958: end if;
1959: end if;
1960: INSERT INTO HXC_TCD_TIMECARDS
1961: (
1962: resource_id,
1963: rec_period_id,
1964: period_start_date,
2079: l_period_start_date(j) := l_timecard(i).assg_start_date;
2080: end if;
2081: end if;
2082:
2083: INSERT INTO HXC_TCD_TIMECARDS
2084: (
2085: resource_id,
2086: rec_period_id,
2087: period_start_date,
2254: l_period_start_date(j) := l_timecard(i).assg_start_date;
2255: end if;
2256: end if;
2257:
2258: INSERT INTO HXC_TCD_TIMECARDS
2259: (
2260: resource_id,
2261: rec_period_id,
2262: period_start_date,
2388: end if;
2389:
2390:
2391:
2392: INSERT INTO HXC_TCD_TIMECARDS
2393: (
2394: resource_id,
2395: rec_period_id,
2396: period_start_date,
2469: l_period_end_date
2470: LIMIT 500; -- INCREASE LATER
2471: EXIT WHEN l_period_start_date.COUNT = 0;
2472: FORALL j in l_period_start_date.first..l_period_start_date.last
2473: INSERT INTO HXC_TCD_TIMECARDS
2474: (
2475: resource_id,
2476: rec_period_id,
2477: period_start_date,
2572: approval_status,
2573: user_id,
2574: count (*) cnt
2575: FROM
2576: hxc_tcd_timecards
2577: GROUP BY
2578: supervisor_id,
2579: approval_status,
2580: supervisor,
3086: ,period_start_date
3087: ,period_end_date
3088: ,last_modified_by
3089: ,last_modified_date
3090: FROM hxc_tcd_timecards htt
3091: WHERE user_id = fnd_global.LOGIN_ID';
3092:
3093: l_dynamic_cursor SYS_REFCURSOR;
3094:
3106: ,period_start_date
3107: ,period_end_date
3108: ,last_modified_by
3109: ,last_modified_date
3110: FROM hxc_tcd_timecards htt
3111: WHERE user_id = fnd_global.login_id
3112: AND supervisor_id = supervisor_id
3113: AND approval_status = 'SUBMITTED';
3114: /*AND nvl (column_name